Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fact Tables

Status
Not open for further replies.

monsky

Programmer
Jul 3, 2002
89
AP
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.
 
Yes, it is acceptable and I recommend you check out Dr. Kimball's "The Data Warehouse Toolkit", second edition, where he has examples of this kind of situation.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top