Transactions vs. Balances

Relationship between balance sheet and profit and loss account: The profit and loss (P&L) account summarises a business' trading transactions... The balance sheet, by comparison, provides a financial snapshot at a given moment --businesslink.gov.uk

The data warehouse strives to integrate the information across diverse business processes, product lines, and sales channels. The data warehouse does not re-invent the wheel, the calculations already performed by the operational systems are never repeated at the data warehouse.

The majority of facts in a star schema fall into 1 of the 3 categories:

1. Transactions: retail sales transactions, financial services transactions - interest payment, loan disbursement, loan payment, etc.
   
2. Balances: that are impacted by the transactions, product inventory, customer account balances, etc.
3. Summarized impact of transactions on balances over a period of time: total sales of a product for the day or week, total interest payment to a customer for the month, etc.
   

The transactions are stored on the operational system where they originate. The data warehouse sources the transactions from the operational system.

The balances can be derived from the transactions, but are also calculated and stored on the operational system. The data warehouse sources the balances from the operational system.

The summarized impact of transactions on balances can be derived from the transactions as well. The summarized transactions are derived by eliminating the transaction detail dimension. The various transaction types are grouped together into a limited set, and helps in general understanding of data. The detailed transaction amounts:

Transaction detail.transaction type	Transactions.amount
Investment interest	100
Margin interest	50
Passive interest	20
etc.	...

are converted to summarized amounts limited by a set of categories:

Aggregated Transactions.interest amount	Aggregated Transactions.principal amount	etc.
170	250	...

Once the transaction detail dimension has been eliminated from the star:

Account (customer) 30 million records				Product
	\		/
Account customer demographics mini dimension	-	Transactions 3 million records per day	-	Organization (branch, financial adviser)
	/	|	\
Account status mini dimension		Transaction detail		Calendar

the dimensions on the transactions fact are same as that on the balances:

Account (customer) 30 million records				Product
	\		/
Account customer demographics mini dimension	-	Summarized Transactions 2 million records per day	-	Organization (branch, financial adviser)
	/		\
Account status mini dimension				Calendar

It is now possible to keep the balances and summarized transactions on the same fact table to make the subsequent aggregations consistent across balances and transactions.

In some cases, for example retails sales, the calculations for summarizing transactions into balance impact are simple and the operational system does not store the calculated results. In other cases, for example financial services, the calculations are quite complex and the operational system stores the calculated results along with the transactions themselves. The data warehouse sources the summarized transaction amounts from the operational system where they are available.