Querying SSAS Tabular Model from QlikView using DAX – 3 Simple Steps

Posted on by

QlikView does not have the ability to natively query SSAS cubes (both Multidimensional and Tabular). But it allows you to query SSAS using the Microsoft MSOLAP driver. The primary motive behind this post is the lack of material or content online about sourcing data from SSAS Tabular using DAX (SQL Server 2012). One could write MDX queries and extract data from SSAS Tabular, but that results in hours if not days for the query to execute and load a large dataset into a QlikView Data file (or QlikView Workbook).

For more information on connecting to SSAS with MDX queries, please refer to this QlikCommunity thread.

The following section is a 3-step simple tutorial to successfully query and extract data from a deployed SSAS Tabular model (In-Memory cube).

The pre-requisite to issue DAX queries from QlikView is to install the latest MSOLAP driver from here.

Step 1: Write the following connection string to conenct to the SSAS Tabular database.

OLEDB CONNECT TO [Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<CubeName>;Data Source=<AnalysisServicesInstance>;Location=<AnalysisServer>]

where

  • <CubeName> – Name of SSAS Tabular database;
  • <AnalysisServicesInstance> – Name of SSAS Instance;
  • <AnalaysisServer> – Name of server where SSAS is running

Step 2: Write the relevant DAX query to extract data. For simplicity, the following code extracts data from a table named “GeoCodes”.

GeoCodes:
SQL
evaluate('GeoCodes');
STORE GeoCodes INTO D:\QVSource\QVD\GeoCodes.qvd;
DROP TABLE GeoCodes;

Step 3: Debug or Run the script to execute and load data into a QVD file.

MSOLAP_DAX

I hope you found this useful. Try playing with advanced DAX queries and you’ll be surprised with the results.

4 thoughts on “Querying SSAS Tabular Model from QlikView using DAX – 3 Simple Steps”

  2. Are you able to do column renaming? I tried the following but it doesn’t seem to take within the QlikView load script:

    Customer:
    SQL
    EVALUATE
    ADDCOLUMNS
    (
    SUMMARIZE
    (
    Customer,
    Customer[Customer Key],
    ),
    “Customer Key”, Customer[Customer Key]
    )

    Reply ↓

    • Hi Arthur, I was able to achieve column renaming in the past using the approach below:
      1. Load the results of DAX query into a QVD file
      2. Load the QVD file into QVW and rename the columns within the LOAD script
      Hope that helps.

      Reply ↓

Leave a comment