Après la création de notre table DimTime dans la 1ère partie de cet article, nous allons maintenant apporter quelques améliorations sur notre dimension.
Par expérience, je vous recommanderais d’utiliser une clé PKDate de type int au format aaaammjj.
Ce type de clé vous permettra d’obtenir de meilleurs performances.
Cette codification au format aaaammjj devra être généralisée à tous les niveaux de notre axe temps : année, semestre, trimestre, mois, semaine… Je vous suggère donc que chaque niveau (année, semestre, trimestre, mois, semaine, jour) soit composé de trois attributs distincts :
- Code
- Date
- Nom
Par exemple, le mois devra être composé des attributs suivants :
| Attribut | Type | Valeur exemple | Commentaire |
| MoisCode | Int | 20131101 | Format aaaammjj, par défaut doit toujours se référer à la 1ère date de la période |
| MoisDate | SmallDateTime | 200931101 00:00:00 | Format date |
| MoisName | Varchar(50) | Novembre 2013 | Valeur au format affichée |
Les valeurs des attributs Code devront être déduites à partir des valeurs attributs Date, à l’aide d’un script SQL de mise à jour (requête UPDATE). Afin de finaliser l’axe temps, je vous conseille d’exécuter le script suivant :
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
Ce script réalise les opérations suivantes :
- Il renomme un certain nombre de colonnes.
- Il modifie des types de données DateTime en SmallDateTime.
- Il modifie des types de données nvarchar en varchar.
- Il crée la clé Temps_PK ainsi que les colonnes AnneeCode, SemestreCode, TrimestreCode, MoisCode et SemaineCode.
- Il remplit les valeurs pour les champs créés précédemment.
- Il modifie les colonnes pour interdire la valeur null.
- Il repositionne la clé primaire sur Temps_PK.
Au final, après exécution du script, la table DimTime devra avoir ce formalisme :
Le champ Time_PK devra bien entendu être une clé primaire. En revanche, exceptionnellement, n’activez pas l’incrémentation automatique pour cette table.
- Vérifiez que la table DimTime est bien remplie :
En effet, la table DimTime, nous venons de créer, est parfaitement standard et réutilisable, quels que soient vos futurs projets d’entrepôt de données.
Leave a comment