5 Querying Dimensional Objects Using OLAP Tools

Getting Started With the OracleBI Spreadsheet Add-In

Installation of the Add-In creates a new OracleBI menu on the Excel menu bar, as shown in Figure 5-2. You can add queries to any spreadsheet. The currently selected cell in Excel identifies the upper left cell of the crosstab that will be downloaded from Oracle Database. A warning message appears if the Oracle data will overwrite any data already entered in the spreadsheet.

On the OracleBI menu, click Supplementary Information for links to the tutorials and demonstrations. The Online Tutorial provides detailed instructions for the tasks shown briefly in this chapter.

Figure 5-2 Spreadsheet Add-In Menu

Description of "Figure 5-2 Spreadsheet Add-In Menu"

Creating a Query Using the Add-In

The Spreadsheet Add-In uses the same query wizard as the Measure Viewer in Analytic Workspace Manager. You select the measures, the layout, and the dimension members. The first page of the wizard lists all the measures that you have privileges to query.

To create a query:

1. On the OracleBI menu, choose New Query.

   The Connect Query to Oracle OLAP Data Source dialog box opens.

2. On the Connection Editor tab, define a connection to Oracle Database.

   Click Help for information about your choices.

3. On the OLAP Connection tab, provide your credentials to log in to Oracle Database.

   The OracleBI Query Wizard opens.

4. Follow the steps of the wizard. When you are done, the data is displayed in Excel.

5. Choose Edit Query from the OracleBI menu, if you want to change the data selection or any other aspect of the query.

   If this choice is not available, click any cell with data returned by the query.

Figure 5-3 shows the first page of the Query Wizard. In this example, a measure folder named "Global Enterprises" contains the measures and calculated measures in the Global analytic workspace.

Figure 5-3 Selecting Measures in the Query Wizard

Description of "Figure 5-3 Selecting Measures in the Query Wizard"

Figure 5-4 shows the downloaded data from Oracle. It appears with the default Excel formatting for the font, size, justification, decimal places, and so forth.

Figure 5-4 Oracle Data Downloaded to a Spreadsheet

Description of "Figure 5-4 Oracle Data Downloaded to a Spreadsheet"

Using Excel Features on Oracle Dimensional Data

You can change all of the default settings in Excel the same as for any other data. Figure 5-5 shows the same data as Figure 5-4, but with the following changes:

• All data is displayed to two decimal places after using the Increase Decimal tool on the Excel toolbar.

• The PctChg Sales PP column displays the data as percentages instead of decimals after using the Percent Style tool.

• A new row contains data calculated in Excel using the Sum function on Sales, Sales PP, and Chg Sales PP, and the Average function on PctChg Sales PP. The row has the label "Total."

You can also add charts in Excel using the Chart Wizard. Figure 5-1 shows the addition of two pie charts.

Figure 5-5 Using Excel to Format the Data

Description of "Figure 5-5 Using Excel to Format the Data"

Getting Starting with Discoverer Plus OLAP

Discoverer organizes worksheets into workbooks, like a spreadsheet package. After you open Discoverer and log in, the Workbook Wizard opens automatically, as shown in Figure 5-7. You can open an existing workbook, or you can create a new workbook and define the contents of the first worksheet.

Figure 5-7 Defining a New Discoverer Workbook

Description of "Figure 5-7 Defining a New Discoverer Workbook"

Creating a Query

When creating a new worksheet, you can specify in the first step of the Workbook Wizard which options you want, such as a title or a graph. The wizard then steps you through the creation of these options. You can add or delete these optional components at any time.

The Workbook Wizard uses the same query wizard as the Spreadsheet Add-In and the Measure Viewer in Analytic Workspace Manager. You select the measures, the layout, and the dimension members.

To create a new workbook and a worksheet:

1. Open Discoverer Plus OLAP and log in to Oracle Database.

   The Workbook Wizard opens to the Create/Open Workbook page.

2. Select Create a New Workbook, the select the optional components of the first worksheet.

3. Follow the steps of the wizard. When you are done, the data is displayed in the main window as a crosstab, or a graph, or both.

4. Modify the worksheet as you like, changing the data selection or the data formats, adding calculations, and so forth.

5. To add another worksheet to the workbook, choose Add Worksheet from the Edit menu.

Figure 5-8 shows a worksheet that was defined in the Workbook Wizard. It appears with the default formatting for the font, justification, decimal places, and so forth.

Figure 5-8 Default Query Results in Discoverer

Description of "Figure 5-8 Default Query Results in Discoverer"

Formatting the Data in Discoverer Plus

You can change every aspect of the query and its formatting using the menus and toolbars. Figure 5-9 shows the same basic query as Figure 5-8, but with the following changes:

• A new title appears in a custom font and background color. Click the default title.

• All data is displayed to two decimal places. Use the Add Decimal tool.

• The Percent Change in Sales From Prior Period column displays the data as percentages instead of decimals. Use the Format As Percent tool.

• Stoplight formatting of the Percent Change in Sales From Prior Period column highlights products that are outside the target range in some areas. Select New Stoplight Format from the Format menu.

• The Time selection changed from 2003 to Q4-03. Use the Members tab on the Available Items pane.

• A pie chart for Sales replaced the bar charts for all measures. Use the Edit Graph Type and Properties tool.

You can also perform calculations on the data, define new calculated measures, and create saved selections of dimension members.

Figure 5-9 Changing the Default Formatting in Discoverer

Description of "Figure 5-9 Changing the Default Formatting in Discoverer"