Consolidations based on Subsets

[Steve Rowe]

Firstly just to make people aware that this functionality exists, I did not know of it (or did and have forgotten, which is more likely)and it has just caused someone a massive problem and not a little embarrassment.

My time dimension is years and periods and I have a subset called 2008 that holds the “Yr 2008” consolidation and all of it’s children.
So I have the consolidation “Yr 2008” and the subset “2008”.
If a user creates an excel report that references the annual total and in error puts “2008” rather than “Yr 2008” the report will double count the result rather than generate a key error.
The reason I get a double count is that TM1 has in the background created a consolidation named "2008" that contains all the elements that the subset "2008" does. Since the subset contains the consolidation and all the children, the result is double counted.
This consolidation does not show up in the dimension and dimix on "2008" will return zero. If however I paste 2008 into the dimension I can see the consolidation in the subset editor.

I think this is a very bad piece of functionality....
It represents a massive loss in control in the way a dimension consolidates. If someone has rights to create a public subset then they can permanently add consolidations into the dimension, you would normally need the perspectives licence to do this.

They are mixing structural information about the way information should be displayed and the way information should be consolidated. The only references that should work as references for spreadsheets are elements that are explicitly set up in the dimension and not any random subsets names as well.

Given the tendency to create subsets with name that are very similar to element names the opportunity for confusion is massive.

There is already the “roll up” functionality in the subset editor that can be used to produce on the fly consolidations.

I know I can correct this by deleting and renaming subsets to something more obscure, but I have three systems to do this on and I have delete every single view that references one of these subsets. As the problem is in the period dimension this is going to be very very horrible....

The second thing I wanted to do was set a poll on this to see what everyone else thinks. Guess which way I'm going to vote!

[Eric]

Have not had that problem....yet. I know it will happen soon. I feel static subsets are dangerous in general. To many users have created static subsets and do not know why they don't updated when new elements are created....

Dynamic subsets are good in my mind, but with what you bring up there are more than just static subsets.

Maybe a prefix should be used for subsets would help with this?


Sidebar
Part of your problem is you are using time as a linear dimension. In my effort to tell everyone I am right breaking months and year into multiple dimensions you would not have this problem.
<Putting on boxing gloves to duke it out if needed>

I know I am in the minority in this belief, but I have seen a lot of benefits from this style of handling years and months separately instead of joined.

[Simon Bradshaw]

All our dynamic subsets are prefixed MDX_
Clients are reminded through a monthly development forum the benefits of using dynamic subsets.
They are encouraged to ask if a dynamic subset is possible before creating stand alone subsets.
Response is good as they know someone else will create the subset and it never needs changing.
It is unlikely in most views to pick a subset that is not MDX_ apart from single elements.

You are right about having a seperate month and year.
In a past life this was how I worked in MIS ALEA [we couldn't afford tm1].
Sadly though the tm1 model I inherited was already mmm-yyyy when I took over.

[Martin Ryan]

I finally found a useful use for this functionality - rolling consolidations. If you have a one dimensional approach to time, you may still often wish to have a rolling 12 months consolidation. I've found this in an insurance forecasting model I'm building.

If you set up a dynamic subset that is based on an attribute or lookup cube you can have the latest 12 months in there, you can then use the subset name as a consolidation in another subset.

I'm not quite saying that I think this bit of functionality is still worth having around (I can think of at least one other way to solve my rolling 12 months issue), I'm just saying that with this thread being over two years old, I can at last see some kind of point.

Martin