Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What's New in Analysis Services Tabular 2017


By:   |   Last Updated: 2018-03-26   |   Comments   |   Related Tips: > Analysis Services Development

Problem

With the release of SQL Server 2017, many products in the Microsoft Data Platform received new features and capabilities. Analysis Services (SSAS) is no exception. In this tip, we’ll give an overview of everything new in SSAS and we’ll reference existing tips for more details.

Solution

Analysis Services consists of two possible services: Multidimensional and Tabular. There are no new features introduced for Multidimensional in SQL Server 2017. All the features discussed in this overview are for Tabular.

Compatibility Level 1400

As with all new releases of SSAS Tabular, a new compatibility level is introduced: 1400. The compatibility level dictates which features a Tabular model can use. When creating a new project, Visual Studio will ask you which level you want to use for a project:

choose compatibility level

Since the 1200 compatibility level, you can choose Integrated workspace to eliminate the need for installing a Tabular instance for development purposes. More information can be found in the blog post Introducing Integrated Workspace Mode for SQL Server Data Tools for Analysis Services Tabular Projects.

You can upgrade an existing project to the 1400 compatibility level in the model properties:

upgrade compatibility level

Keep in mind it’s not possible to downgrade a project to a lower compatibility level. When you open the properties of an SSAS 2017 instance, the supported compatibility level is shown as 1200:

supported compat level

However, this is most likely a bug and the dialog is actually showing the default compatibility level. You can verify this using an XMLA query and the DISCOVER_XML_METADATA command:

discover_xml_metadata

As you can see, the supported compatibility level goes up to 1400.

Modern Get Data Experience

One of the biggest features for SSAS 2017 is the introduction of the Modern Get Data experience, which is very similar to Power Query in Excel (“Get Data” in the most recent versions) or the Query Editor in Power BI Desktop:

modern get data experience

There are two tips published on how to use the Get Data in SSAS 2017:

Keep in mind the new Get Data experience and the mash-up engine (the M language) are only supported for new models in the 1400 compatibility level, not for upgraded models.

It’s still possible to use the legacy wizard. If you upgraded from an earlier compatibility level, you can right-click on an existing connection to import new tables. The advantage of the legacy wizard is the possibility to easily specify your own SQL statements or stored procedures.

legacy data source

In some older versions of SSDT, you can force the legacy data sources by adding a key to the registry:

edit registry

The process is described in the blog post Using Legacy Data Sources in Tabular 1400.

structured data source

In newer versions of SSDT, the legacy data sources can be enabled through the options of Visual Studio:

enable legacy data source through options

The new data sources are called StructuredDataSource, while a legacy data sources is a ProviderDataSource.

Encoding Hints

Encoding hints are an advanced feature which allows you to influence how a column is encoded during processing: using value encoding or hash encoding.

encoding hint property

Hash and value encoding has always been around in Tabular models, but now you can specify a hint through a column property. This allows you to:

Ragged Hierarchies

A welcome addition to Tabular models is to specify a Hide Members property which tells front-end tools to hide repeated members of a ragged hierarchy.

tabular ragged hierarchy in Excel

Check out the tip Creating Ragged Hierarchies in SQL Server Analysis Services for more information. It also includes a walkthrough on how to implement the same behavior in SSAS Multidimensional. The tip How To Handle a Parent-Child Relationship in Analysis Services Tabular explains how you can flatten a parent-child hierarchy into a normal hierarchy in Tabular, which is the starting point for your ragged hierarchy.

Detail Rows

The Detail Rows property is another great new feature. It allows you to specify a custom row set for a measure or a table for a drillthrough action. In contrast with Multidimensional, you are free to specify the order and the names of the columns.

default details rows expression

This functionality is also supported by the DAX function DETAILROWS. The tip How to Define Drillthrough in Analysis Services Tabular 2017 describes this new feature in detail.

Object-level Security

In SSAS 2017, you can easily secure columns and tables through object security in a role.

role manager

This is an elegant approach which is not available in SSAS Multidimensional. Check out the tip Object Security in Analysis Services Tabular 2017 for more info.

DAX Enhancements

With every new release of SSAS Tabular, new functions and capabilities are added to DAX. The following functions are added:

  • ERROR
  • GENERATESERIES
  • The IN operator
  • SELECTEDVALUE
  • TREATAS
  • UNICHAR
  • The Table constructor

A detailed overview is given in the tip DAX Enhancements in SSAS Tabular 2017. Make sure to also check out Kasper de Jonge’s blog post on TREATAS: Dynamically switching axis on visuals with Power BI.

simple KPI using unichar

Miscellaneous

  • Improvements have been made to some Dynamic Management Views. More info can be found in this official blog post.
  • Hierarchy and column reuse are shown in more helpful locations in the Power BI field list.
  • There are now date relationships so you can easily create relationships between date dimensions and date fields.
  • The default installation mode for Analysis Services during the SQL Server set-up is now Tabular, instead of Multidimensional.
  • Existing DirectQuery sources have support for M queries.
  • Improvements in SSMS for viewing, editing and scripting of structured data sources.
  • SQL Server Data Tools finally has support for writing DAX queries in Reporting Services reports. It’s also possible to write DAX queries directly in SSMS.
dax editor in SSRS
Next Steps
  • The official overview can be found here.
  • You can find more Analysis Services tips in this overview.


Last Updated: 2018-03-26


next webcast button


next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools