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.

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.

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:

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).

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.

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:

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:

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?

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.

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.

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.

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.