Currently there are 4 options for rounding numbers in Cognos TM1. They are:
- Rounding in reports.
- Rounding during loads.
- Rounding with the Cube Viewer.
- Rounding with rules.
Rounding in Reports
The most popular method to apply rounding in TM1 is in reporting. Cognos TM1 leverages MS Excel for reporting and supports all of the formatting and calculations available within Excel. Typically, “templates” are created that apply the organizations (or individuals) desired formatting and/or rounding in a consistent way. In addition, Excel workbooks can be published to TM1Web for viewing by wider audiences (other reporting options, such as Cognos BI, also support formatting/rounding in report presentation).
The following is a simple illustration of using Excel formatting on TM1 data:
Rounding during loads
Another popular method for rounding numbers in TM1 is to round as data is being loaded (into TM1). This allows information to always be presented in the expected format (or precision) throughout the TM1 application. Based upon specific requirements, it is also common to model a TM1 application with reporting cubes to isolate the calculation and transactional processing from the reporting and presentation (of specific information). In this case, data may be transferred from a source cube to various reporting cubes and during that transfer process logic can be applied to round to the desired precision. Simply put, you may have a summary cube specifically for reporting that holds dollars rounded up to the nearest thousand.
Rounding with the Cube Viewer
Cognos TM1 cube Viewer does support some formatting options for viewing data in TM1 cubes. Although this method is somewhat elementary, some precision can be set which will invoke some level of rounding for display.
The format dialog in Cognos TM1:
Rounding with Rules
Finally, Cognos TM1 supports the ability to create cube rules that apply business logic to data in TM1. This business logic can include rounding. Generally speaking, as a user navigates through a cube, TM1 executes the rule (in real time) applying the logic to certain data intersection points within the cube. The result of the rule can be based upon just about any algorithm. Below is an example of very simple rounding logic.
The user-entered value is “Valueof” and there are 2 TM1 cube rules applied:
- RoundedValue shows the “Valueof” with basic rounding logic applied (rounds up or down to the nearest thousand).
- RoundedUp shows the “Valueof” with basic rounding-up logic applied (rounds up to the nearest thousand).
Recommendations
So what do I recommend? A best practice recommendation would be to evaluate your requirements and take an approach that best serves the model’s needs. The options that may best serve from an enterprise perspective would be to maximize flexibility by storing the raw numbers in TM1 and then either:
- Using a reporting tool (such as Excel with defined formatting templates) to apply rounding
- Create reporting cubes within the model and load data into those cubes at the appropriate precision and format for reporting
To be clear, it is important to understand that programmatically introducing rounding (either via during a load/transfer of data or by TM1 cube rule) can introduce material differences in some consolidation situations (shown in the cube view image above as “All Locations”).
