Queries breaking

[schaema3]

Newly I am facing problems when working on a report that used to work without problems. I am receiving the following error randomly on different raw data files:  Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. I've been facing also huge performance problems latelty. Sometimes BI eats up all my resources so I can't even open the task manager to stop it. Is my report getting too big? I'm using more than 10 million lines. Or is there a problem with the latest November release? Does anyone else face simialr problems? Thank you for your help!

[Nickgastaldi]

hello @schaema3

what kind of datasource are you using?

10 million lines aint a huge dataset, but it also depends on data type (string for eg. are memory eaters) , connection type, and iteration type you are using.

if you are using IMPORT MODE all the processing will happen locally so its expected that it eats up your resources, if you are in direct query mode, some DAX cant be directly translated to DB, so it background imports the table to your machine , and it uses memory instead of disk so depending on your tables this can also be very consuming.

but as i mentioned 10m aint a lot, i have dashboards with over 200m lines in fact tables and i am using a fairly poor machine (2cpus, 8gm ram) and everything works fine, so i guess to help diagnostic performance impact it would take a little understanding on what you are doing and what you have 🙂

try to avoid things like intensive cross filtering, many-to-many join , unencessary columns in data entry, grouping with high granularity column , all that can be a huge improvement.

sorry for being so generic 😛 its a starting point i guess

[schaema3]

Many Thanks Nickgastaldi, I am mainly using Excel tables pulled out of dedicated folders. The report is complex as I need to relate in total 27 different files (9 files with lookup tables, 6 raw data source files and 12 files for allowing appropriate slicing across the reports). What's most weird is that sometimes it works and other times not. When I am rebuilding a query that the error is indicating I am automatically running into the next. I've now cleared the cache and re-booted my PC (that is quite a powerful one) and will give it another trial. Many to Many relationships I am avoiding usually as I know that this can cause issues.

[v-piga-msft]

Hi @schaema3,

This blog will be helpful for you.

In addition, if your data source is very large or the queries are complex will affect the performance of your report.

For reference about Power BI performance, you could have a good look at this document Power BI Performance Best Practices.

Best  Regards,

Cherry

[schaema3 · Accepted solution]

Thank you @Nickgastaldi, I think I found the problem. One of the issues was that I am running 32bit Excel on my computer but the server had a 64bit Excel version installed. When changing the server version to 32bit the query started to run but froze at one point. When going one by one through the almost 50 different raw data files I noticed that with moving the files from my local computer to the server about a handful fell into 'protected view'. When resolving the 'protected view' it worked. Thank you again for your help!