Optimising a large switch measure - SSAS

[Anonymous]

Hi All, 

I'm a little new at dax optimisation, however I am building a few different reports off a SSAS (2012) tabular model which has a huge number of measures. In order to reduce the size of the reports I've used switch measures for Actuals and Budgets etc., however the switch measures now contains around 80+ measures within them, most of which have formatting applied to them using ROUND().

Now however, I'm running into performance issues and hitting memory limits on the server. It seems like even though it is returning one measure from within the switch measure using a slicer, it is still processing the subsequent measures and is very intensive on resources. 

Does anyone have experience of this and know of any method to mitigate this? I've already tried nesting a switch statement within a switch statement looking at sub goups within the report but not having any luck. 

I've done some Googling which reveals some issues with SSAS 2012 but thought there may be a way to run this without removing the flexibility of the switch measures?

Thanks,

[v-sihou-msft · Accepted solution]

@Anonymous'

I don't think there's any better way to optimize it for SSAS 2014 or earlier versions than the approach in that article. I suggest you upgrade it into SSAS 2016.

Regards,

[Anonymous]

We've got a proof of concept 2016 server now and it runs great even with a single CPU. I'd suggest the same to anyone else and move to 2016.

Thanks