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.

Saturday, September 5, 2009

New Products Forecasting

Yesterday Nokia officially launched X6 touch phone at Nokia world. This is a nice touch phone with features comparable to or better than iphone 3GS. It took them almost two years to release a phone which is comparable to or better than iphone. This phone is also very competitively priced (Rs 30,000). It's good to see that competition for Apple Iphone has finally arrived. Apple has been ignoring Indian market for long time now. Apple Iphone launch in India was a big failure due to very high pricing. India is suppose to be one of the largest mobile handset markets in the world. One can't afford to ignore them. I hope Apple learns from their past mistakes and launches Iphone 3GS at very competitive pricing in India.

Nokia comes out with a new product or model every month. Lot of time they launch a model which has overlapping features with existing model in market. I always wondered how they forecast inventory for their new phone.Typically, forecasting is done based on past data and events. There is no such data available for new products. Moreover, there are similar products available in market from the same manufacturer. Each of these products eat into each other's revenue. In high tech companies, typically there are 50-60% of mature or stable products, 35% of new products and 5% of "first-of-its-kind" products. How do you ensure that the new product doesn't impact sales of existing product in market?

Each product follows a particular life cycle. A new product launch should be decided in such a way that it doesn't cannablize revenue of other similar products in market. E,g Nokia X3 is a new music phone. Most probably, it will replace Nokia X5300 express music phone. Launch of Nokia X3 is planned when Nokia X5300 is reaching towards end of its life. If two models are going to co-exists than you need to ensure that messaging & target consumer audience is different for both the products. In case of Nokia, both Nokia N97 and N97 mini are going to co-exist. Both the products are meant for different consumer segment.

There are several new product forecasting techniques available. One of the most common one is a Bass diffusion technique. The bass diffusion technique requires 3 parameters
  • Lifetime expected sales - the total amount of units sold in its product lifetime. Also known as market potential
  • P(Mass media) - influenced by the technical aspects of a product that drives a consumer purchase. Also known as coefficient of innovation.
  • Q(Word of Mouth) - Reflects the internal dynamics of the consumer. Also known as coefficient of imitation.
Typically, the following methodology is used for new product forecasting
  • Find a cluster of like (similar) products. This is used to determine the historical data we can use from like products.
  • Perform regression analysis on the cluster
  • Use Bass diffusion model to determine the forecast of the new product
  • Adjust or reforecast after we have some actual data
There are several high tech companies in world who are using sophisticated forecasting techniques to ensure that there is increase in profitability, market share & reduction in excess inventory.

I am ardent fan of Apple iphone. I personally believe that competition is always good for end consumer. With Nokia X6 launch, Apple will not be complacent. It will accelerate innovation at both the places. Finally end consumers like me will get benefited.

You can find more details about Nokia X6@

Nokia Phone Comparision