Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Composite models: query result too large with Direct Query

Hi all,

 

I have the following issue using Composite models:

 

I have two fact tables which require to be connected with a bridge table.

 

The two fact tables are imported while the bridge table is used with Direct Query to stay within the 1 GB limit of Power BI (with it, the file size is 1,8 GB, without is 600 MB).

 

Publishing the file is not a problem, but when refreshing the dataset it returns the error that the Queryresult is too large and it subsequently fails.

 

Any suggestions to approach this issue?

 

Thanks!

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi wongchungjec,

     

    As per Microsoft, Power BI can supports upto 1 GB of data. Also, your visuals can hold upto 1 GB of data. If it exceeds, then it will not load all the visuals in the report. 

     

    We have experienced a lot on this. For this, there is a way around to rectify this problem.

     

    Instead of loading all the data directly into Power BI, you need to have SQL Server Analysis Services(SSAS-Tabular Model) in loading all the data and for your manipulations. With this, SSAS can hold all the data and throw only the results to your PBI Report. Then, your visuals can be easily loaded with the results.

     

    Regards,

    Pradeep

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This is not really what the poster is reporting. I have the same error and I have 1 table that is still an import and it is not even 50 MBs....all other tables are DirectQuery. I still receive the Query is too large error on refreshing that one table. This seems to be a composite model issue as when I intially upload the report all the directquery visuals work and so does the small (<50 MB) imported query. AS soon as I run a scheduled refresh I instantly get a query is too large issue. Normally when this is an import issue the refresh stops only once the limit has been breached, 15-30 minutes into refresh depending on latency.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        This is what I meant yes. Already solved it another way by aggregating my dataset but my initial problem hasn't been solved

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous, thanks for your reply!

       

      To give more context: I use a drillthrough to go from the data from one fact table to the data from the (more detailed) fact table.

       

      The  limitations of the 1 GB per visual I mitigate by making a subselection first one the first Report page (which only shows data from one fact table) and then drilling down to the next page. The bridge table is only required when doing this drillthrough.

       

      The filesize of the .pbix file is 600 MB and therefore I don't expect to exceed the limit of 1 GB. Also, normally when I only use import I get a different error message then I do now (table cannot exceed 10GB or something like that?) That's the reason it is caused by there DQ part.... We also don't have SSAS currently as it not planned in the near future..