Reporting & OLAP

There exist many (Many!) different reporting software (i.e. OLAP tools – Online analytical processing tools) that allows you to create charts and graphs about the evolution of different KPI (Key Performance Indexes) that characterizes your business.

Typical KPI’s are:

  • Number of newly acquired customers
  • Churn rate
  • Sales amount
  • Etc.

Usually, OLAP tools are just adding a small “user-interface-layer” above an already-existing database or data warehouse. This interface allows the users to easily create (with their mouse) some reports & dashboards.

A little bit of Terminology: The difference between an OLAP tool and a reporting tool is the following: OLAP tools are displaying their reports inside a webpage while “simple” reporting tools are producing MSWord, MSExcel, etc. reports. OLAP 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 OLAP 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 OLAP 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 OLAP tool/ Maintaining a correct synchronization between both database copies might prove challenging.

The only advantage of In-RAM-OLAP 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 OLAP 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-OLAP 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-OLAP tools are now obsolete.

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

Differences between these tools

The differences between different OLAP tools are:

  • Is it “In-RAM-OLAP”?
    If yes: limited scalability, marginal speed increase
    If no: unlimited scalability (depends on the underlying database)
  • Charts: Some OLAP software propose only a small sets of charts
  • Report Exportation:Some OLAP 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 OLAP 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 OLAP 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-OLAP” 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 OLAP 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 OLAP tool. Even for limited In-Memory OLAP 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 OLAP 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 OLAP tool that is integrated inside Microsoft Excel! Microsoft Excel is able to use your database “in direct” without any exportation, like any other classical OLAP tool would do. With Microsoft Excel, you can directly connect to your database system and easily design with your mouse very complex OLAP charts.

Although the differences between the different OLAP 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.