Page 1 of 1
YTD in Discrete Time Dimension
Posted: Tue Sep 03, 2013 12:39 am
by 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 7618 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 7618 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
Re: YTD in Discrete Time Dimension
Posted: Tue Sep 03, 2013 2:12 am
by 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?
Re: YTD in Discrete Time Dimension
Posted: Tue Sep 03, 2013 4:31 am
by 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.
Re: YTD in Discrete Time Dimension
Posted: Tue Sep 03, 2013 8:50 am
by 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.
Re: YTD in Discrete Time Dimension
Posted: Wed Sep 04, 2013 12:47 am
by 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 7478 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 7478 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.
Re: YTD in Discrete Time Dimension
Posted: Wed Sep 04, 2013 12:55 am
by 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.
Re: YTD in Discrete Time Dimension
Posted: Wed Sep 04, 2013 1:09 am
by 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
.
Re: YTD in Discrete Time Dimension
Posted: Wed Sep 04, 2013 9:31 am
by lotsaram
Rams wrote: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??
Re: YTD in Discrete Time Dimension
Posted: Thu Sep 05, 2013 11:59 pm
by 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!!