|Oracle® Warehouse Builder Concepts
11g Release 2 (11.2)
|PDF · Mobi · ePub|
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:
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.
"Designing Source and Target Schemas" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
"Overview of Data Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for a complete list and discussion of the data objects that you can use in Oracle Warehouse Builder, and the data types that you can use and create.
"Defining Business Intelligence Objects" in Oracle Warehouse Builder Sources and Targets Guide.
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.
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.
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:
Dimensions, discussed under "About Creating and Using Cubes and Dimensions" in this guide.
Cubes, discussed under "Cubes: Measures and Dimensionality" in this guide.
See Also:"Defining Dimensional Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for complete procedures.
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.
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.
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.
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:
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.
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:
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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 RAW, or
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.
Creating a dimension consists of:
See Also:"Defining Dimensional Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
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.
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.
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.
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.
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.
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:
MANAGER_ID. In this dimension, ID is the surrogate identifier and
MANAGER_ID is the parent identifier.
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.
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.
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.
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.
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.
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:
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 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:
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.
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.
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.
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.
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.
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.
See Also:"Creating Time Dimensions" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
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.
See Also:"Overview of Cubes" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
A cube consists of the set of measures defined over a set of dimensions as follows.
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.
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.
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.
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.
See Also:"Creating Cubes" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
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
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".
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.
See Also:"Configuring Cubes" in Oracle Warehuse Builder Data Modeling, ETL, and Data Quality Guide.