Forum Discussion
Merging largish Power BI dataflow sources takes forever
I'm trying to follow best practices for dataflows and breaking up ingestion dataflows from transformation flows (as recommended here: https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-understand-optimize-refresh#guidance-for-long-refresh-durations). In my transformation flow, I am trying to join two tables that were previously loaded via a separate Power BI dataflow each with about 400,000 records and no duplicate keys (I'm sure the join condition is correct as well because I'm not getting duplicates in the results). It takes about 15 *minutes* to refresh - a query that would take less than a few seconds in SQL because the join is on a key. This is a pretty basic scenario so I'm just not sure what I'm missing.
I don't know if it's relevant, but if I try the same join in Power BI desktop, during the "Load" phase, it's reporting over 1 GB read from "PowerPlatformDataflows" by the time it's done and takes a similar amont of time. Moreover, I am actually loading both of the dataflows into the current query and then merging them (as recommended here: https://docs.microsoft.com/en-us/power-query/optimize-expanding-table-columns), but based on the amount of data it's downloading, that doesn't actually seem to be helping (e.g. it more or less seems to be re-downloading the same amount of data during the merge as it did when it downloaded the source tables initially).
One other thing to note is that I am licensed through Power BI Premium per User so I don't believe I have any control over the capacity it is using and I'm not sure what the baseline specs are in this case. That said, it kind of defeats the purpose of Premium per User if it won't handle something like this.
5 Replies
- lbendlin
Super User
Anonymous Do you have to merge them in Power Query or can this wait until the data sources are loaded into the Vertipaq memory? Usually it is considerably faster to let the data model do the work.
Can you elaborate on what you mean by doing the merge in Vertipaq vs Power Query? Even if both datasets are loaded locally (e.g. using PBI desktop), the only way I know to merge them is still using Power Query and, as I mentioned in the initial question, it seems to be "folding" the query back to the PBI service when expanding the joined table. Also, isn't pre-forming data like this the entire purpose of a dataflow?
Perhaps there's a better way to tackle this problem, but what I'm trying to do is build a normalized Orders dataset that has orders from various systems in it, build a pseudo key and then build a second dataset that augments a subset of the orders with additional data from our transportation line-of-business system, while retaining the pseudo key in case downstream consumers want to join in other datasets on that key.
If I use "Get Data" in PBI Desktop using the Dataflows connector to add my standardized Orders and transportation orders datasets, it takes about 2 minutes to do a complete refresh. If I then "Merge as new" the transportation orders into the Orders data set on the natural key, it still takes about 2 minutes to load. However, if I expand even to include even a single column from the transportation orders dataset, it takes 15 - 20 minutes to refresh. So, as I said, it seems to be expanding the table by querying the service rather than performing the merge locally. Is there a way to force it not to do that that would be more performant?
- lbendlin
As I said I would use the Power BI Data Model connections (and DAX calculated columns) instead. But you can also experiment with Table.Buffer (assuming you have enough RAM to do so).
Personal, biased opinion: I have yet to see a really compelling use case for dataflows.
