Single Attribute Dimensions - why not instead write textual attribute into Fact table?
3 posters
Page 1 of 1
Single Attribute Dimensions - why not instead write textual attribute into Fact table?
The dimension is DimStatus and there are about 20 rows. e.g.: "Application Received", "Application Reviewed", ..."Active Account"
One way to design this is to create:
DimStatus (table)
---------------------------------
StatusKey (surrogate) int PK Identity(1,1)
Status varchar(50)
...and a Fact:
FactApplicationHistory (table) [grain statement: each 1 row represents an application with a particular status]
---------------------------------
ApplicationKey
StatusKey
DateKey
I am running into the argument why we can't just get rid of DimStatus and write the Status ("Application Received") directly into FactApplicationHistory. This would make it easier on users to slice/dice (using PowerPivot).
I understand the principle that Fact tables should only have [keys] and [additive values] but can't seem to find a solid downside of just writing the text into the fact.
Besides the principle or best practice... are there any experiences of why we shouldn't just write this single attribute into the fact?
e.g.:
FactApplicationHistory (sample population)
------------------------------------------------
Any help or experience is very much appreciated.
-Sami
One way to design this is to create:
DimStatus (table)
---------------------------------
StatusKey (surrogate) int PK Identity(1,1)
Status varchar(50)
...and a Fact:
FactApplicationHistory (table) [grain statement: each 1 row represents an application with a particular status]
---------------------------------
ApplicationKey
StatusKey
DateKey
I am running into the argument why we can't just get rid of DimStatus and write the Status ("Application Received") directly into FactApplicationHistory. This would make it easier on users to slice/dice (using PowerPivot).
I understand the principle that Fact tables should only have [keys] and [additive values] but can't seem to find a solid downside of just writing the text into the fact.
Besides the principle or best practice... are there any experiences of why we shouldn't just write this single attribute into the fact?
e.g.:
FactApplicationHistory (sample population)
------------------------------------------------
ApplicationKey | Status | DateKey |
1 | Application Received | 20130601 |
1 | Application Reviewed | 20130604 |
1 | Active Account | 20130608 |
2 | Application Received | 20130609 |
Any help or experience is very much appreciated.
-Sami
Re: Single Attribute Dimensions - why not instead write textual attribute into Fact table?
Sure it's easier.
Imagine that the description for one of the values change. In a type 2 dimension, you can gracefully update the appropriate row and move on with consistent reporting. In your single fact table example, I need to update all my queries.
Also imagine a hierarchy to your statuses. The first 3 can be termed as "Application Evaluation", the second two statuses, "Underwriting", and the final status, "Active Accounts". These hierarchies are wonderful for building reports and gaining insights to business information. In a dimension, you simply add the column and now the hierarchy is available for all metrics. If your column is in the fact table, you have to add the column, and then update every historical row to gain the same reporting. That sounds harder to me.
Imagine that the description for one of the values change. In a type 2 dimension, you can gracefully update the appropriate row and move on with consistent reporting. In your single fact table example, I need to update all my queries.
Also imagine a hierarchy to your statuses. The first 3 can be termed as "Application Evaluation", the second two statuses, "Underwriting", and the final status, "Active Accounts". These hierarchies are wonderful for building reports and gaining insights to business information. In a dimension, you simply add the column and now the hierarchy is available for all metrics. If your column is in the fact table, you have to add the column, and then update every historical row to gain the same reporting. That sounds harder to me.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Single Attribute Dimensions - why not instead write textual attribute into Fact table?
Besides, if you want to present it together, you can always create a view.
Re: Single Attribute Dimensions - why not instead write textual attribute into Fact table?
Thank you for your reply. Those are great reasons!
After trying it the wrong way (text in fact), I found another issue and I wanted to share this:
Your fact table only contains the values actually used whereas your dimension (lookup) would have all possible values. This leads to confusion on the business user side because the value would be missing.
For instance, if your facts don't actually have a row for "Application Evaluation" (say you just added it or it's never used), then it won't show up in PowerPivot's slicer.
-Thank you so much for your reply
Sami
After trying it the wrong way (text in fact), I found another issue and I wanted to share this:
Your fact table only contains the values actually used whereas your dimension (lookup) would have all possible values. This leads to confusion on the business user side because the value would be missing.
For instance, if your facts don't actually have a row for "Application Evaluation" (say you just added it or it's never used), then it won't show up in PowerPivot's slicer.
-Thank you so much for your reply
Sami
Similar topics
» [solved]An explanation of sentence: Any descriptive attribute that takes on a single value in the presence of a fact table
» Dimension Attribute vs Fact Table Key
» Dimension Attribute or Fact Attribute
» data as an attribute on a dimension or a key on the fact table
» dimension attribute denormalisation in fact table
» Dimension Attribute vs Fact Table Key
» Dimension Attribute or Fact Attribute
» data as an attribute on a dimension or a key on the fact table
» dimension attribute denormalisation in fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum