Using Oracle BI Office in OBIEE for Microsoft Excel and Powerpoint

Installing the BI Office Client

To install the BI Office Client:

1. Log in to Oracle BI Answers or Oracle BI Interactive Dashboards with your Oracle BI EE user credentials.

2. Select the More Products link and then select Download Oracle BI for Microsoft Office.

   

3. A dialog box prompts you to save or run OracleBIOffice.exe. Save the file to your local directory.

4.Close the Excel or PowerPoint applications on your system, if open. Navigate to the saved location and double-click OracleBIOffice.exe.

InstallShield Wizard appears.
Note: Microsoft .Net Framework 2.0 is required for the BI Office client. If it is not installed on your computer, you are prompted to exit and install Microsoft .Net Framework 2.0 from the Microsoft Web site.

When the Welcome page appears, click Next.

5. Select the installation type. Typical performs an installation for both Excel and PowerPoint Add-Ins to C:\Program Files\Oracle\BIOffice. Custom enables you to select the install directory and choose the components to install. Select Typical as the installation type, and click Next.

6. Review the setup information and then click Next.

7.When the InstallShield Wizard completes, click Finish.

When Oracle BI EE was installed on your computer, the BI Office Server was installed automatically. The installer performs other configuration tasks such as the following:

Deploying the bioffice.ear file

Updating the instanceconfig.xml file with the location of the OracleBIOffice.exe client

Copying the OracleBIOffice.exe file to the location where Presentation Services is running, which makes the client installation available for download from the More Products menu (as performed in step 1)

Initializing values in the BI Office configuration file (bioffice.xml file)

The following image depicts a sample bioffice.xml file, which contains critical information used by the client to connect to Presentation Services. (Observe the highlighted line in the file, which defines the URL for the Presentation Services.) You create the connection in the BI Office client in the next step.

Note: If any of the above configuration tasks were not performed due to some reason, you can look up the documentation available here to perform these installation and configuration tasks manually.

9. Configure the BI Office client to associate the BI Office Add-Ins with Presentation Services:

Enter the following information in the Connection Detail dialog box:

Server Name: This is the name that you give to the BI Office Server connection. Enter localhost as the name.

BI Office Server: This is the BI Office Server URL. You can enter localhost because the BI Office Server is installed on your local machine.

Port: For a Java 2, Enterprise Edition (J2EE) deployment, the BI Office Server port is 9704. The default value is 80; change it to 9704.

Application Name: The default value is bioffice. When you deploy the BI Office Server application file to OC4J or to your supported J2EE container, you can provide any name for the application. In this case, the default value of bioffice was accepted.

Click Test Connection.

Note: If you have given a different name for the application than the default value of bioffice, enter the name of the application accordingly while creating the connection. You can also edit the connection details after it is created, select the connection from Oracle BI > Preferences, and then click Edit.

Using BI Office with Excel and PowerPoint

Now that you have successfully installed the BI Office client and configured the connections, you are ready to start using the BI Office Add-Ins. In this topic, you are guided to use the features in the BI Office Add-Ins that enable you to analyze and deploy Business Intelligence reports easily in Microsoft Office applications, Excel and PowerPoint.

Some key features provided by BI Office are listed here:

You can copy views from Answers and Interactive Dashboard pages and paste them into Excel and PowerPoint documents by using the Paste feature of BI Office Add-Ins. Copied views are pasted as Office documents (Excel or PowerPoint tables and charts). This functionality also extends to copying compound views from Answers and Dashboards. Compound views copied from Answers or Dashboards are pasted into Microsoft Office documents as native Microsoft Office tables and charts.

For every request, the flat data view provides the entire data set for the original request in a simple tabular format. This view is more suitable for use when users want to obtain only the data and then use the Excel functionality to perform further analysis on that data.

The table view displays results in a tabular format. Users can navigate through the results, add totals, customize headings, and change the formula or aggregation rule for a column.

The pivot table view, available in the BI Office Excel Add-In, presents data from BI Requests with page items as well as a section-based layout if these sections were defined in the BI pivot table view for the request in Answers.

A BI Presentation Catalog browser is provided in Excel and PowerPoint to browse requests (both user created and shared).

Support is provided for prompts defined in the BI request.

If a view selected for insertion has multiple levels from one or more dimensions, you can choose to limit the data for each dimension by deselecting the levels from a level-selection dialog box. You can also edit the prompts and levels after the view is inserted .

You can insert table and chart views into PowerPoint presentations as PowerPoint tables and charts (subject to the limitations of PowerPoint).

You can insert chart views as images in Excel spreadsheets and PowerPoint presentations that can be refreshed.

You can insert chart views as high-quality Flash objects in PowerPoint presentations that can be refreshed.

For BI chart views inserted as native Excel or PowerPoint charts, you can change the chart type and apply other formatting changes by using Excel and PowerPoint charting capabilities. These changes are preserved during refreshes.

You can define Excel-specific conditional formats to data from BI views. These conditional formats can be preserved during data refreshes.

You can add gauge and funnel views in Excel and PowerPoint for better analysis of the reports.

You can secure a slide or worksheet, or you can secure the entire presentation or workbook. When you secure a particular object in Excel or Powerpoint, all BI views on that object—that is, slide, worksheet, presentation, or workbook are secured. Users must authenticate themselves before they can refresh and view this secured BI data. Secure communication is handled through SSL.

Using BI Office with Excel

In this topic, you work with basic views such as table, chart, and pivot table. This topic shows you how to log in to BI Office in Excel, locate a table and insert the data as a list (flat data) into Excel (so that you can manipulate the data by using Excel capabilities), copy a chart from the Presentation Catalog, switch between Excel and Answers to modify the chart, and copy and paste this chart into Excel. You also insert the pivot table view into Excel.

1. To use BI Office Add-Ins, you must log in. Select Start > All Programs> Microsoft Office > Microsoft Excel 2007 to launch Excel.

From the Oracle BI menu on the Ribbon, select Login. (Observe the Oracle BI–specific options added in the toolbar. Note that these menu options are added in Excel during the installation of BI Office Add-Ins.)

2. The Login dialog box appears. Ensure that the connection you created before (localhost) is selected from the Connection drop-down box. Enter Administrator in both the User ID and Password fields, accept the default for the remaining fields, and click Login.

You are now connected to Presentation Services.

3. The Presentation Catalog appears in Oracle B I Task Pane on the right.

To view catalog details, click the plus sign to expand the Shared and Paint Demo folders.

Also note that the Presentation Catalog can be hidden or displayed by clicking the Oracle BI Catalog icon on the toolbar.

4.Click the plus sign () to expand Brand Analysis.

Click the plus sign () to expand the Sales by Brand for Current Year Trends request. (Observe the listed views available for this request.)

The following table identifies the types of request views that are supported, and can be inserted (or copied and pasted) into Excel from the Presentation Catalog:

Table view

Enables you to select levels before insertion into Excel, allowing you to limit the data. You can add Excel formatting to the inserted view. Use this view to see data in the grouped format defined in the Answers request.

Pivot table view

Offers the features of the table view and maintains features defined in Answers (such as page item lists and group sectioning)

Chart view

Inserts charts as defined in Answers

List view (Insert as List)

Available for all table views. This inserts the entire data set for the original request. Use this view when you want to perform filtering, pivoting, charting, and other manipulations in Excel. (In the previous version of BI Office, this was available as flat data view).

Funnel View
Displays results as a three-dimensional chart that represents target and actual values by using volume, level, and color. It is useful for depicting target values that decline over time, such as a sales pipeline.

Gauge View
Shows results as gauges, such as dial, bar, and bulb-style gauges. (Funnel and gauge are two new views, which are introduced in BI Office 10.1.3.4.0.)

Compound view
Provides an assembly of different views on a dashboard. This view must be composed of supported view types (those listed above) to be fully inserted. If the compound view includes unsupported views, only those from this list of views will be pasted into Excel or PowerPoint.

Right-click Table and select Insert as List from the shortcut menu. The Edit Prompts and Levels dialog box appears. Accept the default options and click Insert.

Note: The Edit View option launches Answers in your browser, which in turn enables you to modify the request.

The entire data set for the original request appears in the worksheet area. (Note that the worksheet is named tableView). All Excel capabilities are available for use with this data.

5. Click any cell in the data area. Pivot icons () are added to each column heading. These pivot icons allow you to sort the data, or filter unwanted data (as shown in the screenshots below).

7. In the Presentation Catalog that is already open in the right pane, right-click Chart under the Sales by Brand for Current Year Trends request, and select Insert from the shortcut menu.

Note: The "Insert as Image" option enables you to add the chart as a static image to Excel.

The 3D Line chart from the request appears in the newly added worksheet.

8. Double-click the chart to view data associated with the chart values.

Setting Properties in the bioffice.xml Configuration File

This section describes the properties that you can configure in the bioffice.xml file.

Following is a sample bioffice.xml file:

<bioffice>
 <!-- log -->
 <!-- LogDir. Default is [O4CJ dir]\j2ee\home\applications\bioffice\bioffice\WEB-INF\log -->
  <!-- <property name="LogDir">D:\BIOffice\Server\log\</property> -->
  <!-- LogLevel Never = 1; Error = 2; Warning = 3; Msg = 4; Debug = 5; -->
  <property name="LogLevel" type="int">3</property>
  <!-- saw -->
 <property name="SawBaseURL">http://localhost/analytics/saw.dll</property>
  <!-- Does SAW use SSO (Single Sign-On): yes = 1; no = 0; -->
 <property name="SawUseSSO" type="int">0</property>
  <!-- Specifies the maximum number of rows to be returned by SAW executeXMLQuery or fetchNext method. -->
  <property name="SawMaxRowsPerPage" type="int">5000</property>
 <!-- Parse hyperlink column: turn on = 1; turn off = 0; -->
 <property name="SawParseHyperLink" type="int">1</property>
 <!-- Parse percentage column: turn on = 1; turn off = 0; -->
 <property name="SawParsePercentageColumn" type="int">1</property>
 <!-- Fetch fresh data: turn on = 1; turn off = 0; -->
  <!-- Turn on will make SAW re-submits the query to refresh data, in stead of reading data from cache. -->
 <property name="SawFetchFreshData" type="int">0</property>
</bioffice>

Setting Up an SSO-Enabled Oracle BI Presentation Server for BI Office

Oracle BI Office currently cannot be fully integrated with SSO. If your Oracle BI Enterprise Edition implementation is SSO-enabled, users can use their SSO credentials from the Microsoft Excel or PowerPoint add-in to log in to the Oracle BI Office system. However, the following options from the client add-in insert menu are not supported:

• Edit View

• Insert as Image
  

• Insert as Flash

To enable the integration of BI Office with SSO-enabled Oracle BI, you must deploy a separate SAW bridge application (analytics.ear) and grant trusted IP address (where BI Office is installed) access privilege to the new SAW bridge without going through SSO authentication. You must configure the BI Office server to send Web service requests to this SAW bridge that bypasses SSO. Depending on the SSO server type, the configuration may differ. Following is an example of how to integrate BI Office with Oracle SSO-enabled Oracle BI:

1. On the same machine where BI Presentation Services Plug-in has been deployed, deploy another Presentation Services Plug-in using the file analytics.ear:

   Locate analytics.ear in the directory OracleBI_HOME/web.

2. Name the new Plug-in "analyticsSOAP". Make the same modifications to the web.xml file for this analyticsSOAP servlet that were made to the web.xml file for the default "analytics" servlet.

3. Make the following modification to the file mod_osso.conf to open analyticsSOAP to requests from the BI Office server:

   (The mod_osso.conf is located in the directory Oracle_HOME/Apache/Apache/conf.)
   

   <Location /analyticsSOAP>
   Require valid-user
     AuthType Basic
     Allow from <IP address of BI Office server>
     Satisfy Any
   </Location>

   where
   

   <IP address of BI Office Server> is the IP address of the BI Office server, for example: 10.155.324.7
   

4. Locate the Office Server configuration file (bioffice.xml). The configuration file bioffice.xml can be found in the directory:

   OAS_HOME\j2ee\home\applications\bioffice\bioffice\WEB-INF
   


5. Update the following properties:
   
   
   • "SawBaseURL" to point to the new analyticsSOAP deployed in the previous steps
     
   • "SawUseSSO" to indicate that you have enabled SSO for BI Office and Presentation Services. Set the value to "1". This setting suppresses the unsupported options from the client add-in insert menu.
     

   Example:

   <property name="SawBaseURL">http://localhost:port/analyticsSOAP/saw.dll</property>
   <!-- Does SAW use SSO (Single Sign-On): yes = 1; no = 0; -->
   <property name="SawUseSSO" type="int">1</property>