Data warehouse automation for better business decision-making 

Cor Winckler, Principal Consultant at PBT Group 

Automation has become such a loaded term with many organisations thinking this will mean they can now forget about managing certain elements of the data value chain. In truth, there will always be a critical need for human intervention whether this is at a data analyst level or all the way through to leadership needing to make decisions. 

Data analysts have always played an important role in data warehouse projects. In the past, they had to manually sift through things to discover what is going on inside a system. Today, there are tools available that automate this discovery. They might run for 10 minutes or a day depending on the size of the system, but in the end the analyst will have the information necessary to pull insights from. 

The same can be said of developers. In the old days, they had to churn out code based on the specifications of the data warehouse. Now, they can use tools to discover the layout of tables or have those tools provide them with code snippets. Software engineering teams can now conduct pattern-based development. For example, once a pattern for a table is created, it can be replicated to 50 other tables to automate the process. Building from here, this can be done at a high level where developers create templates for code to be put in the correct format to save even more time. 

However, neither of these are sufficient to reach the stage of what managers might think of as having a truly automated warehouse in place. Fortunately, these steps do provide the building blocks with analysts and developers using the tools and techniques to help them automate what was once manually-driven a few years ago. 

Process in making 

Take the architect or solution designer as a case in point. They are not interested in code fragments, but in how everything works together. In the 90s, people scheduled scripts to do this. Today, the favoured term is orchestration. This refers to how the cloud provides the ability to execute things in a logical order. It is also enabling business to approach the era of when data warehouse automation will truly enable the system to function as effective as possible. 

Dependency-driven orchestration (for example, Point B can only happen when Point A achieves something) can be automated in a data warehouse environment. It now becomes the nuts and bolts of what happens at a production level. The modern paradigm and availability of advanced software have made this much more granular. For example, event-based processing can now be automated to deliver the scale needed for execution. 

Thanks to cloud architecture, the processing of these massive event-based inputs can happen in real-time. However, someone must still be involved to create the action needed for the automation to deliver value. 

Cloud enablement 

Certainly, the cloud does provide businesses with the ability to provision infrastructure on the fly. What used to take administrators days or weeks to physically buy hardware can now be done via virtual machines in the cloud within five minutes. The automated deployment of this virtual hardware has already become commonplace. Furthermore, as the need arises, the system can automatically provision additional hardware for the business to consume during peak periods like the end of the month. 

Of course, elements of this have been around for some time. Certain database platforms have been able to automatically tune systems based on the behaviour of users since the 90s. However, people are starting to apply this approach to data analysis given the high-performance computing capabilities available to them from the cloud. 

The cloud can automatically deploy changes that are stable in a testing environment to the production or live systems via DevOps teams. In fact, this level of automation is preferred as the less manual intervention done on this granular level, the less likely human error can impact the successful deployment of a system. 

Decision-making 

Certainly, data warehouse automation can provide businesses with key assistance for better decision-making. The system that is being automated will result in the ability to enhance the decision-making process. But this can only happen if the system is accurate, up to date, and complete from a data quality perspective. 

This means there must be automatic checks and balances in place to ensure the system is able to produce insights on data that is of good quality. Yes, an organisation can pay hundreds of people to do it manually and get a quality answer. This might be fine for a businessperson simply looking at sales figures. But for someone in IT who might not be able to afford 100 technical specialists, automating processes can result in the same quality of service in less time. There is a business case for IT get people to become more productive through automation that delivers better value for money. 

If people are putting automation in place at a higher level, then it can deliver value far more effectively. It is not just about the technology required to do so, but also the willingness of the business to embrace automation. 

Automation must solve the business problems. It is not a case of cost versus returns, but rather the value behind the cost to unlock organisational value.