After working with .NET and SharePoint technologies for long
time, I started to look in BI Analytics area. This article is to give an overview
of data warehouse, dimensional modeling, and fact constellation. First of all
we will see what is data warehouse and data mart.
Data warehouse and
data mart
Data mart is a subset of data ware house where it represents
a specific business scope. Multiple data marts together create a data
warehouse. In a typical organization, there are multiple departments focusing
different business objectives such as IT, administration, finance,
manufacturing, HR, etc. each department data can be considered as data marts
and altogether it will come under a data ware house.
Data marts often holds only one subject are as explained in
the above figure. Also it may holds more summarized data. Data mart focused on
a dimensional model using star schema. Whereas data warehouse holds multiple
subject areas in very detailed manner. It works to integrate all data sources. Data
warehouse does not necessarily use a dimensional model but feeds dimensional models.
Start schema and snow
flake
Start schema and snowflake are two different ways to
organize data marts and data warehouse using relational database. Both methods
use dimension tables to describe aggregate data in fact tables.
Star schema has single fact table connected to multiple
dimension tables and it visualize as star. In this model only one link
establishes the relationship between the fact table and any of the dimension
tables. Following figure explains the design of the star schema.
Snowflake schema can be seen as the extension of star
schema. Here large dimension tables are normalized into multiple sub dimension
tables. Every dimension table in this model is associated with sub dimension
table and multiple links. Following figure explains the design of the snowflake
schema.
No comments:
Post a Comment