Slow Report Runtime with TopCount/Optional Slicers

[Corrigon]

Hi there, I am hoping someone can provide me with some advice to resolve some performance issues I am experiencing with a report I am building. Profuse apologies in advance for the length of the post: I am new to dimensional reporting and really am not sure where the problem lies, so I'm listing all of the component parts.

Source is MSAS cube on CRM data.

My report is 2 pages. I have 3 mandatory slicers and 13 optional ones.

Page 1 is two crosstabs. Rows are 'Region' and 'Country' levels respectively.
Column is a calculated item ('Months') based on a prompt for the level Financial Month (to a data item called 'PromptMonth'), and a prompt for an integer to populate in the lastPeriods function (so the user picks the month, then the number of months prior to that so that the end result is a range of months.) The measure is 'Value'.

The 13 optional slicers are displayed in a table grid above the Crosstabs. There is a Reprompt button for the user to make selections and rerun the report.

All slicers use a variation of the below expression:

#promptmany ('pProdGroup','memberuniquename','rootMember ([Sales Cube].[Stock].[Product Group])','set(','',')')#

The rootmember is the All member. Each slicer prompt is linked to a separate Query which is based on the All member (prompt use value) and a data item children(All) (prompt display value).

Runtime on this page with no other prompt values on the optional slicers = 15 seconds, which is acceptable.

Page 2 is also two crosstabs. Rows are calculated items 'Product Rank' and 'Customer Rank' respectively. 'Customer Rank' also has the nested level 'Billing Type'. it is a requirement of the report that all of the optional slicers are also displayed on the second page in the same manner as the First Page. This has been accomplished by copying them from one page to the other.

Measure is 'Value', plus some calculated columns. First column is the calculated item 'Months' mentioned above. Adjacent to this are the calculated columns, 'Period Average', 'Period Total', 'Annualised Amount (from Chosen Month)' and 'First/Last Period Movement'. they are calculated as follows:

• 'Period Average' = (average(currenMeasure within set [Months])
• 'Period Total' = (aggregate(currenMeasure within set [Months])
• ''Annualised Amount (from Chosen Month)' = (tuple([Value],[PromptMonth],currentMember([Sales Cube].[Customer Sales].[Product]))* 12) 
• 'First/Last Period Movement' = ((tuple(currentMember([Sales Cube].[Customer Sales].[Product]), [Value], [PromptMonth])) - (tuple(currentMember([Sales Cube].[Customer Sales].[Product]), [Value], lag([PromptMonth],((?MonthsPast?) - 1)))))

'Product Rank' and 'Customer Rank' are based on a combination of TopCount (with the integer being populated by a data item called 'Rank', which is linked to a prompt so that the user can select how many results they wish to see, e.g. top 5, top 10, top 250, etc.)

Both 'Product Rank' and 'Customer Rank' use as their ranking criteria: a tuple of CurrentMember of their respective hierarchies, and the PromptMonth data item (so that the ordering of the ranking is set by the Product/Customer with the highest 'Value' for the chosen Month (e.g 'PromptMonth'.)

Both 'Product Rank' and 'Customer Rank' use a filter expression, so that only Products/Customer which have a 'Value' in PromptMonth will be shown in the crosstab. The full 'Product Rank' expression is below:

filter(topCount(children([All]),[Rank], tuple([Value],[PromptMonth])), tuple(currentMember([Sales Cube].[Customer Sales].[Product]), [PromptMonth], [Value]) is not null)

When I run the report, moving from Page 1 to Page 2 with Rank of 250 and no optional slicers selected takes 2 minutes. I'm baffled by this as I don't think that the 'Product Rank' and 'Customer Rank' items are all that complex. But I can't rule them out as being a cause of the problem as they are different to the Rows in Page 1.

I think the most likely culprit is the sheer number of Optional Slicers. I suspect this because when I remove them from the main report query (leaving just the mandatory slicers) Page 1 to 2 takes 32 seconds. But why does this occur only on page 2, and not page 1?

So - does anyone have any suggestions as to why this is occurring? If it is the slicers as I suspect, can I make them more efficient? Or is there something in my TopCount rows or calculated expressions that could be causing it? Or something else entirely?

Thanks in advance for any help offered, and apologies for the wall-o-text. Posting this was my last resort to try and get this report over the line.

Cheers,
Dave

[Corrigon]

UPDATE - I sort of solved this problem by completely rebuilding the report from scratch and splitting Page 1, and the two crosstabs into separate reports. So, where I had 1 I now have 3. Doing this meant that not all slicers were relevant for the respective reports, so I was able to reduce the overall number on each. Not ideal but I was out of ideas.

Upshot is the Product Report runs at 55 seconds for a 7 month range and rank of 250. The Customer report runs at 48 seconds for the same parameters.

The lesson learned for me is that if you are using optional slicers (especially if they you are intending to display them on the report page so they can be re-prompted) that there will be a performance hit depending on the size of the slicer sets, and the number of slicers you are displaying.