THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY.

COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Appendix A
Practices and Solutions

Oracle University and Oradist use only

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practices Overview

The goal of the course practices is to progressively build an OLAP data model to support
the following requirements:
• Summary management for existing SQL table queries by designing OLAP cubes
and enabling query rewrite against the cubes
• Enhanced analytic content, including a wide range of calculated measures that
enable easy end-user access to rich analytic information
• Easy access to OLAP data by using simple SQL:
– Creating and executing OLAP queries in SQL Developer
– Using OLAP queries in Oracle Application Express (APEX)
– Using OLAP queries in Oracle BI Enterprise Edition (BI EE)
• Cube security

Oracle University and Oradist use only

• Efficient cube design for performance and scalability

Oracle Database 11g: OLAP Essentials A - 2

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practices for Lesson 3

In the practices for this lesson, you start Analytic Workspace Manager (AWM), create a
new AW, and build an OLAP cube. You will perform the following tasks:
• Create a database connection for your OLAP environment and log in.
• Create an Analytic Workspace.
• Create Dimensions for your OLAP data model, including:
– Levels
– Hierarchies
– Attributes
– Dimension mappings
• Load dimension data.

Oracle University and Oradist use only

• View dimension data using:
– OLAP Data Viewer
– Dimension and hierarchy views
• Create a cube for Sales data.
– Add the Sales measure.
– Map the cube to relational sources.
• Load data for the cube.
• View the cube build logs:
– In AWM
– At the SQL level
• View cube data using:
– OLAP Data Viewer
– Cube views

Oracle Database 11g: OLAP Essentials A - 3

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 3-1a: Create the Analytic Workspace

In this practice section, you create an analytic workspace using an existing database
connection to the classroom schema. To perform this task:
1. Launch AWM from the desktop.
2. Select the Databases node.
3. Result: The database connection node named “Oracle11g” appears.

Oracle University and Oradist use only

4. Click the plus sign (+) next to the Oracle11g database node.

5. In the Connect to database dialog box, enter and perform the following:
a. Username: olaptrain
b. Password: oracle
c. Click OK.
Result: Two folders appear below the database connection – Schemas and Reports.
6. Select Schemas > OLAPTRAIN. Then, right-click Analytic Workspaces and select
Create Analytic Workspace from the menu.

7. In the Create Analytic Workspace dialog box, enter or select the following:
a. Name: SALESTRACK
b. Tablespace: <default>
c. Click Create.
Result: An AW named SALESTRACK appears in the navigator.

Oracle Database 11g: OLAP Essentials A - 4

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

objects.

Oracle Database 11g: OLAP Essentials A - 5

8. Select the SALESTRACK node to display the folders that organize OLAP data

Oracle University and Oradist use only

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 3-1b: Create the Dimensions

In this practice section, you create the four dimensions of what will organize your first
OLAP cube: Channel, Time, Product, and Geography. You also create the levels and a
hierarchy for each dimension. Finally, you will map the dimensions to their relational
sources, and then load the data for one of the dimensions.
1. Right-click the Dimensions node and select Create Dimension from the menu.
2. In the General tab of the Create Dimension window, enter or select the following:
a. Name: CHANNEL
b. Dimension Type: User Dimension
Note: Caps Lock is auto-enabled for object names. Label and Description fields
are auto-filled.

Oracle University and Oradist use only

3. In the Levels tab, create three levels for the Channel dimension:
• ALL_CHANNELS
• CLASS
• CHANNEL
Note: Enter each level name (as listed previously) in the Name field. As before, Label
and Description fields are auto-filled. A new level is automatically added when you
place the cursor in the Name field.
When you are done, the Levels tab should look like this:

4. In the Implementation Details tab of the Create Dimension dialog box, select Use
Keys from Data Source.

5. Click Create.

Oracle Database 11g: OLAP Essentials A - 6

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

6. Select Dimensions > CHANNEL. The following should appear in the navigator:

a.
b. Select the Levels node.

7. Create a Hierarchy for the Channel dimension by performing the following:

a. Right-click Hierarchies and select Create Hierarchy.
b. In the Create Hierarchy dialog box, enter SALES_CHANNEL as the name.

Oracle University and Oradist use only

c. Click the Add All tool . The General tabbed page should look like this:

d. Click Create.
8. Create a User Attribute for Channel Type by doing the following:
a. Rick-click the Attributes node below the Channel dimension, and select Create
Attribute.
b. In the Create Attribute dialog box, select or enter the following:
i) Name = CHANNEL_TYPE
ii) Attribute Type = User
iii) In the “Apply Attributes To” box:
– Click the plus sign next to the Channel dimension.
– Deselect the Channel dimension check box.
– Select the CHANNEL level check box (the lowest level).

Oracle Database 11g: OLAP Essentials A - 7

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Note: The Channel Type attribute only applies to the lowest level in the Sales
Channel hierarchy.
The dialog box should look like this:

Oracle University and Oradist use only

c. Click Create.
The Channel Type attribute is added to the dimension.

9. Map the Channel dimension to its relational sources by performing the following:
a. Click the Mappings node below the Channel dimension.
Result: The Schemas and Mapping panes appear to the right.

Oracle Database 11g: OLAP Essentials A - 8

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

b. In the Schemas pane, select OLAPTRAIN > Tables to display the source tables
for the OLAPTRAIN schema. Then, select CHANNELS to display its columns.
c. Ensure that Star Schema is selected for the Type of Dimension Table, and that
Table Mapping View is selected.
d. Drag the following source columns from the Schema pane to the Mapping pane
for the CHANNEL and CLASS levels. In addition, enter the following constants
for the ALL_CHANNELS level:
– Member = ‘ALL_CHANNELS’
– Description attributes = ‘All Channels’

Oracle University and Oradist use only

e. Click Apply.
Result: The Channel dimension is ready for data loading.
10. To create another dimension, right-click the Dimensions node and select Create
Dimension from the menu.
11. In the General tab of the Create Dimension dialog box, enter or select the following:
a. Name: TIME
b. Dimension Type: Time Dimension
12. Using the same techniques that you applied for the Channel dimension, add the
following levels to the Time dimension:
• ALL_YEARS
• CALENDAR_YEAR
• CALENDAR_QUARTER
• MONTH

Oracle Database 11g: OLAP Essentials A - 9

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

13. On the Implementation Details tabbed page, select Use Keys from Data Source.

14. Click Create.

Result: TIME appears under the Dimensions node.
15. Select TIME to display its associated nodes.

Oracle University and Oradist use only

16. Using the same techniques that you applied for the Channel dimension, create a
Hierarchy named CALENDAR for the Time dimension.
The completed Create Hierarchy dialog box should look like this:

17. Click Create.

18. Using the TIMES table in the OLAPTRAIN schema, map the Time dimension as
shown in the following image.

Oracle Database 11g: OLAP Essentials A - 10

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Note: For the ALL_YEARS level, use the following values in the Source Column
fields (including single quotes):
• Member: ‘ALL_YEARS’
• LONG DESCRIPTION: ‘All Years’
• SHORT DESCRIPTION: ‘All Years’
• END_DATE: TO_DATE(‘2008-DEC-31’, ‘YYYY-MON-DD’)
• TIME_SPAN: 3000

Oracle University and Oradist use only

19. Click Apply.
Result: The Time dimension is ready for data loading.
20. Create the Geography dimension from an XML template file.
a. In the navigator, right-click Dimensions and select Create Dimension From
Template.

b. In the Create Dimension From Template dialog box, navigate to the

home/oracle/common_schema/olaptrain/templates folder.

Oracle Database 11g: OLAP Essentials A - 11

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

c. Select GEOGRAPHY.XML and click Create.

Oracle University and Oradist use only

Result: The Geography dimension is created.
21. Under the Dimensions node in the navigator, select GEOGRAPHY, and then the
Mappings node.

Oracle Database 11g: OLAP Essentials A - 12

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

The completed mapping for the Product dimension appears.

Oracle University and Oradist use only

22. Create the Product dimension from an XML template file.
a. In the navigator, right-click Dimensions and select Create Dimension From
Template.
b. Select PRODUCT.XML and click Create.

23. Under the Dimensions node in the navigator, select PRODUCT and then the
Mappings node.

Oracle Database 11g: OLAP Essentials A - 13

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

The completed mapping for the Product dimension appears.

Oracle Database 11g: OLAP Essentials A - 14

Oracle University and Oradist use only
THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 3-1c: Load and View Dimension Data

In this practice section, you load data for the Product dimension and view the results.
1. Load the data for the Product dimension by performing the following steps:
a. In the navigator, right-click PRODUCT and select Maintain Dimension
PRODUCT from the menu.

Oracle University and Oradist use only

Result: The Maintenance Wizard appears.

b. Accept the defaults and click Finish.

Oracle Database 11g: OLAP Essentials A - 15

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Result: Data for the Product dimension is loaded and the AWM Build Log
appears when the data load is finished.

2. Click Close.
3. In the navigator, right-click PRODUCT and select View Data PRODUCT from the

Oracle University and Oradist use only

menu.

Result: The Product dimension appears in the AWM Data Viewer.

Oracle Database 11g: OLAP Essentials A - 16

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

a. You can review the hierarchical structure of a dimension using the Data Viewer.
For example, select All Products > Computers > Computer Printers and
Supplies. You should see the following display:

Oracle University and Oradist use only

b. Feel free to navigate through the dimension. After you are finished, click Close.
4. Next, view data from the relational Hierarchy View that was created for the Product
dimension.
Note: Remember that OLAP automatically creates a Dimension view, and at least
one Hierarchy view for each OLAP dimension that you create. These views are used
for SQL access to the OLAP data.
a. In the navigator, select Views node under the PRODUCT dimension, and then
select PRODUCT_STANDARD_VIEW.

Result: The structure of the Hierarchy view appears in the General tab, displayed
in the right pane.
b. Click the Data tab.

Oracle Database 11g: OLAP Essentials A - 17

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Result: The following appears in the right pane:

Oracle University and Oradist use only

c. Enter the following WHERE clause in the Filter box:
LEVEL_NAME=‘CATEGORY’
d. Press Enter.
Result: The following set of data should appear:

Note: Later in the course, you will learn how to use these columns in SQL queries
to return the desired OLAP data.
5. Collapse the PRODUCT dimension in the navigator.

Oracle Database 11g: OLAP Essentials A - 18

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 3-2a: Create the Sales Cube

In the practices in this section, you create a cube and add Sales and Quantity measures to
it. You map the data, and then enable the cube for query rewrite. Finally, you load the
data and view the results.
1. Right-click the Cubes node and select Create Cube from the menu.
2. In the General tab of the Create Cube dialog box, specify the following:
a. Name: SALES_CUBE
b. Selected dimension order:
– CHANNEL
– TIME
– GEOGRAPHY
– PRODUCT

Oracle University and Oradist use only

Result: The Create Cube dialog box should look like this:

3. Select the Aggregation tab. Then, in the Precompute subtab, specify a value of 30 for
Cost-based aggregation.

Oracle Database 11g: OLAP Essentials A - 19

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

4. Select the Storage tab. Select the Sparse option for all dimensions.

Oracle University and Oradist use only

5. Click Create.
6. In the navigator, select SALES_CUBE.
7. Right-click Measures and select Create Measure.
8. In the Create Measure dialog box, enter SALES as the name and click Create.
9. In the navigator, select Measures. You should see the following:

10. Using the same techniques described in steps 7 and 8, create a second measure named
QUANTITY.
11. Click the Mappings node under SALES_CUBE, and ensure that the Table Mapping
View tool (shown below) is selected.

12. Under the Schemas node, select OLAPTRAIN > Tables.

Oracle Database 11g: OLAP Essentials A - 20

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

13. Using the SALES_FACT, CHANNELS, TIMES, CUSTOMERS, and PRODUCTS

tables, map the SALES_CUBE as shown in the following table.
Note: When mapping the Join Condition:
• First, drag the foreign key column from the fact table to the Source Column field.
• Then, drag the primary key column from the dimension table to the Source
Column field. The equal sign (“=”) is automatically inserted after you drag the
second column into the Source Column field.

Oracle University and Oradist use only

14. Click Apply.

Oracle Database 11g: OLAP Essentials A - 21

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 3-2b: Load and View Data for the Sales Cube
In the practices in this section, you load the data to the Sales cube, and you view cube
data using both the AWM Data Viewer and the cube views.
1. In the navigator, right-click SALES_CUBE and select Maintain Cube
SALES_CUBE from the menu.

2. In the Maintenance Wizard, accept all defaults and click Finish to begin the load

Oracle University and Oradist use only

process.

Oracle Database 11g: OLAP Essentials A - 22

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

When the build and load process is complete, the AWM Build Log appears:

Oracle University and Oradist use only

3. After examining the Build Log, click Close.
4. Right-click the SALES measure and select View Data SALES from the menu.
Result: Sales data is displayed for a default set of dimension members.
5. In the Data Viewer, select All Years.

6. Click the Query Builder tool , under the File menu.

7. Click the Layout tab, and then perform the following:
a. Move Channel to the Page Items region.
b. Move Time underneath the Measure in the column axis

Oracle Database 11g: OLAP Essentials A - 23

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

c. Move Product to the row axis.

The layout tab should look like this:

Oracle University and Oradist use only

8. Click the Dimensions tab, and then do the following:
a. Select Geography from the Choose list.

b. Click the Remove All Items tool to clear the Selected list.
c. In the Members tab of the Available list, select All Regions and select North
America, Asia, and Europe.

d. Click the Add Selected Items tool to move the chosen dimension members
to the Selected list, like this:

e. Click OK to refresh the data with your new selections.

Oracle Database 11g: OLAP Essentials A - 24

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

9. In the Data Viewer, select All Products. Also, notice that you can select from a list of
members for the Geography dimension.

Oracle University and Oradist use only

10. When you are done experimenting with the report, close the Data Viewer.
11. Select the Views node under the SALES_CUBE.
12. Select SALES_CUBE_VIEW from the navigator, and click the Data tab in the right
pane.

Note: AWM provides a view of the relational cube view representation of the data from
within the administrative tool. Later, you learn how to query the OLAP data directly by
using SQL against cube views.
13. Collapse the SALES_CUBE node by clicking its minus sign.

Oracle Database 11g: OLAP Essentials A - 25

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practices for Lesson 4

In the practices for this lesson, you enable Query Rewrite and MV Refresh for the Sales
Cube, and then perform a build to create the Cube MVs. Then, you run some queries that
do not rewrite correctly to the Cube MVs. Using the mv_cube_advice package, you
determine why the queries are not rewriting. You then modify the queries so that they
rewrite to the cube MVs.

Practice 4-1: Enabling and Building Cube MVs

In this practice section, you enable Cube MVs in AWM and run a build.
1. In the AWM navigator, click SALES_CUBE.

Oracle University and Oradist use only

2. In the right pane, click the Materialized Views tab and select:
a. Enable Materialized View Refresh of the Cube
b. Enable Query Rewrite

c. Accept the default settings for all other options, and click Apply.
Result: The following information box appears:

3. When the processing is complete, go to the navigation pane and select Dimensions >
Geography.

Oracle Database 11g: OLAP Essentials A - 26

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

4. Select the Mappings node under Geography, and notice that OLAP has added a new
Unique Key attribute to each level in the dimension, and automapped the attributes:

Oracle University and Oradist use only

These Unique Key attributes are required by the Materialized View subsystem for the
query rewrite. Each level of each dimension hierarchy in the cube contains the correct
additional attributes.
5. In the navigator, right-click SALES_CUBE and select Maintain Cube
SALES_CUBE from the menu.
6. In the Maintenance Wizard, accept all defaults and click Finish to begin the load
process.
7. When the build completes, close the Build Log window.
8. Exit AWM.

Oracle Database 11g: OLAP Essentials A - 27

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 4-2: Running Summary Queries with and Without Query

Rewrite
In this practice section, you run a set of summary queries against the OLAPTRAIN
schema. First, you turn query rewrite off to see how the summary queries perform against
the fact table. Then, you turn on rewrite and run the queries again.
• Observe how the database automatically rewrites the summary queries to the
OLAP Cube MVs.
• Compare the performance of the SQL summary queries to the OLAP Cube MVs
queries.
1. Launch SQL Developer from the desktop.
2. In the Connections tab of the navigator, click the olaptrain connection node.
Result: A connection to the olaptrain schema is made, and schema object types

Oracle University and Oradist use only

appear in the navigator.
3. In the navigator, select the Materialized Views node.

The CB$ tables – one each for the cube and the associated dimension hierarchies –
were automatically created by the OLAP build process.
4. Close the olaptrain (local) tab by clicking the X.

Next, you will see how these Cube MVs may be used for rewrite when summary
queries are executed against the OLAPTRAIN fact and dimension tables.

Oracle Database 11g: OLAP Essentials A - 28

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

5. From the main menu, select File > Open.

a. Navigate to the /home/oracle/common_schema/olaptrain/labs folder.
b. Select the Prac4-2.sql file.

c. Click Open.

Oracle University and Oradist use only

6. Vertically resize the SQL statement pane so that it takes up at least half of the space
in the SQL Developer window.
7. At the top-right corner of the Enter SQL Statement pane, select olaptrain from the
list.

8. Scroll down until you reach the ALTER materialized VIEW statements. Click the
statement that disables query rewrite for cb$sales_cube.

Oracle Database 11g: OLAP Essentials A - 29

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

9. Press F9 or click the tool to execute the statement.

Result: Query rewrite to the Sales Cube MV is now disabled.
10. Place your cursor anywhere inside the first query, Quantity and Sales by Year and
Product Group, which is just below the statements that turn query rewrite on and off.
11. Press F6 to display the Explain Plan for the query.
Result: When run, the query joins the fact table to associated dimension tables and
performs a full table scan of the fact table in order to return data the requested data.

Oracle University and Oradist use only

12. Press F9 to execute the query.
Result: A report similar to the following example appears in the Results tab.

13. Record the time it took to run the query, up to two decimal places.

Oracle Database 11g: OLAP Essentials A - 30

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Note: The value appears at the top of the SQL pane, as shown in the following
screenshot. In the example, the query time would be rounded to 2.54 seconds. The
value you see will probably be different from the example, although it should be
close.

Query 1 performance: seconds

14. Click inside the second query and press F9 to execute the query, as the following
screenshot shows. Again, record your query time.

Oracle University and Oradist use only

Query 2 performance: seconds
15. Using the same technique as in steps 10-13, execute the third and fourth queries.
Record each of the query times.

Query 3 performance: seconds

Query 4 performance: seconds

Oracle Database 11g: OLAP Essentials A - 31

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

16. Scroll back up in the SQL file. Enable query rewrite to cb$sales_cube by clicking the
enable query rewrite statement, and then pressing F9.

17. To confirm that the queries will rewrite, click within the first query and press F6 to
display the Explain Plan, as shown in the following screenshot:

Oracle University and Oradist use only

18. Press F9 to execute the query. Record your performance to two decimal places.

Oracle Database 11g: OLAP Essentials A - 32

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Query 1 – Cube MV performance: seconds

19. Using the same technique as in step 18, execute the second through fourth queries.
Record each of the query times.

Query 2 – Cube MV performance: seconds

Query 3 – Cube MV performance: seconds

Query 4 – Cube MV performance: seconds

Note: Query times for a Cube MV rewrite commonly are 10 to 50 times faster than
summary queries against relational fact tables.
20. Close the Prac4_2.sql file.
21. Exit SQL Developer.

Oracle University and Oradist use only

Oracle Database 11g: OLAP Essentials A - 33

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practices for Lesson 5

In the practice for this lesson, you create a range of calculations, including:
• Calculated measures using the Calculation Builder
• Calculated measures using XML templates
• Custom calculations using expression syntax

Practice 5-1: Creating Calculations Using the Calculation Builder

In this practice section, you create several calculated measures using the AWM
Calculation Builder. These calculations include:
• Sales Year-To-Date (YTD)

Oracle University and Oradist use only

• Sales YTD Prior Year
• Sales YTD Prior Year Percent Change
• Sales Prior Year
Follow these steps to create the YTD calculations:
1. Open AWM, connect to the database using the id/password combination
olaptrain/oracle, and then open your analytic workspace by clicking the plus sign (+)
next to SALESTRACK.
2. In the AWM navigator, select Cubes > SALES_CUBE as the following screenshot
shows.

Oracle Database 11g: OLAP Essentials A - 34

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

3. Right-click the Calculated Measures node and select Create Calculated Measure
from the menu.
4. In the Create Calculated Measure dialog box, enter or select the following:
a. Name = SALES_YTD (the Name field is automatically all caps, and the Label
and Description fields are autofilled)
b. Calculation Type = Period to Date
c. In the Calculation inputs section, select the following:
i) First hyperlink = Ancestor At Level

Oracle University and Oradist use only

Result: A new hyperlink appears next to the Ancestor At Level hyperlink.
ii) Second hyperlink = TIME.CALENDAR_YEAR
Result: The Create Calculated Measure dialog box should now look like this:

d. Click Create.
5. Select the Calculated Measures node.
Result: The SALES_YTD calculation appears.

Oracle Database 11g: OLAP Essentials A - 35

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

6. Create a second YTD calculation:

a. Right-click Calculated Measures and select Create Calculated Measure from the
menu.
b. In the Create Calculated Measure dialog box, enter or select the following:
i) Name = SALES_YTD_PY
ii) All Label and Description boxes = Sales Ytd Pr Year
iii) Calculation Type = Parallel Period
iv) In the Calculation inputs section, click the SALES hyperlink.

Oracle University and Oradist use only

Result: The Select Measure dialog box appears.
v) Select SALES_YTD, and then click OK.

Result: The Calculation updates with the selected measure.

vi) Click the TIME.CALENDAR.ALL_YEARS hyperlink and select
TIME.CALENDAR. CALENDAR_YEAR from the list.

c. Click Create.

Oracle Database 11g: OLAP Essentials A - 36

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Result: The SALES_YTD_PY calculation appears below the Calculated Measures

node in the Navigator.
7. Create a third Year-to-Date calculation.
a. Right-click Calculated Measures and select Create Calculated Measure from the
menu.
b. In the Create Calculated Measure dialog box, enter or select the following:
i) Name = SALES_YTD_PY_PCT_CHG
ii) All Label and Description boxes = Sales Ytd Pr Yr Pct Chg
iii) Calculation Type = Percent Difference From Parallel Period
iv) In the Calculation inputs section, click the SALES hyperlink.
v) In the Select Measure window, select SALES_YTD and click OK.

Oracle University and Oradist use only

vi) Click the TIME.CALENDAR.ALL_YEARS hyperlink and select
TIME.CALENDAR. CALENDAR_YEAR from the list.
The calculation should now look like this:

vii) In the Expression field, add the following syntax to the beginning of the
expression: 100 *
Note: This additional syntax causes the calculation to display percentage figures
in whole numbers.
The expression should now look like this:

c. Click Create.

Oracle Database 11g: OLAP Essentials A - 37

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

The Sales Cube now contains the following measures:

Oracle Database 11g: OLAP Essentials A - 38

Oracle University and Oradist use only
THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 5-2: Creating Calculations Using XML Templates

In this practice section, you create a number of calculated measures using XML templates
and a few more using the Calculation Wizard, including the following:
From XML Templates Using the Calculation Wizard
• Sales Prior Year • Sales Share of Total – Channel
• Sales Prior Year Percent Change • Sales Rank By Product Level
• Sales Prior Period • Sales Moving Average – 3 Periods
• Sales Prior Period Percent Change
• Sales Share of Parent – Channel
• Sales Share of Total – Product
• Sales Share of Parent – Product
• Sales Rank By Product Parent
• Sales Share of Total – Channel

Oracle University and Oradist use only

• Sales Moving Total – 3 Periods
Use the following steps:
1. Right-click Calculated Measures and select Create Calculated Measure from
Template.
2. Navigate to the …olaptrain/templates/calcs directory and select SALES_PY.XML.

3. Click Create.
Result: The calculation appears in the navigator.
4. Select the new calculation.

Oracle Database 11g: OLAP Essentials A - 39

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Result: The calculation definition appears in the right pane.

Oracle University and Oradist use only

5. Repeat steps 1-3 to create three more calculations, using the following XML files:
SALES_PY_PCT_CHG.XML, SALES_PP.XML, and
SALES_PP_PCT_CHG.XML.
6. Select the Calculated Measures node in the navigator to display the list of calculated
measures in the cube.

7. Create a Share measure for the Channel dimension. You may either use the
Calculation Builder or an XML template.
Note: If using the Calculation Builder, follow steps 7a – 7b. If using an XML
template, select the SALES_SHARE_TOT_CHAN.XML file.
a. Right-click Calculated Measures and select Create Calculated Measure from the
menu.
b. In the Create Calculated Measure dialog box, enter or select the following:

Oracle Database 11g: OLAP Essentials A - 40

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

i) Name = SALES_SHARE_TOT_CHAN
ii) Leave all Label and Description boxes with the autofilled text.
iii) Calculation Type = Share
iv) Click the dimension hyperlink (TIME) and select CHANNEL from the list.
v) Select the Multiply result by 100 option.
The calculation should look like this:

Oracle University and Oradist use only

Note: This calculation returns the share of sales for the currently selected
channel of distribution to the total for all Channels (top of hierarchy).
i) Click Create.
8. Repeat steps 1-3 to create three more calculations using the following XML files:
SALES_SHARE_PRNT_CHAN, SALES_SHARE_TOT_PROD, and
SALES_SHARE_ PRNT _PROD.
9. Create a Rank measure for the Product dimension. You may either use the
Calculation Builder or an XML template.
Note: If using the Calculation Builder, follow steps 9a – 9b. If using an XML
template, select the SALES_ RANK_PROD_LVL.XML file.
a. Right-click Calculated Measures and select Create Calculated Measure from the
menu.
b. In the Create Calculated Measure dialog box, enter or select the following:
i) Name = SALES_RANK_PROD_LVL
i) All Label and Description boxes = Sales Rank in Prod Lvl
ii) Calculation Type = Rank
iii) In the Calculation inputs section, click the dimension hyperlink (TIME) and
select PRODUCT from the list.

Oracle Database 11g: OLAP Essentials A - 41

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

The calculation should look like this:

Oracle University and Oradist use only

iv) Click Create.
10. Finally, repeat steps 1-3 to create two more calculations using the following XML
files: SALES_RANK_PROD_PRNT.XML and SALES_3P_MOV_AVG.XML.
When you are done, the following calculations should be part of the Sales Cube:

11. Feel free to examine any of the definitions for the calculations that you created from
XML template files.

Oracle Database 11g: OLAP Essentials A - 42

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 5-3: Viewing Calculated Measures

In this practice section, you use the AWM Data Viewer to see calculated measures.
• First, you will create a comparative time-series report on Product categories,
using the Prior Year, and Prior Year Percent Change measures.
• Second, you will create a Product analysis report using Rank and Share measures.
1. In the Navigator, right-click the SALES measure and select View Data Sales from
the menu. Sales data is displayed for a default set of dimension members.
2. Remove the Graph by clicking the down arrow of the Hide/Show tool. ( )

3. Click the Query Builder tool , under the File menu.

4. In the Items tab:

Oracle University and Oradist use only

a. Select Sales Pr Year and Sales Pr Year Pct Chg.

b. Click the Add Selected Items tool to move those two measures to the
Selected list, like this:

5. Click the Layout tab.

6. In the Layout tab, drag the appropriate dimension tiles to the correct axis so that the
layout looks like this:

7. Click the Dimensions tab.

8. In the Dimensions tab, perform the following:

Oracle Database 11g: OLAP Essentials A - 43

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

a. Select the Time dimension from the Choose list.

b. Click the Remove All Items tool to clear the Selected list.
c. In the Members tab of the Available list, select All Years > CY2007.
d. Select all four quarters in CY2007, like this:

Oracle University and Oradist use only

e. Click the Add Items tool to move the 2007 quarters to the Selected list, like this:

f. Select Product from the Choose list.

g. Click the Remove All Items tool and then select All Products in the Available
list. Select the three Product department members, like this:

h. Click the Add Items tool to move the department members to the Selected list.

Oracle Database 11g: OLAP Essentials A - 44

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

i. Click OK to view the data.

9. Select any of the members from the Product dimension header. The calculations are
instantaneously updated.

Oracle University and Oradist use only

10. Select any of the 2007 Quarter values to view the data at the month level.
Next, you modify the Data Viewer for a Product ranking and share report.
11. Click the Query Builder tool.
12. In the Items tab, perform the following:
a. In the Selected list, select Sales Pr Year and Sales Pr Year Pct Chg measures,
and click the Remove Selected Items tool .
b. In the Available list, select Sales Rank In Prod Prnt and Sales Share Prnt
Prod, and click the Add Selected Items tool.
Result: The rank and share measures are added to the Selected list.
13. In the Layout tab, swap the Product and Time dimensions, so that Product is in the
Row axis, and Time is in the Page Items axis.
14. Click OK to view the data.

The calculated measures show the rank of each Product dimension member within its
hierarchy parent. The share measure shows the percentage of sales returned by the
product member to its parent in the hierarchy.

Oracle Database 11g: OLAP Essentials A - 45

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

15. Select Computers.

The rank and share measures show the relative ranking and share contribution for
each of the Product division members in the Computers department.

Oracle University and Oradist use only

16. Select any Time member from the Page Items axis, and the calculations update
instantaneously.
17. Feel free to modify the report by selecting other dimension members. When you are
done, close the Measure Data Viewer.

Oracle Database 11g: OLAP Essentials A - 46

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 5-4: Creating and Using Custom Calculations

In this practice section, you create and use a number of custom calculations that
demonstrate the following:
• Expression syntax using QDRs
– Sales 2006
– To Go (Sales for 2006 – Sales YTD)
– PCT of 2006
• Conditional expression syntax
– How is Sales YTD (a CASE Statement example)
• OLAP DML code example
– Product Alert

Practice 5-4a: Create three custom calculations that illustrate the use of a QDR (qualified
data reference).

Oracle University and Oradist use only

1. In the navigator, right-click Calculated Measures and select Create Calculated
Measure from the menu.
2. In the Create Calculated Measure dialog box, enter or select the following:
a. Name = SALES_2006
d. Leave all Label and Description boxes as autofilled
e. Calculation Type = Expression
f. In the Expression box, enter SALES_CUBE.SALES[“TIME” = ‘CY2006’]

Note: The braces for the QDR are square “[“ and “].”
g. Click Create.
3. Open the Create Calculated Measure dialog box. Enter or select the following:

Oracle Database 11g: OLAP Essentials A - 47

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

a. Name = TO_GO
b. Leave all Label and Description boxes as autofilled
c. Calculation Type = Expression
d. In the Expression box, enter:
SALES_CUBE.SALES_2006 - SALES_CUBE.SALES_YTD
e. Click Create.
4. Open the Create Calculated Measure dialog box and enter or select the following:
a. Name =PCT_OF_2006 (leave all Label and Description boxes as autofilled.)
b. Calculation Type = Expression
c. In the Expression box, enter:

Oracle University and Oradist use only

100 * (SALES_CUBE.SALES_YTD / SALES_CUBE.SALES_2006)
d. Click Create.
The three Expression calculations are now added to the list of available calculated
measures:

Now, use these three calculations in the Data Viewer by performing the following:
5. In the Navigator, right-click the SALES measure and select View Data Sales from
the menu.
6. Remove the Graph by clicking the down arrow of the Hide/Show tool. ( )
7. Click the Query Builder tool.
8. In the Items tab, add the following measures (in the order specified) from the
Available list to the Selected list: Sales YTD, Sales 2006, To Go, and Pct of 2006.
9. Using the Layout tab, place Geography, Channel, and Product on the Page Items axis,
and Time on the Row axis.
10. In the Dimension tab, select the four quarters of CY2007, using the same techniques
described previously in this practice.
11. Click OK to view the data.

Oracle Database 11g: OLAP Essentials A - 48

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

The report dynamically compares several sales measures for a selected time period to
a different “benchmark” sales value for the year 2006.
Notice how the:
• “To Go” calculation shows the Sales revenue required to meet CY2006 Sales
• “Pct of 2006” measure shows the percent of YTD sales against the benchmark

Oracle University and Oradist use only

Sales_2006 figures

Practice 5-4b: Now, create a custom calculation that includes the use of conditional
expression syntax.
1. Open the Create Calculated Measure dialog box. Enter or select the following:
a. Name = HOW_IS_SALES_YTD
b. Leave all Label and Description boxes as autofilled
c. Calculation Type = Expression
d. In the Expression box, enter the following CASE statement:
CASE
WHEN SALES_CUBE.SALES_YTD_PY_PCT_CHG < 0 THEN ‘Needs Improvement’
WHEN SALES_CUBE.SALES_YTD_PY_PCT_CHG > 15 THEN ‘Outstanding’
ELSE ‘On track’
END
e. Click Create.
2. In the Navigator, right-click the SALES_YTD_PY_PCT_CHG measure and select
View Data SALES_YTD_PY_PCT_CHG from the menu.
3. Remove the Graph by clicking the down arrow of the Hide/Show tool.
4. Click the Query Builder tool.
5. In the Items tab, add How Is Sales Ytd to the Selected list.

Oracle Database 11g: OLAP Essentials A - 49

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

6. In the Layout tab, place Time above the measures in the Column axis, like this:

7. In the Dimensions tab, select the Time dimension from the Choose list, and then
select the four quarters of CY2007, using the same techniques described previously in

Oracle University and Oradist use only

this practice.
8. In the Dimensions tab, select the Product dimension from the Choose list. Then do
the following:
a. In the Available list, select All Products.
b. Select the three Product departments and click the Add Selected Items button.
The Selected list should look like this:

9. Click OK to view the data.

10. Select the Measure headings and select the Wrap Text tool . Then, resize the
column edges, like this:

Oracle Database 11g: OLAP Essentials A - 50

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

11. Finally, drill down on Cameras and Camcorders.

The report should look something like this: (Hint: Resize the Measure Data Viewer.)

Oracle University and Oradist use only

12. Close the Data Viewer.

Oracle Database 11g: OLAP Essentials A - 51

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 5-4c: Finally, create a custom calculation that calls an OLAP DML program.
In order to create the final custom calculation, you must first have access to the program
that it calls. Therefore, use the following instructions to import an OLAP DML program
named product_alert.dml:
1. Select Tools > OLAP Worksheet.
2. In the command window, enter the following command (and then press F5):
infile cubes/product_alert.dml
Result: The predefined OLAP DML program is imported into the AW.
3. Execute the following three commands in the command window:
compile product_alert
update

Oracle University and Oradist use only

commit
When done, the OLAP Worksheet should look like this:

4. Select File > Close to close the OLAP Worksheet window.

Note: This is exactly the same DML program that was covered in the lesson.
Now, create and use a calculation that calls the OLAP DML program.
5. Open the Create Calculated Measure dialog box. Enter or select the following
a. Name = PRODUCT_ALERT
b. Leave all Label and Description boxes as auto-filled.
c. Calculation Type = Expression
d. In the Expression box, enter the following syntax:
OLAP_DML_EXPRESSION(‘PRODUCT_ALERT’, VARCHAR2)
e. Click Create.

Oracle Database 11g: OLAP Essentials A - 52

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

6. In the navigator, right-click the SALES measure and select View Data SALES from
the menu.
7. Remove the Graph by clicking the down arrow of the Hide/Show tool.
8. Click the Query Builder tool.
9. In the Items tab, add Sales Pr Year Pct Chg and Product Alert to the Selected list.
10. In the Layout tab, create the following layout:

Oracle University and Oradist use only

11. In the Dimension tab, select the Time dimension, and then select CY2007 as the only
time dimension member. The Selected pane should look like this:

12. Click OK to view the data.

13. Select All Products.

Note:
An Alert is raised on Computers, even though it is by far the largest selling product
department, and also shows good growth (a good percent change from last year).
This means that there is a hidden sales problem somewhere in the Computers
department.

Oracle Database 11g: OLAP Essentials A - 53

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

14. Select Computers.

Oracle University and Oradist use only

Note:
As indicated by the Alert, at least one of the computer categories had a sales drop-off.
The computer furniture category has had an 8.19 percent drop-off as compared to the
previous year.
In addition, an Alert is raised on the Total Personal Computers category, indicating
further investigation is required.
• Of all of the Personal Computer product types that were alerted, how many
seemed obvious by only viewing the Sales Prior Year Percent Change measure?
Which one(s)?
• On which product members in the Personal Computer category has an alert been
raised?
15. After you have finished your investigations, close the Data Viewer.
16. Exit AWM.

Oracle Database 11g: OLAP Essentials A - 54

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practices for Lesson 6

In the practices for this lesson, you use SQL Developer to query OLAP cubes directly
using the OLAP cube views. You apply techniques that leverage the unique properties of
OLAP cubes, including:
• Level and Member filters
• Embedded Total features
• Hierarchical (“Family”) relationships
• Parent columns in order to “drill down”

Practice 6-1: Creating a Simple OLAP Cube Query

Write a query that returns total SALES for products at the DEPARTMENT level.

Oracle University and Oradist use only

Hints
• Use the Views node in SQL Developer’s Connection navigator to examine the
OLAP cube views, column names, and column values for your query. (You can
view the data values for any column by double-clicking the desired view in the
Connection navigator, and then selecting the Data tab.)
• Ensure that all dimensions include a filter, even if they are not in the select
statement. Therefore, Channel, Geography, and Time should be filtered at the
“ALL” level.
• Use the LEVELNAME_LONG_DECRIPTION column for the product dimension.
(Notice that some column names are truncated by SQL Developer.)
• In SQL Developer, drag column names from the navigator to the SQL Statement
window and drop them into the query.
Note: Solutions for each of the following lesson 6 practices are in Prac6_solutions.sql.
Try to complete the query on your own before examining the solution.
1. In SQL Developer, open /home.oracle/common_schema/olaptrain/labs/Prac6.sql,
and fill in the body of the query.
2. Execute the query. The query should return three rows, and results should look like
this:

Oracle Database 11g: OLAP Essentials A - 55

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

The correct Explain plan should look like this:

Practice 6-2: Using Level Filters in an OLAP Cube Query

Write a query that returns SALES for:
• Channel members at the CLASS level
• Product members at the DEPARTMENT level
• Quarters in calendar year 2007

Oracle University and Oradist use only

• All Products
Hints
• Use the LEVELNAME_LONG_DECRIPTION columns for each dimension (notice
that some of the column names are truncated by SQL Developer).
• Use the DIMENSION_HIERARCHY_VIEW for each dimension.
• Use the Views node in the Connection navigator to identify view names, column
names, and column values.
• Drag column names from the navigator into your query.
• View the data values for any column by double-clicking the desired view in the
Connection navigator, and then selecting the Data tab.

1. Scroll down in Prac6.sql to 6-2. Examine the query, and fill in the remaining
elements.

Oracle Database 11g: OLAP Essentials A - 56

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Oracle Database 11g: OLAP Essentials A - 57

2. Execute the query. The query should return 24 rows, and results should look like this:

Oracle University and Oradist use only

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 6-3: Adding Calculated Measures to the Query

Start with the completed query from practice 6-2. Then, modify it by adding the
following measures:
• SALES_YTD
• SALES_2006
• TO_GO
• PCT_OF_2006
• HOW_IS_SALES
1. Copy the completed query from the previous practice and paste it below the
commented lines for 6-3.

Oracle University and Oradist use only

2. Modify the query using the preceding instructions (use the round function with all
measures except for HOW_IS_SALES).
3. Execute the query.
The results should look like this:

Oracle Database 11g: OLAP Essentials A - 58

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 6-4: Leveraging Embedded Total Features of Cubes

The cube and dimensions are represented as embedded totals in the views. This makes it
easy to show multiple levels of aggregation at the same time using a simple query.
Using the completed query from practice 6-3, modify the query as follows:
• For Time, select the following three levels for 2007 using a “member” filter:
– 'CY2007'
– 'Q3-CY2007'
– 'Nov-2007'
• To simplify the query, select from the LONG_DECRIPTION column for each of
the dimensions (instead of the “level” description columns).
1. Copy the completed query from the previous practice and paste it below the

Oracle University and Oradist use only

commented lines for 6-4.
2. Modify the query using the preceding instructions.
3. Execute the query.
The results should look like this:

Oracle Database 11g: OLAP Essentials A - 59

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 6-5: Applying Hierarchical Relationships in Queries

The dimension views also include special hierarchy columns that make it easy to navigate
the dimensions (for example, drill down from ‘All Years’ to the ‘Years’). In this practice,
you use “Parent” columns in order to perform drilling.
Modify the completed query from practice 6-4 by applying a condition on the PARENT
columns for the Product and Geography dimensions. The query should:
• Select data for the children of “ALL_PRODUCTS” and “ALL_REGIONS.”
• Not display the Channel dimension
• Order by geography, product, and t.end_date (time)
1. Copy the completed query from the previous practice and paste it below the
commented lines for 6-5.
2. Modify the query using the preceding instructions.

Oracle University and Oradist use only

3. Execute the query.
Query results should look like this:

Oracle Database 11g: OLAP Essentials A - 60

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 6-6: Using Parameterized Drilling

In this practice, add a substitution parameter to the query for Time. This enables the user
to enter a valid Time member value. Perform the following
• For the Time dimension:
– Use the PARENT column for drilling.
– Use the nvl function so that if no value is provided, the ALL_YEARS
member is automatically used as the parent value.
• For the Channel dimension:
– Add Channel back into the results.
– Use the PARENT column for drilling, to return the children of
‘ALL_CHANNELS.’
• Remove Geography from the results.
1. Copy the completed query from the previous practice and paste it below the

Oracle University and Oradist use only

commented lines for 6-6.
2. Modify the query using the preceding instructions and execute the query.
Result: The Enter Bind Values dialog box appears:

3. Leave the Value box blank, and click Apply to execute the query.
Query results include a drilldown on ‘ALL_YEARS’ for the TIME dimension:

Oracle Database 11g: OLAP Essentials A - 61

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

4. Re-execute the query.

5. This time, enter CY2007 as the bind variable value, and click Apply.

Query results include a drilldown on ‘CY2007’ for the TIME dimension:

Oracle University and Oradist use only

6. Save the Prac6.sql file.
7. Minimize SQL Developer.

Oracle Database 11g: OLAP Essentials A - 62

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 6-7: Aggregating over Attributes

In this practice, you run a query that combines OLAP cube views with SQL aggregation
to make use of the dimension attribute “Channel Type.” Recall that you created this
attribute in Lesson 3, when you first created your dimensional model.
This example illustrates how to use attributes in an OLAP query. In this type of query:
• The Cube delivers summary data at the specified levels in the WHERE clause.
• Then, the relational engine summarizes cube data using a GROUP BY clause in
combination with the SUM aggregation functions in the SELECT statement.
Perform the following:
1. Open the /home.oracle/common_schema/olaptrain/labs/Prac6_solutions.sql file.
2. Scroll to the bottom of the file and examine the query under 6-7.

Oracle University and Oradist use only

3. Execute the query. The query should return the following:

Oracle Database 11g: OLAP Essentials A - 63

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practices for Lesson 7

In the practices for this lesson, you enhance the analytic content of your OLAP data
model by adding the following elements:
• Cubes with varying dimensionality
• Integrated measures from data with different dimensionality
• Forecast measures of sales data
In practice sections, you will create statistical forecasts based on historic sales data.

Practice 7-1: Creating and Populating Forecast Measures

In this practice section, you create statistical forecast measures based on historic sales

Oracle University and Oradist use only

data. These forecast measures are designed to deliver “crossover” calculations that show
actual sales for historical time periods and forecast sales for future time periods.
You will:
• Create a three-dimensional forecast cube that contains two forecast measures.
• Import and run a program that contains the logic for the forecast.
• Create and then execute a new cube script that will call the forecast program.
Follow these steps:
1. In AWM, right-click the Cubes node and select Create Cube from the menu.
2. In the General tab of the Create Cube dialog box, specify the following:
a. Name: FORECAST
b. Selected dimension order:
– TIME
– GEOGRAPHY
– PRODUCT
3. In the Storage tab, deselect the Compression option, and leave the Sparse option
unchecked for Time (Forecast is dense over Time).

Oracle Database 11g: OLAP Essentials A - 64

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Oracle University and Oradist use only

4. Click Create.
5. Create two measures in the Forecast cube, with the following names:
– BEST_FIT
– LINEAR_REGRESSION
Result: The Forecast cube now contains two forecast measures.

6. Import and view a DML program that contains the forecasting logic:
a. Open the OLAP Worksheet, and execute the following commands:
– infile cubes/do_forecast.dml
– update
– commit
b. Then, open the program by executing the following command:
– edit do_forecast

Oracle Database 11g: OLAP Essentials A - 65

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

The program contains four primary sections, which perform the following tasks:
i) Define the historical basis for the forecast (LIMIT commands)
ii) Identify forecast method (definition of forecast function variables)

Oracle University and Oradist use only

iii) Execute the appropriate forecast
iv) Aggregate the forecast measures

Oracle Database 11g: OLAP Essentials A - 66

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

c. Close the program window when you are done viewing the DML code.
7. Close the OLAP Worksheet.
8. Under the FORECAST cube node, right-click Cube Scripts and select Create Cube
Script from the menu.
9. In the Create Cube Script dialog box, perform the following:
a. Enter RUN_FORECAST as the name.
b. Click the New Step button, and select New OLAP DML Step from the menu.

Oracle University and Oradist use only

c. In the General tab of the New OLAP DML Step dialog box, enter the DML
program name do_forecast.

d. Click OK.
e. In the Create Cube Script dialog box, click Create.
10. To run the forecast:
a. Right-click the RUN_FORECAST cube script and select Run Cube Script
RUN_FORECAST from the menu.
b. In the Maintenance Wizard, select all three dimensions and click the Remove
button. The dimensions are removed from the Selected list, as the following
screenshot shows (the dimensions do not need to be loaded again).

Oracle Database 11g: OLAP Essentials A - 67

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

c. Click Finish.
11. When the Build Log appears, close the window.

Practice 7-2: Creating and Viewing Forecast “Crossover”

Calculations

Oracle University and Oradist use only

In this practice section, you create two “cross-over” calculations in the Sales cube. These
calculations return actual sales for historical data (available through CY2007) and
forecasted sales for time periods in CY2008.
1. In the Sales cube, create a new Calculated Measure by using the techniques you
learned previously. Use the following input:
a. Name = CROSS_OVER_BEST_FIT
b. Calculation Type = Expression
c. In the Expression box, enter the following CASE statement:

Note: In our example, forecasts are not broken out by channel. Therefore, the
crossover measures only show forecast data for the top level in the Channel
dimension (‘ALL_CHANNELS’).
2. Click Create.
3. Create another Calculated Measure, with the following input:
a. Name = CROSS_OVER_LINEAR_REGRESSION
b. Calculation Type = Expression

Oracle Database 11g: OLAP Essentials A - 68

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

c. In the Expression box, enter the following CASE statement:

4. Click Create.
5. Open the CROSS_OVER_BEST_FIT calculated measure in the Data Viewer.
6. Remove the Graph by clicking the down arrow of the Hide/Show tool.
7. Click the Query Builder tool.
8. In the Items tab, add SALES to the Selected list.

Oracle University and Oradist use only

9. In the Layout tab, place Geography and Channel on the Page Items axis, place
Product on the Row axis, and place Time beneath Measures in the Column axis.
10. In the Dimensions tab, remove the Time current selection and replace it with
CY2006, CY2007, and CY2008.
11. Click OK to view the data.
12. Select All Products.

Note: The Cross Over calculation uses Sales data for historical time periods, but
applies the specified forecast method for future periods

Oracle Database 11g: OLAP Essentials A - 69

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

13. Experiment with the report, for example:

a. Select the Cross Over Linear Regression calculation from the Query Builder and
move the measures to the Page Items axis, like this:

Oracle University and Oradist use only

b. Select other time periods (for example, the quarters CY2007 and CY2008).
c. Select other Product members, Geography members, or Channel members
(remember, if any Channel dimension member is selected other than
ALL_CHANNELS, the forecast returns a Null value).
14. When you are finished experimenting with the report, close the Data Viewer.
15. Exit AWM.

Practice 7-3: Querying Forecast Measures Using SQL

In this practice section, you use SQL Developer to query the Forecast data that you just
created.
Follow these steps:
1. Open SQL Developer.
2. Open the …/olaptrain/labs/Prac7-3.sql file and view the query.

Oracle Database 11g: OLAP Essentials A - 70

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Oracle University and Oradist use only

Notice that the query is similar in every way to the other OLAP cube view queries that
you created and ran in the practices for lesson 6. As you have learned, you simply select
calculated measures as columns from the cube view.

Oracle Database 11g: OLAP Essentials A - 71

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

4. Close SQL Developer.

The results should look like this:
3. Click inside the query and press F9 to execute.

Oracle Database 11g: OLAP Essentials A - 72

Oracle University and Oradist use only
THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practices for Lesson 8

In the practices for this lesson, you access OLAP data using APEX. You perform three
tasks:
1. Create an interactive report that allows you to view highly stylized output. This
report will use parameters; however the parameters will not be used until the
second practice.
2. Add formatting to the report.
3. Leverage the parameters to enable data exploration using “drilling” across the
product, time, and geography dimensions.
You will find that this style of drilldown will work with any type of hierarchy: level-

Oracle University and Oradist use only

based, parent-child, skip-level, ragged, and so on.
When you are done, you will have a formatted, interactive sales analysis report that
enables users to explore data by drilling down on products, times, and geographies.
The resulting report will look like the following:

Using this report, users:

• Can drill down on Geography, Product, and Time to any level of detail
• May want to follow the “Product Alert” column when drilling down, since this
column indicates that Sales have dropped compared to last year for at least one of
the children of the current product
For example, in the preceding report, Computers’ Sales in 2006 have improved by
22.4% compared to last year, but there is an ALERT. Drilling down on Computers
will show that there is a problem with sales within the department.

Oracle Database 11g: OLAP Essentials A - 73

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 8-1: Creating an Interactive Sales Analysis Report

In this practice section, you log into APEX as a developer and create an interactive report
in the predefined OLAP ILT – Hands On application. This application currently
contains a login page and an empty Sales Analysis page.
Follow these steps to create the interactive report:
1. Start your web browser and navigate to the Application Express login page:
http://localhost:8080/apex
2. Log in using the following details:
a. Workspace: SALESTRACK
b. Username: olaptrain
c. Password: oracle

Oracle University and Oradist use only

3. If you are prompted for a new password (if not, move to step 4):
a. Enter oracle as both the old and the new password, and then click Apply.
b. Click Return.
c. Once again, enter oracle as the password, and click Login.
Result: You should now be at the APEX developer Home page, as shown here:

4. To access the predefined application, click Application Builder > OLAP ILT –
Hands On.

Oracle Database 11g: OLAP Essentials A - 74

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

5. Click the Sales Analysis icon.

Result: You are now in the Page Editor.

Oracle University and Oradist use only

Next, you will perform the following tasks:
• Create three Hidden Items that will be used for your parameterized query. These
parameters will be used for setting up dimension “drilling” in the next practice.
For example, a hidden item named P1_PRODUCT will be used as a parameter for
drilling down on the Product dimension in the sales query as follows:
p.parent = nvl(:P1_PRODUCT, 'ALL_PRODUCTS')
This condition selects the children of the product represented by the parameter
(and selects “ALL_PRODUCTS” if P1_PRODUCT is null).
• Create an interactive report region that shows sales performance by region,
product, and time.
First, create the Hidden Items.

Oracle Database 11g: OLAP Essentials A - 75

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

6. Create a hidden item P1_PRODUCT:

a. Click the Create button in the Items pane.

b. On the Create Item page, select Hidden and click Next.

Oracle University and Oradist use only

c. Select Hidden for the Hidden Item Type and click Next.
d. Name the item P1_PRODUCT and click Next.
e. Accept the defaults for the item source and click Create Item.

7. Create a second hidden item P1_GEOGRAPHY using the preceding steps.

8. Create a final hidden item P1_TIME using the preceding steps.
When you are done, the Items pane should look like this:

Next, create the Interactive report.

9. Create an interactive report region:

a. On the Page Definition page, click the Create button in the Regions pane.
b. On the Create Region page, select Report and click Next.
c. On the Report Implementation page, select Interactive Report and click Next.
d. On the Display Attributes page, enter Explore as the title and click Next.

Oracle Database 11g: OLAP Essentials A - 76

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

e. On the Source page, you will use a predefined query. Perform the following:
i) Select File > Open File from the browser’s main menu.
ii) Open the home/oracle/common_schema/olaptrain/labs/Prac8-1.sql file.
iii) Select the query (that is explained in the following) and copy it to the
clipboard.

SELECT
g.long_description "Geography",
p.long_description "Product", Dimension Descriptions
t.long_description "Time",

trunc(sales) "Sales",
trunc(sales_pp_pct_chg, 1)
"% Chg Pr Period",

Oracle University and Oradist use only

trunc(sales_py_pct_chg, 1) Measures
"% Chg Pr Year",
product_alert "Product Alert",
trunc(sales_ytd) "YTD",
trunc(sales_ytd_py_pct_chg, 1)
"YTD % Chg Pr Year",

t.dim_key as time_dim_key,
t.parent as time_parent,
t.end_date as end_date, Dimension Key and
g.parent as geography_parent, Parent Columns (used
g.dim_key as geography_dim_key, for drilling)
p.parent as product_parent,
p.dim_key as product_dim_key

FROM sales_cube_view s,
time_calendar_view t, Cube and dimension
product_standard_view p, views
geography_regional_view g

WHERE s.time = t.dim_key

AND s.product = p.dim_key
AND s.geography = g.dim_key
AND s.channel = 'ALL_CHANNELS'
AND t.parent =
nvl(:P1_TIME, 'ALL_YEARS') Parent conditions that
AND g.parent = select children of
nvl(:P1_GEOGRAPHY, 'ALL_REGIONS') parameter value
AND p.parent =
nvl(:P1_PRODUCT, 'ALL_PRODUCTS')

Note: This single select statement is used to navigate vast regions of the cube.
Notice that the parameter names match the hidden items you created previously.
iv) Paste the query into the SQL SELECT statement box.

Oracle Database 11g: OLAP Essentials A - 77

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

v) Select No for the Link to Single Row View option.

The Source page should look like this:

Oracle University and Oradist use only

vi) Click Create Region.
The Regions pane should look like this:

Oracle Database 11g: OLAP Essentials A - 78

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 8-2: Formatting the Interactive Sales Analysis Report

Now that your report has been created, you will apply formatting to it. You will apply the
following type of formatting:
• Select the columns to display.
• Sort the report by time.
• Add a control break on Geography to the report.
• Add color coding to the report.
Follow these steps to format the interactive report:

1. Click the button in the middle of the page to view and then format the report.
2. In the Login screen, enter olaptrain/oracle and click Login.

Oracle University and Oradist use only

3. Select the report columns to display:

a. Click the Gear icon , and then click Select Columns.

b. Select the following dimension and measures columns in the Display in Report
list. Then, click Apply.

4. Sort the rows by the End Date column:

a. Click the Gear icon and then select Sort.

b. For Column 1, select End Date and then click Apply.
5. To add a control break on Geography to the report, click the Geography report
header and select Control Break.

6. Color the % Chg Pr Year cells green if sales growth exceeds 10%:

a. Click the Gear icon and then select Highlight.

b. Name the Highlight format Winners.

Oracle Database 11g: OLAP Essentials A - 79

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

c. Set the Highlight type to Cell.

d. Set the Background Color to green.
e. Specify the following Highlight Condition: % Chg Pr Year > 10
f. Click Apply.
7. Color Product Alert red if there is an ALERT.

a. Click the Gear icon and then select Highlight.

b. Name the format Product_Alert.
c. Set the Highlight type to Cell.
d. Set the Background Color to red and the Text Color to yellow.
e. Specify the following Highlight Condition: Product Alert = ALERT

Oracle University and Oradist use only

f. Click Apply.
Your report should now look something like the following:

8. Scroll through the report to view the formatting.

9. Save the Report as the default:

a. Click the Gear icon and then select Save Report.

b. Name the report Default Report Settings and click Apply.

Oracle Database 11g: OLAP Essentials A - 80

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 8-3: Adding Drilldown Capabilities to the Report

The final step is to enable drilldown capabilities to the report. When users click a
Geography, Product or Time value, the details for the selected item will replace what was
selected. For example, clicking CY2007 displays the calendar quarters for that year.
Application Express allows you to specify links for columns on the Report Attributes
page. In our case, the link will set the relevant dimension parameter (a.k.a. Hidden Item)
to the item the user selected.
Consider the flow for the time drilldown previously described:
• User clicks the CY2007 link.
• The logic behind the link specifies that the parameter P1_TIME is set to the
selected value.
• The query is re-executed, whereby the condition:

Oracle University and Oradist use only

t.parent = nvl(:P1_TIME, 'ALL_YEARS')
Selects all time values whose parent is ‘CY2007.’ This is the equivalent to
selecting the children of ‘CY2007.’
Result: A drilldown has been executed.
Perform the following steps to add drilldown capabilities to the report:

1. Return to the page editor by clicking the link at the bottom of the report
page.
2. Go to the Report Attributes page by clicking the Interactive Report link in the
Regions pane.
3. Add a drilldown link to Geography:
a. Click the Edit icon for Geography.
b. Click the button above the Column Definition page.
c. For Link Text, click the [Geography] link.
d. In the Page text box, enter 1 (that is, the current page number is “1”).
e. Use the flashlight icons to enter name value pairs as follows:

Note:
• You set the “link” dimension to the current member. This value, as described
earlier, will cause the drilldown on Geography.

Oracle Database 11g: OLAP Essentials A - 81

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

• You also set parameters for the Product and Time dimensions as well. By
setting these values to the parent column, you are effectively preserving the
state of the report (the children of a parent produces the current set of
members). This allows browser operations like bookmarking to re-create the
report in its drilldown state; these parameters appear in the URL.
f. Click Apply Changes.
4. Add a drilldown link to Product:
a. Click the Edit icon for Product.
b. For Link Text, click [Product].
c. For Page, enter 1 (that is, the current page number is “1”).
d. Use the flashlights to enter name value pairs as follows:

Oracle University and Oradist use only

e. Click Apply Changes.
5. Finally, add a drilldown link for Time:
a. Click the Edit icon for Time.
b. For Link Text, click [Time].
c. For Page, enter 1 (that is, the current page number is “1”).
d. Use the flashlights to enter name value pairs as follows:

e. Click Apply Changes.

The Column Attributes window should look like this:

6. In the Region Name pane, click Apply Changes.

Oracle Database 11g: OLAP Essentials A - 82

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

7. Run the report from the Application Page. The report should look like this:

Oracle University and Oradist use only

8. For Africa, click the Computers link for CY2005. This is the row that had excellent
percent growth in sales, but showed an Alert in the following data.

The resulting report shows the Computer division products that contain underlying alerts:

9. Feel free to experiment with the interactive report. If you have time, there is an
optional exercise to examine on the following page.

Oracle Database 11g: OLAP Essentials A - 83

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 8-4: Optional Interactive Report Steps

It is often useful to add a reset button to the report. This allows you to reset the report to
its original state.
To add a reset button:

1. In the Page Editor, click Create in the Buttons area.

2. Add the button to the Explore region and click Next
3. Select position Create a button in a region and click Next.
4. Specify button properties:
a. Button Name: Reset
b. Label: Reset
c. Select HTML Button, and then click Next

Oracle University and Oradist use only

5. Add the button to the Top and Bottom of Region and click Next
6. Select branch to Page: 1 (the current page). Click Create Button.
Now that the button has been created, you need to specify an action for the button:
7. In the Page Processing > Branches section of the page editor, click Go to Page.
8. In the Action section of the page, enter “1” for Clear Cache. This will clear the
parameter values for P1_PRODUCT, P1_TIME, and P1_GEOGRAPHY.
9. Click Apply Changes.
10. Run the page.
Result: You will see two reset buttons. Clicking these buttons will reset the report.

Oracle Database 11g: OLAP Essentials A - 84

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practices for Lesson 9

In this practice, you will enable secured access to cube data. Specifically, you will
perform two exercises:
1. Allow the DM user to see all of the data in the OLAPTRAIN sales cube.
2. Restrict DM’s view of the data to a) indirect distribution channels and b) sales across
the entire company.
You will use SQL Developer to perform the first exercise. The second exercise will be
performed using AWM.
Practice 9-1: Grant DM User Access to Sales Cube
In this practice section, you connect to both the DM and OLAPTRAIN schemas using
SQL Developer. The OLAPTRAIN user will grant the DM user access to all the objects

Oracle University and Oradist use only

required to access data in the SALES_CUBE plus the associated dimension views.
Follow these steps to grant the DM user access to the SALES_CUBE and the related
dimension views:
1. In SQL Developer, open a connection to the database using the OLAPTRAIN user by
clicking the local (OLAPTRAIN) node in the Connections pane. You will use the
SQL Worksheet for the following steps.
2. Grant select privileges on the SALESTRACK analytic workspace:
grant select on AW$SALESTRACK to dm;
3. Grant select privileges on the dimensions and the dimension views:
grant select on product to dm;
grant select on product_view to dm;

grant select on time to dm;

grant select on time_view to dm;

grant select on geography to dm;

grant select on geography_view to dm;

grant select on channel to dm;

grant select on channel_view to dm;
4. Grant select privileges on the sales cube and its view:
grant select on sales_cube to dm;
grant select on sales_cube_view to dm;
5. Remember, some of the calculations in the SALES_CUBE refer to measures in the
FORECAST cube. If you plan to query the “crossover” calculations, make sure you
grant access to the FORECAST cube (and optionally, the FORECAST_VIEW) as
well:
grant select on forecast to dm;
grant select on forecast_view to dm;

Oracle Database 11g: OLAP Essentials A - 85

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

6. Open a connection to the database using the DM user by clicking the local (DM)
node in the Connections pane.
7. In the SQL Worksheet for the following steps, select sales data from the
SALES_CUBE:
select c.long_description,
s.sales
from olaptrain.sales_cube_view s,
olaptrain.channel_view c
where s.product = 'ALL_PRODUCTS'
and s.geography = 'ALL_REGIONS'
and s.time = 'ALL_YEARS'
and s.channel = c.dim_key;

Oracle University and Oradist use only

The results should look like the following:

Oracle Database 11g: OLAP Essentials A - 86

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Practice 9-2: Limit the Channels That the DM User May View
In this practice section, you will use AWM to define a data security policy over the
Channel dimension.
The DM user will only be allowed to see the following Channel members:
• All Channels
• Indirect, Web
• Catalog
• Indirect Warehouse.
Follow these steps in AWM to limit the Channels that the DM user may view:
1. Right-click the Data Security node for the Channel dimension and select Create
Data Security Policy.
2. In the General tab of the Create Data Security Policy dialog box, specify the

Oracle University and Oradist use only

following:
a. Name: Indirect_Channels
b. User or Role: DM
c. Ensure that the user has Select privileges, like this:

Oracle Database 11g: OLAP Essentials A - 87

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Oracle University and Oradist use only

3. Click the Member Selection tab to identify the Channels that DM may select.
4. Define a three-step query using the Query Builder. Use the hypertext links to edit the
conditions as follows:
a. In the Selected list, change the first step to Start with ALL_CHANNELS.
b. In the Members tab of the Available list, select Indirect and shuttle it to the
Selected list.
c. In the Conditions tab of the Available list, select Children of All Channels and
shuttle it to the Selected list.
d. In the third step of the Selected list, change Children of All Channels to
Children of Indirect. (Hint: To do this, click the “All Channels” hyperlink, and
select “Indirect” from the list.)
e. Click the Show Condition button to display the condition expression that this
security policy will create.
The completed dialog box should look like this:

Oracle Database 11g: OLAP Essentials A - 88

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

f. Click Create.

Oracle Database 11g: OLAP Essentials A - 89

Oracle University and Oradist use only
THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

5. Go back to SQL Developer, and log off of DM. Then log back on as DM.
6. Re-execute the following query using the DM connection:
select c.long_description,
s.sales
from olaptrain.sales_cube_view s,
olaptrain.channel_view c
where s.product = 'ALL_PRODUCTS'
and s.geography = 'ALL_REGIONS'
and s.time = 'ALL_YEARS'
and s.channel = c.dim_key;

Oracle University and Oradist use only

Note:
• The results have changed; DM only has access to the “All Channels” and the
indirect channels.
• Every view in the database that uses the Channel dimension will now be
automatically scoped.

Oracle Database 11g: OLAP Essentials A - 90