|Share this article:|
Connecting to Vertica 7.1.x with QlikView 11.2
This document discusses connecting to Vertica 7.1.x using QlikView Business Discovery 11.2.
QlikView Business Discovery is a business intelligence (BI) tool that, once connected to the Vertica Analytics Platform, allows you to access and analyze your data and create visualizations.
Before using QlikView with Vertica , download and install QlikView from the QlikView web site. Launch the executable and follow the prompts for installation.
A general note on Vertica ’s technology partner documents: Vertica creates ecosystem documents using one specific version of the partner product with one specific version of Vertica . In creating each document, Vertica makes connections from the partner product using one specific connection type. Versions used are noted within the documents. Other versions of the partner product may work with Vertica , but Vertica may not have tested other versions.
Before Connecting QlikView to Vertica
Before you use QlikView with Vertica :
- If you do not have the ODBC driver installed, download and install the driver. You can find the ODBC driver available for download on https://my.vertica.com/, on the Download tab. You can download and install the specific Vertica ODBC driver alone or an Vertica client package that includes the driver.
Note General information on driver and server compatibility appears in the Vertica product documentation. See Client Driver and Server Version Compatibility.
- Configure your ODBC data source.
- Disable the QlikView wizard. (You do not need the wizard because it works with Excel files only.
Note QlikView runs on the Windows OS.
Configuring an ODBC Data Source
Configure a data source by setting up a DSN. The examples in this section specify User DSN; alternatively, you can specify System DSN.)
- From your Windows desktop, open the ODBC Data Source Administrator.
- Under the User DSN tab, click Add to create a new data source.
- On the Create New Data Source dialog, locate and select the Vertica driver.
- Click Finish.
- In the Vertica ODBC Data Source Configuration dialog box, enter your specific data source information.
- Click Test Connection, and OK when the Connection Succeeded message appears. The User DSN tab now lists the Vertica driver under User Data Sources.
- Click OK to close the ODBC Data Source Administrator.
Note There is more complete information on DSNs in the Creating an ODBC DSN for Windows Clients.
Disabling the QlikView Wizard
The QlikView wizard does not work with Vertica . Follow these steps to disable it.
- Launch QlikView.
- From the QlikView start page, choose Settings > User Preferences.
- From the General tab under User Preferences, uncheck Show “Getting Started Wizard” when creating new document.
- Click OK.
Connecting to the Vertica Database
Follow these steps to connect to the Vertica database through QlikView.
- From the QlikView Start Page, select the Edit Script.
- The Edit Script dialog box displays.
- Under the Database drop-down list, verify that ODBC is selected.
- Click Connect. The Connect to Data Source dialog box appears. (The following example uses the Vertica data source created earlier.)
- Check Show User DSNs.
- Click Test Connection. (Note that you entered the User ID and Password earlier when creating the data source.)
- Click OK when you receive the Connection Test Succeeded.
- Click OK to open the Connect to Data Source dialog box.
Notice that a line is added to the Edit Script screen:
ODBC CONNECT TO Vertica.
Leave the Edit Script dialog box open, and continue with the next procedure.
Editing a Script
To query the Vertica database, you create SELECT statements through the QlikView interface by choosing database tables and fields in the Create Select Statement dialog box. QlikView then adds the statements to a script that, when executed, queries and collects information from your Vertica database. You can include this information in reports and charts in QlikView.
Follow these steps to create SELECT statements and add them to your script:
- From the Edit Script dialog box, click Select.
The Create Select Statement dialog box appears, listing your database tables.
- Select the desired tables and fields. As you select, click Add; QlikView adds them to the Script When you are done, click OK. Informatica adds the SELECT statements to the Edit Script dialog box.
- Click OK to accept the Edit Script dialog box as it appears. You can also edit your script manually on this page. The Main page (QlikView’s “sheet”) appears. See the QlikView documentation for complete information.
- Execute the script by clicking Reload.
Save the document if you are prompted to do so.
Your script executes.
For More Information
Refer to the QlikView documentation for complete information on editing and executing scripts, and defining and displaying objects and charts within QlikView.