A few notable solutions from the IBM Insight conference

There were many wonderful examples of ingenuity at the IBM Insight conference. From the cognitive computing teams (I <3 Watson) to the tips and tricks from the various sessions, the conference was well worth every penny. Unfortunately I can't describe every session or expo booth I attended, but here are three that are outstanding. 1. QUBEDocs
I recently had the pleasure of working with a certain organization with a “mature” TM1 environment. The culmination of years of bad practices left thousands of rules, and the stability of a house of cards. Except the cards are made of crystal and the house is in San Fransisco. That makes it sound worse than it was, but as the reporting guy there were a few days I was sitting on my hands waiting for the system to come back up. The people there were wonderful, and I honestly hope to come back there.

If only we had QubeDocs software. At the most basic level, Qubedocs is an automated documenting and tracking tool for TM1. But that hides the wonderful intricacies of the tool. At this project, like many others, we had to deal with a spiderweb of interconnecting rules and cubes. Qubedocs could very quickly parse through the rules and describe dependencies and find invalid rules.

Impact analyses, error highlighting, change tracking, documentation. These are all things that should natively part of the tool. Nothing I can say would give it the justice it deserves. I strongly recommend clicking on “See how it works” to see the videos. I’m going to see if I can get one of my regular clients to buy a license to give me the chance to play with it.

2. ITGain
ETL is one of those things that I can do, but it’s never sexy. ITGain’s software SpeedGain is ETL monitoring software. In their demo, they showed a simple dashboard displaying the state of server, CPU time and sort operations. In ETL proceses, high sort means bottlenecks. CPU means the ETL is doing something, anything else means you’re sitting there twiddling your thumbs.

speedgain dashboard

A mature environment might have dozens or more processes. How can you identify which module is causing the bottleneck? SpeedGain will allow you to drill down to find the module in question, or even the individual SQL statements. It will list the individual statements by executions and cpu time.

speedgain SQL Statements by CPU time

Sort operations, as we all know now, are often caused by poorly designed indexes. So SpeedGain will actually recommend one. After implementing the suggestions the runtime of the slow ETL module is reduced dramatically. Since the statements didn’t need to scan the table, we could see the sort operations drop to almost nothing, with CPU time increasing as it has data to work with.

speedgain SQL analysis

At the moment they are supporting DB2 and Oracle.

3. Miami-Dade County Cognos Audit
The Miami-Dade County is a large user of Cognos. The reporting for many services have been folded into their implementation. They also have internal and external facing Cognos setups, meaning that we can actually view many reports. Because of their immense set up, and thousands of named users, they need to have a robust security and audit system set up. The audit also serves as proof of use to justify budgets and to encourage moving more services to their systems.

By default the Cognos audit reports are fairly ugly and slow. When dealing with thousands of users, and even more reports, just opening the prompt page can be an exercise in patience. The reports here are designed to be fast dashboards.

The biggest issue I have with this solution is that is was built natively against Oracle. Adapting it to SQL Server or DB2 is possible, but takes some work.

I’ve attached the solution with their permission below.
Miami-Dade County Audit Reports (674 downloads)