Dimensional modeling - Star schema vs Fact constellation

-

Dimensional model of Data warehousing

Ralph Kimball introduced the dimensional modeling in 1996 with his book, The Data Warehouse Toolkit. Dimensional modeling is implemented by de-normalizing the database for the reporting purposes, because the normalized databases are too hard to query and extract data for business reports.

There are two main approaches for implementing dimensional data warehousing models:
  •          Star schema
  •          Snowflake schema
They describe how the dimensions relate to the fact tables. 

Facts and fact tables hold the information that the business is concerned about,  that measure the performance of the organization. Dimension tables contain descriptive attributes which gives the ability to the qualify the measures (facts). These two tables are related to each other and are connected by a key.
Granularity is the measure of how the dimensions and facts relate to each other. Facts with different granularity should be in different tables.

Star Schema

In star schema, one fact table is surrounded by many dimension tables. Every dimension is tied directly to the fact table and typically this is a de-normalized solution.

               




Snowflake Schema

     This is more normalized solution compared to the star schema. It typically contains multiple tables per dimension. It can also be called as a star schema with dimensions connected to some more dimensions.
                 



Fact Constellation Schema

This schema is viewed as collection of stars hence called galaxy schema or fact constellation. It is possible to construct fact constellation schema from a star schema by splitting the original star schema into more star schemes. The fact constellation approach contains multiple fact tables that share many dimension tables. It's typically a combination of star and snowflake schemes.






Comments

Post a Comment

Popular posts from this blog

Using Entity Model for connecting the database

-
When using Entity Model to connect the database, database is connected to the orject as a data model and instances of databse tables are used to perform insert,update,delete and search operations. This is a simple example of a database file connected using Entity Model namespace cecbClasses.Data_Adapters {     public class Func_FillMatrix     {         DSSEntities cecbContext; // This is the databse context         LeopoldMatrixValues matrixVal; // This is an variable of a database table         public Func_FillMatrix()             {                 cecbContext = new DSSEntities();             } // Inserting values to a database table public bool InsertValues(String impR, String actR, String proR, String impV, String magV)             {                 bool IsInserted = false;                 try                 {                     matrixVal = new LeopoldMatrixValues(); // Assigning the variable with a databse object                     matrixVal.impt_referenc
Read more

Populating data to the Date Dimension table

-
Date dimension plays an important role in data warehouse designing. It allows you to examine behavior and trends in the data over a period of time. The columns for the DimDate table (date dimension table) should be chosen according to the requirement of the data analysis.  When entering data to the DimDate table, extracting dates from the source database is not a good method because there can be missing days in between as the transactions may not have occurred on each day of the year.  Therefore, the best way is to populate data to the DimDate table separately, as the date is an universal attribute. There are various methods of populating data to the table;     Using an Excel file In this method, an excel spreadsheet which contains the date values is used as the data source and it’s extracted to the data warehouse using SSIS.     Using a SQL script In this method, a SQL script or a stored procedure is used to generate dates between two days and to populate data t
Read more