July 14, 2014 at 1:07 am
Hi,
i need small help, i know the type of Dimension but can any one give us real time example for Dimension table
1) Conformed Dimension
2) Junk Dimension
3) Degenerated Dimension
4) Role Playing Dimension
Thanks In Advanced
Thanks,
Deepak
July 14, 2014 at 1:29 am
I would suggest you pick up a good book about dimensional modelling, such as The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling and especially Star Schema The Complete Reference.
The topic of dimensions is quite broad to explain in just a few sentences, but I'll give it a shot:
1) A conformed dimension is an enterprise-wide dimension that can be used in multiple fact tables. The dimension can be populated from various sources. This is the backbone of the dimensional model.
2) This is a combination of different flags and codes that actually do not belong together. Each column can stand on its own, but it is too much effort to create multiple dimensions with only one columns. For example, suppose your sales process tracks two flags for each order: MustBeGiftWrapped and IncludePersonalNote. Both flags can only take the values true or false. They do not belong in any other dimension (because they are specific to the sale). Are you going to create the dimensions GiftWrap and PersonalNotes? No, because they do not add value. So you create a junk dimension with all possible values (which are only 4 rows in this case) and you link that to the fact table.
3) A degenerate dimension is a bit like a junk dimension. It is a column that does not belong in any other dimension. However, it takes a unique value for each transaction, so you cannot create a junk dimension because that dimension would have as many rows as the fact table (a junk dimension typically has a limited amount of rows). So you just leave that column in the fact table for informational purposes. A good example is the OrderID. It does not belong in any dimension, it is not a measure but it just stays in the fact table for informational purposes. Chances are slim you will actually analyse on this.
4) Role playing dimensions are actually just one single dimension - most likely the time dimension - but it is linked multiple times to the fact table. For example, suppose you have multiple dates in your fact table: order date, ship date and due date. These are all linked to the same time dimension, but depending on which join you use, the time dimension gives information on order dates, or on ship dates or on due dates. So the time dimension can play multiple roles, hence the name.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 15, 2014 at 3:44 pm
Great answer!
July 15, 2014 at 8:47 pm
Thank you..................................
great answer
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply