After creating our DimTime table in the 1st part of this article, we will now make some improvements on our dimension.
From experience, I would recommend using a key PKDate int type in format yyyymmdd.
This type of key will allow you to get better performance.
This codification in yyyymmdd format should be generalized to all levels of our time axis: year, semester, quarter, month, week ... I therefore suggest that each level (year, semester, quarter, month, week, day) be made up of three distinct attributes:
- --
- Reserve
- Name
For example, the month should consist of the following attributes:
| Attribute | Type | Example value | Comments |
| MoisCode | Int | 20131101 | Format yyyymmdd, by default must always refer to the 1st date of the period |
| MoisDate | SmallDateTime | 200931101 00:00:00 | Date format |
| MoisName | Varchar (50) | November 2013 | Value in displayed format |
The values of the Code attributes must be deduced from the Date attribute values, using an update SQL script (UPDATE query). In order to finalize the time axis, I advise you to run the following script:
USE [SSASTime]
-- Suppression de la clé primaire
ALTER TABLE [dbo].[DimTime] DROP CONSTRAINT [PKDimTime]
GO
-- Renommage des colonnes
EXEC sp_rename N'[dbo].[DimTime].[PKDate]', N'Date', 'COLUMN'
GO
EXEC sp_rename N'[dbo].[DimTime].[DateName]', N'Day', 'COLUMN'
GO
EXEC sp_rename N'[dbo].[DimTime].[Year]', N'YearDate', 'COLUMN'
GO
EXEC sp_rename N'[dbo].[DimTime].[HalfYear]', N'HalfYearDate', 'COLUMN'
GO
EXEC sp_rename N'[dbo].[DimTime].[Quarter]', N'QuarterDate', 'COLUMN'
GO
EXEC sp_rename N'[dbo].[DimTime].[Month]', N'MonthDate', 'COLUMN'
GO
EXEC sp_rename N'[dbo].[DimTime].[Week]', N'WeekDate', 'COLUMN'
GO
-- Modification des types de données DateTime en SmallDateTime
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [Date] [smalldatetime] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [YearDate] [smalldatetime] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [HalfYearDate] [smalldatetime] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [QuarterDate] [smalldatetime] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [MonthDate] [smalldatetime] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [WeekDate] [smalldatetime] NOT NULL
GO
-- Modification des types de données nvarchar en varchar
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [Day] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [YearName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [HalfYearName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [QuarterName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [MonthName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [WeekName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
-- Création des colonnes de type _Code
ALTER TABLE [dbo].[DimTime] ADD [Time_PK] [int] NULL
ALTER TABLE [dbo].[DimTime] ADD [YearCode] [int] NULL
ALTER TABLE [dbo].[DimTime] ADD [HalfYearCode] [int] NULL
ALTER TABLE [dbo].[DimTime] ADD [QuarterCode] [int] NULL
ALTER TABLE [dbo].[DimTime] ADD [MonthCode] [int] NULL
ALTER TABLE [dbo].[DimTime] ADD [WeekCode] [int] NULL
GO
-- Remplir Time_PK
UPDATE [dbo].[DimTime]
SET [Time_PK] =
(
CAST(YEAR([Date]) AS varchar(50))
+
(
CASE WHEN MONTH([Date])<10
THEN '0' + CAST(MONTH([Date]) AS varchar(50))
ELSE CAST(MONTH([Date]) AS varchar(50))
END
)
+
CASE WHEN DAY([Date])<10
THEN '0' + CAST(DAY([Date]) AS varchar(50))
ELSE CAST(DAY([Date]) AS varchar(50))
END
)
-- Remplir YearCode
UPDATE [dbo].[DimTime]
SET [YearCode] =
(
CAST(YEAR([YearDate]) AS varchar(50))
+
(
CASE WHEN MONTH([YearDate])<10
THEN '0' + CAST(MONTH([YearDate]) AS varchar(50))
ELSE CAST(MONTH([YearDate]) AS varchar(50))
END
)
+
CASE WHEN DAY([YearDate])<10
THEN '0' + CAST(DAY([YearDate]) AS varchar(50))
ELSE CAST(DAY([YearDate]) AS varchar(50))
END
)
-- Remplir HalfYearCode
UPDATE [dbo].[DimTime]
SET [HalfYearCode] =
(
CAST(YEAR([HalfYearDate]) AS varchar(50))
+
(
CASE WHEN MONTH([HalfYearDate])<10
THEN '0' + CAST(MONTH([HalfYearDate]) AS varchar(50))
ELSE CAST(MONTH([HalfYearDate]) AS varchar(50))
END
)
+
CASE WHEN DAY([HalfYearDate])<10
THEN '0' + CAST(DAY([HalfYearDate]) AS varchar(50))
ELSE CAST(DAY([HalfYearDate]) AS varchar(50))
END
)
-- Remplir QuarterCode
UPDATE [dbo].[DimTime]
SET [QuarterCode] =
(
CAST(YEAR([QuarterDate]) AS varchar(50))
+
(
CASE WHEN MONTH([QuarterDate])<10
THEN '0' + CAST(MONTH([QuarterDate]) AS varchar(50))
ELSE CAST(MONTH([QuarterDate]) AS varchar(50))
END
)
+
CASE WHEN DAY([QuarterDate])<10
THEN '0' + CAST(DAY([QuarterDate]) AS varchar(50))
ELSE CAST(DAY([QuarterDate]) AS varchar(50))
END
)
-- Remplir MonthCode
UPDATE [dbo].[DimTime]
SET [MonthCode] =
(
CAST(YEAR([MonthDate]) AS varchar(50))
+
(
CASE WHEN MONTH([MonthDate])<10
THEN '0' + CAST(MONTH([MonthDate]) AS varchar(50))
ELSE CAST(MONTH([MonthDate]) AS varchar(50))
END
)
+
CASE WHEN DAY([MonthDate])<10
THEN '0' + CAST(DAY([MonthDate]) AS varchar(50))
ELSE CAST(DAY([MonthDate]) AS varchar(50))
END
)
-- Remplir WeekCode
UPDATE [dbo].[DimTime]
SET [WeekCode] =
(
CAST(YEAR([WeekDate]) AS varchar(50))
+
(
CASE WHEN MONTH([WeekDate])<10
THEN '0' + CAST(MONTH([WeekDate]) AS varchar(50))
ELSE CAST(MONTH([WeekDate]) AS varchar(50))
END
)
+
CASE WHEN DAY([WeekDate])<10
THEN '0' + CAST(DAY([WeekDate]) AS varchar(50))
ELSE CAST(DAY([WeekDate]) AS varchar(50))
END
)
-- modifier les colonnes pour interdire la valeur null
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [Time_PK] [int] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [Day] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [YearCode] [int] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [YearName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [QuarterCode] [int] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [QuarterName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [HalfYearCode] [int] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [HalfYearName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [MonthCode] [int] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [MonthName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [WeekCode] [int] NOT NULL
ALTER TABLE [dbo].[DimTime] ALTER COLUMN [WeekName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
GO
-- Positionner la cle primaire sur Time_PK
ALTER TABLE [dbo].[DimTime] ADD CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time_PK])
GO
This script does the following:
- It renames a number of columns.
- It changes DateTime data types to SmallDateTime.
- It changes nvarchar data types to varchar.
- It creates the Temps_PK key as well as the YearCode, SemesterCode, QuarterCode, MonthCode and WeekCode columns.
- It fills in the values for the fields created previously.
- It modifies the columns to prohibit the null value.
- It puts the primary key back on Temps_PK.
In the end, after running the script, the DimTime table should have this formalism:
The Time_PK field must of course be a primary key. However, exceptionally, do not enable automatic incrementation for this table.
- Check that the DimTime table is full:
Indeed, the DimTime table we just created is perfectly standard and reusable, whatever your future data warehouse projects.
Leave a comment