We are excited to announce the public preview of the 1400 compatibility level for tabular models in Azure Analysis Services! This brings a host of new connectivity and modeling features for comprehensive, enterprise-scale analytic solutions delivering actionable insights. The 1400 compatibility level will also be available in SQL Server 2017 Analysis Services, ensuring a symmetric modeling capability across on-premises and the cloud.
Here are just some highlights of the new features available to 1400 models.
- New infrastructure for data connectivity and ingestion into tabular models with support for TOM APIs and TMSL scripting. This enables:
- Support for additional data sources, such as Azure Blob storage.
- Data transformation and data mashup capabilities.
- Support for BI tools such as Microsoft Excel enable 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.
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 similar functionality in Power BI Desktop and Microsoft Excel 2016. At this point, only the following cloud-based data sources are supported with the 1400 compatibility level in Azure Analysis Services. We intend to add support for more data sources soon. For more information, please refer to the Analysis Services Team blog, and watch out for future posts to the Azure blog.
- Azure SQL Data Warehouse
- Azure SQL Database
- Azure Blog Storage
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 in this blog post.
Object-Level Security
Roles in tabular models already 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 current version requires that OLS 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”: “General Users”,
“description”: “All allowed users to query the model”,
“modelPermission”: “read”,
“tablePermissions”: [
{
“name”: “Employee”,
“columnPermissions”: [
{
“name”: “Base Rate”,
“metadataPermission”: “none”
}
]
}
]
}
Unauthorized users cannot access the Base Rate column using client tools like Power BI and Excel Pivot Tables. Additionally, such users cannot query the Base Rate column using DAX or MDX, or measures that refer to it.
Further information on OLS is available in this blog post.
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 also introduced with the 1400 compatibility level. For more information, please refer to the Analysis Services Team blog for what's new in SQL Server 2017 CTP 2.0 and SQL Server vNext on Windows CTP 1.1 for Analysis Services.
- Transaction-performance improvements for a more responsive developer experience.
- Dynamic Management View improvements enabling dependency analysis and reporting.
- 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.
- DAX enhancements to make DAX more accessible and powerful. These include the IN operator and table/row constructors.
Try it Now!
To get started, simply create a 1400 model in SSDT and deploy it to Azure Analysis Services! See this post on how to create your first model. Be sure to keep an eye on this blog to stay up to date on Azure Analysis Services.