Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

How to model merged donors

2 posters

Go down

How to model merged donors Empty How to model merged donors

Post  dwdssarc 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

Back to top Go down

How to model merged donors Empty Re: How to model merged donors

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

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

Back to top Go down

Back to top

- Similar topics

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