Oracle® Business Intelligence Spreadsheet Add-In

2.1 Hardware requirements

Your PC must meet the following minimum hardware requirements:

• IBM or compatible PC with a Pentium III 500 MHz processor.

• 256MB of RAM. Additional memory improves performance substantially.

• 100MB available on a hard drive for add-in files.

2.2 Software requirements

Your PC must meet the following software requirements:

• Microsoft Windows 2000 or Microsoft Windows XP.

• Microsoft Excel 2000, Microsoft Excel XP, or Microsoft Excel 2003.

2.3 Database access

Before you can use the add-in to display multidimensional data in Excel, the database administrator must have installed and configured the Oracle database for the appropriate version of the OLAP Option, as described in the following list:

• Oracle9i Release 2, as follows:

  • 9.2.0.6 for all platforms with the latest OLAP patch.

  • 9.2.0.5 with the latest OLAP patch for Windows-only.

• Oracle Database 10g with the latest OLAP patch.

For complete information on configuring the appropriate Oracle OLAP version, see the Oracle Business Intelligence Tools Installation Guide, which you can download from Oracle Technology Network.

3.1 Installation tips

When installing OracleBI Spreadsheet Add-In, bear these tips in mind:

• Close Excel -- Before installing the add-in, ensure that Excel is not running. Once you begin running the installation program, do not open Excel until the program completes.

• Error log file -- The installation directory contains a file named error.log, which is generated when certain types of errors occur. You might find this file helpful when diagnosing issues with Oracle Support Services.

3.2 Installing OracleBI Spreadsheet Add-In

Perform the following steps to install OracleBI Spreadsheet Add-In:

1. Download the executable installation file for OracleBI Spreadsheet Add-In from Oracle Technology Network to a temporary directory on your PC.

2. Ensure that Microsoft Excel is not running.

3. In Windows Explorer, double-click the executable file name.

4. When the installation program starts running, choose Install.

5. On the next screen, specify the directory in which you want to install the add-in and choose OK.

   The installation program begins to copy files to your PC and to register the add-in with Excel.

6. When the installation program is complete, you can specify whether to view the supplemental information for the add-in. Choose Finish.

3.3 Running OracleBI Spreadsheet Add-In

After installation, you can run the add-in with Excel on your PC. Simply start Excel and you will see the OracleBI menu in the menu bar.

To obtain the best results using OracleBI Spreadsheet Add-In, follow one simple guideline. If you add values to cells in a worksheet using OracleBI menus or dialogs, then use only OracleBI menus or dialogs to remove those values. For example, if you add an OLAP calculation to a worksheet, then use the appropriate option from the OracleBI menu to remove that calculation. Do not remove the column that contains the calculation by simply deleting the column using features of Excel.

Tip: The Spreadsheet Add-In ships with a sample workbook, which you can access from Oracle Technology Network. Download this workbook to see examples of the features of the Spreadsheet Add-In.

3.4 Uninstalling OracleBI Spreadsheet Add-In

If you no longer plan to use OracleBI Spreadsheet Add-In, then you can uninstall it. In the Windows Control Panel, use the Add or Remove Programs facility to uninstall the add-in. Alternatively, you can run the uninstall.exe program, which is located in the directory in which you installed the add-in. Use this uninstall program only if you installed the add-in using the standalone installation program, as described in this document.

Do not use Oracle Universal Installer to uninstall a standalone installation of OracleBI Spreadsheet Add-In.

4.1 Known Issues with Microsoft Excel

The issues that are described in this section are the result of the design of OracleBI Spreadsheet Add-In or of Microsoft Excel. It may not be possible to resolve these issues for OracleBI Spreadsheet Add-In.

• Error message in Excel 2003 -- When a query's page edge contains only numbers (such as 2000), then the following message is displayed in Excel 2003: "The number in this cell is formatted as text or preceded by an apostrophe." The error does not affect the use of the page control or the display of data in the query. You can prevent the message from being displayed as follows: from the Tools menu, choose Options, then Error Checking. Clear the "Number stored as text" box.

• Sharing workbooks on a LAN -- Due to limitations in the Share Workbooks feature of Excel, OracleBI Spreadsheet Add-In cannot update workbooks that are edited by multiple users. Therefore, do not use the Share Workbooks feature with workbooks that were created with OracleBI Spreadsheet Add-In.

• Canceling the closing of Excel -- If you close Excel when there are unsaved changes in a worksheet, then Excel closes OracleBI Spreadsheet Add-In before it asks if you want to save the changes. If you choose Cancel, then Excel remains open even though OracleBI Spreadsheet Add-In is already closed. To continue to use the add-in, you must shut down and restart Excel.

• Use care when stacking queries in a worksheet. For example, suppose that you have one query that begins in cell A1 and whose last row ends in cell A15. You then insert a second query that begins in cell A20. Both queries have data that extends into column D. If you add a calculation to the first query, then empty data cells that correspond to the added calculation will be added to the second query.

• Typing in pager cells -- When typing into the pager cell to change a page, (such as from 1999 to 2000), you might notice that this action does not change the query on the page. After you enter a new page value and end the editing session by pressing the Enter key, you will notice that the focus cell drops down to the cell below it. If the cell below is a cell within the same "query area" (such as another pager control or cell), then the page changes. However, if the cell is not part of the "query area" (such as an empty cell in Excel), then nothing happens.

  The following list describes ways to work around this problem:

  • To change pages, use the mouse and drop-down the pager control, without typing.

  • Use the arrow keys to "end the editing session" and move to a cell that is still part of the same query area, such as the pager above, if applicable.

  • Type the desired value into the pager cell and then, using the mouse, click on another cell within the same query area. This will accept the new value and OracleBI Spreadsheet Add-In will also detect that the pager value has changed and will change the page.

4.2 General Issues and Workarounds with OracleBI Spreadsheet Add-In

This section provides general issues and workarounds for OracleBI Spreadsheet Add-In:

• Specifying the Help language -- You can specify a language for the Help system that is different from the current locale. To do so, in the preferences file, which is named oraolapxl.prefs and is located in OracleBI Spreadsheet Add-In's installation directory, add the following line of text: help.locale=xx_XX where xx is the language code, and XX is the country code, if required. For example, to use French Canadian, enter help.locale=fr_CA.

  To see which language codes and country codes to specify, view the files in the help subdirectory of the installation directory for OracleBI Spreadsheet Add-In. The file names for the various translated Help systems include the appropriate language and country codes.

• Error log file -- The installation directory contains a file named error.log, which is generated when certain types of errors occur. You might find this file helpful when diagnosing issues with Oracle Support Services.

• Overlapping queries -- It is possible to create queries in a worksheet that overlap one another. If you try to create one query on top of another, then OracleBI Spreadsheet Add-In will usually detect it. However, in certain cases the data from multiple queries can overlap. Such overlapping can produce unpredictable results and should be avoided.

• Inserting a row below a paging control -- When you insert a row below a cell that acts as a paging control, the paging control will be duplicated, but it will not be functional. A paging control is effectively an Excel cell that is linked to a validation list. This is standard behavior for Excel and cannot be changed by the OracleBI Spreadsheet Add-In. To work around this problem, click the duplicated cell, choose Validation from the Data menu, and choose the Clear All button. If the cell is formatted, then also remove the formatting.

• Sharing saved selections and calculations across queries -- Saved selections and calculations that you create are specific to one query, and you cannot share them across queries. If you create saved selections and calculations in a workbook and you want to share those items with another user, then give that user a copy of the workbook.

• Using Cut and Paste with queries -- This release does not support moving queries between worksheets using cut and paste. You can move queries only within a single worksheet. Using cut and paste to move individual cells of a query may have unexpected results.

• Copying queries -- You cannot copy queries using Windows copy and paste functions. To copy a query, you must recreate it.

• Displaying Date measures -- If you attempt to display measures of type Date in OracleBI Spreadsheet Add-In, then the data in the cells that contain the Date data will be converted to text strings in Excel.

• Launching another instance of Excel -- If you start one instance of Excel, then start a second instance by clicking the Excel icon on your desktop or by another similar means, then you will not see the OracleBI menu in the second instance. To work around this issue, start only one instance of Excel and create a new workbook from within Excel by choosing New from the File menu, then clicking the Workbook icon.

• Autosizing -- When you exit the Query Editor or Calculation Editor, Autosizing is invoked. Very wide columns can result if you then drill on the row header, which uses long labels by default. To work around this problem, you can size the columns manually.

• Expand pages to worksheets -- In a query, if you hide rows containing zero or NA values, then you cannot expand pages to worksheets. If you want to use this feature, then you must first uncheck both the Hide NA rows and the Hide Zero and NA rows boxes in the Query Options dialog.

4.3 Accessibility Issues for OracleBI Spreadsheet Add-In

This section provides accessibility issues for OracleBI Spreadsheet Add-In:

• You cannot drill dimension values using the keyboard. Instead, you can use the Drill option from the OracleBI menu or from the right-mouse menu. See the "Accessibility Information" topic in the Help system for more information on accessibility, including keyboard navigation.

• If mnemonic keys are not visible in dialogs and wizards, then from the Windows Control Panel, choose Display, click the Effects tab, and verify that the "Hide keyboard navigation indicators until I use the Alt key" box is not checked.

4.4 Known Issues with Oracle9i OLAP Option

This section describes some of the known issues with the Oracle9i (Release 2) OLAP option. These issues affect all products that access OLAP data, including OracleBI Spreadsheet Add-In.

• You may encounter performance problems, particularly with large dimensions, if you suppress NA or zero values.

• If you have a complex query that involves a moving total calculation, then when you navigate the query results, you may see an ArrayIndexOutOfBounds exception.

• At times, database resources are not properly released. This may result in excessive memory consumption. To release the resources, close OracleBI Spreadsheet Add-In.