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

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

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.

Example: [KIND]=’NAMED_DEPENDENCY’

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')

Example: [KIND]=’DATA_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')

Example: [OBJECT_CATEGORY]=’DATA_ACCESS’

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')

Example: [OBJECT_CATEGORY]=’ANALYSIS’

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')

MDSCHEMA_MEASUREGROUP_DIMENSIONS

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;

Download now!

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!