Conversion of de-duplicated Customer Numbers due to source system change
We have a Data Warehouse which de-duplicates Customers from 3 operational systems to enable more accurate customer reporting, data mining & CRM activities. We have several month-end snapshots history.
Our organisation is building a new Operation Data Store which will now take over the de-dupe & key persistency functions. The ODS uses more up to date techniques for de-duping so it gets different results.
We want to align the DW with the ODS so operations and analytics functions have the same view of the customer but because the de-dupe results are different, we can?t just have a straight swap of keys. For example, DW matches 3 source IDs together, yet ODS matches 4 source IDs together. We estimate that about 5% of source IDs will change keys each month.
What?s the best way to convert our Customer Keys to:
(a) minimise impact to history (b) retain usability and (c) retain a proper star schema.
We will consider different solutions for conversion of history versus ongoing processing.
Our organisation is building a new Operation Data Store which will now take over the de-dupe & key persistency functions. The ODS uses more up to date techniques for de-duping so it gets different results.
We want to align the DW with the ODS so operations and analytics functions have the same view of the customer but because the de-dupe results are different, we can?t just have a straight swap of keys. For example, DW matches 3 source IDs together, yet ODS matches 4 source IDs together. We estimate that about 5% of source IDs will change keys each month.
What?s the best way to convert our Customer Keys to:
(a) minimise impact to history (b) retain usability and (c) retain a proper star schema.
We will consider different solutions for conversion of history versus ongoing processing.
ASKED:
May 11, 2005 2:03 AM GMT
UPDATED:
May 18, 2005 9:02:16 PM GMT
RELATED QUESTIONS
- How widespread is de-duplication in your data center?
- VMWare, Windows, Deduplication, Defragmentation: Bad combo?
- Windows to Linux Migration - Clients and Server
- How to strip comments from CL program?
- Conversion Project
- Document Management System?
- Not compressed backup of databased with data compression enabled
- Unexpected results in data
- dbcc checkdb error; object id 0
- Data Warehouse Query Performance
Answer Wiki:
With respect to "history," there would seem to to two cases: 1) situations in which you want to be able to report history based on "truth" as you then saw it, and 2) situations in which you want to report based on the improved version of "truth" made possible by better "deduping." As an example of the first case, if someone used the existing data warehouse for some form of government reporting, and there might be a need for future drilldown, you want to have consistency, so keep an "old model" DW running. On the other hand, if you are using the data warehouse for, say, some form of sales compensation based on this year versus last year, you probably want to run the previous year's data through the "better" deduping process so that you have apples to apples comparison between last year and this year. Going forward, you presumably want to move to the better version of deduping.
It isn't obvious why a difference in deduping would impact your star schema.
It isn't obvious why a difference in deduping would impact your star schema.
0 pts.
To see other answers submitted to the Answer Wiki View Answer History.
