TM1 Forum

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.

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

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,

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.

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

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

Hi, The reference dimension [Dimension].[Subset] is not one of the dimensions in the cube.

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.

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

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.

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

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] )

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.

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.

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

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,