Financial Transaction Data

I am new to data warehouses and trying to understand the best way to store financial transactions. The financial data I am working with is stored in 3 tables: Total, Transaction, Detail. The TOTAL table is updated after monthly closes. The TRANSACTION table stores the header information and the DETAIL table stores the lines items related to the header in the TRANSACTION table. I am trying to understand how these 3 tables would work into a warehouse. Would all three be FACT tables?

Anyone know of some examples that I could look at?

Thanks.

Without knowing all the detailed business requirements, I would guess that one monthly periodic snapshot fact table would meet your needs. The transaction (header) and detail tables could be collapsed down to store the detail level facts and header and detail level dimensions. You would snapshot the fact table after month-end close and that would replace your total table. During the month (before close) you would have month-to-data details. Do you have facts at the header level? That would affect this design.

Thanks for that. I think that should get me started.

I had another related question. How could I model the chart of accounts if I have accounts that get grouped differently depending on the report? For example, I have a rent1 that is included in rental revenue in one report1 and broken out as a separate item in report 2.

Thanks.

Hi,

one easiest way is to capture that in the lowest grain, ie when you are capturing the account in your fact table you can have column where this will be grouped if for report1, or report2, but if you have many report group then the 2nd option is to have a bridge table which can have the account number and the grouping captured.
With the 2nd option even if you create a grouping after the account is captured your load process can add the new grouping in the bridge table.

thanks

Look at creating a bridge table for the account hierarchy.
Keep the fact at the account level, then the report can display whichever level of the hierarchy required.

If you include a "hierarchy type" attribute in the bridge, then you can easily maintain multiple hierarchies as well.

Thanks for you help. I have been playing around with the bridge tables a bit but not sure if I am setting it up correctly.

This is the scenario I am working with:
Report1
Revenue A
Acct1
Acct2
Acct3

Report2
Revenue B
Acct1
Acct2

How would I set this model up? Thanks