Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
schaema3
Advocate III
Advocate III

Queries breaking

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!

 

 

1 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! 

View solution in original post

4 REPLIES 4
Nickgastaldi
Resolver I
Resolver I

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

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.

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.