OLE DB/ ODBC Connection to Qlikview Data Sources

OLE DB/ ODBC connection to data sources in Qlikview

New users of QlikView will often create a new QlikView document (application or QVW (QlikView Worksheet)) by navigating to File | New from the QlikView toolbar. This command opens the Select data source wizard, where the user may browse to a locally stored Excel file and follow the wizard steps to extract that data into the new QVW.
The basic steps for connecting QlikView to a database are:

• driver installation of a supported database, configuring ODBC (if using an ODBC driver), and

• connecting it via the QlikView Script Editor dialog.

Learn how to use QlikView, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free QlikView Training Demo!

Installing drivers

As with any data extraction tool, it is necessary to have Object Linking and Embedding Database (OLE DB) or Open Database Connectivity (ODBC) installed on the computer or server in order to access a database. The type of connection is determined by the database to which you are connecting.

Microsoft Windows comes pre-installed with several OLE DB/ ODBC drivers. For other database connections, you should visit the appropriate database vendor site’s download section to get the latest ODBC connection driver for that database.

OLE DB drivers are most commonly used to connect to Microsoft Access databases, and this driver is usually installed with the Microsoft Windows operating system. Many OLE DB connectors are also available for other Relational Database Management Systems (RDBMS) such as PostgreSQL, DB2 and Firebird, among others. OLE DB is reported to be superior to an ODBC connection for QlikView applications (speed, connectivity, efficiency), so consider using OLE DB if possible. An easy way to check whether or not the computer already has the OLE DB connector installed is from within QlikView. In QlikView, navigate to File | Edit Script. In the Script Editor dialog’s Data tab, select OLE DB in the drop-down list and click Connect. The Data Link Properties dialog should appear if an OLE DB driver is installed. If a driver is not installed, you will receive an error message indicating that the OLE DB driver is not installed and a connection cannot be made to the database.

In practical use, most databases (except Microsoft Access) connect through the most widely used ODBC connection drivers available. Though both 32-bit and 64-bit ODBC drivers can be used with QlikView, when installing a new ODBC connection driver, select the proper type to work with your version of QlikView. If you are using the 32-bit QlikView application, install the 32-bit driver only. If you are using the 64-bit version of QlikView, you can use either the 32-bit or 64-bit ODBC driver. If the database you are connecting to is a 64-bit database (and you are connecting from a 64-bit QlikView version), be sure to use the 64-bit ODBC driver so that you can gain speed and efficiency from the 64-bit connection.

Check if your computer or server already has the desired driver installed by navigating to the Windows Control Panel (Start | Settings | Control Panel). In the Control Panel, open the Administrative Tools panel, then locate the Data Sources ( ODBC) icon, and find the ODBC driver in the system DSN tab. Click on Add in the ODBC Data Source Administration dialog box.

If you cannot find the Data Sources icon, search for ODBC in Control Panel to display the Administrative Tools window. If you can’t find the Administrative Tools window, it is likely that no OLE DB/ODBC drivers are installed yet, and you must visit the RDBMS vendor download site in order to install the necessary driver.

Frequently Asked QlikView Interview Questions & Answers

When you have installed the proper vendor ODBC driver on the computer or server that will be running QlikView queries, continue the setup by configuring the ODBC DSN information.

Configuring ODBC connection

Depending on what type of driver you are using (32 bit or 64 bit), navigate and open the correct administrative setup tool to configure your ODBC connection:

• The 32-bit version of the Odbcad32. exe file is located at %systemdrive% Windows System32

• The 64-bit version of the Odbcad64. exe file is located at %systemdrive% Windows SysWoW64

The ODBC Data Source Administrator dialog displays and allows you to choose the database to use with QlikView. To add a new connection, navigate to the System DSN tab in the ODBC Data Source Administrator dialog, select Add, then select the ODBC driver for the database to which you are connecting (remember, if your RDBMS is not listed, you must install a driver for it).

After selecting the desired ODBC driver, click on Finish. A dialog for the selected database driver is displayed that allows you to enter the name of your data source and other connection parameters. Click on the OK button when you finish entering the connection information, and the new database connection name is displayed in the System DSN tab.

Connecting to the database from QlikView

QlikView can connect to most of the popular databases like MySql, SQL Server, Oracle, Postgress etc. It can fetch data and table structures into QlikView environment and store the results in its memory for further analysis. The steps to connect to any of these databases involve creating an ODBC connection using a DSN and then using this DSN to fetch the data.

Related Page: Definition And Advantages Of QVDs In QlikView

Once the DSN information has been created, continue setting up the connection in QlikView, using the QlikView Script Editor. Open a QlikView document and access the Script Editor (by navigating to File | Edit Script, or by holding Ctrl + E). In the Script Editor dialog’s Data tab, select ODBC from the drop-down list and click on Connect (if the driver installed 64 bit, deselect the Force 32 bit checkbox). Select the database name in the Connect to Data Source dialog and click on OK. Enter a password for the data source if prompted. Once connected, notice that QlikView inserts a connect statement in the script, such as ODBC CONNECT TO [DATABASE NAME; DBQ =DBNAME];.

From this point, the database is connected to QlikView and you may now create SELECT statements to start building your script. If you are not connected to the database when moving forward, you will be prompted for the data source again. In the Script Editor window, press Enter once or twice to advance to the next line after the connect statement, and click on the Select button to select the database tables. Note that while you can connect to multiple databases in a QlikView document, only one database is connected at a time. Each successive connect statement disconnects the previous connection. You can also use the Disconnect command to explicitly terminate the most current database connection. In the next chapter, we will continue building the QlikView script.

Supported databases

QlikView supports the most common databases, and any database that uses ODBC or OLE DB connections, including the most common database systems: Oracle, MS Access, MS SQL Server, Teradata, PostgreSQL, MySQL, DB2, Sybase, Netezza, and Informix. Connectors are also available via the QlikView Expressor tool, which adds ETL functionality.

Related Page: Process Of Incremental Load In QVD QlikView

QlikTech also offers connectors to a number of Software as a Service (SaaS) packages, such as J.D.Edwards, SAP, and Sales Force (additional Licensing fees apply). A connector to Informatica (ETL) is also available for purchase. Third-party connectors to LinkedIn, Facebook, Twitter, and others are also available for purchase.

This concludes the article on a few aspects regarding how to connect to ODBC/ OLE DB Data Sources  using QlikView.