Forum Discussion
Performance of running multiple queries
Hi All
Have the following settup for loadig data to Power BI:
- SAP BW server, where the source data is stored
- SAP BW Connector
- Power BI Report
The question is how to best load a big data from SAP Bw to Power BI, with the SAP connector, if there is any optimization done by the framework/engine Example:
A. Have created the query, which load the data from SAP. But because the data is quite big, I need to load it by months and years (in the query is parameter for filtering the data from SAP, so it load each time only 1 month). Then I have created a table, where are listed the months of a year to be downloaded. The query, loading query, is stored as a function and invoked for each row of this table, to load all the required months. Then this is repeated for 4 years as separated query (so the load query is executed 12 time for each year, and in 4 different queries). Result are stored in a table and appended or used as custom partition definition.
B. The same load query as in A, but this time the query will be invoked only for 1 month, or 2 months. So depepnding of the number of splits, there will be much more, but smaller queries (loading smaller amont of rows).
Which case, is from performance better? Are there any optimization in the engine/framework?
Hi tomas12344，
It's nice to see that you provide a query about optimizing data loading from SAP BW to Power BI using the SAP BW connector. Based on your description, there is no fundamental way to judge that the performance is excellent. Performance can vary depending on a variety of factors, including the complexity of the data, network bandwidth, and more. But it can be judged from some of the usual parts.First, consider optimizing the query used to extract data from SAP BW. Make sure your query is as efficient as possible, extracting only the necessary columns and rows. This can significantly reduce the amount of data transferred and processed.Second, evaluate the use of DirectQuery vs. import patterns in your scenario. DirectQuery doesn't import data into Power BI; Instead, it queries data from the SAP BW system in real-time. This can be useful for very large datasets, but can lead to slower report performance. On the other hand, import mode can be faster for report interactions, but requires the data to be refreshed periodically.Finally, if you're using import mode, consider setting up an incremental refresh policy in Power BI. This allows you to refresh only the most recent data that has changed, rather than the entire dataset. This can significantly reduce the refresh time and load on the SAP BW system.Of course, because of the overhead of starting each query, fewer large queries (Scenario A) may be more efficient than many smaller queries (Scenario B). However, this may depend on the specifics of the SAP BW setup and the data itself. You may need to test both methods in your environment to gauge the actual performance differences.
You can check out the following documents for more information
DirectQuery and SAP Business Warehouse (BW) in Power BI - Power BI | Microsoft Learn
Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn
Use the SAP Business Warehouse (BW) Connector in Power BI Desktop - Power BI | Microsoft LearnBest regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi tomas12344，
It's nice to see that you provide a query about optimizing data loading from SAP BW to Power BI using the SAP BW connector. Based on your description, there is no fundamental way to judge that the performance is excellent. Performance can vary depending on a variety of factors, including the complexity of the data, network bandwidth, and more. But it can be judged from some of the usual parts.First, consider optimizing the query used to extract data from SAP BW. Make sure your query is as efficient as possible, extracting only the necessary columns and rows. This can significantly reduce the amount of data transferred and processed.Second, evaluate the use of DirectQuery vs. import patterns in your scenario. DirectQuery doesn't import data into Power BI; Instead, it queries data from the SAP BW system in real-time. This can be useful for very large datasets, but can lead to slower report performance. On the other hand, import mode can be faster for report interactions, but requires the data to be refreshed periodically.Finally, if you're using import mode, consider setting up an incremental refresh policy in Power BI. This allows you to refresh only the most recent data that has changed, rather than the entire dataset. This can significantly reduce the refresh time and load on the SAP BW system.Of course, because of the overhead of starting each query, fewer large queries (Scenario A) may be more efficient than many smaller queries (Scenario B). However, this may depend on the specifics of the SAP BW setup and the data itself. You may need to test both methods in your environment to gauge the actual performance differences.
You can check out the following documents for more information
DirectQuery and SAP Business Warehouse (BW) in Power BI - Power BI | Microsoft Learn
Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn
Use the SAP Business Warehouse (BW) Connector in Power BI Desktop - Power BI | Microsoft LearnBest regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly