A Quik Look at QlikView
August 15th, 2009 by Mark Rittman
Outside the world of Oracle, one of the rising stars of the BI world has been Qliktech, the Swedish software company behind QlikView. QlikView is one of a new breed of desktop, in-memory BI tools (see also the forthcoming Gemini tool from Microsoft, an in-memory, column-store BI tool that’ll be delivered as part of Microsoft Office 2010) that are being evaluated by customers as alternatives to traditional relational query tools such as Oracle BI Enterprise Edition and OLAP servers such as Essbase or Oracle OLAP. So what is QlikView, and how does it compare to Oracle’s BI tools?
QlikView, compared to Oracle’s relational and OLAP query tools, has a slightly different approach to storing and querying data. Whilst it can load data from relational and file-based data sources, it then stores the data in-memory in a compressed form with associations defined between data items rather than the traditional joins that we see in relational query tools. The idea behind these associations is that they are derived by Qlikview during the data load based on matching up columns across tables with the same name, and then when you query the resulting dataset you can analyze your data using these associations.
To take an example, the following QlikView demonstration shows data for a consulting company in a Windows thick client application. To analyze your data, you click on a data item, the numbers on the charts are then filtered by this value, and other data items that are associated with this selected value are then also made available for selection. The screenshot below shows this filtering in action, you can see the selected fields in green, the ones that are greyed out are no longer available for selection.
Now obviously this way of selecting data items, and this method of “association”, is fairly alien to traditional Oracle BI users and so I put together a simple example using the Sales History (SH) dataset available on recent Oracle databases. The first step in creating some QlikView reports is to define a new document, which in the end will hold the report layout, the data loading queries and the actual reporting data itself, which gets loaded in to memory when you open the QlikView document. Once you’ve defined the new document, you create a script to load data in from in this case an Oracle database.
This is in fact a similar process to putting load scripts together for Essbase and Oracle Express, with connections being made through ODBC and OLE DB for databases and direct connections being made to file and HTTP data sources. I get the impression that the most regular use case for data loading in QlikView is loading from files, and this would correspond to the target market for this tool which is desktop analysts who want to report on their data separately from the infrastructure around their enterprise BI tools and data warehouse.
Once you’ve brought your data in, you can view it either as a database (source) data model, or as the logical model that QlickView generates when it loads in the data. In fact for the SH schema, these are both the same as the joins in the Oracle schema are turned into the associations that QlikView uses, so what you see looks just like the regular sort of data model you’d see in any relational query tool.
The joins (or associations) are defined by QlikView during the import process by matching up columns of the same name – each match creates an association. This means that to create a join, you need to alias column names as they come in (to change ID to CUSTOMERID for example, if this is required to join to another CUSTOMERID), and you need to create aliases to break associations so that the STATUS column from the orders table isn’t linked to the STATUS column from the customer table when this isn’t really appropriate.
For an Oracle dataset with well defined primary and foreign keys, the logical table structure that QlikView creates in memory is usually pretty much the same as the Oracle schema, but for files or with data sets with lots of cross-related data, QlikView will create synthetic keys and synthetic tables to record the associations and assist with analysis. Internally QlikView tokenizes and compresses the data as well which meant that, for the Sales History data set that I worked with earlier, the total size of the QlikView document (including the data) was only 4MB and took about 1 minute to load and prepare, whereas the corresponding Essbase data set (as built in this OTN article) took up several gigabytes of space and took over an hour to load and prepare. Impressive stuff.
Loading the normalized data set that I used for this article on OBIEE data modeling produced the following logical QlikView table diagram, which as you can see is a direct reflection of the normalized Oracle data model.
This ability to report against normalized data obviously makes setting up the data model easier compared to OBIEE (notwithstanding the need to rename columns to make and break associations), but it then doesn’t make any distinction between columns that are measures and ones that are dimension attributes, which makes working with data in reports a bit more complicated.
So once you’ve got the data in, how do you create a report? And more importantly, how do these reports compare to Essbase or OBIEE reports? The actual report creation process is a bit like working with tools such as Hyperion Interactive Reporting or Web Analysis in that you construct an application made up of crosstabs, graphs, gauges and the like, which are then run either in the thick-client Windows application or online using a variety of thick and thin web clients. If I wanted to create a bar chart that allows users to select the product category, month and state of purchase and then graph product sales broken down by customer satisfaction rating, I’d add three field list boxes to my document and then create a bar graph chart, which would be automatically filtered by whatever I select in the lists.
In the screenshot above, I’ve first selected the “Computers” product category which filters the graph, and highlights the states and months that have sales for this category. If I then selected the CA and NY states, only the months that are associated with sales (quantity) for Computers and CA / NY sales would be highlighted, and so on. As such, the “Qlik” part of Qlikview seems to translate to progressive filters of your data based on the items you have selected, with the filtered-down dataset being made up of the data that will join (or “associate”) with the data items that you have selected.
If you’re looking to produce Essbase-like reports where data is shown in hierarchies, this is a bit trickier. There isn’t really a concept of hierarchies in Qlikview; you can create column groups which group together columns that are related, but I can’t see a simple way to select column members, for example, by their position in a hierarchy which is pretty standard in OLAP tools such as Essbase.
Displaying data in hierarchies is a bit simpler, especially as QlikView automatically calculates the relationship between columns and their values so that, for example, if you display product categories and products in a pivot table chart, the product are automatically shown under the categories that they relate to.
What I found trickier (as in I couldn’t do it) was to create a pivot table with regions, for example, across the top of the pivot, or to add page items or other controls. I’ve only used QlikView for a few days and so I may well have missed this feature, but none of the example documents that came with either of these features and therefore I suspect that it’s not something that the product does, for the moment.
Working through the examples that ship with QlikView 9, visualizations and fast access to data seem to be the main strengths of the tool. For example, the Sparkline graph type that features strongly in Stephen Few’s “Information Dashboard Design” book is present and correct in QlikView:
As are butterfly financial-style tables, used for displaying P&L reports and other finance data.
One of the examples uses Google Maps to illustrate data, in this case around house for sale in London.
Where QlikView is weaker in my view, compared to any of the Essbase-backed query tools, is around number-intensive and hierarchy-intensive applications. It’s not easy to select dimension members via a hierarchy, the scripting and calculation language that comes with the tool obviously doesn’t support the richness of languages like MDX and Essbase calculations scripts, and of course there are not the applications such as Planning and Financial Management that you’d expect to make use of in the Finance Department.
That said, for what it does (provide fast access to star and normalized data sets, including data sourced from files and from the web) it does the job very well, with excellent load and preparation times and sub-second response times, even with large data sets. You probably wouldn’t use Qlikview instead of Essbase or Oracle BI EE as they address different requirements, but as a sales analysis tool or a tool to analyze OLTP datasets, it takes some beating.
It wouldn’t surprise me to find that Oracle or another of the big vendors acquires QlikTech at some point as QlikView would make a nice complement to the more enterprise, heavy-duty query tools, especially when tools such as this and the forthcoming Gemini from Microsoft threaten to create desktop BI tool “bridgeheads” on users’ desktops as they are just so easy to use and to set up (can you imagine getting Business Objects and all its technology stack up and running over a couple of evenings, for example, let alone learning how to use it?). It wouldn’t also surprise me if vendors such as Oracle do more to bring their database and query tool processing in-memory, given the amounts of RAM available on users’ desktops these days and some of the advances being made in data compression technologies.
August 16th, 2009 at 12:08 am
Mark,
Quite interesting and timely article as Qlikview has been mentioned very frequently at my current customer site Qlikview has been mentioned and positioned as an alternative for OBIEE for a large upcoming project involving a considerable number of OLTP applications as source on different formats, mostly file based, for operational and desktop analysis reporting. Considering the kind and purpose of the desired reporting, fast access to OLTP datasets and response time to facilitate fast reaction to a reported situation, I do see Qlikview as a good solution, not as a substitute of OBIEE, but as the right tool for the right purpose. Another arguments that has been used for acquiring Qlikview as reporting tool is the pricing compared to OBIEE licensing. Should not be the nr 1 reason for making a decision but it does counts in time of crisis!
There one things I was wondering while reading your article; would you still have advantage by creating an integration/consolidation layer for the source data instead of leave it to Qlikview? Once Qlikview bases joins (associations) on columns with the same name it could be useful to have a data layer where naming conventions are applied and eventual data quality conditions and business rules are previously used.
August 16th, 2009 at 8:11 am
Hi Chico
I haven’t used QlikView enough to know whether it would benefit from denormalized (star schema) tables, as opposed to normalized ones, but I suspect from what I have seen that it doesn’t really matter, although simplified joins declared using FK and PK constraints will make the detection of associations easier and simpler.
I would agree with your comment though about an integration layer, which would achieve as you say (a) consistent naming standards, to make and break associations accordingly, and (b) to deal with data quality issues.
One thing I would say having worked with it briefly, is that the style of UI and the method of selecting and filtering data might not suit all users and all reporting scenarios. It’s tricky to work with and display hierarchies, and building a new report is more like extending an application rather than creating a report using the kind of report writer you get with Answers or Discoverer. That said, where the tool is strong is on fast access to data (including lots of data), the ease with which you can bring in new data (though I don’t think the integration features are as strong as Oracle BI EE’s federated query feature) and the ease in adding filtering etc.
regards
Mark
August 16th, 2009 at 2:47 pm
Chico and Mark,
as a QlikView professional, I’m very glad to see such a detailed review done by an Oracle BI pro.
I can confirm that denormalized data structures generally work better than normalized, however you can only feel the difference on truly large data sets (in comparison to the size of the hardware).
For smaller data sets, when performance is not an issue, we typically organize data based on developers’ convenience, speed of load and readability.
I’m pretty sure you’ll find QlikView refreshingly flexible and easy to use, as you develop your first projects for your customers.
cheers,
Oleg Troyansky
Natural Synergies, Inc.
August 17th, 2009 at 8:20 am
Hi Mark,
I’ve linked to your post on my qlikview blog. Thanks for the interest and qool to see that an authority on Oracle BI writes about Qlikview!
Thanks,
Gilles
August 17th, 2009 at 1:09 pm
Fairly good write-up.. as someone who’s looking at both QlikView and OBIEE as potential enterprise BI solutions, I agree with a good chunk of what you’ve written. QV has taken a step in the right direction allowing users to build their own objects, but it’s still extremely bulky when compared to most of the BI/analysis tools in the market. In many cases, functionality available through Oracle’s tools requires a non-trivial QlikView mechanic – a good example of this is using variables or dummy columns to simulate page items, which QV does not support natively.
One thing that bears a mention – QV9 does, in fact, support hierarchies, via the Hierarchy() and HierarchyBelongsTo() script functions (these can then be displayed in listboxes). There are some limitations – as per your example, I’m not familiar with a trivial way to select members based on hierarchy position – but the hierarchical listbox is sufficient for most uses.
I’m inclined to agree that OBIEE and QV would be effective used side-by-side (although obviously, this is an expensive proposition for medium-sized companies), but right now, I’d be more inclined to use QV as the primary tool, with OBIEE’s tools used to complement QV’s shortcomings where it comes to power-user / analyst requirements.
August 17th, 2009 at 4:53 pm
“What I found trickier (as in I couldn’t do it) was to create a pivot table with regions, for example, across the top of the pivot”
Yes you can create pivots with both vertical & horizontal fields & subtotals.
Try clicking on the column name and dragging it around the pivot table – you should be able to get a horizontal display at the top.
September 9th, 2009 at 3:34 pm
Small point re. pricing of OBIEE. As I understand it, you can get (almost) all the OBIEE components under the “starter” licence OBISE1 for £609 for up to 50 users, then trade up to OBIEE later.
That said, I’ve just been talking to a Qlikview user who loves it.
Simon (BeLife.co.uk).
January 29th, 2010 at 11:01 pm
I think hierarchies are nothing but grouping of items. say region,district and territory or market and product. You can have drill down and cyclic groups in QlikView. you can also have horizontal components(dimensions) on the pivot table by using expressions. If you want a granular report for hierarchy dimensions, just expand it to the lowest dimension and export it to a text or excel file. or create a loosen table with the required hierarchy and show it in a separate sheet as a table which is nothing but a report. relational schema is the ultimate because that is how the world is based on relations. you can also see that there is not much demand for hierarchical and network databases.Although these products came earlier. But QlikView supports any hybrid or specific data model. The only problem with Qlikview applications is as the data grows in your application the application may become slow and depends on RAM. Sitting infront of your desktop or laptop everybody wants results immediately. Buts moors law has been defeated by the slowness of the available software where as hardware capability and innovation is going strong. You need to have a well thought design model of your schema then Qlikview will do wonders for you. This is the only product I found that has blurred the line between the Techy geek and the user.You don’t need any further layer of database schema because you can connect to the OLTP database or OLAP Data warehouse and get what you want.
July 12th, 2010 at 12:46 pm
How you put an OR condition when filtering attributes from two different dimension in QV?
DimA.ColumnA = ‘ValueA’ OR DimB.ColumnB = ‘ValueB’
this in OBIEE Answers can be done in less than a blink of an eye.
There’s not an obvious way to do this in QV as far as I’m concerned.
This is to me the biggest issue with QV (it is not design for that) and why you still need OBIEE to be able to fulfull reoporting requirements.
Other than that great reporting Tool.
To me QV is not a substitude of OBIEE.