ODBC

Loading data from ODBC data sources

You can use the Database connectors in the Qlik ODBC Connector Package that supports the most common ODBC sources. This lets you define the data source in Qlik Sense without the need to use the Microsoft Windows ODBC Data Source Administrator. To connect directly to a database through one of the Qlik-licensed ODBC drivers in the ODBC Connector Package, see the instructions for Database connectors on the Qlik Connectors help site.

For more information, see Qlik Connectors: Database.
You can install an ODBC driver for the DBMS in question, and create a data source DSN. This is described in this section.

You can access a DBMS (Database Management System) via ODBC with Qlik Sense:

The Create new connection (ODBC) dialog displays the User DSN connections that have been configured. When you are using the Qlik Sense Desktop, the list of DSN connections displays the ODBC drivers included in the ODBC Connector Package. They are identified by the "Qlik-" attached to the name (for example, Qlik-db2). These drivers cannot be used to create a new ODBC connection. They are used exclusively by the database connectors in the ODBC Connector Package. The ODBC drivers from the ODBC Connector Package are not displayed when you are using Qlik Sense in a server environment.

The alternative is to export data from the database into a file that is readable to Qlik Sense.

Normally, some ODBC drivers are installed with Microsoft Windows. Additional drivers can be bought from software retailers, found on the Internet or delivered from the DBMS manufacturer. Some drivers are redistributed freely.

In a server environment, the Microsoft Access Database driver has limitations. To avoid issues, use SQL Server Express Edition.
Microsoft Access Database Engine 2016 Redistributable.

The ODBC interface described here is the interface on the client computer. If the plan is to use ODBC to access a multi-user relational database on a network server, additional DBMS software that allows a client to access the database on the server might be needed. Contact the DBMS supplier for more information on the software needed.

ODBC data connection settings

ODBC data connection settings
UI item	Description
User DSN System DSN	Select which type of DSN to connect to. For User DSN sources you need to specify if a 32-bit driver is used with Use 32-bit connection. System DSN connections can be filtered according to 32-bit or 64-bit.
Single Sign-On	You can enable Single Sign-On (SSO) when connecting to SAP HANA data sources. For more information, see Configuring SAP HANA for SAML Single Sign-On with Qlik Sense. If this option is not selected, Engine service user credentials are used, unless you specify credentials in Username and Password. If this option is selected, Engine service user or Username / Password credentials are used to do a Windows logon, followed by a subsequent logon to SAML (SAP HANA) using current user credentials.
Username	User name to connect with, if required by the data source. Leave this field empty if you want to use Engine service user credentials, or if the data source does not require credentials.
Password	Password to connect with, if required by the data source. Leave this field empty if you want to use Engine service user credentials, or if the data source does not require credentials.
Name	Name of the data connection.

Adding ODBC drivers

An ODBC driver for your DBMS (DataBase Management System) must be installed for Qlik Sense to be able to access your database. Please refer to the documentation for the DBMS that you are using for further details.

An ODBC driver for your DBMS must be installed for Qlik Sense to be able to access your database. This is external software. Therefore the instructions below may not match the software of all vendors. For details, refer to the documentation for the DBMS you are using.

Do the following:

Double-click the Administrative Tools icon in the Control Panel.
Double-click the Data Sources (ODBC) icon.

The ODBC Data Source Administrator dialog appears.
Select the database to use with Qlik Sense.
Select the Drivers tab in the Data Sources dialog.

In the Drivers tab you can see a list of installed ODBC drivers. If your DBMS is not listed you must install a driver for it. Run the install program delivered with the ODBC driver, for example the Microsoft ODBC install program.

64-bit and 32-bit versions of ODBC configuration

A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open DataBase Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):

The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWOW64 folder.
The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

Creating ODBC data sources

An ODBC data source must be created for the database you want to access. This can be done during the ODBC installation or at a later stage.

Before you start creating data sources, a decision must be made whether the data sources should be User DSN or System DSN (recommended). You can only reach user data sources with the correct user credentials. On a server installation, typically you need to create system data sources to be able to share the data sources with other users.

Do the following:

Open Odbcad32.exe.
Go to the tab System DSN to create a system data source.
Click Add.

The Create New Data Source dialog appears, showing a list of the ODBC drivers installed.
If the correct ODBC driver is listed, select it and click Finish.

A dialog specific to the selected database driver appears.
Select Microsoft Access Driver (*.mdb, *.accdb) and click Finish.

If you cannot find this driver in the list you can download it from Microsoft’s downloads website and install it.

Name the data source and set the necessary parameters.
Name the data source Scripting tutorial ODBC.
Under Database:, click Select....
Under Directories, navigate to the location of your Sales.accdb file (a tutorial example file).
When the file Sales.accdb is visible in the text box on the left, click on it to make it the database name.
Click OK three times to close all the dialogs.
Click OK.

Best practices when using ODBC data connections

Moving apps with ODBC data connections

If you move an app between Qlik Sense sites/Qlik Sense Desktop installations, data connections are included. If the app contains ODBC data connections, you need to make sure that the related ODBC data sources exist on the new deployment as well. The ODBC data sources need to be named and configured identically, and point to the same databases or files.

Security aspects when connecting to file based ODBC data connections

ODBC data connections using file based drivers will expose the path to the connected data file in the connection string. The path can be exposed when the connection is edited, in the data selection dialog, or in certain SQL queries.

If this is a concern, it is recommended to connect to the data file using a folder data connection if it is possible.

Stopping preview of large datasets in tables

If you have large data sets and you do not want to see a data preview while adding ODBC data sources to Data manager or Data load editor, hold down the Shift key while selecting your ODBC data connection.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here