Skip Headers
Oracle® Business Intelligence New Features Guide
Release 10.1.3.4.2

Part Number E10416-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

31 Installing Oracle BI for Microsoft Office in Release 10.1.3.3

Oracle Business Intelligence for Microsoft Office includes the Oracle BI Office server and the Oracle Business Intelligence Add-in for Microsoft Office client. The client includes both the Oracle BI Add-In for Microsoft Excel and the Oracle BI Add-in for Microsoft PowerPoint. This chapter describes how to install and configure the add-in and the BI Office Server.

This chapter includes the following sections:

31.1 System Requirements

Following are the required components for Oracle BI for Microsoft Office. For the detailed list of system requirements for Oracle BI EE and for updates to this list, see System Requirements and Supported Platforms for Oracle Business Intelligence Suite Enterprise Edition, Version 10.1.3.4.

31.1.1 Supported Client Operating Systems

  • Windows XP

  • Windows Vista

31.1.2 Supported Versions of Microsoft Office

  • Microsoft Office 2003

  • Microsoft Office 2007

31.1.3 Supported Application Servers

  • Oracle Application Server (OC4J/HTTP) 10.1.3.1 on Linux

  • Oracle Application Server 10.1.3.3

  • Apache Tomcat 5.5x on Linux

  • BEA Weblogic 9.x

  • IBM Websphere Application Server 5.1 on Windows

  • IBM Websphere Application Server 6.0, 6.1

  • Sun Java System Web Server 6.1, 7.0

31.2 Installing the BI Office Server

The Oracle BI EE Release 10.1.3.3 (and later) installer installs the BI Office Server. The installer performs the following tasks:

31.2.1 Deployment of the bioffice.ear

The Oracle BI EE 10.1.3.3 (and later) installer installs the Oracle BI Office Server into the J2EE container with the Presentation Services Plug-in.

  • The basic Oracle BI EE installation option deploys the bioffice.ear to the standalone OC4J home.

  • The advanced Oracle BI EE installation option creates a new OC4J instance called "bioffice" in Oracle Application Server and deploys the bioffice.ear to this new OC4J instance.

31.2.2 Initialization of the Server Configuration File

The BI Office Server configuration file is bioffice.xml. The location of the file depends on the installation type. For example:

  • In an Oracle BI EE basic installation, it is installed under <OracleBI_Home>/oc4j_bi/j2ee/home/applications/bioffice/bioffice/WEB-INF/bioffice.xml

  • In an Oracle BI EE advanced installation, it is installed under ORACLE_HOME/j2ee/bibioffice/applications/bioffice/bioffice/WEB-INF/bioffice.xml

  • In an Oracle BI EE installation with IBM WebSphere 5.1 (Windows), it is installed under C:\Program Files\WebSphere\AppServer\installedApps\<server name>\bioffice.ear\bioffice.war

  • In an Oracle BI EE installation 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 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 31.5, "Setting Properties in the bioffice.xml Configuration File."

31.2.3 Location of the Installed Client OracleBIOffice.exe 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:

<BIforOfficeURL>client/OracleBIOffice.exe</BIforOfficeURL>

31.3 Deploying the BI Office Server on J2EE Application Servers

If you deployed Oracle BI EE on a non-Oracle J2EE Application Server, then you must manually deploy the bioffice.ear or bioffice.war file to use the BI Office Server. It is not included in the analytics.ear file. The bioffice application must be deployed to the same container as the analytics application.

To deploy the BI Office Server:

  1. From a browser, open the Web application deployment page for the J2EE application server.

  2. Locate the bioffice.ear or bioffice.war file under OracleBI/office/server.

  3. 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.

To make the client executable files available to Presentation Services (required for Release 10.1.3.3 only):

Note:

Do not perform this step if you are installing Oracle BI EE Release 10.1.3.3.1 or later. In the 10.1.3.3.1 and later releases, the installer automatically places the client executable file in the deployed analytics/analytics/client directory.

Copy the <OracleBI_Home>/office/client directory to the deployed analytics/analytics/client directory.

To update configuration files:

  1. Update the instanceconfig.xml to point to the location of the OracleBIOffice.exe file.

    1. Locate the instanceconfig.xml file in the directory <OracleBI_Home>/web/config.

    2. Add the following entry to the file:

      <BIforOfficeURL>client/OracleBIOffice.exe</BIforOfficeURL>
      

    Note:

    For more information on modifying instanceconfig.xml, refer to Oracle Business Intelligence Presentation Services Administration Guide.
  2. Update the bioffice.xml file to point to the Presentation Services server.

    Update the property <SawBaseURL> with the host name and port of the Presentation Services server. See Section 31.5, "Setting Properties in the bioffice.xml Configuration File" for the file location and more information.

31.4 Installing the Oracle BI Add-In for Microsoft Office

After installing Oracle BI EE, you can download the Oracle BI Add-In for Microsoft Office from Oracle BI Answers and Interactive Dashboards.

To install the add-in:

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

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

    This image is described in the surrounding text.
  3. A dialog prompts you to run or save OracleBIOffice.exe. Save the file to a local directory.

  4. 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 add-in. If it is not installed on your computer, then you are prompted to exit and install Microsoft .Net Framework 2.0 from the Microsoft Web site.

    Note:

    In Oracle BI EE Release 10.1.3.3, Microsoft .Net Framework 2.0 was included with the add-in's installer. If you are installing this version, then you are prompted to install Microsoft .Net Framework 2.0 without being required to exit.

    In addition, if the following prerequisites do not exist on your computer, then you are prompted to install them:

    • Office2003-kb907417sfxcab-ENU_Patch

    • Shared Add-In Extensibility Microsoft .Net 2.0

    • Shared Add-In Support Update Microsoft .Net 2.0

    After installing the prerequisites, you might be prompted to restart the computer.

    After restarting, navigate back to the location of the OracleBIOffice.exe file and double-click to reinitiate the install.

  5. From the Welcome screen, select Next.

  6. In the Customer Information screen, enter your User Name and Company Name, and select the desired sharing option.

  7. 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.

  8. After all components are installed, the InstallShield Wizard Complete dialog is displayed. Click Finish.

31.4.1 Performing a Maintenance or Upgrade Install

If you have an existing installation on your computer, then you are prompted to Modify, Repair or Remove the existing installation.

This image is described in the surrounding text.

Modify: Prompts you to select the specific add-in to install.

Repair: Uses a cached copy of the original install.

Remove: Removes the add-ins.

31.4.2 Configuring the Client

When the software is downloaded and installed, each user must enter the connection information in the Oracle BI Add-in for Microsoft Office Preferences dialog on the client computer to enable connection to Presentation Services.

Note:

The Preferences dialog was updated in Releases 10.1.3.3.1 and 10.1.3.4. If you are on Release 10.1.3.3, then the Application Name field is not available. In Release 10.1.3.4, the following field names in the dialog have been updated:
  • Name is updated to Server Name

  • Office Server is updated to BI Office Server

  • Application is updated to Application Name

To add connection information to the client computer:

  1. Open Microsoft Excel or Microsoft PowerPoint. (The connection information is shared, therefore you can enter it through either application and it is available to both.)

  2. From the Oracle BI menu, select Preferences.

  3. On the Connections tab, select New.

  4. Enter the following fields:

    1. Server Name for the connection

    2. BI Office Server — The URL for the Oracle BI Office Server (for example: bioffice-server.mycompany.com)

    3. Application Name - (this field was added in release 10.1.3.3.1) enter the Application Name that you defined for the BI Office server when you deployed the BI Office Server application to its J2EE container. The name defaults to "bioffice", but if you specified another name when you deployed the application, then enter that name in this field.

      Note:

      The value entered in the Application Name field is appended to the values for BI Office Server URL and Port to construct the URL that the client uses to connect to the BI Office server application. For example: http://bioffice-server.mycompany.com:80/bioffice
    4. Port for the BI Office Server. The default is 80.

    This image is described in the surrounding text.

    Note:

    If your environment is SSL enabled, then see Section 31.6, "Setting Up SSL-Enabled OC4J for Oracle BI for Microsoft Office" for the appropriate setup 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.

  5. Click Test Connection to test the connection between the add-in and the BI Office Server. (Note that this does not test the connection between the Oracle BI Office Server and Presentation Services.)

31.5 Setting Properties in the bioffice.xml Configuration File

This section describes the properties that you can configure in the bioffice.xml file. For file location see Section 31.2.2, "Initialization of the Server Configuration File".

Following is a sample bioffice.xml file. The sample file is from Release 10.1.3.4. If you are using a release before 10.1.3.4, then the file does not contain the following properties:

<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>
<!-- In SSO/NTLM case, sawBaseURL is for web service only,
 sawWebURLforSSO is for external web request. In none SSO/NTLM
 case, sawBaseURL is for both web service and external web request. 
 --> - <!-- <property name="SawWebURLforSSO">http://localhost/analytics/saw.dll</property> 
 --> - <!-- <property name="SawSSOImpersonator"></property>  --> 
- <!-- <property name="SawSSOImpersonatorPassword"></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 makes SAW re-submits the query to refresh data, in stead 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. 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 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) unless you are troubleshooting an issue.

SawBaseURL http://<HOSTNAME>:<PORT>/analytics/saw.dll

or

https://<HOSTNAME>:<PORT>/analytics/saw.dll

Enter the URL for Presentation Services. This property is set initially by the BI EE installer.

Important If SSO or NTLM is enabled, then enter the URL for the protected analytics servlet that you deployed when configuring Oracle BI for Microsoft Office to integrate with the SSO-enabled BI Server. The URL that is specified for this property is used for Web service requests between Oracle BI for Microsoft Office and Presentation Services. For information, see the appropriate topic for your system: Section 31.7, "Setting Up an SSO-Enabled Presentation Server for Releases Before 10.1.3.4," Section 31.8, "Setting Up an SSO-Enabled Presentation Server for Releases 10.1.3.4 and Later," or Section 31.9, "Setting Up an NTLM-Enabled Presentation Server for Releases 10.1.3.4 and Later."

If SSO or NTLM is not enabled, then SawBaseURL is the only URL that is required to access BI Presentation Services.

SawUseSSO 0 = No (Default)

1 = Yes

For releases before 10.1.3.4: Default is 0. Set this property to 1 if your BI Server implementation is SSO-enabled. In an SSO-enabled implementation, the 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 add-in menu.

For versions 10.1.3.4 and later: Set this property to 1 if the Oracle BI EE implementation is enabled for SSO or NTLM.

SawWebURLforSSO http://<HOSTNAME>:<PORT>/analytics/saw.dll

or

https://<HOSTNAME>:<PORT>/analytics/saw.dll

Added in Release 10.1.3.4

When SSO or NTLM is enabled, use this property to enter the public URL that allows external users to access the Oracle BI Answers page.

SawSSOImpersonator N/A Added in Release 10.1.3.4

When SSO or NTLM is enabled for the BI Server, use this property to enter the Impersonator user that you defined for the BI Server.

SawSSOImpersonatorPassword N/A Added in Release 10.1.3.4

When SSO or NTLM is enabled for the BI Server, use this property to enter the Impersonator user password that you defined for the BI Server.

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 the data does not include any hyperlink text, then set this property to 0 for better performance.
SawParsePercentageColumn 0 = Off

1 = On (Default)

In Answers, if the Column Properties dialog 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 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 in Answers, see 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.
OfficeCompressResult 0 = No

1 = Yes (Default)

Added in Release 10.1.3.4

This property is turned on by default to compress the Answers result set on the BI Office Server before being sent to the add-in. Define the minimum file size that triggers the compression in the property "OfficeCompressResultMinSize".

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 may be required before achieving the optimal setting.

OfficeCompressResultMinSize Express as an integer in bytes. Default value is 16384. Added in Release 10.1.3.4

If the property "OfficeCompressResult" is turned on, then enter the minimum size file in kilobytes that triggers compression. See the description for "OfficeCompressResult" for guidelines on setting these properties.


31.6 Setting Up SSL-Enabled OC4J for Oracle BI for Microsoft Office

Oracle Containers for J2EE supports Secure Sockets Layer (SSL) communication. For information on setting up SSL in an OC4J environment, see Oracle Containers for J2EE Security Guide 10g (10.1.3.1.0).

Assuming the Oracle BI Server is installed under C:\OracleBI, the following instructions describe how to set up SSL in a standalone OC4J environment on Windows.

Note:

In the Oracle BI EE installation, you find the keytool utility in this location: C:\OracleBI\uninstall\_jvm\bin\ keytool.exe.
  1. Create a keystore with an RSA private/public key pair using the keytool utility.

    Example:

    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?

       [Unknown]: Support

    What is the name of your organization?

       [Unknown]: Oracle

    What is the name of your City or Locality?

       [Unknown]: Redwood Shores

    What is the name of your State or Province?

       [Unknown]: CA

    What is the two-letter country code for this unit?

        [Unknown]: US

    Is <CN=Test User, OU=Support, O=Oracle, L=Redwood Shores, ST=CA, C=US> correct?

       [no]: yes

    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.

  2. If you do not have a secure-web-site.xml file, then 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.

  3. Update secure-web-site.xml with the following elements:

    1. Update the <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 must 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>
      
    2. 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 file name extensions are used, for example: ./mykeystore.jks.

    3. Ensure 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" />
      
  4. Restart OC4J to initialize the secure-web-site.xml file additions.

  5. Test the SSL port by accessing the site in a browser on the SSL port. For example: https://bieeserver.mycompany.com:4443/em

  6. Import the certificate into the Oracle BI JDK and export the self-signed certificate.

    1. cd JAVA_HOME\jre\lib\security

    2. 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
      
    3. Use the keytool utility to import the mykeystore.cer to replace the Oracle BI JDK systemwide keystore cacerts.

      keytool -import -keystore cacerts -storepass changeit -alias mykeycert -file mykeystore.cer
      
  7. Update SawBaseURL in the BI Office Server configuration file bioffice.xml as follows:

    <property name="SawBaseURL">https:// /bioffice-server.mycompany.com:4443/analytics/saw.dll</property>
    
  8. Now you can access this SSL-enabled OC4J and Oracle BI EE from the Oracle BI Add-In for Microsoft Office. In the Add-In, create a connection as follows:

    Name: BI Office

    Office Server: bioffice-server.mycompany.com

    Port: 4443

    Use Secure Sockets Layer (SSL): selected

    This image is described in the surrounding text.

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 Step 6).

31.7 Setting Up an SSO-Enabled Presentation Server for Releases Before 10.1.3.4

Oracle BI for Microsoft Office currently cannot be fully integrated with SSO. If the Oracle BI EE implementation is SSO-enabled, then users can use their SSO credentials from the Microsoft Excel or PowerPoint add-in to log in. However, the following options from the add-in's Insert menu are not supported:

To enable the integration of Oracle BI for Microsoft Office with SSO-enabled Oracle BI EE, you must deploy a separate Presentation Services Plug-in (analytics.ear) and grant trusted IP address access privileges to the new Presentation Services Plug-in from the IP address of the BI Office Server. This process enables Web service requests from the IP address of the trusted BI Office Server to be handled by the Presentation Services Plug-in without going through SSO authentication. You then must configure the BI Office Server to send Web service requests to this Presentation Services Plug-in that bypasses SSO.

Depending on the SSO server type, the configuration might differ. Following is an example of how to integrate Oracle BI for Microsoft Office with Oracle SSO-enabled Oracle BI EE:

  1. On the same computer where the 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. For more information on updating the web.xml file, see the topic "Configuring BI Presentation Services Plug-in" in Oracle Business Intelligence Enterprise Edition Deployment Guide.

  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.224.7

  4. Locate the BI Office Server configuration file (bioffice.xml) in the following 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 Oracle BI for Microsoft 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>
    

For more information on the configuration file, see Section 31.5, "Setting Properties in the bioffice.xml Configuration File".

31.8 Setting Up an SSO-Enabled Presentation Server for Releases 10.1.3.4 and Later

Oracle BI for Microsoft Office currently cannot be fully integrated with SSO. If the Oracle BI EE implementation is SSO-enabled, users can use their SSO credentials from the Microsoft Excel or PowerPoint add-in to log in.

To enable the integration of Oracle BI for Microsoft Office with SSO-enabled Oracle BI EE, you must deploy a separate Presentation Services Plug-in (analytics.ear) and grant trusted IP address access privileges to the new Presentation Services Plug-in from the IP address of the BI Office Server. This process enables Web service requests from the IP address of the trusted BI Office Server to be handled by the Presentation Services Plug-in without going through SSO authentication. You then must configure the BI Office Server to send Web service requests to this Presentation Services Plug-in that bypasses SSO.

Note:

There is an alternative method that you can use that does not require the deployment of a second Presentation Services Plug-in. See the Section 31.8.3, "Alternative Configuration Method."

To perform the Edit View function in the add-in, you must configure the BI Office Server to route these requests to the public URL for Presentation Services. The user is challenged for credentials to sign in to Oracle BI EE to launch the Answers page for editing.

31.8.1 Prerequisites

Following are the prerequisites for configuring Oracle BI for Microsoft Office with an SSO-enabled Oracle BI Server:

  1. Install Oracle BI EE, Release 10.1.3.4. For information, see Oracle Business Intelligence Infrastructure Installation and Configuration Guide.

  2. Configure the Oracle BI Server to enable Single Sign-on. See the chapter: "Enabling Oracle Single Sign-on For Oracle Business Intelligence" in Oracle Business Intelligence Enterprise Edition Deployment Guide for information.

    When enabling SSO for the Oracle BI Server, you are required to set up an Impersonator user. You need this Impersonator user name and password to complete the configuration of the BI Office Server. For more information, see the topic "Creating the Oracle BI Server Impersonator User" in Oracle Business Intelligence Enterprise Edition Deployment Guide.

31.8.2 Integrating Oracle BI for Microsoft Office with SSO-Enabled Oracle BI EE

Depending on the SSO server type, the configuration might differ. Following is an example of how to integrate Oracle BI for Microsoft Office with Oracle SSO-enabled Oracle BI EE:

  1. On the same computer where the 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. For more information on updating the web.xml file, see the topic "Configuring BI Presentation Services Plug-in" in Oracle Business Intelligence Enterprise Edition Deployment Guide.

  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.224.7

  4. Locate the BI Office Server configuration file in the following 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

    • "SawWebURLforSSO" to point to the URL used for public and external users to access the Oracle BI Answers page.

      Note:

      When specifying the URL for SawWebURLforSSO, you can use the domain names in the URL. However, note the following special cases:
      • If the Oracle BI Server, the BI Office Server, and the client computer run in different domains, then you must specify the complete domain name. For example, you would specify:

        http://myserver.mycompany.com:443/analytics/saw.dll

        not

        http://myserver:443/analytics/saw.dll

      • If the BI Server or the BI Office Server 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.

    • "SawSSOImpersonator" with the Impersonator user that you set up for the Oracle BI Server.

    • "SawSSOImpersonatorPassword" with the password for the Impersonator user.

    • "SawUseSSO" to indicate that you have enabled SSO for Oracle BI for Microsoft Office and Presentation Services. Set the value to "1".

    Example:

    <property name="SawBaseURL">http://localhost:port/analyticsSOAP/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="SawSSOImpersonator">Impersonator</property>    
    <property name="SawSSOImpersonatorPassword">password<property> 
    <property name="SawUseSSO" type="int">1</property>
    

For more information on the bioffice.xml file, see Section 31.5, "Setting Properties in the bioffice.xml Configuration File".

31.8.3 Alternative Configuration Method

As an alternative to deploying a second Presentation Services Plug-in, you can perform the following configuration steps:

  1. Modify the web.xml file (located at OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\WEB-INF\web.xml) by entering a duplicate of the servlet SAWBridge and the servlet mappings. Give a different servlet name and URL pattern to your duplicate entry.

  2. Restart the Web server. Now you have two different servlets with two different URL patterns.

  3. Follow the instructions in the Section 31.8.2, starting with Step 3 to unprotect the second servlet and configure the bioffice.xml file.

Following is a sample web.xml file showing the duplicate entry:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app 
PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN" 
 http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">
 
<web-app>
   <servlet>
      <servlet-name>SAWBridge</servlet-name>
      <servlet-class>com.siebel.analytics.web.SAWBridge</servlet-class>
      <init-param>
          <param-name>oracle.bi.presentation.sawserver.Host</param-name>
          <param-value>localhost</param-value>
      </init-param>
      <init-param>
         <param-name>oracle.bi.presentation.sawserver.Port</param-name>
         <param-value>9710</param-value>
      </init-param>    
   </servlet>
   <servlet-mapping>
      <servlet-name>SAWBridge</servlet-name>
      <url-pattern>/saw.dll</url-pattern>
   </servlet-mapping>
 
   <servlet>
      <servlet-name>SAWBridge2</servlet-name>
      <servlet-class>com.siebel.analytics.web.SAWBridge</servlet-class>
                <init-param>
         <param-name>oracle.bi.presentation.sawserver.Host</param-name>
         <param-value>localhost</param-value>
      </init-param>
      <init-param>
                                <param-name>oracle.bi.presentation.sawserver.Port</param-name>
         <param-value>9710</param-value>
      </init-param>    
   </servlet>
   <servlet-mapping>
      <servlet-name>SAWBridge2</servlet-name>
      <url-pattern>/soap</url-pattern>
   </servlet-mapping>
 
   <mime-mapping>
      <extension>xsd</extension>
      <mime-type>text/xml</mime-type>
   </mime-mapping>
   <welcome-file-list>
      <welcome-file>default.jsp</welcome-file> 
   </welcome-file-list>
</web-app>

31.9 Setting Up an NTLM-Enabled Presentation Server for Releases 10.1.3.4 and Later

Oracle BI for Microsoft Office connections running on supported Windows client operating systems that use Active Directory as the LDAP server for Windows authentication can now log in to Oracle BI EE without requiring the user to enter a login user name and password, if Oracle BI EE is configured to authenticate against the same Active Directory server.NT LAN Manager (NTLM) Authentication Protocol is the name of a family of security protocols in Windows. Starting with Release 10.1.3.4, Oracle BI for Microsoft Office supports NTLM protocol to achieve the following features:

For details on NTLM, refer to the Microsoft Web sites at www.microsoft.com and msdn.microsoft.com.

31.9.1 Example of Deploying and Configuring with NTLM Authentication

This section provides an example of deploying and configuring Oracle BI for Microsoft Office with NTLM authentication. This example uses the following components:

  • Windows Operating System

  • Apache HTTP Server

  • OC4J Web server

Deployment and configuration steps might differ if you choose different components for your system.

The following diagram displays a sample deployment topology:

Description of ntlm_topology.gif follows
Description of the illustration ntlm_topology.gif

Features of this topology:

  • Every external HTTP request goes through the HTTP server with NTLM enabled. These requests are authenticated by the NTLM module.

  • The BI Office Server and Presentation Services Plug-in modules, and Presentation Services and Oracle BI Server, are deployed behind the HTTP server. These components therefore cannot be accessed without passing HTTP server authentication.

  • In this example a second Presentation Services Plug-in is deployed on a dedicated instance of an OC4J Web server. Access to the Web server is restricted to protected IP addresses, including the IP address of the computer where the BI Office Server runs. This configuration enables the BI Office Server to send Web service requests directly to the Presentation Services Plug-in without going through Apache NTLM authentication.

  • Optionally, you can use the BI Administration Tool to configure the Oracle Business Intelligence user repository (rpd) to use Microsoft Active Directory or another third-party LDAP server to achieve a single user repository. If you choose not to configure a single repository, then the Oracle Business Intelligence user repository must duplicate the Microsoft system user repository to achieve silent sign-on. For example, Windows user AJones with password abc123 must be duplicated in the Oracle Business Intelligence user repository. For information on configuring the user repository, see Oracle Business Intelligence Enterprise Edition Deployment Guide.

31.9.2 Prerequisite Tasks

Following are the prerequisite tasks:

31.9.2.1 Configure Oracle BI EE to Enable Single Sign-On

For details see the chapter, "Enabling Oracle Single Sign-on for Oracle Business Intelligence" in Oracle Business Intelligence Enterprise Edition Deployment Guide.

31.9.2.2 Optionally Install and Configure Active Directory or Another Third-Party LDAP Server

To achieve single user repository management, you can use the BI Administration Tool to configure the Oracle Business Intelligence user repository (rpd) to use Microsoft Active Directory or another third-party LDAP server. If you choose not to configure a single repository, then the Oracle Business Intelligence user repository must duplicate the Microsoft system user repository to achieve silent sign-on. For information on configuring the user repository, see Oracle Business Intelligence Enterprise Edition Deployment Guide

31.9.2.3 Create a Second BI Presentation Services Servlet to Bypass Authentication

There are two methods for doing this:

  • Install a standalone instance of the Web server, deploy the Presentation Services Plug-in to this Web server, and configure as described below.

  • Create a duplicate SAWBridge servlet in the web.xml file as described below.

31.9.2.3.1 Method 1: Install a Standalone Instance of the Web Server and Deploy and Configure the Presentation Services Plug-in
  1. On the same computer where the BI Presentation Services Plug-in has been deployed, install an instance of OC4J or other supported Web server.

  2. Deploy the Presentation Services Plug-into this new Web server instance using the file analytics.ear:

    Locate analytics.ear in the directory OracleBI_HOME/web.

  3. After the plug-in has been deployed, make the same modifications to the web.xml file for this servlet that were made to the web.xml file for the default "analytics" servlet. For more information on updating the web.xml file, see the topic "Configuring BI Presentation Services Plug-in" in Oracle Business Intelligence Enterprise Edition Deployment Guide.

  4. Apply access restriction to this Web server so that it only accepts requests from the protected IP address where the BI Office server is installed.

31.9.2.3.2 Method 2: Create a Duplicate SAWBridge Servlet in the web.XML File

Rather than deploy a second Web server and Presentation Services Plug-in you can create a duplicate servlet directly within the web.xml file.

  1. Locate the web.xml file as follows:

    OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\WEB-INF\web.xml

  2. Duplicate the entries for the SAWBridge servlet and the servlet mappings. Following is a sample servlet and servlet mapping from the web.xml file:

    Note:

    For more information on updating the web.xml file, see the topic "Configuring BI Presentation Services Plug-in" in Oracle Business Business Intelligence Enterprise Edition Deployment Guide
    <servlet> 
     <servlet-name>SAWBridge</servlet-name> 
      <servlet-class>com.siebel.analytics.web.SAWBridge</servlet-class> 
      <init-param>
      <param-name>oracle.bi.presentation.sawserver.Host</param-name> 
      <param-value>localhost</param-value> 
      </init-param>
      <init-param>
      <param-name>oracle.bi.presentation.sawserver.Port</param-name> 
      <param-value>9710</param-value> 
      </init-param>
      </servlet>
      <servlet-mapping>
      <servlet-name>SAWBridge</servlet-name> 
      <url-pattern>/saw.dll</url-pattern> 
      </servlet-mapping>
    
  3. Give your duplicate servlet a new name and URL pattern. For example:

    <servlet-name>SAWBridge2</servlet-name>

    <url-pattern>/soap</url-pattern>

  4. Restart the Web server (for example: oc4j or IBM Websphere).

Now you have two Presentation Services plug-in servlets with two different URL patterns. You can protect one with SSO and unprotect the other.

31.9.3 Installing the NTLM Module

The NTLM authentication module is an open source application. There are several implementations available that can be found on the Web. This document describes how to install NTLM as an Apache module on a Windows operating system. For different implementations, see the documentation that accompanies the specific system components.

The NTLM module described in this document is downloadable from this location: http://sourceforge.net/projects/mod-auth-sspi

Perform the following to download and install the NTLM module:

  1. Download the NTLM module.

    Note:

    For UNIX operating systems, download mod_ntlm from: http://www.jamiekerwick.co.uk/?page_id=5. The installation guide is available from this link: http://modntlm.sourceforge.net/
  2. Install the NTLM module to the application server's Apache HTTP server, as described in the installation documentation. Following is a brief overview of the install steps:

    1. Copy the mod_auth_sspi.so module to the Apache modules directory.

    2. Update the Apache configuration file, httpd.conf, to load the module. Then add location directives to protect the bioffice and the analytics applications.

      Following is an example of the updates to the httpd.conf file:

      …
      # Load ntlm module
      LoadModule sspi_auth_module modules/mod_auth_sspi.so
      …
      #Add location protection like this:
       
      # Configuration for mod_auth_sspi
      <IfModule mod_auth_sspi.c>
          <Location /bioffice/>
              AuthName "A Protected Place"
              AuthType SSPI
              SSPIAuth On
              SSPIAuthoritative On
              SSPIOfferBasic On
              # SSPIBasicPreferred
              # SSPIUsernameCase lower
              require valid-user
          </Location>
       
          <Location /analytics/>
              AuthName "A Protected Place"
              AuthType SSPI
              SSPIAuth On
       SSPIAuthoritative On
       SSPIOfferBasic On
       # SSPIBasicPreferred
       # SSPIUsernameCase lower
       require valid-user
      </Location>
       
      </IfModule>
      # End of mod_auth_sspi
      

31.9.4 Configuring Oracle BI for Microsoft Office to Enable NTML

Perform the following steps to enable NTLM for Oracle BI for Microsoft Office.

  1. Locate the BI Office Server configuration file (bioffice.xml) in the following directory:

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

  2. Update the following properties:

    • "SawBaseURL" to point to the new Presentation Services Plug-in that you installed on the dedicated Web server. Web service requests from Oracle BI for Microsoft Office to the Oracle BI Server are routed through the Presentation Services Plug-in that is specified in this property. These requests bypass NTLM authentication.

    • "SawWebURLforSSO" to point to the URL used for public and external users to access the Oracle BI Answers page.

      Note:

      When specifying the URL for SawWebURLforSSO, you can use the domain names in the URL. However, note the following special cases:
      • If the Oracle BI Server, the BI Office Server, and the client computer run in different domains, then you must specify the complete domain name. For example, specify:

        https://myserver.mycompany.com:443/analytics/saw.dll

        not

        https://myserver:443/analytics/saw.dll

      • If the BI Server or the BI Office Server runs 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.

    • "SawSSOImpersonator" with the Impersonator user you set up for the Oracle BI Server.

    • "SawSSOImpersonatorPassword" with the password for the Impersonator user.

    • "SawUseSSO" to indicate that you have enabled SSO for Oracle BI for Microsoft Office and Presentation Services. Set the value to "1".

    Example:

    <property name="SawBaseURL">http://152.68.81.15:443/analytics/saw.dll</property>
    <!-- Does SAW use SSO (Single Sign-On): yes = 1; no = 0; -->
    <property name="SawWebURLforSSO>http://152.68.196.231:443/analytics/saw.dll</property>
    <property name="SawSSOImpersonator">Impersonator</property>    
    <property name="SawSSOImpersonatorPassword">password<property> 
    <property name="SawUseSSO" type="int">1</property>
    

    For more information on the bioffice.xml file, see Section 31.5, "Setting Properties in the bioffice.xml Configuration File".

31.9.5 Testing the NTLM Deployment

After you have completed the setup steps, to test the deployment, access Oracle BI for Microsoft Office from a computer within the authenticated Microsoft system domain. You are silently signed in without being challenged for credentials.

Select the Edit View link for a request. You are silently signed in to Presentation Services and the Answers request Edit page is displayed.