Click here to monitor SSC
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 (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 468
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3095 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 (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 468
Thanks Paul for your reply. Great help!
Puja
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16522 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Raunak Jhawar
Raunak Jhawar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 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 (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

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

@Raunak - Every little helps!
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3095 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3095 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