What’s new in SQL Server 2017 CTP 2.1 for Analysis Services

The public CTP 2.1 of SQL Server 2017 is available here! This public preview includes the following enhancements for Analysis Services tabular models.

• Shared M expressions are shown in the SSDT Tabular Model Explorer, and can be maintained using the Query Editor.
• Data Management View (DMV) improvements.
• Opening an file with the .MSDAX extension in SSDT enables DAX non-model related IntelliSense.
• Encoding hints can be set in the SSDT properties window.

Shared M expressions are shown in the Tabular Model Explorer in SSDT! By right clicking the Expressions node, you can edit the expressions in the Query Editor. This should seem familiar to Power BI Desktop users.

M dependencies are included in DISCOVER_CALC_DEPENDENCY for CTP 2.1. As communicated in the CTP 2.0 blog post, DISCOVER_CALC_DEPENDENCY now works with 1200 models.

The following query returns the output shown below. M expressions and structured data sources are included for 1400 models.

 SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
WHERE OBJECT_TYPE = 'PARTITION' OR OBJECT_TYPE = 'M_EXPRESSION';

The output is a superset of the information shown by the Query Dependencies visual, which is available in SSDT from the Query Editor.

This information is useful for numerous scenarios including the following.

• Documentation of tabular models.
• Community tools such as BISM Normalizer that perform incremental metadata deployment and merging, as well as other 3^rd party tools, can use it for impact analysis.

CTP 2.1 provides a fix for MDSCHEMA_MEASUREGROUP_DIMENSIONS. This DMV is used by various client tools to show measure dimensionality. For example, the Explore feature in Excel Pivot Tables allows the user to cross-drill to dimensions related to the selected measures.

Prior to CTP 2.1, some rows were missing in the output for 1200 models, which meant the Explore feature did not work correctly. This is fixed in CTP 2.1.

We intend to make further DMV improvements in forthcoming releases, so stay tuned.

Opening a file with the .MSDAX extension in SSDT allows DAX editing with non-model related IntelliSense such as highlighting, statement completion and parameter info. As you can imagine, we intend to use this for interesting features to be released in the future!

As documented in this blog post, encoding hints are an advanced feature introduced by CTP 1.3. They can help optimization of processing (data refresh) for large in-memory tabular models. In CTP 2.1, encoding hints can be set in the SSDT Visual Studio properties window.

To get started, download SQL Server 2017 CTP2.1. The May 2017 release of the Analysis Services VSIX for SSDT is available here. VSIX deployment for Visual Studio 2017 is discussed in this blog post.

Be sure to keep an eye on this blog to stay up to date on Analysis Services!

• Anonymous
  May 21, 2017
  Fantastic work on the shared expressions, it feels like the feature parity is almost there between SSDT, Power BI and Excel PQ+PP! I'm finding that the load times for a new table into SSDT that uses shared expressions seems to get longer the more shared expressions you have (including expressions unrelated to the table you want to load). That said, it is very stable if you're patient!
  • Anonymous
    May 30, 2017
    The comment has been removed
    • Anonymous
      May 31, 2017
      Thanks for the response Kay. Is the best place to leave feedback/bugs for SSAS 2017 tabular on the MSDN forums?
      • Anonymous
        May 31, 2017
        If it's for an in-market release, Microsoft Support might have to be involved. Otherwise, the most direct way to reach the devs would be Microsoft Connect, but it's not very popular. A more modern feedback channel would be the Azure Analysis Services forum: https://feedback.azure.com/forums/556165-azure-analysis-services?query=attribute%20hierarchy. You could use it for on-prem issues or cloud issues. Perhaps the easiest is to simply send an email to the 'ssasprev' alias @microsoft.com. Any complains, maybe if you sometimes just need to vent a bit, send it to ssasprev. I'm not kidding. Keep it coming. It is super helpful and very much appreciated. Of course, repro steps are always appreciated as well, if possible.