Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 24, 2004

MDX in Analysis Services: Mastering Time: Change across Periods - Page 2

By William Pearson

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:

  • Time dimension(s)
  • Alternate hierarchy capabilities
  • MDX time-series functions
  • Calculated members.

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM