Creating a Product Category Analysis Report - Lesson 1
Lesson 1: Creating a Product Category Analysis Report
Purpose
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.
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.
Overview
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.
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.
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.
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:
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:
Clicking Next will allow you to specify a layout for the crosstab,
and then select members for each dimension in the query.
Clicking Finish will exit the wizard
and create the crosstab using a default layout and default dimension
member selections.
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.
Specify Dimension
Member Selections
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:
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:
Dimension
Selections
Product
Electronics, Hardware, Peripherals and Accessories, Photo, Software/Other
Time
2001
Geography
World Total, Americas, Asia, Europe,
Oceania
After you replace each of the dimension selections, the resulting crosstab
looks like this:
Create
a Saved Selection
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.
Modify the Crosstab Layout
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:
Products appear as rows on the left of the report.
The single Time dimension (2001) appears
above the measures, which are the columns.
Both Geography and Channel should be
Page Items.
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.
Create Calculations
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.
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.
Create
a % Margin Calculation
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.
View and
Modify the Graph
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.
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.
Summary
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.
Bookmark
