What's new in SQL Server 2017? For SSAS Tabular, lots, it transpires.
Part six of a six-part series of blogs

This blog summarises the main new features of SQL Server for non-administrators (that is, most of us). Most parts of SQL Server get minor changes at best, but SSAS Tabular 2017 gets a host of major improvements.

  1. What's new in SQL Server 2017 (differences versus 2016)
  2. Changes to SQL Server 2017 installation
  3. What's new in the T-SQL language in SQL Server 2017
  4. What's new in Integration Services 2017 (SSIS 2017)
  5. What's new in Reporting Services 2017 (SSRS 2017)
  6. What's new in Analysis Services 2017 (SSAS 2017) (this blog)

Posted by Andy Brown on 23 January 2018

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

What's new in Analysis Services 2017 (SSAS 2017)?

There are more new features in Analysis Services 2017 than in the rest of SQL Server 2017 put together!

All of the new features are specific to using SSAS Tabular (the multi-dimensional model version of SSAS is no longer being updated by Microsoft).

In addition to all of the new features listed above, Microsoft are also deprecating and discontinuing features in both versions of Analysis Services (tabular and multi-dimensional models).  You can see a full list here.  The biggest announcement we can see is that the data-mining features of SSAS multi-dimensional are now deprecated, meaning that they will be discontinued (ie removed) at some stage in the near future.

New compatibility level

When creating a new tabular model, you can choose a higher compatibility level:

Compatibility level 1400

The highest level of compatibility you can now choose is 1400.

There's a facility to upgrade 1200-level models, but you can't downgrade a model's compatibility.

New user interface for getting data

When you get data, you now see a user interface similar to that used in PowerPivot and/or Power BI Desktop:

Getting data

The new way to get data.

You can choose to edit your data before loading it using something which used to be called Power Query:

Power Query

Power Query by any other name (Microsoft describe this feature as the Modern Get Data Experience, suggesting even they can't decide on a new name).

For those who haven't used this in PowerPivot, Power BI Desktop or Excel, Power Query allows you to transform data before you load it into an application, using an underlying language called M.  Note that you can't do this with models upgraded from compatibility level 1200.

You can now hide blank members from parent-child hierarchies

This is a niche feature.  Suppose that you have created a hierarchy:

Personnel hierarchy

Within a staff table I've created a management hierarchy, with 6 levels.

 

However, when you view this in a client tool like Excel, you get some gaps:

Blanks in hierarchy

Previously you'd have to create complicated measures to hide these if blank.

 

You can now click on your hierarchy and press F4 to show its properties:

Displaying hierarchy properties

Select your hierarchy in Tabular Model Explorer and choose to show its properties.

 

You can now choose to hide blank members:

Hide blank members

A lot of fuss over a very small drop down?

 

Pivot tables and the like then look much better:

Pivot table without blank members

What you'll see for this example after refreshing your pivot table.

 

Defining drill-down rows

Until now drilling down in a pivot table based on a tabular model hasn't been that rewarding:

Drilling down (show details)

You can right-click on a cell to see its "details", but they may not be easy to interpret.

For the example above by default you get the rows from the ultimate child table, which in this case is a list of purchases made:

The rows returned

The column headings are hard to read, and you have no control over which you see.

Now, you can press F4 to show the properties of any measure:

Measure properties

Any measure like this has properties that you can alter.

 

You can now type an expression to use for detail rows:

Detail rows expression

Click on the build button to build an expression.

 

Here's an example of an expression, listing the town, shopping centre and total sales for it:

SELECTCOLUMNS(

'tblPurchase',

"Town", RELATED(tblTown[TownName]),

"Centre", RELATED(tblCentre[CentreName]),

"Sales", [Sales]

)

So now when you show details for this measure, you see something far more sensible:

Details for region

You've now got control over the columns displayed.

You can now also set a default details row expression for a table, which is used when you show the details for any measure for which a specific details row expression hasn't been set.

New IN keyword and CONTAINSROW functions in DAX

DAX now has an IN keyword which works in the same way as the IN keyword in SQL.  For example, this expression would categorise animals into amphibians and other animals:

=IF(

[Animal] IN { "Frog", "Natterjack toad", "Newt" },

"Amphibian",

"Other"

)

The keyword actually builds up a table, in this case comprising 3 rows and 1 column.  When filtering, you can use the CONTAINSROW function instead.  For example, this measure will show total sales for amphibians:

Amphibian sales: =CALCULATE(

SUM([Quantity]),

CONTAINSROW(

{ "Frog", "Natterjack toad", "Newt" },

'tblFamily'[FamilyName]

)

)

The IN and CONTAINSROW functions are - apparently - functionally identical.

New DAX Editor

We've been clamouring for this for years, but in the end this is a bit disappointing:

DAX editor

The new DAX editor allows you to split your lines and indent formulae, but that's about it!

Other changes

In addition to all of the above, the new version of SSAS Tabular model also includes the following changes:

Change Notes
Date relationships You can now create relationships to date dimensions based on date fields (although I can't see why you'd want to do this).
Object security You can write code in JSON or the tabular model scripting language to secure tables and columns, so users can't see that they exist.

As a conclusion, if you're using SQL Server Analysis Services (Tabular Model) you should upgrade to SQL Server 2017 immediately, I'd say; otherwise, I'd stick with your current version for now.

This blog has 0 threads Add post