View Size issue

Post Reply
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

View Size issue

Post 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
Attachments
ViewError.png
ViewError.png (2.61 KiB) Viewed 9393 times
Wim Gielis
MVP
Posts: 3103
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: View Size issue

Post 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
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
Paul Segal
Community Contributor
Posts: 306
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: View Size issue

Post 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.
Paul
User avatar
PavoGa
MVP
Posts: 616
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: Charleston, Tennessee

Re: View Size issue

Post 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
Ty
Cleveland, TN
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: View Size issue

Post 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)
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: View Size issue

Post 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?
Last edited by ExApplix on Wed Mar 14, 2018 2:26 pm, edited 1 time in total.
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: View Size issue

Post 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 );
User avatar
PavoGa
MVP
Posts: 616
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: Charleston, Tennessee

Re: View Size issue

Post 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. :)
Ty
Cleveland, TN
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: View Size issue

Post 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?
Wim Gielis
MVP
Posts: 3103
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: View Size issue

Post 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
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
User avatar
PavoGa
MVP
Posts: 616
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: Charleston, Tennessee

Re: View Size issue

Post 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.
Ty
Cleveland, TN
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: View Size issue

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