0
votes

I am facing very strange issue with MDX (SSAS 2014), on which simplest calculated member is taking forever to execute. Could someone please help me to understand why i am facing this issue. If i not use calculated member everything works fast and result comes in seconds. When i remove Producer attribute, query performances well.

Below is the complete query.

WITH MEMBER Measures.AsOfDate AS ("[Policy Effective Date].[Year-Month].[Date].&[2018-01-04T00:00:00]")

MEMBER Measures.YTDPremium AS AGGREGATE (YTD(STRTOMEMBER(Measures.AsOfDate)), [Measures].[Written Premium]) SELECT NON EMPTY { Measures.YTDPremium

} ON COLUMNS, NON EMPTY { ( [Program].[Program Name].[Program Name]

,[Insuring Company].[Insuring Company Name].[Insuring Company Name]

,[Line Of Business].[Line Of Business].[Line Of Business]

,[Producer].[Producer Name].[Producer Name]

) } ON ROWS FROM [Premium]

1

1 Answers

0
votes

Try understand what the following part does in your query

NON EMPTY { ( [Program].[Program Name].[Program Name]

,[Insuring Company].[Insuring Company Name].[Insuring Company Name]

,[Line Of Business].[Line Of Business].[Line Of Business]

,[Producer].[Producer Name].[Producer Name]

) } ON ROWS

In the above MDX you are telling the server to take a cross product of all values of "Programs", "Line Of Business" and "Producer Name". So lets say you have 4 values of programs , 3 values of line of business and 2 values of producer name. The total combinations are 4*3*2=24

Now the "Non empty" removes any combinations that are not present in your dataset. This is done by removing all rows that have "Null" value in column value.

Your measure is returning value irrespective if that combination exists or not. You can modify your Calculatedmeasure to return value only in the case if the combination is valid. This can be achived by checking an actual measure for that combination

Edit: based the below example is based on the comment

In the below example i am trying to get the internet sales amount categories and components

select 
{ [Measures].[Internet Sales Amount]  } 
on columns,
(
[Product].[Category].[Category],
[Customer].[Country].[Country]
)
on rows 
from [Adventure Works]

Result enter image description here

Now add "Non empty" to the query and observe the results.

Results enter image description here

Now lets add calculted measure that returns "hello". Notice how the non empty clause is ineffective.

enter image description here

Now modify the code make the calculated measure check other measures for null

with member measures.t as 
case when [Measures].[Internet Sales Amount] = null then null else "hello" end

select 
{ [Measures].[Internet Sales Amount] ,measures.t }
on columns,
non empty
(
[Product].[Category].[Category],
[Customer].[Country].[Country]
)
on rows 
from [Adventure Works]

Result

enter image description here

The bottom line: Because of cross product your result is so huge that SSAS is having hard time handling it.