Friday Aug 16, 2013

How To Create New Report From Scratch Within Excel Using Smart View?

This article describes the steps to create a new report from scratch within Excel using Smart View:
  1. Connect to OBIEE instance using Smart View as mentioned in https://blogs.oracle.com/ExalyticsOBIEE/entry/integrating_obiee_with_microsoft_office
  2. Click on Tab OBIEE ->View Designer Pane.
    The 'View Designer' pane will display a layout similar to the 'Criteria' Pager in Analytics.
    Here you can selecting from any subject area in the BI Catalog and drag and drop the dimensions and measures you want into respected rows,columns and Measures section.

    View Designer
  3. Add filters if required.

    Add Filters
  4. This report can then be manipulated either as an Excel pivot table or an OBIEE pivot table.

    Report
  5. Calculated Field can be added through 'Options->Fomulas'.

    Calculated Field

    Calc Field Report

  6. Now create Charts or Graph if required through 'Options->PivotCharts'.You can do any modification that you want using 'Options' tab.

    Pivot Chart

    Excel Chart

  7. Save the Excel document to your personal folder or you can upload to the BI Presentation Catalog using the Publish View button.

    Save

  8. Open the report in the Analysis Editor, just like any other analysis in the catalog.

    9
 NOTE:
Only SmartView 32-bit is supported with OBIEE 11.1.1.7.x.
SmartView 64-bit is not supported since it requires MS Office 2010, which is not certified with OBIEE 11.1.1.7.x

Tuesday Apr 09, 2013

OBIEE 11g:Excel Connectivity Using ODBC Drivers

This blog describe the method to connect Excel to BI Server using ODBC. 

For this you dont need to use the Analytics. You can use Excel to create the report by connecting to BI server using ODBC Datasource.

Two steps to achieve this are:

  1. Create a system DSN
  2. Create the report using Microsoft Query.

Step 1: Create  the system DSN

1.Go to Control Panel->Administrative Tools->Data Sources (ODBC)->System DSN


2. Select Oracle BI Server and Click Add



3.Click on Finish and enter a name for your DSN and enter the BIServer name and click finish




Now your BI Server DSN Configuration is done.

Step 2. Create the report using Microsoft Query.

1. Open the Excel and Click on Microsoft Excel->Data->From Data Sources->From Microsoft Query


2. Select the DSN name you created above and enter the credentials to log in to BI Server.

3. A new screen to choose column will appear.


4. Drill down on the dimensions and select the required tables as shown below:


5. If you get a message like below, click on OK


6. Clicking OK will take you to the screen shown below:

7. Click on Add Tables icon from the menu. This will allow you to select the tables that you want in your report.

 For example,

7.1. In this case we are using Sample Sales lite rpd and we will add the fact table Base Facts (click on Add)


7.2. Base fact will be added to Microsoft query. Drag Actual Unit Price from ‘query from …’ section to the Result pane header.

              (Note the difference in screenshot from step 6)

8. Close the window for ‘Microsoft Query’. Now ‘Import Data’ wizard will open.

9. Select the type of table you wanted. (In this example we have used the pivot table report)

        a.Drag Product to Drop Row Field Here
        b.Time to Drop columns Field Here
        c.Actual unit price to Drop Data Items here.
        d.The report will be generated in excel like below:








About

This Blog is for sharing the latest information regarding Exalytics and OBIEE

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today