Datamart : la dimension du temps
La dimension du temps dans un datamart avec PostgreSQL
Le modèle en étoile de l'informatique décisionnelle repose sur 2 grands types de tables : les dimensions (dim) et les faits (fact).
En règle générale, le calcul des faits nécessite une dimension de temps afin de suivre l'évolution d'un indicateur dans la durée. De cette nécessité découle donc le besoin de générer une dimension du temps suffisamment précise pour être utilisée dans la plupart des contextes.
Même si ce n'est pas une règle absolue, il convient de diviser le temps en 2 dimensions : la date et l'heure.
La date calendaire correspond au jour et s'incrémente d'un enregistrement dans la dimension à chaque nouveau jour.
Le temps correspond à l'heure dans une journée. Le nombre d'enregistrements est généralement fini car il correspond au nombre de tranches horaires possibles dans une journée. Cela peut être à la minute, à l'heure, au quart d'heure… Tout dépend de la granularité désirée.
La dimension date : la table dim_calendar
La date calendaire est stockée dans une table (nommée ici) dim_calendar afin d'y recueillir toutes les informations relatives à un jour donné. Elle ressemble à :
CREATE TABLE dim_calendar
(
id_dim_calendar bigint NOT NULL, -- Identifiant du jour (epoch)
date_day date NOT NULL, -- Date du jour
lib_day character varying(20) NOT NULL, -- Libellé du jour
num_month integer NOT NULL, -- Numéro du mois (1-12)
lib_month character varying(15) NOT NULL, -- Libellé du mois
num_year integer NOT NULL, -- Année (YYYY)
num_day integer NOT NULL, -- Jour du mois (1-31)
num_quarter integer, -- Trimestre
num_semester integer, -- Semestre
num_week integer, -- Semaine de l'année (1-53)
num_day_week integer, -- Jour de la semaine
id_day_julian integer, -- Jour Julien
id_week integer NOT NULL, -- Semaine (YYYYWW)
CONSTRAINT pk_dim_calendar PRIMARY KEY (id_dim_calendar)
)
id_dim_calendar
Il s'agit de l'indentifiant du jour. Il peut s'agir d'un nombre auto-incrémenté, mais le jour au format epoch fournit déjà une valeur numérique et peut, dans bien des cas, facilité la détermination du jour juste par une opération SQL.
Il se définit (ou se calcule) de la façon suivante :
date_part('epoch',date_trunc('days',ma_date))::bigint
date_day
Il s'agit de la date du jour sous la forme d'une valeur «humaine» de jour, telle que nous la connaissons. Elle se calcule de différentes façons :
date_trunc('days',ma_date)::date
dans le cas où ma_date est de type timestamp, ou simplement par la fonction to_date() dans le cas d'un format textuel.
En règle générale, la date d'une mesure est de type timestamp.
lib_day
Libellé du jour.
to_char(date_day,'day')
num_month
Numéro du mois (1-12).
to_number(to_char(date_day,'mm'),'99')
lib_month
Libellé du mois.
to_char(date_day,'month')
num_year
Année (au format numérique).
to_number(to_char(date_day,'yyyy'),'9999')
num_day
Jour au format numérique (1-31).
to_number(to_char(date_day,'dd'),'99')
num_quarter
Numéro du trimestre (1-4).
to_number(to_char(date_day,'q'),'9')
num_semester
Numéro du semestre (1-2).
CASE WHEN to_number(to_char(date_day,'mm'),'99') < 7 THEN 2 ELSE 1 END
num_week
Numéro de la semaine dans l'année (1-53).
to_number(to_char(date_day,'iw'),'99')
num_day_week
Numéro du jour dans la semaine (0-7).
to_number(to_char(date_day,'d'),'9')
id_day_julian
Numéro du jour julien. Cette valeur pourrait aussi servir d'identifiant à la place de id_dim_calendar.
to_char(date_day,'J')::integer
id_week
Semaine au format YYYYWW.
to_number(to_char(date_day,'yyyy')||to_char(date_day,'iw'),'999999')
Avec un petit ajustement (la semaine 53 doit être positionnée à l'année N-1) :
UPDATE dim_calendar
SET id_week = to_number(to_char(num_year - 1,'9999')||to_char(date_day,'iw'),'999999')
WHERE
dim_calendar.num_month = 1
AND dim_calendar.num_week = 53;
La dimension temps : la table dim_time
La structure et le contenu de cette table varient beaucoup avec la granularité. Il est donc difficile de proposer un exemple utile.
A minima, la table doit contenir 3 colonnes :
CREATE TABLE dim_time
(
id_dim_time bigint NOT NULL,
begin_time time without time zone NOT NULL,
end_time time without time zone NOT NULL,
CONSTRAINT pk_dim_time PRIMARY KEY (id_dim_time)
)
id_dim_time est dans ce cas une valeur numérique gérée par une séquence. Ainsi, contrairement à un calendrier dont le niveau le plus fin est le jour, si les intervalles de temps devaient être modifiés, il suffirait l'ajouter des lignes et la séquence s'incrémenterait automatiquement, sans chercher d'opération complexe.
Il ne reste plus qu'à insérer les données.
Supposons que l'aggrégation nécessite un intervalle au quart d'heure à son niveau le plus fin, il convient alors d'insérer les données de la façon suivante :
INSERT INTO stats.dim_time(begin_time,end_time) VALUES('00:00:00','00:15:00'); INSERT INTO stats.dim_time(begin_time,end_time) VALUES('00:15:00','00:30:00'); … INSERT INTO stats.dim_time(begin_time,end_time) VALUES('23:45:00','24:00:00');
Notez que la valeur «24:00:00» est parfaitement acceptée par PostgreSQL.
Si le travail de création du script SQL peut s'avérer fastidieux, ce dernier peut assez facilement être généré.
L'usage de ces intervalles se font avec une borne large sur la valeur inférieure et une borne stricte sur la valeur supérieure, ce qui implique :
mon_heure >= dim_time.begin_time AND mon_heure < dim_time.end_time
et non
mon_heure BETWEEN dim_time.begin_time AND dim_time.end_time
qui est un intervalle à bornes larges des deux côtés.
Usage
Une fois les deux tables créées et alimentées, l'usage est assez simple :
-
la table de faits possède deux clefs étrangères pointant respectivement vers dim_calendar (id_dim_calendar) et dim_time (id_dim_time);
-
tout calcul agrégé au niveau du jour possède obligatoirement un id_dim_calendar auquel il fait référence;
-
tout calcul agrégé au niveau d'une tranche horaire (intraday) possède un id_dim_calendar et un id_dim_time auxquels il fait référence.
Modèle en flocon
Plutôt que de gérer deux liens vers deux dimensions depuis la table de faits, ce qui oblige a évaluer cahque date d'opération selon sa composante de date et sa composante de temps, il est tout à fait possible de conserver une comparaison date/temps plus naturelle en utilsant un modèle en flocon.
Dans ce cas, les deux tables dim_calendar et dim_time demeurent, mais une nouvelle table vient s'intercaler (dim_date_time). Cette dernière contiendra la combinaison des deux dimensions, soit un enregistrement pour chaque composante date/tranche horaire.
CREATE TABLE dim_date_time ( id_dim_date_time bigint NOT NULL, id_dim_time bigint NOT NULL, id_dim_calendar bigint NOT NULL, CONSTRAINT pk_dim_date_time PRIMARY KEY (id_dim_date_time) )
Ce nouvel enregistrement aura son propre identifiant qui sera utilisé directement dans la table de faits.
L'alimentation de cette table sera le produit cartésien de dim_calendar et de dim_time. id_dim_date_time sera la date au format epoch du début de tranche horaire :
SELECT date_part('epoch',TO_TIMESTAMP(TO_CHAR(dim_calendar.date_day,'YYYY-MM-DD') ||' '|| TO_CHAR(dim_time.begin_time,'HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'))::bigint as id_dim_date_time FROM dim_calendar CROSS JOIN dim_time ORDER BY 1;