Slow Refresh Power Pivot cube formula

[PeteSmith6730]

Newbie here with a slow spreadsheet refresh issue.

I have a powerpivot spreadsheet with a lot of cubevalue and cubeset formula which seems very slow to refresh

Data model is:

Fact table - 200,000 rows 8 columns (non calculated)

Dimension Table 1 - 219 rows 15 columns - linked to fact table

Dimension Table 2 - 1100 rows 17 columns - linked to fact table

Date table - shrunk back to include only 1st of month across 7 years - so 84 rows 12 columns

So not terribly huge.

I then have 3 spreadsheet tabs with in total 28,000 cube value formulae of which 8,000 are referencing cubeset formulas.

There are 280 cubeset formula. I need to use cube formula rather than pivot tables to get the presention right.

In order to reduce the number of measures I am using cubeset formula to reduce the dataset to the required data for the calculation.

I am working with the minimum number of slicers, ie 4.

However the refresh speed is about 5 minutes which is far too long? So could somebody suggest how I might speed this up?

My thoughts/questions/fixes are:

1. Create a lot of meaures to replace the cubeset formula. (long job and might not fix)

2. Just write standard excel formula to replace the cubeset formula. Many of these are working out per unit, mix, percentage calcs and the data is in the worksheet from another cubevalue formula. So could use standard excel and I might be working under the misbelief that a cube lookup is quicker than an excel formula

3. Bring the measures into the pivot table on which the slicers are linked to. This could be quicker since data already in pivot table and the xl cube formula doesn;t have to carry on its journey to the cube? is this technically correct?

4. The refresh seems to making multiple trips to the data model to do the job. How do I stop it doing this?

5. I have read some google about changing the Dax (or MDX query) which feeds the pivot table, but I have no idea how to do this and what would that query text look like to cover all those cubeset formula i one sweep?

So  I have some ideas myself but as a newbie I have no idea which route to go down or whether there are any other options to explore. Please could somebody help a dissappointed newbie!!

[v-juanli-msft]

Hi @PeteSmith6730

For question1,2, you have a correct understanding by yourself.

Let us come to the next three questions:

3: I'm afraid applying slicers can't be helpful.

4: you could consider the fuction "Enable load", "Include in Report refresh", reference here.

5: How to change the Dax (or MDX query) which feeds the pivot table should be done, as appropriate.

Generally, you could refer to the DAX reference.

https://msdn.microsoft.com/en-us/query-bi/dax/data-analysis-expressions-dax-reference

Best Regards

Maggie

[PeteSmith6730]

Thanks for assist Maggie.

I think I may revert to standard excel formulae where poss and see how I go with that.

I may also break the workbook into 3 - 1 for each tab.

I looked at your Enable Load link, but this refers to power BI and I'm working in power pivot.

Is there similar functionality in Power Pivot and how do I find it?

Assuming I can write the correct DAX (newbie is doubtful!), where do I enter the DAX code to refresh the Pivot table.

I think the refresh is performing one trip to the data model to get the bulk of what I need and then separate trips for each of the cubeset formula. How does Dax look to make one trip?

Pete