none
SSAS adding a time dimension and using it

    Question

  • 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

    and

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

     

    Thanks GREATLY!

    Dan

     

    Wednesday, October 6, 2010 11:03 PM

Answers

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

    http://msdn.microsoft.com/en-us/library/ms174832.aspx

    http://technet.microsoft.com/en-us/library/ms174832(SQL.90).aspx


    vinu
    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'

     

    )

     

    AS

     

    BEGIN

     

    SET NOCOUNT ON;

     

    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,

     

    CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED

     

    (

     

    [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 )

     

    begin

     

    insert into [dbo].[dim_Date]

     

    (

     

    [MonthKey] ,

     

    [MonthName] ,

     

    [YearKey] ,

     

    [YearName] ,

     

    [MonthOfYearKey] ,

     

    [MonthOfYearName]

     

    )

     

    values

     

    (

     

    -- 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 )

     

    end

     

    END

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

    http://msdn.microsoft.com/en-us/library/ms174543.aspx

    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.

    http://msdn.microsoft.com/en-us/library/ms174832.aspx

    http://technet.microsoft.com/en-us/library/ms174832(SQL.90).aspx

     


    vinu
    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.

    http://blog.programmingsolution.net/ssas-2008/period-dimension-time-dimension-creation-with-year-month-day-hierarchy/

     


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