Datamart : la dimension du temps

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;