Historical Balance ??

Last Post 21 Mar 2008 12:43 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

20 Mar 2008 08:16 AM
Hello - I have this requirement that seems like it should be fairly straight forward to solve - but I can't seem to get it to work quite right. It's a basic accounting system, with charges/receipts occuring daily. What I also need to get - is what the current balance of an account was at any given point in time in history. For example:

John Doe gets an invoice for:
$100 on 01/01/2008

He makes payments of:
$10 on 01/10/08
$10 on 01/15/08
$10 on 02/10/08

Looking at each level of the time hierarchy should look like this:
Date: Q1 2008 Amount Billed: $100 Amount Paid: $30 Amount Outstanding: $70
Date: Jan 2008 Amount Billed: $100 Amount Paid: $20 Amount Outstanding: $80
Date: Feb 2008 Amount Billed: $0.00 Amount Paid: $10 Amount Outstanding: $70
Date: Feb 10, 2008 Amount Billed: $0.00 Amount Paid: $10 Amount Outstanding: $70
Date: Feb 20, 2008 Amount Billed: $0.00 Amount Paid: $0.00 Amount Outstanding: $70

I tried to use the Adventure Works example with the DimAccount dimension, that had an Account Balance parent, and two children of Charge and Receipt. Charge had a unary operator of '+' and Receipt had a unary operator of '-'. This sort of worked - except not down to the day level. For example, on Feb 10, 2008 it would take $0.00 - $10 and give a balance of -$10.

I then created a calculated measure as:
NULL:[Transaction Date].[Year - Quarter - Month - Date].CurrentMember,

This seemed to work (keep in mind the Amount measure automatically subtracts Receipts from Charges because of the unary operator) but I fear a performance hit on this when the DW gets populated with all the invoices (roughly 24,000 per year).

The ultimate goal is to be able to get Amount Paid, Amount Billed, Amount Outstanding and the Avg. Days Outstanding for any point in time.

Any performance suggestions?
Any links to related articles?
Any experience with accounting related type cubes?

Anything would help - thanks very much!!

New Member
New Member

21 Mar 2008 12:43 AM

The goal of the data warehouse is to provide analysis of data via aggregates over dimensions using OLAP cubes.

SQL Server 2005 and SQL Server 2008 do this job excellently.

Getting a historic snapshot day by day is difficult.

Some thoughts: you can introduce a day dimension and use it for filtering for a particular day or prior. Naturally this would add overhead. You would have to tag each fact with this dimension.

You can also take a snapshot of the data warehouse database daily and run OLAP cube processing on them when needed like end of months or end of quarters.

Let us know how you decide.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/

Acceptable Use Policy