5 Data Objects

Dimension Creation

When you define dimensional objects such as cubes, 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.

Oracle Warehouse Builder provides the following two methods to define dimensions:

• Create Dimension Wizard: Creates a fully functional dimensional object along with implementation objects that store the dimension data. Provides default settings for the most common values. Use this wizard when you want to quickly create a dimension without manually specifying settings.

  See Also:

  "Defaults Used By the Create Dimension Wizard" under "Creating Dimensions" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

• Dimension Editor: 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.

Dimension Implementation

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

• "Relational Implementation of Dimensions"

• "ROLAP Implementation of Dimensional Objects"

• "MOLAP Implementation of Dimensional Objects"

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" in Oracle Warehouse Builder Help.

Dimension Deployment

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.

Oracle 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 Oracle Business Intelligence Enterprise Edition 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.

  See Also:

  "About the OLAP Catalog" in this guide.

• 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. It means that the dimensional object should be bound to its implementation objects. Also ensure that the dimensional object is valid.

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 with the dimensional object.

Dimension Loading

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

About the 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 Oracle 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 11g, 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 Oracle Business Intelligence Enterprise Edition use the OLAP catalog to query relational and multidimensional data. The application must not be aware of whether the data is located in relational tables or in analytic workspaces, nor does it require to know the mechanism for accessing it.

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

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

Description of "Figure 5-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.

Orphan Management Policy for Dimensions

The orphan management policy in Oracle Warehouse Builder enables you to manage orphan records in dimensions and cubes. An orphan record is one that does not have a corresponding, existing parent record.

Orphan records can occur when:

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

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

Oracle Warehouse Builder enables you to specify a different orphan management policy for loading dimensional data and removing dimensional data.

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 Oracle 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 Oracle Business Intelligence Suite Enterprise Edition (OBI EE) 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 for 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, for a composite business identifier) of the dimension.

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 implements. 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) for 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 multiple 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, Oracle 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.

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

Control Rows

When you load data into a dimension, Oracle Warehouse Builder creates control rows. Control rows link fact data to a dimension at any level, thus enabling the reuse of a dimension in different cubes.

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 different 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" in Oracle Warehouse Builder Help.

Star Schema

In a star schema implementation, Oracle Warehouse Builder stores the dimension data in a single table. Because the same table or view stores data for multiple 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 equals as the dimension attribute name. To avoid name conflicts caused by all level data being stored in the same table, Oracle Warehouse Builder uses the following guidelines for naming in a star table:

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

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

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

Figure 5-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 5-2 Star Schema Implementation of Products Dimension

Description of "Figure 5-2 Star Schema Implementation of Products Dimension"

For relational or ROLAP dimensions that use a star implementation, you can bind attributes from multiple levels to the same database column. A database column that is bound to attributes from multiple 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, Oracle Warehouse Builder uses multiple table to store the dimension data. Separate database tables or views store the data pertaining to each level in the dimension.

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

Figure 5-3 Snowflake Schema Implementation of the Products Dimension

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

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. Oracle 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 equals 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" or "Dimensions Tab" in Oracle Warehouse Builder Help. For more information on sparsity, see "Advanced Dialog Box" in Oracle Warehouse Builder Help.

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. Oracle Warehouse Builder enables you to use the same aggregation function for all the cube measures or specify different aggregate functions for each measure.

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

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 "Dimension Implementation".

To implement a cube:

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

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

• For each dimension reference, select a column that stores 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 5-4 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 5-4 Implementation of the Sales Cube

Description of "Figure 5-4 Implementation of the Sales Cube"

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