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

  • Thread starter Thread starter Sunil_Sabat
  • Start date Start date
S

Sunil_Sabat

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



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:
    View: https://youtu.be/vmJVoNSBdpM
    .



medium?v=v2&px=400.png





  • 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: https://www.youtube.com/playlist?list=PLTum8dvrbVA05nV3hsr8rMPjqGHc2oOAqmedium?v=v2&px=400.png


  • 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



medium?v=v2&px=400.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.

medium?v=v2&px=400.png

medium?v=v2&px=400.png




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



medium?v=v2&px=400.png




Then, the data syncs to Fabric Onelake:



medium?v=v2&px=400.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 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...
 
Back
Top