Data warehouse is a method that provides business performance trend data to the non-technical person in the company. This business user, the customer of the data warehouse:
- loves spreadsheets; I have never met a non-technical person, who needs to manipulate data, who does not like Excel
- wants to monitor business performance by analyzing trends over a period of time.
The RockOil Jeans Co., Sales Trend 2006 ($ millions)
| 2006 Q1 | 2006 Q2 | 2006 Q3 | 2006 Q4 |
| 24 | 28 | 35 | 22 |
Depending on the business cycle, the trend may be reported daily, weekly, monthly, quarterly, or yearly. Looking at this report it is obvious that the company did something right in the third quarter, but then something went terribly wrong in the fourth quarter. Compare this performance report with an operational report:
The RockOil Jeans Co., Sierra Store, Ranch City, 1/9/07 Sales ($ '000s)
| Style | $ '000s |
| Kerosene | 8 |
| JetFuel | 10 |
| RocketFuel | 2 |
| Other | 4 |
| Total | 24 |
This report is for one day (no trend is shown), and for a particular store. The operational reports are generated by the transaction processing systems where the original transactions take place. Going back to the business performance trend report, the marketing manager is now bound to ask for more detail to understand the sales trend. Will looking at the report in terms of the product category throw some light?
The RockOil Jeans Co., Sales Trend 2006 ($ millions)
| Style | 2006 Q1 | 2006 Q2 | 2006 Q3 | 2006 Q4 |
| Kerosene | 8 | 9 | 10 | 11 |
| JetFuel | 10 | 9 | 6 | 6 |
| RocketFuel | 2 | 6 | 15 | 2 |
| Other | 4 | 4 | 4 | 3 |
| Total | 24 | 28 | 35 | 22 |
Aha! The new style RocketFuel introduced in the beginning of 2006 and heavily promoted in the middle of 2006 was a short lived fad. It not only cannibalized JetFuel, but also took it down with it by being too closely associated with it. A stream of questions with now come forth in rapid succession: what was the sales trend by geographical regions, by individual stores, by... This process of diving into more and more detail starting from higher level trend is called "drill down" in the data warehousing parlance. Drill down along with trend reporting constitutes most of what is called "OLAP".
The data warehouse developers get ecstatic imagining implementation of a reporting tool that provides this drill down functionality to the non-technical user. The reporting tool is supposed to empower the user. Terms such as dimensional model, MOLAP, OLAP, ROLAP, star schema (alphabetical order listed here) are thrown around as a means to this end. It is a nightmare for the non-technical user to use the reporting tool. It boils down to familiarity. Every cubicle dweller intimately knows and understands the Excel user interface, whereas, every new tool comes with a new user interface. Excel also allows for easy introduction of valuable fudge factor experienced insight into the stats. Fight your customer's survival instinct with caution.
In addition to sales revenues which are a direct result of sales transactions, the data warehouse also reports balances that in this case happen to be the inventory levels in the stores and in the supply chain. As the the data warehouse matures, some bright person inevitably gets the idea: so we have sales revenue, but what we really need is profitability. Profitability! The holy grail of data warehousing. The cause of frustration for all the salespersons. The cause of heartburn for all the data warehouse developers. The source of gleeful delight for all the accountants.
What is profitability? It is like asking, what is good? Or, what is beauty?
Still, at some point profitability has to be tackled. For retail sales, the profitability will be calculated at the product and/or the organizational unit level. For sales to other large companies, the profitability will be calculated at the customer level as well. Profitability calculation and reporting is not for the faint of the heart, the reason why many data warehouses never go beyond sales revenue reporting. Sales revenue is black and white, profitability is all the shades of gray you can imagine and many that you cannot imagine.
The easiest profitability calculation is the gross margin that accounts for costs of goods sold:
The RockOil Jeans Co., Gross Margin Trend 2006 ($ millions)
| Style | 2006 Q1 | 2006 Q2 | 2006 Q3 | 2006 Q4 |
| Kerosene Revenue | 8.0 | 9.0 | 10.0 | 11.0 |
| Gross Income | 2.0 | 2.50 | 3.0 | 3.0 |
| Gross Margin | 25% | 28% | 30% | 37% |
| RocketFuel Revenue | 2.0 | 6.0 | 15.0 | 2.0 |
| Gross Income | 0.3 | 1.5 | 4.0 | 0.4 |
| Gross Margin | 15% | 25% | 27% | 20% |
The manufacturing costs are a little higher as can be expected for a new product category. The things get really interesting when other expenses such as advertising and promotional costs are brought into the picture:
The RockOil Jeans Co., Net Margin Trend 2006 ($ millions)
| Style | 2006 Q1 | 2006 Q2 | 2006 Q3 | 2006 Q4 |
| Kerosene Revenue | 8.0 | 9.0 | 10.0 | 11.0 |
| Net Income | 0.8 | 1.0 | 1.1 | 1.2 |
| Net Margin | 10% | 11% | 12% | 12% |
| RocketFuel Revenue | 2.0 | 6.0 | 15.0 | 2.0 |
| Net Income | -2.0 | -1.0 | 0.8 | 0.1 |
| Net Margin | -100% | -17% | 5% | 5% |
