1 Creating Analytic Workspaces with DBMS_AWM

The DBMS_AWM package provides stored procedures for creating an analytic workspace cube from a star schema and enabling it for access by the OLAP API. The DBMS_AWM package is used by Analytic Workspace Manager. This chapter explains how to work with the DBMS_AWM procedures directly.

1.1 Overview

If your data is stored in a star or snowflake schema, then you can use the DBMS_AWM package to simplify the process of loading it into an analytic workspace.

The first step is to create OLAP Catalog metadata that describes the functionality of your schema in multidimensional terms, that is, as a cube with dimensions, attributes, and measures. You can then use the DBMS_AWM package to instantiate these objects in an analytic workspace, create relational views of the workspace objects, and optionally generate a secondary set of OLAP Catalog metadata that maps to the workspace views.

Note:

Analytic workspaces created by the DBMS_AWM procedures are in database standard form, ensuring compatibility with related Oracle OLAP tools and utilities. See Oracle OLAP Application Developer's Guide for information about standard form.

The DBMS_AWM package provides a feature–rich set of APIs that you can use to manage analytic workspaces. To effectively use these APIs, you will need to understand how the APIs work together to move data from a relational source to a multidimensional target and how they establish relational access to that target.

The basic flow of events involves the creation of three separate logical cubes:

Relational Source Cube. This cube must exist before you call any of the DBMS_AWM procedures. The cube's metadata is defined within the OLAP Catalog. Its data is unsolved (lowest level only) and stored in a star schema.

Multidimensional Target Cube. DBMS_AWM procedures define and populate this cube from the relational source cube. The cube's standard form metadata is defined in the analytic workspace. Its data is stored in the workspace, typically with full or partial summarization.

Relational Target Cube. DBMS_AWM procedures define this cube from the multidimensional target cube. The cube's metadata is defined within the OLAP Catalog. Its data is stored in the analytic workspace and accessed through relational views. The views present the data as fully solved (embedded totals for all level combinations).

The basic process of creating and enabling an analytic workspace with the DBMS_AWM package is illustrated in Figure 1-1.

1.1.1 Creating OLAP Catalog Metadata for the Source Cube

Before you can use the DBMS_AWM procedures, you must create a cube in the OLAP Catalog and map it to the source fact table and dimension tables. The source tables must be organized in a basic star or snowflake schema.

You can use Enterprise Manager, or you can write scripts that use the CWM2 PL/SQL packages, as described in Chapter 2. You can also use Oracle Warehouse Builder to create OLAP Catalog metadata.

1.1.2 Creating and Populating Workspace Dimensions

For each dimension of a cube defined in the OLAP Catalog, you must run a set of procedures in the DBMS_AWM package to accomplish the following general tasks:

Create a dimension load specification, which contains instructions for populating the dimension in the analytic workspace. The load specification may include a filter that identifies criteria for selecting data from the source dimension tables.

Create containers for the dimension in an analytic workspace.

Use the dimension load specification to populate the dimension in the analytic workspace from the source dimension tables.

1.1.3 Creating and Populating Workspace Cubes

After creating the cube's dimensions, run another set of procedures to create and populate the cube itself.

Create a cube load specification, which contains instructions for populating the cube's measures in the analytic workspace. The load specification may include a filter that identifies criteria for selecting data from the source fact table.

Create a composite specification, which contains instructions for ordering the cube's dimensions and storing sparse data in the analytic workspace.

Add the composite specification to the cube load specification.

Create containers for the cube in an analytic workspace.

Use the cube load specification to populate the cube's measures in the analytic workspace from the source fact table.

This cube is the Multidimensional Target Cube identified in Figure 1-1.

1.1.5 Enabling Relational Access to the Workspace Cube

Once you have created, populated, and aggregated the cube in an analytic workspace, run another set of procedures to enable relational access. The enablement process consists of generating and running a set of enablement scripts. These scripts create the relational views that use the OLAP_TABLE function to access the workspace cube. The scripts may also create an OLAP Catalog cube that maps to the views.

The cube created by the enablement scripts is the Relational Target Cube identified in Figure 1-1.

To enable a workspace cube, you can either generate the scripts and run them yourself or you can use a one-step procedure to create and run the scripts automatically.

1.1.6.1 Active Catalog Views

These views use OLAP_TABLE functions to return information about logical standard form objects within analytic workspaces. For example, you could query an Active Catalog view to obtain information about the dimensionality of a workspace cube. The Active Catalog view names have the prefix ALL_OLAP2_AW. For more information, see Chapter 3.

1.1.6.2 Analytic Workspace Maintenance Views

These views return information about building and maintaining analytic workspace cubes. For example, you could query an Analytic Workspace Maintenance view to obtain information about the load specifications associated with an analytic workspace dimension or cube. The Analytic Workspace Maintenance view names have the prefix ALL_AW. For more information, see Chapter 4.

1.2 Understanding the DBMS_AWM Procedures

The procedures in the DBMS_AWM package support methods on several types of logical entities. These entities are described in Table 1-1.

1.3 Creating and Refreshing a Workspace Dimension

Once you have defined a dimension in the OLAP Catalog for your source dimension table, you can create the dimension in the analytic workspace.

Only one workspace dimension may be created from a given dimension in the OLAP Catalog. For example, if you have used the OLAP Catalog PRODUCT dimension as the source for the PROD_AW dimension in an analytic workspace, you cannot create another dimension PROD_AW2 from the same source dimension in the same workspace.

Note:

CREATE_AWDIMENSION opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL COMMIT.

The analytic workspace must already exist before you call CREATE_AWDIMENSION or any other procedures in the DBMS_AWM package.

Example 1-1 shows the procedure calls for defining and populating workspace objects for the XADEMO.CHANNEL dimension. The load specification includes a filter condition that causes only the row for 'DIRECT' to be loaded.

1.3.1 Refreshing the Dimension's Metadata

CREATE_AWDIMENSION ensures that the generic standard form objects that support dimensions exist in the workspace, and it registers the specified dimension in the workspace. However, the metadata that defines the logical structure of this particular dimension is not instantiated in the workspace until you call REFRESH_AWDIMENSION.

For example, if you have just created a dimension AW_PROD in a workspace MYAW in XADEMO from a source dimension XADEMO.PRODUCT, you can query the Active Catalog to check the workspace.

The following query shows that there are no levels associated with the dimension. The levels, hierarchies, attributes, and descriptions will be instantiated when the dimension is refreshed.

SQL>select * from ALL_OLAP2_AW_DIM_LEVELS where AW_LOGICAL_NAME in 'AW_PROD';
no rows selected

1.3.2 When To Refresh a Dimension

You must refresh a dimension whenever changes occur in the source dimension tables. These changes could be additions or deletions of dimension members, for example removing a product from a Product dimension, or they could be changes to the dimension's metadata, such as adding a Day level to a time dimension.

When you refresh a dimension, you must also refresh each cube in which it participates.

1.3.3 What To Do After a Dimension Refresh

When you refresh a dimension because of structural metadata changes to its hierarchies, you must re-enable the dimension and its related cubes. When you refresh a dimension because of data changes, you do not need to re-enable.

When you refresh a dimension whose cube has associated stored summaries in the analytic workspace (the result of an aggregation specification), you must also reaggregate the cube.

1.4 Creating and Refreshing a Workspace Cube

Once you have defined a cube in the OLAP Catalog for your star schema, you can create the cube in the analytic workspace.

You must call CREATE_AWDIMENSION to create each of the cube's dimensions before calling CREATE_AWCUBE to create the cube. To populate the cube, you must call REFRESH_AWDIMENSION to populate each of the cube's dimensions before calling REFRESH_AWCUBE to refresh the cube's measures. On subsequent refreshes, you only need to refresh the dimensions that have changed.

Within an analytic workspace, dimensions can be shared by more than one cube. When creating a new workspace cube, you will only call CREATE_AWDIMENSION for OLAP Catalog dimensions that have not been used as the source for dimensions of cubes that already exist in the workspace.

Note:

CREATE_AWCUBE opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL COMMIT.

The analytic workspace must already exist before you call CREATE_AWCUBE or any other procedures in the DBMS_AWM package.

Example 1-2 shows the procedure calls for creating and populating the XADEMO.ANALYTIC_CUBE cube in an analytic workspace.

1.4.1 Data Type Conversion

The measures in the source fact table may have numeric, text, or date data types. When REFRESH_AWCUBE loads the data into a workspace cube, it converts the RDBMS data types to types that are native to analytic workspaces. The data type conversion is described in Table 1-8.

If a source measure has a data type not described in Table 1-8, the measure is ignored by REFRESH_AWCUBE and none of its data or metadata is loaded into the analytic workspace.

Table 1-8 Conversion of RDBMS Data Types to Workspace Data Types

RDBMS Data Type

Analytic Workspace Data Type

NUMBER

DECIMAL

CHAR, LONG, VARCHAR, VARCHAR2

TEXT

NCHAR, NVARCHAR2

NTEXT

DATE

DATE

1.4.2 Refreshing the Cube's Metadata

CREATE_AWCUBE ensures that the generic standard form objects that support cubes exist in the workspace, and it registers the specified cube in the workspace. However, the metadata that defines the logical structure of this particular cube is not instantiated in the workspace until you call REFRESH_AWCUBE.

For example, if you have just created a cube AW_ANACUBE in a workspace MYAW in MYSCHEMA from the source cube XADEMO.ANALYTIC_CUBE, you can query the Active Catalog to check the workspace.

The following query shows that there are no measures associated with the cube. The measures, dimensions, and descriptions will be instantiated when the cube is refreshed.

SQL>select * from ALL_OLAP2_AW_CUBE_MEASURES where AW_CUBE_NAME in 'AW_ANACUBE';
no rows selected

1.4.3 When To Refresh a Cube

You must refresh a cube whenever changes occur in the source fact table. These changes could be additions or deletions of data, for example updating sales figures, or they could be changes to the cube's metadata, such as adding a measure or renaming a description.

When you refresh a cube, you must first refresh any of its dimensions that have changed.

1.4.4 What To Do After a Cube Refresh

When you refresh a cube because of structural metadata changes to its dimension hierarchies, you must re-enable the cube and its related dimensions. When you refresh a cube because of data changes, you do not need to re-enable.

Everytime you refresh a cube that has an associated aggregation specification, you must reaggregate the cube.

If you make changes to the composite specification associated with a cube, you must drop the cube and re-create it in the analytic workspace. You cannot refresh a cube with a modified composite specification.

1.5 Managing Sparse Data and Optimizing the Workspace Cube

A composite is an object that is used to store sparse data compactly in a variable in an analytic workspace. A composite consists of a list of dimension-value combinations in which one value is taken from each of the dimensions on which the composite is based. Only the combinations for which data exists are included in the composite.

Composites are maintained automatically by the OLAP engine. With composites, you can keep your analytic workspace size to a minimum and promote good performance. For more information on composites, see the Oracle OLAP DML Reference. For information on managing sparsity and optimizing performance in your analytic workspaces, see the Oracle OLAP Application Developer's Guide

For example, you might have some products in your analytic cube that are not sold in all regions. The data cells for those combinations of PRODUCT and GEOGRAPHY would be empty. In this case, you might choose to define PRODUCT and GEOGRAPHY as a composite. The OLAP DML syntax for defining the dimensionality of the Costs measure in this cube could be as follows.

To specify that a cube's data be loaded into an analytic workspace using this definition of the cube's dimensionality, you would define a composite specification for the cube. The composite specification would define the following expression.

<time channel prod_geog<product geography>>

Each member of a composite specification has a name, a type, and a position. Table 1-9 identifies this information for the preceding example.

Table 1-9 Composite Spec Members for XADEMO.ANALYTIC_CUBE

Member

Type

Position

TIME

dimension

1

CHANNEL

dimension

2

PROD_GEOG

composite

3

PRODUCT

dimension

4

GEOGRAPHY

dimension

5

1.5.1 Dimension Order

Dimension order determines how the cube's data is stored and accessed in the analytic workspace. The first dimension in the dimension's definition is the fastest-varying and the last is the slowest-varying.

By default, REFRESH_AWCUBE defines a workspace cube's dimensionality with Time as the fastest varying dimension followed by a composite of all the other dimensions. The dimensions in the composite are ordered according to their size. The dimension with the most members is first and the dimension with the least members is last. For example, the default dimensionality of the ANALYTIC_CUBE in an analytic workspace would be as follows.

<time comp_name<geography, product, channel>>

You can override the default dimensionality by specifying a composite specification and including it in the cube load specification.

You can modify a composite specification by applying it to a different cube or giving it a different name. You can rename, move, and change the segment size of a primary member of a composite specification. However, you cannot rename, move, or change the segment size of a member of a composite. To edit the composite itself, you must delete it and define a new composite.

Suppose that you wanted to make Channel, instead of Time, the fastest varying dimension of the cube in the analytic workspace. You could reposition Channel in the composite specification as follows.

1.6 Aggregating the Data in an Analytic Workspace

The DBMS_AWM package allows you to store aggregate data for level combinations of measures in a workspace cube.

Stored aggregates in an analytic workspace are similar to materialized views for relational data. However, a workspace cube is always presented as fully solved with embedded totals when enabled for SQL access by an application. If you do not preaggregate any of the workspace data, all the aggregate data is still available but it must be calculated on the fly.

Preaggregating some or all of your workspace data will improve query performance in most circumstances. For information on choosing an aggregation strategy, refer to the Oracle OLAP Application Developer's Guide

Note:

The aggregation process (AGGREGATE_AWCUBE) opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL COMMIT.

The cube refresh process stores detail data in the workspace and sets up the structures to support dynamic aggregation. If you want to preaggregate some or all of your data, you must create an aggregation specification and run a separate aggregation procedure for the workspace cube.

1.6.1 Creating an Aggregation Specification

Example 1-4 shows sample procedure calls for preaggregating the Costs and Quota measures of the analytic workspace cube AC2, which was created from XADEMO.ANALYTIC_CUBE.

The quarter totals (level 'L2' of TIME) for product groups (level 'L3' of PRODUCT), product divisions (level 'L2' of PRODUCT), and all channels (level 'STANDARD-2' of CHANNEL) are calculated and stored in the analytic workspace.

1.6.2 Choosing an Aggregation Method

An aggregation method specifies the operation used to summarize the data by level. The default aggregation method is addition. For example, sales data is typically aggregated over time by adding the values for each time period.

The OLAP Catalog supports a set of aggregation methods, which may be included to the definition of a cube. These aggregation methods are listed in Table 1-10.

When a workspace cube is refreshed, the aggregation operators specified in the OLAP Catalog are converted to the corresponding operators supported by the OLAP DML RELATION command. These operators are incorporated in the aggregation map that controls dynamic aggregation for the cube.

To specify a different operator for your stored aggregates, you can use the SET_AWCUBEAGG_SPEC_AGGOP procedure. This procedure enables you to specify any of the operators supported by the OLAP DML RELATION command to preaggregate your data.

Note:

The DBMS_AWM package currently does not support weighted aggregation operators. For example, if the OLAP Catalog specifies a weighted sum or weighted average for aggregation along one of the cube's dimensions, it is converted to the scalar equivalent (sum or average) when the cube is refreshed in the analytic workspace. Weighted operators specified by SET_AWCUBEAGG_SPEC_AGGOP are similarly converted.

The OLAP Catalog and corresponding OLAP DML aggregation operators are described in Table 1-10.

Table 1-10 Aggregation Operators

OLAP Catalog

OLAP DML

DML Abbvr

Description

SUM

SUM

SU

Sum. Adds data values (default)

SCALED SUM

SSUM

SS

Converted to Sum.

WEIGHTED SUM

WSUM

WS

Converted to Sum.

AVERAGE

AVERAGE

AV

Average. Adds data values, then divides the sum by the number of data values that were added together.

HIERARCHICAL AVERAGE

HAVERAGE

HA

Hierarchical Average. Adds data values, then divides the sum by the number of the children in the dimension hierarchy.

WEIGHTED AVERAGE

WAVERAGE

WA

Converted to Average.

HWAVERAGE

HW

Converted to Hierarchical Average.

MAX

MAX

MA

Maximum. The largest data value among the children of any parent data value.

MIN

MIN

MI

Minimum. The smallest data value among the children of any parent data value.

FIRST

FIRST

FI

First. The first non-NA data value.

HFIRST

HF

Hierarchical First. The first data value that is specified by the hierarchy, even if that value is NA.

LAST

LAST

LA

Last. The last non-NA data value.

HLAST

HL

Hierarchical Last. The last data value that is specified by the hierarchy, even if that value is NA.

AND

AND

AN

(Boolean variables only) If any child data value is FALSE, then the data value of its parent is FALSE. A parent is TRUE only when all of its children are TRUE.

OR

OR

OR

(Default for Boolean variables) If any child data value is TRUE, then the data value of its parent is TRUE. A parent is FALSE only when all of its children are FALSE.

COUNT

NO

Converted to NOAGG.

NOAGG

NO

Do not aggregate any data for this dimension.

1.7 Creating Relational Access to the Workspace Cube

Once you have created an analytic workspace cube and refreshed and aggregated its data, you can generate views that will allow applications to access that data using standard SQL. The DBMS_AWM procedures that generate the views are known as the OLAP API Enabler procedures. They generate views and OLAP Catalog metadata in the format required by the OLAP API and BI Beans, as follows.

An embedded total dimension view for each dimension hierarchy.

An embedded total fact view for each combination of dimension hierarchies.

If your analytic workspace will support different applications, then you need to generate views that conform to their requirements. You can use the OLAP_TABLE function, described in Chapter 26, to generate views in a variety of different formats.

To enable a workspace cube, you can either generate the scripts and run them yourself or you can use a one-step procedure to create and run the scripts automatically.

1.7.1 Procedure: Generate and Run the Enablement Scripts

Use the following steps to enable a workspace cube for access by the OLAP API and BI Beans:

Determine how your system is configured to write to files. The enabler procedures accept either a directory object or a directory path. If you specify a directory object, make sure that your user ID has been granted the appropriate access rights to it. If you specify a path, make sure that it is the value of the UTL_FILE_DIR initialization parameter for the instance.

Run the REFRESH_AWCUBE and REFRESH_AWDIMENSION procedures to refresh the cube. These procedures create metadata in the analytic workspace to track the generations of enablement view names.

NOTE: If you use some other process to refresh the cube (for example, the OLAP Analytic Workspace Java API), this metadata is not created. If you want to specify your own names for the enablement views (as described in the following step), you must create this metadata by calling the REFRESH_AWDIMENSION_VIEW_NAME and REFRESH_AWCUBE_VIEW_NAME procedures.

The enablement process automatically provides system-generated names for the enablement views. To provide your own view names, call the SET_AWDIMENSION_VIEW_NAME and SET_AWCUBE_VIEW_NAME procedures.

Call the CREATE_AWDIMENSION_ACCESS procedure for each of the cube's dimensions. Set the access_type parameter to OLAP. Each procedure call will create an enablement script in a directory that you specify. The script will contain statements that create the dimension views and an OLAP Catalog dimension that maps to the views.

Call the CREATE_AWCUBE_ACCESS procedure. Set the access_type parameter to OLAP. This procedure call will create an enablement script in a directory that you specify. The script will contain statements that create the fact views and an OLAP Catalog cube that maps to the views.

Run the enablement scripts. The scripts will delete any previous generation of views and metadata before creating new views and metadata.

1.7.2 Procedure: Run the Enablement Scripts Automatically

To create and run the enablement scripts automatically, use the following steps:

Call CREATE_AWDIMENSION_ACCESS_FULL for each of the cube's dimensions. This procedure creates the enablement scripts in temporary memory and runs the scripts to create the dimension views and OLAP Catalog metadata. The scripts delete any previous views and OLAP Catalog metadata before creating new views and metadata.

Call the procedure CREATE_AWCUBE_ACCESS_FULL to create the fact views for the cube. This procedure accomplishes the same basic steps as the corresponding procedure for dimensions.

Replaces the system-generated names for the views of an analytic workspace dimension.

Note:

If you capture the SQL generated by Analytic Workspace Manager and use it to create your own scripts, you will need to edit the enablement procedure calls. Analytic Workspace Manager uses different versions of the enablement procedures. In your scripts, you must use the syntax described in this manual.

1.7.4 Enablement Metadata in the Analytic Workspace

The REFRESH_AWDIMENSION and REFRESH_AWCUBE procedures create metadata in the analytic workspace related to enablement. This metadata includes a set of default names for the views that will be created by the enablement scripts.

Whenever you refresh, new view names are generated. If you have previously created your own names (SET_AWDIMENSION_VIEW_NAME and SET_AWCUBE_VIEW_NAME), the refresh process uses them as the basis for the new names.

Note:

If you use some other process to refresh the cube (for example, the OLAP Analytic Workspace Java API), you must run REFRESH_AWDIMENSION_VIEW_NAME and REFRESH_AWCUBE_VIEW_NAME before setting the view names .

If you refresh and there has been no change to the source cube's metadata, you do not need to re-create the enablement scripts.

1.7.5 Disabling Relational Access

The enablement procedures automatically delete any previous generation of views and OLAP Catalog metadata. However, in some circumstances, you might want to drop the views and metadata without re-creating them. In particular, if you drop the workspace cube or the workspace itself, you will need to clean up the orphaned views and metadata.

In this case, you can run the DELETE_AWDIMENSION_ACCESS and DELETE_AWCUBE_ACCESS procedures to generate scripts that will drop the views and metadata that enable relational access to the cube. These scripts do not delete any enablement metadata that is stored within the analytic workspace.

To delete all the enablement views and metadata for a dimension or a cube, use DELETE_AWCUBE_ACCESS_ALL and DELETE_AWDIMENSION_ACCESS_ALL.

1.7.6 Default Dimension View Names

REFRESH_AWDIMENSION constructs default names for the views. You can override the default names by calling SET_AWDIMENSION_VIEW_NAME.

The default view name is: aaaa_bbbbb_ccccc_ddddd#view, where:

aaaa is the first four characters of the analytic workspace owner

bbbbb is the first five characters of the analytic workspace name

ccccc is the first five characters of the analytic workspace dimension name

ddddd is the first five characters of the analytic workspace hierarchy name

# is an automatically-generated sequence number between 1 and 9,999 to ensure uniqueness.

Default names are also generated for the abstract objects (ADTs) populated by OLAP_TABLE. For example, the workspace dimension AWGEOG, in a workspace called AWTEST in the XADEMO schema could have the following system-generated names for the STANDARD hierarchy.

Default Name

Description

XADE_AWTES_AWGE0_STAND34VIEW

Name of the relational view

XADE_AWTES_AWGEOG34OBJ

Name of the abstract object that defines a row in the abstract table of objects populated by OLAP_TABLE

XADE_AWTES_AWGEOG34TBL

Name of the abstract table type populated by OLAP_TABLE

1.7.7 Default Fact View Names

The REFRESH_AWCUBE procedure constructs default names for the views. You can override the default names by calling SET_AWCUBE_VIEW_NAME.

The default view name is: aaaa_bbbbb_cccccccc#view, where:

aaaa is the first four characters of the analytic workspace owner

bbbbb is the first five characters of the analytic workspace name

cccccccc is the first eight characters of the analytic workspace cube name

# is an automatically-generated sequence number between 1 and 9,999 to ensure uniqueness.

Default names are also generated for the abstract objects (ADTs) populated by OLAP_TABLE. For example, the workspace cube AWCUBE, in a workspace called AWTEST in the XADEMO schema could have the following system-generated names.

Default Name

Description

XADE_AWTES_AWCUBE8VIEW

Name of the relational fact view for the first hierarchy combination.

XADE_AWTES_AWCUBE9VIEW

Name of the relational fact view for the second hierarchy combination.

XADE_AWTES_AWCUBE10VIEW

Name of the relational fact view for the third hierarchy combination.

XADE_AWTES_AWCUBE11VIEW

Name of the relational fact view for the fourth hierarchy combination.

XADE_AWTES_AWCUBE7OBJ

Name of the abstract object that defines a row in the abstract table of objects populated by OLAP_TABLE

XADE_AWTES_AWCUBE7TBL

Name of the abstract table type populated by OLAP_TABLE

1.7.8 Column Structure of Dimension Enablement Views

The enablement process generates a separate view for each dimension hierarchy. For example, a workspace cube with the four dimensions shown in Table 1-12 would have six separate dimension views since two of the dimensions have two hierarchies.

Table 1-12 Sample Dimension Hierarchies

Dimensions

Hierarchies

Number of Views

geography

standard

consolidated

2

product

standard

1

channel

standard

1

time

standard

ytd

2

The dimension views are level-based, and they include the full lineage of every level value in every row. This type of dimension table is considered solved, because the fact table related to this dimension includes embedded totals for all level combinations.

1.7.8.2 Grouping ID Column

The GID identifies the hierarchy level associated with each row by assigning a zero to each non-null value and a one to each null value in the level columns. The resulting binary number is the value of the GID.

For example, a GID of 1 is assigned to a row with the following three levels.

1.7.9 Column Structure of Enablement Fact Views

The CREATE_AWCUBE_ACCESSprocedure generates a separate view for each dimension/hierarchy combination. For example, an analytic workspace cube with the four dimensions shown in Table 1-12, would have four separate fact views, one for each hierarchy combination show in Table 1-14.

Table 1-14 Sample Dimension/Hierarchy Combinations

Geography Dim

Product Dim

Channel Dim

Time Dim

geography/standard

product/standard

channel/standard

time/standard

geography/standard

product/standard

channel/standard

time/ytd

geography/consolidated

product/standard

channel/standard

time/standard

geography/consolidated

product/standard

channel/standard

time/ytd

The fact views are fully solved. They contain embedded totals for all level combinations. Each view has columns for the cube's measures, and key columns that link the fact view with its associated dimension views.

These columns return predefined custom measures with a text data type. These custom measures result from the execution of a formula within the analytic workspace and are managed by procedures in the DBMS_AW_UTILITIES package. For more information, see Chapter 22.

These columns return predefined custom measures with a numeric data type. These custom measures result from the execution of a formula within the analytic workspace and are managed by procedures in the DBMS_AW_UTILITIESpackage. For more information, see Chapter 22.

1.7.10 Example: Enable a Workspace Cube for Access by the OLAP API

The following example creates, refreshes, and enables a cube AWUSR.AWTEST based on the source cube XADEMO.ANALYTIC_CUBE.

The following queries show the system names and user names for the cube enablement views. Included are the hierarchy combination numbers, in this case 1 - 4, and the hierarchy strings, consisting of each unique combination of dimension hierarchies for this cube.

The final step is to run the enablement scripts to generate the views and OLAP Catalog metadata for the analytic workspace cube. The scripts produced by this example are described as follows.

Directory

Script

Description

/users/awuser/scripts

awprod_views.sql

Creates an abstract object, a table of objects, and a view for the PRODUCT dimension. Also creates and validates an OLAP Catalog dimension AWUSER.AWPROD that maps to the view.

/users/awuser/scripts

awchan_views.sql

Creates an abstract object, a table of objects, and a view for the CHANNEL dimension. Also creates and validates an OLAP Catalog dimension AWUSER.AWCHAN that maps to the view.

/users/awuser/scripts

awgeog_views.sql

Creates an abstract object, a table of objects, and a view for each hierarchy of the GEOGRAPHY dimension. Also creates and validates an OLAP Catalog dimension AWUSER.AWGEOG that maps to the view.

/users/awuser/scripts

awtime_views.sql

Creates an abstract object, a table of objects, and a view for each hierarchy of the TIME dimension. Also creates and validates an OLAP Catalog dimension AWUSER.AWTIME that maps to the view.

/users/awuser/scripts

awcube_views.sql

Creates an abstract object, a table of objects, and a separate view for each hierarchy combination of the AWCUBE cube. Also creates and validates an OLAP Catalog cube AWUSER.AWCUBE that maps to the view.