Forum Discussion
How connect SAPBydesign and PowerBI
Connecting SAP Business ByDesign to Power BI, especially when dealing with OData feeds, involves several considerations to ensure a smooth data retrieval process. It looks like you've encountered challenges with limiting records using $top and scheduling data refresh due to dynamic data sources. Let's address these issues step by step:
1. Limiting Records with $top
The $top parameter in OData queries is used to limit the number of records returned by a query. If you're having trouble incorporating $top into your query URL, the syntax typically looks something like this:
This URL should return the top 50 records. You may need to adjust the URL to match the specific requirements of your SAP Business ByDesign OData service.
2. Pagination for Large Datasets
It's good that you've found a solution for pagination to fetch every 50 rows. Pagination is essential for handling large datasets efficiently. Using Power Query to paginate requests involves creating a loop that increments the offset ($skip) after each batch of 50 records until all data is retrieved. The blog post you found likely provides a detailed guide on setting this up.
3. Scheduling Data Refresh with Dynamic Data Sources
Power BI has limitations when it comes to refreshing datasets that include dynamic data sources directly in the Power BI service. However, there are a few strategies you can consider to automate the data refresh:
Data Gateway: Use a Power BI Gateway to connect your on-premises data source (in this case, the system where you're running the queries against SAP ByDesign) with the Power BI service. This gateway acts as a bridge and can help manage data refreshes even with dynamic sources.
Power Automate: Consider using Power Automate to automate the process of fetching data from SAP ByDesign and then pushing it into a Power BI dataset. You can set up a flow that runs at a scheduled time each day, retrieves the data using your pagination logic, and then updates a Power BI dataset through the Power BI REST API.
Scheduled Refresh with Static Parameters: If possible, modify your query to use static parameters that do not change with each execution. This might not be fully applicable given your dynamic data source, but even partial static parameters might help in some scenarios.
API-based Refresh: Use the Power BI REST API to programmatically trigger dataset refreshes. This can be combined with any server-side scripts that fetch and prepare your data, essentially allowing you to schedule when and how your data is updated in Power BI.
Additional Tips
- Ensure your OData queries are optimized for performance to reduce the load times and improve the efficiency of data refreshes.
- Regularly check the Power BI and SAP Business ByDesign documentation for any updates or new features that might simplify the integration process.
If you continue to face challenges, consider reaching out to the Power BI and SAP Business ByDesign communities for more specific advice or consulting with a professional who specializes in integrations between these two platforms.
Thank you for your answer !
I think i will try with the gateway, i can use power bi personnal gateway in this case ?
Have you some documentation for refresh the data in this way ?
