Problem With Slow process execution when Selecting child Elements and a single consolidated Element

Post Reply
pikolikoli
Posts: 15
Joined: Sun May 17, 2020 2:37 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

Problem With Slow process execution when Selecting child Elements and a single consolidated Element

Post by pikolikoli » Wed Jan 06, 2021 2:57 pm

Hello ,

I have a process who selects only Child elemnts. I wanted to add a single consolidated element to the MDX list ised in this process. so that my process can take in consideration my new consolidated elemnt I had to activate the consolidation by putting : ViewExtractSkipCalcsSet ( zCube, zView, 0 ).

But Then I notice that my process Takes a long time in execution compared to the old logic. Is there any way to add my consolidated element and be able to modify ir without Taking So long ?

Here is my Old Code (it runs in less that 1 minute ):

Code: Select all

SubsetDestroy( zDim, zView );
Mdx='{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Account] )}, 0)}';
SubsetCreateByMDX( zView,Mdx);
ViewSubsetAssign( zCube, zView, zDim, zView );

ViewExtractSkipRuleValuesSet( zCube, zView , 0 );
ViewExtractSkipCalcsSet ( zCube, zView, 1 );
ViewExtractSkipZeroesSet( zCube, zView , 1 );
Then After Adding the consolidated Element ( Total Takes more than 30min I had to stop it ):

Code: Select all

SubsetDestroy( zDim, zView );
Mdx='{{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Account] )}, 0)},[Account].[Total]}';
SubsetCreateByMDX( zView,Mdx);
ViewSubsetAssign( zCube, zView, zDim, zView );

ViewExtractSkipRuleValuesSet( zCube, zView , 0 );
#I think If I put consolidation to 1 I will not have access to the consolidated element 'Total' so that's why I put it to 0
ViewExtractSkipCalcsSet ( zCube, zView, 1 );
ViewExtractSkipZeroesSet( zCube, zView , 1 );
Is there any Better way to do it and reduce the execution Time ?

Thank you In Advance

User avatar
Steve Rowe
Site Admin
Posts: 2137
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Problem With Slow process execution when Selecting child Elements and a single consolidated Element

Post by Steve Rowe » Wed Jan 06, 2021 3:21 pm

Remember that by turning the C levels on you are returning the C levels across all dimensions. Potentially a huge number of intersections.

You should set all the other dimensions in the view to use a level 0 subset and just have the exact C level you want in the other dimension.

HTH
Technical Director
www.infocat.co.uk

User avatar
PavoGa
Community Contributor
Posts: 479
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: Cleveland, Tennessee

Re: Problem With Slow process execution when Selecting child Elements and a single consolidated Element

Post by PavoGa » Wed Jan 06, 2021 4:19 pm

We have seen performance of MDX views vs. standard/classic views to be poor. Looking at your MDX code, it does not appear you are using alternate hierarchies, so building a classic view should not be a problem. I would suggest trying that.

I believe the performance problem is directly related to what Steve described. What I think happens is the engine builds the MDX view in its entirety so it is calculating all the consolidations before submitting to the METADATA/DATA procedures, whereas on a classic view it begins processing almost immediately. That is just a guess, but my experience has been the same view processing begins immediately in a classic view, while an MDX view is delayed before it begins processing and the classic view finishes much earlier. For that reason, and some others mentioned in the forum, for now we use MDX views on a limited basis.
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 2601
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Problem With Slow process execution when Selecting child Elements and a single consolidated Element

Post by Wim Gielis » Wed Jan 06, 2021 6:39 pm

PavoGa wrote:
Wed Jan 06, 2021 4:19 pm
We have seen performance of MDX views vs. standard/classic views to be poor. Looking at your MDX code, it does not appear you are using alternate hierarchies, so building a classic view should not be a problem. I would suggest trying that.

I believe the performance problem is directly related to what Steve described. What I think happens is the engine builds the MDX view in its entirety so it is calculating all the consolidations before submitting to the METADATA/DATA procedures, whereas on a classic view it begins processing almost immediately. That is just a guess, but my experience has been the same view processing begins immediately in a classic view, while an MDX view is delayed before it begins processing and the classic view finishes much earlier. For that reason, and some others mentioned in the forum, for now we use MDX views on a limited basis.
Hi

Why do you think the OP is using an MDX view instead of a classic view ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Using TI to change elemant names, like Biden to Trump

User avatar
PavoGa
Community Contributor
Posts: 479
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: Cleveland, Tennessee

Re: Problem With Slow process execution when Selecting child Elements and a single consolidated Element

Post by PavoGa » Wed Jan 06, 2021 8:01 pm

Wim Gielis wrote:
Wed Jan 06, 2021 6:39 pm
PavoGa wrote:
Wed Jan 06, 2021 4:19 pm
We have seen performance of MDX views vs. standard/classic views to be poor. Looking at your MDX code, it does not appear you are using alternate hierarchies, so building a classic view should not be a problem. I would suggest trying that.

I believe the performance problem is directly related to what Steve described. What I think happens is the engine builds the MDX view in its entirety so it is calculating all the consolidations before submitting to the METADATA/DATA procedures, whereas on a classic view it begins processing almost immediately. That is just a guess, but my experience has been the same view processing begins immediately in a classic view, while an MDX view is delayed before it begins processing and the classic view finishes much earlier. For that reason, and some others mentioned in the forum, for now we use MDX views on a limited basis.
Hi

Why do you think the OP is using an MDX view instead of a classic view ?
Because I glanced over it too quickly. Thanks for pointing that out. Sorry about that, OP!

I'll escort myself out...
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 2601
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Problem With Slow process execution when Selecting child Elements and a single consolidated Element

Post by Wim Gielis » Wed Jan 06, 2021 8:02 pm

We both think Steve’s reply is already what the OP needs 👍
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Using TI to change elemant names, like Biden to Trump

User avatar
PavoGa
Community Contributor
Posts: 479
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: Cleveland, Tennessee

Re: Problem With Slow process execution when Selecting child Elements and a single consolidated Element

Post by PavoGa » Wed Jan 06, 2021 9:21 pm

Wim Gielis wrote:
Wed Jan 06, 2021 8:02 pm
We both think Steve’s reply is already what the OP needs 👍
Yes, absolutely. Would not have even responded if I'd read it more closely. It suits me to just delete the extraneous posts.
Ty
Cleveland, TN

Post Reply