What’s new in SQL Server 2017 RC1 for Analysis Services

The RC1 public preview of SQL Server 2017 is available here! It includes Dynamic Management View improvements for tabular models with compatibility level 1200 and 1400.

DMVs are useful in numerous scenarios including the following.

• Exposing information about server operations and health.
• Documentation of tabular models.
• Numerous client tools use DMVs for a variety of reasons. For example, BISM Normalizer uses them to perform impact analysis for incremental metadata deployment and merging.

RC1 rounds off the DMV improvements introduced in CTP 2.0 and CTP 2.1.

DISCOVER_CALC_DEPENDENCY now works with 1200 and 1400 models. 1400 models show dependencies between M partitions, M expressions and structured data sources.

Further enhancements in RC1 include the following for 1200 (where applicable) and 1400 models.

• Named dependencies result from DAX or M expressions that explicitly reference other objects. RC1 introduces named dependencies for DAX in addition to DAX data dependencies. Previous versions of this DMV returned only data dependencies. In many cases a dependency is both named and data. RC1 returns the superset.
• In addition to dependencies between M partitions, M expressions and structured data sources, dependencies between provider data sources and non-M partitions (these are the traditional partition and data source types for tabular models) are returned in RC1.
• The following new schema restrictions have been introduced to allow focused querying of the DMV. The table below shows the intersection of the schema restrictions with the type of objects covered.
  • KIND with values of ‘DATA_DEPENDENCY’ or ‘NAMED_DEPENDENCY’.
  • OBJECT_CATEGORY with values of ‘DATA_ACCESS’ or ‘ANALYSIS’.

• Mashup dependencies are dependencies between M partitions, M expressions and structured data sources. They are named, M-expression based, and only apply to 1400 models.
• Provider data source & non-M partitions are dependencies between traditional partitions and provider data sources. They are based on properties in tabular metadata rather than expression based, so are not considered “named”. They are available for 1200 and 1400 models.
• DAX named dependencies are explicit named references in DAX expressions. They are available for 1200 and 1400 models.
• Other data dependencies are data dependencies for DAX expressions and other types of data dependencies such as hierarchies and relationships. To avoid potential performance issues, data dependencies from DAX measures are only returned when using a QUERY schema restriction. They are available for 1100, 1103, 1200 and 1400 models.

1100 and 1103 models only return other data dependencies, and they ignore the new schema restrictions.

DAX data dependencies and DAX named dependencies are not necessarily the same thing. For example, a calculated table called ShipDate with a DAX formula of “=DimDate” clearly has a named dependency (and data dependency) on the DimDate table. It also has data dependencies on the columns within DimDate, but these are not considered named dependencies.

The following query returns the output shown below. All DAX and M expression named references in the model are included. These can originate from calculated tables/columns, measures, M partitions, row-level security filters, detail rows expressions, etc.

 SELECT * FROM SYSTEMRESTRICTSCHEMA
    ($SYSTEM.DISCOVER_CALC_DEPENDENCY, [KIND] = 'NAMED_DEPENDENCY')

The following query returns the output shown below. Some data dependencies happen to also be named dependencies, in which case they are returned by this query and the one above with a NAMED_DEPENDENCY schema restriction.

 SELECT * FROM SYSTEMRESTRICTSCHEMA
    ($SYSTEM.DISCOVER_CALC_DEPENDENCY, [KIND] = 'DATA_DEPENDENCY')

The following query returns the output shown below. Partitions, M expressions and data source dependencies are included.

 SELECT * FROM SYSTEMRESTRICTSCHEMA
    ($SYSTEM.DISCOVER_CALC_DEPENDENCY, [OBJECT_CATEGORY] = 'DATA_ACCESS')

The following query returns the output shown below. The results of this query are mutually exclusive with the results above with a DATA_ACCESS schema restriction.

 SELECT * FROM SYSTEMRESTRICTSCHEMA
    ($SYSTEM.DISCOVER_CALC_DEPENDENCY, [OBJECT_CATEGORY] = 'ANALYSIS')

RC1 provides improvements for this DMV, which 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.

RC1 corrects the cardinality columns, which were previously showing incorrect values.

 SELECT * FROM $System.MDSCHEMA_MEASUREGROUP_DIMENSIONS;

To get started, download SQL Server 2017 RC1. The latest 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
  July 20, 2017
  Just curious, will we see the Web data source connector in the GA release of SSAS 2017, or should we expect this and other Power BI parity features to land post-release?
• Anonymous
  July 25, 2017
  To echo Tate, when will the web data source connector be available in SSAS 2017?
  • Anonymous
    August 01, 2017
    We still have about 20 connectors left to enable. The Web connector is one of them. It's a particularly challenging one because it might require you to run arbitrary JavaScript, which isn't something we want to enable on a SSAS server without some additional controls. Of course, we'll blog about it when it is ready. The plan is to make any connectors that don't make it into SSAS 2017 RTM available afterwards in a Cumulative Update (CU). And don't be disappointed if the upcoming SSDT releases in the next couple of months don't feature too many new connectors. This is because of a shift to much needed quality and robustness improvements. The connector work will resume again once SSDT reaches the desired quality bar.Hope this helps.Cheers,Kay
    • Anonymous
      August 01, 2017
      Thanks Kay! Looking forward to seeing what you all have in store for us.
    • Anonymous
      August 05, 2017
      Thanks for the update Kay. It is very reassuring!
    • Anonymous
      August 14, 2017
      Will there be a connector for Redshift?
      • Anonymous
        August 23, 2017
        The goal is to provide parity with Power BI Desktop, and so, yes, Amazon Redshift is on our list of connectors. Cheers,Kay