Share via

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

  • Article

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
}
</Statement>

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.

Comments

  • Anonymous
    February 03, 2016
    Perhaps that should say "The corresponding tools, SSDT January 2016 for Visual Studio 2015 will be available for download here"
  • Anonymous
    February 03, 2016
    "... The corresponding tools, SSDT January 2016 for Visual Studio 2015 can be downloaded here...." - can't see new SSTD for CTP 3.3.
  • Anonymous
    February 03, 2016
    Hello Team,Can't find SSDT January 2016 for Visual Studio 2015 , look like SSDT December 2015 Preview for Visual Studio 2015 is the only one available for the moment ? Regards,
  • Anonymous
    February 03, 2016
    "... The corresponding tools, SSDT January 2016 for Visual Studio 2015 can be downloaded here.." - can't see new SSDT for CTP 3.3
  • Anonymous
    February 04, 2016
    SSDT link ends up on CTP 3.2 pages, is it intentional?
  • Anonymous
    February 04, 2016
    It is available right now https://msdn.microsoft.com/en-us/mt429383
  • Anonymous
    February 15, 2016
    In regards to the new support for row level security to a DirectQuery model, does this include support for functions.We have the need to perform dynamic security for row level security where the allowed rules are defined in a sql table,thus we need to use the lookupvalue function in the dax expression for the row level filter. When we use the lookupvalue function then we receive the error (Function '' is not allowed as part of the row level security expression on DirectQuery models.)Additionally the error puts an entry in the event log and stops the analysis services service.
    • Anonymous
      February 18, 2016
      Hi Michael,This function currently is not supported, it would be really bad for performance. We are working on a scenario that allows you to do the same without using this function for this release. We have received other reports that it stops the server and of course it shouldn't do so, we are investigating the bug.Thanks,Kasper
      • Anonymous
        February 18, 2016
        KasperThank you for the input, we would be interested in the scenario you are working on to provide an alternative. We have been working with the Microsoft MTC and the recommendation was made by the MTC to use Tabular cubes with direct query. Also that row level security was a requirement. so if this can be accomplished in another way or some feature being added to the CTP please let me know.Thanks Michael
        • Anonymous
          February 18, 2016
          The comment has been removed
  • Anonymous
    July 27, 2016
    beware of the "beautiful apos" when pasting the tag in your SSIS task. They are not valid characters in XML!