by Shiva Molabanti

OBIEE and SmartView integration – A Great Oracle BI / MS-Office Add-in

Oracle Hyperion SmartView for Office Integration.

SmartView is much more powerful than its well-known predecessor, the Hyperion Excel Add-In. It not only connects to Essbase, Hyperion Planning, and now to Oracle BI EE. The Smart View 11.1.2.2 Add-in replaces the Oracle BI Office Add-in as the primary Office application for Oracle BI Foundation. For benefit of the users, both are still available with OBI 11.1.1.7.

Smart View allows users to connect to Oracle BI EE server and utilize the content created in Oracle Business Intelligence Answers dashboards and analyses. In addition to working with pre-created content, Smart View users can create ad hoc queries in the form of simple views with the View Designer, using Oracle BI EE Presentation Server as the data source. The views created can then be published to the Oracle BI EE Presentation Catalog, where they can be edited further.

SmartView Features

When connected to an Oracle BI EE data source in Smart View, you can:

• Connect to Oracle BI EE Presentation Server.

• Navigate through the Oracle BI EE Presentation Catalog.

• Import pre-created BI Answers views from Presentation Catalog into Microsoft Excel, PowerPoint, and Word. For example, you can: 

• Insert Oracle Business Intelligence tables, BI pivot tables, and BI graphs into Excel as refreshable, editable objects. 

• Apply Excel formatting to BI data; formats are retained when you refresh. 

• Copy Oracle BI EE Dashboard content and paste it into Smart View client documents. The data, metadata, and view layout are copied.

• Create simple views in Excel using Oracle BI EE Presentation Server as the source for metadata and data.

• Interact with content imported into Smart View documents, such as prompt selections.

• Manage the imported content in Smart View; for example, refresh, mask, copy and paste, and document contents.

• Mask BI data from Oracle BI EE objects in Office so that users must log in to view the data. Masked objects can be viewed upon refresh.

• You can use Visual Basic to program your own Oracle BI EE interfaces. 

The New VBA Functions to Support Oracle BI EE are:

• InsertView—Insert an Oracle BI EE object into Smart View 

• EditPrompts—Edit the prompts of a view.

• EditPagePrompts—Edit the page selections of a view.

• GetPrompts—Get prompt values of a view.

• GetPagePrompts—Get page selections of a view.

• DeleteView—Delete a view in an Office application.

• DirProperties—Fetch properties of a directory.

• InvokeMenu—Invoke the Oracle BI EE ribbon in Smart View

Install SmartView Add-in for MS Office

1. In Oracle BI EE Analytics Home page, click the arrow next to Download BI Desktop Tools, and then select Smart View for MS Office.

2. Choose an option: 

• Click Run to run the installer directly. If the User Account Control dialog box is displayed, click Yes, and then continue to step 3. 

• Click Save to save the Smart View installer file to a directory to which you have access and from which you can run executable files. Open the folder where you saved the Smart View executable and double-click it to run it. Then continue to Step 3.

Examples of SmartView Integration

Example 1: Connect to OBIEE Analysis from Excel spreadsheet with SmartView Add-in

I have a year-order Sales report in OBIEE catalog:

Connect to OBIEE from SmartView and insert this report in Excel spreadsheet. Once you imported the OBI Analysis view, you can use all native Excel features (e.g. Cell formatting, Text formatting, Excel functions like Sum,Avg, Min,Max and any other functions etc..).

If we apply any changes to OBI analysis in presentation services, the formats applied in spreadsheet retain same and just the data will be refreshed with respect to the OBI Analysis updates.

In following example I have applied a filter on year = 2008 in OBIEE presentation services.

Now in Excel, The formats are retained the same, but just data is limited to year 2008, since there is a filter in OBIEE Analysis. (If data is not changed immediately, just click the refresh button available in Excel toolbar ribbon)

Example 2: Create an analysis in Excel SmartView and save in OBIEE catalog

To create a new analysis from SmartView, Navigate to Oracle BI EE tab. And then click on “View Designer” button.

The View Designer button will open the Oracle BI EE presentation services Subject Areas. Expand the Subject Areas of your choice and drag the columns of analysis into the areas of report layout.

The SmartView has variety of View types, for demonstration purpose I am using Pivot table.

The Available Report View types are:

In my example I am creating a pivot table, so dragged the Order type in Rows, Order Status in Columns, and Revenue in Measure block. And I am grouping the result set by year, so moved the Per Name Year in Section part.

The wizard will generate a report like this in Spreadsheet:

Now to save this report in OBIEE catalog, click on publish View button.

It will open the OBIEE catalog folders. You can save the report into a folder of your choice that created in OBIEE catalog.

Now the report is saved in OBIEE catalog like the analysis we generally save within OBIEE:

See how the report is rendered in OBIEE Presentation services.

There are many scenarios where we could use this powerful SmartView Add-in for a great flexible usage of OBIEE reports.

Shiva Molabanti is a Manager and Senior Architect at KPI Partners.  He is a business intelligence enthusiast who likes blogging about acquisitions in the BI space, technical workings of BI tools, and Oracle Business Intelligence.  Visit Shiva at his personal blog: http://shivabizint.wordpress.com/