TM1 MDX problem

[Guillaume Galtier]

Hi evrybody,

I'm facing a problem, trying to build dynamic subset with MDX syntax.

I want to filter elements from a dimension, and base my logical expression on cubes values.

My dimension to filter is called "PC_NRC_Pc".

I've got a cube of references containing 2 dimensions:
Cube: PC_NRC_Refs
Dim1: PC_NRC_Pc_Definition
Dim2: PC_NRC_Pc

I've got a cube of Variables containing 2 dimensions:
Cube: PC_Variables
Dim1: PC_VariableName
Dim2: PC_VariableType (2 elements: vNumber [N elt], vString [S elt])

Hereunder my query MDX:

{FILTER(
{[PC_NRC_Pc].[PC NOT VALIDATED].Children},
[PC_NRC_Refs].([PC_NRC_Pc_Definition].[Validation Month]) = [PC_Variables].([PC_VariableName].[Closing Month], [PC_VariableType].[vNumber]))
}
--> No error, but the result is false.

If I replace the right part of my logical expression by a static value, it's working fine:
{FILTER(
{[PC_NRC_Pc].[PC NOT VALIDATED].Children},
[PC_NRC_Refs].([PC_NRC_Pc_Definition].[Validation Month]) = 5)
}
--> result OK

Please, can you tell me what's wrong on my first query ?

Is existing some documentation (official or non official) on MDX for TM1 ?

Thanks in advance

Guillaume

[Steve Vincent]

I've only used filter on actual values, not values from a cube so i'm not too sure. The old Applix forums are not responding atm, there was a wealth of MDX info in there There is half the square root of naff all in the way of MDX documentation for TM1 - its a Microsoft designed code that Applix used but also made some additions / changes to. The help mentions a few bits and pieces but nothing that helps you understand how to apply them, and all Applix suggested was to read up on the Mircosoft docs and go from there

On a similar note, but hopefully easier. The code below works fine, but i want to EXCLUDE anything ending in "Latest" rather than include it. Does anyone know how to turn that around, as what i've tried so far hasn't worked.

Works but includes the pattern

Code: Select all

{TM1FILTERBYPATTERN( {BOTTOMCOUNT(TM1FILTERBYLEVEL( {TM1SUBSETALL( [PerfTime] )}, 0),120)}, "*latest")}

Doesn't compile

Code: Select all

{TM1FILTERBYPATTERN( {BOTTOMCOUNT(TM1FILTERBYLEVEL( {TM1SUBSETALL( [PerfTime] )}, 0),120)}, <>"*latest")}

Doesn't compile

Code: Select all

{TM1FILTERBYPATTERN( {BOTTOMCOUNT(TM1FILTERBYLEVEL( {TM1SUBSETALL( [PerfTime] )}, 0),120)}, ~"*latest")}

[Guillaume Galtier]

Steve, I think you have to use the EXCEPT function to exclude ...

Try this:

{EXCEPT(
{BOTTOMCOUNT(TM1FILTERBYLEVEL( {TM1SUBSETALL( [PerfTime] )}, 0),120)},
{TM1FILTERBYPATTERN( {BOTTOMCOUNT(TM1FILTERBYLEVEL( {TM1SUBSETALL( [PerfTime] )}, 0),120)}, "*latest")},
ALL )
}

Guillaume

[Steve Vincent]

Perfect, thanks!

[Eric]

Being a MDX novice I am not sure, but lets take a shot in the dark and see if we hit something. Alan be sure to duck!

When your are referencing the cube is it possible MDX is converting the number 5 to the string "5"? Is there a way in MDX to convert to number?

[Guillaume Galtier]

When your are referencing the cube is it possible MDX is converting the number 5 to the string "5"?

No I don't think so (but not really sure ... )

In fact, when the type of either left or right part of the logical expression is not the same than the other, compilation fails and return an error "Incorrect parameter type"...

[Guillaume Galtier]

Is there no specialist MDX here ?
Perhaps the explanation of my problem isn't enough explicit ?

Any help would be very appreciated

Thanks

Guillaume

[Mike Cowie]

Hi Guillaume,

Well, all I can tell you is that I can replicate the results of your MDX expression (in 9.1 SP3). In my case, using similar cubes I don't see any filtering happening even though in my expression one of my cube references (just as in yours) is explicitly referencing one and only one possible value in the cube, which to me would mean it is effectively a constant.

So, this leaves me thinking that TM1 is not able to do what you want with a cube reference on each side. It might be worth you contacting IBM/Cognos with this specific question to report it as a bug and see where it goes from there.

A possible workaround... instead of referencing the "PC_Variables" cube on the right-hand side, can you use an attribute value reference instead? For example, if you added a "Lookup Value" attribute (String or Numeric - it doesn't matter because MDX will always treat them as strings) to your "PC_VariableName" dimension and then populated it for "Closing Month" with a 5, you could use this:

Code: Select all

FILTER( [PC_NRC_Pc].[PC NOT VALIDATED].Children, [PC_NRC_Refs].([PC_NRC_Pc_Definition].[Validation Month]) = 
STRTOVALUE( [PC_VariableName].[Closing Month].Properties("Lookup Value") ) )

Note that STRTOVALUE is used because a reference to TM1 attributes in MDX will always return a string and, if your reference to the cube "PC_NRC_Refs" returns a number you need to convert it using this non-TM1-specific MDX function (which may not be supported in older TM1 versions). Note also that I think I remember some people noticing that changes to an attribute value may not always cause your dynamic subset to refresh in certain versions of TM1. For example, if your "Lookup Value" attribute value for "Closing Month" changed from "5" to "6" the subset may not update immediately. Quite possibly you need a dimension save or some other update on the TM1 Server to force it to refresh -- I did not see this problem in my tests, but you may find it behaves differently in your version.

Hope that helps.

Regards,

[Guillaume Galtier]

Hi Mike,

First of all, many thanks for your very clear answer !

I have made many tests trying differents MDX functions and syntaxes, and my filter never worked. So my conclusion is the same than yours : TM1 seems not able to manage cubes references on each side of a logical expression (even if i've found this syntax in this documentation: http://www.bihints.com/book/export/html/68).
I will try to contact IBM/Cognos support to fix this problem...

Your workaround is working fine !

Thanks a lot

[Mike Cowie]

Hi Guillaume,

To be fair to that link of MDX notes at bihints, it is quite possible that this has worked in other, older versions of TM1. TM1's implementation of MDX has evolved a lot over the last few years, largely for the better, but it is possible that some things have been broken along the way.

Regards,

[Guillaume Galtier]

Hi Guillaume,

To be fair to that link of MDX notes at bihints, it is quite possible that this has worked in other, older versions of TM1. TM1's implementation of MDX has evolved a lot over the last few years, largely for the better, but it is possible that some things have been broken along the way.

Regards,

I even havn't thought that this link could be obsolete...
and this is the only one I have found about MDX for TM1...

Is existing some official (or not) documentation, with concrete samples, about MDX for TM1 ??
If yes I'm very interested

Guillaume

[Steve Vincent]

Unfortunately, no

[Steve Vincent]

or maybe I'm wrong

Stumbled upon this when searching for some stuff on Google (so much for Applix being a secure site!)

http://www.applix.com/rp/Using_MDX_Para ... ubsets.htm

The repository is still there, although if you try and get to its front page, you will need to login

http://www.applix.com/rp

[Steve Vincent]

also, there's a whole load of useful bits on bihint.com

http://www.bihints.com/creating_dynamic ... x_a_primer

Check out the links down the left hand side for lots of examples on certain aspects on MDX

[PXB]

I'm the author of the MDX Primer. I'm really snowed under at the moment but will post the latest version in Word format over the weekend. Others in the past have contacted me directly and received prompt answers. YMMV.

And no, it's not tested and re-tested on each and every patch, SP, etc but if the answer you get in your version is clearly wrong (and as a query language it should be clear when the answer is wrong unlike, say, some debates in macro economics forums...) you should report it to whoever it is nowadays. I've always been baffled by how little people use MDX - but it all starts with Applix and that great documentation! [And yes, I did offer it to them for free a couple years ago. I'm sure I'll get a response any day now...]

[Marcus Scherer]

Hi all,
I appreciate your approach to dynamic subsets with MDX. In the past I used this wonderful TI to build it and it worked well and fast. So I wonder if there are advantages using the MDX approach. Maybe the older TM1 experts have compared both approaches and can give me and us some insight about pros and cons?

Thanks,
Marcus