Current and Historic Dimensions (one table or two?)

View previous topic View next topic Go down

Current and Historic Dimensions (one table or two?)

Post  pcs on Tue Feb 08, 2011 11:34 am

Hello all,

I have a situation where I have modeled an Item Dimension as per business requirements to be SCD type 2.

It is a conformed dimension.

Our first business process dimensional model is customer grocery sales.

The grain is one fact line for each cash register receipt detail line.

Dimensions include date, store, customerCard, promotion and item

Facts include item count, item amount, discount count, discount amount.

90 percent of users/user queries will only be concerned with the current view of the item (what it is today – or the day the report is run)

10% of usage is anticipated to be concerned with what items looked like at the point in time of the measurements.

With this in mind, I have designed the table with a type 1 “current_attribute” value for each type 2 attribute. (for example - item category is tracked as type 2 so I have included both item_category and current_item_category)

I have some sophisticated users reviewing the dimensional model and they are pushing back for including 2 item dimension tables:
Item Dimension History – SCD type 2
Current Item Dimension – SCD type 1

This can be done, but should it be?
All it would mean is adding a new dimension and a key to the fact table. But it forces maintenance of 2 tables, instead of one. It feels like a compromise of best practices.

Perhaps I could create a Current Item Dimension View, off of the single dimension table, but I wanted to see if the community had any thoughts to address this situation.

I have been asked point blank what is my reluctance to add an additional table? My argument was about the additional maintenance required. I would appreciate any points of view on this.

Thank you,
Patrick

pcs

Posts : 20
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Current and Historic Dimensions (one table or two?)

Post  BoxesAndLines on Tue Feb 08, 2011 2:34 pm

There are 3 ways to handle this. The one your users have identified. The second is a self join filtering on the natural dimension key and current row indicator. The last is to add an additional FK to the fact table which provides a read peformance boost for option 2. I would push for option 2 or 3 before creating another dimension due to the redundant data.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Current and Historic Dimensions (one table or two?)

Post  pcs on Tue Feb 08, 2011 3:32 pm

Thank you for the response -
If I follow your thought correctly - option 2 would remove the redundant columns and is appealing from that point of view (do that and create a view for that join...) performance could be troublesome (maybe)
Just to make sure I understand - option 3 would require adding the natural key to the fact table? Is that correct or am I missing your point?

pcs

Posts : 20
Join date : 2009-02-03

View user profile

Back to top Go down

Dim View with a Join to the Current Dim row..

Post  owvi4 on Tue Feb 08, 2011 3:44 pm

When faced with this situation, I have most often done option 2 - built a VIEW on the Dim, that contains a column for CurrentItemID, which links to the Dim row that IsCurrent and matches the business key. Has worked well.
avatar
owvi4

Posts : 3
Join date : 2009-04-16
Location : Indianapolis, IN

View user profile

Back to top Go down

Re: Current and Historic Dimensions (one table or two?)

Post  ngalemmo on Tue Feb 08, 2011 8:00 pm

There are three ways to implement type 2 dimensions and designing the table with both type 1 and type 2 versions of the attributes is not one of them.

Options are:
1: Textbook type 2. Use a self join based on business key and current flag to get current attributes (can be done with a view).

2: Type 1 and type 2 versions of the table, two keys on the fact. Simple and fastest for queries. Don't worry about redundancy... the warehouse is already full of redundancy.

3: A type 2 with two keys, a type 1 key and type 2 key. Carry both on the fact. Type 1 key stays same for all versions of the same NK, filter on current flag to get current data. Better than 1 and easier to implement than 2.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Current and Historic Dimensions (one table or two?)

Post  BoxesAndLines on Tue Feb 08, 2011 11:56 pm

pcs wrote:Thank you for the response -
If I follow your thought correctly - option 2 would remove the redundant columns and is appealing from that point of view (do that and create a view for that join...) performance could be troublesome (maybe)
Just to make sure I understand - option 3 would require adding the natural key to the fact table? Is that correct or am I missing your point?
Not the natural key, another surrogate key that represents the natural key. Put that on the fact. It works the same way except you get the added benefit of abstracting the natural keys of the source system.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Current and Historic Dimensions (one table or two?)

Post  pcs on Wed Feb 09, 2011 10:56 am

Ok - this makes much better sense to me now. thank you Nick, Boxes and Lines, and owvi4. I appreciate your time, thought and feedback. This has helped a lot.

pcs

Posts : 20
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Current and Historic Dimensions (one table or two?)

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