
Brief history
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.

“For more than ten years, synvert has been a competent partner that has supported us in all areas of BI development. From business analysis and the development of complex ETL routes to reporting, synvert's consultants take on all the work. Thanks to their excellent know-how and good social skills, the project goals can be achieved at all times. The collaboration is so easy and good that we can concentrate on the work at all times. The collaboration is a real success!”
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 extended requirements were placed on the newly created DWH, which went beyond the basic functionalities and necessitated a comprehensive expansion of the architecture as well as a far-reaching adaptation to the needs of the specialist departments and the company as a whole. These requirements reflect the constantly growing need for efficient data processing, flexible data analysis and prompt provision of information in order to meet the dynamic requirements of the company:
- 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
As not all legacy data was transferred to the new source system, a separate archiving solution was implemented as part of the data warehouse (DWH). This archive now functions as an additional data source and enables a complete view of Suva's entire data history since 1918. A central component of the project was the use of a new BI technology (OBIEE) to improve the analysis and reporting functionalities. In addition, the loading cycle was changed from a monthly to a daily load in order to provide the operational reports in particular with up-to-date data on a daily basis. This led to an optimisation of loading times and an increased need for automation. The implementation was under great time pressure, as the DWH had to be adapted to the new source data structures at the same time as the new operational system was introduced. Timely completion was therefore essential to ensure a smooth transition and continuous data availability.
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 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
- 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