The Global Star Schema - OLAP

The Global schema consists of two fact tables and four dimension tables. The dimension tables use numeric surrogate keys for each level column to assure that dimension members are unique across levels. For example, a geography dimension can easily have identical values at different levels, for example, New York at the City level and New York at the state level. In an analytic workspace, dimension members at all levels are fetched into a single dimension, and duplicate values overwrite each other unless additional steps are taken to assure uniqueness.

Figure shows the relationships among the tables. In addition, the Global schema contains update fact tables, which are omitted from the diagram but occupy the same logical position as the history fact tables.

Global Schema Diagram

Global Schema Diagram

Dimension Table: TIME_DIM

The TIME_DIM table defines a time dimension with three levels. Each level is supported by four columns: a numeric surrogate key, a textual description, an end date (last day in time period), and a time span (number of days in time period). This is the most basic information required to define a time dimension. The surrogate keys are artificial values with no meaning outside the context of the table. They assure that the same values are not repeated at different levels, and they provide the fastest processing speeds both in the relational tables and in the analytic workspace. The descriptive columns provide meaning to these numeric identifiers. The end date and time span columns support time-series analysis, such as:

  • Change from a prior period
  • Change from a year ago
  • Year-to-date
  • Range of time

There are seven years defined, from 1998 to 2004, with data provided for 1998 to early 2003. The last year is available for forecasting. In the standard hierarchy, the rollup sequence from the base to the top level is:

MONTH -> QUARTER -> YEAR

TIME_DIM Column Descriptions

TIME_DIM Column Descriptions

TIME_DIM Column Descriptions

Dimension Table: CUSTOMER_DIM

The CUSTOMER_DIM table defines seven levels that will be used to define two hierarchies. Each level has a numeric surrogate key and a textual description, which is the most basic information to define a "normal" dimension, that is, a dimension that is not time. SHIP_TO is the primary key, and its values will become the base-level members for both Customer hierarchies.

In the Market hierarchy, the rollup sequence from the base to the top level is:

SHIP_TO -> ACCOUNT -> MARKET_SEGMENT -> TOTAL_MARKET

In the Customer hierarchy, the rollup sequence is:

SHIP_TO -> WAREHOUSE -> REGION-> ALL_CUSTOMERS

CUSTOMER_DIM Column Descriptions

CUSTOMER_DIM Column Descriptions

CUSTOMER_DIM Column Descriptions

Dimension Table: PRODUCT_DIM

The PRODUCT_DIM table defines a product dimension with four levels. Each level has a numeric surrogate key and descriptive text. ITEM_ID is the primary key, so its values will become the base-level members of the Product dimension. In the Product hierarchy, the rollup sequence from the base level to the top level is:

ITEM -> FAMILY -> CLASS -> TOTAL_PRODUCT

PRODUCT_DIM Column Descriptions

PRODUCT_DIM Column Descriptions

PRODUCT_DIM Column Descriptions

Dimension Table: CHANNEL_DIM

The CHANNEL_DIM table contains four columns. CHANNEL_ID is the primary key, and its values will become the base-level members of the Channel dimension. ALL_CHANNELS_ID defines a single value that represents all of the channels. In the OLAP Catalog, these two columns will define the two levels of a single Channel hierarchy. The rollup sequence from the base level to the top level is simply:

CHANNEL -> ALL_CHANNELS

The remaining columns, CHANNEL_DSC and ALL_CHANNELS_DSC, provide textual descriptions that give the surrogate keys meaning.

CHANNEL_DIM Column Descriptions

CHANNEL_DIM Column Descriptions

Fact Tables: UNITS_HISTORY_FACT and _UPDATE_FACT

The UNITS_HISTORY_FACT and UNITS_UPDATE_FACT tables contain four foreign key columns, which together comprise a multi-column primary key. The foreign keys are related to the primary keys of the four dimension tables. In UNITS_HISTORY_FACT, every foreign key value for Product, Customer, and Channel is used at least once, and 65 time periods are used. The table contains 169,487 rows of a possible 513,864 unique key combinations.

UNITS_UPDATE_FACT adds data for month 91 (Jun-03). Every foreign key value for Product, Customer, and Channel is used at least once. The table contains 3,459 rows of a possible unique 6,588 key combinations.

UNITS_HISTORY_FACT and UNITS_UPDATE_FACT Column Descriptions

UNITS_HISTORY_FACT and UNITS_UPDATE_FACT Column Descriptions

Fact Tables: PRICE_AND_COST_HISTORY_FACT and _UPDATE_FACT

The PRICE_AND_COST_HISTORY_FACT and PRICE_AND_COST_UPDATE_FACT tables contain two foreign key columns, which together comprise a multi-column primary key, and two fact columns.

In PRICE_AND_COST_HISTORY_FACT, data is provided for all products for 65 months.

PRICE_AND_COST_UPDATE_FACT adds data for month 91 (Jun-03) for all products.

PRICE_AND_COST_HISTORY_FACT and PRICE_AND_COST_UPDATE_FACT Column Descriptions

PRICE_AND_COST_HISTORY_FACT and PRICE_AND_COST_UPDATE_FACT Column Descriptions


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics