Create a Date type Dimension
In Microsoft SQL Server Analysis Services, a time dimension is a dimension type whose attributes represent time periods, such as years, semesters, quarters, months, and days. The periods in a time dimension provide time-based levels of granularity for analysis and reporting. The attributes are organized in hierarchies, and the granularity of the time dimension is determined largely by the business and reporting requirements for historical data. For example, most financial and sales data in business intelligence applications use a monthly or quarterly granularity.
Typically, cubes in Analysis Services incorporate a time dimension in one form or another. A cube may include more than one time dimension, or several hierarchies from the same time dimension, depending on the granularity of the data and the reporting requirements. However, not all cubes require a time dimension. Some OLAP applications, such as activity-based costing, do not require a time dimension, because .costing in an activity-based dimension is based on activity instead of time.
Dimension Structure
The dimension structure for a time dimension depends on how the underlying data source stores the time period information. This difference in storage produces two basic types of time dimensions:
Time dimension
Time dimensions are similar to other dimensions in that a dimension table supplies the attributes for the dimension. Each column in the dimension main table defines an attribute for a specific time period.Like other dimensions, the fact table has a foreign key relationship to the dimension table for the time dimension. The key attribute for a time dimension is based either on an integer key or on the lowest level of detail, such as the date, that appears in the dimension main table.
Server time dimension
If you do not have a dimension table to which to bind time-related attributes, you can have Analysis Services define a server time dimension based on time periods. To define the hierarchies, levels, and members represented by the server time dimension, you select standard time periods when you create the dimension.Attributes in a server time dimension have a special time-attribute binding. Analysis Services uses the attribute types that are related to dates, such as Year, Month, or Day, to define members of attributes in a time dimension.
After you include the server time dimension in a cube, you set the relationship between the measure group and the server time dimension by specifying a relationship on the Define Dimension Usage page of the Cube Wizard.
Calendars
In a time dimension or server time dimension time period attributes are grouped together in hierarchies. Such hierarchies are generally referred to as calendars.
Business intelligence applications frequently require multiple calendar definitions. For example, a human resource department may track employees by using a standard calendar—a twelve-month Gregorian calendar starting on January 1 and ending on December 31st. However, that same human resource department may track expenditures by using a fiscal calendar—a twelve-month calendar that defines the fiscal year used by the organization.
You can construct these different calendars manually in Dimension Designer. However, the Dimension Wizard provides several hierarchy templates that can be used to automatically generate several types of calendars when you create a time dimension or server time dimension. The following table describes the various calendars that can be generated by the Dimension Wizard.
Calendar |
Description |
|---|---|
Standard calendar |
A twelve-month Gregorian calendar starting on January 1 and ending on December 31st. Regardless of whether you use the Dimension Wizard to create a time dimension or a server time dimension, the wizard generates a hierarchy for a standard calendar after you define the attributes that represent the time periods for the dimension. If you use the Dimension Wizard to create a server time dimension, you can adjust the starting date of the standard calendar to start on a day other than January 1. |
Fiscal calendar |
A twelve-month fiscal calendar. When you select this calendar, specify the starting day and month for the fiscal year used by your organization. Note This calendar is only available if you use the Dimension Wizard to create a server time dimension. |
Reporting calendar (or marketing calendar) |
A twelve-month reporting calendar that includes two months of four weeks and one month of five weeks in a repeated three-month (quarterly) pattern. When you select this calendar, specify the starting day and month and the three-month pattern of 4–4–5, 4–5–4, or 5–4–4 weeks, where each digit represents the number of weeks in a month. Note This calendar is only available if you use the Dimension Wizard to create a server time dimension. |
Manufacturing calendar |
A calendar that uses 13 periods of four weeks, divided into three quarters of three periods and one quarter of four periods. When you select this calendar, you specify the starting week (between 1 and 4) and month for the manufacturing year used by your organization, and also identify which quarter contains four periods. Note This calendar is only available if you use the Dimension Wizard to create a server time dimension. |
ISO 8601 Calendar |
The International Organization for Standardization (ISO) Representation of Dates and Time standard calendar (8601). This calendar has an integral number of 7-day weeks. The new year may start several days before or after the start of the new year, based on the Gregorian calendar. The first week of this calendar is determined by the first week of the Gregorian calendar which contains a Thursday. Therefore, the first day of this week, the Sunday, may actually fall within the previous year. Note This calendar is only available if you use the Dimension Wizard to create a server time dimension. |
When you create a server time dimension, and specify the time periods and the calendars to use in that dimension, the Dimension Wizard adds the attributes for the time periods that are appropriate for each specified calendar. For example, if you create a server time dimension that uses years as the time period and includes both fiscal and reporting calendars, the wizard will then add FiscalYear and ReportingYears attributes, together with the standard Years attribute, to the dimension. A server time dimension will also have attributes for combinations of selected time periods, such as a DayOfWeek attribute for a dimension that contains Days and Weeks. The Dimension Wizard creates a calendar hierarchy by combining attributes that belong to a single calendar type. For example, a fiscal calendar hierarchy may contain the following levels: Fiscal Year, Fiscal Half Year, Fiscal Quarter, Fiscal Month, and Fiscal Day.
Adding Time Intelligence with the Business Intelligence Wizard
After you have defined a time dimension and added that dimension to a cube, you can use the Business Intelligence Wizard to add time intelligence functionality, such as period-to-date, period-to-period, and rolling average measures. For more information, see Define Time Intelligence Calculations using the Business Intelligence Wizard.
Note
You cannot use the Business Intelligence Wizard to add time intelligence to server time dimensions. The Business Intelligence Wizard adds a hierarchy to support time intelligence, and this hierarchy must be bound to a column of the time dimension table. Server time dimensions do not have a corresponding time dimension table and therefore cannot support this additional hierarchy.
See Also
Reference
Business Intelligence Wizard F1 Help