TM1 Forum

Hello,

We have a need for static read-only historical versions of our current production TM1 servers which will contain last year's numbers. I have currently set these up as copies of the current TM1 servers and restricted the security to Read for all users but the Admin which works well. My next task is to try and reduce the memory requirement of these servers by converting the cubes to static versions, i.e. replacing all calculations with the resulting values, and taking any similar action with dimensions, processes, etc. if they exist. I have tried enabling the Load on Demand option on these cubes, but the time it takes to open each cube is less than ideal and it seems like the same amount of RAM gets used in the end, it just gets used when the cube is opened as opposed to being allocated when the server starts.

We do have a TI process that copies the calculations from one element of a given dimension to another as static values, but it only works on a per-cube basis and needs a source view created with all dimensions wide-open before it can be run. I would prefer not to do this on 75+ cubes to create a static version of the server. I can post the code if someone thinks this TI process can be easily modified to work on the entire list of cubes. I have also looked into Replication, but it does not appear to have the option of only replicating static values instead of calculations.

My questions, after searching the forum and TM1 documentation, are these:

• 1. What is the best way to basically take a static 'snapshot' copy of a current TM1 server?
  2. Is there a way to reduce the memory footprint of the dimensions? Is this even a concern if the server is read-only and the cubes contain only static values?
  3. What is the impact on memory from the processes and rules? Since these servers would be read-only and static, would there be any benefit and/or negative effects to deleting them?

Thanks, and please don't hesitate to ask for more information and/or clarification.


sleepy

There is no easy way to accomplish your goal of creating a totally rule-free environment. I think after further analysis you'll find that you want some of the rules, just not all. I wouldn't undertake anything until you've done a complete look at all the measures in the models and ruled out the fact you won't need some of the rules. After you've done that, the next step is to create a series of views off the cubes that contain the year you want to extract from the Time dimension(s) and leaf level elements only in all the other dimensions. Set the Skip options to only skip Zero/Blank values. Write a TI process to extract all these views to text files. Now you have to data you want. Go to the new server and write TIs to load the data.

FYI, you may be able to leverage Bedrock code (do a search on this forum for "Bedrock") to help with the extract and load TIs. Good luck.

Hi tomok, thanks for the quick reply!

tomok wrote:There is no easy way to accomplish your goal of creating a totally rule-free environment. I think after further analysis you'll find that you want some of the rules, just not all. I wouldn't undertake anything until you've done a complete look at all the measures in the models and ruled out the fact you won't need some of the rules.

Maybe I should clarify. While I do want the values in these historical cubes to appear as they did when the rules were active, it will truly be a read-only no-changes model...a photo-copied reference of the original. My thought in not having any active rules was that not only do the calculations not have to be stored in memory, but there would also not be any active feeders either. Am I not thinking about this correctly?

tomok wrote:After you've done that, the next step is to create a series of views off the cubes that contain the year you want to extract from the Time dimension(s) and leaf level elements only in all the other dimensions. Set the Skip options to only skip Zero/Blank values. Write a TI process to extract all these views to text files. Now you have to data you want. Go to the new server and write TIs to load the data.

This is a good point...maybe we don't need *every* cube, especially since these models are just going to be used as reference materials. I have already created the new model by copying the existing model's Data directory and registering a new TM1 Service, and was approaching this problem from that viewpoint...trying to convert the newly created (and active) model into a static version of itself. Perhaps taking a step back and exporting the desired values from the active model and then loading the static values into a new 'blank' model is the way to go.

tomok wrote:FYI, you may be able to leverage Bedrock code (do a search on this forum for "Bedrock") to help with the extract and load TIs. Good luck.

Thanks, I'll look into that.


sleepy

Hi Sleepy,

There are many possible approaches, and none of them are particularly easy, unfortunately.

If you already have a copy process that works for every cube, you could create a new set of versions in your version dimension (assuming you have one). So, say you have a "Budget" version, create one called "Archive - Budget".

In your rules for each cube, add a line at the top that stets out the archive version.

['Archive - Budget'] = N: STET;

Then run your copy processes to copy from the rule-based version to the static one.

Once all the values have been copied, you can simply kill all the rules.

If it's important that the version name stays the same, you could then copy your static numbers back to their correct version and delete the archive versions.

If you're only doing this once a year, it might be an acceptable approach.

There is no completely easy approach to archiving in TM1, but I have used the above approach (automated in my case, and month by month, region by region) in the past.

And, as Tomok said, Bedrock is your best friend on this one.

For me the solution you are talking about seems a bit overblown compared to the requirement. I assume that the reason for creating the archive server is to preserve a static copy of things exactly as they were so that for slowly changing dimensions like Cost Center and Accounts likewise for more rapidly changing dimensions like Product things ramain exactly as reported as of financial year end YYYY or month end MM-YYYY and despite any later changes to these dimensions, changes in company reporting structure, changes in accounting principles that these changes should never be felt in the archive as it is "frozen in time". Am I right so far?

If that is the case then all that is needed is a time-stamped version of the server. It can be complete with all rules. Maybe you might identify some cubes that don't have a requirement to be archived and remove these to save memory and time to load the server but that's all. I actually believe this is the safest approach with least risk and least work. The only requirement is sufficient memory on a server somewhere to load the database, and as pointed out many times elsewhere on this forum these days RAM is a far more plentiful and far cheaper resource than TM1 expertise. From a licensing point of view you can successfully argue that an archive server is non-productive should this ever come up.

There are also a few things you should consider before embarking headlong on your current approach.
1. As Tom pointed out it is unlikely that you would want to remove all rules. More likely there are some you need to keep such as KPI calculations, variance % and any other ratios or where consolidation aggregation needs to be overridden. Otherwise your static copy doesn't have the all the right numbers and that is definitely not what you want when the whole intention is to preserve validated figures exactly as they were. It is a potentially time consuming exercise to go through all rules and figure out which rules need to be preserved and which should become static and also adds complexity to the TI process to do the static rendering.
2. Following from point 1 if you make a static copy and especially if you need to modify rules to preserve some calculations but not all then it is essential to validate the results after the static copy and after any change in rules. This is a potentially exhaustive exercise in itself! How extensive should the validation be? Who is responsible, IT or business? Who will sign it off? If there are any discrepancies who will sign those off?
3. It is a fallacy to think that a static values only model will consume less memory than a rule based model in fact it is likely to be the opposite. Remember that the results of rule calculations are calculated on demand and not loaded into RAM when the model starts like leaf cell values are. By converting all leaf calculations to values you are storing a lot more data and this probably means consuming more memory.
4. You say you are using 9.5.1 which means that you can use persistent feeders. With this switched on a rule based model can load into memory just as fast as a values only model if it is server start time that you are concerned about. This is especially true for a model where nothing is changing
5. Response time.This is the one area where static values beat rules hands down. But is it really an issue? The model will be an archive, which to me means it will not be used for day to day reporting but will only be used sparingly and only when someone has a specific need to see something from a past period that is no longer maintained on production or specifically to compare to a number as reported due to some change in company structure or accounting treatment change. These cases should be rare and so it is really a problem if the query is slow?

For archiving within the productive model I would strongly advocate taking a static copy but for archiving a whole server instance as a separate model I would strongly advocate against it due to the reasons above as simply taking a copy of the database as is is both a whole lot less risky and a whole lot less work. (If you haven't guessed I have been through this process!)

Thank you both, Lazarus and lotsaram...outstanding posts, both of which deserve detailed replies!

Lazarus wrote: If you already have a copy process that works for every cube, you could create a new set of versions in your version dimension (assuming you have one). So, say you have a "Budget" version, create one called "Archive - Budget".
In your rules for each cube, add a line at the top that stets out the archive version.
['Archive - Budget'] = N: STET;
Then run your copy processes to copy from the rule-based version to the static one.
Once all the values have been copied, you can simply kill all the rules.
If it's important that the version name stays the same, you could then copy your static numbers back to their correct version and delete the archive versions.
If you're only doing this once a year, it might be an acceptable approach.
There is no completely easy approach to archiving in TM1, but I have used the above approach (automated in my case, and month by month, region by region) in the past.

Interesting, I hadn't thought of using the rules to help in creating the static version, thanks!

Lazarus wrote: And, as Tomok said, Bedrock is your best friend on this one.

Yes, I've downloaded the latest Bedrock package and have begun looking through the processes. They seem very useful, though I'll admit I need to spend more time with them before I am comfortable with putting them into production.

lotsaram wrote:For me the solution you are talking about seems a bit overblown compared to the requirement. I assume that the reason for creating the archive server is to preserve a static copy of things exactly as they were so that for slowly changing dimensions like Cost Center and Accounts likewise for more rapidly changing dimensions like Product things ramain exactly as reported as of financial year end YYYY or month end MM-YYYY and despite any later changes to these dimensions, changes in company reporting structure, changes in accounting principles that these changes should never be felt in the archive as it is "frozen in time". Am I right so far?

Spot on.

lotsaram wrote:If that is the case then all that is needed is a time-stamped version of the server. It can be complete with all rules. Maybe you might identify some cubes that don't have a requirement to be archived and remove these to save memory and time to load the server but that's all. I actually believe this is the safest approach with least risk and least work. The only requirement is sufficient memory on a server somewhere to load the database, and as pointed out many times elsewhere on this forum these days RAM is a far more plentiful and far cheaper resource than TM1 expertise. From a licensing point of view you can successfully argue that an archive server is non-productive should this ever come up.

Initially, preserving resources and trying to fit everything on our current single TM1 server was a requirement. Recent developments have shown that paying for a second server may be a viable option which, I agree, makes this whole task much easier. I was not aware, however, of the difference in licensing between a production and archive server...is there an official distinction with a reduction in price, or are you referencing more of a 'grey area'?

lotsaram wrote:There are also a few things you should consider before embarking headlong on your current approach.
1. As Tom pointed out it is unlikely that you would want to remove all rules. More likely there are some you need to keep such as KPI calculations, variance % and any other ratios or where consolidation aggregation needs to be overridden. Otherwise your static copy doesn't have the all the right numbers and that is definitely not what you want when the whole intention is to preserve validated figures exactly as they were. It is a potentially time consuming exercise to go through all rules and figure out which rules need to be preserved and which should become static and also adds complexity to the TI process to do the static rendering.

I agree, that would be bad. My point of reference in this request was from our TI process that creates the static copy of the Plan element in the Version dimension. It takes the calculated values from Plan and uses a CELLPUT to place a static copy into a different element in the Version dimension. This, more or less, is what I had envisioned doing on a server-wide scale.

lotsaram wrote:2. Following from point 1 if you make a static copy and especially if you need to modify rules to preserve some calculations but not all then it is essential to validate the results after the static copy and after any change in rules. This is a potentially exhaustive exercise in itself! How extensive should the validation be? Who is responsible, IT or business? Who will sign it off? If there are any discrepancies who will sign those off?

I would think that using a version copy TI process would negate this requirement, assuming it's just taking an exact copy of the production values, correct?

lotsaram wrote:3. It is a fallacy to think that a static values only model will consume less memory than a rule based model in fact it is likely to be the opposite. Remember that the results of rule calculations are calculated on demand and not loaded into RAM when the model starts like leaf cell values are. By converting all leaf calculations to values you are storing a lot more data and this probably means consuming more memory.

Interesting, I was not aware of this. I thought that by removing the calculations, and thus the feeders, the model would be less RAM-intensive. If this is not the case, creating whole read-only copies of the production models and putting them on another box is even more enticing.

lotsaram wrote:4. You say you are using 9.5.1 which means that you can use persistent feeders. With this switched on a rule based model can load into memory just as fast as a values only model if it is server start time that you are concerned about. This is especially true for a model where nothing is changing

Server start time is not really a concern, since these models will probably be just be up and running most of the time with minimal restarts.

lotsaram wrote:5. Response time.This is the one area where static values beat rules hands down. But is it really an issue? The model will be an archive, which to me means it will not be used for day to day reporting but will only be used sparingly and only when someone has a specific need to see something from a past period that is no longer maintained on production or specifically to compare to a number as reported due to some change in company structure or accounting treatment change. These cases should be rare and so it is really a problem if the query is slow?

You are correct. As archives to be used as reference-only, response time is not of paramount importance.

lotsaram wrote:For archiving within the productive model I would strongly advocate taking a static copy but for archiving a whole server instance as a separate model I would strongly advocate against it due to the reasons above as simply taking a copy of the database as is is both a whole lot less risky and a whole lot less work. (If you haven't guessed I have been through this process!)

I too am beginning to feel the same way. If acquiring a second box turns out to be a real possibility, I will definitely just take copies of the entire model and set them up as new services to run as read-only copies on the new server.

Thanks again to everyone for your replies and insight!


sleepy