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

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

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: 3rd Party Tools
    • Labels:
      None
    • Environment:
      Qlik View Desktop 12
    • Solution:
      Hide

      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 View Desktop and go "File" -> "Edit script" to the data load editor and add the ODBC Data Source

      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:

      ODBC CONNECT TO RETAIL_DEMO;
      
      DIRECT QUERY DIMENSION
      DESCRIPTION as PRODUCT_DESCRIPTION,
      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 View documentation:
      (https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/DirectDiscovery/direct-discovery-introduction.htm)

      Run the script

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

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

      Best Practices

      Referencing columns in DIRECT QUERY DIMENSION

      If you must reference a column in a fully qualified manner you may get the
      error message: "." character is not allowed within quoted identifiers.
      To avoid this please use the NATIVE function to fully qualify the COLUMN:

      
      DIRECT QUERY DIMENSION
      NATIVE('EXA_DB.RETAIL_STAR.FACT_SALES_POSITIONS.DESCRIPTION') as PRODUCT_DESCRIPTION,
      
      
      Show
      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 View Desktop and go "File" -> "Edit script" to the data load editor and add the ODBC Data Source 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: ODBC CONNECT TO RETAIL_DEMO; DIRECT QUERY DIMENSION DESCRIPTION as PRODUCT_DESCRIPTION, 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 View documentation: ( https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/DirectDiscovery/direct-discovery-introduction.htm ) Run the script Click on "Reload". This step precalculates the distinct values for the dimension fields and doesn't load any detailed fact data to Qlik View. Start to build real-time dashboards on huge fact tables using Qlik View on EXASOL Best Practices Referencing columns in DIRECT QUERY DIMENSION If you must reference a column in a fully qualified manner you may get the error message: "." character is not allowed within quoted identifiers. To avoid this please use the NATIVE function to fully qualify the COLUMN: DIRECT QUERY DIMENSION NATIVE( 'EXA_DB.RETAIL_STAR.FACT_SALES_POSITIONS.DESCRIPTION' ) as PRODUCT_DESCRIPTION,
    • 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: