Preparing an Analytic Workspace for Access by Oracle BI EE 10g
Lesson 1: Preparing an Analytic Workspace for Access
by Oracle BI EE 10g
Purpose
This tutorial covers the creation of a relational view over an
analytic workspace using Analytic Workspace Manager (AWM) 10.2.0.3. The relational
view presents fully calculated OLAP data to SQL-based tools, such as Oracle
Business Intelligence Suite Enterprise Edition 10g.
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
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) 10.2.0.3.
Have access to or have
Installed Oracle Database 10.2.0.1.0 Enterprise Edition with Data Warehouse
database configuration (preferably 10.2.0.3.0).
2.
Have access to or have installed the
Global sample schema, version 10.2.0.3.0. 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.
3.
Have access to or have Installed Oracle
Business Intelligence Suite Enterprise Edition 10g Release
3 (version 10.1.3.2.0).
4.
Have access to or have Installed Analytic
Workspace Manager (AWM), version 10.2.0.3, available for download from
OTN.
5.
Download and install the OLAP View Generator
Sample from OTN.
This sample is a Plug--in to AWM 10.2.0.3.0, 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.
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.
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 10.2.0.3.
To create the embedded total view for the Global analytic workspace
using use the OLAP View Generator Plug-in, perform the following steps.
To launch the OLAP View Generator Plug-in, perform the following
steps:
1.
In the Navigator, drill on Cubes node. The GLOBAL
analytic workspace contains one cube: UNITS_CUBE.
2.
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.
3.
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.
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:
1.
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.
2.
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.
3.
Each level in the hierarchy will be represented in a simiar fashion.
Right-click on Quarter Description to view a description
of that item.
4.
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.
5.
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.
6.
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.
Notes:
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.
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
