3ilabsbiz (11/20/2010)
I have 3 tables in OLTP like Account,Contact,Address related with foreignkey relationship and account has account type attribute, address has city,state,country,zipHow I can design a Dimensional data store with this info. The basic business requirement is to find the no of Accounts from a particular zip code , keeping the history of count if the a customer moves from one location to another. I thought account as Fact , Address as slowly changing dimension with type 2, but then if an address changes then I have to add additional row in Account Fact and not a good design.
What is the logic for relationship based on type 2 SCD.
You do not add an additional row on the FACT table, you add an additional row on the DIM table.
My favorite design for a Type II SCD DIM table is by adding three columns to the design:
- Version
- ValidFromDate
- ValidUntilDate
In my design version=0 is the current version while older versions go 1,2,3,etc.
For rows with version > 0 ValidFromDate/ValidUntilDate tells you "when" that particular row was current.
Hope this helps.
_____________________________________
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.