MDX "JOIN"

[tomok]

Is there a way to do an MDX equivalent of an SQL Join between two dimensions? Right now the way I do it in TI is to do a While loop and cycle through Dim2, building a Dim1 subset of the elements that are in both dimensions or vice versa. Doing it in MDX would mean a lot less code.

[gtonkin]

You can try to use the GENERATE command to do this. I do not have the best example to hand but you would do something like:

{GENERATE(
{[Month_A].Members},
{StrToMember("[Month_A].[" + [Month_B].CurrentMember.Name + " ]")}
)}

Basically doing a for each Member in Month (could be another dimension), build a member based on an "attribute" of that member. "Attribute" could be the name, index, a real attribute etc. etc.
In this example, I am using the same dimension but as long as you return a valid member defined by the StrToMember part, you should be fine.

That is the theory I use, YMMV

Good luck.

[lotsaram]

Not sure exactly what you mean, but if you mean a cross-join or the product of 2 or more dimensions this is easy with the dimension1 * dimension2 syntax. e.g.

Code: Select all

SELECT
      NON EMPTY
         { Tm1FilterByLevel( TM1SubsetAll( [plan_version] ), 0) }
         * { Tm1FilterByLevel( TM1SubsetAll( [plan_business_unit] ), 0) }
      ON ROWS,
      ...

[PavoGa]

Here is another method that uses GENERATE as gtonkin suggests, but does not require the StrToMember. A subset we use:

Code: Select all

GENERATE(
	TM1DRILLDOWNMEMBER( {[Employee].[Non-Active Employees]}, ALL)
	, FILTER(
		TM1SUBSETALL([Employee - Historical])
		, [Employee - Historical].currentmember.name = [Employee].currentmember.name)
		)

Unfortunately, in some versions of PA, if a value is passed to StrToMember that does not exist in the dimension, the instance will crash.

[tomok]

I am talking about an INNER JOIN. I have two dimensions, one with all the accounts in the GL and one with only a few accounts. I want an MDX that will create a subset on the all accounts dimension of only the elements that exist in both dimensions. Thanks for the ideas. I'll test them out tomorrow.

[David Usherwood]

Sorry to be late on this, but what about INTERSECT?
https://www.iccube.com/support/document ... ersect.php

[PavoGa]

Sorry to be late on this, but what about INTERSECT?
https://www.iccube.com/support/document ... ersect.php

Intersect requires the same dimensionality in both sets. Tom is asking for the inner join of two dimensions.