MDX views, testing cell value against a subset

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

MDX views, testing cell value against a subset

Post by Steve Rowe » Tue Oct 12, 2021 11:09 am

Hi,

I've a tricky MDX view problem I am wrestling with.

Cube A contains a string measure, "Reference", this holds an element name from another dimension as a piece of data, let's call that dimension "Test Dimension". The dimension Test Dimension is not part of Cube A.

I can write a MDX view expression easy enough to give me all the tuples where Reference = A specific element in the Test Dimension.

What I need to do is scale this up to return all the tuples where the Reference = A Member of Test Dimension set.
mdxquery.jpg
mdxquery.jpg (67.87 KiB) Viewed 4063 times
My current approach is to loop over the Test Dimension set and execute the MDX View for each member of the set one by one. This is too slow as it takes ~20s to execute each query giving five hours for a complete pass over the subset of 1,000 members.

I'd like to execute a single MDX query, in order to do this I need to test the cell value against the enitre subset rather than one element at a time.

Option 1
Create a long concatenated string containing all the members of the subset and use the Instr MDX function to test for membership.
Should be fairly simple to write but I'm not expecting the Instr function to be fast and my test string will be about 13k characters long so this is going to bring it's own set of problems that will be hard / impossible to solve. Non-starter

Option 2
I could convert the cell value to a member using StrToMember and then test this against the set, so my filter expression on my view becomes
INTERSECT(StrToMember("[Test Dimension].[" + MDX to Return the cell value +"]),{[Test Dimension].[Test Subset]}).Count > 0
Seems to make sense on paper though getting the MDX written might be a challenge.

Option 3
Build another cube with Test Dimension in it as well as those in Cube A and then write a rule to perform the filter activity. A bit old school and I rather not build a cube just to support this, I should be able to handle this with MDX.

Anyone else have any bright ideas? Feels like I might be missing a trick somewhere...

TIA for any pointers.
Technical Director
www.infocat.co.uk

MarenC
Regular Participant
Posts: 177
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX views, testing cell value against a subset

Post by MarenC » Tue Oct 12, 2021 11:26 am

Hi,

Cant you create another measure in Cube A, which is rule based on the dimix of the other dim.

So, If( Dimix('Test Dimension', !reference) > 0,1,0);

And then build the view based on this value being a 1?

Maren

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: MDX views, testing cell value against a subset

Post by Steve Rowe » Tue Oct 12, 2021 11:37 am

Hi Maren,

Yes, good idea and it had already dawned on me that this might be the best(?) approach, honest. Conveniently I already have a measure in the cube that is doing some flagging in a different direction so I can add a condition to that.

I'll need to build a dimension to match the subset but that's acceptable.

Cheers,
Technical Director
www.infocat.co.uk

lotsaram
MVP
Posts: 3535
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX views, testing cell value against a subset

Post by lotsaram » Tue Oct 12, 2021 11:40 am

Maybe I misunderstood your explaination, but isn't this exactly what the MDX Generate function does?

That would put your TI loop through the subset all in one MDX but it wouldn't do anything for the performance. It would still take just as long.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: MDX views, testing cell value against a subset

Post by Steve Rowe » Tue Oct 12, 2021 11:57 am

Not sure if Generate would do the job, though maybe it could be pursuaded? If Generate is just short hand for looping over the MDX, it's not going to help me though...

Basically what I want to do is

Select [All Tuples] Where Cell Value is a member of [Dimension].[Subset] From Cube A
Technical Director
www.infocat.co.uk

burnstripe
Posts: 76
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX views, testing cell value against a subset

Post by burnstripe » Tue Oct 12, 2021 12:38 pm

Do you have multiple hierarchies on the model? If so it looks like a classic case the the CreateHierarchyByAttribute ti function and store the mapping as an attribute instead. You could have the cube A values/mappings as consolidated elements with the leaf as child. Then you wouldn't need to iterate through the dimension to check the mapping

Even without multiple hierarchy you could have another rollup created to do the same effect

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: MDX views, testing cell value against a subset

Post by Steve Rowe » Tue Oct 12, 2021 12:52 pm

Hi, The reference dimension [Dimension].[Subset] is not one of the dimensions in the cube.
Technical Director
www.infocat.co.uk

burnstripe
Posts: 76
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX views, testing cell value against a subset

Post by burnstripe » Tue Oct 12, 2021 5:55 pm

Hi Steve,

I've gone back on my idea and have gone with lotsaram's about using the generate. In the attachment I have:
a dimension (DIMC) with the elements A, B, C
a dimension (DIMB) with the elements 1,2,3,4,5

In DimB I have an string attribute called mapping which you can see has the following mapping
Element Mapping
1 A
2 A
3 A
4 C
5 C

So based on the above I'm looking to have a MDX Statement which brings back only A and C from DIMC, leaving B out.

For the MDX I have the following statement used within subset editor for dimension DIMC

Generate( Filter( TM1SubsetAll( [DimB] ), Len([DimB].[Mapping]) > 0), {StrToMember("[DimC].[" + [DimB].[Mapping] + "]")})

And the result is A and C is returned

Whilst I've used an attribute in my example you could do the same with a cube value. Whichever is your preference.
Attachments
Screenshot.PNG
Screenshot.PNG (98.01 KiB) Viewed 4003 times

MarenC
Regular Participant
Posts: 177
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX views, testing cell value against a subset

Post by MarenC » Thu Oct 14, 2021 1:33 pm

Hi,

Burnstripe,

where you say,
Whilst I've used an attribute in my example you could do the same with a cube value. Whichever is your preference.
Can you please provide a specific example of this working, as I am struggling to imagine how this could be done in the context of this issue.

Maren

User avatar
PavoGa
MVP
Posts: 548
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX views, testing cell value against a subset

Post by PavoGa » Thu Oct 14, 2021 1:40 pm

Steve,

Here is a thread that may provide some insight using TM1TupleSize to test what you're looking for.

viewtopic.php?f=21&t=15809&p=78817&hili ... ize#p78817

Sorry, I just glanced over the post and will not be able to dig in until later, but thought this might help.
Ty
Cleveland, TN

MarenC
Regular Participant
Posts: 177
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX views, testing cell value against a subset

Post by MarenC » Thu Oct 14, 2021 1:56 pm

Hi PavoGa,

Not sure TM1TupleSize will work either because the dimension does not exist in the cube, therefore currentmember wont work.

But please don't let that put you off trying to come up with a solution, as I would be very intrigued if this is possible.

Maren

burnstripe
Posts: 76
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX views, testing cell value against a subset

Post by burnstripe » Thu Oct 14, 2021 2:54 pm

MarenC wrote:
Thu Oct 14, 2021 1:33 pm
Hi,

Burnstripe,

where you say,
Whilst I've used an attribute in my example you could do the same with a cube value. Whichever is your preference.
Can you please provide a specific example of this working, as I am struggling to imagine how this could be done in the context of this issue.

Maren
I've re-read the requirements, if you want to restrict the results of Dim6 based on the elements listed in the test dimension then you could use something like this

Generate( TM1SubsetAll( [DimD] ), {StrToMember("[DimC].[" + [DimD].Currentmember.name + "]")})

DimD, being your test dimension, and dimc being dim6 in this case.

In my example attached
DimD consists of 2 elements A, B
DIMC consists of 3 element A, B, C

And the statement executed in DIMC returns A, B as these are the only elements that exist in that dimension. If you wanted it based on a subset list you'd just need to change this section of the MDX above TM1SubsetAll( [DimD] )
Attachments
Example 20211014.PNG
Example 20211014.PNG (99.31 KiB) Viewed 3941 times

User avatar
PavoGa
MVP
Posts: 548
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX views, testing cell value against a subset

Post by PavoGa » Thu Oct 14, 2021 2:59 pm

MarenC wrote:
Thu Oct 14, 2021 1:56 pm
Hi PavoGa,

Not sure TM1TupleSize will work either because the dimension does not exist in the cube, therefore currentmember wont work.

But please don't let that put you off trying to come up with a solution, as I would be very intrigued if this is possible.

Maren
I don't think that is necessary. The FILTER on the target dimension is going to test TM1TupleSize using, say, TM1MEMBER & STRTOMEMBER against on the cube value to test existence in the test dimension. Like I said, I'll look at it later because it has my interest piqued.
Ty
Cleveland, TN

burnstripe
Posts: 76
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX views, testing cell value against a subset

Post by burnstripe » Thu Oct 14, 2021 3:01 pm

MarenC wrote:
Thu Oct 14, 2021 1:56 pm
Hi PavoGa,

Not sure TM1TupleSize will work either because the dimension does not exist in the cube, therefore currentmember wont work.

But please don't let that put you off trying to come up with a solution, as I would be very intrigued if this is possible.

Maren
currentmember can work, if used in the right context, see my last message. The MDX Expression is being set in DimC yet I've reference the currentmember on DimD.

MarenC
Regular Participant
Posts: 177
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX views, testing cell value against a subset

Post by MarenC » Thu Oct 14, 2021 3:14 pm

Hi,
currentmember can work, if used in the right context, see my last message. The MDX Expression is being set in DimC yet I've reference the currentmember on DimD.
I was referring to TM1TUPLESIZE, not your attribute based MDX.

Your attribute MDX, I think, would break down as soon as an attribute was equal to something that did not exist in the dimension, for example if you made element 1 = Z.

Edited this, as rereading the original post, dim 6 is a dimension, not a cube value! Though that just confuses me more! It isn't a cube value or a dimension but a set of dimension combinations! :?

Maren

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: MDX views, testing cell value against a subset

Post by Steve Rowe » Thu Oct 14, 2021 9:03 pm

Hi,

Glad you're all having fun!

In the end I went with using a rule to calculate the filter flag with a simple Dimix of the cube value versus a dimension I built on the fly from the subset as per MarenCs suggestion at the top of the post. I then used the ruled filter flag to drive the MDX view.

This operated orders of magnitude faster, <10 mins rather than multiple hours. (Be careful with this timing as it is not like for like, the multiple hours is from looping over the subset calling the MDX repeatedly, so there is an overhead here from calling the TI and building all the views / subsets required to do the processing).

So whilst we can do clever things with MDX views now we have "MDXViewCreate" it was significantly quicker to write and execute using the tools native to TM1 or at least blending the two technologies.

Cheers,
Technical Director
www.infocat.co.uk

Post Reply