Forum Discussion

tessahurr's avatar
tessahurr
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

Hit Reply and let us know what you think of the DirectQuery for Power BI datasets and Azure Analysis Services.  To learn more about this feature, please visit this blog post or our documentation.   ...
General Comment
adriantheuma's avatar
adriantheuma
New Member
4 years ago

We are having a problem with DirectQuery for Power BI datasets for large tables. In the screenshot below 'Account Attributes' has 6M rows and 'Retail Sales' has 24M rows. 

 

Retail Sales is in a separate PBIX file that is loaded on the Power BI service. 

 

When we try to use attributes from the 'Account Attributes' table or refresh the 'Account Attributes' table the report does not refresh. The 'Account Attributes' table would normally refresh in a minute or so but since moving the 'Retail Sales' table to a separate PBIX file the refresh remains in the status shown in the screenshot below and does not complete.

 

There are no issues when refreshing or using attributes in the report from the other tables connected to 'Retail Sales', such as the 'Date' table. These are all tables that have a few hundred rows.

 

Is there a service limitation on the size of the tables that can be in separate files?

 

 

 

  • adriantheuma's avatar
    adriantheuma
    New Member
    4 years ago

    Looks like I have answered this myself. Will this improve for composite models? 1M rows is a bit restrictive.

     

    Feedback Type:
    Frown (Error)

    Timestamp:
    2021-09-21T08:21:50.6919927Z

    Local Time:
    2021-09-21T18:21:50.6919927+10:00

    Session ID:
    39705183-f0c9-4b74-8532-e8249d95ab8f

    Release:
    August 2021

    Product Version:
    2.96.1061.0 (21.08) (x64)

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

    OS Version:
    Microsoft Windows NT 10.0.19041.0 (x64 en-US)

    CLR Version:
    4.7 or later [Release Number = 528372]

    Peak Virtual Memory:
    105 GB

    Private Memory:
    997 MB

    Peak Working Set:
    1.24 GB

    IE Version:
    11.789.19041.0

    User ID:
    b39f14f6-ec17-4057-b5eb-030a835a41ae

    Workbook Package Info:
    1* - en-AU, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: False.

    Telemetry Enabled:
    True

    Snapshot Trace Logs:

    Model Default Mode:
    Composite

    Model Version:
    PowerBI_V3

    Performance Trace Logs: