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 involves creating an ODBC connection using a DSN and then using this DSN to fetch the data.

For this tutorial, we will be connecting to MySQL database. This tutorial assumes you have a MySQL environment available. Create an ODBC DSN (Data Source Name) for MySQL, following these steps − to create DSN. Name the DSN as mysqluserdsn or you may prefer to use the existing one if you have already created a DSN for MySql.

Connecting to the Database

For this chapter we will use the MySql inbuilt database named sakila. We create a new QlikView document and open the script editor (pressing Control+E). Under the tab Data, we locate the section named Database. Choose ODBC from the drop down list and click Connect. The following window opens. Choose the DSN named mysqluserdns and click Test Connection. The message Connection Test succeeded should appear.

Verifying Connection to Database

On successful connection, the screen given below appears showing the connection to the DB in the main window of the script editor.

Select Database Table

Click Select in the above window to get the list of tables and columns. Here as we have created the DSN with sakila as the default database we get the list of tables and columns from this database. We can choose another database from the database drop down list as shown in the screenshot given below. We will continue using the sakila database for this chapter.

Table Loader Script

On Clicking OK in the above window, we get back to the main script editor showing the script for using the table named actor.

Showing the Result in qvd File

Now the data loaded into QlikView document needs to be stored permanently to be analyzed further. For this, we will edit the script to store the data in the form of a qvd file. Press Control+E to open the edit script window and write the following code.

In the code, we give appropriate names to the columns and mention the table name above the load statement. In addition, we give a path where the generated qvd file will be stored. Save this file as QV_mysql.qvw

Using the qvd File

The qvd file can be loaded into the main document and used to create graphs and tables for further analysis. Press Control+R to reload the QV_mysql.qvw file and click Next in the chart wizard. Choose the straight table to be created with actor_id, first_name, last_name as the dimensions and count of actor_id as the expression. A chart appears as given below.

So, this brings us to the end of blog. This Tecklearn ‘Database Connection in QlikView’ blog helps you with commonly asked questions if you are looking out for a job in QlikView BI. If you wish to learn QlikView and build a career in Business Intelligence domain, then check out our interactive, QlikView Certification Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Qlik View Certification Training

QlikView Certification Training

About the Course

Tecklearn’s QlikView Certification Training will help you become an expert in Data Visualization with QlikView. Learn all the basics and advanced features of QlikView such as data modelling, reports and dashboards, visualization, object formatting, system table etc. Along with this, you will be given hands-on working experience on real-time projects that will help you pass the QlikView certification exam. You also get hands-on experience in QlikView applications.

Why Should you take QlikView Training?

• The average annual pay for a QlikView Professional is $122,000. -PayScale.com.
• Deloitte, Cisco, Qualcomm, Sony, AON & other top Fortune 500 companies use QlikView
• QlikView – A Leader in 2017 Gartner Magic Quadrant for Business Intelligence & Analytics Platforms (For 7th Consecutive Year).

What you will Learn in this Course?

Introduction to QlikView

• Concept of Business Intelligence
• Features and components of QlikView
• Comparison with other BI tools
• Architecture
• Installation & Navigation
• QVS, QVW and .log files
• Sheet Objects
• Dimensions and Expressions
• Various file types and extensions

Various QlikView Products

• Overview of the various QlikView products

Introduction to Data and Scripting

• Structuring the Script
• Create tabs in the scripts, Debugging of scripts
• Hands On

Data Model

• QlikView Data File Types: QVD, QVX
• System fields
• Star schema
• Synthetic Key Tables
• Data Modelling Considerations
• Straight and Pivot Table
• Hands On

Components of Qlik View

• Sheets and Sheet Objects
• The List Box
• The Table Box
• The Multi Box
• The Button & Text Object
• Basic Charts
• Hands On

Set Analysis

• Set analysis
• Working with modifiers, identifiers, operators, example of expressions,
• Indirect set analysis
• YTD & MTD

QlikView Functions, Reports and Charts

• Uses of Expressions
• Chart Properties: Dimensions
• Qlikview Functions
• Adhoc Reports
• Incremental Load using QVD Files
• Bookmarks
• Hands On

Data Analysis

• What-if Analysis
• Comparative Analysis
• Dynamic Reporting or Ad-hoc Reporting
• Document Analyzer

QlikView Security

• QlikView security
• Defining the access controls and levels of access
• Maintaining the access control database

QlikView Administration

• QMC (QlikView Management Console)
• Server Installation & Deployment
• QlikView Project

Got a question for us? Please mention it in the comments section and we will get back to you.

579