Hi,

We recently started implementing some cubes for our retailing
operations using Analysis Services 2000. We created an accounts cube
with financial period (time) and accounts (parent-child) dimensions,
with Credit Amount and Debit Amount as measures.

Our requirement is to have account balances shown as calculated members
at the end of each financial period. While this can be done using a
simple calculation like Debit Amount minus Credit Amount, while
drilling down into months that may not give us the exact closing
balance as per each month, but instead would give us only the
difference between debit and credit.

I thought it would be great if I am able to write an MDX expression
that allows me to create a calculated member 'Closing Balance' that
does a calculation like (Debit-Credit)+Closing Balance of last
financial period member (If I am viewing June data in my cube browser,
then it should give me the sum of debit-credit with the Closing Balance
of May). I know this sounds like an iterative calculation, and I am not
sure if it is the right approach.

Can anyone help me on this? I would be very grateful.

Thadeus Anand.

Hi Thadeus,

The problem with what you have at the moment, is that to calculate *any*
closing balance on the fly you have to calculate right from the opening
balance at the start of your cube. That's always going to be slow,
especially as your cube grows.

Is it too late to modify your fact table? If you can add a closing
balance column then you can add a single entry per month/week or
whatever which has no credits, no debits but just the balance. The
column would be <NULL> or zero for all proper transactions.

This way you can have "closing balance" as a measure in the cube (use
MDX to pick the last closing balance for the time period you are
selecting), and also have "running balance" which is the sum of the
prior period closing balance and the appropriate debits and credits.

This is still fairly complex MDX, but it should outperform having to
calculate everything from the beginning of time.

Best Regards,

Richard


Thadeus Anand wrote:

Dear Richard,

Thank you very much for a detailed reply. According to your advice, I
finally succeeded finding the closing balance on any day by using a new
calculated member with the following MDX.

Sum({[Financial Period].CurrentMember.Level.Members}.Item(0):[Financial
Period].CurrentMember, [Measures].[Debit Amount]-[Measures].[Credit
Amount])

This works beautifully for any given date, though there are certain
issues, the major one being performance.

To handle the performance issue, I decided to have the running balance
in my fact table itself. Now the problem is when I look at a particular
month, the balance shows the sum of all balances during the entire
month.

Can you help me out and tell me how to get the last day's closing
balance entry alone when I look at a year or a month?

Thanks for your help again.

Thadeus.