MDX Query : Subset vs 2 Dimensions

[Steve Rowe]

Hi,

I'm building a source view for a data transfer between a detailed to summary cube. The dimensions in the destination / summary cube are related subsets of the source / detailed cube.

I hold some flags in a cube to say if the detailed elements should be in the summary cube but these includes all the consolidated ones too.

So what I want to do is a piece of MDX like this

Code: Select all

{INTERSECT (
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [DetailedDim] )},[sys-BIDefn_DetailedDim].
     ([BI_Cube_Name].[BI_Cube_1],[BI_Cube_Measure].[Element Inclusion]) = "1")} ,
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [SummaryDim] )}, 0)}
)}

i.e all the flagged elements that are N level in the summary dimension.

This isn't valid syntax however as I can't directly reference two different dimensions (I think) and I get an error
INTERSECT: Sets have incompatible dimensionality expression:
from the TI process.

I can easily add another measure to my flagging cube that folds the level 0 test in and reference this, except I have to do it in many places. So there are plenty of ways of me to solve my actual problem. Just want to know if this is a basic limitation of MDX or if I am missing something?

Cheers,

[Wim Gielis]

Hello Steve

Most probably not related to your question, but don't you miss the 2nd parameter in the TM1FILTERBYLEVEL() function ?

Wim

[Martin Ryan]

My MDX is not fantastic but from what I can work out this isn't possible. I think the closest you'll get is using StrToMember, but I can't see how to make that apply to multiple elements.

I'd expect you can find much easier ways of handling this, like doing some dimix's and ellev's in the prolog of the TI process that moves the data across. A static subset will also be faster as MDX will be continually re-evaluated.

[Steve Rowe]

Yes thanks Martin,

The whole constant reevaluation of MDX thing is a real pain, really limits its use in process.

I can already build the subs manually but I just wanted to try some other methods since it was taking a long time to loop through all the dimensions.

Once I have the MDX right I can use that to build the static subsets I need.

Cheers,