Performance audit on SAP BODS ETL flows on MS SQL Server
Context
Business intelligence project
Challenge
Evaluate the load carried by the ETL and source and target databases of ETL interfaces
Technologies used
DBMS: MSSQL Server 2008R2 then 2016 Enterprise Edition • ETL: SAP BODS 4.2 • Reporting: SAP BO XI then SAP BO BI 4
Methodology and implementation
Complete audit of the ETL platform and associated databases:
**Methodology** 1. Instrumentation of SAP BODS flows 2. Server resource monitoring (CPU, RAM, disk I/O) 3. Analysis of extractions from Progress sources 4. Study of impact on SQL Server target database 5. Review of data modeling
**Findings** - Undersized disk infrastructure for ETL load - Non-optimized Progress extractions - Oversized staging tables with excessive historization - Absence of purge strategy
**Implemented solutions** - Storage infrastructure upgrade (SSD + RAID optimization) - Rewriting of Progress extraction queries - Modeling redesign: switching from full history tables to daily snapshots - BODS transformation optimization for new SQL Server 2016 - Implementation of automatic purge strategies
Results
Change of disk infrastructure to support the very heavy load demanded by ETL processing on the MSSQL database. Optimization of extractions from Progress sources. ETL processing optimizations to use the new reinforced SQL platform. Reduction of certain tables by 1000 by changing data modeling. Processing ends at 5am instead of noon and database volume is reduced by 600GB
Visualizations
Heavy ETL interfaces dashboard
Gantt chart and parallelism
Table and database volumes
Data growth
Database size evolution