Share via

Extension\Expansion of Time Dimension in Analysis Services

  • Article

Introduction

In any Enterprise data model, time dimension plays a crucial role and generally every Enterprise data warehouse has at least one time dimension.
In most of the data warehouses time table is built as a one time load for a specific date period and later when required it is extended\expanded as per the requirement. This article would focus on techniques to extend\expand the time dimension in Microsoft Analysis Services.

Time Dimension in Analysis Services

Microsoft Analysis Services offers a flexibility to create time dimension while creating a multidimensional model. in Analysis Services time dimension can be generated in two ways

  1.  Generate a time table in the data source
  2.  Generate a time table on the server

In the first case analysis services generates a time table and store this table in the data source specified, this generated time table is used to build the time dimension.

In the later case analysis services generates and stores a time table on the server instead of in the data source, this time table from server is used to build the time dimension.

For more information on this please see the following link.
http://msdn.microsoft.com/en-us/library/ms174832.aspx

Even in case of Analysis Services while building a time dimension it is required to mention the date range for which time dimension would be created. And later when required the time dimension can be expanded in the techniques mentioned in the following sections.

Extension\Expansion of Time Dimension

As we saw that there are two ways of generating time dimension in Analysis Services, the technique to expand the time dimension is more or less different for both the cases.

Extension\Expansion technique for time dimension created using time table generated in the data Source

Below is the snapshot of time dimension before change :

         

  • Identify the time table generated in the data source. In my case it is [Time_Dim]. Here on I will refer this as 'old time table'.
  • Rename the time table to any name  of your choice.                                                                                                                                      EXEC sp_rename 'dbo.Time_Dim', 'Time_Dim_Backup';
  • Create a new Analysis Services Project and  build a new time dimension using 'generate a time table in the data source' option, the new time dimension must be very similar to the existing time dimension which we renamed in the previous step, but with extended date range as per requirement ( In my case I am extended it to 2010). While generating the new time table make sure that the time table is generated in same database as the old time table and name of the new time table must be same as that of the old time table. In my case the name of new time table would be [Time_Dim].
  • Close the new analysis services project , this project is no more required. You can go ahead and delete this project.
  • Now process update the time dimension and by doing so time dimension would have been extended to the date range required.

             

Extension\Expansion technique for time dimension created using time table generated on the server

  • In SQL Server Management Studio (SSMS) connect to the cube database containing time dimension in focus.
  • Generate an 'Alter Script' for the time dimension. For example consider [Time_Dim].

   

  • Modify the StartDate and EndDate i.e. date range hard-coded in the script as show in the figure below.

  • Execute the modified alter script (After Script).
  • Now process update the time dimension and by doing so time dimension would have been extended to the date range required.