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

Refreshing report using Mixed Mode (Import Mode, Direct Query and Dual Mode) and Aggregations

Hello all,

 

I created a report that has these Tables:

- "Sales" (all Sales detail by Date) - using Direct Query Mode,

- "Sales Agg" (Same as "Sales" but aggregated by Year and Month) - using Import Mode

- Some dimension tables - using Dual Mode

 

Sales and SalesAgg are, each, linked with the corresponding dimensions

There's no relationship defined between Sales and SalesAgg

The "Manage Aggregations" was setup as described in https://docs.microsoft.com/en-us/power-bi/desktop-aggregations and the report seems to be working properly in both PowerBI Desktop and Service.

 

 

My problem is that if I try to refresh the Dataset (either by using the Refresh Now or the Scheduler) I get this error:

 

The query result is too large. Consider removing unused columns, adding visual filters, or reducing the number of string type columns.

 

Regarding "size" I checked and found out that:

-The PBIX file is ony 35MB.

-The SQL "SalesAgg" table size is 510MB. (u

-The SQL "Sales" table size is 6,9GB (but since this is in Direct Query Mode, it doesn't get loaded so I wouldn't expect this to be the reason, as I believe that's one of the main purposes of the Aggregations feature)

 

Can someone help?

 

Thanks.

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Refreshing report using Mixed Mode (Import Mode, Direct Query and Dual Mode) and Aggregations

Hi @Datahungry,

 

You can disable the query-option to try again. But warning: This is a global setting, so it will applied to other queries as well.

 

4.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Datahungry
Frequent Visitor

Re: Refreshing report using Mixed Mode (Import Mode, Direct Query and Dual Mode) and Aggregations

Hi Frank,

Thanks for the reply. I tried what you suggested but nothing changed. I still get the error message:

 

The query result is too large. Consider removing unused columns, adding visual filters, or reducing the number of string type columns.

 

Is this a bug? Anyone else experiencing the same problem?

 

Anonymous
Not applicable

Re: Refreshing report using Mixed Mode (Import Mode, Direct Query and Dual Mode) and Aggregations

Yes, exactly the same problem

 

Something went wrong

The query result is too large. Consider removing unused columns,
adding visual filters, or reducing the number of string type columns.

 

What I did, removed all the unused columns, reduced the data size to almost  nothing and still the same problem.

When I click on refresh on the online Power BI, I get the yellow triangle with the above message.

Now the data volume is almost nothing, so somethings else is going on here.

 

Eric

 

 

 

Anonymous
Not applicable

Found the solution to my query result is too large error.

Found the solution to my query result is too large error.

 

What did not work :
Reduced the data to almost nothing.
Removed all the pages except one.
Removed all the visualization except one.

 

What did WORK :
Removed a table (lucky first one I tried) and that worked.
Without this table, I can press refresh on Power BI Online
and it works.

 

Without this table Power BI Online successfully refreshed.
So, I confirmed this was the table giving me the error.

 

But I need this table, so I removed all the measures etc
so that I only have the native columns from the table.

 

Still had problems, so I restarted and kept only the measures
and columns that I need. Turns out in the columns that I
did comment out was one of the columns that was causing the
refresh error.

 

Then I keep only the columns that I need and comment out
all the rest. Well, that solved my problem.


Sounds easy but this took a day and a half to figure this out.

I can now press refresh on Power BI online with no errors 🙂

 

Also, quick tip, when you get data, use the Advanced options
section and the SQL statement (optional, requires database)
box to enter your select.

 

When you have 50 columns etc
use only the columns you need and comment out the rest.

 

select
col1
, col2
--, col3
--, col4
, col5
from table

 

Hope this helps someone with this issue.

 

Eric (Montreal, Canada)

 

cemkestek
Frequent Visitor

Re: Found the solution to my query result is too large error.

Hi Eric,

 

I'm experiencing the same problem.

The table you changed, was that a DirectQuery, Dual or Import table? So I can focus on a set of tables on my model, rather than trying each of them one by one.

cemkestek
Frequent Visitor

Re: Refreshing report using Mixed Mode (Import Mode, Direct Query and Dual Mode) and Aggregations

Hi @v-frfei-msft 

I'm experiencing the same problem when I trigger a refresh in the PBI service (either scheduled or manual). Disabling the query-option didn't help either. It works fine when I manually refresh the model in PBI desktop in my local. 

 

Actually I can't even see which table in my model causing the trouble. Maybe you are able to see it with the below log?

Thanks,

Cem

 

Last refresh failed: Thu Dec 05 2019 18:15:56 GMT+0100 (Central European Standard Time)
The query result is too large. Consider removing unused columns, adding visual filters, or reducing the number of string type columns.

Cluster URI:WABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activity ID:2f87361a-6400-4634-90e1-c22e58578ca6
Request ID:f8a68b1d-0032-6001-6b4c-878e93b2c9f4
Time:2019-12-05 17:15:56Z

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors