Get started with QlikView

QlikView is a data analysis tool from QlikTech. They call it Business Discovery, or self-service BI.QlikviewLogo

One of the strength of QlikView is that it is quick to set up and get started with QlikView in a proof of concept.

Here I’ll show how to load data from AdventureWorks2012 into QlikView and how to create a simple interactive chart in QlikView.

Download free version of QlikView

There is a free downloadable version of QlikView here (you’ll have to register).

Note that the free version means your QlikView application only will work on your computer, you won’t be able to share it with others:

QlikView Personal Edition Limitations
QlikView Personal Edition Limitations

Those are the only limitations in the personal edition however. There are no limitations in functionality, neither is it a time limited demo version. A Personal Edition FAQ can be found here.

Create a new QlikView application

After downloading and installing, open QlikView and select “File” -> “New” to see the “Select data source” window. That dialog is all about reading data from Excel files however. Since we are going to read from SQL Server, just click “Cancel”.

Instead select “File” -> “Edit Script”:

QlikView Edit Script
QlikView Edit Script

Create a connection from QlikView to SQL Server

First, we need a connection to our AdventureWorks2012 database. In the lower part of the script window, select “OLE DB”, and click “Connect”:

QlikView OLEDB Connect
QlikView OLEDB Connect

This will open the standard “Data Link Properties” window. Select “Microsoft OLE DB Provider for SQL Server” as provider, and click “Next”. Enter your server name, select NT Integrated security or specific user name, and lastly select the AdventureWorks2012 database:

QlikView OLEDB Data Link Properties
QlikView OLEDB Data Link Properties

When you click “OK”, there will be a connection row created in the script, starting with “OLEDB CONNECT TO…”.

SQL statements in QlikView script to load data

If you prefer, you can use a wizard to select the views, tables and columns to include in the load script. If so, click the “Select” button in the lower part of the script window. However I’m simply going to create my sql statements in SSMS and then paste them into the script window. This way I will have code completion and other features of SSMS at disposal, which you won’t if you only use the QlikView script window.

One thing to note early on is that QlikView uses a natural join method to assume a connection/relation between tables. So if you have a fact table with a foreign key named ProductId, and a dimension table with the primary key column ProductId and ProductDesc, QlikView will automatically create a connection between the two tables. So in your analysis of the metrics in the fact table you’ll also have access to ProductDesc. Natural joins simply uses column names for “joins” which gives you control over this behaviour, you can always rename columns when needed. But when the script uses more tables, this is something you’ll have to pay attention to, since the model in QlikView can become complex and unpredictable when column names are repeated frequently. Pay special attention to generic column names such as “Name”, which can create… interesting results.

Basically, normal SQL is fine in the script window. There are a couple of differences however. Each “SELECT” statement must start with the keyword “SQL”. So “SELECT” simply becomes “SQL SELECT”. The other difference is that the “;” sign is mandatory as a separator between sql statements.

For demonstration purposes, I have created a really simple sql script that selects only some parts of the AdventureWorks2012 database. Basically, a fact table and three dimension tables. The sql statements that you can paste into the QlikView script window looks like this (don’t paste over the connection string):

SQL SELECT
	OrderDate
	,Status
	,OnlineOrderFlag
	,SalesPersonID
	,TerritoryID
	,ShipMethodID
	,SubTotal
	,TaxAmt
	,Freight
	,TotalDue
 FROM
	Sales.SalesOrderHeader;

SQL SELECT
	BusinessEntityID AS SalesPersonID
	,FirstName + ' ' + LastName AS SalesPersonName
FROM
	Person.Person;

SQL SELECT
	TerritoryID
	,[Group] as TerritoryGroup
	,[Name] as TerritoryName
FROM
	Sales.SalesTerritory;

SQL SELECT
	ShipMethodID
	,[Name] as ShipMethodName
FROM
	Purchasing.ShipMethod;

That’s all we need to load the QlikView application with data. Hit the “Reload” button in the upper left corner of the window. You will be prompted to save the application, do so at your preferred location. QlikView will now load the data from the sql statements and store the data locally in the application file. (There is not much data, so the load will probably be very fast.)

As noted, all the data is saved locally in the QlikView application file. That means once you have loaded the application, you don’t need the connection to the database anymore, except for refreshing the data. This also means that you need to apply some sort of security to the QlikView file (and in the QlikView application). There is also a client server version of QlikView that I strongly recommend.

The QlikView Table Viewer

When QlikView has finished loading the data, it will have created a data model over the loaded data. Click “File” -> “Table Viewer”:

QlikView Table Viewer
QlikView Table Viewer

Here you can see how QlikView creates connections between columns, and spot any problems with the model such as loops or incorrect joins that I wrote about earlier.

Analysing data in QlikView with charts

The next step is to start analysing the data with charts or other visualizations. This can also act as a natural boundary between responsibilities. A developer (you) makes sure the data is loaded correctly into the QlikView application. Someone else, probably not from the IT department but rather a business analyst, creates the data visualizations and reports.

After QlikView has finished loading the data, a “Sheet Properties” window will show. Cancel that window for now.

We now have an empty QlikView application. The data is loaded but there is no visualisations of it yet. Right click anywhere on the empty sheet and select “New Sheet Object” -> “Chart”. We are going to create a line chart showing sales over time. In the “General” window that appears, type “Sales Over Time” as window title, and select “Line Chart” as “Chart Type”:

QlikView New Sheet Object General
QlikView New Sheet Object General

Click “Next”. In the “Dimensions” window, we are going to use Order Date but we will summarize per month to get less detail in the chart. Click on the “Add Calculated Dimension” button. In the expression window that opens, type “=Year(OrderDate) & Month(OrderDate)”:

QlikView New Sheet Object Add Calculated Dimension
QlikView New Sheet Object Add Calculated Dimension

Click “OK”. Our newly created dimension will need to have a name (otherwise the calculation expression will show up as the name in charts). Type “OrderMonth” in the “Label” textbox:

QlikView New Sheet Object Dimension Label
QlikView New Sheet Object Dimension Label

Click “Next”. This will bring up the “Edit Expression” window. Here we will set what is measured, in this case it is the sum of sales. Type Sum(SubTotal) in the expression window:

QlikView New Sheet Object Edit Expression
QlikView New Sheet Object Edit Expression

Click “OK”, and “Next” on the “Expressions” window. Click “Next” on the “Sort”, “Style” and “Presentation” windows.

You should now be on the “Axis” window. Select the sloping text orientation for the “Primary Dimension Labels”:

QlikView New Sheet Object Axes
QlikView New Sheet Object Axes

As you can see there are a lot of settings that we have skipped, but in this demonstration, click “Finish”.

You should now have a chart that looks something like this:

QlikView New Sheet Object Line Chart
QlikView New Sheet Object Line Chart

If you like you can drag in the corners of the chart to expand the size.

Click and drag anywhere in the chart to zoom in. What happens when you do that is that you filter the data along the dimension (OrderMonth in this case). Anytime you have created a filter, use the buttons in the menu bar to go back or clear all filters:

QlikView Clear And Back Buttons
QlikView Clear And Back Buttons

Other dimension exploring options in QlikView

Next, we are going to add a so called “Multi Box” which is used to further enhance our analysis. Right click on the sheet add select “New Sheet Object” -> “Multi Box”. This opens the “New multibox” window. Add TerritoryGroup, TerritoryName and SalesPersonName:

QlikView New Multibox
QlikView New Multibox

Click “OK”. If you select North America in the multibox, you’ll see that the chart gets filtered on North America only. The other fields in the multibox are filtered as well. If you check the values in TerritoryName, you’ll see that the valid values for North America have a white background but the not valid values are greyed out (Australia and so forth). Same thing with the SalesPersonName. Only sales persons active in North America have the white background:

QlikView Multibox Filtered
QlikView Multibox Filtered

As you can see, by using interactive charts and other dimension exploring tools, QlikView can be a very powerful tool in a “Business Discovery” process.

This is just a very short introduction aimed to cover the basics, there are tons of other tools and functions in QlikView not mentioned here. But since you now have the QlikView Personal Edition, you can explore the other features on your own 🙂

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

Leave a Reply