Forum Discussion

cheese_za's avatar
cheese_za
Frequent Visitor
1 year ago
Solved

SAP B1 data connection change from onprem SQL to service layer

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 t...
Need Help
Tutorial Requests
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.