What’s new for SQL Server 2019 Analysis Services CTP 3.0

This is the first blog post dedicated to Analysis Services on the Power BI blog. As discussed in this post on the old Analysis Services team blog, Microsoft is moving off MSDN and TechNet blogs. We have made it clear that Power BI will be a one-stop shop for both enterprise and self-service BI on a single, all-inclusive platform. Power BI will be a superset of Analysis Services, so there is no better place to announce future Analysis Services features than here on the Power BI blog. The Analysis Services engine is at the foundation of Power BI and powers its datasets, so this just feels right.

The calculation groups feature was announced for SQL Server Analysis Services 2019 in the CTP 2.3 blog post. CTP 3.0 provides enhancements on top of calculation groups. Calculation groups will soon be officially launched and supported in Azure Analysis Services and Power BI Premium through the XMLA endpoint.

CTP 3.0

We are excited to announce the public CTP 3.0 of SQL Server 2019 Analysis Services. This public preview includes the following enhancements for Analysis Services tabular models.

· Dynamic format strings with calculation groups

· Calculation group support for MDX queries

Dynamic format strings

Until now, tabular models in Analysis Services and Power BI have supported dynamic formatting of measures using the FORMAT function. The FORMAT function has the disadvantage of returning a string, forcing measures that would otherwise be numeric to be returned as strings. This causes limitations such as not working with most Power BI visuals depending on numeric values like charts, etc.

Dynamic format strings with calculation groups allow conditional application of format strings to measures without forcing them to return strings.

Dynamic format string example for time intelligence

Please refer to the time-intelligence example provided in the CTP 2.3 blog post. All the calculation items except YOY% should use the format of the current measure in context. Sales YTD should be currency; Orders YTD should be a whole number. YOY% however should be percentage regardless of the format of the base measure.

For YOY%, we can override the format string by setting the format string expression property to “0.00%;-0.00%;0.00%”. For more information on the sections of format strings in Analysis Services, please see this document.

The following matrix visual in Power BI shows that Sales Current/YOY and Orders Current/YOY retain their respective base measure format strings. Sales YOY% and Orders YOY% however override the format string to use percentage format.

Dynamic format string example for currency conversion

Currency conversion takes dynamic format strings to the next level. Consider the following Adventure Works data model. It is modeled for “one-to-many” currency conversion as defined by this document.

The FormatString column is added to the DimCurrency table and populated with format strings for the respective currencies.

This download file contains sample format strings.

The following calculation group is defined.

"No Conversion"

SELECTEDMEASURE()

"Converted Currency"

IF(
    //Check one currency in context & not US Dollar, which is the pivot currency:
    SELECTEDVALUE( DimCurrency[CurrencyName], "US Dollar" ) = "US Dollar",
    SELECTEDMEASURE(),
    SUMX(
        VALUES(DimDate[Date]),
        CALCULATE( DIVIDE( SELECTEDMEASURE(), MAX(FactCurrencyRate[EndOfDayRate]) ) )
    )
)

SELECTEDVALUE(
    DimCurrency[FormatString],
    SELECTEDMEASUREFORMATSTRING()
)

The format string expression must return a scalar string. It uses the new SELECTEDMEASUREFORMATSTRING() function to revert to the base measure format string if there are multiple currencies in filter context.

The following visual shows the dynamic format of the Sales measure.

Previously, using the FORMAT function, it was not possible to switch visual type to a chart and display the values. Using dynamic format strings with calculation groups, we can.

MDX query support for calculation groups

In previous CTPs, calculation groups were not enabled for MDX queries. Now they are, so clients such as Excel can benefit.

Tooling

Calculation groups are currently engine-only features. SSDT support will come before SQL Server 2019 general availability. In the meantime, you can use the fantastic open-source community tool Tabular Editor to create calculation groups. Alternatively, you can use SSAS programming and scripting interfaces such as TOM and TMSL.

New 1470 compatibility level

To use calculation groups, existing models must be upgraded to the 1470 compatibility level. 1470 models cannot be deployed to SQL Server 2017 or earlier or downgraded to lower compatibility levels.

Download now

To get started with SQL Server 2019 CTP 3.0, find download instructions on the SQL Server 2019 web page. Enjoy!