Optimized Data Warehouse / BI Environment

A retail services customer operated a sophisticated DW/BI environment. The DW/BI environment was built around information such as store sales data and customer purchase pattern data. The process to create the desired BI environment involved numerous steps, beginning with moving data from several sources into a data staging area and performing Quality Assurance (QA) to make sure the data looked complete. After that, employees initiated ETL processing to transform and normalize the data again, and then performed QA to confirm that the results of the ETL processing made sense. At that point, the transformed data was moved into a single data warehouse, from which additional ETL processing was done to move subsets of the normalized data into purpose-built data marts on which the business units could perform their analysis. The data marts included one for determining target store inventories, one to drive personnel recruitment strategies, and one to develop marketing campaigns.

This end-to-end cycle involved hundreds of steps across several databases and about 20 servers. While the ETL and BI steps were automated, much of the rest of the overall cycle was performed manually. The problem was that this process often failed, causing the data in the data marts to be either obsolete or just plain wrong. There were many causes of these failures: missing execute permissions in a script, expired passwords, an extract process fired-off against an unavailable data source, insufficient processing capacity to perform the ETL crunching – the list goes on.

On any given day, a number of the 20-person support staff devoted an entire day to orchestrating the overall process, trying to assess where the most recent breakdown was and recovering from it in time to ensure that the data didn’t become stale.

This particular customer didn’t just want to automate this end-to-end process, but instead, wanted to harden the process in order to achieve the higher service levels the business unit clients were demanding. 

The customer knew from experience that the primary causes of failure were related to unavailable data prior to firing off the ETL process, as well as insufficient computing capacity to allow the ETL process to complete in the time allowed. Accordingly, the company utilized Optinuity’s technology to implement an Autonomic Policy that not only orchestrated the overall end-to-end process, but also included autonomic routines to ensure the presence of correct data before firing the ETL, assess available computing capacity, and provision additional capacity as necessary. Thus, the company could make sure the ETL process would complete within the allotted time. 

Results:

• Ensured that the data in the data marts was timely and accurate
• Reduced the support staff around this DW/BI environment from 20 to three people