Forum Discussion
SAP B1 data connection change from onprem SQL to service layer
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.
Super UserHi 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.
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
Thanks for the info.
It seems that unfortunately, I have a long road ahead of me.
