Use same dimension in multiple axes

[Kaveenga]

Use same dimension in multiple axes

Hi All,
Is there an IBM Cognos TM1 product that can use a single dimension as multiple axes in a report?

For example:

Say this is the Time dimension with a simple year hierarchy.

All
2008
2008-Jan
2008-Feb
2008-Mar
2008-Dec
2009
2009-Jan
2009-Feb
2009-Mar
2009-Dec

Using different subsets of the above dimension I would like to know whether its possible to create a report like the one below?

Same dim different subsets.jpg (48.11 KiB) Viewed 6045 times

I knew this is possible with SSAS cubes and I have created reports like this with SSRS and seen it more 'beautifully' demonstrated by Calumo. However, I thought this was not possible for reports based on TM1 cubes, a belief embedded by an TM1 EV demo presenter 3 years ago who said that its not possible, and not been able to do so in TM1's cube viewer (9.1). This belief was shaken yesterday when I was shown an indepth presentation of Cubeware's Cockpit 6 reporting off TM1 cubes.

Using Cockpit they could do this:

Same dim different subsets 2.jpg (26.08 KiB) Viewed 6045 times

Whereas in traditional TM1 Reporting tools, we would need 3 dimensions (Year, Month, Week) - Cockpit didn't. The period dimension went down to days, days rolling up to months, weeks, day of week, etc, and all these hierachies could be used as different axes using Cockpit.

Now coming back to my original question, if Cockpit can do that with TM1 cubes, is there a IBM Cognos TM1 product that can do the same?

Would appreciate your feedback.

Many thanks.

Kaveenga

[Alan Kirk]

Is there an IBM Cognos TM1 product that can use a single dimension as multiple axes in a report?

For example:

Say this is the Time dimension with a simple year hierarchy.

All
2008
2008-Jan
2008-Feb
2008-Mar
2008-Dec
2009
2009-Jan
2009-Feb
2009-Mar
2009-Dec

Using different subsets of the above dimension I would like to know whether its possible to create a report like the one below?

Not only is is it not possible, I can't see any earthly benefit in doing that. Because...

Whereas in traditional TM1 Reporting tools, we would need 3 dimensions (Year, Month, Week) - Cockpit didn't. The period dimension went down to days, days rolling up to months, weeks, day of week, etc, and all these hierachies could be used as different axes using Cockpit.

... you can create a dimension which is exactly the same in TM1. Days are the N level which consolidate to weeks at level 1, to months at level 2 or whatever. This of course brings us back to the auld discussion of 1 time dimension vs 2 time dimensions which isn't germane here. The point is that you can structure your time dimension however you want, and for the purposes of this illustration you appear to want it in a single dimension.

Think about what the purpose of the title element in your illustrations would be; it's to restrict the returned data set to values which fall into that year.

However by choosing a column subset consisting of elements which are descendants of the "2008" year consolidation, that already limits the data to the 2008 year anyway. Having "2008" selected in the title area is therefore redundant.

If you just want it up there for display purposes in a Websheet or an Excel report, it's easy enough use formulas to determine the year(s) that your column subset elements relate to and have that / those displayed, which is I suspect what you may have seen demonstrated. But that doesn't mean that the title year plays any part in selecting the data because there's absolutely no need for it to do so.

[Kaveenga]

Hi Alan,

Thank you for your reply. Basically you are reaffirming that TM1's reporting tools cannot do this and the only solution is to use mulitple dimensions.

Not only is is it not possible, I can't see any earthly benefit in doing that. Because...

I can think of many in addition to my earlier time dimension example

For example, a dimension with responsibility centres as elements may have 2 hierarchies, one with RCs grouped as Operational or Exceptional, and another with RCs grouped by Division. A report can then be created which cross tabulates the operational vs exceptional spend by divisions. However, since this is not possible from current TM1 reporting tools, we have to use 2 dimensions.

... you can create a dimension which is exactly the same in TM1. Days are the N level which consolidate to weeks at level 1, to months at level 2 or whatever. This of course brings us back to the auld discussion of 1 time dimension vs 2 time dimensions which isn't germane here. The point is that you can structure your time dimension however you want, and for the purposes of this illustration you appear to want it in a single dimension.

Wouldn't it be easier to have a single time dimension and then be able to use that same time dimension as mulitple axes? Would make time based rules so much easier, instead of having to deal with multiple time dimensions!

Think about what the purpose of the title element in your illustrations would be; it's to restrict the returned data set to values which fall into that year.

Actually, the title element filtered the column headings to only those elements which roll up to it. The data set is then restricted by the col headings, atleast this is what I think happened in the demo! The same effect can be done by creating a SUBSET for each year, and allowing users to select the SUBSET as a pseudo title element, and then using SUBNM's to derive the col heading could work in Excel reports, but this still stops short of being able to slice and dice data using different hierarchies of the same dim.

Cheers

Kaveenga

[Marcus Scherer]

Kaveenga,

from an MDX query point of view you can't put members of one dimension on more than one axis. So I wonder how you did with SSAS/SSRS. -> You may have used "role playing dimensions". You may show us an example. The MDX for the Cockpit query would be interesting, too.

[Michel Zijlema]

The MDX for the Cockpit query would be interesting, too.

Hi Marcus,

I don't think this was (fully) done using MDX. Cockpit has a nice feature called 'range layout', which can be used to combine multiple grids (different rows and/or columns) into a single view. I assume this was used here.

Michel

[lotsaram]

Not only is is it not possible, I can't see any earthly benefit in doing that. Because...
... you can create a dimension which is exactly the same in TM1. Days are the N level which consolidate to weeks at level 1, to months at level 2 or whatever. This of course brings us back to the auld discussion of 1 time dimension vs 2 time dimensions which isn't germane here. The point is that you can structure your time dimension however you want, and for the purposes of this illustration you appear to want it in a single dimension.

Yes, but imagine Alan if you didn't have to create a cube with multiple time dimensions in order to be able to lay out say years on rows and months on columns. I think this would be quite neat, in fact it would be great functionality! (Of course there are other reasons you might want multiple time dimensions apart from slice and dice such as ease of spreading from one year to the next for budgeting.)

I don't have much experience with SSAS but I do recall seeing SSAS cube interfaces where this happened natively. Eg. a cube with a single time dimension of months rolling to years. The view was constructed with year as a filter (like a title element) but then months across the columns. Of course it's relatively easy to set up a TM1 based report to do this in Excel or TM1 web with a few simple cell formulas, but being able to achieve the same result in the cube viewer would be a nice feature IMO. (Although how the MDX would work I don't know, probably end up splicing multiple views together.)

[Alan Kirk]

Not only is is it not possible, I can't see any earthly benefit in doing that. Because...
... you can create a dimension which is exactly the same in TM1. Days are the N level which consolidate to weeks at level 1, to months at level 2 or whatever. This of course brings us back to the auld discussion of 1 time dimension vs 2 time dimensions which isn't germane here. The point is that you can structure your time dimension however you want, and for the purposes of this illustration you appear to want it in a single dimension.

Yes, but imagine Alan if you didn't have to create a cube with multiple time dimensions in order to be able to lay out say years on rows and months on columns. I think this would be quite neat,

I can certainly imagine that, but that's rather different to the original question and the illustrations therein which consisted of nothing more than redundant double filtering. It's pointless having a filter in the title area if you're already filtering by columns and/or rows.

Having some kind of cross-tabulation as you're describing here is a completely different question. Though I'd agree that such functionality in the situation that you're describing wouldn't be unwelcome.