Dimensional modeling for donations with multiple designations
2 posters
Page 1 of 1
Dimensional modeling for donations with multiple designations
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
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
Pre-aggregate count distinct or separate view
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
Re: Dimensional modeling for donations with multiple designations
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.
Similar topics
» Design Fact Table in Dimensional Modeling with Multiple Grain
» Dimensional modeling on HospitalStay
» Dimensional Modeling Certification
» Need for a dimensional modeling tool?
» Dimensional Modeling design
» Dimensional modeling on HospitalStay
» Dimensional Modeling Certification
» Need for a dimensional modeling tool?
» Dimensional Modeling design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum