QlikView Scripting

4.4 (5 reviews total)
By Matt Floyd
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Connecting to ODBC/OLE DB Data Sources

About this book

QlikView is a powerful business intelligence and data discovery platform that allows people to quickly develop relevant data visualization applications for business users. The relative ease of QlikView development—including backend scripting—allows applications to be developed rapidly, and allows for more collaboration in application development for business users.

A comprehensive guide that offers QlikView developers a rich discussion of scripting topics, from basic to advanced concepts, features, and functions in a compact mini-book format. This book allows developers to quickly gain confidence in understanding and expanding their QlikView scripting knowledge, and serves as a springboard for even more advanced topics in QlikView scripting.

The book starts off by covering basic topics such as connecting to data sources, scripting, dealing with load statements, data transformations, and the concepts of the basic data model. It then dives into advanced concepts such as advanced scripting and data model optimization, the creation and use of QlikView datafiles, debugging, and essential functions and features. It also provides layout tips for developers. Qlikview Scripting is a great overview and reference guide for beginner to intermediate Qlikview developers.

Publication date:
November 2013
Publisher
Packt
Pages
138
ISBN
9781782171669

 

Chapter 1. Connecting to ODBC/OLE DB Data Sources

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.

 

Installing drivers


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.

 

Configuring ODBC


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


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.

 

Supported databases


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.

 

Summary


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.

About the Author

  • Matt Floyd

    Matt Floyd has worked in the software industry since 2000 and has held career roles from project management to technical writing and business intelligence analysis. His career has spanned many industries, including environment, healthcare, pharmaceuticals, and insurance.

    Matt's hands-on experience with Tableau started in 2008 after evaluating alternatives to reporting and analytical software used by his clients. Since then, he has been a technical writer, implementation engineer, consultant, developer, and analyst in BI projects. His passion for Tableau stems from his fascination of discovery through data and the art, science, and power of data visualization. He is currently interested in text mining and the combination of that data with powerful visualizations that tell fascinating stories. He and his family live in metro Atlanta, and when not stuck in traffic, he sometimes offers musings on his blog covering various visualization topics at http://floydmatt.com/.

    Browse publications by this author

Latest Reviews

(5 reviews total)
It is clear that team has taken efforts before publishing this book. Clearly seen going thru pages. Well Done
I can not download the files, because I have a personal edition of QlikView.
I like your book, but I disappointed in Qlik prodcuts.
Book Title
Access this book, plus 7,500 other titles for FREE
Access now