TM1 Subsets Copy Paste

I'm sure a lot of people are familiar with the copy and paste facility in the subset editor. It allows you to build a list of elements that may come from anywhere within the dimension using the find facility or reorder existing subsets.
One trick that I don't think people are aware of is the ability to copy from Excel into the subset editor.
e.g. someone sends you a list of costcentres in Excel they need a report on, you can go into your cube, open the costcentre dimension subset, delete any costcentres showing, flick into Excel and copy the costcentres, flick back into TM1 and paste into the subset editor.
This certainly is quicker to build a list of elements than going through and selecting them one by one with the find facility.
The other cool thing is that it will display any alias you have switched on too.

MDX, Dynamic Subsets and Server Crashes

I recently got asked to look at a site that was experiencing TM1 server crashes.
I had a look and found that on an MDX report that ran in Excel, it was referencing a dynamic subset. This dynamic subset was producing empty results due to changes in their structure.

Essentially whenever they ran this Excel MDX report it would crash the server instantly. As soon as I fixed the Dynamic Subset, the MDX report worked fine and the server is stable again.

I guess the lesson is to check that your dynamic subsets are never producing empty results.

Subset Cut and Paste

In the subset editor you can change the order of elements by dragging and dropping the elements.
Sometimes this can be a little awkward though and I actually find it easier to right click the element, select cut, and then use the Paste above (or below) option to get the elements in the order that I require.

Referencing Subsets in TM1 formulas...

One handy feature of TM1 is the ability to reference not only elements but also subset names in TM1 formulas.

For example, if we have the subset called "Fast Cars" (on a cars dimension), which contains the elements "Porsche" and "Ferrari," we can pass our DBRW formula the actual name of the subset (instead of the individual element names).

That is:
=DBRW("tm1server:Cars", "2007","Fast Cars", "New Zealand","Car Numbers")

Instead of:
=DBRW("tm1server:Cars", "2007","Porche", "New Zealand","Car Numbers")

This will give us a rollup of the 2007 Car numbers for both Porsche and Ferrari in New Zealand.

I have found this feature to be incredibly powerful, especially when filtering is applied to the subset based on an attribute value.

Notes:
- Be weary of referencing subsets containing both consolidated elements and their children, as values will be counted multiple times
- Subsets cannot be referenced as elements in MDX queries

Default Subsets

I prefer not to have dynamic subsets as my default subset due to the annoying alerts that pop up to users.
To get round this I create a dynamic subset and then base my default subset off it in my load process.
So in my TI process that loads the departments I place this code in the epilog:

SubsetDeleteAllElements('Product', 'Default');
x = 1;
While(x <= SubsetGetSize('Product', 'CORPPRODUCT'));
vElement = SubsetGetElementName('Product', 'CORPPRODUCT', x);
SubsetElementInsert('Product', 'Default', vElement, x);
x = x + 1;
END;

It loops round the dynamic subset called CORPRODUCT and recreates the default subset from it.
The code to produce the dymanic subset is:
{TM1DRILLDOWNMEMBER( {[PRODUCT].[CORPPRODUCT]}, ALL, RECURSIVE )}

You can exchange the name CORPPRODUCT for your top level element you want to drill down on.