3 Defining Dimensional Objects

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 must use a surrogate key if:

• your dimension is a Type 2 or Type 3 SCD. In these cases, you can have multiple dimension records loaded for each business key value, so you 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 references multiple 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 work with no surrogate key, because the leaf levels are referenced by the cube.

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. Oracle 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 triggers 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 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.

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.

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, Oracle 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 and 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.

Oracle 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, Oracle 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.

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.

Oracle 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 Oracle 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 Oracle Warehouse Builder also creates this record.

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

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 becomes orphan records.

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

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

• No Maintenance: This is the default behavior. Oracle 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

Oracle 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 equals the implementation object suffixed with an _ERR. If the implementation table is called CITY, then the error table is called CITY_ERR.

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.

Oracle 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 is 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 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 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 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.

   Oracle 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 multiple 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

Oracle 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.

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.

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:

• "ROLAP: Relational storage"

• "ROLAP: with MVs"

• "MOLAP: Multidimensional storage"

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 Oracle 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 to store detailed, high volume data or you have high refresh rates combined with high volumes of data. Use relational storage 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. Data for all the levels in the dimension is stored in a single database table.

ROLAP: with MVs Oracle 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 Database11g. Query performance is greatly improved, must make 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 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 Oracle 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 must 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 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.

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 enabled 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 enable 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.

Oracle 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.

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 Slowly Changing Dimension functionality requires the Oracle 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. Oracle 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. Oracle 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. Oracle 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. Oracle 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.

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.

Oracle 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. Oracle 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.

Oracle 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".

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 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 module.

Dimension Attributes

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

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

Hierarchies

Oracle 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. Oracle 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. Oracle 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. Oracle 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 must 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 module containing the dimension is created.

MOLAP: Multidimensional Storage

For a multidimensional storage, if it does not exist, an analytic workspace with the same name as the 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 exist, it is first created and then the dimension is stored in this analytic workspace.

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.

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. 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 multiple 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 Oracle 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. Oracle 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.

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 has 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 multiple 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.

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

• Type 1: Do not keep history: Creates a normal dimension that stores no history.

• Type 2: Store the complete change history: Select this option to create a Type 2 SCD. Click Settings to specify the additional details such as triggering attribute, effective date and expiration date for each level.as described in "Creating Type 2 Slowly Changing Dimensions Using the Dimension Editor".

• Type 3: Store only the previous value: Select this option to create a Type 3 SCD. Click Settings to specify the additional details such as effective date and the attributes used to store the previous value of versioned attributes as described in "Creating Type 3 Slowly Changing Dimensions Using the Dimension Editor".

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: Oracle Warehouse Builder does not actively detect, reject, or fix orphan rows.

• Default Parent: Oracle 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: Oracle 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: Oracle Warehouse Builder does not actively detect, reject, or fix orphan rows.

• Reject Removal: Oracle 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 stores 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, Oracle 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, Oracle 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 bound, Oracle Warehouse Builder uses the following rules:

• If the implementation method of the dimension remains the same, then Oracle 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, Oracle Warehouse Builder rebinds the Products dimension attributes to the same implementation tables.

• If the implementation method of a dimension is changed, then Oracle Warehouse Builder deletes the old implementation objects and creates a new set of implementation tables. 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, Oracle 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.

If the dimension is defined without a surrogate key, the dimension key attribute must be left unbound.

To perform manual binding:

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

   Oracle 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. Oracle 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".

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.

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.

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, Oracle 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.

Standard Calculation

Standard calculations are based on the templates. Oracle 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.

Advanced Arithmetic

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

Prior/Future Comparison

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

Time Frame

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

Custom Expression

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

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:

• "ROLAP: Relational storage"

• "ROLAP: with MVs"

• "MOLAP: Multidimensional storage"

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

Oracle 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 to store detailed, high volume data or you have high refresh rates combined with high volumes of data. Use relational storage 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

Oracle 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 11g Database. Query performance is greatly improved, without the requirement 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 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

Oracle 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 must 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.

Oracle 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 enabled 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.

Oracle 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. For more information, see "MOLAP Implementation of Dimensional Objects".

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.

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

  Oracle 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.

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 enabled 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.

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.

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.

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: Oracle Warehouse Builder does not actively detect, reject, or fix orphan rows.

• Default Dimension Record: Oracle 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: Oracle 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. Oracle Warehouse Builder maps the measures and dimension references in the cube to the table that stores the cube data.

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 stores the cube data. For example, if the cube data is stored in a table, right-click a blank area, select Add and then Table.

   Oracle 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".

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. It 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 load cube dimension members for this level only.

Defining Aggregations

Oracle 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 is included in the aggregation. If the cube dimension level referenced is a non-leaf level of the hierarchy, then levels lower in the hierarchy is 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 run.

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.Oracle 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

• Oracle 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 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 need the latest database patch 10.2.0.2. If you have Database 10g Release 1, then refer to patch 4550247 for details. The options defined on cube measures for solve indicate which measures is included in the primary solve. The solve indicator on the cube operator in the map however indicates whether this solve is processed 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 started 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

It 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 run a mapping that loads a cube, one of the output parameters is AW_EXECUTE_RESULT. When the map is run using parallel solve, this output parameter contains 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 run using parallel solve, the AW_EXECUTE_RESULT output parameter returns the 'Successful' tag or an error. For more information about the error, see the OLAPSYS.XML_LOAD_LOG table.

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.

  Oracle 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 requirement 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.

  Oracle 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 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. Oracle 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. Oracle 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.

Oracle 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. Oracle 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.

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. Dimension data is stored in a single database table or view.

• Snowflake schema: The time dimension is implemented using a snowflake schema. The 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. Oracle 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 enabled 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 re-create 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. Oracle 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 to include in your hierarchy by selecting the Applicable option.