Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-369

How To connect Qlik Sense Desktop to an EXASOL database using DIRECT QUERY

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: 3rd Party Tools
    • Labels:
      None
    • Environment:
      QlikSense 2.2
    • Solution:
      Hide

      Note: This solution is no longer maintained. Please find the latest information in our documentation portal: https://docs.exasol.com/connect_exasol/bi_tools/qlik.htm

      ----------------------------------------------------------------------------------------------------------------------------------------

      Prerequisites

      • Install the 64 bit EXASOL ODBC driver on your system
      • Configure a DNS using the Windows Data Source Administrator tool

      Add data source

      Open Qlik Sense Desktop and go to the data load editor and create a new connection

      Write a load script

      Use the DIRECT QUERY feature to tell Qlik which EXASOL tables/views should be used for SQL pushdown (Direct Discovery function)

      Example script:

      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; 
      

      The documentation of the DIRECT QUERY command can be found in the Qlik Sense documentation
      (http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/direct-query.htm)

      Run the script

      Click on load data. This step precalculates the distinct values for the dimension fields and doesn't load any detailed fact data to Qlik Sense.

      Start to build real-time dashboards on huge fact tables using Qlik Sense on EXASOL

      Show
      Note: This solution is no longer maintained. Please find the latest information in our documentation portal:  https://docs.exasol.com/connect_exasol/bi_tools/qlik.htm ---------------------------------------------------------------------------------------------------------------------------------------- Prerequisites Install the 64 bit EXASOL ODBC driver on your system Configure a DNS using the Windows Data Source Administrator tool Add data source Open Qlik Sense Desktop and go to the data load editor and create a new connection Write a load script Use the DIRECT QUERY feature to tell Qlik which EXASOL tables/views should be used for SQL pushdown (Direct Discovery function) Example script: 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; The documentation of the DIRECT QUERY command can be found in the Qlik Sense documentation ( http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/direct-query.htm ) Run the script Click on load data. This step precalculates the distinct values for the dimension fields and doesn't load any detailed fact data to Qlik Sense. Start to build real-time dashboards on huge fact tables using Qlik Sense on EXASOL
    • Category 1:
      3rd Party Tools - Vizualization & Analytics

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: