I had a bug in a TM1 application recently that was very difficult to track down, and it was because I didn’t understand the different ways that TM1 might respond to vague or incorrect MDX expressions. MDX makes some unexpected assumptions.
Take the following simplified example cube:
Let’s say that I have an active form report and I want to filter on customers with net sales greater than or equal to $600, across all product lines.
If I create a customer subset of:
{ FILTER ( {TM1SUBSETALL( [Customer] )} , ( Sales.( [Product].[All Products], [m_Measures].[Net] )>= 600 ) ) }
it correctly returns:
GM Tesla
The TI process had a typo that was generating the equivalent of:
{ FILTER ( {TM1SUBSETALL( [Customer] )} , ( Sales.( [Product].[All Products], [m_Measures].[Nt] )>= 600 ) ) }
So what will the result be? I would have guessed it would throw a runtime error or produce an empty subset. Instead, it returns:
Nissan Rivian
What?!
Because the interpreter doesn’t recognize the element name, it is going to the first element in the dimension! This seemed really crazy and inconsistent, but later on a colleague who was new to TM1 wrote an expression like this:
{ FILTER ( {TM1SUBSETALL( [Customer] )} , ( Sales.( [m_Measures].[Net] )>= 600 ) ) }
We didn’t notice it for a while because the expression was working fine, even though not all of the dimensions were defined. Why was it working? It was the same assumption at work as the problem above. When a dimension element wasn’t defined for the query, TM1 was just defaulting to the first element in the dimension. That assumption is probably wrong, but might be close enough that you don’t catch the error until it embarrasses you in production.
There are some nuances to this behavior. In “[m_Measures].[Nt]”, the system knows what dimension I am calling out, so it can decide what dimension to take a guess on. If I don’t specify the dimensions, this syntax will work correctly (if the element names are not ambiguous):
{ FILTER ( {TM1SUBSETALL( [Customer] )} , ( Sales.( [All Products], [Net] )>= 600 ) ) }
But this:
{ FILTER ( {TM1SUBSETALL( [Customer] )} , ( Sales.( [All Products], [Nt] )>= 600 ) ) }
will finally throw an error!
For dealing with this exact issue, it might seem better not to declare the dimensions in the query specification, but in a large model that will cause other problems. The no-dimension syntax only works if the element you are calling out is unique across all dimensions in that TM1 instance. Even if it works when you first create it, it might break when someone creates a similar dimension later, even if it is in a completely unrelated cube.
So what do you think? Have you seen other examples of this? I don’t have any MDX experience outside of TM1, but of course it is used widely elsewhere. Is this normal MDX behavior in other systems? Let me know in the comments.
