Category Archives: SQL Server 2012

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.

 

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

Posted on by
4

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.

SQL Saturday #226 in South Florida, USA

Posted on by
Reply

I am excited yet again as I will be speaking at SQL Saturday #226 in sunny South Florida, USA. I will be speaking in the BI and Reporting Track in the afternoon. If you are from the South Florida region, please don’t miss out on this exclusive training day on everything SQL Server.

The event schedule is listed here.

sqlsat226

Cheers.

Update: I had a great time speaking at Fort Lauderdale, Florida. Below is a farewell photo with other speakers (happy faces)

SQL Saturday South Florida Speakers

Speaking at SQL Saturday #224 – Sydney 2013

Posted on by
Reply

I am excited about speaking in my residential city of Sydney for SQL Saturday #224 on 25 May, 2013. I am extending the presentation I did for SQL Saturday #210 in Brisbane last month (Agile BI with SQL Server 2012 and TFS 2012). I am looking forward to seeing my mates from the Sydney BI community.

SQLSat224

Please register if you haven’t done already.

The event will be held at:

Epping Boys High School (EBHS),
213 Vimiera Road
Eastwood, NSW, 2122
Australia

The schedule for the day is put up here.

Statistical Median with T-SQL (2012)

Posted on by
1

I have been interacting with my statistician colleagues lately and there was a reporting requirement to calculate medians over a spread of continuously distributed values. Thankfully with SQL Server 2012, you do not have to use the ROW_NUMBER() function as you had to with earlier T-SQL versions.

PERCENTILE_CONT and PERCENTILE_DISC are now available in T-SQL 2012. According to the SQL BOL, these functions are described as:

PERCENTILE_CONT: Calculates a percentile based on a continuous distribution of the column value in SQL Server 2012. The result is interpolated and might not be equal to any of the specific values in the column.

PERCENTILE_DISC: Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset in SQL Server 2012. PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values; the result is equal to a specific value in the column.

Let us put these functions to test and understand them a bit more in detail:

I have a sample table(ServiceCost) with ServiceIDs (that will be the data element to group by) and Cost (the field for which medians have to be calculated).

MedianSampleTable

I calculated the medians using PERCENTILE_CONT and PERCENTILE_DISC as below:

</code>

SELECT DISTINCT ServiceID,

              PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Cost)

              OVER (PARTITION BY ServiceID) AS MedianValue,

              PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Cost)

              OVER (PARTITION BY ServiceID) AS MedianDisc

  FROM ServiceCost

  order by ServiceID<code>

 

The result of this query is:

MedianOutputTable

The difference between the way these two functions work is clear from the output for ServiceID: SA456. The PERCENTILE_CONT function calculated the mean of the values {62,73} to result in 67.5; while the PERCENTILE_DISC function resulted in the value 62 which is one of the column values in the data set.

We can now show off the power of T-SQL for statistical analysis… Hurray!

SSIS Script Task – Upgrade to SSIS 2012

Posted on by
5

I upgraded a set of SSIS packages from SSIS 2008 R2 to SSIS 2012 and all of them had a smooth upgrade except for one. I had script tasks in almost all the packages which ran succesfully after I upgraded and tweaked them.

There was one package which was giving me trouble and functionally it dealt with moving, loading and deleting files from various folders. I upgraded the script task, updated all references, modified the project paramaters (as I am using the Project Deployment Model) and when I tried to run the package (Debug mode), the following generic error popped up:

Exception has been thrown by the target of an invocation.
at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

errorssis2012

This error message was very generic and did not help me in tracking down the root cause. I decided to put a breakpoint into the Main() method of my script and tried debugging the package again. I was surprised when the script task opened up in VSTA and reached my breakpoint (which contradicted the generic error message – Invocation Error). The issue was with folder security to access the files.

Hope this helps fellow ETL developers who might come across similar issues with a generic invocation error.