SSAS MDX Union Performance SQL 2019

Thomas Hafner 0 Reputation points
2023-02-28T13:17:04.08+00:00

Hello, we found out that the UNION function in SSAS 2019 has a 5-10 times worse performance than in the other versions. SQL2012: 8 seconds, SQL2014: 8 seconds, SQL2016: 8 seconds, SQL2017: 7 seconds, SQL2019: 42 seconds, SQL2022: 8 seconds. Here's an example:

SELECT {} on 0,
UNION(
  NonEmpty(DESCENDANTS([Dim1].[Hierachy1].[All], 3, self), ([Dim2].[Hierachy1].[Level1].&[Value1]
, [Measures].[Measure1]
))
,
  NonEmpty(DESCENDANTS([Dim1].[Hierachy1].[All], 3, self), ([Dim2].[Hierachy1].[Level1].&[Value2]
, [Measures].[Measure1]
))
,
  NonEmpty(DESCENDANTS([Dim1].[Hierachy1].[All], 3, self), ([Dim2].[Hierachy1].[Level1].&[Value3]
, [Measures].[Measure1]
))
,
...
,
  NonEmpty(DESCENDANTS([Dim1].[Hierachy1].[All], 3, self), ([Dim2].[Hierachy1].[Level1].&[Value100]
, [Measures].[Measure1]
))
) on 1
FROM [Cube1]
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,967 questions
0 comments No comments
{count} votes