Estructura de la Dimensión Tiempo y script de carga para SQLServer

 

In a Data Warehouse there are a number of common dimensions such as Geographic and Time. 

I leave here a script to create the Time Dimension table and a procedure to loading it between two dates:

 

/ * Destination database * /

use PAnalisys

/ * Create table * / 
create table DIM_TIME
( 
   DateSK int not null, 
   Date date not null, 
   Year smallint not null, 
   Quarter smallint not null, 
   Month smallint not null, 
   Week smallint not null, 
   Day smallint not null, 
   WeekDay smallint not null, 
   NQurater char (7) not null, 
   NMonth char (15) not null, 
   NMonth3L char (3) not null, 
   NWeek char (10) not null, 
   NDay char (6) not null, 
   NWeekDay char (10) not null 
   constraint PK_DIM_TIME CONSTRAINT PRIMARY KEY CLUSTERED 
   ( 
       Asc Date 
    ) 
)

 

/ * Loading Script * /

DECLARE @DateFrom as smalldatetime, @DateTo as smalldatetime 
DECLARE @DateYYYYMMDD  int
DECLARE @Year smallint, @Quarter char (2), @Month smallint 
DECLARE @Week smallint, @Day smallint, @WeekDay smallint 
DECLARE @NQuarter char (7), @NMonth char (15) 
DECLARE @NMonth3l char (3) 
DECLARE @NWeek char (10), @NDay char (6), @NWeekDay char (10) 
-- Initial Date Set if different from the server 
SET DATEFORMAT dmy
SET DATEFIRST 1

BEGIN TRANSACTION 
   -- Delete current data, if necessary 
   -- TRUNCATE TABLE FROM DIM_TIME

   -- Range of dates to generate: 01/01/2006 to 31/12/Current year +2 
   SELECT @DateFrom = CAST ('20060101 'AS smalldatetime) 
   SELECT @DateTo = CAST (CAST (YEAR (GETDATE ()) +2 AS CHAR (4)) + '1231 'AS smalldatetime) 

   WHILE (@DateFrom <= @DateTo) BEGIN 
     SELECT @FechaAAAAMMDD = YEAR (@DateFrom) * 10000 + 
                                                          MONTH (@DateFrom) * 100 + 
                                                          DATEPART (dd, @ DateFrom) 
     SELECT @Year = DATEPART (yy, @DateFrom) 
     SELECT @Quarter = DATEPART (qq, @DateFrom) 
     SELECT @Month = DATEPART (m, @DateFrom) 
     SELECT @Week = DATEPART (wk, @DateFrom) 
     SELECT @Day = RIGHT ('0 '+ DATEPART (dd, @DateFrom), 2) 
     SELECT @WeekDay = DATEPART (DW, @DateFrom) 
     SELECT @NMonth = DATENAME (mm, @DateFrom) 
     SELECT @NMonth3l = LEFT (@NMonth, 3) 
     SELECT @NQuarter = 'Q' + CAST (@Quarter as CHAR (1)) + '/' + RIGHT (@Year, 2) 
     SELECT @NWeek = 'Week' + CAST (@week AS CHAR (2)) + '/' + RIGHT (RTRIM (CAST (@Year AS CHAR (4))), 2) 
     SELECT @NDay = CAST (@Day AS CHAR (2)) + '' + RTRIM (@NMES) 
     SELECT @NWeekDay = DATENAME (dw, @DateFrom) 
     INSERT INTO PAnalytics.dbo.DIM_TIME
     ( 
       DateSK, 
       Date, 
       Year, 
       Quarter, 
       Month 
       Week, 
       Day, 
       WeekDay, 
       NQuarter, 
       NMonth, 
       NMonth3L, 
       NWeek, 
       NDay, 
       NWeekDay
     ) VALUES 
     ( 
       @DateYYYYMMDD, 
       @DateFrom, 
       @Year 
       @Quarter, 
       @Month 
       @Week, 
       @Day, 
       @WeekDay, 
       @NQuarter, 
       @NMonth, 
       @NMonth3l, 
       @NWeek, 
       @NDay, 
       @NWeekDay
     ) 

     - Increase the loop 
     SELECT @DateFrom = DATEADD (DAY, 1, @DateFrom) 
   END 
COMMIT TRANSACTION 
 

From here everyone can modify it at his convenience by adding or removing attributes