How a retail customer unleashed the power of SAP data and improved data ingestion with ADF data extraction in Azure.
A US-based multinational manufacturing and retail company had its legacy systems, aged infrastructure, and traditional data extraction tools running on-premises. With this current infrastructure, they were unable to perform real time analytics to derive meaningful business insights. They grappled with challenges around reducing operational cost and using quality data-driven insights in real-time to increase revenue.
The company decided to take benefits of the Azure platform services like Azure Data Factory (ADF) to overcome their current challenges, while their SAP real estate remain on on-premises. They also wanted to leverage modern technologies like Azure Machine Learning to predict the sales pattern and customer requirements to increase their revenue.
Read this article to learn how Cognizant teamed up with Microsoft to help this multinational manufacturing and retail company unleashed the power of SAP data and improved data ingestion with ADF data extraction in Azure .
This blog is co-authored by Madhan Kumar Munipandian, Solution Architect – Data Analytics and Rajib Mandal, Data Engineering - Lead, Cognizant; Prabhjot Kaur, Senior CSA, Microsoft.
Legacy deployment and its challenges
The customer’s legacy data warehouse was designed a decade ago, resulting in a very slow and inefficient data extraction process.
The following challenges were presented by the customer to the team:
- Legacy data warehouse was designed to connect to the transactional database directly. This design impacted the performance of the transactional systems during the data extraction process causing long running transactions for users
- To reduce performance impact because of direct pull, legacy data warehouse was designed to pull data once a week or month
- Legacy data warehouse was designed to extract full data during each batch. No real time data extraction available
- Incremental pull capabilities weren’t configured. Performing full extract each time took a long time, and consumed a lot of storage space
- Analytics and reporting tools were not intuitive to visualize the data for business analysis
Customer requirements
Speed and accuracy are the basic requirements for many customers performing ETL (Extract, Transform, and Load) operation. This retail customer had the following requirements:
- Derive accurate insights from sales and revenue data to unblock hindering factors to potentially increase the revenue
- Reduce operational costs in the supply chain by leveraging cost effective infrastructure and retire legacy on-premises infrastructure which are expensive to support
- Redundant dataset is being extracted from various SAP system - Improve data quality, data redundancy, data accuracy by pulling the data from source of the truth systems
- Ability to build intuitive dashboards and visualization to get supply chain operational cost, customer demand, store’s inventory etc
- Real time and near real time data processing
- Leverage Machine Learning capabilities to predict the sales pattern
- Offer Data as a service (DaaS) to its suppliers for product info, and inventory
Proposed Solutions
To modernize the business, to meet the requirements, and to take advantages of analytics and integration platform services, the company decided to migrate their on-premises enterprise ware house workload to Azure.
One of the important requirement is to seamlessly integrate SAP (running on on-premises) and non-SAP systems and extract the data from various sources, which would then be analyzed by business decision makers to make key decisions. To fulfill this requirement, the company decided to embark on a digital transformation initiative to build Data Lake on the cloud and leverage Azure data analytics services. This will help them to resolve challenges faced in legacy data warehouse solution with on-premises.
Here are the primary business drivers for the solution(s):
- Leverage Azure PaaS offering to reduce cost and save time spent on infrastructure provision and management
- SAP data extraction must work seamlessly with Azure Databricks for data analytics and data standardization
- Highly available, scalable and proven platform to extract data from global instance of the SAP systems – SAP ECC, Supply Chain (APO) and SAP BW (Business Warehouse)
- Ingest data into Azure Data Lake Storage (ADLS) with great performance and throughput
- Configurable capabilities to extract Incremental data from the source systems
- Must support dynamic configuration (metadata driven extraction ingestion framework) to extract full and incremental data from SAP systems
Solution must also consider the following technical requirements :
- SAP extracted data size varies widely from couple of MB’s to 100 GB’s
- SAP systems – ECC & supply chain are highly transactional system; Don’t extract the data from direct database layer to comply with SAP support requirements, and to avoid negative performance impact on the SAP systems
- Data extraction from SAP systems must be done from SAP application layer using RFC protocol
- SAP source systems (from where the data is being extracted) had various technical configurations like process connection time out, memory allocation, maximum process run time etc. that could have impact on the extraction process
Pilot phase was executed to compare different Integration Platform as a Service (iPaaS) solutions to evaluate SAP data extraction and design pattern’s to handle different avenues of data scenario’s such as source data size, delta extraction, etc. Based on Pilot results, performance benchmark and primary drivers tool selection criteria, ADF was selected as the SAP data extraction tool for full batch and mini batches. After a lot of analysis, ADF emerged as the ETL engine that could fulfill the business requirements, and overcome the current technical challenges.
The big question that still needed to be addressed was: “How to efficiently leverage Azure Data Factory to extract data from different SAP Applications, SAP - ECC (ERP Central Component) , SAP – APO (Advance planning) and SAP – BW (Business Warehouse) ?”
Solution Deployment
ADF was deployed with the following connectors and configuration. However, to meet the business SLA, we had to make some configuration changes (documented below):
- SAP Table/Open Hub connector: SAP table connector was used to extract the data from SAP systems. During performance testing, we noticed huge drop in copy throughput (from Mb/s to Kb/s) when source data size was above 2GB. Worked with Microsoft engineering team and SAP support team to identify the root cause. Copy throughput was low due to the design of classic RFC protocol handle size more than 2GB. The solution was to change the protocol from RFC to BASXML to handle large size (>2GB) data copy (if you use latest S-H-IR, this protocol config is already there). After the protocol change in Self-hosted-IR, performance improved significantly:
- Before changes: 2 hours 25 minutes 7 throughput; approx. 90 KB/s
- After changes: 2 minutes and 45 seconds & Throughput: approx. 3 MB/s
Just with this small change, process was 48 times faster, and throughput was 35 times higher
- Database connector: SAP systems like ECC and supply chain are transactional in nature, directly extracting the data from transaction database may degrade the overall system. To overcome the performance bottleneck on the primary database, the extraction was performed on the secondary database of the highly available database configuration of Oracle and SAP HANA systems. This also helped to overcome the SAP technical configuration challenge mentioned in the technical requirements section of the proposed solution.
Additionally, the oDATA connector was evaluated. However, it was not deployed to the production as it didn’t meet customer’s requirement.
The following diagram illustrates the reference architecture of the deployed solution:
Here the various component and their roles in the architecture:
- RAW: File Storage layer data are stored as is from the source systems.
- Enriched: File Storage layer where data are cleansed, standardized and Data quality checks
- Curated: Database storage layer where Business rules & transformation are applied and stored for easily consumption for Reports, dashboard, and visualization
- ADF: Data extraction & for orchestration
- DataBricks: Compute for Business, Transformation, Integration, cleansing and standardization
- Azure Synapse analytics: Enterprise integrated storage for low latency queries
- Azure SQL Database: Storage layer for Operation Data source and data that can’t be integrated
- Azure API App: Delivery data as a REST API for the external 3rd party organization
- Power BI: Visualization, dashboard, and reporting
Lessons Learned
ADF is a great solution to extract data from various source systems (SAP and non-SAP), perform transformation, and load to the target data sink.
We performed extensive analysis, comparison, and performance tests, and here are some recommendations (based on our learning) on when to use which ADF connectors for efficient data extraction from SAP applications:
- SAP Table /Open Hub– You can use RFC based protocol if the data is less than 2GB in size; however, with more than 2GB of data, protocol BASXML is recommended. Since you can’t have two protocols defined in the same configuration, use BASXML protocol to serve both the small and big size data.
- Native DB Connector – great option to extract the data from secondary database. Ensure that SAP support the database extraction method, and database provider support read from secondary feature.
Here are some learnings/best practices to consider while using the various data factory connectors.
|
SAP Application |
Data Factory Connector |
Key features and key considerations |
|
SAP ECC, SAP SCM/APO, SAP BW |
SAP Table |
• Data extraction through SAP ECC Application layer using ADF SAP Table Connector • Support full and Delta extraction • Maximum runtime configuration to be considered when defining data extraction pattern • Support low to high data size • Use BASXML protocol |
|
SAP ECC, SAP SCM/APO, SAP BW |
Native DB connector |
• Data extraction directly from Database objects • Primary database access is restricted in SAP Transaction applications (SAP ECC & APO), so leverage. Secondary database/High Availability instance for data extraction • Support full and Delta extraction • Frequency of replication between Primary to Secondary instance to be considered |
|
SAP – ECC |
SAP OData |
• Recommended for only Small data volume • Entities exposed by SAP ECC OData services • OData Services internally creates objects in SAP ECC for each ODATA services. • Recommended for very low volume (a few thousands records) |
|
SAP – BW |
SAP Open Hub |
• Data need to be moved from SAP ECC to SAP BW using (BW Business content or customer extractor) • Data extraction through SAP BW Application layer using OpenHub Connector • SAP BW will become intermediate data storage layer with transformed and Aggregated data • Support full and Delta extraction • Open Hub Destination Object need to be built in SAP BW • Support for low to high data size • Use BASXML protocol |
|
SAP – BW on HANA |
SAP HANA |
• Only when data stored in SAP HANA • Support full and Delta extraction • Support for low to high data size |
Conclusion
With the existing tools and services offered in Azure, you can rapidly deploy, build, and configure your solutions. These solutions were successfully deployed to the customer site, and many more customers are in pipeline to get this solution implemented.
You can integrate data silos with Azure Data Factory. Easily construct ETL and ELT processes code-free or write your own code. Visually integrate data sources using more than 90+ natively built and maintenance-free connectors at no added cost. Focus on your data—the serverless integration service does the rest. Read “Azure Data Factory - Hybrid data integration service that simplifies ETL at scale” for more information.