Source data migra­tion with auto­ma­tion of the source data connection


short to story

As an inde­pend­ent com­pany under pub­lic law, Suva is the most import­ant pro­vider of com­puls­ory acci­dent insur­ance in Switzer­land. It insures around 2 mil­lion employ­ees against occu­pa­tional acci­dents, occu­pa­tional ill­nesses and non-occu­pa­tional accidents.

Seit mehr als 10 Jahren unter­hält die Suva ein stetig wach­sendes Data Ware­house als Datendreh­scheibe 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!"

Reto Christen (Suva)

Bereichsleiter VTI (Competence Center Data Warehouse)

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:

  • Migra­tion of all data due to the replace­ment of the old sys­tem with a new, oper­a­tional system
  • Trans­fer of the entire database
  • Auto­ma­tion of the source data connection
  • Max­imum pos­sible fur­ther use of the exist­ing core DWH layer
  • Con­sist­ent data quality


The following additional requirements were also placed on the new DWH:

  • Intro­duc­tion of an addi­tional BI tool
  • Intro­duc­tion of an addi­tional sys­tem for oper­a­tional report­ing to be imple­men­ted with BI technologies
  • Con­ver­sion from monthly load to daily load

key points

The PHAROS project posed the following challenges:

  • As not all leg­acy data was trans­ferred to the new source sys­tem, the data had to be archived sep­ar­ately as part of the DWH. This archive now serves as an addi­tional data source in order to con­tinue to provide a com­plete view of Suva’s entire data his­tory (from 1918).
  • Use of a new BI tech­no­logy (OBIEE)
  • Optim­iz­a­tion of load­ing times and increased need for auto­ma­tion by switch­ing the load­ing cycle from monthly load to daily load, as at least the oper­a­tional reports must be sup­plied with new data on a daily basis.
  • High time pres­sure for timely com­ple­tion, as the DWH also had to be adap­ted to the new source data struc­tures by the time the oper­a­tional sys­tem was introduced


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.

gen­er­ated bene­fits

  • By using GenDel, the source data con­nec­tion can be auto­mated to the highest degree up to the DWQ layer close to the source
  • By switch­ing to a daily char­ging cycle, data can be made avail­able more quickly
  • In addi­tion to plan­ning eval­u­ations, require­ments for oper­a­tional eval­u­ations can also be and data inter­faces can be cre­ated with high cadence from the DWH
  • Strength­en­ing the data ware­house as a cent­ral data hub within Suva

ser­vices accom­plished by syn­vert saracus

As a competent partner, synvert saracus provided the following services:

  • GenDel imple­ment­a­tion for auto­mat­ing source con­nec­tion: instead of indi­vidual ETL pro­cesses, new source tables can now be con­nec­ted much more effi­ciently and easily
  • Ana­lysis and detailed design of entire data areas in the DWH (e.g. costs, premi­ums and payroll totals)
  • Exe­cu­tion of the entire pro­ject cycle for oper­a­tional report­ing: from require­ments ana­lysis and design to imple­ment­a­tion and test­ing of the sys­tem and reports.

deliv­ery / 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

Success Stories

other success stories


Insights from our cus­tom­ers

Your message

Are you interested in tackling your projects with us?

Send us a message!

* Required fields