To Degenerate or not to degenerate... that is the question.
2 posters
Page 1 of 1
What's the best thing to happen to Apple since the iPhone?
To Degenerate or not to degenerate... that is the question.
First, I'd like to thank everyone who regularly answers these forum questions. This group has helped me many times and I really appreciate any insight or advice you can offer for my question.
I could use a little guidance on deciding whether or not to use a “true” degenerate dimension (a dimension without its own table) or to populate a physical table and use it as a degenerate dimension “in spirit.”
The reason this decision is not obvious is for two reasons:
1. The degenerate dimension would be based on a compound key consisting of three business keys two of which are character data types
2. The degenerate dimension will participate in a many-to-many relationship in a Microsoft SQL Server Analysis Services OLAP cube.
To create an Analysis Services many-to-many relationship, I need four objects: the fact table, the event dimension (this is my degenerate candidate), and bridge table, and the many-to-many dimension itself. Right now I have the event dimension (my degenerate candidate) as a physical table with a surrogate integer key and the three business keys. I have a single join from the fact table to the event dimension, a single join from the event dimension to the bridge table, and a single join from the bridge table to the many-to-many dimension.
If I change my event dimension to a true degenerate dimension I would have to join my fact table to the degenerate event dimension using three joins (two of which are on character fields), join the event dimension using three joins to the bridge table, and finally a single join to the many-to-many. I believe query performance will suffer with this approach.
One alternative I thought about was to create a surrogate key (identity) field on my fact table and use that to create a fact relationship to a true degenerate event dimension.
The row count for the event dimension is very close to the row count for the fact table.
What would you do? Have you ever faced a similar situation? Is it okay to make an exception to Kimball’s recommendation in this case? Is the idea of adding a surrogate key to the fact table a viable option or should I just join on the composite business keys (twice?)
Thank you very much!
I could use a little guidance on deciding whether or not to use a “true” degenerate dimension (a dimension without its own table) or to populate a physical table and use it as a degenerate dimension “in spirit.”
The reason this decision is not obvious is for two reasons:
1. The degenerate dimension would be based on a compound key consisting of three business keys two of which are character data types
2. The degenerate dimension will participate in a many-to-many relationship in a Microsoft SQL Server Analysis Services OLAP cube.
To create an Analysis Services many-to-many relationship, I need four objects: the fact table, the event dimension (this is my degenerate candidate), and bridge table, and the many-to-many dimension itself. Right now I have the event dimension (my degenerate candidate) as a physical table with a surrogate integer key and the three business keys. I have a single join from the fact table to the event dimension, a single join from the event dimension to the bridge table, and a single join from the bridge table to the many-to-many dimension.
If I change my event dimension to a true degenerate dimension I would have to join my fact table to the degenerate event dimension using three joins (two of which are on character fields), join the event dimension using three joins to the bridge table, and finally a single join to the many-to-many. I believe query performance will suffer with this approach.
One alternative I thought about was to create a surrogate key (identity) field on my fact table and use that to create a fact relationship to a true degenerate event dimension.
The row count for the event dimension is very close to the row count for the fact table.
What would you do? Have you ever faced a similar situation? Is it okay to make an exception to Kimball’s recommendation in this case? Is the idea of adding a surrogate key to the fact table a viable option or should I just join on the composite business keys (twice?)
Thank you very much!
g8rpal- Posts : 10
Join date : 2010-12-03
Location : Jacksonville, FL
Re: To Degenerate or not to degenerate... that is the question.
Firstly I would like to clarify the concept of degenerate dimension (DD). If you call something as DD, then you don't have a dimension table for it. Anything associated with event is a dimension called something else on its own, like event status dim or event type dim. So event dimension does not exist in normal dimensional sense, and it is represented by the event number in the event fact table.
If a DD is involved in a bridge relationship, you would have DD directly in the bridge. It does not mean you have referential relationship between fact tables as DD is just a special dimension of the fact table, not a PK of the fact table, although it may be a candidate key if the fact grain is one DD per row.
Molders can easily get carried away by trying to create a dimension table for a degenerate dimension because other fact or bridge tables are referencing it. If you think of a DD also as an SK, you would freely use it in any other fact and bridge tables.
If a DD is involved in a bridge relationship, you would have DD directly in the bridge. It does not mean you have referential relationship between fact tables as DD is just a special dimension of the fact table, not a PK of the fact table, although it may be a candidate key if the fact grain is one DD per row.
Molders can easily get carried away by trying to create a dimension table for a degenerate dimension because other fact or bridge tables are referencing it. If you think of a DD also as an SK, you would freely use it in any other fact and bridge tables.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Degenerate Dimension Question
» Degenerate dimension question.
» Question on breaking out Degenerate Dimension to separate dimension
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Degenerate Dimensions
» Degenerate dimension question.
» Question on breaking out Degenerate Dimension to separate dimension
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Degenerate Dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum