Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data warehouse - Dimensions Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 4:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:07 AM
Points: 294, Visits: 347
Hi

I have a question on Data warehousing - Dimension Types. I am interested to know what are different types of Dimesnions that can be used in Data warehousing.

I am aware of three different types of Dimensions -

1. Confirmed Dimension
2. Junk Dimension
3. Degenerated Dimension

Is this list correct or are there any more types? Please advice.

Thanks very much,
Puja
Post #970244
Posted Tuesday, August 17, 2010 9:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
PrincessPuja (8/17/2010)
I have a question on Data warehousing - Dimension Types. I am interested to know what are different types of Dimesnions that can be used in Data warehousing.

I am aware of three different types of Dimensions -

1. Confirmed Dimension
2. Junk Dimension
3. Degenerated Dimension

Is this list correct or are there any more types? Please advice.

Depending on the author you may get anything in between nine to a dozen "types" of dimensions - not to mention sub-types like Slowly Changing Dimensions of the Type 0, 1, 2 and 3.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #970542
Posted Thursday, August 19, 2010 6:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:07 AM
Points: 294, Visits: 347
Thanks Paul for your reply. Great help!
Puja
Post #971807
Posted Friday, August 20, 2010 6:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:15 PM
Points: 13,731, Visits: 10,692
You'll find a great explanation of the different dimension types in Kimball's book "The Data Warehouse Toolkit" (2nd edition).

Actually, to be precise, a dimension is not of SCD type 1,2,3 or hybrid, it's attributes are. A dimension can have a combination of different types of SCD. For example, the customer gender and name are SCD Type 1, but the address is type 2.

More dimensions, next to those you already mentioned:
* mini dimension
* monster dimension
(* a special table to link different dimensions with many-to-many relationships: the bridge table) --> not really a dimension




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #972477
Posted Friday, August 20, 2010 9:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839


You are scaring the girl!!!


Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #972609
Posted Saturday, August 21, 2010 9:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:07 AM
Points: 294, Visits: 347
Thanks da-zero for clarification. will read that book!

@Raunak - Every little helps!
Post #972951
Posted Sunday, August 22, 2010 6:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
da-zero (8/20/2010)
Actually, to be precise, a dimension is not of SCD type 1,2,3 or hybrid, it's attributes are. A dimension can have a combination of different types of SCD. For example, the customer gender and name are SCD Type 1, but the address is type 2.


Actually, to be precise, SCD types refer to the chosen method to track changes therefore they are related to the object and not to the attributes.

A hybrid SCD type 6 is nothing but the single implementation of Types 1,2 and 3 (1+2+3=6) for a specific object a.k.a. table.

Hope this clarifies.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #973041
Posted Sunday, August 22, 2010 9:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
Type 6 SCD transformation are very very specific.
Seldom the need arises to maintain a history table.

Puja...you know my comment was on a lighter note...we allare here to help each other....

I would suggest that you familiarize yourself with...
What is fact, fact table, dimension, dimension table and most important what goes into fact/dimension table.
Once you set the right momentum...you'll find your learning curve smooth enough to model the same into advanced analytics.


Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #973065
Posted Sunday, August 22, 2010 4:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
Raunak Jhawar (8/22/2010)
Type 6 SCD transformation are very very specific.
Seldom the need arises to maintain a history table.


I do not like history tables either - talking here about a data warehouse environment but, History Tables refer to a SCD of the Type 4.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #973119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse