Lesson 3: Using the OracleBI Spreadsheet Add-In with OLAP Data

Purpose

This tutorial teaches you how to use the OracleBI Spreadsheet Add-In that is contained in the AS/DS 10G R2 PATCH SET 2 (10.1.2.2).

Note: The Prerequisites topic in the first lesson in this focus area (Lesson 1: Creating Analytic Reports Using Discoverer Plus OLAP 10.1.2.2) contains important information on the 10.1.2.2 Patch Set requirements.

There are many new features for Oracle Business Intelligence that is associated with the 10.1.2.2 patch set release. For the Spreadsheet Add-In, a number of new features are available. This lesson will include an examination of three of these new features:

	Discoverer Catalog Support: You can save and retrieve calculations, saved selections,and calculated members from the Discoverer Catalog for use in OLAP queries.
	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.

Using the Spreadsheet Add-In, you will create reports against the same OLAP data that you accessed with OracleBI Discoverer Plus OLAP in the first two lessons in this focus area. The same wizards that you have become familiar with while using Discoverer Plus OLAP are also in the Spreadsheet Add-in, making the user experience consistent regardless of the end-user tool selected.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Create a Product Ranking Report
	Modify the Ranking Report
	Create a Geographical Share Report
	Summary
	Related information

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

In this lesson, you create two reports in separate worksheets.

In the first worksheet, you create a product ranking report that uses saved selection that you created using Discoverer Plus OLAP. In addition, you will also use one of the custom Calculated Members that you created in Discoverer. You will modify the report using the new floating Query Editor, create new saved selections, and finally you will add an Excel chart to the worksheet.

In the second worksheet, you create a geographical share report. This report will also use existing saved selections. The report will show how much sales and profit that each of the top performing countries contribute to both the global total and also to their region.

Back to Topic List

Prerequisites

Before starting this tutorial, you should have:

Completed the first and second lessons in this Focus Area: Lesson 1: Creating Analytic Reports Using Discoverer Plus OLAP 10.1.2.2 Lesson 2: Create a Report that Uses Calculated Members

Back to Topic List

Create a Product Ranking Report

In this topic, you will connect to the same common schema analytic workspace that you accessed in the previous two lessons. Then, using the same query wizard that you learned to use in Discoverer Plus OLAP, you will create a product ranking report. This report will make use of a saved selection and calculated member that you previously created while using Discoverer.

	Connect to the OLAP Data Source
	Use the OracleBI Query Wizard

Back to Topic List

Connect to the OLAP Data Source

To connect to the common schema analytic workspace (AW), perform the following steps:

Back to Topic

Use the OracleBI Query Wizard

Here, you will use the OracleBI Query Wizard to create the OLAP query for the Product ranking report.

1.	In the Query Wizard Welcome page, click Next.
2.	In Step 1 of the Query Wizard, perform the following: A. Open the Electronics – KPIs folder. B. Select the Sales Revenue and Profit measures. C. Move the measures to the Selected list, like this:   D. Click Next.
3.	In Step 2, create the following layout by dragging the crosstab items. Then, click Next.
4.	For the Time dimension (Step 3 of the wizard), perform the following: A. Select the years 2004 and 2005. Then, move them to the Selected list. B. Drill on the Calculated Members folder, select the 2004-2005 calculated member, and move it to the Selected list. The resulting selection should look like this: Note: This is an illustration of the shared Discoverer catalog. This calculated member, which you created using Discoverer Plus OLAP, is accessible to users of the Spreadsheet Add-In.   C. Rearrange the order of the Time members in the Selected list so that the Calculated Member displays last in the list by doing the following: - First, click the Members tab of the Selected list. - Then, select the 2004-2005 calculated member. - Finally, click the Move down button twice (located at the bottom right-hand corner of the Selected list). The dimension member order should look like this:   D. Click Next.
5.	For the Channel dimension (Step 4of the wizard), move All Channels, Direct, Indirect, and Others to the Selected list and then click Next.
6.	For the Geography dimension (Step 5 of the wizard), use the My Geographic Regions saved selection. A. Click the Saved Selections tab, and open the Users\SCOTT folder. B. Select My Geographic Regions and move it to the Selected list. Note: This is another illustration of the shared Discoverer catalog. This saved selection, which you created using Discoverer Plus OLAP, is accessible to users of the Spreadsheet Add-In.   C. Click Next.
7.	For the Product dimension (Step 6 of the wizard), create a multi-step ranking selection. This ranking selection will return the top 5 products from the set of products that you saved in a previous lesson: The My Products saved selection. The ranking will be based on Sales Revenue. Then, when the ranking selection criterion is complete, you will save it for later use. A. In the Available list, click the Saved Selections tab , open the Users\SCOTT folder, and move My Products to the Selected list.   B. In the Available list, click the Conditions tab, open the Top/Bottom folder, choose the first condition template (Top 10 based on Profit), and move it to the Selected list, like this:   C. Next, you will modify the ranking condition. Select the ranking condition step and click the Edit Step (pencil) icon.   D. In the Edit Step dialog box, change the condition criteria to keep the top 5 products based Sales Revenue, as shown below.   E. Next, you will qualify the product-ranking step for the other dimensions in the query. Click the Qualify button. F. In the Qualify Measure dialog box, select 2005 for the Time dimension, and then select the Each option for both the Channel and Geography dimensions, as shown below. This means that the product ranking condition will be reevaluated when new Channel and Geography members are selected from the page items area in the report. Also, the ranking condition will be based on the Time value of the year 2005. Click OK to close the Qualify Measure dialog box.   G. Click OK to close the Edit Step dialog box. The Selected list should now look like this:   H. At the bottom of the Selected list, click Save. I. In the Save Selection As dialog box, enter My Top 5 Products for 2005 Each Chan Geog as the name, accept the Save Steps option, and click OK. Notes: You can create saved selections in the Spreadsheet Add-In, just like in Discoverer Plus OLAP. As you saw earlier, you can use calculated members that have been created in Discoverer. However, you can not create them from within the Spreadsheet Add-In.
8.	Click Finish to create the report, which should look like this:
9.	If your report does not display decimal places, create formatting of two decimal places for all of the measure data by following the instructions in these next two steps. If your data already contains two decimal places as shown above, move to step 11. A. Select the data body for all of the measure data (from cell B6 to G10) B. Select Format > Cells from the main menu. C. In the Number tabbed page, select Number as the Category and 2 as the value for Decimal places. Then click OK.
10.	With the same selection of the data body, set the current data formatting as the default for all queries. A. Select OracleBI > Default Options from the main menu. B. In the General tabbed page, select the Current data body formatting option in the Default query formatting section. Then click OK. Now, when you add new measures to your OLAP reports, the data should display 2 decimal places
11.	Select members from the Geography and Channel dimension boxes. The product ranking condition reevaluates for each new Geography and Channel member
12.	In addition, you can drill on Time dimension members by double-clicking the drill down (+) or collapse (-) symbols.

Back to Topic

Modify the Ranking Report

In this topic, you will use the Query Editor to modify the layout, dimension selections, and ranking condition of the report.

	Use the Floating Query Editor
	Add an Excel Chart to the Worksheet

Back to Topic List

Use the Floating Query Editor

The Query Editor is the same wizard that you used in Discoverer Plus OLAP to edit a query. The name of this wizard in Discoverer is the Worksheet Wizard, but all aspects of the wizard are the same in both products.

In the Spreadsheet Add-In, you can specify how you want the Query Editor to be displayed. You can have the Query Editor close after you apply query changes (this is the default behavior), or you can have the Query Editor stay open after you apply query changes. This is also called enabling the "floating" Query Editor.

1.	To establish the floating Query Editor, first select OracleBI > Default Options from the menu.
2.	In the Default Options dialog box, select the following query editor option in the General tabbed page:
3.	To open the Query Editor, click the Edit Query icon. Result: The floating Query Editor appears in the lower-right hand corner of the spreadsheet.
4.	Change the position of, and selection for, the Time dimension by following these steps: A. Click the Layout tab. Drag Time to the Page Items area, like this: Then, click Apply. Result: The worksheet layout changes as Time is moved to the page items area. In addition, the Query Editor stays open.   B. Click the Dimensions tab. By default, the Time dimension should be selected. Click the Remove All button to clear the current selection. Drill on 2005 and then move the four quarters in 2005 to the Selected list, like this:   C. Click Apply. The report should look similar to this:
5.	Modify the product ranking condition so that it reevaluates for any Time dimension member, rather than basing the ranking on the year 2005. Then, save the new selection. A. In the Dimensions tab of the Query Editor, select Product. B. In the Selected list, click the ranking condition step, and then click the Edit Step (pencil) icon. C. In the Edit Step dialog box, click the Qualify button.   D. In the Qualify Measure dialog box, change the Time selection from 2005 to Each Time and then click OK.   E. Then, click OK to close the Edit Step dialog. F. At the bottom of the Selected List, click Save. In the Save Selection As the selection, enter the name My Top 5 Products Each Time Geog Chan, use the Save Steps option, and click OK. G. Click Apply in the Query Wizard. HI. Select Q3 05 from the Time drop-down box. The product ranking reevaluates and the data updates. Select Q4 05 from the Time drop-down box. Again, the ranking reevaluates and the data updates.
6.	Add the Margin % measure to the report by following these steps: A. In the Items tab of the Query Editor, open the Electronics KPIs folder, select Margin %, and move it to the Selected list.   B. Click Apply. Result: The Margin % measure is added to the report.
7.	Modify the report to rank products based on Profit, rather than Sales Revenue. A. In the Dimensions tab of the Query Editor, select Product. B. In the Selected list, click the condition step. C. Click the Sales Revenue hypertext link and select Profit from the list. The modified ranking condition should look like this: Result: The condition will now perform product ranking using the Profit measure.   D. Click Apply. The report updates to rank product members based on Profit.   E. Select members from any of the page items dimensions. The ranking condition reevaluates based on Profit.
8.	Now, modify the ranking condition to be based on Margin %. A. In the Dimensions tab of the Query Editor, ensure that Product is still selected. B. As before, change the measure in the ranking condition by clicking the Profit hypertext link. Select Margin % from the list. The modified ranking condition should look like this:   C. Click Apply. The report updates to rank product members based on Margin %.   D. Now, when you select members from any of the page items dimensions, the ranking condition reevaluates based on Margin %. The floating Query Editor makes it easy to modify the OLAP query to perform varied analysis.

Back to Topic

Add an Excel Chart to the Worksheet

Next, you add an Excel chart to the worksheet. Although you will use Excel features to insert the chart, the chart will be associated with the OLAP query, and will dynamically update as you modify the query.

1.	First, in the Query Editor, change the product ranking condition once again to rank data based on Sales Revenue. Then, click OK to apply the change and close the Query Editor.
2.	Add an Excel chart by following these instructions: A. Select the cells containing data in the Excel spreadsheet, like this:   B. Select Insert > Chart from the main menu. C. In step 1 of the Chart Wizard, select Pie as the Chart type, and Exploded pie with 3-D visual effect as the Chart subtype.   D. Click Finish to insert the chart.
3.	Reposition the chart underneath the crosstab, and then reposition the title over the pie graph.
4.	You can select a new Geography, Channel, and Time member from the drop-down lists, and both presentations are automatically updated. For example:
5.	Rename the worksheet. A. Right-click on “Sheet 1” and select Rename from the menu. B. Enter My Top 5 Products. C. Click Enter.
6.	When you are done experimenting with the report, save the workbook. A. Select File > Save As from the menu bar. B. Enter OracleBI-SSA.xls as the name. C. Click Save.

Back to Topic

Create a Geographical Share Report

In this topic, you will create a geographical share report in a new worksheet. This report will show how much sales and profit that each of the top performing countries contributes to both the global total and also to their region.

Note: The share measures are computed as a % contribution.

1.	Click on Sheet 2, and then click the New Query icon.
2.	The Connect Query 2 dialog box opens, with the appropriate connection information pre-filled. Click Connect. Result: The Query Wizard appears.
3.	Click Next on the Welcome screen. Then, in Step1 of the Query Wizard, perform the following: A. In the Available list, open the Electronics - Share Calculations folder. B. Select the following measures: Sales Revenue, Geography Share of Parent Revenue, and Geography Share of Total Revenue. C. Move the measures to the Selected list, like this:   D. Click Next.
4.	In Step 2, create the following layout: Then click Next.
5.	For the Time dimension (Step 3 of the wizard), create a condition to return the last six quarters. A. Click the Conditions tab and open the Time/Ordinal folder. B. Select the Last 3 Calendar Month template, and change the hypertext links to read: Last 6 Calendar Qtr.   C. Move the condition to the selected list. You can preview the results of this condition by clicking the Selected Members tab.   D. Click Next.
6.	For the Channel dimension (Step 4 of the wizard), select All Channels, Direct, Indirect, and Others. Then click Next.
7.	For the Geography dimension (Step 5 of the wizard), use the “Countries Sales GT 10K” saved selection that you previously created while using Discoverer Plus OLAP.. A. Click the Saved Selections tab, and open the Users\SCOTT folder. B. Select Countries Sales GT 10K Each Time Prod Chan and move it to the Selected list.   C. Click Next.
8.	For the Product dimension (Step 6 of the wizard), use the Members tab of the Available list: A. Drill on All Products. B. Select Electronics, Hardware, Accessories, Photo, and Software/Other, and move them to the Selected list.
9.	Click Finish to create the report. The report should look something like this: The report illustrates the difference between Parent and Total Share calculations: “Parent” share data represents the percentage contribution of a member to its parent level within the hierarchy – in this case for each country to its region. · “Total” share data represents the percentage contribution of a member to the total for that dimension – in this case for each country to the global total. For example, in the Electronics Product category, for All Channels of distribution, in Q3 of 2004, Germany contributed 30% of the sales revenue to Europe, and 9% to global sales.
10.	Experiment with the report by selecting new Product, Time, and Channel members. In each case, the Geography exception condition eevaluates, and measures update appropriately.
11.	When you are done with the report, rename the worksheet and save the workbook. A. Right-click on “Sheet 2” and select Rename from the menu. B. Enter Country Revenue Share. C. Click Enter. D. Select File > Save from the menu bar.

Back to Topic List

Summary

In this lesson, you learned how to:

	Use the Spreadsheet Add-In to query and report on Oracle OLAP data within Excel.
	Use saved selections and calculated members that were previously saved in the Discoverer catalog.
	Use the floating Query Editor to perform OLAP analysis.
	Add an Excel chart to an OLAP report.

Back to Topic List

Related Information

To learn more about Oracle Business Intelligence features of the AS/DS 10G R2 PATCH SET 2 (10.1.2.2) release, refer to additional OBEs in the same focus area.

Back to Topic List

Place the cursor over this icon to hide all screenshots.