Designing the Dimensional Model and Preparing the data for OLAP Page - 2

Page 2 of 3

Author : Exforsys Inc. Published on: 1st Mar 2005 | Last Updated on: 13th Apr 2011

Designing the Dimensional Model and Preparing the data for OLAP

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.

Ads

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..

Product

Product Key

Product name

Brand key

Product_Brand

Brand key

Brand name

Product_category key

Product_category

Product_category_key

Product category name

Snowflake schemas usually co-exist with the star schema. The figure below gives a combination of the star schema and the snowflake schema.

Optimizing star schema

Sometimes the star schema is optimized by combining all the dimension tables into a single table. The resultant table is known as a Cartesian product. The optimized star table consists of the fact table and the combined dimension tables. The advantage of this combination is that the queries do not have to perform join operations. The only join operation would be between the fact table and the combined dimension table.

Scalability of the Dimension model

Data warehouse structures must be designed to accommodate current and future business needs of the enterprise. It must be scalable enough to accommodate additional demands with minimum of change to the fundamental design of the warehouse. Dimensional modeling has the advantage of being scaleable. It can be expanded by addition of records to dimension tables. New dimensions and schemas can be added with ease. Existing dimensions can be used with the new dimensions without modification to maintain conformity throughout the entire warehouse. If granularity is to be added, dimension tables can be partitioned to granular levels for drilling down operations on the data. OLAP cubes can be extended to accommodate new dimensions by extending their schemas and reprocessing or creating new virtual cubes that contain new dimensions. Existing cubes can be incorporated without modification.

1 2 3

Read Next: Understanding OLAP Models

This tutorial is part of a Analysis Services Training tutorial series. Read it from the beginning and learn yourself.

Analysis Services Training