Dimensional Modeling

n      As DW is mostly used for analysis, the queries are more to execute. So we usually follow de normalization technique to improve the query performance. In some cases we can even have DW in 3^rd normal form itself.

n      In dimensional modeling we create the data model either on Star Schema or Snowflake schema

n      Star Schema makes queries run faster as the number of tables is considerably less when compared to ER Model of OLTP application.

n      In star schema all the hierarchies defined per dimension will be stored in single table. So the data redundancy is high. In snow flake we can have one more table for the hierarchy. Thats the difference between the star schema and snow flake schema.

n      Based on RDBMS we have to choose what type of model suits better for the data warehouse.

n      Example: Teradata is an RDBMS which can give the results in reasonable time as its a parallel processing database engine in the market. So we can design the Enterprise data model in the 3rd normal form. But we cant have the same approach for SQL server or Oracle, we should think of denormalizing the data model.