There is a plethora of content available on utilizing the various TM1 functions, but what happens when these functions do not work as expected? What do you do when a rule does not return the expected value, a TI process does not load the values you had hoped for, or a cube’s performance is below acceptable levels? This article will cover some techniques for debugging TM1 that you can use to resolve issues with rules, TI processes, and cube performance.
Debugging TM1 Rules
The most common rule issues are either feeder-related processes or situations where the rule’s logic is not returning the expected value.
The easiest way to identify a feeder issue is when a value does not roll up or disappears from the cube view when zero suppression is activated. In the example below, the “Direct Costs” value in “Feb-2004” is not rolling up to “COS.” This value also disappears if zero suppression is activated.
To verify that it is not fed, right click on the value in question and select check feeders. The resulting screen will show the value as “not fed.”
Adding a simple feeder returns the correct value.
What happens if the value is fed, but is simply not returning the expected value? In many cases a different rule could be executing for the cell in question, or one of the rule’s components could be an unexpected value.
In the example below, “Direct Cost” should equal (Sales-Other Revenue)*.2, but as you can see from the view below “Mar-2004” does not seem to follow this logic.
You can use the trace calculation function to determine what rule is executing and what values it is getting for the components of the rule. If you right-click on the cell in question and select “Trace Calculation,” you will see the screen below, which shows the rule being executed at the top and the components of that rule at the bottom. In this case, there is a rule for just the quarter ending months that also includes “Other Costs”, resulting in the unexpected value.
Debugging TM1 TI Processes
Most users are aware of the basic Turbo Integrator error log and how to review it, but what if the process runs without errors and still shows incorrect numbers? How can an admin determine what is happening from within the process when debugging TM1?
The “ProcessQuit” function ensures that a line in a TI process is being executed. Insert the line “ProcessQuit;” into a TI process to force it to quit when it reaches that line. This function can be used to determine if an “IF” condition is being executed or if a section of the TI process is ever being reached.
The “asciioutput” function writes out multiple variables during the execution of the TI process to determine what is happening during the process’s execution.
The example below uses two asciioutput functions to write process data out to two different text files during the process execution. The first writes out the year data for every record the process processes, and the second writes out the variables used in the “cellputn” function further down in the process. These processes can be reviewed after they execute to see what happened within the process.
Debugging TM1 Cube Performance
Poor cube performance has a number of potential causes, but the simplest to test and correct is dimension order. The optimal order is sparsest to densest, then smallest to largest. However, the optimal order can change as data is loaded or rules are added. The optimal order when the cube was created may not be the same after the cube has been used for a period of time.
TM1 includes a tool to determine the optimal order and modify it as needed. Right-click on the cube and select “Re-order Dimensions.”
Select “System” and the optimal dimension order will be displayed in the new order column. Please note that the system will consume substantial memory determining the optimal order, and it could take substantial time for a large cube. Selecting “OK” will use this new order and not affect existing processes, rules, or Excel sheets.
A more common reason for poor cube performance is too many feeders or feeders that are too broad being used in the rules. There is no simple method for identifying a cube as being overfed, but there are some steps you can take to determine if feeders are the problem and narrow down which feeder is the issue.
Step 1 – Create a test view and get a baseline of the query response time. Ideally, this view will be the top level of every dimension within the cube.
Step 2 – Remove all feeders from the cube in question. This may also include feeders from another cube that are feeding into the cube being tested.
Step 3 – Restart the TM1 service. For a feeder removal to take effect, the TM1 service needs to be restarted. This is only the case for removing feeders, not adding them.
Step 4 – Add the feeders back one by one and recalculate the test view. If there are too many feeders to make testing one by one practical, try grouping the feeders by similar structure. You may need to start over within the group if you are able to identify a group of feeders as problematic.
Following this logic should help determine what is causing the largest increase in query response time and identify the modifications needed to the feeder(s) in question.
Some common overfeeding tips:
- Do use calculated elements to feed another element.
- If a formula is A = B * C, use the more sparse of B and C to feed A.
- Conditional feeders can be used, but are not recommended for a novice developer.
- Feederless rules are possible if you use as rule to overwrite a consolidation, using the child element to force the consolidation to execute.
The above are just some of the basic techniques used for debugging TM1 during development. Using these techniques will not resolve every development challenge you will encounter, but should help with the majority. Stay tuned for future updates where we will go into greater detail and address more complex challenges. You can also check out our Financial Performance Management team page to see how Ironside can help you optimize your TM1 environment.