Advice needed on fact table with multiple granularites

View previous topic View next topic Go down

Advice needed on fact table with multiple granularites

Post  tyke3090 on Mon Dec 12, 2011 12:28 pm

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)

tyke3090

Posts : 1
Join date : 2011-12-12

View user profile

Back to top Go down

Transaction type

Post  elmorejr on Mon Dec 12, 2011 2:48 pm

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

elmorejr

Posts : 25
Join date : 2011-10-20
Location : Columbus, Ohio, United States of America, Earth, Sol

View user profile

Back to top Go down

Re: Advice needed on fact table with multiple granularites

Post  arnaudnorgdegren on Sun Jan 08, 2012 7:54 am

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

View user profile

Back to top Go down

Re: Advice needed on fact table with multiple granularites

Post  Jeff Smith on Mon Jan 09, 2012 10:34 am

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?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

RE:

Post  sgudavalli on Wed Jan 11, 2012 4:08 am

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$








sgudavalli

Posts : 29
Join date : 2010-06-10
Age : 33
Location : Pune, India

View user profile

Back to top Go down

Re: Advice needed on fact table with multiple granularites

Post  Vishy on Thu Mar 01, 2012 4:26 am

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 ???

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Advice needed on fact table with multiple granularites

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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