Enterprise DWH with Data Vault

The customer requirement and objective of the project was to provide a large number of different file formats, which had to be integrated and aggregated in a data warehouse using a flexible and configurable loading process. The previous loading process was unable to provide the data in the required quality or time frame due to a large number of manual interventions. As a result, the data could not be provided in a timely manner or with the required quality. sumIT was able to successfully implement the customer’s requirements in terms of data quality and provision with process-specific metadata and a data vault modeling approach.

Challenge

 
  • Development of an enterprise data warehouse for corporate management
  • The challenge was the high number of heterogeneous source system interfaces, which cause problems in traditional evaluation systems due to frequent structural changes.
  • With the help of data vault modeling, the data was to be broken down into such granular detail that it could later be merged at the most granular level. Purely according to the principle of the “lowest common denominator.”
  • The aim of the project was to use an enterprise data warehouse to make all relevant information for controlling the company available in a quality-assured and aggregated form in the future.
  • Need for a flexible loading routine that responds to structural changes in source files and automatically makes the data available in the target system.

Approach and customer benefits

 
  • Structured recording of requirements and creation of a technical/IT concept
  • Conception and design of a data warehouse that reacts flexibly to structural changes in the delivery files during the loading process
  • Creation of mappings that read cross-process standard tables from external tables and automatically process structural adjustments in the target
  • Creation of a data process that independently calls up the data loading, cleans and enriches data if necessary, and then generates a CIP standard file
  • High degree of harmonization and integration of source system data for uniform reporting and corporate management
  • High flexibility in data integration through interface changes in the source systems
  • Reduction of manual data adjustments due to constantly changing data structures
  • Early provision of company data due to flexible data modeling
  • Higher data quality through integration of all data content necessary for KPI calculation
Delivery sumIT
  • Gathering requirements and use cases for deriving the data model
  • Modeling the data in the Data Vault approach with satellites, hubs, and links
  • Establishing the loading processes and populating the data model
  • Providing an automated data interface that flexibly and independently recognizes and integrates data structures
  • Providing dimensionally and domain-specifically modeled data marts for high-performance reporting and interface support

Tools and technologie

    • Data Vault 2.0
    • Oracle Datenbank (DBMS)
    • Oracle data management with SQL/PLSQL
    • Oracle Data Integrator