The data within an organization is usually stored in relational databases and data files. Extracting data is the first step toward creating a data model. This section demonstrates the steps to extract data from an MS Access database and a delimited (.CSV
) file. The procedure to extract data from other relational databases is the same as the process for extracting data from MS Access.
The dataset that we will use is available publicly and covers information about routes and fares for various transport systems in Hong Kong. The original data files have been downloaded from the https://data.gov.hk/ website. This dataset can also be obtained from the Packt Publishing website.
The data connections in the Qlik Sense Data load editor
save shortcuts leading to commonly used data sources, such as databases and data files. The following types of connections exist in Qlik Sense:
- ODBC database connection
- OLE DB database connection
- Folder connection
- Web file connection
- Qlik Essbase Connector
- Qlik ODBC Connector Package
- Qlik REST Connector
- Qlik Salesforce Connector
- Qlik GeoAnalytics Connector
- Web Storage Provider Connector
This recipe deals with the ODBC, OLE DB, and Folder connections. The web file connection and REST Connector will be dealt with in a separate recipe.
For the following connections, here is a short description, but we don't have recipes for them in this book:
- Qlik Essbase Connector allows data extraction from Hyperion Essbase cubes
- Qlik ODBC Connector Package allows data-extraction from several data sources, such as Google Big Query, Amazon Redshift, Hive, Cloudera Impala, IBM DB2, MS SQL Server, My SQL Enterprise Edition, Oracle, PostgreSQL, Sybase ASE, and Teradata
- Qlik Salesforce Connector allows data extraction from Salesforce reports with SOAP or the Bulk API
- Qlik GeoAnalytics Connector is a new service to make geo-analytics calculations, such as calculating the distance between points, clusters, and merging shapes
- Web Storage Provider Connector allows a connection to storage services, such as DropBox, to retrieve data from files, such as XlSX or CSV
With the exception of Qlik GeoAnalytics Connector, all connectors are free to use. GeoAnalytics Connector requires the purchase or subscription of a separate license from Qlik.
The dataset required for this recipe that is downloaded from the Packt Publishing website (https://www.packtpub.com/big-data-and-business-intelligence/qlik-sense-cookbook-second-edition) comes in a zipped folder called QlikSenseData
. Extract all the files from this zipped folder and save them on your hard drive at the desired location.
If you are connecting to the database using Open Database Connectivity (ODBC):
- Install the relevant ODBC drivers on your system.
Note
For the sake of this exercise, we need the MS Access drivers. The system DSN connection can be set up through the ODBC administrator under Administrative Tools
in Control Panel
.
- While setting up the ODBC connection, select the
ROUTE_BUS.mdb
file as the data source from theQlikSenseData
folder. - Name the ODBC DSN connection as
HongKong Buses
. - Create a new Qlik Sense application and open the
Data load editor.
- Click on the
Create New Connection
and selectODBC.
- Select
HongKong Buses
underSystem DSN.
- Name the data connection as
Qlik Sense CookBook ODBC
. - The following screenshot shows the details we enter in the
Create new connection (ODBC)
window:
If you are connecting to the database using OLE DB connectivity, we can directly set this up through the editor:
- Open the
Data load editor
in Qlik Sense. - Click on the
Create new connection
and selectOLE DB
. - Select the
Microsoft Jet 4.0 OLE DB Provider (32-bit)
driver from the provider drop-down list. - Insert the
Data Source
file path, which, in our case, will be the path for theROUTE_BUS.mdb
file in theQlikSenseData
folder. - Name the data connection as
QlikSense CookBook OLE DB
. - The following screenshot shows the details we enter in the
Create new connection (OLE DB)
window:
If you are extracting the data from a data file, such as .CSV
, perform the following steps:
- Open the
Data load editor
in Qlik Sense. - Click on
Create new connection
and selectFolder
. - Select the location of the
QlikSenseData
folder, which contains our data files. Alternatively, you can enter the path of the source folder directly underPath
. - Name the data connection as
Qlik Sense CookBook Data
.
- The following screenshot shows the details we enter in the
Create new connection (folder)
window:
- Once the connections are created in the Qlik Sense library, they will be seen
as a list under
Data connections
in theData load editor
, as shown in the following screenshot:
If you are working with an ODBC or an OLE DB data connection, follow these steps:
- Insert the relevant data connection string to the script by clicking on
Insert connection string
, as shown in the following screenshot:
- Click on Select data under
Data connections
to view and extract data from theROUTE
table in the MS Access database, as shown:
- Click on
Insert script
in thePreview
window. This will insert the connection string as well as load the statement to the script. Make sure that you delete the duplicateLIB CONNECT TO 'Qlik Sense CookBook ODBC';
statement from your script. - Load the data in your application by clicking on the button.
Keep the Close when successfully finished
option checked in the data load progress window. If the data is loaded successfully, the window automatically closes or else the error encountered is highlighted.
On a similar note, in order to test the Qlik Sense data files:
- Click on the
Select data
option under theQlik Sense CookBook Data
connection. - Select the
ROUTE_GMB.csv
file from theQlikSenseData
folder and load it in the application. - The preview of the
ROUTE_GMB.csv
table will look like the following screenshot. Make sure that you selectEmbedded field names
underField names
. Note that theDelimiter
, in this case, is automatically set toComma
:
- Insert the script and then save and load it.
The LIB CONNECT TO
statement connects to a database using a stored data connection from the Qlik Sense library, thus acting as a bridge between our application and the data source.