Server Time Dimension

In certain data warehouses you might not have a special table for Time. However the fact table might contain date as a column. Analysis Services 2005 provides you the functionality of creating a Time dimension based on time range with appropriate hierarchies. You can configure the range based on the beginning and end dates found in your fact tables. This range-based Time dimension is created on Analysis Services and is called Server Time dimension. Once a Server Time dimension is created you can add this to the cube and specify appropriate granularity. Follow the steps below to create a Server Time dimension.

1. Right click on the Dimensions folder in Solution Explorer and select New Dimension to launch the Dimension Wizard.

2. Select the default value in the Select Build Method as shown in Figure 8-40 and click Next.

   
   Figure 8-40

3. In the Select Data Source View page select the default as shown in Figure 8-41 and press Next.

   
   Figure 8-41

4. In the Select the Dimension Type page select the option Server time dimension as shown in Figure 8-42 and click Next.

   
   Figure 8-42

5. In the Define Time Periods page select the date ranges as shown in Figure 8-43 and the Time periods Year, Quarter, Month, Week, and Date and press Next.

   
   Figure 8-43

6. In the Select Calendars page select Fiscal calendar as shown in Figure 8-44 and click Next.

   
   Figure 8-44

7. In the Review New Hierarchies page the wizard shows the various hierarchies to be created within the dimension as shown in Figure 8-45. Please review them and click Next.

   
   Figure 8-45

8. In the final page of the Wizard enter the name of the dimension as ServerTimeDimension as shown in Figure 8-46 and click Next.

Figure 8-46

The ServerTimeDimension is now created and you can see all the hierarchies and attributes in the Dimension Designer as shown in Figure 8-47. Since this dimension is created from Analysis Services instead of the DSV you will see a pane called Time Periods that lists all the periods that are available for selection. You can add additional Time Periods as attributes in the dimension. Date is the key attribute of this dimension and it cannot be deleted.

Figure 8-47

Deploy the changes to Analysis Services instances. You can now browse the hierarchies in ServerTimeDimension as shown in Figure 8-48. The formats used for members in various levels cannot be changed. You can add this ServerTimeDimension to your cubes and define the relationship. If you want to establish a relationship at the key level with the ServerTimeDimension you do need to have a column of type Date in the fact table(s). If a time table was not present in your data warehouse then you do need to create that table and then create a dimension from that time. However Analysis Services simplifies the creation Time dimension with the help of Server Time dimension.

Figure 8-48