Integrate Your SAP BW Data in Microsoft Fabric

Discover how to integrate SAP with Microsoft Fabric for enhanced data analysis, informed decision-making, and optimized business operations.
Microsoft Fabric
SAP BW
ERPL
DuckDB
Author
Affiliation

Simon Müller

Published

February 26, 2024

Install our RFC DuckDB Extension for Free

SET custom_extension_repository = 'https://get.erpl.io';
INSTALL erpl;

Summary

  • Integrating SAP systems with Microsoft Fabric breaks down data silos, unlocking a more comprehensive understanding of your organization’s information. This leads to improved decision-making across departments.

  • Fabric’s analytics tools, when combined with your SAP data, provide real-time insights. This enables proactive problem-solving and empowers you to make informed decisions as market conditions change.

Introduction

In today’s interconnected data landscape, the ability to integrate your core SAP systems with the flexibility of the Microsoft Fabric suite is a potent advantage. This integration unlocks powerful insights, streamlines processes, and opens new possibilities for innovation. If you manage SAP data and want to leverage the power of Microsoft’s cloud solutions, this article is for you. We’ll cover the basics of SAP and Microsoft Fabric integration and provide step-by-step guidance inspired by the approach outlined on erpl.io.

Absolutely! Here’s a first draft article based on the provided resource, tailored for a Medium publication. I’ve focused on making it engaging and informative while assuming the reader has some familiarity with SAP and Microsoft environments.

Understanding the Essentials

  • SAP: The cornerstone of many enterprise operations, SAP (Systems, Applications, and Products) is a business software suite managing everything from financials to supply chains.

  • Microsoft Fabric: A broad umbrella term encompassing Microsoft’s cloud-based tools, including Azure, Power Platform, and Dynamics 365. Fabric provides data storage, analytics, AI capabilities, and more.

  • BW Queries: SAP’s Business Warehouse (BW) queries are how we extract and structure data from complex SAP systems. See a detailed example of how to query, create, and retrieve data from SAP BW using our guide from erpl.io: Understand and Execute a BW Query from within DuckDB.

Why Integrate SAP and Microsoft Fabric?

Break Down Silos

Siloed data is one of the biggest obstacles to informed business decisions. Your SAP system holds a treasure trove of information on sales, inventory, financials, and more. But this data’s actual value is realized when it correlates with your organizational information.

Here are some examples of what becomes possible when you integrate SAP with Fabric:

  • Customer 360: Combine SAP sales records with CRM data (like interactions and preferences) stored in Dynamics 365 to create a holistic customer view for personalized marketing and support.

  • Operational Efficiency: Link SAP supply chain data to IoT sensor data (monitored via Azure) from your warehouses and distribution centres, enabling predictive maintenance and real-time inventory adjustments.

  • Trend Analysis: Analyze SAP financial data alongside market trends pulled from external sources (via Power BI connectors), providing insights into profitability and performance forecasting.

  • Real-Time Insights: Fabric’s analytics tools enable fast, data-driven decision-making based on your SAP information.

  • Automation and Innovation: Connect SAP processes with Power Automate flows, build intelligent applications with AI, and seamlessly extend your SAP capabilities.

The Integration Process

Inspired by the erpl.io guide, here’s a simplified integration workflow:

  1. Data Extraction: Develop a suitable BW Query to extract the desired data from your SAP system. This may involve working with SAP specialists. As mentioned above, you may use our guide on Understand and Execute a BW Query from within DuckDB to create such a query.

  2. Data Transfer: Use DuckDB and our ERPL (Enterprise Resource Planning Loader) extension for transferring data from SAP BW to Microsoft Fabric.

  3. Data Storage (Fabric Side): Select the proper storage solution: Azure SQL Database, Cosmos DB, or simple data lakes — the choice depends on how you organise your data in Microsoft Fabric, e.g. as described here.

  4. Data Consumption: Use Fabric tools: Power BI for dashboards, Power Apps for tailored interfaces, and Azure Logic Apps for process automation.

In the following, I will show a possible setup for transferring data from SAP BW to Microsoft Fabric by setting up an environment, creating the notebook, and saving your data to Lakehouse.

Step 1: Create an Environment for installing the necessary packages for SAP data extraction:

For this use case, we install pandas for data wrangling and duckdb for extracting the SAP BW data.

Step 2: Create a notebook

As mentioned above, create a notebook that includes the code that fetches your SAP BW data and saves it to your Microsoft Fabric warehouse.

Step 3: Create your BW query using the notebook created in Step 2 or by doing it locally

I will refer to our tutorial on our website: Understand and Execute a BW Query from within DuckDB.

Step 4: Test and execute your notebook and integrate your data into your warehouse.

In the first code block, we will import duckdb and install the ERPL duckdb extension.

import duckdb
con = duckdb.connect(config={"allow_unsigned_extensions": "true"})
con.sql("""SET custom_extension_repository = 'http://get.erpl.io';""")
con.sql("""FORCE INSTALL erpl;""")
con.load_extension("erpl");

You should get the following message if the extension is installed and loaded successfully:

-- Loading ERPL Trampoline Extension. --
(The purpose of the extension is to extract dependencies and load the ERPL implementation)
Saving ERPL SAP dependencies to '/home/trusted-service-user/.duckdb/extensions/v0.9.2/linux_amd64_gcc4' and loading them ... done
ERPL RFC extension extracted and saved to /home/trusted-service-user/.duckdb/extensions/v0.9.2/linux_amd64_gcc4.
ERPL BICS extension extracted and saved to /home/trusted-service-user/.duckdb/extensions/v0.9.2/linux_amd64_gcc4.
ERPL ODP extension extracted and saved to /home/trusted-service-user/.duckdb/extensions/v0.9.2/linux_amd64_gcc4.
ERPL RFC extension installed and loaded.
ERPL BICS extension installed and loaded.
ERPL ODP extension installed and loaded.
ERPL extensions loaded. For instructions on how to use them, visit https://erpl.io

After the successful installation, we need to connect to our SAP system. In this tutorial, we will add the credentials directly to the notebook as we use publicly available credentials from the SAP trial system (see, e.g. Develop, execute, and manage Microsoft Fabric notebooks).

con.sql("""
SET sap_ashost = '192.168.178.36';
SET sap_sysnr = '00';
SET sap_user = 'DEVELOPER';
SET sap_password = 'ABAPtr1909';
SET sap_client = '001';
SET sap_lang = 'EN';
""");

Retrieve data from a query or cube.

Finally, and most importantly, retrieve data from a query or cube. However, there is a challenge, as the multi-dimensional data model of BW can not be matched to the relational model of DuckDB. This is why we have to use a set of functions to define the structure of the result set. Let’s go through the process step by step.

Step 1: Begin the query

con.sql("SELECT * FROM sap_bics_begin('0D_NW_C01', ID='q1', RETURN='RESULT');")

With this function, we start the query. The parameters are the following:

  • technical_name: The technical name of the InfoProvider

  • ID: The ID of the query. This is a unique identifier for the query. The system will generate one for you if you do not provide an ID. The function returns the generated ID, but only if the RETURN parameter is set to META.

  • RETURN: The return type of the function. The following values are possible:

  • META: The function returns the ID of the query

  • RESULT: The function returns the result of the query

Step 2: Drill down the query

Next, we will drill down by adding a characteristic to the row dimension. The following command adds the characteristic 0CALMONTH to the row dimension:

con.sql("SELECT * FROM sap_bics_rows('q1', '0CALMONTH', OP='ADD', RETURN='RESULT');")

The result shows that the characteristic 0CALMONTH is added to the row dimension. We can add another characteristic to the row dimension. The following command adds the characteristic 0D_NW_REGIO to the row dimension:

con.sql("SELECT * FROM sap_bics_rows('q1', '0D_NW_REGIO', OP='ADD', RETURN='RESULT');")

Step 3: Finally, receive the query result

Finally, we can receive the result of the query. The following command returns the result of the query:

con.sql("SELECT * FROM sap_bics_result('q1')")

Underneath the hood, the extension creates a table in DuckDB to persist the query state between the individual function calls. If you use a file-based database connection, this query definition also survives the restart of the DuckDB connection. This is why you can use the sap_bics_result function to retrieve the query result, e.g., in a daily running job.

Conclusion

Integrating your SAP data with Microsoft Fabric creates a robust foundation for analytics, automation, and innovation. By breaking down data silos and leveraging the strengths of both platforms, you can gain a competitive edge and drive more excellent value for your organization.