MDX views, testing cell value against a subset
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
MDX views, testing cell value against a subset
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.
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.
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.
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
www.infocat.co.uk
-
- Regular Participant
- Posts: 350
- 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
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
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
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: MDX views, testing cell value against a subset
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,
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
www.infocat.co.uk
-
- MVP
- Posts: 3652
- 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
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.
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.
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: MDX views, testing cell value against a subset
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
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
www.infocat.co.uk
-
- Regular Participant
- Posts: 197
- 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
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
Even without multiple hierarchy you could have another rollup created to do the same effect
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: MDX views, testing cell value against a subset
Hi, The reference dimension [Dimension].[Subset] is not one of the dimensions in the cube.
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Regular Participant
- Posts: 197
- 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
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.
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 (98.01 KiB) Viewed 14903 times
-
- Regular Participant
- Posts: 350
- 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
Hi,
Burnstripe,
where you say,
Maren
Burnstripe,
where you say,
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.Whilst I've used an attribute in my example you could do the same with a cube value. Whichever is your preference.
Maren
- PavoGa
- MVP
- Posts: 617
- 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: Charleston, Tennessee
Re: MDX views, testing cell value against a subset
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.
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
Cleveland, TN
-
- Regular Participant
- Posts: 350
- 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
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
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
-
- Regular Participant
- Posts: 197
- 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
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 thisMarenC wrote: ↑Thu Oct 14, 2021 1:33 pm Hi,
Burnstripe,
where you say,
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.Whilst I've used an attribute in my example you could do the same with a cube value. Whichever is your preference.
Maren
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 (99.31 KiB) Viewed 14841 times
- PavoGa
- MVP
- Posts: 617
- 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: Charleston, Tennessee
Re: MDX views, testing cell value against a subset
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.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
Ty
Cleveland, TN
Cleveland, TN
-
- Regular Participant
- Posts: 197
- 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
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 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
-
- Regular Participant
- Posts: 350
- 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
Hi,
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
I was referring to TM1TUPLESIZE, not your attribute based MDX.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.
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
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: MDX views, testing cell value against a subset
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,
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
www.infocat.co.uk