Automated Regression Testing for Clarity and TM1

  One of the best things about IBM Clarity7 and IBM Cognos TM1 is their flexibility and empowerment to build templates fast. This quick development time introduces a natural challenge... did we break something? Here are some examples of simple things that break old code.

Common Challenges That Break Old Code

• Adding new children under a node that was previously a leaf
• Changing network share names
• Changing server names
• Changing paths (of anything)
• Renaming (of anything)
• Changing scope statements
• Changing stored procedure calculations 

Change is Good

  One solution is to never change the system. That is equivalent to shelving the product and going back to Excel. Given that option is out the door, we have to assume change WILL happen. In fact, we want to encourage change. We want the system to be the most valuable representation of the business for decision makers.

Trust

  The system should be the trusted, single source of the truth. Numbers must be consistent, reliable, punctual and accessible. Once trust in the numbers is broken, it is hard to rebuild that trust. Fear of losing this trust hinders many organizations from making any changes at all. This latency pushes users to go back to Excel and find other ways to get the job done under time pressure. Instead of helping information workers, the system sits idle. We need a way to keep the trust of users, while still improving and evolving the system to keep up with business realities. This solution must also be transparent. If anyone wishes to question the numbers, they should be able to do so. This helps them build confidence that they understand exactly how numbers flow and calculate across the system. If they are concerned something is wrong, they are empowered to investigate and identify issues themselves, without reliance on IT.

Excel is the Common Denominator

  Whether files are created manually, with TM1, Clarity or something else, Excel is the common denominator. Excel is the defacto business standard for analysis. Once we accept this, we can steer away from proprietary comparisons of weird format types. To build trust, we need to be transparent to users down to the Excel level.

Generating Excel Files

  Any reporting tool can automatically spit out Excel files. There is always a tool to export data to Excel. There is usually a tool to automate generation of set of Excel files. Even if this isn't available through a nice tool, there are lots of VBA code snippets to do the trick. Here is one on bulk reporting in TM1 that is quite good. So let's assume we now automatically generate 50 excel files with the click of a button.

Comparison

  After a full round of testing, we can take a copy of these generated files and call them our Gold Standard. From that point on, each night we generate the same set of reports and compare them to the Gold Standard version. If there is any difference between the files, we get an alert. This simple alert allows us to proactively notify users about a data challenge while we investigate. Instead of the users telling the team where the errors are, the team can tell users where the errors are. At first, this may sound like a bad thing, but it's not. Users get transparency into the deep level of regression testing that happens on a nightly basis. Over time, this transparency and notification build trust in the system. Users will be more comfortable using the data because they know we are watching our data quality very carefully. Users can invest more time doing analysis, rather than testing and data validation.

High Level Design

  Here is a quick diagram of the high level design for this process. First we build a list of the pairs of Excel files we want to compare each night. This could be as simple as comparing one directory to another. For each pair, we compare the Gold Standard Version to the one generated last night and capture the results. When reading results, if we encounter differences, we can decide what type of action to take or notification to send.

Next Steps

So, how do we build this? Firstly, you can ask us for help (http://www.clearinsight.ca @clearinsightfpm or info@clearinsight.ca)  or follow some of these steps.

• Build or buy an Excel comparison tool. I recommend Diff Doc.
• Build or buy a method to automatically generate sets of excel files periodically from your reporting tool. I recommend using the native scheduling tool in your reporting tool if you have one, or some VBA code for bulk reporting.
• Build a process to compare these files and make appropriate action or send appropriate notification. I recommend using an SSIS package. Here is a link on how to start.