Massive Legacy ERP Data + Reports Migration with Power of HANA Enterprise

[Former Member]

Introduction

This blog is intended to showcase a case study of massive data + report migration (AS400 -> HANA) project during large scale end to end greenfield HANA Enterprise Implementation. HANA Live RDS was already deployed in previous phases to set the basic building blocks.

Objective

Earlier this year, I was presented with challenge of migration of large number of crystal reports from legacy AS400 applications to state of art HANA enterprise.

Few main obstacles I figured out earlier while planning –

1. Large number (~150) of crystal reports with steep timeline
2. Complexity of logic in reports – extreme hardcoding, restrictions on AS400 ERP hard codes/sub reports (just like traditional hardcoded ABAP programs in another ERP system)
3. Of course, we want to migrate up to 2 years of AS400 data history (all sales, finance & master data) along with reports (yayyyy!! :shock: )

Approaching Solution

During several blueprint sessions, we realized this massive crystal architecture was developed with available technology (AS400 MAPICS ERP) + crystal designer 2008. This was matured over years and if we have to rebuilt (with history data), it was going to take real long time. Also, due to audit and validation requirements, collective decision was made to keep every crystal report AS-IS and model SAP HANA backend to match existing AS400 backend.

Wait a min, what? Did I hear it right, yes, you did. :sad: :shock: :grin:

Couple of rationales behind this decision:

1. Analysis of existing AS400 MAPICS ERP suggested, that there are handful of Master data, Sales, Billing, Finance tables in AS400 (~30) on which all these reports are built. These are much simpler tables than SAP and modeling SAP data to match legacy might work. (Still “might”, coz I’m sure no one has done it before)
2. Crystal reports migration procedure lets us swap the data source of report if all technical names of fields are exact match. This will make report migration as simple as flipping switch on source systems. Minor tweak needs to be done but no need to rewrite report on new source.

Challenges that we envisioned

1. Of course these are two different ERP systems with totally disparate table structures, seriously are we going to harmonize it?
2. Data type mismatch – (let’s not even start about it!)
3. How about hardcoded codes/types. They won’t even exists in SAP.
4. Date types (AS400 – CYYMMDD – century y2k format, SAP – yyyymmdd)

Solution

UNION…UNION…UNION (of course hana node) was running in my mind when I literally started thinking of this project and that’s the recipe.

Prep

1. Reverse engineered all crystal reports to documents which backend data elements are being used.
2. Documented all relevant AS400 tables with exact technical names/ data types.

Design

1. Performed massive mapping exercise to map AS400 tables to SAP tables/ data elements. Only for those fields which are used on reports (from step 1.a)
2. HANA Live RDS was already deployed in previous phases, so majority of SAP data/structures were in place

Realization

1. Created new schema to house history data (Same name to be used in dev/QA/prod for simplicity).
2. Created AS400 table structures in new schema (WITH EXACT field technical names). E.g. below – all field technical names are driven by AS400 field names

3. Developed HANA calculation views with union node and only AS400 data (right node below)

4. Once we had all HANA calculation views (with exact same technical names), crystal report migration began in parallel (even without having completed SAP data mapping)

5. Now comes the fun part – to actually model SAP HANA data to match AS400 structure using our mapping documents. Some of calculation views became very complex with several levels (~25-30) of joins from base ECC tables. Showing couple of simpler structures for your reference.

Another Example

Mappings in UNION

AS400 part matches one to one because target HANA structure matches with legacy table structure (remember we are modeling HANA for AS400 structures)

But SAP data needs to be mapped one by one as follows:

6.  We developed several mapping cross reference tables to harmonize hardcodes (e.g. GL account mapping between two ERP systems). So SAP data would give us new code, which will be translated using cross reference and crystal report continues to use old code.

7.  Once complete, new SAP HANA data magically appeared on EXISTING crystal reports!!! :smile:

Migration/Cutover

1. Obtained historic data in CSV files and loaded using file import wizard in dev system.
2. Reconciled data with business to obtain sign off
3. Once data was signed off in dev/QA system, we actually transported table structuresWITH data to avoid data load times. This turned out to be great decision and saved us huge time (THUMBS UP HANA Enterprise…$$$ saved!!!)
4. Over few weeks (couple of month end closes) most of the crystal reports stabilized. Of course, we have issues but all changes and enhancements were super quick due to HANA modeling.

CONCLUSION

1. Overall project delivery was accomplished around in 7 months timeframe with handful of BO Crystal developers and senior HANA / BW/ ECC architects.
2. HANA Enterprise (with real-time SLT), significantly saved implementation costs to achieve this impossible gig. I cannot imagine accomplishing such complex with BW extractors and multi providers.
3. I don’t think from performance perspective this is great solution and HANA union may not be smart enough to just branch out in specific data set (like our multi providers); but overall ease of HANA modeling and performance of columnar storage saved the day!
4. Clearly proves the flexibility and agility of HANA modeling to literally mold SAP to any other legacy ERP.

Hats off to our team and of course HANA Enterprise!! :smile: Cheers!!!