QlikView : optimisation du modèle en étoile

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.

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 :

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 !

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 ».

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 :

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) :

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

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 :

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 :

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.

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.

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.

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 :

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 :

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 :

La table creuse a de meilleures performances.

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