MDX in Analysis Services: Mastering Time: Change across Periods - Page 2
May 24, 2004
Time and Relative Measurement
As virtually any accountant or finance manager knows, the need to present results in reference to time is important. It is hard to imagine a cube without a time dimension (although I have seen it, it is rare, indeed), because time is a pervasive concept in analysis. Business is measured by activity and activity is based heavily upon the concept of time. Because we measure virtually everything at a point in time (such as an asset, or other balance, within a balance sheet), or over a period of time (such as revenues within an income statement), time becomes a consideration that relates with most other common dimensions we see in cubes.
Time also serves as the foundation that underlies another important concept, growth. Growth of course, can be a positive or negative quantity, the desirability of which is dictated by the dimension and measure intersects that are posed in a given scenario. Revenues, production volumes, returns on equity and other measures are commonly seen to be good candidates for growth, especially when that growth is happening within the scope of our own interests. Likewise, decreases, shrinkage and other antonyms of growth can be equally delightful news, when announced within the context of overhead expenses, operating costs, tax liabilities, customer complaints and many others. To have meaning, growth and its opposites must have referential context, and often the more "comparability" we can obtain, the better. It is thus vital to be able to ascertain the change in measures over time. Second in importance only to the positive / negative nature of those changes are the tandem considerations of "how much" and "how rapidly."
The cyclical nature of business (more in some industries than others, of course) is another compelling reason to be able to provide period-based analysis. As is somewhat obvious, a cyclical business will obtain more useful "period over period" analysis if they are able to compare "apples and apples." An example might be the sales of those frivolous, transitory items that seem to appear in advertising only around the Christmas season; things one would purchase as a gift to in-laws, if at all, such as "hotdog toasters" and the like. Month-to-month comparisons of revenues / units sold information for these products would not be scrutinized as heavily as, say, November and December sales for the most recent year compared to the sales of the same months in the previous year.
MSAS provides excellent tools for presenting data within the relative time concepts so valued by information consumers. The implements it offers us in this arena include:
NOTE: For detailed information on time-series functions, see the following Database Journal articles in my MDX Essentials series:
This article, as well as subsequent articles in this series, will treat the practical presentation of data with time as a consideration. While the specialized time-series functions are called upon, we will look at a combination of MDX elements to bring about the results we are called upon to obtain, explaining each within a multi-step practice exercise.