Amount Outstanding & Avg. Days Outstanding
Amount Outstanding
Building a cube around a fairly basic accounting system, with Invoices and Payments. Need to be able to get the Amount Outstanding for an invoice at any particular time (historically as well as currently). For example, if John Doe gets an invoice for $100 on 01/01/08, then makes a payment of $20 on 01/10/08 and another payment of $20 on 01/15/08, then @ 01/01/08 his Amount Outstanding is $100, on 01/05/08 it’s $100, on 01/11/08 it’s $80 and currently (03/19/08) it’s $60. The user needs to be able to see transactions that happened on a particular day, and also the current amount outstanding. So if the user were to look at the cube in January, this is what it would look like:
Customer Date Amount Billed Amount Paid Amount Outstanding
John Doe 1/1/2008 $ 100.00 $ - $ 100.00
John Doe 1/2/2008 $ - $ - $ 100.00
John Doe 1/3/2008 $ - $ - $ 100.00
John Doe 1/4/2008 $ - $ - $ 100.00
John Doe 1/5/2008 $ - $ - $ 100.00
John Doe 1/6/2008 $ - $ - $ 100.00
John Doe 1/7/2008 $ - $ - $ 100.00
John Doe 1/8/2008 $ - $ - $ 100.00
John Doe 1/9/2008 $ - $ - $ 100.00
John Doe 1/10/2008 $ - $ 20.00 $ 80.00
John Doe 1/11/2008 $ - $ - $ 80.00
John Doe 1/12/2008 $ - $ - $ 80.00
John Doe 1/13/2008 $ - $ - $ 80.00
John Doe 1/14/2008 $ - $ - $ 80.00
John Doe 1/15/2008 $ - $ 20.00 $ 60.00
John Doe 1/16/2008 $ - $ - $ 60.00
John Doe 1/17/2008 $ - $ - $ 60.00
John Doe 1/18/2008 $ - $ - $ 60.00
John Doe 1/19/2008 $ - $ - $ 60.00
John Doe 1/20/2008 $ - $ - $ 60.00
John Doe 1/21/2008 $ - $ - $ 60.00
John Doe 1/22/2008 $ - $ - $ 60.00
John Doe 1/23/2008 $ - $ - $ 60.00
John Doe 1/24/2008 $ - $ - $ 60.00
John Doe 1/25/2008 $ - $ - $ 60.00
John Doe 1/26/2008 $ - $ - $ 60.00
John Doe 1/27/2008 $ - $ - $ 60.00
John Doe 1/28/2008 $ - $ - $ 60.00
John Doe 1/29/2008 $ - $ - $ 60.00
John Doe 1/30/2008 $ - $ - $ 60.00
John Doe 1/31/2008 $ - $ - $ 60.00
If the user was looking at JUST January, the Amount Outstanding would be $60 (the amount it was on the last day of the period) If John Doe made another $20 payment in Feb, then the Amount Outstanding for the quarter would be $40.
Avg. Days Outstanding
Once Amount Outstanding is working correctly, then I need to tackle the Avg. Days Outstanding measure. To get this number, you would add up the Amount Outstanding measure for all the days in the period, and divide it by the total Amount billed for that period. Using the example above, to get the Avg. Days Outstanding for January you could add up all the Amount Outstanding measures for each day in Jan ($2,320.00) and divide it by the total Amount Billed in Jan ($100.00) which would give you an Avg. Days Outstanding of 23.2. Also, if the user were to look down to the DAY level of January, the cube should add up the Amount Outstanding measures for each day above itself, and then divide that number by the Amount Billed total for each day above itself. For example, looking at 01/12/08 would give you ($1,140.00 / $100) = 11.4 Avg. Days Outstanding.
My Initial Solution
To solve the first issue of Amount Outstanding, I created an Account dimension that had Charges and Receipts as children under Account Balance. I used the Unary Operator functionality to have Account Balance = (Charges – Receipts) at the rolled up level. Then I had a Fact Table that had CustomerID, TransactionDate, AccountID & Amount. This didn’t work right off the bat, because at the day level the Amount Outstanding for 01/10/08 would be $0.00 - $20 = -$20, which wasn’t right. I couldn’t keep the cumulative amount going for the balance. At the month level of January it worked right. Then I created a calculated measure called [Amount Outstanding] as
Aggregate
(
NULL:[Transaction Date].[Year - Quarter - Month - Date].CurrentMember,
[Measures].[Amount]
)
This works, but I fear the performance hit I may get – it already seemed kinda pokey on my tiny tiny fact table, which isn’t good.
Now, what I’m thinking, is to have two Fact tables – one that holds transactional data, and one that holds balance data.
Anybody have any suggestions?
Amount Outstanding
Building a cube around a fairly basic accounting system, with Invoices and Payments. Need to be able to get the Amount Outstanding for an invoice at any particular time (historically as well as currently). For example, if John Doe gets an invoice for $100 on 01/01/08, then makes a payment of $20 on 01/10/08 and another payment of $20 on 01/15/08, then @ 01/01/08 his Amount Outstanding is $100, on 01/05/08 it’s $100, on 01/11/08 it’s $80 and currently (03/19/08) it’s $60. The user needs to be able to see transactions that happened on a particular day, and also the current amount outstanding. So if the user were to look at the cube in January, this is what it would look like:
Customer Date Amount Billed Amount Paid Amount Outstanding
John Doe 1/1/2008 $ 100.00 $ - $ 100.00
John Doe 1/2/2008 $ - $ - $ 100.00
John Doe 1/3/2008 $ - $ - $ 100.00
John Doe 1/4/2008 $ - $ - $ 100.00
John Doe 1/5/2008 $ - $ - $ 100.00
John Doe 1/6/2008 $ - $ - $ 100.00
John Doe 1/7/2008 $ - $ - $ 100.00
John Doe 1/8/2008 $ - $ - $ 100.00
John Doe 1/9/2008 $ - $ - $ 100.00
John Doe 1/10/2008 $ - $ 20.00 $ 80.00
John Doe 1/11/2008 $ - $ - $ 80.00
John Doe 1/12/2008 $ - $ - $ 80.00
John Doe 1/13/2008 $ - $ - $ 80.00
John Doe 1/14/2008 $ - $ - $ 80.00
John Doe 1/15/2008 $ - $ 20.00 $ 60.00
John Doe 1/16/2008 $ - $ - $ 60.00
John Doe 1/17/2008 $ - $ - $ 60.00
John Doe 1/18/2008 $ - $ - $ 60.00
John Doe 1/19/2008 $ - $ - $ 60.00
John Doe 1/20/2008 $ - $ - $ 60.00
John Doe 1/21/2008 $ - $ - $ 60.00
John Doe 1/22/2008 $ - $ - $ 60.00
John Doe 1/23/2008 $ - $ - $ 60.00
John Doe 1/24/2008 $ - $ - $ 60.00
John Doe 1/25/2008 $ - $ - $ 60.00
John Doe 1/26/2008 $ - $ - $ 60.00
John Doe 1/27/2008 $ - $ - $ 60.00
John Doe 1/28/2008 $ - $ - $ 60.00
John Doe 1/29/2008 $ - $ - $ 60.00
John Doe 1/30/2008 $ - $ - $ 60.00
John Doe 1/31/2008 $ - $ - $ 60.00
If the user was looking at JUST January, the Amount Outstanding would be $60 (the amount it was on the last day of the period) If John Doe made another $20 payment in Feb, then the Amount Outstanding for the quarter would be $40.
Avg. Days Outstanding
Once Amount Outstanding is working correctly, then I need to tackle the Avg. Days Outstanding measure. To get this number, you would add up the Amount Outstanding measure for all the days in the period, and divide it by the total Amount billed for that period. Using the example above, to get the Avg. Days Outstanding for January you could add up all the Amount Outstanding measures for each day in Jan ($2,320.00) and divide it by the total Amount Billed in Jan ($100.00) which would give you an Avg. Days Outstanding of 23.2. Also, if the user were to look down to the DAY level of January, the cube should add up the Amount Outstanding measures for each day above itself, and then divide that number by the Amount Billed total for each day above itself. For example, looking at 01/12/08 would give you ($1,140.00 / $100) = 11.4 Avg. Days Outstanding.
My Initial Solution
To solve the first issue of Amount Outstanding, I created an Account dimension that had Charges and Receipts as children under Account Balance. I used the Unary Operator functionality to have Account Balance = (Charges – Receipts) at the rolled up level. Then I had a Fact Table that had CustomerID, TransactionDate, AccountID & Amount. This didn’t work right off the bat, because at the day level the Amount Outstanding for 01/10/08 would be $0.00 - $20 = -$20, which wasn’t right. I couldn’t keep the cumulative amount going for the balance. At the month level of January it worked right. Then I created a calculated measure called [Amount Outstanding] as
Aggregate
(
NULL:[Transaction Date].[Year - Quarter - Month - Date].CurrentMember,
[Measures].[Amount]
)
This works, but I fear the performance hit I may get – it already seemed kinda pokey on my tiny tiny fact table, which isn’t good.
Now, what I’m thinking, is to have two Fact tables – one that holds transactional data, and one that holds balance data.
Anybody have any suggestions?
