Balance Sheet for Transactional System

[cdhodge2002]

I have a balance sheet that is continually updated from a transactional system. So my numbers tie as long as I am not pulling a certain point in time, in other words I can only pull present date. My issue is that some of the formula numbers they are requesting need data from the balance sheet from a certain date and time. How would be the best way to solve this issue?

[Mike L]

There are two flavors of this issue, depending on whether the question relates to the date for which transactions are posted, or the date on which they entered the system.

In the first case, date for which transactions are posted, you simply need to have a time dimension in your cube. This is standard accounting stuff.

In the second case, date on which transactions entered the system, you need a version dimension (or archive cubes). Been there, done that. Avoid it if you can.

[stephen waters]

Accounting balance sheets are normally stored and analysed at set intervals, very often monthly, sometimes weekly and very occassionally ( eg trading systemes ) daily. In an OLAP system this is normally represented in the time dimension(s).

Your request, to be able to look at a balance sheet at _any_ particular date and time, is unusual. I am interested in your application and why this is needed, can you provide further information?

Is this simply a question of posting date\time vs transaction date\time?


If you need to look at historic balance sheets at any date or time an OLAP tool is probably not right.

[cdhodge2002]

it is a transactional system, each entry is loaded on a date, so the cube is down to the day. So if I want a calculated field, like Gross Profit, it would be correct only at the accumulated current date, not just August 2008. Any body ever encounter this before?

[Mike L]

each entry is loaded on a date, so the cube is down to the day.... it would be correct only at the accumulated current date

Not quite sure what you mean. Does the cube have time dimension(s) with granularity of a day, or does it not?

Any body ever encounter this before?

This is quite common. In the finance industry, e.g., a history of daily balances is very important.

[Eric]

it is a transactional system, each entry is loaded on a date, so the cube is down to the day. So if I want a calculated field, like Gross Profit, it would be correct only at the accumulated current date, not just August 2008. Any body ever encounter this before?

I have to agree I am confused with the request. Daily Gross Profit information can be done by product, service, or customer depending on your needs, but that has nothing to do with a Balance Sheet. Detail like that is for the Income Statement which accumulates until an end of a period, while a balance sheet is information for a point in time.

I think there has been miscommunications between you the developer and the functional request. You may want to circle back to make sure you are building what is being requested.

[Alan Kirk]

it is a transactional system, each entry is loaded on a date, so the cube is down to the day. So if I want a calculated field, like Gross Profit, it would be correct only at the accumulated current date, not just August 2008. Any body ever encounter this before?

Like Eric said, Gross Profit <> Balance Sheet.

We have one instance of this, but it's not a financial cube but rather a statistical cube. In that cube we get a weekly feed from a distribution system, and the feed relates any adjustments back to the dates of the original transactions. We use two time dimensions; one to track the original transaction date, and one to track the date of the feed. This therefore allows us to get a view of what the balance was at any particular time.

This isn't a huge cube, though.

[paulsimon]

I regularly use cubes where I have movement based data coming in to the balance sheet. I then use a set of cumulative to date consolidations to show the balance at any point in time. This works well, as TM1 is very efficient at consolidation so even adding up 10 years - 240 months of history doesn't cause it too much trouble. Most balance sheet data is movement data, but the balance sheet is always shown as at a point in time with cumulative movements.

However, if someone is asking you to show Profit and Loss aka Income Statement data at a point in time, then there is some confusion over accounting. Retained Earnings does appear on the Balance Sheet but not Gross Profit.

You can build a CTD consolidation as eg

C CM_2008-04_CTD
CM_2008-04
CM_2008-03_CTD

If you chain each item, reusing the prior one then you can show the cumulative position to any point in time.

If your question is about effective vs posting dates, then two time dimensions is the answer. I used to have cubes in insurance with 5 time dimensions. TM1 coped very well.

Regards


Paul Simon