cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
schaema3 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
schaema3 Frequent Visitor
Frequent Visitor

Re: Queries breaking

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 Regular Visitor
Regular Visitor

Re: Queries breaking

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 Frequent Visitor
Frequent Visitor

Re: Queries breaking

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.

Community Support Team
Community Support Team

Re: Queries breaking

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.
schaema3 Frequent Visitor
Frequent Visitor

Re: Queries breaking

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors