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

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

• Object-level security to secure model metadata in addition to data.
• Transaction-performance improvements for a more responsive developer experience.
• Dynamic Management View improvements for 1200 and 1400 models enabling dependency analysis and reporting.
• Improvements to the authoring experience of detail rows expressions.
• Hierarchy and column reuse to be surfaced in more helpful locations in the Power BI field list.
• Date relationships to easily create relationships to date dimensions based on date columns.
• Default installation option for Analysis Services is tabular, not multidimensional.

Other enhancements not covered by this post include the following.

• New Power Query data sources. See this post for more info.
• DAX Editor for SSDT. See this post for more info.
• Existing Direct Query data sources support for M expressions. See this post for more info.
• SSMS improvements, such as viewing, editing, and scripting support for structured data sources.

Incompatibility with previous CTP versions

Tabular models with 1400 compatibility level that were created with previous versions are incompatible with CTP 2.0. They do not work correctly with the latest tools. Please download and install the April 2017 (17.0 GA) release of SSDT and SSMS.

Object-level security

Roles in tabular models already support a granular list of permissions, and row-level filters to help protect sensitive data. Further information is available here. CTP 1.1 introduced table-level security.

CTP 2.0 builds on this by introducing column-level security, which allows sensitive columns to be protected. This helps prevent a malicious user from discovering that such a column exists.

Column-level and table-level security are collectively referred to as object-level security (OLS).

The current version requires that column-level security is set using the JSON-based metadata, Tabular Model Scripting Language (TMSL), or Tabular Object Model (TOM). We plan to deliver SSDT support soon. The following snippet of JSON-based metadata from the Model.bim file secures the Base Rate column in the Employee table of the Adventure Works sample tabular model by setting the MetadataPermission property of the ColumnPermission class to None.

 "roles": [
  {
    "name": "Users",
    "description": "All allowed users to query the model",
    "modelPermission": "read",
    "tablePermissions": [
      {
        "name": "Employee",
        "columnPermissions": [
          {
            "name": "Base Rate",
            "metadataPermission": "none"
          }
        ]
      }
    ]
  }

DAX query references to secured objects

If the current user is a member only of the Users role, the following query that explicitly refers to the [Base Rate] column fails with an error message saying the column cannot be found or may not be used.

 EVALUATE
SELECTCOLUMNS(
    Employee,
    "Id", Employee[Employee Id],
    "Name", Employee[Full Name],
    "Base Rate", Employee[Base Rate] --Secured column
)

The following query refers to a measure that is defined in the model. The measure formula refers to the Base Rate column. It also fails with an equivalent error message. Model measures that refer to secured tables or columns are indirectly secured from queries.

 EVALUATE
{ [Average of Base Rate] } --Indirectly secured measure

As you would expect, IntelliSense for DAX queries in SSMS also honors column-level security and does not disclose secured column names to unauthorized users.

Detail-rows expression references to secured objects

It is anticipated that the SELECTCOLUMNS() function will be commonly used for detail-rows expressions. Due to this, SELECTCOLUMNS() is subject to special behavior when used by DAX expressions in the model. The following detail-rows expression defined on the [Reseller Total Sales] measure does not return an error when invoked by a user without access to the [Base Rate] column. Instead it returns a table with the [Base Rate] column excluded.

 --Detail rows expression for [Reseller Total Sales] measure
SELECTCOLUMNS(
    Employee,
    "Id", Employee[Employee Id],
    "Name", Employee[Full Name],
    "Base Rate", Employee[Base Rate] --Secured column
)

The following query returns the output shown below – with the [Base Rate] column excluded from the output – instead of returning an error.

 EVALUATE
DETAILROWS([Reseller Total Sales])

However, derivation of a scalar value using a secured column fails on invocation of the detail-rows expression.

 --Detail rows expression for [Reseller Total Sales] measure
SELECTCOLUMNS(
    Employee,
    "Id", Employee[Employee Id],
    "Name", Employee[Full Name],
    "Base Rate", Employee[Base Rate] * 1.1 --Secured column
)

Limitations of RLS and OLS combined from different roles

OLS and RLS are additive; conceptually they grant access rather than deny access. This means that combined membership from different roles that specify RLS and OLS could inadvertently cause security leaks. Hence combined RLS and OLS from different roles is not permitted.

RLS additive membership

Consider the following roles and row filters.

Users who are members of both RoleA and RoleB can see data for the UK and the US.

OLS additive membership

A similar concept applies to OLS. Consider the following roles.

RoleB allows access to all tables and columns in the model. Therefore, users who are members of both RoleA and RoleB can query the [Base Rate] column.

RLS and OLS combined from different roles

Consider the following roles that combine RLS and OLS.

The following diagram shows the intersection of the tables and rows relevant to this discussion.

RoleA is intended to expose data only for the top right quadrant.

RoleB is intended to expose data only for the bottom left quadrant.

Given the additive nature of OLS and RLS, Analysis Services would be allowing access to all 4 quadrants by combining these permissions for users who are members of both roles. Data would be exposed that neither role had the intention of exposing. For this reason, queries for users who are granted RLS and OLS permissions combined from different roles fail with an error message stating that the combination of active roles results in dynamic security configuration that is not supported.

Transaction-performance improvements

SSDT updates the workspace database during the development process. Optimized transaction management in CTP 2.0 is expected to result in a more responsive developer experience due to faster metadata updates to the workspace database.

DMV improvements

DISCOVER_CALC_DEPENDENCY is back! This Dynamic Management View (DMV) is useful for tracking and documenting dependencies between calculations and other objects in a tabular model. In previous versions, it worked for tabular models with compatibility level of 1100 and 1103, but it did not work for 1200 models. In CTP 2.0, it works for all tabular compatibility levels including 1200 and 1400.

The following query shows how to use the DISCOVER_CALC_DEPENDENCY DMV.

 SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY;

There are differences in the output for 1200 and 1400 models. The easiest way to understand them is to compare the output for models with different compatibility levels. Notable differences are listed here for reference.

• Relationships in 1200 and higher are identified by name (normally a GUID) in the OBJECT column. Active relationships have OBJECT_TYPE of “ACTIVE_RELATIONSHIP”; inactive relationships have OBJECT_TYPE of “RELATIONSHIP”. 1103 and lower models differ because they include all relationships with OBJECT_TYPE of “RELATIONSHIP” and an additional “ACTIVE_RELATIONSHIP” row to flag each active relationship.
• 1103 and lower models include a row with OBJECT_TYPE “HIERARCHY” for each attribute hierarchy dependency on its column. 1200 and higher do not.
• 1200 and higher models include rows for calculated tables with OBJECT_TYPE “CALC_TABLE”. Calculated tables are not supported in 1103 or lower models.
• 1200 and higher models currently do not include rows for measure data dependencies on tables and columns. Data dependencies between DAX measures are included.

We intend to may make further improvements to DISCOVER_CALC_DEPENDENCY in forthcoming CTPs, so stay tuned.

Improved authoring experience for Detail Rows

The April 2017 release (17.0 GA) of SSDT provides an improved authoring experience with IntelliSense and syntax highlighting for detail rows expressions using the new DAX Editor for SSDT. Click on the ellipsis in the Detail Rows Expression property to activate the DAX editor.

Hierarchy & column reuse

Hierarchy reuse is a Power BI feature, although it is surfaced differently in Analysis Services. Power BI uses it to provide easy access to implicit date hierarchies for date fields. Introducing such features for Analysis Services furthers the strategic objective of enabling a consistent modeling experience with Power BI.

Tabular models created with CTP 2.0 can leverage hierarchy reuse to surface user hierarchies and columns – not limited to those from a date dimension table – in more helpful locations in the Power BI field list. This can provide a more guided analytics experience for business users.

For example, the Calendar hierarchy from the Date table can be surfaced as a field in Internet Sales, and the Fiscal hierarchy as a field in the Sales Quota table. This assumes that, for some business reason, sales quotas are frequently reported by fiscal date.

The current version requires that hierarchy and column reuse is set using the JSON-based metadata, Tabular Model Scripting Language (TMSL), or Tabular Object Model (TOM). The following snippet of JSON-based metadata from the Model.bim file associates the Calendar hierarchy from the Date table with the Order Date column from the Internet Sales table. As shown by the type name, the feature is also known as variations.

 {
  "name": "Order Date",
  "dataType": "dateTime",
  "sourceColumn": "OrderDate",
  "variations": [
    {
      "name": "Calendar Reuse",
      "description": "Show Calendar hierarchy as field in Internet Sales",
      "relationship": "3db0e485-88a9-44d9-9a12-657c8ef0f881",
      "defaultHierarchy": {
          "table": "Date",
          "hierarchy": "Calendar"
      },
      "isDefault": true
    }
  ]
}

The current version also requires the ShowAsVariationsOnly property on the dimension table to be set to true, which hides the dimension table. We intend to remove this restriction in a forthcoming CTP.

 {
  "name": "DimDate",
  "showAsVariationsOnly": true

The Order Date field in Internet Sales now defaults to the Calendar hierarchy, and allows access to the other columns and hierarchies in the Date table.

Date relationships

Continuing the theme of bringing Power BI features to Analysis Services, CTP 2.0 allows the creation of date relationships using only the date part of a DateTime value. Power BI uses this internally for relationships to hidden date tables.

Date relationships that ignore the time component currently only work for imported models, not Direct Query.

The current version requires that date relationship behavior is set using the JSON-based metadata, Tabular Model Scripting Language (TMSL), or Tabular Object Model (TOM). The following snippet of JSON-based metadata from the Model.bim file defines a relationship from Reseller Sales to Order based on the date part only of the Order Date column. Valid values for JoinOnDateBehavior are DateAndTime and DatePartOnly.

 {
  "name": "100ca454-655f-4e46-a040-cfa2ca981f88",
  "fromTable": "Reseller Sales",
  "fromColumn": "Order Date",
  "toTable": "Date",
  "toColumn": "Date",
  "joinOnDateBehavior": "datePartOnly"
}

Default installation option is tabular

Tabular mode is now the default installation option for SQL Server Analysis Services in CTP 2.0.

Note: this also applies to installations from the command line. Please see this document for further information on how to set up automated installations of Analysis Services from the command line. In CTP 2.0, if the ASSERVERMODE parameter is not provided, the installation will be in tabular mode. Previously it was multidimensional.

Extended events

Extended events were not working in CTP 1.3. They do work again in CTP 2.0 (actually since CTP 1.4).

Download now!

To get started, download SQL Server 2017 on Windows CTP 2.0 from here. Be sure to keep an eye on this blog to stay up to date on Analysis Services.