Dynamic Cube Set Really Slow

[cwd123]

Hi this is my first post, I have just started using cubedata and am trying to get some top 10 info based on different measures It needs to be dyanmic by week. What I have works but is incredibly slow is their a more effieicent way to write my query, Otherwise I may have to go back to using the pivot tables and populating a dashbaord that way.
any help appreciated:

The cubeset:

=CUBESET("Main Cube","topcount([Customer].[Username].children,10,sum(([Date].[Fiscal Date Hierarchy].[Fiscal Week].&["&$X$4&"],[Product].[Product Vertical].[Product Vertical].&[1]),[Measures].[turnover]))","top 10 turnover")


(where x4 is the ficsalweek date)

The cuberankedmember:

=CUBERANKEDMEMBER("Main Cube",$C$21,$R10)

(Where c21 is the cubeset and r10 is the rank to return)

=CUBEVALUE("Main Cube",D$6,$A25,$P$9,$I$6)

(where d6 is the measure (turnover), a25 is the username retutned by the the cuberankedmember, p9 is channel and i6 is the date)

Many thanks in advance
Chris

 

[ImkeF]

Hi Chris,
how are all these cells filled you're referring to in your formulas (i.E. X4 here:Date].[Fiscal Date Hierarchy].[Fiscal Week].&["&$X$4&"])? :
"Harvested" slicer selections, fixed formulas or will the users fill in values there manually?

 

[cwd123]

Hi ImkeF

X4 is a vlookup from a dropdown box.

 

[ImkeF]

Hi Chris,
if you could use a slicer instead of the dropdown-box, this alternative would probably give you the improved performance:

You replace your cubeset-function by a hidden pivot that will deliver the Top10 Usernames. Therefore you start on a new sheet that will be hidden later. In your case:

• Drag the field you’re after into the row section of your pivot: Username
• Drag the Measure into the value-section: Turnover
• Set report filters for fixed filter: Product Vertical
• Insert slicer for interactive filter: Fiscal week date. Move this slicer to the sheet where it shall be used.
• Apply your Top-10 Filter
• Select the area where your usernames are shown and give it a nice name: TopTen. Then hide this sheet.

So what before you’d have gotten from the CUBERANKEDMEMBER-function will now be delivered by this formula: INDEX(TopTen, $R10)