Dashboards, Web Reporting and Business Intelligence (BI)

Obviously web reporting is nothing really new; one basic approach is this:

1. Aggregate your data and output it in some kind of structured format, usually XML
2. Request this information and generate some visualizations of it
3. Serve these visualizations through a web server

Pretty basic stuff. All of the tools that I have seen essentially make one or all of these processes easier. Unfortunately no matter what tools you choose, the rule of engineering stands: Fast, cheap, good – choose two

In this context it is more like: Less proprietary code, free software, easy to use/maintain – choose two. The good news is that all of the solutions described essentially have the same result: a web dashboard. The choice that needs to be made, is where you want to go with it? In the spirit of Enterprise software the clear winner is the solution with NO proprietary code, easy to use interfaces, and maybe some support.The biggest obstacle in this model is step 1, data aggregation and standardization.

Step 1 may involve lots of substeps that require some substantial development work including:

1. Connecting to various datasources
2. Writing queries to retrieve the data
3. Parsing the data into XML (or some other standardized format)
4. If interaction is needed, the data aggregator will also need to accept inputs and query dynamically

The issue is that all of these steps may differ among various datasources like oracle, csv, excel, etc. And yes, there are plenty of tools to help with this step. The main point though is that no matter what solution you choose, there will a substantial portion of development time needed for Step 1; even with a GUI you still need to write queries. It is really up to you if you want to follow this model and incur development time, however for an “executive” dashboard I feel it essential to follow this model.

The solutions I found can be divided into 2 categories: Web Reporting and Business Intelligence (BI). Web Reporting covers the basic aspects of the above model and nothing more; whereas the above model is just a subset of what  Business Intelligence can do. One thing to remember also is that its always “pay to play” with the support for the free solutions. They’ll give you the software, but you need to pay for their help. Also I tried to chose solutions whose free software should have all the features we need.

Web Reporting Solutions

• 

  Open Flash Charts Pie Chart

  
  1. Open Flash Chart – The simplest, (cheapest?) and fastest way to get some dashboards is to just write some code. If we are only connecting to one database, no problem using some free tools like Open Flash Chart. It is the simplest because we don’t really need to abstract any data, so step 1 processes are minimized. However it isn’t Enterprise level and makes big assumptions on the sources and format of the data. Any changes to the model will require code edits and support calls which doesn’t make for good sustainability.
• 

  Open Flash Charts - Area Chart

  
  2. WSO2 Web Application Server + Open Flash Chart – This combination is a little more enterprise. The tools are still free, which is great. The Web Application Server can handle step 1 pretty well and has a nice GUI interface to make all the data connections and output some XML as a web service, definitely enterprise level. However we still need a small middleware layer to accept the XML and generate the flash charts which require less (but still some) proprietary code as the first option. The only code changes that would be needed would be if you wanted say different charts, or a different layout, but could probably be done by someone with some basic HTML and scripting knowledge.
• 

  SAP Xcelsius Dashboard

  
  3. WSO2 Web Application Server + SAP Excelsius Engage – This combination is fully enterprise level. It could probably bet set up with no proprietary coding at all. Excelsius is stand alone program that allows one to graphically design the dashboards and connect them up with various datasources. The only problem is that it costs a minimum of $1000, and for the server edition (which I feel is unnecessary) could be as much as $5000. So that may be out of budget. But it does produce beautiful results.

Business Intelligence Solutions

I had never heard of Business Intelligence until I started doing research for this project. The best way I can describe it is that is a huge web based platform that seems to do everything. First off it has something called Data Integration. This is a very cool program that will not only aggregate different data sources, but allow you to transform among the datasources and output to pretty much anything. For example I could query an Oracle table and an Excel Sheet, aggregate those two into one dataset perform some transformation like sorting or pivoting and then output the result to a MySQL table. Its actually quite amazing. All of these data integrations can be set up as jobs that can be run in batch or run from command or run from some web service. From there it also includes a Web BI Platform, which is kinda like a portal that can show reports, dashboards, tables, basically anything and also has some admin for user control and permissions. It also includes tools to design and build reports and dashboards. It can also do complex analysis and data mining and will probably take out your trash and wash your car too.

Anyways, the benefits are intense, but may be a bit overkill if you just want a dashboard. Things this complicated can quickly turn from dream to nightmare however with these BI solutions almost all aspects can be managed through GUI tools. The real problem is that it will take a serious commitment to make it work because of its complexity. I am not sure they are the right solution for you, but maybe you will think of something else that it may be useful for.

• 

  Pentaho Dashboard

  
  1. Pentaho – A free, open source collection of BI tools. Again with the “pay to play” support. Pentaho offers all of the functions specified above and has GUI tools for managing all of them. I was able to actually install Pentaho and was able to generate some reports, however it was quite difficult to configure the server and generating my own dashboard required some very complex configuration. But the data integration tool was stellar, I think on its own it is a very powerful tool that could be used outside the scope of the BI platform.
• 

  IBM Websphere Dashboard

  
  2. IBM Websphere – I read all the papers for this but wasn’t actually able to try it out. But it is a huge system that probably does everything and I won’t even talk about it more because it probably costs many thousands. But it is a good basis to see what can be done.

Unless you see other potential value in implementing a BI system, I would say that option 3 under the web reporting is the best bet if you can spring for the software and development costs.

Related posts:

1. YAML – My Favorite Non-Markup Language I have been using YAML for awhile and decided I’d...