Since the release of Analysis services 2022 I never took the chance to explore its new features and improvements so in this post I will deep dive into each new and updated feature.

The public preview of SQL Server Analysis Services 2022 was released on May 2022 and at the time of writing this post, SSAS 2022 is still in public preview.

Analysis Services 2022: Why don’t we just migrate to Azure AS or PBI premium?

One might ask if it is still worth using an on-prem solution (or IASS), and the answer is always “it depends” but there are still some valid reasons for companies to stick with an on-prem/IASS solution.

• Cost-wise (this one really depends!)
• Memory and CPU limitation, you can allocate more than 400GB of memory on your physical server or virtual machine (since gen2 PBI manages the memory in a dynamic way but still…)
• Security (even if I tend to believe that the cloud is probably more secure than an on-prem solution…)
• No internal skills to move to the clouds
• Gouvernance, compliance, intellectual property, etc…

These are just some examples and I’m sure the list can go longer than that but of course, we can also argue these reasons.
Anyway, the purpose of this post is not to discuss cloud or not cloud but rather to emphasize that as of today there are still a lot of companies even large ones that heavily rely on SQL Server Analysis and thankfully Microsoft understood it and came up with this new release which for me a game changer for model and report designers.

What’s new in Analysis Services 2022?

Before jumping into the new features here are the two Microsoft links where you can find more information about the new release and keep up to date with new feature releases or cumulative updates:
The power BI Blog: What’s new in SQL Server 2022 Analysis Services CTP 2.0
The Microsoft learn doc: What’s new in SQL Server Analysis Services

So what’s new in SSAS 2022? According to Microsoft here are the newly available features:

New features and improvements in CTP 2.0

• The big game changer: Composite Models
• Improved MDX query performance
• Improved resource governance
• Query interleaving – Short query bias with fast cancellation

New features and improvements in RC0

• Horizontal Fusion
• Parallel Execution Plans for DirectQuery

Although there’s no information about it on the Microsoft side, there’s also great news for the DAX developers! All and I repeat All the new DAX functions or updated functions are supported in Analysis Services 2022!

Without further talking let’s review these new features together. Since I want to keep this post short enough I will only review the Composite Model and the new DAX functions and will write another post for the performance part. So this post is more intended for the PBI and SSAS developers while the other post will target a broader audience such as developers but also the DBA or infra administrators.

Composite Models

The composite models feature in SSAS is documented here: Using DirectQuery for Power BI datasets and Analysis Services (preview)

This new feature is now well known by the Power BI community and Azure AS, so no need to repeat the Microsoft documentation. I did some tests and as far as I can see all the limitations on PBI or Azure AS also apply to SSAS. Except for a few things that I detail below I did not notice any difference of course the only thing that differs is that you must configure a data source gateway for any model used in your composite model as long as they are SSAS models.

The great thing about this game-changer feature is that you can now create a report that combines tables from different models and no longer need to create silo reports for each department or duplicate data into every single model, this will save tons of time and simplify a lot the model creation. However, you should still make sure to follow the composite model guidance otherwise you may end up with over complex reports or slow reports.

Limitations and Bugs

Only Analysis Services 2022 models are supported

On top of the known limitations listed in Microsoft documentation, you have to bear in mind that only models deployed on Analysis Services 2022 are supported (Of course Azure AS and PBI are supported). So if you try to directly connect to a model deployed on an older version such as SSAS 2019 for example you will get the following error message:

So to fully leverage the composite model on your on-prem/IASS architecture you must migrate all your models to SSAS 2022.

Relationships with SQL Direct query mode do not work

This one sounds like a bug to me, in fact, if you try to create a relationship with an SQL Direct Query table, Power BI will crash and you will need to kill its process. I try to reproduce it using an Azure model with an SQL Direct Query table and it worked just fine.

Distinguish between local and different models

I tried to connect and link my Analysis Service 2022 model with 4 other models.

• Local Model: Light blue header bar (Since I use connect live connection, my local model is not actually on my laptop but on the SSAS server)
• SSAS 2022 Direct Query: Dark blue header bar
• Azure AS: Red header bar
• SQL Import: No colour and sometimes Yellow header bar (looks like a small bug to me)
• SQL Direct Query: Purple header bar

I have to say that being able to easily distinguish each model location by colour is a great feature.

DAX

As of now, there’s no information about the new DAX functions supported in SSAS 2022 so I will give a bit more details about it and present just a couple of new or improved functions.

As you already know each month there’s a new Power BI release and in some releases Microsoft introduces new DAX functions or sometimes adds improvements to existing DAX functions, some of the recently added or improved functions were not available in SSAS 2019.

So the first thing that I wanted to check after I installed Analysis Service 2022 (after the composite model of course) was to find out if all the recent new and updated DAX functions were supported in SSAS 2022.

And the short answer is a big YES but that’s not all! At the time of writing this post, there’s a hidden function in Power BI not yet released called Offset and it turns out that this function is also supported in Analysis Services 2022.
Until Microsoft officially releases this new function and related documentation you can find more about it in Marc’s blog post: How OFFSET in DAX will make your life easier

As Marc suggested this function is going to make writing DAX much easier so having this function also available in Analysis Services 2022 is great news for the DAX developers.

Let’s now deep dive into some of the new and updated DAX functions that I like the most and which will for sure make DAX coding easier.

Dax new functions supported

The full list of new functions documented by Microsoft can be found here: New DAX functions. In reality, there have been more functions added within the last few years but I’m guessing Microsoft is listing only the most important functions.

Calculate and CalculateTable

This is for me by far the biggest change in fact there are at least two improvements to the calculate function that makes DAX much easier to write.

Multiple filter conditions in Calculate:

If you were to write this function in SSAS 2019:

multiple filters in calculate = 
CALCULATE (
    sum(Sales[Net Price]),
        'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"
    )

You will get the following error message:

Surprisingly, I discovered that the following function was working in SSAS 2019

CALCULATE (
    sum(Sales[Net Price]),
       'Product'[Color] = "Red" ,'Product'[Brand] = "Contoso"
    )

But this exact same function was not:

CALCULATE (
    sum(Sales[Net Price]),
       'Product'[Color] = "Red" && 'Product'[Brand] = "Contoso"
    )

Anyway, they both work on Azure Analysis Services 2022 now.

In addition to that, another great improvement on the Calculate function is the ability to use an aggregation function in boolean filter expressions.

The following function DAX expression was not working on SSAS 2019 and works fine on SSAS 2022:

Total sales on the last selected date =
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    'Sales'[OrderDateKey] <= MAX ( 'Sales'[OrderDateKey] )
)

I only detailed the updates on the Calculate function but the same updates apply to the CalculateTable function and everything seems to work fine.

NETWORKDAYS

Another great function available is the Networkdays even though I prefer using my own Date table and maintaining it with the internal ETL process. This function has only been released recently (after the CPT2 release of analysis services 2022) so I did not expect to find it, but it is there. ( Maybe it was added in the RC0 update…)

Offset

This function was probably the one that I expect the least to be available since it’s not even fully released in Power BI yet but to my great surprise, it is also there.

I will not elaborate much on it since I already mentioned it above but all I can say is that having this function available means that at the time of writing this post Analysis Services 2022 is fully up to date with Power BI and Azure AS in terms of Dax functions.

Conclusion

In this part, I only described and reviewed the new features in Analysis Services 2022 related to the composite model and the support of the new DAX functions. I will write in a second post the review of the new features that are mainly related to the performance improvements.

So except for one bug that I found (there may be a few others not identified yet), I can say that for the DAX and composite model the SQL Server Analysis Services 2022 is now a complete equivalent to Azure Analysis Services so if you still have an on-prem/IASS solution make sure to check when this release will be available to General Availability as it comes with greats new features and improvements.

Of course, it is still lacking behind Power BI premium in terms of features but that’s another discussion.