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

[pikolikoli]

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

[Steve Rowe]

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

[PavoGa]

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.

[Wim Gielis]

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 ?

[PavoGa]

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...

[Wim Gielis]

We both think Steve’s reply is already what the OP needs

[PavoGa]

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.