Page 1 of 1
MDX Union Question
Posted: Tue Oct 14, 2014 6:42 am
by davidh
Hi all.
Just completed my first attempt for an MDX expression in TM1. After a bit of reading/searching on here, I got it working. Basically I wanted to select all items in the product dimension, in consolidation 0400 that have 01-R or 02-R or 03-R in the element. I have used the union function to do this. I noticed however union seems to only union two things together at a time. is it true then that if i had say 10 different expressions to filter on then I would need UNION 9 times.
The example is;
{UNION({UNION(
{TM1FILTERBYPATTERN({TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({ [Product].[0400] }, ALL, RECURSIVE)}, 0)} , "*01-R*")},
{TM1FILTERBYPATTERN({TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({ [Product].[0400] }, ALL, RECURSIVE)}, 0)} , "*02-R*")} )},
{TM1FILTERBYPATTERN({TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({ [Product].[0400] }, ALL, RECURSIVE)}, 0)} , "*03-R*")} )}
Thanks in advance.
David
Re: MDX Union Question
Posted: Tue Oct 14, 2014 7:51 am
by rmackenzie
You can use the following syntax instead of nested UNIONs:
Code: Select all
{ [dim].[sub1], [dim].[sub2], [dim].[sub3] }
If you have subsets named the same as elements in the dimension then you may need to look into the bespoke TM1SubsetToSet function.
Edit: I should have mentioned that [dim].[subN] can be replaced by {your MDX goes here}
Re: MDX Union Question
Posted: Tue Nov 11, 2014 3:40 pm
by Headon01
David,
You could also venture a little outside of the TM1 box on this one.
Code: Select all
FILTER(DESCENDANTS([Product].[0400], 25, LEAVES), INSTR([Product].CurrentMember.Name, "01-R")>0 OR INSTR([Product].CurrentMember.Name, "02-R")>0 OR INSTR([Product].CurrentMember.Name, "03-R")>0 )
With the DESCENDANTS function you can return the 0 level elements in one shot. I don't know how many levels deep your hierarchy goes but using a larger number in the second parameter (in this case I used 25) doesn't hurt. The FILTER function is great for multiple criteria, although using the INSTR function for wildcards is a bit of a pain.
The TM1 MDX recorder is great for learning MDX but, if you have time, play around with what it gives you. It's much like the Excel macro recorder...it will give you something that works but it is not always the most efficient code (especially all those braces it sticks in there).
Micheal
Re: MDX Union Question
Posted: Tue Nov 11, 2014 11:02 pm
by EvgenyT
Another suggestion would be to assign a new attribute for the product dimension (i.e. 01-R, etc) and then just use {Filter function to get the products you want. It would avoid having to write complex/chunky MDX statement.
The downside of this approach would be having to maintain those attributes regulary via TI.
Just a thought
Re: MDX Union Question
Posted: Thu Nov 13, 2014 1:33 am
by rmackenzie
Headon01 wrote:You could also venture a little outside of the TM1 box on this one.
Interesting - in a dimension of a million elements and DNLEV of 5, this works a couple of seconds quicker:
Code: Select all
DESCENDANTS([test].[root], 25, LEAVES)
than this:
Code: Select all
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({ [test].[root] }, ALL, RECURSIVE)}, 0)}
My expactation was that the custom TM1 functions would perform better.
It seems the distance parameter can be arbitrarily large without affecting performance. However, it should be >= DNLEV - 1 of the dimension in order to work.
Re: MDX Union Question
Posted: Thu Nov 13, 2014 2:18 pm
by Headon01
Robin,
Probably has to do with the two TM1-MDX functions being evaluated instead of one MS-MDX function. I would think toe-to-toe the TM1 MDXs would out perform the MS-MDXs but I have never tested that out.
As far as the levels parameter goes, when I first started using this function I would put 99 as that parameters value. When I saw that it worked the way I wanted it to I put in the actual number of dimension levels (against production data) and noticed there was no difference in performance. I usually put in the number of dimension levels + 5 into these functions.
Micheal