New Upload weather data from web services into TM1/Planning Analytics — Read the post →

The Excel Hell Menace

In the first Episode of the epic webinar saga – MAY TM1 BE WITH YOU, we explore how you can solve some of the issues that can make a great tool like Excel an absolute hell.

The road to Excel Hell is normally paved with good intentions. Almost every firm uses Excel for financial reporting but let's be honest — we have all been there or seen it happening. When a new Excel report is created, your boss loves it and a few "easy" suggestions are added to the report.

The report becomes more complex and hence error prone, specially if you take into consideration that 9 in 10 spreadsheets contain errors. Then many people in the company are using the report and the "users" send updates and tweaks, eventually macros and other linked spreadsheets are incorporated but they don't always work and not everyone is a VBA expert.

In 2013 JP Morgan had a $6 billion USD loss from a copy and paste error.

You may be ahead of the curve by already using TM1 and taking the concept of Excel to a robust database level (the Functional Database). Yet, you could benefit even more from learning handy tips and best practices on how to effectively leverage TM1's power through Planning Analytics for Excel (PAx). By using dynamic reports in conjunction with MDX you can solve some of the issues that can make Excel an absolute hell.

Tips and best practices

  • TM1User identifies which instance is connected

  • Excel defined ranges allow for more dynamic formulas

  • Organize your layout for easier maintainability

  • Centralized parameter cubes to reduce monthly updates (e.g., date)

  • SUBNM for selections reduces the reports required

  • Format ranges with Excel "IF" formulas allow for many visual design options

  • MDX statements for dynamic rows without creating multiple subsets

  • Action buttons can enhance user experience by triggering refreshing or TI scripts

Debugging check list

  • TM1RPTTITLE needs to reference existing elements

  • No error returned in TM1RPTTITLE formula cell does not indicate mistake-free formula

  • Only use TM1RPTTITLE for "fixed" dimensions — otherwise could still work but performance may suffer

  • Ensure MDX statement returns values

  • DBRW formulas should be right of TM1RPTROW cell and needs to reference an element in every dimension

In the exciting second Episode of this webinar saga, you will learn how to implement Best Practices for the new revolutionary functionality of Planning Analytics known as Hierarchies.