Applying Queries Taking Long (design or lack of resources)?

[DarthTim]

I'm having issues with a PowerBI report I have created and was wanting to get some advice. When applying the query changes, it’s taking an extremely long time to complete, with the process status for each query as either ‘Waiting for other queries’ or ‘Evaluating…’

I have tried to apply the changes four times over the past few days. The first three attempts I cancelled after 2 hours when I got messages that BI was not responding. The latest attempt finally completed today after running for 3 hours. I wanted to get input on whether this was a hardware issue, a design issue, or both. Here are the details:

BI Report Data Sources:

• The amount of data running through this is fairly minimal, but it is coming from a lot of data sources.
• The source to the report is about 50 different Sharepoint Online lists. Each list has about 5 columns, and between 3 and 4 rows on each list (a new row will be added each month). I’m running about 3 ETL steps on each table to pivot the data, then add two new columns. It seems to be working through these fairly quickly.
• From the 50 sharepoint lists I’m ingesting, after the steps I mentioned above, I append these into a single table. I believe this is where the resources are running into problems. This is appending the 50 sharepoint lists into a single table, and then running about 5 steps where I add extract columns. This is the table I’m doing my reporting off of.

BI Report Settings:

• I have disabled ‘Enable parallel loading of tables’ (Options --> Data Load --> 'unchecked' Enable parallel loading of tables)
• I have the list of tables in the query editor with the smaller 50 sharepoint list tables first, and then at the end the data append table which is appending all 50 tables. It appears to be applying the queries in this order.

Laptop Specs:

• The details of the laptop I’m running this on are as follows:

OS Name:       Microsoft Windows 10 Enterprise

Total Size on C:       236.54 GB

Total Free on C:       61.06 GB

System Memory: 7.88 GB (ESTIMATED)

Total Physical Memory:     8,073 MB

Available Physical Memory: 2,733 MB

Virtual Memory: Max Size:  31,100 MB

Virtual Memory: Available: 21,368 MB

Virtual Memory: In Use:    9,732 MB

Processor(s):  1 Processor(s) Installed. [01]: Intel64 Family 6 Model 142 Stepping 10 GenuineIntel ~1696 Mhz

Questions:

1. Does this sound like a design problem, a too few resources for the laptop I’m using, or a bit of both?
2. Are there any other settings I could use to try and help with this?
3. I know I didn’t explain things out too much, but are there any obvious design tips I’m missing?

Any advice would be appreciated.

Thanks.

[v-angzheng-msft]

Thank you for sharing 😀

[DarthTim · Accepted solution]

After various googling and testing, I tried the following which seem to have helped with performance quite a bit. 

1) I copied the report off a remote file share where it resided, and placed it on my local drive. After loading the data, saving, etc., I then copied it back out to the remote file share where I would publish it from. 

2) I disabled privacy settings. I chose to ignore these. I felt comfortable doing this since there is no PI used in our data sources.  From what I could read, this will allow for a small performance increase. 

3) In query editor, I unchecked 'Enable Load' on all of my sharepoint lists. I will now only be loading the appended table to my report. I believe this helped significantly. Here is an article explaining:

https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up

4) I cleared the cache.  I had this report crash on me the other day. I was reading that if this happens, bad data can still remain in the cache and will only fully clear out once you close BI. I think this was being brought back in when trying to use the auto recovery files and could have been contributing to the problem. Of course I have shut down BI, rebooted etc, but just in case it was still in the cache I thought it best to clear that out. 

After performing all of the above, the apply and load was much faster and it appears this has done the trick. I need to confirm that all the sharepoint lists will still refresh and update as expected, but so far, everything seems to be working much better.