Clarification sur le Datawarehousing

Depuis quelques temps déjà on nous parle de plus en plus de datawarehouse (c'est un assez vieux concept qui semble revenir à la mode) et d’un terme que je trouve un peu pompeux le BI. Etant obligé par mon travail à replonger dans cet univers (mauvais jeu de mot ?), voici un petit billet concernant quelques cogitations à ce sujet…

OLTP et OLAP

OLTP : online transaction process : il s’agit là des base de données bien connu et bien classique (RDMS, entendez relational database management system). Le but de ces bases de données est de stocker des données utilisateurs via des applications clientes. Les paramètres importants sont :

1        1)Transactionnel (toujours se retrouver dans un état cohérent, même si le système crash)

2          2)Intégrité de donnée (clé primaire, foreign key, contrainte)

3          3)Index

4          4)Gestion d’accès concurrent (cfr transaction : Atomicity, Consistency, Isolation, Durability)

L’accent est mis sur la rapidité d’accès aux données et l’intégrité de celle-ci. Idéalement, de mon point de vue, une base de donnée OLTP ne devrait même pas être utilisée pour du reporting (sauf reporting bête et méchant).  Un chirurgien doit attendre d’opérer un patient, car il ne peut accéder aux informations du patient parce que le système est bloqué par quelqu’un générant un rapport trop lourd. Ces types de situations sont évidemment inacceptables.

Pour optimiser et réduire la redondance des informations, on utilise abusivement des formes normales.

Dans certains cas on utilise des ORM (iBATIS, Hibernate…) pour construire la structure du RDMS et assurer un mapping cohérent entre l’architecture objet et sa persistance transactionnelle.

OLAP : online analytical process

Ces bases de données sont dédiées au reporting, voir au reporting sur plusieurs dimensions. Dans cec cas de figure aucun souci par rapport au performance, puisque les deux bases de données sont séparées physiquement.

La structure des tables n’a plus rien à voir avec une base de donnée classique puisque la plupart des tables sont dé-normalisées. L’accès en écriture se fait en général via un processus d’ETL (Export, Transform, Load).

Pratiquement on utilise une table de fait représentant des comptes  en fonction de différentes dimensions. (Axe d’analyse), il s’agit de la table centrale. Les  dimensions sont des variables catégorielles independantes. L’ensemble des dimension forment un espace appelé Univers. La table centrale contient une foreign key pour chaque dimension (on appelle cette modélisation, modélisation en étoile, c’est une des plus simples). On peut aussi décomposer les dimensions suivant leur hiérarchies et définir des sous tables par dimension et niveau hiérarchique (architecture en flocon de neige). On peut aussi faire un mix des deux…

D’un point de vue pratique, même si des clés primaires peuvent être définies conceptuellement, aucune contrainte n’est mise sur la DB et l’autocommit est aussi désactivé puisque la DB fonctionne en read only.

Une fois les dimensions clairement établies, celles-ci peuvent être lues via certains outils de BI (Pentaho, offre quelque bon exemple open source, ceci dit il est aussi possible de faire du BI avec Excel).

Avant de se lancer dans la conception du système, il est important de se figurer les rapports dont on a besoin et les axes suivant lesquels on voudrait naviguer dans les données. Il faut veiller que ses axes soient indépendants (ou alors les regrouper sous forme hiérarchique). On définit ensuite la hiérarchie, puis on modélise.

La partie tricky à mon sens est la partie ETL car si quelque chose foire là-dedans, cela peut très vite devenir problématique. D’une part, les données peuvent venir d’une foulée de systèmes différents, donc on a besoin de réconciliation et de consolidation (remettre les bons ID), d’autre part si la réconciliation se passe mal, ce sont tous les résultats qui peuvent être impacté. C’est pourquoi il est important d’une part de travailler dans une sandbox, et d’autre part de bien dater les modifications.

Une fois les données prêtes (dans mon cas via des scripts PERL), il faut ensuite faire un bulk-copy dans la base de donnée OLAP. Comme cette DB est read-only, la seule phase d’écriture se fait au moment du chargement (LOAD), en général on utilise un bulk-copy (bcp) ou COPY en Postgres.

Les bases de données OLAP sont souvent qualifiées de Datawarehouse, en général le domaine qui nous occupe s’appelle un datamart (partie d’un datawarehouse), si le domaine concerné impacte le business par rapport à ses ventes on parle alors de BI (Business intelligence). Dans ce cas les dimensions sont souvents:la géographie (Région/Pays/Ville), le temps(Year/Quarter/Month), les types de produits. La table de fait est en général le total des ventes en fonctions des dimensions précitées.

Plusieurs systèmes peuvent être utilisés pour créer une base de donnée OLAP : SAS, SQL-Server, ORACLE ou dans les gratuits : R et PostgreSQL. Il en existe bien sûr d’autres mais ce sont ceuxt là que je connais le mieux…

Cross Tab queries en Oracle 10

Cross-tab queries ou Pivot en Oracle 10g.

Je sais qu'Oracle 11 introduit la notion de Pivot table qui permette de créer des cross tab queris (affichage d'une réponse classée dans un tableau par valeur d'une variable catégorielle). Les adorateurs de SAS connaissent cela via la procedure PROC TABLE qui peut faire un tantinet plus qu'afficher un simple tableau, puisque l'on peut aussi calculer un test de Khi carré mais bon même si mon blog est dédié au stat là n'est pas le sujet du billet d'aujourd'hui.
Par contre les utilisateurs d'Oracle 10 ne peuvent pas bénéficier de cette avancée (bien fait...). Il faut donc trouver un palliatif.
Je prends un cas usuel, nous avons des sujets et ces sujets on différentes propriétés 3 en l'occurrences dans notre exemple. Chaque sujet possède une valeur par propriété. En gros j'ai deux tables, une table sujet et une table propriété.

Une jointure entre les tables nous donne le tableau suivant: V1 et V2 sont des variables numériques, V3 est une variable de type texte.

Sujets
Sujet DOB
1 15/01/2000
2 1/1/1992

Variables
Sujet Nom Valeur
1 V1 15
1 V2 2
1 V3 test
1 V1 17
1 V2 3
1 V3 subj2

La jointure des tables sujets - variables nous donnent ceci

Sujet DOB Nom Valeur
1 15/01/2000 V1 15
1 15/01/2000 V2 2
1 15/01/2000 V3 test
2 1/1/1992 V1 17
2 1/1/1992 V2 3
2 1/1/1992 V3 subj2

Cette approche n'est pas très commode et souffre même d'un certain gigantisme tabulatoire.

Une approche simplificatrice consiste à utiliser une combinaison judicieuse de MAX et de DECODE afin de produire un tableau de type
Sujet V1 V2 V3
1 15 2 test
2 17 3 subj2

Ce que permet le bout de SQL suivant:
select Sujet, MAX(DECODE(Nom, 'V1', VALEUR, NULL)) IV1,
MAX(DECODE(Nom, 'V2', Valeur, NULL)) IV2,
MAX(DECODE(Nom, 'V3', Valeur, NULL)) IV3
FROM Variables
group by Sujet
order by Sujet;

Explication rapide : le DECODE affiche
Select Sujet, DECODE(Nom, 'V1', VALEUR, NULL) IV1,
DECODE(Nom, 'V2', Valeur, NULL) IV2,
DECODE(Nom, 'V3', Valeur, NULL) IV3
FROM Variables

Nous donne un tableau constitué du sujet auquel on concatène chaque valeur de V1, V2, V3. si la variable vaut V1 et qu'elle a une valeur alors sa valeur est affichée, sinon on affiche NULL

Cela nous donne

Sujet V1 V2 V3
1 15 null null
1 null 2 null
1 null null test
2 17 null null
2 null 3 null
2 null null subj2

On remarque que par sujet on obtient une matrice diagonale. L'utilisation d'un regroupement sur MAX permet d'éliminer les valeurs nulles.

Vous remarquerez qu'Oracle a une drôle d'interprétation de la nullité. Nous savons en effet que null signifie que l'on ne connaît pas la valeur. Si Alain à un age de 30 ans et Philippe un age inconnu que vaut le MAX(Age) entre Alain et Philippe. La réponse est normalement je ne sais pas. Pour Oracle la réponse est Alain. C'est assez divinatoire (sans mauvais jeu de mot) comme approche mais ça marche pour notre exemple, nous ne nous en plaindrons donc pas !

Surrogate key in database design

L’usage de terme "surrogate" en informatique est parfois controversé.

Lorsque l’on conçoit une base de donnée, une des premières règles (imposées par les formes normales de conception) est de définir une clé primaire. Il s’agit d’un identifiant unique représentant un enregistrement donné et assurant qu’il ne peut être confondu avec une autre.
Les problèmes surviennent lors du choix de cette clé. Si je crée une table qui stocke les employés d’une entreprise, prendre le nom (ou le nom et le prénom) des personnes ne m’assurera pas forcémment l’unicité. Deux employés peuvent avoir les même noms et prénoms. D’où problème pour les reconnaître.
D’une manière général on introduit une colonne supplémentaire qui prend des valeurs entières uniques et qui identifie de manière distincte chaque enregistrement
ID Nom Prénom Age
1 Picard Jacques 30
2 Picard Jacques 24

En général les informaticiens appellent cette clé la clé primaire qui est par définition unique pour chaque enregistrement. (En pratique ça peut vite devenir différent). D'autres plus puristes diront qu'il s'agit d'une surrogate primary key (mais c'est plus rare).
En effet, une manière idiote de générer cette clé est qu’a chaque fois que j’enregistre un nouvel employé, je prends le maximum de la colonne ID et j’ajoute 1. “INSERT INTO tblEmployee (ID, Nom, PRENOM) VALUES (SELECT MAX(ID)+1, $Nom, $Prenom)”

Cela marchera bien si je suis l’unique utilisateur de la table employé, le problème est que le serveur de base de donnée sert plusieurs connections (utilisateurs connectés) en même temps. Que ce passe-t-il si deux personnes veulent faire une insertion dans la table employé au même moment.
U1 : veut insérer Alain Térieur qui a 36 ans. U1 Récupère le MAX(ID)+1=3 (ok)
U2 : veut insérer Jacques Cepte qui 28 ans. U2 Récupère le MAX(ID)+1 avant que U1 n’est eu le temps de faire l’insert=> Il obtient 3 aussi
U1 : fait son update et tout se passe bien
ID Nom Prénom Age
2 Picard Jacques 24
3 Térieur Alain 36
U2 : fait son update et il tente de mettre un second 3 dans le champ ID et là deux cas de figure, soit on a imposé une contrainte d’unicité sur le champ ID et heureusement la transaction explose, soit on ne l’a pas fait et on se retrouve avec clé primaire qui n’est plus unique (même si elle est censée l’être).
ID Nom Prénom Age
2 Picard Jacques 24
3 Térieur Alain 36
3 Cepte Jacques 28

Pour pallier à ce problème le vendeurs de bases de données ont crée des champs de type SERIAL ou AUTOINCREMENT, ce sont en général des entiers 32 bits. C'est-à-dire que ce n’est plus au programme de donner le numéro d’ID mais c’est le serveur de base de donnée qui s’en occupe. Ce type de clé primaire basée sur un tel champ est en général appelé par les informaticiens une surrogate key.
Avec une telle clé on se fiche de l’ID et pour insérer un utilisateur on indiquera simplement : INSERT INTO tblEmployee (Nom, Prenom) VALUES (‘Térieur’, ‘Alain’)
L’ID sera automatiquement incrémenté par la base de donnée dans ce cas de figure ma clé primaire sera toujours unique d’où le qualificatif de surrogate. Ceci est encore malheureusement de la théorie.
En pratique certains systèmes peuvent se connecter à une base de donnée y récupérer des données et puis s’en déconnecter. (Exemple : des moniteurs qui vont visiter un site clinique où il n’ont pas un accès directe à la base de donnée de la CRO, ils récupèrent les données sur leur PC, ajoute des nouvelles données lors de la visite, puis ils vont resyncrhoniser à leur retour avec la base de donnée d’origine). Même avec des clés primaires de type Autoincrement, l’utilisateur A aura de bonne chance d’avoir les mêmes valeurs de clé dans la base de donnée locale de son système que l’utilisateur B qui visitait le même site que lui au même moment.
Le système embarquer devra soit faire la réconciliation lui-même (ce qui est en pratique une très mauvaise idée)
Soit le système de base de donnée est sufisamment malin que pour réconcilier (ce n’est pas toujours gagné)
Soit on utilise un autre type de champ pour la clé primaire, pas mal de serveur de base de données aujourd’hui proposent des types GUID (Global Unique Identifier). Sa taille est de 16 octets (nettement plus lourd qu’un type integer) mais chaque valeurs générées est vraiment unique =>P(générer une valeur valeur existe)=0. Certains appelleront ce type de clé des surrogate keys alors qu'ils appelleront les autres type de champ clé des simple primary keys... Le principe consiste à définir clairement les conventions au sein d'une équipe.

Heritage de tables

En statistique nous sommes souvent confronté à trois grands types de variables:
- Binaire
- Catégorielle (qualitative)
- Quantitative (continue ou discrète).

Lorsque l'on doit stocker ce type de variable dans une base de donnée, il serait tentant d'utiliser trois tables différentes et de stocker le type de la variable dans une table de définition des variables:
-- Définition de la variable (métadonnée)
tblVariable
VarID int;
type int; (1-Binaire, 2-Categorielle, 3-Quantitative)
Code varchar(10)

-- valeur de la variable binaire
tblBinaire
ID int;
VarID int;
Valeur BIT;

-- valeur de la variable catégorielle
tblCategorielle
ID int;
VarID int;
Valeur varchar(10);

-- valeur de la variable quantitative
tblQuantitative
ID int;
VarID int;
Valeur DECIMAL 10.4;

Le problème de cette approche est qu'il est difficile de créer une intégrité référencielle entre la table Variable et les autres tables puisque le type de variable définira avec quelle table on doit faire le lien et donc les ID entre les différentes tables pourront se recouvrir. Il est donc difficile de définir une FOREIGN key entre 3 tables pouvant avoir des ID Identique et une table source...

Une approche plus simple pourrait considérer la table variable comme une table de base dont hérite les autres tables.

CREATE TABLE tblVariable
(
varid SERIAL PRIMARY KEY,
Code varchar(10);
)

CREATE TABLE tblVariableInst
(
id SERIAL PRIMARY KEY, -- idéalement autonumber
varid INT
FOREIGN KEY (varid) REFERENCES tblVariable(varid)
)

CREATE TABLE tblBinaire
(
id INT UNSIGNED PRIMARY KEY NOT NULL,
Valeur BIT,
FOREIGN KEY (id) REFERENCES tblVariableInst(varid)
)

CREATE TABLE tblCategorielle
(
id INT UNSIGNED PRIMARY KEY NOT NULL,
Valeur Varchar(10),
FOREIGN KEY (id) REFERENCES tblVariableInst(varid)
)

CREATE TABLE tblQuantitative
(
id INT UNSIGNED PRIMARY KEY NOT NULL,
Valeur NUMERIC 10.4;
FOREIGN KEY (id) REFERENCES tblVariableInst(varid)
)

Pour créer une nouvelle variable liée à une variable modèle 1234 par exemple.
@var_id=INSERT INTO tblVariableInst (1234) RETURN ID
INSERT INTO tblQuantitative (@var_id, 9.25)

Ces deux lignes devront être placée dans une transaction, pour éviter d'avoir des ID de VarInstance perdu en cas de problème.

L'avantage de cette approche est que l'intégrité référentielle est parfaitement maintenue entre la table de définition tblVariable (métadonnée) et les tables filles.

Rmk: En sql server on utilisera @@IDENTITY_SCOPE pour obtenir l'ID crée dans la table de base. Par contre en PostgreSQL il est possible de demander au INSERT de retourner le dernier ID crée. L'ID crée pour toutes les tables filles est stocké dans la table tblVariableInst (Variable instance) qui contient une clé étrangère sur la table de définition.

Comprendre la nullité

La valeur NULL (pour SQL) ne signifie pas 0, FALSE ou chaine vide. Il s'agit d'une erreur classique dans le développement.
NULL signifie indéterminé ou inconnu. Beaucoup de développeurs ont cette incompréhension de l'inconnu.

Exemple:

date
13/10/2010
NULL
13/12/2010

SELECT COUNT(DATE) where DATE<'13/12/2010' retournera 1 et non pas 2.
Car NULL représente une date inconnue et pas une date nulle (par ex 31/12/1899), comme la date est inconnue il est impossible de savoir si elle est plus petite ou plus grande que le 13/12/2010. Un autre exemple, si je dit que Jacques à 32 ans et que Pierre à un age à NULL. Dans ce cas de figure, cela signifie que l'age de Pierre n'a pas été entré dans le système. Si je demande si Jacques est plus vieuw que Pierre la réponse sera je ne sais pas.
=> Comparer un numérique avec un NULL donne un résultat indéterminé.
Si maintenant je ne connais pas l'age d'Alain. Comme savoir si Alain est plus vieux que Pierre, après tout ils ont peut-être le même age.
=> Comparer un NULL avec un NULL donne un résultat indéterminé.

Si j'ajoute l'age d'Alain à celui de Jacques le résultat peut aussi être n'importe quoi et ne sera certainement pas32
NULL{+,-,*,/}Nombre donne un NULL.
Même chose avec les concaténation de chaine de caractères. Concaténer un NULL avec une autre chaîne donne une chaîne indéterminée.

Cas particuliers dans les opérations booléennes:
NULL true=true
NULL & false=false

Toutes les autres opérations incluants des NULL donnent des NULL.

Cross Tab Queries en SQL Server 2005

Les cross tab queries sont souvent bien utiles pour présenter des synthèses de résultats par catégories par exemple (En statistique cela est très utile pour l'ANOVA ou les tests du khi carré sur des comptes variables binaires).

Certains informaticiens ont le réflexe de récupérer les données via un data set puis de les trier eux même sous formes de tableau, cette opération est souvent très couteuse alors que SQL Server 2005 nous offre la commande PIVOT.

Cette commande permet de créer un tableau de donnée en un clin d'oeil.

Voici un cas d'exemple

SELECT ID_Project, ID_FormModel, [1],[2],[3],[4],[5],[6] FROM
(select FM.ID_Project, FM.ID_FormModel, FI.FormStatus, 1 AS cnt from tblDMFormModel FM
LEFT OUTER JOIN tblDMForminstance FI on FI.ID_FormModel=FM.ID_FormModel) AS RawData
PIVOT (
SUM(cnt) FOR FormSTATUS IN ([1],[2],[3],[4],[5],[6])
) pvt
WHERE ID_Project=165

La première ligne formatte le tableau en affichant les deux colonnes non pivotables : l'id_project et le form model, les valeurs en gras indique les valeur du status des formes.

La seconde ligne construit le tableau des données brutes sur laquelle nous allons appliquer le pivot. (Le left outer join sert à récupérer tous les form modèles, même ceux qui n'ont pas de form instance). 1 indique que le form instance apparait une fois.
PIVOT fait pivoter les valeurs de rows autours de FormStatus en sommant les form instance pour obtenir les comptes.

Voilà, c'est peut-être pas super bien expliqué mais ça marche !

SQL-Server Load-Unload

Il existe une commande "DOS" bien pratique pour décharger le contenu d'une table dans un fichier texte. Ce déchargement se fait à une vitesse assez incroyable:

c:\bcp <dbname>..<tablename> out <c:\file.unl> -n -S <server-ip> -U <user> -P <password>

Ex:
c:\bcp txsoct..tblAudit out c:\test.unl -n -S localhost -U toto -P keep_the_secret

L'opération réciproque existe également et permet de recharger une table à une vitesse relativement grande.

c:\bcp <dbname>..<tablename> in <c:\file.unl> -n -S <server-ip> -U <user> -P <password>
Ceci peut s'avérer quelquefois bien utile !

SQL Server Transaction Log

Le transaction log est le fichier utilisé par SQL Server pour effectuer ses transactions. Ce fichier peut suivant le paramétrage gonfler d'un certain % de son volume chaque fois que ses limites sont atteintes. C'est pourquoi il est bon de temps à autres de faire un SHRINK de ce fichier.

Voici une méthode qui vous donne l'espace utilisé par le transaction log.

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMBFROM sys.database_files;

La taille restante avant que le fichier ne se mette à gonfler est donnée en MB.

USE DatabaseName
GO
DBCC SHRINKFILE(FileName, 1)
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(FileName, 1)
GO

Voici une méthode qui vous permet de réduire drastiquement la taille du transaction Log, ceci dit vous pouvez aussi le faire à partir de l'interface graphique.