Creating BI EE 10g Metadata for the Analytic Workspace
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.
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.
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.
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.
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.
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:
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:
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.
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:
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:
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:
Bookmark
