Measure for Measure - Page 2

Do OLAP tools shortchange users with time-varying data?

Determining the Method

And a last point on this subject: What if your company reports some data monthly, but your analysis must be done with a daily output frequency? Is dividing a monthly figure by the number of days in the month enough, or must you account for weekends and holidays? Or is any kind of division simply wrong, because data along the particular dimension is not additive? For example, having 300 employees at the end of April doesn't mean you only had 10 employees on a given day in that month.

A more realistic method is an average value for the month, although a straight-line interpolation between the current and previous-period values or a moving average (an average of the values immediately before and after the time point in question) could be more meaningful. Regardless, the key is having the right capabilities in your software tools and the flexibility to use the approach appropriate to each dimension in your cube.

Analyzing time-varying data can be an even bigger challenge than simply storing it and transforming frequencies. I'll look at two examples: seasonal adjustment and forecasting.

Seasonal Adjustment and Forecasting

The concept of seasonality is familiar to all of us: the changes in farming and construction employment due to winter weather and the increase in retail sales and retail-sales employment in the months before the end-of-year holidays. Simply measuring employment and sales levels doesn't help you understand the underlying conditions that are independent of seasonal factors. Decomposing demand for labor and retail sales into seasonal and nonseasonal terms can help provide that understanding. Oracle's Express Server and Lucent Technologies Inc.'s Strategist, however, are the only OLAP tools I know of with built-in capabilities to help analysts meet this widespread need.

The goal of forecasting is to discover trends that you can use to predict the future values of variables. You could forecast measure values directly, but this method is less safe than first estimating the future values of the underlying variables from which you can then derive the measure values.

The most basic forecasting technique is regression and extrapolation: fitting a straight-line or more complex curve to a set of data points and projecting to future times. Although Microsoft's MDX includes linear regression, some mass-market OLAP tools don't even have this ability.

But even linear regression, which may not reveal the dependencies across time that are inherent in most business data, can be insufficient for your needs. Think what would happen if you fit a straight line to the outside temperature. If you were working with many years of data, you might be able to detect warming or cooling trends. But if you live in the northeast United States. as I do and you only had data from January to June of one year, you might conclude that you need to put heavy-duty air conditioners on your holiday wish list.

The Right Tool

Seasonal analysis and forecasting techniques will be interesting topics for a later column, as will specialized software and techniques for managing time-varying data. In many fields, understanding and respecting the time basis of the data is the key to meaningful analysis. We have much to gain by examining these cases - and looking at techniques for management and analysis of time series data - in more depth.

For now, take a good look at your analysis tools and decide if they're handling your time-varying data adequately. If they aren't, you have several alternatives worth exploring in both OLAP and more specialized tools. In the OLAP realm, Accrue Software Inc.'s Pilot Decision Support Suite comes to mind. And relational-database object extensions for time series management and other complex-typed data are gaining wider acceptance.

Rate This Article Rating: 1 (best) 2 3 4 5 (worst) Comments: Optional e-mail address:

Being able to do advanced analyses like forecasting requires understanding the advantages of software that truly understands time - and the limitations of mass-market OLAP tools that don't.

RESOURCES