print Printer View  e-mail E-mail this page  Bookmark Bookmark
Oracle® Business Intelligence Spreadsheet Add-In Installation Guide and Release Notes
10 g Release 2 (10.1.2.2) for Microsoft Windows
Part No. B16035-03
 

 

Oracle® Business Intelligence Spreadsheet Add-In

Installation Guide and Release Notes

10 g Release 2 (10.1.2.2) for Microsoft Windows

Part No. B16035-03

November 20, 2006

This document provides installation instructions and release notes for Oracle Business Intelligence Spreadsheet Add-In (OracleBI Spreadsheet Add-In) when downloaded from Oracle Technology Network. The installation instructions explain how to install OracleBI Spreadsheet Add-In into an existing Microsoft Excel installation. The release notes describe known issues and workarounds for OracleBI Spreadsheet Add-In.

The release notes are updated periodically as new information becomes available. To ensure that you are reading the latest version of the release notes, check the Oracle Business Intelligence Web site:

http://www.oracle.com/technology/products/bi

The following list describes the sections of this document:

1 System Requirements

This section provides the following information about requirements for installing OracleBI Spreadsheet Add-In:

1.1 Hardware requirements

Your PC must meet the following minimum hardware requirements:

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

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

  • 100MB available on a hard drive for the extracted OracleBI Spreadsheet Add-In files; 50MB available for the file that you first download from Oracle Technology Network. You can delete the downloaded file after extracting its contents.

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

Note that Oracle BI Spreadsheet Add-In is not certified against Windows Vista.

1.3 Database access

Release 10.1.2.2 of OracleBI Spreadsheet Add-In supports the following versions for the database that contains the data on which you want to report:

  • Oracle9 i Release 2 Database (9.2.0.6 and higher)

  • Oracle Database 10 g Release 1 (10.1.0.4 and higher)

  • Oracle Database 10 g Release 2 (10.2.0.1 and higher)

You must use Oracle Database Enterprise Edition with the OLAP Option. In addition, if you want to use:

  • Calculated members that were created in OracleBI Discoverer Plus OLAP, then:

    • You must use Oracle Database 10 g Release 2, Patchset 10.2.0.2 with the 5396619 (OLAP C) patch and higher.

    • You must connect to an analytic workspace in Standard Form that was built with an Oracle tool such as Oracle 10 g Analytic Workspace Manager.

  • The write-back features of OracleBI Spreadsheet Add-In, then see Section 5, "Setting Up for Write-Back".

Tip: If you want to upgrade an analytic workspace from Oracle Database 10 g Release 1 to Release 2, then consult the appropriate section in the Oracle OLAP 10g Release 2 Application Developer's Guide.

The latest information on supported versions is on the Certify application on:

http://metalink.oracle.com

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.

2 Installation Instructions

This section provides the following information about installing OracleBI Spreadsheet Add-In:

2.1 Installation tips

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

  • Close Excel -- Before installing OracleBI Spreadsheet 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 error.log file is generated when certain types of errors occur during installation. You might find this file helpful when diagnosing issues with Oracle Support Services. This file is located in this directory:

    c:\Documents and Settings\ username \Application Data\Oracle\BISpreadsheetAddin

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

    The executable installation file is called OBISpAddinInst_10.1.2.2. xx .exe (where xx is a version number) and is available from this location:

    http://download.oracle.com/otndocs/software/spreadsheet_addin

  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 OracleBI Spreadsheet Add-In and choose OK.

    The installation program begins to copy files to your PC and to register OracleBI Spreadsheet Add-In with Excel.

  6. When the installation program is complete, you can specify whether to view the supplemental information for OracleBI Spreadsheet Add-In. Choose Finish.

2.3 Running OracleBI Spreadsheet Add-In

After installation, you can run OracleBI Spreadsheet 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: A sample workbook for the OracleBI Spreadsheet Add-In is included with the Oracle Business Intelligence sample schemas that demonstrates the features of this product. See Section 4.1.1, "Updated Samples Available" for complete information.

2.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 OracleBI Spreadsheet Add-In. Alternatively, you can run the uninstall.exe program, which is located in the directory in which you installed OracleBI Spreadsheet Add-In. Use this uninstall program only if you do not want to use the Add or Remove Programs facility and you installed OracleBI Spreadsheet 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.

3 New Features

In addition to bug fixes, this release provides important features, many of which are described in the following list. For information on all the features, consult the updated Help system for OracleBI Spreadsheet Add-In.

  • Calculated Members: You can create calculated members through a wizard in OracleBI Discoverer Plus OLAP and view them in OracleBI Spreadsheet Add-In.

  • Write-back: With the appropriate privileges, you can write data back to the analytic workspace in the Oracle database. You can also perform calculations and validations on the data, which will run on the server. See Section 5, "Setting Up for Write-Back".

  • Discoverer Catalog Support: You can save and retrieve calculations, saved selections, and calculated members from the Discoverer Catalog for use in OLAP queries.

  • Share Calculations and Saved Selections In a Workbook: You share calculations and saved selections across queries in a workbook, even when the workbook is not connected to the Discoverer Catalog.

  • Toolbar: You can use toolbar buttons to perform actions that were previously available only via menus and keystrokes.

  • Floating OracleBI Query Editor: You can allow the Query Editor to stay on top of Excel while you edit the query.

  • Enhanced Refreshing of Queries: You can refresh multiple queries in a single request, including refreshing all queries in a workbook.

  • VBA Macros: You can call numerous VBA macros to programmatically perform write-back and other operations.

4 Release Notes

This section provides release notes for OracleBI Spreadsheet Add-In. It contains the following topics:

4.1 General Issues and Workarounds with OracleBI Spreadsheet Add-In

This section provides general issues and workarounds for OracleBI Spreadsheet Add-In. It contains the following topics:

4.1.1 Updated Samples Available

To assist you in working with the new features that are available in this release, new and updated sample schemas called SH_OLAP and CS_OLAP are available. You can download these sample schemas and information on installing and working with them, as described in the following list:

  • SH_OLAP schema has the following characteristics:

    • It was created in Standard Form in Oracle Database 10 g Release 2. Therefore, it supports all the features of the 10.1.2.2 release, including calculated members and write-back in OracleBI Spreadsheet Add-In.

      You must install this schema only on a supported version of Oracle Database 10 g Release 2.

    • It does not support forecasting functionality, so you cannot use it with OracleBI Beans sample applications that previously worked with the CS_OLAP schema. Forecasting support will be added to this schema in a future release.

    • You can access the file for the schema via this URL:

      http://download.oracle.com/otndocs/products/bi/samples/bi_10_1_2_2_samples.zip

  • CS_OLAP schema has the following characteristics:

    • It was not created in Standard Form in Oracle Database 10 g Release 2. Therefore, it does not support all the features of the 10.1.2.2 release.

    • It has been updated since its last release with several bug fixes and has been modified to include more recent labels for the Time dimension. The installation scripts have been modified, and they no longer support Oracle9 i Release 2 Database. You must install this schema only on a supported version of Oracle Database 10 g Release 1 or higher.

    • It includes metadata and structures in the analytic workspace to support forecasting. Therefore, it can support legacy OracleBI Beans sample applications, such as Executive Insight.

    • You can access the file for the schema via this URL:

      http://download.oracle.com/otndocs/products/bi/samples/bi_10_1_2_v2_samples.zip

4.1.2 User Files Stored in New Location

In previous releases, the following files were stored in the installation directory, which is generally named c:\Program Files\Oracle\BIAddin:

  • oraolapxl.prefs, which contains user preferences.

  • oraolapxl.con, which contains information about the connections that the user created.

Starting in release 10.1.2.2, the files are stored in the user's Application Data directory, which is generally named c:\Documents and Settings\ username \Application Data\Oracle\BISpreadsheetAddin.

When you install the OracleBI Spreadsheet Add-In release 10.1.2.2 over an existing installation, these two files are copied to this new location.

4.1.3 Launching Another Instance of Excel

If you start one instance of Excel and subsequently 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.

4.1.4 Renaming Sheets when OracleBI Spreadsheet Add-In is not Running

Use care when renaming a worksheet that contains an OLAP query in the following situations:

  • Do not rename a worksheet that contains an OLAP query when you open it on a PC on which OracleBI Spreadsheet Add-In is not installed or is not running or on which you have deferred startup of OracleBI Spreadsheet Add-In. If you rename the worksheet under these circumstances, then OracleBI Spreadsheet Add-In cannot associate the query on that worksheet with the database when you next open the worksheet.

    You can easily determine whether OracleBI Spreadsheet Add-In is running by verifying that the full OracleBI menu is included in Excel's menu bar.

  • If you have manual calculation turned on, then you see a message to remind you of this setting being on when you start OracleBI Spreadsheet Add-In. By default, calculation is set to automatic. You change the calculation setting on the Calculation tab of Excel's Options dialog. If you have manual calculation turned on and you rename a worksheet, then you must press F9 to force a recalculation and to allow the name change to be recognized.

4.1.5 Displaying Date Measures

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

4.1.6 Specifying when Formatting is Applied

An enhancement was added to the Use default formatting for new queries option on the General tab of the Default Options dialog. Previously when the box was unchecked, OracleBI Spreadsheet Add-In did not apply formatting, but did attempt to detect and reapply any user formatting that was added after the query was created. As part of applying the formatting, OracleBI Spreadsheet Add-In reset any number formatting. This behavior was unexpected and undesirable.

With this release, if you uncheck the box, OracleBI Spreadsheet Add-In will no longer detect and apply any formatting when the query changes. This means that OracleBI Spreadsheet Add-In preserves all user formatting. It also means that you are fully responsible for maintaining all formatting. For example, if you change the shape of the query due to editing operations, then color and number formatting may no longer be applied to the desired cells. You must manually format all cells.

When the check box is checked, OracleBI Spreadsheet Add-In applies the default formatting to the query. This behavior has not changed.

4.1.7 Editing Changes Lost After Cut and Paste

You can lose editing changes in the following situation. You highlight all the cells in a query, cut the cells, paste the cells to a different location in the same worksheet, and edit the query. As you edit, the body of the query moves back to its original location, the edits are lost, and the page controls remain in their new location.

This situation occurs because Excel does not provide OracleBI Spreadsheet Add-In with enough information about the cells that you moved. To work around this situation, either refresh the query or save the workbook immediately after the cut and paste operation and before editing the query.

4.1.8 Issue with Saving Selections with Insufficient Privileges

As you work in OracleBI Spreadsheet Add-In, you can save selections to the Discoverer Catalog. If you try to save a selection to a folder in the Catalog for which you lack write privileges, then you will see no warning message to this effect and the selection will appear to be saved although it is not saved. You will discover this situation when you try to locate the saved selection.

To work around this issue, save selections only to those folders for which you know that you have the appropriate privileges.

4.1.9 Issue with Hidden Rows and Queries Without Data

If you hide rows whose values are all NA or whose values are all zero and all NA and you create a query that returns no data, then you might see only a cell that contains the text "No Data" or you might be unable to edit the query again.

Normally, you can select a cell in the query area and choose Edit Query from the OracleBI menu to begin the process of editing the query. Suppose you have hidden rows whose values are all NA and you create a query that returns no data. The resulting worksheet has all the rows hidden, and OracleBI Spreadsheet Add-In tries to create a cell that contains the text "No Data".

If you find it impossible to edit the query because the worksheet has no cells with data and no cell that contains the text "No Data", then work around this issue by completing the following steps:

  1. Exit Excel without saving changes to the workbook.

  2. Reopen the workbook to restore the query to the point at which it was last saved.

4.1.10 Issue with Attributes Listed on Conditions Tab in Query Editor

There is a known issue that affects the display of attributes on the Conditions tab of the Dimensions panel in the Query Editor for OracleBI Discoverer Plus OLAP, OracleBI Spreadsheet Add-In, and OracleBI Beans. Suppose that you are working in the Query Editor, access the Dimension panel and select a hierarchy, then select the Available Conditions tab. In the Conditions tab, drill in the Match folder. If you can create conditions with attributes, you will see a line for attributes. If you click the hyperlink for an attribute name, you will see a drop-drop box that lists all the attributes for the dimension, rather than only those attributes that are associated with the hierarchy that you selected.

This issue has no work around.

4.1.11 Toolbar Returns to Default Position

By default, the OracleBI toolbar appears docked along the left side of the Excel window. You can move the toolbar to a different location, undock it, and so on during an Excel session. After you exit and restart Excel, you will find that, unlike other Excel toolbars, the OracleBI toolbar has returned to its default location. To work around this issue, move the toolbar back to the desired position each time that you start Excel.

4.1.12 Canceling the Closing of Microsoft 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 OracleBI Spreadsheet Add-In, you must shut down and restart Excel.

4.1.13 Issues with Multiple Users Writing to a Partitioned Cube

When multiple users are accessing a read/write query from a partitioned cube and they are writing back to that same cube, they might see errors about the partitioned cube such as:

Sheet1!C6 - Object WBDEMO!BUDGET_CUBE_PART_TIME_PRT_TOPVAR is locked by another user.

This error occurs in the following situations:

  • When multiple users attempt to save changes to the database by selecting Save from the File menu. To work around this issue, a user can wait until another user has finished saving, then the first user can attempt the save operation again.

  • When multiple users attempt to perform calculation and validation by selecting Calculate and Validate Now from the OracleBI menu. To work around this issue, a users can:

    • Perform calculation and validation only in read-only mode.

    • Wait until another user has finished performing calculation and validation, then the first user can attempt the operation again.

4.1.14 Issues with Multiple Users on a Single Machine

If you and another user are running OracleBI Spreadsheet Add-In on the same machine, then the other user must manually perform the following steps:

  • Copy the oraolapxl.prefs and oraolapxl.con files to the other user's Application Data directory, to ensure that you and the other user have your own copies of these files. See Section 4.1.2, "User Files Stored in New Location".

  • Manually load OracleBI Spreadsheet Add-In into Excel by choosing Add-Ins from the Tools menu.

4.1.15 Issues with Turkish Regional Settings

Because of Sun JRE 1.4 bug 4688797, you might encounter issues when connecting to a database schema while your computer has Turkish regional settings. You will encounter the issue when you attempt to connect to a database schema with a user name that contains certain letters, such as the letter 'I' or 'i', for example, in "bibdemo". See the Sun JRE bug for information on the letters that are affected.

To work around this issue, either do not use Turkish regional settings or use a user name that does not contain the affected letters.

4.1.16 Issues with VBA Macros

The following list describes issues with VBA macros

  • If you call the BIA_Disconnect() macro and specify a value for the QueryName argument, then the macro will not disconnect the specified query unless the active cell is within the query. If you call the BIA_Disconnect() macro without any arguments, then you will not experience any issue and all queries will be disconnected.

    To work around this issue when you need to specify a query, set the active cell before calling the BIA_Disconnect() macro.

  • Several keyboard mnemonics have changed on the OracleBI menu. If you wrote any VBA code that uses the SendKeys command to invoke menu items from the OracleBI menu, then compare that code against the menu to ensure that it is still accurate.

4.2 Known Issues with Oracle OLAP Option

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

  • At times, database resources are not properly released in the Oracle9 i (Release 2) OLAP option. This may result in excessive memory consumption. To release the resources, close OracleBI Spreadsheet Add-In.

  • There are potential performance issues running against analytic workspaces in Oracle OLAP 10 g Release 1 version 10.1.0.4. To work around these issues, always check Oracle Metalink for the appropriate Oracle OLAP patches at the following location:

    http://metalink.oracle.com

5 Setting Up for Write-Back

This section provides information for database administrators and application developers to set up for write-back for users of OracleBI Spreadsheet Add-In. It contains the following sections:

Important: Write-back is a very powerful feature. You should implement write-back only if you are very familiar with Oracle OLAP and analytic workspaces.

5.1 What is Write-Back?

As a database administrator or application developer for OracleBI Spreadsheet Add-In, you can use write-back in the following two cases:

  • To allow the values to be written back permanently to an analytic workspace in the database.

  • To allow a calculation and validation program from the analytic workspace to be run on each value. This type of write-back can simulate Excel-style calculations.

    This OLAP DML program is triggered by the user entering data in an Excel worksheet. All the values in the active query are scanned for any cell value changes. If changes are found, they are submitted to the analytic workspace but not committed (that is, saved permanently) to the database. OracleBI Spreadsheet Add-In then refreshes the query with the latest data from the analytic workspace.

    One flavor of this approach is sometimes referred to as "what-if analysis." In this type of analysis, users enter values to see the effect that those changed values have on other values, such as totals. Users can view the effects of the changes, then discard their work without saving values permanently to the worksheet (if they have a read-only connection) or back to the analytic workspace (if they have a read/write connection).

5.2 What Are the Design Considerations for Write-Back?

Write-back should be performed only on analytic workspaces that have been designed for that purpose. As a database administrator who is designing an analytic workspace to be used for write-back, you must adhere to the following considerations:

  • Database access: You must adhere to the following requirements:

    • In single-writer mode, use Oracle Database 10 g Release 1 (10.1.0.4 and higher).

    • In multiwriter mode with compressed composites, use Oracle Database 10 g Release 2, Patchset 10.2.0.2 with the 5146470 patch and higher.

    • Connect to an analytic workspace in Standard Form that was built with an Oracle tool such as Oracle 10 g Analytic Workspace Manager.

  • User access: You must ensure that users who need to write back to the analytic workspaces have the appropriate privileges.

  • PERMITs: If you plan to perform scoping of the analytic workspace using the OLAP DML PERMIT command, then ensure that you read and understand the information on the PERMIT _READ and PERMIT _WRITE startup programs in the Oracle OLAP DML Reference. In particular, note the following about startup programs:

    • When OracleBI Spreadsheet Add-In establishes a read-only connection to an analytic workspace, the PERMIT_READ program runs, if it exists.

    • When OracleBI Spreadsheet Add-In establishes a read/write connection to an analytic workspace, the PERMIT_WRITE program runs, if it exists.

      Important: Ensure that you understand the use of the PERMIT_WRITE startup program, as errors in the PERMIT _WRITE program can lock you out of the analytic workspace.

  • Data types: When performing write-back, users can enter values only of one of the following supported types: integer, decimal, and text. Date and Boolean types are not supported.

  • Partitioning strategy: You must implement an appropriate partitioning strategy to accommodate write-back, as outlined in Oracle OLAP documentation.

  • Aggregation considerations: You must use care when handling aggregated values for write-back. See Section 5.2.1, "What Are the Considerations for Aggregation?"

  • Global composites: When global composites are used, the analytic workspace does not support multiwriter connections. See the Help for Analytic Workspace Manager for information on global composites.

  • Value caching: By default, analytic workspaces in Standard Form cache dynamically computed aggregate values during the user's session. When debugging a write-back solution, you might find it helpful to temporarily disable session caching of dynamically computed aggregate values by setting the SESSCACHE option to NO.

  • Additional information: For more information, see the following sources:

    • Oracle OLAP documentation, especially, the Oracle OLAP DML Reference for information about the multiwriter feature in Oracle OLAP. Consult the topics for the AGGREGATE command, the AGGREGATE function, the CACHE statement within the AGGMAP command, the CLEAR command, the SESSCACHE option, and the $VARCACHE property.

    • Write-back topics in the OracleBI Spreadsheet Add-In Help system for end-user information on this topic.

    • Section 4, "Release Notes" for important known issues that affect write-back in OracleBI Spreadsheet Add-In.

5.2.1 What Are the Considerations for Aggregation?

The aggregation strategy for the cubes in the analytic workspace affects which data cells can be updated and what the results of the updates are, as described in the following list:

  • Writing data back to a leaf level: Generally you allow users to enter data at the lowest leaf level. Ensure that you consider any dimensions that are on the page edge when creating the query.

    If users want to see the effect of the data that they enter at a leaf level on an aggregate level, another step is necessary. In this case, if any aggregate levels are precomputed or caching is enabled, then you must either rerun the AGGREGATE command or (if no levels are precomputed), flush the cache.

  • Writing data back to an aggregate level: Use care when allowing users to enter data for aggregated values and follow these guidelines:

    • Data that users write back to a dynamically computed aggregate level has no effect on any other levels, either above or below that level.

    • If you want to allow users to write back data at only specified aggregate levels, then in the aggregation plan for the cube, specify those levels as precomputed.

    • Data that users write back to a precomputed aggregate level affects non-cached, dynamically computed aggregate values at higher levels, up to but not including the next precomputed aggregate level. If users want to see the effect of that data at higher aggregate levels and caching is enabled, then you must either rerun the AGGREGATE command or (if no higher levels are precomputed), flush the cache

    • You must execute any Allocation logic that is required for users to see the effect of the newly written-back data at lower levels.

    • You can handle the automatic allocation of write-back data at the aggregate level by attaching a write trigger to the target variable or formula. This trigger is an OLAP DML program that runs when an object is the target of an assignment (=) command. You can use the write trigger for a formula to write back to the source data variables and even allocate the value to detail levels.

  • Writing data back as NA: Users can write NA data back at the leaf level and at precomputed aggregation levels. They cannot write NA data back at dynamically computed aggregation levels, because the AGGREGATE function triggers its own dynamic computations when it encounters an NA value.

5.3 What User Interface Elements Affect Write-Back?

OracleBI Spreadsheet Add-In includes the following user interface elements that support write-back:

  • Connect dialog: The Connect dialog includes two boxes that affect write-back:

    • Read-only box – This box specifies whether users establish a read-only connection to the database, and the box is checked by default. Users can uncheck this box to establish a read/write connection, if they have the appropriate privileges and want to enter data in cells.

    • Enable calculation & validation by Oracle OLAP box – This box indicates whether users want to perform calculations and validations from the Oracle OLAP option on the data that they enter in a worksheet. This box is unchecked by default for read-only connections, and users must check it each time that they establish a read-only connection and want to perform calculation and validation operations.

  • Options dialog: The Options dialog includes a Calculation & Validation tab. This tab has these options that allow users to specify when values are calculated and validated during write-back:

    • Automatic – Specifies to calculate and validate automatically each value that users enter into a cell in worksheets that contain data from Oracle OLAP.

    • Manual – Specifies to calculate and validate cells that contain data from Oracle OLAP only when users choose the appropriate menu option or toolbar button.

  • Menu options and toolbar buttons – The following menu options and toolbar buttons affect write-back:

    • Calculate and Validate Now – This option on the OracleBI menu and toolbar specifies to calculate and validate the OLAP values that users have entered in the current worksheet.

    • Save – This option on the Excel File menu and toolbar specifies to calculate and validate the OLAP values that users have entered and to save to the analytic workspace the appropriate changes. Changes are saved for all queries that use the same read/write connection. Changes are also saved to the workbook files.

5.4 How Do I Set Up for Writing Data Back Permanently?

For users to write data back permanently to the analytic workspace from Excel, you must perform the following steps:

  1. Adhere to the requirements that are specified in Section 5.2, "What Are the Design Considerations for Write-Back?"

  2. Start Excel with a new, empty workbook and select New Query from the OracleBI menu.

  3. In the Connect dialog, perform the following steps:

    1. Uncheck the Read-only box.

    2. Enter the appropriate information to connect to the database.

    3. Choose OK.

  4. In the Query Editor, create the query in which users will enter values.

    Important: Ensure that users know which values in the worksheet can be edited. Users can enter values in cells that contain OLAP calculations and calculated members and will receive no notification of this, but these values cannot be saved to the analytic workspace. If you include calculations or calculated members in the query, then ensure that users understand this limitation, for example by providing instructions and visual cues in the worksheet.

  5. Save changes to the workbook and close it.

  6. Send the workbook to users.

A user performs the steps that are described in the "Performing Write-Back" topic in the OracleBI Spreadsheet Add-In Help system to write values back to the analytic workspace. The Read-only box in the Connect dialog will be unchecked, because the workbook was last saved with that setting.

5.5 How Do I Set Up for Writing Data for Calculation and Validation?

For users to write data for calculation and validation, you must perform the following steps:

  1. Adhere to the requirements that are specified in Section 5.2, "What Are the Design Considerations for Write-Back?"

  2. Start Excel with a new, empty workbook and select New Query from the OracleBI menu.

  3. In the Connect dialog, perform the following steps:

    1. Uncheck the Read-only box.

    2. Check the Enable calculation & validation by Oracle OLAP box.

    3. Enter the appropriate information to connect to the database.

    4. Choose OK.

  4. In the Query Editor, create the query in which users will enter values.

    Important: Ensure that users know which values in the worksheet can be edited. Users can enter value in cells that contain OLAP calculations and calculated members and will receive no notification of this, with unpredictable results. If you include calculations or calculated members in the query, then ensure that users understand this limitation, for example by providing instructions and visual cues in the worksheet.

  5. Decide in which analytic workspace you will store the OLAP DML program that will perform the calculation and validation. Consider the following:

    • You can store the program in the same analytic workspace that contains the data on which users are querying. The workspace is attached to the user's session at connection time.

    • You can store the program in an analytic workspace that has been designated to store programs for applications. You must ensure that you attach this workspace so that programs are available during the user's session.

  6. Create the OLAP DML program in the appropriate workspace, using Analytic Workspace Manager or OLAP Worksheet. See Section 5.5.1, "How Do I Create an OLAP DML Program?"

  7. Use the BIA_RegisterCalcValidationProgram() macro to register the OLAP DML program. In the following example code, the name of the query in the worksheet is ÒQuery 1Ó and the name of the OLAP DML program is ÒwbvalidatorÓ. If the program does not exist in the same analytic workspace as the data, then you must specify the fully qualified name of the program, in the form owner.workspace!program , such as pedro.mydemo!wbvalidator.

    Private Sub CommandButton1_Click()
       BIA_RegisterCalcValidationProgram", "Query 1", "wbvalidator"
    ' comment
       MsgBox "Calculation & Validation enabled."
    End Sub
    
    

    You can include the BIA_RegisterCalcValidationProgram() macro in the auto_open procedure to automatically register the program for calculation and validation when a workbook is opened.

  8. Save changes to the workbook and close it.

  9. Send the workbook to users.

A user performs the steps that are described in the "Performing Write-Back" topic in the OracleBI Spreadsheet Add-In Help system to write data for calculation and validation.

5.5.1 How Do I Create an OLAP DML Program?

Use the following procedure to create an OLAP DML program:

  1. In Analytic Workspace Manager, locate and make note of the object names that you need to reference in the program. These objects include the name of the measure for which users are writing values and the dimensions and dimension values of that measure.

    For example, users might select the Sales measure in OracleBI Spreadsheet Add-In. In Analytic Workspace Manager, you might find that the object name for the Sales measure is WB_DEMO.SH_AW!SALES.

  2. In Analytic Workspace Manager, right-click the analytic workspace in which you want to create the program, then select OLAP Worksheet.

    You can store the program in the same analytic workspace that contains the data on which users are querying or in a workspace that has been designated to store programs for applications.

  3. At the command line, enter the following:

    DEFINE WBVALIDATOR PROGRAM

  4. To edit the program, enter the following:

    EDIT WBVALIDATOR

  5. Enter the code for the program. See Section 5.6, "Code Example for Calculation and Validation Program".

  6. Save your changes and exit the editor.

  7. To save the program in the database,, enter the following at the command line:

    UPDATE COMMIT

  8. Exit the OLAP Worksheet.

For information on the OLAP DML, see the Oracle OLAP Application Developer's Guide and the Oracle OLAP DML Reference.

5.6 Code Example for Calculation and Validation Program

This section describes and provides an example of an OLAP DML program called WBVALIDATOR that validates the data that is entered within certain ranges for various measures in OracleBI Spreadsheet Add-In. It contains the following sections:

5.6.1 How the Program Works

The WBVALIDATOR program is run for every cell that a user has updated. The write-back engine in Oracle OLAP passes to the program a fully qualified data reference (QDR) and the data value that the user entered in the cell. The program then checks the QDR and data value and takes the appropriate action. If an error occurs, then the program returns a string for display in OracleBI Spreadsheet Add-In.

5.6.2 Argument for the Program

The WBVALIDATOR program takes a single argument that is passed in from the write-back engine. The string that is passed in contains information about the active measure, the current dimension values, and the data value that is being written back. The format of this string argument is as follows:

schema.awname!variable_name(schema.awname!dimension1_name 'dimension1_value\', schema.awname!dimension2_name 'dimension2_value\'... ,schema.awname!dimensionN_name 'dimensionN_value\')=2000

The following is an example of an argument that can be passed to the program:

SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM 'REVENUE' 
SH_OLAP.SH_AW!TIME '1717',SH_OLAP.SH_AW!GEOGRAPHIES '52785',
SH_OLAP.SH_AW!PRODUCTS '15',SH_OLAP.SH_AW!CHANNELS '3')=12

5.6.3 Specifying the Measure

To calculate and validate the data, the WBVALIDATOR program must use the name of the measure on which it is running. The following code fragment shows how to specify the Sales Revenue measure in the SH_OLAP database schema.

measID = 'SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM \'REVENUE\''

5.6.4 Finding the Data Value

The WBVALIDATOR program must know the data value that the user entered in the cell, such as 12345. If the argument that is passed into the program is stored in a variable called _qdr, then the following line of code extracts the data value from the argument and stores that name in a variable called _value.

_value = blankstrip(extchars(_qdr, findchars(_qdr, '=') + 1), BOTH)

The calculation and validation program must handle the case where the user clears a cell in Excel and saves the changes. Without the handling of empty cells, errors can occur with unpredictable results. The program can check for empty cells by locating NA values. In the following example, the program checks for NA values and exits the program if one is found. You can write a program that handles NA values in a different way.

if nafill(_value, 'NA') eq 'NA'
      then return

5.6.5 Finding the Value of a Specific Dimension

The WBVALIDATOR program must know the value of each dimension, to perform the appropriate actions. To find the value of a specific dimension, the program uses the OLAP DML EXTCHARS and FINDCHARS functions to first remove the text beyond value name and then the text before the value name, as shown in the following example.

" Find the QDR value for PRODUCTS 
ProdID = 'SH_OLAP.SH_AW!PRODUCTS' 
_ProdQDRValue = extchars(_qdr, findchars(_qdr, '\'', - 
                findchars(_qdr, ProdID))+1) 
_ProdQDRValue = extchars(_ProdQDRValue, 1, findchars(_ProdQDRValue, '\'')-1) 

5.6.6 Taking Actions with the Values

When the WBVALIDATOR program obtains the values that it needs, it can check for conditions and take appropriate actions. For example, the program can verify that the value that the user entered is within a certain limit. If the value is outside the limit, then the program can display an error message.

The following example code checks if the value is above 200,000 and if it is not, then the program stores error message text in the _retcode variable. The RETURN command displays the text in an error message box in Excel.

if convert(_value, decimal) le 200000
     then _retcode = joinchars('Sales Revenue for ', _TimeQDRLongDesc, -
        ' must be above $200000')
return _retcode

See the complete program code for an example of performing a calculation if values are within an allowed range.

5.6.7 Complete Program Code

This section contains the complete code for the WBVALIDATOR program.

DEFINE WBVALIDATOR PROGRAM
PROGRAM
" This program is a sample OLAP DML program that can be used 
" with the BI Beans write-back engine 
" or the OracleBI Spreadsheet Add-in write-back feature.
" It demonstrates how to find data and dimension values and 
" process them for validation purposes,
" in this case, to check whether values entered are in a certain range.
" The program is called once for each cell that has been updated.
" You should be familiar with OLAP DML in order to create or 
" modify such a program.
 
arg _qdr            text       " Argument to program
                               " Contains full string of QDR pairs/data values
                               " from the write-back engine for all cells.
vrb _value          text       " Contains the data value
vrb _retcode        text       " Return code from this program will be 
                               " passed back to add-in for display
vrb _numeric        text
vrb _text           text
vrb measID text        " Must be set to full measure ID from first part of _qdr
vrb ProdID text 
vrb _ProdQDRValue      text     " After arguments have been parsed, this 
                               " variable contains the PRODUCT dimension value.
vrb GeogID text
vrb _GeogQDRValue text        " After arguments have been parsed, this
                              " variable contains the GEOGRAPHY dimension value.
vrb ChanID text
vrb _ChanQDRValue text        " After arguments have been parsed, this
                              " variable contains the CHANNEL dimension value. 
vrb TimeID text
vrb _TimeQDRValue text        " After arguments have been parsed, this 
                              " variable contains the TIME dimension value.
vrb _TimeQDRLongDesc text 
vrb _dimLongDesc text 
 
trap on error noprint
 
_retcode = '0'
if findchars(_qdr, '=') gt 0
then do
" Specify measure for the program to process, in this case 'Sales Revenue'
measID = 'SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM \'REVENUE\'' 
 
" Exit if not correct measure 
if findchars(_qdr, measID) eq 0 
  then return 
 
" Parse arguments to find MEASURE data value
_value = blankstrip(extchars(_qdr, findchars(_qdr, '=') + 1), BOTH) 
 
" Find the QDR value for PRODUCTS 
ProdID = 'SH_OLAP.SH_AW!PRODUCTS' 
_ProdQDRValue = extchars(_qdr, findchars(_qdr, '\'', - 
                findchars(_qdr, ProdID))+1) 
_ProdQDRValue = extchars(_ProdQDRValue, 1, findchars(_ProdQDRValue, '\'')-1) 
 
 " Find the QDR value for GEOGRAPHIES
GeogID = 'SH_OLAP.SH_AW!GEOGRAPHIES' 
_GeogQDRValue = extchars(_qdr, findchars(_qdr, '\'', -
                findchars(_qdr, GeogID))+1) 
_GeogQDRValue = extchars(_GeogQDRValue, 1, findchars(_GeogQDRValue, '\'')-1)
 
 " Find the QDR value for CHANNELS
ChanID = 'SH_OLAP.SH_AW!CHANNELS' 
_ChanQDRValue = extchars(_qdr, findchars(_qdr, '\'', - 
                findchars(_qdr, ChanID))+1) 
_ChanQDRValue = extchars(_ChanQDRValue, 1, findchars(_ChanQDRValue, '\'')-1) 
 
 " Find the QDR value for TIME
TimeID = 'SH_OLAP.SH_AW!TIME'
_TimeQDRValue = extchars(_qdr, findchars(_qdr, '\'', - 
               findchars(_qdr, TimeID))+1) 
_TimeQDRValue = extchars(_TimeQDRValue, 1, findchars(_TimeQDRValue, '\'')-1) 
 
" Get TIME long description to use in message 
 dimLongDesc = lmt(name to obj(property 'AW$ROLE') eq 'ATTRDEF' and -
     obj(property 'AW$PARENT_NAME') eq 'TIME' and - 
     obj(property 'AW$TYPE') eq 'MEMBER_LONG_DESCRIPTION')
_TimeQDRLongDesc = &_dimLongDesc(TIME _TIMEQDRValue) 
 
" Handle NA values by setting cells to NA 
if nafill(upcase(_value), 'NA') eq 'NA' 
    then do
      push PRODUCTS GEOGRAPHIES CHANNELS 
      limit PRODUCTS to _ProdQDRValue
      limit GEOGRAPHIES to _GeogQDRValue 
      limit CHANNELS to _ChanQDRValue 
SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM 'REVENUE' TIME '1717') = NA 
SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM 'REVENUE' TIME '1718') = NA 
SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM 'REVENUE' TIME '1719') = NA 
       pop PRODUCTS GEOGRAPHIES CHANNELS
      return 
      doend 
 
 " Display message if Sales Revenue is
" less than 200000 or more than 2000000 
if convert(_value, decimal) le 200000 
   then _retcode = joinchars('Sales Revenue for ', _TimeQDRLongDesc, ' -
                   must be above $200000')
if convert(_value, decimal) gt 2000000 
   then _retcode = joinchars('Sales Revenue for ', _TimeQDRLongDesc, ' -
                  must be below $2000000') 
 
" Perform calculation, if values are within allowed range.
" Take input value for October 05 (1717) 
" Multiply by 1.10 and put in Nov 05 (1718) 
" Multiply by 1.20 and put in Dec 05 (1719) 
if _retcode eq '0' 
  then do 
SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM 'REVENUE' TIME '1718') = -
SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM 'REVENUE' TIME '1717')*1.1
SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM 'REVENUE' TIME '1719') = -
SH_OLAP.SH_AW!SALES_PRT_TOPVAR(SH_OLAP.SH_AW!SALES_PRT_MEASDIM 'REVENUE' TIME '1717')*1.2 
      doend
doend
 
" If no error conditions have been encountered, 
" then _retcode will be '0', which indicates success.
" If an error occurs, such as a variable out of range,
" then _retcode contains text to be passed back to 
" OracleBI Spreadsheet Add-in 
return _retcode
 
" If an error occurred during execution of the program, 
" then return the error message.
error:
    return joinchars('An error occured during validation:',  errorname, ' ', -
         errortext) 
END

6 Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at

http://www.oracle.com/accessibility/

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

TTY Access to Oracle Support Services

Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, seven days a week. For TTY support, call 800.446.2398.


Oracle Business Intelligence Spreadsheet Add-In Installation Guide and Release Notes, 10 g Release 2 (10.1.2.2) for Microsoft Windows

Part No. B16035-03

Copyright © 2006 Oracle. All rights reserved.

The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software—Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle, JD Edwards, and PeopleSoft are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

 E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy