This chapter gives basic information on how to connect to various database 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.
In this chapter, we will take a step past Excel and other static file extractions and explore connecting QlikView to a database. 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 via the QlikView Script Editor dialog. In addition to exploring database connections, we will also briefly discuss supported databases as well as exciting connectors that will help you connect to other data providers such as SalesForce, LinkedIn, Twitter, SAP, and Facebook using custom connectors.
As in 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 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 ODBC 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 more widely used ODBC drivers available. Though both 32-bit and 64-bit ODBC drivers can be used with QlikView, when installing a new ODBC 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 you 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 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 Administrator 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 at all, and you must visit the RDBMS vendor download site in order to install the necessary driver.
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.
The 32-bit version of the
Odbcad32.exefile is located at
The 64-bit version of the
Odbcad64.exefile is located at
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.
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 doing 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 is 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 into 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.
QlikView supports most common databases, and any that use 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 but is not covered in this book.
QlikTech also offers connectors to a number of Software as a Service (SaaS) packages, such as J.D. Edwards, SAP, and SalesForce (additional licensing fees apply). A connector to Informatica (ETL) is also available for purchase. Third-party connectors to LinkedIn, Facebook, Twitter, and others are available for purchase as well.
In this chapter, we have discussed database connectivity driver installation, ODBC and OLE DB configuration, and connecting to databases from QlikView. We have also briefly touched on supported databases and other supported connectors to hosted data such as SalesForce, SAP, and LinkedIn. In the next chapter, we will cover the fundamentals of creating QlikView scripts.