Data Warehousing Community Forum
November 21, 2018, 02:17:32 am

Pages: [1]   Go Down
  Print  
Author Topic: Dimensional Attributes in a Fact Table?  (Read 1343 times)
boats2468
DW Fresher
*

Reputation: +0/-0
Offline Offline

Posts: 1


View Profile
« on: April 19, 2017, 12:17:27 am »

I've been charged with developing my first Star Schema.  I'm trying to follow the Kimball methodology.
I need your help refuting a design decision being proposed by another architect.

In short, there are a few columns of one of my dimension tables that the business wants a history on. For this reason, I modeled the dimension including "IsCurrentYN", "RecordEffectiveDate" and "RecordExpireDate"  columns.

This other architect is proposing that we move the columns that the business wants a history on into the Fact table.
His reasoning is that we are currently doing all of our ETL work using stored procedures rather than more advanced ETL tools which can easily support Type 2 slowing changing dimensions.  He feels it will be easier to store these fields in the fact table where there current value is stored on a daily basis.

In my mind, I'd rather us invest the time to store the data in the dimension, if for no other reason then to conform the dimension. Otherwise, we'll have to store those data attributes in every future fact table we build.

What are your thoughts?   Can you refer to me to any articles that I can share with the team to support the best way to model this?

Thank you for your time and consideration.
Logged
advertisement
SuGoSu
DW Fresher
*

Reputation: +0/-0
Offline Offline

Posts: 5


View Profile
« Reply #1 on: November 05, 2018, 05:38:41 pm »

Fact tables are event based. They don't represent slowly changing dimensions. If there is no event between effective dates period, then you might miss these Change Data. SCDs help you to keep them even there is no event occur.

For Kimball, there should be no attributes on your fact table, (except ETL attributes like creation date, update date).

If you do not want to use SCD, you might try Type7 SCD which is actually a 3NF dimension for your fact table.

Good luck.


Logged
Pages: [1]   Go Up
  Print  
Jump to: