Page 1 of 1
MDX "JOIN"
Posted: Thu Oct 24, 2019 1:52 pm
by 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.
Re: MDX "JOIN"
Posted: Thu Oct 24, 2019 5:02 pm
by 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.
Re: MDX "JOIN"
Posted: Thu Oct 24, 2019 5:13 pm
by 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,
...
Re: MDX "JOIN"
Posted: Thu Oct 24, 2019 5:25 pm
by 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.
Re: MDX "JOIN"
Posted: Thu Oct 24, 2019 7:37 pm
by 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.
Re: MDX "JOIN"
Posted: Fri Oct 25, 2019 8:59 am
by David Usherwood
Re: MDX "JOIN"
Posted: Fri Oct 25, 2019 9:43 am
by PavoGa
Intersect requires the same dimensionality in both sets. Tom is asking for the inner join of two dimensions.