Financial Transaction Data

View previous topic View next topic Go down

Financial Transaction Data

Post  stout27 on Tue May 14, 2013 10:20 am

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.

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Financial Transaction Data

Post  cjrinpdx on Tue May 14, 2013 3:16 pm

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.

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Financial Transaction Data

Post  stout27 on Thu May 16, 2013 7:48 pm

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.

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Financial Transaction Data

Post  hkandpal on Fri May 17, 2013 8:29 am

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


hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Financial Transaction Data

Post  LAndrews on Fri May 17, 2013 1:02 pm

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.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Financial Transaction Data

Post  stout27 on Thu May 23, 2013 12:25 pm

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

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Financial Transaction Data

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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