Creating OBIEE Metadata for OLAP 11g Cubes
Lesson 1: Creating OBIEE Metadata for OLAP 11g Cubes
This tutorial covers the creation of Oracle Business Intelligence
Enterprise Edition (OBIEE) metadata for access to Oracle Database 11g OLAP Option data
and calculations by using the Oracle OLAP 11g Analytic Workspace Manager Plug-in for OBIEE.
Approximately 15 minutes.
This tutorial covers the following topics:
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.
What is Oracle OLAP?
Oracle OLAP is an integrated component of Oracle Database
11g that enables companies to easily gain insights into business performance.
It offers:
 |
Exceptional
query, calculation and data preparation performance |
|
Rich analytic capabilities |
|
Simple user model that reflects business
usage |
|
Open access to any SQL tool |
Native multidimensional object types in Oracle database are
provided by Oracle OLAP Cubes. Cubes are made up of Measures
and organized by Dimensions.
|
Measures
represent factual data, such as sales, cost, profit, and margin. Measures
may be stored or calculated at query time. Stored measures are loaded and
stored in the database. The values for calculated measures are computed
dynamically by the OLAP calculation engine at query time. Common calculations
include measures such as ratios, differences, time-series, indicies, moving
totals, and averages. Calculations do not require disk storage space, and
they do not extend the processing time required for data maintenance. |
|
Dimensions identify and
categorize your measure data. They shape measures by forming the edges of
the measures. Examples of dimensions include product, geography, time, and
distribution channel. Dimension hierarchies are optional but are
common in OLAP systems. A hierarchy is a logical structure that groups like
members of a dimension together for the purpose of analysis. A dimension’s
structure is organized hierarchically based on parent-child relationships.
These relationships enable navigation between levels, and aggregation from
child values to parent values. |
|
Cubes provide a convenient
way of collecting similar measures of the same dimensionality. It is not
uncommon for many measures to have the same shape, and so by defining their
shape (and other shared characteristics) for a cube, you can save time when
building your OLAP data model. |
To access OLAP cubes and leverage the OLAP calculation engine,
a SQL tool -- such as OBIEE -- uses the built-in SQL interface
to OLAP. Oracle OLAP cube data is made directly accessible to SQL by a set of
relational views. These views represent an OLAP cube as a star schema with the
following characteristics:
- A cube view plays the role of a fact table.
- Dimension views or hierarchy views play the role of dimension
tables.
The star design exposed by OLAP cubes is very similar to traditional
table-based star models. The dimension views form a constellation around one
or more cube views. However, there are two key differences:
- A fact tables in a star schema stores detail data (called
leaves), while a cube view reveals all summary levels defined in the OLAP
cube.
- Calculations in a cube are simply exposed as columns
in the cube view, and the computation for the equations occurs in the OLAP
engine.
Note: The OLAP data for this tutorial was created using steps
found in the Building
OLAP11g Cubes tutorial. For information about the OLAP model used in this
tutorial, and for step-by-step instructions on how to create OLAP 11g cubes,
click the link.
Understanding OBIEE Metadata
In order to use any BI end-user tool that depends on its own
metadata layer (which is the case with OBIEE), the metadata repository
must describe how queries should be constructed against the relational data
sources. With Oracle OLAP data, you must complete the same
metadata administrative tasks that are required for any relational source.
At the highest level, an OBIEE Metadata Repository includes three layers of information:
| 1. |
First, a Physical layer is defined. The metadata layer identifies the source data. |
| 2. |
Second, a Business Model and Mapping
layer is defined. This metadata layer organizes the physical layer into logical categories and
records the appropriate metadata for access to the source data. |
| 3. |
Finally, the Presentation layer is defined. This metadata layer exposes the business model entities for end-user access. |
By using the OBIEE Plug-in for AWM, the three metadata layers for an OBIEE repository are created for you.
To learn more about Oracle Business Intelligence Enterprise
Edition, see More information.
What is the OBIEE Plug-in for AWM?
Analytic Workspace Manager (AWM) is the administrative tool used for managing Oracle OLAP data. The OBIEE Plug-in for AWM allows you to quickly create an OBIEE repository that will allow the OBIEE Server (and therefore any OBIEE client, including Dashboards, Answers, Delivers and the MS Office Plug-in) to query Oracle Database 11g OLAP cubes.
By using the OBIEE Plug-in for AWM, creating the metadata repository is a simple four-step process:
| 1. |
Choose one or more OLAP Cubes in AWM and generate the OBIEE-ready metadata. |
| 2. |
Save the metadata to the System Clipboard (or to a file). |
| 3. |
Copy and paste the metadata into the OBIEE Administration tool. |
| 4. |
Add an OBIEE security policy that leverages the unique aggregation properties of OLAP Cubes. |
In this lesson, you use the OBIEE Plug-in to automatically generate the OBIEE repository UDML code and security policy content. You simply copy and paste this content into the BI Administrator tool, and you are ready to query your OLAP data with OBIEE tools.
The completed metadata repository provides
access an OLAP cube in the sample schema: the SALES_CUBE in the SALESTRACK analytic workspace. This AW is
part of the OLAPTRAIN sample schema.
A completed repository -- which is the ultimate goal of this lesson -- is available for download
in the Related information section.
Back to Topic List
Before starting this tutorial, you should:
| 1. |
Install Oracle Database
11g with the OLAP Option (Patch level 11.1.0.7 or higher), and Analytic Workspace Manager (AWM 11g), which is included with the OLAP Option.
|
| 2. |
Download and install both components of the sample schema
following the instructions in Installing
the Oracle OLAP 11g Sample Schema.
Notes: The Sample Schema installation package includes two parts:
1. Installing the base OLAPTRAIN schema
2. Installing the SALESTRACK analytic workspace in the OLAPTRAIN
schema
|
| 3. |
Have access to or have Installed Oracle
Business Intelligence Suite Enterprise Edition 10g Release
3 (version 10.1.3.4).
Note: You need a general understanding of BI EE administration.
This tutorial only addresses administrative task that are associated
with setting up access to OLAP 11g data.
|
| 4. |
Download the Oracle Business Intelligence Enterprise Edition Plug-in for Analytic Workspace Manager.
The plug-in file, and readme instructions are located on OTN at the Oracle OLAP Downloads page. Go to the Sample Schemas & Code section to download the plug-in and view the readme file.
|
Back to Topic List
As referenced in the OBIEE Plug-in for AWM readme file (see the Prerequisites section), download the OBIEE Plug-in for AWM (obieeplugin.jar) into a plugin subdirectory below the AWM installation directory.
Note: Do not copy the obieeplugin.jar file into the same subdirectory as the awm.jar file.
AWM will recognize the new plug-in the next time it is started once the Enabled Plugins option is selected in the Configuration dialog.
To enable the Plug-ins option in AWM, perform the following steps.
| 1. |
Open AWM 11g.
|
| 2. |
On the main menu, select Tools > Configuration.
|
| 3. |
In the Configuration window, select the Enable plugins option.
|
| 4. |
Also in the Configuration window, double-click in the Value box of the Plugin directory option.
Result: the Open window appears.
|
| 5. |
In the Open window, navigate to and select the directory location for the OBIEE plugin file. For example:
Then, click Open.
The Configuraion window displays the plugin directory path:
|
| 6. |
Click OK.
|
| 7. |
Exit and restart AWM.
|
Back to Topic
To generate the required metadata code for OBIEE using the AWM plugin, perform three simple steps: A) Choose one or more OLAP Cubes; B) Launch the plugin from the right-mouse menu; C) Generate the metadata by clicking a button.
Perform the following steps to generate OBIEE Metadata for the SALES CUBE in the sample schema.
| 1. |
If you have not yet created a database connection for the sample (OLAPTRAIN) schema in AWM, follow these instructions. Otherwise, move to step 2.
A. Right-click Databases and select Add Database to tree.
B. Enter Oracle11g in the Description field and <hostname>:1521:<SID> in the Connection Information field.
C. Click Create.
|
| 2. |
To log in:
- Click the Plus sign (+) next to your database connection.
- In the Connect to database window, enter olaptrain for the Username, and enter the password that you specified when you installed the sample schema.
|
| 3. |
In the AWM navigation pane, expand Schemas > OLAPTRAIN > Analytic Workspaces > SALESTRACK > Cubes.
Result: the navigator window should look like this:
|
| 4. |
Right-click on SALES_CUBE and select Export to OBIEE Administrator from the pop-up menu.
|
| 5. |
In the Export Analytic Workspace To OBIEE Administrator window, note that SALES_CUBE is automatically selected.
Notes:
- You can select other cubes here as well, if required.
- You may provide names for the three BI EE metadata layers in the Export tab (or accept the defaults)
|
| 6. |
Click the Options tab. Here, you specify whether to use Cube and Dimension descriptions or object names in the OBIEE repository.
Select the Descriptions option.
|
| 7. |
Finally, when you generate the required OLAP metadata, you can export to:
- The System Clipboard
- A File
A. Since you will copy the metadata directly to the BI EE Administrator tool, select Export to Clipboard.
Result: A confirmation window appears.
|
| 8. |
Select Yes to view the system clipboard contents.
Result: A metadata document for the SALES_CUBE appears in the System Clipboard window.
The Plug-in creates a single document that contains:
- Instructions (commented out using a # symbol)
- Text for the Security Filter (commented out using a # symbol)
- UDML Code for the physical, business model, and presentation catalog layers
In the next topic, you paste the appropriate contents of metadata document into an OBIEE repository.
Note: leave the System Clipboard window open. |
Back to Topic
To create the OBIEE metadata repository for your OLAP cube, you:
1. |
Create a new repository (or open an existing one) in the BI Administrator tool. |
2. |
Paste the contents of the metadata document into the any box of the metadata repository. |
3. |
Add a security policy to the repository. |
Follow these steps to create theOBIEE metadata repository for the SALES_CUBE:
| 1. |
Open the BI Adminstration tool and create a new repository.
Name the new repository file Sales_Cube.
|
| 2. |
Right-click in any of the repository panes and select Paste from the pop-up menu.
Result: All three of the repository layers are populated.
|
| 3. |
Drill on the SALESTRACK Sales Cube node in all three of the repository panes, and then drill down on the following nodes in each pane to view the metadata components:
- The Physical database contains OLAP Dimension views, Hierarchy Views, and Cube views.
- The Business model is fully configured with Logical Tables, Logical Table Sources, and OBIEE Dimension objects.
- The Presentation catalog represents Dimensions, Levels, Cubes, and all the Measures of the Cube.
|
| 4. |
Save the repository. When prompted to check global consistency, select No.
|
| |
Notes:
Because the Dimension and Cube views contain both leaf and aggregate level data, it's important that OBIEE add filters to the SQL that is used to query these views. These filters specify the desired level of summarization for any query.
In order to make sure that the correct summarization level for each dimension always apply in every query -- even in cases where a dimension is not included in a query -- a security filter is added to the Cube View. This approach is characterized by the following:
|
OBIEE security filters are applied automatically
to queries, forcing a join between the Dimension (or Hierarchy) Views and the Cube Views. |
|
As a result, a star join is forced, causing
the level filters to be applied to every query. |
|
These security filters are similar to Oracle Database VPD; they
are filters that are automatically applied to any query against the “secured”
table. |
A security filter can be applied to a user or a group. In
this example, a new group is created, and users of the repository must be assigned
to this group.
In the following steps, you will:
- Create a new User Group
- Create a new security policy, using the security filter that was generated for you by the AWM / OBIEE plug-in
- Add a new User to the group
|
| 5. |
To create a the new User Group:
A. From the main menu, select Manage > Security.
B. In the Security Manager window, select Action > New > Group.
C. In the Group window, enter OLAPUSERS as the name, and then click Permissions.
|
| 6. |
To create the security policy:
A. In the User/Group Permissions window, select the Filters tab and click the Add button at the bottom of the window.
B. In the Browse window, select the Business Model tab, and then drill on the SALESTRACK Sales Cube node.
Note: Here, you will add the filter to the logical table representing the Cube in the business model.
C. Select the Sales Cube logical table. Notice that the Select button in the right pane is enabled.
D. Click Select.
Result: the User/Group Permissions window now looks like this:
E. Edit the text of the business model by clicking the elipses button (...) next to the Business Model Filter box.
Result: The Expression Builder - Security Filter window appears, as shown here:
F. Toggle back to AWM. Select and copy the text of the security filter that was generated by the AWM plug-in, as shown below.
Important:
- Take care not to include the comment character (# symbol) in your selection, but do include the opening and closing quote marks (").
- Use Ctrl+C for the copy action.
G. Toggle back to the BI Administrator and paste the filter text into the Expression box. The Expression Builder - Security Filter window should now look like this:
H. To save the security filter and the User Group:
- Click OK in the Expression Builder window.
- Click OK in the User/Group Permissions window.
- Click OK in the Group window
|
| 7. |
To add a new User:
A. In the Security Manager window, select Action > New > User.
B. In the User window:
- Enter olaptrain in the User name box
- Specify the password you created for the olaptrain user in the Password and Confirm Password boxes
- Select the OLAPUSERS group option
C. Click OK to save the user.
|
| 8. |
Perform the following:
A. Dismiss the Security Manager window and click Save to save the repository.
B. Click Yes to check global consistency.
Result: The Consistency Check passes with two informational 'warnings'.
C. Close the Consistency Check Manager window.
You are done, and are now ready to access the OLAP Sales Cube using any of the OBIEE user tools, including BI Answers and BI Dashboards.
|
| 9. |
Finally, close the applications by doing the following:
A. Exit the Oracle BI Adminstration Tool.
B. Close the System Clipboard window.
C. Close the Export Analytic Workspace window.
D. Exit Analytic Workspace Manager.
Note: The first topic in the next lesson (Lesson 2: Querying OLAP 11g Data Using Oracle BI Answers), shows you how to deploy the repository that you just created.
|
Back to Topic List
The following report from Oracle BI Answers illustrates the
optimal SQL to leverage OLAP aggregations. The SQL query leverages the security filter that the AWM / OBIEE plug-in generated.
BI Answers Report
SQL Query for the Report (automatically generated by OBIEE)
In the SQL query:
Note: for information on how to manually create SQL queries
against OLAP 11g data, see Querying
OLAP 11g Cubes.
Back to Topic List
|
For a completed repository
for this lesson, download Sales_Cube_OBE.zip.
In the File Download window, select the C:\<OracleBI_Installation_Location>\server\repository
folder as the destination, and click Save. Then, unzIp the archive, which
contains the repository file (.rpd).
The following IDs and passwords are used with this completed repository:
- Admin ID/password: Administrator/Administrator
- User ID/Password: olaptrain/oracle
|
|
For hands-on practice with BI Answers against this OLAP
data model, click Querying OLAP
11g Data Using Oracle BI Answers. |
|
To see a demonstration of BI Answers against this OLAP
data model, click Fast
Answers to Tough Questions Using Simple SQL. |
|
To learn more about Oracle Business Intelligence Enterprise
Edition, refer to additional OBEs on the OTN
Web site. From this page, select Business Intelligence Start
> Oracle BI Enterprise Edition
|
Back to Topic List
Place the cursor over this icon to hide all screenshots.
|
Bookmark
