TI View Execution vs MDX View

[PlanningDev]

I have been working a bit with TM1py and have been using MDX to retrieve cube data. I have run into an issue where my MDX seems to timeout when executing, both in a Cube Viewer and and in TM1py. This is somewhat understandable as I have put all the dimensions on the rows except one.

There appears to be some kind of difference between the way a TI executes a view to get data and building a similar MDX view on your own. The TI process seems to execute the view as if all dimension are nested on rows. Even though in a TI you don't specify this, it will execute a very large view of data and return the records flattened out. When I pull the MDX from the view created in TI through script it appears to put the Measures dimension and time dimension in the columns and then everything else gets set as a context dimension with a single element selected.

My question here is if anyone knows what a TI does to execute the view and return all the records as clearly the MDX that is set for the view doesn't match what the TI returns. Somehow a TI is able to execute the MDX in a different way where it actually returns the data flat vs what I have to create in MDX to replicate the same output.

The goal would be to use the same view the TI executes (which works) in the rest api using TM1py.

[tomok]

The TI process seems to execute the view as if all dimension are nested on rows. Even though in a TI you don't specify this, it will execute a very large view of data and return the records flattened out.

This is the way a TI process works when a cube view is the data source. You get one record for every intersection of elements in the view and then the value associated with that intersection. There is no concept of row, column or title dimensions. AFAIK the engine behind TI processes has not changed in years so I am pretty sure it does not execute an MDX query to access the data when a cube view is the source.

[PlanningDev]

I figured it was doing something different than executing MDX due to the difference in behavior. I guess the question is, how would I get a similar view of data out through the rest api if all I get is MDX/View capability? Wondering if anyone else has run into a similar issue? When I execute the MDX with all dimensions on rows and one dimension in the columns it runs a ExecuteMDX but its always single threaded and times out after 20 min.

[gtonkin]

Can you share the code? Are you using Non Empty?

[PlanningDev]

MDX is fairly simple. Its roughly something like this.

Code: Select all

SELECT {[Dim1].[Dim1].[Version1],[Dim1].[Dim1].[Version2]}
*{[Dim2].[Dim2].[Local Currency],[Dim2].[Dim2].[USD]} ON 0,
 NON EMPTY 
 {DESCENDANTS([Dim3].[Dim4].[Total Dim3] , 99 , LEAVES)}
 *{DESCENDANTS([Dim4].[Dim4].[Total Dim4] , 99 , LEAVES)}
 *{DESCENDANTS([Dim5].[Dim5].[Total Dim5] , 99 , LEAVES)}
 *{DESCENDANTS([Dim6].[Dim6].[Total Dim6] , 99 , LEAVES)}
 *{DESCENDANTS([Dim7].[Dim7].[Total Dim 7] , 99 , LEAVES)}
 *{DESCENDANTS([Dim8].[Dim8].[Total Dim8] , 99 , LEAVES)}
 ON 1 FROM [Reporting_Cube] WHERE ([Dim9].[Dim9].[Element1], [Dim10].[Dim10].[Value])

[gtonkin]

TBH, not sure then - MDX looks reasonable - should be 2 columns and dim3-8 stacked on rows.
As a stab in the dark, have you tried rationalising from Dim8 backwards by setting Dim8 to the Total member, then Dim7 etc to see if there is something happening between the quantum of members returned by the Descendants() function and the cross join?

[MarenC]

Hi,

I presume this is a typo:

{DESCENDANTS([Dim3].[Dim4].[Total Dim3] , 99 , LEAVES)}

and Dim4 should read Dim3?

Edit: I do know these are not the real dimension names but just double checking your actual mdx is not mixing dims like this!

Maren

[PlanningDev]

No that was just a typo.

I have been able to use the NonEmpty() function instead of NON EMPTY keyword on the rows. While this has helped and it now completes, there is still a noticeable performance drop as more dimensions are added to the NonEmpty() function. My guess is that it still relates to the single threaded nature of TM1 determining the tuples of members that are non empty. For whatever reason Turbo Integrator seems to just skip this step and starts processing immediately after the View has been executed (using multiple cores).

Something like this

Code: Select all

SELECT {[Dim1].[Dim1].[Version1],[Dim1].[Dim1].[Version2]}
*{[Dim2].[Dim2].[Local Currency],[Dim2].[Dim2].[USD]} ON 0,
 NONEMPTY(
 {DESCENDANTS([Dim3].[Dim3].[Total Dim3] , 99 , LEAVES)}
 *{DESCENDANTS([Dim4].[Dim4].[Total Dim4] , 99 , LEAVES)}
 *{DESCENDANTS([Dim5].[Dim5].[Total Dim5] , 99 , LEAVES)}
 *{DESCENDANTS([Dim6].[Dim6].[Total Dim6] , 99 , LEAVES)}
 *{DESCENDANTS([Dim7].[Dim7].[Total Dim 7] , 99 , LEAVES)}
 *{DESCENDANTS([Dim8].[Dim8].[Total Dim8] , 99 , LEAVES)}
 ,{[Dim1].[Dim1].[Version1],[Dim1].[Dim1].[Version2]}
*{[Dim2].[Dim2].[Local Currency],[Dim2].[Dim2].[USD]}
 )
 ON 1 FROM [Reporting_Cube] WHERE ([Dim9].[Dim9].[Element1], [Dim10].[Dim10].[Value])

[ascheevel]

When I use an MDX view as a datasource for TI, I always set the skip zeroes the same way I would with a native view. It's been a while, but I seem to recall that I was running into a similar issue where the MDX view, no matter what I did with NON EMPTY would take forever to execute until I added ViewExtractSkipZeroesSet([cube name], [view name], 1). After adding that, I got performance comparable to using a native view. Below is sample prolog script I use when setting up a TI to use an mdx view.

Code: Select all

sMDX1 = '';
ViewCreateByMDX([cube_name], [view_name], sMDX1, 1);
ViewExtractSkipZeroesSet([cube_name], [view_name], 1);

[PlanningDev]

In my case im trying to use TM1py to execute the mdx which doesn't leave me with an option for ViewExtractSkipZeroesSet (At least not with the ExecutePowerBI function. I may try and play around with some of the other TM1py execute functions to see if anything helps. The best case would be to find a way to get the MDX to execute the same as it does within a TI.

[ascheevel]

My mistake PlanningDev, looks like I wasn't following the thread as closely as I led myself to believe.

[David Usherwood]

In my case im trying to use TM1py to execute the mdx which doesn't leave me with an option for ViewExtractSkipZeroesSet (At least not with the ExecutePowerBI function. I may try and play around with some of the other TM1py execute functions to see if anything helps. The best case would be to find a way to get the MDX to execute the same as it does within a TI.

You can execute 'loose' TI statements in TM1py using execute_ti_code - so if you build an MDX view you can then suppress zeroes. I suspect the view may need to be permanent which may not suit your approach (temporary views don't cause server locks). What TM1py does is to create, execute and delete a TI process so you have to be admin to run it.

[MariusWirtz]

Hi,

I can confirm that one can process through a cubeview/MDX more efficiently with a TI than one could do with REST/TM1py.
Somehow TI doesn't materialize the full view in Memory before consuming it.

In the REST world it works differently. It's essentially a two-step process.
1. First, one executes the MDX/cube-view through REST. That triggers the TM1 server to create a "cellset" (= a physical copy of the data).
2. Then one retrieves the data from the cellset through a second REST request
That approach is inherently less efficient than whatever TI does behind the scenes.
(TM1py does give you the impression that it's one operation because it wraps both steps into one easy to use function: execute_mdx)

To speed up the data retrieval with REST/TM1py you can break your query into smaller chunks and execute/retrieve them separately.
I suggest you break your query into n smaller ones (e.g., perhaps one chunk per month) and execute them in parallel (or sequentially).

[PlanningDev]

I am currently working on breaking each export down into 12 chunks (1 per month) and executing in parallel using 24 processes. My initial findings are that its still slower even through this route than using the TI to export the data to file.

33 Versions x 12 Months = 396 Total slices to export
(396 Slices x 90 seconds per slice) / 24 processes in parallel = 1,485 seconds or ~25Min + 5min write to file = 30 Min overall time

Current TI process = 33 versions x 12 parallel threads using RushTI = 13min Total Execution Time.

Its been a bit of a fun rabbit hole to go down! Learning quite a bit about Python and TM1/TM1py