Product Innovation

[Anand_Rao]

Overview

Data Warehouse Automation (DWA) lowers maintenance costs by enabling DevOps, DataOps, and other forms of agile development by dramatically reducing the work required to build, operate, and maintain the data warehouse.

Developers gain agility and productivity by generating and regenerating code to accommodate source schema changes or a new target platform. With this automation, dependence on external staff, such as contractors, is also reduced.

Finally, while DWA spans many life cycle stages, such as data warehouse design, prototyping, testing, monitoring, maintenance, and production updates, it also extends to building data pipelines to ingest the data.

[Anand_Rao]

Data Warehouse Challenges

When an organization needs real-time data to feed the warehouse, they typically hire a third-party consultancy to create a manual change data capture (CDC) process and a data vault. However, after initial implementation, errors in this manual CDC process take many months to uncover and rectify. Adding new tables to the data warehouse will take many days since administrators make updates in a limited time window off business hours.

Hence an organization should develop and configure its CDC and DWA processes without depending on third-party integrators or consultants. This way, they can increase the scalability to accommodate new data sources with minimal downtime and not be forced to choose between database availability and development effort.

An Ideal Data Warehouse Automation (DWA) Solution

The principal component of a DWA solution is the metadata repository. The development team uses metadata to describe and generate all data structures, data flows, and presentation data objects. They can use a client tool or a web portal to edit the metadata, thus saving a lot of manual coding effort.

The DWA solution will read a source database's data definition language (DDL) and import it into its repository. The DWA solution can also generate unique code to transition from one state to another, i.e., to unload the data mart, reshape it and then merge and reload the data in its new form. The same applies to generating test code harnesses.

Most DWA tools assist with data vault data modeling, which needs a well-disciplined approach as it uses many physical tables. A data vault sits on top of a data warehouse storing all changes made to the data warehouse and outlining the facts and dimensions of the data model.

Qlik's DWA Solution

Qlik's DWA automatically creates data vault-like structures with a data history, addressing one of the primary goals of a modernization project. Another critical benefit is eliminating reporting issues caused by missing rows in tables with compensating records for late-arriving dimensions.

Qlik's data replication automatically copies data table structures and data description language (DDL) to build the data pipelines and automatically update to changes. The Qlik solution is straightforward to use compared to legacy systems, helping to deliver the time savings described above.

Qlik's DWA Benefits

Qlik's DWA solution reduces development costs for data warehouse and data vault construction, resulting in cost savings from retired legacy systems and support. It increases business agility and future proofs with modernized data architecture. It also serves as a valuable tool for risk reduction, governance, and adoption of best practices.

Customers, on average, achieve 75 percent cost reduction in creating CDC processes and data vaults and 75 percent on data warehouse development. The time required to add new source systems to the data warehouse also shrinks by 4x - 20 days to 5 days. Additionally, the time to create new data mart facts and dimensions reduces, resulting in delivering a new data mart from scratch within a couple of weeks, an increase in speed of 400 percent on average.

Data and analytics teams can handle incoming new and ad-hoc BI requests for reports as they pop up and incorporate new data sources as needed. Also, with the new, flexible architecture, they are not dependent on outside database administrators or integrators, and organizations typically recoup deployment costs midway through a license subscription period.

Self-Service Click-Through Experience

Get a hands-on experience on Qlik's DWA solution here. Also, read here about a Qlik customer who experienced the results.