To insure that acceptable levels of performance and maintainability can be achieved, an “architectural purist” (like me) will want to recommend that when implementing your Cognos TM1 model, great care be taken when deciding where you implement the specific logic that the solution requires.
Although there will always be “room for exception” (for example to address specific limitations due of the deployment environment, client policies, license allowances, support of the model after the initial release, etc.), the following guidelines will generally hold true:
All of the fundamental calculations to be performed by the model (for example consolidations) should be implemented with TM1 dimensional hierarchies and element weightings.
Formal Model Calculations
Formal business model calculations (for example currency translations or the multiplication of prices by units) should be implemented using TM1’s real-time Rules Engine.
ETL, Report & KPI Generation and Optimizations for Performance
Logic required for transformation, loading and reporting should almost always be implemented using TurboIntegrator scripting. In addition, certain model calculations (initially implemented as TM1 Rules) may also be considered as candidates for implementing in TI, based upon either performance test results or anticipated performance results. In fact, generally, whenever you can move “rule logic” into a TI script (without compromising the overall value-add of the model), do it!
It may be a requirement that your users have the ability to perform individual “unstructured” or “spontaneous” modeling. Cognos TM1 Spreading and Sandboxing features should be used for this.
Ad-Hoc Reporting and Specialized Presentation
When reporting and presentation of information require additional (simple) calculation or re-formatting of the information supplied by the model , the use of MS Excel functions is appropriate.
Any specialized logic to satisfy specific usability needs should be implemented using VBA programming. (Note: this would not include any business logic requirements -no matter how specific or unique they may be!)
Extremely Specialized Solutions
In the most extreme cases, a combination of VBA and API programming can be used to implement solution logic requirements.
Also keep in mind that logic considered “generic” to (most) models (for example logic associated with consuming the outputs of your model) may change while logic that performs specific formal model calculations would most likely be the unique “value add” the model provides and most likely would not change over time. Be particularly careful where you “place” your “value add” logic.