Dimensional modeling for donations with multiple designations

View previous topic View next topic Go down

Dimensional modeling for donations with multiple designations

Post  ttran on Thu May 06, 2010 11:08 am

I'm trying to find the most appropriate design for the above topic.

Transactional-wise, our system processes incoming donations. Each donation (say, someone walks in and hands you a check) may have 1 or more designations associated with it (e.g., 50% goes to help Haiti, the other 50% goes to help Chile earthquake victims).

One way I thought of is to have a donation_designation_fact table with the grain being a single designation with the following relevant columns:
...
-- Dimensional key connects to designation_dimension
designation_key,
...
-- Whether the donation is a whole donation going to a single designation
is_whole,
-- Whether the donation is a "partial" donation
is_partial,
designation_percentage,
...
dollar_amount,
...

This works well to answer any questions regarding breakdown of how much is given to any designation, and is very "slice-and-dice-able."

However, we want to be able to answer the simple question of "How many donations were there? As in, how many times did someone walk up to you and hand you a check?"

This isn't possible with the above design. Some options I can think of:
1. If we add a degenerate dimension recording the donation ID, then we have to do count(distinct) which I think we should avoid.
2. There's also the possibility of having 2 separate fact tables, one having the grain of a single donation, the other having the grain of a single designation (like above). This feels like an overkill for what seems to be relatively simple problem.

Any other thoughts or suggestions? Many thanks in advance!
--Tri

ttran

Posts : 2
Join date : 2010-05-06

View user profile

Back to top Go down

Pre-aggregate count distinct or separate view

Post  ttran on Thu May 06, 2010 11:42 am

Perhaps my situation is very similar to the classic retail sales fact table, where one would question "How many times did a customer have a POS transaction?" (regardless of what they bought in that transaction). In that case, it looks like having a roll-view of my donation_designation_fact table would do.

ttran

Posts : 2
Join date : 2010-05-06

View user profile

Back to top Go down

Re: Dimensional modeling for donations with multiple designations

Post  ngalemmo on Thu May 06, 2010 11:47 am

You should be ok with a single fact table with a degenerate donation ID, designated charity, donor, date, etc... as dimensions. Storing the distribution percent and allocated amount should allow you to do any sort of reporting.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimensional modeling for donations with multiple designations

Post  Sponsored content


Sponsored content


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