This article describes how our customers utilized Azure data platform and AecorSoft ETL Solution capabilities to bring complex data out of S/4HANA for advanced analytics in an effective, efficient, and economical manner. This article provides reference architecture for modern Cloud based end-to-end analytical solutions.
Successful data warehousing solutions should provide trusted and integrated data with flexible scale-out architecture using Cloud.
Data is becoming large, complex, and is coming from a variety of sources and forms. Data warehousing solutions need to provide secure, trusted, and integrated data in order to derive meaningful value.
Being able to effectively drive business values from the massive enterprise data is more important than ever to the IT enabled business. In the era of digital transformations, businesses are shifting IT enterprise data solutions from on-premise to Cloud. This is to take advantage of the elasticity, scalability, security, and supportability of cloud storage and computer power.
This architecture has three components: dataflow from S/4HANA into Enterprise Datawarehouse, Operational Analytics using S/4HANA Embedded Analytics, and Excel based Operational Analytics.
The architecture diagram displays dataflow from S/4HANA system into SQL Server Datawarehouse using the SSIS based AecorSoft data extraction tool. The AecorSoft SSIS integration toolkit can be used with SSIS or Azure Data Factory and allows extraction of S/4HANA data from ODP DataSources, CDS Views (ODP CDS DataSources or CDS View), HANA Models, Table, InfoSet Query, BAPI/RFC, BW InfoCube, and BEx Query.
In our implementation, we used the AecorSoft SSIS integration toolkit to create an SSIS dataflow. This dataflow extracts data from the SAP standard content datasource, as well as CDS views, across all functional areas. It supports delta mechanisms such as initialize with or without data transfer, delta load, repeat of last delta, full load, and flexible load with SSIS variables.
Extracted data is loaded into a staging table as it is with batch control logic. Second dataflow moves data from the staging table to a fact or dimension table where data is inserted or overwritten based on delta logic.
We also have dataflows to load data from relational databases, non-SAP systems, Excel, and flat files. This data is included with S/4HANA for integrated reporting purposes.
Azure analysis service is used to create tabular models that run in-memory or in DirectQuery mode, connecting facts and dimensions directly from back-end SQLServer. Azure Analysis Services delivers fast access to tabular model objects and data by reporting client applications like Power BI and Excel. Certain high availability dashboards and reports which need faster response time can use in-memory models which use state-of-the-art compression algorithms and multi-threaded query processors to render dashboards and reports very quickly.
When data refresh rate or data volume is very high, we use the DirectQuery model which provides faster throughput using query optimizations.
Here is a sample tabular model created on GL line items connected to GL Account, Company Code, Vendor, and Customer dimensions:
Sample report in Power BI from this tabular model:
The S/4HANA system has built in real-time analytics with an intuitive user interface known as S/4HANA Embedded Analytics. Core components of Embedded Analytics are Analytical Fiori Apps, Query Browser, Multi-Dimensional Reports, Smart Business KPIs, Smart Business Cockpits and Analysis Path (APF), Overview Pages, SAP Lumira (optional), SAP Business Objects reporting (optional), and SAP Analytics Cloud (SAC, optional).
The Foundation of Embedded Analytics is ABAP Code Data Services (CDS) which creates rich semantic virtual data models on base transactional tables. SAP has provided thousands of standard CDS views that encompass all lines of business and industry solutions. This view can be further enhanced using extensions, or we can build custom views. CDS views are consumed by Fiori Apps, Data extractions for EDW, SAP Lumira, SAC, etc.
Excel based Operational Analytics
SAP Business Objects Analysis for Office can be used to connect transient provider and analytics queries based on CDS views with special annotations. Once connected, data can be explored in Pivot table-like interactive interfaces on SAP Data.
We used the AecorSoft Excel reporting tool which enables Excel-based real-time, self-service querying and reporting on S/4HANA and BW data. It can connect to SAP Table, CDS View, BAPI/RFC, SAP Query, SAP Extractor, InfoCube, or BEx Query. It also provides Pivot table-like interactive interface on SAP Data.
The below screenshot shows an Excel report with Query on CDS transient provider from S/4HANA system.