An introduction
The most successful Business Intelligence reporting tool I have come across in my career was written in 2,000 lines of code. Let me preface this by saying I’ve been in the Business Intelligence/Data Warehousing industry for about 15 years. Half way through I switched to doing DataWarehousing. I have a laundry list of dot com’s under my belt. I have done development on Cognos, Tableau, Microstrategy, QlikView and a few in house BI tools. The tool that stands out the most is a lightweight framework that essentially executed blocks of SQL code and delivered report result sets to your browser. It was basic to say the least. The reason why this tool worked so well was because the reports were highly curated to meet reporting needs. And I think that’s a problem with today’s BI tools. Reporting tools are becoming increasingly complex at the expense of users.
Before BI tools existed
In the early days of Commission Junction we had a need to produce reports and there were few 3rd party solutions available. Employees at CJ would typically run a query, copy results to excel then create a graph. Having taken statistics classes in college, I more or less migrated towards doing reporting tasks. My first attempt at fixing this clumsy reporting problem was to use the GD Graphing library(http://libgd.github.io/) and Perl to generate graphs. My tool could connect to the database, aggregate results then produce graphs in png image files. The graphs were not historicl but over time they became historical. My tool could collect data and become valuable over time. The good news is that this tool could provide some relief to those people crunching data in excel. The bad part was the tool was difficult to manage and not scaleable what so ever. Along came SEMSA.
SEMSA enters the picture
One of the early engineers at CJ built a tool that dramatically improved and changed how BI was done within the company. The tool essentially captured SQL queries, executed them then published results to a browser. It was by far the most powerful tool I have used in my career. The initial version probably took a weekend to build and upon delivery it was not much to look at. It didn’t have much in the way of value. Over the course of two years, the SEMSA(“SQL Execution Management System”) tool became extremely curated and useful. It lacked many of the fancy features found in tools like Tableau but what it lacked in feature it made up in powerful information.
What made SEMSA valuable?
- Reports were highly curated
Reports were efficiently organized for users based on their job responsibilities or department. Feedback was collected on a daily basis to manage reports, delete unused reports, improve popular reports and create new reports. Generally speaking, significant effort went into working with users to give users what they wanted. - Training was pervasive
Every employee and new employee went through training sessions. The training sessions began with an introduction to CJ’s business and how customers behaved. Training ended with lengthy discussions about how data was saved, where it went, what reports existed and why those reports were created. The training sessions were tailored to meet the needs of specific departments. Those in sales had different needs than those in client development. - Constant feedback from users
Given that training sessions were often, there was a constant feedback loop where users could voice their frustrations. This was key to weeding out the dead weight and improving the good parts. - Managing the BI tool was trivial
Reports were easy to create and manage. Adding a report or updating a report could be done with a few lines of code and a few clicks. - Report usage was closely monitored
SEMSA collected stats every time a report was run. Using these stats, I monitored report usage by employees by department. If a report was commonly used by most people in a department and a person wasn’t using the report then I would bring this up…usually by creating a training session for the dept. It was big brother-ish but it worked. - The BI tool was lightweight
The SEMSA tool iteslf was not very complex. The deliverable was generally a spreadsheet of results and users could export data to excel for complex analysis. The Tableau’s, Congnos and QlikView installations I think fail in this regard. These tools are too often implemented with too much complexity.
While SEMSA started out as little more than a shell, it became something valuable though constantly curating and updating of reports.
What technical features existed in SEMSA?
- Drill downs between reports
For example, generate a report for revenue by day, click on a specific day to see the top customers for that day, click on that customer to see their products, then click on that product to see revenue by day by product. - Report usage was saved
With this I could monitor what reports were used by who and how often. Given this is kind of big brother-ish, it allowed me to segment report usage by department. With that info I could see who within a department was a heavy user and a light user. I could interview the heavy users to see what they found valuable, interview the light users to see what they thought was missing then try to bridge that gap. - Ability to categorize reports
Another feature of the SEMSA tool was the ability to create report categories and segment them by purpose or department.
That’s it so far as features. Wish there were more to it but there really wasn’t. The tool acted as a database for blocks of SQL code then executed the SQL when necessary. The tool was very primitive.
What made SEMSA fail?
- It lacked features
SEMSA did not have features that were common in the marketplace, such as the ability to produce graphs or schedule reports. - Performance problems existed
Performance was dictated by the database. There was no layer on top of the database to prevent SEMSA from hammering the database. For small business, this isn’t an issue, but as CJ grew it became a concern. - Lack of scaleable management features
SEMSA worked well when CJ was a small to medium size business. As CJ continued to grow beyond 300 people, SEMSA became increasingly difficult to manage. At some point SEMSA was more of a burden than a value add to the business.
Conclusions…
For small business, I think the choice is easy. Keep it simple. There are plenty of tools on the market that have the ability to meet the demands of a small business with a small data footprint. At worst, a small company may pay too much for a reporting tool or poorly implement the tool. Large companies run a different kind of risk. For a large company with a large data footprint, things like scaleability, features and ability to manage are important. Choose poorly and the problem could be catastrophic. Like all things, choose the right tool for the right job. At the time when CJ needed a simple reporting tool, SEMSA was nice to have.
In the future I’ll go over the worst ETL tool I ever used. It was basically group think gone awry then into production without much thought.