Prepare data by using the Time dimension

更新： 2009-04-30

The following procedures explain how to prepare source dates to conform to the Time dimension. Before you begin, you should review and understand the format of the Time dimension and the date formats in your source database. For example, a date of 4/3/06 could represent April 3, 2006 or March 4, 2006, depending on the date format that is used in the locale of the source database.

To conform source data to the Time dimension

1. Examine the format of date/time data in the source database.

2. Extrapolate Absolute Dates by using the AbsoluteYear, AbsoluteMonth, and AbsoluteDay fields to prepare source data.

3. Map the extrapolated AbsoluteYear, AbsoluteMonth, and AbsoluteDay fields from the source database to the AbsoluteYear, AbsoluteMonth, and AbsoluteDay fields in the staging database.

4. Determine the member set or view that is used to create a model.

5. Determine the appropriate Time Dimension ID field that is that you want to use. Base this determination on the member set or view of the model.

6. Populate the required Time Dimension ID into the staging database Measure Group tables.

To map date and time data from the source database to the fields in the Time dimension

1. The date and time data from the source database must now be mapped to the fields in the Time dimension in the staging database. See the sample data at the end of this topic.

2. Extrapolate the absolute dates from the source database by using the AbsoluteYear, AbsoluteMonth, and AbsoluteDay fields to prepare source data.

3. Then, you must understand the data format of the data in the staging database. In the example in this topic, the Month field equals 200601 and the Year field equals 2006, but the MonthLabel equals January2006.

4. Then, map the absolute date fields from the source database to the Absolute Date fields in the staging database.

5. Then, determine the member set or view used in the model creation. In this example, the member set or view of the model is Monthly.

6. Determine the appropriate Time Dimension ID field that you want to use, based on the member set or view of the model. In the example in this topic, the member set or view of the model is the Monthly view. Therefore, you should use the Time_Month field.

7. Using the Time_Month field, populate the staging database Measure Group tables.

   If you are preparing data in a label-based format, you must use the related label. For example, map the label-based data for a month to Time_Month_Label, not Time_Month.

Sample data

• Source Database

  datetime: [1/1/2006 10:17:55 PM]
  

• Staging Database Time Dimension

  MemberID: 20060101
  Date: 01/01/2006
  Month: 200601
  MonthLabel: January 2006
  Year: 2006
  YearLabel: 2006
  ActualDay: 1
  ActualMonth: 1
  ActualYear: 2006
  

• Model Configuration

  Time Dimension Member Set or View: Yearly, Monthly, and Daily

  注意：
  This example assumes that no prefixes were specified during application calendar creation for Year, Month, or Day for the Time dimension in the staging database.

另请参阅

概念

About preparing measure group data for a model