dimension best practice!

View previous topic View next topic Go down

dimension best practice!

Post  Mir on Mon Sep 19, 2011 6:55 am

Hi,
I have found the following statements in a best practice paper from a consulting firm:
The dimension tables will be split in two physical tables one contain the current state of the dimension and a second table containing all the history and current state (type 2). The history dimension is a child of the current state dimension. Only the current state dimension will join to the facts.
This gives the opportunity to reload the history dimension without any changes to the fact tables. To use the history dimension you should always use a date criteria for filtering.

It kind of defeats the purpose of Dimensional modeling by making it more complex for the user, present additional joins when querying the tables by making it a snowflake schema (disregarding the complexity added to the ETL).

Is this something that I should consider to use?
Thanks!


Mir

Posts : 1
Join date : 2011-09-19

View user profile

Back to top Go down

Re: dimension best practice!

Post  BoxesAndLines on Mon Sep 19, 2011 9:14 am

I would just use partitioning and let the database manage the tables.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: dimension best practice!

Post  Jeff Smith on Mon Sep 19, 2011 10:37 am

Fire the consulting group.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: dimension best practice!

Post  buckleyc on Mon Sep 19, 2011 2:44 pm

The design you are mentioning seems to be a misunderstanding of what I've heard referred to as Type 4 SCD, where you have your current record in one table and your history in a second table. If what the consultant company is trying to achieve is a simpler way to join to the facts, they could just as easily create a view off of an SCD2 design, where the view filters for RowEndDate IS NULL. In SQL Server you can index the view if performance is an issue.


buckleyc

Posts : 7
Join date : 2011-09-19

View user profile

Back to top Go down

Re: dimension best practice!

Post  ngalemmo on Mon Sep 19, 2011 4:09 pm

I have often, in the past, recommended maintaining both a type 1 and type 2 version of a dimension, but only if the requirements are to maintain history but most of the time they user want's current data. Both the type 1 and type 2 keys are maintain on fact tables. Having two tables allows either current or historical querys to perform optimally.

An alternative, also discussed in this forum, is to maintain a type 1 alternate key in a single a type 2 dimension table. Again, both keys are maintained in the fact table. If you want current data you join on the the type 1 key and filter for the current version of the dimension row. This is ok, but current queries are not optimal.

What is being proposed is interesting. It basically uses the premise that nobody really wants history but if they do, queries will be sub-optimal. Which is ok, but why keep history if nobody wants it?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: dimension best practice!

Post  VHF on Wed Sep 21, 2011 2:59 pm

The "history dimension table" model is useful for reporting using attributes that were in effect on a given date. I use a dimension history table to track customer sales territory assignments. That way users can run reports over time using the territory assignments that were in effect on any given date.

This is different from SCD2, which is great for for reporting on things as they actually happened, but can't handle "as-of date" restatement reporting as well.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: dimension best practice!

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