Defining a Server Time Dimension
A time dimension in Microsoft SQL Server 2005 Analysis Services (SSAS) can be based either on a table in a data source view or on a date range. A time dimension that is based on a table is really no different from any other standard dimension. Its attributes are bound to columns of a dimension table just like any other standard dimension.
In contrast, a range-based time dimension is typically used when there is no separate time table to define time periods. Attributes of a range-based time dimension have time-attribute bindings, which define the attributes according to specified time periods such as Years, Months, Weeks, or Days. Because the data for a range-based time dimension is created and stored on the server instead of coming from any table in the data source, a range-based time dimension is called a server time dimension.
In either a table-based or a server time dimension, the Type property setting of a time attribute specifies the corresponding time period recognized by Analysis Services, such as Years for an attribute named Calendar Year. For an overview of time dimensions, see Time (SSAS).
When you create a server time dimension, you specify the time periods and also the start and end dates for the dimension. The wizard uses the specified time periods to create the time attributes. When you process the dimension, Analysis Services generates and stores the data on the server that is required to support the specified dates and periods. The wizard uses the attributes created for a server time dimension to recommend hierarchies for the dimension. The hierarchies reflect the relationships between different time periods and take account of different calendars. For example, in a standard calendar hierarchy, a Weeks level appears under a Years level but not under a Months level, because weeks divide evenly into years but not into months. In contrast, in a manufacturing or reporting calendar hierarchy, weeks divide evenly months, so a Weeks level appears under a Months level.
Define Time Periods
If you choose to create a server time dimension, which is based on a date range, use the Define Time Periods page of the wizard to specify the range of dates that you want to include in the dimension. For example, you might select a range that starts on January 1 of the earliest year in your data and that ends one or two years beyond the current year (to allow for future transactions). Transactions that are outside the range either do not appear or appear as unknown members in the dimension, depending on the UnknownMemberVisible property setting for the dimension. You can also change the first day of the week used by your data (the default is Sunday).
Select any time periods that apply to your data, such as Years, Half Years, Quarters, Trimesters, Months, Ten Days, Weeks, or Date. You must always select at least the Date time period. The Date attribute is the key attribute for the dimension, so the dimension cannot function without it.
Next to Language for time member names, select the language to be used to label the members of the dimension.
After you create a time dimension that is based on a range of dates, you can use Dimension Designer to add or remove time attributes. Because the Date attribute is the key attribute for the dimension, you cannot remove it from the dimension. To hide the Date attribute from users, you can change the AttributeHierarchyVisible property on the attribute to False.
Select Calendars
The standard (Gregorian) 12-month calendar, starting on January 1 and ending on December 31, is always included when you create a time dimension. On the Select Calendars page of the wizard, you can specify additional calendars on which to base hierarchies in the dimension. For descriptions of the calendar types, see Time (SSAS).
Depending on which time periods you select on the Define Time Periods page of the wizard, the calendar selections determine attributes that are created in the dimension. For example, if you select the Year and Quarter time periods on the Define Time Periods page of the wizard and select Fiscalcalendar on the Select Calendars page, the FiscalYear, FiscalQuarter, and FiscalQuarterOfYear attributes are created for the fiscal calendar.
The wizard also creates calendar-specific hierarchies that are composed of the attributes that are created for the calendar. For every calendar, each level in every hierarchy rolls up into the level above it. For example, in the standard 12-month calendar, the wizard creates a hierarchy of Years and Weeks or Years and Months. However, weeks are not contained evenly within months in a standard calendar, so there is no hierarchy of Years, Months, and Weeks. In contrast, weeks in a reporting or manufacturing calendar are evenly divided into months, so in these calendars weeks roll up into months.
Reviewing New Hierarchies
On the Reviewing New Hierarchies page of the wizard, review the hierarchies created by the wizard. You can expand or collapse each hierarchy to view its levels. Clear the check box next to any hierarchy to remove that hierarchy from the dimension. Clear the check box next to any level in a hierarchy to remove that level from the hierarchy. After you finish creating the dimension, use Dimension Designer to make additional changes to the dimension.
Completing the Dimension Wizard
On the Completing the Wizard page, review the attributes and hierarchies created by the wizard, and then name the time dimension. Click Finish to complete the wizard and create the dimension. After you complete the dimension, you can change it by using Dimension Designer.
See Also
Concepts
Data Source Views (Analysis Services)
Time (SSAS)
Configuring Database Dimension Properties
Dimension Relationships