Forum Discussion

TrevLc's avatar
TrevLc
Helper III
2 years ago
Solved

joining 2 large semantic models (live connection direct query)

Hi,

I have 2 large semantic models that are already published (one 4 million records, one 6 million records) that I wish to connect to and join together.

They both have an "ID" field which is the only field I can join on, which is unique per row of data.

 

When I connect to the 2 Power BI published semantic models, they both become direct query connections.

 

Whe I try and create any visualisation (even aggregated by a categroy column), I get the error "The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000’ rows"

 

I have tried using TREATAS to create a virtual relationship, but this wont work, as I don't have ID in the visualisation, and I have also tried an inactive relationship and USERELATIONSHIP in a measure, but still get the error.

 

I looked at creating an additional aggregate table, but I cannot, as both queries are direct connection (and cannot duplicate one table as import mode).

 

Does anyone have any ideas how I can re-use these 2 semantic models rather than going back to the source database?

 

Thanks

 

Need Help
  • TrevLc's avatar
    TrevLc
    2 years ago

    Thank you. I have tried aggregations, which helps when it is one fact table, but I have 2 large fact tables, and when joined together, causes the error.

    I think I will need to go back to the source and extract what I need from there rather than connecting to the live Power BI semantic models.

5 Replies

    • TrevLc's avatar
      TrevLc
      Helper III
      2 years ago

      Thanks for that great advice amitchandak ... Unfortunately my organisation has disabled all external tools, so I don't have access to the ALM toolkit.

      Do you have any other suggestions?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi TrevLc 

         

        You may consider using Aggregations in Power BI. Aggregations in Power BI can improve query performance over large DirectQuery semantic models. By using aggregations, you cache data at the aggregated level in-memory. Aggregations can significantly reduce the volume of data needed for your visualizations, potentially avoiding the 'maximum allowed size' error. To learn more about it, please read User-defined aggregations - Power BI | Microsoft Learn

         

        As the aggregated table needs to be set up on an Import table, you can bring in a table which has "ID" field from the source database and manage aggregations on it. 

         

        Hope my advice would be helpful. 

         

        Best Regards,
        Jing
        If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!