QlikView : optimisation du modèle en étoile

Yves Ducourneau

Le schéma en étoile par la méthode de la table creuse n'exige pas d'optimisation particulière. En revanche, la méthode de la table des clés requiert un peu d'attention.

Commentez

- Titre : QlikView : optimisation du modèle en étoile

- Auteur : Yves Ducourneau

- Parution : 14 février 2012

- Licence : Copyright ® 2012 Yves Ducourneau. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- 1. Introduction

- 2. La fonction autonumberhash128()

- 3. Suppression des clés inutiles

- 4. Pourquoi un tel gain ?

- 5. Comment procéder ?

- 6. Tables de faits fonctionnant en association

- 7. Remerciements

1 - Introduction

Fig. 1 - Rappel du principe du schéma en étoile avec table des clés.

Trois optimisations seront discutées ici

- La fonction autonumber() et sa concurrente, autonumberhash128().

- La suppression des clés inutiles dans la table des clés.

- L'utilisation d'une table creuse pour les tables de faits fonctionnant en association.

2 - La fonction autonumberhash128()

Le recours à la fonction autonumber() est recommandé pour construire des clés composites (comme celles de la table des clés justement), car elle permet à QlikView de manipuler un nombre au lieu d'une chaîne de caractères. C'est en soi-même une optimisation. Cependant, QlikView a introduit récemment une autre option : la fonction autonumberhash128(). Selon nos mesures, cette fonction se montre extrêmement rapide mais, hélas, produit un fichier qvw un peu plus gros. Le taux d'utilisation mémoire étant le plus critique, autonumber() reste donc un bon choix.

Voici le résultat de nos mesures en taille et en durée de chargement, sur un cas réel :

Fig. 2 - Taille du fichier qvw pour deux fonctions autonumber().

Fig. 3 - Durée de chargement pour deux fonctions autonumber().

Compte tenu du fait que le temps de chargement présenté ici correspond au total de l'application, et non à la seule table des clés, l'accélération de la fonction "hash" est tout à fait considérable. Quel dommage que le fichier soit plus gros !

3 - Suppression des clés inutiles

Lors de la construction de la table des clés, il est important de travailler granularité par granularité afin de ne pas produire de clés composites inutiles. On ne doit PAS réunir toutes les clés de dimension (toutes granularités confondues), puis calculer toutes les clés composites. Souvenons-nous en effet que dans une base vectorielle, il faut surveiller le nombre de valeurs distinctes, et nous verrons bientôt à quel point ceci est important.

Dans le schéma suivant, par exemple, on ne doit calculer la clé « dv » QUE pour les combinaisons de « d » et « v » existant dans la table des objectifs de vente (et de façon générale dans les tables de granularité « dv »). Une combinaison de « d » et « v » existant uniquement dans les actions commerciales (de clé « dvr ») ne doit pas avoir de clé « dv ».

Fig. 4 - Détail des clés composites dans un schéma en étoile avec table des clés.

C'est pourquoi il faut travailler granularité par granularité.
En supprimant les clés composites inutiles, nos mesures indiquent une baisse de 44 % en taille et de 23 % en durée de chargement :

Fig. 5 - Taille du fichier qvw avec ou sans clés composites inutiles.

Fig. 6 - Durée de chargement avec ou sans clés composites inutiles.

4 - Pourquoi un tel gain ?

De façon générale, et comme nous l'avons dit, dans une base vectorielle il convient de réduire le nombre de valeurs distinctes. Mais concernant la table des clés, toute négligence en la matière a des conséquences potentiellement dramatiques. La table des clés risque en effet d'être celle, dans la base, comportant le plus grand nombre de lignes, et c'est effectivement le cas dans l'application qui nous sert pour les mesures. La figure suivante montre le nombre de lignes par table, avec en rouge la table des clés (et en jaune une table de clés secondaire) :

Fig. 7 - Nombre de lignes par table, dans un schéma en étoile avec table des clés.

La table des clés est aussi celle comportant le plus de « grands » champs (champs possédant beaucoup de valeurs distinctes) :

Fig. 8 - Nombre de « grands » champs par table, dans un schéma en étoile avec table des clés.

La table des clés, si toutes les clés composites ont été calculées, est aussi celle qui a le plus grand nombre de valeurs distinctes :

Fig. 9 - Nombre de valeurs distinctes par table, dans un schéma en étoile avec table des clés.

Tous ces « plus » s'additionnant, le résultat en termes de durée de calcul est catastrophique, et la taille sur disque est à l'avenant :

Fig. 10 - Durée de chargement par table, dans un schéma en étoile avec table des clés.

La table des clés représente un poids considérable !
Voilà pourquoi il est très important de laisser à nul les clés composites non nécessaires.

5 - Comment procéder ?

Pour réaliser cette construction par granularité ne laissant que les valeurs utiles, on utilisera par exemple une table temporaire par granularité, récoltant les clés de dimension. Voici un exemple de script pour une seule granularité et une seule table de faits :

// Création d'une table temporaire pour la
// granularité « dv »
Tmp_dv:
LOAD * INLINE [
d, v
0, 0
];

// Chargement de la table de faits de granularité « dv »
Tmp_Objectifs_de_vente:
SQL SELECT d, v, quantite_obj from objectif_de_vente;

// Table de faits finale, avec uniquement la clé composite
Objectifs_de_vente:
LOAD autonumber(d & '-' & v) as dv,
quantite_obj
resident Tmp_Objectifs_de_vente;

// Collecte des clés
JOIN (Tmp_dv)
LOAD distinct d, v resident Tmp_Objectifs_de_vente;

DROP TABLE Tmp_Objectifs_de_vente;

// Une fois toutes les tables de faits et de clés chargées,
// on construit la table des clés finale
Cles:
LOAD
d,
v,
autonumber(d & '-' & v) as dv
resident Tmp_dv;

// Autres granularités
JOIN
LOAD
/*...*/;

// Nettoyage
DROP TABLE Tmp_dv, /*...*/;

On utilise une table temporaire pour chaque table de faits afin de retirer du schéma final les clés de dimension, qui ne servent qu'à la construction. Recopier une table dans une autre est très rapide dans QlikView.

6 - Tables de faits fonctionnant en association

Lorsque des tables de faits fonctionnent en association, c'est-à-dire se rapportent à une même entité, la première tentation est de faire de cette entité une dimension et de coordonner les tables de faits grâce à la clé de cette entité.
Procédons ainsi avec une action commerciale (l'entité) possédant trois sous-tables, des dépenses globales, des dépenses détaillées par invité et des remises d'échantillons de produits.

Fig. 11 - Mauvaise modélisation de tables de faits fonctionnant en association.

Les clés composites qui résultent d'un tel schéma sont, malheureusement, à l'usage exclusif des tables liées à l'entité 'Action commerciale', autrement dit nous augmentons le nombre de valeurs distinctes de la base. Il est possible, et préférable, de s'appuyer sur des clés existantes afin de se donner des chances de les partager. Une table creuse va permettre cela :

Fig. 12 - Bonne modélisation de tables de faits fonctionnant en association.

Tous les faits sont réunis dans la même table, dont l'entité est devenue une sous-table.
On dit que la table est « creuse » parce que chaque ligne ne comporte que les données relatives à son type, les autres champs étant nuls :

Fig. 13 - Détail de la table creuse.

Voici les performances avec et sans table creuse, mesurées dans une application réelle comportant deux entités complexes, avec deux sous-tables pour la première et trois pour la seconde :

Fig. 14 - Taille du fichier qvw avec ou sans table creuse.

Fig. 15- Durée de chargement avec ou sans table creuse.

La table creuse a de meilleures performances.

7 - Remerciements

Je tiens à remercier TomDuBouchon pour la mise en forme de cet article et ClaudeLELOUP pour sa relecture.