mgm data warehouse for business analyses and consolidated reporting with tamper proof audit trail

To meet the high regulatory requirements concerning data management and the reporting derived from the MaRisk (VA) by the BaFin (R3/2009), a complete historical database with a tamper proof audit trail had to be developed to enable accurately timed reporting at all times. On the basis of the mgm data warehouse reference model for insurance companies and in collaboration with the experts from Köln.Assekuranz, a solution has been created that meets information disclosure obligations of Köln.Assekuranz. Furthermore, there is a possibility to carry out expanded business analyses, thus also making it possible to provide clients with even better information services.

Enhanced requirements for business analysis at Köln.Assekuranz

As a next step, after implementing the new agency system, Köln.Assekuranz planned a solution for its reporting requirements that conforms to the regulatory requirements of MaRisk(VA):


  • Statutory reporting requirements (BaFin):
    Meeting statutory information requirements on the status of the operational insurance business including claims.
  • Group reporting within Munich Re:
    Automated preparation of monthly, quarterly and annual reports as input for the Group’s annual financial statement.
  • Historical business analysis:
    Option for business analysis with a tamper proof audit trail to freely select evaluation and observation times based on a scheduled database.
  • Reference data model:
    To guarantee the security of the investment and the expandability of the new data warehouse under preparation, the database should be developed on the basis of the mgm DWH reference model for insurance companies.
  • Cost efficient implementation:
    The implementation costs should stay within the low range of six figures.

Based on these requirements, a joint team of the customer’s experts and mgm’s actuaries and engineers conceived a solution. The mgm reference model for insurance companies was selected as the technical basis for this. Starting with the operational business information of the agency system and other neighbouring systems, a historical standardized database was developed serving as a data source providing a tamper proof audit trail for defined reporting (single point of truth).

Implementation based upon the mgm data warehouse

The implementation of the data warehouse by mgm consists of the following components:


  • Data model:
    mgm data warehouse reference model
  • Data warehouse:
  • Analysis data base:
    Oracle, MS SQL Server
  • Data extraction and mapping:
    Pentaho Data Integration/Kettle
  • Reporting:
    MS SQL-Reporting

One core area was mapping the data of Köln.Assekuranz to the data warehouse and the presentation of the reports.

“Our solution allows the user to view their business over the course of time using just a few clicks. In terms of the presentation we adjust almost in fully to the sort of presentation used by the company.” explains Birgit Becker, project manager at mgm technology partners.

Data quality creates trust

After the technical implementation of the data warehouse the intensive commissioning phase started, or to be more precise, the challenge of establishing trust in the figures within the KA staff. For this purpose, mainly colleagues from project Köln.Assekuranz meticulously checked all the numbers.
In addition, individual data had to be traced at field level on its way between the source system and data ware-house, between the data warehouse and analysis database and between the analysis database and reporting system.
The first report with error-free data could only be prepared in November 2009, after every proof concerning the accuracy of the data had been established.
One particularly attractive feature of the reports generated is that they almost completely meet BaFin and Munich Re’s ergonomic and optic standards.
Other advantages have been created for customers accessing reports via the KA customer portal (alternatively via e-mail) by using the business data in a better way.


“The whole team committed itself to the set goal of going live within three months.” said Alexander Ruppel, Head of Controlling at Köln.Assekuranz, assessing the joint project work. “With the first stage of extension of our data warehouse we can carry out our reporting in a fraction of the time previously needed.”


*  This reference has been officially confirmed by Köln.Assekuranz.