Why Data Warehousing?

Data warehousing is not just another means of reporting off company data. If that were all that there was to it, then spending hundreds of thousands, or millions, of dollars on a data warehouse would be nothing more than an expensive exercise in data replication with little intrinsic benefit. The question to ask is: "how can we use information to significantly improve the way we do business?" Key indicators of data warehouse readiness is understanding how information can be used to improve profits, lower long-term expenses or make your organization more competitive by more effectively understanding your customers and/or expanding into new markets. Bottom line is if your company is expected to grow, then you will likely need to explore new and better ways to evaluate your organization and your customers. Information is key to your success. This essay will discuss the most effective subjects for a data warehouse. Within each subject we will discuss the relevant corporate benefits as they apply to profits, expenses or competitive advantages.

 

The Sales and Marketing Subject Areas

The sales data warehouse typically measures the effects of sales revenue against customers, sales channels and products over time. For example: During the past quarter, show me the top 10 sales reps in the Northeast, what products are they selling, who are their customers, and how does it compare to same period last year? Show me products that are they not selling. How much money are the top 10% of the sales reps are making versus the bottom 10%? Rank customers by profit margin. Rank sales reps by profit margin. Alert me when sales for X product fall below 10% of overall product sales. Alert me if by December 1 sales revenues fall below X dollars.

Sales management uses this information to determine areas of growth potential, areas of potential problems, sales compensation programs and understanding top performers/poor performers, who are our most important customers, what are our profit margins, etc.

For the sales subject area, defining the method in which you sell your products is extremely important. Some companies sell mainly by direct sales channels and others via indirect sales channels, while some organizations employ a combination of both direct and indirect sales channels. In any case, it is very important to clearly define your channels of distribution because it will allow you to measure sales based upon criteria for the method in which your company sells its products. For example, grocery store chains have a much different method of rewarding success (additional self space or self space in higher traffic areas of the store) than a software company that sells product through a direct sales channel. The hierarchy for product sales should be depicted as standard throughout the company. It is common for large organizations to have more than one sales hierarchy for different divisions (e.g. direct sales channel for one division, indirect sales channel for the another division).

Where the sales organization measures performance, the marketing department’s job is to drives sales. The marketing subject area of the data warehouse focuses on the way in which a company sells product. They work to find potential customers through marketing campaigns and advertising. They look for patterns and trends.

For the marketing subject area, defining customer demographics is vital to successful target marketing. Customer demographics are required for marketing to effectively target ad campaigns or product promotions to specific customer profiles. As a marketing manager of a company selling weather stripping, it would be foolish to send direct mailings to people living in warm climate areas. Likewise, BMW would not necessarily target the same type customer as Chrysler’s minivan division. Demographic information, such as age, gender, income, location, and etc. should be identified at the outset building the marketing subject area.

For both the sales and marketing subject areas, a common entity is attributes that differentiate one product from another. These are descriptors such as product size, color and style. In a service industry such as insurance industry, it could be items such as the type of coverage (liability, collision, comprehensive, etc.) for a specific line of business such as auto or workers compensation. There are obvious benefits to differentiating product groups based unique attributes such as determining which product groups sell best during specific time frames or within certain regions of the country.

 

The Fulfillment Subject Area

While the sales and marketing subject areas of the data warehouse depicts commerce between customer and vendor and creates demand in the customer’s mind to purchase product, the fulfillment subject area is critical to long-term business success. Fulfillment results in the shipment of goods to the customer. Typically, the customer’s short-term experience with the product is also considered part of the fulfillment commitment. Thus, long-term success is dependent upon short-term customer satisfaction. Understanding why a customer returns product is part of the fulfillment process for evaluating your commitment to the customer.

Understanding your value proposition to the customer is important if you are to measure your fulfillment success and the cost of a dissatisfied customer. Typical value propositions include product quality and selection, on-time delivery and competitive prices. In the case of a grocery store providing fresh produce, the value proposition includes product freshness, product selection and competitive prices. All of these must be met if the customer is to purchase your product. In addition, a customer could make an initial purchase but buy elsewhere for his/her next purchase if the criterion for satisfaction is not met.

For companies who manufacture and sell products to customers such as Home Depot, Lowes, Kmart, OfficeMax, Staples, etc., on-time delivery and replenishment are equally important to stated product quality and highly competitive pricing. Measuring the satisfaction of these customers can be the difference of realizing incredible profits or filing for Chapter 11. Therefore understanding and measuring the cost of dissatisfied customers should be a requirement for the long-term success of any organization.

The fulfillment data warehouse is typically measured along on-time delivery (usually actual delivery time versus expected delivery time), cost (total product price, margin, warrantee cost, etc.), product, customer and customer disposition (reason for dissatisfaction). For companies selling through multiple channels of distribution, the various channels should also be measured.

 

Product Inventory Management

Ineffective inventory management can be costly to your organization in terms of lost revenue due to dissatisfied customers and cost overruns from excessive and obsolete product inventory. The inventory management subject area of the data warehouse is one of the most effective ways for corporations to maximize profits. Questions such as "how much of product X do we have on hand", "how much of product X should we have", "how much should it cost" and "how much did we ship" are common questions that a good inventory management subject area should answer.

Inventory is the quantity of goods or materials on hand or available for use. Product can be either acquired or produced. Thus, some companies manufacture its products while others distribute product acquired from its suppliers. It is not uncommon for some organizations to do both. Acquired product is not to be confused acquired materials. The distinction is that acquired product is available for sale to customers, while acquired materials are used during the manufacturing process. Acquired product may be repackaged or included within a product bundle. Regardless, it is a finished product for sale to the customer.

The status of inventoried product is important to the corporation. Some product may be damaged in shipment or need further quality assurance tests. Thus, understanding the changing levels of damaged product or lack of quality assurance could set a trend that results in an unexpected earnings shortfall. This needs to be accounted for and available in the inventory management subject area of the data warehouse.

Capacity inventory is typically found in service industries such as consulting, the legal profession and the medical profession where there is no tangible product. Therefore, capacity inventory management is the maximum amount of capacity that can be serviced at any given point in time. There is a maximum limit. In addition, expansion is limited to the capital investment required, such as hiring additional lawyers.

Capacity inventory can be found in other industries such as telecommunications, airlines and hotels. In telecommunications there is a maximum bandwidth available and airlines have a maximum capacity per airplane. Likewise, hotels have a maximum capacity for occupancy. The capital cost of expansion in these industries is extremely high. It is therefore very important to fully understand impact of expansion and when and where to expend capital.

Regardless of the industry, inventory management through data warehousing should be a key focus of any organization.

 

The Financial Subject Area

The financial subject area of the data warehouse is designed to supplement, not replace, financial reporting found in the existing financial accounting system. It is used for quick and simplified access to the detailed data behind past results. Financial source data for the data warehouse must be found in the approved, audited accounting system. All financial reports from the data warehouse must reflect any changes in the financial system. These reports must be as reliable as reports from the existing financial reporting system.

Financial reporting from the data warehouse includes all reporting required for external sources such as shareholders, creditors and government agencies. This includes assets, liabilities, and equity measurements in addition to revenue and expense measurements. For example, balance sheet and income statement reports will be available with drill down capabilities into each category.