Planning Analytics using twice the memory of 10.1

User avatar
paulsimon
MVP
Posts: 651
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by paulsimon » Fri Aug 03, 2018 8:19 pm

Hi qml

We no longer use Cognos BI to query TM1. However, when we did have it doing that in 10.1 we didn't notice any dramatic increase in RAM when the first cube query was run.

This still seems to be a problem that is unique to PA.

I suspect that if 10.1 was generating MUNs when a cube query occrured, that it was only doing it for the dimensions in that cube, and not every single dimension on the server.

Regards

Paul Simon

User avatar
paulsimon
MVP
Posts: 651
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by paulsimon » Fri Aug 03, 2018 11:05 pm

Hi

The attached might help anyone else having problems with an explosion in size due to the generation of all possible MUNs at start up in PA 2.0

It uses the TM1RESTAPI to get a list of all dimensions on the server, with their Element and MUN Count and the Ratio between them. It seems to be the MUN count that is the important thing. The Ratio just indicates cases where the dimension has a lot of alternate hierarchies leading to potentially a lot more MUNs than elements.

Use at your own risk. It only works in TM1 Authentication Mode.

For those of you that don't know, to get the TM1RESTAPI working you have to put HTTPPortNumber= some value eg 8010 in your TM1S.CFG, and that is the Port Number that you need to enter on this sheet. The UseSSL flag just determines whether the call to the TM1RESTAPI needs to use http or https.

Regards

Paul Simon
Attachments
TM1DimElemAndMUNCountLister 003.zip
(33.53 KiB) Downloaded 31 times

User avatar
Steve Rowe
Site Admin
Posts: 1828
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Planning Analytics using twice the memory of 10.1

Post by Steve Rowe » Mon Aug 06, 2018 9:43 am

Thanks to lotsaram and Paul for sharing this, a very interesting read.

If you are impacted by this you could ended up quite blocked.

- Convert dimension to hierarchy based, this may be complex and won't always result in a natural query structure for the user. Plus we do not (yet, when is it coming??) have DBR type reporting that can address hierarchies. Irrespective existing reporting would need updating.
- Potentially switch to a multi- dimension approach (time, one lump or two) which hierarchies really stops us needing to do. Major rebuild.

Will also impact customers who go to "TM1 11", new server build and stay on Perspectives.

Great info to have when designing a system.
Cheers

User avatar
Mike Cowie
Site Admin
Posts: 440
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: TM1, MSAS
Version: Anything thru 11.x
Excel Version: 2003 - 2016
Location: Alabama, USA
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by Mike Cowie » Mon Aug 06, 2018 4:03 pm

Big thanks to Lotsa, Paul and others for this-- has been a really interesting thread. If anyone needs a pre-REST API method of counting MUNs, just create an MDX subset like this and count/SUBSIZ the elements in that dynamic subset:

Code: Select all

GENERATE( TM1SUBSETALL( [Your Dimension Name] ), DESCENDANTS( [Your Dimension Name].CurrentMember ) )
Should match up exactly with the REST API's member/MUN count across the dimensions I'd tested.

Have definitely seen some RAM increases moving to PA Local 2.0.x, especially with daily time and larger dimensions with alternate hierarchies or rollups. I also isolated, in its own server instance, a larger dimension with a single hierarchy (approx. 50,000 elements) which was 1:1 elements to MUNs and saw an increase of around 50% in RAM usage comparing 10.2.0 to 2.0.5. This was one dimension in isolation, so I'm not saying you'll see a 50% jump in RAM if you upgrade-- just that there's clearly a general RAM increase for dimensions going to PA Local 2.0.x regardless of any MUN/Element factor. I didn't see an increase like this from 10.2.0 to 10.2.2.

Last note is that any "Memory Used" values you can see in Perspectives' Properties pane, which were already pretty inaccurate, are now even more useless in 2.0.x. They don't seem to reflect, at all, the impact of what 2.0.x does to precache all the MUNs for a dimension (unfortunately).

Hope that helps!
Mike

User avatar
jim wood
Site Admin
Posts: 3617
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by jim wood » Mon Aug 06, 2018 5:01 pm

I do wonder if this memory increase is down to them adding the attribute based hierarchies? I doubt we'd be able to prove it either way, but for me the biggest difference between the 2 versions,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
paulsimon
MVP
Posts: 651
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by paulsimon » Tue Aug 07, 2018 1:33 pm

Hi

We are still awaiting a response from IBM.

I have trimmed down our Day level dimension which we only use for user logging so that it only has a single hierarchy. This saved around 4GB of the 13GB increase (13.9 to 27.4GB).

The system has been around for 5 years or so and had lots of old unused cubes. I have deleted them and all the related dimensions some of which had highish (10,000+) numbers of elements with lots of alternate hierarchies.

The server is now using 17.2GB. That is still 3.3GB more than it did on 10.1 with the old cubes. I now have to do some further work to cure the problems caused by deleting the old cubes, which I was hoping to avoid until after the upgrade completed.

Obviously the biggest reduction came from stripping out hierarchies from the Day level dimension so thanks to Lotsaram for that. However, we were only able to do this because most of our data is at the monthly level. In a retail environment where sales at Day level are key this is obviously going to be much more of a problem.

Some of the alternate hierarchies in our dimensions that we have are effectively just combining different attributes so you have eg a split by one categorisation of codes within another categorisation of codes. Potentially we could do this by creating the new Hierarchies. However, virtually all of the system is built in TM1 Web and this will not recognise hierarchies. Similarly we have a number of users who use Perspectives via Citrix. This also will not recognise hierarchies. As the client will not pay the license for PAW, that only leaves PAX as a possible client that can recognise Hierarchies. That would require a lot of retraining of users and re-development of the system. It would need to be deployed via Citrix to reach our remote partners.Even then as I understand it, Hierachies can only be used in Exploration Views and Quick Reports which are limited in their formatting cababilities compared to Dynamic Reports, the new name for Active Forms. Therefore, we are still going to need conventional alternate hierarchies in Dimensions, as well as the new Hierarchies. If we are still going to need alternate hierarchies then we are still going to have the problem of the huge increase in size caused by the MUN issue.

It would seem that the obvious thing to do would be to make the generation of all possible MUNs optional, ideally on a Dimension by DImension basis.

Regards

Paul SImon

User avatar
PavoGa
Community Contributor
Posts: 264
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Planning Analytics using twice the memory of 10.1

Post by PavoGa » Thu Aug 09, 2018 12:46 pm

Mike Cowie wrote:
Mon Aug 06, 2018 4:03 pm
Big thanks to Lotsa, Paul and others for this-- has been a really interesting thread. If anyone needs a pre-REST API method of counting MUNs, just create an MDX subset like this and count/SUBSIZ the elements in that dynamic subset:

Code: Select all

GENERATE( TM1SUBSETALL( [Your Dimension Name] ), DESCENDANTS( [Your Dimension Name].CurrentMember ) )
Should match up exactly with the REST API's member/MUN count across the dimensions I'd tested.

Have definitely seen some RAM increases moving to PA Local 2.0.x, especially with daily time and larger dimensions with alternate hierarchies or rollups. I also isolated, in its own server instance, a larger dimension with a single hierarchy (approx. 50,000 elements) which was 1:1 elements to MUNs and saw an increase of around 50% in RAM usage comparing 10.2.0 to 2.0.5. This was one dimension in isolation, so I'm not saying you'll see a 50% jump in RAM if you upgrade-- just that there's clearly a general RAM increase for dimensions going to PA Local 2.0.x regardless of any MUN/Element factor. I didn't see an increase like this from 10.2.0 to 10.2.2.

Last note is that any "Memory Used" values you can see in Perspectives' Properties pane, which were already pretty inaccurate, are now even more useless in 2.0.x. They don't seem to reflect, at all, the impact of what 2.0.x does to precache all the MUNs for a dimension (unfortunately).

Hope that helps!
Mike
Did the same by removing all but three dimensions, two of over 300K members and one with 13K members. The element to MUN ratio is 1:1 on all three dimensions.

To test, restarted the server, opening and closing the subset editor on each dimension starting with the largest, reopening the dimension and running:

Code: Select all

[dimname].members
# (provides the same results as the GENERATE code above)
then closing the dimension again. I took memory readings after each operation.
  • PA2.0 started out using approximately 44% more memory than 10.2.2. (541.6MB vs 783.1)
  • Opening the subset editor on each dim increased the memory usage in both by a few % points. (<3.5%, <1.6%, < 1%)
  • Opening the subset editor and running the MDX statement from above significantly increased the memory usage in 10.2.2, not so much in PA2.0 (10.2 vs PA 2.0 by dim: 19.9% vs 5.3%, 14.1% vs 1.6%, .2% vs .1%)
So there may be merit to the IBM claim 10.2 does the same thing to some extent. The gap in memory usage dropped 55% after opening the 3rd dimension and running the MDX.
Ty
Cleveland, TN

User avatar
jim wood
Site Admin
Posts: 3617
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by jim wood » Thu Aug 09, 2018 5:13 pm

This goes back to the point I raised earlier regarding attribute based alternate hierarchies. Performance would be hit if the dimension had run in memory before the alt hierarchy, if it was already it would be an advantage. You could also argue it would have initial performance updates after restart. Has anybody tracked the memory usage impact of running an update to the dimension in both? When updating the dimension does PA then fully recompile again? Does this have an impact on dimension update times in TI?
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

lotsaram
MVP
Posts: 3148
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Planning Analytics using twice the memory of 10.1

Post by lotsaram » Thu Aug 09, 2018 7:15 pm

jim wood wrote:
Thu Aug 09, 2018 5:13 pm
This goes back to the point I raised earlier regarding attribute based alternate hierarchies. Performance would be hit if the dimension had run in memory before the alt hierarchy, if it was already it would be an advantage. You could also argue it would have initial performance updates after restart. Has anybody tracked the memory usage impact of running an update to the dimension in both? When updating the dimension does PA then fully recompile again? Does this have an impact on dimension update times in TI?
The attribute based hierarchies aren't dynamic. Although yes there is a single function to build an attribute based hierarchy this just builds the hierarchy at a point in time. If attribute values subsequently change, or new leaf elements are added to the dimension this won't impact the hierarchy. (and in fact the last time I tested it the function actually fails if the hierarchy already exists; you need to destroy and then recreate it). Therefore in a production system you would never actually use this feature as is, rather you would use standard hierarchy functions to build and maintain attribute based hierarchies. Then you can control things like the hierarchy name, the number of levels, what to do with blank attribute values, unwinding vs. rebuilding, etc. All of which you can't do using the automatically generated attribute hierarchies.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
paulsimon
MVP
Posts: 651
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by paulsimon » Mon Aug 20, 2018 10:47 pm

Hi

An update on this.

Still no response from IBM Support or Development :(

I reduced memory requirements by cleaning up all old cubes. This is something that we were going to do anyway, but ideally the other side of the upgrade. I now need to do more testing to ensure that removing references to old cubes has not upset anything.

I also removed all but the bare minimum of hierarchy from the Day level dimension.

This got the memory down to a more reasonable 17GB but still around 4GB more than the 10.1 Server needed with all those old cubes and a fully functional Day level dimension.

That then got us to the point where the TM1 Service would at least start. However, as soon as we put any serious activity through it, ie our typical overnight dim builds and loads, then

On Windows Server 2016 the TM1 Service crashed so badly that it actually took out the Remote Desktop capability of the Server. It was some time before we worked out that the Server was still up but you just could not connect to it.

On Windows Server 2008 the TM1 Service was failing at random points saying that it was unable to write to disk even though there was several GB of free disk space. In some cases it was failing trying to write .vue$ files, in other cases .sub$ files, and in some cases the tm1s.log.

We eventually found a post on the IBM Website which said that random write failures can be caused by the TM1S.CFG setting

LockPagesInMemory=T

In theory this setting is supposed to improve performance by stopping Windows from paging out virtual memory pages to disk when the TM1 Server is idle. In a typical scenario the TM1 Service is idle for some time until someone comes along with a query, so it seemed like a good idea to set this. The description of this TM1S.CFG parameter recommends that it should be set on all Windows 64 bit servers. However, the advice we eventually found on the IBM Support site is that if you encounter random errors writing to disk that you should set this to False. There was no explanation as to why a setting related to virtual memory management should prevent normal files being written to disk. There was no explanation of the likely impact of being unable to use a recommended setting. Anyway changing the setting does seem to have cured the problem.

It would be nice if, having found this problem, IBM had thought to change the documentation on this TM1S.CFG setting said don't ever set this to True, since it doesn't work properly and will cause your server to crash.

I don't think that this File Write Error is related to the original problem of the TM1 Service using far more RAM than before. We did run tests with a minimal CFG and the default if this parameter is missing is False.

Regards

Paul Simon

User avatar
paulsimon
MVP
Posts: 651
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by paulsimon » Wed Sep 26, 2018 8:36 pm

Hi

Just to let you know IBM Support have now reproduced the considerable increase in memory that we were seeing when upgrading from 10.1.1 to PA 2.0.5 and have referred this to Engineering for some sort of improvement.

Regards

Paul Simon

Paul Segal
Community Contributor
Posts: 251
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Planning Analytics using twice the memory of 10.1

Post by Paul Segal » Thu Sep 27, 2018 8:15 am

Good work Paul, I know how much effort it takes to get IBM to this stage. FWIW, I believe we're seeing similar memory issues.

Regards
Paul

User avatar
paulsimon
MVP
Posts: 651
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Planning Analytics using twice the memory of 10.1

Post by paulsimon » Fri Sep 28, 2018 8:39 pm

Hi Paul

It is likely to be some time before IBM provide a solution. It might be quicker for you to change your design to reduce the number of alternate hierarchies and remove any old cubes, etc, to cut down on memory requirements.

We have encountered several issues when upgrading to PA. I will post them when I get more time, along with the workarounds that we used. I have certainly been writing a lot of VBA recently to fix workbooks, etc.

Regards

Paul Simon

Post Reply