| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
Warehouse Builder enables you to define, deploy, and load dimensional objects. You can deploy dimensional objects either to a relational schema or to an analytical workspace in the database.
This chapter includes:
To create a dimension in Warehouse Builder, use one of the following methods:
Using the Create Dimension Wizard
The wizard enables you to create a fully functional dimension object quickly. When you use the wizard, Warehouse Builder defaults many settings to the most commonly used values. You can modify these settings later using the Data Object Editor. If you choose a relational implementation for the dimension, the implementation tables are also created in the Warehouse Builder repository using auto binding.
For more information about the defaults used by the Dimension wizard, see "Defaults Used By the Create Dimension Wizard".
The Data Object Editor gives you full control over all aspects of the dimension definition and implementation. This provides maximum flexibility. You use the editor to create a dimension from scratch or to edit a previously created dimension.
Using the Time Dimension wizard
The Time Dimension wizard enables you to create and populate time dimensions. For more information on the Time Dimension wizard, see "Creating Time Dimensions".
To create a dimension using the Dimension wizard:
From the Warehouse Builder Project Explorer expand the Databases node and then the Oracle node.
Expand the target module where you want to create the dimension.
Right-click Dimensions, select New, and then Using Wizard.
Warehouse Builder displays the Welcome page of the Create Dimension wizard. Click Next to proceed. The wizard guides you through the following pages:
You use the Name and Description page to describe your dimension. Enter the following information on this page:
Name: This is the name that Warehouse Builder uses to refer to the dimension. The dimension name must be unique within a module.
Description: You can type an optional description for the dimension.
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 as follows:
Relational storage (ROLAP)
Multidimensional storage (MOLAP)
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.
Relational storage (ROLAP) Warehouse Builder stores the dimension definition and its data in a relational form in the database. Select this option to create a dimension that uses a relational or ROLAP implementation.
Relational storage is preferable if you want to store detailed, high volume data or you have high refresh rates combined with high volumes of data. Use relational storage if you want to perform one of the following:
Store detailed information such as call detail records, point of sales (POS) records and other such transaction oriented data.
Refresh high volumes of data at short intervals.
Detailed reporting such as lists of order details.
Ad-hoc queries in which changing needs require more flexibility in the data model.
Operational data stores and enterprise data warehouses are typically implemented using relational storage. You can then derive multi-dimensional implementations from this relational implementation to perform different analysis types.
When you choose a relational implementation for a dimension, Warehouse Builder creates the implementation tables used to store the dimension data. The default implementation of the dimension is using a star schema. This means that the data for all the levels in the dimension is stored in a single database table.
Multidimensional storage (MOLAP) Warehouse Builder stores the dimension definition and dimension data in an analytic workspace in the database. Select this option to create a dimension that uses a MOLAP implementation.
Multidimensional storage is preferable when you want to store aggregated data for analysis. The refresh intervals for a multidimensional storage are usually longer than relational storage as data needs to be pre-calculated and pre-aggregated. Also, the data volumes are typically smaller due to higher aggregation levels. Use multidimensional storage to perform the following:
Advanced analysis such as trend analysis, what-if analysis, or to forecast and allocate data.
Constant analysis using a well defined consistent data model with fixed query patterns.
When you choose a MOLAP implementation, Warehouse Builder stores the dimension in an analytic workspace that uses the same name as the Oracle module to which the dimension belongs. The tablespace that is used to store the analytic workspace is the tablespace that is defined as the users tablespace for the schema that contains the dimension metadata.
Note:
For information about certain limitations of deploying dimensions to the OLAP catalog, see the Oracle Warehouse Builder Release Notes.You 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, Warehouse Builder creates the following attributes 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.
Business: Indicates that the attribute is the business identifier of the dimension
Parent: In a value-based hierarchy, indicates that the attribute stores the parent value of an attribute. You can create value-based hierarchies only when you choose a MOLAP implementation for the dimension.
If the attribute is a regular dimension attribute, leave this field blank.
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 drop-down list.
Note:
The following data types are not supported for MOLAP implementations: BLOB, Interval Day to Second, Interval Year to Month, RAW, Timestamp with Time Zone, Timestamp with Local Time Zone.Length: For character data types, specify the length of the attribute.
Precision: For numeric data types, define the total number of digits allowed for the column.
Scale: For numeric data types, define the total number of digits to the right of the decimal point.
Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP data types.
Descriptor: Select the type of descriptor. The options are: Short Description, Long Description, End date, Time span, Prior period, and Year Ago Period.
Descriptors are very important for MOLAP implementations. For example, in a custom time dimension, you must have Time Span and End Date to allow time series analysis.
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 Data Object Editor only.
Enter the following details on the Levels page:
Name: This is the name of the level. The level name must be unique within the dimension.
Description: Type an optional description for the level.
List the levels in the dimension such that the parent levels appear above the child levels. Use the arrow keys to move levels so that they appear in this order.
Warehouse Builder creates a default hierarchy called STANDARD that contains the levels in the same order that you listed them on the Levels page. The attributes used to store the parent key references of each level are also created. For a relational or ROLAP dimension, Warehouse Builder creates two attributes, 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. If you choose a relational or ROLAP implementation, Warehouse Builder creates two attributes, in both the Product and Groups levels, that reference the surrogate identifier and business identifier of the parent level. If you choose a MOLAP implementation, Warehouse Builder creates an attribute in both the Product and Groups levels, that reference the business identifier of the parent level.
Note:
To create additional hierarchies, you use the Hierarchies tab of the Data Object Editor as described in Hierarchies Tab.The Level Attributes page shown in Figure 13-1 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.
Figure 13-1 Level 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:
Select the State level in the Levels section.
In the Level Attributes section, select the Applicable option for the attributes ID, Name, Description, and Budget.
By default, Warehouse Builder uses the following defaults:
The attributes ID, Name, and Description are applicable to all levels.
All dimension attributes are applicable to the lowest level in the dimension.
Use of this functionality requires the Warehouse Builder Enterprise ETL Option.
The Slowly Changing Dimension page enables you to define the type of slowly changing policy used by the dimension. This page is displayed only if you had chosen Relational storage (ROLAP) as the storage type on the Storage Type Page. For more information on slowly changing dimensions concepts, see "About Slowly Changing Dimensions".
Select one of the following options for the slowly changing policy:
Type 1: Do not store history: This is the default selection. Warehouse Builder creates a dimension that stores no history. This is a normal dimension.
Type 2: Store the complete change history: Select this option to create a Type 2 slowly changing dimension. Warehouse Builder creates the following two additional dimension attributes and makes them applicable for the lowest level in the Type 2 SCD:
Effective date
Expiration date
All the attributes of the lowest level in the Type 2 SCD, except the surrogate and business identifier, are defined as the triggering attributes.
Note:
You cannot create a Type 2 or Type 3 slowly changing dimension if the type of storage is MOLAP.Type 3: Store only the previous value: Select this option to create a Type 3 slowly changing dimension. Warehouse Builder assumes that all the level attributes at the lowest level, excluding the surrogate ID and business ID, should be versioned. For each level attribute that is versioned, Warehouse Builder creates an additional attribute to store the previous value of the attribute.
The Pre Create Settings page displays a summary of the options selected on the previous pages of the Create Dimension wizard. This includes the attributes, levels, hierarchies, storage type, and the slowly changing policy used for the dimension. Warehouse Builder uses these settings to create the dimension definition and the database tables that implement the dimension. It also binds the dimension attributes to the table columns that store the attribute data.
Click Next to proceed with the implementation of the dimension. To change any of the options you previously selected, click Back.
Note:
Review this page carefully as it summarizes the implementation and its objects.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.
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 Project Explorer.
Warehouse Builder creates the metadata for the following in the repository:
The dimension object.
The tables that store the dimension data.
For a relational implementation, a database table that stores the dimension data is created. Warehouse Builder binds the attributes in the dimension to the database columns used to store their values.
For a MOLAP implementation, the analytic workspace that stores the dimension data is created.
The database sequence used to generate the surrogate identifier for all the dimension levels.
Note that Warehouse Builder creates the definitions of these objects in the repository 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 "MOLAP Implementation of Dimensional Objects".
Note:
When you delete a dimension, the associated objects such as sequence, database tables, or AWs are not deleted. You must explicitly delete these objects.When you create a dimension using the Create Dimension wizard, Warehouse Builder sets default values for some of the attributes that are used to create the dimension. The following sections describe the defaults used.
For a relational storage, Warehouse Builder uses the star schema as the default implementation method.
When you choose multidimensional storage, Warehouse Builder stores the dimension in an analytic workspace that has the same name as the Oracle module in which the dimension is defined. If the analytic workspace does not exist, Warehouse Builder creates it. The analytic workspace is stored in the users tablespace of the schema that owns the Oracle module.
Warehouse Builder creates default dimension attributes with the properties specified in Table 13-1.
Table 13-1 Default Dimension Attributes
| Dimension Attribute Name | Identifier | Data Type |
|---|---|---|
|
ID |
Surrogate |
NUMBER |
|
Name |
Business |
VARCHAR2 |
|
Description |
VARCHAR2 |
You can add additional attributes. For your dimension to be valid, you must define the surrogate and business identifiers.
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.
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.
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, the attributes UPC, Package_type, and Package_size are versioned. Warehouse Builder creates two additional attributes called EXPIRATION_DATE and EFFECTIVE_DATE, of data type DATE, to store the effective date and expiration date of versioned records.
For a Type 3 SCD, all level attributes of the lowest level, except the surrogate identifier and the primary identifier, are versioned. Warehouse Builder creates additional attributes to store the previous value of each versioned attribute. In addition to this, and 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, Warehouse Builder creates additional attributes called PREV_DESCRIPTION, PREV_PACKAGE_TYPE, PREV_PACKAGE_SIZE, and PREV_UPC to store the previous values of the versioned attributes. These data type for these attributes are the same the ones used to store the current value of the attribute. Warehouse Builder also creates an attribute EFFECTIVE_TIME to store the effective time of versioned records. This attribute uses the DATE data type.
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 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 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.
For a multidimensional storage, if it does not already exist, Warehouse Builder creates an analytic workspace with the same name as the Oracle module that contains the dimension. For example, if you create a dimension called PRODUCTS in the SALES_WH module, the dimension is stored in an analytic workspace called SALES_WH. If an analytic workspace with this name does not already exist, Warehouse Builder first creates it and then stores the dimension in this analytic workspace.
For time dimensions, irrespective of the storage type, Warehouse Builder creates a map that loads the time dimension. The name of the map is the dimension name followed by '_MAP'. For example, the map that loads a time dimension called TIMES will be called TIMES_MAP.
The Data Object Editor enables advanced users to create dimensions according to their requirements. You can also edit a dimension using the Data Object Editor. Use the Data Object Editor to create a dimension if you want to perform one of the following:
Use the snowflake implementation methods.
Create value-based hierarchies.
Create dimension roles.
Skip levels in a hierarchy.
Use existing database tables or views to store the dimension data. This is referred to as manual binding.
To define a dimension using the Data Object Editor:
From the Warehouse Builder Project Explorer expand the Databases node and then the Oracle node.
Expand the target module where you want to create the dimension.
Right-click Dimensions, select New, then Using Editor.
Warehouse Builder displays the Add a New or Existing Dimension dialog.
Select the Create a New Dimension option. Enter the name of the dimension and select the module to which the dimension belongs.
Click OK.
Warehouse Builder displays the Data Object Editor. To define a dimension, provide information on the following tabs of the Dimension Details panel:
When you use the Data Object Editor to create a dimension that has a relational implementation, Warehouse Builder does not automatically create the physical structures that store the dimension data. You must create these structures.
For dimensions that have a relational implementation, bind the attributes in the dimension to the database columns that store their data, see "Binding Attributes".
You 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. You use the Description field to enter an optional description for the dimension.
Dimension Roles You use the Dimension Roles section to define dimension roles. For more information about dimension roles, see "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.
You 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.
Relational: Relational data structures Select the Relational option to store the dimension and its data in a relational form in the database. Use this option to create a dimension that uses a relational or ROLAP implementation.
For a relational storage, you can select one of the following methods to implement the dimension:
Star schema: Implements the dimension using a star schema. This means that the dimension data is stored in a single database table or view.
Snowflake schema: Implements the dimension using a snowflake schema. This dimension data is stored in more than one database table or view.
Manual: You must explicitly bind the attributes from the dimension to the database object that stores their data.
When you perform auto binding, Warehouse Builder uses these storage settings 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, Warehouse Builder creates a unique key that includes the business identifiers of all three levels. Creating a composite unique key enforces uniqueness of a dimension record across the dimension at the database level.
MOLAP: Multi dimensional data structures 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. Alternately, you can click the Ellipsis button to display a list of MOLAP objects in the current project. Warehouse Builder displays a node for each module in the project. Expand a module to view the list of dimensional objects in the module. Selecting an object from list stores the dimension in the same analytic workspace as the selected object.
AW Tablespace Name: Enter the name of the tablespace in which the analytic workspace is stored.
Dimensions with multiple hierarchies can sometimes use the same source column for aggregate levels (that is, any level above the base). In such cases, you select the Generate surrogate keys in AW option. During a load operation, Warehouse Builder adds the level name 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 there is no need to create new surrogate keys in the analytic workspace. The Use natural keys from data source option enables you to use the same natural keys from the source in the analytic workspace.
Note:
If you edit a dimension and change the Storage type from ROLAP to MOLAP, Warehouse Builder changes the data type of the surrogate identifier to VARCHAR2.Use the Attributes tab to define the dimension attributes. The Attributes tab contains two sections: Sequence and Dimension Attributes.
Sequence You need to select a sequence only for dimensions that have a relational implementation. 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. This dialog 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".
You use the Levels tab to define the level attributes for each level in the dimension. You also use this tab to create value-based hierarchies. Select the Value-based option to create a value-based hierarchy. A dimension with a value-based hierarchy can contain only one level.
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 need to 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. You 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, the level attribute will have the same name as the dimension attribute.
Description: Specify an optional description for 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.
You use the Hierarchies tab to create dimension hierarchies. The Hierarchies tab, shown in Figure 13-2, contains two sections: Hierarchies and Levels.
Figure 13-2 Hierarchies Tab of the Data Object Editor
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 choose MOLAP: Multidimensional data structures on the Storage tab. For more information on value-based hierarchies, see "Value-based Hierarchies".
Description: Enter an optional description for the hierarchy.
Default: Select the Default option if the hierarchy is the default hierarchy for the dimension. When a dimension has more than one hierarchy, query tools show the default hierarchy. It is recommended that you set the most commonly used hierarchy as the default hierarchy.
To delete a hierarchy, right-click the cell to the left of the Hierarchy field and select Delete. Alternately, 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 Data Object 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 drop-down 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 drop-down list.
Use the arrows to the left of the Levels section to change the order in which the levels appear in the section.
You 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 choose Relational: relational data structures on the Storage tab. The options that you can select for slowly changing policy are as follows:
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 a Type 2 SCD".
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 a Type 3 SCD".
Note:
You cannot create a Type 2 or Type 3 slowly changing dimension if you have specified the type of storage as MOLAP.When you create a Type 2 or Type 3 SCD using the Data Object Editor, you must create the dimension attributes that store the effective data and expiration date and apply them to the required levels.
You use the Data Viewer tab to view the dimension data. Click the Execute button to display the data viewer for the dimension. The data viewer displays the data stored in all the levels and hierarchies of the dimension. Before you attempt to view the data stored in the dimension, ensure that you have deployed the dimension and executed the map that loads the dimension.
After you define the dimension structure, you must specify the details of the database tables or views that store the dimension data. You can choose one of the following options for binding dimension attributes to the database columns that store their data:
Auto binding
Manual binding
Auto Binding When you perform auto binding, Warehouse Builder automatically maps the attributes in the dimension to the database columns that store their data.
To perform auto binding, in the Data Object Editor, select Object and then Bind. You can alternately right-click the dimension object in the Implementation tab of the graphical canvas and select Bind. For more information on the auto binding rules, see "Auto Binding".
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.
To perform manual binding:
In the Dimensional tab of the Data Object Editor, right-click the dimension and select Detail View.
Warehouse Builder displays the Implementation tab. This tab displays the dimension.
Drag and drop the operator that represents the database object that stores the dimension data.
For example, if the dimension data is stored in a table, drag and drop the Add New or Existing Table icon onto the Implementation canvas. Warehouse Builder displays the Add a new or existing Table dialog. To store the dimension data, you either select an existing table or create a new table.
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, perform Step 2 thrice.
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 on deploying dimensions, see "Deploying Dimensions".
You can create an SCD either using the Dimension wizard or the Data Object Editor. Creating SCD type 2 or 3 requires the Warehouse Builder Enterprise ETL Option.
To create an SCD using the Dimension Wizard, you use the Slowly Changing Dimension page of the Dimension Wizard. You need to only specify the type of SCD that you want to create on this page. Warehouse Builder assumes default values for all other required parameters. For more information on the Slowly Changing Dimension page, see "Slowly Changing Dimension Page".
To create SCDs using the Data Object Editor, see the following sections:
A Type 2 SCD stores the full history of values for each attribute and level relationship. You can create a Type 2 SCD only for dimensions that have a relational implementation.
To create a Type 2 SCD using the Data Object Editor, define the following:
The attributes that trigger history saving.
The attributes that store the effective date and the expiration date.
To create a Type 2 SCD using the Data Object Editor:
From the Warehouse Builder Project Explorer, expand the Databases node and then the Oracle node.
Expand the target module where you want to create the Type 2 SCD.
Right-click Dimensions, select New, then Using Editor
Provide information on the Name tab of the Data Object Editor as described in the "Name Tab".
On the Attributes tab, for each level, create two additional attributes to store the effective date and the expiration date. For more information on creating attributes, see "Attributes Tab".
Provide information on the following tabs of the Data Object Editor:
On the Slowly Changing tab, select the Type 2: Store the complete change history option.
Click Settings to the right of this option.
Warehouse Builder displays the Type 2 Slowly Changing Policy dialog. Specify the details of the Type 2 SCD as described in "Type 2 Slowly Changing Dimension Dialog".
Provide information on the Storage Tab of the Data Object Editor.
You use the Type 2 Slowly Changing Dimension dialog shown in Figure 13-3 to specify the effective date attribute, expiration date attribute, and the versioned attribute. This dialog displays a table that contains the following columns: Levels, Identifying Attribute, Data Type, and Record History.
Figure 13-3 Type 2 Slowly Changing Dialog
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 drop-down 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 Figure 13-3 the attributes that store the effective date and expiration date are EFFECTIVE_TIME and EXPIRATION_TIME respectively. Note that 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 the Record History option for the Trigger History property. When the value of the PACKAGE_TYPE attribute changes, the existing record is closed and a new record is created using the latest values.
A Type 3 SCD stores two versions of values for certain selected attributes. You can create a Type 3 SCD only for dimensions that have a relational implementation. Specify the following:
The attributes that should be versioned.
The attributes that will store the previous value of each versioned attribute.
For each versioned attribute, you must create an additional attribute to store the previous value of the attribute. For example, if you want to version the Population attribute, you create an additional attribute to store the previous value of population.
To create a Type 3 SCD using Warehouse Builder:
From the Warehouse Builder Project Explorer, expand the Database node and then the Oracle node.
Expand the target module where you want to create the Type 3 SCD.
Right-click Dimensions, select New, then using Using Editor.
Provide information on the Name tab of the Data Object Editor as described in "Name Tab".
On the Attributes tab, for each level, create an additional attribute to store the expiration date of the attributes in the level as described in "Attributes Tab".
Consider an example where you need to store previous values for the package_type and package_size attributes of the Products dimension. In this case, create two new attributes prev_package_type and prev_package_size to store the previous values of these attributes.
Provide information on the following tabs of the Data Object Editor:
On the Slowly Changing tab, select the Type 3: Store only the previous value option. Click Settings to the right of this option.
Warehouse Builder displays the Type 3 Slowly Changing Policy dialog. Specify the details of the Type 2 SCD using this dialog as described in "Type 3 Slowly Changing Dimension Dialog".
Provide information on the Storage Tab of the Data Object Editor.
Use the Type 3 Slowly Changing Dimension dialog shown in Figure 13-4 to specify the implementation details. You use this dialog 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.
Figure 13-4 Type 3 Slowly Changing Dialog
This dialog 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. You use the drop-down 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, select Effective date from the Effective drop-down list.
The surrogate ID of a level cannot be versioned.
Consider the Products Type 3 SCD whose slowly changing policy settings are shown in Figure 13-4. 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 property, is PREVIOUS_PACKAGE_TYPE. When the value of the PACKAGE_TYPE attribute changes, Warehouse Builder does the following:
Moves the existing value of the PACKAGE_TYPE attribute the PREVIOUS_PACKAGE_TYPE attribute.
Stores the new value of population in the PACKAGE_TYPE attribute.
You use the Data Object Editor to edit the definition of a dimension. When you edit a dimension definition, the changes are made only in the object metadata. To update the physical object definition, deploy the modified dimension using the control Center.
You cannot modify the type of SCD using the Data Object Editor once the SCD has been created.
To edit a dimension definition, open the Data Object Editor using one of the following methods:
Right-click the dimension in the Project Explorer and select Open Editor.
Double-click the dimension in the Project Explorer.
Modify the definition of the dimension using the tabs in the Data Object Editor. For more information on the tabs in the Data Object Editor, see "Using the Data Object Editor".
When you configure a dimension, you configure both the dimension and the underlying table.
To configure the physical properties for a dimension:
From the Project Explorer, right-click the dimension name and select Configure.
The Configuration Properties window is displayed.
Configure the following dimension properties:
Deployable: Select TRUE to indicate if you want to deploy this dimension. Warehouse Builder generates scripts only for table constraints marked deployable.
Deployment Options: Select one of the following options: Deploy All, Deploy Data Objects Only, Deploy to Catalog Only. For more information on deployment options, see "Deployment Options for Dimensions".
View Name: Specify the name of the view that is created to hide the control rows in the implementation table that stores the dimension data. This is applicable for relational or ROLAP dimensions that use a star schema. The default view name, if you do not explicitly specify one, is the dimension name suffixed with "_v".
For a dimension that uses a relational or ROLAP implementation, you can also configure the implementation tables. For more information, see "Configuring Tables".
Use the Deployment Option configuration property to specify how the dimension metadata is deployed. The options that you can select for a dimension are as follows:
Following are the options that you can set for this property.
Deploy All
Deploy Data Objects Only
Deploy to Catalog Only
For more information on these options, see "Deploying Dimensional Objects".
Depending on the type of implementation you require, set the following configuration options:
Relational dimension: Select Deploy Data Objects as the Deployment Option. This creates the dimension metadata in a relational schema in the database.
ROLAP and MOLAP dimensions: Select Deploy to Catalog Only or Deploy All as the Deployment Option.
Warehouse Builder provides the following two methods of creating a cube:
Use the Create Cube wizard to create a basic cube quickly. Warehouse Builder assumes default values for most of the parameters and creates the database structures that store the cube data.
Use the Data Object Editor to create a cube when you want to specify certain advanced options such as aggregation methods and solve dependency order. These options are not available when you use the Create Cube wizard.
Alternatively, you can use the Create Cube wizard to quickly create a basic cube object. Then use the Data Object Editor to specify the other options.
Use the following steps to create a cube using the wizard:
From the Warehouse Builder Project Explorer expand the Databases node and then the Oracle node.
Expand the target module where you want to create the cube.
Right-click Cubes, select New, then Using Wizard.
Warehouse Builder displays the Welcome page of the Cube wizard. Click Next to proceed. The wizard guides you through the following pages:
You use the Name and Description page to describe the cube. Enter the following details on this page:
Name: The name of the cube in Warehouse Builder. The cube name must be unique within the module.
Description: Specify an optional description for the cube.
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 as follows:
Relational storage (ROLAP)
Multidimensional storage (MOLAP)
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.
Relational storage (ROLAP)
Warehouse Builder stores the cube definition and its data in a relational form in the database. Use this option to create a cube that has a relational or ROLAP implementation.
Relational storage is preferable if you want to store detailed, high volume data or you have high refresh rates combined with high volumes of data. Use relational storage if you want to perform one of the following:
Store detailed information such as call detail records, point of sales (POS) records and other such transaction oriented data.
Refresh high volumes of data at short intervals.
Detailed reporting such as lists of order details.
Operational data stores and enterprise data warehouses are typically implemented using relational storage. You can then derive MOLAP implementations from this relational implementation to perform different types of analysis.
When you choose a relational implementation for a cube, Warehouse Builder automatically creates the implementation table used to store the cube data.
Multidimensional storage (MOLAP)
Warehouse Builder stores the cube definition and the cube data in an analytic workspace in the database. Use this option to create a cube that has a MOLAP implementation.
Multidimensional storage is preferable when you want to store aggregated data for analysis. The refresh intervals for a multidimensional storage are usually longer than relational storage as data needs to be pre-calculated and pre-aggregated. Also, the data volumes are typically smaller due to higher aggregation levels. Use multidimensional storage to perform the following:
Advanced analysis such as trend analysis, what-if analysis, or to forecast and allocate data
Drill and pivot data with instant results
When you choose a MOLAP implementation, Warehouse Builder generates the name used to store the cube in the analytic workspace. If no analytic workspace exists, Warehouse Builder creates one using the name you specify.
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. The Search For option enables you to search for a dimension using the dimension name. 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 Warehouse Builder repository. Each module in the project is represented by a separate node. Expand a module node to view all the dimensions in that module.
Warehouse Builder filters the dimensions displayed in the Available Dimensions section based on the implementation type chosen for the dimension. If you select ROLAP as the storage type, Warehouse Builder lists only dimensions that have a relational implementation. If you select MOLAP as the storage type, 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.
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 only for character data types.
Precision: Define the total number of digits allowed for the measure. Precision is defined only for numeric data types.
Scale: Define the total number of digits to the right of the decimal point. Scale is defined only for numeric data types.
Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP data types.
Expression: Use this option to define calculated measures. Click the Ellipsis button to display the Expression Builder. Define the expression used to calculate the measure.
You 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 Project Explorer.
Warehouse Builder creates the metadata for the following in the repository:
The cube object.
The definition of the table that stores the cube data.
For a relational or ROLAP implementation, Warehouse Builder creates the definition of the database table that stores the cube data. It also creates foreign keys 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, Warehouse Builder creates the analytic workspace that stores the cube data. Note that the wizard only creates the definitions for these objects in the repository. It does not create the objects in the target schema.
Deploying Cubes To create the cube and its associated objects in the target schema, you must deploy the cube. Before you deploy a ROLAP cube, ensure that you successfully deploy the database table that stores the cube data. Alternatively, you can deploy both the table and the cube together. For more information, see "MOLAP Implementation of Dimensional Objects".
Note:
When you delete a cube, the associated objects such as the database table or analytic workspace is not deleted. You must explicitly delete these objects.When you create a cube using the Create Cube wizard, Warehouse Builder uses the following defaults:
MOLAP Storage: The cube is stored in an analytic workspace that has the same name as the Oracle module in which the cube is created. The analytic workspace is stored in the users tablespace of the schema that owns the Oracle module.
Solve: By default, the cube is solved on demand.
Aggregation Function: The default aggregation function for all dimensions that the cube references is SUM.
The Data Object Editor enables advanced users to create cubes according to their requirements. You can also use the Data Object Editor to edit a cube.
Use the Data Object Editor to create a cube if you need to:
Specify the dimensions along which the cube is sparse.
Define aggregation methods for the cube measures.
Precompute aggregations for a level.
To create a cube using the Data Object Editor:
From the Warehouse Builder Project Explorer expand the Databases node and then the Oracle node.
Expand the target module where you want to create the cube.
Right-click Cubes, select New and then Using Editor.
Warehouse Builder displays the Add a New or Existing Cube dialog.
Select the Create a New Cube option. Enter the name of the cube and select the module to which the cube belongs.
Click OK.
Warehouse Builder displays the Data Object Editor. To define a cube, provide information on the following tabs of the Cube Details panel:
When you use the Data Object Editor to create a cube, Warehouse Builder does not automatically create the physical objects that stores the cube data. You will need to create these objects.
To bind the cube measures and the dimension references to the database columns that store their data, see "Binding Cube Attributes". You need to perform this step only for cubes that use a ROLAP implementation.
You 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.
The Storage tab specifies how the cube and its data should be stored. You can select either Relational or MOLAP as the storage type.
Relational Select the Relational: Relational data structures 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, refer to the Oracle Database Data Warehousing Guide 10g Release 2.
Select the Create composite unique key option to create a unique key on the dimension foreign key columns.
MOLAP Select the MOLAP: Multidimensional data structures option to store the cube data in an analytic workspace. Use this option to create a cube with a MOLAP implementation. You 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. This dialog 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, Warehouse Builder stores the analytic workspace in the default users tablespace of the owner of the Oracle module.
You 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.
Use the Advanced button to define the sparsity of the dimensions referenced by the cube. Clicking this button displays the Advanced dialog. Since you can define sparcity only for MOLAP cubes, the Advanced button is enabled only if the Storage type is MOLAP. For more information about the Sparcity dialog, see "Advanced Dialog".
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. This dialog displays the list of dimensions in the current project. Select a dimension from this list.
Warehouse Builder filters the dimensions displayed in this list based on the storage type specified for the cube. If you define a relational implementation for the cube, Warehouse Builder displays only those dimensions that use a relational implementation. If you define a MOLAP implementation for the cube, Warehouse Builder displays only the dimensions that use a MOLAP implementation.
Level: The Levels drop-down displays all the levels in the dimension selected in the Dimension field. Select the dimension level that the cube references.
Role: The Role drop-down 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.
Use the Advanced dialog to specify the sparsity of the dimensions that the cube references. Sparsity is applicable for MOLAP cubes only. For more information on sparsity, refer to Chapter 3 of the Oracle OLAP Application Developer's Guide 10g Release 2(10.2).
This dialog displays a table that contains two columns: Dimensions and Sparsity.
Dimensions: This column displays all the dimensions listed on the Dimension tab of the Data Object Editor. The dimensions are listed in the order in which they appear on the Dimensions tab. To change the order in which the dimensions appear on this dialog, you must change the order in which the dimensions are listed on the Dimensions Tab of the Data Object Editor.
Sparsity: Select Sparsity for a dimension reference if the cube data is space along that dimension. Sparsity specifies that the cube data is sparse along a particular dimension. For example, if the data in the SALES cube is sparse along the Promotions dimension, select Sparsity for the Promotions dimension.
All the sparse dimensions in a cube must be grouped together starting from the least sparse to the most sparse. For example, the SALES cube references the dimensions Times, Promotions, Products, and Channels. This is the order in which the dimensions are listed in the Advanced dialog. The cube data is sparse along the dimensions Promotions, Channels, and Times, with Promotions being the least sparse and Times being the most sparse. Then all these dimensions should appear as a group in the following order: Promotions, Channels, Times. You cannot have any other dimension listed in between these dimensions. Thus the order listed originally on the Advanced dialog is wrong because the Products dimension appears in between the dimensions Promotions and Channels. The following order of listing the dimensions are correct:
Promotions, Channels, Times, Products
Products, Promotions, Channels, Times
There is a correlation between the storage option used for the cube and the order in which you specify the dimensions on the Sparcity dialog. When the cube is not stored in compressed form (that is, when the Compress Cube option on the Advanced dialog is not selected), list the sparse dimensions in order from the one with the most members to the one with the least. When the cube is stored in compressed form, the order in which you list the dimensions is unimportant. This is because the OLAP engine determines the optimal order. When the cube uses a normal storage (not compressed), list the dimensions such that the least sparse dimension appears at the top of the list.
Defining sparsity for a cube provides the following benefits:
Improves data retrieval speed.
Reduces the storage space used by the cube.
Compress Cube Select this option to compress the cube data and then store it. Compressed storage uses less space and results in faster aggregation than a normal space storage. For more details on compressing cubes, refer to the Oracle OLAP Application Developer's Guide 10g Release 2(10.2)
Compressed storage is normally used for extremely sparse cubes. A cube is said to be extremely sparse if the dimension hierarchies contain levels with little change to the number of dimension members from one level to the next. Thus many parents have only one descendent for several contiguous levels. Since the aggregated data values do not change from one level to the next, the aggregate data can be stored once instead of repeatedly.
For compressed composites, you can only choose SUM and non-additive aggregation operators.
Partition Cube Select this option to partition the cube along one of its dimensions. Partitioning a cube improves the performance of large measures.
Use the table below the Partition Cube option to specify the dimension along which the cube is partitioned. The specified dimension must have at least one level-based hierarchy and its members must be distributed evenly, such that every parent at a particular level has roughly the same number of children. Use the Dimension column to select the dimension along which the cube is partitioned. Use the Hierarchy and Level columns to select the dimension hierarchy and level.
Time is usually the best choice to partition a cube because it meets the required criteria. In addition, data is loaded and rolled off by time period, so that new partitions can be created and old partitions dropped as part of the data refresh process.
Use a Global Index Select this option to create a global partitioned index.
You use the Measures tab to define the cube measures. Specify the following details for each measure:
Name: The name of the measure. The measure name must be unique within the cube.
Description: An optional description for the measure.
Data Type: The data type of the measure.
Length: The maximum number of bytes for the measure. Length is specified only for character data.
Precision: Define the total number of digits allowed for the column. Precision is defined only for numeric data types.
Scale: Define the total number of digits to the right of the decimal point. Scale is defined only for numeric data types.
Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP 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 on the Calculated Measure wizard, see "Calculated Measure Wizard".
You can use any other measures defined in the cube to create an expression for a measure. The expression defined can be validated only at deploy time.
Note:
You can create calculated measures for MOLAP dimensions only.Click the Generate Standard 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.
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.
Use this page to define the type of calculated measure. You can create the following types of calculated measures:
Standard Calculation Select the Standard Calculation option to define standard calculations based on the templates. Warehouse Builder enables you to define the following standard calculations:
Basic Arithmetic: This type enables you to perform basic arithmetic calculations such as the following:
Addition
Use this calculation to add either two measures or a measure and a number.
Subtraction
Use this calculation to subtract two measures or a measure and a number.
Multiplication
Use this calculation to multiply two measures or a measure and a number.
Division
Use this calculation to divide two measures or a measure and a number.
Ratio
Advanced Arithmetic: This type enables you to create the following advanced calculations:
Cumulative Total
Use this calculation to return the cumulative total of measure data over time periods within each level of a specified dimension.
For example, Cumulative Sales for 2001= Sales Q1 + Sales Q2 + Sales Q3 + Sales Q4
Index
Use this calculation to return the ratio of a measure's value as a percentage of a baseline value for the measure. The formula for the calculation is:
(Current member / Base Line member)
For example, Consumer Price Index (assuming baseline cost of goods is 1967) = (2001 Cost of Goods/1967 Cost of Goods)) * 100
Percent Markup
Use this calculation to return the percentage markup between two measures where the basis for the calculation is the older measure. the formula used for this calculation is: (y-x)/x.
For example, the new price is 110 and the old price is 100. The percentage markup is calculated as follows:
(110-100)/100 = +10%.
Percent Variance
Use this calculation to return the percent difference between a measure and a target for that measure. For example, the percentage variance between sales and quota is as follows:
(Sales-Quota)/Quota.
Rank
Use this calculation to return the numeric rank value of each dimension member based on the value of the specified measure. For example, the rank of TV sales where DVD is 150, TV is 100, and Radio is 50 would be 2.
Share
Use this calculation to return the ratio of a measure's value to the same measure value for another dimension member or level. The formula for this calculation is as follows:
(Current member / Specified member).
Variance
Use this calculation to calculate the variance between a base measure and a target for that measure. An example of variance is:
Sales Variance = Sales - Sales Forecast.
Prior/Future Comparison: Use this type to define prior and future value calculations such as the following:
Prior Value
Use this calculation to return the value of a measure from an earlier time period.
Difference from Prior Period
Use this calculation to return the difference between the current value of a measure and the value of that measure from a prior period. The formula for this calculation is as follows:
(Current Value - Previous Value)
Percent Difference from Prior Period
Use this calculation to return the percentage difference between the current value of a measure and the value of that measure from a prior period. The formula for this calculation is as follows:
((Current Value - Previous Value) / Previous Value)
Future Value
Use this calculation to return the value of an item for a future time period. For example, Sales a Year from Now = Sales from October 2006 if the current time is October 2005.
Time Frame: This type enables you to create the following time series calculations:
Moving Average
Use this calculation to return the average value for a measure over a rolling number of time periods. And example of this calculation is as follows:
Moving average sales for the last 3 months = (Jan Sales + Feb Sales + March Sales)/3
Moving Maximum
Use this calculation to return the maximum value for a measure over a rolling number of time periods. An example of this calculation is:
Moving maximum sales for the last 3 months = the largest Sales value for Jan, Feb, and March.
Moving Minimum
Use this calculation to return the minimum value for a measure over a rolling number of time periods. An example of this calculation is:
Moving minimum sales for the last 3 months = the smallest Sales value for Jan, Feb, and March.
Moving Total
Use this calculation to return the total value for a measure over a rolling number of time periods. An example of this calculation is:
Moving total sales for the last 3 months = (Jan Sales + Feb Sales + March Sales).
Year to Date
Use this calculation to sum measure data over time periods, to create cumulative measure data. An example of this calculation is:
Year-to-date sales to March = Jan Sales + Feb Sales + March Sales.
Custom Expression Select the Custom Expression option to specify an expression that is used to compute the calculated measure.
Use this page to define the details of the calculated measure. The contents of this page depend on the type of calculation you chose on the Select Calculated Measure Type page. For example, if you choose addition as the calculated measure type, this page displays the two drop-down lists that enable you to select the measure s that should be added.
If you chose Custom Expression on the Select Calculated Measure Type page, the Expression Builder interface is displayed. Use this interface to define a custom measure. For more information on the Expression Builder, see "The Expression Builder".
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, Warehouse Builder performs aggregation for every alternate level starting from the lowest level. The default aggregate function is SUM. For more details on the rules for summarizing data, refer to Chapter 3 of the Oracle OLAP Application Developer's Guide 10g Release 2(10.2).
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 is displayed. Use this dialog to select the levels in the dimension along which the measure data is precomputed. You can specify the levels to be precomputed for each dimension hierarchy. By default, alternate levels, starting from the lowest level, are precomputed.
Note:
You cannot define aggregations for pure relational cubes (cubes implemented in a relational schema in the database only and not in OLAP catalog).For ROLAP cubes, Warehouse Builder implements aggregation by creating materialized views that store aggregated data. These materialized views improve query performance. For MOLAP implementations, the aggregate data is generated and stored in the analytic workspace along with the base-level data. Some of the aggregate data is generated during deployment and the rest is aggregated on the fly in response to a query, following the rules defined in the Aggregation tab.
Note:
The materialized views that Warehouse Builder creates to implement ROLAP aggregation are not displayed under the Materialized Views node in the Project Explorer.You use the Data Viewer tab to view cube data. This tab contains the following three buttons: Execute, Query Builder, and Calculation Builder.
Execute Click the Execute button to display the data viewer for the cube. The data viewer displays the cube data, 100 rows at a time. To view the remaining rows click Next.
Query Builder Click the Query Builder button to launch the query builder. You use the query builder to customize the query that fetches data from the cube. For example, you can swap edges or select dimension members.
Calculation Builder The Calculation Builder is used to create a calculation that is based on the existing cube measures. Once you create a calculation, you can use it just like any other measure.
After you define the cube structure, you must specify the details of the database tables or views that store the cube data. You can 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, Warehouse Builder automatically maps the measures and dimension references of the cube to the database columns that store their data.
To perform auto binding:
In the Data Object Editor, select Object and then Auto Binding. You can alternately right-click the cube object in the Implementation tab of the graphical canvas and select Auto Binding.
Warehouse Builder displays the Auto binding dialog.
Select Yes to perform auto binding.
Warehouse Builder maps the measures and dimension references in the cube to the table that stores the cube data.
For more information on the auto binding rules, see "Auto Binding".
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:
In the Data Object Editor, right-click the cube object in the Dimensional tab of the Graphical canvas and select Detail View.
Warehouse Builder displays the Implementation tab. This tab contains the cube object.
Drag and drop the mapping operator that represents the database object that will store the cube data. For example, if the cube data will be stored in a table, drag and drop the Add New or Existing Table icon onto the Implementation canvas.
Warehouse Builder displays the Add a new or Existing table dialog. You either select an existing table or create a new table to store the cube data.
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 on deploying cubes, see "Deploying Cubes".
Cubes that use a MOLAP implementation are stored in analytic workspaces. The analytic workspace engine in Oracle Database 10g provides APIs called AWXML. These APIs enable both client-server usage (as in Analytic Workspace Manager) and batch-like usage with java stored procedures (like Oracle Warehouse Builder). This section describes implementation details for MOLAP cubes.
If you select Use natural keys from data source on the Storage tab of a dimension, Warehouse Builder generates mapping code (AWXML mapping code) that can handle ragged fact data for any cube that uses this dimension. The source column for the cube dimension level is actually mapped to every parent level also. This enables ragged fact data to be loaded.
If you select Generate surrogate keys in the analytic workspace on the Storage tab of a dimension, when you create a mapping that loads data at the level of this dimension, you will be loading cube dimension members for this level only.
Warehouse Builder enables you to reuse existing dimensions without the need of defining additional hierarchies. Aggregations are generated based on the cube dimension level references you define. Only hierarchies where the cube dimension level is a member will be included in the aggregation. If the cube dimension level referenced is a non-leaf level of the hierarchy, then levels lower in the hierarchy will be excluded when the cube or measures are solved. For example, if you have two cubes, BUDGET and SALES, they can share the same dimension definitions without additional dimension hierarchy definitions.
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 is selected.
A cube is auto-solved if any of the following conditions are satisfied:
The cube is compressed
The cube is not compressed, and the following additional conditions are true:
The solve property for all the measures is set to Yes.
The dimension levels that the cube references are at the leaf level of all hierarchies the level is a member of.
Mapping that contains the cube is executed
Incremental Aggregation of cube is dependent on auto-solve (load and aggregate in one operation). Incremental aggregation is a property of the cube operator in the mapping and applies only to auto-solved cubes.Warehouse Builder can generate cubes that are not auto-solved cubes if any of the following conditions are true:
The cube is solved by the mapping that loads the cube
Warehouse Builder transformations are used to solve the cube
The cube is non-compressed and any of the following conditions are true:
Some of the measures have the Solve property set to No.
The dimension levels that the cube references are non-leaf levels of a hierarchy the level is a member of.
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 is selected.
To solve only one measure in a compressed cube, use the following steps:
Open the Data Object Editor for the cube and navigate to the Aggregation tab.
You can open the Data Object Editor by double-clicking the cube name in the Project Explorer.
Select the measure that you want to solve on the Measures section of the Aggregation tab.
The Aggregation for measure section displays a row for each dimension that the cube references. In the row that represents the dimension along which you want to solve the cube, select NOAGG in the Aggregation Function column.
To solve only one measure in a non-compressed cube, you will need the latest database patch 10.2.0.2. If you have Oracle Database 10g Release 1 (10.1), refer to bug 4550247 for details about a patch. The options defined on cube measures for solve indicate which measures will be included in the primary solve. The solve indicator on the cube operator in the map however indicates whether this solve will be executed or not. So the map can just load data or load and solve the data.
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 Global Explorer 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, an asynchronous solve job is launched and the master job ID is returned through the return value of the function.
Calculation Plans Generated by Warehouse Builder The following calculation plans are generated by Warehouse Builder:
Calculation plan for the cube
Calculation plan for each stored measure
This allows 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 Warehouse Builder metadata.
You can enable parallel solving of cubes using 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 configuration 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.
When you execute a mapping that loads a cube, one of the output parameters is AW_EXECUTE_RESULT. When the map is executed using parallel solve, this output parameter will contain the job ID. You can then use the following data dictionary views to determine when the job is complete and what to do next:
ALL_SCHEDULER_JOBS
ALL_SCHEDULER_JOB_RUN_DETAILS
ALL_SCHEDULER_RUNNING_JOBS
If the mapping is not executed using parallel solve, the AW_EXECUTE_RESULT output parameter will return the 'Successful' tag or an error. For more details about the error, refer to the OLAPSYS.XML_LOAD_LOG table.
You can edit a cube and alter its definition using the Data Object Editor. When you edit a dimension definition, the changes are made only in the object metadata. To update the physical object definition, deploy the modified dimension using the Control Center.
To edit a cube definition, open the Data Object Editor using one of the following methods:
Right-click the cube in the Project Explorer and select Open Editor.
Double-click the cube in the Project Explorer.
You use the tabs of the Data Object Editor to edit the cube definition. For more information on the tabs in the Data Object Editor, see Using the Data Object Editor.
When you configure a cube, you configure both the cube and the underlying table.
To configure the physical properties for a cube:
From the Project Explorer, right-click the cube name and select Configure from the pop-up menu.
The Configuration Properties window is displayed.
Configure the following cube parameters:
Deployable: Select TRUE to indicate if you want to deploy this cube. Warehouse Builder generates scripts only for table constraints marked deployable.
Deployment Options: Use this property to specify where the dimension is deployed. The options are:
Deploy Aggregations: Deploys the aggregations defined on the cube measures.
Deploy All: For a relational implementation, the cube is deployed to the database and a CWM definition to the OLAP catalog. For a MOLAP implementation, the cube is deployed to the analytic workspace.
Deploy Data Objects only: Deploys the cube only to the database. You can select this option only for cubes that have a relational implementation.
Deploy to Catalog only: Deploys the CWM definition to the OLAP catalog only. Use this option if you want applications such as BI Beans or Discoverer for OLAP to access the dimension data after you deploy data only.
Materialized View Index Tablespace: The name of the tablespace that stores the materialized view indexes.
Materialized View Tablespace: The name of the tablespace that stores the materialized view created for the cube.
Although there are no additional configuration parameters for cubes, the following are some guidelines for configuring a cube.
Foreign Key constraints exist for every dimension.
Bitmap indexes have been generated for every foreign key column to its referenced dimension.
Warehouse Builder provides the Create Time Dimension wizard that enables you to create a fully functional time dimension quickly. The mapping that populates the time dimension is also created automatically. When you choose a relational implementation for a time dimension, Warehouse Builder also creates the implementation objects that store the time dimension data.
You can also use the Data Object Editor to define a time dimension with your own specifications. In this case, you need to create the implementation objects and the map that loads the time dimension.
Use the following steps to create a time dimension using the Create Time Dimension wizard:
From the Warehouse Builder Project Explorer expand the Databases node and then the Oracle node.
Expand the target module where you want to create a time dimension.
Right-click Dimensions, select New, then Using Time Wizard.
Warehouse Builder displays the Welcome page of the Create Time Dimension wizard. Click Next to proceed. The wizard guides you through the following pages:
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.
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:
Relational storage (ROLAP): Stores the time dimension definition in a relational form in the database. Select this option to create a time dimension that uses a relational or ROLAP implementation.
Warehouse Builder automatically creates the underlying tables required to implement this time dimension. A star schema is used to implement the time dimension.
Multidimensional storage (MOLAP): Stores the time dimension definition and data in an analytic workspace. Select this option to create a time dimension that uses a MOLAP implementation.
Warehouse Builder stores the time dimension in an analytic workspace with same name as the module. The tablespace that is used to store the analytic workspace is the tablespace that is defined as the users tablespace for the schema that contains the dimension metadata.
For more information on these options, see "Storage Type 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 drop-down 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 drop-down list.
Type of Time Dimension Use the Type of Time Dimension section to specify the type of time dimension to create. Select one of the following options for type of time dimension:
Calendar: Creates a calendar time dimension.
Fiscal: Creates a fiscal time dimension. Enter the following additional details to create a fiscal time dimension:
Fiscal Convention: Select the convention that you want to use to represent the fiscal months. The options available are 544 and 445.
Fiscal Year Starting: Select the date and month from which the fiscal year starts.
Fiscal Week Starting: Select the day from which the fiscal week starts.
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, Warehouse Builder provides the following two options 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.
Use the Levels page to select the levels that should be included in the fiscal hierarchy. The levels you can select are:
Fiscal year
Fiscal quarter
Fiscal month
Fiscal week
Day
You must select a minimum of two levels. Warehouse Builder creates the fiscal hierarchy that contains the selected levels. To create additional hierarchies, use the Data Object Editor. For more information on using the Data Object Editor, see "Editing Time Dimension Definitions".
The Pre Create Settings page displays a summary of the options you selected on the previous pages of the Create Time Dimension wizard. This includes the attributes, levels, hierarchies, and the name of the map that is used to populate the time dimension. Warehouse Builder uses these settings to create the objects that implement the time dimension. Click Next to proceed with the implementation of the wizard. Click Back to change any options that you selected on the previous wizard pages.
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.
The Summary page summarizes the options selected in the wizard pages. You 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 Project Explorer. The mapping that loads this time dimension is displayed under the Mappings node in the Project Explorer.
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 repository. Warehouse Builder also binds the time dimension attributes to the database columns that store their values. For a MOLAP implementation, the analytic workspace that stores the time dimension and its data is created.
A mapping that populates the time dimension.
Note:
When you delete a time dimension, Warehouse Builder does not delete the table, sequence, and the mapping associated with the time dimension. You need to explicitly delete these objects.When you create a time dimension using the Time Dimension wizard, Warehouse Builder uses the following defaults:
Storage: The default implementation for the relational storage is the star schema. For a MOLAP implementation, the dimension is stored in an analytic workspace that has the same name as the Oracle module in which the time dimension is created. The analytic workspace is stored in the tablespace that is assigned as the users tablespace for the schema that owns the Oracle module containing the dimension.
Hierarchy: Warehouse Builder creates a standard hierarchy that contains all the levels listed on the Levels page of the Create Dimension wizard. The hierarchy contains the levels in the same order that they are listed on the Levels page.
To edit a time dimension:
From the Warehouse Builder Project Explorer expand the Databases node then the Oracle node.
Expand the target module that contains the time dimension to be edited.
Right-click the time dimension that you want to edit and select Open Editor. You can also double-click the time dimension. Warehouse Builder displays the Data Object Editor for the time dimension.
Edit the information on the following tabs:
When you modify a time dimension, a new population map and new implementation tables are created. You can choose to either delete the existing population map and implementation tables or to retain them.
You use the mapping editor to modify the time dimension population map. You must deploy the mapping that populates the time dimension.
If you delete the population map before deploying the map, you cannot populate data into the time dimension. The work around is to run the time dimension wizard again and create another dimension population map.
You 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 drop-down 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 drop-down list.
You use the Storage tab to specify the type of storage for the time dimension. The storage options you can use are Relational or MOLAP.
Relational Selecting the Relational option stores the time dimension definition in a relational form in the database. Select one of the following options for the relational implementation of the time dimension:
Star schema: The time dimension is implemented using a star schema. This means that the time dimension data is stored in a single database table or view.
Snowflake schema: The time dimension is implemented using a snowflake schema. This means that the time dimension data is stored in multiple tables or views.
MOLAP Select MOLAP to store the time dimension definition and data in an analytic workspace in the database. This method uses an analytic workspace to store the time dimension data. Provide the following details for a MOLAP implementation:
AW Name: Enter the name of the analytic workspace that stores the time dimension. Click the Ellipsis button to display a list of available AWs. Warehouse Builder displays a node for each module in the current project. Expand a module to view the list of dimensional objects in the module. Selecting an object from list stores the time dimension in the same analytic workspace as the selected object.
Tablespace Name: Enter the name of the tablespace that stores the analytic workspace. If you do not enter a value, Warehouse Builder stores the analytic workspace in the tablespace that is defined as the users tablespace for the schema containing the time dimension metadata.
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. You use the Select to the right of this field to select a sequence from the Available Sequences dialog. This dialog 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 drop-down lists displays two options: Surrogate and Business. Select the type of identifier.
Data Type: Select the data type of the attribute.
Length: Specify length only for character data types.
Precision: Define the total number of digits allowed for the column. Precision is defined only for numeric data types.
Scale: Define the total number of digits to the right of the decimal point. Scale is defined only for numeric data types.
Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP data types.
Descriptor: Select the type of descriptor. The options are: Short Description, Long Description, Start date, End date, Time span, and Prior period.
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 on the contents of the Levels tab, see "Level Attributes Page".
You 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 is displayed. Use this dialog to modify the fiscal convention, fiscal year start, and fiscal week start.
The Hierarchies tab contains two sections: Hierarchies and Levels.
Hierarchies: Use this section to create hierarchies. Warehouse Builder displays any existing hierarchies in the time dimension. You create additional hierarchies by specifying the name of the hierarchy and type of hierarchy. The options for type of hierarchy are None, Fiscal, Calendar Week, and Calendar Year. Use the Default property to indicate which of the hierarchies is the default hierarchy.
Levels: The Levels section lists the levels in the time dimension. When you create a new hierarchy, choose the levels that you want to include in your hierarchy by selecting the Applicable option.
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|