What’s new for SQL Server 2016 Analysis Services in CTP3.3

What is new in SQL Server Analysis Services 2016 CTP3.3

It is a new year and again we are delivering more updates to Analysis Services with SQL Server 2016 CTP3.3. This month’s update delivers support for calculated columns and row level security for DirectQuery, translations for Tabular models into different languages, additional performance optimizations and more TMSL scripts.

Add a calculated column to a DirectQuery model

In this CTP we have removed more restrictions previously applicable to tabular models in DirectQuery mode. In this CTP we allow for DirectQuery models in the 1200 compatibility level to add calculated columns to any table.

Let’s take a quick look at an example. I have this very simple model that is running in DirectQuery mode:

Now I want to extend the DimProduct table by creating a column that concatenates the ProductKey and EnglishProductName into a new column. This was not possible in SQL Server 2014 or earlier version when using DirectQuery mode. With CTP 3.3 we allow intra-row calculated columns to be created using DAX, the DAX functions are restricted to a limited set, as these calculations now get send down to the data source directly and could result in unwanted performance issues.

Let’s add a calculated column to the table in our model:

After creation I can use this new column in Excel where see it showing us the data we expect:

Running the SQL Server Profiler to catch the queries send I see that the following SQL is being generated:

SELECT [t0].[ProductKey] AS [ProductKey]
,(CAST([t0].[ProductKey] AS NVARCHAR(4000)) +
(N’ – ‘ + COALESCE([t0].[EnglishProductName], ”)))
AS [Full productname]

As you can see all the computation is done by the underlying SQL Server Data Source directly.

Apply row level security to a DirectQuery model

Besides adding a calculated column to models in DirectQuery, you can now also can add DAX filters to them. In the same example as above we want the sales team to only see products with key values larger than 400. I now can go to the role manager and add a new role that includes a DAX expression that filters the products:

Now again testing this in Excel we’ll see it works:

Note that in the previous screenshot we had products in the 200s and now it starts with 463.

If we look at the profiler again we will see it adds the following where clause to the query:

WHERE (COALESCE([t0].[ProductKey], 0) > 400)

Again this restriction is pushed down to the underlying data source instead of this being calculated in SSAS itself.

More script actions for the 1200 compatibility level mode now available

In CTP 3.3 we added even more script actions for the 1200 compatibility level. Besides the ones already supported before we now also support:

  • Create, alter, delete databases
  • Backup and restore databases
  • Attach and detach databases

The final command left is merge partitions that will be enabled in an upcoming preview release.

 Translating your tabular model

In this CTP we now also allow translations of the SQL Server 2016 Analysis Services Tabular model into different languages to be consumed by any client tool connecting to SSAS. Very often the translations themselves are not done by the modeler but by either someone from the business users or a translator.

The modeler or BI professional can now export the model metadata to be translated and reimport it back into SSDT after it is translated.

Let’s see how this works. To manage translations, we now expose a new “Translations” option in the ribbon:

This allows me to add or remove translations from the model, in my case I want to translate my model in Dutch so I add it to the list of languages that the model can be translated in:

I now export the selected language that gives me a JSON file that I can translate. If I select multiple languages I get a list of languages in my exported file.

This file contains two parts, 1 a referenceCulture part that shows the actual names and descriptions in the model and a culture part that contains the translations for name and caption:

I can now add translations to these elements into the file:

And re-import the file:

This now gives me a translated model that I can test trough “Analyze in Excel”:

This now gives me my Excel field list in Dutch:

TMSL Support for the SSIS Analysis Services Execute DDL Task

With CTP 3.3 you can now use the SSIS 2016 Analysis Services Execute DDL tasks to run TMSL scripts:

Note: if you want to use a version of SSIS before SQL Server 2016 you can wrap the JSON with the following XMLA tags to make it work in any older version:

<Statement xmlns=”urn:schemas-microsoft-com:xml-analysis”>
TSML command HERE

In the upcoming CTP’s we will update the other SSIS tasks and destinations to work with the new 1200 model.

TMSL Support for the SSAS PowerShell cmdlet Invoke-ASCmd

Just like the SSIS execute DDL task you can now pass in TMSL commands directly as a parameter to the Invoke-ASCmd powershell cmdlet.

Invoke-ASCmd -Database:”Adventure Works DW” -Query:”{\”refresh\”: {\”type\”: \”automatic\”,\”objects\”: [{\”database\”: \”TabularMM\”}]}}”

Similarly, as for SSIS if you want to use an older version of PowerShell to execute TMSL commands you can wrap the TMSL command with the same XMLA tags to make it work.

In the upcoming CTP’s we will update the other PowerShell cmdlet’s to work with the new 1200 model.

Redundant join elimination for MDX queries using VertiPaq and DirectQuery.

One of the most common operations using Analysis Services is for end users to do a simple join between two tables. In this Analysis Services is unique compared to SQL Server as the joins are defined in the model not the query. In the SQL 2014 there were scenario’s using MDX where this wasn’t done as optimal as this connect item attests to.

In this CTP we have optimized the way that joins are done for MDX queries as part of the MDX for DirectQuery work. This means that when using MDX against either a VertiPaq or DirectQuery model we now push down filters in much more scenario’s, limiting the size of data that has to be worked on by Analysis Services significantly. In addition to pushing down filter we also eliminate tables which effectively have no impact on the result of the join. This in turn reduces number of queries sent to the data source even more and eliminate in-memory joins which effectively have no impact on the result.

Download now!

To get started, download SQL Server 2016 CTP3.3 here. The corresponding tools, SSDT January 2016 for Visual Studio 2015 can be downloaded here.

Make sure server and tools are the same version to avoid errors related to incompatible components. You might need to uninstall an existing CTP build before getting a newer version.