Data Warehouse Architect

At one of the largest insurers, I helped build a cloud-based enterprise data warehouse on a data lake foundation with a core DWH modeled on Data Vault principles. Initially, data from the Guidewire claims and policy system is transferred into a business-oriented, historized Data Vault environment using WhereScape, and made available to consumers via defined interfaces. The underlying Snowflake database is hosted in AWS.

In the IFRS17 year-end project, I supported data delivery from various policy and claims systems through defined interfaces. Operational system data is stored in an AWS-based data lake in Oracle and Snowflake. Using Denodo, data is moved into standardized bi-temporal historization and enriched with dynamic, business-supplied transformations (group-of-contract aggregation). The standardized historization enabled semi-automated quality checks on timing criteria. Business plausibility rules are applied on data import from source systems using Informatica PC/CDC. I defined modeling guidelines for the Denodo virtualization layer and ensured processing speed and result reproducibility.

Built a company-wide marketing DWH for customer/household selection in central marketing campaigns. I consolidated information from different business areas and delivered it with high performance. I handled requirements analysis, source system analysis, Erwin data modeling, and Informatica data management design/implementation. I also supported the data management team on performance tuning, historization concepts, and health insurance unit’s needs. For health insurance, I implemented complex pre-aggregation logic (External Rollup) in Informatica and DB2 as support for Cognos cube building.

Designed, prepared, and led multi-day “ETL Power Coaching” workshops. Performance optimization was a major focus. The workshops transferred know-how to the internal ETL team with concrete, hands-on exercises on cache handling, partitioning, and bottleneck analysis.

Quality assurance and optimization of data management processes at a leading German gas trading company. The DWH runs on Oracle, and data from various sources is loaded into DWH layers with Informatica. Reporting uses MicroStrategy. Large daily data volumes drove requirements for ETL/reporting performance, delta load optimization, and required versioning.

This project aimed to consolidate the existing DWH as an outsourcing solution in banking at a major German IT services, data center, and software house. As Data Warehouse Architect, I handled data modeling and DWH architecture integration. Beyond mapping various business areas (bank controlling, securities, sales analysis, risk controlling), I focused on versioning/historization, flexible hierarchies, and data consolidation. IBM InfoSphere Data Architect was used for modeling, DB2 for storage, and Cognos tools for analysis. Apparo Fast Edit handled plan and manual data input with audit trails. PitneyBowes Sagent managed the consolidation layer, and UC4 orchestrated processes. I also ensured modeling, ETL, and analysis tool quality and performance.

Defined and implemented various interfaces to integrate sales data from several international subsidiaries into the group DWH using Informatica. A multi-level contribution margin schema across ERP systems (VACOS, agma data) was mapped in the interfaces. Data came from DB2, Informix, and Caché sources into a DB2 iSeries environment. Interfaces run daily and monthly with automated handling of issues documented for business owners. Users access standard reports and flexible Cubeware Cockpit analysis, with permissions by division. Data marts use SQL Server Analysis Services.

Supported a top German food group in designing, building, and launching a cross-group procurement information system (EIS) integrated into the corporate DWH. Data from ERP systems (SAP, GEAC, BRAIN) loaded with Informatica, focusing on source data consolidation and historized/versioned storage. DB2/iSeries stores the data. Analysis layer cubes in SQL Server Analysis Services serve data marts. End users access analysis and standard ABC/timeframe reports in Cubeware Cockpit.

The goal was to integrate ten benefit areas of health & care insurance into an existing DWH. I focused on modeling benefits (pharmacy, doctors, transport, remedies, aids) in ERwin for DB2 and MicroStrategy. Workshops with business areas defined informational needs, which I turned into logical and physical models. I modeled the core DWH and data mart structures with MicroStrategy in mind. I also created source-to-target mappings and documented transformations across stages.

Supported a leading flavor manufacturer on their international DWH. I provided MicroStrategy expertise in various subprojects for the global analysis & reporting system.

Designed and built an analytical CRM system based on Siebel CRM and other sources, focusing on opportunity analysis over time.

Another subproject delivered headcount analysis with flexible views.

Built flexible controlling reports showing price and volume effects in detail.

A challenge was identifying recurring business by allowing users to set a percentage threshold to classify revenue as new, lost, or recurring on customer/item level.

Supported a large German retail chain in building, optimizing, and operating their corporate DWH analysis tool stack. MicroStrategy suite was used with Teradata as the relational database. I designed analysis for central invoice auditing based on SAP Retail and led the rollout process.

For a major German health insurance association, I built an analysis system for prescriptions. Informatica PowerCenter handled data management. The project included a holistic coaching transfer to internal staff. Focus was on integrating tens of millions of insured persons and tuning performance. Integration and cleansing of data from various funds was another key area.

Designed, implemented, deployed, and enhanced several data management components for the core DWH and data marts at a large German beverage group. Systems covered corporate and sales controlling, contract management, listing control, and hospitality tracking. We integrated data from SAP R/3, flat files, and other relational sources using Informatica PowerCenter. We planned various approaches to load legacy data from new subsidiaries.

Analyzed dealer surveys for a large German automaker using Microsoft Analysis Services. We built multilingual cubes and queried them with MDX. Data was kept down to contract and question level. Reports were delivered via XML for Analysis and formatted as required.

Reviewed, designed, and built an internal database marketing reporting system at a wine and sparkling wine mail-order company using MicroStrategy. We created production deployment strategies and coached staff.

Developed a CRM charter for a large German online retailer. Gathered campaign management requirements in workshops, covering operational and analytical CRM.

Enhanced the central network controlling analysis system at Germany’s leading air freight company. I set up a separate development environment and implemented various enhancements in the MicroStrategy-based system.

Built a web-based reporting and analysis environment with EUREKA:Strategy at Europe’s largest retail group. We delivered analysis systems across business areas and provided expert support to the internal DWH competence center.

• Extended click-stream analysis with banner usage metrics for partner billing.
• Planned and built an international procurement order analysis system with custom web UI.
• Analyzed user behavior (click stream) and integrated it into detailed revenue reports for the new website using Strategy scripts.
• Managed project, business analysis, advanced analytics, metadata design/admin, and scripting for SAP-based central accounts receivable analysis.
• Processed daily sales from stores and mail with intranet presentation for the board using Strategy scripting.
• Tested all DWH applications for Y2K readiness.

Developed the feasibility study for a broadcasting DWH at a major private TV station. This included workshops and a thorough tool selection.

At Germany’s leading air freight company, I delivered analysis systems for various business areas using CA’s EUREKA:Strategy and MicroStrategy DSS. I provided ongoing expert support to the internal DWH competence center.

• Designed, developed, deployed, and trained users on a global web-based sales information system for 800 users with intranet connectivity and external sales rep security. We historized and versioned dimensions and facts for reproducible reports.
• Led and supported development of a DWH-based corporate controlling system. I managed the project and built a multi-level contribution margin P&L to support the company’s new network strategy with MicroStrategy DSS tools.
• Assisted client project management with RFPs, bid evaluation, testing, and QA. Represented the DWH project across the company.
• Conducted the feasibility study for a corporate DWH, including an Oracle vs. RedBrick benchmark and an OLAP tool evaluation.