Balance Sheet for Transactional System

Post Reply
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Balance Sheet for Transactional System

Post by 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
Posts: 58
Joined: Fri Jun 13, 2008 4:01 pm

Re: Balance Sheet for Transactional System

Post by 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.
User avatar
stephen waters
MVP
Posts: 324
Joined: Mon Jun 30, 2008 12:59 pm
OLAP Product: TM1
Version: 10_2_2
Excel Version: Excel 2010

Re: Balance Sheet for Transactional System

Post by 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
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Re: Balance Sheet for Transactional System

Post by 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
Posts: 58
Joined: Fri Jun 13, 2008 4:01 pm

Re: Balance Sheet for Transactional System

Post by Mike L »

cdhodge2002 wrote: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.
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: Balance Sheet for Transactional System

Post by 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.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Balance Sheet for Transactional System

Post by Alan Kirk »

cdhodge2002 wrote: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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Balance Sheet for Transactional System

Post by 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
Post Reply