SSAS Server Time Dimension

While working with SQL Server Analysis Services, I found server time dimension quite interesting because of the fact that the data for this dimension does not come from a dimension table in the data warehouse, but it is generated by Analysis Services and stored in a proprietary file structure on the server. We simply specify the beginning date and end date of the dimension, select the time periods to include such as year, quarter, month, or date, and choose the special calendars, if any, to add to the dimension. When we create a Server Time dimension, no such table is created but data available to this dimension type is maintained solely by Analysis Services. In order to use this dimension with a fact table, we will need to have a date/time column instead of a dimension key in the fact table.

It is important to understand that Analysis services will use only the date part of this column to join the fact table with Server Time dimension which means we need to remove the time part from the date/time column in our data warehouse table. This can easily be achieved by creating a Named Calculation in our data warehouse table. Using a Named Query or a Named Calculation gives us the ability to manipulate the data structures for use by Analysis Services even if we don’t have permissions to make similar changes at the database level.

Named Calculation can be created by following the below steps.

• In Solution Explorer, double-click SSAS Step by Step DW.dsv to open the Data SourceView Designer.
• Right click the table that will have the desired Named Calculation and select New Named Calculation.
• In the Column name write a name of your choice. Note that this Name Calculation will act as a column for the table and will be used instead of date/time column in the table.
• In the Expression column, we can to use SQL CONVERT function to get the date only part of the date/time column.
  
  
  CONVERT(varchar(11), CreatedDate, 20)
  
  
  
  
  
  
  N.B. in the above statement we are trying to convert a date/time column (CreatedDate) into a format that will only have date part. We must use 20 in the third parameter which represents yyy-mm-dd format. This is used by server time dimension which it generates dates to be stored in a file.  

That is it. We can now use Named Calculation (Calendar - in my case here), anywhere in our OLAP cube.

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
•