Qlik Sense: Loading Multiple Data Sources into a single application
One of the key benefits of the Qlik platform is the ability to load and associate data from a variety of data sources. In fact, this is a feature that truly differentiates Qlik from most other BI platforms. In this post I will load data into Qlik Sense from several different sources and then associate the relevant data to complete a usable data model. I will use some “non-traditional” data sources to keep it interesting. In addition, I will utilize the Data Manager to load in most of the data.
Do you want to see this done in real time? Register for our live webinar below.
Malaria – A Killer Worthy of Our Analysis
Our data will focus on the current state of malaria cases and deaths by country. Malaria is a disease passed to people by mosquitos. In most developed countries malaria has been completely eradicated, but in primarily African countries, malaria infects 200 million people and kills about 400,000 each year, mostly children. Malaria is completely treatable, but the areas where it spreads easily, lack both the funds and proximity to proper medical care. It is a topic worthy of analysis. CDC
Data Source Variety is the Spice of Life
I compiled some data from several mediums to create our analysis story.
- Qlik Data Market: Economy
- Web File (HTML)
- Microsoft Excel
- Qlik Web Connector: Twitter Connector
Data Source 1: Country GDP and Population – Qlik Sense Data Market
I wanted to start with some baseline specs for GDP and Population along with the list of countries and their shape information (so we can create maps). You can easily get this data by tapping into the Qlik Data Market. In addition to the analytics tools we know and love, Qlik also is in the business of curating and selling data to utilize within your apps. There are several price levels that are primarily determined by the level of detail that you require. I am getting basic data by country so, I will be using my favorite level…free. There are several categories of data from currency exchange rates to weather. Today, we are using data from the Economy category. There are 2 ways to load data into Qlik Sense. Using “Add data” will take you to a simple visual tool called the Data Manager. The “Data load editor” will take you to the text-based script editor. I was “raised” on the normal script editor, but just for fun, let’s try the Qlik Data Manager. Selecting Qlik Data Market will give us our package options. I am opting for the free version in this case.
Note: If you are on a server, the Data Market must be “turned on” in the QMC by accepting the license agreement and/or licensing the package you have purchased (if not using the free version). In the QMC go to License and Tokens > Qlik DataMarket.I will start with my country list. Select Economy > Selected development indicators. In the “Select data to load” screen, I checked the Country box, and then went down to year and selected Most recent. Then select Indicator. I chose to look at total GDP, GDP per capita and total population. Now select Prepare data on the bottom of the screen. Now you will be presented with a visual preview of the data that will be loaded into Qlik Sense. Selecting one of the circles will display a preview of the actual data. Qlik Sense has decided that the data will be broken into two tables. Clicking on the “connecting rod” will show you how the two tables will be related. The data contains an alphanumeric ID field that joins the metric data to the country names and area. Press load data in the upper-right to populate the data model. Go to the data model viewer to see the tables and preview the data. It looks like we have 214 countries, their areas, GDP and population…A great start.
Data Source 2: Malaria Cases and Deaths by Country – Web Page Table
Our second data source will be a web page that has some tables in it. I found a site that contains current malaria statistics by country. We will use these tables in our Qlik Sense data model.
There is some added complication here because it appears the data is divided into a table for each continent. Altogether, there are 5 tables for us to suck in to Qlik Sense. So let’s go back to our Data manager. In the right side, selecting the plus will allow you to bring in a new source. This time, I will click on Connect my data and scroll down to Web file. Enter the URL from above and name the source and we can continue. The preview will select the first table it finds on the page. This data looks good. Because we need the rest of the continents I will also select the other tables up to number 5. If you select the 6th table, you can tell from the preview pane that it does not contain any data. Select Prepare data. Once you are in the visual preview, you might notice there is no connection between the new table and our existing Geographic information. You can highlight each table and figure out how they should be connected but it is probably easier to let Qlik Sense do it for you. Select the magician’s wand on the right side and “watch the magic happen”. Selecting the connecting rod between the two tables will reveal that Qlik Sense connected them on the Country field, which sounds fine to me. Select Load data to run the generated script.
Validate your data: Let’s create some objects
Generally, after I have loaded a few tables, it is time to do a little “sanity check”. Putting a few tables on the page will tell us if the data is associated correctly and any issues will become obvious. I created some master dimensions to do some basic math and then created a table to look how my countries are lining up. It became apparent that there was a minor issue. Most of the countries are fine but there are a few that seem to be repeated and don’t line up when looking at population that came from the Qlik Data Market and the malaria data that came from the web page. It looks like a few of the country names are not matching. And because this is the field that associate the tables together, we are seeing some inconsistency. For example, Congo and Democratic Republic of Congo (2 distinct nations) each have 2 rows in this table. We will need to solve this problem.
Data Source 3: Country Mapping Table – MS Excel
To solve this problem, I will just “rename” the countries that exhibit this problem. So I manually went through the list and created a 2-column table in Microsoft Excel.
We will use a concept called “mapping load” to get this into Qlik Sense. We will take the Malaria Data we loaded from the web page and if we find a match for the Country field in the data with the first column in the spreadsheet, we will replace that country name with what is in column two. To learn more about mapping load read this post: LivingQlik Roots: The Complete Guide to QlikView Mapping Load Now when it comes to some of the more complex scripting operations, the visual Data Manager simply won’t cut it. We will have to go to the Data load editor. And where should we write this script? You could add this script to the existing Auto-generated section, but I would suggest we do this in a completely separate section of the script. This is because if you choose to edit any script that was composed using the visual Data Manager, you will break the ability to go back to that method and make changes. Note:
Note:Editing the Auto-generated section of the script will break the ability to go back to the visual Data Manager to adjust that data. For this reason alone, I generally choose to bypass the Data Manager all together, performing all my data modelling in the Data load editor.
Data Source 4: #Malaria on Twitter – Qlik Web Connectors
I wanted to add a little more context to this dashboard. It might be good to see what people are saying about malaria on Twitter. This will add context and depth to our data. The Qlik Web Connectors are PREMIUM connectors. They are sold through a partner or directly from Qlik on a subscription basis. Because I work with a partner, I have access to this connector, but you might not be able to replicate this. So again, I can navigate to the Data load editor and create a new section of my script for this data source. The Qlik Web Connectors operate a little differently. There are no wizards in the Qlik Sense app to assist. Instead we will use a web page that comes with the Qlik Web Connector package that will help us authenticate with the data provider and create script which we can then copy and paste into our Qlik Sense application. Here is the completed data model. We will leave the Twitter data unassociated since it is related to the rest of the data only by virtue of being related to the word “Malaria”.
Final Thoughts: Analyzing Malaria – The Dashboard
We can now build out any dashboard objects we want. I built out a few pages just to prove the validity of the data model. You can get the entire qvf file here: Malaria-Analysis.qvf So the ability to bring several data sources into a single Qlik Sense application is one of the many reasons why I love the Qlik platform. I know of no other Business Intelligence tool that will allow you to bring in data from so many varied sources and combine them so easily to create a real analytical application. There is no need for me to load all the data into SQL and perform dramatic, time-consuming ETL. All the techniques are “in the box”. Very cool stuff.
Do you want to see this done in real time?
Visual Data Group will be hosting a webinar to go over this process live in real time for those that are interested in exploring this further. I will follow the spirit of this post, building this data model live, and then (not withstanding demo demons) do some Q&A. You can register for this event here:
Help LivingQlik Fight Malaria
Against Malaria Foundation
There is strong evidence that distributing mosquito nets reduces child mortality and overall malaria cases in the hardest hit regions. Against Malaria Foundation invests all your donation into the distribution of long-lasting insecticide-treated nets to the most heavily affected areas. GiveWell features AMF as one of their Top Rated Charities so you KNOW your donation has the greatest possible effect.
Please join LivingQlik and save some lives: