14 Defining Dimensional Objects

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

This chapter includes:

About Dimensional Objects

This section describes the basic concepts related to dimensional objects. If you are familiar with dimensional objects concepts and the types of implementations for dimensional objects in Warehouse Builder, skip the next few sections and continue with "Creating Dimensions".

Objects that contain additional metadata to identify and categorize data are called dimensional objects. Warehouse Builder enables you to design, deploy, and load two types of dimensional objects: dimensions and cubes. In this chapter, the word dimensional object refers to both dimensions and cubes.

Most analytic queries require the use of a time dimension. Warehouse Builder provides tools that enable you to easily create and populate time dimensions by answering simple questions.

Design versus Implementation

Warehouse Builder separates the logical design of dimensional objects from their storage. The logical design (business rules) allow you to focus on the structure and the content of the dimensional object first. You can then choose a relational, ROLAP, or MOLAP implementation for the dimensional object.

ROLAP and relational implementations store the dimensional object in a relational schema in the database.

A MOLAP implementation stores the dimensional object in analytic workspaces in the database.

Warehouse Builder enables you to use the same metadata to create and manage both your relational and multidimensional data stores. Separating the design from the implementation has the following advantages:

  • Implementation is easier, because you first design and then implement.

  • ETL is transparent as it is always the same for any type of implementation.

Uses of OLAP

Business organizations typically have complex analytic, forecast, and planning requirements. Analytic Business Intelligence (BI) applications provide solutions by answering critical business questions using the data available in your database.

Dimensional objects provide complex analytic power to your data warehouse. After you load data into dimensional objects, you can use tools and applications to run complex analytical queries that answer your business questions. These analytic queries include time-series analysis, inter-row calculations, access to aggregated historical and current data, and forecasts. Multidimensional objects are more effective in answering these types of queries quickly.

About Creating Dimensional Objects

Creating dimensional objects consists of four high-level tasks:

  1. Defining Dimensional Objects

  2. Implementing Dimensional Objects

  3. Deploying Dimensional Objects

  4. Loading Dimensional Objects

Defining Dimensional Objects

When you define dimensional objects, you describe the logical relationships that help store data in a more structured format. For example, to define a dimension, you describe its attributes, levels, and hierarchies.

Warehouse Builder provides the following two methods to define dimensional objects:

  • Wizards: Use wizards to create dimensional objects easily. The wizard creates a fully functional dimensional object along with the implementation objects that store the dimensional object data. Many options are defaulted to the most common settings. You can change these settings later using the editors.

    You use the Create Dimension Wizard to create dimensions, the Create Time Dimension Wizard to create time dimensions, and the Create Cube Wizard to create cubes.

  • Editors: Use editors to create or edit dimensional objects. Use editors to create a dimensional object when you want to specify settings that are different from the default settings used by the wizards. Also use editors to create dimensional objects that use certain advanced options that are not available when you use wizards. For example, to create a relational dimension that uses a snowflake schema implementation, you must use the editor. When you use the wizard, the default implementation method used is the star schema. However, you can edit a dimension that you created using the Create Dimension Wizard and modify it to use a snowflake schema implementation.

Implementing Dimensional Objects

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

Note:

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

  • OLAP 10.1.0.4 or higher

You set the Deployment Option configuration property to specify the type of implementation for a dimensional object. For more information on setting this property, see "Configuring Dimensions" and "Configuring Cubes".

Relational Implementation of Dimensional Objects

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

When you use the wizard to define dimensional objects, Warehouse Builder creates the database tables that store the dimensional object data. When you define a dimensional object using the Data Object Editor, you can decide whether you want Warehouse Builder to create the implementation tables or you want to store the dimensional object data in your own tables and views. The following section on binding describes how you specify the relationship between the dimensional object and its implementation objects.

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

Binding

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

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

Warehouse Builder provides two methods of binding:

  • Auto binding

  • Manual binding

Auto Binding In auto binding, Warehouse Builder creates the implementation tables, if they do not already exist. The attributes and relationships of the dimensional object are then bound to the columns that store their data. You can perform auto binding using both the wizards and the editors.

In the case of a dimension, the number of tables used to store the dimension data depends on the options you select for the storage. For more information on these options, see "Relational and ROLAP Implementation of a Dimension".

When you use the editors to create dimensional objects, you can perform both auto binding and manual binding.

To perform auto binding:

  1. In the Project Explorer, right-click the dimensional object and select Open Editor.

    The Data Object Editor for this dimensional object is displayed.

  2. On the Dimensional tab, right-click the dimensional object node and select Bind.

    Alternatively, select the dimensional object node on the canvas and from the Object menu select Bind.

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

Manual Binding In manual binding, you must explicitly bind the attributes of the dimensional objects to the database columns that store their data. You use manual binding when you want to bind a dimensional object to existing tables or views.

To perform manual binding for a dimensional object:

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

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

  2. In the Project Explorer, right-click the dimensional and select Open Editor.

    The Data Object Editor for the dimensional object is displayed. On the canvas, the Dimensional tab is active.

  3. Right-click the dimensional object and select Detail View.

    Warehouse Builder opens a new tab that has the same name as the dimensional object.

  4. From the palette, drag and drop the operator that represents the implementation object onto the canvas.

    Warehouse Builder displays the Add a New or Existing <Object> dialog box. For example, if the dimension data is stored in a table, drag a Table operator from the Palette and drop it onto the canvas. The Add a New or Existing Table dialog box is displayed.

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

  6. Click OK.

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

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

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

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

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

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

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

To unbind a dimensional object from its current implementation, right-click the dimensional object on the Relational tab of the Canvas and select Unbind. Unbinding removes the bindings between the dimensional object and its implementation objects. However, it does not modify the implementation objects.

ROLAP Implementation of Dimensional Objects

A ROLAP implementation, like a relational implementation, stores the dimensional object and its data in a relational form in the database. In addition to creating DDL scripts that can be deployed to a database, a ROLAP implementation enables you to create CWM2 metadata for the dimensional object in the OLAP catalog.

MOLAP Implementation of Dimensional Objects

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

Analytic Workspace

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

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

OLAP Catalog

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

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

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

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

Figure 14-1 describes how the OLAP catalog enables applications to access data stored in relational tables and analytic workspaces.

Figure 14-1 Using the OLAP Catalog to Access Dimensional Objects

Description of Figure 14-1 follows
Description of "Figure 14-1 Using the OLAP Catalog to Access Dimensional Objects"

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

Deploying Dimensional Objects

To instantiate the dimensional objects in the database, you must deploy them. To specify the type of implementation for dimensional objects, you set the deployment option. The configuration parameter Deployment Options enables you to set the deployment option.

Warehouse Builder provides the following deployment options for dimensional objects.

  • Deploy All: For a relational or ROLAP implementation, the dimensional object is deployed to the database and a CWM definition to the OLAP catalog. For a MOLAP implementation, the dimensional object is deployed to the analytic workspace.

  • Deploy Data Objects Only: Deploys the dimensional object only to the database. You can select this option only for dimensional objects that use 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 Discoverer for OLAP to access the dimensional object data after you deploy data only. Use this option if you previously deployed with "Data Objects Only" and now want to deploy the CWM Catalog definitions without re-deploying the data objects again.

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

Deploying Dimensional Objects that Use a MOLAP Implementation

Dimensional objects that use a MOLAP implementation can be deployed just after you define them. You can use the Design Center or the Control Center Manager to deploy a dimensional object.

Deploying Dimensional Objects that Use a Relational or ROLAP Implementation

Before you deploy a relational or ROLAP dimensional object, ensure that the implementation details are specified. This means that the dimensional object should be bound to its implementation objects. Also ensure that the dimensional object is valid. For more information on implementing dimensional objects, see "Relational Implementation of Dimensional Objects". For more information on performing binding, see "Binding".

After you perform binding, deploy the dimensional object. Before you deploy a dimensional object, ensure that all its implementation objects are deployed. For a dimension, this includes the sequence that is used to generate the surrogate identifier of the dimension levels. Alternatively, you can deploy the implementation objects together with the dimensional object.

Loading Dimensional Objects

After you deploy a dimensional object, you load data into it by creating a mapping. Use the Mapping Editor to create the mapping that loads data from the source objects into the dimensional object. You then deploy and execute this mapping.

For more information on loading dimensions, see "Dimension Operator as a Target". For information on loading cubes, see "Cube Operator".

About Dimensions

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

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

Typical relational dimension tables have the following characteristics:

  • A single column primary key populated with values called warehouse keys.

    Warehouse keys that provide administrative control over the dimension, support techniques that preserve dimension history, and reduce the size of cubes.

  • One or more hierarchies that are explicitly defined as dimension objects. Hierarchies maximize the number of query rewrites by the Oracle server.

Rules for Dimension Objects

When you create a dimension object using Warehouse Builder, the dimension must conform to the following rules:

  • A dimension must have a surrogate identifier and a business identifier.

  • The surrogate identifier can consist of only one attribute. However, the business identifier can consist of more than one attribute.

  • Every dimension level must have at least one attribute.

  • A dimension attribute can be either a surrogate identifier, a business identifier, a parent identifier, or a regular attribute.

  • A regular attribute can also play only one of the following roles at a time: effective date, expiration date, or triggering attribute.

  • A dimension that uses a relational or ROLAP implementation must have at least one level.

  • Any database table or view that implements a dimension that uses a relational or ROLAP implementation must have only one LONG, LONG RAW, or NCLOB column.

  • For a dimension that uses a relational or ROLAP implementation, all level attributes must bind to database tables or views only.

  • A dimension that uses a relational or ROLAP implementation must be associated with a sequence that is used to load the dimension key attribute.

  • The dimension key attribute of a dimension that uses a relational or ROLAP implementation must bind to the primary key of a table.

  • A Type 2 Slowing Changing Dimension (SCD) must have the effective date, expiration date, and at least one triggering attribute.

  • A Type 3 Slowing Changing Dimension (SCD) must have the effective date and at least one triggering attribute.

Limitations of Deploying Dimensions to the OLAP Catalog

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

The following are items that are affected by this limitation:

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

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

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

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

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

Defining a Dimension

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

  • Dimension Attributes

  • Levels

  • Level attributes

  • Hierarchies

Defining Dimension Attributes

A dimension attribute is a descriptive characteristic of a dimension member. It has a name and a data type. A dimension attribute is applicable to one or more levels in the dimension. They are implemented as level attributes to store data.

In Warehouse Builder, you define dimension attributes when you define a dimension. The list of dimension attributes must include all the attributes that you may need for any of the levels in the dimension. Dimension attributes are the only attributes that are visible in Discoverer and other OLAP tools.

For example, the Products dimension has a dimension attribute called Description. This attribute is applicable to all the levels Total, Groups, and Products and stores the description for each of the members of these levels.

Defining Levels

The levels in a dimension represent the level of aggregation of data. A dimension must contain at least one level, except in the case of a dimension that contains a value-based hierarchy. Every level must have level attributes and a level identifier.

For example, the dimension Products can have the following levels: Total, Groups, and Product.

Surrogate, Business, and Parent Identifiers

Every level must have two identifiers: a surrogate identifier and a business identifier. When you create a dimension, each level must implement the dimension attributes marked as the surrogate identifier and business identifier (attributes, in the case of a composite business identifier) of the dimension.

Surrogate Identifiers

A surrogate identifier uniquely identifies each level record across all the levels of the dimension. It must be composed of a single attribute. Surrogate identifiers enable you to hook facts to any dimension level as opposed to the lowest dimension level only.

For a dimension that has a relational or ROLAP implementation, the surrogate identifier should be of the data type NUMBER. Because the value of the surrogate identifier must be unique across all dimension levels, you use the same sequence to generate the surrogate identifier of all the dimension levels.

For a relational implementation, the surrogate identifier serves the following purposes:

  • If a child level is stored in a different table from the parent level, each child level record stores the surrogate identifier of the parent record.

  • In a fact table, each cube record stores only the surrogate identifier of the dimension record to which it refers. By storing the surrogate identifier, the size of the fact table that implements the cube is reduced.

Business Identifiers

A business identifier consists of a user-selected list of attributes. The business identifier must be unique across the level and is always derived from the natural key of the data source. The business identifier uniquely identifies the member. For example, the business identifier of a Product level can be its Universal Product Code (UPC), which is a unique code for each product.

Note:

For a dimension that has a MOLAP implementation, the business identifier can consist of only one attribute.

The business identifier does the following:

  • Identifies a record in business terms

  • Provides a logical link between the fact and the dimension or between two levels

  • Enables the lookup of a surrogate key

When you populate a child level in a dimension, you must specify the business identifier of its parent level. When you populate a cube, you must specify the business identifier of the dimension level to which the cube refers.

Parent Identifier

A parent identifier is used to annotate the parent reference in a value-based hierarchy. For more information on value-based hierarchies, see "Value-based Hierarchies".

For example, an EMPLOYEE dimension with a value-based hierarchy, has the following dimension attributes: ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, JOB_ID, HIRE_DATE, and MANAGER_ID. In this dimension, ID is the surrogate identifier and MANAGER_ID is the parent identifier.

Defining Level Attributes

A level attribute is a descriptive characteristic of a level member. Each level in the dimension has a set of level attributes. To define level attributes, you select the dimension attributes that the level will implement. A level attribute has a distinct name and a data type. The data type is inherited from the dimension attribute that the level attribute implements. The name of the level attribute can be modified to be different from that of the dimension attribute that it implements.

Every level must implement the attribute marked as the surrogate identifier and the business identifier in the set of the dimension attributes.

Defining Hierarchies

A dimension hierarchy is a logical structure that uses ordered levels or a set of data values (for a value-based hierarchy) as a means of organizing data. A hierarchy describes parent-child relationships among a set of levels. A level-based hierarchy must have at least one level. A level can be part of more than one hierarchy.

For example, the Time dimension can have the following two hierarchies:

Fiscal Hierarchy: Fiscal Year > Fiscal Quarter > Fiscal Month > Fiscal Week > Day

Calendar Hierarchy: Calendar Year > Calendar Quarter > Calendar Month > Day

All hierarchies must be strict 1:n relationships. One record in a parent level corresponds to multiple records in a child level. But one record in a child level corresponds to only one parent record within a hierarchy.

Dimension Roles

A dimension role is an alias for a dimension. In a data warehouse, a cube can refer to the same dimension multiple times, without requiring the dimension to be stored multiple times. Multiple references to the same dimension may cause confusion. So you create an alias for each reference to the dimension, thus allowing the joins to be instantly understandable. In such cases, the same dimension performs different dimension roles in the cube.

For example, a sales record can have the following three time values:

  • Time the order is booked

  • Time the order is shipped

  • Time the order is fulfilled

Instead of creating three time dimensions and populating them with data, you can use dimension roles. Model one time dimension and create the following three roles for the time dimension: order booked time, order shipped time, and order fulfillment time. The sales cube can refer to the order time, ship time, and fulfillment time dimensions.

When the dimension is stored in the database, only one dimension is created and each dimension role references this dimension. But when the dimension is stored in the OLAP catalog, Warehouse Builder creates a dimension for each dimension role. Thus, if a time dimension has three roles, three dimensions are created in the OLAP catalog. However, all three dimensions are mapped to the same underlying table. This is a workaround because the OLAP catalog does not support dimension roles.

Note:

Dimension roles can be created for dimensions that have a relational implementation only.

Level Relationships

A level relationship is an association between levels in a dimension hierarchy. Level relationships are implemented using level attributes that store the reference to the parent level in the hierarchy.

For example, the Products dimension has the following hierarchy: Total > Groups > Product. Warehouse Builder creates two level relationships: Product to Groups and Groups to Total. Two new attributes implement this level relationship: one in the Product level and one in the Groups level. These attributes store the surrogate ID of the parent level.

Dimension Example

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

Table 14-1 Products Dimension Level Details

Level Attribute Name Identifier

Total

ID

Surrogate

 

Name

Business

 

Description

 

Groups

ID

Surrogate

 

Name

Business

 

Description

 

Product

ID

Surrogate

 

UPC

Business

 

Name

 
 

Description

 
 

Package Type

 
 

Package Size

 

The PRODUCTS dimension contains the following hierarchy:

Hierarchy 1: Total > Groups > Product

Control Rows

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

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

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

Table 14-2 Control Rows Created for the Products Dimension

Dimension Key Total Name Categories Name Product Name

-3

TOTAL

   

-9

TOTAL

Hardware

 

-10

TOTAL

Software

 

-11

TOTAL

Electronics

 

-12

TOTAL

Peripherals

 

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

Value-based Hierarchies

A value-based hierarchy is a dimension in which hierarchical relationships are defined by a parent dimension attribute and a child dimension attribute. This is different from a level-based hierarchy, referred to as a hierarchy in this chapter, in which the hierarchical relationships are defined between levels.

You create a value-based hierarchy when the parent-child relationships cannot be grouped into meaningful levels. A value-based hierarchy has no levels. When you create the dimension attributes, you must specify which dimension attribute is the parent attribute.

For example, consider an EMPLOYEE dimension that has the following dimension attributes: ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, JOB_ID, HIRE_DATE, DESCRIPTION, and MANAGER_ID. This dimension contains a parent-child relationship in which the MANAGER_ID attribute identifies the manager of each employee. But these relationships may not form meaningful levels across the organization. This is because the number of levels between an employee and the CEO is not the same for all employees. There may be four levels between employee A and the CEO, whereas, there may be six levels between employee B and the CEO. In such cases, you create a value-based hierarchy with MANAGER_ID as the parent identifier.

You can create value-based hierarchies using the Data Object Editor only. For more information about specifying a parent attribute, see "Attributes Tab".

Note:

Value-based hierarchies can be created only in dimensions that use a MOLAP implementation.

Implementing a Dimension

Implementing a dimension consists of specifying how the dimension and its data are physically stored. You can choose either a relational implementation, ROLAP implementation, or MOLAP implementation for a dimension. For more information about setting the implementation method, see "Implementing Dimensional Objects".

Relational and ROLAP Implementation of a Dimension

When you store dimension data in a relational form, you can implement the dimension using one of the following methods:

Star Schema

In a star schema implementation, Warehouse Builder stores the dimension data in a single table. Because the same table or view stores data for more than one dimension level, you must specify a dimension key column in the table. The dimension key column is the primary key for the dimension. This column also forms the foreign key reference to the cube.

Each level implements a subset of dimension attributes. By default, the level attribute name is the same as the dimension attribute name. To avoid name conflicts caused by all level data being stored in the same table, Warehouse Builder uses the following guidelines for naming in a star table:

  • If the level attribute name is not unique, Warehouse Builder prefixes it with the name of the level.

  • If the level attribute name is unique, Warehouse Builder does not use any prefix.

Note:

To ensure that no prefixes are used, you must explicitly change the level attribute name in the Create Dimension wizard or the Data Object Editor.

For example, if you implement the Products dimension using a star schema, Warehouse Builder uses a single table to implement all the levels in the dimension.

Figure 14-2 displays the star schema implementation of the Products dimension. The attributes in all the levels are mapped to different columns in a single table called PRODUCTS. The column called DIMENSION_KEY stores the surrogate ID for the dimension and is the primary key of the table.

Figure 14-2 Star Schema Implementation of Products Dimension

Description of Figure 14-2 follows
Description of "Figure 14-2 Star Schema Implementation of Products Dimension"

For relational or ROLAP dimensions that use a star implementation, you can bind attributes from more than one levels to the same database column. A database column that is bound to attributes from more than one dimension levels is referred to as a shared column. For a Type 2 SCD, you cannot set the level attributes that are bound to a shared column as triggering attributes.

Snowflake Schema

In a snowflake schema implementation, Warehouse Builder uses more than one table to store the dimension data. Separate database tables or views store the data pertaining to each level in the dimension.

Figure 14-3 displays the snowflake implementation of the PRODUCTS dimension. Each level in the dimension is mapped to a different table.

Figure 14-3 Snowflake Schema Implementation of the Products Dimension

Description of Figure 14-3 follows
Description of "Figure 14-3 Snowflake Schema Implementation of the Products Dimension"

Binding

When you perform binding, you specify the database columns that will store the data of each attribute and level relationship in the dimension. You can perform either auto binding or manual binding for a dimension. For more information about binding, see "Binding".

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

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

  • If the implementation method of the dimension remains the same, Warehouse Builder rebinds the dimensional object to the existing implementation objects. The implementation method can be either Star or Snowflake. For more information on implementation methods, see "Relational and ROLAP Implementation of a Dimension".

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

  • If the implementation method of a dimension is changed, Warehouse Builder deletes the old implementation objects and creates a new set of implementation tables. If you want to retain the old implementation objects, you must first unbind the dimensional object and then perform auto binding. For more information on implementation methods, see "Relational and ROLAP Implementation of a Dimension".

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

For information about how to perform auto binding, see "Auto Binding". Auto binding uses the implementation settings described in "Relational and ROLAP Implementation of a Dimension".

Manual Binding You would typically use manual binding to bind existing tables to a dimension. Use manual binding if no auto binding or rebinding is required.

For information about how to perform manual binding, see "Manual Binding".

MOLAP Implementation

When a dimension is implemented in a MOLAP environment, the dimension definition and data are stored in an analytic workspace. This is done using analytic workspace objects such as dimensions, relationships, and so on. You can store multiple cubes in the same analytic workspace. For more information on MOLAP implementation, see "MOLAP Implementation of Dimensional Objects".

About Slowly Changing Dimensions

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

With the appropriate licensing, you can use Warehouse Builder to define, deploy, and load all three types of SCDs. You can create slowly changing dimensions only for dimensions that use a relational implementation.

Note:

Type 1 does not require additional licensing. However, type 2 and type 3 SCDs require the Oracle Warehouse Builder Enterprise ETL Option as described in Oracle Database Licensing Information.

Table 14-3 describes the three types of SCDs.

Table 14-3 Types of Slowly Changing Dimensions

Type Use Description Preserves History?

Type 1

Overwriting

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

No

Type 2

Creating a new version of a dimension record

There are multiple versions of the same dimension record, and new versions are created while the old ones are still kept upon modification.

Yes

Type 3

Creating a current value field

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

Yes


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

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

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

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

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

About Type 1 Slowly Changing Dimensions

In a Type 1 Slowly Changing Dimension (SCD), the new data overwrites the existing data. Typically, this type is not considered an SCD and most dimensions are of this type. Thus the existing data is lost as it is not stored anywhere else. This is the default type of dimension you create. You need not specify any additional information to create a Type 1 SCD. Unless there are specific business reasons, you must assume that a Type 1 SCD is sufficient. For more information on how to define and implement a Type 1 SCD, refer to the following:

About Type 2 Slowly Changing Dimensions

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

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

Note:

Be aware of the impact that all levels in a dimension not storing historical data has on query tools.

Defining a Type 2 Slowly Changing Dimension

To define a Type 2 Slowly Changing Dimension (SCD):

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

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

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

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

Type 2 SCD Example

Consider the Customers Type 2 SCD that contains two levels, Household and Customer. Table 14-4 lists level attributes of the Customers Type 2 SCD.

Table 14-4 Customers Type 2 SCD Attributes

Attribute Name Identifier

ID

Surrogate identifier

BUSN_ID

Business identifier

ADDRESS

 

ZIP

 

MARITAL_STATUS

 

HOME_PHONE

 

EFFECTIVE_DATE

Effective Date

EXPIRATION_DATE

Expiration Date


Customer is the leaf level and Household is the non-leaf level.

The Household level implements the following attributes: ID, BUSN_ID, ADDRESS, ZIP, EFFECTIVE_DATE, and EXPIRATION_DATE. The Customer level implements the following attributes: ID, BUSN_ID, MARITAL_STATUS, HOME_PHONE, EFFECTIVE_DATE, and EXPIRATION_DATE.The table that implements this Type 2 SCD (for a relational or ROLAP implementation) contains the following columns: DIMENSION_KEY, H_ID, H_BUSN_ID, H_ADDRESS, H_ZIP, H_EFFECTIVE_DATE, H_EXPIRATION_DATE, C_ID, C_BUSN_ID, C_MARITAL_STATUS, C_HOME_PHONE, C_EFFECTIVE_DATE, and C_EXPIRATION_DATE.

To create the CUSTOMERS Type 2 SCD:

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

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

Note:

While loading some Type 2 SCDs, if the target is an Oracle 9i database, only row-based mode is supported.

A workaround is to switch on hierarchy versioning, by setting the parent surrogate ID reference attribute as a trigger for all levels.

Hierarchy Versioning

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

Hierarchy versioning is not enabled by default for Type 2 SCDs. When you create a Type 2 SCD using the Create Dimension Wizard, hierarchy versioning is disabled. You must use the Data Object Editor to enable hierarchy versioning.

To enable hierarchy versioning:

  1. Right-click the Type 2 SCD in the Project Explorer and select Open Editor.

    The Data Object Editor is displayed.

  2. Navigate to the SCD tab.

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

    The Type 2 slowly changing dimension dialog box is displayed. The attributes of each level are displayed under the level node.

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

    For example, you create the Customers Type 2 SCD using the Create Dimension Wizard. Open the Data Object Editor for this Type 2 SCD and navigate to the Type 2 slowly changing dimension dialog box as described in steps 1 to 3. The Customer level has an attribute called HOUSEHOLD_ID. This attribute represents the parent attribute of each Customer record. For the HOUSEHOLD_ID attribute, select Trigger History in the Record History column.

Updating Type 2 Slowly Changing Dimensions

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

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

You can update the following in a Type 2 SCD:

  • Leaf level attribute

  • Leaf level versioned attribute

  • Non-leaf level attribute

  • Non-leaf level versioned attribute

  • Leaf level parent attribute

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

Updating a Leaf Level Attribute 

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

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

Updating a Leaf Level Versioned Attribute 

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

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

Updating a non-leaf Level Attribute 

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

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

Updating a non-leaf Level Versioned Attribute 

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

Hierarchy Versioning Disabled

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

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

Hierarchy Versioning Enabled

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

Updating the Leaf Level Parent Attribute 

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

Hierarchy Versioning Disabled

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

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

Hierarchy Versioning Enabled

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

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

About Type 3 Slowly Changing Dimensions

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

Defining a Type 3 Slowly Changing Dimension

To define a Type 3 Slowly Changing Dimension (SCD):

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

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

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

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

    • The surrogate ID cannot have previous values.

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

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

Type 3 SCD Example

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

About Time Dimensions

A time dimension is a dimension that stores temporal data. Time dimensions are used extensively in data warehouses. Warehouse Builder enables you to create and populate time dimensions. You can use Warehouse Builder to create both fiscal and calendar time dimensions.

When you create a time dimension using the wizard, Warehouse Builder creates the mapping for you to execute to populate the time dimension. Also, the data loaded into the time dimension conforms to the best practices recommended by Warehouse Builder for a time dimension.

This section contains the following topics:

Best Practices for Creating a Time Dimension

Warehouse Builder provides an accelerator to create time dimensions. It also specifies a set of rules as best practices for defining a time dimension. Warehouse Builder enforces these rules when you use Create Time Dimension wizard to create a time dimension.

The rules are as follows:

  • The time dimension can contain only a subset of the predefined levels specified by Warehouse Builder.

  • Each level in a time dimension must have attributes for the time span and ending date.

  • A time dimension can have one or more hierarchies. Each hierarchy should be either a fiscal hierarchy or a calendar hierarchy.

  • When you deploy a time dimension to the OLAP catalog, you must attach the time span and end date descriptors related to the levels to the dimension and its levels. When you create a time dimension using the Create Time Dimension wizard, Warehouse Builder performs this for you.

If you find these rules too restrictive for your business environment, you can create your own time dimension by setting the time attributes in the Data Object Editor. Ensure that you set the descriptors when you create a time dimension using the Data Object Editor.

Defining a Time Dimension

A time dimension consists of a set of levels and a set of hierarchies defined over these levels. Dimension roles are used extensively in time dimensions. For more information about dimension roles see "Dimension Roles". To create a time dimension you must define the following:

  • Levels

  • Dimension Attributes

  • Level Attributes

  • Hierarchies

Levels

A level represents the level of aggregation of data. A time dimension must contain at least two levels. You can use a level only once in a time dimension. For example, a time dimension can contain only one Calendar Month level. Each level must have a surrogate identifier and a business identifier. The surrogate identifier should be the ID level attribute.

A Warehouse Builder time dimension can contain only a subset of the following levels:

  • Day

  • Fiscal week

  • Calendar week

  • Fiscal month

  • Calendar month

  • Fiscal quarter

  • Calendar quarter

  • Fiscal year

  • Calendar year

Dimension Attributes

A dimension attribute is an attribute that is implemented by more than one level in the time dimension. Table 14-5 describes the dimension attributes of the Warehouse Builder time dimension.

Table 14-5 Dimension-level Attributes of the Time Dimension

Dimension Attribute Description

ID

The ID attribute is implemented as level ID in all the levels.

Start Date

The start date for the period. It always starts at 00:00:00 of the first day of the period.

End Date

The end date for the period. It always ends on 23:59:59 of the last day of the period.

Time Span

Number of days in the period.

Description

Description of the level record.


Level Attributes

A level attribute is a descriptive characteristic of a level value. Warehouse Builder creates level attributes for the time dimension based on the levels that you decide to implement for the time dimension.

Table 14-6 lists the attributes of each level in the Warehouse Builder time dimension. For a description of each attribute, refer to Appendix B.

Table 14-6 Time Dimension Level Attributes

Level Name Attribute Name

DAY

ID, DAY, START_DATE, END_DATE, TIME_SPAN, JULIAN_DATE, DAY_OF_CAL_WEEK, DAY_OF_CAL_MONTH, DAY_OF_ CAL_ QUARTER, DAY_OF_CAL_YEAR, DAY_OF_FISCAL_WEEK,DAY_OF_FISCAL_MONTH, DAY_OF_FISCAL_ QUARTER, DAY_OF_FISCAL_YEAR. DESCRIPTION.

FISCAL WEEK

ID, WEEK_NUMBER, WEEK_OF_FISCAL_MONTH, WEEK_OF_FISCAL_QUARTER, WEEK_OF_FISCAL_YEAR, START_DATE, END_DATE, TIME_DATE, DESCRIPTION.

CALENDAR WEEK

ID, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION.

FISCAL MONTH

ID, MONTH_NUMBER, MONTH_OF_QUARTER, MONTH_OF_YEAR, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION.

CALENDAR MONTH

ID, MONTH_NUMBER, MONTH_OF_QUARTER, MONTH_OF_YEAR, START DATE, END_DATE, TIME_SPAN, DESCRIPTION.

FISCAL QUARTER

ID, QUARTER_NUMBER, QUARTER_OF_YEAR, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION

CALENDAR QUARTER

ID, QUARTER_NUMBER, QUARTER_OF_YEAR, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION.

FISCAL YEAR

ID, YESR_NUMBER, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION.

CALENDAR YEAR

ID, YEAR_NUMBER, START_DATE, END_DATE, TIME_SPAN, DESCRIPTION


Hierarchies

A hierarchy is a structure that uses ordered levels to organize data. It defines hierarchical relationships between adjacent levels in a time dimension. A time dimension can have one or more hierarchies. Each hierarchy must be either a fiscal hierarchy or a calendar hierarchy. A single time dimension cannot contain both fiscal and calendar hierarchies.

Calendar Hierarchy A calendar hierarchy must contain at least two of the following levels: DAY, CALENDAR_WEEK, CALENDAR_MONTH, CALENDAR_QUARTER, CALENDAR_YEAR.

There is no drill-up path from CALENDAR_WEEK to any other levels. Thus, if a calendar hierarchy contains CALENDAR_WEEK level, it cannot contain either the CALENDAR_MONTH, CALENDAR_QUARTER, or CALENDAR_YEAR levels.

Fiscal Hierarchy A fiscal hierarchy should contain at least two of the following levels: DAY, FISCAL_WEEK, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR.

When you create a fiscal hierarchy, you must specify the following:

  • Start month of the fiscal year

  • Start date of the fiscal year

  • Start day for the fiscal week

  • Fiscal Convention used by the time dimension.

    The options that you can select for fiscal convention are:

    • 455: Select this option if the first month in the quarter has 4 weeks, the second month in the quarter has 5 weeks, and the third month in the quarter has 5 weeks.

    • 544: Select this option if the first month in the quarter has 5 weeks, the second month in the quarter has 4 weeks, and the third month in the quarter has 4 weeks.

Implementing a Time Dimension

When you implement a time dimension, you specify how the time dimension and its data are physically stored. You can store the time dimension data either in a relational form or multidimensional form in the database.

The implementation of a time dimension is similar to the implementation of a regular dimension. For more information on implementing a dimension, see "Implementing a Dimension".

Using a Time Dimension in a Cube Mapping

A time dimension created using the Create Time Dimension wizard uses the attribute ID as the surrogate identifier and the attribute CODE as the business identifier. The data type of both these attributes is NUMBER. When you create a cube that references a time dimension, the cube contains attributes that pertain to the surrogate identifier and the business identifier of the lowest level of the time dimension. Both these attributes have a data type of NUMBER.

When loading a cube, if you use a Warehouse Builder created time dimension as the source, both the source attributes and the cube attributes are of data type NUMBER. For example, consider a cube ALL_SALES that references two dimensions PRODUCTS and TIME_FISCAL. TIME_FISCAL is a calendar time dimension created using the Time Dimension wizard and it contains the levels Year, Month, and Day. When you create a map to load the ALL_SALES cube, you can directly map the attribute DAY_CODE of the Day level of TIME_FISCAL to the attribute ALL_SALES_DAY_CODE in the cube ALL_SALES. The data type of both these attributes is NUMBER.

Consider a scenario where you load data into the ALL_SALES cube from a source object in which the time data is stored as a DATE attribute. In this case, you cannot directly map the DATE attribute from the source to the attribute ALL_SALES_DAY_CODE of the ALL_SALES cube. Instead, you use an Expression operator in the mapping to convert the input DATE attribute to a NUMBER value and then load it into the ALL_SALES cube. In the Expression operator you convert the input using the following expression:

TO_NUMBER(TO_CHAR(input,'YYYYMMDD'))

where input represents the DATE attribute from the source object that needs to be converted to a NUMBER value. For information on using the Expression operator, see "Expression Operator".

Populating a Time Dimension

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

  • Start year of the data

  • Number of years of the data

  • Start day and month of fiscal year (only for fiscal time dimensions)

  • Start day of fiscal week (only for fiscal time dimensions)

  • Fiscal type (only for fiscal time dimensions)

The values of these attributes are initialized at the time of creating the time dimension using the Create Time Dimension wizard. You can alter the values of these parameters using the Data Object Editor. To change the values of the start date of the calendar year and the number of calendar years, use the Name tab of the Data Object Editor. To change the values of the parameters pertaining to fiscal time dimensions, use the Fiscal Settings button on the Hierarchies tab of Data Object Editor.

Note:

When you alter the values of any of the parameters pertaining to the data to be loaded into the time dimension, you must re-create the map that loads the time dimension. For more information on re-creating the map, see "Hierarchies Tab".

Figure 14-4 displays a mapping to load a calendar time dimension. The Mapping Input operator DATE_INPUTS represents the attributes needed to populate the time dimension.

Figure 14-4 Mapping that Populates a Time Dimension

Description of Figure 14-4 follows
Description of "Figure 14-4 Mapping that Populates a Time Dimension"

Overlapping Data Populations

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

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

About Cubes

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

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

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

A typical cube contains:

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

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

Defining a Cube

A cube consists of the set of measures defined over a set of dimensions. To create a cube, you must define the following:

Cube Measures

A measure is data, usually numeric and additive, that can be examined and analyzed. Examples of measures include sales, cost, and profit. A cube must have one or more measures. You can also perform aggregation of measures. Only numeric measures can be aggregated.

Cube Dimensionality

A cube is defined by a set of dimensions. A cube can refer to a level that is not the lowest level in a dimension.

For cubes that use a pure relational implementation, you can reuse the same dimension multiple times with the help of dimension roles. For more information on dimension roles, see "Dimension Roles".

Before you validate a cube, ensure that all the dimensions that the cube references are valid.

To define a dimension reference, specify the following:

  • The dimension and the level within the dimension to which the cube refers.

    For a cube that uses a relational implementation, you can refer to intermediate levels in a dimension. However, for cubes that use a MOLAP implementation, you can only reference the lowest level in the dimension. Warehouse Builder supports a reference to the non surrogate identifier of a level, for example, the business keys.

  • For dimensions that use a relational or ROLAP implementation, a dimension role for each dimension to indicate what role the dimension reference is performing in the cube. Specifying the dimension role is optional.

When you define a MOLAP cube, the order in which you define the dimension references is important. The physical ordering of dimensions on disk is the same as the order in which you define the dimension references. The physical ordering is tightly coupled with the sparsity definition. Define the dimension references in the order of most dense to least dense. Time is usually a dense dimension, and listing it first expedites data loading and time-based analysis. For more information on defining dimension references, see "Dimensions Page" and "Dimensions Tab". For more information on sparsity, see "Advanced Dialog Box".

Default Aggregation Method 

You can define aggregations that should be performed on the cube. For ROLAP cubes, you can only define a single aggregation method for the cube. For MOLAP cubes, you can define a different aggregation method for each dimension of each measure. Warehouse Builder enables you to use the same aggregation function for all the cube measures or specify different aggregate functions for each measure.

Warehouse Builder supports the following default aggregation methods: SUM, SSUM (scaled SUM), AVERAGE, HAVERAGE (hierarchical average), MAX, MIN, FIRST, LAST, AND, OR, HIERARCHICAL_FIRST and HIERARCHICAL_LAST. If you do not want to perform aggregation, select NOAGG. The methods AND and OR are not applicable for cubes that use a multidimensional implementation.

Note:

You cannot define aggregation for pure relational cubes.

Cube Example

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

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

  • Dollar_sales: Stores the amount of the sale.

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

Table 14-7 Dimensionality of the Sales Cube

Dimension Name Level Name

Products

Product

Customers

Customer

Times

Day


Implementing a Cube

When you implement a cube, you specify the physical storage details for the cube. You can implement a cube in a relational form or a multidimensional form in the database.

The types of implementation you can use for cubes are:

  • Relational implementation

  • ROLAP implementation

  • MOLAP implementation

To set the type of implementation for a cube, use the Deployment Option configuration property. For more details on setting this option, see "Configuring Cubes".

Relational and ROLAP Implementation of a Cube

The database object used to store the cube data is called a fact table. A cube must be implemented using only one fact table. The fact table contains columns for the cube measures and dimension references. For more information on setting the implementation option for a cube, see "Implementing Dimensional Objects".

To implement a cube:

  • Select a table or materialized view that will store the cube data.

  • For each measure, select a column that will store the measure data.

  • For each dimension reference, select a column that will store the dimension reference.

    Each dimension reference corresponds to a column on the fact table and optionally a foreign key from the fact table to dimension table. The 1:n relationships from the fact tables to the dimension tables must be enforced.

Figure 14-5 displays the bindings for the relational implementation of the SALES cube. The data for the SALES cube is stored in a table called SALES.

Figure 14-5 Implementation of the Sales Cube

Description of Figure 14-5 follows
Description of "Figure 14-5 Implementation of the Sales Cube"

Binding

When you perform binding, you specify the database columns that will store the data of each measure and dimension reference of the cube. You can perform auto binding or manual binding for a cube. For more information on binding, see "Binding".

Auto Binding When you perform auto binding, Warehouse Builder creates the table that stores the cube data and then binds the cube measures and references to the database columns. For detailed steps on performing auto binding, see "Auto Binding".

When you perform auto binding for a cube, ensure that you auto bind the dimensions that a cube references before you auto bind the cube. You will not be able to deploy the cube if any dimension that the cube references has been auto bound after the cube was last auto bound.

For example, you create the SALES cube that references the TIMES and PRODUCTS dimensions and perform auto binding for the cube. You later modify the definition of the PRODUCTS dimension. If you now attempt to auto bind the SALES cube again, Warehouse Builder generates an error. You must first auto bind the PRODUCTS dimensions and then auto bind the cube.

Manual Binding In manual binding, you must first create the table or view that stores the cube data and then map the cube references and measures to the database columns that store their data. Alternatively, you can use an existing database table or view to store the cube data.

For information about how to perform manual binding, see "Manual Binding".

MOLAP Implementation of a Cube

Storing the cube and its data in an analytic workspace is called a MOLAP implementation. You can store multiple cubes in the same analytic workspace. For more information on OLAP implementation, see "MOLAP Implementation of Dimensional Objects".

Solve Dependency Order of Cube

Certain business scenarios may require the dimensions in a cube to be evaluated in a particular order. The order in which the dimensions are evaluated is called the solve dependency order of the cube. For example, in the Sales cube, the Time dimension may need to be evaluated before the Products dimension. For each dimension of the cube, you can specify a dependency on another dimension of the cube.

The advantage of specifying the dependency order is that it enables Warehouse Builder to optimize the query speed of calculating the joins of the dimension and cubes. For example, retrieving results from the sales cube based on Time criteria may be more selective than retrieving result based on Products criteria. In this case, you can specify that for the Sales cube, the Products dimension depends on the Time dimension.

Specifying the solve dependency order is optional. If you do not specify a dependency order, the optimizer determines the solve-order with additional flexibility.

Creating Dimensions

To create a dimension, 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, many settings are defaulted 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 workspace using auto binding.

    For more information about the defaults used by the Dimension wizard, see "Defaults Used By the Create Dimension Wizard".

  • Using the Data Object Editor

    The Data Object Editor gives you full control over all aspects of the dimension definition and implementation. This provides maximum flexibility. 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 about the Time Dimension wizard, see "Creating Time Dimensions".

Using the Create Dimension Wizard

To create a dimension using the Dimension wizard:

  1. From the Project Explorer expand the Databases node and then the Oracle node.

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

  3. Right-click Dimensions, 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:

Name and Description Page

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

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

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

Storage Type Page

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

  • 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, the implementation tables used to store the dimension data are created. The default implementation of the dimension is using a star schema. This means that the data for all the levels in the dimension is stored in a single database table.

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, the dimension is stored in an analytic workspace that uses the same name as the Oracle module to which the dimension belongs. The tablespace that is used to store the analytic workspace is the tablespace that is defined as the users tablespace for the schema that contains the dimension metadata.

Note:

For information about certain limitations of deploying dimensions to the OLAP catalog, see Oracle Warehouse Builder Release Notes.

Dimension Attributes Page

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

Specify the following details for each dimension attribute:

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

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

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

    Surrogate: Indicates that the attribute is the surrogate identifier of the dimension.

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

Levels Page

The Levels page defines the levels of aggregation in the dimension. A dimension must contain at least one level. The only exception is value-based hierarchies that contain no levels. You can create a value-based hierarchy using the 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, two attributes are created, one for the surrogate identifier and one for the business identifier, that correspond to the parent level of each level. For a MOLAP dimension, for each level, one attribute that corresponds to the business identifier of the parent level is created.

For example, the Products dimension contains the following levels: Total, Groups, and Product. If you choose a relational or ROLAP implementation, two attributes are created, 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, an attribute is created in both the Product and Groups levels, that reference the business identifier of the parent level.

Note:

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

Level Attributes Page

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

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

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

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

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

  1. Select the State level in the Levels section.

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

By default, the following defaults are used:

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

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

Slowly Changing Dimension Page

The Slowly Changing Dimension page enables you to define the type of slowly changing policy used by the dimension. This page is displayed only if you had chosen Relational storage (ROLAP) as the storage type on the Storage Type Page. For more information about slowly changing dimensions concepts, see "About Slowly Changing Dimensions".

Note:

Use of this feature requires the Oracle Warehouse Builder Enterprise ETL Option as described in Oracle Database Licensing Information.

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

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

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

    • Effective date

    • Expiration date

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

    Note:

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

Pre Create Settings Page

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

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

Note:

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

Dimension Creation Progress Page

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

Summary Page

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

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

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

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

Deploying Dimensions To create the dimension in the target schema, you must deploy the dimension. For a ROLAP dimension, ensure that you deploy the sequence and the implementation tables before you deploy the dimension. Alternatively, you can deploy all these objects at the same time. For more information see "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.

Defaults Used By the Create Dimension Wizard

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

Storage

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

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

Dimension Attributes

Warehouse Builder creates default dimension attributes with the properties specified in Table 14-8.

Table 14-8 Default Dimension Attributes

Dimension Attribute Name Identifier Data Type

ID

Surrogate

NUMBER

Name

Business

VARCHAR2

Description

 

VARCHAR2


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

Hierarchies

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

Level Attributes

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

Slowly Changing Dimensions

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

Implementation Objects

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

For 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, an analytic workspace with the same name as the Oracle module that contains the dimension is created. For example, if you create a dimension called PRODUCTS in the SALES_WH module, the dimension is stored in an analytic workspace called SALES_WH. If an analytic workspace with this name does not already exist, it is first created and then the dimension is stored in this analytic workspace.

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

Using the Data Object Editor

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:

  1. From the Project Explorer expand the Databases node and then the Oracle node.

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

  3. Right-click Dimensions, select New, then Using Editor.

    Warehouse Builder displays the Add a New or Existing Dimension dialog box.

  4. Select the Create a New Dimension option. Enter the name of the dimension and select the module to which the dimension belongs.

  5. Click OK.

    Warehouse Builder displays the Data Object Editor. To define a dimension, provide information about the following tabs of the Dimension Details panel:

    When you use the Data Object Editor to create a dimension that has a relational implementation, the physical structures that store the dimension data are not automatically created. You must create these structures.

  6. For dimensions that have a relational implementation, bind the attributes in the dimension to the database columns that store their data, see "Binding Attributes".

Name Tab

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

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

Dimension Roles Use the Dimension Roles section to define dimension roles. 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.

Storage Tab

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

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, these storage settings are used to perform auto binding.

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

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, the level name is added as a prefix to each value. It is recommended that you select this option unless you know that every dimension member is unique.

If you are sure that dimension members are unique across levels, then you can use the exact same names in the analytic workspace as the source. For example, if your relational schema uses numeric surrogate keys to assure uniqueness, then 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, the data type of the surrogate identifier is changed to VARCHAR2.

Attributes Tab

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

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

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

Levels Tab

Use the Levels tab to define the 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. 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.

Hierarchies Tab

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

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

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

  • Value-based: Select this option to create a value-based hierarchy. A value-based hierarchy contains no levels. It must have an attribute identified as the parent identifier. Since you can create value-based hierarchies only for MOLAP dimensions, this option is displayed only if you select MOLAP: Multidimensional data structures on the Storage tab. For more information about 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 list to display all the levels defined in the dimension.

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

    For example, the Products dimension contains the following hierarchy:

    Total > Product

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

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

SCD Tab

Use this tab to specify the type of slowly changing policy that the dimension implements. Since you can create a slowly changing dimension only for dimensions that use a relational implementation, the options on this tab are enabled only if you select Relational: relational data structures on the Storage tab. The options that you can select for slowly changing policy are:

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

  • Type 2: Store the complete change history: Select this option to create a Type 2 SCD. Click Settings to specify the additional details such as triggering attribute, effective date and expiration date for each level.as described in "Creating 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.

Data Viewer Tab

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.

Binding Attributes

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, the attributes in the dimension are mapped 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 about 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:

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

  2. 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 box. To store the dimension data, you either select an existing table or create a new table.

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

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

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

Creating Slowly Changing Dimensions Using the Data Object Editor

You can create an SCD either using the Dimension wizard or the Data Object Editor.

Note:

Creating SCD type 2 or 3 requires the Oracle Warehouse Builder Enterprise ETL Option as described in Oracle Database Licensing Information.

To create an SCD using the Dimension Wizard, 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 about the Slowly Changing Dimension page, see "Slowly Changing Dimension Page".

To create SCDs using the Data Object Editor, see the following sections:

Creating a Type 2 SCD

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:

  1. From the Project Explorer, expand the Databases node and then the Oracle node.

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

  3. Right-click Dimensions, select New, then Using Editor.

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

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

  6. Provide information about the following tabs of the Data Object Editor:

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

  8. Click Settings to the right of this option.

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

  9. Provide information about the Storage Tab of the Data Object Editor.

Type 2 Slowly Changing Dimension Dialog Box

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

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

  • Identifying Attribute: Represents the level attribute.

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

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

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

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

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

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

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

Creating a Type 3 SCD

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:

  1. From the Project Explorer, expand the Database node and then the Oracle node.

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

  3. Right-click Dimensions, select New, then using Using Editor.

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

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

  6. Provide information about the following tabs of the Data Object Editor:

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

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

  8. Provide information about the Storage Tab of the Data Object Editor.

Type 3 Slowly Changing Dimension Dialog Box

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

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

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

  • Identifying Attribute: Represents the level attribute.

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

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

    The surrogate ID of a level cannot be versioned.

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

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

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

Editing Dimension Definitions

Use the 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 about the tabs in the Data Object Editor, see "Using the Data Object Editor".

Configuring Dimensions

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

To configure the physical properties for a dimension:

  1. From the Project Explorer, right-click the dimension name and select Configure.

    The Configuration Properties window is displayed.

  2. 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 about 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".

    Visible: This property is not used in code generation.

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

Deployment Options for Dimensions

Use the Deployment Option configuration property to specify how the dimension metadata is deployed. The options you can select for a dimension are:

Following are the options that you can set for this property.

  • Deploy All

  • Deploy Data Objects Only

  • Deploy to Catalog Only

For more information about these options, see "Deploying Dimensional Objects".

Deployment Options for Different Dimension Implementations

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.

Creating Cubes

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

  • Using the Create Cube Wizard

    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.

  • Using the Data Object Editor

    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.

Using the Create Cube Wizard

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

  1. From the Project Explorer expand the Databases node and then the Oracle node.

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

  3. Right-click Cubes, select New, 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:

Name and Description Page

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

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

  • Description: Specify an optional description for the cube.

Storage Type Page

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

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

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, the implementation table used to store the cube data is created.

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, the name used to store the cube in the analytic workspace is generated. If no analytic workspace exists, one is created using the name you specify.

Dimensions Page

The Dimensions page defines the dimensionality of the cube. A cube must refer to at least one dimension. You define dimensionality by selecting the dimensions that the cube references. 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 workspace. Each module in the project is represented by a separate node. Expand a module node to view all the dimensions in that module.

Warehouse Builder filters the dimensions displayed in the Available Dimensions section based on the implementation type chosen for the dimension. If you select ROLAP as the storage type, only dimensions that have a relational implementation are listed. 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.

Measures Page

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

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

  • Description: An optional description for the measure.

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

    Note:

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

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

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

  • Seconds Precision: Represents the number of digits in the fractional part of the datetime field. It can be a number between 0 and 9. The seconds precision is used only for TIMESTAMP 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.

Summary Page

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

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

  • The cube object.

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

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

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

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

Note:

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

Defaults Used by the Create Cube Wizard

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

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

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

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

Using the Data Object Editor

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:

  1. From the Project Explorer expand the Databases node and then the Oracle node.

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

  3. Right-click Cubes, select New and then Using Editor.

    Warehouse Builder displays the Add a New or Existing Cube dialog box.

  4. Select the Create a New Cube option. Enter the name of the cube and select the module to which the cube belongs.

  5. Click OK.

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

    When you use the Data Object Editor to create a cube, the physical objects that store the cube data are not automatically created. You will need to create these objects.

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

Name Tab

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

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

  • Description: Specify an optional description for the cube.

Storage Tab

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

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, see Oracle Database Data Warehousing Guide.

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. Use the Analytic Workspace section to specify the storage details. Enter the following details in this section:

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

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

Dimensions Tab

Use the Dimensions tab to define the dimensionality of the cube. This tab displays a table that you use to select the dimensions that the cube references and the Advanced button. You can change the order of the dimensions listed in this tab by using the arrows on the left of this tab.

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

The table on the Dimensions tab contains the following columns:

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

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

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

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

Advanced Dialog Box

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

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

  • Dimensions: This column displays all the dimensions listed on the Dimension tab of the 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 box, you must change the order in which the dimensions are listed on the Dimensions Tab of the Data Object Editor.

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

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

    Use the following guidelines to order dimensions:

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

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

    Defining sparsity for a cube provides the following benefits:

    • Improves data retrieval speed.

    • Reduces the storage space used by the cube.

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

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

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

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

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

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

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

Measures Tab

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

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

  • Description: An optional description for the measure.

  • Data Type: The data type of the measure.

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

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

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

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

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

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

Note:

You can create calculated measures for MOLAP dimensions only.

Click the Generate 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.

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.

Select Calculated Measure Type

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:

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

    • Percent Variance

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

      (Sales-Quota)/Quota.

    • Rank

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

    • Share

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

      (Current member / Specified member).

    • Variance

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

      Sales Variance = Sales - Sales Forecast.

  • Prior/Future Comparison: Use this type to define prior and future value calculations such as the 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:

      (Current Value - Previous Value)

    • Percent Difference from Prior Period

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

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

    • Future Value

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

  • Time Frame: This type enables you to create the 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:

      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.

Define Calculated Measure Details

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

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

Reviewing the Summary Information

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

Aggregation Tab

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

Specify the following options on the Aggregations tab:

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

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

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

Note:

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

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

Note:

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

Data Viewer Tab

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 display the query builder. 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.

Binding Cube Attributes

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, the measures and dimension references of the cube are automatically mapped to the database columns that store their data.

To perform auto binding:

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

  2. 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 about 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:

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

  2. 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 box. You either select an existing table or create a new table to store the cube data.

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

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

Cubes Stored in Analytic Workspaces

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

Ragged Cube Data

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

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

Defining Aggregations

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

Auto Solving MOLAP Cubes

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

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

  • The cube is compressed

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

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

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

  • Mapping that contains the cube is executed

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

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

  • Warehouse Builder transformations are used to solve the cube

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

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

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

Solving Cube Measures

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

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

  1. Open the 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.

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

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

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

Solving Cubes Independent of Loading

You can solve cubes independent of loading using the predefined transformation WB_OLAP_AW_PRECOMPUTE. This function also enables you to solve measures independently of each other. This transformation function is available in the 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 The following calculation plans are generated:

  • 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 the metadata.

Parallel Solving of Cubes

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.

Output of a MOLAP Cube Mapping

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

  • ALL_SCHEDULER_JOBS

  • ALL_SCHEDULER_JOB_RUN_DETAILS

  • ALL_SCHEDULER_RUNNING_JOBS

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

Editing Cube Definitions

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.

Use the tabs of the Data Object Editor to edit the cube definition. For more information about the tabs in the Data Object Editor, see "Using the Data Object Editor".

Configuring Cubes

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

To configure the physical properties for a cube:

  1. From the Project Explorer, right-click the cube name and select Configure.

    The Configuration Properties window is displayed.

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

    Visible: This property is not used in code generation.

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.

Creating Time Dimensions

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

You can also use the 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.

Creating a Time Dimension Using the Time Dimension Wizard

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

  1. From the Project Explorer expand the Databases node and then the Oracle node.

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

  3. Right-click Dimensions, select New, 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:

Name and Description Page

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

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

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

Storage Page

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

  • 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 about these options, see "Storage Type Page".

Data Generation Page

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

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

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

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

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

  • Calendar: Creates a calendar time dimension.

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

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

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

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

Levels Page (Calendar Time Dimension Only)

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

  • Normal Hierarchy

  • Week Hierarchy

Normal Hierarchy The Normal Hierarchy contains the following levels:

  • Calendar year

  • Calendar quarter

  • Calendar month

  • Day

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

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

Levels Page (Fiscal Time Dimension Only)

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

  • Fiscal year

  • Fiscal quarter

  • Fiscal month

  • Fiscal week

  • Day

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

Pre Create Settings Page

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

Time Dimension Progress Page

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

Summary Page

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

Click Finish to complete the creation of the time dimension. You now have a fully functional time dimension. This dimension is displayed under the Dimensions node of the 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 workspace. Warehouse Builder also binds the time dimension attributes to the database columns that store their values. For a MOLAP implementation, the analytic workspace that stores the time dimension and its data is created.

  • A mapping that populates the time dimension.

Note:

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

Defaults Used by the Time Dimension Wizard

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

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

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

Editing Time Dimension Definitions

To edit a time dimension:

  1. From the Project Explorer expand the Databases node then the Oracle node.

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

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

  4. Edit the information about the following tabs:

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

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

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

Name Tab

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

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

  • Description: An optional description for the time dimension.

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

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

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

Storage Tab

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

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

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

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

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, the analytic workspace is stored in the tablespace that is defined as the users tablespace for the schema containing the time dimension metadata.

Attributes Tab

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

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

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

  • Description: An optional description for the attribute.

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

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

  • Length: Specify length only for character data types.

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

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

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

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

Levels Tab

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

Hierarchies Tab

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

The Hierarchies tab contains two sections: Hierarchies and Levels.

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

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