TI MDX query of 2 different cubes

[tsw]

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!

[tomok]

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.

[Wim Gielis]

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.

[tsw]

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?

[BCave]

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]

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.

[Wim Gielis]

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

[tsw]

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]

Did you read the earlier replies ?

[kangkc]

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]

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.

[tsw]

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]

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


Thanks

[lotsaram]

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.

[tsw]

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