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
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:
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
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
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
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
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
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
OLAP Related Interview Questions
|Informatica Interview Questions||Data Warehouse ETL Toolkit Interview Questions|
|PL/SQL Interview Questions||Data Warehousing Interview Questions|
|Testing Tools Interview Questions||SQL Database Interview Questions|
|MySQL Interview Questions||ERP Tools Interview Questions|
|Oracle 11g Interview Questions||Hyperion Financial Management Interview Questions|
|Hyperion Essbase 5 Interview Questions||Database Design Interview Questions|
|Data modeling Interview Questions||Oracle Hyperion Planning Interview Questions|
|Biztalk Esb Toolkit Interview Questions|
OLAP Related Practice Tests
|Informatica Practice Tests||PL/SQL Practice Tests|
|Data Warehousing Practice Tests||Testing Tools Practice Tests|
|SQL Database Practice Tests||MySQL Practice Tests|
|ERP Tools Practice Tests||Oracle 11g Practice Tests|
|Hyperion Financial Management Practice Tests||Hyperion Essbase 5 Practice Tests|
|Database Design Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.