How can repeating tasks be simplified? The question of automating processes in a data warehouse (DWH) keeps project teams busy.
The need for a comprehensive automation product then usually arises very quickly. Meanwhile, there are many products on the market: established ones, newcomers and in-house developments. Project teams are spoiled for choice. And not infrequently experience disappointment after the product selection or product launch.
So what is it all about? Many data warehouse project teams have dealt in the past or are currently dealing with the question of how the development and execution of processes in a data warehouse (referred to as data solution in the following) can be automated: How can recurring tasks be simplified and the development of processes be carried out with as little effort as possible?
Especially in a Data Solution with many repetitive patterns - for example, with Data Vault - there is a desire for an automation tool that supports teams in development and promises an advantage in overall performance.
I describe what the expectations are for an automation solution, what vendors promise, and what the reality ends up looking like in this multi-part blog post.
Expectations on automation
The project teams' expectation of an automation solution is to support the new development or a necessary restructuring and reorganization of a data solution. At the latest when the acquisition or development of an automation solution is being considered, a number of buzzwords pop up in the minds of team members.
Automated ETL
Automated creation of all ELT or ETL data logistics processes - When data logistics processes are developed manually, they are prone to implementation errors or, if the loading pattern changes, are difficult to update. This is often the starting point for considering an automation solution. After all, these are mostly recurring patterns (tables and processes) that should be easily automated.
Simplification
Simplifying the development process - How can the project team simplify the work to be done while delivering artifacts faster? Is this possible with a suitable tool? It is expected that the automation solution will simplify the daily work.
Orchestration
Orchestration of data logistics processes - Many small processes (often several 1000) exist in the Data Solution and need to be executed in parallel and/or in a specific order. If these all have to be manually inserted into an orchestration tool, this is very time-consuming and the automation solution is expected to take over this task.
Metadata
Metadata, the DNA of the Data Solution - At the latest, teams are becoming aware of the need for metadata. It is expected that they will be the foundation to create, simplify, orchestrate and finally automate the processes as much as possible.
Speed
Faster delivery of artifacts - Previous thoughts and buzzwords about an automation solution lead to the expectation that automating, simplifying, and orchestrating with metadata will make the delivery of artifacts faster.
Data Vault Standard
Use existing Data Vault standards - Of course, it is expected that all Data Vault standards are supported. Regardless of which Data Vault standard is planned or already in use in the Data Solution. And the automation solution should be adaptable to the needs in the project, such as reusing standards from a book or another automation tool.
Insensitive
Insensitive to (external) changes - If something changes in the operational system or new data needs to be integrated, it is expected that the resulting changes or refactoring can be done very easily in the Data Solution.
Integration
Operational Systems Integration - EIt is expected that the integration of different operational systems will be easy to manage. This also applies to support for the integration of different, overlapping or competing business keys.
Top 10
High ranking - The automation solution is rated as high as possible in a ranking. The criteria or the evaluated features do not (or must not) play a role.
These are just some of the expectations that I have encountered in recent years and which are demanded for the automation solution. But they are also the reasons to invest in an automation solution.
The second part of this blog post will focus on the promises made by automation vendors, as well as case studies from my day-to-day coaching.
More on that in one of the next parts of this series. Be sure to check back.
Until then
Your Dirk