Single Attribute Dimensions - why not instead write textual attribute into Fact table?

View previous topic View next topic Go down

Single Attribute Dimensions - why not instead write textual attribute into Fact table?

Post  SamiMajed on Thu Jun 06, 2013 4:26 pm

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)
------------------------------------------------
ApplicationKeyStatusDateKey
1Application Received20130601
1Application Reviewed20130604
1Active Account20130608
2Application Received20130609

Any help or experience is very much appreciated.
-Sami
avatar
SamiMajed

Posts : 4
Join date : 2013-05-23
Location : Santa Ana, CA, USA

View user profile http://www.linkedin.com/in/samimajed

Back to top Go down

Re: Single Attribute Dimensions - why not instead write textual attribute into Fact table?

Post  BoxesAndLines on Thu Jun 06, 2013 4:46 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Single Attribute Dimensions - why not instead write textual attribute into Fact table?

Post  ngalemmo on Thu Jun 06, 2013 7:34 pm

Besides, if you want to present it together, you can always create a view.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Single Attribute Dimensions - why not instead write textual attribute into Fact table?

Post  SamiMajed on Thu Jun 06, 2013 7:46 pm

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
avatar
SamiMajed

Posts : 4
Join date : 2013-05-23
Location : Santa Ana, CA, USA

View user profile http://www.linkedin.com/in/samimajed

Back to top Go down

Re: Single Attribute Dimensions - why not instead write textual attribute into Fact table?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum