|Oracle® Business Intelligence New Features Guide
Part No. E10416-04
Oracle Business Intelligence for Microsoft Office includes the Oracle Business Intelligence Office server and the Oracle Business Intelligence Add-in for Microsoft Office client. The client includes both the Oracle BI Office Add-In for Microsoft Excel and the Oracle BI Office Add-in for Microsoft PowerPoint. This chapter describes how to install and configure the BI Office server and client.
This chapter includes the following topics:
Following are the required components for the BI Office client and server. For the detailed list of system requirements for Oracle Business Intelligence Enterprise Edition and for updates to this list, see System Requirements and Supported Platforms for Oracle Business Intelligence Suite Enterprise Edition, Version 10.1.3.3.
The Oracle BI EE version 10.1.3.3 installer installs the BI Office server. The installer performs the following tasks:
Deploys the bioffice.ear file.
Initializes values in the BI Office configuration file.
Copies the client OracleBIOffice.exe file to the location where Presentation Services is running.
Updates the instanceconfig.xml file with the location of the client OracleBIOffice.exe.
Backs up the BI Office server configuration file, bioffice.xml, to a subdirectory under <OracleBI_Home>/Install_Backup/<upgrade_timestamp>.
Note:During an upgrade installation this file is used to retain the existing configuration settings.
The Oracle BI Enterprise Edition installer for 10.1.3.3 installs the Oracle BI Office server into the J2EE container with the Presentation Services Plug-in.
The basic BIEE install option deploys the bioffice.ear to the standalone OC4J home.
The advanced BIEE install option creates a new OC4J instance called "bioffice" in Oracle Application Server and deploys the bioffice.ear to this new OC4J instance.
The BI Office configuration file is bioffice.xml. The location of the file depends on your installation type. For example:
In a BIEE basic install, it is installed under <OracleBI_Home>/oc4j_bi/j2ee/home/applications/bioffice/bioffice/WEB-INF/bioffice.xml
In a BIEE advanced install, it is installed under ORACLE_HOME/j2ee/bibioffice/applications/bioffice/bioffice/WEB-INF/bioffice.xml
In a BIEE install with IBM WebSphere 5.1 (Windows), it is installed under C:\Program Files\WebSphere\AppServer\installedApps\<server name>\bioffice.ear\bioffice.war
In a BI EE install with Apache Tomcat 5.5 (Windows), it is installed under C:\Program Files\<Apache_Home>\Tomcat 5.5\webapps\bioffice
In other non-Oracle Application Servers, it is installed under the bioffice application directory, parallel to the Oracle BI analytics application.
The Oracle BI EE installer configures the BI Office server to point to the Oracle BI Presentation Services server by setting the property "SawBaseURL" in the bioffice.xml file. To configure the Oracle BI Office server to point to a different Presentation Services server, modify this property with the new Presentation Services URL. For more details about other properties in the bioffice.xml file, see Section 24.5, "Setting Properties in the bioffice.xml Configuration File".
The installer copies the client/OracleBIOffice.exe file to the location where Presentation Services is running and updates the instanceconfig.xml file with this location. This makes the client install executable available for download from the More Products menu in Oracle BI Interactive Dashboards and Oracle BI Answers.
For example, in an OC4J install, the OracleBIOffice.exe is installed to <OracleBI_Home>/oc4j_bi/j2ee/home/applications/analytics/analytics/client/OracleBIOffice.exe.
In an Oracle Application Server install, the OracleBIOffice.exe is installed to ORACLE_HOME/j2ee/bianalytics/applications/analytics/analytics/client/OracleBIOffice.exe.
The installer modifies the instanceconfig.xml (OracleBIData/web/config/instanceconfig.xml) by adding the new element
<BIforOfficeURL> and initializing the property to point to the location of the client download. For example:
If you deployed Oracle BI Enterprise Edition on a non-Oracle J2EE Application Server, you must manually deploy the bioffice.ear or bioffice.war file. It is not included in the analytics.ear file. The bioffice application must be deployed to the same container as the analytics application.
Deploy the BI Office server:
From a browser, open the Web application deployment page for your J2EE application server.
Locate the bioffice.ear or bioffice.war under OracleBI/office/server.
Using the guidelines for your application server, deploy the bioffice.ear or bioffice.war file as an application named "bioffice" to your J2EE application server environment.
Make the BI Office Client Executable Available to Presentation Services (Required for 10.1.3.3 only):
Note:Do not perform this step if you are installing Oracle BI EE version 10.1.3.3.1. In the 10.1.3.3.1 release, the installer automatically places the BI Office Client install executable in the deployed analytics/analytics/client directory.
Copy the <OracleBI_Home>/office/client directory to the deployed analytics/analytics/client directory.
Update Configuration Files:
Update the instanceconfig.xml to point to the location of the OracleBIOffice.exe file.
Locate the instanceconfig.xml file in the directory <OracleBI_Home>/web/config.
Add the following entry to the file:
Note:For more information on modifying instanceconfig.xml, refer to the Oracle Business Intelligence Presentation Services Administration Guide.
Update the bioffice.xml file to point to the Presentation Services server.
Update the property
<SawBaseURL> with the hostname and port of the Presentation Services server. See Section 24.5, "Setting Properties in the bioffice.xml Configuration File" for the file location and more information.
After installing Oracle BI EE 10.1.3.3, the BI Office client is available for download from Oracle BI Answers and Oracle BI Interactive Dashboards.
To install the BI Office Client:
Log in to Oracle BI Answers or Oracle BI Interactive Dashboards with your Oracle BI EE user credentials.
Select the More Products link and then select Download Oracle BI for Microsoft Office.
A dialog will prompt you to run or save OracleBIOffice.exe. Save the file to your local directory.
Navigate to the saved location. Close all Microsoft Office applications. Double-click OracleBIOffice.exe to launch the installer.
Microsoft .Net Framework 2.0 is required for the BI Office client. If it is not installed on your computer you will be prompted to exit and install Microsoft .Net Framework 2.0 from the Microsoft Web site.
Note:In the Oracle BI EE version 10.1.3.3, Microsoft .Net Framework 2.0 was included with the BI Office client installer. If you are installing this version, you will be prompted to install Microsoft .Net Framework 2.0 without being required to exit.
In addition, if the following prerequisites are not on your computer, you will be prompted to install them:
Shared Add-In Extensibility Microsoft .Net 2.0
Shared Add-In Support Update Microsoft .Net 2.0
After installing the prerequisites, you may be prompted to restart your computer.
After restarting, navigate back to the location of the OracleBIOffice.exe file and double-click to reinitiate the install.
From the Welcome screen, select Next.
In the Customer Information screen, enter your User Name and Company Name, and select the desired sharing option.
On the Setup Type screen choose one of the following:
Typical — Performs installation of both add-ins to C:\Program Files\Oracle\BIOffice
Custom — Enables you to select the install directory, and choose which components to install.
After all components are installed, the InstallShield Wizard Complete dialog box will display. Click Finish.
If you have an existing installation on your machine, you will be prompted to Modify, Repair or Remove the existing installation.
Modify: Prompts you to select the specific Add-in you wish to install.
Repair: Uses a cached copy of the original install.
Remove: Removes the BI Office Add-Ins.
Once downloaded and installed, each user must enter the connection information to the Oracle BI for Microsoft Office Add-in Preferences dialog on the client computer to enable connection to Presentation Services.
To add connection information to the client computer:
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.
Enter the following fields:
Name for the connection
Office Server — The server URL for the Oracle BI for Microsoft Office server (for example: bioffice-server.mycompany.com)
Port for the Oracle for BI for Microsoft Office server
Note:If your environment is SSL enabled, see Section 24.6, "Setting Up SSL-Enabled OC4J for BI Office" for the appropriate set up steps.
Click Test Connection to test the connection between the add-in and the BI Office server.
This section describes the properties that you can configure in the bioffice.xml file. For file location see Section 24.2.2, "Initialization of the BI Office Configuration 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>
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. Default is: <O4CJ dir>\j2ee\home\applications\bioffice\bioffice\WEB-INF\log|
|LogLevel||1 = Never
2 = Error
3 = Warning (Default)
4 = Message
5 = Debug
|Set the level of information you wish 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 the Oracle BI Presentation Services. This property is set initially by the BIEE installer.|
|SawUseSSO||0 = No (Default)
1 = Yes
|Default is 0. Set this property to 1 if your BIEE implementation is SSO-enabled. In an SSO-enabled implementation, the BI Office add-in features "Insert as Flash," "Insert as Image," and "Edit View" are not supported. Setting this option to "1" suppresses these options from the office add-in menu.|
|SawMaxRowsPerPage||N/A||Default setting is 5000. Specifies the maximum number of rows to be returned by SAW executeXMLQuery or fetchNext method. Increase this setting in a multiple-user environment.|
|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, set this property to 0 for better performance.|
|SawParsePercentageColumn||0 = Off
1 = On (Default)
|In Oracle BI Answers, if the Column Properties dialog box for a given column is set up 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 then formats the data as a percentage in Excel, the data will display correctly. If the property is set to 0, no percentage processing is done by the BI Office server.
For more information on setting Column Properties in Oracle BI Answers, see the Oracle Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide.
|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 cache.|
Oracle Containers for J2EE supports Secure Sockets Layer (SSL) communication. For information on setting up SSL in your OC4J environment, see Oracle Containers for J2EE Security Guide 10g (10.1.3.1.0).
Assuming your Oracle BI EE is installed under C:\OracleBI, the following instructions describe how to set up SSL in a standalone OC4J environment on Windows.
Note:In the BI EE install, you will find the keytool utility in this location: C:\OracleBI\uninstall\_jvm\bin\ keytool.exe.
Create a keystore with an RSA private/public key pair using the keytool utility.
This example generates a keystore to reside in a file named mykeystore.jks, which has a password of 123456, using the RSA key pair generation algorithm.
cd C:\OracleBI\oc4j_bi\j2ee\home\config keytool -genkey -keyalg RSA -keystore mykeystore.jks -storepass 123456
The keytool prompts you for additional information, as follows:
What is your first and last name?
[Unknown]: Test User
What is the name of your organizational unit?
What is the name of your organization?
What is the name of your City or Locality?
[Unknown]: Redwood Shores
What is the name of your State or Province?
What is the two-letter country code for this unit?
Is <CN=Test User, OU=Support, O=Oracle, L=Redwood Shores, ST=CA, C=US> correct?
Enter key password for <mykey>
(RETURN if same as keystore password):
Always press RETURN for the key password. In OC4J 10.1.3.x implementations, the keystore password must be the same as the key entry password.
The mykeystore file is created in the current directory. The default alias of the key is mykey.
If you do not have a secure-web-site.xml file, create one in the following location: C:\OracleBI\j2ee\home\config\secure-web-site.xml. You can start by copying content from default-web-site.xml.
Update secure-web-site.xml with the following elements:
<web-site> element to add secure="true" and to set the port value to some available port. (For example, port="4443". To use the default of 4443, you have to be a super user.) For standalone OC4J, use HTTP protocol, which is the default setting. (The setting protocol="http" in combination with secure="true" results in HTTPS being used.)
<web-site port="4443" secure="true" protocol="http" display-name="Default OracleAS Containers for J2EE Web Site" > ... </web-site>
Add the following under the <web-site> element to define the keystore and password.
<ssl-config keystore="./mykeystore.jks" keystore-password="123456" />
Note that the relative path and filename extensions are used for example: ./mykeystore.jks.
Make sure that the server.xml points to the secure-web-site.xml file.
As necessary, uncomment or add the following line in server.xml:
<web-site path="./secure-web-site.xml" />
Restart OC4J to initialize the secure-web-site.xml file additions.
Test the SSL port by accessing the site in a browser on the SSL port. For example: https://bieeserver.mycompany.com:4443/em
Import the certificate into the OracleBI JDK and export the self-signed certificate.
Use the keytool utility to export a certificate from your keystore to mykeystore.cer.
keytool -export -keystore C:\OracleBI\oc4j_bi\j2ee\home\config\mykeystore.jks -storepass 123456 -alias mykey -rfc -file mykeystore.cer
Use the keytool utility to import the mykeystore.cer to replace the OracleBI JDK system-wide keystore cacerts.
keytool -import -keystore cacerts -storepass changeit -alias mykeycert -file mykeystore.cer
Update SawBaseURL in the BI Office configuration file bioffice.xml as follows:
<property name="SawBaseURL">https:// /bioffice-server.mycompany.com:4443/analytics/saw.dll</property>
Now you can access this SSL-enabled OC4J and Oracle BI EE from your Oracle BI Add-In for Microsoft Office. In the Add-In, create a connection as follows:
Office Server: bioffice-server.mycompany.com
Use Secure Sockets Layer (SSL): selected
Tip:If you can connect to SSL successfully in the browser, yet the connection fails in the client, confirm that the certificate was exported and imported properly (see Step 6).
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:
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:
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.
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.
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>
<IP address of BI Office Server> is the IP address of the BI Office server, for example: 10.155.324.7
Locate the Office Server configuration file (bioffice.xml). The configuration file bioffice.xml can be found in the directory:
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.
<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>
For more information on the bioffice.xml file, see Section 24.5, "Setting Properties in the bioffice.xml Configuration File".