Creating a Ranking Report and Publishing the Workbook - Lesson 3
Lesson 3: Creating a Ranking Report and Publishing the
Workbook
Purpose
This lesson teaches you how to use Discoverer Plus OLAP to
create a report that identifies products that are performing poorly or well.
You will learn how to create ranking conditions that enable Top / Bottom analysis.
Also, you learn how to publish the Workbook that you have
developed in Lessons 1 - 3 to a shared folder in the Discoverer Catalog. When
a Discoverer Plus OLAP Workbook is made public in this fashion, other authenticated
Discoverer Catalog users can access the Workbook by using Discoverer Viewer
or Discoverer Plus OLAP.
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
Executive users want to be able to identify products
that are performing poorly or well. The primary indicator for determining product
performance is a comparison that shows the change in product sales this year,
compared to last year.
In this lesson, you will create a third worksheet that contains
a crosstab which enables clear identification of product performance. Unlike
the previous two lessons, this report created in this worksheet will display
only a crosstab.
The goal for this crosstab is to have as much product ranking
information available as is possible. This will help determine if any of the
poorly performing products should be discontinued. It will also show how much
the best and worst performing products are contributing to sales.
The primary questions that executives want to answer using
this report are:
What
are my top ten and bottom ten performing products based on sales from last
year?
How are these products performing for each
sales channel?
How are these products performing across
different geographic regions?
As a percent share, how much does each product
contribute to total product sales?
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.
Open the workbook you created in the previous
lesson by selecting File > Open. In the Open dialog
box, select the Corporate Profitability
workbook that you created in Lesson1, as shown below.
Click OK. The Workbook
is opened.
2.
From the main menu, select Edit >
Add Worksheet.
In the Worksheet
Wizard, select the Title and Page Items
options. Choose the Crosstab radio button and deselect
the Text Area option.
Click Next to continue.
3.
In the Available list, select Sales
Revenue from theElectronics – KPIs folder
and the Sales % Change Year Ago calculation from the
/Users/SCOTT folder.
Remove the Promotion dimension from the Selected list.
Do not close the wizard. Click Next to continue and
go to the next topic in this lesson.
Specify Layout, Dimension
Selections, and Ranking Conditions
In this topic, you will use the Worksheet wizard
to:
1.
Create
the layout for the crosstab.
2.
Specify the member selections for the Channel,
Geography, and Time dimensions.
3.
Create two ranking conditions for the Product
dimension.
In the previous two lessons, you have used the
OLAP Navigator and the Edit > Worksheet menu option to specify the layout
and dimension member selections for your reports. Now, you will use the Worksheet
Wizard to specify the layout and dimension selections for the crosstab.
The Worksheet wizard incorporates the same interface
that is accessible from the OLAP Navigator and the Edit > Worksheet menu
option.
To specify the layout, dimension selections,
and ranking conditions for the query, follow these steps:
1.
To complete this topic, you must first perform
the steps in the previous topic, Create a New Worksheet.
In Step 3 of the Worksheet Wizard, change the layout of the crosstab
so that:
Product members appear as rows on the left of the report.
The measures appear as column titles.
Geography,
Channel, and Time are Page Items.
The crosstab layout is shown here:
Click Next to continue.
2.
In Steps 4 and 5 of the Wizard, specify the selections
for the following dimensions:
Dimension:
Selection:
Channel
Use the Members tab to select: Channel total,
Direct, Indirect, Others.
Then, click Add Selected Items (">").
Click Next.
Geography
Use the Saved Selections tab. Open
the Users/SCOTT folder and and select My Geographic Regions.
Then, click Add Selected Items
(">"). The Wizard should look like
the image shown below:
Click Next.
3.
In Step 6 of the wizard, you will create two ranking conditions for the
Product dimension. The first will return the Top 10 products based on
Sales % Change Year Ago, and the other will return the Bottom 10 products
based on Sales % Change Year Ago. You will save each of these conditions
as a saved selection.
To create these Product ranking conditions, follow these steps:
A.
Select the Conditions
tab and open the Top/Bottom folder.
B.
Select the Top 10 based on
X condition template.
C.
Then click the Edit Step
icon (pencil ) that appears to the right of the text.
D.
The Edit Step dialog
is displayed. Change the step so that it specifies a Top
10 based on % Change Sales Year Ago, as
shown in the screenshot below.
Notes:
An OLAP ranking condition returns the appropriate dimension members
for the condition by using a specific combination of values for
the remaining, or qualifying, dimensions in the query.
The values for these dimensions are displayed in the For
box.
For example, as shown in the Edit Step dialog above, our ranking
condition would return the top 10 products for World total,
Promotion total, 1998, and Channel
total.
In an OLAP ranking condition, you can use the Qualify button
to select a different dimension member for each of the qualifying
dimensions in the query.
Even more, you can specify that the ranking condition should be
re-executed each time you select a new member from a qualifying
dimension that is in the Page Items region of the crosstab.
To specify this kind of dynamic qualification, go to the next step
in these instructions.
E.
In the Edit Step dialog, choose
Qualify to display the Qualify Measure dialog.
For this Product ranking condition, qualify the dimensions as follows:
Geography: Each Geography
Promotion: Promotion Total
Time: 2001
Channel: Each Channel
These selections enable re-execution of the Product ranking condition
when you select a new Geography or Channel dimension member in the
crosstab.
Click OK in the Qualify Measure dialog box to
save the qualifications.
F.
Then, click OK
in the Edit Step dialog box to complete the ranking condition step.
G.
Add this completed condition to the
Selected Steps tab by clicking the Add Selected Items
button (">").
4.
Still in Step 6 of the Worksheet Wizard, create the first
Saved Selection.
Click Save at the bottom of the Selected region.
In the Save As dialog box, enter Top 10 Products based on Sales
% Change as the name.
Click OK to store the Saved Selection in the SCOTT folder.
5.
Still in Step 6 of the Worksheet Wizard, create the second
Saved Selection.
Click the selected condition in the Steps tab. Then, click on the Top
hyperlink in the condition step and change it to Bottom.
Once again, click Save and name this saved selection
Bottom 10 Products based on Sales % Change.
Click OK to store the Saved Selection.
6.
Finally, while still in Step 6 of the Worksheet Wizard,
use the "Top 10" Saved Selection as default ranking condition
to apply to the query.
First, click the Remove All button ("<<")
to remove the condition step from the Selected region.
Then, choose the Saved Selections tab in the Available
region. Open the Users/SCOTT folder and choose the Top 10 Products
based on Sales % Change selection.
Click the Add Selected Items button (">").
Click Next to continue in the Worksheet Wizard.
7.
In Step 7 of the wizard, select the Time dimension member
2001 from the Members tab of the Available list. Click
the Add Selected Items button (">").
Click Finish to execute the query and exit the Worksheet
Wizard. The Crosstab should look like this:
Create a % Share
Calculation
In this topic, you will create a calculation object named
% Share that is based on Product and shows each product’s
contribution as a proportion of total Product Sales.
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 pop-up menu. In the Header Format dialog, click the Font
tab, and then select the Wrap Words in Cell option, as
shown here:
Click OK to apply the formatting. Then, resize the columns
to resemble the following:
2.
Create a stoplight format for the % Share measure.
A.
Click the New
Stoplight Format tool on the toolbar.
B.
In the New Stoplight Format dialog
box, select the options and enter the values as are displayed in
the following screenshot:
Note: The Unacceptable threshold is 0.005
(.50 %), and the Desirable threshold is 0.02 (2.0
%)
Click OK to apply the format.
C.
The stoplight Format is applied
to the % Share measure, and is automatically updated when new dimension
members are selected.
3.
Double-click the title area of the worksheet. Change the
text for the title to Product Performance Compared to Last Year.
You may also want to modify the title font, alignment, size, and color.
When you are done formatting the title, click OK.
4.
Right-click the worksheet tab that contains the worksheet
name and choose Rename Worksheet. Change the name to
Product Indicators.
5.
From the File menu, choose Save.
The worksheet should look like the one that is shown in the following
screenshot:
6.
In the two Product ranking conditions, you qualifiied the
Geography and Channel dimensions to any value. Therefore, when you select
a new Geography or Channel dimension member from the Page Item tile, the
product ranking list will be reevaluated, and the data -- including the
% Share stoplight format -- will be updated.
For example, if you select Americas from the Geography
tile, the crosstab will update as shown here (this query may take a moment
to run):
Experiment with other Geography and Channel dimension members.
7.
Now, use the "Bottom 10" selection you created
earlier.
In the OLAP Navigator, select Product from the Dimension
list. Then, click the Saved Selections tab and open the
/Users/SCOTT folder.
Right-click the Bottom 10 Products based on Sales % Change
saved selection, and choose Replace from the menu.
The new ranking query is executed.
The crosstab updates with a new product list and the associated data.
8.
You can experiment with other nonranking Product dimension
saved selections that were created for the Product dimension as part of
the OracleBI Discoverer Plus OLAP Workbook Sample installation.
Note: The other two Product ranking conditions ("Top
5 .." .and "Bottom 5...") were created for a different
worksheet that is stored in the Discoverer Plus OLAP Sample workbook named
Scott's Sales Briefing. That worksheet is defined with different measures,
and therefore, those two ranking conditions should not be used in this
worksheet.
When you are done experimenting with this ranking report, replace the
last Product selection with your Top 10 Products based on Sales
% Change saved selection, and then save the Worksheet.
9.
Close the Corporate Profitability workbook by selecting
File > Close from the Discoverer
main menu.
Publish the
Workbook for Public Access
As first discussed in the Prerequisites
section of Lesson 1, the Discoverer Catalog is the repository for Discoverer
Plus OLAP Workbooks, and the analytic objects that are referenced by those Workbooks.
In this suite of Discoverer Plus OLAP OBE lessons, you have
created three worksheets that contain crosstabs and graphs. As you have developed
these worksheets, you have created a number of analytic objects, including Calculations
and Saved Selections, that support these worksheets. All of these objects are
part of the Corporate Profitability Workbook, and they are stored in the <root>/Users/SCOTT
folder in the Discoverer Catalog.
Complete these subtopics to
publish the Corporate Profitability Workbook and its supporting analytic objects
so that other Discoverer users can access the Workbook.
The Discoverer Catalog contains two main branches of subfolders:
Users and Shared.
Users.
All Discoverer Catalog users have a private folder that is added
to the <root>/Users/ branch of the Catalog. This private folder
is designed as a storage location for the objects that a particular Discoverer
user creates. By default, when an object is stored in a private folder,
only the user who created the object can see it.
This branch of the Discoverer Catalog has the following structure:
<root>/Users/USERNAME
where USERNAME is the
name of a particular Discoverer user.
Note: The folder <root>/Users/SCOTT
is the location where all of the objects that you have created in these
lessons are stored.
Shared. All
Discoverer Catalog users also have a public folder that is added
to the <root>/Shared/ branch of the Catalog. A public folder is
designed as a storage location for worbooks and other analytic objects
that are made accessible to other Discoverer Catalog users.
Discoverer users can access shared workbooks by
using Discoverer Viewer or Discoverer Plus OLAP.
A folder added to this branch of the Discoverer Catalog has the following
structure:
<root>/Shared/USERNAME
where USERNAME is the
name of a particular Discoverer user.
Publish the Workbook to a
Shared Folder
In this subtopic, you will perform the following tasks to
publish the Corporate Profitability workbook for
public access:
Delete an old Workbook and supporting
analytic objects in the <root>/Shared/SCOTT folder.
Copy the updated Workbook
objects from the <root>/Users/SCOTT folder to the <root>/Shared/SCOTT
folder.
Use the following steps to publish
the Workbook and its supporting objects:
1.
In the main menu, select Tools >
Manage Catalog. The Manage Catalog window is opened, and the
contents of the <root>/Users/SCOTT folder are displayed.
2.
Now, navigate to the <root>/Shared/SCOTT
folder.
Notice that there is a workbook and seven analytic objects already in
this folder. These Catalog objects were installed in the Discoverer Catalog
as part of the OracleBI Discoverer Plus OLAP Workbook Sample installation.
They are copies of the workbook and analytic objects that you have created
in these three Discoverer Plus OLAP OBE lessons.
You will update this Shared folder with the workbook and analytic objects
that you have created. First, delete the older objects in the <root>/Shared/SCOTT
folder. Then, you will copy the objects from your private folder into
the shared folder.
Select all of the objects in the <root>/Shared/SCOTT
folder, and then click Delete. The folder is now empty.
3.
Navigate back to the <root>/Users/SCOTT
folder.
In the Manage Catalog window, select the following objects:
% Margin
% Share
Bottom 10 Products based on Sales %
Change
Corporate Profitability
My Geographic Regions
Profit
Sales % Change Year Ago
Top 10 Products based on Sales % Change
Then, click Copy.
In the Copy To dialog box, navigate to the <root>/Shared/SCOTT
folder.
Click OK to copy the selected objects to the shared
folder.
4.
In the Manage Catalog window, navigate to the <root>/Shared/SCOTT
folder to view the copied objects.
5.
Click the Up One Level tool to navigate
back to the <root>/Shared folder.
6.
Right-click on the SCOTT folder and select Properties
from the menu. The SCOTT Properties dialog box is displayed. Here, you
can set the type of access privileges to objects that are stored in the
folder.
7.
Open the Privileges tab. Then, click the
Privileges drop-down list for the D4OPUB
Role, as shown here:
Notes:
Every user that is added to the Discoverer Catalog is assigned the D4OPUB
role. This role provides general access to the Catalog.
By default, the D4OPUB role has "List" rights to Shared folders,
which means that all Discoverer Catatog users can see the objects that
are stored in Shared folders, but they cannot open and use those objects.
The D4OPUB role was changed to "Read" privileges on this folder
so that other Disocoverer users can see and open objects that are stored
in this folder.
Folder privilages include the following (each subesquent privilege includes
the previous rights):
List
User can see folder objects,
but cannot open them.
Read
User can open and use folder objects.
Add Folder
User can add folders under this folder.
Write
User can open, modify, save, and delete
objects in the folder.
Full Control
All privileges including rights to
delete the folder.
Note that SCOTT, the owner of this folder, has "Full Control"
privileges to this folder. The Catalog schema owner, D4OSYS, also has
Full Control privilages.
You can use the Add and Remove buttons
to add and remove specified users to, or from, the folder. When a user
is directly added to a folder, then you can select the kind of access
rights that you want for that particular user.
With the current privileges established for the <root>/Shared/SCOTT
folder, all Discoverer Catalog users can open and use the workbooks and
objects stored in this folder, but they cannot save any objects back to
this folder. (They could, however, save objects to a folder for which
they have Write privileges, such as their own private folder.)
8.
Click Cancel to close the Scott Properties
dialog box.
Then, click Close to close the Manage Catalog window.
Test Access to the Shared
Workbook
Now, you will disconnect, and reconnect as another user. Then
you will open the Shared workbook that you have just published.
Follow these steps to test access to the shared workbook:
1.
From the main File menu, choose Disconnect.
Then, select File > Connect.
In the Connect dialog box, use the following Username and Password combination:
bi_user / bi_user, and then click Connect.
2.
In the Workbook Wizard, click Cancel.
3.
From the main menu, select File >
Open.
In the Open dialog box, navigate to the <root>/Shared/SCOTT
folder and select the Corporate Profitabiilty workbook.
Click OK to open the workbook.
All of the functionality of the workbook is the same as the private workbook
created by scott.
4.
In the main menu, select File > Close.
5.
To exit Discoverer, select File > Exit.
Summary
In this lesson, you created a third worksheet in the Corporate
Profitability workbook that enables product ranking analysis using Sales, Sales
% Change Year Ago, and % Share measures.
While creating this report, you learned how to:
Use the
Worksheet wizard to specify the layout and dimensions selections for a report.
Create qualified ranking
conditions for the Product dimension.
Use multiple saved selections to facilitate
more sophisticated analysis of the data.
Add a stoplight format to the measure data
to enhance analytic clarity.
In addition, you learned how to the publish the Workbook that
you have developed in Lessons 1 - 3 to a shared folder in the Discoverer Catalog.
Bookmark
