The 29FORWARD project team took on the task of developing and implementing a modern data warehouse based on Data Vault for a bank in North Rhine-Westphalia. The aim was to sustainably improve data analysis and at the same time create the technical basis for scalable and rule-based data processes
The following problems had to be solved:
Before beginning its collaboration with 29FORWARD, the North Rhine-Westphalian bank was faced with a number of fundamental challenges in its existing data architecture:
Lack of data consistency and structure: The existing data structure was so complex and inconsistent that it posed a significant risk of incorrect evaluations and decisions
Outdated and unmaintainable data marts: Many existing data marts were no longer operationally usable due to historical burdens and repeated adjustments. Data maintenance was time-consuming, error-prone and hardly scalable.
Creation of a shadow data warehouse: Although numerous external data sources were integrated into the analysis processes, they were not recorded in the central data warehouse – with the result that a parallel, uncontrolled shadow data warehouse was created, which made data governance much more difficult.
This initial situation made it clear that a far-reaching modernization of the data architecture was required in order to create a reliable, scalable and future-proof analysis basis. The use of an advanced data vault model promised the necessary flexibility, traceability and structure to make the complex data ecosystem manageable in the long term.
Optimized data flow in reporting
In order to make data management future-proof and efficient, 29FORWARD implemented a data vault architecture that serves as a scalable foundation for all of the bank’s current and future data requirements. This model supports parallel data loading, facilitates the integration of new data sources and creates a robust basis for flexible, growth-oriented data management.
As the bank was already working with SAS and SAS Visual Analytics (VA), the project team specifically used SAS Base and SAS Macro to efficiently control and automate all ETL processes. This consistent use of existing tools accelerated the implementation and significantly reduced the amount of training required.
Another key element was the creation of a raw-in layer, which centrally records all incoming raw data as a data integration layer. In this upstream layer, 29FORWARD collected, harmonized and structured the data before it was transferred to the actual data vault. This enabled the team to consolidate contract information from four different source systems, for example, and assign it to a common hub – a significant step towards a standardized and traceable data model.
Orientation and semantic modeling of data and analysis of existing reports
At the same time, 29FORWARD obtained an overview of the initial situation.
29FORWARD controlled the Vault loading via a central Excel tool that contained all relevant information on the raw-in layer and the individual Data Vault components. The team took advantage of the data vault model and filled hubs, links and satellites in parallel – without being dependent on each other. A clear loading sequence was only necessary due to the downstream transformation layer and the defined cleanup.
At the same time, the team analyzed the existing system landscape. It recorded all proprietary, historically grown systems and transferred the structure into a semantic data model. This model served as the basis for an initial logical data model with 8 hubs, 15 links and 16 satellites.
29FORWARD systematically worked its way through 280 reports and 30 data marts in order to correctly map all loading processes and reports. The aim was to map the specialist logic completely and consistently in the new data model.
Raw Vault secures data source – Business Vault maps specialist logic
The team stored all data in the Raw Vault in a source-authentic and unaltered form – a key step towards implementing the single point of truth. The business logic and report calculations are now based directly on this secure database.
In the next step, 29FORWARD transferred the content to the Business Vault, where specialist structures such as Bridge Tables or Point-in-Time Tables (PIT) were used for the first time. These enable time-based analyses and facilitate complex connections between distant hubs. This created a powerful functional layer for reporting.
Monitoring accelerates charging processes and increases transparency
The team set up a separate monitoring system for each load – whether hub, link, satellite or transformation. This tracking records runtimes, last processing times, data volumes and anomalies. In the event of an error, affected tables can be specifically reloaded.
To minimize loading times, 29FORWARD implemented the data vault logic, in which new data is appended instead of sorted. The functional assignment takes place later in the Business Vault.
Through monitoring, the team identified slow processes (long runners) and grouped fast loading processes into batches. This optimized job grouping noticeably reduces the overall runtime due to parallel loading processes.
Visualization and hashing optimize Vault architecture
For a better overview, 29FORWARD created a graphical representation of the data vault in Microsoft Visio. Using Data Vault-compliant symbols, the team visualized all levels – if desired, only hubs, links or satellites – including the key structures.
The architecture uses hash values as business keys to uniquely identify business objects. In order to avoid collisions (hash collisions) as far as possible, 29FORWARD used extended hash lengths and supplemented the architecture with control mechanisms for the automated detection and reporting of possible conflicts.
Data architecture grows with the complexity of the systems
While the original semantic model only mapped simple business processes, the team quickly recognized the need for expansion. Many operational systems had missing timestamps or simultaneously valid data rows – a complex scenario for historicized data models.
29FORWARD expanded the model iteratively. In the end, it comprised 62 hubs, 72 links and 169 satellites – a structured representation of even the most demanding data constellations.
Project result: Efficient, expandable data warehouse with full transparency
The bank now benefits from a fully automated data vault-based data warehouse. The system loads raw data and business information automatically – controlled via a central Excel interface.
All reports now use valid, centralized data sources. New reports or additional data can be easily integrated. Loading times have been significantly reduced, monitoring increases transparency – and the new architecture creates the basis for sustainable data quality in reporting.
The 29FORWARD team of this project would like to thank you once again for the efficient and enriching cooperation!
