Performance hit 4r the report that display 13 week avg SSAS cube is a datasource

[xyz]

Hi All,

I have a requirement to show a report for 13 weeks data including current selected week, fiscal week is the prompt. In the report basically I have a Employee dimension and Labor Hours and Absent % two measures are present. In these two measures(Labor Hours and Absent %), there are null values as well as 0 values as well as there are 300 plus records of employees. I have to find the display the average of Absent % measure and sum of Labor Hours measure. Can you please let us know, how will I improve the performance as the report is taking 1 minute some 10 seconds. I am using dimensional functions to achieve it.

I am using the below expression to suppress the nulls rows as well as rows with values greater than 0 by applying details filters, I know this is against dimensional rules in OLAP.  I am suspecting this is causing the performance hit. Can you please let me know, what all the other options I can try to improve the performance.

List

Employee   Actual Labor    Absent %
A               4                    1
B               8                   
C               0                   
D               9                    3
                   

13 Weeks Date data item:

lastperiods(13, [Cube].[Dt].[Hierarchy - Fscl Year].[Dt Fscl Week]->?p_date?)

Absent % data item calculation below.

total([Absent %] within set [13 Weeks Date])
/   total(
if(
tuple(CurrentMember([Cube].[Date].[Hierarchy - Fscl Year]),[Absent %])>0) then (1) else (0)

within set [13 Weeks Date])

Labor Hours dataitem expression below:

total([Labor Hours] within set [13 Weeks Date])

To suppress the nulls and display records greater than 0, I am using the detail filters, but excuse me, as I have no other option.

[Absent %] > 0 and [Labor Hours] > 0

If I try to use dimensional functions then only on one measure only I can apply the below expression.

filter([Employee], [Absent %] > 0)

Can someone please help me, how will I improve the performance as well as avoid applying detail filters?

Your help will be much appreciated :)

Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone please suggest my with some idea and guidance as it is something critical, your help is much appreciated :)



Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone please share some suggestions on this post?


Your help is much appreciatred?

Thanks & Regards,
XYZ