I noticed that Microsoft is continuing to improve the MDX query language on Power BI. Eg. they have features called "MDX Fusion" that was released relatively recently. While Power BI also has the DAX queries, the MDX ones are still very important for data analysis, and they are used in various places, like Microsoft Excel.
... MDX is also used for "import" mode queries when pulling data into Power Query from any olap source (SQL Analysis Services or Azure Analysis Services).
Despite the improvements, there are some performance issues in MDX that I'm having trouble with. I cannot seem to influence the performance of my custom measures, with the help of "NON_EMPTY_BEHAVIOR". Take the following query for example:
WITH
MEMBER [Measures].[NewSalesEmployeeId]
as Iif(IsEmpty([Measures].[Units Sold]), NULL, "000000")
, NON_EMPTY_BEHAVIOR=[Sales].[ValUnits]
SELECT
{{
[Measures].[Units Sold]
,
[Measures].[NewSalesEmployeeId]
}}
ON 0,
NON EMPTY
[Branch].[Branch Code].[Branch Code] *
[Customer].[Customer Code].[Customer Code] *
[Product].[Product Type Code].[Product Type Code] *
[Product].[Descriptor - Species Code].[Descriptor - Species Code] *
[Product].[Descriptor - Grade Code].[Descriptor - Grade Code] *
[Product].[Descriptor - Grade Name].[Descriptor - Grade Name] *
[Product].[Descriptor - Size Name].[Descriptor - Size Name] *
[Product].[Descriptor - Thick Name].[Descriptor - Thick Name] *
[Product].[Product Code].[Product Code] *
[Product].[Length].[Length] *
[Time].[Fiscal Day Value].[Fiscal Day Value]
ON 1 FROM ( SELECT [Time].[Time F].[Week].[Y 2022 W 10] on 0 FROM (SELECT [Branch].[Branch Code].[Branch Code].[B123] on 0 from [Sales Budget and Activity]))
This takes far too long.
It clocks in at ~3422 ms in profiler. See below.
The use of NON_EMPTY_BEHAVIOR was a "plan B". My goal when I introduced the NON_EMPTY_BEHAVIOR on [Measures].[NewSalesEmployeeId] was simply to improve performance. I only want to evaluate the member if the units are available for the tuples in question.
Please note that if I simply remove [Measures].[NewSalesEmployeeId] from my query then the it takes only ~80 ms or so. See below. This is the approximate timeframe I would expect in every permutation of my query. The final results consist of only about ~1000 rows. IMHO, There is no reasonable scenario where this query should take ~3422 ms to return ~1000 rows, especially if the only thing I need is a few additional measures (ones that are directly related to "units sold").
In every case I'm testing with warm cache, on a Power BI model in premium P1.
Is there any hope to get an MDX query working in a way that pays respect to my NON_EMPTY_BEHAVIOR? Is there any other technique to improve the version of my query that has a custom calculation in my "Measures"?
It is a bit disappointing that the NON_EMPTY_BEHAVIOR feature seems to be ignored. That feature always been a low-tech and simple way to improve performance when all else fails. I do understand that MDX is not the primary language of PBI tabular models, but it is still an important query language nonetheless. And we need it to be fast.