Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Blog by Rahul Sahay

I have a total experience of 6 years primarily in databases (T-SQL and Performance Tuning). I have worked on different versions starting with SQL Server 2000 to SQL Server 2008 R2 for companies like Accenture and Airtel. I have completed Microsoft certification in MCTS (SQL SERVER). I did my MCA (Masters Of Computer Application) degree from Bangalore and have a BCA (Bachelor Of Computer Applications) from Indore.

Creating Dimension Table ==> Time (Example)


Script for Dimension table for Time.

Here Date_ID is the primary key which can be used as the foreign key in the fact tables.

CREATE TABLE [dbo].[DATE_DIM]

                (

                 [DATE_ID] [bigint] IDENTITY(1, 1)

                                    NOT NULL

                ,[SQL_DATE] [datetime] NOT NULL

                ,[DAY] [smallint] NOT NULL

                ,[DAY_OF_WEEK] [smallint] NULL

                ,[WEEK] [smallint] NOT NULL

                ,[MONTH] [SMALLINT] NOT NULL

                ,[QUARTER] [SMALLINT] NOT NULL

                ,[YEAR] [SMALLINT] NOT NULL

                ,[DAY_OF_YEAR] [SMALLINT] NOT NULL

                ,[DAY_TEXT] [VARCHAR](50) NOT NULL

                ,[MONTH_TEXT] [VARCHAR](50) NOT NULL

                ,[QUARTER_TEXT] [VARCHAR](50) NOT NULL

                 )

                

 declare @date datetime

 declare@enddate datetime

 set @date = CONVERT(datetime,'01-01-2012')

 set @enddate = CONVERT(datetime,'12-31-2025')

 while @date <= @enddate

   begin

   insert into DATE_DIM

   ([SQL_DATE]

   ,[day]

   ,[DAY_OF_WEEK]

   ,[WEEK]

   ,[MONTH]

   ,[QUARTER]

   ,[YEAR]

   ,[DAY_OF_YEAR]

   ,[DAY_TEXT]

   ,[MONTH_TEXT]

   ,[QUARTER_TEXT]

   )

     select

    @date assqldate

    ,datepart(D,@date) as [day]

    ,datepart(DW,@date) as [day_of_week]

    ,DATEPART(WK,@date) as [week]

    ,DATEPART (M,@date) as [month]

    ,DATEPART(Q,@date) as [quarter]

    ,DATEPART(DY,@date) as [day_of_year]

    ,DATEPART(Y,@date) as [year]

    ,DATENAME(DW,@date) as day_text

    ,DATENAME(M,@date) as month_text

    ,'Q' + CONVERT(CHAR(1), DATENAME(QQ, @DATE)) AS QUARTERTEXT

    set @date = DATEADD(D,1,@date)   

   end                

 

Comments

Leave a comment on the original post [rahulsahay123.blogspot.com, opens in a new window]

Loading comments...