Lesson 2: Creating BI EE 10g Metadata for the Analytic Workspace

Purpose

This tutorial covers the creation of metadata for access to analytic workspace data and the OLAP engine. You will use the Oracle BI Administrative Tool to create the required metadata.

Time to Complete

Approximately 1 hour.

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Define the Physical Layer

Define Standard Business Model Metadata
Define Additional Metadata to Leverage ETV Aggregations
Specify a Complex Join on the Logical Tables

Define the Presentation Layer
Summary
Related information

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

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

1.

First, you define a Physical layer, which identifies the source data.
2. Second, you define the Business Model and Mapping layer, which organizes the physical layer into logical categories and records the appropriate metadata for access to the source data.
3. Third, you define the Presentation layer, which exposes the business model entities for end-user access.

In this lesson, you will define all three layers of metadata for access to the Global AW.

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:

Have successfully completed the following tutorial: Lesson 1: Preparing an Analytic Workspace for Access by Oracle BI EE 10g.

Back to Topic List

Define the Physical Layer

The first step in defining BI EE 10g metadata for the AW is to import the embedded total view (ETV) into the Physical Layer. This step identifies the ETV as the data source though which the AW is accessed.

In this topic, you will (optionally) create an ODBC Data Source, import the ETV into the Physical Layer, and then test access to the imported data.

In order to define the Physical Layer for access to the AW, you must first have an ODBC Data Source defined. The first subtopic shows how to create an ODBC Data Source for the Oracle 10g database. If you already have the appropriate ODBC Data Source created, move to the second subtopic.

Create an ODBC Data Source
Import the Embedded Total View
Test the Import

Back to Topic List

Create an ODBC Data Source

In order to connect to an Oracle data source for purposes of metadata creation, the BI EE 10g Administration Tool uses an ODBC connection. If you have not yet created an ODBC Data Source for your Oracle 10g database, follow these steps.

Note: In this example, an ODBC Data Source for Microsoft Windows XP will be used.

1.

Click the Start button, and then select Settings > Control Panel.

 
2.

Double-click the Administrative Tools icon, and then the Data Sources (ODBC) icon.
3.

In the ODBC Data Sources Administrator, click the System DSN tab. The window should look something like this:

Click the Add button.

 
4.

In the Create New Data Source wizard, select the appropriate driver for the Oracle 10g database, and click Finish.

 
5.

In the Oracle ODBC Driver Configuration window, provide a name and description, specify the TNS service name, and enter global as the User ID.

Then, click the Test Connection button.

Result: an Oracle ODBC Driver Connect window is displayed.

 
6.

In the Oracle ODBC Driver Connect window, enter global as the password, and click OK.

Result: If the test is succesful, the following window appears. If the test is unsuccesful, reenter the correct password.

 
7.

Click OK to close the Oracle ODBC Driver Connect window. The System DSN tab of the ODBC Data Source Administrator should now show the Oracle data source, like this:

 
8. Click OK to close the Oracle Data Source Administrator.

Back to Topic

Import the Embedded Total View

To import the ETV, perform the following steps.

1.

Open the Oracle BI Administration Tool.

Hint: Using the Start menu, select Programs > Oracle Business Intelligence > Administration.

 
2.

In the Administration Tool, select File > New. In the New Repository window. Enter global.rpd as the file name and click Save.

Result: a new repository named global.rpd is displayed.

 
3.

Select File > Import > from Database.

 
4.

In the Select Data Source window, click the Oracle DSN connection. The User Name field is automatically filled with "global".

Enter global as the password, and click OK.

 
5.

In the Import window, ensure that both the Tables and Views options are selected. Then, drill on the GLOBAL node, like this:

 
6.

Scroll down in the list of GLOBAL tables and view, select UNITS_CUBE_CUBEVIEW.

Click Import.

Result: The Connection Pool window is displayed.

 
7.

In the Connection Pool window, ensure that the Default (OCI 10g) call interface is selected, and that the Data source name field is set to the TNS service name for your Oracle Database connection.

In this example, the TNS service name is orcl.

Hint: the service name is the same value that you specified when creating the ODBC Data Source connection.

Accept the other default settings and click OK. Once the import is finished, close the Import window.

 

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, preform the following steps.

1.

In the Physical layer pane, drill on the Oracle node, and then drill on the GLOBAL node.

Result: the OLAP embedded total view is displayed as a node. Drill on the UNITS_CUBE_CUBEVIEW node to display the view's columns.

 
2.

Right-click on the CUSTOMER_LEVEL column and select View Data from the menu. The following window should be displayed.

Data is returned for the column, which confirms that the import worked correctly.

Click Close.

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

Perform the following steps to create logical tables 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 - Global window, enter Global as the name, and click OK.

 

2.

Create a logical table for the measures by peforming the following steps:

A. Right-click on the Global 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 Global 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 Customer dimension:

A. Right-click on the Global business model, and select New Object > Logical Table from the menu.
B. In the General tab of the Logical Table window, enter Customer as the name.
C. Click OK.

 
5.

Create a logical table for the Product dimension:

A. Right-click on the Global 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 Global 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 will map columns from the Physical layer to the Business Model layer. Measures from the ETV will be mapped to the logical Fact table, and certain columns that describe the dimensions in the ETV will be mapped to the logical dimension tables.

For each logical dimension table, you will map the appropriate ETV description column for each hierarchical level, and also the "_LEVEL" column for that dimension. (The LEVEL column(s) describe the level of aggregation for a row).

1.

First, click the collapse (-) symbol next to each of the logical tables, so that the display looks like this:

Drag the COST 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 COST appears under the Facts table.

 

2.

In the Physical layer pane, scroll down so that all of the columns that begin with "PROFIT" are displayed. Holding down the Shitt key, select all of the profit measures, like this:

Still holding down the Shift key, drag and drop the profit measures on the Facts table as before.

Note: Drop the columns on the logical Facts table, and not on the Sources folder.

 
3.

Using the same technique as above, select all of the columns that being with "SALES". Also select the UNITS column at the bottom of the Physical layer pane. As before, drag these measures to the Facts table.

The Facts table should now contain the following columns:

Finally, set the Aggregation Rule for all measures to Sum. This aggregation rule assignment is performed so that BI EE recognizes the column as a measure. The data is already aggregated (in the AW or by the OLAP engine). In fact, this methodology produces results that 'aggregate' a single row, so that measures such as percentages will be correct.

To assign the aggregation rule to all measures, perform the following:

A. Under the Facts logical table, select all of the measures. Then right-click and select Set Aggregation from the menu.
B.

In the Aggregation window, select the All columns the same option, and choose Sum in the Default aggregation rule drop-down, as shown here:

 
C. Click OK.

The list of measures should look like this:

Mapping for the logical Facts table is complete. Click the collapse (-) symbol next to Facts.

 
4.

In the following order, drag these columns from the Physical layer pane to the logical Channel logical table: CHANNEL_TOTAL_CHAN_LVLDSC, CHANNEL_CHANNEL_LVLDSC, and CHANNEL_LEVEL.

When you are done, the display should look like this:

Note: As stated previously, you map the level description columns for each dimension level, rather than the ID columns.

 
5.

Next, rename the logical hierarchy level description columns as follows:

A.

Double-click on the CHANNEL_TOTAL_CHAN_LVLDSC column under the logical Channel table. In the General tab of the Logical Column window, change the name to Total Channel, as shown here:

Then, click OK.

 
B.

In the same way, change the name of CHANNEL_CHANNEL_LVLDSC to Channel.

The logical Channel table should now look like this:

 
6.

Next, map the columns for the Shipments hierarchy in the Customer dimension.

As you may recall from the previous lesson, the Customer dimension has two hierarchies, as shown below.

Although both hiearchies can be added to the business model, you will map only the Shipments hierarchy, which is the default hierarchy in the AW. Recall from the previous lesson, that the description column for the lowest level ("Ship To") in the Shipments hierarchy is named CUSTOMER_SHIP_TO_LVLDSC1.

A.

Using the same techniques described in Step 5., drag the following columns from the Physical layer to the logical Customer table -- in the order listed. Then, rename each of the columns in the logical Customer table as shown.
  Physical Layer Column New Logical Column Name
  CUSTOMER_TOTAL_CUST_LVLDSC Total Customer
  CUSTOMER_REGION_LVLDSC Region
  CUSTOMER_WAREHOUSE_LVLDSC Warehouse
  CUSTOMER_SHIP_TO_LVLDSC1 Ship To
 

Notes: Map each column individually in order. This approach will simplify a metadata definition task that follows later. Make sure to drop the columns on the logical Customer table, and not on the Sources folder.
B.

Next, drag the CUSTOMER_LEVEL column from the Physical layer to the logical Customer table.

When you have completed 6.A and 6.B, the logical Customer table should look like this:

 
7.

Next, map the columns for the Product dimension.

Drag and then rename the following columns from the Physical pane to the logcial Product table, in the following order:

Physical Layer Column New Logical Column Name
PRODUCT_TOTAL_PROD_LVLDSC Total Product
PRODUCT_CLASS_LVLDSC Class
PRODUCT_FAMILY_LVLDSC Family
PRODUCT_ITEM_LVLDSC Item

Then, drag the PRODUCT_LEVEL column from the Physical layer to the logical Product table.

When you are done, the logical Product table should look like this:

 
8.

Next, map the columns for the Time dimension.

Drag and then rename the following columns from the Physical pane to the logcial Time table, in the following order:

Physical Layer Column New Logical Column Name
TIME_ALL_YEARS_LVLDSC All Years
TIME_YEAR_LVLDSC Year
TIME_QUARTER_LVLDSC Quarter
TIME_MONTH_LVLDSC Month

Next, drag the TIME_LEVEL and TIME_END_DATE columns from the Physical layer to the logical Time table.

Note: the TIME_END_DATE column will be used to specify the default sort order for the logical Month column. 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, first double-click on the logical Month column under the logical Time table. Then, in the Logical Column Window, click the Set button next to the Sort order column box. In the Browse window, select the TIME_END_DATE column and click OK.

The sort order column is set. Finally, click OK to close the Logical Column window.

When you are done, the logical Time table should look like this:

 
9.

Finally, assign a primary key for each logical dimension table.

A.

Double-click the logical Channel table. In the Keys tab, click New. Then, select the Channel column, as shown here:

Click OK to assign Channel as the primary Key. Then, click OK to close the Logical Table - Channel window.

The logical Channel table should now look like this:

 
B. Using the same technique as in 9.A, assign Ship To as the primary key for the logical Customer table.
C. For the logical Product table, assign Item as the primary key.
D. For the logical Time table, assign Month as the primary key.

The display should look like this:

 

Back to Topic

Enable Drilling by Dimension Level

In this topic, you define additional metadata that enables drilling by dimemsion level.

First, define Dimension objects and their associated Level objects. Then, map the columns from the logical dimension tables to the associated dimension object levels.

1.

In the Business Model and Mapping pane, create a Dimension object for each of the logical "dimension" tables.

A. Right-click on the Global business model, 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. A Dimension object appears under the Global business model.

 
D.

Using the same technique described in 1.A - 1.C, create three more dimension objects named: Customer Dim, Product Dim, and Time Dim.

When you are finished, the Global 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 Total Channel as the name and click OK. A new Level appears below Channel Dim.

 
C.

Right-click on the Total Channel level object that you just created, and select New Object > Child Level from the menu.
D.

In the General tab of the Logical Level window, enter Channel as the name and click OK. A new Level appears below Total Channel.

Notes:

The top level for each dimension hierarchy is created using the technique described in 2.A - 2.B.

Subsequent child level for all hierarchies are created using the technique descibed in 2.C - 2.D.

E.

Using the techniques described in 2.A - 2.D, create hierarchy levels under the Customer Dim object for the default hierarchy, like this:

Total Customer > Region > Warehouse > Ship To

When you are done, Customer Dim should look like this:

 
F.

Using the techniques described in 2.A - 2.D, create the following levels under the Product Dim object:

Total Product > Class > Family > Item

When completed, the Product Dim object should look like this:

 
G.

Using the techniques described in 2.A - 2.D, 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.

Within the Business Model and Mapping pane, drag the columns from the logical tables to the associated dimension object levels. This provides the required mapping information for drilling purposes.

Then, assign each dimension level column as a 'Logical Key'.

For the Channel dimension, perform the following:

A. Expand all nodes in Channel Dim, and expand the Channel logical table node.
B. Drag and drop the Total Channel column from the logical Channel table to the Total Channel level inside the Channel Dim object. A Total Channel column appears directly below the Total Channel level.
C.

Repeat step 3.B for the Channel column. A Channel column appears directly below the Channel level. The mapping technique, and resulting display is illustrated here:

 
D.

Right-click on the Total Channel column inside the Total Channel level, and select New Logical Level Key from the menu. In the Logical Level Key dialog, click OK.

 
E. Using the same technique described in 3.D, assign the Channel level column as a Logical Level Key.

When the mapping and logical key assignments are complete, Channel Dim should look like this:

 
4.

Use the same mapping technique as described in step 3 to map all columns from the logical Customer table to the associated levels in the Customer 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, the display should look like this:

Back to Topic

Define Additional Metadata to Leverage ETV Aggregations

Next, additional metadata is defined in the Business Model and Mapping layer. This additional metadata is required so that 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, OLAP calculations on aggregate data occur in Oracle OLAP.

The following report illustrates the optimal SQL to leverage OLAP aggregations.

In the SQL query:

Level conditions are applied to all four dimensions, even though only three dimensions are in the SELECT statement (Product, Customer, and Time).
Level conditions are applied at the lowest selected level for each dimension in the query: FAMILY, REGION, and QUARTER.

Since the channel dimension is omitted from the SELECT statement, the 'TOTAL_CHANNEL' level condition is automatically applied to the query for that dimension.

To ensure that BI EE generates SQL which is optimized for OLAP, the following additional metadata creation tasks are required: (A) Make the logical dimension tables “Level-aware”, by setting up logical table sources for each dimension level; (B) Create a logical table and Dimension object that describe the dimension levels; and (C) Force the level conditions to be applied to every query, by utilizing BI EE security filters (which is conceptually similar to Oracle VPD policy).

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

Define a Table Source for Each Dimension Level
Create a Logical Table and a Dimension Object Describing Dimension Levels
Provide Filter Conditions for the Logical Dimension and Fact Tables

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.

1.

First, create logical table sources for all of the levels in the logical Channel table. Then, modify the table sources as specified below.

To begin, drill on the Sources folder under the logical Channel table. Then, right-click on the UNITS_CUBE_CUBEVIEW table source and select Duplicate from the menu. A second table source appears.

Since the Channel dimension only contains two levels, you have the number of table sources that you need.

Modify the table sources as follows:

A. Double-click the UNITS_CUBE_CUBEVIEW table source. The Logical Table Source window appears.
B. In the General tab, enter Total Channel in the name field.
C.

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. In addition, the Column Mappings tab is used to specify the literal value for the column level in the <DIMENSION>_LEVEL Expression field.

In the Column Mappings tab, find the Logical Column for Channel. (The Expression that is associated with this column is "CHANNEL_CHANNEL_LVLDSC'.)

Since this column is at a grain level below Total Channel, you must delete the associated Expression value and the associated Physical Table value. Click the "X" icon for the Expression column next to CHANNEL_CHANNEL_LVLDSC.

This action deletes both the Expression value and the Physical Table value, and moves the Channel logical column down, as shown here:

Then, change the Expression value for the CHANNEL_LEVEL logical column to the literal value for the Total Channel level. This value is: 'TOTAL_CHANNEL' (single quotes are required). When you move out of the Expression field, the Physical Table value is deleted.

Note: You can verify the literal values for any <DIMENSION>_LEVEL column by selecting View Column from the right-mouse menu on that column in the Physical layer. For example, the Physical layer CHANNEL_LEVEL column contains these literal values:

 
D.

Next, click on the Content tab. Select the appropriate Logical Level for the current Dimension object. In this case, for Channel Dim, select Total Channel from the Logical Level drop-down list.

 
E. Click OK to close the Logical Table Source window.
F.

Now, double-click on the second table source -- UNITS_CUBE_CUBEVIEW#1 -- and apply the same techniques shown in steps 1.A - 1.D as follows:

General tab: Change the Name to Channel.

Column Mapping tab: Change the Expression for the CHANNEL_LEVEL column to: 'CHANNEL'. When complete, the Column Mapping tab should look like this:

Note: The Expression and Physical Table mapping for both the Channel and Total Channel logical columns are retained. This is correct. You should only delete the mappings for levels below the current grain.

Content tab: Select Channel from the Logical level drop down list for Channel Dim, like this:

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 Customer table. Then, modify the table sources as specified below.

As before, drill on the Sources folder under the logical Customer table. Then, use the same technique as step 1 to create three duplicates of the UNITS_CUBE_CUBEVIEW table source. When you are done, the following table sources should appear:

Use the techniques shown in steps 1.A - 1.E to complete the following modifications to the sources for the logical Customer table:

A.

For UNITS_CUBE_CUBEVIEW:

General tab: Change the Name to Total Customer.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the CUSTOMER_LEVEL column to: 'TOTAL_CUSTOMER'. When complete, the Column Mapping tab should look like this:

Note: The literal values for the CUSTOMER_LEVEL column in the Physical layer are shown here:

Content tab: Select Total Customer from the Logical level drop down list for Customer Dim.

Click OK to close the Logical Table Source window.

 
B.

For UNITS_CUBE_CUBEVIEW#1:

General tab: Change the Name to Region.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the CUSTOMER_LEVEL column to: 'REGION'. When complete, the Column Mapping tab should look like this:

Content tab: Select Region from the Logical level drop down list for Customer Dim.

Click OK to close the Logical Table Source window.

 
C.

For UNITS_CUBE_CUBEVIEW#2:

General tab: Change the Name to Warehouse.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the CUSTOMER_LEVEL column to: 'WAREHOUSE'. When complete, the Column Mapping tab should look like this:

Content tab: Select Warehouse from the Logical level drop down list for Customer Dim.

Click OK to close the Logical Table Source window.

 
D.

For UNITS_CUBE_CUBEVIEW#3:

General tab: Change the Name to Ship To.

Column Mapping tab: Change the Expression for the CUSTOMER_LEVEL column to: 'SHIP_TO'. When complete, the Column Mapping tab should look like this:

Content tab: Select Ship To from the Logical level drop down list for Customer Dim.

Click OK to close the Logical Table Source window.

The logical Customer 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 four levels in the Product hierarchy, create three 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 UNITS_CUBE_CUBEVIEW 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 UNITS_CUBE_CUBEVIEW:

General tab: Change the Name to Total Product.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the PRODUCT_LEVEL column to: 'TOTAL_PRODUCT'. When complete, the Column Mapping tab should look like this:

Content tab: Select Total Product from the Logical level drop down list for Product Dim.

Click OK to close the Logical Table Source window.

 
B.

For UNITS_CUBE_CUBEVIEW#1:

General tab: Change the Name to Class.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the PRODUCT_LEVEL column to: 'CLASS'. When complete, the Column Mapping tab should look like this:

Content tab: Select Class from the Logical level drop down list for Product Dim.

Click OK to close the Logical Table Source window.

 
C.

For UNITS_CUBE_CUBEVIEW#2:

General tab: Change the Name to Family.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the PRODUCT_LEVEL column to: 'FAMILY'. When complete, the Column Mapping tab should look like this:

Content tab: Select Family from the Logical level drop down list for Product Dim.

Click OK to close the Logical Table Source window.

 
D.

For UNITS_CUBE_CUBEVIEW#3:

General tab: Change the Name to Item.

Column Mapping tab: Change the Expression for the PRODUCT_LEVEL column to: 'ITEM' and then move out of the column. When complete, the Column Mapping tab should look like this:

Content tab: Select Item from the Logical level drop down list for Product Dim.

Click OK to close the Logical Table Source window.

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 UNITS_CUBE_CUBEVIEW 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 UNITS_CUBE_CUBEVIEW:

General tab: Change the Name to All Years.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the TIME_LEVEL column to: 'ALL_YEARS'. When complete, the Column Mapping tab should look like this:

Content tab: Select All Years from the Logical level drop down list for Time Dim.

Click OK to close the Logical Table Source window.

 
B.

For UNITS_CUBE_CUBEVIEW#1:

General tab: Change the Name to Year.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the TIME_LEVEL column to: 'YEAR'. When complete, the Column Mapping tab should look like this:

Content tab: Select Year from the Logical level drop down list for Time Dim.

Click OK to close the Logical Table Source window.

 
C.

For UNITS_CUBE_CUBEVIEW#2:

General tab: Change the Name to Quarter.

Column Mapping tab: Delete mappings for the columns shown below, and change the Expression for the TIME_LEVEL column to: 'QUARTER'. When complete, the Column Mapping tab should look like this:

Content tab: Select Quarter from the Logical level drop down list for Time Dim.

Click OK to close the Logical Table Source window.

 
D.

For UNITS_CUBE_CUBEVIEW#3:

General tab: Change the Name to Month.

Column Mapping tab: Change the Expression for the TIME_LEVEL column to: 'MONTH' and move out of that column. When complete, the Column Mapping tab should look like this:

Content tab: Select Month from the Logical level drop down list for Time Dim.

Click OK to close the Logical Table Source window.

The logical Time table should now look like this:

 

Back to Topic

Create a Logical Table and a Dimension Object Describing Dimension Levels

You have just finished making the logical dimension tables “Level-aware”, by setting up logical table sources for each dimension level.

However, you still need to add metadata that forces level conditions to be applied to every query in the WHERE clause. This is actually a two-part process, which includes:

A.

Creating a Logical Table and a Dimension object that specifically describe the dimension levels. This part is covered in this topic.

B. Provide filter conditions for each dimension, utilizing BI EE security filters. This part is covered in the next topic.

Follow these steps to create a Logical Table and a Dimension object that describe the dimension levels.

1.

Perform the following to create the “Dimension Levels” logical table:

A. Right-click on the Global business model, and select New Object > Logical Table from the menu.
B. In the General tab of the Logical Table window, enter Dimension Levels as the name.
C. Click OK.

The display looks like this:

 
2.

Drag the following columns from the Physical Layer pane to the Dimension Levels logical table:

CHANNEL_LEVEL
CUSTOMER_LEVEL
PRODUCT_LEVEL
TIME_LEVEL

When done, the Dimension Levels logical table should look like this:
3.

Double-click on the Dimension Levels logical table to display the Logcial Table - Dimension Levels window. In the Keys tab, click New.

In the Logical Key dialog, select all of the Level columns, as shown below, and click OK.

Then, click OK in the Logical Table - Dimension Levels window. The display should now look like this:

 
4.

Perform the following to create the Dimension object that describes the "_LEVEL" columns:

A. Right-click on the Global business model, and select New Object > Dimension from the menu.
B. In the General tab of the Logical Table window, enter Levels Dim as the name.
C. Click OK. Result: The new Dimension object appears in the display.
D.

Create one Logical Level placeholder for the Dimension Level columns by performing the following: Right-click the Levels Dim object and select New Object > Logical Level. Name the logical level Detail and click OK. The Levels Dim object now looks like this:

 
E.

Map all of the logical Dimension Levels columns by dragging all four _LEVEL columns from the Dimension Levels logical table to the Detail logical level in the Levels Dim object, like this:

 
F.

Specify that the "_LEVEL" columns in the Levels Dim object should be Keys by performing the following:

1. Right-click on CHANNEL_LEVEL column and select New Logical Level Key from the menu.

2. Select all four LEVEL columns, and then click OK in the Logical Level Key window.

When you are done, the display should look like this:

 
5.

In the previous topic, you defined table sources for each of the dimension levels.

Now that you have created a Logical Table and a Dimension object that describe the dimension levels, you are ready to modify the table source for the logical Facts table, and the table source for the Dimension Levels table.

In the Facts table source, you must specify the lowest hierarchical level as the Logical Level for each Dimension object. To do this, follow these instructions:

A. Drill on Facts > Sources, and then double-click on UNITS_CUBE_CUBEVIEW.
B. In the Logical Table Source window, select the Content tab.
C.

For each Dimension, select the lowest hierarchical level from the Logical Level drop down, as shown here:

 
D. Click OK to save your changes.

In the Dimension Levels source, you must specify the 'Detail' level as the Logical Level, as described here:

E. Drill on Dimension Levels > Sources, and then double-click on UNITS_CUBE_CUBEVIEW.
F. In the Logical Table Source window, select the Content tab.
G.

Select Detail from the Logical Level drop-down list for Levels Dim, as shown here:

 
H. Click OK.

Back to Topic

Provide Filter Conditions for the Logical Dimension and Fact Tables

Now, you will finish the task of enforcing appropriate level conditions for all queries by providing a specific filter condition for each logical dimension, and the logical Facts table, in the business model. The filter conditions will effectively “join” the logical dimension tables to the facts based on the level columns. The same filter condition will be applied to each of the logical tables.

The BI EE Security Filters feature will be used to create the required filters. A Security Filter is always assocated with a Security Group. However, since security filters are not applied to any user in the Administrators group, a new group must be created, and users of the Global repository must be assigned to this group.

1.

From the main menu, select Manage > Security. The Security Manager window appears.

 
2.

In the Security Manager window, select Action > New > Group. In the Group window, enter Global Users as the name, and then click Permissions.
3.

In the Permissions window, select the Filters tab and then click the Add button.

 
4.

In the Browse window, select the Business Model tab. Drill on the Global node then select all of the logical tables except Dimension Levels, as shown below. Then click the Select button.

Result: the Permissions window appears, like this:

 
5.

Next, click on the first elipses button (...) under the Business Model Filter column. The Expression Builder - Security Filter window is displayed.

 
6.

In the Expression Builder window, create the following filter:

Note: You can use the panes and operators at the bottom of the Expression Buider window to select and insert the appropriate logical columns and operators as you construct the filter.

Once you have the filter complete, select the entire filter and copy it to the clipboard.

Then, click OK. The Permissions window now looks something like this:

 
7.

Paste the filter for each of the remaining logical tables by performing the following:

A. Click the elipses button (...) for the next empty Business Model Filter column.
B. Paste the filter into the Expression Builder results pane.
C. Click OK.

Repeat this process to complete the filter creation for all of the logical tables. When you are done, the Permissions window should look something like this:

This strategy ensures that the same filter will be applied when any one of these logical tables are selected by an end user.

 
8.

Click OK to close the Permissions window. Then click OK to close the Groups window.

 
9.

Now, you wil add a user to the Global Users group.

Actual implementation should integrate Authentication and Authorization procedures as required. However, for the sake of simplicity, you will add a user to the Global Users group using the BI Administration tool.

In the Security Manager window, select Action > New > User. In the User window, enter global as the user name and select the Global Users group.

Then, click OK.

Finally, close the Security Manager window.

 

Back to Topic

Specify a Complex Join on the Logical Tables

There is one more metadata creation task before you define the Presentation Layer. This task could have been completed ealier in the process. Using the Logical Table Diagram window, create a complex join on the logical tables to create a logical star.

1.

Right-click on any one of the logical tables and select Business Model Diagram > Whole Diagram from the menu.

The Logical Table Diagram window opens. Arrange the logical tables into a 'star', something like this:

 
2.

Create logical joins between the dimension tables (including the Dimension Levels table) 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 the Dimension Levels table. 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. The Global business model should now look like this:

The Business Model is complete.

Back to Topic List

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 Dimension Levels columns are of no value to the end user. 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 Global business model, perform the following steps:

1.

Right-click in the Presentation pane and select New Presentation Catalog from the menu.

In the Presentation Catalog window, enter Global as the name. By default, the Global business model is selected, as it is the only business model in this repository. Click OK to create the new Presentation catalog object.
2.

Drag the following logical tables over to the Global presentation catalog: Channel, Customer, Facts, Product, and Time.

 
3.

Perform the following modifications to the presentation tables:

A.

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 CHANNEL_LEVEL
Customer CUSTOMER_LEVEL
Product PRODUCT_LEVEL
Time TIME_LEVEL, TIME_END_DATE
B. Next, double-click on Facts to open the Presentation Tables window. Change the name to Measures and click OK.

Tthe Presentation pane should should now look like this:

 
4.

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

Summary

In this lesson, you've learned how to:

Import the Embedded Total View into the Physical Layer.
Create the required Business Model metadata for access OLAP data in the ETV.
Create a Presentation catalog for end-user access.

Back to Topic List

Related Information

A completed repository zip file for this lesson can be downloaded from here. In the File Download window, click Save. Then, select the C:\OracleBI\server\Repository folder as the destination.
To learn more about Oracle Business Intelligence Enterprise Edition, refer to additional OBEs on the OTN Web site. From this page, select Business Intelligence Start > Oracle BI Enterprise Edition (EE-10.1.3.2)

Back to Topic List

Place the cursor over this icon to hide all screenshots.