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.