Professional SQL Server™ Analysis Services 2005 with MDX

5.8. Creating a Time Dimension

Almost every data warehouse designed will have a Time dimension. The Time dimension can be comprised of the levels Year, Semester, Quarter, Month, Week, Date, Hour, Minute, and Seconds. Most data warehouses contain the levels Year, Quarter, Month, and Date. The Time dimension helps in analyzing business data across similar time periods; for example, determining how the current revenues or profit of a company compare to those of the previous year or previous quarter.

Even though it appears that the Time dimension has regular time periods, irregularities often exist. The number of days in a month varies across months, and the number of days in a year changes each leap year. In addition to that, a company can have its own fiscal year, which might not be identical to the calendar year. Even though there are minor differences in the levels, the Time dimension is often viewed as having regular time intervals. Several MDX functions help in solving typical business questions related to analyzing data across time periods. ParallelPeriod is one such function, which you learned about in Chapter 3. Time dimensions are treated specially by Analysis Services and certain measures are aggregated across the Time dimension uniquely and are called semi-additive measures. You learn more about semi-additive measures in Chapter 9.

The following steps show you how to create a Time dimension on the Dim Time table of the AdventureWorksDW database:

1. Launch the Dimension Wizard ...