Analysis Services Innovations in SQL Server 2017
This post was authored by Christian Wade, Senior Program Manager, Microsoft
Microsoft SQL Server Analysis Services (SSAS) enables IT professionals to build semantic models over large, integrated managed datasets for end-user consumption. SSAS runs on Windows and is part of the larger suite of SQL Server’s BI offerings. SSAS 2017 brings a host of new connectivity and modeling features for comprehensive, enterprise-scale analytic solutions delivering actionable insights.
SSAS 2017 introduces the 1400 compatibility level. Here are just some highlights of the new features:
- New infrastructure for data connectivity and ingestion into tabular models with support for TOM APIs and TMSL scripting. This enables support for a range of additional data sources, and data transformation and mashup capabilities.
- Support for BI tools such as Microsoft Excel enables drill-down to detailed data from an aggregated report. For example, when end-users view total sales for a region and month, they can view the associated order details.
- Object-level security to secure table and column names in addition to the data within them.
- Enhanced support for ragged hierarchies such as organizational charts and chart of accounts.
- Various other improvements for performance, monitoring, and consistency with the Power BI modeling experience.
To benefit from the new features for models at the 1400 compatibility level, you’ll need to download and install SQL Server Data Tools (SSDT) 17.0 from documentation.
In SSDT, you can select the new 1400 compatibility level when creating new tabular model projects. Alternatively, you can upgrade an existing tabular model by selecting the Model.bim file in Solution Explorer and setting the Compatibility Level to 1400 in the Properties window. Models at the 1400 compatibility level cannot be downgraded to lower compatibility levels.
New infrastructure for data connectivity
1400 models introduce a new infrastructure for data connectivity and ingestion into tabular models with support for TOM APIs and TMSL scripting. This is based on the similar Power Query functionality in Power BI Desktop and Microsoft Excel 2016. For more information, please refer to the Analysis Services Team blog.
Detail Rows
A much-requested feature for tabular models is the ability to define a custom row set contributing to a measure value. Multidimensional models achieve this by using drillthrough and rowset actions. This allows end-users to view information in more detail than the aggregated level.
For example, the following PivotTable shows Internet Total Sales by year from the Adventure Works sample tabular model. Users can right-click the cell for 2010 and then select the Show Details menu option to view the detail rows.
By default, all the columns in the Internet Sales table are displayed. This behavior is often not meaningful for the user because too many columns may be shown, and the table may not have the necessary columns to show useful information such as customer name and order information.
Detail Rows Expression Property for Measures
1400 models introduce the Detail Rows Expression property for measures. It allows the modeler to customize the columns and rows returned to the end user. The following example uses the DAX Editor in SSDT to define the columns to be returned for the Internet Total Sales measure.
With the property defined and the model deployed, the custom row set is returned when the user selects Show Details. It automatically honors the filter context of the cell that was selected. In this example, only the rows for 2010 value are displayed.
Further information on Detail Rows is available our blog post, What’s new for SQL Server vNext on Windows CTP 1.1 for Analysis Services.
Object-Level Security
Roles in tabular models support a granular list of permissions, and row-level filters to help protect sensitive data. 1400 models introduce table- and column-level security allowing sensitive table and column names to be protected in addition to the data within them. Collectively these features are referred to as object-level security (OLS).
The following example uses the Role Manager in SSDT to protect sensitive employee information. Unauthorized users cannot access the selected columns using client tools like Power BI and Excel Pivot Tables. Additionally, such users cannot query the columns using DAX or MDX, or measures that refer to them.
Further information on OLS is available in a recent post, titled, What’s new in SQL Server 2017 CTP 2.0 for Analysis Services.
Ragged Hierarchies
Tabular models with previous compatibility levels can be used to model parent-child hierarchies. Hierarchies with a differing number of levels are referred to as ragged hierarchies. An example of a ragged hierarchy is an organizational chart. By default, ragged hierarchies are displayed with blanks for levels below the lowest child. This can look untidy to users, as shown by this organizational chart in Adventure Works:
1400 models introduce the Hide Members property to correct this. Simply set the Hide Members property to Hide blank members.
With the property set and the model deployed, the more presentable version of the hierarchy is displayed.
Other Features
Various other features such as the following are introduced with the 1400 compatibility level. For more information, please refer to the Analysis Services Team blogs, What’s new for SQL Server vNext on Windows CTP 1.1 for Analysis Services and What’s new in SQL Server 2017 CTP 2.0 for Analysis Services.
- Transaction-performance improvements for a more responsive developer experience.
- Dynamic Management View improvements enabling dependency analysis and reporting.
- Date relationships to easily create relationships to date dimensions based on date columns.
- DAX enhancements to make DAX more accessible and powerful. These include the IN operator, and table and row constructors.
