Page 1 of 1

View Size issue

Posted: Wed Mar 14, 2018 1:10 am
by ExApplix
Hello everyone, I need some help/direction for the below issue. Your guidance will be appreciated in this matter.

I am constructing a View in the Prolog of a TI. The view consists of LEAF elements from all of the dimensions but one dimension subset contains only one element which is a Consolidated node.

The View works fine without any errors if the Consolidated element being used contains a smaller number of child elements. But it fails if the Consolidation contains thousand of child elements. Please note in both cases the subset of this dimension will only contain one single consolidated element. I am also Skipping the Zeros in the View. There are no rules on the cube.

Code: Select all

VIEWEXTRACTSKIPCALCSSET( cubTarget , sDataTargetView , 0);   #have to keep this as 0 because my source element is consolidated element
VIEWEXTRACTSKIPZEROESSET( cubTarget , sDataTargetView , 1);
When the Consolidated element is large it throws the following error in the TM1S.LOG. See the pool size is well below the MaximumViewSize setting in the TM1S.CFG but still it fails. The TI shows the error as in the attached screenshot "Error opening the log file".

Code: Select all

TM1.Server.Memory   al_Alloc() outOfMemory Exception <<< MEMORY_TEMP_POOL_EXCEEDED >>> MaximumViewSize memory exceeded - apifunc# "196" - pool# "0" - poolsize "535201358.000000"

Code: Select all

MaximumViewSize=1600
MTQ=8

Re: View Size issue

Posted: Wed Mar 14, 2018 7:23 am
by Wim Gielis
Hello,

Never seen this one before.

Does it work if you take the leaf elements instead of the consolidation?

Are the other selections in the dimensions also large and containing 1000s of elements?

Wim

Re: View Size issue

Posted: Wed Mar 14, 2018 8:15 am
by Paul Segal
You don't say what version you're on - I've just had this on a PAX report that normally runs OK on 10.2.2 and threw this error in the log in 11.030. FWIW, I increased the maximumviewsize and it went away - you can increase this dynamically, so it should be easy to test higher maximumviewsize settings. I was surprised to get the error, though.

Re: View Size issue

Posted: Wed Mar 14, 2018 1:57 pm
by PavoGa
One thing to remember is that the ViewExtract... functions do not have the same effect as the ViewRowSuppressZeroesSet, ViewColumnSuppressZeroesSet and ViewSuppressZeroesSet. The first are for views used as data sources and I do not think they have any effect on a datasourceview you then try to open. Use the latter for views you build for users to open and view.

Another example of the mixup on these functions is people setting dimension and column orders, title dimensions on a view used strictly for a data extract. Those functions have no effect in that case either.

Ty

Re: View Size issue

Posted: Wed Mar 14, 2018 2:01 pm
by ExApplix
Wim Gielis wrote: Wed Mar 14, 2018 7:23 am Are the other selections in the dimensions also large and containing 1000s of elements?
Hi Wim, most of the other dimensions are fairly small (less than 1000). One dimension has about 8000. But the dimension from which I am picking up the consolidated element is large (~100K)

Re: View Size issue

Posted: Wed Mar 14, 2018 2:04 pm
by ExApplix
Paul Segal wrote: Wed Mar 14, 2018 8:15 am You don't say what version you're on - I've just had this on a PAX report that normally runs OK on 10.2.2 and threw this error in the log in 11.030. FWIW, I increased the maximumviewsize and it went away - you can increase this dynamically, so it should be easy to test higher maximumviewsize settings. I was surprised to get the error, though.
I am on version 10.2.2 FP6. I know increasing the MaximumViewSize may fix it but in my case the pool size is only 500Mb while my setting in TM1S.CFG is about 1.5 Gb. Which is what the confusion is about. Also the weird thing is that my View size should be fairly small as my subset has only one Consolidated element.

You mentioned that we can increase the MaximumViewSize "dynamically". Can you explain how?

Re: View Size issue

Posted: Wed Mar 14, 2018 2:18 pm
by ExApplix
PavoGa wrote: Wed Mar 14, 2018 1:57 pm One thing to remember is that the ViewExtract... functions do not have the same effect as the ViewRowSuppressZeroesSet, ViewColumnSuppressZeroesSet and ViewSuppressZeroesSet. The first are for views used as data sources and I do not think they have any effect on a datasourceview you then try to open. Use the latter for views you build for users to open and view.
The View which I am building is used as a Data Source. Logically these other functions which you have mentioned should not make any difference as they are for Cube Views. But still I tried also putting them in the code and still it did not help.

Code: Select all

ViewSuppressZeroesSet( cubTarget , sDataTargetView , 1);
ViewColumnSuppressZeroesSet( cubTarget , sDataTargetView , 1 );
ViewRowSuppressZeroesSet( cubTarget , sDataTargetView , 1 );

Re: View Size issue

Posted: Wed Mar 14, 2018 4:04 pm
by PavoGa
ExApplix wrote: Wed Mar 14, 2018 2:18 pm
PavoGa wrote: Wed Mar 14, 2018 1:57 pm One thing to remember is that the ViewExtract... functions do not have the same effect as the ViewRowSuppressZeroesSet, ViewColumnSuppressZeroesSet and ViewSuppressZeroesSet. The first are for views used as data sources and I do not think they have any effect on a datasourceview you then try to open. Use the latter for views you build for users to open and view.
The View which I am building is used as a Data Source. Logically these other functions which you have mentioned should not make any difference as they are for Cube Views. But still I tried also putting them in the code and still it did not help.

Code: Select all

ViewSuppressZeroesSet( cubTarget , sDataTargetView , 1);
ViewColumnSuppressZeroesSet( cubTarget , sDataTargetView , 1 );
ViewRowSuppressZeroesSet( cubTarget , sDataTargetView , 1 );
To be clear, are you getting your error when the view is processing in the METADATA/DATA tabs or are you getting the error when you try to open it in cube viewer? If the former, frankly, I've never heard of a cube view used as a datasource being too large for TI to handle (not saying it may not be possible, just never heard tell of such). If the ViewSuppress functions still do not allow the view to open, well, as stated above by other posters, your view is too large to open with the available memory...

The point I was trying to make is the ViewExtract functions have no effect when opening a view with cube viewer.

Btw, ViewSuppressZeroesSet is the same as using both ViewColumnSuppressZeroesSet + ViewRowSuppressZeroesSet. :)

Re: View Size issue

Posted: Wed Mar 14, 2018 6:36 pm
by ExApplix
PavoGa wrote: Wed Mar 14, 2018 4:04 pm To be clear, are you getting your error when the view is processing in the METADATA/DATA tabs or are you getting the error when you try to open it in cube viewer?
Btw, ViewSuppressZeroesSet is the same as using both ViewColumnSuppressZeroesSet + ViewRowSuppressZeroesSet. :)
The error appears on the Prolog while it is creating this View. My issue is TI Process related - not related to the Cube Viewer.

You are right that the ViewSuppressZeroesSet = ViewColumnSuppressZeroesSet + ViewRowSuppressZeroesSet. I just put everything to make sure nothing is missed. Also technically these will not be applicable in my case as my problem is in Prolog tab and not in the Cube Viewer.

I changed the MaximumViewSize setting from 1.5 Gb to 2.5 Gb but still issue persist. Any other thoughts?

Re: View Size issue

Posted: Wed Mar 14, 2018 7:59 pm
by Wim Gielis
What about my earlier question ?
Wim Gielis wrote: Wed Mar 14, 2018 7:23 amDoes it work if you take the leaf elements instead of the consolidation?

Wim

Re: View Size issue

Posted: Wed Mar 14, 2018 8:40 pm
by PavoGa
ExApplix wrote: Wed Mar 14, 2018 6:36 pm
PavoGa wrote: Wed Mar 14, 2018 4:04 pm To be clear, are you getting your error when the view is processing in the METADATA/DATA tabs or are you getting the error when you try to open it in cube viewer?
Btw, ViewSuppressZeroesSet is the same as using both ViewColumnSuppressZeroesSet + ViewRowSuppressZeroesSet. :)
The error appears on the Prolog while it is creating this View. My issue is TI Process related - not related to the Cube Viewer.

You are right that the ViewSuppressZeroesSet = ViewColumnSuppressZeroesSet + ViewRowSuppressZeroesSet. I just put everything to make sure nothing is missed. Also technically these will not be applicable in my case as my problem is in Prolog tab and not in the Cube Viewer.

I changed the MaximumViewSize setting from 1.5 Gb to 2.5 Gb but still issue persist. Any other thoughts?
Gotcha. And you cannot open the server log file or the process error log file with Notepad++? Might help posting more of the code. I've got a cube I use regularly as a datasource that has one dimension with approx 300K elements in it, seven total dims and it has no problem. It would be interesting to know exactly what line it is failing on.

Re: View Size issue

Posted: Thu Mar 15, 2018 12:33 am
by ExApplix
ISSUE RESOLVED!!!

SOLUTION:
I just changed the method of creation of the Subset from standard way SUBSETELEMENTINSERT() to SUBSETCREATEBYMDX() and it is now working. The

Looks like there is a bug in version 10.2.2 FP6 (or may be other version too). It appears that in the background it includes all the children of the consolidation in the VIEWSUBSETASSIGN() and put unnecessary load on the View. Now the same TI is executing at least 15 times faster than before.