Construire un chargement DELTA fiable entre une base de données et un QVD

1. Le DELTA porte sur un objet métier complet

Imaginons une table COMMANDES_DETAIL. Une commande peut être constituée de plusieurs lignes. L’unité de rafraîchissement choisie n’est pas la ligne physique mais l’objet métier ID_COMMANDE. Dès qu’une ligne de la commande évolue, la commande entière est considérée comme impactée.

Si la quantité de SOURIS passe de 3 à 4, le DELTA ne cherche pas à modifier uniquement cette ligne dans le QVD. Il marque CMD-1024 comme impactée. Lors de la reconstruction, toutes les anciennes lignes de cette commande sont retirées puis sa version courante complète est chargée depuis la base.

2. Le snapshot N-1 : la mémoire du traitement précédent

Pour savoir ce qui a changé, il faut disposer de deux états comparables. COMMANDES_DETAIL représente l’état courant N. COMMANDES_DETAIL_SNAP_N_1 représente l’état mémorisé à la fin du traitement précédent.

Le snapshot n’est donc pas une sauvegarde destinée au Qlik. Il sert de référence de comparaison côté base. Il doit rester inchangé pendant toute la phase de détection. Ce n’est qu’une fois la liste DELTA constituée qu’il est reconstruit à partir de l’état courant.

3. Détecter qu’un objet métier a évolué

Pour chaque ID_COMMANDE, on agrège les deux états. Le nombre de lignes permet de détecter un changement de structure. Les sommes d’indicateurs significatifs permettent de détecter des changements de valeurs. Les indicateurs doivent être choisis en fonction de la table : ici CA_HT, QUANTITE et MARGE.

WITH ACTUEL AS (
 SELECT ID_COMMANDE,
        COUNT(*) AS NB_LIGNES,
        SUM(NVL(CA_HT,0)) AS CA_HT,
        SUM(NVL(QUANTITE,0)) AS QUANTITE,
        SUM(NVL(MARGE,0)) AS MARGE
 FROM COMMANDES_DETAIL
 GROUP BY ID_COMMANDE
),
SNAP AS (
 SELECT ID_COMMANDE,
        COUNT(*) AS NB_LIGNES,
        SUM(NVL(CA_HT,0)) AS CA_HT,
        SUM(NVL(QUANTITE,0)) AS QUANTITE,
        SUM(NVL(MARGE,0)) AS MARGE
 FROM COMMANDES_DETAIL_SNAP_N_1
 GROUP BY ID_COMMANDE
)

Cette approche ne dépend pas obligatoirement d’un timestamp de modification fiable. Elle déduit l’évolution du contenu métier lui-même. En contrepartie, les indicateurs de comparaison doivent couvrir les changements que l’on souhaite réellement détecter.

4. FULL OUTER JOIN : construire la liste DELTA

Un FULL OUTER JOIN entre les deux états agrégés conserve les objets présents d’un côté ou de l’autre. Il couvre ainsi naturellement les trois cas : apparition d’un nouvel objet, disparition d’un objet et modification d’un objet présent dans les deux états.

INSERT INTO COMMANDES_DETAIL_DELTA (ID_COMMANDE)
SELECT COALESCE(a.ID_COMMANDE, s.ID_COMMANDE)
FROM ACTUEL a
FULL OUTER JOIN SNAP s
 ON s.ID_COMMANDE = a.ID_COMMANDE
WHERE NVL(a.NB_LIGNES,0) <> NVL(s.NB_LIGNES,0)
   OR NVL(a.CA_HT,0) <> NVL(s.CA_HT,0)
   OR NVL(a.QUANTITE,0) <> NVL(s.QUANTITE,0)
   OR NVL(a.MARGE,0) <> NVL(s.MARGE,0);

La table COMMANDES_DETAIL_DELTA ne contient pas nécessairement les données à injecter dans le QVD : elle contient avant tout la liste des ID_COMMANDE à remplacer.

Avancer ensuite le snapshot

DROP TABLE COMMANDES_DETAIL_SNAP_N_1 PURGE;

CREATE TABLE COMMANDES_DETAIL_SNAP_N_1 NOLOGGING AS
SELECT *
FROM COMMANDES_DETAIL;

À partir de cet instant, le snapshot correspond à N et deviendra le N-1 du prochain traitement.

5. Reconstruire le QVD côté Qlik

Le principe est volontairement simple : partir de l’ancien QVD, retirer tous les objets identifiés dans la liste DELTA, puis charger depuis la base la version courante complète de ces mêmes objets.

5.1 Charger la liste des objets impactés

COMMANDES_DETAIL_DELTA:
LOAD ID_COMMANDE AS ID_COMMANDE_DELTA;

SQL SELECT DISTINCT ID_COMMANDE
FROM COMMANDES_DETAIL_DELTA;

5.2 Charger l’ancien QVD en excluant ces objets

COMMANDES_DETAIL:
LOAD *
FROM [lib://QVD_RAW/COMMANDES_DETAIL.qvd] (qvd)
WHERE NOT Exists(ID_COMMANDE_DELTA, ID_COMMANDE_Id);

À ce stade, la table en mémoire contient tout l’ancien QVD sauf les commandes qui doivent être remplacées.

5.3 Charger depuis la base les versions courantes

CONCATENATE (COMMANDES_DETAIL)
LOAD
    ID_COMMANDE AS ID_COMMANDE_Id,
    PRODUIT,
    QUANTITE,
    CA_HT,
    MARGE;

SQL SELECT
    ID_COMMANDE,
    PRODUIT,
    QUANTITE,
    CA_HT,
    MARGE
FROM COMMANDES_DETAIL c
WHERE EXISTS (
    SELECT 1
    FROM COMMANDES_DETAIL_DELTA d
    WHERE d.ID_COMMANDE = c.ID_COMMANDE
);

5.4 Stocker le nouvel état

STORE COMMANDES_DETAIL
INTO [lib://QVD_RAW/COMMANDES_DETAIL.qvd] (qvd);

6. Pourquoi une suppression complète fonctionne sans DELETE Qlik

Supposons que CMD-1024 existait dans le snapshot mais ait totalement disparu de la table courante. Le FULL OUTER JOIN la détecte et son identifiant entre dans COMMANDES_DETAIL_DELTA.

Lors du chargement de l’ancien QVD, toutes les lignes de CMD-1024 sont exclues. Ensuite, le chargement SQL des objets impactés ne retourne aucune ligne pour cette commande puisqu’elle n’existe plus dans COMMANDES_DETAIL. Le nouveau QVD ne contient donc plus la commande.

7. Prévoir le mode FULL

Le DELTA suppose qu’un QVD précédent existe. Si le QVD a été supprimé, s’il doit être reconstruit ou si l’on souhaite volontairement repartir de la source complète, le script doit pouvoir basculer en FULL.

LET vQvdCommandes = 'lib://QVD_RAW/COMMANDES_DETAIL.qvd';

IF NOT IsNull(QvdCreateTime('$(vQvdCommandes)')) THEN
    SET vTypeChargement = 'DELTA';
ELSE
    SET vTypeChargement = 'FULL';
ENDIF

En FULL, COMMANDES_DETAIL est simplement chargée intégralement depuis la base puis stockée dans le QVD. Ce mode de reprise est important : un incrémental ne doit pas rendre impossible une reconstruction complète.

8. DELTA ou MERGE : deux besoins différents

Le DELTA est particulièrement intéressant lorsque l’objet métier constitue une unité de remplacement raisonnable. Si un objet ne contient que quelques lignes ou quelques dizaines de lignes, remplacer son état complet peut rester très efficace tout en conservant un script simple.

Il ne faut pas considérer le MERGE ligne à ligne comme une évolution nécessairement plus rapide du DELTA. Les performances dépendent du volume, de la granularité des objets, de la base et de la façon dont les changements sont détectés. Dans mes tests, le DELTA s’est d’ailleurs souvent montré plus rapide, mais ce constat reste dépendant du contexte.

Le MERGE répond surtout à un autre besoin : appliquer précisément les insertions, modifications et suppressions au niveau de chaque ligne. Cette granularité apporte davantage de contraintes : construction détaillée des mouvements côté base, gestion d’une séquence et utilisation du Partial Reload côté Qlik.