This lesson teaches you how to use Discoverer Plus OLAP to create a sophisticated, interactive business intelligence report that uses Oracle OLAP data. Discoverer Plus OLAP wizards and the Discoverer Plus OLAP navigator make the creation of sophisticated OLAP reports quick and easy.
While learning to use the Discoverer Plus OLAP interface, you will create a product category anaylsis report.
|
Overview | |
|
Prerequisites | |
|
Create a Connection, Workbook, and Worksheet | |
|
||
|
Modify the Crosstab Layout | |
|
Create Calculations | |
|
View and Modify the Graph | |
|
Use the Report | |
|
Summary | |
1 hour
Move your mouse over this icon to show all screenshots.
You can also move your mouse over each individual icon to see only the screenshot
associated with it.
In this lesson, you will create a report that enables product category analysis.
Reports created in Discoverer Plus OLAP can be then be viewed by executive managers and business analysts alike, using either Discoverer Viewer or Discoverer Plus OLAP.
Lesson Objectives
The specific goal of this lesson is to create a report that makes it easy to compare sales of different product categories, showing sales performance for each product category in 2001 as compared to the previous year. When complete, the report will be titled: Product Category Performance 2001.
Questions that executives want to answer using this report are:
|
Which product categories that contribute significantly to the total sales have unacceptable margins? | |
|
Which product categories are not performing as compared to last year in revenue and profit? Particularly, which products have margins that are too low, and in which geographic regions? | |
|
How do product sales compare across categories? How do sales and margins for the different categories compare with last year? I want to see this graphically so I can spot problems quickly. |
|
Data Model
For information on the data model used in the Discoverer Plus OLAP OBE lessons, please see the Data Model page.
High Level Objectives
The general business requirements of the Discoverer Plus OLAP OBE lessons are described in the Business Requirements page.
In order for this lesson to work successfully, you will need to have performed the following:
| 1. | Install Oracle Discoverer by following the instructions in the Oracle Business Intelligence Installation Guide.
|
| 2. | Oracle Discoverer provides a sample workbook to help you learn how to use the product. For OLAP data access, the sample workbook uses the Oracle Common Schema analytic workspace as its sample data set. The lessons in the Discoverer Plus OLAP OBE focus area use the same sample data set and some workbook objects that are part of the Discoverer Sample. Therefore, the Discoverer sample must be installed to perform these OBE lessons. For instructions on installing the Discoverer sample workbook, please see Installing the Discoverer Sample.
|
In order to access OLAP data using Discoverer Plus OLAP, you must create three primary objects:
|
A Connection: You must supply the appropriate authentication information to connect to the OLAP data source. |
|
|
A Workbook: The Discoverer container that is used to organize worksheets. | |
|
A Worksheet: The Discoverer object that contains an OLAP report. Every Discoverer OLAP worksheet contains a Crosstab and a Graph. You may choose to view either the crosstab, the graph, or both. |
|
If both the crosstab and graph are visible, the following behavior is evident: |
||
| - Query | The crosstab and graph in a worksheet share the same query. They will both update when the query is modified in either the crosstab or the graph. | |
| - Layout | Optionally, a crosstab and graph may also have a linked layout. In this case, both update when the layout of one object is modified. You can un-link the layout of the crosstab and graph. | |
Complete these subtopics to create the Discoverer Plus OLAP report:
|
Create a Connection | |
|
Create a Workbook and Worksheet | |
To create a connection for Discoverer Plus OLAP, you perform the following steps:
| 1. |
From the Oracle Discoverer main window, select File > Connect. The Connect dialog box is displayed.
|
| 2. | Enter the appropriate connection information. The User Name and Password are scott / tiger.
Then, click Connect. The Workbook Wizard is automatically launched. Please to go the next subtopic.
|
Create a Workbook and a Worksheet
In Discoverer Plus OLAP, your analytic reports are stored as Worksheets. Worksheets are organized into containers called Workbooks. When you connect to an Oracle OLAP data source, the Workbook wizard is automatically launched. The Workbook wizard lets you create a new workbook or open an existing workbook.
Follow these steps to create a new workbook and a worksheet.
| 1. | In the Workbook wizard, select the Create a new workbook option. Within this option, deselect everything except Page Items and Crosstab.
Click Next to continue.
|
||||||
| 2. | In the Available list, open the Electronics – KPIs folder and select the Sales Revenue measure. Use the Add Selected Items button (">") to move Sales Revenue from the Available to the Selected list.
|
||||||
| 3. | In the Selected list, select Promotion and then click Remove Selected Items ("<") remove it from the list.
|
||||||
| 4. | At this point in the wizard, you have two choices:
You will modify the layout and specify dimension member selections shortly, using the Discoverer OLAP navigator. Therefore, click Finish to exit the wizard. In the right pane of the Discoverer Plus OLAP window, you will see a worksheet that displays a crosstab.
|
||||||
You can select members for each dimension in a query in the Worksheet wizard, or you can use the Discoverer Plus OLAP navigator to specify dimension member selections. In this topic, you will use the navigator to modify the dimension selections for each dimension in your report.
The OLAP navigator contains two tabs: Members and Saved Selections.
|
You use the Members tab to select new dimension members for your report. | |
|
You use the Saved Selections tab to choose a previously saved set of dimension members. The definitions of OLAP objects such as saved selections are stored in the Discoverer Catalog. The catalog is defined in an Oracle database, which allows analyses to be shared within a user community. | |
You will learn how to use the OLAP navigator in the following subtopics:
|
Modify Dimension Selections | |
|
Create a Saved Selection | |
The Members tab of the OLAP navigator is used to modify the members that are used for each dimension in a report. In the Members tab, you can view a list of all members for a chosen dimension or a subset of dimension members at desired levels within the dimension hierarchy.
After you select the members for a particular dimension, you can either:
|
Add the selected dimension members to the report. | |
|
Replace the current list of dimension members in the report with your selection. | |
In addition, there are three different ways to incorporate your dimension selections from the Members tab into the report. You can:
|
Use the Add or Replace tools on the OLAP navigator toolbar. | |
|
Right-click and choose Add or Replace from the pop-up menu. | |
|
Drag them onto the report. This action will add them to your current selection. | |
To modify the dimension member selections using the OLAP navigator, follow these steps:
| 1. | In the Members tab of the OLAP navigator, choose Channel from the Dimension drop-down list. If necessary, click on the drill-down icon ("+") next to Channel total to display the three channels of distribution.
|
||||||||||||
| 2. | Multiselect Channel total, Direct, Indirect, and Others. Right-click on the selection and choose Replace from the menu.
After you replace the Channel dimension selection, the resulting crosstab looks like this:
|
||||||||||||
| 3 . | Use the same technique to Replace the current selections for each of the remaining dimension in the report as follows:
After you replace each of the dimension selections, the resulting crosstab looks like this:
|
||||||||||||
A Saved Selection is an object that specifies a set of members for a dimension in a query. These objects are stored in the Discoverer Catalog and can be reused in other queries.
Here, you will save the current selection for the Geography dimension. You will use the saved selection in the creation of other reports in subsequent OBE lessons.
To create a saved selection for the Geography dimension, follow these steps:
| 1. | From the main menu, select View > Query Steps Pane. The Query Steps Pane is displayed at the bottom of the navigator.
|
| 2. | Ensure that the Dimension field in the navigator is set to Geography. Then, in the Query Steps Pane, click the Save tool.
The Save Selection As dialog box is displayed.
|
| 3. | The SCOTT user folder contains a number of saved selections that were copied to the Discoverer Catalog by the installation of the Discoverer Sample (for more information, see the Prerequisites section).
Here, save this new saved selection to the same folder. Enter My Geographic Regions as the name. Accept the default Save Steps option and click OK to save your selection.
Notes: Saved steps: The "Save steps" option creates an object that records the steps that result in the dimension selection. This object can be thought of as a dynamic saved selection. For example, if you create a saved selection that contains a condition, you should choose this option. Each time the query is run, this kind of saved selection is re-evaluated using the current state of the dimension. Saved members: The "Save members" option creates an object that records a static list of values that are specified for the dimension when the saved selection is created. Therefore, this object contains a list of dimension members that never change.
|
To change the layout, you can manipulate the crosstab directly by clicking dimension tile grabbers and dragging them to different edges of the crosstab.
Alternatively, from the main menu, you can choose Edit > Crosstab Layout and then use the dialog box to rearrange the dimensions.
Perform the following to modify the crosstab layout:
| 1. | Using drag-and-drop, change the layout of the crosstab so that:
The crosstab layout should like this:
Hint: Drag and drop the dimension tiles to the appropriate positions. If you drop a dimension tile directly over another tile, then you will swap their locations. If you drop a tile next to another, then you will place it at the side of the other dimension.
|
|||||||||
In this topic, you will create three new calculations that will meet some of the analytic requirements of this report and reports to follow. The definitions of OLAP objects such as calculations are stored in the Discoverer Catalog.
Use the Discoverer OLAP Calculation Wizard to create calculations. You perform the following steps to create a calculation in the wizard:
| 1 | Name your calculation and select the calculation type. | |
| 2 | Select items and enter values to define your calculation. | |
| 3 | Select the location where you want to save your calculation. | |
Calculations can be used like any measure stored in the data model. For example, you can use them to display calculated data in graphs and crosstabs.
In the following subtopics, you will:
|
Create a Profit Calculation | |
|
Create a % Margin Calculation | |
|
Create a Sales % Change Year Ago Calculation | |
Here, you create a calculation named Profit that uses the Sales Revenue and Sales Cost measures.
To create the Profit calculation, follow these steps:
| 1. | In the Members tab of the OLAP navigator, select Measures from the Dimension dropdown list. Then, click the New Calculation tool on the navigator toolbar as shown here:
This starts the Calculation Wizard.
|
| 2. | In Step 1 of the wizard, name the calculation Profit. Here, you also specify the calculation type. For Calculation type, open the Basic Arithmetic folder and select Subtraction.
Click Next to continue.
|
| 3. | In Step 2, select Sales Revenue in the Value box, and Sales Cost in the Minus box. If the measure you want is not listed in the drop-down box, select More to display a list of all available measures. In the Select Measure dialog box, open the Electronics-KPIs folder, select the appropriate measure from this folder. and click OK.
Click Next to continue.
|
| 4. | In Step 3, click Finish to save the new Calculation to the <root>/Users/SCOTT folder.
The calculation is added to the crosstab, and is also available in the Members tab of the navigator.
Note: The other Calculation object that appears in the SCOTT folder was copied to the Discoverer Catalog as part of the installation of the Discoverer Plus OLAP Sample. |
Now, you will create a second calculation that makes use of the Profit calculation. This time, however, invoke the calculation wizard from the Tools menu, rather than from the navigator.
To create the % Margin Calculation, follow these steps:
| 1. |
In the main menu, select Tools > Calculations. The Calculation wizard is lauched.
|
| 2. | In Step 1 of the wizard, name the calculation % Margin. For Calculation type, open the Basic Arithmetic folder and select Division. Click Next to continue.
|
| 3. | In Step 2 of the wizard, choose Profit in the Divide field, and choose Sales Revenue in the By field. Hint: If necessary, use the More option in the drop-down list to find Profit in the /Users/SCOTT folder, and Sales Revenue in the Electronics - KPIs folder.
|
| 4. |
Still in Step 2, click the Options button. In the Number Format dialog box, select Percent using 2 Decimal Places.
Click OK to save the format. Then, click Next in the wizard to continue.
|
| 5. | In Step 3, click Finish to save the new Calculation to the <root>/Users/SCOTT folder.
The calculation is added to the crosstab, and is also available in the Members tab of the navigator.
|
Create a Sales % Change Year Ago Calculation
The Sales % ChangeYear Ago calculation will be a time-based calculation that compares this year’s sales to sales in a previous year.
Once you have created this calculation, you will remove the Profit calculation from the crosstab, and then format the measure headings so that the text wraps in the measure heading cells.
Follow these steps:
| 1. | In the main menu, select Tools > Calculations. The Calculation wizard is lauched.
|
| 2. | In Step 1 of the wizard, name the calculation Sales % Change Year Ago. For Calculation type, open the Prior/Future Comparison folder and select Percent Difference from Prior Period. Click Next to continue.
|
| 3. | In Step 2 of the wizard, choose Sales Revenue in the Measure field. Choose Year Ago as the From option. Use the Options button to format the result as Percent with 2 decimal places.
Click Next to continue.
|
| 4. | In Step 3, click Finish to create the calculation and add it to the crosstab.
|
| 5. | In the crosstab, right-click on the Profit measure. Select Remove from the menu.
The crosstab now contains three measures: Sales Revenue, % Margin, and Sales % Change Year Ago.
|
| 6. | Format the Measure headings so that the measure title text wraps in the crosstab. To do this, click the bar next to the Measure headings as shown here:
Then, right-click on a measure and select Format Header from the menu. In the Header Format dialog, click the Font tab, and then select the Wrap Words in Cell option, as shown here:
Click OK. Now, when you resize the measure columns, the measure title text will wrap to fit the column size.
|
You have been working with a crosstab in your worksheet. However, worksheets automatically include both a crosstab and a graph, whether or not they are visible. In this step, you will make make the graph visible and modify the graph.
Graphs present complex query results in an interactive manner allowing you to to explore the data by drilling or pivoting. There are 70+ graph types, with special customizers for formatting various graph components (layout, legend, series, axes).
To view and modify the graph, follow these steps:
| 1. | From the main menu, choose View > Graph. Your view of the worksheet will now show both the crosstab and the graph.
|
| 2. | Right-click the graph and choose Change Graph Type from the menu.
|
| 3. | In the Edit Graph Type dialog, choose Scatter/Bubble. Then, in the Graph Subtypes pane, choose Bubble.
Click OK. The graph type is changed.
|
| 4. | Right-click the graph again and choose Edit Graph Layout from the menu. Change the layout to match the example below. Pay close attention to ensure that the correct measures are selected for the X-Axis, Y-Axis, and Size.
Click OK to incorporate the changes to the graph.
|
| 5. | Right-click the graph and choose Edit Graph from the menu. In the Edit Graph dialog, add a graph title on the Title and Footnote tab that reads: Bubble Size = Sales Revenue. In the X-Axis tab, enter % Change Year Ago as the title and change the Font Title size to 12. In the Y1-Axis tab, enter % Margin as the title and change the Font Title size to 12. Click OK to close the Edit Graph dialog. The graph should look similar to the following:
|
| 6. | Finally, right-click the graph and choose Position Graph > Above Crosstab. This changes the layout of the worksheet so that the graph is displayed above the crosstab.
|
Complete the Worksheet
To complete the worksheet, perform the following steps:
| 1. | From the main menu, choose View > Title to make the worksheet title area visible. Then, double-click the title area above the graph and enter Product Category Performance 2001 as the title.
|
| 2. | If you wish, you can use the text formatting tools to change the color, style, position, and size of the title text. When done formatting the title, click OK.
|
| 3. | From the main menu, choose Edit > Rename Worksheet. Specify Product Category Performance as the worksheet’s new name, and click OK.
|
| 4. | Choose File > Save. In the Save As dialog box, name the workbook as Corporate Profitability.
Click OK. The final worksheet should look like this:
|
The completed worksheet contains a common underlying query, which is shared by the graph and the crosstab.
Using the Crosstab
To analyze the data in the crosstab, you can:
|
Page through the dimensions in the report to view new data for those dimension members. | |
|
Drill into the hierarchical dimension members for any dimension on the row or column edges. | |
|
Use the tools in the navigator to change the dimension members that are included in your query. | |
When you perform any of these actions, you will notice the change affects both the graph and crosstab. This behavior is expected because the crosstab and graph views are linked to the same query.
Using the Bubble Graph
A bubble graph allows you to see, very easily, the relationships among three measures. The bubble graph in this worksheet can help you analyze four factors at the same time:
|
Each bubble represents a different product, colored to make each distinctive. | |
|
The size of each bubble reflects that product’s sales revenue. | |
|
The X-Axis position of each bubble represents the % change in sales revenue for that product, compared to last year. | |
|
The Y-Axis position of each bubble represents the % margin grossed for that product. | |
You use a bubble graph to see relationships among three measures. As in a scatter graph, the more the bubbles seem to form a diagonal line, the stronger the correlation between the X and Y measures. If the bubbles get bigger along that line, then the Z measure is also correlated.
For example, in the worksheet you created, the bubble graph indicates the following:
| A. | Electronics and Hardware have the lowest margins of the products, and they brought in about the same Sales Revenue in 2001, but Hardware revenue is up 30% over the previous year, while Electronics revenue is down. | |
| B. | Software has acceptable margin, but compared to last year, sales increases are very low. Again, sales are also small compared to other products. | |
In this lesson, you learned how to use Discoverer Plus OLAP to connect to an OLAP data source, and create a report that contains a linked crosstab and a graph. You learned how to modify the query by selecting new dimension members, and how to save a dimension selection. You also learned how to create and use OLAP calculations.
In the next lesson, you will use the knowledge from this lesson to create another report that meets some of the other business intelligence requirements of the Executive Reporting system.
Move your mouse over this icon to hide all screenshots