Data Warehouse Dimensional Model Components

Data Warehouse Dimensional Model Components

After the data warehousing architect locates all data elements necessary to support the data warehouse, it is time to build a dimensional model. Dimensional model is equivalent of logical data design of Data Warehouse,

Dimensional Modeling Concept

Detailed data analysis of a single business process is performed to identify the fact table granularity, associated dimensions and attributes, and numeric facts. Dimensional models contain the same data content and relationships as models normalized into third normal form, but structured differently. To Improve understandability and query performance required by DW/BI.

Primary constructs of a dimensional model

– fact tables

– dimension tables

Every dimensional model is composed of one table with a multi-part key, called the fact table, and a set of smaller tables called dimension tables. Each dimension table has a single-part primary key that corresponds exactly to one of the components of the multi-part key in the fact table.

Fact tables

A fact table is a table that contains the measures of interest A fact table is a table that stores facts that measure the business, such as sales, cost of goods, or profit. Fact tables also contain foreign keys to the dimension tables. These foreign keys relate each row of data in the fact table to its corresponding dimensions and levels.

Dimension tables

A dimension table is a table that stores attributes that describe aspects of a dimension. For example, a time table stores the various aspects of time such as year, quarter, month, and day. A foreign key of a fact table references the primary key in a dimension table in a many-to-one relationship.

Star Schema
Star Schema is a relational database schema for representing multimensional data. It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema are slicing down, performance increase and easy understanding of data.

Steps in designing Star Schema

· Identify a business process for analysis(like sales).

· Identify measures or facts (dollar sold).

· Identify dimensions for facts(time dimension, location dimension, item dimension, branch dimension).

· List the columns that describe each dimension.(item name, brand, country).

· Determine the lowest level of summary in a fact table(sales dollar).

Star Schema