TI MDX query of 2 different cubes

Post Reply
tsw
Posts: 43
Joined: Tue Nov 12, 2013 12:14 am
OLAP Product: TM1, PowerPlay, Transformer
Version: PA 2.0.4
Excel Version: Office 365

TI MDX query of 2 different cubes

Post by tsw » Thu Jan 23, 2020 7:41 pm

Hopefully, this is an easy question for someone out here:

I'm needing to ODBO mdx query a result from a TI process of data from 2 cubes (in same TM1 server) from a different TM1 server.

cubeA(dim1,dim2,dim3) and cubeB(dim1,dim2)

Is there syntax that allows me to either:
do a calculated member like "cubeA(dim1,dim2,dim3) * cubeB(dim1,dim2)" ?
or, at least, get the result in 2 columns which I can then multiple in TI before loading?

Thanks in advance!
Last edited by tsw on Thu Jan 23, 2020 8:52 pm, edited 1 time in total.

tomok
MVP
Posts: 2686
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: TI MDX query of 2 different cubes

Post by tomok » Thu Jan 23, 2020 8:34 pm

A TI process can only have one cube as a data source so the standard way of handling this is to have one cube as the data source (the one with the main intersection of cells you are trying to process) then in the Data tab of that process you issue a CELLGETN against the secondary cube to get it's value. Then you can do whatever math you want with the two values.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2345
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI MDX query of 2 different cubes

Post by Wim Gielis » Thu Jan 23, 2020 8:49 pm

That's correct. There are other tools, like Jedox, that can do a kind of "join" on cube slices, or joining a cube slice and an Excel file, ... (something to dream about in TM1).
Having said this, your best bet is indeed processing 1 set of cube data and exposing it to the data in the other cube slice. Pay attention to how you define the cube slices, such that the correct "combinations" of data can be made.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

tsw
Posts: 43
Joined: Tue Nov 12, 2013 12:14 am
OLAP Product: TM1, PowerPlay, Transformer
Version: PA 2.0.4
Excel Version: Office 365

Re: TI MDX query of 2 different cubes

Post by tsw » Thu Jan 23, 2020 8:54 pm

tomok wrote:
Thu Jan 23, 2020 8:34 pm
A TI process can only have one cube as a data source so the standard way of handling this is to have one cube as the data source (the one with the main intersection of cells you are trying to process) then in the Data tab of that process you issue a CELLGETN against the secondary cube to get it's value. Then you can do whatever math you want with the two values.
Thanks for the quick reply!
I'm querying from a separate TM1 server.. I edited the original post for clarification. I don't think CELLGETN works on getting data from another server, right?

User avatar
BCave
Posts: 2
Joined: Thu Jan 23, 2020 6:53 pm
OLAP Product: Planning Analytics; PAX
Version: 2.09 and 49
Excel Version: Office 2016
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: TI MDX query of 2 different cubes

Post by BCave » Thu Jan 23, 2020 8:56 pm

This is a pretty straightforward approach:

1) Define a new Process and assign a view for Cube A with the 3 dimensions as the TM1 Data Source
2) Rename your Variables to vDim1, vDim2 and vDim3 for the dimension references and vData1 for the data (this is just to connect them to the material outlined later in this post)
3) Place any code needed in the Prolog tab to limit the content of the data to be evaluated to ONLY the relevant intersections (i.e. suppress zeroes, ignore consolidations, etc.). If this type of pre-processing is done before the Data tab is done, be sure to issue the "DataSourceNameForServer = " and "DataSourceCubeView = " statement to have the Data tab loop over the revised view
4) In the Data tab, you can now use the Dim 1 & 2 references from Cube A to obtain the corresponding cell value from Cube B, as follows:

Code: Select all

vTempData1 = CellGetN('CubeB', vDim1, vDim2);
5) You then multiple the Cube B value by the data cell already available from Cube A:

Code: Select all

 vTempData2 = vData * vTempData1;
6) Using a limited set of set of test data in Cubes A and B, the following command writes out the results to an ASCII file for review:

Code: Select all

 AsciiOutput(vExportFile, 'CubeA = ', NumberToString(vData), 'Cube B = ',NumberToString(vTempData1), 'Cube A * Cube B = 
',NumberToString(vTempData2));
7) The results from the external CSV file show the original data from Cube A and B, and the multiplied result:

Code: Select all

"CubeA = ","5","Cube B = ","5","Cube A * Cube B = ","25"
"CubeA = ","10","Cube B = ","5","Cube A * Cube B = ","50"
"CubeA = ","15","Cube B = ","5","Cube A * Cube B = ","75"
This is pretty basic as outlined. Of course there can be numerous other checks and requirements in both the Prolog preparation and the Data processing itself, but this gives the bare bones. The multiplied result itself would also likely be directed to another cube target using a CellPutN() statement.

Wim Gielis
MVP
Posts: 2345
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI MDX query of 2 different cubes

Post by Wim Gielis » Thu Jan 23, 2020 8:58 pm

tsw wrote:
Thu Jan 23, 2020 8:54 pm
tomok wrote:
Thu Jan 23, 2020 8:34 pm
A TI process can only have one cube as a data source so the standard way of handling this is to have one cube as the data source (the one with the main intersection of cells you are trying to process) then in the Data tab of that process you issue a CELLGETN against the secondary cube to get it's value. Then you can do whatever math you want with the two values.
Thanks for the quick reply!
I'm querying from a separate TM1 server.. I edited the original post for clarification. I don't think CELLGETN works on getting data from another server, right?
No, it won't work the way it was suggested. You can try with ODBO as the data source type of the process, rather than a normal cube view. You will see that it's not that straightforward. I haven't done this before. Another option is to execute a process with RunTI and extract cube data to a file on the remote server, and import those data in the 'main' server.
Last edited by Wim Gielis on Thu Jan 23, 2020 8:59 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Wim Gielis
MVP
Posts: 2345
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI MDX query of 2 different cubes

Post by Wim Gielis » Thu Jan 23, 2020 8:59 pm

Well, BCave, that's not helping the OP for now, but hopefully someone stumbles on this after a Google search :D
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

tsw
Posts: 43
Joined: Tue Nov 12, 2013 12:14 am
OLAP Product: TM1, PowerPlay, Transformer
Version: PA 2.0.4
Excel Version: Office 365

Re: TI MDX query of 2 different cubes

Post by tsw » Thu Jan 23, 2020 9:05 pm

Since I can't do this in a single mdx query or process...
Maybe I can query cubeA() and get ResultA
then pass it into a sub-TI process and multiply ResultA with result of ResultB to load?
There's only a single row returned for each call of ResultA.. does this sound okay?

Wim Gielis
MVP
Posts: 2345
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI MDX query of 2 different cubes

Post by Wim Gielis » Thu Jan 23, 2020 9:15 pm

Did you read the earlier replies ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

kangkc
Community Contributor
Posts: 198
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1
Version: 9.x
Excel Version: 200x
Location: Singapore

Re: TI MDX query of 2 different cubes

Post by kangkc » Fri Jan 24, 2020 1:27 am

I think TSW is trying to get Cube A from Server 1 and Cube B from Server 2.
Traditionally it will be like using TI to generate an interface fike from Server 1, followed by another TI in server 2 picking up the interface file and while processing the file records, CELLGETN Cube B. If this is to be automated, both servers have to agreed on the timing.

However with REST API, I will do this instead:
In Server 2,use a TI to issue an external command to run a REST API (how to do this is beyond this topic) to execute another TI in Server 1. Then check for the status of the file generated, kick off a child process to process this file. The rest will be the same.

Alternatively, you can use TM1RunTI to trigger the TI in Server 1 from server 2 using external command,this will be easier if you know how to use TM1RunTI.

Not sure there are better suggestion for cross server interfacing.

tomok
MVP
Posts: 2686
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: TI MDX query of 2 different cubes

Post by tomok » Fri Jan 24, 2020 11:50 am

You can use ODBO to access a cube from another TM1 server as the primary data source and then use a CELLGETN against a cube on the server that is housing the TI process but this would only be a viable solution if there is a record in the ODBO source for every combination such that the end result of the ODBO record and the CELLGETN includes everything you need. If the ODBO cube is only a lookup cube, where there may not be a record for every cost center/department/location/etc., then this will not give you what you want. You cannot do a CELLGETN against a secondary TM1 server. Other than this I cannot think of a viable solution.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

tsw
Posts: 43
Joined: Tue Nov 12, 2013 12:14 am
OLAP Product: TM1, PowerPlay, Transformer
Version: PA 2.0.4
Excel Version: Office 365

Re: TI MDX query of 2 different cubes

Post by tsw » Tue Jan 28, 2020 6:59 pm

Hi all, sorry for the late response.

Clarification: I needed to mdx query and multiply server1.cubeA() * server1.cubeB() data to load into server2.cubeC
I think some suggestions of exporting cubeA data and then reading it back to multiply by cubeB data before loading into server2 will work.
But since I already have my TI process set up with a singleton cubeA mdx result back per call... I just created a sub-TI process that MDX-queried cubeB while taking as parameter of cubeA mdx result to load into cubeC... not as efficient as the file exporting of multiple cubeA results, but works for my specific case since result is singleton.

If I misunderstood some of your suggestions, let me know and I can try it out.
Thanks!

EvgenyT
Community Contributor
Posts: 316
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: TI MDX query of 2 different cubes

Post by EvgenyT » Thu Jan 30, 2020 5:03 am

Just going back to basics... does all this data need to be in 3 separate models?


Thanks

lotsaram
MVP
Posts: 3351
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TI MDX query of 2 different cubes

Post by lotsaram » Thu Jan 30, 2020 6:37 am

tsw wrote:
Tue Jan 28, 2020 6:59 pm
Clarification: I needed to mdx query and multiply server1.cubeA() * server1.cubeB() data to load into server2.cubeC
You can use calculated members in MDX. SO why not do a "with member as cubeA() * cubeB()" in your MDX query. Use that as the ODBO data source for your process on server2.

TM1's ODBO connector is a little flakey and not that performant though. So doing the same via Rest API might be the better longer term option.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

tsw
Posts: 43
Joined: Tue Nov 12, 2013 12:14 am
OLAP Product: TM1, PowerPlay, Transformer
Version: PA 2.0.4
Excel Version: Office 365

Re: TI MDX query of 2 different cubes

Post by tsw » Mon Feb 03, 2020 6:45 pm

lotsaram wrote:
Thu Jan 30, 2020 6:37 am
You can use calculated members in MDX. SO why not do a "with member as cubeA() * cubeB()" in your MDX query. Use that as the ODBO data source for your process on server2.
well, I wished I knew this.. as this was the syntax I was looking for in my orig post. I couldn't find anything online.
Can you provide a sample syntax for next time?
Thanks

Post Reply