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:
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.
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:
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.
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:
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.
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)
Bookmark
