YTD in Discrete Time Dimension

[Rams]

Hi,
I have a scenario where YTDs is not fitting in the existing time dimension(Discrete), say in my existing system has YEAR and MONTH dimension separately like below,

Time Dim.PNG (9.5 KiB) Viewed 7614 times

Now we got something to enhance by creating new cubes for cash flow, so only way is to stick with existing dimensions, in this above scenario, YTD is not possible for eg

YTD.PNG (19.14 KiB) Viewed 7614 times

I can choose 2013 YTD and “All months” instead of 09 YTD, that gives me the right value, but in some places we need to use 2013-02 YTD, 2013-03 YTD etc., to do some calc for interest.
Any suggestion please let me know. Thanks in advance

[rmackenzie]

The problem is typically solved by having an 'opening balance' element in the Month dimension and ruling in the prior full year closing balance into this element where you need rolling balances. You can then report on 2013 in the Year dimension and 09 YTD in the Month dimension, where the YTD consolidation includes the opening balance elements e.g. OB. Some people prefer to call this a life-to-date (LTD) or inception-to-date (ITD) position.

You say you are 'creating new cubes for cash flow' so what is stopping you from having a continuous time dimension?

[Rams]

You are legend!! I have created similar thing but haven't think to add this in '09 YTD' i.e all months YTD by default. Thanks a lot.

Reason for not going to continuous time dimension,
1. The existing system already has discrete time dimensions as YEAR and MONTH, all cubes (including GL) has been loaded against these dimensions, so I just want to stick with these dimension to create my new cash flow cube, moreover I'm pulling some values from GL as well.

2. If I plan to do that change(i.e from 2 dims to 1 dim), then huge piece of effort requires to do modification in all Load TIs & cubes.

[David Usherwood]

There's nothing stopping you having both a single time dimension and a pair (month/year) of time dimensions in the same server and using rules/feeders to link or TI to transfer the content. For modelling apps I often do the work against a flat period dimension and top and tail it with cubes using month and year. Of course there is redundancy but the benefits can outweigh the cost.

[Rams]

Hi rmackenzie,

Just realised, one of my scenario will not fit in for creating "opening balance" in all months YTDs, say for example in below scenario. I have to get cost to date(CTD) spent on 2012-08 YTD for calculating some interest,

CTD.PNG (16.17 KiB) Viewed 7474 times

So If I use Opening balance it gives me the below but not the split of each month YTDs.

Suggested Scenario.PNG (13.7 KiB) Viewed 7474 times

I assume this is not possible, Any other suggestions please. In worst case scenario I'll try to do all YTDs in spreadsheet instead of cube.

[rmackenzie]

You could create life-to-date consolidations (e.g. Mar LTD = OB, Jan, Feb and Mar) and year-to-date consolidations (e.g. Mar YTD = Jan, Feb, Mar). Then you can use whatever is appropriate for your report or calculation where you need to include/ exclude the opening balance. Doing any sort of consolidation in the spreadsheet is very sub-optimal.

[Rams]

Hi David,

Just curious about your suggestion on adding another time dim using rule/feeders to link, I haven't done this before. So just trying to understand here, Say for eg., existing system has a GL cube with below dims,

Existing GL
Year
Month
Cost Centre
Account
Scenario
GL Measures

New Design
Time
Cost Centre
Account
Scenario
GL Measures

If I'm right, I presume I have to recreate a new GL cube, dump all history data, load it in new one( i.e., modifying the process as well). Probably I have to do this for all cubes in the system. Could you please throw me some more lights on linking 2 dims to 1 dim for better understanding,because I'm excited to do this .

[lotsaram]

Hi David,

Just curious about your suggestion on adding another time dim using rule/feeders to link, I haven't done this before. So just trying to understand here, Say for eg., existing system has a GL cube with below dims,

Existing GL
Year
Month
Cost Centre
Account
Scenario
GL Measures

New Design
Time
Cost Centre
Account
Scenario
GL Measures

If I'm right, I presume I have to recreate a new GL cube, dump all history data, load it in new one( i.e., modifying the process as well). Probably I have to do this for all cubes in the system. Could you please throw me some more lights on linking 2 dims to 1 dim for better understanding,because I'm excited to do this .

I don't see why you would think you need to dump data from existing cubes to new cubes using the discrete time dimension. You can create a join with rules and feeders relatively simply. For example:
In "new design"

Code: Select all

['GL Measures'] = N: DB( 'Existing GL', SubSt( !Time, 1, 4 ), SubSt( !Time, 6, 2 ), !Cost Center, !Account, !Scenario, !GL Measures );

In "old design"

Code: Select all

['GL Measures'] => DB( 'NewDesign', !Year |'-'| !Month, !Cost Center, !Account, !Scenario, !GL Measures );

Get it?

However I don't see why you need a new cube with a discrete time dimension. Is the account dimension the same? Is the basis of data storage the same or different (movement vs. balances)? Perhaps you have oversimplified the problem to present it here to the point of eliminating it??

[Rams]

Get it?

Thanks lotsaram! now I get it, I'm pretty much close to the workaround to solve this issue.

However I don't see why you need a new cube with a discrete time dimension. Is the account dimension the same? Is the basis of data storage the same or different (movement vs. balances)? Perhaps you have oversimplified the problem to present it here to the point of eliminating it??

Account dim is same for both the cubes and its for capturing the data movements(cashflow), You are right I oversimplified the problem instead of putting the whole set of backgrounds, but some times it easy to explain the point where I get stuck in then a brief description.

But You guys are awesome, If I locked down in some sort of corner, I can post for a brainstorming to get some tips to do workaround for the issue. Thanks to everyone!!