Lesson 1: Creating BI EE Metadata for OLAP 11g Cubes

Purpose

This tutorial covers the creation of Oracle Business Intelligence Enterprise Edition metadata for access to Oracle Database 11g OLAP Option data and calculations.

Time to Complete

Approximately 1 hour.

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Define the Physical Layer
	Define Standard Business Model Metadata
	Specify a Complex Join on the Logical Tables
	Define Additional Metadata to Leverage OLAP Cube Aggregations
	Define the Presentation Layer
	More 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

What is Oracle OLAP?

Oracle OLAP is an integrated component of Oracle Database 11g that enables companies to easily gain insights into business performance. It offers:

	Exceptional query, calculation and data preparation performance
	Rich analytic capabilities
	Simple user model that reflects business usage
	Open access to any SQL tool

Native multidimensional object types in Oracle database are provided by Oracle OLAP Cubes. Cubes are made up of Measures and organized by Dimensions.

	Measures represent factual data, such as sales, cost, profit, and margin. Measures may be stored or calculated at query time. Stored measures are loaded and stored in the database. The values for calculated measures are computed dynamically by the OLAP calculation engine at query time. Common calculations include measures such as ratios, differences, time-series, indicies, moving totals, and averages. Calculations do not require disk storage space, and they do not extend the processing time required for data maintenance.
	Dimensions identify and categorize your measure data. They shape measures by forming the edges of the measures. Examples of dimensions include product, geography, time, and distribution channel. Dimension hierarchies are optional but are common in OLAP systems. A hierarchy is a logical structure that groups like members of a dimension together for the purpose of analysis. A dimension’s structure is organized hierarchically based on parent-child relationships. These relationships enable navigation between levels, and aggregation from child values to parent values.
	Cubes provide a convenient way of collecting similar measures of the same dimensionality. It is not uncommon for many measures to have the same shape, and so by defining their shape (and other shared characteristics) for a cube, you can save time when building your OLAP data model.

To access OLAP cubes and leverage the OLAP calculation engine, a SQL tool -- such as Oracle BI EE 10g -- uses the built-in SQL interface to OLAP. Oracle OLAP cube data is made directly accessible to SQL by a set of relational views. These views represent an OLAP cube as a star schema with the following characteristics:

- A cube view plays the role of a fact table.

- Dimension views or hierarchy views play the role of dimension tables.

The star design exposed by OLAP cubes is very similar to traditional table-based star models. The dimension views form a constellation around one or more cube views. However, there are two key differences:

- A fact tables in a star schema stores detail data (called leaves), while a cube view reveals all summary levels defined in the OLAP cube.

- Calculations in a cube are simply exposed as columns in the cube view, and the computation for the equations occurs in the OLAP engine.

Note: The OLAP data for this tutorial was created using steps found in the Building OLAP11g Cubes tutorial. For information about the OLAP model used in this tutorial, and for step-by-step instructions on how to create OLAP 11g cubes, click the link.

Understanding BI EE Metadata

In order to use any BI end-user tool that depends on its own metadata layer, such as BI Answers and BI Dashboards, the metadata repository must describe how queries should be constructed against the relational data sources.

To use the same BI tool with OLAP data, you follow the same metadata administrative tasks that are required for any relational source, and then you update the metadata to leverage the unique aggregation properties of the OLAP cube views.

At the highest level, the creation of metadata for BI EE is a three-step process.

In this lesson, you will define the three layers of metadata for access to the SALES_CUBE in the SALESTRACK analytic workspace. This AW is part of the OLAPTRAIN sample schema.

A completed repository for this lesson is available for download in the Related information section.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

Back to Topic List

Define the Physical Layer

The first step in defining BI EE metadata for OLAP data is to define the Physical Layer. To do this, you create a repository file (.RPD) and import the OLAP cube views into the Physical Layer. Then you create joins for the views. When completed, these steps identify the OLAP cube views as the data source though which the OLAP cubes are accessed.

In the following subtopics, you will import the views, test the import, and create the required joins in the physical model.

	Create the RPD File and Import the OLAP Cube Views
	Test the Import
	Create Joins in the Physical Model

Back to Topic List

Create the RPD File and Import the OLAP Cube Views

As stated previously, Oracle OLAP creates and maintains views for each Cube, Dimension, and Hierarchy in the data model.

For example, the SALES_CUBE in the sample schema (olaptrain) AW is dimensioned by Channel, Time, Product, and Geography. There is one hierarchy defined for each dimension.

Therefore, the following views are maintained for the Sales Cube:

Note: For more information on the cubes views that are created for sample schema, see the Building OLAP11g Cubes tutorial.

To create the physical layer, you import the Cube view, and one Hierarchy view per dimension. In this topic, you import the following views:

	SALES_CUBE_VIEW
	TIME_CALENDAR_VIEW
	PRODUCT_STANDARD_VIEW
	GEOGRAPHY_REGIONAL_VIEW
	CHANNEL_SALES_CHANNEL_VIEW

Note: In many cases, you may use either dimension views or hierarchy views to represent the dimensions within the OLAP model. The view you use for a dimension depends on your business requirement. If a dimension level is shared across hierarchies - and the members within that level are the same across hierarchies - then you can use the dimensions view instead of the hierarchy view.

Perform the following steps.

Back to Topic

Test the Import

After the import is complete, a new node appears in the Physical layer pane. To test access to the source data, perform the following steps.

Back to Topic

Create Joins in the Physical Model

Next, you define joins between the Foreign Key column in the cube view and the Primary Key column in the hierarchy view for each of the dimensions.

1.	In the Physical pane: a. Select all of the cube views. b. Right-click on the views and select Physical Diagram > Object(s) and All Joins from the menu, like this:
2.	In the Physical Diagram window, arrange the views in a similar way to the image below:
3.	Click the New complex join tool, as shown here:
4.	Click SALES_CUBE_VIEW and drag the mouse to GEOGRAPHY_REGIONAL_VIEW, as shown here:
5.	Click GEOGRAPHY_REGIONAL_VIEW. Result: The Physical Join window appears, like this:
6.	Create a join between the two views by selecting the GEOGRAPHY column from SALES_CUBE_VIEW, and the DIM_KEY column from GEOGRAPHY_REGIONAL_VIEW, as shown below. Result: The join syntax is automatically created in the Expression box.
7.	Click OK to save the join. Result: the Physical Diagram window should now look like this:
8.	Using the same techniques described in steps 3 - 5, draw a join between SALES_CUBE_VIEW and PRODUCT_STANDARD_VIEW. Then, in the Physical Join window, perform the following: a. Select the PRODUCT column from SALES_CUBE_VIEW, and the DIM_KEY column from PRODUCT_STANDARD_VIEW, as shown here:. b. Click OK. Result: the Physical Diagram window should now look like this:
9.	Using the same techniques described above, create joins for the remaining two hierarchy views, using the following columns for each join: a. SALES_CUBE_VIEW and TIME_CALENDAR_VIEW: b. SALES_CUBE_VIEW and CHANNEL_SALES_CHANNEL_VIEW: Result: the Physical Diagram window displays the completed complex join:
10.	Close the Physical Diagram window.

Back to Topic

Define Standard Business Model Metadata

Creating busness model metadata for an OLAP data source is comprised of two phases. First, you define a business model in the typical way you would for any relational source. Then, you define additional metadata that leverages the OLAP embedded total view.

In this topic, you will define standard business model metadata, which includes the following steps:

	Create Logical Tables for a Star
	Map Physical Layer columns to Business Model Objects
	Enable Drilling by Dimension Level

Back to Topic List

Create Logical Tables for a Star and Define Joins

Perform the following steps to create the logical tables and joins for the business model.

1.	First, create the Business Model object. Select the Business Model and Mapping pane, and then right-click in the white space. Select New Business Model from the menu. In the Business Model - olaptrain window, enter olaptrain as the name, and click OK.
2.	Create a logical table for the measures by peforming the following steps: A. Right-click on the olaptrain business model, and select New Object > Logical Table from the menu. B. In the General tab of the Logical Table window, enter Facts as the name. C. Click OK. A logical table named Facts is displayed:
3.	Create a logical table for the Channel dimension: A. Right-click on the olaptrain business model, and select New Object > Logical Table from the menu. B. In the General tab of the Logical Table window, enter Channel as the name. C. Click OK.
4.	Create a logical table for the Geography dimension: A. Right-click on the olaptrain business model, and select New Object > Logical Table from the menu. B. In the General tab of the Logical Table window, enter Geography as the name. C. Click OK.
5.	Create a logical table for the Product dimension: A. Right-click on the olaptrain business model, and select New Object > Logical Table from the menu. B. In the General tab of the Logical Table window, enter Product as the name. C. Click OK.
6.	Create a logical table for the Time dimension: A. Right-click on the olaptrain business model, and select New Object > Logical Table from the menu. B. In the General tab of the Logical Table window, enter Time as the name. C. Click OK. The following logical tables should now be included in the business model:

Back to Topic

Map Physical Layer Columns to Business Model Objects

In this topic, you map columns from the Physical layer to the Business Model layer, using the following rules:

	For the logical Facts table, you map the dimension columns, and then the desired measure columns, from the cube view.
	For each logical dimension table, you will map the appropriate description column for each hierarchical level, and also the DIM_KEY column. For the Time dimension, you will also map the END_DATE column, which is used for sorting purposes..

Perform the following steps:

1.	First, click the collapse (-) symbol next to each of the logical tables, so that the display looks like this:
2.	In the Physical layer, drill on SALES_CUBE_VIEW, and then drag the CHANNEL column from the Physical layer pane to the Business Model pane and drop it on the logical Facts table, as shown here: Result: A column named CHANNEL appears under the Facts logical table.
3.	From the Physical layer pane, drag the GEOGRAPHY column to the Facts logical table as before (and not on the Sources folder). Result: the GEOGRAPHY column appears under the Facts logical table.
4.	From the Physical layer pane, drag the PRODUCT and TIME columns to the Facts logical table. Result: the four dimension columns are under the Facts logical table.
5.	In the Physical layer pane, select the QUANTITY column, and also all of the columns that begin with "SALES", like this: Drag and drop the selected measures on the Facts logical table as you did with the dimension columns.
6.	Using the same technique as above, select the remaining measure columns from the Physical layer, as shown below, and drag them to the Facts logical table. The Facts logical table should now contain the following columns: Mapping for the logical Facts table is complete.
7.	Provide more user-friendly descriptions of the measure columns by performing the following: a. Double-click on the measure column under the Facts logical table. b. Select the General tab in the Logical Column window c. Provide a new description in the Name field and click OK. For example, for the QUANTITY measure, enter Quantity as the name, like this When you click OK, the new column name appears under the Facts logical table:
8.	Using the same technique as described in the previous step, rename the remaining measure columns so that they look like the following: Next, you map certain columns to each of the logical dimension tables.
9.	For each logical dimension table, you map the following columns from the associated OLAP hierarchy view: A description column (Long or Short) for each level in the hierarchy The DIM_KEY column Any attribute columns that you wish to query For the logical Time table only -- the END_DATE column -- for sorting purposes To map the Logical Channel table, perform the following: a. Drill on CHANNEL_SALES_CHANNEL_VIEW. b. Drag ALL_CHANNELS_LONG_DESCRI (the long description column for the 'All Channels' level) from the hierarchy view to the Channel logical table, as shown here: Note: OLAP truncates cube view column names at 24 characters Result: The long description column is added to the Channel logical table: c. In the same way, drag CLASS_LONG_DESCRIPTION from the hierarchy view to the Channel logical table (and not the Sources folder). d. Drag CHANNEL_LONG_DESCRIPTION from the hierarchy view to the Channel logical table. e. Finally, drag DIM_KEY from the hierarchy view to the Channel logical table. Result: the Channel logical table should look like this:
10.	Rename each of the long description columns in the Channel logical table using the following techniques: a. Double-click on the column. b. Select the General tab of the Logical Column window. c. Change the name. d. Click OK. For example: Rename use the following new column names:   Physical Layer Column New Logical Column Name   ALL_CHANNELS_LONG_DESCRI All Channels   CLASS_LONG_DESCRICRIPTION Class   CHANNEL_LONG_DESCRICRIPTION Channel When you are done, the Channel logical table should look like this:
11.	Next, map the columns for the Geography dimension. Drag the following columns from GEOGRAPHY_REGIONAL_VIEW in the Physical pane, to the logical Geography table in the Business Model and Mapping pane, using the following techniques: a. Map each column individually, and in the order shown. This approach simplifies an upcoming metadata definition task. b. Make sure to drop the columns on the logical Geography table, and not on the Sources folder. c. After mapping, rename each of the long description columns in the logical Geography table as shown (do not rename the DIM_KEY column).   Physical Layer Column New Logical Column Name   ALL_REGIONS_LONG_DESCRIP All Regions   REGION_LONG_DESCRIPTION Region   COUNTRY_LONG_DESCRIPTION Country   STATE_PROVINCE_LONG_DESC State-Province   DIM_KEY   When you are done, the Geography logical table should look like this:
12.	Next, map the columns for the Product dimension. Drag each of the following columns from PRODUCT_STANDARD_VIEW in the Physical pane, to the logical Product table in the Business Model and Mapping pane. Then rename the logical column descriptions using the same techniques as with previous dimensions (do not rename the DIM_KEY column):   Physical Layer Column New Logical Column Name   ALL_PRODUCTS_LONG_DESCRI All Products   DEPARTMENT_LONG_DESCRIPT Department   CATEGORY_LONG_DESCRIPTIO Category   TYPE_LONG_DESCRIPTION Type   SUBTYPE_LONG_DESCRIPTION Subtype   ITEM_LONG_DESCRIPTION Item   DIM_KEY   When you are done, the Product logical table should look like this:
13.	Next, map the columns for the Time dimension. Drag each of the following columns from TIME_CALENDAR_VIEW in the Physical pane, to the logical Time table in the Business Model and Mapping pane. Then rename the logical column descriptions using the same techniques as with previous dimensions (do not rename the END_DATE or DIM_KEY columns):   Physical Layer Column New Logical Column Name   ALL_YEARS_LONG_DESCRIPTI All Years   CALENDAR_YEAR_LONG_DESCR Calendar Year   CALENDAR_QUARTER_LONG_DE Calendar Quarter   MONTH_LONG_DESCRIPTION Month   END_DATE     DIM_KEY   When you are done, the Time logical table should look like this:
14.	For the logical Time dimension, the END_DATE column will be used to specify the default sort order. Otherwise, when you display Month columns in a report, they will be sorted using BI EE's default alphabetic sorting rule, like this: Q1 > Feb, Jan, Mar; Q2 > Apr, Jun, May; and so on. To specify the sort order for Month: a. First double-click on the Month column under the logical Time table. b. Then, in the Logical Column Window, click the Set button next to the Sort order column box. c. In the Browse window, select the END_DATE column and click OK. d. Finally, click OK to close the Logical Column window.
15.	Just as you did for the Month logical column in step 14, set END_DATE as the Sort Order column for the Calendar Quarter and Calendar Year logical columns. However, do not set a Sort Order column for the All Years logical column.
16.	Next, assign a primary key for each logical dimension table, by performing the following: a. Double-click the logical Channel table. In the Keys tab, click New. Then, select the Channel column, as shown here:   b. Click OK to assign Channel as the primary Key. Then, click OK to close the Logical Table window. The Channel logical table should now look like this:   c. Using the same technique as in 16a - b, assign State-Province as the primary key for the logical Geography table. d. Assign Item as the primary key for the logical Product table. e. Assign Month as the primary key for the logical Time table. The logical dimension tables should now look like this:

Back to Topic

Enable Drilling by Dimension Level

In this topic, you define metadata that enables drilling by levels within the dimension hierarchy be performing the following:

	First, define Dimension objects and their associated Level objects that correspond to the dimensions and levels modeled in the logical dimension tables.
	Second, map the columns -- within the Business Model and Mapping pane -- from the logical dimension tables to the associated dimension object levels.

Follow these steps:

1.	In the Business Model and Mapping pane, create a Dimension object for each of the logical dimension tables. a. Right-click on the olaptrain business model icon, and select New Object > Dimension from the menu. b In the General tab of the Dimension window, enter Channel Dim as the name. c. Click OK. The Dimension object appears in the business model.   d. Using the same technique described in 1a - 1c, create three more dimension objects named: Geography Dim, Product Dim, and Time Dim. When you are finished, the olaptrain business model should look like this:
2.	For each Dimension object, create Level objects that represent the dimension's hierarchical structure. a. Right-click on Channel Dim and select New Object > Logical Level from the menu. b. In the General tab of the Logical Level window, enter All Channels as the name and click OK. A new Level appears below Channel Dim.   c. Right-click on the All Channels level object that you just created, and select New Object > Child Level from the menu. Then, in the General tab of the Logical Level window, enter Class as the name and click OK. Result: The Class Level appears below All Channels.   d. Right-click on the Class level object that you just created, and select New Object > Child Level from the menu. Then, in the General tab of the Logical Level window, enter Channel as the name and click OK. Result: The Channel Dim object contains the following levels: Notes: The top level for each dimension hierarchy is created using the technique described in 2a - 2b. Subsequent child level for all hierarchies are created using the technique descibed in 2c - 2d. e. Using the techniques described in 2a - 2d, create hierarchy levels under the Geography Dim object like this: All Regions > Region > Country > State-Province When you are done, Geography Dim should look like this:   f. Using the same techniques, create the following levels under the Product Dim object: All Products > Department > Category > Type > Subtype > Item When completed, the Product Dim object should look like this:   g. Using the same techniques, create the following levels under the Time Dim object: All Years > Year > Quarter > Month When completed, the Time Dim object should look like this:
3.	Next, you map columns within the Business Model and Mapping pane: First, drag the appropriate columns from the logical table to the associated dimension object levels. This provides the required mapping information for drilling purposes. Second, assign each dimension level column as a 'Logical Key'. For the Channel dimension, perform the following: a. Expand all the level nodes in Channel Dim, and expand the Channel logical table node. b. Drag and drop the All Channels column from the logical Channel table to the All Channels level inside the Channel Dim object. Result: A column appears directly below the All Channels level.   c. Assign the level object column as the Key by performing the following: - Right-click on the All Channels column and select New Logical Level Key from the menu. - In the Logical Level Key window, accept the default settings and click OK. Result: The level object column is designated as a key.   d. Repeat step 3b - 3c for the Class and Channel levels. When you are done, the completed Channel Dim object should look like this:
4.	Use the same mapping technique as described in step 3 to map all columns from the logical Geography table to the associated levels in the Geography Dim dimension object. Then, specify Logical Key status to each level column. When done mapping and specifying logical key assignments, the display should look like this:
5.	Use the same mapping technique as described in step 3 to map all columns from the logical Product table to the associated levels in the Product Dim dimension object. Then, specify Logical Key status to each level column. When done mapping and specifying logical key assignments, the display should look like this:
6.	Use the same mapping technique as described in step 3 to map all columns from the logical Time table to the associated levels in the Time Dim dimension object. Then, specify Logical Key status to each level column. When done mapping and specifying logical key assignments for the Time table, the display should look like this:
7.	Collapse all of the dimension object and logical table nodes.

Back to Topic

Specify a Complex Join on the Logical Tables

1.	In the Business Model and Mapping pane, right-click on any one of the logical tables. Then, select Business Model Diagram > Whole Diagram from the menu. In the Logical Table Diagram window, arrange the logical tables into a 'star', something like this:
2.	Create logical joins between the dimension tables and the Facts table by performing the following: a. Click the New Complex Join icon in the toolbar.   b. Click on one of the logical dimension tables, such as Channel. Then, click on the Facts table. Result: The Logical Join window is displayed. Simply click OK to create the join. The first logical join is established.   c. Repeat steps 2.A and 2.B for each of the logical dimension tables. When you are done, the Logical Table Diagram window should something look like this:
3.	Close the Logical Table Diagram window. Result: The olaptrain business model should now look like this:

Back to Topic List

Define Additional Metadata to Leverage OLAP Cube Aggregations

Next, you define two additional metadata elements in the Business Model and Mapping layer:

1. Define a logical table source for each dimension level.

2. Use BI EE security filters to automatically add level conditions to each query.

By defining the additional metadata, you leverage the aggregations in the OLAP cube views by making queries “Level-Aware”. As a consequence, the following ideal OLAP query characteristics will apply to the generated SQL:

	Level conditions are applied to every dimension in the WHERE clause.
	Level conditions are applied to lowest selected level for a dimension. For example, if “Year” and “Quarter” are both in a query, then the condition time_level=‘QUARTER’ is applied.
	An “All/Total Level” condition is applied to dimensions that are omitted from the SELECT statement. This is required in order to leverage cube aggregation.

Queries that are tuned to leverage OLAP aggregations in this way will optimize performance. In addition, all OLAP calculations occur in the Oracle OLAP calculation engine.

Example of a Level-Aware Query and Report

The following report from Oracle BI Answers illustrates the optimal SQL to leverage OLAP aggregations.

BI Answers Report

SQL Query for the Report

In the SQL query:

	Calculated measures are simply selected as columns. The data is computed in the OLAP calculation engine and passed through the cube view.
	Level conditions are applied to all four dimensions, even though only three dimensions are in the SELECT statement (Geography, Product, and Time).
	Level conditions are applied at the lowest selected level for each dimension in the query: REGION, DEPARTMENT, and QUARTER.
	Since the channel dimension is omitted from the SELECT statement, the 'ALL_CHANNELS' level condition is automatically applied to the query for that dimension. This feature ensures that OLAP cube aggregations are leveraged.

Note: for information on how to manually create SQL queries against OLAP 11g data, see Querying OLAP 11g Cubes.

To achieve the required OLAP cube query results, perform the following administrative tasks:

	Define a Table Source for Each Dimension Level
	Automatcially Add Level Condtions Using Security Filters

Back to Topic List

Define a Table Source for Each Dimension Level

Currently, each logical dimension contains a single source. Now, you will create logical table sources for each level in the logical dimension.

Each level table source should contain mapping information that describes its source, and also the sources for parent levels above it within the dimension hierarchy. However, columns below the grain of the level should not be mapped. In addition, each "level" table source will specify a literal value that describes its level within the hierarchy.

IMPORTANT: As shown in the steps below, the logical table sources for each dimension should be ordered from most aggregate to most detail level (i.e. All Years to Month).

Follow these steps to create logical table sources for all levels in each dimension:

1.	First, create logical table sources for all of the levels in the logical Channel table. Then, modify the table sources as specified below. a. To begin, drill on the Sources folder under the logical Channel table. Then, right-click on the CHANNEL_SALES_CHANNEL_VIEW table source and select Duplicate from the menu. Result: A second table source appears, named CHANNEL_SALES_CHANNEL_VIEW#1.   b. Since the Channel dimension contains three levels, you need to create another copy of the table source. Perform exactly the same actions as specified in step 1a. Result: a third table source appears. Modify the table sources as follows: c. Double-click the CHANNEL_SALES_CHANNEL_VIEW table source. The Logical Table Source window appears. d. In the General tab, enter CHANNEL_VIEW (All Channels) in the name field.   e. For all dimension levels, you use the Column Mapping tab to specify mappings for the current level, and all levels above it in the hierarchy. Therefore, mappings for all levels below it must be deleted. However, the DIM_KEY column mapping must be left as is. Initially, the Column Mapping tab shows that all levels are mapped, as shown here: Delete the mappings for the logical columns below the current level. To delete the appropriate mapping(s), click the "X" icon to the right of the Expression column, as shown here: In this case, you delete the mappings for the Channel and Class logical columns, since they are at a lower level than All Channels. After deleting the mappings for Channel and Class, the Column Mapping tab should look like this:   f. Next, click on the Content tab. In the Content tab, you: Select the Logical Level for the Dimension object that is associated with the Logical Source. Create a 'WHERE clause' that selects the correct level from the OLAP Dimension or Hierarchy View. Recall the BI Answers report and query example at the beginning of this topic. In the WHERE clause (shown below), the cube and dimension hierarchy views are joined. Then, conditions are applied to each dimension using the LEVEL_NAME column from the hierarchy views. These "level" conditions help ensure that the appropriate OLAP Cube aggregations are leveraged in the SQL query. The next step in your metadata preparation tells Oracle BI how to create the appropriate level condition for any OLAP cube query. In the Content tab: First, select the correct Logical Level for the Dimension object. In this case, Select All Channels for Channel Dim. Second, create the WHERE clause that will generate the correct level condition for any query that uses this level. Follow these directions: - Click the elipses button next to the WHERE clause box, as shown here:   - In the Expression Builder window, select Physical Tables > CHANNEL_SALES_CHANNEL_VIEW > LEVEL_NAME in the boxes at the bottom of the window. Double-click on the LEVEL_NAME column to create the first part of the where clause, which appears in the Expression box: Then, add an equal sign (=), and the literal value from the OLAP hierarchy view that identifies the level, like this: = 'ALL_CHANNELS'   - Finally, click OK in the Expression Builder to save the WHERE clause in the Logical Table Source - Content tab. Note: To determine the correct value for the LEVEL_NAME column for each hierarchy level, simply use the Physical layer pane. Select View Data from the right-mouse menu for the LEVEL_NAME column in each of the Hierarchy Views. The example below shows how to view the LEVEL_NAME values for the CHANNEL_SALES_CHANNEL_VIEW:   g. Click OK to close the Logical Table Source window. The logical source for the All Channels level in the Channel dimension is complete.   h. Now, double-click on the second logical table source -- CHANNEL_SALES_CHANNEL_VIEW#1 -- and apply the same techniques shown in steps 1a - 1g as follows: General tab: Change the Name to CHANNEL_VIEW (Class). Column Mapping tab: Delete the entry for the Channel Logical Column. Result: The following Logical Columns are still mapped: Content tab: For Channel Dim, select Class in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window.   i. Finally, double-click on the third table source -- CHANNEL_SALES_CHANNEL_VIEW#2 -- and apply the same techniques shown in steps 1a - 1g as follows: General tab: Change the Name to CHANNEL_VIEW (Channel). Column Mapping tab: Leave all of the entries for each Logical Column, as shown here: Content tab: For Channel Dim, select Channel in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window. The logical Channel table should now look like this:
2.	Next, create logical table sources for the levels in the logical Geography table. Then, modify the table sources as specified below. As before, drill on the Sources folder under the logical Geography table. Then, use the same technique as step 1 to create three duplicates of the GEOGRAPHY_REGIONAL_VIEW table source. When you are done, the following table sources should appear: Use the techniques shown in steps 1a. - 1i. to complete the following modifications to the sources for the logical Geography table: a. For GEOGRAPHY_REGIONAL_VIEW: General tab: Change the Name to GEOGRAPHY_VIEW (All Regions). Column Mapping tab: Delete the entries for the following Logical Columns: Region, Country, and State-Province. When complete, the Column Mapping tab should look like this: Content tab: For Geography Dim, select All Regions in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window. Note: The literal values for the LEVEL_NAME column in the GEOGRAPHY_REGIONAL_VIEW are shown here:   b. For GEOGRAPHY_REGIONAL_VIEW#1: General tab: Change the Name to GEOGRAPHY_VIEW (Region). Column Mapping tab: Delete the entries for the Country and State-Province Logical Columns. When complete, the Column Mapping tab should look like this: Content tab: For Geography Dim, select Region in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window.   c. For GEOGRAPHY_REGIONAL_VIEW#2: General tab: Change the Name to GEOGRAPHY_VIEW (Country). Column Mapping tab: Delete the entry for the State-Province Logical Column. When complete, the Column Mapping tab should look like this: Content tab: For Geography Dim, select Country in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window.   d. For GEOGRAPHY_REGIONAL_VIEW#3: General tab: Change the Name to GEOGRAPHY_VIEW (State-Province). Column Mapping tab: Leave all of the entries for each Logical Column, as shown here: Content tab: For Geography Dim, select State-Province in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window. The logical Geography table should now look like this:
3.	Create logical table sources for all of the levels in the logical Product table. Then, modify the table sources as specified below. Since there are six levels in the Product hierarchy, create five duplicates of the original table source. As before, drill on the Sources folder under the logical Product table. Then select Duplicate from the right-mouse menu on the PRODUCT_STANDARD_VIEW table source. When you are done, the following table sources should appear. Next, use the techniques shown above to complete the following modifications to the sources for the logical Product table: a. For PRODUCT_STANDARD_VIEW: General tab: Change the Name to PRODUCT_VIEW (All Products). Column Mapping tab: Delete the entries for the following Logical Columns: Department, Category, Type, Subtype, and Item. When complete, the Column Mapping tab should look like this: Content tab: For Product Dim, select All Products in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window. Note: The literal values for the LEVEL_NAME column in the PRODUCT_STANDARD_VIEW are shown here:   b. For PRODUCT_STANDARD_VIEW#1: General tab: Change the Name to PRODUCT_VIEW (Department). Column Mapping tab: Delete the entries for the following Logical Columns: Category, Type, Subtype, and Item. When complete, the Column Mapping tab should look like this: Content tab: For Product Dim, select Department in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window.   c. For PRODUCT_STANDARD_VIEW#2: General tab: Change the Name to PRODUCT_VIEW (Category). Column Mapping tab: Delete the entries for the following Logical Columns: Type, Subtype, and Item. When complete, the Column Mapping tab should look like this: Content tab: For Product Dim, select Category in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window.   d. For PRODUCT_STANDARD_VIEW#3: General tab: Change the Name to PRODUCT_VIEW (Type). Column Mapping tab: Delete the Subtype and Item Logical Column entries . When complete, the Column Mapping tab should look like this: Content tab: For Product Dim, select Type in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window.   e. For PRODUCT_STANDARD_VIEW#4: General tab: Change the Name to PRODUCT_VIEW (Subtype). Column Mapping tab: Delete the Logical Column Item entry. When complete, the Column Mapping tab should look like this: Content tab: For Product Dim, select Subtype in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown:   f. For PRODUCT_STANDARD_VIEW#5: General tab: Change the Name to PRODUCT_VIEW (Item). Column Mapping tab: Leave all of the entries for each Logical Column, as shown here: Content tab: For Product Dim, select Item in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: The logical Product table should now look like this:
4.	Create logical table sources for all of the levels in the logical Time table. Then, modify the table sources as specified below. Since there are four levels in the Time hierarchy, create three duplicates of the original table source. As before, drill on the Sources folder under the logical Time table. Then select Duplicate from the right-mouse menu on the TIME_CALENDAR_VIEW table source. When you are done, the following table sources should appear. Next, use the techniques shown above to complete the following modifications to the sources for the logical Time table: a. For TIME_CALENDAR_VIEW: General tab: Change the Name to TIME_VIEW (All Years). Column Mapping tab: Delete the entries for the following Logical Columns: Year, Quarter, and Month. When complete, the Column Mapping tab should look like this: Content tab: For Time Dim, select All Years in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window. Note: The literal values for the LEVEL_NAME column in the TIME_CALENDAR_VIEW are shown here:   b. For TIME_CALENDAR_VIEW#1: General tab: Change the Name to TIME_VIEW (Year). Column Mapping tab: Delete the entries for the Quarter and Month Logical Columns. When complete, the Column Mapping tab should look like this: Content tab: For Time Dim, select Year in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window.   c. For TIME_CALENDAR_VIEW#2: General tab: Change the Name to TIME_VIEW (Quarter). Column Mapping tab: Delete the Month Logical Column entry. When complete, the Column Mapping tab should look like this: Content tab: For Time Dim, select Quarterin the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window.   d. For TIME_CALENDAR_VIEW#3: General tab: Change the Name to TIME_VIEW (Month). Column Mapping tab: Leave all of the entries for each Logical Column, as shown here: Content tab: For Time Dim, select Month in the Logical Level box. Then, in the WHERE clause filter box, use the Expression Builder to create the WHERE clause shown: Click OK to close the Logical Table Source window. The logical Time table should now look like this:

Back to Topic

Automatically Add Level Conditions Using Security Filters

Recall that a condition must be applied to all dimensions in the WHERE clause of an OLAP cube query. To accomplish this, you add a Security Filter to the fact table that forces a join between the fact table and the dimension tables.

Using BI EE Security Filters

The BI EE Security Filters feature is used to automatically generate the required level conditions.

	BI EE security filters are applied automatically to queries. These security filters are similar to Oracle Database VPD; they are filters that are automatically applied to any query against the “secured” table.
	A filter condition on each logical fact table ensures that the logical dimension tables are joined to the facts based on the dimension keys. As a result, a star join is forced, causing the level filters to be applied to every query.

A security filter can be applied to a user or a group. In this example, a new group is created, and users of the repository must be assigned to this group.

Use the following steps to create a security group, define the required security filter, and define a user that will automatically use the filter:

Back to Topic

Define the Presentation Layer

In order for users to query the data in a tool such as BI Answers, you must define a Presenation layer, which organizes objects from the business model layer in a user-friendly format.

In the Presentation layer, columns should be orgainzed in such a ways as to make it easy for end-users to navigate the data that they want to access. In addition, you should only expose the logical columns which the users need in order to view data. For example, the DIM_KEY and END_DATE columns are of no value to the end user. Niether are key columns in the logical Facts table.

The presentation of user-visible columns can be quite sophisitcated. However, here you will create a simple presentation catalog.

To define the Presenation layer for the olaptrain business model, perform the following steps:

1.	In the Presentation pane: a. Right-click in the Presentation pane and select New Presentation Catalog from the menu. b. In the Presentation Catalog window, enter Oracle 11g Cubes as the name. By default, the olaptrain business model is selected, as it is the only business model in this repository. c. Click OK to create the new Presentation catalog object.
2.	Drag the following logical tables over to the the OLAP 11g Cubes node in the Presentation pane: Channel, Geography, Product, and Time.
3.	Perform the following modifications to the presentation tables:   Drill on each of the dimension nodes, and delete the following columns in each of the following presentation tables: Presentation Layer Table Column to Delete Channel DIM_KEY Geography DIM_KEY Product DIM_KEY Time DIM_KEY, END_DATE Tthe Presentation pane should should now look like this:
4.	Next, drag the Facts logical table from the Business Model pane to the OLAP 11g Cubes node in the Presentation pane. Then, change the name of the table from Facts to Sales.
5.	Delete the dimension keys from the Sales table, like this:
6.	To organize the OLAP measures for end user purposes, create two copies of the Sales presentaion table. a. First, collapse all of the Presentation tables. b. Then, right-click on Sales and select Duplicate from the menu. c. Create another duplicate of the Sales table using the same technique. The Presentation pane should now look like this:
7.	Delete the following measure columns from the original Sales presentation table: To Go, Cross Over Best Fit Forecast, Cross Over Best Fit Fcst, and % of 2006 Sales.
8.	Rename the Sales#1 presentation table to Forecasts. Then delete all columns in the Forecasts table except for: Cross Over Best Fit Forecast and Cross Over Best Fit Fcst, like this:
9.	Rename the Sales#2 presentation table to Targets. Then delete all columns in the Forecasts table except for: Sales, Sales for 2006, Sales YTD, To Go, and % of 2006 Sales, like this:
10.	To ensure that no errors have been made, click the Save button, and select Yes when prompted to "Check for global consistency". If the metadata model is correct, the following message is displayed: Click Yes. You are ready to move to the next lesson. Notes: - If any errors are returned that refer to either the Business Model or Presenation Table in the Object Type column, correct these errors and then check global consistency again. - Consistency check Warnings can be ignored.

Back to Topic List

More Information

	Download the OLAP11g_OBE.zip file which contains a complete, predefined repository of this lesson. Unzip the repository file (.rpd) into the following location: C:\<OracleBI_Installation_Location>\server\repository The following IDs and passwords are used with this completed repository:     Admin ID/password:     Administrator/Administrator     User ID/Password:        olaptrain/oracle
	To see a demonstration of BI Answers against this OLAP data model, click Fast Answers to Tough Questions Using Simple SQL.
	For hands-on practice with BI Answers against this OLAP data model, click Querying OLAP 11g Data Using Oracle BI Answers.
	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

Back to Topic List

Place the cursor over this icon to hide all screenshots.