Free VS Non-Free

Our company recently implemented a BI Reporting solution intended for a retail company with both, an offline retail chain and an online web-shop. This was a Demo project, so during the planning phase our team decided to use two different platforms, including different back-end and front-end software.

In general, we planned to compare the free and non-free software available for similar BI Reporting implementation.

We chose the following platforms for comparison:

• Oracle XE (Express edition) as DB/DWH + SpagoBI as reporting tool
• MS SQL Server as DB/DWH + MS SSRS as reporting tool

These two platforms were chosen due to the following reasons:

• Oracle XE is free and has only resources restrictions, so it is good for small and medium size projects.
• SpagoBI is free, open source software. Only technical documentation and support services are paid.
• MS SQL Server and MS SSRS are widely distributed and one of the least costly commercial products used for BI implementation.

To make this competition fair and comparable we used the same data sets on both DBs and were implementing the same reports with the same UI (or at least the most similar UI) on both front-end sides.

For data synchronization between the two DBs we used MS SSIS for ETL implementation (data was generated on Oracle and then moved to MS SQL).

After the decision of which two platforms are to be compared was made, the project was started.

 

Initial setup, tools tuning and preparation phase.

At the very beginning the software engineers successfully installed all MS software (Server + SSIS + SSRS), connected all the parts, built an ETL, etc. In general it took about one work week (5 work days) for all these activities. First positive conclusion about MS was that they can have really good documentation with no issues occurring during setup.

For testing, we used MS SQL Server 2008 R2.

The second platform was not as easy to setup. Although Oracle was installed pretty fast and without tweaks, dealing with SpagoBI was pretty tough. It took four work weeks for complete installation, tuning up, and removing all issues and side effects.

The following were the most cumbersome issues:

• Metadata scheme for Oracle is completely dysfunctional. We were a little disappointed with this conclusion, since Metadata was downloaded from the SpagoBI web-site.
• SpagoBI uses other reporting engines (like JasperReport iReport, etc.), which are embedded into it. When SpagoBI was installed, only few engines were working correctly, but most of them were non-usable and required a lot of additional tunings and tweaks to make them functional.
• Error logging was not very convenient. In most cases only “NullPointerException” occurs in the log, so it is impossible to detect the roots of the problem.
• Issues with data types (for example, SpagoBI has issues with understanding ‘date’ type, etc.).
• Manual tweaks for establishing connection to Oracle.

Here we used Oracle XE 11g and SpagoBI 3.6.0.

SpagoBI installation and tuning would be much easier with manual in hands and support service from SpagoWorld, but we agreed to make the experiment fair and not pay any money in this case.

Although our team is already experienced with SpagoBI installation and next time it will take no more than one week to complete, for new users it may be challenging at first.

Nevertheless, all troubles were eventually eliminated and the team was ready to start on the next task.

 

Data preparation

The next stage of our project was all about data preparation. Here is what we did:

• Generated customer lists with their attributes (first name, last name, date of birth, marital status, place of residence, etc.)
• Generated products with attributes (name, category, sub-category, price, etc.)and matched them to commodities
• Generated vendors and connected them to commodities
• Generated stores and assigned geographical information to them
• Generated marketing campaigns with all attributes (begin data, end date, promo channel, promo sales, etc.)
• Generated transactions (both offline and online) with all the attributes

All generated data was carefully validated and a lot of improvements were made to make the data as close to real retail data as possible. We were using this “fake” data to secure the real retail data and customer information.

The same data set was used to test both platforms.

 

Reports implementation

This project phase was most enjoyable. In this phase a real “battle” between SpagoBI and SSRS took place.

We implemented a set of different reports intended for company executives and the marketing department in order to track company performance using benchmarks and KPIs, review sales performance, analyze effectiveness of marketing campaigns, and analyze customer segments and their behavior.

In this task, both MS SSRS and SpagoBI proved to be good tools, providing good graphic capabilities with some differences in implementation of the UI.

Below you will find the most remarkable pros and cons of each tool.

 

SpagoBI

Jasper Engine embedded into SpagoBI

Pros:

• An impressive number of various charts and UI elements
• Convenient implementation of links between documents (for drills implementation)
• Interactive charts, which allow information change without necessity to re-run the whole report

Cons:

• No full support of iReport functionality (for example, Spago column sorting in iReports does not work)
• Pagination is not supported in iReport under Spago
• Expand/Collapse functionality is missing in the tables

Composite Document Engine

Pros:

• It is possible to combine few documents in one using frames. It is very convenient to update one document’s information basing on actions performed in other document.

Cons:

• Frame size could not be set in some proportion or dynamically, it is just static
• Unable to drill from the frame

Geo engine

Pros:

• Geo report is a combination of SVG and the data set
• Data set could be built using various data sources like SQL, Groovy, JS, etc.
• SVG is convenient since it could be edited in any graphic editor

Cons:

• It is impossible to choose document map as document parameter
• It is impossible to drill from the map
• Scaling is very slow
• Not all browsers support SVG format, so maps can often not be displayed

 

MS SSRS

In general, SSRS is a really nice and usable reporting solution.

We observed the following:

Pros:

• Really powerful out-of the-box Geo Reports
• It is possible to source maps on Geo Reports from data sets
• It is possible to use ARC maps (a widely distributed maps format)

Cons:

• Static charts
• It is impossible to add additional axis on charts
• It is impossible to customize filters in the report parameters
• It is impossible to expand standard SSRS reporting functionality with 3^rd party engines

 

Afterword

We’ve successfully developed BI Reporting, with both solutions currently live and fully functional. Some users of these reports prefer SSRS , others prefer SpagoBI reports. The users keep arguing which solution is better and what functionality is more convenient in the 1st and in the 2nd case.

In general, both solutions are usable and BI Reporting systems can be built using either of the platforms. The following statements provide deeper insight into our findings:

• MS SSRS is good as out-of-the box tool: easy to install and easy to develop reports
• SpagoBI is a bit hard to tune and use when you are a new user
• SpagoBI is pretty flexible and provides much more powerful capabilities when you’re familiar with this software
• SpagoBI is free, while MS SSRS is not

  

Do you have experience in using different BI software?  Please, provide your comments if you do. We would very much appreciate if you share your own experience of using BI tools, e.g. which tools you were using and for which tasks, how do you rate these tools, and what difficulties you observed during development.