Qlik Sense vs Power BI – Importing Data

A review of the available data sources for and Qlik Sense vs Power BI as well as a comparison of the import processes across a number of data samples.

Available data sources

Both Qlik Sense and PowerBI allow connection to a growing number of data sources.

The specific list for Qlik Sense is here http://www.qlik.com/us/products/data-sources

The list for PowerBI is here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-sources/

I should mention that just because the particular data source you have is not listed doesn’t mean you are excluded from using either tool. There may just be an intermediate step before you can import the data or the data you have needs manipulation within the tool.

For the vast majority of data, there will be a solution out there that exists in both tools, the complexity in each, however, will vary.

Common data sources:  SQL Server

SQL Server is one of the most common sources of data that we will encounter in the workplace, so it is the first that I used to test both tools.

In this case, I used the World Wide Importers database in SQL Server 2016.  I first created views in SQL server of the data I wanted and imported the data in both PowerBI and Qlik Sense.

In both cases this was very easy:

Here is loading a customer view from SQL server in PowerBI:

And now the same process in Qlik Sense with the same customer view from SQL server:

 

 

 

Total time to load 1 table in each tool is roughly 2 minutes.  Time will obviously vary depending on the location of the server and the size and number of tables.

Key differences:

If you are pulling a table or a view from a database server, there is little difference in this process.  If you require calculated columns or any data shaping the differences start to appear.  I’m going to talk about this in the next section further.

The difference once the data loads is the ability to preview the table data.  In PowerBI, you can preview the entire data set in two different screens: the data screen and the edit query screen.  In both cases, you can see every single field, and in the data screen, you can scroll through every row.

The data preview in Qlik Sense is very much a preview as seen above which is a truncated version showing only 3 of the fields.  There are other ways to see the raw data, but they are not as simple as with Power BI especially if you have used a custom script to bring in the data.

Do you know the 7 components of Power BI?  Take a read and test your knowledge. 

CSV, text files and Web tables:

I tested both tools across the following data:

A list of countries and their populations from Wikipedia https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population

 

Key Differences:

PowerBI recognises all fields as text or type “Any”.  All were easy to convert except that % of the world population which required the % symbol be trimmed off before conversion.  The Note – shown above by the green arrow is ignored in Power BI.  PowerBI also by default treated the headers as data.  The headers are then promoted by the click of 1 button.

Qlik is remarkably savvy at recognising data types during import.Click To Tweet

Qlik is remarkably savvy at recognising data types during import.  It recognised all correctly without me having to override them.  The Note shown with the green arrow is included as text.  Qlik automatically treated the headers as headers, which is easily overiden if incorrect.

A standard set of comma-delimited data (CSV format)

The raw data in notepad looks like this:

In Excel, the data looks like this:

Key Differences:

No real difference here because the data is clean and does not need shaping or appending.   If however, we had several CSV files that had the same structure that we put in a folder, PowerBI can automate this while we would need to write a script in QlikSense.

Nasty Data:

What is my definition of Nasty data?  It’s data that has one of the following:

• Inconsistent columns
• Repeating headers
• Multiple field headers
• Total lines on each page or for each group of data
• Field headers that don’t line up with the data
• No delimiters and inconsistent widths between columns (not fixed width)

Often we see this kind of data coming out of reporting tools like crystal reports.  ERP systems love to generate this type of report.

Below is an example of a payroll report (a very fake one, I might add) that is consistent with the reports or outputs that I call “nasty data”.  Below are the first two pages of the 24-page payroll report for Fake Company Party.

In this example, the report is exported to Excel / CSV format and has the following issues:

1. The report is 34 Columns wide, much of which has no data.
2. There are repeating report headers
3. It has repeating field headers that don’t line up with the data
4. Different data shows up on different rows.
5. Much of the data also contains text labels in the same field example “Date: 31/07/2015”.
6. It has sub total rows
7. It has a total for employee section.

All of which makes this data difficult to put into a table for data extraction.

Can both Power BI and Qlik Sense handle the import of this data?

In my opinion, Yes.

Would you use both: Highly unlikely.

This is where the Power Query advantage comes in for Power BI.  Power BI utilises this same tool originally developed as an add-in for Excel to do data shaping which is renamed “Query Editor” in PowerBI desktop.

Nasty data is where the Power Query advantage shows up in Microsoft Power BIClick To Tweet

Using the PowerBI Query editor, I was able to turn the above report into the below table in approximately 15 minutes. In this case, there was no requirement to write a single line of script.  You can also do this work in Excel first using PowerQuery (Called Get and Transform in Excel 2016) and then copy the script from Excel (which it created for you) and Paste it into the Query Editor in PowerBI.

 

This power query shaping process also has the advantage that if you had multiple files with the same structure, you could put them in a directory and have PowerQuery put them all together with little effort.

I need to spend the time to see how long it takes me to write the script in Qlik Sense to do the same thing.  In my opinion, a smart user wouldn’t do this in Qlik Sense. The user would save considerable time by using PowerQuery in Excel to transform the data before importing it into Qlik Sense.

Data import / Acquisition Summary

Both tools are capable of importing data from multiple sources.  Qlik is driven from scripts that you write while PowerBI has a Graphical User Interface which simplifies the process.

For database data, there is no meaningful difference between the tools.

When dealing with more raw data and ad-hoc / one off requirements where the data might not be in an ideal state to start, or large groups of files such as CSV’s in a folder, PowerBI has a massive advantage with the query editor over Qlik’s scripting approach.

Viewing the raw data in PowerBI is easier.

Does one tool have a significant advantage:

For ad-hoc analysis, Power BI has advantages.

If you are working with system based data such as from an ERP both tools are on an even playing field.

Do you agree?  What are the gaps you are currently facing importing data?

*** The second post in this series which looks at a comparison of building data models in both tools is now up.

Part 2: Data Models.