Source data migration with automation of the source data connection
short to story
As an independent company under public law, Suva is the most important provider of compulsory accident insurance in Switzerland. It insures around 2 million employees against occupational accidents, occupational illnesses and non-occupational accidents.
Seit mehr als 10 Jahren unterhält die Suva ein stetig wachsendes Data Warehouse als Datendrehscheibe für den internen und externen Informationsbedarf.
"Seit mehr als zehn Jahren ist synvert saracus consulting AG ein kompetenter Partner, der uns in allen Bereichen der BI Entwicklung unterstützt. Von der Business Analyse über die Entwicklung komplexer ETL Strecken bis zum Reporting übernehmen die Berater der synvert saracus consulting AG sämtliche Arbeiten. Dank des guten Know-hows und der guten Sozialkompetenz können die Projektziele jederzeit erreicht werden. Die Zusammenarbeit ist so einfach und gut, dass wir uns jederzeit auf die Arbeit konzentrieren können.
Die Zusammenarbeit ist ein echter Erfolg!"
Goals & challenges
After more than 20 years, a new claims and premium system had to be introduced. This changeover from the self-developed, Cobol-based insurance system to an industry solution extended for Suva (AdCubum Syrius) fundamentally changed the data foundation for the DWH. The DWH had to be adapted to the new requirements with the following objectives:
- Migration of all data due to the replacement of the old system with a new, operational system
- Transfer of the entire database
- Automation of the source data connection
- Maximum possible further use of the existing core DWH layer
- Consistent data quality
requirements
The following additional requirements were also placed on the new DWH:
- Introduction of an additional BI tool
- Introduction of an additional system for operational reporting to be implemented with BI technologies
- Conversion from monthly load to daily load
key points
The PHAROS project posed the following challenges:
- As not all legacy data was transferred to the new source system, the data had to be archived separately as part of the DWH. This archive now serves as an additional data source in order to continue to provide a complete view of Suva’s entire data history (from 1918).
- Use of a new BI technology (OBIEE)
- Optimization of loading times and increased need for automation by switching the loading cycle from monthly load to daily load, as at least the operational reports must be supplied with new data on a daily basis.
- High time pressure for timely completion, as the DWH also had to be adapted to the new source data structures by the time the operational system was introduced
architecture
In order to cope with the rapidly increasing number of source objects, a generic approach was used to process mutations from the source system. New tables can now be registered using entries in metadata tables. A PL/SQL framework (GenDel) provides the deltas of the data to be processed using database views. Informatica PowerCenter is still used for the ETL processes. The consolidated anchor modeling of the core DWH introduced by synvert saracus in previous projects provided the ideal basis for reusing existing structures and also ensured the flexibility of extensibility.
An additional BI tool, ORACLE Business Intelligence Enterprise Edition (OBIEE), was added to the existing SAS infrastructure. With ORACLE as the strategic database, the strengths in the area of Online Analytical Processing (OLAP) can be utilized. OBIEE is also used as a front end for operational reporting.
generated benefits
- By using GenDel, the source data connection can be automated to the highest degree up to the DWQ layer close to the source
- By switching to a daily charging cycle, data can be made available more quickly
- In addition to planning evaluations, requirements for operational evaluations can also be and data interfaces can be created with high cadence from the DWH
- Strengthening the data warehouse as a central data hub within Suva
services accomplished by synvert saracus
As a competent partner, synvert saracus provided the following services:
- GenDel implementation for automating source connection: instead of individual ETL processes, new source tables can now be connected much more efficiently and easily
- Analysis and detailed design of entire data areas in the DWH (e.g. costs, premiums and payroll totals)
- Execution of the entire project cycle for operational reporting: from requirements analysis and design to implementation and testing of the system and reports.
delivery / conclusion
The conversion of the DWH was implemented on schedule. Thanks to the neutral core DWH modeling, which was already based on technical logic in earlier project phases, a large part of the existing data model could continue to be used - despite the complete conversion to a new source system - and the importance of the DWH within Suva was further strengthened. By using GenDel, Suva meets the growing demand for automation of ETL processes and has a flexible and freely configurable interface for the simple connection of additional source data
