How to model merged donors

View previous topic View next topic Go down

How to model merged donors

Post  dwdssarc on Wed May 25, 2011 8:37 am


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?

dwdssarc

Posts : 1
Join date : 2011-05-25

View user profile

Back to top Go down

Re: How to model merged donors

Post  BoxesAndLines on Wed May 25, 2011 9:01 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum