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

Does Power BI import everything before aggregating with Direct Query?

Hi Everyone,

 

I'm on Power BI desktop using Direct Query to connect to a database on Amazon Redshift.

 

I'm getting an issue when I try to get the count of a column from one table grouped by the values of another table. 

 

When I write the query on redshift it returns ~70 values with the count of the first column next to it. When I try to do this in Power BI however, I get an error saying 

 

Error Message:
The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

 

My question is, how does aggregation work with Power BI? If the query it's sending Redshift already has a group by in it, I shouldn't be running into this error. Is Power BI really importing all of the values from both tables before attempting to group them?

 

If so is there a way to work around this?

 

Thank you,

 

Nikhil

1 ACCEPTED SOLUTION

Accepted Solutions
NikhilC Frequent Visitor
Frequent Visitor

Re: Does Power BI import everything before aggregating with Direct Query?

It looks like the issue was with the complexity of the Query Editor operations? I removed one of the steps which was "remove column" that someone added and it works now...

 

I did find this in the documentation to support it:

 

"If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor"

 

Not too sure at this point why the remove column step was problematic but I can work with this. Hopefully this'll help others if they run into this issue.

 

I appreciate your help @natelpeterson !

View solution in original post

4 REPLIES 4
Super User II
Super User II

Re: Does Power BI import everything before aggregating with Direct Query?

@NikhilC - Is there a Relationship defined between the 2 tables? 

NikhilC Frequent Visitor
Frequent Visitor

Re: Does Power BI import everything before aggregating with Direct Query?

@natelpeterson  Yes there is, I just checked to make sure.

Super User II
Super User II

Re: Does Power BI import everything before aggregating with Direct Query?

With DirectQuery, the DAX needs to be translated into the data source's query language (SQL). So you could run a trace on the source DB to find out what SQL is being run. 

What is the DAX of the Measure used to count rows? And what is the SQL that it is converted into?

 

NikhilC Frequent Visitor
Frequent Visitor

Re: Does Power BI import everything before aggregating with Direct Query?

It looks like the issue was with the complexity of the Query Editor operations? I removed one of the steps which was "remove column" that someone added and it works now...

 

I did find this in the documentation to support it:

 

"If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor"

 

Not too sure at this point why the remove column step was problematic but I can work with this. Hopefully this'll help others if they run into this issue.

 

I appreciate your help @natelpeterson !

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

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