Best approach

Question

Best approach

mpradeesh

SSC Enthusiast

Points: 188
More actions
October 14, 2010 at 2:44 pm

#241722

Here is my situation for Designing a Data Warehouse Star Schema
For each transactional (Fact) data the , account(dimension , but a fact table as it may change the attributes ) has different dimensions which can be changed during the course of time (eg : Customer is moved from one location to another).
Q1. Whats the best method to keep track of Account History ( changes from one location to another or from one status to another and so on )
Slowly Changing Dimenions with either Type 2 or Type 6 is an option here, but what is the impliciation of creating a cube with a Main/history table or all history in one table !!
Q2. How feasible to use this history table approach to create cubes on top of DWH

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

mpradeesh (10/14/2010)
Here is my situation for Designing a Data Warehouse Star Schema
For each transactional (Fact) data the , account(dimension , but a fact table as it may change the attributes ) has different dimensions which can be changed during the course of time (eg : Customer is moved from one location to another).
Q1. Whats the best method to keep track of Account History ( changes from one location to another or from one status to another and so on )
Slowly Changing Dimenions with either Type 2 or Type 6 is an option here, but what is the impliciation of creating a cube with a Main/history table or all history in one table !!
Q2. How feasible to use this history table approach to create cubes on top of DWH

If tracking changes is a business requirement my personal preference is to go with the Type 2 SCD treatment, just add an ACTIVE_FLAG as well as VALID_FROM_DATE and VALID_UNTIL_DATE columns and everything will fall into place.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

mpradeesh SSC Enthusiast Points: 188 More actions · Answer 2

Thanks, but if the records are keep on updating, so I will end up in 100s of records for the same Account in the same table and its too difficult to query. Do you foreseen that I can use a History table to keep track ( Like a fact-less table) so that the master tables can be used for better performing queries. ?

Steve Jones - SSC Editor SSC Guru Points: 736627 More actions · Answer 3

You could potentially use a second fact table, but is that easier to query? I mean you'll have multiple rows of the same data there.

I think having Paul's advice works, and update all queries to use that table as some type of "as of" marker for data.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

mpradeesh (10/15/2010)
Thanks, but if the records are keep on updating, so I will end up in 100s of records for the same Account in the same table and its too difficult to query. Do you foreseen that I can use a History table to keep track ( Like a fact-less table) so that the master tables can be used for better performing queries. ?

How many times would a Customer move to a different location?

That's why the "S" in SCD stands for "Slowly" 🙂

I really don't see queries getting more complicated.

In my personal experience SCD Type 2 works just fine but there are another ways to track changes - feel free to choose the one that fits your circumstances the better.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

mpradeesh SSC Enthusiast Points: 188 More actions · Answer 5

mpradeesh

SSC Enthusiast

Points: 188

October 15, 2010 at 1:56 pm

#1237857

Thanks Paul