Append Query with Large, Static Data to Query with Small Changing Data

[johnsauber]

I am experiencing performance issues with appending queries with OData sources.

Assumptions / Scenario:

• I have 4 OData feeds that show sales data I would like to join.
• Queries A and B are historical sales from a legacy system that largely do not change (only if we make a correction to a historical sale does this data change
• Queries A and B take a long time to load with OData, as the dataset is pretty large
• Because of the load time, I have Queries A and B set with "Enable Load" turned off in the Query Editor in hopes that they wouldn't be refreshed with every hit of the Refresh button in the PBIX file
• Queries C and D come from our current system, and need to be refreshed at each load of the PBIX file
• Queries C and D do not take a long time to load, as there is not much data there
• I am taking Query D and appending Queries A, B and C to it to get a unified sales table.

I am under the impression I need to do this append procedure in order to make the data appear, on the visualizations, as if it's all coming from one data source. I am expecting, that with "Enable Load" turned off on the large queries, the load of the appended Query D would be fast. However, it would appear that all of the data is being refreshed from all of the Queries, since the overall refresh of the workspace takes as long as manually refreshing Queries A and B (the large ones).

Does anyone have any strategies we could use here? If there is a way to not append these queries, but make them all appear as if they're coming from the same datasource, I'd be interested in that solution as well. Currently, if I were to chart the Sales from the 4 separate queries, they'd always look distinct from one another; something we want to avoid.

Thanks for any insight here!

John

[marcodea]

I’m the same situation:
- table A of 10MB
- table B of 100MB (“enable load”turned off)

Scenario1)
Using APPEND in query editor. Every refresh take a lot of time and Power BI load 100MB

Scenario2)
Using DAX function UNION to create an additional table. Refresh is fast and load only 10MB of data

Any suggestion?

[v-chuncz-msft]

@johnsauber,

You may check if the following article helps.

https://www.thebiccountant.com/speedperformance-aspects/

[johnsauber]

@v-chuncz-msft

Thanks for the suggestion! I did toggle that option but it isn't making an impact. There aren't "many" queries in the workspace, some just seem like they're "large" datasets for OData to deliver (65 MB); that doesn't seem to large to me and would suspect downloading that OData wouldn't take but a few seconds with our connection speed.