Design question – Dynamic Relative Time Dimension

[LanceTylor]

Hi All,

I am currently designing a TM1 model (in Cognos Express 10.1) for Income Statement Reporting (Only actuals at this stage). Key requirements are:
1. Ability to present IS using “Current Month, Current YTD, Fiscal YTD, Current Year Last 12 Months, Prior Year Last 12 months
2. Ability for users to select the different current month’s (driving the relative time periods above) at the same time (User 1 selects Aug-09, User 2 – selects Sep-09)
3. Ability to present this in a BI Report and select the current month in a prompt

*Point 2 is still up for discussion as I need to determine if it can be done
*Point 3 might have to persuade them to switch licenses from Reporter to Xcelarator if not achievable or only the current month can be chosen

Further below is how I am planning on designing the cubes (there will be more dimensions – this is just for illustration).
1. Data is loaded into the IS Cube. This cube is a staging cube and will be used for analysis (slice and dice).
2. The TimePeriod contains all the attributes for the relativetimeperiod (current month, prior month, current year last 12months—Previous Month, PreviousMonth -1,PreviousMonth -2 etc….)
3. Using Rules in the IS Relative Cube based on the “Control Cube” and TimePeriod attributes I am able to capture all of the relative time periods sourcing the data from the IS Cube

My questions are:
1. Is there another approach or additional logic I can build in order to make the selection of the current month more dynamic giving users the ability to view different “Current Months” at the same time?
a.With my current approach I can create another “Current Month & Year” element in the Control Cube and either have another cube as a source for the report or additional elements in the RelativeTimePeriod in the IS Relative Cube (Ex: Current Month, Current Month 1 etc….)

2. TM1 Perspectives can handle this dynamic reporting and interaction (choosing the Current Month) but has anyone been able to achieve this in a BI Report. More specifically, using a prompt to select the current month?

IS cube
ChartOfAccounts
TimePeriod (Months -> Quarters –> Years -> TOTAL Years)
Measures ($)

Control Cube
Variable (Current Month, Current Year)
Variable_M

IS Relative Cube
ChartOfAccounts
RelativeTimePeriod (Current Month, Prior Month, Current YTD, Current Year Last 12 months, Prior Year Last 12 months)
Measures ($)


As always, you feedback is really appreciated

Regards
Lance

[tomok]

The idea of creating user-specific rollups and rules to dynamically populate these cubes is insane. Why do all this when you can just configure the time dimension with all the necessary rollups and just use the control cube to translate the user's definition of "current month" to the real calendar dates and periods. I would never even dream of attempting your setup.

[rmackenzie]

Hi Lance,

No-one does income statement reporting in the way you are describing, which to be honest, sounds like you are setting yourself up to have a nightmare both technically and professionally.

Almost everyone will approach this reporting model with two options for time - either separate year and month dimensions, or a continuous time dimension. With either option you can define consolidations that work for YTD, rolling 12 months, etc. With separate year and month dimensions you may need YTG (year to go) dimensions to get rolling totals.

With a cube with these time dimensions, User 1 can select Nov (and Nov YTD, and Dec YTD, Nov 12 months rolling and Nov 12 months rolling for PY) and see their results. Simultaneously, User 2 can do the same thing with Jul (and Jul YTD etc etc). You need to kind of drop the idea of 'current month' because your second key requirement is obviously saying that different users want to report from different time periods:

2. Ability for users to select the different current month’s (driving the relative time periods above) at the same time (User 1 selects Aug-09, User 2 – selects Sep-09)

So, you need to work out your static and fixed time dimensions first and let it be that it is the user selections that are dynamic and relative to one another!

Have you actually seen a time dimension before? E.g. for 'Month' it may have Jan, Feb, Mar at n-level and Mar YTD as a consolidation of Jan, Feb, Mar. If you're not sure then you're going to need to get some examples to work through. Even the documentation and sample data that ships with TM1 will have some examples. Searching for 'time dimensions' on this forum will get you lots of information too.

Getting your time dimensions right is not specific to TM1 and if you look at other OLAP technology you will see that modelling time is a key design point to resolve before you actually get stuck into building reports.

HTH
Robin

[lotsaram]

Further below is how I am planning on designing the cubes (there will be more dimensions – this is just for illustration).
1. Data is loaded into the IS Cube. This cube is a staging cube and will be used for analysis (slice and dice).
2. The TimePeriod contains all the attributes for the relativetimeperiod (current month, prior month, current year last 12months—Previous Month, PreviousMonth -1,PreviousMonth -2 etc….)
3. Using Rules in the IS Relative Cube based on the “Control Cube” and TimePeriod attributes I am able to capture all of the relative time periods sourcing the data from the IS Cube

My questions are:
1. Is there another approach or additional logic I can build in order to make the selection of the current month more dynamic giving users the ability to view different “Current Months” at the same time?
a.With my current approach I can create another “Current Month & Year” element in the Control Cube and either have another cube as a source for the report or additional elements in the RelativeTimePeriod in the IS Relative Cube (Ex: Current Month, Current Month 1 etc….)

I agree with Tom that having user specific current month values in a control cube or use specific time rollups would be insane and insanely bad design. I disagree with Robin that no-one does income statement reporting with relative time offsets. We do it and it works very well and adds a lot of utility for users in analysis and reporting.

The best way to make it work though is not to track the current month in a cube or in fact have any kind of control cube for this. (It may still be useful to have a "current month" value in a system control cube but you certainly don't need it for the kind of dynamic time period offset reporting that I think you are after.) All you need is an additional dimension in the cube that I'll just call "Lookup Measure" which handles all the time period offsets and variance calculations, (in our case it also handles version variance calculations as well.) It looks something like this:

Lookup Measure

Lookup_M.png (11.28 KiB) Viewed 15730 times

The dimension has only one element where data is loaded to, called "input value", "period value" or in this case just "period". All other elements are calculated. The dimension is structured such that all other elements are parents of the input value as this avoids any requirement to feed the calculations.

Lookup_M_detail.png (989 Bytes) Viewed 15730 times

Note there is element security on the dimension such that users see all elements EXCEPT "user invisible". This avoids users being able to drill down on the parent. (You can also give a format for the element of "b:;;" to suppress display of any numbers as well.)
Of course there are still month and version dimensions in the cube.

Month_&_Version.png (9.42 KiB) Viewed 15730 times

With the lookup measure dimension and rules in place you can even choose to use element security to hide the month rollups from end users to make the interface more streamlined. (I tend not to as this can be dealt with via training and the month dimension tends to be used in other places as well where direct access to YTD and YTG rollups is useful for users.)

How the time offset works is that basically if "Period Value" is selected in the Lookup Measure dimension then there are no lookup rules applied, everything is "as is" with reference to the currently selected element in both the Month and Version dimension (that is this is equivalent to a "standard cube" without the extra lookup measure dimension). However as soon as a lookup measure calculation element is selected then the value displayed references and offsets based on the selected month (or version). So it is all absolutely dynamic in the crosstab. works brilliantly in all native TM1 interfaces and EV, I can't see any reason why it wouldn't also work in the Cognos Studios (usual watchouts with element security which seems problematic with MDX hierarchy traverses). All the offset calculations take a bit of setting up but it is largely a once off exercise as they are fairly portable (provided you make good use of attributed in the time and version dimensions and have something like a "zDates" cube to leverage in the rules.)

As there's a limit of 3 attachments I'll post an example below.

[lotsaram]

Here's one of possible very many examples of how it works in a cross-tab:

TimeOffsetRedacted.png (17.07 KiB) Viewed 15730 times

I initially thought to blur the numbers but it defeated the purpose of the example as an illustration of the technique so the figures are scrambled from reality instead but still serve the purpose of being able to follow the calculation and reporting logic. (If 2011 was selected for the year filter not 2012 then you would of course see all the "LY" values reflected in the "Period" and "current year" time offsets.)

Also a point I didn't emphasize in answering Lance's question but in case it isn't obvious it isn't a matter of having a special "Relative Last 12 months" time dimension or having 2 month dimensions in the cube or even having a separate "Relative cube". You use just one cube with the same year and month dimensions as always, it is just the inclusion of the additional "lookup measure" dimension that makes all the relative, offset and variance reporting happen.

[qml]

The dimension is structured such that all other elements are parents of the input value as this avoids any requirement to feed the calculations.

I'm curious, if there is no input value on the Period measure for a given month (say, you only sell that product in specific months), how do you ensure that all the cumulative measures like YTD are fed and therefore e.g. will be displayed in a zero-suppressed view?

[lotsaram]

I'm curious, if there is no input value on the Period measure for a given month (say, you only sell that product in specific months), how do you ensure that all the cumulative measures like YTD are fed and therefore e.g. will be displayed in a zero-suppressed view?

I guess a basic assumption of the design is that if the input base has no value entered then the offset reference values and comparative analysis is not relevant or not required. For example Nov 2012 actual is not yet loaded (i.e. "2012", "11", "Period" intersection is 0). Therefore if "2012", "11", "YTD" or "2012", "11", "Last Period" were queried with zero suppression on then there would be no result even though there are values at the underlying reference points in the cube "2012", "YTD 11", "Period" and "2012", "10", "Period". There's no point doing comparative variance analysis for Nov YTD vs LY Nov YTD if we only have actuals up to Oct. On the whole this assumption holds up very well. Obviously if you have a very detailed cube down to customer x product level then there will be holes in the data when you get really low down but that is to be expected and it natural with any system doing variance analysis as data always gets dirty or choppy when you get really detailed. But the other thing is to remember that the consolidation only acts as a virtual feeder, if the query is executed without zero suppression then the correct value is still pulled. For reporting purposes most if not all of the time numbers will be pulled in a formatted report with "static" rows and columns (whether that is Excel or Report Studio) so the zero suppression is moot anyway.

The other thing this does is incorporate all sorts of variance analysis internally within the cube itself (without any feeder overhead) as opposed to additional calculations in the reporting interface. This opens up many possibilities in Excel and TM1 Web to pass MDX in active forms to query the cube based on filters like "show me all units with growth on LY of +5% or greater". Really opens up a lot of possibilities.

[qml]

Lotsa, don't get me wrong, I actually like this setup a lot and can instantly imagine many nice things you can do with it quite easily. My only concern is the fact that its feederlessness (ha!) can be viewed both as a pro and as a con. It's definitely a con in the scenario where you want to look at cumulative data at a relatively low level and your periodic figures have holes in them and you want/need to use zero suppression. This is probably not an extremely frequent use case, but it does mean your cube has a slight flaw in it that can cause numbers to disappear from users' views in specific circumstances. This doesn't have to be a problem, but it can be.

[lotsaram]

This is a valid point but goes to a more fundamental design issue about feeders, over vs. (deliberate) underfeeding and "feederless" calculations versus the question of the original OP about how to do relative time lookups. If we go any deeper it probably deserves another thread ore even use of "the lounge".

My only concern is the fact that its feederlessness (ha!) can be viewed both as a pro and as a con. It's definitely a con in the scenario where you want to look at cumulative data at a relatively low level and your periodic figures have holes in them and you want/need to use zero suppression. This is probably not an extremely frequent use case, but it does mean your cube has a slight flaw in it that can cause numbers to disappear from users' views in specific circumstances. This doesn't have to be a problem, but it can be.

Yeah I don't disagree or deny it is a potential issue but in any "traditional" variance type measure with a feeder there are always 2 sides that you can feed from. In the case of actual vs. budget or actual vs. last year I'm sure most of us would say it is only relevant to do the comparison once we have current year actuals for the month in question as to feed the calculation from budget or LY in addition to from actual would result in overfeeding. At very granular level of detail in the cube though some zeros for current year actual for actualized months (where there is a non-zero value for budget or LY) will be valid and this choice to avoid overfeeding will result in exactly the same "underfed or not fed" scenario you describe. However I think that (most?!) people would happily live with this and prefer it to the alternative of overfeeding. Worth mentioning also that such underfeeding in the "traditional" or "standard" N level rule with feeder approach will retain the hole or error in the measure as you aggregate up, whereas using a consolidated measure will "auto correct" once at a level where ther is underlying data as the variance calculation is done fresh from the relevant level and variance is not aggregated from the base. Of course there is also a well reasoned school of thought that variance calculations never ever need to be fed anyway and should not be fed unless there's a really, really good reason (like feeding a further downstream calculation maybe ..)

Overall I really like the "use a consolidation as a virtual feeder" approach* as it gives the benefit of still appearing in the grid with zero suppression on (as qml has pointed out not 100% of the time but 90-something % of the time) but without any of the performance overhead or mental gymnastics of feeders. The approach is not for universal use but for any ratio, variance or offset calculation it is really in its element.

* I should add for any calculation that doesn't require further consolidation (as ConsolidateChildren is a real PITA for a good many reasons)

[LanceTylor]

Hi All,

I really appreciate your input and examples provided! It seems the survey says "I or my initial design attempt is insane".....its a good thing it was only a test and everyone passed.

Thanks again for taking the time to respond, I would like to think that a lot of TM1 developers/designers out there will find this thread quite valuable.

Regards
Lance

[paulsimon]

Hi Lance

I have done this, and I didn't need any relative time dimensions. I just used regular time dimensions with base level of eg 2012-M01, 2012-M02 etc. Where M02 is February

Part of the key is the time dimension generator that I use. It allows you to define a hierarchy that has different consolidations for Calendar and Fiscal years. As well as current month it has YTD, CTD, etc.

The next trick is in Cognos BI where you can have prompts for the month and the type of month eg Month level, YTD, etc, and in your case you would also need prompts for Calendar or Fiscal year. You could potentially have more than one Fiscal Year, eg Year starting in April and July.

You then use macro expressions in Cognos BI to match against elements under the appropriate consolidation, eg Fiscal Year starting in April YTD consolidations. This gives you the period consolidation that you need.

For the Fiscal year you need to select the appropriate Alias, so that eg CL_2012-M04 appears as F4_2012-P01, ie Calendar Period 2012 Month 04 appears as Fiscal Year Starting in April Period P01.

You can do that via Member Caption.

However, to echo what others are saying in practice, we also introduced the ability to select a default period, which was translated into the current month behind the scenes, and most reports ran on that, ie people generally only want to report on the current month.

Regards

Paul Simon

[LanceTylor]

Hi Paul, Thanks for your response. I am going to look into the Macro expression.

With your suggestion how would you go about capturing the rolling Last 12 months? I presume you would create all the consolidations/rollups for a specific year (2012_M1_LTM, 2012_M2_LTM etc….) and use the macro expression. I can see the use of the single time dimension having quite a few elements.

Lotsa,

I have started testing out your suggestion and it works great. I just had one question
1.What rolls up to the YTG and how is it used? (This is the first time I have heard of this)

Again, just wanted to say thank you for everyone’s help!!

Lance

[lotsaram]

What rolls up to the YTG and how is it used? (This is the first time I have heard of this)

"YTG" Is short for "Year to Go", it is the inverse of "Year to Date" and essential to any planning model and any situation with split Year and month dimensions where moving annual total (MAT) calculations are required.

For MAT having YTG in addition to YTD makes the calculation simple:
[MAT] = DB('cube',...,DBRA('Year', !Year, 'Prev Year'), !Month | 'YTG', ...) + DB('cube',...,!Year, !Month | 'YTD', ...);
(clearly fill in your own case specific correct rule syntax).
(The number of times I've audited a model to find 12 separately handcrafted MAT rules one for each month, each with 12 separate additions I couldn't tell you but I've certainly lost count ... )

YTG is also immensely useful for planning applications to allow spreading over the remaining months of the year which are still forecast.

What to build in the rollups should be obvious but in case not.

YTG.png (5.41 KiB) Viewed 15578 times

You can nest YTD and YTG consolidations such that each only has 2 children. I am NOT a fan of this approach,1/ because it violates the principle of each dimension level within a given hierarchy having a consistent definition of what it is, but mainly 2/ because it creates clutter and is not as easy and non-intuitive for users to navigate.

(point 1 ("normalized" or "balanced" dimensions) is of course nonsense for "business oriented" dimensions like chart of account, division, legal entity, etc where ragged or unbalanced hierarchies simply make sense as the concept of "level" is meaningless, but for dimensions like time and often SKU or product it often makes a lot of sense.)

[LanceTylor]

Thanks for quick response Lotasram. Your example makes perfect sense and minimizes the rules and maintenance of the model.

This is definitely the way I am going to implement TM1 Time dimensions from this point onward (where applicable). I am officially in the 2 time dimension camp.

Regards
Lance

[rmackenzie]

Just gone back and re-read over this thread... Lotsa - great design and coverage over possible requirements. Certainly if you started off down a road by including Prior Period as a measure you'd soon come unstuck and need this sort of implementation. Good luck Lance!