Advice needed on fact table with multiple granularites
+2
elmorejr
tyke3090
6 posters
Page 1 of 1
Advice needed on fact table with multiple granularites
Sorry this should have been posted in Dimensional Modelling
Hi, i wonder if anyone can help clear my thoughts regarding a design decision i have to make for a data warehouse project.
The project deals with data for humanitarean aid programs.
An aid program has a single target amount defined when the program is set up.
Thereafter donations are sought from participating donor countries and amounts are allocated to various recieveing countries.
for example
program xyz has a target amount of 10,000,000
country A donates 2,000,000
country B donates 1,500,000
Country C donates 4,000,000
country m receives 3,000,000
country n receives 1,000,000
notice that at any point in time the totals of the values do not have to equal each other.
How would you model this in such a way as to lower the end users chances of creating inaccurate reports?
My initial thought was to have a program dimension table holding all the program dates, program id, title etc.
I would then have a fact table, which as well as holding the target amount for the program would have fields to hold donor and receiving country codes and the relative amounts.
In the example above this would give us a fact table with 6 columns and 6 rows:
fk_program | amount_program | ctry_don | amount_don | ctry_rec | amount_rec
xyz | 10000000 | null | 0 | null | 0
xyz | 0 | A | 2000000 | null | 0
xyz | 0 | B | 1500000 | null | 0
xyz | 0 | C | 4000000 | null | 0
xyz | 0 | null | 0 | m | 3000000
xyz | 0 | null | 0 | n | 1000000
My concern hear is about mixing the granularity of the fact table between the program (granularity 1 to 1) and the donor/receiver countries (1 to many)
Hi, i wonder if anyone can help clear my thoughts regarding a design decision i have to make for a data warehouse project.
The project deals with data for humanitarean aid programs.
An aid program has a single target amount defined when the program is set up.
Thereafter donations are sought from participating donor countries and amounts are allocated to various recieveing countries.
for example
program xyz has a target amount of 10,000,000
country A donates 2,000,000
country B donates 1,500,000
Country C donates 4,000,000
country m receives 3,000,000
country n receives 1,000,000
notice that at any point in time the totals of the values do not have to equal each other.
How would you model this in such a way as to lower the end users chances of creating inaccurate reports?
My initial thought was to have a program dimension table holding all the program dates, program id, title etc.
I would then have a fact table, which as well as holding the target amount for the program would have fields to hold donor and receiving country codes and the relative amounts.
In the example above this would give us a fact table with 6 columns and 6 rows:
fk_program | amount_program | ctry_don | amount_don | ctry_rec | amount_rec
xyz | 10000000 | null | 0 | null | 0
xyz | 0 | A | 2000000 | null | 0
xyz | 0 | B | 1500000 | null | 0
xyz | 0 | C | 4000000 | null | 0
xyz | 0 | null | 0 | m | 3000000
xyz | 0 | null | 0 | n | 1000000
My concern hear is about mixing the granularity of the fact table between the program (granularity 1 to 1) and the donor/receiver countries (1 to many)
tyke3090- Posts : 1
Join date : 2011-12-12
Transaction type
I think you would be better served to implement a transaction type (goal, donation, distribution). Thus you would have
fk_program | fk_trsact_type | fk_country | amount
xyx | target | n/a | 10,000,000
xyx | donation| A | 2,000,000
xyx | donation| B | 1,500,000
xyx | donation| C | 4,000,000
xyx | distribution | m | 3,000,000
xyx | distribution | n | 1,000,000
This will give you the flexibility to mix-n-match data as needed without the worry of handling null values and awkward zero fill in columns.
For the target goal, you would set the Country to n/a (unknown) as the target is not specific to a country.
But if you had a country specific target, it would be supported here as well...
fk_program | fk_trsact_type | fk_country | amount
xyx | target | n/a | 10,000,000
xyx | donation| A | 2,000,000
xyx | donation| B | 1,500,000
xyx | donation| C | 4,000,000
xyx | distribution | m | 3,000,000
xyx | distribution | n | 1,000,000
This will give you the flexibility to mix-n-match data as needed without the worry of handling null values and awkward zero fill in columns.
For the target goal, you would set the Country to n/a (unknown) as the target is not specific to a country.
But if you had a country specific target, it would be supported here as well...
elmorejr- Posts : 25
Join date : 2011-10-20
Location : Columbus, Ohio, United States of America, Earth, Sol
Re: Advice needed on fact table with multiple granularites
elmorejr wrote:I think you would be better served to implement a transaction type (goal, donation, distribution). Thus you would have
fk_program | fk_trsact_type | fk_country | amount
xyx | target | n/a | 10,000,000
xyx | donation| A | 2,000,000
xyx | donation| B | 1,500,000
xyx | donation| C | 4,000,000
xyx | distribution | m | 3,000,000
xyx | distribution | n | 1,000,000
This will give you the flexibility to mix-n-match data as needed without the worry of handling null values and awkward zero fill in columns.
For the target goal, you would set the Country to n/a (unknown) as the target is not specific to a country.
But if you had a country specific target, it would be supported here as well...
thanks for the
______________________
find niche markets| internet marketing tip| affiliate marketing keyword
Last edited by arnaudnorgdegren on Sun Jan 15, 2012 8:42 pm; edited 1 time in total
arnaudnorgdegren- Posts : 2
Join date : 2012-01-08
Re: Advice needed on fact table with multiple granularites
Should the target go on a different fact table? Or at least on an aggregate of the first fact table of the Distributed amount?
What happens if the target amount changes? Is the incremental amount inserted into the first fact table as a ne row.
Is there any way to connect the distributed amount to the donating country?
What happens if the target amount changes? Is the incremental amount inserted into the first fact table as a ne row.
Is there any way to connect the distributed amount to the donating country?
Jeff Smith- Posts : 471
Join date : 2009-02-03
RE:
how about creating 3 dimensions and one dimension table i.e..
dimension tables:
program=> programid|| targetamount|| etc....
country=> countryid||country names|| etc...
date=> dateid||date||etc..
fact tables:
programfacts => programfactid||programid||countryid||dateid||programfactflag||amount
ie.. programfactflag says if its a donation or recieved amount. and donation is +ve amounts and received is -ve amounts....
in the below sample i am trying to pull in the 1000$ donations made by USA and 50$ recieved by JPY and 950$ received by KOR
x||USA||Jan1st||1||1000$
x||JPY||Jan2nd||0||-50$
x||KOR||Jan3nd||0||-950$
dimension tables:
program=> programid|| targetamount|| etc....
country=> countryid||country names|| etc...
date=> dateid||date||etc..
fact tables:
programfacts => programfactid||programid||countryid||dateid||programfactflag||amount
ie.. programfactflag says if its a donation or recieved amount. and donation is +ve amounts and received is -ve amounts....
in the below sample i am trying to pull in the 1000$ donations made by USA and 50$ recieved by JPY and 950$ received by KOR
x||USA||Jan1st||1||1000$
x||JPY||Jan2nd||0||-50$
x||KOR||Jan3nd||0||-950$
sgudavalli- Posts : 29
Join date : 2010-06-10
Age : 40
Location : Pune, India
Re: Advice needed on fact table with multiple granularites
Create folowing
1) Program Dim ( Having target here itself for that program)
2) Have a Country Dim having all the donor and receiver countries.
3) Have a Time dim
4) Have a fact table having program keys,country keys,time keys,amount,TYPE.
"TYPE" will be a degenerating dimension having only 2 values -- DONOR,RECEIVER
does it solve your issue ???
1) Program Dim ( Having target here itself for that program)
2) Have a Country Dim having all the donor and receiver countries.
3) Have a Time dim
4) Have a fact table having program keys,country keys,time keys,amount,TYPE.
"TYPE" will be a degenerating dimension having only 2 values -- DONOR,RECEIVER
does it solve your issue ???
Vishy- Posts : 75
Join date : 2012-02-28
Location : India
Similar topics
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Why not snowflake if the aggregate dimension is needed for a different fact table?
» How to handle multiple aggregations for multiple KPIs in fact table
» I would like some advice regarding the following FACT table construction
» Advice on Fact Table Design
» Why not snowflake if the aggregate dimension is needed for a different fact table?
» How to handle multiple aggregations for multiple KPIs in fact table
» I would like some advice regarding the following FACT table construction
» Advice on Fact Table Design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum