Sunday, September 13, 2009

Application Data Warehouse

The definition of data warehouse is changing in Indian Market. Earlier people use to build data warehouse to cater to MIS reporting need of an organization nowadays data warehouse is built to support various business applications such as
  • Cross Sell/Up Sell
  • Retention
  • Campaign Management
  • Marketing Optimization
  • Market Mix Modeling
  • Basel II compliance
  • Market Risk Analysis
  • Op Risk Analysis
  • Warranty Analysis
  • Supply Chain Optimization etc...
I started my BI implementation career with a data warehouse implementation at one of the media company in India.  The objective of data warehouse project was to replace all excel based MIS reporting with automated reporting system. The sponsor of data warehouse project was IT director.  We designed our data warehouse schema based on reporting requirements of different business functions within the organization. It took 10 months to build a warehouse. We delivered some 25 odd reports using Business Objects. While the data warehouse project was appreciated well within IT department, end users didn't appreciated it. They felt  data warehouse is too rigid. They can't make changes in data warehouse easily. It used to take 4-6 weeks to include any new business requirement change in data warehouse. They also felt that data warehouse was not giving any value add or insight which can help them in their day to day activity. After a year, data warehouse project was scrapped by that organization because the ROI generated from data warehouse was not enough to justify it's investment.


Today scenario has changed. Very recently, we had worked on two enterprise data warehouse RFPs wherein the end goal of implementing data warehouse was to support various business applications. Prospect had clearly stated objective of data warehouse in RFP. They wanted to built a data warehouse to support the following business applications
  • Basel II Compliance
  • Market Risk
  • Credit Scoring
  • Cross Sell/Up Sell
  • Retention
  • Campaign Management 
They wanted to ensure that all variables that are required to do say e.g cross sell/Up sell analysis are included in data warehouse model. There are some 750+ variables to do only cross sell/up sell analysis. Similarly, there are thousands of variables available to support other applications. Lot of time the data warehouse is built keeping in mind only MIS reporting requirements. Hence whenever business users want to do business analysis, they end up creating a seperate mart for data specific to that analysis. This results in data duplication and system overhead. Last week I met up with a senior executive of one of the large bank. Currently they have three data marts. One data mart caters to MIS reporting requirement, second data mart caters to Risk compliance requirements and  third data mart caters to PM requirements. Soon, they are coming up with a RFP to consolidate all three data marts into a single data warehouse.


In today's economic conditions, it is very critical to build "Analytics" friendly data warehouse. Typically, you require historical data to do analytics. Hence you need to capture data related to analytical variables right from day one when data warehouse is implemented. I have seen lot of organization making a mistake of building MIS reporting data warehouse. There are several disadvantages of this approach.

  1. It takes significant amount of time & efforts to build such data warehouse. Your reporting requirements change by the time data warehouse is implemented.
  2. ROI generated from such data warehouse is not significant enough to justify it's investment.
  3. If right variables are not captured in the data model then it takes significant amount of time and efforts to incorporate them in data model at later stage. It involves change in data model, ETL & BI Strategies. Lot of time, it is not possible to incorporate such changes due to complexity of data model and ETL routines, and you end up creating a data mart to cater to Analytical requirements. This results in data de-duplication.
In today's world, it is not just sufficient to know who is buying what & when. You will need to know what they will buy next, & whether they are profitable customer for you or not. This requires analytical capabilities built into your data warehouse. Hence "Application Data warehouse" is way to go.

No comments:

Post a Comment