IBM Cognos TM1 – Freezing Old Data: How and Why
Many TM1 users have cubes with rules associated with historical data or completed plan versions and are needlessly recalculating their data on a continuous basis. This creates an unnecessary performance drain, and to make matters worse, what happens when the business rules change? Do you change history? Do you make rules overly complex by adding a time or version component to all of your rules? Using a few simple steps, this data could be “frozen” or made static, resulting in faster response time, less RAM consumption, and alleviating issues when business rules change. In this article, we will explain how to go about freezing this data.
The first step is to identify the dimension(s) that we will use to determine what data we need to freeze. It could be one dimension or the intersection of multiple dimensions, such as freezing certain months for certain versions. For this example, we will use the “Version” dimension. This dimension contains Actuals, Budgets, and various plans. You will need to create a simple numeric attribute that identifies the elements as active or inactive. We will call this attribute “Inactive” and set the inactive value to 1; this will indicate the value should be frozen.
Next, any rule files for any cubes that should be frozen will need to be updated with a line to exclude any “Version” elements that have an inactive flag value of 1. Just be aware, you may want to leave some rules active, such as rules to overwrite consolidations for items like headcount, or percentages. Simply put these rules above the line to exclude elements with the flag.
[]=N:if(attrn(‘version’,!version,’inactive’)=1,STET,CONTINUE);
The next and final step has several components. A TI process will need to be written to export the frozen values to a flat file. This process should include all rule calculated values, but should exclude consolidated elements. Next, the process should set the attribute value to 1. If the data to be frozen spans multiple cubes, simply add multiple exports to the same process, but create one file per cube.
Finally, a second process will need to be created to reload the values from the flat file into the cube from which they were exported. Again, if the data spans multiple cubes, create one process per cube and use the unique files created in each process above. It is possible to use one process to load from multiple cubes, but this makes the TI processes more difficult to create.
These formally calculated values will not show as inputtable non-calculated values. Just be aware trace calculation will no longer function, removing the audit trail of how the value was derived.
Before:
After:
To ensure maximum performance gain, conditional feeders should be used to exclude the frozen value from being fed by the rule file. Even if the conditional feeders are in place, the memory will remain consumed until the server is restarted or the cube in question is unloaded and reloaded.
=>DB(‘Income Statement’,!Account,!currency,!Market,!Period,if(attrn(‘version’,!version,’inactive’)=1,’ ‘,!Version),!Measure);
If you would ever like to reactivate a version, simply reset the flag back to zero, reprocess the feeders, and the value will be recalculated based on the current rules in place. The trace calculation functionality will also now be enabled. If the rules have changed, the calculated values could differ from the previously frozen values.
