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 and accept the defaults in the initial screens of the Dimension Wizard. On the Dimension Type page of the Wizard, select Time dimension and choose the dbo_DimTime table as shown in Figure 5-29. Click Next to continue.

    image from book
    Figure 5-29

  2. On the Define Time Periods screen you need to define the columns in the relational table that correspond to the levels within a Time dimension. This page allows you to define the most common time hierarchies used in the business world: fiscal year, calendar year, reporting year, and manufacturing year. Define the properties Year, Half Year, Quarter, Month, and Date as shown in Figure 5-30 and click Next.

    image from book
    Figure 5-30

  3. The Review New Hierarchies screen shows the hierarchies that will be created by the Dimension Wizard based on the time property names you defined in the previous screen. Figure 5-31 shows that one hierarchy will be created. If you had defined other time properties, additional hierarchies might be shown in this screen. You have the option of selecting the hierarchies that you want the wizard to generate. You also have the option of including or excluding the default levels shown by the wizard. Accept the defaults and click Next.

    image from book
    Figure 5-31

  4. Complete the Time Dimension Wizard. The Time dimension with one hierarchy and several attributes will be created as shown in Figure 5-32. Go ahead and deploy and process the project.

    image from book
    Figure 5-32

Figure 5-33 shows the hierarchy that you created. Notice that the order of months within a quarter is not the default calendar order. For example, the order of months of CY Q1 of year 2002 is February, January, and March. To change the order, change the KeyColumns, NameColumn, and SortOrder appropriately and re-deploy the project.

image from book
Figure 5-33

You have now successfully created a Time dimension. If you review the basic properties of each attribute in the Time dimension, you will notice that the property Type has values such as Quarters, HalfYears, Years, Days, and Months. You can define the right property type for the chosen attribute. Defining the right property type is important since a client application could use this property to apply the MDX functions for the Time dimension.

Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176 © 2008-2017.
If you may any questions please contact us: