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

[Guest Sunil_Sabat]

[HEADING=1]USB4SAP and Data Factory [/HEADING]

 

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.
     

[HEADING=1]Conceptual architecture[/HEADING]

 

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 storage 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
  

 

 

 

[HEADING=1]Application architecture[/HEADING]

 

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:
  
  
  
  .
  

 

 

 

 

 

 

 

 

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

 

Microsoft Azure Marketplace

 

 

 

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 Gateway] , with SAS-key [over HTTPS] based security
  
• Onelake data creation with support for CSV, JSon and Parquet
  
• Video guide is available here:
  
   
   
   
  
• Following is the link for Azure marketplace listing for this solution (free trial available):
  

 

Microsoft Azure Marketplace

 

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 Gateway] , with SAS-key [over HTTPS] based security
  
• Onelake data creation with support for CSV and Parquet
  

 

 

 

 

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.

 

[attachment=53224:name]

[attachment=53225:name]

 

 

 

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

 

 

 

 

 

 

 

Then, the data syncs to Fabric Onelake:

 

 

 

 

 

 

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 data lake. 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.

 

Continue reading...