Forum Discussion

MTracy's avatar
MTracy
Advocate I
5 years ago
Solved

Slow Power Query & refresh performance w/ multiple merges/lookups possible cause

I'm working with Help Desk ticket data from a 3rd-party vendor's reporting server, and a Dataflow handles the initial pull of needed tables and some light transformations.  Using Incremental Refresh,...
Need Help
  • Burningsuit's avatar
    Burningsuit
    5 years ago

    Whilst you can have only one active relationship in a table, you can have any number of inactive relationships (dotted lines) between tables. I'd create the inactive relationships in the model then use the DAX USERELATIONSHIP on measures to swap between them. 

    Stuart

     

MTracy's avatar
MTracy
Advocate I
5 years ago

Thanks for the different approach, I do appreciate that!  Normally, my first step would be to try to do as much as possible by getting the source data modeled as much as possible before loading it.  Unfortunately in my case, the source SQL server belongs to a 3rd party vendor, so we don't have the freedom to create views or even connect directly with SSMS due to their security policies.  Dataflows ended up being our best connection option, and while it's possible to input a SQL query, that workflow prompts you for the query before you can even see the 1,095 data tables, making developing such queries quite a bit of trial and error - something there's not a lot of free time for.

 

Looks like my best solution is the USERELATIONSHIP option - at least until we can develop queries that push the work back on that robust SQL engine.

edhans's avatar
edhans
Community Champion
5 years ago

MTracy - one more thing you can try. Intentionally break folding after the point of which incremental refresh is happening, if possible.

 

You can arbitrarly add a column that uses Table.TextBetweenDelimiters() or something that will not fold. I've done this where Table.Buffer doesn't help, which is anything over 100,000 records or something.

I've used this on several models where I needed 5-6 joins, and after the 4th or 5th join, it bogged the server down. So after the 3rd join/merge, for example, do something that breaks folding, then continue with the merges, then remove the arbitrary column I created. Power Query is still very efficient at merges even if the mashup engine is doing it vs sending it back to the server.

I hope the best for your project. Sometimes it is about trying 10 different things to figure out which one is the least of a problem. 😁