Sunday, 22 November 2009

QlikView and Reporting Services

I recently downloaded the Office 2010 and PowerPivot Betas so I could compare Microsoft’s current and near-future BI offerings against the competition – which in this case is QlikView. As it is, I haven’t actually had a chance to muck around with PowerPivot yet so I’ll just summarise what I found about QlikView vs SSRS.

Firstly, I should qualify this by pointing out that although it may not be comparing apples with oranges, it could be considered comparing Granny Smith with Cox’s. In other words, while QlikView and SSRS do pretty much the same thing, they do not have parallel feature sets or business contexts. However, there are a lot of BI implementations taking place where the people making the decision will see it as either/or.

As a long-time SSRS user it was going to take a lot to impress me, but QlikView has achieved this. More because of its reporting tools than its in-memory database. Anyone who claims it negates the need for a data warehouse should be dismissed with a withering glance as this simply isn’t true. Like any application, if you put crap in you will get crap out. The fact is that you need to put as much effort into preparing your data as you would when building as SSAS cube. However slightly less effort is required thereafter and it’s very quick with the data processing. But it looks pretty and makes configuration of most common charting features (e.g. median lines, multi-coloured bars) a simple matter of selecting a checkbox. Dashboards can be put together in minutes – good ones in less than an hour.

There are two killer features: chart scrollbars and parameter selection.

Now this may sound trivial, but if you’ve every tried to present a bar chart with 100 different data points then you’ll realise how clever the first of these is. Basically all charts that have data points extending beyond their defined area will put a scrollbar on the axis. Not the chart, the axis. So the legend stays fixed and you can scroll through all the data items within the small space allocated to the chart on the dashboard. There are some clever implementations of this in Excel but they are non-standard and you sure as heck can’t do it out of the box in SSRS. The SQL Server and Office teams need to sort this out pronto!

Parameter selection in QlikView is in a completely different league to SSRS. Basically the list of parameters have mutual filters and allow Ajax-style selection. So a list of 100,000 parameters doesn’t slow things to a snail’s pace or make it difficult to find items. It’s most impressive although it does highlight one of the major shortcomings of QlikView – namely that it doesn’t understand recursive hierarchies (although if anyone would care to dispute this I’d be most grateful). Feeding in a table with recursive keys leads to somewhat unexpected and unpredictable results. and even does recursive hierarchies (see comments).

The only other drawbacks I’ve found so far to QlikView are around security and customisation. Basically it isn’t too easy to do either, certainly nothing to match the logic-based security of SSAS or flexible security of SSRS. Also the drill through isn’t quite as intuitive, even if it is logical (charts drill through to more charts).

Overall I’d say QlikView is a very impressive product but it won’t preclude the need to get your data straight first. If Microsoft want to stay ahead then they need to get scrollbars on the chart axes and much better parameter selection than SSRS currently provides.

I haven’t covered QlikView as a server product (we were working with the desktop edition) so I suspect this will not be my last post on the topic.


  1. Actually QlikView has recursive hierarchies covered pretty well. When loading parent-child tables the prefix 'hierarchy' along with some parameters nominating the various components of any balanced or unbalanced hierarchy can be used. This is complemented by the function 'HierarchyBelongsTo', which generates and associates ancestor->leaf tables, so that QlikView can correctly aggregate measures in unbalanced hierarchies. Finally a tree-view list box takes cares the UI for users to select nodes from different levels in a tree.

    (any opinion expressed here are my own and not that of my employer, who happens to be QliView)

  2. I stand corrected! Seriously, thanks for clearing that up - I'll try it out shortly. I deal with a lot of healthcare data so hierarchies are really important to help make sense of it. That closes a very important gap for me.

  3. One other note with the recursive hierarchies - try using a hierarchical bridge table as outlined by Ralph Kimball... Works wonders.

  4. That's true Davydd - bridge tables are very good for natural hierarchies (e.g. employees, organisation units etc) and it's a real boon that SSAS manages them so effortlessly.