To all of my regular readers, I apologize for the lack of activity on this blog the last few months. With moving, new jobs, and and a new city I haven’t quite found the time to be doing writing, but hopefully now as things quiet down I can get back into the groove.
The very first BI tool I used way back in the day was Qlik Desktop. It was an old edition that the company I worked for just had laying around and that the IT department didn’t want to use anymore. After I’d installed it and started getting used to the tool, I realized that I needed to remodel the data that I was connecting to into more of a star schema and thus – my journey into data warehousing and BI started. So when I heard last week that Qlik had released their next-gen product, called Qlik Sense, I was eager to go try it out and get a feel for the tool that’s been marketed as a complete makeover of Qlik.
Qlik Sense is Qlik’s new tool for self-service analytics. The really great thing about Qlik is that unlike some of the other vendors, the full feature desktop version is free. That’s right, no cash outlay required. Qlik charges for the server version, but if you just want to download and add a feature rich application to your BI deployment, it’s easy and straight-forward to do. That being said, how good is the app? First, let’s go download the tool, located on Qlik’s site. Once you’ve downloaded it and run the installer, you’ll enter into the Qlik Sense “hub”, which is designed to be the central location for your work:
Click on the “Create a New app” link, and we’ll give it a name. I’m just going to call mine “MyQlikSenseTest”, and click Create. After the “successful” prompt, click “Open App”. Once you enter into the app, you’re given a choice as to whether you’ll be loading data from a flat file or from a database.
As a database professional, I find this…annoying. I need to go to two different places if I’m trying to join data between a flat data file or from a database. However, from a self-service user perspective this is probably a good feature as I don’t have to know the ins and outs of where to go in order to get to data readily and easily. So I think I need to think on this some more…
I’m going to click on “Data Load Editor” as I’ll be bringing data in from the Adventure Works DW database I have installed on the machine. On the main page for the data load editor, over on the right hand side we want to add the connection to import the data.
Click “Create New Connection”, and select the connection type you’d like. Since SQL Server is OLE DB natively, I’ll be using that for this test.
Next you want to input all of the connection string information. For anyone running SQL Server 2012, make sure you select “SQL Server Native Client 11.0” instead of the default “Microsoft OLE DB Provider for SQL Server”. My completed screen looks like the below:
Click Save, and your connection will appear over on the right hand side of the screen:
The middle icon allows you to select the data that you want. If we click it, we’ll get a data exploration tab that can be used to explore and view the data prior to importing it. I’m going to select where the owner is dbo, and get a list of all of the tables. I do wish that it would default to that, I can see trying to explain what dbo is to users and getting them to click it would/will? be a pain. I do really like the filter table feature, that’s nice.
Let’s select FactInternetSales, DimCustomer, DimDate, DimSalesTerritory, DimProduct, DimProductCategory, and DimProductSubCategory, and click “Insert Script”. In the script pane in the middle, find the section loading the factinternetsales tables and find the OrderDateKey column. Change it from OrderDateKey to just DateKey and in the Select statement below, add an alias for the OrderDateKey as DateKey so the load will function. One nice thing is that if you click on a table name, you can go through and select just the columns you want to use for your analysis. This is a nice feature, something very similar to what you can do in Power Pivot. Up at the top right is a “Load Data” icon. Clicking it will import all of the data into the interface. Now we have some data we can work with. Once you load it, switch the view up in the top left of the screen by clicking on the compass icon and selecting data model viewer. There should be a relationship between the fact table and all of the related tables.
One thing I can find an issue with on this page is that you can’t define your own relationships, you have to do it in the loading layer. That’s why we had to change “OrderDateKey” to “DateKey” in the load script. This means that if you have a typical role-playing scenario with dates (order date, ship date, due date) you’d have to bring the date table over 3 times and rename it to each of these columns in order to get the engine to do the mapping. When you get to year over year calculations and other time intelligence functions, this could become very time consuming. I don’t know all of the internals of Qlik, but I do wonder how this affects the engine’s ability to handle data and puts more pressure on it unnecessarily. Once you’ve confirmed your relationship, click on the Compass again and go to the App Overview page. This is where you create your dashboard sheets, Click on “create new sheet”, and give it a title. Click on the icon to the left and you’ll enter the sheet design page. Click the pencil icon up at the top of the page, and you’ll have an empty page you can use to start designing:
Starting with a simple bar chart, it’s just a drag and drop from the icon into the main part of the page. After you drag it over, the chart asks you to add a dimension and a measure. We’ll select sales territory country as our dimension and sales amount as the measure, resulting in the following screen:
One thing I love about this is that it’s so EASY! It’s so convenient to just click the AddDimension link and then click the file you want. Adding a line chart, we can apply the same premise, by clicking the calendaryear dimension attribute and selecting salesamount again as the measure. My final desktop screenshot looks like the below:
When you’re done, click done up at the top right of the page, where the edit icon used to be. With Qlik, if you want to publish it you have to have Qlik server, which requires a license.
In summary, importing data doesn’t seem to have changed much from the old Qlik Desktop in terms of syntax, but it has a nicer and fancier interface which should appeal to the masses. In my limited review of the product, it looks nice and I can see why Qlik is proud of it. I do like it as a tool and can see where companies can benefit from it. My personal opinion is that it brings Qlik up to speed with the Tableau/Power View contingent of the world, but I didn’t see anything truly groundbreaking and revolutionary in the product. Another BI tool into an increasingly saturated market. A major selling point that is awesome about this product though is that the desktop edition is free, you’re just paying for the server, unlike the Tableau’s of the world which charge you for both the desktop and server versions.