Skip Headers
Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide
11g Release 2 (11.2)

Part Number E10935-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Defining Dimensional Objects

Warehouse Builder enables you to define, deploy, and load dimensional objects. You can deploy dimensional objects either to a relational schema or to an analytical workspace in the database.

This chapter contains the following topics:

Overview of Dimensional Objects

Objects that contain additional metadata to identify and categorize data are called dimensional objects. Warehouse Builder enables you to design, deploy, and load two types of dimensional objects: dimensions and cubes. In this chapter, dimensional object refers to both dimensions and cubes. Most analytic queries require the use of a time dimension. Warehouse Builder provides tools that enable you to easily create and populate time dimensions by answering simple questions.

Steps to Create Dimensional Objects

Creating dimensional objects consists of following high-level tasks.

  1. Define dimensional objects

    Defining dimensional objects consists of specifying the logical relationships that help store data in a more structured format. For example, to define a dimension, you describe its attributes, levels, and hierarchies. To define a cube, you define its measures and dimensions.

    You can use wizards or editors to define dimensional objects. For more details, see:

  2. Implement dimensional objects

    See "Overview of Implementing Dimensional Objects"

  3. Deploy dimensional objects

  4. Load dimensional objects

    To load data into dimensional objects, create a mapping that defines the data flow and transformations from the source objects to the dimensional object. You then deploy and execute this mapping.

Overview of Dimensions

A dimension is a structure that organizes data. Examples of commonly used dimensions are Customers, Time, and Products.

For relational dimensions, using dimensions improves query performance because users often analyze data by drilling down on known hierarchies. An example of a hierarchy is the Time hierarchy of year, quarter, month, day. The Oracle Database uses these defined hierarchies by rewriting queries that retrieve data from materialized views rather than detail tables.

A dimension consists of a set of levels and a set of hierarchies defined over these levels. To create a dimension, you must define the following:

  • Dimension attributes

  • Levels

  • Level attributes

    This includes surrogate and business identifiers for levels.

  • Hierarchies

See Also:

Oracle Warehouse Builder Concepts for more information about defining dimension attributes, levels, level attributes, and hierarchies

Overview of Surrogate Identifiers

A surrogate identifier uniquely identifies each level record across all the levels of the dimension. It must be composed of a single attribute. Surrogate identifiers enable you to hook facts to any dimension level as opposed to the lowest dimension level only. For a dimension that has a relational or ROLAP implementation, the surrogate identifier should be of the data type NUMBER.

You need to use a surrogate key if:

  • your dimension is a Type 2 or Type 3 SCD. In these cases, we can have multiple dimension records loaded for each business key value, so we need an extra unique key to track these records.

  • your dimension contains more that one level and is implemented using a star schema. Thus, any cube that references such a dimension will reference more than one dimension level.

    If no surrogate key is defined, then only the leaf-level dimension records are saved in the dimension table, the parent level information is stored in extra columns in the leaf-level records. But there is no unique way to reference the upper level in that case.

You do not need a surrogate key for any Type 1 dimensions, implemented by star or snowflake, where only the leaf level(s) are referenced by a cube. Dimensions with multiple hierarchies will still work with no surrogate key, as long as only the leaf levels are referenced by the cube.

Overview of Slowly Changing Dimensions

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. In data warehousing, there are three commonly recognized types of SCDs. describes the types of SCDs, as described in Table 3-1.

Table 3-1 Types of Slowly Changing Dimensions

Type Description

Type 1

Stores only one version of the dimension record. When a change is made, the record is overwritten and no historic data is stored.

Type 2

Stores multiple versions of the same dimension record. When the dimension record is modified, new versions are created while the old ones are retained.

Type 3

Stores one version of the dimension record. This record stores the previous value and current value of selected attributes.


Use Type 2 and Type 3 SCDs to store and manage both current and historical data over time in a data warehouse. Type 1 dimensions, referred to as dimensions, do not preserve historical data.

Additional Attributes for Slowly Changing Dimensions (SCDs)

To create a Type 2 SCD or a Type 3 SCD, in addition to the regular dimension attributes, you need additional attributes that perform the following roles:

  • Triggering Attributes: These are attributes for which historical values must be stored. For example, in the PRODUCTS dimension, the attribute PACKAGE_TYPE of the Product level can be a triggering attribute. This means that when the value of this attribute changes, the old value needs to be stored.

  • Effective Date: This attribute stores the start date of the record's life span.

  • Expiration Date: This attribute stores the end date of the record's life span.

  • Previous Attribute: For Type 3 SCDs only, this attribute stores the previous value of a versioned attribute.

An attribute can play only one of the above roles. For example, an attribute cannot be a regular attribute and an effective date attribute. When you use the wizard to create a Type 2 SCD or a Type 3 SCD, Warehouse Builder creates the required additional attributes.

Overview of Defining Type 2 Slowly Changing Dimensions

A Type 2 SCD retains the full history of values. When the value of a triggering attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective date and expiration date to identify the time period for which the record was active. Warehouse Builder also enables you to set a specific non-null date value as the expiration date. The current record is the one with a null or the previously specified value in the expiration date.

All the levels in a dimension need not store historical data. Typically, only the lowest levels is versioned.

To define a Type 2 Slowly Changing Dimension (SCD), you must identify the following:

  • For the level that stores historical data, specify the attributes used as the effective date and the expiration date.

  • Choose the level attribute(s) that will trigger a version of history to be created.

    You cannot choose the surrogate identifier, effective date attribute, or expiration date attribute as the triggering attribute.

Each version of a record is assigned a different surrogate identifier. The business identifier connects the different versions together in a logical sense. Typically, if there is a business need, Type 2 SCDs are used.

Type 2 SCD Example

Consider the Customers Type 2 SCD that contains two levels, Household and Customer. Customer is the leaf level and Household is the non-leaf level. Table 3-2 lists dimension attributes of the Customers Type 2 SCD.

Table 3-2 Dimension Attributes of the Customers Type 2

Attribute Name Identifier

ID

Surrogate identifier

BUSN_ID

Business identifier

ADDRESS

 

ZIP

 

MARITAL_STATUS

 

HOME_PHONE

 

EFFECTIVE_DATE

Effective Date

EXPIRATION_DATE

Expiration Date


The Household level implements the following attributes: ID, BUSN_ID, ADDRESS, ZIP, EFFECTIVE_DATE, and EXPIRATION_DATE. The Customer level implements the following attributes: ID, BUSN_ID, MARITAL_STATUS, HOME_PHONE, EFFECTIVE_DATE, and EXPIRATION_DATE.The Customers_tab table implements the Customers Type 2 SCD (for a relational or ROLAP implementation). Table 3-3 lists the columns in the Customers_tab table, along with details about the dimension level and the attribute that each column implements.

Table 3-3 Columns that Implement the Customers Type 2 SCD Level Attributes

Column Name in the Customers_tab table Level Name Dimension Attribute Name

DIMENSION_KEY

   

H_ID

Household

ID

H_BUSN_ID

Household

BUSN_ID

H_ADDRESS

Household

ADDRESS

H_ZIP

Household

ZIP

H_EFFECTIVE_DATE

Household

EFFECTIVE_DATE

H_EXPIRATION_DATE

Household

EXPIRATION_DATE

C_ID

Customer

ID

C_BUSN_ID

Customer

BUSN_ID

C_MARITAL_STATUS

Customer

MARITAL_STATUS

C_HOME_PHONE

Customer

HOME_PHONE

C_EFFECTIVE_DATE

Customer

EFFECTIVE_DATE

C_EXPIRATION_DATE

Customer

EXPIRATION_DATE


To create the Customers Type 2 SCD:

  • Specify that the ZIP attribute of the Household level and the MARITAL_STATUS attribute of the Customer level are the triggering attributes.

  • Use two additional attributes to store the effective date and the expiration date of the level records. When you use the Create Dimension wizard, Warehouse Builder creates these additional attributes for the lowest level only. If you use the Dimension Editor, then you must explicitly create these attributes and apply them to the required levels.

Overview of Hierarchy Versioning

For Type 2 SCDs, when the non-leaf level of a dimension contains versioned attributes, the versioning of this non-leaf level results in the versioning of its corresponding child records, if they have effective date and expiration date attributes. For example, in the Customers Type 2 SCD described in "Type 2 SCD Example", when the value of the H_ZIP is updated in a particular Household level record, the child records corresponding to this Household level are automatically versioned.

Hierarchy versioning is not enabled by default for Type 2 SCDs. Thus, when you create a Type 2 SCD using the Create Dimension Wizard, hierarchy versioning is disabled. Use the Dimension Editor to enable hierarchy versioning.

Steps to Enable Hierarchy Versioning

  1. Right-click the Type 2 SCD in the Projects Navigator and select Open.

    The Dimension Editor is displayed.

  2. Navigate to the SCD tab.

  3. Click Settings to the right of the Type 2: Store the Complete change history option.

    The Type 2 Slowly Changing Dimension dialog box is displayed. The attributes of each level are displayed under the level node.

  4. In the child level that should be versioned when its parent attribute changes, for the attribute that represents the parent attribute of this child level, select Trigger History in the Record History column.

    For example, you create the Customers Type 2 SCD using the Create Dimension Wizard. Then open the editor for this Type 2 SCD and navigate to the Type 2 Slowly changing Dimension dialog box. The Customer level has an attribute called HOUSEHOLD_ID. This attribute represents the parent attribute of each Customer record. For the HOUSEHOLD_ID attribute, select Trigger History in the Record History column.

Overview of Defining Type 3 Slowly Changing Dimensions (SCDs)

A Type 3 Slowly Changing Dimension (SCD) stores two versions of values for certain selected level attributes. Each record stores the previous value and the current value of the versioned attributes. When the value of any of the versioned attributes changes, the current value is stored as the old value and the new value becomes the current value. Each record stores the effective date that identifies the date from which the current value is active. This doubles the number of columns for the versioned attributes and is used rarely.

Before you define a Type 3 SCD, identify the following:

  1. For each level, specify which attributes should be versioned. That is, identify which attributes should store the previous value as well as the current value.

  2. For each versioned attribute, specify the attribute that stores the previous value.

    The following restrictions apply to attributes that can have a previous value.

    • An attribute specified as a previous value cannot have further previous values.

    • The surrogate identifier cannot have previous values.

  3. For each level that is versioned, specify the attribute that stores the effective date.

Warehouse Builder recommends that you do not include previous value attributes in the business identifier of a Type 3 SCD.

Type 3 SCD Example

The PRODUCTS dimension described in "Dimension Example" can be created as a Type 3 SCD. The attributes PACKAGE_TYPE and PACKAGE_SIZE of the Product level should be versioned. You define two additional attributes to store the previous values, say PREV_PACK_SIZE and PREV_PACK_TYPE in the Product level. Suppose the value of the PACKAGE_TYPE attribute changes, Warehouse Builder stores the current value of this attribute in PREV_PACK_TYPE and stores the new value in the PACKAGE_TYPE attribute. The effective date attribute can be set to the current system date or to any other specified date.

Overview of Cubes

Cubes contain measures and link to one or more dimensions. The axes of a cube contain dimension members and the body of the cube contains measure values. Most measures are additive. For example, sales data can be organized into a cube whose edges contain values for Time, Products, and Promotions dimensions and whose body contains values from the measures Value sales, and Dollar sales.

A cube is linked to dimension tables over foreign key constraints. Since data integrity is vital, these constraints are critical in a data warehousing environment. The constraints enforce referential integrity during the daily operations of the data warehouse.

Data analysis applications typically aggregate data across many dimensions. This enables them to look for anomalies or unusual patterns in the data. Using cubes is the most efficient way of performing these type of operations. In a relational implementation, when you design dimensions with warehouse keys, the cube row length is usually reduced. This is because warehouse keys are shorter than their natural counterparts. This results is lesser amount of storage space needed for the cube data. For a MOLAP implementation, OLAP uses VARCHAR2 keys.

A typical cube contains:

  • A primary key defined on a set of foreign key reference columns or, in the case of a data list, on an artificial key or a set of warehouse key columns. When the cube is a data list, the foreign key reference columns do not uniquely identify each row in the cube.

  • A set of foreign key reference columns that link the table with its dimensions.

To create cubes, you must define the cube measures and the cube dimensionality. For more information about measures and cube dimensionality, see Oracle Warehouse Builder Concepts.

Orphan Management for Dimensional Objects

Warehouse Builder's orphan management policy enables you to manage orphan records in dimensional objects (dimensions and cubes). An orphan record is one that does not have a corresponding existing parent record. Orphan management automates the process of handling source rows that do not meet the requirements necessary to form a valid dimension or cube record.

Orphan records can occur when:

  • a record that is loaded into a dimensional object does not have a corresponding parent record.

    A dimension record is considered an orphan if one or more of its level references is null or nonexistant. A cube record is considered an orphan if one or more dimension records that it references is either nonexistent or null.

  • a record is deleted from a dimensional object. This could result in the child records of the deleted record not having an existing parent record.

Warehouse Builder enables you to specify different orphan management policies for loading dimensional object data and for removing dimensional object data.

Note:

Orphan management is not supported for MOLAP dimensions and cubes.

Orphan Management While Loading Data Into Dimensional Objects

An orphan record is created while loading data into a dimensional object if you insert a record that does not have an existing parent record. For example, you load data into the City level of the Geography dimension. The value of the State attribute in this record does not exist in the State level. This record is an orphan record. Or you load data into the SALES cube, but the value for the Customer ID does not exist in the Customers dimension.

Warehouse Builder enables you to specify the integrity policy used while loading orphan records into a dimensional object. You can specify different actions for records that have a null parent record and records that have an invalid parent record.

The orphan management policy options that you can set for loading are:

  • Reject Load: The record is not inserted.

  • Default Parent: You can specify a default parent record. This default record is used as the parent record for any record that does not have an existing parent record. If the default parent record does not exist, then Warehouse Builder creates the default parent record.

    You specify the attribute values of the default parent record at the time of defining the dimensional object. If any ancestor of the default parent does not exist, then Warehouse Builder also creates this record.

  • No Maintenance: This is the default behavior. Warehouse Builder does not actively detect, reject, or fix orphan records.

See Also:

  • "Orphan Tab" for details about setting an orphan management policy for dimensions

  • "Orphan Tab" for details about setting an orphan management policy for cubes

Orphan Management While Removing Data From Dimensional Objects

Orphan records can be created while removing data if the record that is being removed has corresponding child records. For example, you remove a record in the State level of the Geography dimension. This state has city records that refer to it. All the city records that refer to the deleted state record will become orphan records.

While removing data from a dimension, you can select one of the following orphan management policies:

  • Reject Removal: Warehouse Builder does not allow you to delete the record if it has existing child records.

  • No Maintenance: This is the default behavior. Warehouse Builder does not actively detect, reject, or fix orphan records.

Error Tables

Error tables store any records that are detected as anomalous, by Orphan Management, during a load or remove operation on a dimension. Error tables are created when you deploy a dimension for the first time if you select the Deploy Error Tables option on the Orphan tab of the dimension editor.

Following are the records that appear in error tables:

  • Records that are not inserted during a load operation

  • Records whose parents are defaulted during the load operation

  • Records that could not be deleted during a remove operation

Warehouse Builder creates one error table for each implementation object. For example, if a dimension is implemented using a snowflake schema, then multiple error tables are created. If the dimension is implemented using a star schema, then one error table is created. The name of the error table is the same as the implementation object suffixed with an _ERR. If the implementation table is called CITY, then the error table is called CITY_ERR.

Note:

Since orphan management is not supported for MOLAP dimensional objects, error tables are created for dimensions and cubes that have a relational or ROLAP implementation only.

Overview of Implementing Dimensional Objects

To implement a dimensional object is to create the physical structure of the dimensional object. Warehouse Builder provides the following implementations for dimensional objects:

Note:

To use a MOLAP implementation, you must have the following:
  • Oracle Database 10g Enterprise Edition with the OLAP option

  • Oracle Database 11g Enterprise Edition with the OLAP option

  • OLAP 10.1.0.4 or higher

The implementation is set using the Storage page of the Wizard that is used to create the dimensional object or the Storage tab of the object editor. You can further refine the implementation deployment options using the Deployment Option configuration parameter. For more information about setting this parameter, see "Configuring Dimensions" and "Configuring Cubes".

Relational Implementation of Dimensional Objects

A relational implementation stores the dimensional object and its data in a relational form in the database. The dimensional object data is stored in implementation objects that are typically tables. Any queries that are executed on the dimensional object obtain data from these tables. Warehouse Builder creates the DDL scripts that create the dimensional object. You can then deploy these scripts to the database using the Control Center. For relational dimensions, Warehouse Builder can use a star schema, a snowflake schema, or a manual schema to store the implementation objects.

See Also:

Oracle Warehouse Builder Concepts for more information about how the star schema and snowflake schema store dimension data

When you use the wizard to define dimensional objects, Warehouse Builder creates the database tables that store the dimensional object data. It also defines the association between the dimension object attributes and the implementation tables that defines the table columns that store the dimensional object data.

When you define a dimensional object using the editors, you can decide whether you want Warehouse Builder to create the implementation tables or you want to store the dimensional object data in your own tables and views. If you want Warehouse Builder to create implementation objects, then perform auto binding for the dimensional object. To use your own implementation tables to store the dimensional object data, perform manual binding.

Note:

For a relational implementation, you cannot view the data stored in the dimensional object using the Data Viewer. However, you can view the data stored in the implementation tables of the dimensional object using the Data Viewer.

Binding

Binding is the process of connecting the attributes of the dimensional object to the columns in the table or view that store their data. You perform binding only for dimensional objects that have a relational or ROLAP implementation. For multidimensional objects, binding is implicit and is resolved in the analytic workspace.

For dimensions, you connect the level attributes and level relationships to the columns in the implementation objects. For cubes, you connect the measures and dimension references to implementation table columns.

Warehouse Builder provides two methods of binding: Auto Binding and Manual Binding.

When to Perform Binding 

  • When you create a dimensional object using the wizard, the object will be bound for you. If you make any changes to the dimensional object using the editor, then you must re-bind the object before you deploy them.

  • When you create a dimensional object using the editor, you must bind the dimensional object to its implementation objects before deployment.

  • When you make any change to a dimensional object definition using the editors, you must rebind the dimensional object to its implementation objects.

Auto Binding

Auto binding refers to the creation of the implementation tables, if they do not already exist. The attributes and relationships of the dimensional object are then bound to the columns that store their data. In the case of a dimension, the number of tables used to store the dimension data depends on the options you select for the storage. You can perform auto binding using both the wizards and the editors. When you use the editors to create dimensional objects, you can perform both auto binding and manual binding.

To perform auto binding:

  1. In the Projects Navigator, right-click the dimensional object and select Open.

    The editor for this dimensional object is displayed.

  2. On the Physical Bindings tab, select node that represents the dimensional object.

  3. From the File menu, select Bind.

    If the Bind option is not enabled, then verify if the dimensional object uses a relational or ROLAP implementation. In the case of dimensions, ensure that the Manual option is not set in the Implementation section of the Storage tab.

Alternatively, you can perform auto binding by right-clicking the dimensional object in the Projects Navigator and selecting Bind.

Manual Binding

In manual binding, you must explicitly bind the attributes of the dimensional objects to the database columns that store their data. You use manual binding when you want to bind a dimensional object to existing tables or views. If a dimensional object is already bound to certain implementation objects (as shown on the Physical Bindings tab of the Dimension Editor), then unbind the dimensional object and then perform manual binding. For details about unbinding dimensional objects, see "Unbinding".

To perform manual binding for a dimensional object:

  1. Create the implementation objects (tables or views) that you will use to store the dimensional object data.

    In the case of relational or ROLAP dimensions, create the sequence used to load the surrogate identifier of the dimension. You can choose to use an existing sequence.

  2. In the Projects Navigator, right-click the dimensional and select Open.

    The editor for the dimensional object is displayed.

  3. On the Physical Bindings tab, right-click a blank area, select Add and then the type of object that represents the implementation object.

    Warehouse Builder displays the Add a New or Existing <Object> dialog box. For example, if the dimension data is stored in a table, then right-click a blank area on the Physical Bindings tab, select Add and then Table. The Add a New or Existing Table dialog box is displayed.

  4. Choose the Select an existing <Object> option and then select the data object from the list of objects displayed in the selection tree.

  5. Click OK.

    A node representing the object that you just added is displayed on the canvas.

  6. For dimensions, if more than one data object is used to store the dimension data, perform steps 3 to 5 for each data implementation object.

  7. For dimensions, map the attributes in each level of the dimension to the columns that store their data. Also map the level relationships to the database column that store their data.

    For cubes, map the measures and dimension references to the columns that store the cube data.

    To map to the implementation object columns, hold down your mouse on the dimension or cube attribute, drag, and then drop on the column that stores the attribute value.

    For example, for the PRODUCTS dimension described in "Dimension Example", the attribute NAME in the Groups level of the PRODUCTS dimension is stored in the GROUP_NAME attribute of the PRODUCTS_TAB table. Hold down the mouse on the NAME attribute, drag, and drop on the GROUP_NAME attribute of the PRODUCTS_TAB table.

Unbinding

Warehouse Builder also enables you to unbind a dimensional object. Unbinding removes the connections between the dimensional object and the tables that store its data.

To unbind a dimensional object from its current implementation, select the dimensional object in the Projects Navigator and, from the File menu, select Unbind. Unbinding removes the bindings between the dimensional object and its implementation objects. However, it does not delete the implementation objects.

ROLAP Implementation of Dimensional Objects

A ROLAP implementation, like a relational implementation, stores the dimensional object and its data in a relational form in the database. Additionally, depending on the type of ROLAP implementation, it either creates CWM2 metadata in the OLAP catalog or OLAP cube materialized views.

This section describes how ROLAP implementations are classified.

ROLAP Implementation

The dimensional object and its data are stored in a relational form in the database and the CWM2 metadata for the dimensional object is stored in the OLAP catalog. This enables you to query the dimensional object from Discoverer (for OLAP).

ROLAP with MVs Implementation

The dimensional object and its data are stored in a relational form in the database. Additionally, cube-organized materialized views are created in an analytic workspace.

Note:

In Oracle Warehouse Builder 11g Release 2 (11.2), only star schema tables are supported for the ROLAP with MVs implementation.

About OLAP Catalog

The OLAP catalog is the metadata repository provided for the OLAP option in the Oracle Database. This metadata describes the data stored in relational tables.

When you deploy a dimensional object using Warehouse Builder, you can specify if the dimensional object metadata should be stored in the OLAP catalog.

OLAP metadata is dynamically projected through a series of views called the active catalog views (views whose names begin with ALL_CWM2_AW).

In Oracle Database 10g, the OLAP catalog metadata is used by OLAP tools and applications to access data stored in relational star and snowflake schemas. External application such as Discoverer use the OLAP catalog to query relational and multidimensional data. The application does not need to be aware of whether the data is located in relational tables or in analytic workspaces, nor does it need to know the mechanism for accessing it.

The OLAP catalog uses the metadata it stores to access data stored in relational tables or views. The OLAP catalog defines logical multidimensional objects and maps them to the physical data sources. The logical objects are dimensions and cubes. The physical data sources are columns of a relational table or view.

MOLAP Implementation of Dimensional Objects

In a MOLAP implementation, the dimensional object data is stored in an analytic workspace in Oracle Database 10g or Oracle Database 11g. This analytic workspace, in turn, is stored in the database.

If the Oracle location of the computer containing the AW uses Oracle Database 10g, then the OLAP 10g form analytic workspaces are generated. If the location used Oracle Database 11g, then the OLAP 11g form analytic workspaces are generated.

Analytic Workspace

An analytic workspace is a container within the Oracle Database that stores data in a multidimensional format. Analytic workspaces provide the best support to OLAP processing. An analytic workspace can contain a variety of objects such as dimensions and variables.

An analytic workspace is stored in a relational database table, which can be partitioned across multiple disk drives like any other table. You can create many analytic workspaces within a single schema to share among users. An analytic workspace is owned by a particular user and other users can be granted access to it. The name of a dimensional object must be unique within the owner's schema. For more information about analytic workspaces, see Oracle OLAP User's Guide.

Deployment Options for Dimensional Objects

After you define dimensional objects, you must deploy them to instantiate them in the database. To specify the type of implementation for dimensional objects, you set the configuration parameter Deployment Option.

Warehouse Builder provides the following deployment options for dimensions: Deploy All, Deploy Data Objects Only, Deploy to Catalog, and Deploy Aggregation.

Deploy All For a relational or ROLAP implementation, the dimension is deployed to the database and a CWM definition to the OLAP catalog. For a ROLAP with MVs implementation, the dimension is deployed to the database and cube-organized materialized views are created in an analytic workspace. For a MOLAP implementation, the dimension is deployed to the analytic workspace.

Deploy Data Objects Only Deploys the dimension only to the database. You can select this option only for dimensions that use a relational or a ROLAP implementation.

Deploy to Catalog Deploys the CWM definition to the OLAP catalog only. Use this option if you want applications such as Discoverer for OLAP to access the dimension data after you deploy data only. You can also use this option if you previously deployed with "Data Objects Only" and now want to deploy the CWM Catalog definitions without redeploying the data objects again.

Deploy Aggregation Deploys the aggregations defined on the cube measures. This option is available only for cubes.

Creating Dimensions

To create dimensions, use one of the following methods:

Dimension Example

An example of a dimension is the Products dimension that you use to organize product data. Table 3-4 lists the levels in the PRODUCTS dimension and the surrogate identifier and business identifier for each of the levels in the dimension.

Table 3-4 Products Dimension Level Details

Level Attribute Name Identifier

Total

ID

Surrogate

 

Name

Business

 

Description

 

Groups

ID

Surrogate

 

Name

Business

 

Description

 

Product

ID

Surrogate

 

UPC

Business

 

Name

 
 

Description

 
 

Package Type

 
 

Package Size

 

The PRODUCTS dimension contains the following hierarchy:

Hierarchy 1: Total > Groups > Product

Creating Dimensions Using the Create Dimension Wizard

To create a dimension using the Create Dimension wizard:

  1. From the Projects Navigator expand the Databases node and then the Oracle node.

  2. Expand the module where you want to create the dimension.

  3. Right-click the Dimensions node and select New Dimension.

    Warehouse Builder displays the Welcome page of the Create Dimension wizard. Click Next to proceed. The wizard guides you through the following pages:

Name and Description Page

Use the Name and Description page to describe your dimension. Enter the following information on this page:

  • Name: This is the name used to refer to the dimension. The dimension name must be unique within a module.

  • Description: You can type an optional description for the dimension.

Storage Type Page

Use the Storage Type page to specify the type of storage for the dimension. The storage type determines how the dimension data is physically stored in the database. The options you can select for storage type are:

You select the storage type based on the volume of data stored at the lowest level of the entire cube and the refresh rate required.

ROLAP: Relational storage Warehouse Builder stores the dimension definition and its data in a relational form in the database. Select this option to create a dimension that uses a relational or ROLAP implementation.

Relational storage is preferable if you want to store detailed, high volume data or you have high refresh rates combined with high volumes of data. Use relational storage if you want to perform one of the following:

  • Store detailed information such as call detail records, point of sales (POS) records and other such transaction oriented data.

  • Refresh high volumes of data at short intervals.

  • Detailed reporting such as lists of order details.

  • Ad hoc queries in which changing needs require more flexibility in the data model.

Operational data stores and enterprise data warehouses are typically implemented using relational storage. You can then derive multi-dimensional implementations from this relational implementation to perform different analysis types.

If the database containing the target schema has the OLAP option installed, then you can also deploy the dimensions to the OLAP catalog.

When you choose a relational implementation for a dimension, the implementation tables used to store the dimension data are created. The default implementation of the dimension is using a star schema. This means that the data for all the levels in the dimension is stored in a single database table.

ROLAP: with MVs Warehouse Builder stores the dimension definition and its data in a relational form in the database. Additionally, cube-organized MVs are created in the analytic workspace. Select this option to create a dimension that uses a relational implementation and stores summaries in the analytic workspace. Using this option provides summary management based on cube-organized MVs in Oracle 11g Database. Query performance is greatly improved, without the need to make any modification to your queries.

When you choose a ROLAP with MVs implementation:

  • the implementation tables used to store the dimension data are created. The default implementation of the dimension is using a star schema.

  • the dimension is stored in an analytic workspace that uses the same name as the Oracle module to which the dimension belongs. The tablespace that is used to store the analytic workspace is the tablespace that is defined as the users tablespace for the schema that contains the dimension metadata.

MOLAP: Multidimensional storage Warehouse Builder stores the dimension definition and dimension data in an analytic workspace in the database. Select this option to create a dimension that uses a MOLAP implementation.

Multidimensional storage is preferable when you want to store aggregated data for analysis. The refresh intervals for a multidimensional storage are usually longer than relational storage as data needs to be pre-calculated and pre-aggregated. Also, the data volumes are typically smaller due to higher aggregation levels. Use multidimensional storage to perform the following:

  • Advanced analysis such as trend analysis, what-if analysis, or to forecast and allocate data.

  • Constant analysis using a well-defined consistent data model with fixed query patterns.

When you choose a MOLAP implementation, the dimension is stored in an analytic workspace that uses the same name as the Oracle module to which the dimension belongs. The tablespace that is used to store the analytic workspace is the tablespace that is defined as the users tablespace for the schema that contains the dimension metadata.

Note:

For information about certain limitations of deploying dimensions to the OLAP catalog, see "Limitations of Deploying Dimensions to the OLAP Catalog".

Dimension Attributes Page

Use the Dimension Attributes page to define the dimension attributes. A dimension attribute is applicable to one or more levels in the dimension. By default, the following attributes are created for each dimension: ID, Name, and Description. You can rename the ID attribute or delete it.

Specify the following details for each dimension attribute:

  • Name: This is the name of the dimension attribute. The name must be unique within the dimension.

  • Description: Type an optional description for the dimension attribute.

  • Identifier: Select the type of dimension attribute. Select one of the following options:

    Surrogate: Indicates that the attribute is the surrogate identifier of the dimension. Specifying a surrogate identifier for a dimension is optional.

    Business: Indicates that the attribute is the business identifier of the dimension

    Parent: Since you can create values-based hierarchies only using the Dimension Editor, this option is displayed only in the Attributes tab of the Dimension Editor. In a value-based hierarchy, selecting Parent indicates that the attribute stores the parent value of an attribute. If the attribute is a regular dimension attribute, then leave this field blank.

    Note:

    You can create value-based hierarchies only when you choose a MOLAP implementation for the dimension.

    The options displayed in the Identifier list depend on the type of dimension. When you create a dimension with a relational or ROLAP implementation, only the Surrogate and Business options are displayed. For MOLAP dimensions, only the Business and Parent options are displayed.

  • Data Type: Select the data type of the dimension attribute from the list.

    Note:

    The following data types are not supported for MOLAP implementations: BLOB, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, RAW, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE.
  • Length: For character data types, specify the length of the attribute.

  • Precision: For numeric data types, define the total number of digits allowed for the column.

  • Scale: For numeric data types, define the total number of digits to the right of the decimal point.

  • Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types.

  • Descriptor: Select the type of descriptor. The options are: Short Description, Long Description, End date, Time span, Prior period, and Year Ago Period.

    Descriptors are very important for MOLAP implementations. For example, in a custom time dimension, you must have Time Span and End Date to allow time series analysis.

Levels Page

The Levels page defines the levels of aggregation in the dimension. A dimension must contain at least one level. The only exception is value-based hierarchies that contain no levels. You can create a value-based hierarchy using the Dimension Editor only.

Enter the following details on the Levels page:

  • Name: This is the name of the level. The level name must be unique within the dimension.

  • Description: Type an optional description for the level.

List the levels in the dimension such that the parent levels appear above the child levels. Use the arrow keys to move levels so that they appear in this order.

Warehouse Builder creates a default hierarchy called STANDARD that contains the levels in the same order that you listed them on the Levels page. The attributes used to store the parent key references of each level are also created. For a relational or ROLAP dimension, two attributes are created, one for the surrogate identifier and one for the business identifier, that correspond to the parent level of each level. For a MOLAP dimension, for each level, one attribute that corresponds to the business identifier of the parent level is created.

For example, the Products dimension contains the following levels: Total, Groups, and Product. Two level relationships are created in the dimension, one each under the Product and Groups levels. For relational or ROLAP dimensions, these level relationships reference the surrogate identifier of the parent level. Level relationships are only displayed in the Physical Bindings Tab of the Dimension Editor.

Note:

To create additional hierarchies, use the Hierarchies tab of the Dimension Editor as described in Hierarchies Tab.

Level Attributes Page

The Level Attributes page defines the level attributes of each dimension level. You define level attributes by selecting the dimension attributes that apply to the level. The dimension attributes are defined on the Dimension Attributes page of the Create Dimension wizard.

The Level Attributes page contains two sections: Levels and Level Attributes.

Levels The Levels section lists all the levels defined in the Levels page of the Create Dimension wizard. Select a level in this section to specify the dimension attributes that this level implements. You select a level by clicking the level name.

Level Attributes The Level Attributes section lists all the dimension attributes defined in the Dimension Attributes page. For each level, choose the dimension attributes that the level implements. To indicate that a dimension attribute is implemented by a level, select the Applicable option for the dimension attribute. The name of the level attribute can be different from that of the dimension attribute. Use the Level Attribute Name field to specify the name of the level attribute.

For example, to specify that the dimension attributes ID, Name, Description, and Budget are implemented by the State level:

  1. Select the State level in the Levels section.

  2. In the Level Attributes section, select the Applicable option for the attributes ID, Name, Description, and Budget.

By default, the following defaults are used:

  • The attributes ID, Name, and Description are applicable to all levels.

  • All dimension attributes are applicable to the lowest level in the dimension.

Slowly Changing Dimension Page

Use of this functionality requires the Warehouse Builder Enterprise ETL Option.

The Slowly Changing Dimension page enables you to define the type of slowly changing policy used by the dimension. This page is displayed only if you had chosen Relational storage (ROLAP) as the storage type on the Storage Type Page.

For more information about Slowly Changing Dimensions concepts, see Oracle Warehouse Builder Concepts.

Select one of the following options for the slowly changing policy:

  • Type 1: Do not store history: This is the default selection. Warehouse Builder creates a dimension that stores no history. This is a normal dimension.

  • Type 2: Store the complete change history: Select this option to create a Type 2 Slowly Changing Dimension. Warehouse Builder creates the following two additional dimension attributes and makes them applicable for the lowest level in the Type 2 SCD:

    • Effective date

    • Expiration date

    All the attributes of the lowest level in the Type 2 SCD, except the surrogate and business identifier, are defined as the triggering attributes.

    Note:

    You cannot create a Type 2 or Type 3 Slowly Changing Dimension if the type of storage is MOLAP.
  • Type 3: Store only the previous value: Select this option to create a Type 3 Slowly Changing Dimension. Warehouse Builder assumes that all the level attributes at the lowest level, excluding the surrogate ID and business ID, should be versioned. For each level attribute that is versioned, an additional attribute is created to store the previous value of the attribute.

Pre Create Settings Page

The Pre Create Settings page displays a summary of the options selected on the previous pages of the Create Dimension wizard. This includes the attributes, levels, hierarchies, storage type, and the slowly changing policy used for the dimension. Warehouse Builder uses these settings to create the dimension definition and the database tables that implement the dimension. It also binds the dimension attributes to the table columns that store the attribute data.

Click Next to proceed with the implementation of the dimension. To change any of the options you previously selected, click Back.

Note:

Review this page carefully as it summarizes the implementation and its objects.

Dimension Creation Progress Page

The Dimension Creation Progress page displays the progress of the dimension implementation that was started on the Pre-Create Settings page. The Message Log section on this page provides information about the individual tasks completed during the dimension implementation. Click Next to proceed.

Summary Page

The Summary page provides a brief summary of the options that you selected using the Create Dimension wizard. Use the Summary page to review the selected options. Click Finish to create the dimension. You now have a fully functional dimension. This dimension is displayed under the Dimensions node of the Projects Navigator.

Warehouse Builder creates the metadata for the following in the workspace:

  • The dimension object.

  • The objects that store the dimension data.

    For a relational implementation, a database table that stores the dimension data is created. Warehouse Builder binds the attributes in the dimension to the database columns used to store their values.

    For a MOLAP implementation, the analytic workspace that stores the dimension data is created.

  • (Relational and ROLAP dimensions only) The database sequence used to generate the surrogate identifier for all the dimension levels.

Warehouse Builder creates the definitions of these objects in the workspace and not the objects themselves.

Deploying Dimensions To create the dimension in the target schema, you must deploy the dimension. For a ROLAP dimension, ensure that you deploy the sequence and the implementation tables before you deploy the dimension. Alternatively, you can deploy all these objects at the same time. For more information see "ROLAP Implementation of Dimensional Objects".

Note:

When you delete a dimension, the associated objects such as sequence, database tables, or AWs are not deleted. You must explicitly delete these objects.

Defaults Used By the Create Dimension Wizard

When you create a dimension using the Create Dimension wizard, default values are set for some of the attributes that are used to create the dimension. The following sections describe the defaults used.

Storage

For a relational storage, the star schema is used as the default implementation method.

When you choose multidimensional storage, the dimension is stored in an analytic workspace that has the same name as the Oracle module in which the dimension is defined. If the analytic workspace does not exist, then it is created. The analytic workspace is stored in the users tablespace of the schema that owns the Oracle module.

Dimension Attributes

Warehouse Builder creates default dimension attributes with the properties specified in Table 3-5.

Table 3-5 Default Dimension Attributes

Dimension Attribute Name Identifier Data Type

ID

Surrogate

NUMBER

Name

Business

VARCHAR2

Description

 

VARCHAR2


You can add additional attributes. For your dimension to be valid, you must define the surrogate and business identifiers.

Hierarchies

Warehouse Builder creates a default hierarchy called STANDARD that contains all the levels listed on the Levels page of the Create Dimension wizard. The hierarchy uses the levels in the same order that they are listed on the Levels page.

Level Attributes

The ID, Name, and Description attributes are applicable to each level defined in the dimension. All the dimension attributes are applicable to the lowest level in the dimension. The lowest level is the level that is defined last on the Levels page.

Slowly Changing Dimensions

When you create a Type 2 SCD, all the attributes of the lowest level, except the surrogate identifier and the business identifier, are versioned. Two additional attributes are created to store the effective date and the expiration date of each record. For example, if you create the Products dimension described in "Dimension Example" as a Type 2 SCD, then the attributes UPC, Package_type, and Package_size are versioned. Warehouse Builder creates two additional attributes called EXPIRATION_DATE and EFFECTIVE_DATE, of data type DATE, to store the effective date and expiration date of versioned records.

For a Type 3 SCD, all level attributes of the lowest level, except the surrogate identifier and the primary identifier, are versioned. Warehouse Builder creates additional attributes to store the previous value of each versioned attribute. Additionally, an attribute to store the effective date is created. For example, if you create the Products dimension described in "Dimension Example" as a Type 3 SCD, then additional attributes called PREV_DESCRIPTION, PREV_PACKAGE_TYPE, PREV_PACKAGE_SIZE, and PREV_UPC are created to store the previous values of the versioned attributes. These data type for these attributes are the same the ones used to store the current value of the attribute. Warehouse Builder also creates an attribute EFFECTIVE_TIME to store the effective time of versioned records. This attribute uses the DATE data type.

Orphan Management Policy

For relational and ROLAP dimensions, the default orphan management policy for loading data into and removing data from dimensions is No Maintenance.

The Deploy Error Tables option is deselected.

Implementation Objects

For each dimension, in addition to the dimension object, certain implementation objects are created. The number and type of implementation objects depends on the storage type of the dimension.

For time dimensions, irrespective of the storage type, a map that loads the time dimension is created. The name of the map is the dimension name followed by '_MAP'. For example, the map that loads a time dimension called TIMES will be called TIMES_MAP.

ROLAP: Relational Storage

For a relational storage, the following implementation objects are created:

Table: A table with the same name as the dimension is created to store the dimension data. A unique key is created on the dimension key column. For example, when you define a dimension called CHANNELS, a table called CHANNELS_TAB is created to store the dimension data. Also, a unique key called CHANNELS_DIMENSION_KEY_PK is created on the dimension key column.

Sequence: For a dimension that uses a relational storage, a sequence that loads the dimension key values is created. For example, for the dimension called CHANNELS, a sequence called CHANNELS_SEQ is created.

ROLAP: with MVs

For a ROLAP with MVs implementation, the implementation table and the sequence that loads the surrogate identifier, as described in "ROLAP: Relational Storage", are created. Additionally, an analytic workspace with the same name as the Oracle module containing the dimension is created.

MOLAP: Multidimensional Storage

For a multidimensional storage, if it does not already exist, an analytic workspace with the same name as the Oracle module that contains the dimension is created. For example, if you create a dimension called PRODUCTS in the SALES_WH module, then the dimension is stored in an analytic workspace called SALES_WH. If an analytic workspace with this name does not already exist, it is first created and then the dimension is stored in this analytic workspace.

Creating Dimensions Using the Dimension Editor

The Dimension Editor enables advanced users to create dimensions according to their requirements. You can also edit a dimension using the Dimension Editor.

Use the Dimension Editor to create a dimension if you want to perform one of the following tasks:

  • Use the snowflake implementation methods.

  • Create value-based hierarchies.

  • Create dimension roles.

  • Skip levels in a hierarchy.

  • Use existing database tables or views to store the dimension data. This is referred to as manual binding.

  • Specify an orphan management policy.

  • Create more than one hierarchies in a dimension.

To define a dimension using the Dimension Editor:

  1. From the Projects Navigator expand the Databases node and then the Oracle node.

  2. Expand the target module where you want to create the dimension.

  3. Right-click Dimensions and select New.

    The New Gallery dialog box is displayed.

  4. Select Dimension without using Wizard and click OK.

    Warehouse Builder displays the Create Dimension dialog box.

  5. Specify a name and an optional description for the dimension and click OK.

    The Dimension Editor is displayed with the Name tab containing the name and description you provided.

    To define the dimension, provide information about the following tabs:

    Note:

    When you use the Dimension Editor to create a dimension that has a relational implementation, the physical structures that store the dimension data are not automatically created. You must create these structures either manually or using the Bind option in the File menu.
  6. For dimensions that have a relational or ROLAP with MVs implementation, bind the attributes in the dimension to the database columns that store their data, see "Physical Bindings Tab".

Name Tab

Use the Name tab to describe your dimension. You also specify the type of dimension and the dimension roles on this tab.

The Name field represents the name of the dimension. The dimension name must be unique within the module. Use the Description field to enter an optional description for the dimension.

Dimension Roles Use the Dimension Roles section to define dimension roles. You define the following for each dimension role:

  • Name: Represents the name of the dimension role.

  • Description: Specify an optional description for the dimension role.

See Also:

Oracle Warehouse Builder Concepts for more information about dimension roles

Storage Tab

Use the Storage tab to specify the type of storage for the dimension. The storage options you can select are described in the following sections.

ROLAP: Relational Storage Select the Relational option to store the dimension and its data in a relational form in the database. Use this option to create a dimension that uses a relational or ROLAP implementation.

For a relational storage, you can select one of the following methods to implement the dimension:

  • Star schema: Implements the dimension using a star schema. This means that the dimension data is stored in a single database table or view.

  • Snowflake schema: Implements the dimension using a snowflake schema. This dimension data is stored in more than one database table or view.

  • Manual: You must explicitly bind the attributes from the dimension to the database object that stores their data. When you select this option, you are assigned write access to the Physical Binding tab in the Dimension Editor and the auto binding feature is disabled so that you do not accidentally remove the bindings that you manually created.

When you perform auto binding, these storage settings are used to perform auto binding.

Click Create composite unique key to create a composite unique key on the business identifiers of all levels. For example, if your dimension contains three levels, when you create a composite unique key, a unique key that includes the business identifiers of all three levels is created. Creating a composite unique key enforces uniqueness of a dimension record across the dimension at the database level.

If the database containing the target schema has the OLAP option installed, then you can also deploy the dimensions to the OLAP catalog by setting the configuration parameter as described in "Specifying How Dimensions are Deployed".

ROLAP: with Cube MVs Warehouse Builder stores the dimension definition and its data in a relational form in the database. Additionally, materialized view summaries are created for the implementation tables in the analytic workspace. Select this option to create a dimension that uses a ROLAP implementation and stores summaries in the analytic workspace.

When you choose a ROLAP with MVs implementation, specify the name of the analytic workspace that should store the summary data using the AW Name field in the MOLAP: Multidimensional storage section.

MOLAP: Multidimensional storage Select the MOLAP option to store the dimension and its data in a multidimensional form in the database. Use this option to create a dimension that uses a MOLAP implementation. The dimension data is stored in an analytic workspace.

Enter values for the following fields:

  • AW Name: Enter the name of the analytic workspace that stores the dimension data. Alternatively, you can click the Select button to display a list of MOLAP objects in the current project. Warehouse Builder displays a node for each module in the project. Expand a module to view the list of dimensional objects in the module. Selecting an object from list stores the dimension in the same analytic workspace as the selected object.

  • AW Tablespace Name: Enter the name of the tablespace in which the analytic workspace is stored.

Dimensions with multiple hierarchies can sometimes use the same source column for aggregate levels (that is, any level above the base). In such cases, you select the Generate surrogate keys in the analytic workspace option. During a load operation, the level name is added as a prefix to each value. It is recommended that you select this option unless you know that every dimension member is unique.

If you are sure that dimension members are unique across levels, then you can use the exact same names in the analytic workspace as the source. For example, if your relational schema uses numeric surrogate keys to assure uniqueness, then you need not create new surrogate keys in the analytic workspace. The Use natural keys from data source option enables you to use the same natural keys from the source in the analytic workspace.

Note:

If you edit a dimension and change the Storage type from ROLAP to MOLAP, then the data type of the surrogate identifier is changed to VARCHAR2.

Attributes Tab

Use the Attributes tab to define the dimension attributes. The Attributes tab contains two sections: Sequence and Dimension Attributes.

Sequence The Sequence attribute is required only for dimensions that have a relational implementation and that have a surrogate identifier defined. Use the Sequence field to specify the name of the database sequence that populates the dimension key column. Click Select to the right of this field to display the Available Sequences dialog box. This dialog box contains a node for each module in the project. Expand a module node to view the sequences contained in the module. Select a sequence from the displayed list.

Dimension Attributes Use the Dimension Attributes section to define the details of the dimension attributes as described in "Dimension Attributes Page".

Levels Tab

Use the Levels tab to define the dimension levels and the attributes for each level in the dimension. You also use this tab to create value-based hierarchies.

Before you define level attributes, ensure that the dimension attributes are defined on the Dimension Attributes tab. To define the level attributes for a level, you must select the dimension attributes that the level implements. The Levels tab contains two sections: Levels and Level Attributes.

Levels The Levels section displays the levels in the dimension. Provide the following details for each level:

  • Name: Enter the name of the dimension level. The name must be unique within the dimension.

  • Description: Enter an optional description for the level.

Level Attributes The Level Attributes section lists all the dimension attributes defined on the Attributes tab. The values that you specify in this section are applicable to the level selected in the Levels section. The Level Attributes section contains the following:

  • Dimension Attribute Name: Represents the name of the dimension attribute.

  • Applicable: Select the Applicable option if the level selected in the Levels section implements this dimension attribute.

  • Level Attribute Name: Represents the name of the level attribute. Use this field to specify a name for the level attribute, a name that is different from that of the dimension attribute. This is an optional field. If you do not specify a name, then the level attribute will have the same name as the dimension attribute.

  • Description: Specify an optional description for the level attribute.

  • Default Value: Specify the default value of the level attribute.

For example, to specify that the Groups level implements the dimension attributes ID, Name, and Description:

  • Select the Groups level in the Levels section.

  • In the Level Attributes section, select the Applicable option for the ID, Name, and Description attributes.

Hierarchies Tab

Use the Hierarchies tab to create dimension hierarchies. The Hierarchies tab contains two sections: Hierarchies and Levels.

Hierarchies Use the Hierarchies section to define the hierarchies in the dimension. For each hierarchy, define the following:

  • Hierarchy: Represents the name of the hierarchy. To create a new hierarchy, enter the name of the hierarchy in this field.

  • Value-based: Select this option to create a value-based hierarchy. A value-based hierarchy contains no levels. It must have an attribute identified as the parent identifier. Since you can create value-based hierarchies only for MOLAP dimensions, this option is displayed only if you select MOLAP: Multidimensional storage on the Storage tab.

    See Also:

    Oracle Warehouse Builder Concepts for information about value-based hierarchies
  • Description: Enter an optional description for the hierarchy.

  • Default: Select the Default option if the hierarchy is the default hierarchy for the dimension. When a dimension has more than one hierarchy, query tools show the default hierarchy. It is recommended that you set the most commonly used hierarchy as the default hierarchy.

To delete a hierarchy, right-click the cell to the left of the Hierarchy field and select Delete. Alternatively, you can select the hierarchy by clicking the cell to the left of the Hierarchy field and press the Delete button.

When you create a hierarchy, ensure that you create the attributes that store the parent level references for each level. For a relational or ROLAP dimension, create two attributes to store the surrogate identifier reference and business identifier reference of each level. For a MOLAP dimension, create one attribute to store the reference to the business identifier of the parent level of each level.

Levels The Levels section lists all the levels defined on the Levels tab of the Dimension Editor. Use this section to specify the levels used in each hierarchy. The Levels section contains the following:

  • Level: Represents the name of the level. Click the list to display all the levels defined in the dimension.

  • Skip to Level: Represents the parent level of the level indicated by the Level field. Use this field to define skip-level hierarchies.

    For example, the Products dimension contains the following hierarchy:

    Total > Product

    This hierarchy does not include the Groups level. Thus the Product level must skip the Groups level and use the Total level as a parent. To create this hierarchy, select the Product level in the Level field and select Total from the Skip to Level list.

  • Summary Level: Represents the dimension level used to load summaries in the analytic workspace. This option is displayed only if you select ROLAP: with Cube MVs on the Storage tab.

Use the arrows to the left of the Levels section to change the order in which the levels appear in the section.

SCD Tab

Use this tab to specify the type of slowly changing policy that the dimension implements. Since you can create a Slowly Changing Dimension only for dimensions that use a relational implementation, the options on this tab are enabled only if you select ROLAP: Relational Storage or ROLAP: with Cube MVs on the Storage tab.

Note:

If you choose a MOLAP implementation on the Storage Tab, then the options on this tab are disabled.

The options that you can select for slowly changing policy are:

Note:

You cannot create a Type 2 or Type 3 Slowly Changing Dimension if you have specified the type of storage as MOLAP.

When you create a Type 2 or Type 3 SCD using the Dimension Editor, you must create the dimension attributes that store the effective data and expiration date and apply them to the required levels.

Orphan Tab

The Orphan tab defines the orphan management policy used while loading data into the dimension or removing data from the dimension. This tab contains two sections: Orphan Management for Removal and Orphan Management for Loading.

Orphan Management for Loading Use this section to specify the orphan management policy for loading data into a dimension. You can specify different orphan management policies for records that have null parent records and records that have invalid parent records. Use the options under the heading Null parent key values to specify the orphan management policy for records that have a null parent. Use the options under the heading Invalid parent key values to specify the orphan management policy for records that have an invalid parent record.

For records with a null parent and records with an invalid parent, select one of the following orphan management policies:

  • No Maintenance: Warehouse Builder does not actively detect, reject, or fix orphan rows.

  • Default Parent: Warehouse Builder assigns a default parent row for any row that does not have an existing parent row at the time of loading data. You use the Settings button to define the default parent row. For more information about assigning a default parent row, refer "Specifying the Default Parent for Orphan Rows".

  • Reject Orphan: Warehouse Builder does not insert the row if it does not have an existing parent row.

Orphan Management for Removal You use this section to specify the orphan management policy for removing data from a dimension. Select one of the following options:

  • No maintenance: Warehouse Builder does not actively detect, reject, or fix orphan rows.

  • Reject Removal: Warehouse Builder does not remove a row if the row has existing child rows.

Deployment Options 

Select Deploy Error Table(s) to generate and deploy the error tables related to orphan management along with the dimension.

Specifying the Default Parent for Orphan Rows

Use the Default Parent dialog to specify the default parent record of an orphan row. You can specify a default parent record for all the dimension levels.

The Default Parent dialog contains a row for each dimension level. displays a table that contains the following four columns:

  • Levels: The Levels column displays a node for each level in the dimension. Expand a level node to display all the attributes in the level.

  • Identifying Attribute: Represents the name of the level attribute.

  • Data Type: Displays the data type of the attribute.

  • Default Value: Specify a default value for the level attribute.

Physical Bindings Tab

Use the Physical Bindings tab to bind the dimension to its implementation objects. Binding is the process of specifying the database columns that will store the data of each attribute and level relationship in the dimension. When you use the Create Dimension wizard to create a dimension, binding is automatically performed. When you use the editor to create a dimension, you must specify the details of the database tables or views that store the dimension data.

Choose one of the following options to bind dimension attributes to the database columns that store their data:

  • Auto binding

  • Manual binding

Auto Binding When you perform auto binding, Warehouse Builder maps the attributes in the dimension to the database columns that store their data. When you perform auto binding for the first time, Warehouse Builder also creates the tables that are used to store the dimension data.

To perform auto binding, select the dimension in the Projects Navigator or on the Physical Bindings tab. From the file menu, select Bind. Alternatively, right-click the dimension in the Projects Navigator and select Bind. For more information about the auto binding rules, see "Auto Binding".

When you perform auto binding on a dimension that is already bound, Warehouse Builder uses the following rules:

  • If the implementation method of the dimension remains the same, then Warehouse Builder rebinds the dimensional object to the existing implementation objects. The implementation method can be either Star or Snowflake.

    For example, you create a Products dimension using the star schema implementation method and perform auto binding. The dimension data is stored in a table called Products. You modify the dimension definition at a later date but retain the implementation method as star. When you now auto bind the Products dimension, Warehouse Builder rebinds the Products dimension attributes to the same implementation tables.

  • If the implementation method of a dimension is changed, then Warehouse Builder deletes the old implementation objects and creates a new set of implementation tables. If you want to retain the old implementation objects, then you must first unbind the dimensional object and then perform auto binding.

    For example, you create a Products dimension using the star schema implementation method and bind it to the implementation table. You now edit this dimension and change its implementation method to snowflake. When you now perform auto binding for the modified Products dimension, Warehouse Builder deletes the table that stores the dimension data, creates new implementation tables, and binds the dimension attributes and relationships to the new implementation tables.

Manual Binding In manual binding, you must explicitly bind the attributes in each level of the dimension to the database columns that store their data. You can either bind to existing tables or create new tables and bind to them. You would typically use manual binding to bind existing tables to a dimension. Use manual binding if no auto binding or rebinding is required.

To perform manual binding:

  1. In the Projects Navigator, right-click the dimension and select Open.

    Warehouse Builder displays the editor for this dimension.

  2. On the Physical Bindings tab, right-click a blank area, select Add and then select the type of database object that stores the dimension data.

    For example, if the dimension data is stored in a table, then right-click a blank area on the Physical Bindings tab, select Add and then Table. Warehouse Builder displays the Add a new or existing Table dialog box. To store the dimension data, you either select an existing table or create a new table.

  3. Repeat Step 2 as many times as the number of database objects that are used to store the dimension data. For example, if the dimension data is stored in three database tables, then perform Step 2 thrice.

  4. Bind each attribute in the dimension to the database column that stores its data.

After you define a dimension and perform binding (for ROLAP dimensions only), you must deploy the dimension and its associated objects. For more information about deploying dimensions, see "Deploying Dimensions".

Limitations of Deploying Dimensions to the OLAP Catalog

For dimensions with a ROLAP implementation, there are implications and limitations related to the various dimension structures when either reporting on the underlying tables or deploying to the OLAP catalog. Although the dimension may be successfully deployed, errors could occur when other applications, such as Oracle Discoverer access the OLAP catalog.

The following are items that are affected by this limitation:

  • No reporting tool has metadata about all aspects of dimensional metadata we capture, so this must be incorporated into the query/reports. Otherwise you will see odd information because of the way the data is populated in the implementation tables.

    The dimension and cube implementation tables store solved rows which contain negative key values. You can filter out these rows in your queries or reports. When you create a query or report, use the view that is associated with a dimension instead of the dimension itself. Each dimension has a view that is associated with it. The view name is specified in the configuration parameter View Name of the dimension or cube.

  • Skip-level hierarchies and ragged hierarchy metadata is not deployed to the OLAP catalog.

    If you create a dimension that contains skip-level or ragged hierarchies, then the metadata for these is stored in the Warehouse Builder repository but is not deployed to the OLAP catalog.

  • Dimensions with multiple hierarchies must have all dimension attributes mapped along all the hierarchies.

Using Control Rows

Control rows enable you to link fact data to a dimension at any level. For example, you may want to reuse a Time dimension in two different cubes to record the budget data at the month level and the actual data at the day level. Because of the way dimensions are loaded with control rows, you can perform this without any additional definitions. Each member in a dimension hierarchy is represented using a single record.

Warehouse Builder creates control rows when you load data into the dimension. All control rows have negative dimension key values starting from -2. For each level value of higher levels, a row is generated that can act as a unique linking row to the fact table. All the lower levels in this linking or control rows are nulled out.

Consider the Products dimension described in "Dimension Example". You load data into this dimension from a table that contains four categories of products. Warehouse Builder inserts control rows in the dimension as shown in Table 3-6. These rows enable you to link to a cube at any dimension level. Note that the table does not contain all the dimension attribute values.

Table 3-6 Control Rows Created for the Products Dimension

Dimension Key Total Name Categories Name Product Name

-3

TOTAL

   

-9

TOTAL

Hardware

 

-10

TOTAL

Software

 

-11

TOTAL

Electronics

 

-12

TOTAL

Peripherals

 

Determining the Number of Rows in a Dimension

To obtain the real number of rows in a dimension, count the number of rows by including a WHERE clause that excludes the NULL rows. For example, to obtain a count on Products, count the number of rows including a WHERE clause to exclude NULL rows in Product.

Creating Slowly Changing Dimensions

You can create an SCD either using the Create Dimension Wizard or the Dimension Editor.

To create an SCD using the Create Dimension Wizard, use the Slowly Changing Dimension page of the Create Dimension Wizard. You only specify the type of SCD that you want to create on this page. Warehouse Builder assumes default values for all other required parameters. For more information about the Slowly Changing Dimension page, see "Slowly Changing Dimension Page".

Note:

Type 1 does not require additional licensing; however, Type 2 and Type 3 SCDs require the Warehouse Builder Enterprise ETL Option.

To create a Type 2 SCD or a Type 3 SCD, in addition to the regular dimension attributes, you need additional attributes that perform the following roles.

Triggering Attribute

These are attributes for which historical values must be stored. For example, in the PRODUCTS dimension, the attribute PACKAGE_TYPE of the Product level can be a triggering attribute. This means that when the value of this attribute changes, the old value needs to be stored.

Effective Date

This attribute stores the start date of the record's life span.

Expiration Date

This attribute stores the end date of the record's life span.

An attribute can play only one of the above roles. For example, an attribute cannot be a regular attribute and an effective date attribute. When you use the wizard to create a Type 2 SCD or a Type 3 SCD, Warehouse Builder creates the required additional attributes.

Creating Type 2 Slowly Changing Dimensions Using the Dimension Editor

A Type 2 SCD stores the full history of values for each attribute and level relationship.

To create a Type 2 SCD using the Dimension Editor, define the following:

  • The attributes that trigger history saving.

  • The attributes that store the effective date and the expiration date.

Note:

You can create a Type 2 SCD only for dimensions that have a relational implementation.

To create a Type 2 SCD using the Dimension Editor:

  1. From the Projects Navigator, expand the Databases node and then the Oracle node.

  2. Expand the target module where you want to create the Type 2 SCD.

  3. Right-click Dimensions, select New, then Dimension without using Wizard.

  4. Provide information about the Name tab of the Dimension Editor as described in the "Name Tab".

  5. On the Attributes tab, for each level, create two additional attributes to store the effective date and the expiration date. For more information about creating attributes, see "Attributes Tab".

  6. Provide information about the following tabs of the Dimension Editor:

  7. On the Slowly Changing tab, select the Type 2: Store the complete change history option.

  8. Click Settings to the right of this option.

    Warehouse Builder displays the Type 2 Slowly Changing Policy dialog box. Specify the details of the Type 2 SCD as described in "Type 2 Slowly Changing Dimension Dialog Box".

  9. Provide information about the Storage Tab of the Dimension Editor.

Type 2 Slowly Changing Dimension Dialog Box

Use the Type 2 Slowly Changing Dimension dialog box to specify the effective date attribute, expiration date attribute, and the versioned attribute. This dialog box displays a table that contains the following columns: Levels, Identifying Attribute, Data Type, and Record History.

  • Levels: Represents the levels in the dimension. Expand a level node to view its level attributes.

  • Identifying Attribute: Represents the level attribute.

  • Data Type: Represents the data type of the level attribute.

  • Record History: Use this list to indicate that an attribute is versioned or that it stores the effective date or expiration date of the level record.

    • Trigger History: Select this option for an attribute if the attribute should be versioned.

    • Effective Date: Select this option for an attribute if it stores the value of the effective date of the level record.

    • Expiration Date: Select this option for an attribute id it stores the expiration date of the level record.

    The surrogate ID and the business ID of a level cannot be versioned.

For example, in the PRODUCTS Type 2 SCD, the attributes that store the effective date and expiration date are EFFECTIVE_TIME and EXPIRATION_TIME respectively. You must create these dimension attributes and apply them to the Product level. The attribute PACKAGE_TYPE should be versioned. Thus, for this attribute, you select Trigger history under the Record History column. When the value of the PACKAGE_TYPE attribute changes, the existing record is closed and a new record is created using the latest values.

Updating Type 2 Slowly Changing Dimensions

All the levels in a dimension need not store historical data. Typically, only the lowest level, also called the leaf level, stores historical data. However, you can also store historical data for other dimension levels.

When a record in a Type 2 SCD is versioned, the old record is marked as closed and a new record is created with the updated values. The expiration date of the record is set to indicate that it is closed. The new record is referred to as the current record and, by default, has a default expiration of NULL. While loading data into the Type 2 SCD, you can set the expiration date by using the configuration parameters for the Dimension operator. For more information, see "Dimension Operator".

You can update the following in a Type 2 SCD:

  • Leaf level attribute

  • Leaf level versioned attribute

  • Non-leaf level attribute

  • Non-leaf level versioned attribute

  • Leaf level parent attribute

The following sections describe the Warehouse Builder functionality for these update operations.

Updating a Leaf Level Attribute 

When you update a leaf level attribute, the value of this attribute is updated in the corresponding record.

For example, if you update the value of C_HOME_PHONE in a Customer level record, then the record is updated with the changed phone number.

Updating a Leaf Level Versioned Attribute 

When you update a leaf level versioned attribute, the current record is marked as closed. A new record is created with the updated value of the versioned attribute.

For example, if you update the marital status of a customer, then the current record is marked as closed. A new record with the updated marital status is created for that customer.

Updating a non-leaf Level Attribute 

When you update an attribute in a non-leaf level, the open records of the non-leaf level and the child records corresponding to this non-leaf level are updated with the new value.

For example, when you update the H_ADDRESS attribute in a Household level record, the current open record for that household is updated. All open child records corresponding to that particular household are also updated.

Updating a non-leaf Level Versioned Attribute 

The update functionality depends on whether hierarchy versioning is enabled or disabled.

Hierarchy Versioning Disabled

The non-leaf level record corresponding to the versioned attribute is closed and a new record is created with the updated value. The child records of this non-leaf level record are updated with the changed value of the non-leaf level versioned attribute.

For example, when the value of H_ZIP in a Household level record is updated, the current open record for that household is closed. A new record with the updated value of H_ZIP is created. The value of H_ZIP is updated in all the child records corresponding to the updated household record.

Hierarchy Versioning Enabled

The non-leaf level record corresponding to the versioned attribute is closed and a new record is created with the updated value. Child records corresponding to this non-leaf level record are also closed and new child records are created with the updated value.For example, when the value of H_ZIP in a Household level record is updated, the current open record for that household and its corresponding child records are closed. New records are created, with the updated value, for the household and for the child records corresponding to this household.

Updating the Leaf Level Parent Attribute 

In addition to updating the level attributes in a Type 2 SCD, you can also update the parent attribute of a child record. In the Customers Type 2 SCD, the attribute H_BUSN_ID in a Customer record stores the parent attribute of that customer. The update functionality for the leaf level parent attribute depends on whether hierarchy versioning is enabled or disabled.

Hierarchy Versioning Disabled

The child record is updated with the new parent attribute value.

For example, when you update the value of the H_BUSN_ID attribute representing the parent record of a Customer record, the Customer record is updated with the new values.

Hierarchy Versioning Enabled

The child record is closed and a new record with the changed parent attribute value is created.

For example, when you update the H_BUSN_ID attribute of a customer record, the current customer record is closed. A new customer record with the updated H_BUSN_ID is created.

Creating Type 3 Slowly Changing Dimensions Using the Dimension Editor

A Type 3 SCD stores two versions of values for certain selected attributes. You can create a Type 3 SCD only for dimensions that have a relational implementation. Specify the following:

  • The attributes that should be versioned.

  • The attributes that will store the previous value of each versioned attribute.

    For each versioned attribute, you must create an additional attribute to store the previous value of the attribute. For example, if you want to version the Population attribute, then you create an additional attribute to store the previous value of population.

To create a Type 3 SCD:

  1. From the Projects Navigator, expand the Database node and then the Oracle node.

  2. Expand the target module where you want to create the Type 3 SCD.

  3. Right-click Dimensions, select New.

    The New Gallery dialog box is displayed.

  4. Select Dimension without Using Wizard and click OK.

  5. Provide information about the Name tab of the Dimension Editor as described in "Name Tab".

  6. On the Attributes tab, for each level, create an additional attribute to store the expiration date of the attributes in the level as described in "Attributes Tab".

    Consider an example where you want to store previous values for the package_type and package_size attributes of the Products dimension. In this case, create two new attributes prev_package_type and prev_package_size to store the previous values of these attributes.

  7. Provide information about the following tabs of the Dimension Editor:

  8. On the Slowly Changing tab, select the Type 3: Store only the previous value option. Click Settings to the right of this option.

    Warehouse Builder displays the Type 3 Slowly Changing Policy dialog box. Specify the details of the Type 2 SCD using this dialog box as described in "Type 3 Slowly Changing Dimension Dialog Box".

  9. Provide information about the Storage Tab of the Dimension Editor.

Type 3 Slowly Changing Dimension Dialog Box

Use the Type 3 Slowly Changing Dimension dialog box to specify the implementation details. Use this dialog box to select the attribute that stores effective date, the attributes that should be versioned, and the attributes that store the previous value of the versioned attributes.

This dialog box displays a table that contains four columns: Levels, Identifying Attribute, Previous Attribute, and Record History.

  • Levels: Displays the levels in the dimension. Expand a level node to view the level attributes.

  • Identifying Attribute: Represents the level attribute.

  • Previous Attribute: Represents the attribute that stores the previous value of the versioned attribute. Use the list to select the previous value attribute. Specify a previous value attribute only for versioned attributes. You must explicitly create the attributes that store the previous values of versioned attributes. Again, create these as dimension attributes and apply them to the required level.

  • Effective: Indicates if an attribute stores the effective date. If the attribute stores the effective date, then select Effective date from the Effective list.

    The surrogate ID of a level cannot be versioned.

Consider the PRODUCTS Type 3 SCD. The EFFECTIVE_TIME attribute stores the effective date of the Product level records. The PACKAGE_TYPE attribute of the Product level should be versioned. The attribute that stores the previous value of this attribute, represented by the Previous Attribute column, is PREVIOUS_PACKAGE_TYPE. When the value of the PACKAGE_TYPE attribute changes, Warehouse Builder does the following:

  • Moves the existing value of the PACKAGE_TYPE attribute the PREVIOUS_PACKAGE_TYPE attribute.

  • Stores the new value of population in the PACKAGE_TYPE attribute.

Editing Dimension Definitions

Use the Dimension Editor to edit the definition of a dimension. When you edit a dimension definition, the changes are made only in the object metadata. To update the physical object definition, deploy the modified dimension using the Control Center.

Note:

Once you create a Slowly Changing Dimension, you cannot modify its type using the Dimension Editor.

To edit a dimension or Slowly Changing Dimension definition:

Right-click the dimension in the Projects Navigator and select Open.

or

Double-click the dimension in the Projects Navigator.

The Dimension Editor is displayed. Modify the definition using the tabs in the Dimension Editor.

Note:

When you modify the implementation (star or snowflake) of a relational or ROLAP dimension, ensure that you first unbind the dimension and then perform binding. This creates the physical bindings according to the modified implementation.

For more information about these tabs, see the following sections:

Configuring Dimensions

When you configure a dimension, you configure both the dimension and the underlying table.

To configure the physical properties for a dimension:

  1. From the Projects Navigator, right-click the dimension name and select Configure.

    The Configuration tab is displayed.

  2. Configure the dimension parameters listed under the following categories.

    For a dimension that uses a relational or ROLAP implementation, you can also configure the implementation tables. For more information, see "Configuring Tables".

Identification

Deployable: Select TRUE to indicate that you want to deploy this dimension. Warehouse Builder generates scripts only for table constraints marked deployable.

Deployment Options: Use this parameter to specify the type of implementation for the dimension. Select one of the following options: Deploy All, Deploy Data Objects Only, Deploy to Catalog Only.

For more information about deployment options, see "Specifying How Dimensions are Deployed".

View Name: Specify the name of the view that is created to hide the control rows in the implementation table that stores the dimension data. This is applicable for relational or ROLAP dimensions that use a star schema. The default view name, if you do not explicitly specify one, is the dimension name suffixed with "_v".

Visible: This parameter is not used in code generation.

Summary Management

The parameters in this section need to be set only if the dimension uses a ROLAP with MVs implementation.

Specifying How Dimensions are Deployed

You can specify the form in which dimensions are deployed to the target schema by setting the Deployment Option configuration parameter. The values you can set for deployment option of dimensions are: Deploy All, Deploy Data Objects Only, and Deploy to Catalog. For steps on setting the Configuration Options parameter, see "Configuring Dimensions".

In addition to the Deployment Option configuration parameter, the form in which dimensions are deployed also depends on the generation mode you specify. The PL/SQL Generation Mode parameter of the Oracle module containing the dimension represents the Oracle Database to which objects in the module are deployed. You can set the PL/SQL Generation Mode to one of the following options: Default, Oracle10g, Oracle10gR2, Oracle11gR1, Oracle11gR2, Oracle8i, and Oracle9i. For more information, see "Configuring Target Modules".

Table 3-7 describes how dimensions with ROLAP implementations are deployed on different Oracle Database versions.

Table 3-7 Deployment Options for ROLAP Dimensions

Deployment Option Target Schema: Oracle Database 10g, ROLAP Implementation Target Schema: Oracle Database 11g, ROLAP Implementation Target Schema: Oracle Database 11g, ROLAP with MVs Implementation

Deploy Data Objects

relational dimension DDL

relational dimension DDL

relational dimension DDL

Deploy to Catalog

CWM2

CWM2

11g form AW+

Deploy All

relational dimension DDL and CWM2

relational dimension DDL and CWM2

relational dimension DDL and 11g form AW+


Table 3-8 describes how dimensions with a MOLAP implementation are deployed on different Oracle Database versions.

Table 3-8 Deployment Options for Dimensions with a MOLAP Implementation

Deployment Option Target Schema: Oracle 10g Database Target Schema: Oracle 11g Database

Deploy Data Objects

10g form AW

11g form AW

Deploy to Catalog

10g form AW

11g form AW

Deploy All

n/a

n/a


Creating Cubes

Warehouse Builder provides the following two methods of creating a cube:

Alternatively, you can use the Create Cube wizard to quickly create a basic cube object. Then use the Cube Editor to specify the other options.

About Calculated Measures in Cubes

While defining measures in a cube, you can also create calculated measures. A calculated measure is a measure whose data is not stored. Its value is calculated when required using the expression defined for the measure.

Calculated measures can be classified into the following two types:

Standard Calculation

Standard calculations are based on the templates. Warehouse Builder enables you to define the following standard calculations: Basic Arithmetic, Advanced Arithmetic, Prior/Future Comparison, and Time Frame.

Basic Arithmetic

This type enables you to perform basic arithmetic calculations such as the following. Table 3-9 lists the basic arithmetic calculations.

Table 3-9 List of Basic Calculated Measures

Calculation Name Description

Addition

Use this calculation to add either two measures or a measure and a number.

Subtraction

Use this calculation to subtract two measures or a measure and a number.

Multiplication

Use this calculation to multiply two measures or a measure and a number.

Division

Use this calculation to divide two measures or a measure and a number.

Ratio

 

Advanced Arithmetic

This type enables you to create the advanced calculations such as the ones defined in Table 3-10.

Table 3-10 List of Advanced Arithmetic Calculated Measures

Calculation Name Description

Cumulative Total

Use this calculation to return the cumulative total of measure data over time periods within each level of a specified dimension.

For example, Cumulative Sales for 2001= Sales Q1 + Sales Q2 + Sales Q3 + Sales Q4

Index

Use this calculation to return the ratio of a measure's value as a percentage of a baseline value for the measure. The formula for the calculation is:

(Current member / Base Line member)

For example, Consumer Price Index (assuming baseline cost of goods is 1967) = (2001 Cost of Goods/1967 Cost of Goods)) * 100

Percent Markup

Use this calculation to return the percentage markup between two measures where the basis for the calculation is the older measure. The formula used for this calculation is: (y-x)/x.

For example, the new price is 110 and the old price is 100. The percentage markup is calculated:

(110-100)/100 = +10%.

Percent Variance

Use this calculation to return the percent difference between a measure and a target for that measure. For example, the percentage variance between sales and quota is:

(Sales-Quota)/Quota.

Rank

Use this calculation to return the numeric rank value of each dimension member based on the value of the specified measure. For example, the rank of TV sales where DVD is 150, TV is 100, and Radio is 50 would be 2.

Share

Use this calculation to return the ratio of a measure's value to the same measure value for another dimension member or level. The formula for this calculation is:

(Current member / Specified member).

Variance

Use this calculation to calculate the variance between a base measure and a target for that measure. An example of variance is:

Sales Variance = Sales - Sales Forecast.


Prior/Future Comparison

Use this type to define prior and future value calculations such as the ones described in Table 3-11.

Table 3-11 List of Prior/Future Comparison Calculated Measures

Calculated Measure Name Description

Prior Value

Use this calculation to return the value of a measure from an earlier time period.

Difference from Prior Period

Use this calculation to return the difference between the current value of a measure and the value of that measure from a prior period. The formula for this calculation is:

(Current Value - Previous Value)

Percent Difference from Prior Period

Use this calculation to return the percentage difference between the current value of a measure and the value of that measure from a prior period. The formula for this calculation is:

((Current Value - Previous Value) / Previous Value)

Future Value

Use this calculation to return the value of an item for a future time period. For example, Sales a Year from Now = Sales from October 2006 if the current time is October 2005.


Time Frame

This type enables you to create the time series calculations listed in Table 3-12.

Table 3-12 List of Time Series Calculated Measures

Calculated Measure Name Description

Moving Average

Use this calculation to return the average value for a measure over a rolling number of time periods. And example of this calculation is:

Moving average sales for the last 3 months = (Jan Sales + Feb Sales + March Sales)/3

Moving Maximum

Use this calculation to return the maximum value for a measure over a rolling number of time periods. An example of this calculation is:

Moving maximum sales for the last 3 months = the largest Sales value for Jan, Feb, and March.

Moving Minimum

Use this calculation to return the minimum value for a measure over a rolling number of time periods. An example of this calculation is:

Moving minimum sales for the last 3 months = the smallest Sales value for Jan, Feb, and March.

Moving Total

Use this calculation to return the total value for a measure over a rolling number of time periods. An example of this calculation is:

Moving total sales for the last 3 months = (Jan Sales + Feb Sales + March Sales).

Period to Date

Use this calculation to sum measure data over time periods, to create cumulative measure data. An example of this calculation is:

Year-to-date sales to March = Jan Sales + Feb Sales + March Sales.


Custom Expression

Select the Custom Expression option to specify an expression that is used to compute the calculated measure.

Cube Example

The Sales cube stores aggregated sales data. It contains the following two measures: Value_sales and Dollar_sales.

  • Value_sales: Stores the amount of the sale in terms of the quantity sold.

  • Dollar_sales: Stores the amount of the sale.

Table 3-13 describes the dimensionality of the Sales cube. It lists the name of the dimension and the dimension level that the cube references.

Table 3-13 Dimensionality of the Sales Cube

Dimension Name Level Name

Products

Product

Customers

Customer

Times

Day


Using the Create Cube Wizard to Create Cubes

Use the following steps to create a cube using the wizard:

  1. From the Projects Navigator expand the Databases node and then the Oracle node.

  2. Expand the target module where you want to create the cube.

  3. Right-click Cubes, select New Cube.

    Warehouse Builder displays the Welcome page of the Cube wizard. Click Next to proceed. The wizard guides you through the following pages:

Name and Description Page

Use the Name and Description page to describe the cube. Enter the following details on this page:

  • Name: The name of the cube. The cube name must be unique within the module.

  • Description: Specify an optional description for the cube.

Storage Type Page

Use the Storage Type page to specify the type of storage for the cube. The storage type determines how the cube data is physically stored in the database. The options you can select for storage type are:

You select the storage type based on the volume of data stored at the lowest level of the entire cube and the refresh rate required.

ROLAP: Relational storage

Warehouse Builder stores the cube definition and its data in a relational form in the database. Use this option to create a cube that has a relational or ROLAP implementation.

Relational storage is preferable if you want to store detailed, high volume data or you have high refresh rates combined with high volumes of data. Use relational storage if you want to perform one of the following:

  • Store detailed information such as call detail records, point of sales (POS) records and other such transaction oriented data.

  • Refresh high volumes of data at short intervals.

  • Detailed reporting such as lists of order details.

Operational data stores and enterprise data warehouses are typically implemented using relational storage. You can then derive MOLAP implementations from this relational implementation to perform different types of analysis.

If the database containing the target schema has the OLAP option installed, then you can also deploy the dimensions to the OLAP catalog.

When you choose a relational implementation for a cube, the implementation table used to store the cube data is created.

ROLAP: with MVs

Warehouse Builder stores the cube definition and its data in a relational form in the database. Additionally, cube-organized MVs are created in the analytic workspace. Select this option to create a cube that uses a ROLAP implementation and stores summaries in the analytic workspace.

Using this option provides summary management based on cube-organized MVs in Oracle 11g Database. Query performance is greatly improved, without the need to make any modification to your queries.

Cubes created using the ROLAP: with MVs implementation can only store summary data in the cube MV.

When you choose the ROLAP with MVs implementation:

  • the implementation table used to store the cube data is created.

  • the cube is stored in an analytic workspace that uses the same name as the Oracle module to which the dimension belongs. The tablespace that is used to store the analytic workspace is the tablespace that is defined as the users tablespace for the schema that contains the dimension metadata.

Note:

If a cube uses the ROLAP: with Cube MVs implementation, then all dimensions that this cube references must also use the ROLAP: with Cube MVs implementation.

MOLAP: Multidimensional storage

Warehouse Builder stores the cube definition and the cube data in an analytic workspace in the database. Use this option to create a cube that has a MOLAP implementation.

Multidimensional storage is preferable when you want to store aggregated data for analysis. The refresh intervals for a multidimensional storage are usually longer than relational storage as data needs to be pre-calculated and pre-aggregated. Also, the data volumes are typically smaller due to higher aggregation levels. Use multidimensional storage to perform the following:

  • Advanced analysis such as trend analysis, what-if analysis, or to forecast and allocate data

  • Drill and pivot data with instant results

When you choose a MOLAP implementation, the name used to store the cube in the analytic workspace is generated. If no analytic workspace exists, then one is created using the name you specify.

Dimensions Page

The Dimensions page defines the dimensionality of the cube. A cube must refer to at least one dimension. You define dimensionality by selecting the dimensions that the cube references. You can use the same dimension to define multiple cubes. For example, the dimension TIMES can be used by the SALES cube and the COST cube.

The Dimensions page contains two sections: Available Dimensions and Selected Dimensions.

Available Dimensions The Available Dimensions section lists all the dimensions in the workspace. Each module in the project is represented by a separate node. Expand a module node to view all the dimensions in that module.

Warehouse Builder filters the dimensions displayed in the Available Dimensions section based on the implementation type chosen for the dimension. If you select ROLAP as the storage type, then only dimensions that have a relational implementation are listed. If you select MOLAP as the storage type, then only dimensions stored in an analytic workspace are listed.

Selected Dimensions The Selected Dimensions section lists the dimensions that you selected in the Available Dimensions section. Use the right arrow to the move a dimension from the Available Dimensions list to the Selected Dimensions list.

Measures Page

Use the Measures page to define the measures of the cube. For each measure, specify the following details:

  • Name: The name of the measure. The name of the measure must be unique within the cube.

  • Description: An optional description for the measure.

  • Data Type: Select the data type of the measure.

    Note:

    The following data types are not supported for MOLAP implementations: BLOB, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, RAW, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE.
  • Length: Specify length for character data types only.

  • Precision: Define the total number of digits allowed for the measure. Precision is defined only for numeric data types.

  • Scale: Define the total number of digits to the right of the decimal point. Scale is defined only for numeric data types.

  • Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types.

Summary Page

Use the Summary page to review the options that you specified using the Cube wizard. Click Finish to complete defining the cube. This cube is displayed under the Cubes node of the Projects Navigator.

Warehouse Builder creates the metadata for the following in the workspace:

  • The cube object.

  • The definition of the table that stores the cube data.

    For a relational or ROLAP implementation, the definition of the database table that stores the cube data is created. Additionally, foreign keys are created in the table that stores the cube data to each data object that stores the data relating to the dimension the cube references.

    For a MOLAP implementation, the analytic workspace that stores the cube data is created. The wizard only creates the definitions for these objects in the workspace. It does not create the objects in the target schema.

Deploying Cubes To create the cube and its associated objects in the target schema, you must deploy the cube. Before you deploy a ROLAP cube, ensure that you successfully deploy the database table that stores the cube data. Alternatively, you can deploy both the table and the cube together. For more information, see "MOLAP Implementation of Dimensional Objects".

Note:

When you delete a cube, the associated objects such as the database table or analytic workspace are not deleted. You must explicitly delete these objects.

Defaults Used by the Create Cube Wizard

When you create a cube using the Create Cube wizard, the following defaults are used:

  • MOLAP Storage: The cube is stored in an analytic workspace that has the same name as the Oracle module in which the cube is created. The analytic workspace is stored in the users tablespace of the schema that owns the Oracle module.

  • Solve: By default, the cube is solved on demand.

  • Aggregation Function: The default aggregation function for all dimensions that the cube references is SUM.

Using the Cube Editor to Create Cubes

The Cube Editor enables advanced users to create cubes according to their requirements. You can also use the Cube Editor to edit a cube.

Use the Cube Editor to create a cube if you must:

  • Specify the dimensions along which the cube is sparse.

  • Define aggregation methods for the cube measures.

  • Precompute aggregations for a level.

To create a cube using the Cube Editor:

  1. From the Projects Navigator expand the Databases node and then the Oracle node.

  2. Expand the target module where you want to create the cube.

  3. Right-click Cubes, select New.

    The New Gallery dialog Box is displayed.

  4. Select Cube without using Wizard and click OK.

    Warehouse Builder displays the Cube Editor. To define a cube, provide information about the following tabs of the Cube Details panel:

    When you use the Cube Editor to create a cube, the physical objects that store the cube data are not automatically created. You must create these objects.

  5. To bind the cube measures and the dimension references to the database columns that store their data, see "Physical Bindings Tab". You perform this step only for cubes that use a ROLAP implementation.

Name Tab

Use the Name tab to describe the cube. Specify the following details on this tab:

  • Name: Specify a name for the cube. The cube name must be unique within the module.

  • Description: Specify an optional description for the cube.

Storage Tab

The Storage tab specifies how the cube and its data should be stored. You can select either Relational or MOLAP as the storage type.

ROLAP: Relational Storage Select the ROLAP: Relational storage option to store the cube definition and its data in a relational form in the database. Use this option to create a cube that has a relational or ROLAP implementation. The cube data is stored in a database table or view.

Select the Create bitmap indexes option to generate bitmap indexes on all the foreign key columns in the fact table. This is required for a star query. For more information, see Oracle Database Data Warehousing Guide.

Select the Create composite unique key option to create a unique key on the dimension foreign key columns.

If the database containing the target schema has the OLAP option installed, then you can also deploy the dimensions to the OLAP catalog.

ROLAP: with Cube MVs Select the ROLAP: with Cube MVs option to store the dimension definition and its data in a relational form in the database and cube materialized view summaries in the analytic workspace.

When you choose a ROLAP with MVs implementation, specify the name of the analytic workspace that should store the summary data using the AW Name field in the MOLAP: Multidimensional storage section.

Note:

If a cube uses the ROLAP: with Cube MVs implementation, then all dimensions that this cube references must also use the ROLAP: with Cube MVs implementation.

MOLAP: Multidimensional storage Select the MOLAP: Multidimensional storage option to store the cube data in an analytic workspace. Use this option to create a cube with a MOLAP implementation. Use the Analytic Workspace section to specify the storage details. Enter the following details in this section:

  • AW Name: This field specifies the name of the analytic workspace that stores the cube definition and cube data. Use the Select button to display the Analytic Workspaces dialog box. This dialog box lists the dimensional objects in the current project. Selecting an object from list stores the cube in the same analytic workspace as the selected object.

  • AW Tablespace Name: Represents the name of the tablespace in which the analytic workspace is stored. If you do not specify a name, then the analytic workspace is stored in the default users tablespace of the owner of the Oracle module.

Dimensions Tab

Use the Dimensions tab to define the dimensionality of the cube. This tab displays a table that you use to select the dimensions that the cube references and the Advanced button. You can change the order of the dimensions listed in this tab by using the arrows on the left of this tab. The Advanced button is enabled only for cubes that use MOLAP implementation or ROLAP with cube MVs implementation.

Use the Advanced button to define the sparsity of the dimensions referenced by the cube. Clicking this button displays the Advanced dialog box. Since you can define sparsity only for MOLAP cubes, the Advanced button is enabled only if the Storage type is MOLAP. For more information about the Sparsity dialog box, see "Advanced Dialog Box".

The table on the Dimensions tab contains the following columns:

  • Dimension: This field represents the name of the dimension that the cube references. Click the Ellipsis button in this field to display the Available Modules dialog box. This dialog box displays the list of dimensions in the current project. Select a dimension from this list.

    Warehouse Builder filters the dimensions displayed in this list based on the storage type specified for the cube. If you define a relational implementation for the cube, then only those dimensions that use a relational implementation are displayed. If you define a MOLAP implementation for the cube, then only the dimensions that use a MOLAP implementation are displayed.

  • Level: The Levels displays all the levels in the dimension selected in the Dimension field. Select the dimension level that the cube references.

  • Role: The Role list displays the dimension roles, if any, that the selected dimension contains. Select the dimension role that the cube uses. You can specify dimension roles for relational dimensions only.

Advanced Dialog Box

Use the Advanced dialog box to specify the sparsity of the dimensions that the cube references. Sparsity is applicable for only for MOLAP cubes and ROLAP cubes that are implemented using cube MVs. For more information about sparsity, see Oracle OLAP User's Guide.

This dialog box displays a table that contains two columns: Dimensions and Sparsity.

  • Dimensions: This column displays all the dimensions listed on the Dimension tab of the Cube Editor. The dimensions are listed in the order in which they appear on the Dimensions tab. To change the order in which the dimensions appear on this dialog box, you must change the order in which the dimensions are listed on the Dimensions Tab of the Cube Editor.

  • Sparsity: Sparsity specifies that the cube data is sparse along a particular dimension. Select Sparsity for a dimension reference if the cube data is sparse along that dimension. For example, if the data in the SALES cube is sparse along the Promotions dimension, select Sparsity for the Promotions dimension.

    All the sparse dimensions in a cube must be grouped together starting from the least sparse to the most sparse. For example, the Sales cube references the dimensions Times, Products, Promotions, and Channels. This is the order in which the dimensions are listed in the Advanced dialog box. The cube data is sparse along the dimensions Promotions and Channels, with Promotions being the most sparse. Then all these dimensions should appear as a group in the following order: Times, Products, Channels, and Promotions. You cannot have any other dimension listed in between these dimensions.

    Use the following guidelines to order dimensions:

    • List the time dimension first to expedite data loading and time-based analysis. Time is often a dense dimension, although it may be sparse if the base level is Day or the cube has many dimensions.

    • List the sparse dimensions in order from the one with the most members to the one with the least. For a compressed cube, list the sparse dimensions in order from the one with the least members to the one with the most.

    Defining sparsity for a cube provides the following benefits:

    • Improves data retrieval speed.

    • Reduces the storage space used by the cube.

Compress Cube Select this option to compress the cube data and then store it. Compressed storage uses less space and results in faster aggregation than a normal space storage. For more details on compressing cubes, see Oracle OLAP User's Guide.

Compressed storage is normally used for extremely sparse cubes. A cube is said to be extremely sparse if the dimension hierarchies contain levels with little change to the number of dimension members from one level to the next. Thus many parents have only one descendent for several contiguous levels. Since the aggregated data values do not change from one level to the next, the aggregate data can be stored once instead of repeatedly.

For compressed composites, you can only choose SUM and non-additive aggregation operators.

Partition Cube Select this option to partition the cube along one of its dimensions. Partitioning a cube improves the performance of large measures.

Use the table below the Partition Cube option to specify the dimension along which the cube is partitioned. The specified dimension must have at least one level-based hierarchy and its members must be distributed evenly, such that every parent at a particular level has roughly the same number of children. Use the Dimension column to select the dimension along which the cube is partitioned. Use the Hierarchy and Level columns to select the dimension hierarchy and level.

Time is usually the best choice to partition a cube because it meets the required criteria. In addition, data is loaded and rolled off by time period, so that new partitions can be created and old partitions dropped as part of the data refresh process.

Use a Global Index Select this option to create a global partitioned index.

Measures Tab

Use the Measures tab to define the cube measures. Specify the following details for each measure:

  • Name: The name of the measure. The measure name must be unique within the cube.

  • Description: An optional description for the measure.

  • Data Type: The data type of the measure.

  • Length: The maximum number of bytes for the measure. Length is specified only for character data.

  • Precision: Define the total number of digits allowed for the column. Precision is defined only for numeric data types.

  • Scale: Define the total number of digits to the right of the decimal point. Scale is defined only for numeric data types.

  • Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types.

  • Expression: Use this field to define a calculated measure. A calculated measure is a measure whose data is not stored. Its value is calculated when required using the expression defined. Click the Ellipsis button to display the Calculated Measure wizard. For more information about the Calculated Measure wizard, see "Calculated Measure Wizard".

    You can use any other measures defined in the cube to create an expression for a measure. The expression defined can be validated only at deploy time.

Note:

You can create calculated measures for MOLAP dimensions only.

Click the Generate Calculated Measures button to generate a series of standard calculations for a base measure. This is a time-saver operation for creating share, rank and time based calculations. Any calculated measure that you create using this option can also be created manually using the Calculated Measure wizard.

Calculated Measure Wizard

Use the Calculated Measure wizard to create calculated measures in a cube that uses a MOLAP implementation. These calculated measures, just like the other measures defined on the cube, are deployed to an analytic workspace. The wizard enables you create certain extra calculations that are not created when you click Generate Calculated Measures.

Define Calculated Measure Details

Use this page to define the details of the calculated measure. The contents of this page depend on the type of calculation you chose on the Select Calculated Measure Type page. For example, if you choose addition as the calculated measure type, this page displays the two lists that enable you to select the measure s that should be added.

If you chose Custom Expression on the Select Calculated Measure Type page, the Expression Builder interface is displayed. Use this interface to define a custom measure. For more information about the Expression Builder, see "About the Expression Builder".

Reviewing the Summary Information

Use the Finish page to review the information defined for the calculated measure. Click Back to change any of the selected values. Click Finish to complete the calculated measure definition.

Aggregation Tab

Use the Aggregation tab to define the aggregations that must be performed for each dimension that the cube references. You select the aggregate function that is used to aggregate data. You can also precompute measures along each dimension that the cube references. By default, aggregation is performed for every alternate level starting from the lowest level. The default aggregate function is SUM. For more details on the strategies for summarizing data, see the chapter about summarizing data in the Oracle OLAP User's Guide.

Specify the following options on the Aggregations tab:

  • Cube Aggregation Method: Select the aggregate function used to aggregate the cube data. The default selection is SUM.

  • Summary Refresh Method: Select the data refresh method. The options you can select are On Demand and On Commit.

Summary Strategy for Cube Use this section to define levels along which data should be precomputed for each dimension. The Dimension column lists the dimensions that the cube references. To select the levels in a dimension for which data should be precomputed, click the Ellipsis button in the PreCompute column to the right of the dimension name. The PreCompute dialog box is displayed. Use this dialog box to select the levels in the dimension along which the measure data is precomputed. You can specify the levels to be precomputed for each dimension hierarchy. By default, alternate levels, starting from the lowest level, are precomputed.

Note:

You cannot define aggregations for pure relational cubes (cubes implemented in a relational schema in the database only and not in OLAP catalog).
Precomputing ROLAP Cubes

For ROLAP cubes, aggregation is implemented by creating materialized views that store aggregated data. These materialized views improve query performance. For MOLAP implementations, the aggregate data is generated and stored in the analytic workspace along with the base-level data. Some of the aggregate data is generated during deployment and the rest is aggregated on the fly in response to a query, following the rules defined in the Aggregation tab.

Note:

The materialized views created to implement ROLAP aggregation are not displayed under the Materialized Views node in the Projects Navigator.

Orphan Tab

Use the Orphan tab to specify the orphan management policy to use while loading data into the cube or deleting it. The Orphan tab contains two sections: Null Dimension key values and Invalid dimension key values, that you use to specify the action to be taken for cube records with null dimension key values and cube records with invalid dimension key values respectively.

Select one of the following options to specify the orphan management policy for cube records with null and invalid dimension key values:

  • No Maintenance: Warehouse Builder does not actively detect, reject, or fix orphan rows.

  • Default Dimension Record: Warehouse Builder assigns a default dimension record for any row that has an invalid or null dimension key value. Use the Settings button to define the default parent row.

  • Reject Orphan: Warehouse Builder does not insert the row if it does not have an existing dimension record.

Select Deploy Error Table(s) to generate and deploy the error tables related to orphan management along with the dimension.

Physical Bindings Tab

After you define the cube structure, you must specify the details of the database tables or views that store the cube data. The Physical Bindings tab enables you to define the implementation objects for cubes. Choose one of the following options to bind the cube to the database object that stores its data:

  • Auto binding

  • Manual binding

Auto Binding When you perform auto binding, the measures and dimension references of the cube are automatically mapped to the database columns that store their data.

To perform auto binding, select the cube in the Projects Navigator. From the File menu, click Bind. Warehouse Builder maps the measures and dimension references in the cube to the table that stores the cube data.

See Also:

Oracle Warehouse Builder Concepts for information about auto binding rules

Manual Binding In manual binding, you must explicitly map the measures and dimension references in the cube to the database objects that store their data. You can either store the cube data in existing tables or create new tables.

To perform manual binding:

  1. Open the Cube Editor for the cube and navigate to the Physical Bindings tab.

  2. Right-click a blank area, select Add and then select the type of objects that will store the cube data. For example, if the cube data will be stored in a table, right-click a blank area, select Add and then Table.

    Warehouse Builder displays the Add a new or Existing table dialog box. You either select an existing table or create a new table to store the cube data.

  3. Map each attribute in the dimension to the database column that stores its data.

After you define the cube using the Data Object and perform binding (for ROLAP cubes only), you must deploy the cube. For more information about deploying cubes, see "Deploying Cubes".

Cubes Stored in Analytic Workspaces

Cubes that use a MOLAP implementation are stored in analytic workspaces. The analytic workspace engine in Oracle Database 10g provides APIs called AWXML. These APIs enable both client/server usage (as in Analytic Workspace Manager) and batch-like usage with java stored procedures. This section describes implementation details for MOLAP cubes.

Ragged Cube Data

If you select Use natural keys from data source on the Storage tab of a dimension, mapping code (AWXML mapping code) that can handle ragged fact data for any cube that uses this dimension is generated. The source column for the cube dimension level is actually mapped to every parent level also. This enables ragged fact data to be loaded.

If you select Generate surrogate keys in the analytic workspace on the Storage tab of a dimension, when you create a mapping that loads data at the level of this dimension, you will be loading cube dimension members for this level only.

Defining Aggregations

Warehouse Builder enables you to reuse existing dimensions without the need of defining additional hierarchies. Aggregations are generated based on the cube dimension level references you define. Only hierarchies where the cube dimension level is a member will be included in the aggregation. If the cube dimension level referenced is a non-leaf level of the hierarchy, then levels lower in the hierarchy will be excluded when the cube or measures are solved. For example, if you have two cubes, BUDGET and SALES, they can share the same dimension definitions without additional dimension hierarchy definitions.

Auto Solving MOLAP Cubes

An important attribute of the OLAP AWXML engine is its ability to auto-solve cubes that are stored in analytic workspaces. You can auto-solve both compressed and non-compressed cubes. A compressed cube is one for which the Compress Cube option on the Advanced Dialog Box is selected.

A cube is auto-solved if any of the following conditions are satisfied:

  • The cube is compressed

  • The cube is not compressed, and the following additional conditions are true:

    • The solve property for all the measures is set to Yes.

    • The dimension levels that the cube references are at the leaf level of all hierarchies the level is a member of.

  • Mapping that contains the cube is executed

Incremental Aggregation of cube is dependent on auto-solve (load and aggregate in one operation). Incremental aggregation is a property of the cube operator in the mapping and applies only to auto-solved cubes.Warehouse Builder can generate cubes that are not auto-solved cubes if any of the following conditions are true:

  • The cube is solved by the mapping that loads the cube

  • Warehouse Builder transformations are used to solve the cube

  • The cube is non-compressed and any of the following conditions are true:

    • Some of the measures have the Solve property set to No.

    • The dimension levels that the cube references are non-leaf levels of a hierarchy the level is a member of.

Solving Cube Measures

You can choose to solve only one cube measure for both compressed and non-compressed cubes. A compressed cube is one for which the Compress Cube option on the Advanced Dialog Box is selected.

To solve only one measure in a compressed cube, use the following steps:

  1. Open the Cube Editor for the cube and navigate to the Aggregation tab.

    You can open the Cube Editor by double-clicking the cube name in the Projects Navigator.

  2. Select the measure that you want to solve on the Measures section of the Aggregation tab.

  3. The Aggregation for measure section displays a row for each dimension that the cube references. In the row that represents the dimension along which you want to solve the cube, select NOAGG in the Aggregation Function column.

To solve only one measure in a non-compressed cube, you will need the latest database patch 10.2.0.2. If you have Oracle Database 10g Release 1 (10.1), then refer to bug 4550247 for details about a patch. The options defined on cube measures for solve indicate which measures will be included in the primary solve. The solve indicator on the cube operator in the map however indicates whether this solve will be executed or not. So the map can just load data or load and solve the data.

Solving Cubes Independent of Loading

You can solve cubes independent of loading using the predefined transformation WB_OLAP_AW_PRECOMPUTE. This function also enables you to solve measures independently of each other. This transformation function is available in the Globals Navigator under the Public Transformations node in the OLAP category of the Predefined node.

The following example solves the measure SALES in the SALES_CUBE:

declare
    rslt VARCHAR2(4000);
begin
    rslt:=WB_OLAP_AW_PRECOMPUTE('MART','SALES_CUBE','SALES');
end;
/

This function contains parameters for parallel solve and maximum number of job queues. If the cube is being solved in parallel, then an asynchronous solve job is launched and the master job ID is returned through the return value of the function.

Calculation Plans Generated The following calculation plans are generated:

  • Calculation plan for the cube

  • Calculation plan for each stored measure

This enables measures to be solved individually after a data load, or entire cubes to be solved. The actual calculation plan can also exclude levels based on the metadata.

Parallel Solving of Cubes

You can enable parallel solving of cubes by configuring the mapping that loads the cube. The cube operator has a property called Allow Parallel Solve and also a property for the Max Job Queues Allocated. These two properties determine if parallel solving is performed and also the size of the job pool. The default is to let the AWXML engine determine this value.

Output of a MOLAP Cube Mapping

When you execute a mapping that loads a cube, one of the output parameters is AW_EXECUTE_RESULT. When the map is executed using parallel solve, this output parameter will contain the job ID. You can then use the following data dictionary views to determine when the job is complete and what to do next:

  • ALL_SCHEDULER_JOBS

  • ALL_SCHEDULER_JOB_RUN_DETAILS

  • ALL_SCHEDULER_RUNNING_JOBS

If the mapping is not executed using parallel solve, the AW_EXECUTE_RESULT output parameter will return the 'Successful' tag or an error. For more information about the error, see the OLAPSYS.XML_LOAD_LOG table.

Restriction on Referencing a Non-Leaf Level

A cube cannot reference a non-leaf-level when a dimension is a star implementation that is defined without surrogate keys, because non-leaf records are not included in the dimension table. While the parent level information is included in the child records, there is no way to uniquely identify it. While the cube editor allows you to pick a non-leaf level for the dimension reference in the cube, you will obtain incorrect results when you load the cube.

A deployment error is possible on the fact table, if the data types of the parent level business key and the leaf-level business key do not match. In that case, the foreign key definition will be incorrect, because the foreign key column will have the data type of the parent level, but the referenced primary key column will have the data type of the leaf level.

For example, create a simple dimension with two levels, L1 and L2. Assign them business key attributes with these data types: L1 NUMBER and L2 VARCHAR2(20). Then, create a cube to reference this dimension and pick L1 (the parent) as the referenced dimension. When creating the surrogate key attribute role for the ID, change the dimension attribute to null, so that the dimension does not have surrogate keys. Rebind the cube after picking the parent level as the reference. If you then attempt to deploy the fact table, then OWB returns an ORA-XXXXX error.

Editing Cube Definitions

You can edit a cube and alter its definition using the Cube Editor. When you edit a dimension definition, the changes are made only in the object metadata. To update the physical object definition, deploy the modified dimension using the Control Center.

To edit a cube definition:

Right-click the cube in the Projects Navigator and select Open.

or

Double-click the cube in the Projects Navigator.

The Cube Editor is displayed. Edit the cube definition using these tabs. For more information about the tabs in the Cube Editor, see Using the Cube Editor to Create Cubes.

Configuring Cubes

When you configure a cube, you configure both the cube and the underlying table.

To configure the physical properties for a cube:

  1. From the Projects Navigator, right-click the cube name and select Configure.

    The Configuration tab for the cube is displayed.

  2. Configure the cube parameters listed in the following categories.

    In addition to these parameters, use the following are some guidelines for configuring a cube.

    • Foreign Key constraints exist for every dimension.

    • Bitmap indexes have been generated for every foreign key column to its referenced dimension.

Identification

Deployable: Select TRUE to indicate if you want to deploy this cube. Warehouse Builder generates scripts only for table constraints marked deployable.

Deployment Options: Use this parameter to specify the type of implementation for the cube. The options are:

Materialized View Index Tablespace: The name of the tablespace that stores the materialized view indexes.

Materialized View Tablespace: The name of the tablespace that stores the materialized view created for the cube.

Visible: This parameter is not used in code generation.

Summary Management

For information about the parameters Enable MV Refresh, MV constraints, Refresh Mode, Refresh Next Date, Refresh On, and Refresh Start Date, see "Summary Management".

Specifying How Cubes are Deployed

You can specify the form in which cubes are deployed to the target schema by setting the Deployment Option configuration parameter. The values you can set for deployment option of cubes are: Deploy All, Deploy Data Objects Only, Deploy to Catalog, and Deploy Aggregation. For steps on setting the Configuration Options parameter, see "Configuring Cubes".

In addition to the Deployment Option configuration parameter, the form in which cubes are deployed also depends on the generation mode you specify. The PL/SQL Generation Mode parameter of the Oracle module containing the cube represents the Oracle Database to which objects in the module are deployed. You can set the PL/SQL Generation Mode to one of the following options: Default, Oracle10g, Oracle10gR2, Oracle11gR1, Oracle11gR2, Oracle8i, and Oracle9i. For more information, see "Configuring Target Modules".

Table 3-14 describes how cubes with ROLAP implementations are deployed on different Oracle Database versions.

Table 3-14 Deployment Options for ROLAP Cubes

Deployment Option Target Schema: Oracle Database 10g, ROLAP Implementation Target Schema: Oracle Database 11g, ROLAP Implementation Target Schema: Oracle Database 11g, ROLAP with MVs Implementation

Deploy Data Objects

n/a

n/a

n/a

Deploy to Catalog

CWM2

CWM2

11g form AW+


Table 3-15 describes how cubes with a MOLAP implementation are deployed on different Oracle Database versions.

Table 3-15 Deployment Options for Cubes with a MOLAP Implementation

Deployment Option Target Schema: Oracle Database 10g Target Schema: Oracle Database 11g

Deploy Data Objects

10g form AW

11g form AW

Deploy to Catalog

10g form AW

11g form AW

Deploy All

n/a

n/a


Creating Time Dimensions

Warehouse Builder provides the Create Time Dimension wizard that enables you to create a fully functional time dimension quickly. The mapping that populates the time dimension is also created automatically. When you choose a relational implementation for a time dimension, the implementation objects that store the time dimension data are also created.

You can also use the Dimension Editor to define a time dimension with your own specifications. In this case, you must create the implementation objects and the map that loads the time dimension.

Creating a Time Dimension Using the Time Dimension Wizard

Use the following steps to create a time dimension using the Create Time Dimension wizard:

  1. From the Projects Navigator expand the Databases node and then the Oracle node.

  2. Expand the target module where you want to create a time dimension.

  3. Right-click Dimensions, select New.

    The New Gallery dialog box is displayed.

  4. Select Time Dimension and click OK.

    Warehouse Builder displays the Welcome page of the Create Time Dimension wizard. Click Next to proceed. The wizard guides you through the following pages:

Name and Description Page

The Name page describes the time dimension. Provide the following details on the Name page:

  • Name: Type the name of the time dimension. The name must be unique within a module.

  • Description: Type an optional description for the time dimension.

Storage Page

Use the Storage page to specify how the time dimension data should be stored in the database. You select the storage type based on the volume of data stored at the lowest level of the entire cube and the refresh rate required. The storage type options are:

  • ROLAP: Relational storage: Stores the time dimension definition in a relational form in the database. Select this option to create a time dimension that uses a relational or ROLAP implementation.

    Warehouse Builder automatically creates the underlying tables required to implement this time dimension. A star schema is used to implement the time dimension.

    If the database containing the target schema has the OLAP option installed, you can also deploy the dimensions to the OLAP catalog.

  • ROLAP with MVs: Stores the time dimension definition and its data in a relational form in the database. Additionally, cube-organized MVs are created in the analytic workspace. Select this option to create a dimension that uses a relational implementation and stores summaries in the analytic workspace.

    Using this option provides summary management based on cube-organized MVs in Oracle Database 11g. Query performance is greatly improved, without the need to make any modification to your queries.

  • MOLAP: Multidimensional storage: Stores the time dimension definition and data in an analytic workspace. Select this option to create a time dimension that uses a MOLAP implementation.

    Warehouse Builder stores the time dimension in an analytic workspace with same name as the module. The tablespace that is used to store the analytic workspace is the tablespace that is defined as the users tablespace for the schema that contains the dimension metadata.

For more information about these options, see "Storage Type Page".

Data Generation Page

Use the Data Generation page to specify additional information about the time dimension such as the type of time dimension and the range of data stored in it. This page contains details about the range of data stored in the time dimension and the type of temporal data.

Range of Data The Range of Data section specifies the range of the temporal data stored in the time dimension. To specify the range, define the following:

  • Start year: The year from which to store data in the time dimension. Click the list to select a starting year.

  • Number of years: The total number of years, beginning from Start Year, for which the time dimension stores data. Specify the number of years by selecting a value from the list.

Type of Time Dimension Use the Type of Time Dimension section to specify the type of time dimension to create. Select one of the following options for type of time dimension:

  • Calendar: Creates a calendar time dimension.

  • Fiscal: Creates a fiscal time dimension. Enter the following additional details to create a fiscal time dimension:

    • Fiscal Convention: Select the convention that you want to use to represent the fiscal months. The options available are 544 and 445.

    • Fiscal Year Starting: Select the date and month from which the fiscal year starts.

    • Fiscal Week Starting: Select the day from which the fiscal week starts.

Levels Page (Calendar Time Dimension Only)

Use the Levels page to select the calendar hierarchy that should be created and the levels that it contains. Since there is no drill-up path from the Calendar Week level to any of the levels above it, the following two options are provided to create a calendar hierarchy:

  • Normal Hierarchy

  • Week Hierarchy

Normal Hierarchy The Normal Hierarchy contains the following levels:

  • Calendar year

  • Calendar quarter

  • Calendar month

  • Day

Select the levels to be included in the calendar hierarchy. You must select at least two levels.

Week Hierarchy The Week Hierarchy contains two levels: Calendar Week and Day. Use this hierarchy to create a hierarchy that contains the Calendar Week level. When you select the Week Hierarchy option, both these levels are selected by default.

Levels Page (Fiscal Time Dimension Only)

Use the Levels page to select the levels that should be included in the fiscal hierarchy. The levels you can select are:

  • Fiscal year

  • Fiscal quarter

  • Fiscal month

  • Fiscal week

  • Day

You must select a minimum of two levels. Warehouse Builder creates the fiscal hierarchy that contains the selected levels. To create additional hierarchies, use the Dimension Editor. For more information about using the Dimension Editor, see "Editing Time Dimension Definitions".

Pre Create Settings Page

The Pre Create Settings page displays a summary of the options you selected on the previous pages of the Create Time Dimension wizard. This includes the attributes, levels, hierarchies, and the name of the map that is used to populate the time dimension. Warehouse Builder uses these settings to create the objects that implement the time dimension. Click Next to proceed with the implementation of the wizard. Click Back to change any options that you selected on the previous wizard pages.

Time Dimension Progress Page

The Time Dimension Progress page displays the progress of the time dimension implementation. The progress status log on this page lists the activities that are performed by the Time Dimension wizard to implement the time dimension. After the process is completed, click Next to proceed.

Summary Page

The Summary page summarizes the options selected in the wizard pages. Use this page to review the options you selected.

Click Finish to complete the creation of the time dimension. You now have a fully functional time dimension. This dimension is displayed under the Dimensions node of the Projects Navigator. The mapping that loads this time dimension is displayed under the Mappings node in the Projects Navigator.

Warehouse Builder creates the following objects:

  • The time dimension object.

  • The sequence that populates the surrogate ID of the time dimension levels

  • The physical structures that store the time dimension data.

    For a relational implementation, the database tables that store the dimension data are created in the workspace. Warehouse Builder also binds the time dimension attributes to the database columns that store their values. For a MOLAP implementation, the analytic workspace that stores the time dimension and its data is created.

  • A mapping that populates the time dimension.

Note:

When you delete a time dimension, the table, sequence, and the mapping associated with the time dimension are not deleted. You must explicitly delete these objects.

Defaults Used by the Time Dimension Wizard

When you create a time dimension using the Time Dimension wizard, the following defaults are used:

  • Storage: The default implementation for the relational storage is the star schema. For a MOLAP implementation, the dimension is stored in an analytic workspace that has the same name as the Oracle module in which the time dimension is created. The analytic workspace is stored in the tablespace that is assigned as the users tablespace for the schema that owns the Oracle module containing the dimension.

  • Hierarchy: A standard hierarchy that contains all the levels listed on the Levels page of the Create Dimension wizard is created. The hierarchy contains the levels in the same order that they are listed on the Levels page.

Editing Time Dimension Definitions

To edit a time dimension:

  1. From the Projects Navigator expand the Databases node then the Oracle node.

  2. Expand the target module that contains the time dimension to be edited.

  3. Right-click the time dimension that you want to edit and select Open. You can also double-click the time dimension. Warehouse Builder displays the Dimension Editor for the time dimension.

  4. Edit the information about the following tabs:

When you modify a time dimension, a new population map and new implementation tables are created. You can choose to either delete the existing population map and implementation tables or to retain them.

Use the Mapping Editor to modify the time dimension population map. You must deploy the mapping that populates the time dimension.

If you delete the population map before deploying the map, you cannot populate data into the time dimension. The work around is to run the time dimension wizard again and create another dimension population map.

Name Tab

Use the Name tab to describe the Time dimension. Enter the following details on the Name tab:

  • Name: The name of the time dimension. The name must be unique within the module. For more information about naming conventions, see "Naming Conventions for Data Objects".

  • Description: An optional description for the time dimension.

  • Range of Data: Specifies the range of the data stored in the time dimension. To specify the range, define the following:

    • Starting year: The year from which data should be stored in the time dimension. Click the list to select a starting year.

    • Number of years: The total number of years, beginning from Starting Year, for which the time dimension stores data. Select a value from the list.

Storage Tab

Use the Storage tab to specify the type of storage for the time dimension. The storage options you can use are Relational or MOLAP.

Relational Selecting the Relational option stores the time dimension definition in a relational form in the database. Select one of the following options for the relational implementation of the time dimension:

  • Star schema: The time dimension is implemented using a star schema. This means that the time dimension data is stored in a single database table or view.

  • Snowflake schema: The time dimension is implemented using a snowflake schema. This means that the time dimension data is stored in multiple tables or views.

If the database containing the target schema has the OLAP option installed, you can also deploy the dimensions to the OLAP catalog.

MOLAP Select MOLAP to store the time dimension definition and data in an analytic workspace in the database. This method uses an analytic workspace to store the time dimension data. Provide the following details for a MOLAP implementation:

  • AW Name: Enter the name of the analytic workspace that stores the time dimension. Click the Ellipsis button to display a list of available AWs. Warehouse Builder displays a node for each module in the current project. Expand a module to view the list of dimensional objects in the module. Selecting an object from list stores the time dimension in the same analytic workspace as the selected object.

  • Tablespace Name: Enter the name of the tablespace that stores the analytic workspace. If you do not enter a value, then the analytic workspace is stored in the tablespace that is defined as the users tablespace for the schema containing the time dimension metadata.

Attributes Tab

The Attributes tab defines the dimension attributes and the sequence used to populate the dimension key of the time dimension. The Sequence field represents the name of the sequence that populates the dimension key column of the time dimension. Use the Select to the right of this field to select a sequence from the Available Sequences dialog box. This dialog box lists all the sequences that belong to the current project.

Dimension Attributes The Dimension Attributes section lists the dimension attributes of the time dimension. You also use this page to create new dimension attributes. For each attribute, you specify the following details:

  • Name: The name of the dimension attribute. The attribute name must be unique within the dimension.

  • Description: An optional description for the attribute.

  • Identifier: Represents the type of identifier of the attribute. The lists displays two options: Surrogate and Business. Select the type of identifier.

  • Data Type: Select the data type of the attribute.

  • Length: Specify length only for character data types.

  • Precision: Define the total number of digits allowed for the column. Precision is defined only for numeric data types.

  • Scale: Define the total number of digits to the right of the decimal point. Scale is defined only for numeric data types.

  • Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP, TIMESTAMP WITH TIME ZONE , and TIMESTAMP WITH LOCAL TIME ZONE data types.

  • Descriptor: Select the type of descriptor. The options are: Short Description, Long Description, Start date, End date, Time span, and Prior period.

Levels Tab

The Levels tab defines the levels in the time dimension. You can create additional levels by entering the name and an optional description for the level in the Levels section. For more information about the contents of the Levels tab, see "Level Attributes Page".

Hierarchies Tab

Use the Hierarchies tab to create additional hierarchies in the time dimension. When you modify the time dimension definition, the map that populates it must reflect these changes. Click Create Map to recreate the map that populates the time dimension. For a fiscal time dimension, you can modify the fiscal settings by clicking Fiscal Settings. The Fiscal Information Settings dialog box is displayed. Use this dialog box to modify the fiscal convention, fiscal year start, and fiscal week start.

The Hierarchies tab contains two sections: Hierarchies and Levels.

  • Hierarchies: Use this section to create hierarchies. Warehouse Builder displays any existing hierarchies in the time dimension. You create additional hierarchies by specifying the name of the hierarchy and type of hierarchy. The options for type of hierarchy are None, Fiscal, Calendar Week, and Calendar Year. Use the Default property to indicate which of the hierarchies is the default hierarchy.

  • Levels: The Levels section lists the levels in the time dimension. When you create a new hierarchy, choose the levels that you want to include in your hierarchy by selecting the Applicable option.

Modifying the Implementation of Time Dimensions

Use the Time Dimension editor to modify the implementation of a time dimension. The implementation details determine if the time dimension is implemented using a star schema or a snowflake schema.

For each time dimension, Warehouse Builder automatically creates a mapping that loads the time dimension. Thus, when you modify a time dimension, the mapping that loads the time dimension must also be modified.

Note:

You cannot use the Unbind option to unbind a time dimension from its implementation objects.

To modify the implementation of a time dimension:

  1. In the Projects Navigator, double-click the time dimension whose implementation you want to modify.

    The editor is opened for the time dimension.

  2. On the Storage tab, under the ROLAP: Relational Storage option, select the new implementation for the time dimension.

    To change to a star schema implementation, select Star.

    To change to a snowflake implementation, select Snowflake.

  3. On the Hierarchies tab, click Create map.

    This redefines the mapping that loads the time dimension based on the implementation changes made.

The Physical Bindings tab displays the modified bindings for the time dimension.

Populating Time Dimensions

You populate a time dimension by creating a mapping that loads data into the time dimension. When you create a time dimension using the Create Time Dimension wizard, Warehouse Builder creates a mapping that populates the time dimension based on the values of the following parameters:

The values of these parameters are initialized at the time of creating the time dimension using the Create Time Dimension wizard.

Figure 3-1 displays a mapping to load a calendar time dimension. The Mapping Input operator DATE_INPUTS represents the attributes needed to populate the time dimension. The values of the attributes in this operator are set based on the values you provide when you created the time dimension. You can modify these values by double-clicking the DATE_INPUTS operator, clicking the Output Attributes link, and modifying the values of the input parameters. However, when you modify parameter values, you must regenerate the redeploy the mapping that loads the time dimension.

Figure 3-1 Mapping that Populates a Time Dimension

Description of Figure 3-1 follows
Description of "Figure 3-1 Mapping that Populates a Time Dimension"

Dynamically Populating Time Dimensions

In certain warehouse scenarios, you may need to dynamically populate your time dimension based on the current requirements. Although the values used to populate the time dimension are set at the time of creating the time dimension, Warehouse Builder enables you to modify these values and dynamically populate the time dimension when required.

The Deployment Preferences contain a preference called Prompt for Execution Parameters. Setting this parameter to True enables you to provide values for the input parameters at runtime.

To dynamically populate time dimensions:

  1. Ensure that the Prompt for Execution Parameters preference is set to True.

    In the Tools menu, select Preferences to display the Preferences dialog box. In the left panel, expand the OWB node, click Deployment in the left panel and select Prompt for Execution Parameters.

  2. Execute the mapping that loads the time dimension by right-clicking the mapping and selecting Start.

    The Input Parameters dialog box is displayed containing the input parameters that are used to load the time dimension such as start year, number of years.

  3. Set values for the parameters YEAR_START_DATE and NUMBER_YEARS and click OK. For fiscal time dimensions, also set values for FISCAL_TYPE, DAYS_OF_FISCAL_WEEK, and FISCALYEAR_START_DATE.

Overlapping Data Populations

You can run a map that populates the time dimension multiple times. During each run you specify the attributes required to populate the time dimension. It is possible that a run of the mapping may overlap with the previous runs, meaning you may attempt to load data that already exists in the time dimension. In such a case, if a record was populated by a previous run, Warehouse Builder does not populate the data again.

For example, in the first run, you populate the time dimension with data from the year 2000 for 5 years. In the second run, you populate the time dimension with data from 2003 for 3 years. Since the records from beginning 2003 to end 2004 already exist in the time dimension, they are not created again.