In order to understand why we need a data warehouse, when and how business or yourself will suggest a data warehouse, what are the advantages of data warehouse etc we will take an example to explain the same.
We will take a company which deals with distribution of goods (Electronic) to various customers as an example.
Company XYZ starts the distribution of electronics items from company ‘ZOOM Computers’ which manufactures the computers and electronic gadgents. The company ‘XYZ Distributors’ started with 10 employees with a turn around of say 500K $. At this stage the management had an idea of where the money spent and how the funds are utilized in the finger tips. At this stage they have only two applications which keeps track of supplier and customer information.
As the time passes say about 5 years, the same XYZ Distributors deals with different electronic items (2000 items) from different suppliers (150). As the business grow, the number of OLTP applications now currently stands at 75 with an employee strength of 3000 and the turn over is over 750 million $. At this stage usually its very difficult to pull the information from various source systems to answer some of the business questions and the turn around time is between a day and a week. IT team needs very specialized skills to make this happen and as the number of ad hoc requirements become more from management side, its very difficult to answer those with in the period they expect. Some times IT team pulls data from oracle, Sybase, SQL Server and push the data in to a common database to integrate the data and run reports. In an average this type of work takes any where between 1 to 7 days some times even more which is based on the complexity of the requirement.
This is the time where we have to think about creating a data repository which consolidates the data from various applications in one place so that management can get 360 degree of the corporate view.
In the above scenario, say we have order management where we deal with orders placed by customers, rejections, canceling orders, we have SMG related activist such as campaign management, HR application to take care of employees, movement of them to different grades, salary information etc.
Management wants to know who are the customers who usually rejects the orders they placed because as a company we are spending our profits as a considerable amount. Want to compare which customer grades palces the cancelled orders.
Management wants to know the percentage of income by products.Management wants to know who is the best sales person in a week, month, quarter and year.
Management wants to know if we outsource this operation what is the impact on the revenue.
Management wants to know the least profit making regions and the amount we send on the same so that they can take a decision whether to continue with the region or how can we improve the business by promoting our products in the region.
To answer most of the management questions, we have to integrate the data across multiple source systems to make some sense of the data.
If management wants see the trend analysis from last three years if OLTP system is storing only 6 months to 1 year then we wont be able to do the analysis
In order to address above mentioned requirements in the industry we create a data warehouse or what we call as DSS (Decision Support System)
Even in OLTP we take decision. Example: You are a customer to a bank, you have 5K as the balance, say you are trying to with draw 10K in this scenario teller takes a decision not to give the money as the balance is less than what you requested for.
So, what’s the different?
When you take a decision in an OLTP application, the impact to the business is almost zero. When you take a decision using Data Warehousing application the impact to the business is fairly large. Example, business identified certain product is not moving fast and more over it generates less than .05% of profit of the total revenue. In this case management can decide to continue with the product or not.
Bottom line is, data warehouse is a place to do analysis for an organization where as OLTP system is mainly to run the business.
With out DW you take decisions which will impact the company big time because of assumptions. With DW every decision which you make is based on the past and with the valid industry standard analysis.
No comments:
Post a Comment