We sometimes get asked what are the three most important things to remember in TM1 development to make a TM1 model work well in the long term. Well, here is our take on what you need to do to make your TM1 models sustainable.
Never use Names or Descriptions as Elements.
This is because change is inevitable, one day that account name will change, and with how TM1 stores data against elements the chances are that a new account will be added with the new name and your data will change paths, starting in the old account name and swapping into the new account from its addition date forward.
The alternative is to store as much detail as possible based on identifiers: account, department, staff and location id’s along with product SKU’s are what should be used as the principal element name, aliases should be used for the names and descriptions.
ExploringTM1.com recommends the following setup:
| Principal Element Name | Name (Alias) | No – Name (Alias) | |
| Example 1. (Product) | PURA011 | Pure Soft Detergent | PURA011 – Pure Soft Detergent |
| Example 2. (Account) | 6010000 | Total Salaries & Wages | 6010000 – Total Salaries & Wages |
This way with large dimensions being loaded via TI Process any non-unique aliases can be spotted easily by comparing the Principal Name with the “Name” Alias and the value it should be can be seen in “No – Name” which since is appended with the unique ID will have loaded correctly regardless of how unique the “Name” is.
Always include a Scenario (or Version) Dimension & Measures Dimension
- Always include a Scenario dimension (sometimes known as a Verison dimension) because of questions that begin with “What if?”
- Always include a Measures dimension so users know what the numbers they are looking at represent (Units Sold, Returns, $, $ – Thousands or Millions?). I should add that there are some system benefits also such as TM1 member formatting and picklists.
If your General Ledger, Profit & Loss, Balance Sheet, Marketing Spend, Sales or Allocations cubes lack either of these dimensions you have lost the ability to (without rebuilding the cube entirely):
- Measures:
- Switch measures to the Thousands or Millions within the cubes without changing the number formatting.
- Report on multiple currencies (unless added in a currency dimension).
- Add “% of Net Sales” and other KPIs.
- Separate feeds from other cubes into Reportable Streams (Like Intercompany Transactions).
- Scenario (or Version)
- A loss of what-if analysis which creates a lack of scalability.
Avoid Storing Data at a Transactional Level of Granularity
We’re not saying don’t ever store data into a cube at a transaction level, but this should be avoided within TM1 if at all possible. Typical places where this kind of granularity is usually loaded into a cube:
- Sales Invoice Analysis (by Invoice)
- Journal Postings – General Ledger (by Journal)
This leads to high volume zero suppression reporting which usually results in MDX generated reports which while are incredibly useful are hard to support internally as they require VBA experience.
The primary reason for mentioning this is that TM1 is not a transactional solution, it is multidimensional. A transactional database has a dynamic number of rows that grow over time. A TM1 cube is usually a summarised representation of the transactions, where values are aggregated. These could be, for example, by month, or customer, or product, but would not usually include a Sales Order dimension to show the detail from every order (normally you would drill to detail for this).
That is not to say that you should not load data at a transactional level. By all means, but make sure you summarise it in TM1. So using the above Sales data illustration, you would have month, customer and product – but not sales order!
Note: I was tempted to include “Do not make Year/Time-specific cubes” simply because I see it all too often, but really now it goes without saying!!!
And if you’re scratching your head trying to understand what that means… We see too often cubes that are created called things like “2020 GL Budget”, or “2018 Revenue”. Don’t, just don’t! Have a time dimension, Have a Version (or Scenario) dimension and avoid this horrible design! When you do this, make sure you use CellIncrementN your TI’s!
