Using USB4SAP and Data Factory to extract SAP data for PowerBI in live and cache mode

USB4SAP and Data Factory 

With USB4SAP, Data Factory users can access SAP data. This data can be used to refresh PowerBI semantic models in live and cache mode. USB4SAP provides deep integration into your SAP system (for raw tables data, as well as modeled information like reports, queries, CDS, BW extractors etc). SAP tables data extraction with delta / CDC capabilities (ADF connector) using #USB4SAP (without the need for SLT or Change Pointers activation ).

Specifically for integration with customers SAP systems you can leverage USB4SAP connector for:

  • PowerBI live and cached mode
  • Onelake based integration
  • REST based synchronous API integration

It supports no-code, native SAP security based access to the following SAP objects (HANA or non-HANA based):

  • Tables (with Change data capture)
  • Views
  • CDS
  • Reports
  • TCodes
  • BW Extractors
  • ABAP queries

Following modes of Change Data Capture are supported:

  • Tables & views:
    1. Time-series based [ie, date & time of the record create, update, delete]
    2. Document & item number series based
  • Reports / Queries / TCodes:
    1. Time-series based using variants on selection screen. 

Conceptual architecture

Following are the key components for the conceptual architecture for MS Fabric integration with SAP systems.

  • Customers SAP systems (ERP, S4HANA, BW, CRM, SRM, APO, Solman etc) are organizational systems of record
  • Data transmission is REST over HTTPS (unless specified otherwise, where RFC / OData may also be used)
  • Data & information is any cloud (eg, Microsoft Azure) or on-premise repository
  • Information security is using SAS key over HTTPS
  • Synthesis layer is combination of tools like PowerAutomate / Logic Apps etc
  • PowerBI/ PowerPlatform / MS Excel and other apps are supported using REST / PowerQuery
  • CX-Portal layer [optional] is MS Sharepoint or other customer Portal solutions

Application architecture

Following are the application architectures for live  and cache connection from Fabric PowerBI to backend SAP systems. Data Factory templates are also available to accelerate use of Ecoservity's connectors and integration platters within a pipeline.

PowerQuey  Connector Method:

  • Fabric live connection to SAP:  Live query to SAP leverages following mechanism
  • PowerQuery module within PowerBI
  • REST API [over HTTPS] connectivity to SAP [based on SICF or Gateway] for Power Platform apps
  • Video guide is available here:  https://youtu.be/vmJVoNSBdpM.

Sunil_Sabat_3-1716735430464.png

  • Following is the link for Azure marketplace listing for this solution (free trial available):

https://azuremarketplace.microsoft.com/en-us/marketplace/apps/ecoservity.peopleatwork4pbi?tab=Overvi…

Cache Method:

  • Fabric cached connection to SAP: Cached query to SAP leverages following mechanism
  • PowerQuery module within Fabric PowerBI
  • REST API [over HTTPS] connectivity to SAP [based on SICF or ] , with SAS-key [over HTTPS] based security
  • Onelake data creation with support for CSV, JSon and Parquet
  • Video guide is available here: https://www.youtube.com/playlist?list=PLTum8dvrbVA05nV3hsr8rMPjqGHc2oOAqSunil_Sabat_2-1716735344445.png
  • Following is the link for Azure marketplace listing for this solution (free trial available):

https://azuremarketplace.microsoft.com/en-us/marketplace/apps/ecoservity.usb4sap_azure_data_factory?…

REST Method:

  • REST API based connection to SAP: Cached query to SAP leverages following mechanism
  • PowerQuery module within Fabric PowerBI
  • REST API [over HTTPS] connectivity to SAP [based on SICF or ] , with SAS-key [over HTTPS] based security
  • Onelake data creation with support for CSV and Parquet

Sunil_Sabat_4-1716735472451.png

Data Factory Template Method:

In collaboration with Microsoft, Ecoservity has developed a set of Data Factory templates that make it faster and easier to integrate SAP into the Fabric ecosystem. These templates use Data Factories REST data source and data sink to read and write data from SAP.

Sunil_Sabat_5-1716735506576.png Sunil_Sabat_6-1716735506582.png

The following screenshots show a Data Factory template that copies data from an SAP semantic model via REST.

Sunil_Sabat_7-1716735506591.png

Then, the data syncs to Fabric Onelake:

Sunil_Sabat_9-1716735506598.png

Conclusion:

In this blog, we reviewed alternate methods of using Ecoservity's USB4SAP product in conjunction with Data Factory to load SAP business  data for PowerBI reports and . You can adopt live and cache modes.  Templates make it easy for end users to adopt the solution in a pipeline. Ecoservity product is available in Azure Market Place. You can go ahead and try it out as alternative to existing connectors available in data factory. 

 

This article was originally published by Microsoft's Azure Data Factory Blog. You can find the original article here.