Forum Discussion

Datahungry's avatar
Datahungry
Frequent Visitor
7 years ago

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.

Need Help

6 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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.

     

     

    Regards,

    Frank

    • Datahungry's avatar
      Datahungry
      Frequent Visitor
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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's avatar
      Anonymous
      Not applicable
      6 years ago

      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