Hi guys,
I'm trying to figure out how to make a better design for a set of fact tables for a data mart.
The report needs to show a previous month's statement balance amount then the net transactions for a month.
To be able to do this I have to use two tables. The first table contains a summary of balances per person per month per year
Date Code Previous Balance
052004 000001 100.00
052004 000002 200.00
The other table contains details of transactions for a particular month.
Date Code Amount
05122004 000001 20.00
05132004 000001 40.00
05142004 000002 10.00
05152004 000002 20.00
My report will look like something like this:
Balances For the month of May 2004
Code Prev Bal Net Trans Curr Bal
000001 100.00 60.00 160.00
000002 200.00 30.00 230.00
The initial design I made basically links this two tables by month, year and person's code. There are additional dimensions to be included by I just presented it this way for simplification.
Person ----- Balances------ Offices
|
Details
What you might say here is basically two fact tables. Now, I'm not sure if this is an acceptable design approach in dimensional modeling but I don't have an idea on how to model this. Currently I'm not satified with the performance.
Now those master dimensional modelers out there might have suggestions.
I'm trying to figure out how to make a better design for a set of fact tables for a data mart.
The report needs to show a previous month's statement balance amount then the net transactions for a month.
To be able to do this I have to use two tables. The first table contains a summary of balances per person per month per year
Date Code Previous Balance
052004 000001 100.00
052004 000002 200.00
The other table contains details of transactions for a particular month.
Date Code Amount
05122004 000001 20.00
05132004 000001 40.00
05142004 000002 10.00
05152004 000002 20.00
My report will look like something like this:
Balances For the month of May 2004
Code Prev Bal Net Trans Curr Bal
000001 100.00 60.00 160.00
000002 200.00 30.00 230.00
The initial design I made basically links this two tables by month, year and person's code. There are additional dimensions to be included by I just presented it this way for simplification.
Person ----- Balances------ Offices
|
Details
What you might say here is basically two fact tables. Now, I'm not sure if this is an acceptable design approach in dimensional modeling but I don't have an idea on how to model this. Currently I'm not satified with the performance.
Now those master dimensional modelers out there might have suggestions.