We had our last post on Fact Tables in Data Warehousing. This post is on Dimension Table Types. Along with Fact Tables there are many types of Dimension Tables which are used in Data Warehousing.
Dimension Tables are those tables which contain attributes that helps in describing the facts of the fact table.
The following are types of Dimension Tables:
* Slowly Changing Dimensions
* Junk Dimensions
* Confirmed Dimensions
* Degenerated Dimensions
Various data in the dimensions undergo changes which is needed to be captured fr analysis. In short, what we need is to maintain a track of changes in the data elements so that we can analyse the changes and come to a conclusion.
SCD is one of the common problems for Data Warehousing.
Example:-
We have so many McDonalds in Mumbai, all have there different Store_ID, Region_ID. And if one of the McDonald changes its location from A to B then that is to be tracked and the change has to be preserved, to maintain a history of that particular McDonald (store_ID) region change.
Its a dimension table which is derived from a Fact table and doesn't have its own dimension table.
Degenerated Dimensions are often used when a fact tables's grainularity represents transactional level data and it wished to have one of the system, generated flags/identifier, example:- Invoice number, order number etc.
Confirmed Dimension is that dimension which is fixed and reusable, therefore it is also called a fixed dimension. Unlike SCD it is a dimension that doesn't gets affected w.r.t. to time.
Example:-, When the name of Girl changes after marriage. The name won't change from Time to Time, once its changed its fixed. This change is permanent.
A dimension where one can store random flags, data, attributes etc that are not related to dimensions and which provide a simple way to find those unrelated attributes.
Example:-
Martial Status: (Yes or No)
Gender : (M or F) etc.
Dimension Tables are those tables which contain attributes that helps in describing the facts of the fact table.
The following are types of Dimension Tables:
* Slowly Changing Dimensions
* Junk Dimensions
* Confirmed Dimensions
* Degenerated Dimensions
Slowly Changing Dimensions (SCD)
Various data in the dimensions undergo changes which is needed to be captured fr analysis. In short, what we need is to maintain a track of changes in the data elements so that we can analyse the changes and come to a conclusion.
SCD is one of the common problems for Data Warehousing.
Example:-
We have so many McDonalds in Mumbai, all have there different Store_ID, Region_ID. And if one of the McDonald changes its location from A to B then that is to be tracked and the change has to be preserved, to maintain a history of that particular McDonald (store_ID) region change.
Degenerated Dimensions
Its a dimension table which is derived from a Fact table and doesn't have its own dimension table.
Degenerated Dimensions are often used when a fact tables's grainularity represents transactional level data and it wished to have one of the system, generated flags/identifier, example:- Invoice number, order number etc.
Confirmed Dimensions
Confirmed Dimension is that dimension which is fixed and reusable, therefore it is also called a fixed dimension. Unlike SCD it is a dimension that doesn't gets affected w.r.t. to time.
Example:-, When the name of Girl changes after marriage. The name won't change from Time to Time, once its changed its fixed. This change is permanent.
Junk Dimensions
A dimension where one can store random flags, data, attributes etc that are not related to dimensions and which provide a simple way to find those unrelated attributes.
Example:-
Martial Status: (Yes or No)
Gender : (M or F) etc.
Excellent information with unique content and it is very useful to know about the information based on blogs.
ReplyDeleteinformatica online training
data ware house nic eposts..
ReplyDeleteinformatica training
Thanks for sharing this webpage.It is really useful. Informatica course in Chennai is really helpful to learn about how to work with the ETL tool. It can work with different data sets, multiple systems and applications, varying standards, etc.
ReplyDelete