The traditional use of a data warehouse has been to store historical data. Stored at both the detailed level and the summary level, data warehouses provide a description of what has happened in the past. Historical data may be used to look at the forest and the trees, look at customer trends and provide a basis for asking "what if" questions, among other things.

Data warehouses are unique in that they recognize that data is captured and stored over a panorama of time. Contrast a data warehouse's recognition of the element of time with that of OLTP (transaction-based) systems. In OLTP transaction-based systems, the only time that matters is right now. In transaction-based systems you ask such questions as:

  • How much money do I have in my account right now?
  • What insurance coverage do I have right now?
  • How many seats are available on an airplane flight right now?
  • What is the status of the order right now?

All questions are phrased in terms of the value of information right now. Transaction-based systems focus almost exclusively on the most current up-to-the-second status of data. They spend a lot of energy on guaranteeing the accuracy of information that is current.

However, what about other forms of time? Can a data warehouse be used to hold other forms of time variance? The answer is yes. A data warehouse can be used to hold future information, such as forecast information, as well as past information.

Like other information in a data warehouse, the forecast can be made at different levels of granularity (i.e., the forecast can be made in terms of days, weeks, months or years). The specifics of the frequency of the forecast depend entirely on the nature of the business of the entity that is being forecast. Making an annual forecast for the amount of bubblegum that will be manufactured hourly doesn't make much sense. Nor does an hourly forecast of annual revenue make sense.

One of the most innovative uses of forecasts is that of periodically comparing forecast data to actual usage data. For example, suppose an energy company makes a monthly forecast for the usage of natural gas. Then, as each month passes, the company tracks the difference between what has been forecast versus what has been actually used. In so doing, the company can start to get a handle on how reliable their forecasts are.

By making a monthly comparison between actual and forecast, an organization can start to understand what variables they are subject to. Consider an energy company, for example. The energy company records variables are used to shape their forecast including:

  • the El Nino weather pattern,
  • the number of new residents in a state,
  • the number of new businesses in the state,
  • the number of high-consumption businesses in the state,
  • the number of new students in the state, and
  • the number of total students in the state.

Once the correlation between the forecast and the actual is calculated, the organization can start to understand what variables the organization is sensitive to and what variables the organization is not sensitive to. In doing so, the organization can start to shape the forecasts ever more accurately.
Multiple forecasts are also useful. The multiple forecasts can be done by different individuals or by sensitivity to different variables. On a monthly basis, the variables can be compared to the actuals to create a confidence index for each forecast. For a forecast that is consistently off, the confidence index will be low. For a forecast index that is consistently close, the confidence index will be high. Forecasts may be both short term and long term.

Once the forecasts are made, as time passes, they become history and are integrated into the historical part of the data warehouse.

The data warehouse designer does not have to be limited to forecasts. Budgets can be treated the same way.

The addition of forecast and budget data to the data warehouse environment can add a dimension of information that rounds out the historical and current information that is found there. Interestingly, tracking the difference between actual and forecast can provide major insight into how to make better forecasts in the future.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access