With the release of CTP 2.3 of SQL Server 2019 today there was big news for Analysis Services Tabular developers: Calculation Groups. You can read all about them in detail in this blog post:
In my opinion this is the most important new feature in DAX since… well, forever. It allows you to create a new type of calculation – which in most cases will be a time intelligence like a year-to-date or a previous period growth – that can be applied to multiple measures; basically the same thing that we have been doing in SSAS Multidimensional for years with the time utility/shell/date tool dimension technique. It’s certainly going to solve a lot of problems for a lot of SSAS Tabular implementations, many of which have hundreds or even thousands of measures for every combination of base measure and calculation type needed.
I’m not going to repeat any of the detailed technical information in the blog post here, though. Instead the point I want to make is that this is very big news for SSAS Multidimensional users too. In the past couple of years many people who have existing SSAS Multidimensional implementations have thought about migrating to SSAS Tabular so they can take advantage of its new features or move to the cloud, and indeed many of them have already migrated successfully. However, up to now, the biggest blocker for those wanting to migrate from Multidimensional to Tabular has been the fact that some complex calculations that can be expressed in MDX cannot be recreated (or recreated easily and efficiently) in DAX, because DAX has not had an equivalent of calculated members not on the Measures dimension or the MDX SCOPE statement.
Calculation groups do not remove this roadblock completely, but they do remove the roadblock for a large group of existing SSAS Multidimensional users whose only complex calculation requirement is a time utility/shell/date tool dimension. As a result these SSAS Multidimensional users will now be able to migrate to SSAS Tabular 2019, Azure Analysis Services or Power BI if they want to. Only those people who have more exotic uses for calculated members not on the Measures dimension (which are not very common at all) and those who use SCOPE statements (a larger group – many people working with financial data use SCOPE statements heavily) will find that Multidimensional is still the correct platform for them.
This is awesome, but this statement worries me: “New features detailed here are planned to ship later in Power BI Premium and Azure Analysis Services.”
There seems to be a trend underway where Power BI Premium is not going to be different from Pro based on capacity alone, but also in terms of functionality (e.g. incremental refresh).
I agree we need these features in Azure Analysis Services quickly as well. I’ve posted the same question on the MS Blog Chris references.
The intent is that modeling features will work in both Pro and Premium.
Calc groups won’t (eventually) be limited to Premium — theoretically, models with calc groups could also deploy to Pro.
But PBI Desktop has no near term plans to let you build calculated groups in the UI, so the only way you could get this capability would be if you used the upcoming XMLA endpoint to alter the dataset — and that feature is limited to Premium (due to technical/architecture limitations that make it impossible in Pro).
So this is an implicit restriction rather than an explicit prevention…
That IS big news! It’s a much needed feature.
Hi, nice update! On a different note, any do you have update SSAS MD as PaaS in Azure? I remember in SQLBITS 2018, Christain Wade from Microsoft mentioned it was coming…
No news, I’m afraid 🙁
Heartfully agree with Chris, this is by far the most important missing feature in Tabular model comparing to multi-dimensional model and good to see it’s finally available in tabular model (though probably not entirely).
Question, can/will the two calculation group to be compounded?
e.g. Calculation Group 1 : Current, MTD, QTD, YTD
Calculation Group 2: Current Year, Last Year, Next Year
==> Calculation Group 1 * Calculation Gorup2 at 2018 March: Current Year MTD, Last Year MTD, Next Year MTD, Current Year YTD, Last Year YTD…etc