Automate the Boring But Essential Parts of Your Data Warehouse

August 13, 2015
155 Views

If you are in IT and responsible for your company’s data warehouse and reporting capabilities, chances are you will identify with at least one of these statements:

If you are in IT and responsible for your company’s data warehouse and reporting capabilities, chances are you will identify with at least one of these statements:

  • “We are too buried with maintaining our existing database to take on new initiatives”
  • “We need to get out of the report writing business”
  • “We cannot give you that information from the data warehouse without a lot of development”
  • “It’s too hard to make changes to our existing process because it breaks too easily”
  • “That new data source doesn’t fit in our existing data model and we can’t extend it” 

If so, then you may be an ideal candidate for Data Warehouse Automation (DWA). Companies realize that data warehouses are not going to be replaced by Hadoop or in-memory solutions.  A 2014 Gartner survey found that only 3% of IT leaders believe that big data or in-memory systems can replace their existing data warehouse infrastructure. That’s a dramatic drop over recent years.  As such, DWA is getting a fresh look as a better way to build and manage a data warehouse.

Until recently, DWA was associated mostly with automating ETL development – such as generating SSIS packages in the Microsoft environment.  Today, however, it covers all the major components of data warehousing from design, development and testing to deployment, operations and change management.  It also covers advanced functionality like support for slowly changing dimensions and change data capture.

In our experience, DWA delivers up to 80% improvements in the cost-effectiveness of building and running a data warehouse.  And, just as important, DWA is far better aligned with modern agile development practices because it encourages a rapid, iterative approach to design.

The benefits of automation are profound for IT organizations.   Developers and DevOps teams see greater efficiency in the development and maintenance phases of the data warehouse lifecycle through a number of advantages: 

Data Warehouse Development

Data Warehouse Operations

 

  • Greater productivity for developers means more delivered in less time
  • Consistency of practices and standards leads to the development of more maintainable systems
  • Automation means increased support for agile development practices
  • Standardized testing processes supports QA cycles
  • Simpler development and prototyping makes it easier to respond to changing requirements
 

  • Maintenance processes are simplified and packaged to reduce manual effort
  • Documentation is generated automatically and kept current with releases
  • Assess impact of proposed DW changes using enhanced metadata capabilities
  • Add enterprise features to basic data warehouses to increase DW lifespan
  • More robust, standard processes leads to a more stable environment

DWA is commonly criticized by vendor competitors as being either 1) “the wrong approach” compared to in-memory; or 2) not as effective as hand-crafted, custom database development approaches.  Both arguments are purist and too simplistic. In-memory has tremendous analytical benefits but is lacking in support for data governance, single source of truth, and performance.  Customization is almost always needed for business critical tasks – but not for the more common routine and repetitive uses.  Custom development is more error prone than DWA, and much harder to maintain over the long run. 

Automation does not mean excluding in-memory analytical tools or precluding customization.  It means blending the best of the best to create a scalable, high performing solution at lower time and development cost.

Next Generation Data Warehouse

To deliver on your company’s future demands for data and insights, you will need to maintain your existing data warehouse – and add the great new capabilities available with big data management and in-memory analytics. The real opportunity is in making those technologies work together smoothly with minimum effort and risk.  That means continuing to automate the rote and routine parts of your developer’s daily task so they can focus on moving your organization to a higher level of development and value, and ultimately provide better access to the data that is valuable to the company.