SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data warehouse - Dimensions


Data warehouse - Dimensions

Author
Message
Pueee
Pueee
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 485
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5541 Visits: 4639
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.
Pueee
Pueee
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 485
Thanks Paul for your reply. Great help!
Puja
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27991 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1669 Visits: 1944
:-D:-D

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
Pueee
Pueee
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 485
Thanks da-zero for clarification. will read that book!

@Raunak - Every little helps!
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5541 Visits: 4639
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.
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1669 Visits: 1944
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5541 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search