cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
johnsauber Frequent Visitor
Frequent Visitor

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

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

3 REPLIES 3
Community Support Team
Community Support Team

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

@johnsauber,

 

You may check if the following article helps.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
johnsauber Frequent Visitor
Frequent Visitor

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

@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.

marcodea Regular Visitor
Regular Visitor

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

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?