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.

Time to Complete

Approximately 30 minutes.

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Identify the Global Repository

Query the OLAP Data
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 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.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

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.

Back to Topic List

Identify the Global Repository

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

Back to Topic List

Query the OLAP Data

To access the Global repository as a Subject Area in BI Answers and create an analytic report, perform the following steps:
Open the Global Subject Area
Create a Product Analysis Report

Back to Topic List

Open the Global Subject Area

1.

Using the Start menu, select Programs > Oracle Business Intelligence > Presentation Services.

Result: The Oracle Business Intelligence login screen appears in your browser:

 

2.

Enter global as the User ID, and leave the Password box empty.

Click Log In.

Result: Oracle Presentation Server - Dashboards appears in the browser.
3.

In the Presentation Server window, click the Answers link.

Result: Oracle BI Answers appears in the browser.

 
4.

In the Subject Areas box, click the Global link, shown here:

Result: The Global presentation catalog is loaded, and the Global subject area appears in the Answers selection pane.

 

Back to Topic

Create a Product Analysis Report

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.

 

Back to Topic

Summary

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.

Back to Topic List

Related Information

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)

Back to Topic List

Place the cursor over this icon to hide all screenshots.