Very slow performance in TI using ViewZeroOut function

Post Reply
hugzer77
Posts: 4
Joined: Wed Dec 20, 2023 9:40 am
OLAP Product: TM1
Version: PAW 2.0.95
Excel Version: Excel 365 64bit

Very slow performance in TI using ViewZeroOut function

Post by hugzer77 »

Hello everyone,

I am currently working on a TI process to clean data from my cube over a given period. To do this, I use the ViewZeroOut function after having specified the view I want to clean.
Basically, I take all the N levels of my dimensions with the exception of the period function, where I take a specific value, which is also at leaf level -> every dimension subset is created by MDX and temporary, rendered static with the MDX set to empty through SubsetMDXSet( DimName , SubsetName, '')

Besides, I set the three extract conditions to 1 :
ViewExtractSkipCalcsSet ( CubeName , vSourceView ,1);
ViewExtractSkipZeroesSet ( CubeName , vSourceView ,1);
ViewExtractSkipRuleValuesSet ( CubeName , vSourceView ,1);

And then, I use the ViewZeroOut function on the view I just created.
This is when the ViewZeroOut takes three minutes to compile on its own and I don't understand why it does take that long (it takes less than 1 second if I comment the function). Can someone please relate from a similar problem or can explain me how does the ViewZeroOut work ?

I hope I've made my message clear.
Thanks in avance
User avatar
Elessar
Community Contributor
Posts: 360
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Very slow performance in TI using ViewZeroOut function

Post by Elessar »

Hi,

Is there a possibility that it is really a huge view and it should take so much time?
Have you switched Logging OFF temporary before zeroing out?
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 7th article - Development requirements.
User avatar
gtonkin
MVP
Posts: 1212
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: Very slow performance in TI using ViewZeroOut function

Post by gtonkin »

Hi,
Cannot see any need to use any of the ViewExtractSkip functions on a zero out.
Making the sets static as you explained is always a good idea where the view is a datasource.

Have you turned off logging or do you need it on?

Anything that could be causing this like intercube feeders and other rules?
lotsaram
MVP
Posts: 3668
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Very slow performance in TI using ViewZeroOut function

Post by lotsaram »

Try using bedrock.cube.data.clear for the same cube area and see if it is faster.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
hugzer77
Posts: 4
Joined: Wed Dec 20, 2023 9:40 am
OLAP Product: TM1
Version: PAW 2.0.95
Excel Version: Excel 365 64bit

Re: Very slow performance in TI using ViewZeroOut function

Post by hugzer77 »

Hi all,

Thank you for your replies.
Elessar wrote: Tue May 21, 2024 4:09 pm Hi,

Is there a possibility that it is really a huge view and it should take so much time?
Have you switched Logging OFF temporary before zeroing out?
It is a huge view but it should not take that much time. I have a similar cube and it takes around 1 second to execute so I expected a similar behavior here. The logging is always OFF for the cube I am working with.
gtonkin wrote: Tue May 21, 2024 4:10 pm Hi,
Cannot see any need to use any of the ViewExtractSkip functions on a zero out.
Making the sets static as you explained is always a good idea where the view is a datasource.

Have you turned off logging or do you need it on?

Anything that could be causing this like intercube feeders and other rules?
Okay I just deleted the ViewExtractSkip functions but nothing changed. I also tried to temporarily delete the rules but once again, it did not change anything.
lotsaram wrote: Wed May 22, 2024 7:22 am Try using bedrock.cube.data.clear for the same cube area and see if it is faster.
Maybe, I should try this, thank you.
User avatar
Steve Rowe
Site Admin
Posts: 2424
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Very slow performance in TI using ViewZeroOut function

Post by Steve Rowe »

Perhaps check out the dimension ordering versus the cube that is performing well?
Technical Director
www.infocat.co.uk
hugzer77
Posts: 4
Joined: Wed Dec 20, 2023 9:40 am
OLAP Product: TM1
Version: PAW 2.0.95
Excel Version: Excel 365 64bit

Re: Very slow performance in TI using ViewZeroOut function

Post by hugzer77 »

Steve Rowe wrote: Wed May 22, 2024 11:36 am Perhaps check out the dimension ordering versus the cube that is performing well?
You may be right. The cube which is performing well has this kind of order : Product, Period, Season, Version, Measure.
On the other hand, the cube with the slow performance looks like Version, Season, Product, Period, Measure (approximately).
At first, we wanted this cube to have the same dimensions order than the other one but it would consume too much memory so we followed the dimension reordering recommendation suggested by TM1 (through Architect) and we obtained the result above and saved a significant amount of memory.

Can dimensions reordering cause latency in such processes ? Even for the easiest case of cleaning data with a ViewZeroOut ?
Wim Gielis
MVP
Posts: 3129
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Very slow performance in TI using ViewZeroOut function

Post by Wim Gielis »

This is strange behavior. What does the }Stats cubes say about number of data points and similar measures? Did someone inflate the number of cells from data input at very high level ? Stet function in the rules or other things ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply