Sample Workbook Overview
January 2005

Release 2 version 10.1.2.0.0 (Build 10.1.2.0.36)

The Oracle Business Intelligence Spreadsheet Add-In ("the Spreadsheet Add-In") provides a sample workbook to help you learn how to use the product. The worksheets in the sample workbook illustrate key features that you will find useful when analyzing and reporting business data. These features include richly formatted reports and charts, advanced OLAP calculations, conditional queries, and more. The workbook also contains samples that show how you can use OLAP data with Excel functionality.

Before you begin to work with the sample workbook, ensure that the following prerequisites have been met:

• Download and install the latest version of the sample workbook from Oracle Technology Network:
  • Create a new directory (referred to as <sample_home>) on the computer where Oracle Business Intelligence is installed.
  • Download the sample zip file to <sample_home> and unzip it.
• The sample data set, which is known as the Common Schema, must be installed. The common schema is used by all of the OLAP tools in Oracle Business Intelligence, including OracleBI Discoverer, Oracle BI Beans and the Spreadsheet Add-In. For instructions, see Installing the Common Schema.
• The Spreadsheet Add-In must be installed on your computer, as described in the installation documentation.

To access the sample workbook, perform the following steps.

1. Start Excel and ensure that the OracleBI menu appears in the menu bar.
2. From the File menu, choose Open and browse to the <sample_home> directory.
3. Select the sample.xls file, and choose Open. A dialog informs you that the workbook contains macros.
4. Choose Enable Macros button. The sample workbook opens in Excel.
5. To connect the query in a worksheet to the database in which the sample data set is installed, perform the following steps:
   1. Select a cell for the query on the worksheet.
   2. From the OracleBI menu, choose Refresh Query. Or right-click the cell and choose Refresh Query from the menu.
   3. In the Connect dialog, select the Connection Editor tab.
   4. Choose New.
   5. Enter a description in the Description field.
   6. Enter the host, port, and SID information for the database in which the sample data set is installed.
   7. Choose Save.
   8. Select the OLAP Connection tab. The newly created connection appears in the Connection field.
   9. Select the new connection.
   10. Enter the user name and password for the sample data set. The default is cs_olap for both the user name and password.

The query in the worksheet is now connected to the database. You can begin working with the sample worksheet.

Important: For each worksheet, you must refresh the query that it contains by selecting a cell in the query and choosing Refresh Query from the OracleBI menu.

The sample workbook contains several worksheets, each highlighting product features and demonstrating how the Spreadsheet Add-In can be used with Excel features such as formulas, charts, and macros. Each worksheet contains a description of the feature that it is demonstrating.

The following subsections describe each sample worksheet, listed by the name of the worksheet.

The Simple worksheet shows a simple query. You can use this worksheet for drilling, paging, and experimenting with a simple query that was created by running the OracleBI Query Wizard.

The Advanced worksheet shows a more advanced query with the following characteristics:

• The query is a ranking query, which calculates the top 5 products for each Geography and Channel.
• Subtotals have been added as Excel formulas.
• A chart has been added, based on the data in the query.
• A title, which dynamically changes with the paging dimension, has been added.

The Controls workbook shows how to use Excel forms controls and macros to interact with the OLAP query.

The QuickActions worksheet contains an example of how to use the applySavedSelections API call with Excel controls and macros. If you create a saved selection, then you can create a macro to apply it to a query. The macro can be called from Excel controls, such as text controls or radio buttons. For example, if you created a saved selection named Top5Products, then you could create a macro similar to the following:

Sub Top5Prods()
Application.Run "OLAP4XL.xla!ApplySavedSel", "Top5Products"
End Sub

Note: This feature is for demonstration purposes only and may be changed, removed, or replaced by a different API call in a later release.

The Stoplight worksheet shows how to use Excel-based conditional formatting with OLAP data.

The VLookup Demo worksheet shows how to combine OLAP data with external data using the Excel VLOOKUP function.

The VLookup Source Data worksheet contains some of the external data that is used with the VLookup demo.