Reporting & BI tools

There exist many (Many!) different reporting software (i.e. BI tools – Business Intelligence tools) that allows you to create charts and graphs about the evolution of different KPI (Key Performance Indiccators) that characterizes your business. Usually, BI tools are just adding a small “graphical-interface” above an already-existing database or data warehouse. This interface allows the users to easily create (with their mouse) some reports & dashboards.

For example, your reporting tool can produces “standard reports” such as:

  • Evolution of your sales over the years, month by month
  • Evolution of the number of Acquired customers, month by month
  • Evolution of the number of “lost customers”, month by month
  • Churn rate
  • Etc.

Typically, these classical questions (or requests) directly translates to SQL queries that are executed by the Data Warehouse system. The BI/Reporting tools only displays in a fancy way, the results computed by the Data Warehouse. Computing those results usually take a large amount of computing-time (during which the Data Warehouse system might become irresponsive). So, if you hired good consultants to create your Data Warehouse, they normally created inside your database a “small, special table” that already stores all the answers to these “tactical” questions (so that there is no need to re-compute all time the same answers to your different “tactical” questions: you only need to consult the “special table” that already contains the answer). This “special table” is re-computed & updated every week or so…

A little bit of Terminology: The difference between an BI tool and a reporting tool is the following: BI tools are displaying their reports inside a webpage while “simple” reporting tools are producing MSWord, MSExcel, etc. reports. BI tools also offers some “interactivity” that simple reporting tool don’t have: e.g. You can click on a specific town in a map and you directly see the report linked to the “clicked” town. This fonctionnality is often named “drill-down” because it allows the end-user to quickly find a specific report by clicking on links (i.e. “drilling-down”) that are more-and-more focused on the researched subject.

Another bit of Terminology: Some people are also calling BI tool “Analytical tool”. This terminology is confusing because “Analytical CRM tools” such as TIMi, SAS, SPSS,… have nothing to do with simple reporting and yet, the word “Analytical” is used for both kind of software. To avoid any confusion, we’ll name the “Analytical CRM tools” as “Modeling & Scoring tools”, “Advanced Analytics tools” or “Predictive Analytics tools”.

Some BI software are not using any database at all as their back-end engine: these tools store all the data to analyze inside central core RAM memory (instead of accessing the data through a SQL query to a database). These kind of reporting tools are usually named “In-Memory Analytics”. The disadvantages of this RAM-based-technique are enormous:

  • You cannot analyze the large enterprise-level databases that won’t fit into the very limited RAM of your computer (Most enterprise databases are now of the Terabyte size and the best servers have only a few Gigabyte of RAM).
  • You have to duplicate one more time your database.One copy of your precious customer database is inside your central database system and one copy is inside the BI tool/ Maintaining a correct synchronization between both database copies might prove challenging.

The only advantage of In-RAM-BI tools (yes: there is one!) is completely marginal: If the data is small enough to fit into your RAM memory, it’s somewhat marginaly faster to create the required dashboards (sometime, a dashboard that takes a few seconds to compute using, as back-end, a database is computed in less than a second when using a in-memory BI tool) but this added speed is useless if your only goal is to “refresh”, once a day during the night, some standard reports.

Some people are using In-RAM-BI tools to “explore” their database in order to create a good segmentation of their customer base. As we will see in the next sections, there now exist special-purpose, 100% automated tools (such as TIMi) that allow you to easily explore your database to create different optimal segmentations, depending of the type of tasks that you want to accomplish with your segmentation (cross-selling, churn prevention, up-selling, etc.). Because of the existence of these new special-purpose tools, In-RAM-BI tools are now obsolete.

Obsolete In-RAM-BI tools are: Qlickview, IBM Cognos TM1,…

Differences between these tools

The differences between different BI tools are:

  • Is it “In-RAM-BI”?
    If yes: limited scalability, marginal speed increase
    If no: unlimited scalability (depends on the underlying database)
  • Charts: Some BI software propose only a small sets of charts
  • Report Exportation:Some BI tools are not able to export their reports into standard formats like: Word documents, Excel spreadsheets, Powerpoint presentations, HTML pages,…You must check carefully your BI tool to see if it’s able to export its report to Microsoft Office documents because most of them are not able to do that. It’s important to have reports as Microsoft Office documents to be able to work collaboratively on the report with your colleagues (e.g. adding annotations and notes that other can see). …or to publish it on a sharepoint portal, for example.

A basic BI tool is included inside Anatella (that is included inside the TIMi framework). With Anatella, you can create any chart that is inside any Microsoft Office document (Word, Excel & Powerpoint). Anatella is not an “In-Ram-BI” tool and thus it’s not adapted to do exploratory analysis of our database. Anatella is scalable and can analyze any size of database (as all ETL’s) to produce the required reports. Hopefully, the TIMi suite contains two other tools specialized in exploratory analysis of your data: TIMi and Stardust. Typically, Anatella is used to automatically refresh every day or week standard Powerpoint reports that give detailed information about the evolution of different KPI’s.

Differences in results between these tools

The differences in results between all these BI tools are:

None: Given enough time and enough human resources, you can create similar reports with all the above tools. The speed of the tool mainly depends on the underlying database. So, I would suggest you to invest in a good database solution rather in an expensive BI tool. Even for limited In-Memory BI tools, there exists some techniques to get around the size limitation of these tools.

If you use the same source data, you’ll obtain the same charts, whatever the BI tool used. This seems logical. The same is not true about Advanced Analytic Software: When working on the same dataset and using the same modeling methodology, you’ll still obtain different predictive models, with different ROI, depending of the modeling tool used. This difference can be very significative: On most cases, TIMi give 5 to 15% more ROI.

One important thing that very few people know is that there is a good BI tool that is integrated inside Microsoft Excel! Microsoft Excel is able to use your database “in direct” without any exportation, like any other classical BI tool would do. With Microsoft Excel, you can directly connect to your database system and easily design with your mouse very complex BI charts.

Although the differences between the different BI tools are inexistant in terms of end-result, you can still observe large differences when it comes to: speed, cost of ownership, quality of the interface (“user-friendliness”), energy efficiency, beauty of the generated reports (is it eye-candy?), etc.