Business Intelligence Battle Royale – Web Dashboards

A Comparison of BI Tools by Kingsley Hau

Recently I have been introduced to a few business intelligence platforms which I haven’t worked with before and it was suggested that a comparison between them would be an interesting topic for an article. As I have already written articles about MicroStrategy and SQL Server Reporting Services, I thought it was quite a cool idea and set about creating a few quick and simple dashboards. There are 4 different technologies in play here which are MicroStrategy, SSRS, Tableau Public, and QlikView.

Undertaking this little exercise can bring about a number of benefits. For example, whilst Aiimi are partners of MicroStrategy, it is sometimes the case where MicroStrategy isn’t the right choice for customers and an alternative solution is required. For this reason it is good for developers within the company to have some form of exposure and experience with different platforms and a willingness to switch over their skillsets for something a little bit different. It also opens up numerous doors where customers are already using a specific BI platform and a change in technology isn’t in the immediate picture. Last but not least, having some experience in working with multiple different platforms can only serve to expand our knowledge in the general area of business intelligence and allows us to improve our skills as consultants in the business.

To try and keep things as fair as possible I will be using a simple table of financial data in the form of an excel sheet or SQL Server table. I will also be aiming to create the same dashboard in all 4 platforms consisting of:

• A pie chart displaying the split of Volume traded by Exchange
• A line graph filtered by a company to show the high and low price of stock over time
• A bar chart of volume traded over time
• A grid displaying the key performance indicators of the company

I will also try to make it possible to filter each dashboard by company and time.

To judge the platforms I will be looking at various factors such as:

• Intuitiveness of the platform, how easy it is to pick up and begin creating dashboards
• Look and Feel of the visualisations
•  Unique features of the platform

I have deliberately restricted the factors in order to keep the article relatively short.

As this is a comparison from my point of view it will be difficult to make things completely fair; for example, I am trained and certified for MicroStrategy whilst my first encounter (in terms of usage) with Tableau and QlikView was only last Friday. However, I’ll try to make my judgement as unbiased as possible and will try to give fair opinions on each platform. As the reader you should be aware that certain drawbacks of each platform which I give might just be a case of myself not being able to figure out certain functions or features which were available to me (I know this would’ve been the case in MicroStrategy if I didn’t have any training).

So without further ado, let’s take a look at the first platform which I used.

Tableau Public

Tableau Public is a free BI platform in which a user can very quickly create a dashboard and immediately host it on the tableau web servers.

Intuitiveness

To begin with, I decided to load my excel sheet into the platform as a data source. This was extremely easy and almost required no thought process, even for someone who had never seen the interface before. After doing this I was immediately able to begin dragging and dropping report objects onto the workspace in order to create data visualisations.

My next step was to check compatibility with SQL Server; this too was quite a simple process as the option is available on a list of possible data connections. Once the option was selected I only needed to type in the connection details and the process was done.

At this point I decided to have a play around with the tool to see what kinds of charts and graphs I could make. A cool feature I found from doing this was the way in which the visualisations would change dynamically as soon as I dragged a new object onto the workspace. This was really interesting as the graph would often generate a suggestion which was either exactly what I wanted or something very similar. Changing the suggestion was also quite simple as a toolbox was provided showing you which available visualisations there were and what data alignments were required for them to work properly (a great feature in my opinion).

Just from quickly playing around with the visualisations I was quickly able to create and complete my dashboard.

Look and Feel

I was given quite a nice array of options when trying to modify the look and feel of my dashboard. It was quite easy to change the colours of the visualisations if I thought necessary and the default colourings in general did not clash. Resizing of each visualisation on the dashboard was achieved simply by dragging the edges of each frame and the sizing of the graphs and tables could be made to fit simply by right clicking and selecting the “fit to…” option.

With these features I was able to create quite a clean looking dashboard which fitted onto my screen quite nicely.

One of the bad things about the look and feel was the legends which came with the graphs. They seemed to come as separate frames to the graph itself and often took up a lot more space than necessary. It also made it difficult to fit other objects onto the dashboard without it looking quite cramped. Having failed to find a way to merge the legend into the actual graph’s frame I decided it would be best to omit them from the entire dashboard. This left the dashboard looking more polished at the expense of slightly less information for the viewer.

Unique Features

As mentioned earlier, one of the best features was the dynamically changing visualisations as I dragged and dropped objects onto the workspace. This was really great because it makes it easy for the user to understand what is happening with their data as they are working; even one who doesn’t know much about data visualisation or business intelligence.

Another great feature – although not unique but definitely worth the mention – was how easy it was to build and publish the dashboards and worksheets onto the web. Tableau public provides web servers for free and users can save their work onto the web, making it available for others to view immediately. Furthermore, the dashboard itself can be embedded onto a website so that viewers can interact with the visualisations.

The third and final feature which I noticed was Tableau’s ability to recognise a datetime attribute and provide in-built functions to generate Year, Quarter, Month, and Day fields. This is an excellent feature as it saves the user any trouble in terms of data management to create time dimensions on different levels.

MicroStrategy

MicroStrategy is a BI tool where developers – with the correct training – have the potential to create some very powerful applications for both the web and on mobile.

Intuitiveness

Since I have received training for MicroStrategy it might seem unfair for me to judge how easy it is to “pick up and go” on this platform. However, when I first joined Aiimi I was actually asked to install and explore MicroStrategy. At that point in time I had not used the tool before and can still remember (somewhat) a bit of what it was like trying to get started. So I’ll use those experiences as the basis for my opinions here.

When I first installed MicroStrategy I was quite overwhelmed with what I was provided with and didn’t quite understand how to get started. I remember opening up the Desktop component only to close it again thinking that it was the wrong application for creating visualisations and dashboards (Actually it was more like graphs at this point since I didn’t know what dashboards were). The next thing I opened was the Web component but was met with a similar situation where I didn’t really know what to do from there. It wasn’t until I consulted the training documentation before I was finally able to begin creating a simple project. From this point things become quite complex where you must create extra ODBC’s, generate metadata, create database instances, and then attributes, facts, and metrics before you can finally go about creating your very first graph. This might sound quite critical compared to what I said for Tableau, however, this is a fact where MicroStrategy is quite a large application and does demand more time and effort from the user to get to grips with the software. I can point out however that I was only just starting out as a business intelligence consultant back then and had never used any other BI platform before. The story could be quite different had I been experienced in some alternative before picking up MicroStrategy.

Of course, the story these days is very different since I am trained and certified as a developer. With this training I am quite capable of quickly creating new projects from scratch and developing a dashboard in the space of a day. Using the stock data I was able to create the following dashboard in about a half an hour:

Look and Feel

Due to my experience with MicroStrategy (and also from recent training) I think I am quite adept at creating a dashboard with a very sleek and sexy look and feel. MicroStrategy web provides a view mode where a developer can actually modify colour themes, graph sizes, placement, scaling, and so forth whilst looking at the live running dashboard. The modifications are applied in real time and the effects are immediately viewable. Due to this the developer can basically make pixel perfect adjustments to the look of their dashboard.

It is also possible to apply special effects, 3D shading, and threshold highlighting to make certain parts of the dashboard more eye-catching.

It is difficult to find drawbacks in terms of design for MicroStrategy due to the fact that it is so flexible and there is often a way to achieve the effect you want. However, one thing I feel should be mentioned is the fact that some widgets – namely the Time-Series widget – are restricted to how they look out of the box. The Time-Series widget is restricted to being coloured black which can work with certain colour themes but may clash heavily with others. Since the Time-Series widget is so useful and I believe quite popular, problems may arise in design opinions when a customer is requesting a specific colour theme for their dashboard.

Unique Features

I know of many features available in MicroStrategy which I didn’t see in the other platforms. Since it would be overkill to list them here I will select three which I think are most important. The first feature has already been mentioned – The view mode called “Editable mode” which allows a developer to apply design changes whilst the dashboard is still running, enabling them to make pixel perfect adjustments. This is really great as it allows the developers to create some of the most amazing looking dashboards which can, through the use of visual manipulation, guide the user through the dashboard in a story-like way.

The second feature is a relatively new addition to MicroStrategy called Transactional Services. This feature allows the developer to create a dashboard which enables the user to write-back to the database on the fly. This is a really powerful component as it means MicroStrategy dashboards are no longer purely tools to display visualisations but also allow users to perform and commit actions based on the intelligence they have gained from the data.

The third and final feature is MicroStrategy’s ability to use Intelligence cubes to cache data for dashboards. This will make things much quicker when a user runs through the dashboard, improving the user experience and allows the rendering of massive datasets in smaller time frames.

QlikView

QlikView is a BI tool intended for very quick dashboarding where the user can combine numerous data sources.

Intuitiveness

Whilst getting started in QlikView wasn’t as easy as Tableau it was still relatively simple. Upon loading the application I was presented with a wizard which guided me through loading a data source (excel file), verifying the data being loaded, and even optionally allowed me to create an initial graph for the dashboard/workspace.

I next wondered if I could connect to a SQL Server database but didn’t manage to figure it out (admittedly I wasn’t looking very hard) but I believe it is possible according to what I saw when looking around the internet and based off the QlikView website.

Since I couldn’t connect to SQL Server I decided to continue creating the dashboard with the excel file as my data source. Since I already had a graph on the workspace (from the wizard) it wasn’t very difficult to go about creating the next graph since I had something to reference. To create a new graph I was prompted to select the data dimensions which was simple enough in itself. Next I was prompted to enter Expressions, which wasn’t immediately obvious but didn’t take long for me to realise it was asking for metrics. In adding a new expression I was required to enter the equation to calculate the metric based on the field. I felt this might be confusing for somebody unfamiliar with developing BI applications but at the same time held some potential to what could be achieved in terms of calculations and derived metrics. After entering the dimensions and expressions I was asked to select the styles, colours, and general presentation of the graph itself.

Once the first graph was created it didn’t take me long to go about creating the rest of the dashboard.

Look and Feel

I felt that the look and feel of my QlikView dashboard didn’t look as good as the one I created in Tableau and had a hard time figuring out how I could achieve certain visual effects with the limited time I allowed myself on the tool. It was possible to resize graphs and grids simply by dragging the edges and I managed to do simple things such as changing colours and styles. However I believe some training and more experience is needed to create something of a similar quality to a MicroStrategy dashboard (I know it’s possible from images I’ve seen on the web).

One of the good things was the legends for the graphs (remember this was a drawback in Tableau). When the list of items in the legend was extensive, the graph introduced scrolling rather than trying to fit everything into the limited space. This ensured that all items were viewable in the legend unlike in MicroStrategy where it would try to squeeze in as much as possible and exclude any overflow.

From looking at the line graph in the QlikView dashboard you can see that axis scaling was a bit of a problem. The option to remove the zero from the axis was available but since the graph extended over a large frame of time it tended to flatten the trends in the line. I tried to restrict the time-frame of the graph but was unable to get it down to a specific month of a specific year (i.e. April 2009). I also couldn’t find any option to make the graph dynamically scale according to the range of values being viewed currently.

Unique Features

Unfortunately for QlikView, I found it difficult to spot any unique features which distinguished it from the other BI platforms. I did mention its ability fit numerous items into a graph’s legend quite nicely but it is a small plus in the larger picture.

However this view might be a bit unfair as it does seem that QlikView requires some degree of training to use properly. If I did have this training then I might’ve been able find a few other features which are unique to QlikView.

SQL Server Reporting Services

SSRS is a BI analysis and reporting tool which comes with Microsoft’s SQL Server studio.

Intuitiveness

Using Visual Studio a developer can create a new project and select the Report Server Project to create a new reports dashboard. There are two options, one of which includes a Wizard. By selecting the wizard I was guided through the whole process of connecting to a data source (SQL Server), defining a query in SQL, and selecting the attributes and metrics of the initial table displayed.

On completion of the wizard I was presented with a workspace containing the title of the report and a table of data objects. I could decide to either keep the table and title or remove them and insert new graphs and charts as desired. Once inserting the graph it is quite easy to begin applying the attributes and metrics by dragging the dataset objects and dropping them into the chart. Similar to Tableau, the graph will change the data being presented based on what you drag and drop but not to the extent where it will change the graph type to intelligently suggest a recommended visualisation for the data alignments. This gives the developer an initial preview into what the graph should look like when executed but they are still required to run the report to view the actual results and how it will look live.

Whilst creating graphs and tables in SSRS seem to be quite easy and straight forward, I found that implementing filters to be quite difficult and actually required the developer to have “external knowledge” to achieve. When creating the report the developer can implement parameters which are treated as filters. These parameters must be passed back to the query which generates the dataset. If the developer doesn’t have any knowledge in SQL or whichever query language is in use, it can be quite difficult to implement a filtering feature for the reports. It also means that extra work is required on the SQL level to filter a report on the time dimension – datetime fields must be converted on the fly if a column doesn’t contain the corresponding month, quarter, or year.

Look and Feel

Due to the complexity of implementing the time filter dimension I skipped that stage and was therefore unable to create a nice looking line graph or bar chart to represent the stock prices and volume traded over time. This somewhat makes the dashboard look worse but I can imagine that it wouldn’t look far off from the other tools if I did make the effort (Therefore I wont be marking SSRS down on this point).

The default colour scheme of the reports seems to be quite clean and uncluttered; and as with the other tools it was possible to resize graphs by dragging the edges. The filters are allocated a space at the top of the report (As far as I know they can’t be moved from here) and appear in the order of the different parameters implemented. Since they are given their own space within SSRS it saves the developer allocating any space to include them. But it could be a problem if the report was to be embedded onto a website; extra coding might be required to include filters where parameter names must be matched exactly (I don’t know if it has to be this way but this was the approach used in one of my projects).

One major drawback to the look and feel was the table object. As you can see in the screenshot above, the table doesn’t have a scrollbar and it actually goes on and on until the list of data comes to an end. I tried to see if something could be done about this but was unable find a solution. Due to this the dashboard was extended to accommodate all the rows, making it look less professional.

Unique Features

There are a few unique features which I found in SSRS whilst creating my dashboard. As I was using stored procedures to drive my datasets I was able to get SSRS to automatically pick up any parameters which I wanted to implement. This saved me the trouble of figuring out what I needed to create in terms of report objects (although I did need to map them correctly for the filters to work).

Another thing about the datasets in SSRS is that they are (or can be) SQL driven. This is similar to MicroStrategy’s freeform SQL component where you can extract data from a database via your own queries. This, whilst more complex and requires technical knowledge of SQL, provides many benefits if used properly. For example the developer can join multiple tables together into a single dataset and can also use case when’s to create different representations of the data.

Conclusion

In my opinion Tableau public was an excellent BI tool. I was very impressed with how easy it was to get started and was able to create dashboards extremely quickly. To put this into perspective, I learnt of tableau public and within an hour I had hosted a simple dashboard on the web. I also quite like the look and feel of the dashboards; even without much consideration into the style and design the dashboard looks quite clean and professional. A drawback to this tool could be the fact that it seems to be purely BI focused. This may sound strange considering the comparison is about business intelligence tools; however, whilst tableau has a mobile component it doesn’t seem to do much more than visualise data on the screen. Going off the lessons taught by Sylvain here I believe it is important for business intelligence to be immediately “actionable” without the requirement of switching to another tool or being dependent on another application.

MicroStrategy is definitely a tool which requires training and experience before it can be used properly, even for some of the simple requirements. However, once a developer has been trained and has gained some experience they can begin to create extremely powerful BI applications. In terms of flexibility and array of tools I think MicroStrategy offers the greatest range, and it is possible to achieve things which are not available in the other platforms. This power and flexibility does come at a price however. The cost of the license to use MicroStrategy can be quite expensive and there are multiple different licenses for the different components such as Transactional services.

QlikView seems to sit in the middle ground between Tableau and MicroStrategy. It is relatively easy to get started and doesn’t take long to make a rudimentary dashboard. However it is not as simple and quick as Tableau in that it has a more complex interface to define attributes (dimensions) and metrics (expressions). I feel that this more complex interface enables a developer with training to create some really impressive visualisations which can provide a lot of intelligence around the data; however, in this sense it still falls behind MicroStrategy.

I feel that SSRS is aptly named as I don’t feel it is a dashboarding tool but rather a reporting tool. I made a very quick search on Google images to see if there were any good examples of a SSRS dashboard but didn’t really come across any (There was one that looked good but it turns out an external component was involved). It isn’t difficult to arrange graphs, charts, and tables in such a way to make it look like a dashboard but I think its intention is to just generate simple reports based on the data stored within the database.

« Major Accident Hazard Analysis by Jez Stroud

It’s On – A Week of Work Experience with Aiimi »