SQL Server Data Tools support for 1500 compatibility level
Published date: October 04, 2019
We are pleased to announce that SQL Server Data Tools (SSDT) for Analysis Services now supports tabular models with 1500 compatibility level. The 1500 compatibility level is required to take advantage of the latest features for tabular models in Azure Analysis Services. You can download the latest SSDT VSIX for Visual Studio from the VS Marketplace.
To create a new tabular model project in SSDT, specify the 1500 compatibility level upon project creation. You can also upgrade an existing tabular project to 1500 in the properties window for the BIM file.
Calculation groups
Calculation groups address the issue of proliferation of measures in complex BI models often caused by common calculations like time-intelligence. Analysis Services models are reused throughout large organizations, so they tend to grow in scale and complexity.
Here’s a link to the official documentation page for calculation groups: https://aka.ms/CalculationGroups. It contains detailed examples for time-intelligence, currency conversion, dynamic measure formats, and how to set the precedence property for multiple calculation groups in a single model. We plan to keep this article up to date as we make enhancements to calculation groups and the scenarios covered.
To create a new calculation group for a tabular model in SSDT, go to the Tabular model Explorer and right click the Calculation Groups folder. You can then add calculation items and set their expressions in the DAX Editor.
Use the Ordinal property to control the ordering of calculation items. For this, you need to add a second column and use it as a sort by column, which is set automatically upon creation of the 2nd column.
Many-to-many relationships
Many-to-many (M2M) relationships allow relationships between tables where both columns are non-unique. A relationship can be defined between a dimension and fact table at a granularity higher than the key column of the dimension. This avoids having to normalize dimension tables and can improve the user experience because the resulting model has a smaller number of tables with logically grouped columns.
