Power Pivot data model slow refresh

[jkapso751]

Hi all,

I have a data model in Power Pivot that takes a very long time to update whenever I make small changes.  It consists of two fact tables; one with 190K rows and another with 1.2K rows.  Probably around 10-12 dimension tables.  I have a few calculated columns in some of the tables (6 in the main dataset and 4 in the smaller one), but I'm trying to rely more on measures (model has maybe less than 100, mostly simple CALCULATE functions).  I'm using some numbers in the dimension tables that I'm using in some of the calculations.  For example, I have one table that has the following:

Month/Month#/Num1/Num2

April / 4 / 1 / 3

May / 5 / 2 / 3

etc...

As my fact table also has the month value(s), I've used a RELATED function in a calculated column to bring in Num1 and 2 from the dimension table for a simple calculation (division).

However, if I make a small edit (for example, change the name of a measure), it takes at least one minute for the pivot table to fully update.  It gets stuck on "Reading data... (Press ESC to cancel)."

I tried running a trace in DAX studio and the only thing I can see is that the queries beginning with "SELECT NON EMPTY CrossJoin..." are the reason why the updates are taking so long (75,000 ms).  Not really sure what this means.

I also brought another fact table into the model (176K rows) but I'm not using it in any of the calculations.  I had it appended to the main fact table before but I removed that step in Power Query to see if it would make a difference (and it really didn't).

If anyone can help, I would definitely appreciate it.  Thanks and God bless.

[jkapso751]

Hi some_bih,

Thanks for replying to my post, appreciate your feedback.  I tried clearing cache but that didn't help unfortunately.  I ended up splitting the model into two files, one for each fact table.  Doing it this way helped me to see that the problem was with the model linked to the smaller fact table.  Still haven't figured out the exact root cause of the problem though, but I can work on the smaller fact table model later.

I'll check to see if I'm allowed to download the diagnostic tool and see if that helps.  I'll also check if working in PBI makes a difference.

Thanks again!

[some_bih]

Hi @jkapso751 I like to use power pivot, but at some point of time (model size) it crash and it makes me crazy. It could be that in your case the workbook and model reach similar point.

Firstly, try to delete query "clear cache" in Data>>>Query options. Check results

What I did is simple imort model into Power BI and continue work there - this is good alternative. 

To trace diagnostic as much as possible, check your model with tool from link 

https://www.sqlbi.com/tools/power-pivot-utilities/ 

Did I answer your question? Mark my post as a solution! Kudos Appreciated!