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>]
- <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.
I hope you found this useful. Try playing with advanced DAX queries and you’ll be surprised with the results.