Qlik Sense

This article explains how to connect Qlik Sense desktop tool to Exasol using ODBC connection.

Exasol takes no responsibility for changes in functionality or terms of use for third-party tools. For more details about the compatibility and use of these tools with Exasol, refer to the respective software vendor’s website.

Environment

The following procedure uses Qlik Sense for Windows. For more information, see Qlik.

Prerequisites

  • Your Exasol SaaS database must be running. For more information, see Database Management.

  • The IP address of the host where the tool is running must be in the allowed IP address list. For more information, see Network Security.

  • Exasol ODBC driver is installed
  • Qlik Sense for Windows installed

Get ODBC Driver and Connection Details

To connect to your Exasol SaaS database from a tool you need a valid connection string. You must also allow network access to the database from the tool, and make sure that the latest Exasol ODBC driver is installed.

  1. On the Databases page in the web console, click on Connect via tools on the cluster that you want to connect to.

    connect via tools

  2. In the wizard, enter the IP address of the client or server that will connect to the database and click Add IP, then click Next. This will add the IP address to the list of IP addresses that are allowed network access to the cluster. The allow list is found on the Security page in the web console. For more information, see Network Security.

    If the IP address is already present in the allow list, just click Next.

    enter ip address

  3. If the latest ODBC driver for your operating system is not already installed, select the driver type and operating system and click on Download to download the latest driver to your local system. For more information about how to install the driver, see ODBC Driver.

    download driver

  4. Click on Next to view your connection details:

    connection details

  5. Copy the Connection string, Port, and User name values from the Connect details dialog. You will need to enter this information in the tool that should connect to the Exasol SaaS database.

  6. To generate a new Personal Access Token (PAT) to use for authentication in the client, click on the Password row, then click on Copy to copy the PAT to your clipboard. For more information, see Personal Access Token.

    generated pat

    The generated PAT is only shown once and cannot be retrieved after you have closed the wizard.

  7. Click on Done to close the wizard.

Connect Qlik Sense to Exasol

Follow these steps to connect Qlik Sense desktop tool to an Exasol instance using ODBC connection:

  1. In Qlik Sense Desktop, from the Qlik Sense Desktop Hub, click on Create New App.

  2. In the Create New App dialog box, type the name of your new app and click Create, and then click Open app.

  3. Click Add data from files and other sources, and in the next screen click on ODBC.

  4. In the Create New Connection (ODBC) dialog box, select your DSN. If your data source requires authentication, then type your credentials in the Username and Password fields.

  5. Click Create. This action will create and open a connection.
  6. The Owner drop-down list displays all the schema in your Exasol database. Select the desired schema. As an example, in the below screen shot RETAIL is selected as the owner.

  7. All the tables for the selected schema is displayed. You can further choose tables and preview the data in the right hand panel. Individual columns can be selected by clicking the Metadata option. When you are done including tables, click Add data.
  8. The selected data is now imported into Qlik Sense. Once the connection is established, dynamic queries can be run through the Direct Query option within Qlik Sense. For more information, refer to Direct Query.

Load Data Using Direct Query

Using the Direct Query option allows you to view the latest or current data without importing all data into the data sets. Direct Query in Qlik Sense allows you access tables and load data using the Direct Discovery function. For additional information, refer to Direct Query and Direct Discovery on Qlik.

You can follow the below steps to load data using the Direct Query option:

  1. Open Qlik Sense Desktop. If you can either create a new connection or use an existing data connection.
  2. Open the data load editor and add your script. The Data load progress dialog is displayed.
  3. Click Load data on the upper right corner of the screen to run the script. This pre-calculates the distinct values and a summary is displayed.

Sample Script

Below is a sample script to load data using the Direction Query option on Qlik Sense.

LIB CONNECT TO [RETAIL_DEMO];

DIRECT QUERY 
DIMENSION
      PRODUCT_GROUP_DESC,
      SALES_DATE,
      Year_Name,  
      Quarter_Name, 
      Month_Name, 
      Week_Name,
      Year, 
      Quarter,
      Month,
      Week,
      AREA,
      CITY,
      NATIVE('  ''['' || longitude || '','' || latitude || '']'' ') as GeoCoord

MEASURE
      PRICE_OVERALL

DETAIL
    SALES_TIMESTAMP
   FROM
    EXA_DB.RETAIL_STAR.FACT_SALES_POSITIONS
   JOIN
  EXA_DB.RETAIL_STAR.ARTICLE 
   ON ( EXA_DB.RETAIL_STAR.FACT_SALES_POSITIONS.ARTICLE_ID = EXA_DB.RETAIL_STAR.ARTICLE.ARTICLE_ID )
   JOIN
  EXA_DB.RETAIL_STAR.MARKETS
   ON ( EXA_DB.RETAIL_STAR.FACT_SALES_POSITIONS.MARKET_ID = EXA_DB.RETAIL_STAR.MARKETS.MARKET_ID )
   JOIN
   EXA_DB.RETAIL_STAR.Time_1
   ON ( EXA_DB.RETAIL_STAR.FACT_SALES_POSITIONS.SALES_DATE = EXA_DB.RETAIL_STAR.Time_1.PK_Date  );
   TAG FIELDS GeoCoord WITH $geopoint;