Create View for Cube Zero Out Performance Questions

[PlanningDev]

We have built a master process that can be called to create a view to be used for zero'ing data out from a cube.

At first we used the ViewExtractSkipZeroesSet but on larger views we ran into performance issues. My question here is do we need to use both ViewExtractSkipZeroesSet and ViewSuppressZeroesSet? Or simply ViewSuppressZeroesSet if we are only trying to use ViewZeroOut?

It appeared that when only using ViewExtractSkipZeroesSet the ViewZeroOut was reading through more records than when we used ViewSuppressZeroesSet. The ultimate problem here was that PI was reserving memory for what seemed to be a much larger data set and it ran the server out of memory vs when we added ViewSuppressZeroesSet.

[tomok]

You don't need to set either on of these. Just leave the default setting that is in place after creating the view, which will have the optimal setting for issuing a ViewZeroOut.

[PlanningDev]

So I guess my question is, how does the ViewZeroOut function work? Is it looking at all cells in the view? or does using either of the Suppress functions have an affect? Are you positive that creating a view inside the TI code vs through the wizard doesn't behave differently for ViewZeroOut?

[rmackenzie]

Firstly, note the remark in the documentation regarding ViewSuppressZeroesSet for the entry on ViewExtractSkipZeroesSet:

Note that this function does not suppress the display of zeroes in a view; it only excludes zeroes from a view extract. Use ViewSuppressZeroesSet to suppress the display of zeroes in a view.

When you set a TI to use a cube view as its data source, you are using a view extract, not a view the way you see them in the cube view. Sure, you can set the process datasource to a cube view that you see in the cube viewer, but the process treats it as a view extract. You can reasonably criticise this setup as it is not at all transparent that this is happening. Some people on this forum argued in the past that there should be a different visible object list to differentiate between views and view extracts and I fully support that. Anyway, the upshot of this is that ViewSuppressZeroesSet isn't relevant to view (extract)s that you processing in TI.

Secondly, it doesn't matter how you create the code, either 'by hand' or using the wizard, when you have this statement:

Code: Select all

ViewCreate ( sCubeName, sViewName );

The view extract property regarding zero-suppression is defaulted to 1. I.e. you don't need to write

Code: Select all

ViewExtractSkipZeroesSet ( sCubeName, sViewName, 1 );
ViewCreate ( sCubeName, sViewName );

(but you could if you really wanted to to make the code super-clear about what you want to happen).

So, to address your question:

So I guess my question is, how does the ViewZeroOut function work? Is it looking at all cells in the view?

It zeroes out your view extract based on how you defined what should be in and out of the view. If you zero-suppress it (either explicitly or per the default) then it zeroes out the input data at leaf level in the view extract. You can include consolidations and rule-based values in the extract too, but the zero out won't apply to those values. If you unsuppress zeroes in your TI by explicitly writing:

Code: Select all

ViewExtractSkipZeroesSet ( sCubeName, sViewName, 0 );

Then you risk having a massive performance issue as the process will trawl through the empty intersections of the cube. However, there may be times you want to do this and you control the performance issues by carefully using ViewSubsetAssign to set-up the view extract such that unsuppressing zeroes will be done in a controlled manner.

...does using either of the Suppress functions have an affect?

No, only ViewExtractSkipZeroesSet is relevant.

Are you positive that creating a view inside the TI code vs through the wizard doesn't behave differently for ViewZeroOut?

The wizard just automates code-writing for you, it isn't doing anything different to you coding it by hand, except you sacrifice control over what is happening as the wizard is only capable of writing code to the level that it has been programmed to do so whereas you can make your zero out processes act in many different ways according to your requirements.

[Steve Rowe]

Also, don't forget if your cube has rules you need to use ViewExtractSkipRuleValuesSet as well or its probable that the viewextract will evaluate ruled values before the VZO executes, also killing performance...
Cheers,

[lotsaram]

Robin's explanation is really good. I would also add that in 9.5.2 and 10.1 I have found it best to explicitly set ViewExtractSkipCalcsSet to FALSE (the default is TRUE) as otherwise if a subset assigned to the view extract contains consolidations, or only consolidations then nothing will be cleared. I noticed this recently and don't recall this being the case in earlier versions, but perhaps it has always been this way.

[Steve Rowe]

Isn't that a bug?
If you have any consolidations (as opposed to only) in a subset and ViewExtractSkipCalcsSet = TRUE then there must be N intersections in your view which would be cleared. Are you saying that you need to set the state to False for data to get cleared?

If you have a subset which is all C then your view can have no N intersections and so there is no data to clear irrespective of the state of ViewExtractSkipCalcsSet .

Confused.....

[lotsaram]

Isn't that a bug?
If you have any consolidations (as opposed to only) in a subset and ViewExtractSkipCalcsSet = TRUE then there must be N intersections in your view which would be cleared. Are you saying that you need to set the state to False for data to get cleared?

If you have a subset which is all C then your view can have no N intersections and so there is no data to clear irrespective of the state of ViewExtractSkipCalcsSet .

Confused.....

yeah wasn't very clear, if subset contains only C elements then no data is cleared.