SAP B1 data connection change from onprem SQL to service layer

[cheese_za]

Hi All,

I am in need of some help/guidance.

In my current setup, PBi desktop is connecting directly to the SQL database of SAP B1. They are on the same server. I connect to both SAP and PBi through Windows RDP.

We are moving to a new vendor and they only offer SAP B1 cloud and told me that I can only connect through the service layer.

Is there a way to migrate/edit the current table connections to the service layer?

Also, is there a howto on ingesting data via the service layer? I saw someone had an issue and said they fixed it by using the odata connection.

Any advice would be appreciated.

Thanks

[v-shamiliv]

Hi @cheese_za,

Thank you for reaching out to the Microsoft Fabric Community Forum.

As  @rajendraongole1  mentioned, Yes, it is possible to migrate from direct SQL connections to the SAP Business One (SAP B1) Service Layer for your Power BI (PBI) reports. To do so, you will need to configure Power BI to use the OData connection, set up authentication, and map the necessary business objects from the Service Layer.

To connect Power BI to SAP B1 Service Layer, follow these steps:

• In Power BI Desktop, go to Home > Get Data > OData Feed and enter the SAP B1 Service Layer endpoint URL.
• Use Basic Authentication or OAuth for authentication with credentials provided by the SAP B1 cloud vendor.
• Once connected, select the desired data entities (e.g., BusinessPartners, Invoices, SalesOrders) to import.
• Set up relationships between entities in Power BI, if necessary, using foreign keys (e.g., linking SalesOrders to BusinessPartners).
• Load the data into Power BI for analysis and reporting.

• Set up a scheduled refresh in Power BI Service to keep reports updated automatically.

This process allows you to seamlessly connect and work with SAP B1 data in Power BI.

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community.

[rajendraongole1 · Accepted solution]

Hi @cheese_za - Yes, moving from SQL Server direct connections to SAP Business One (B1) Service Layer via the cloud will require you to adapt your setup. 

SAP B1 Service Layer is a RESTful OData API designed for cloud-hosted SAP B1 systems. It allows you to interact with the data and tables in SAP B1 using OData.

• In SAP B1 on-cloud, direct SQL access is not available.
• Instead, you query and pull data via an OData feed exposed by the Service Layer.

Unfortunately, you cannot directly "edit" your current SQL connections to point to the Service Layer because:

• SQL queries are replaced with OData calls.
• Data retrieval via OData requires endpoints that map to SAP tables.

You’ll need to rebuild your queries or Power BI connections to point to the OData service endpoints.

Since OData can be slower than direct SQL, implement incremental refresh in Power BI.

• Test the Endpoints: Use tools like Postman or Power Query to test the OData endpoints before loading into Power BI.
• Vendor Support: Work with your vendor to confirm:
  • List of available endpoints.
  • Authentication method.
  • Data limits or throttling policies.

[cheese_za]

What would suggest?

1. Replicate table by table in my current PBi file and switch the relations, that way maintain the reports?

2. Start from scratch and reference the old setup?

Thanks

[rajendraongole1]

Hi @cheese_za - Replicate Table by Table and Switch Relationships

This approach involves maintaining your current reports while gradually replacing the data source.

Pros:
Preserve Reports: Your existing visuals, measures, and relationships remain intact as you replace the tables one by one.
Less Manual Work: You won’t need to recreate every report or visual from scratch.
Faster Transition: By switching tables gradually, you can ensure a smooth migration without breaking the entire report.
Cons:
Complexity of Switching: SAP B1 Cloud’s Service Layer (OData) might provide data slightly differently than SQL—field names, data types, or structures may not match perfectly.
Risk of Breaking Relationships: If fields or keys are not consistent, relationships and measures may require manual updates.
Maintenance Overhead: Switching each table while checking visuals and measures for compatibility may take time.

or 

If you have time and want to optimize your data model, starting fresh

[cheese_za]

Thanks for the info.

It seems that unfortunately, I have a long road ahead of me.