Page 2 of 2

Re: Planning Analytics using twice the memory of 10.1

Posted: Fri Aug 03, 2018 8:19 pm
by paulsimon
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

Re: Planning Analytics using twice the memory of 10.1

Posted: Fri Aug 03, 2018 11:05 pm
by paulsimon
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

Re: Planning Analytics using twice the memory of 10.1

Posted: Mon Aug 06, 2018 9:43 am
by Steve Rowe
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

Re: Planning Analytics using twice the memory of 10.1

Posted: Mon Aug 06, 2018 4:03 pm
by Mike Cowie
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

Re: Planning Analytics using twice the memory of 10.1

Posted: Mon Aug 06, 2018 5:01 pm
by jim wood
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.

Re: Planning Analytics using twice the memory of 10.1

Posted: Tue Aug 07, 2018 1:33 pm
by paulsimon
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

Re: Planning Analytics using twice the memory of 10.1

Posted: Thu Aug 09, 2018 12:46 pm
by PavoGa
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.

Re: Planning Analytics using twice the memory of 10.1

Posted: Thu Aug 09, 2018 5:13 pm
by jim wood
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?

Re: Planning Analytics using twice the memory of 10.1

Posted: Thu Aug 09, 2018 7:15 pm
by lotsaram
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.