Transforming Tables into Multidimensional Data Structures

Differences in data models

When an OLAP application runs, OLAP Services fetches the required data from an Oracle9i database into a temporary cache. Within this cache, the data is stored in multidimensional data objects. For the data to be fetched correctly, you must identify which columns will be fetched and what role they will play.

The basic data model in a relational database is a table composed of one or more columns of data. All of the data is stored in columns. In contrast, the basic data model in a multidimensional cache is a cube, which is composed of measures, dimensions, and attributes. Once you determine that you will want to access a particular column through OLAP Services, you must identify whether the data from that column will function in it as a measure, a dimension, or an attribute. You also identify which columns are keys. These decisions are stored as metadata and constraints.

Types of data structures

The data warehouse built in your Oracle RDBMS and OLAP Services use different data structures. Note that even though both use dimensions, their implementations are different, as described in "Dimensions".

A data warehouse has the following data structures:

• Tables

• Materialized Views

• Dimensions

OLAP Services has these data structures:

• Levels

• Attributes

• Dimensions

• Measures

• Cubes

Identifying your data requirements

Before you can begin mapping columns to multidimensional structures, you must know what data users want to view and at what levels they want to view it. If you have already created a data warehouse, then you have already done most of this research. You only need to verify that the requirements haven't changed for the analytical applications that will be run using OLAP Services.

You can use Oracle Enterprise Manager to explore the existing schemas. Then make a note of the columns that you are going to use and the types of multidimensional objects you want to define them as: measures, dimensions, or attributes. Keep in mind that the OLAP API only has access to objects in the database through the metadata definitions. Thus, if an object (such as a column in a table) has not been defined in the metadata, then it is not available to OLAP applications.

Measures

Measures are the same as facts. The term "fact" is typically used in relational databases, and the term "measure" is typically used in multidimensional applications. You will encounter both terms in Oracle Enterprise Manager, since the creation of metadata is the process of associating relational objects with their multidimensional counterparts.

Measures are thus located in fact tables. A fact table typically has two types of columns: measures (or facts) and foreign keys to dimension tables.

Measures contain the data that you wish to analyze, such as Sales or Cost. Oracle Enterprise Manager requires that a column have a numerical or date data type to be identified as a measure. Most frequently, a measure is numerical and additive.

One or more columns in the dimension tables form constraints on the fact tables. These constraints are defined by foreign keys in the fact tables, by the metadata, or both.

Dimensions

Dimensions identify and categorize your data. Dimension members are stored in a dimension table. Each column represents a particular level in a hierarchy. In a star schema, the columns are all in the same table; in a snowflake schema, the columns are in separate tables for each level.

Because measures are typically multidimensional, a single value in a measure must be qualified by a member of each dimension to be meaningful. For example, a Sales measure might have dimensions for Product, Geographic Area, and Time. A value in the Sales measure (37854) is only meaningful when it is qualified by a product (DVD Player), a geographic area (Pacific Rim), and Time (March 2001).

Defining a dimension in your data warehouse creates a database dimension object, in addition to creating metadata. A dimension object contains the details of the parent-child relationship between columns in a dimension table; it does not contain data. The database dimension object is used by the Summary Advisor and query rewrite to optimize your data warehouse.

However, in the OLAP API, a dimension does contain data, such as the names of individual products, geographic areas, and time periods. The OLAP API uses the metadata, dimension objects, and dimension tables to construct its dimensions.

Hierarchies and levels

A hierarchy is a way to organize data according to levels. Dimensions are structured hierarchically so that data at different levels of aggregation can be manipulated together efficiently for analysis and display. Each dimension must have at least one level.

Each level represents a position in the hierarchy. Levels group the data for aggregation and are used internally for computation. Each level above the base (or lowest) level represents the aggregate total of the levels below it. For example, a Time dimension might have Day, Week, Quarter, and Year for the levels of a Time dimension hierarchy. If data for the Sales measure is stored in days, then the higher levels of the Time dimension allow the Sales data to be aggregated correctly into weeks, quarters, and years.

The members of a hierarchy at different levels have a one-to-many parent-child relationship. For example, "QTR1" and "QTR2" are the children of "YR2001," thus "YR2001" is the parent of "QTR1" and "QTR2".

If you define more than one hierarchy for a dimension, then the hierarchies must have the same base level. For example, you might define two hierarchies for your Time dimension, one for the calendar year and another for the fiscal year. Both hierarchies would use Day for the base level.

All levels of a dimension are stored in dimension tables. A dimension can have multiple hierarchies, but all of them must have the same base level. The values of that level are stored in the key used to join the dimension table to a fact table.

Attributes

Attributes provide supplementary information about the dimension members at a particular level. Attributes are often used for display, since the dimension members themselves may be meaningless, such as a value of "T296" for a time period.

For example, you might have columns for employee number (ENUM), last name (LAST_NAME), first name (FIRST_NAME), and telephone extension (TELNO). ENUM is the best choice for a level, since it is a key column and its values uniquely identify the employees. ENUM also has a NUMBER data type, which makes it more efficient than a text column for the creation of indexes. LAST_NAME, FIRST_NAME, and TELNO are attributes. Even though they are dimensioned by ENUM, they do not make suitable measures because they are descriptive text rather than business measurements.

Attributes are associated with a particular level of a dimension hierarchy and must be stored in the same table as that level.