Balance Sheet Fact

Hi,

I need to deliver some balance sheet reporting from the warehouse. I current already have a GL Transaction fact, which holds data by GL Account, Posting Date, Reporting Date, Customer etc.

Is it best practice to build a new fact to deliver the balance sheet reporting?

My current thinking is that my GL Transaction fact will give the details of the actual postings and adjustments etc, by various slices. Then I was thinking of building a fact called "Account Balance" or something similar, which would hold the balances each day for each Balance Sheet account.

Is this the normal way to do it?

Thank you

Usually there are two fact tables. One contains transaction level detail (the journal) and the other a snapshot of the GL. The snapshot is usually the current balance and net activity by fully qualified account (i.e. including stuff like project, cost center and other subdivisions) and fiscal period. I have not seen a daily account balance table. Usually the period balance and the transaction data can be integrated in a query to derive a specific day's balance if it was needed.

May be look into periodic snapshot