SSAS adding a time dimension and using it


  • Hi

    (Using SQL Server 2008 R2)

    Im rather new to SQL Server and certainly to SSAS. AT the moment I am working on a project in which I need to implement SSAS on a SQL Server. The database originates from UniVerse and it does not have a time table I can use as a dimension.


    I have found a script that will be able to set up the time dimension table, but:

    -how do I add it to the database


    -how do I make it available to be chosen as a dimension?


    Thanks GREATLY!



    Wednesday, October 6, 2010 11:03 PM


All replies

  • 1)

    1. Create a table with the necessary schema and populate the tables based on your script

    2. In SSAS, DSV add the Time table and the in create a Time Dimension on top of the Time table

    2) You can also use the Server Time Dimension provided by the SSAS. Go to Time Dimension and create a new Dimension. A window appears, there you have an option to create a Time Dimension

    Thursday, October 7, 2010 2:37 AM
  • Thanks for that


    I have been able to create the table (but did that in SQL Server MAnagement Studio. I will add the code I found to do that at the end). 


    Im using SQL Server 2008r2 and dont seem to have a separate SSAS Data Source View. I am also not able to add the Time Dimension.


    I also managed to create a time dimension within a SSAS project, but again cannot select the dim to include it in my project (though it shows in the Solution Explorer).


    Any directions (new to SQL Server)?


    The code I found and used to create Time table:


    CREATE PROCEDURE [dbo].[usp_Create_dim_Date_Table]




    @StartDate smalldatetime = '20080101',


    @EndDate smalldatetime = '20081231'










    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dim_Date]') AND type in (N'U'))


    DROP TABLE [dbo].[dim_Date]


    CREATE TABLE dbo.dim_Date(


    [MonthKey] [int] NOT NULL,


    [MonthName] [nvarchar](15) NOT NULL,


    [YearKey] [smallint] NOT NULL,


    [YearName] [nvarchar](4) NOT NULL,


    [MonthOfYearKey] [tinyint] NOT NULL,


    [MonthOfYearName] [nvarchar](15) NOT NULL,






    [MonthKey] ASC






    set language English -- or any other


    set datefirst 1 -- 7 for Sunday, 1 for Monday


    set dateformat dmy -- set differently if necessary


    declare @CurrentDate smalldatetime


    declare @TimeID as int


    set @CurrentDate = @StartDate


    set @TimeID = 1


    while ( @CurrentDate <= @EndDate )




    insert into [dbo].[dim_Date]




    [MonthKey] ,


    [MonthName] ,


    [YearKey] ,


    [YearName] ,


    [MonthOfYearKey] ,










    -- month


    datepart(year, @CurrentDate ) * 100 + datepart(month, @CurrentDate ),


    convert(varchar(4), datepart(year, @CurrentDate )) + ' ' +


    'M ' + convert(char(2), datepart(month, @CurrentDate )),


    -- year


    datepart(year, @CurrentDate ),


    convert(varchar(4), datepart(year, @CurrentDate )),


    -- month of year


    datepart(month, @CurrentDate ),


    datename(month, @CurrentDate )




    set @TimeID = @TimeID + 1


    set @CurrentDate = dateadd( month, 1, @CurrentDate )





    Sunday, October 10, 2010 11:32 PM
  • Link to create data source view

    So here you can add table to data source view. There are two ways to create a time dimension. 

    1. Create a table of your own. Populate the data in the table and then add that table in DSV and then add it as a dimension under Dimension folder. (Here you need to choose existing tables

    2) You can also use the readily available feature provided by microsoft to create time dimension. It populates data also into the table.


    Monday, October 11, 2010 3:31 AM
  • You can see this article. It shows how to create a time dimension. It also discusses some possible problem you might encounter while creating a time dimension.


    My Blog
    Tracking SSRS
    Monday, October 11, 2010 6:32 AM