Share this article:

Vertica QuickStart for QlikView

To find this document in PDF format, click here.

What is a QuickStart Application?

The Vertica QuickStarts are sample applications that show how complementary technologies can work together to deliver outstanding benefits to end users. Each QuickStart uses a different BI client tool with Vertica Analytic Database. Each BI client is a product of a Vertica technology partner.

The QuickStarts are available for download free of charge on the Big Data Marketplace in the QuickStart Examples category

About this Document

This document explains how to deploy and use the Vertica QuickStart for QlikView. The document includes the setup information that you need to get up and running, and it provides an overview of the QlikView dashboards and the Vertica data source.

Vertica QuickStart for QlikView Overview

The Vertica QuickStart for QlikView is a sample BI application implemented as a QlikView app and powered by Vertica Analytic Database. The dashboards in the app present sample retail data for analysis. The QuickStart shows how retail companies could use Vertica and QlikView to quickly explore, visualize, and gain insight into their data stored in Vertica.

For a quick introduction to the Cognos QuickStart for Vertica, see this short video:

Requirements

The Vertica QuickStart for QlikView requires Vertica database server with the VMart example database, Vertica client, and QlikView.

The QuickStart was created using QlikView for Windows version 11.20 – 64-bit edition and Vertica Analytic Database version 7.1.

Installation and Setup

To install and start up the required components, follow these steps.

Install the Vertica Database Server

The Vertica database server runs on Linux platforms. If you do not have Vertica, you can download the RPMs or a virtual machine free of charge from the Vertica Community Portal at https://my.vertica.com/.

To download and install Vertica Community Edition:

  1. On myVertica, under Vertica Community Edition, click Signup Now to register for a Community Edition Account.
  2. Provide your information and click Signup.
  3. Follow the on-screen instructions to download and install Vertica Community Edition.

Install the VMart Example Database

The Vertica QuickStart for Product_Name assumes a default installation of the Vertica VMart example database. Follow the tutorial in the Vertica Getting Started Guide to install VMart.

In the tutorial:

The VMart example database includes three schemas: Public, Online Sales, and Store. The schemas are interrelated and share many dimensions. For details, see VMart Example Database Schema, Tables, and Scripts.

Install QlikView

QlikView runs on Windows desktop platforms. If you do not have QlikView, you can download and install it as follows:

  1. Go to the Qlik website to download the free version of QlikView:

     http://www.qlik.com/us/explore/products/qlikview/free-download

  1. Download the QlikView installer that is compatible with your Windows architecture (32- or 64-bit).
  2. Start the installer.
  3. Follow the prompts to install QlikView.

Tip Watch the QlikView video, QlikView Installation and Prerequisites, for a quick overview.

Install the ODBC Client Driver

QlikView uses ODBC to connect to HP Vertica and a data source name (DSN) connection to the VMart example database. The ODBC driver for HP Vertica is part of the HP Vertica client package.

Download and Install the Vertica Client Package

  1. Go to http://www.vertica.com/resources/vertica-client-drivers/.
  2. Download the Vertica client package that matches the architecture of your operating system (32- or 64-bit) and your version of Vertica database.
  3. As Windows Administrator, launch the installer and follow the prompts to install the Vertica client.

Note Vertica drivers are forward compatible. You can connect to the Vertica server using earlier versions of the client. For more information about client and server compatibility, see the Vertica documentation.

Create a DSN Connection to the VMart Example Database

Depending on the architecture of your operating system and the QlikView application that you downloaded, create a 32- or 64-bit DSN connection. QlikView uses the DSN to connect to your Vertica database.

To create a 64-bit DSN:

  1. From the Windows Start menu, click Run.
  2. Type this text: Setup data sources (ODBC).
  3. Select the executable.

For a 64-bit Windows machine, this shortcut points to the 64-bit ODBC data source executable located in C:\Windows\System32\.

To create a 32-bit DSN:

  1. Navigate to C:\Windows\SysWOW64\.
  2. Locate the program file called exe.
  3. Double-click this executable to create a 32-bit DSN.

For details, see Setting Up an ODBC DSN in the Vertica documentation.

Download the QuickStart

  1. Go to the Big Data Marketplace and log in with your Marketplace credentials.
  2. Select QuickStart Examples.
  3. Select Vertica QuickStart for QlikView.
  4. Click Download.

Open the QuickStart and Connect to Vertica

  1. Make sure that you have created a DSN as described in Create a DSN Connection to the VMart Example Database.
  2. Navigate to the folder where you downloaded the compressed file and extract the contents.
  3. Place the QuickStart file, qvf, in the default QlikView applications folder. On Windows, the default QlikView apps location is:
     C:\Users\dbadmin\Documents\Qlik\View\Apps
  1. Launch QlikView from the Start Menu.
  2. On the QlikView Start page, go to File > Open and navigate to the QlikView apps folder.
  3. Select and open VMart_quick_start.
  4. Go to File > Edit Script.
  5. In the Edit Script window, replace the connection information with your specific HP Vertica VMart database information. To edit the connection information, locate the following line on the Main page and on the Facts page and replace VMart_DSN with the DSN that you previously created.
      ODBC CONNECT TO VMart_DSN

     You can easily make this edit by clicking Connect.

  1. In the Connect to Data Source dialog box, select the appropriate bitness and the DSN connection that you previously created.
  2. Enter the user name and password for the DSN. If the user name and password are included in the DSN, leave these fields blank.
  3. Click Test Connection.
  4. Click OK.
    image002.png
  5. Click Reload on the left hand side of the screen.

 image003.png

  1. QlikView executes the script to load the data. When the script completes, click Close.
  2. Click OK to close the Sheet Properties dialog box.
  3. Click the specific dashboard that you want to explore.

About the QuickStart Dashboards

The QuickStart dashboards present sample business and operational data that a large retail chain might track over time. The chain operates brick-and-mortar stores and an online marketplace. It sells a wide variety of products that it purchases from different vendors.

Note The data in your dashboards will not match the data in the screen shots in this document. This is because the VMart data generator generates data randomly.

Some Features of the User Interface

The QuickStart user interface supports several mechanisms for interacting with the dashboards. A number of them are listed below.

Additionally, you can mouse over elements of the display to display descriptive text.

Work with Individual Panes

Each dashboard contains a set of panes that can be individually maximized, minimized, printed, or exported to Excel. Icons for each of these operations appear in the upper right corner of each pane.

image004.jpg

Drill Up or Down on the Data

A small arrow at the bottom of a pane indicates aggregated data. Click the arrow, then click a data element to drill to the next level in the hierarchy. For example, if you click the arrow in the following pane, then click the data labeled Medical, the display changes to show the categories of medical data that are available.

image005.jpg

Lock the Data Selection

You can lock or unlock the filters that you set on the data. When locked, the filters remain in effect until you unlock them. A menu above the dashboards provides the Lock and Unlock operations, arrows for returning to an earlier data selection, and a Clear operation to clear all filters and reload the data.

Executive Dashboard

The Executive Dashboard presents a high-level view of the business data that is shown in greater detail in the other dashboards. You can use this dashboard to analyze the overall functioning of the business over time.


In this instance, we are viewing sales_dollar_amount for 2006 and 2007. You can clearly see that the general pattern of returns closely tracks the general pattern of sales for both online and store sales in both years.

image006.jpg

Online Sales Dashboard

The Online Sales Dashboard presents an overview of the online business. In this instance, we are viewing sales to individual customers in 2005, 2006, and 2007. The data is displayed by region.

image007.jpg

Click the arrow beneath the bar graph to view the data for individual states. If you click the arrow and then click SourthWest, you will see the data for the states in the SouthWest sales region: Arizona, Colorado, Kansas, and Nevada.

image008.jpg

Store Sales Dashboard

 The Store Sales Dashboard presents an overview of sales for business conducted in stores. In this instance, we are viewing sales to individual customers in 2006 and 2007.

The dashboard presents the data by region and includes the top 5 customers, products, stores, employees, and promotions.

image009.jpg

The top store across all regions is Store249. If you want to see the top store across all states in the SouthWest region is, click the arrow, and then click SouthWest. You will see that the top store in the SouthWest region is Store246.

image010.jpg

Vendor Dashboard

The Vendor Dashboard displays information about the vendors that sell to this retail chain. In this instance, we are viewing Perfect Order Rate for one vendor, Sundry Outlet, for three years: 2005, 2006, and 2007. The overall rate is about 71%. The line graph shows the fluctuation over the three year period.

image011.jpg

Product Dashboard

The Product Dashboard displays information about product sales. In this instance, we are viewing Sales_Dollar_Amount for 2006 and 2007.

image012.jpg

If you click the arrow, and then click Medical, you will see product sales for the categories of medical products Medical and Pharmacy. The data will refresh for both online and store sales.

image013.jpg

Inventory Dashboard

The Inventory Dashboard displays the quantity of items in stock over time and by location over time. In this instance, we are viewing the total inventory for all warehouses in 2007.

image014.jpg

If you drill down on Texas, you will observe the items in inventory in Texas in 2007. If you drill further by clicking Warehouse 2, you will see the items in inventory in Warehouse 2 in Texas in 2007.

image015.jpg

Customer Dashboard

The Customer Dashboard displays information about the customers of this retail chain, both for online and store purchases. In this instance, we are viewing sales data by customer in 2005. Click a customer name to view the sales data for that customer.

image017.jpg

Call Center/Employee Dashboard

The Call Center/Employee Dashboard presents information about sales personnel, both in stores and in call centers for online sales. In this instance, we are viewing the sales data for all sales employees in 2005. Click one or more employee names to see the sales data for those individual employees.

image017.jpg

Find More Information

Subject Link

QuickStart video

https://www.youtube.com/watch?v=3z-zU-MiqaQ

QuickStart Guide (this document) PDF

http://my.vertica.com/docs/Ecosystem/QS_BI_QlikView.pdf

Vmart example database

Introducing the VMart Example Database

VMart Example Database Schema, Tables, and Scripts

Vertica

https://my.vertica.com/

Vertica documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data Marketplace

https://marketplace.saas.hpe.com/big-data

QlikView

http://www.qlik.com/

 

Contact Us

The Vertica QuickStart for QlikView is intended as an example of complementary technologies: QlikView with Vertica Analytic Database. As such, it is freely available for demonstration and educational purposes to anyone wishing to explore these technologies. The QuickStart is not a product, and is not governed by any license or support agreement.

We welcome your feedback. If you have questions, comments, or suggestions, please contact us by clicking the Contact developer button on the Big Data Marketplace download page for the QuickStart.

Share this article: