MDX "JOIN"

Post Reply
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

MDX "JOIN"

Post 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX "JOIN"

Post 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.
Last edited by gtonkin on Thu Oct 24, 2019 5:24 pm, edited 1 time in total.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX "JOIN"

Post 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,
      ...
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
PavoGa
MVP
Posts: 616
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 "JOIN"

Post 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.
Ty
Cleveland, TN
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: MDX "JOIN"

Post 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: MDX "JOIN"

Post by David Usherwood »

Sorry to be late on this, but what about INTERSECT?
https://www.iccube.com/support/document ... ersect.php
User avatar
PavoGa
MVP
Posts: 616
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 "JOIN"

Post by PavoGa »

David Usherwood wrote: Fri Oct 25, 2019 8:59 am 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.
Ty
Cleveland, TN
Post Reply