Qlik Sense Desktop – Querying Analysis Services Tabular Model

Posted on July 27, 2014 by mdkabir

Qlik launched their new product Qlik Sense Desktop that officially marks the launch of their next generation platform. The Qlik community and practitioners had been waiting for Qlik.Next for a while and the launch of Qlik Sense Desktop marks the beginning of a new era for Qlik embracers.

A few months ago, I blogged about using Microsoft Analysis Services Tabular Model as a data source for Qlikview. As a practitioner of SQL Server Analysis Services (SSAS) Tabular Model, I was curious to find out if there was a native connector for SSAS in Qlik Sense Desktop.

The following steps describe the process of loading data from SSAS Tabular Model using Qlik Sense Desktop. As a pre-requisite, please ensure you have the necessary OLEDB drivers for SSAS (MSOLAP) installed on your desktop.

Step 1: In the ‘Data Load Editor’, click on ‘Create New Connection‘ and select OLEDB from the list of connections.

Step 2: Select the provider for Analysis Services and enter the connection details to connect to the tabular model. Click Save once done.

Step 3: Once the connection is successfully saved, it appears in the Data Connections pane on the right.

Step 4: Though the metadata can be viewed using the select data option, the OLEDB provider for SSAS cannot programmatically generate DAX queries. To extract data from the Tabular Model connection created in the previous step, DAX queries should be written in the data load editor as below.

The verdict is that there are subtle differences in the interface but there is no native SSAS connector in Qlik Sense desktop yet. There are major enhancements in the visualisation side of things and I’m sure a lot of us will be talking about them in the coming days.

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

Posted on July 16, 2013 by mdkabir

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.

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

BIDS Helper to the rescue (SSAS cubes)

Posted on June 12, 2012 by mdkabir

Most often we think that the cubes we design and implement will process without any issues when the corresponding dimensions and facts have sensible data. This, however, is only a myth. I learnt this the hard way when I modified an existing SSAS cube which had some changes to the dimension attributes. The processing of the cube failed due to a mismatch of an attribute’s data type between a dimension in the data source view (DSV) and the dimension in the cube. That’s when BIDS Helper came to my rescue. I quickly installed BIDS Helper from here and ran its inbuilt Dimension Health Check feature to discover the hidden truth. I found BIDS Helper to be extremely useful to perform health checks on measures as well.

As soon as I run this feature on my measure group, it points out that the data type of measures contained within the group can be changed for optimised results.