First time opening the cube takes 5mins

[kudzis]

Hello,

I have rules and feeders heavy project with may interconnected cubes. When I'm opening large cube for the first time after server restart, it takes ~5min to open. After that, it works flawlessly (response time is always <2sec) in the same tool, reopens lightning-fast.

If I open a cube in other TM1 tool, opening takes long time again. For example, if I open it in Perspectives, it takes 5mins for the first opening. After that, if I open it in Web Contributor, it takes 5mins again.

In cube properties, "Load on demand" is unchecked.

Is there a way to "cache" the cubes somehow to avoid this lag in first time opening? My TM1 server is stopped and started every night for backups, so every morning client complains for long opening times.

[qml]

ViewConstruct

This is a TM1® TurboIntegrator function, valid only in TurboIntegrator processes.

This function constructs, pre-calculates, and stores a stargate view in memory on a server.

This function is useful for pre-calculating and storing large views so they can be quickly accessed after a data load or update.

Syntax
ViewConstruct(CubeName, ViewName);

Example
ViewConstruct('99sales', '1st Quarter Actuals')

This example generates and stores the data for 1st Quarter Actuals, which is a public view of the 99sales cube.

[lotsaram]

Is there a way to "cache" the cubes somehow to avoid this lag in first time opening? My TM1 server is stopped and started every night for backups, so every morning client complains for long opening times.

Yes you can schedule a process using ViewConstruct to pre-calculate and store views for fast retrieval later. NOTE this will only work effectively if cube data is not changing!

However based on your description it sounds like there is something wrong with the fundamental design if views are taking 5 minutes to open. Failing a review or reworking of the design you might want to consider putting in one or more "break points" where you use TI to copy from one cube to another rather than rules or else have a "reporting version" which is value input by TI reading from the rule calculated version and populated overnight. This would solve your immediate performance issues for users querying reports.

Why would you stop the server each night for backups? This is totally unnecessary. You can take a copy of the data directory without unloading the model from memory. However, you should save data first so that the disk based copy of the model is in sync with the in memory model (note a save data will also clear the cache and require rebuilding views.)

[qml]

It's still advisable to recycle the server, say, once a week. Then the problem of the first opening would happen more rarely, but it would happen nonetheless. I think it's definitely worth looking at the overal design and possibly changing some of the rules into TI or making other changes in the model.
However, the simple and most immediate solution would be to schedule ViewConstruct after the server gets up.

[David Usherwood]

@lotsa - I don't think that SaveDataAll clears the calculation cache - can you confirm? I don't see why it should as no values have been updated. It may just clear the view caches but I've never been convinced they help all that much.
@qml - can you explain why you consider a weekly restart to be 'advisable'?

[Catherine]

note a save data will also clear the cache and require rebuilding views.

I don't agree with you. A save data does not clear the cache if there is no entry in the cube.

[qml]

@qml - can you explain why you consider a weekly restart to be 'advisable'?

Back in the old day when I worked for an Iboglix partner, that was the official guideline we got from the vendor. I think I've also seen it in some sort of document issued by IBM, but I can't recall exactly - I'll try and find it.
The reason for weekly restarts would be to get rid of garbage memory that TM1 does not return to the OS and to improve overall stability that can degrade with time. This does not mean that if you do not bounce your server every week it will necessarily become unstable - I've seen TM1 services to run for months with no issues. However, I can't imagine any modern piece of software of this size to have absolutely no memory leaks and no stability issues. So to me setting up some sort of server recycling shedule makes absolute sense, but that it should be a weekly restart is not exactly my idea.

Also, @lotsa, from my experience SaveData does not purge stargate views from memory.

Edit: found the technote on server recycling.

https://www-304.ibm.com/support/docview ... wg21454290

8. Even though TM1’s memory management is very stable and some customers leave their TM1 servers running for weeks or months at a time, the best practice is to restart it at least once a week. Many customers restart it nightly after running their administrative chores and performing a SaveDataAll. Such periodic recycling ensures a more compact and efficient storage scheme.

If the TM1 server is only restarted once a week, the database should still be saved nightly. This can easily be done by scheduling a chore with a SaveDataAll on the Epilog tab of a TI process. Doing this nightly saves time loading the data when the server is restarted, as the tm1s.log will not be so large. It also reduces recovery time in the case of hardware or software failure.

[Alan Kirk]

note a save data will also clear the cache and require rebuilding views.

I don't agree with you. A save data does not clear the cache if there is no entry in the cube.

Agreed. I have a cube which is purely calculated. Performance Monitor showed the amount of memory used for calculations. I made some changes in other cubes which did not affect the calculated one, did a data save, and got no change for the memory used. As soon as the interface which refreshed the underlying cubes ran later in the hour, though... that dropped the memory used for calculations back to zero.

[Alan Kirk]

@qml - can you explain why you consider a weekly restart to be 'advisable'?

I know where your thoughts are going on that one... the less you restart the server, the more cached results you have available. However if you're still stuck in the 32 bit badlands, and are sticking a fork into the upper reaches of memory usage on a regular basis, you either (a) restart on an almost daily basis or (b) wait for the message that you get later in the day that the server has run out of memory and is restarting itself. There's pretty much a one-to-one correlation between busy days when I don't restart the server before hours, and the days that we get that message. It still happens occasionally with a server restart, but much, much less frequently; maybe once every couple of weeks at most.

In theory that should be less of an issue with 64 bit but if I had a model which is pushing the envelope of physical memory, I'd probably still be inclined to "clean out the pipes" periodically.

[paulsimon]

Kudzis

Do you have a default view defined on your cube? If not then perhaps the system default that it is opening is a top level view which selects the top most element in each dimension, and therefore takes a while to calculate. If most users don't need that top level view then saving a public default view with element selections somewhat lower down the hierarchy can make the cube open faster. I sometimes add zSelect elements to one or more dimensions, and make this the selection in the Default View. In this way you can make the Default View open on something that everyone can have access to, and which will never have data (you can enforce that using rules). It also prompts them to select something different.

It will still take 5 minutes when someone first asks for the top level view, however, so pre-caching using TI overnight is probably a good idea. Even if you move to a Weekly Service restart, on the assumption that you are changing data at least once a day, then the pre-caching will still help.

I am guessing that from the fact that you say that you have many linked cubes that you have a planning application. However, I would also guess that you are reporting from it too.

I can see why you would need the linked cubes on the planning side, and it is probably unavoidable if users want to instantly see the impact of any changes they make. On the planning side they will generally be entering at a lower level in the hierarchy so the zSelect idea in the Default View may work for you. Most users will then move off this to select the Cost Centre, etc, that is applicable to them. They probably won't want to change to a top level Cost Centre and they will probably all change to different Cost Centres.

However, on the reporting side, they are more likely to want to look at the overall picture.

In the past I have used a split architecture, where there are a series of linked cubes for the planning side. The reporting side then has similar dimensions to the planning side but doesn't have any rule linkages to the other cubes. Instead it is loaded via TI with calculated results at the base level from the planning cube, on a regular basis - frequency to be decided with the users, or adhoc on demand - the users I worked with preferred to only transfer plans when a planning cycle was complete. The other benefit of this architecture is that it prevents updates to the planning cube from slowing down reporting and vice-versa. The reporting cube was also loaded with Actual from the GL every 10 minutes, but these were only transfered to the planning cube overnight, since they were only used as a guide to planning and did not need to be updated so frequently. Updating every 10 minutes would also have locked the cube too much for the planners.

Having said all that 5 minutes does seem like a long time for an initial view. It might be worth checking that you are not over-feeding, and optimising dimension order.

Regards

Paul Simon

[lotsaram]

Catherine & David - it is my recollection that at the BB training in Boston that Tom or Max warned against using SaveDataAll and SecurityRefresh due to the impact of dumping the cache. I don't think it was specified whether it was the view versus calculation cache but I'm sure this was mentioned. (Do you recall the same thing or is it just me?) This is something that I haven't ever empirically tested but have just kinda accepted that it must be true if the software engineers say so. If saving data doesn't dump the calculation cache then this is good and I'm happy to be wrong.

One thing I have noticed is a remarkable improvement in the behavior of SaveSataAll in large models in 9.5.1 and 9.5.2 versus 9.4.1 and all prior versions. It used to be that SaveDataAll held a lock on all objects and released the lock only when the data save was complete for the whole server, which effectively froze the server for all users (which in a very large model could be a very long time). In 9.5.1 the lock seems to be held only on the object being saved and the lock for each cube is released as each .cub file is updated, this seems much more sensible and means users can generally continue working as normal even during a save data as they only need to wait if they are accessing the cube being saved at that moment. In 9.5.2 with ParallelInteraction a save data seems to have no impact on users at all.

[kudzis]

I wrote: "If I open a cube in other TM1 tool, opening takes long time again. For example, if I open it in Perspectives, it takes 5mins for the first opening. After that, if I open it in Web Contributor, it takes 5mins again."
Please disregard this - in contributor, I was opening another view; after first time, view opens instantly in all tools.

Do you have a default view defined on your cube? If not then perhaps the system default that it is opening is a top level view which selects the top most element in each dimension, and therefore takes a while to calculate. If most users don't need that top level view then saving a public default view with element selections somewhat lower down the hierarchy can make the cube open faster. I sometimes add zSelect elements to one or more dimensions, and make this the selection in the Default View. In this way you can make the Default View open on something that everyone can have access to, and which will never have data (you can enforce that using rules). It also prompts them to select something different.

yes, I have defined a default view, and it contains some dimensions with top level elements. I've tested another view with all dimensions set to lowest elements - it always opens instantly, however, when I choose a top level in one of dimensions, view building takes long time again. So I've decided to give a ViewConstruct a try, will see how it goes.

I am guessing that from the fact that you say that you have many linked cubes that you have a planning application. However, I would also guess that you are reporting from it too.

Yes, I have planning application; reporting needs are minimal (I'll export cube to MS SQL and do reporting in BI in the future). However, my client is doing what-if analysis while planning, so he needs top level views.

Currently full project is linked using rules, but I'm pushing client to do away with full real-time solution and use TI to transfer data from some cubes.

I'm not overfeeding a lot (except with some IF's, but I'm planning to redo it with TI data transfer if client agrees).

Optimising dimension order is a good idea, i'll give it a try.

[kudzis]

I've optimized dimensions order (gain was -90%), but opening time stayed basically the same. ViewConstruct seems to have helped (but I'll keep checking for a next few days to make sure views open instantly).

Thank you everyone for your input.