Kimball Vs inmon

There are two major design methodologies followed in data warehousing Ralph Kimball and Bill Inmon.We will discuss about both of these in detail.

Bill Inmon Approach : According to Bill Inmon Data warehouse need to fulfill need of all category of users .In an organization there are different type of user like

· Marketing
· Supply Change Management
· Operations

Each department has its different way of interpreting data so Data warehouse should be able to answer each department queries. This can be achieved by designing tables in 3NF form. According to him data in Datawarehouse should be in 3NF and lowest granularity level. The data should be accessible at detailed atomic levels by drilling down or at summarized levels by drilling up.

He stressed that data should be organized into subject oriented, integrated, non volatile and time variant structures. According to him an organization have one Data warehouse and Data mart source there information from Data warehouse. Inmon Approach is also called Top Down approach .

In this methodology data is brought into staging area from OLTP system or ODS (Operational Data store) and then summarized and aggregated. After this process data mart will source their data from data warehouse and will apply new set of transformation and aggregation according to their need.

Key points to be noted about this approach

1.Data should be organized into subject oriented, integrated, non volatile and time variant structures
2.Data in 3rd Normalization form
3.Top to down approach

4.Data Mart source from Datawarehouse

Pro’s of Bill Inmon approach

1. Easy to maintain
2. Well integrated

Cons of Bill Inmon approach
Difficult to implement

Ralph Kimball Approach :

Kimbell views Data warehouse as combination of data marts connected to Data warehouse bus structure.Data marts are focused on delivering business objectives of different departments and Data warehouse bus consists of conformed dimension, measures defined for whole organization. User can query all data marts together using conformed dimensions.

In this approach the data warehouse is not a physical storage of the data as in the Inmon approach. It is “virtual.” It is a combination of data marts, each having a star schema design .
In this approach data is always stored in dimensional model.

Key points to be noted about this approach are

1. Data is always stored in the dimensional model.
2. Bottoms Approach
3. Data ware house is Virtual
4. Bottoms's up approach

Pro’s of Ralph Kimball approach
Fast to build

Cons of Ralph Kimball approach
Difficult to maintain because of redundancy of data across data marts

Conclusion : In reality there is no right or wrong between these two approaches. In reality actual methodology implemented is combination of both.

My Profile

Read More

Star Vs Snowflake Schema

In continuation to my last post we will continue with dimensional modeling in detail

In dimension modeling there are mainly two types of schemas

1.Star Schema
2.Snowflake Schema

Star Schema :
Star schema is simplest data warehouse schema .It is called star schema because ER diagram of this schema looks like star with points originating from center. Center of star schema consists of large fact table and points of star are dimensional table.

Star schema is identified by one or more large fact table at center that contain primary information in data warehouse and lot of small dimensional tables each of which contain information about particular attribute of fact tables.

Advantage of Star Schema :

1.Provide a direct mapping between the business entities and the schema design.
2.Provide highly optimized performance for star queries.
3.It is widely supported by a lot of business intelligence tools.

Disadvantage of Star Schema:
There are some requirement which can not be meet by star schema like relationship between customer and bank account can not represented purely as star schema as relationship between them is many to many.

Snow Flake Schema:
Snowflake is bit more complex than star schema. It is called snow flake schema because diagram of snowflake schema resembles snowflake.

In snowflake schema tables are normalized to remove redundancy. In snowflake dimension tables are broken into multiple dimension tables, for example product table is broken into tables product and sub product.
Snowflake schema is designed for flexible querying across more complex dimensions and relationship. It is suitable for many to many and one to many relationship between dimension levels.

Advantage of Snowflake Schema:
1.It provides greater flexibility in interrelationship between dimension levels and components.
2.No redundancy so it is easier to maintain.

Disadvantage of Snowflake Schema :

1.There are More complex queries and hence difficult to understand
2.More tables more joins so more query execution time.

My Profile

Read More

Dimensional Data Modeling

Dimensional Data Modeling :
It is a modeling technique used in data warehousing systems. It is different from ER modeling technique used in OLTP systems. In Dimensional modeling a model of tables is combined together with aim of optimized query performance in Decision Support systems in relational databases.

Before further going into details of dimensional data modeling we should be aware of key concepts in Dimensional Data Modeling

Dimension : It can be considered as category of information. For example geographic dimension. It provides a way to slice and dice the data in data warehouse.

Attribute : Attribute can be considered as characteristic of dimension. For example region is attribute of geographic dimension. Dimension attribute is column in dimension table.

Hierarchy : It represents relationships between attributes of dimension. It defines different level with in dimension, In case of geographic dimension it is like

Continent ->Country –> Region –> State ->City ->Street

Fact Table : It represents the measure of interest, for organization product revenue is measure of interest. The level of granularity is an important factor in designing fact tables. For example it can be revenue by continent or revenue by country. In this example fact table will have three columns Product, Geographical region, Revenue.

Lookup Table: The look up table represents a dimension and constitutes attributes for dimension. Lookup table for product will consist of all products available.

Dimensional Modelling Vs ER Modelling :
In Dimensional Modelling emphasis is on optimising decision support query performance,On other hand ER Model are focussed on

1. Removing redundancy in data model
2. Optimse OLTP Performance
3. Focus is on retrieval of single record

We will continue with dimensional data modelling in next post.

My Profile

Read More