Tutorials MSAS Tutorial 5 : Designing the Dimensional Model and Preparing the data for OLAP Tutorial 5 : Designing the Dimensional Model and Preparing the data for OLAP - Page 2 Tutorial 5 : Designing the Dimensional Model and Preparing the data for OLAP - Page 3
Design of the Dimensional Model schemas
The primary characteristic of a dimensional model is set of dimension tables connected to a fact table through the foreign keys in the fact table and the primary keys in the dimension tables. The fact table and the dimension tables in a database can be linked directly or indirectly. When multiple dimension tables are linked to a fact table a Star Schema is formed. When some tables are linked directly to a fact table while others are linked indirectly by linking to the linked dimension table a Snowflake Schema is realized.
The Star Schema
The star schema is created when all the dimension tables directly link to the fact table. This is graphically represented as under.
The star schema dimensional model.
Since the graphical representation resembles a star it is called a star schema. It must be noted that the foreign keys in the fact table link to the primary key of the dimension table. This sample provides the star schema for a sales_ fact for the year 1998. The dimensions created are Store, Customer, Product_class and time_by_day. The Product table links to the product_class table through the primary key and indirectly to the fact table. The fact table contains foreign keys that link to the dimension tables.
The Snowflake Schema
The snowflake schema is a schema in which the fact table is indirectly linked to a number of dimension tables. The dimension tables are normalized to remove redundant data and partitioned into a number of dimension tables for ease of maintenance. An example of the snowflake schema is the splitting of the Product dimension into the product_category dimension and product_manufacturer dimension..
|
|
|
