Supposedly “timing is everything”, yet analysis across time is a surprisingly neglected topic in many data warehouse implementations. If you are a marketer, it is clear that time is a critical issue: you want to be able to compare seasonal sales patterns, for example. A retailer may even be interested in the pattern of buying at different times of the day, and change stock layout in response to this. Yet in many data warehouse designs, time is an afterthought. For example in SAP BW you can only analyze a field for date/time reporting if you specify this up-front at the time of implementation, and this carries a performance penalty. Even this is an improvement on many custom-build warehouses, where data is not routinely date-stamped and so even basic reporting using time is impractical.
Advanced data warehouse technology should enable you to not only do simple time-based analysis like “last summer’s sales v this summer’s sales” but also be able to keep track of past business hierarchies. For example you may want to see the sales profitability before and after a reorganization, and so want to look at a whole year’s data as if the reorg never happened, or as if it had always happened. One major UK retailer has a whole team of staff who take historic data and manually edit a copy of that data in order to be able to make such like-for-like comparisons, and yet this type of analysis should be something that their data warehouse can automatically provide. An example of doing it right is Labatt where the marketing team now had access to a full-range of time-based analysis, enabling to take more data-driven decisions.
Another sophisticated user of time-based analysis is Intelsat, who used sophisticated time-based analysis to improve their understanding of future satellite capacity. Satellite time is sold in blocks, usually in recurring contracts to news agencies such as CNN or the BBC e.g. “two hours every Friday at 16:00 GMT”. Each of these contracts has a probability of being renewed, and of course there are also prospective contracts that salesmen are trying to land but may or may not be inked. Hence working out the amount of satellite inventory actually available next Tuesday is a non-trivial task, involving analysis that was previously so awkward that it was only done on occasion. After implemented a data warehouse that inherently understands time-variance, Intelsat were able to identify no less than USD 150 million of additional capacity, and immediately sell USD 3 million of this, a handsome return on investment on a project that was live in just three months and cost less in total than even the immediate savings.
If your data warehouse can’t automatically give you sophisticated time-based analysis then you should look at best-practice cases like this. Make time to do it.