By: Koen Verbeeck | Comments | Related: > 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:
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:
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:
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:
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:
There are two tips published on how to use the Get Data in SSAS 2017:
- How to import data for SSAS Tabular vNext
- How to manipulate data during import in SSAS Tabular vNext
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.
In some older versions of SSDT, you can force the legacy data sources by adding a key to the registry:
The process is described in the blog post Using Legacy Data Sources in Tabular 1400.
In newer versions of SSDT, the legacy data sources can be enabled through the options of Visual Studio:
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.
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:
- Possibly avoid costly re-encoding during the processing phase. This is explained in the tip Improve Analysis Services Tabular 2017 Processing with Encoding Hints.
- Better compress the model by choosing the more optimal value encoding for integer columns. The tip Reduce the Size of an Analysis Services Tabular Model – Part 2 goes into more detail how this can be achieved. Be sure to also check out part 1 of that tip.
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.
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.
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.
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.
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.
Next Steps
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
