OWB - Alias Dimension (for instance for multiple Time Dimension implementation)

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

Table of Contents

1 - About

In a dimension definition, a dimension role is an alias for a dimension.

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

  • Name: Represents the name of the dimension role.
  • Description: Specify an optional description for the dimension role

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.

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

Advertising