Lesson 3: Querying OLAP Data Using Oracle BI Answers
Purpose
This tutorial covers querying OLAP data using Oracle BI Answers.
The OLAP data was prepared for end-user access by completing the previous two
lessons of this OBE suite.
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 the previous lessons, you prepared an OLAP Analytic Workspace
for access by Oracle BI Enterprise Edition. Then, using the Oracle BI Administration
tool, you created a repository that contains the appropriate metadata to describe
the OLAP data.
In this lesson, you will use the repository that you previously
created to query the OLAP data. Using BI Answers, you will create a report against
the OLAP data that provides product family sales analysis for all channels of
distribution in European countries.
Have successfully completed the following tutorials:
Lesson 1: Preparing an Analytic Workspace for Access by Oracle
BI EE 10g, and Lesson 2: Creating BI EE 10g
Metadata for the Analytic Workspace.
Before you can access the Global repository that you previously
created, you must identify it as the default repository in the NQSConfig.ini
file. To modify the confiig file appropriately, perform
the following steps:"
1.
Using an edit tool such as MS Notepad, open the
<drive>:\OracleBI\server\Config\NQSConfig.ini
file.
Locate the [ REPOSITORY ] section
of the config file, as shown here:
The sample paint.rpd repository, which is shipped
with the BI Enterprise Edition, is listed as the default repository.
2.
Modify the config file as follows:
A.
Make a copy of the repository statement.
B.
Comment out the paint.rpd
repository statement by adding a # (pound) symbol
at beginning of the line.
C.
Modify the copied repository statement
to specify Global.rpd as the default repository.
When you are done, the Repository section should look like this:
3.
Save the NQSConfig.ini file, and close the Editor.
4.
Stop, and then restart, the Oracle BI Server
service.
This step causes the BI Server to automatically read the Global repository
Using BI Answers, you query the OLAP data in the same way
you would any data source. Open the desired folder in the selection pane, and
click on the item that you want to view. The item will be displayed as a column
in the Criteria tab.
Query items may be orgainized for table, pivot (cross-tab),
and graphical views. Filters may be applied to any selected column, and various
formatting criteria may be applied.
When you are done, you will have a report that analyzes product
family sales, with a focus on European countries, for all channels of distribution,
like this:
Follow these steps to create and modify a query against the
OLAP data:
1.
Using the Answers selection pane, perform the following:
A.
Drill on Customer and click Region
to add Customer [Region] to the Criteria tab.
B.
Then, drill on Measures and select
SALES to add Measures [SALES] to the Criteria tab.
The resulting display should look like this:
2.
Click the Results tab (or the Display Results button).
Sales for the three Customer regions appears in the display.
Note: the Sales values are returned for the "Top" level in
the other three dimension, as follows: Channel = "Total Channel";
Product = "Total Product"; Time = "All Years"
3.
In the Results tab, drill on Europe. Sales data
for the five European countries are returned.
4.
In the selection pane, drill on Time and select
Year. The display should now look something like this:
5.
Now, add the following criteria to the query and format the display
as a pivot table:
A.
In the Results tab, click the Pivot Table icon
in the Add View toolbar:
Result: Pivot Table controls appear above the data display.
B.
Drag Time
and Customer to the Pages axis, like this:
Result: The Display Results section updates to refect the pivot
table layout.
C.
In the selection pane, drill Product
and select Family. Product [Family] is added to
the Rows axis of the pivot table.
D.
In the selection pane, drill
Channel and select Channel.
Channel [Channel] is added to the Rows axis of the pivot table,
next to Product.
Note: the Display Results section updates to each time a change
is made to the pivot table layout.
E.
Drag Channel
to Columns axis, above the Measure Lables, like this:
F.
Finally, in the selection pane,
click the SALES_CHG_PP (Sales Change from Prior
Period) measure. The measure appears in the piviot table layout
as shown here:
When you are done, the display results should look like this:
Note: There is no data in the system for the Prior Period (in this
case, 1997). In addition the Internet channel of distribution was added
in the year 2000.
In the report, select 2001 - Europe - United Kingdom
from the Pages drop-down, like this:
The data updates to reflect the new selection.
6.
Apply a filter to the Time dimension to select the years starting
with 2001, like this:
A.
In the Criteria tab, click the Filter icon for
Time.
B.
In the Filter window, create the
following filter for Year, and then click OK.
C.
Click the Results
tab to view the change. The Pages list now only includes the years
2001 - 2004.
7.
Select the Chart Pivoted Results option to add a
graph to the display. Then, select the following options for the chart:
Result: aA3D Pie graph is displayed below the crosstab, showing the
contriibution of sales for each product family in the report.
8.
In the report, select 2004 - Europe - United Kingdom
from the Pages drop-down.
The crosstab and graph automatically update to reflect changes to report
selections.
In previous lessons, you created a repository for your
OLAP (Global) data. In this lesson, you accessed the repository and created
an analytic report against the OLAP data using Oracle BI Answers.
In this lesson, you've learned how to:
Modify the NQSConfig.ini
file to identify the required repository.
Launch the Presentaton
Server and open Oracle BI Answers.
Query the OLAP data
in Global repository and create an analytic report.
To learn more about Oracle
Business Intelligence Enterprise Edition, refer to additional OBEs on
the OTN Web site.
From this page, select Business Intelligence Start >
Oracle BI Enterprise Edition (EE-10.1.3.2)
Bookmark
