Uncategorized user January 29, 2023

Modernize your Data Warehouse on the Cloud

– Migrate from SAP BW to AWS Redshift

Background

Any modern enterprise in general would have a minimum of three essential elements as part of its IT strategy: A robust ERP system, a data warehouse platform that usually runs on a batch managed (ETL) process and a BI analysis and reporting tool. Forrester refers to this as the “layer cake” BI architecture.

One of UXLI’s clients had a similar setup – SAP ERP, BW and Business Objects (BO) and approached us to help decommission and migrate their existing SAP BW & BO systems, to a “modern” data warehouse in cloud.

The expectations from the new bi solution included:

Leverage existing infrastructure as much as possible while utilising advances in technologies that will aid new business intelligence functions
Allow a fresh visual approach to delivering data; thereby achieving better adoption rates, pervasiveness and an incentive to use the solution
Central governance of data with flexible self-service BI opportunities for business departments
Enable data exploration opportunities from dynamic data sets and the ability to link this information with non-SAP ERP data sets to enable a 360-degree view to the management, leading to improved decision-making
A flexible and scalable architecture so that emerging technologies such as Data Science, Artificial Intelligence and Machine Learning can be introduced as required
Maintain cost parity – will not cost more than the existing BI platform (in contrary opportunities to save cost), but provides more functionality
Scalable data warehouse to accommodate growing data volumes on demand

Typical challenges faced by the customer were:

Slow responses – both from a solution turn around and (long) report execution times
Inability to get SAP & Non-SAP data together – leading to heavy reliance on data extraction and local Excel analysis
Inflexible and complex BI front-end tools forcing reliance on IT
Time consuming SAP BW changes to data model
Limited opportunities of data exploration and analysis for new models
Limited self-service

The UXLI solution team was faced with the following restrictions:

Unchangeable data sources – the SAP ERP system that formed the transactional backbone
Non-SAP data sources including websites which would push / pull information, files that would be dropped by the business at random etc
Retaining the security requirements of the existing solutions

Solution

After an early assessment of the requirements, our proposed solution involved the use of multiple cloud-based technologies architected to work seamlessly while leveraging and recognising the boundaries of the existing technologies.

The customer benefitted from retaining the existing core systems and there was no impact to their existing business processes (figure below). Migration to the new data warehouse was achieved for all the core business processes including finance (FI), Inventory (MM/IM), logistics (SD), procurement (PUR) and plant maintenance (PM).

The new data warehouse (figure below) was flexible, scalable and not restrictive. The business was able to explore data in different ways. Reporting was possible from multiple technologies – PowerBI and /or BIRST.

Technologies used for the solution included:

AWS S3 Service – used for staging and data lake formation.
AWS Glue – used for Metadata and Data Catalog Management
AWS Lambda – used for event management and serverless workflow execution
AWS RedShift – backbone for transforming and consolidating data gathered from multiple sources
BIRST – A cloud-based data warehousing solution to improve reporting performance and create smart interactive dashboards
AWS API gateway – used to communicate with other applications using warehouse data

Sources:

Data was sourced from the underlying SAP ERP system by reusing the SAP Standard and Custom Extractors. The system benefitted from the built-in delta management mechanism.
Additional information required by the business was extracted by querying the SAP tables and views directly
The platform is also open to extract data from non-SAP data sources as required

Data Lake formation:

Data Lake is an important complement to the modern data warehouse. All the SAP and non-SAP data is extracted into the Data Lake. The data lake also allows the interconnection of data sets to explore and identify early patterns / indications without going through an elaborate development cycle. Early growth allows the storage of structured and unstructured data at any scale and helps improve data/information predicative operations by using alerting algorithms.

Data Management:

AWS Redshift forms the backbone of the data warehouse, providing information to all downstream applications. It is used to govern and manage the data models and acts as a shared meta data library.

Data Services:

A data warehouse becomes truly flexible when it allows the Line of Business (LoB) users to use the corporate and local Business Intelligence layers seamlessly. The LOB users are able to have their own Operational Reporting, Exploration, Text, GIS or simple data extraction services; thereby reducing the IT dependence.

This flexibility and independence removed the dependence on a single BI toolset. “Bring your own BI tool” became a reality!

Security:

Information / Data security remains the top priority. UXLI recommended a “least privilege” approach which means that data is exposed to users only on need basis. The solution used the inherent infrastructure and related services security provided by the cloud platform and the customer was responsible for data content and user security.

Solution Highlights

Achievements:

The new data warehouse in the cloud was ready for business use within six months from inception.

No disruption to the business. LOBs continued to use SAP BW while the migration was on
Reports and content were migrated in logical groups to minimise impact on business functions
Finance was the first function to go live within four months of project initiation, in time for the financial year end
Inventory (MM/IM), logistics (SD), procurement (PUR) and plant maintenance (PM) functions were ready within the next three months
Since Go-Live, UXLI has implemented further projects and has been supporting the system
Implementation of the above solution resulted in the rapid deployment of:

Performance was improved in three ways:

By redesigning the data model in AWS Redshift and ensuring performant joins between facts and dimensions
By including all the complex joins within the AWS Redshift layer
This redesign also proved advantageous to the reporting layer providing the muchrequired boost to performance

Scalability was addressed on two fronts :

New business contexts / attributes can be directly added to the AWS Redshift data model and processing only the impacted dimensions in the reporting layer
A Networked BI layer was created which led to:
– LOB wide data spaces for Transaction & Master Data provide a reusable foundation for a single source of truth for all future reporting initiatives – Allowed the creation of a LOB wide corporate reporting portugal

Improved Usability :

Self-service BI enabled – making it easy for the users to search for information and perform their analysis
Data-driven Interactive Dashboards designed to tell a story on a single page. Users can also filter and navigate to the details to gain further insights

Delivery Methodology

UXLI’s Agile Business Intelligence approach was applied for the timely delivery of this project. Our Agile BI methodology is focused on creating business value through the early and frequent delivery of working BI solutions.

Data and information rather than processes and tools
Business insights rather than comprehensive documentation
Team collaboration rather than contract negotiation
Responding to ideas for change rather than following a rigid plan