This chapter describes the integration of Oracle BI Enterprise Edition with Microsoft Office and how to configure the Oracle BI Add-in for Microsoft Office. It contains the following topics:
For information on using the Oracle BI for Microsoft Office after it is installed, see the Help system that you can access from the Microsoft Office application such as Excel or PowerPoint.
From the Oracle BI EE Home page, you can download and install the Oracle Business Intelligence Add-in for Microsoft Office. This add-in enables you to access and run Oracle BI EE analyses directly within your Microsoft Office workspace. The refreshable analyses leverage not only native Office functionality but also the scalability and performance of the Oracle BI Server.
When the Oracle BI Add-in for Microsoft Office is properly installed and configured, you can work with analyses directly in Microsoft Excel and PowerPoint using the following means:
You can copy and paste into Microsoft Office documents the following view types: table, pivot table, graph, funnel graph, gauge, title, and filters view. Copied views are pasted as Microsoft Office documents, including Excel or PowerPoint tables and graphs. By pasting these views, you leverage Office functionality while referencing the Oracle BI semantic layer. Only these Oracle BI EE views that are supported by the Oracle BI Add-in for Microsoft Office are pasted into Office documents.
This section provides the following information:
The following general guidelines apply to copying and pasting all supported views:
You can copy views from the Analysis editor and from dashboard pages and paste them into Microsoft Excel and PowerPoint documents. You can use the Copy link for an analysis on a dashboard page to copy the analyses definition and current state as applicable of Oracle BI EE views on dashboard pages and paste the view objects into Microsoft Office documents. The term "state" includes criteria such as drilling in columns, values for filters, and selections on the page edge.
For example, you can drill on a graph from year to quarter, then drill from region to the district level. Use the Copy link, if available for that view, to copy this state and paste it into a Microsoft Office document using the Oracle BI Add-in for Microsoft Office. When you refresh the Oracle BI EE views inside the Office document, the data is refreshed and displayed based on this state of the request. Filters are preserved and can be changed subsequently in Microsoft Office documents.
All views pasted from the Oracle BI Add-in for Microsoft Office are refreshable, even views pasted as images or as Flash objects. Title views cannot be refreshed so that user customizations are maintained. You can customize native Office objects, but you cannot customize images.
You can copy compound views from the Analysis editor and dashboard pages. Compound views are pasted into Microsoft Office documents as native Office tables and graphs.
When you copy and paste a table or pivot table:
You can specify whether to include the title view and filters for the analysis.
Table and pivot table views from Oracle BI EE are pasted as native Office format tables.
An inserted pivot table view presents data from an analysis with page items as well as a section-based layout, if these had been defined in the Oracle BI EE pivot table view for the analysis.
When you copy and paste a graph, funnel graph, or gauge:
Graph views from Oracle BI EE are pasted as native Excel or PowerPoint graphs, as static but refreshable images, or as high-quality Flash objects (in PowerPoint).
You can change the graph type and apply other formatting changes using Excel and PowerPoint graphing capabilities. These changes are preserved during data refreshes.
Gauge views, funnel graph views, and time-line series graph views from Oracle BI EE can be pasted into Office documents as static but refreshable images and as Flash objects.
You can start Microsoft Excel and PowerPoint and use the Oracle BI menu or ribbon to sign in and display the Oracle BI Presentation Catalog. The Catalog browser is presented as a pane in Excel and PowerPoint to browse analyses, both user-created and shared, as shown in Figure D-1. You can also display the individual views that are available for each analysis.
Gauge views and funnel graph views from Oracle BI EE are inserted from the Catalog browser in the following formats:
Image, in Excel and PowerPoint
Flash, in PowerPoint only
Oracle BI for Microsoft Office provides useful features for working with analyses in Microsoft Excel and PowerPoint, as described in the following sections:
For additional information on the features of Oracle BI for Microsoft Office, see the Help system that you can access from the Microsoft Office application such as Excel or PowerPoint
Oracle BI for Microsoft Office provides the following general features:
Office access — When installed on Excel 2007, the Oracle BI Add-in for Microsoft Office functionality is made available via a native Office ribbon interface. When installed on Excel 2003, the Oracle BI Add-in for Microsoft Office-in's functionality is made available via a menu and a toolbar. In both cases, an Office pane is available for browsing the Oracle BI Presentation Catalog and for selecting views to insert inside Office documents.
Ability to secure BI data in BI views inserted inside Excel spreadsheets or PowerPoint presentations — This feature enables you to secure inserted BI tables and graphs so that users must present credentials to view the Oracle BI data. When you secure the data, the BI data is removed from the document. Only the view definitions are retained, which allows the Oracle BI Add-in for Microsoft Office to refresh the data within these BI views.
Views — See "Copying Views and Analyses" for information on working with these view types: table, pivot table, graph, funnel graph, and gauge.
Graph customization — When you work with native graph objects in Excel and PowerPoint, you can customize the look and feel of the inserted views by editing the objects and changing the graph type and changing the formatting of various graph components (such as the axes, legend, and title). These customizations are retained when the views are refreshed.
Prompts — You can use the following features of Oracle BI EE prompts:
Multi-selection of values
Search for prompt values
Shuttle UI for selecting multiple values
Enter ranges of values
Cascading prompts where the value of one prompt limits the values available in a subsequent prompt (Year = 2010 will restrict Quarters to only Q1, Q2, if the current date is May 10 2010, for example.)
Runtime operator selections where you can select operators (such as equal, greater than, and less than) in the same ways that you select prompt values.
Column selection — When you insert an Oracle BI EE view in an Excel or PowerPoint document, you can select a limited set of columns in that view to be inserted. When the view is inserted, you can select the view and click the Edit Prompts and Levels menu item, toolbar button, or ribbon item to modify the original selection and to add or remove columns from the view.
You can edit or change the levels or prompts for the following types of views:
Views that you inserted from the Oracle BI Presentation Catalog within Excel and PowerPoint.
Views that you copied using the Copy link in analyses on dashboard pages and pasted into Excel and PowerPoint documents using the Oracle BI Add-in for Microsoft Office's Paste option.
Server independence — Views inserted in Excel and PowerPoint documents by the Oracle BI Add-in for Microsoft Office that is connected to one server can be refreshed by connecting Oracle BI for Microsoft Office against a different server, in two ways:
If you insert the view into Microsoft Office by copying and pasting from Oracle BI EE, then the view is considered to be detached from the original analysis that was saved in a directory in the catalog. Each data refresh obtains the data that corresponds to a snapshot of the analysis definition.
If you insert the view into Microsoft Office by selecting it from the Oracle BI Presentation Catalog, then the analysis must exist in the same directory in the catalog on the two servers.
Support for the Oracle BI Security model, including support for both encrypted (SSL) and Single Sign-On modes — The same login that you use for Oracle BI EE is available for the Oracle BI Add-in for Microsoft Office. For information, see "Configuring SSL on Oracle WebLogic Server for Oracle BI for Microsoft Office" and "Configuring an SSO-Enabled Oracle BI EE for Oracle BI for Microsoft Office".
Logging — You can enable logging in the Oracle BI Add-in for Microsoft Office on the Advanced tab of the Preferences dialog. Log messages are written in an HTML format to files in the Oracle BI Add-in for Microsoft Office's installation folder.
Oracle BI Add-in for Microsoft Excel provides the following features for working with analyses:
Formats — You can change the formats of data items that are displayed in Oracle BI EE tables and pivot tables that were imported into Excel and PowerPoint documents. To change the format of an item in an Excel sheet, you must modify the style for that cell. These formatting changes are then preserved even during refreshes. Furthermore, these formatting changes are applied to all cells with the same style.
Conditional formats — When you import a view, any conditional formatting that was defined in Oracle BI EE is not maintained in Microsoft Excel. You can define Excel-specific conditional formats for data that was imported from BI views. Conditional formatting that you define using Excel's native conditional formatting features can be preserved during data refreshes. This requires that the Oracle BI Add-in for Microsoft Office perform cell-level comparisons to preserve the formatting while updating the cell values with updated data, which is performance-intensive.
You select the Preserve Conditional Formatting option in the General tab of the Preferences dialog if you want to preserve formatting at the cost of performance. If performance is more important then preserving conditional formatting, then deselect this option to remove the formats when the view is next refreshed.
Customization — You can customize the default appearance of Oracle BI EE table and pivot table views that are inserted in Excel worksheets using an Excel template that is installed during the client Oracle BI Add-in for Microsoft Office installation. The template resides in the following location:
where ADDIN_HOME is the location where you installed the Oracle BI Add-in for Microsoft Office.
Oracle BI Add-in for Microsoft PowerPoint provides the following features for working with analyses:
Flash objects — You can insert or copy and paste Oracle BI EE graphs as Flash objects.
Crosstab — Pivot tables inserted in PowerPoint documents are displayed with a true crosstab look and feel.
Customization — During data refreshes, tables and pivot tables retain most of the customizations that you make to the views inserted in PowerPoint documents. Examples of customization include font properties such as font size, color, and bold or italic styles.
The following list outlines features of Oracle BI EE that are not available in Oracle BI for Microsoft Office. If you try to paste into Oracle BI for Microsoft Office an analysis or view that uses any of these features, then you see a warning message that the paste cannot occur.
Graph section sliders
Section sliders in general
KPIs and Scorecard views
Table and graph views with sections and pages
This section describes how to install and configure Oracle BI for Microsoft Office. It includes the following topics:
The Oracle BI Office Server is a server-side J2EE application. The BI office client on a Windows system communicates with the BI Office Server, which itself communicates with the Oracle BI Presentation Server using Web services. The configuration file for the BI Office Server is named bioffice.xml.
The following list describes the steps that are required to install and configure Oracle BI for Microsoft Office:
The administrator runs the installer for Oracle BI EE.
The administrator configures Oracle BI for Microsoft Office to work with Secure Socket Layer (SSL) and Single Sign-On (SSO), as described in the following sections:
Users download and run the installation file for the Oracle BI Add-in for Microsoft Office from the Oracle BI EE Home page.
Users configure for connections from the Oracle BI Add-in for Microsoft Office to Oracle BI Presentation Services.
When you run the installer for Oracle BI EE, the installer performs the following tasks for Oracle BI for Microsoft Office:
Installs and configures the Oracle BI Office Server into the Oracle WebLogic Server container.
The installer for Oracle BI EE configures the BI Office Server to point to the Oracle BI Presentation Services server by setting the SawBaseURL property in the bioffice.xml configuration file. After Oracle BI EE is installed, you can modify the configuration of the BI Office Server, as described in "Setting Properties in the bioffice.xml Configuration File".
Deploys the bioffice.ear and biofficeclient.war files.
Content designers and end users can download and run the installation file for the Oracle BI Add-in for Microsoft Office from the Oracle BI EE Home page.
Important Notes: Note the following before installing the Oracle BI Add-in for Microsoft Office:
If you have an existing version of the Oracle BI Add-in for Microsoft Office, then remove that version before you run the installation program to obtain the new version. Run the Microsoft Windows "Add or Remove Programs" feature in the Control Panel to uninstall the existing version.
Microsoft recommends uninstalling all add-ins before upgrading to a new version of an Office product. If you plan to upgrade from Microsoft Office 2003 to 2007, then ensure that you first uninstall the Oracle BI Add-in for Microsoft Office. With Office 2003 still installed, do one of the following:
Run the client OracleBIOffice.exe file and use the wizard to uninstall the Oracle BI Add-in for Microsoft Office.
Use the Microsoft Windows "Add or Remove Programs" feature in the Control Panel to uninstall the Oracle BI Add-in for Microsoft Office.
Then upgrade to Office 2007 and run the OracleBIOffice.exe file to install the Oracle BI Add-in for Microsoft Office.
If the following prerequisite software does not exist on your computer, then you are prompted to install it. You might be prompted to restart the computer after installing the software. You can find this software on the Microsoft Web site:
Shared Add-In Extensibility Microsoft .Net 2.0
Shared Add-In Support Update Microsoft .Net 2.0
Microsoft .Net Framework 2.0.
To install the Oracle BI Add-in for Microsoft Office:
Ensure that the administrator has granted you the Access to Oracle BI for Microsoft Office privilege.
For information on this privilege, see Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
Ensure that you are familiar with the "Important Notes" that are documented earlier in this section.
Close all Microsoft Office applications.
Display the Home page for Oracle BI EE.
In the Get Started section, click the Download BI Desktop Tools link, then click Oracle BI for MS Office.
In the Download dialog, specify a location to which you want to download the executable file with which you will install Oracle BI for Microsoft Office.
When the file is downloaded, display Windows Explorer and double-click the file that you downloaded to run it.
During the installation, you might be prompted to install prerequisite software. After installing the prerequisite software, you might be prompted to restart the computer.
After restarting, navigate back to the location of the downloaded file and double-click it to reinitiate the install.
From the Welcome screen, select Next.
Follow the instructions in the wizard to select the appropriate options for your system.
After all components are installed, the Install Shield Wizard Complete dialog is displayed. Click Finish.
See "Copying Results to Microsoft Applications" for information on how end users work with Oracle BI for Microsoft Office.
When the software is downloaded and installed, each user must enter the connection information in the Preferences dialog for Oracle BI Add-in for Microsoft Office on the client computer to enable communication with the BI Office Server.
To configure connection information from the Oracle BI Add-in for Microsoft Office to Presentation Services:
Open Microsoft Excel or Microsoft PowerPoint. (The connection information is shared, therefore you can enter it through either application and it will be available to both.)
From the Oracle BI menu, select Preferences.
On the Connections tab, select New.
Complete the following fields in the Connection Detail dialog:
Server Name — Enter the name that you want to use for this connection.
BI Office Server — Enter the URL for the BI Office Server (for example: bioffice-server.example.com).
Application Name — Enter the Application Name that was defined for the BI Office Server when the BI Office Server application was deployed to its Oracle WebLogic Server container. The name defaults to "bioffice", but if another name was specified during deployment, then enter that name in this field.
Note:The value in the Application Name field is appended to the values for BI Office Server and Port to construct the URL that the client uses to connect to the BI Office Server. For example: http://bioffice-server.example.com:9704/bioffice
Port — Enter the port for the BI Office Server. The default is 80.
Note:If your environment is SSL enabled, then see "Configuring SSL on Oracle WebLogic Server for Oracle BI for Microsoft Office" for the required steps.
If you require security for the data that is passed between the client and server, then host Oracle BI EE on a secured HTTPS server.
Click Test Connection to test the connection between the Oracle BI Add-in for Microsoft Office and the BI Office Server. (Note that this does not test the connection between the BI Office Server and Presentation Services.)
Complete the fields on the General and Logging tabs as appropriate.
See the Help for Oracle BI Add-in for Microsoft Office for information on the Preferences dialog.
Click OK to close the Preferences dialog.
This section describes the properties that you can configure in the bioffice.xml file. You can modify the contents of this file as needed. Before making any modifications, ensure that you make a backup copy of the file. After saving modifications to the file, ensure that you restart the BI Office Server.
Following is a sample bioffice.xml file.
<bioffice> <!-- log --> <!-- LogLevel Never = 1; Error = 2; Warning = 3; Msg = 4; Debug = 5; --> <property name="LogLevel" type="int">5</property> <!-- saw --> <!-- property name="SawBaseURL">http://localhost:9704/analytics/saw.dll</property --> <!-- Set SAW version manually: 11 (for 11.1). This is optional. SAW version can be detected automatically by Office Server. --> <!-- <property name="SawVersion">11</property> --> <!-- Does SAW use SSO (Single Sign-On)? Yes = 1; No = 0; --> <property name="SawUseSSO" type="int">0</property> <!-- In SSO case, sawBaseURL is for web service only, sawWebURLforSSO is for external web request. In none SSO case, sawBaseURL is for both web service and external web request. --> <!-- <property name="SawWebURLforSSO">http://localhost/analytics/saw.dll</property> --> <!-- Specify the maximum number of rows to be returned by SAW executeXMLQuery or fetchNext method. --> <property name="SawMaxRowsPerPage" type="int">5000</property> <!-- Parse hyperlink column? Yes = 1; No = 0; --> <property name="SawParseHyperLink" type="int">1</property> <!-- Parse percentage column? Yes = 1; No = 0; --> <property name="SawParsePercentageColumn" type="int">1</property> <!-- Fetch fresh data? Yes = 1; No = 0; --> <!-- Turn on will make SAW re-submits the query to refresh data, instead of reading data from cache. --> <property name="SawFetchFreshData" type="int">0</property> <!-- Office --> <!-- Compress result sent to Office client? Yes = 1; No = 0; --> <property name="OfficeCompressResult" type="int">1</property> <!-- If compress Office result, specify the minimum size (in bytes) to trigger the compression. --> <property name="OfficeCompressResultMinSize" type="int">16384</property> </bioffice>
The following table describes the bioffice.xml properties:
|Property Name||Valid Values||Description|
|LogDir||N/A||Enter the path to the directory for the BI Office Server to write the log file.|
|LogLevel||1 = Never
2 = Error
3 = Warning (Default)
4 = Message
5 = Debug
|Set the level of information you want to be written to the log file.
Note that as the log level value increases, performance is impacted. Therefore do not set this property to debug (5) except when troubleshooting an issue.
|Enter the URL for Oracle BI Presentation Services. This property is set initially by the Oracle BI EE installer.
Important: If SSO is enabled, then enter the URL for the protected analytics servlet that you deployed when configuring BI Office to integrate with the SSO-enabled Oracle BI Server. The URL that is specified for this property will be used for Web service requests between the BI Office Server and Presentation Services. For more information, see "Configuring an SSO-Enabled Oracle BI EE for Oracle BI for Microsoft Office".
If SSO is not enabled, then SawBaseURL is the only URL that is required to access Presentation Services.
|SawUseSSO||0 = No (Default)
1 = Yes
|Set this property to 1 if the Oracle BI EE implementation is enabled for SSO.|
|When SSO is enabled, use this property to enter the public URL that allows external users to access Oracle BI EE via SSO from the Oracle BI Add-in for Microsoft Office.
See Oracle Fusion Middleware Administrator's Guide for Oracle Access Manager for information on public URLs and SSO.
|SawMaxRowsPerPage||N/A||Default setting is 5000, which is acceptable in most cases. Specifies the maximum number of rows to be returned by the executeXMLQuery or fetchNext method in Presentation Services. Increase this setting in a multiple-user environment to reduce the number of calls back to Presentation Services, provided that sufficient memory is available to handle the volume of data.|
|SawParseHyperLink||0 = Off
1 = On (Default)
|When set to 1, the BI Office Server parses the HTML content in the results set and converts the content to HTML display format in Excel. When set to 0, the BI Office Server inserts the hyperlink text as is; that is, as text with markup tags. If your data does not include any hyperlink text, then set this property to 0 for better performance.|
|SawParsePercentageColumn||0 = Off
1 = On (Default)
|In the Analysis editor of Oracle BI EE, if the Column Properties dialog box for a given column is configured as follows: On the Data Format tab, Override Default Data Format is checked and Treat Numbers as Percentage is selected; then setting this property to 1 instructs the BI Office Server to treat the number as a percentage (divide by 100). If the user formats the data as a percentage in Excel, then the data displays correctly. If the property is set to 0, then no percentage processing is done by the BI Office Server.
For more information on setting column properties, see "Column Properties dialog".
|SawFetchFreshData||0 = Off (Default)
1 = On
|Default is 0. Set this property to 1 to resubmit the query to refresh data. When set to 0, the data is read from the Presentation Services cache.|
|OfficeCompressResult||0 = No
1 = Yes (Default)
|This property is turned on by default to compress the analysis result set on the BI Office Server before being sent to the BI Office client. The BI Office client detects this result set as compressed and decompresses the result, transparently to and without any intervention on the part of the user. Define the minimum file size that will trigger the compression in the OfficeCompressResultMinSize property.
Guidelines for setting this property: If the results are expected to be large, (more than 16KB or 32KB), then turn compression on. There is a performance overhead associated with compressing the result set on the server side, and then decompressing it on the client side. The benefit of compression is a significant reduction in the size of the data sent from the server to the client; because the results are usually in the form of XML, which is verbose, compression can shrink the result set by as much as 90%. The cost versus benefit can vary for your environment, therefore some trial and error might be required before achieving the optimal setting.
|OfficeCompressResultMinSize||Express as an integer in bytes. Default value is 16384.||If the property "OfficeCompressResult" is turned on, then enter the minimum size file in kilobytes that will trigger compression. See the description for the OfficeCompressResult property for guidelines on setting these properties.|
The following procedure describes how to configure SSL in a standalone Oracle WebLogic Server environment for use with Oracle BI for Microsoft Office on Windows.
To configure SSL for Oracle BI for Microsoft Office:
Follow the instructions in Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition to configure SSL for Oracle BI EE.
Oracle BI for Microsoft Office uses the SSL configuration that you enable for Oracle BI EE.
Update the SawBaseURL element in the bioffice.xml file of the BI Office Server computer as shown in the follow line, replacing server-name and port-num with the appropriate values:
In the Oracle BI Add-in for Microsoft Office on the client computer, create a connection by completing the following fields:
Name: Enter a name for the connection, such as My BI Office.
Office Server: Enter the server machine name, such as bioffice-server.example.com
Port: Enter the port number for the connection.
Use Secure Sockets Layer (SSL): Select this box.
Tip:If you can connect to SSL successfully in the browser, yet the connection fails in the client, then confirm that the certificate was exported and imported properly. See Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition for information.
Oracle BI for Microsoft Office currently cannot be fully integrated with Single Sign-On. If the Oracle BI EE implementation is SSO-enabled, users can use their SSO credentials from the Oracle BI Add-in for Microsoft Excel or the Oracle BI Add-in for Microsoft PowerPoint to sign into Oracle BI for Microsoft Office.
For users to perform the Edit View function in Oracle BI for Microsoft Office, you must configure the BI Office Server to route these requests to the public URL for Presentation Services. The user will be challenged for credentials to sign in to Oracle BI EE.
Depending on the server type for Single Sign-on, you might configure SSO slightly differently. The following procedure provides an example of how to integrate Oracle BI for Microsoft Office with an SSO-enabled Oracle BI EE.
To configure SSO for Oracle BI for Microsoft Office:
Follow the instructions in Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition to configure SSO for Oracle BI EE.
Oracle BI for Microsoft Office uses the SSO configuration that you enable for Oracle BI EE.
On the BI Office Server computer, open the bioffice.xml configuration file for editing.
Update the following properties in the file:
SawBaseURL to point to the analytics-ws servlet that you deployed as part of configuring SSO for Oracle BI EE.
SawWebURLforSSO to point to the URL that is used for public and external users to access Oracle BI EE.
Note:When specifying the URL for SawWebURLforSSO, you can use domain names in the URL. However, note the following special cases:
If the Oracle BI Server, the server for Oracle BI for Microsoft Office, and the client computer are running in different domains, then you must specify the complete domain name. For example, specify:
If the Oracle BI Server or the server for Oracle BI for Microsoft Office is running on a computer that has two or more IP addresses configured, then you must specify the correct IP address in the URL for the location of the Oracle BI Server.
SawUseSSO to indicate that you have enabled SSO for Oracle BI for Microsoft Office and for Presentation Services. Set the value to "1".
The following example shows properties set in the bioffice.xml file for SSO.
<property name="SawBaseURL">http://localhost:port/analytics-ws/saw.dll</property> <!-- Does SAW use SSO (Single Sign-On): yes = 1; no = 0; --> <property name="SawWebURLforSSO">http://localhost:port/analytics/saw.dll</property> <property name="SawUseSSO" type="int">1</property>
See Oracle Fusion Middleware Administrator's Guide for Oracle Access Manager for information on public URLs and SSO.
Save your changes and restart the BI Office Server.