TI View Execution vs MDX View
-
- Community Contributor
- Posts: 349
- Joined: Tue Aug 17, 2010 6:31 am
- OLAP Product: Planning Analytics
- Version: 2.0.5
- Excel Version: 2016
TI View Execution vs MDX View
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.
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.
-
- MVP
- Posts: 2832
- 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 View Execution vs MDX View
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 wrote: ↑Sat Nov 06, 2021 11:36 pm 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.
-
- Community Contributor
- Posts: 349
- Joined: Tue Aug 17, 2010 6:31 am
- OLAP Product: Planning Analytics
- Version: 2.0.5
- Excel Version: 2016
Re: TI View Execution vs MDX View
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
- MVP
- Posts: 1204
- 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: TI View Execution vs MDX View
Can you share the code? Are you using Non Empty?
-
- Community Contributor
- Posts: 349
- Joined: Tue Aug 17, 2010 6:31 am
- OLAP Product: Planning Analytics
- Version: 2.0.5
- Excel Version: 2016
Re: TI View Execution vs MDX View
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
- MVP
- Posts: 1204
- 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: TI View Execution vs MDX View
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?
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?
-
- Regular Participant
- Posts: 356
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: TI View Execution vs MDX View
Hi,
I presume this is a typo:
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
I presume this is a typo:
and Dim4 should read Dim3?{DESCENDANTS([Dim3].[Dim4].[Total Dim3] , 99 , LEAVES)}
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
-
- Community Contributor
- Posts: 349
- Joined: Tue Aug 17, 2010 6:31 am
- OLAP Product: Planning Analytics
- Version: 2.0.5
- Excel Version: 2016
Re: TI View Execution vs MDX View
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
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])
-
- Community Contributor
- Posts: 288
- Joined: Fri Feb 15, 2013 5:49 pm
- OLAP Product: TM1
- Version: PA 2.0.9.1
- Excel Version: 365
- Location: Minneapolis, USA
Re: TI View Execution vs MDX View
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);
-
- Community Contributor
- Posts: 349
- Joined: Tue Aug 17, 2010 6:31 am
- OLAP Product: Planning Analytics
- Version: 2.0.5
- Excel Version: 2016
Re: TI View Execution vs MDX View
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.
-
- Community Contributor
- Posts: 288
- Joined: Fri Feb 15, 2013 5:49 pm
- OLAP Product: TM1
- Version: PA 2.0.9.1
- Excel Version: 365
- Location: Minneapolis, USA
Re: TI View Execution vs MDX View
My mistake PlanningDev, looks like I wasn't following the thread as closely as I led myself to believe.
-
- Site Admin
- Posts: 1457
- Joined: Wed May 28, 2008 9:09 am
Re: TI View Execution vs MDX View
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.PlanningDev wrote: ↑Wed Nov 10, 2021 4:02 am 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.
-
- Posts: 31
- Joined: Sat Apr 08, 2017 8:40 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: 2016
Re: TI View Execution vs MDX View
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).
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).
-
- Community Contributor
- Posts: 349
- Joined: Tue Aug 17, 2010 6:31 am
- OLAP Product: Planning Analytics
- Version: 2.0.5
- Excel Version: 2016
Re: TI View Execution vs MDX View
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
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