Skip Headers
Oracle® Warehouse Builder Concepts
11g Release 2 (11.2)

E10581-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Data Objects

The data in the data system that you implement with Oracle Warehouse Builder is stored in target schemas. This data is in the form of data objects such as tables, views, dimensional objects, and cubes. This section discusses relational and dimensional data objects that you design for your target system, and business intelligence objects for analytical views.

This section contains these topics:

Types of Data Objects

Oracle Warehouse Builder uses relational and dimensional data objects and intelligence objects as follows:

  • Relational objects rely on tables and table-derived objects to store and link all of their data. Relational objects include tables, views, materialized views, and sequences.

  • Dimensional objects contain additional metadata to identify and categorize your data. Dimensional objects include dimensions and cubes.

  • Intelligence objects enable you to store definitions of business views. You can deploy these definitions to Oracle Business Intelligence Suite Enterprise Edition (OBI EE). In Oracle Warehouse Builder, intelligence objects are displayed under the Business Intelligence node in the Project Navigator.

See Also:

Data Object Editors

Oracle Warehouse Builder provides contextual data object editors to create, edit, configure, validate, and deploy Oracle data objects. The data object editors work with relational, dimensional, and business intelligence objects.

Use data object editors to:

  • Create, edit, and delete relational and dimensional objects.

  • Create, edit, and delete the following business intelligence objects: Business Areas and Item Folders.

  • Define relationships between Oracle data objects.

  • Validate, generate, and deploy Oracle data objects.

  • Define and edit all aspects of a data object such as its columns, constraints, indexes, partitions, data rules, and attribute sets.

  • Define implementation details for dimensional objects with a relational implementation.

Data Viewers

Oracle Warehouse Builder Data Viewers are available for dimensions and cubes, and relational objects (tables, views, materialized views, sequences, external tables and so forth). The dimension and cube Data Viewers enable interactive, logical-level browsing of the contents of these objects at a logical level, regardless of the details of the underlying physical storage. The dimension Data Viewer lets you browse and drill into the dimension members organized by hierarchy and level. The cube Data Viewer enables interactive browsing of the contents of the cube, and pivoting and drilling down into the data along any dimension.

To access a Data Viewer, from the Projects Navigator, right-click a data object and select Data.

By default, the Data Viewer for the selected object displays the first hundred rows of data. To retrieve the next set of rows, click Get More. Alternatively, you can click More to perform the same action. The columns and column names displayed in the Data Viewer are taken directly from the location in which the actual table is deployed.

See Also:

"Using the Data Viewer to View Data Stored in Data Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Dimensional Objects: Dimensions and Cubes

The term dimensional object refers to both dimensions and cubes. Oracle Warehouse Builder provides wizards to create and maintain dimensions by answering simple questions. Oracle Warehouse Builder supports two types of dimensional objects:

See Also:

"Defining Dimensional Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for complete procedures.

ROLAP versus MOLAP Implementations

Oracle Warehouse Builder separates the logical design of dimensional objects from their storage. The logical design, which consists of business rules, first focuses on the structure and the content of the dimensional object. You can then choose to store the dimensional object in a relational ROLAP or an analytic MOLAP implementation.

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

Oracle Warehouse Builder enables you to use the same metadata to create and manage both your relational and multidimensional data stores. Separating the logical design from the physical implementation has the advantage of making design of ETL logic transparent. Regardless of the physical storage implementation, the logic for loading dimensions and cubes is identical.

Uses for OLAP

Dimensional objects provide complex analytic power to your data warehouse. After you load data into dimensional objects, you can 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.

See Also:

"ROLAP Implementation of Dimensional Objects" and "MOLAP Implementation of Dimensional Objects" under "Overview of Implementing Dimensional Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

About Creating and Using Cubes and Dimensions

A dimension consists of a set of levels and a set of hierarchies defined over these levels. Working with cubes and dimensional objects consists of four high-level processes:

  1. "Dimension Creation"

  2. "Dimension Implementation"

  3. "Dimension Deployment"

  4. "Dimension Loading"

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:

Note:

To use a MOLAP implementation, you must have the following software installed:
  • Oracle Database 11g 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" in Oracle Warehouse Builder Help.

Relational Implementation of Dimensions

The dimensional data is stored in implementation objects that are typically tables. For relational dimensions, Oracle Warehouse Builder can use a star schema, a snowflake schema, or a manual schema to store the implementation objects.

See Also:

"Star Schema" and "Snowflake Schema" in this guide.
ROLAP Implementation of Dimensional Objects

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.

See Also:

"About the OLAP Catalog" in this guide.
MOLAP Implementation of Dimensional Objects

The dimension data is stored in an analytic workspace in Oracle Database 11g. This analytic workspace, in turn, is stored in the database.

See Also:

Oracle OLAP User's Guide for more information about analytic workspaces.

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.

See Also:

"Dimension Operator as a Target" and "Cube Operator" in Oracle Warehouse Builder Help for more information on loading dimensions and cubes

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

See Also:

"Orphan Management for Dimensional Objects" under "Overview of Cubes" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Relational Dimensions

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

Relational dimensions provide improved 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.

See Also:

"Creating Relational Data Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Rules for Dimension Objects

When you create a dimension object using Oracle 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 multiple 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.

Note:

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. Refer to the topic, "Limitations of Deploying Dimensions to the OLAP Catalog" under "Overview of Dimensions" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

See Also:

"About the OLAP Catalog" under "Overview of Implementing Dimensional Objects" in Oracle Warehouse Builder Data Modeling, ETl, and Data Quality Guide.

About Defining a Dimension

Creating a dimension consists of:

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.

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

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

See Also:

"Using Control Rows" in Chapter 3, "Defining Dimensional Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

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.

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

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

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, 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 follows
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 follows
Description of "Figure 5-3 Snowflake Schema Implementation of the Products Dimension"

Slowly Changing Dimensions (SCDs)

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: Type 1, Type 2, and Type 3.

With the appropriate licensing, you can use Oracle 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 Oracle Warehouse Builder Enterprise ETL Option. Refer to Oracle Database Licensing Information.

See Also:

"Overview of Slowly Changing Dimensions" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for complete information about the types of SCDs and how to use them.

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.

See Also:

"Creating Slowly Changing Dimensions" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for detailed information about all types of SCDs.

Time Dimensions

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

When you create a time dimension using the wizard, Oracle 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 Oracle Warehouse Builder for a time dimension.

Cubes: Measures and Dimensionality

A cube is a data object that contains measures, and links 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.

Note:

In the relational implementation of a cube, the 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.

Cube Definitions

A cube consists of the set of measures defined over a set of dimensions as follows.

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.

Note:

You cannot define aggregation for pure relational cubes.

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 are:

To set the type of implementation for a cube, use the Deployment Option configuration property.

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

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