MDX

Duplicate Elements in Rollups

Large nested rollups can lead to elements being counted twice or more within a rollup.
The following process helps you to find, across all dimensions of your system, all rollups that contain elements consolidated more than once under the rollup.

The code isn't the cleanest and there are probably better methods to achieve the same result so don't hesitate to edit the page or comment.

It proceeds like this:

Comments

Comments allow you to explain, to yourself and/or to your users, what the query is trying to achieve, how it works, who wrote it or amended, etc.

Use "//" or "—" (without the double quotes) to end a line with a comment or to have the comment on its own line.

You can also use “/* COMMENT */” (again without the quotes) to insert a comment in the middle of a
line. You are also able to type anything after the command.

Count and IIF

Caveat: Note that IIF is not listed in the TM1 v9.0 SP2 help file as being supported so use at your own risk.

Count returns the number of items in a set but this set can be a set of members or a set of data values. The result is, obviously, a number and is often returned in reports when used in MDX queries outside of TM1. When trying to use it do define a TM1 subset it can only be used as part of the query logic and not as a result itself.

Generate

The Generate function applies a second set to each member of a first set, performing a union of the results. Duplicates are dropped by default but can be retained with “,ALL”.
Although Generate doesn’t really do anything unique in itself it is a very useful way of shortening what would otherwise be long, laborious and error-prone queries.

Data-based queries, Filter, Sum, Avg and Stdev

Sometimes it is not adequate to simply use a single value in a query; you need to consider a combination of values. It might be that this combination is only needed for one or two queries, though, so it is not desirable to calculate and store the result in the cube for all to see. Therefore it is more logical to quickly calculate the result on the fly and although this is then repeated every time the subset is used, it is still the preferred choice.

Username and StrToMember

It returns the TM1 username (or Windows domain username depending on the security system being used – for example, “GER\JEREMY”) of the user who runs the query. Note that you may need to give all users Read access to the }Clients dimension and all its elements.

Using TM1 Subsets, TM1Member and TM1SubsetToSet

One of the special features of using MDX with TM1 dimensions is that existing subsets can be used within the query for defining a new subset. This can be useful in allowing a simpler ‘building block’ approach and for not having to repeat the same code over and over again – and having to maintain it.

Used throughout this section, [Report Date] is an existing subset in the Date dimension containing one leaf date member and [test2] is an existing 20-member subset.

ToggleDrillState

ToggleDrillState changes the default drill state from a returned set – so if the first query returns a member in a hierarchy rolled up then it will drill it down, or vice versa.

For example,
{[Product].[Customer Lending].Children}
Returns:

Subset Product Children