We have a situation where we are loading and tracking financial donors and their donations in a not-for-profit increment to our future data warehouse. The problem is that the donors are being match-merged after the initial load, so we can get changed donor idís after the initial seed load. There will be some method of tracking these changes through a legacy donor id. Our user community may need to report on the history of changes for a particular donor and their associated donations. For example, there is one known instance we a donor donated in ten different regions under the different variations of the same name, e.g., Smith, Smythe, Smithe, etc. The goal is to have all of these have different donor idís representing the same person to be merged into a final donor id. Unfortunately due to the regulatory aspects of this venture, we apply data quality or cleansing to the donor idís beforehand.
Current thinking is to create a separate table tracking donor merge history and only keep the latest, final merged donor in the donor dimension. We could track the history of the donors by adding both a donor key and a merge donor key to the donor dimension and carry both foreign keys in all associated facts. This would resemble having a type-1 dimension containing the current and latest donor and type-2 dimension table where donor history would be covered.
Does this seem to be a reasonable modeling solution to handle this situation? Are there any other approaches to handle this situation?
- Posts : 1
Join date : 2011-05-25
Yes you need a table to track new donor id (let's call it party id) to all legacy donor id's. It is a simple cross reference table. You do not need to carry the legacy donor id in the new dimension. In fact, you can't since there may be more than one for a given party.
- Posts : 1212
Join date : 2009-02-03
Location : USA
Permissions in this forum:You cannot reply to topics in this forum