Showing posts with label Datawarehousing. Show all posts
Showing posts with label Datawarehousing. Show all posts

Saturday, 7 February 2009

2/07/2009 03:10:00 am

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



 
Inmon
Kimball

Source Required
ü   
ü   

Staging
ü   
ü   

ETL
ü   
ü   

Data Marts
ü   
ü   

Business Requirements
ü   
ü   

Time attribute of data
ü   
ü   

Enterprise DW
ü   
                      X

Dimensional Tools
                      X
ü   

Relational Tool
ü   
X

Process Oriented
                      X
ü   

Normalised data Model
ü   
X

Complex to Design
ü   
X

Continous and Discrete Time Frame
ü   
X

Slowly changing Time Frame
                      X
ü   


Inmon vs. Kimball: Same or different?

  

"You can catch all the minnows in the ocean and stack them together and they still do not make a whale." ~Inmon

“The data warehouse is nothing more than the union of all the data marts" ~Kimball


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

My Profile
3
By
at 3 comments:
Labels:

Monday, 12 January 2009

1/12/2009 02:48:00 pm

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.





Below table summarizes all differences


 
Star Schema
Snowflake Schema

Ease of maintenance / change
Has redundant data and hence difficult to maintain/change
No redundancy, so snowflake schemas are easier to maintain and change.

Ease of Use
Less query complexity and easy to understand
More complex queries and hence less easy to understand

Query Performance
Less number of foreign keys and hence shorter query execution time (faster)
More foreign keys and hence longer query execution time (slower)

Type of Datawarehouse
Good for DataMart’s with simple relationships (1:1 or 1:many)
Good to use for datawarehouse with complex relationships (many:many)

Joins
Less Joins
Higher number of Joins

Dimension table
A star schema contains only single dimension table for each dimension.
A snowflake schema may have more than one dimension table for each dimension.

When to use
When dimension table contains less number of rows, we can choose Star schema.
When dimension table is relatively big in size, snow flaking is better as it reduces space.

Normalization/ De-Normalization
Both Dimension and Fact Tables are in De-Normalized form
Dimension Tables are in Normalized form but Fact Table is in De-Normalized form

Data model
Top down approach
Bottom up approach



My Profile
1
By
at 1 comment:
Labels:

Saturday, 10 January 2009

Dimensional Data Modeling
1/10/2009 06:48:00 am

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
3
By
at 3 comments:
Labels: