| Author |
 Topic  |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 12/10/2012 : 19:37:49
|
Hello Experts,
Need a suggestion with re-architecting the table...
In our Datawarehousing system, we have a customer table with million records (Customer_ID, Customer_First_Name, Customer_Last_Name, ....). Now the source system(OLTP) is renumbering all the customer_ID's and our datawarehouse system may end up with same customers with multiple Customer_ID's. Since it is datawarehouse, we need all the old numbers as well as new numbers and at the same time, we also do not want to make Customer table as Type 2. What is the best way to achieve this? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 12/10/2012 : 19:49:21
|
That's more of a business issue than a database one I'd say. Now you have two where previously you had one and it has the potential to get really difficult.
How do you use the customer number? On reports? On searches? For billing? Are you going to refer to the old, new or both? What do you do when you count customers for some criteria? Do you count them twice or treat the old & new IDs as the same? Can the old & new overlap?
Depending on your use, you've got a huge deal on your hands.
Anyway, the way I'd do it is allocate an internal "DW ID" and hang a bunch of external references off that, one being the original customer number and the other being the new. e.g.
DW ID, RefType, ExternalRefNumber
1000 ORIGID 123
1000 NEWID 456
(unique constraint on ref type & ref #)
Then all queries need to be rewritten in terms of the old or new identifiers.
I'd also assign my new "DW IDs" from a number range that does not already get used so you don't accidentally provide results from old queries that maybe don't know there are now multiple identifiers. Not doing this may seem like a shortcut to getting things done but I assue you, it will just muddy the waters.
|
 |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 12/10/2012 : 20:08:10
|
Thanks for the response!!!
We are going to use it for Reports. So, we cannot report it as old and new.The new ID should be the one which will have old data too(In Reporting). There should be only one customer for any criteria and they will not OverLap(For Example...if the customer number in source changes today, all the referential integrity tables will get updated with the latest customer_ID. So there will be no OVERLAP). If we use "DW ID", aren't we storing two records for one customer? Is there a way that we can merge the old and new and create a new table with only one record(Latest Customer_ID)? |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 12/10/2012 : 21:51:17
|
I'm afraid I don't understand what you are doing then. If it's a wholesale change then just change the IDs everywhere in the DW when you change it in the OLTP and be done with it.
If you just need a history of who changed to what and when, then a table of old/new IDs will do.
Sorry - not sure if I'm helping but I don't understand what your business problem is. If you need to change something, just change it. |
|
|
| |
Topic |
|
Re-Architecting the table