English
AnalyticsCreator Congress 2021 data warehouse automation at a big German insurer
No video selected.
Select a HubSpot video or paste a YouTube embed URL.
Questions
- How was AnalyticsCreator used in a large insurance data warehouse project?
- How do insurance companies structure contracts and product hierarchies?
- Why is historization important for insurance contract data?
- How was the enterprise data warehouse architecture organised?
- How did AnalyticsCreator help manage more than 3,000 objects?
- Why were AnalyticsCreator repositories separated into multiple modules?
Key Takeaways
- The project was implemented for a large German insurer focused on group controlling and reporting.
- Insurance products were structured hierarchically into business lines, insurance types, products, and tariffs.
- Main insurance domains included life insurance, health insurance, legal protection, and composite insurance.
- The controlling department required KPI analysis by product, sales area, broker, premium, and insured amount.
- The target architecture replaced multiple heterogeneous data warehouses with one central enterprise warehouse.
- The architecture used staging, integration, and business layers.
- Sensitive personal data was separated into dedicated schemas because of European data protection requirements.
- The modelling approach used Data Vault-inspired linked structures between contracts, partners, finance, and process data.
- Historization was critical because insurance contracts change over long periods of time.
- AnalyticsCreator supported SCD1, SCD2, source historization, and process-table historization.
- The implementation included more than 600 tables, 2,200 views, and 520 procedures.
- Repository separation improved synchronization speed, deployment performance, and parallel developer work.
- Power BI and SAS were the primary reporting and analytics tools.
Transcript
The next session is presented by Amir Nasirai from D5. This session focuses on a data warehouse implementation with AnalyticsCreator at a large German insurance company for internal group controlling.
The first part explains how insurance business structures influence the data model, including products, sales channels, brokers, consumers, and controlling requirements.
Insurance companies are divided into lines of business such as health insurance, legal protection, life and pension insurance, and composite insurance. Each line contains product hierarchies, from insurance type to product and tariff options.
The main parties are the insurer, the sales market, and the consumer. Controlling teams analyse dimensions such as line of business, product type, sales area, distribution channel, contract status, and payment method. Key KPIs include premium sum, insured amount, premium per agent, and insured amount per agent.
The previous data warehouse landscape was complex, with many heterogeneous data warehouses and data marts. Operational systems provided life insurance contract data, partner and broker data, composite contract data, and finance data.
The goal was to create a central Microsoft SQL Server data warehouse with fewer data marts and reduced complexity. Power BI was introduced as the main analysis and reporting tool on top of the BI layer.
The technical architecture follows a three-layer model: staging area, integration layer, and business layer. The staging area acts as the import layer, while the integration layer handles raw data, personal data, business rules, and dynamic data.
Personal data is separated because of European data protection requirements. Business rules are stored in mappings and control tables so they can be changed centrally. The business layer provides data marts, ad hoc views, standard reporting, and cubes.
The model was implemented in AnalyticsCreator with test, integration, and production environments in Azure. AnalyticsCreator helped build prototypes of tables, views, and stored procedures quickly and made data lineage easier to explain to the client.
Historization was especially important because contract data changes over time. AnalyticsCreator’s historization procedures, including SCD Type 1 and SCD Type 2, helped create import, historization, and persisting logic efficiently.
The project became large, with 628 tables, 2,208 views, 520 procedures, and around 3,400 objects in total. To manage this, the repositories were separated by topic areas and layers, such as customer data, contract data, staging, integration, and business.
This improved synchronisation, enabled developers to work independently, reduced the impact of errors, and made deployments faster. The project shows how AnalyticsCreator can support large-scale enterprise data warehouse modelling with complex insurance data
