# Dynamic Cube Set Really Slow

#### cwd123

##### New Member
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)

Chris

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?

Hi ImkeF

X4 is a vlookup from a dropdown box.

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.
• 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)

Last edited:

Replies
11
Views
12K

1,203,145
Messages
6,053,746
Members
444,681
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back