How connect SAPBydesign and PowerBI

[AROCHE-SI]

Hello everyone.

I try to connect SAPBydesign on PowerBI, i have see a many post and tutorial, but nothing want to works.

I try to get the data from odata feed, but i don't know how to $top= the amount of the record.

The other solution, is to make a paginate query for get every 50 row. This solution works, and i find this on this blog : 

How to Paginate an OData Service Using Power Query - Iteration Insights

The problem is, i can't scheldud this data : 

This dataset includes a dynamic data source. Because dynamic data sources are not updated in the Power BI service, this dataset will not be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

Have you and idea how i can get the data everyday ?

Thanks you.

[nc33wingroup]

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:

https:// <Your_SAP_ByDesign_OData_Service_URL>?$top=50

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.

    33win

[AROCHE-SI]

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 ?

[Anonymous]

Hi @AROCHE-SI ,

To address this, consider the following options:

• Direct Query Mode: If your data source supports it, consider using Direct Query mode in Power BI. This way, the data remains live, but it relies on the data source for real-time updates.
• Custom Connector: Explore creating a custom connector that handles the paginated query and refresh logic. Custom connectors can be more flexible in managing data sources.

Below is the official link will help you:

Power Query OData Feed connector - Power Query | Microsoft Learn

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AROCHE-SI]

Up

[AROCHE-SI]

up