Tag Archives: Dashboard

Qlik Sense Desktop – Querying Analysis Services Tabular Model

Posted on by
1

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.

qliksense_oledb

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

qliksense_oledb_ssas

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

qliksense_oledb_ssasconnection

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.

qliksense_oledb_testdata

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.

 

SSRS KPI Indicators for non-numerical values

Posted on by
Reply

Today, I would like to share a quick and easy solution to display Traffic-Lights indicator for statuses in a workflow process. I recently implemented a workflow-driven report using SSRS which allows users to mark the receipt of documents and to further update the status of documents as “Signed Off”, “Submitted” etc. The requirement was purely to provide a workflow-driven report to the users. I took advantage of SSRS’ native KPI indicators to include a traffic-lights indicator to show the status graphically as it changes. This proved to be a quick win for my client users.

To start with, I designed the following report with link buttons for users to update the status. These links point to the same report with a dataset calling a stored procedure to update the status. Since this post focusses on the indicators, I shall stick to doing just that.

Image

I have an record for a given document and it is marked as “Received” by default. (Notice the yellow colour traffic light indicator). The user can change the status to “Current” which then turns the colour of the indicator to green as shown below.

Image

Lets take a closer look at the settings to achieve this.

Right click on the report body and select Indicator from the list of available report items.

Image

From the available Indicator types, select the “rimmed traffic lights indicator” and place it on the report. After resizing and adjusting the position of the indicator, right click on the indicator and select Indicator Properties.

Image

In the Indicator Properties dialog, select the Values and Scales tab and notice the preset numerical values for each colour. These range values can be modified to ranges that suit your needs. As discussed earlier, my requirement was to change the colour depending on a string field value which holds the status as “Received“, “Current”, “Submitted” or “Cancelled“. The value expression comes in handy to set numerical values to each of the string statuses.

I assigned a numerical value to each of the string values in my status field. The indicator then uses the corresponsing numerical value to change the colour based on the defined range.

From the expression above, if the status is set to “Cancelled” a zero value is returned which falls in red colour’s range. Thus, the report then displays the red coloured indicator. While this is a simple solution to use non-numerical KPIs to display indicators on your report, more advanced indicators can be implemented along the same lines.