Lesson 1: Preparing an Analytic Workspace for Access by Oracle BI EE 10g

This tutorial covers the creation of a relational view over an analytic workspace using Analytic Workspace Manager (AWM) The relational view presents fully calculated OLAP data to SQL-based tools, such as Oracle Business Intelligence Suite Enterprise Edition 10g.

Approximately 15 minutes.


This tutorial covers the following topics:

Hierarchical Modeling Requirement

Create the Embedded Total View for an AW

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.


Oracle OLAP is an integrated component of Oracle Database 10g 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 both SQL and multidimensional tools

Native multidimensional data types in Oracle database are provided by the analytic workspace (AW). Although there are many data types in an AW, the the dimensional model is essentially made up of Measures, Dimensions, and Cubes.

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. Calculated measures are measures whose values are calculated dynamically at query time. Common calculations include measures such as ratios, differences, time-series (such as lag), 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.
While measures contain or calculate data, and dimensions organize data, 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 AW. A cube is simply a logical object that helps an administrator to build and maintain an AW.

An AW may be accessed both by SQL tools and multidimensional tools alike. To access AW data and leverage the OLAP calculation engine, a SQL tool -- such as Oracle BI EE 10g -- uses the built-in SQL interface to OLAP. The SQL interface to OLAP makes use of a relational view that is created over dimensions and cubes in an analytic workspace. This view presents fully calculated data accross all levels of aggretagation to the SQL tool.

In this tutorial, you will create the relational view over an existing AW by using a Plug-in to Analytic Workspace Manager (AWM)

Back to Topic List

Before starting this tutorial, you should:


Have access to or have Installed Oracle Database Enterprise Edition with Data Warehouse database configuration (preferably


Have access to or have installed the Global sample schema, version The sample is available for download from OTN. This sample includes the Global AW, which is used in this suite of OBE lessons. Download the Global sample schema, and then follow the instructions in Readme_Global_Sample_Schema.html for a "complete" installation.


Have access to or have Installed Oracle Business Intelligence Suite Enterprise Edition 10g Release 3 (version

4. Have access to or have Installed Analytic Workspace Manager (AWM), version, available for download from OTN.
5. Download and install the OLAP View Generator Sample from OTN. This sample is a Plug--in to AWM, enabling creation of embedded total views through a graphical user interface. Follow the instructions found in the OLAPViewGenerator.html file to install the plug-in.

Back to Topic List

Hierarchical Modeling Requirement

As stated previously, the purpose of this lesson is to create a relational view that exposes AW data for access by SQL. Then, in the next lesson you will create BI EE 10g metadata that defines access to this view. When both the relational view and correct metatdata are created, the Oracle BI Server can generate appropriate SQL to access the AW data that is exposed in the view.

In order for the SQL interface to gernerate correct SQL against an AW, the following dimensional modeling technique is employed for all dimension hierarchies in the AW: Dimensions should have a single value at the highest level of each hierarchy.

This requirement is not necessary for tools that use the Oracle OLAP API, but is required for some tools that use the SQL interface, including BI EE 10g.

For example, the standard Time dimension hierarchy in the Global AW includes an "All Years" value:

The same technique is used for all hierarchies in the AW. The Customer dimension contains two hierarchies: a Market Segment hieararchy and a Shipments hierarchy. Both of these hiearchies have a 'Total' value that is used at the highest level of the hierachy.

The Market Segment hierarchy for the Customer dimension contains a "TOTAL_MARKET" level:

The Shipments hierarchy for the Customer dimension contains a "TOTAL_CUSTOMER" level:

Note: In an AW, one hierarchy is always specified as the "Default" hierarchy. For the Customer dimension, the SHIPMENTS hierarchy is the default.

Back to Topic List

Create the Embedded Total View for an AW

To expose the OLAP data for SQL access, a single embedded total view for all aggregations is created. In this view, Dimensions map to the primary key columns while Attributes and Measures map to data columns. The single view contains data for all summary levels. The summary level for a row is determined by dimension level columns.

A single embedded total view approach provides the following benefits:

Simple summary management for ad hoc environments.

Summaries are computed efficiently in OLAP engine based on rules defined in the OLAP model (e.g. sum, last, average, weighted average).

Measure columns return stored and calculated data at all summary levels.

Support for all hierarchy types (skip level, value-based, ragged, etc.).

Simple SQL for complex queries (no aggregation operators required, just select the information you need).

By following step 3 in the Prerequisites section, you have already downloaded and installed the OLAP View Generator sample from OTN. The sample is installed as a Plug-in to AWM

To create the embedded total view for the Global analytic workspace using use the OLAP View Generator Plug-in, perform the following steps.

Back to Topic List

Connect to the GLOBAL analytic workspace in AWM

To open the Global schema in AWM, perform the following steps:


Open AWM, right-click on the Databases node, and select Add Database to Tree from the menu.



In the Add Database to tree dialog, enter global as the name, and provide the appropriate database connection information.

Then, click Create.

Result: A new database node appears in the navigator.



Drill on the global node in the navigator to display the Connect to database dialog. Enter global as both the Username and the Password.

Click OK.

Result: A Schema node appears in the navigator.



Drill on the Schema node. Then, drill the GLOBAL node. Finally drill on the Analytic Wokspaces node.

Result: The GLOBAL Analytic Workspace is displayed in the navigator, as shown here:



Finally, drill on the GLOBAL analytic workspace node.

Result: A tree that displayes the contents of the the GLOBAL Analytic Workspace is displayed in the navigator, as shown here:


Back to Topic

Launch the OLAP View Generator Plug-in

To launch the OLAP View Generator Plug-in, perform the following steps:


In the Navigator, drill on Cubes node. The GLOBAL analytic workspace contains one cube: UNITS_CUBE.



Then, right-click on UNITS_CUBE and select Plug-in - Create Relational View from the menu.

Result: The Create View dialog is diaplayed (shown in the next step).

At this point, you could click Create View to create the embedded total view. However, first you will examine some of the elements of the view before creating the view.


Drill on the View 'units_cube_cubeview' node.

A node is diaplayed for each dimension in the AW, and also for the measures that are associated with UNITS_CUBE.

Next, you will examine some of the columns that are created to support hierachical analysis.

Back to Topic

Examine Hierarchy Levels

When the embedded total view is created, columns are defined that describe the attributes and hierarchies for each dimension. For dimensions that contain multiple hierarchies, unique columns are created to describe all levels in each hierarchy branch.

In this topic, you will examine the columns that will be created for the Time dimension (which contains one hierarchy), and the Customer dimension (which contains two hierarchies). This information will assist you in the creation of BI EE 10g metadata, which is covered in Lesson 2.

Perform the following steps:


In the previous topic, you launched the Create View plug-in, and expanded the navigation tree to display nodes for the measures and each dimension.

Now, drill on the following nodes: Time > Hierarchies > CALENDAR_YEAR.

By default, the Plug-in will create a column for each hierarchy level, using the Description columns, rather than the ID columns. This is a requirement for BI EE metadata creation.

Note: Items that appear as disabled are required columns.



Right-click on Month Description to view a description of that item. (Hint: make sure you right-click. Otherwise, the item will be deselected.)

The description pane shows that a column named TIME_MONTH_LVLDSC will be created to represent this level in the Time hierarchy.


Each level in the hierarchy will be represented in a simiar fashion. Right-click on Quarter Description to view a description of that item.



Now, drill on the following nodes in the navigator: : Customer > Hierarchies > MARKET_SEGMENT. Also, drill on the SHIPMENTS hierarchy. The resulting display should look like this:

As with the Time dimension, a column for each hierarchy level description will be created. Since the Customer dimension has two hierarchies, a column is created for all levels in each hierarchy.



In the MARKET_SEGMENT hierarchy, right-click on Ship To Description, as shown here:

The column CUSTOMER_SHIP_TO_LVLDSC will be created to represent this lowest level in the MARKET_SEGMENT hierarchy.



In the SHIPMENTS hierarchy, right-click on Ship To Description, as shown here:

The column CUSTOMER_SHIP_TO_LVLDSC1 will be created to represent this lowest level in the SHIPMENTS hierarchy. Take note that a "1" has been appended to the "LVLDSC" part of the column name. The View Generator plug-in uses this naming technique in order to uniquely identify leaf-levels that are shared in multiple hierarchies within the same dimension.


When creating views of cubes that contain dimensions with multiple hierarchies, you should make note of this information. You must identify the correct source column when creating the BI EE 10g metadata for each of the dimension hierarchies.

In the next lesson, you will expose only one of the two Customer hierarchies -- the default SHIPMENTS hierarchy. Therefore, you will use the CUSTOMER_SHIP_TO_LVLDSC1 column when identifying the leaf level column for the default Product dimension hierarchy.

Back to Topic

Create the Embedded Total View

Follow these steps to create the embedded total view.


Ensure that all of the hierarchy level description columns for Time and Customer are selected in the Create View dialog box.

Then, click the Create View button.



When the embedded total view is created, the following message is displayed:

Click OK to close the Create View plug-in.


3. Close AWM by selecting File > Exit from the main menu.

Back to Topic

In this lesson, you've learned how to:

Back to Topic List

Back to Topic List

Place the cursor over this icon to hide all screenshots.