Dynamic subset creation using TI

[rmjohnston]

We have created many many subsets to provide alternate roll-up of locations not identified in the location dimension. Currently the subsets use dynamic MDX and are based on attribute values. However, when a new attribute value is created, I have to manually add a new subset and manually add the new subset to the UDC.
For example: "Customer" is an attribute. And some customer attributes values are "Rhonda" and another is "Bruce". The locations codes with the 'Customer" attribute value of "Rhonda" are ABC, DEF and locations GHI, JKL have the "Customer" attribute "Bruce". So subset "Customer - Rhonda" = ABC, DEF and subset "Customer - Kwan" = GHI and JKL.

Code: Select all

 {FILTER( {TM1FILTERBYLEVEL( {TM1SubsetBasis()}, 1)}, [MLOC].[CUSTOMER] = "Rhonda")}

provides ABC and DEF.

What I would like to be able to do is have TI do two things :
1) dynamically create subsets for each "Customer" attribute value, so when a new "Customer" attribute "Sue" is created a new subset "Customer - Sue" is automatically created. Subset "Customer - Sue" would contain the locations with the "Customer" attribute = to "Sue" say MNO & PQR.
2) dynamically create a UDC containing all the subsets for the "Customer" attribute, name it "Customer - ALL" and it would contain all "Customer - ???' subsets, for example "Customer - Rhonda", "Customer - Burce" and "Customer - Sue".

I am using office 2007 and am using TM1 9.5.1.

Am I asking too much? I've been told that is typical of me
Thank you for your time.

[Gregor Koch]

Hi

1)
That's fairly straight forward depending on how "dynamically" you need to create the subsets and what you define as a UDC. Unfortunately you cannot trigger the process automatically as soon as someone enters/changes a value for the attribute. But obviously you can manually execute a process, schedule it overnight or run it, let's say, every hour or x minutes (handle with care because of possible locking, not really recommended unless absolutely necessary) to check for new values in the attribute.
Leaving that alone you can write a process that has a subset of the dimension as source and do a

SubsetExists(dimname, subname)

where subname is the value of the attribute of each element and then do a SubsetCreateByMDX.

But why not just create consolidations because...

2)
As far as I know and I am quite sure, you cannot create UserDefinedConsolidations (btw, they are always private) in TI. You probably noticed that you can only create them when you accessed the Subset Editor ("Roll Up") from within a cube view. At least that is my definition of a UDC.
Even you are referring to the 'Insert Subset' function that is always available in Subset Editor, this is not available in TI. If you try to insert a subset into a subset in TI this will just fail with 'element not found' (at least in 9.4).
Not all is lost as the whole lot can be done with normal consolidations which you can (re-) create in your TI process.

Even if you need the "Customer All" only for navigation purposes I guess the consolidations are the only dynamic way of (kinda) solving what you are looking for.

Cheers

[Andy Key]

You should also look at what TM1SubsetBasis actually does, and realise that dynamic subsets using this won't always give you the same result. Replace it with TM1SubsetAll for consistent results.