Lesson 1: Creating OBIEE Metadata for OLAP Cubes

Purpose

This tutorial covers the creation of Oracle Business Intelligence Enterprise Edition (OBIEE) metadata for access to Oracle Database OLAP Option data and calculations by using the Oracle OLAP Analytic Workspace Manager Plug-in for OBIEE.

NOTE: This tutorial requires either Oracle Database 11.2 or 12.1.

Time to Complete

Approximately 15 minutes.

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Enable the OBIEE Plug-in for AWM

Generate the OBIEE Metadata for Oracle OLAP
Create the OBIEE Metadata Repository
More information

Viewing Screenshots

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

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

Overview

What is Oracle OLAP?

Oracle OLAP is an integrated component of Oracle Database 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 OLAP Cubes tutorial. For information about the OLAP model used in this tutorial, and for step-by-step instructions on how to create OLAP cubes, see the Oracle OLAP Tutorial Series on Oracle Learning Library.

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 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 More information section.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.

Install Oracle Database 11.2 or 12.1 with the OLAP Option, and Analytic Workspace Manager (AWM), 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 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 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

Enable the OBIEE Plug-in for AWM

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.

 
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:

a

 
6.

Click OK.

 
7.

Exit and restart AWM.

Back to Topic

Generate the OBIEE Metadata for Oracle OLAP

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.

a

B. Enter Oracle in the Description field and <hostname>:1521:<SID> in the Connection Information field.

b

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.

2a

  • Then click OK.

 
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

Create the OBIEE Metadata Repository

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:

b OBIEE security filters are applied automatically to queries, forcing a join between the Dimension (or Hierarchy) Views and the Cube Views.
b As a result, a star join is forced, causing the level filters to be applied to every query.
a 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:

e

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

Calculated measures are simply selected as columns. The data is computed in the OLAP calculation engine and passed through the cube view.
Level conditions are applied to all four dimensions, even though only three dimensions are in the SELECT statement (Geography, Product, and Time).
Level conditions are applied at the lowest selected level for each dimension in the query: REGION, DEPARTMENT, and QUARTER.

Since the channel dimension is omitted from the SELECT statement, the 'ALL_CHANNELS' level condition is automatically applied to the query for that dimension. This feature ensures that OLAP cube aggregations are leveraged.

Note: for information on how to manually create SQL queries against OLAP data, see the Oracle OLAP Tutorial Series on Oracle Learning Library.

Back to Topic List

More Information

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 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 the BIEE home page on OTN.

Back to Topic List

Place the cursor over this icon to hide all screenshots.