Construire un chargement DELTA entre Oracle et un QVD

1Le 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.

2Le 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 à être rechargée dans Qlik. Il sert de référence de comparaison côté base. Il doit rester inchangé pendant toute la phase de détection. Sa mise à jour doit rester cohérente avec la version du QVD effectivement publiée : en cas d’échec du chargement, le prochain DELTA doit pouvoir retrouver les changements non publiés.

3Identifier les changements à partir du système source

La conception d’un DELTA commence par l’analyse des opérations possibles sur les données : INSERT, UPDATE et DELETE. Cette analyse s’appuie sur les règles de l’application, la documentation de l’éditeur et les équipes qui connaissent les traitements d’alimentation. Une table qui ne reçoit que des insertions ne se traite pas comme une table dont les enregistrements peuvent être modifiés ou supprimés.

Les tables alimentées exclusivement par INSERT, sans mise à jour ni suppression, constituent des candidates particulièrement favorables. Lorsque cette règle est garantie et qu’un critère fiable permet de retrouver les nouveaux enregistrements, il suffit de charger les ajouts : les objets déjà présents n’ont pas à être contrôlés à nouveau.

Lorsque le système source identifie directement les opérations réalisées — journal de modifications, mécanisme CDC, suivi des suppressions ou colonne de modification fiable — ces informations peuvent servir à constituer la liste des objets impactés. Elles doivent couvrir toutes les opérations susceptibles de modifier le contenu du QVD. Une date de modification qui ne renseigne pas les suppressions ne suffit pas à elle seule.

Comparer les agrégats par objet métier

Quand la source ne permet pas d’identifier exhaustivement les changements, on peut comparer l’état courant N au snapshot N-1. La première approche consiste à regrouper les données par ID_COMMANDE et à comparer le nombre de lignes ainsi que les mesures numériques importantes.

SELECT
    ID_COMMANDE,
    COUNT(*) AS NB_LIGNES,
    SUM(NVL(QUANTITE, 0)) AS TOTAL_QUANTITE,
    SUM(NVL(CA_HT, 0)) AS TOTAL_CA_HT,
    SUM(NVL(MARGE, 0)) AS TOTAL_MARGE
FROM COMMANDES_DETAIL
GROUP BY ID_COMMANDE;

Cette comparaison détecte les objets ajoutés ou supprimés, les variations du nombre de lignes et les écarts de totaux.

Quand COUNT(*) peut suffire

Dans certains systèmes ERP ou comptables, les corrections génèrent de nouveaux mouvements plutôt que de modifier les écritures existantes. Lorsque chaque évolution pertinente entraîne une variation du nombre de lignes à la maille métier, COUNT(*) peut suffire à identifier les changements.

Dans les autres cas, la comparaison des agrégats et, si nécessaire, une signature des dimensions permettent de renforcer la détection.

Une limite : les dimensions peuvent changer sans modifier les sommes

Supposons qu’une ligne de commande passe de la région Nord à Sud, sans changer ni quantité, ni chiffre d’affaires, ni marge. Les agrégats restent identiques, alors que le QVD devrait refléter la nouvelle région. Le même problème peut apparaître lorsqu’une catégorie de produit ou un canal de vente est modifié, ou lorsque des variations numériques se compensent.

Option : renforcer la détection avec JSON_OBJECT et ORA_HASH

Si les dimensions peuvent évoluer indépendamment des mesures, on peut ajouter une signature calculée à partir des seules colonnes dimensionnelles à surveiller. Dans notre exemple, nous retenons PRODUIT, REGION et CANAL_VENTE : ces noms sont fictifs et doivent être adaptés au modèle métier.

SUM(
    ORA_HASH(
        JSON_OBJECT(
            'produit'     VALUE PRODUIT,
            'region'      VALUE REGION,
            'canal_vente' VALUE CANAL_VENTE
            NULL ON NULL
            RETURNING VARCHAR2(4000)
        )
    )
) AS SIGNATURE_DIMENSIONS

JSON_OBJECT représente les trois valeurs de chaque ligne dans une structure explicite ; NULL ON NULL conserve les champs nuls dans cette représentation. ORA_HASH calcule une empreinte numérique par ligne. Enfin, SUM additionne ces empreintes pour produire une signature par commande, sans nécessiter de clé unique pour chaque ligne et en conservant la contribution des doublons.

La signature est comparée entre N et N-1 en complément des compteurs et des sommes. Elle ne remplace pas les contrôles numériques. La même sélection de colonnes et les mêmes règles de représentation doivent être appliquées aux deux états. Une clé métier composite peut également servir au regroupement ; le hachage de la seule clé ne suffirait pas à détecter un changement du contenu.

La limite de VARCHAR2(4000) utilisée dans JSON_OBJECT s’applique à chaque ligne : elle doit être vérifiée si les dimensions sont volumineuses.

ORA_HASH produit une empreinte sur 32 bits au maximum : deux contenus différents peuvent donc produire la même valeur. De plus, l’addition des empreintes ne garantit pas de détecter toutes les modifications : certaines variations peuvent se compenser, et des permutations de valeurs entre lignes peuvent passer inaperçues.

4FULL OUTER JOIN : construire la liste DELTA

Lorsque la source ne fournit pas une liste exhaustive des changements, les états N et N-1 sont comparés par objet métier. Le FULL OUTER JOIN couvre les apparitions, disparitions et modifications. L’exemple ci-dessous combine les agrégats de base et la signature des trois dimensions ; pour rester sur le contrôle de base, il suffit de retirer le calcul SIGNATURE_DIMENSIONS et sa comparaison finale.

INSERT INTO COMMANDES_DETAIL_DELTA (ID_COMMANDE)
WITH ETATS AS (
    SELECT 'N' AS ETAT,
           ID_COMMANDE, PRODUIT, REGION, CANAL_VENTE,
           QUANTITE, CA_HT, MARGE
    FROM COMMANDES_DETAIL
    UNION ALL
    SELECT 'N_1' AS ETAT,
           ID_COMMANDE, PRODUIT, REGION, CANAL_VENTE,
           QUANTITE, CA_HT, MARGE
    FROM COMMANDES_DETAIL_SNAP_N_1
),
AGREGATS AS (
    SELECT
        ETAT, ID_COMMANDE,
        COUNT(*) AS NB_LIGNES,
        SUM(NVL(QUANTITE, 0)) AS TOTAL_QUANTITE,
        SUM(NVL(CA_HT, 0)) AS TOTAL_CA_HT,
        SUM(NVL(MARGE, 0)) AS TOTAL_MARGE,
        -- Contrôle complémentaire facultatif :
        SUM(
            ORA_HASH(
                JSON_OBJECT(
                    'produit'     VALUE PRODUIT,
                    'region'      VALUE REGION,
                    'canal_vente' VALUE CANAL_VENTE
                    NULL ON NULL
                    RETURNING VARCHAR2(4000)
                )
            )
        ) AS SIGNATURE_DIMENSIONS
    FROM ETATS
    GROUP BY ETAT, ID_COMMANDE
),
ACTUEL AS (
    SELECT * FROM AGREGATS WHERE ETAT = 'N'
),
SNAP AS (
    SELECT * FROM AGREGATS WHERE ETAT = 'N_1'
)
SELECT COALESCE(a.ID_COMMANDE, s.ID_COMMANDE)
FROM ACTUEL a
FULL OUTER JOIN SNAP s
  ON s.ID_COMMANDE = a.ID_COMMANDE
WHERE a.ID_COMMANDE IS NULL
   OR s.ID_COMMANDE IS NULL
   OR DECODE(a.NB_LIGNES, s.NB_LIGNES, 0, 1) = 1
   OR DECODE(a.TOTAL_QUANTITE, s.TOTAL_QUANTITE, 0, 1) = 1
   OR DECODE(a.TOTAL_CA_HT, s.TOTAL_CA_HT, 0, 1) = 1
   OR DECODE(a.TOTAL_MARGE, s.TOTAL_MARGE, 0, 1) = 1
   -- À conserver uniquement si la signature est activée :
   OR DECODE(a.SIGNATURE_DIMENSIONS,
             s.SIGNATURE_DIMENSIONS, 0, 1) = 1;

ETATS réunit les deux états, puis AGREGATS calcule une ligne par commande et par état. ACTUEL et SNAP isolent les deux résultats avant la comparaison. Les tests IS NULL détectent les ajouts et suppressions ; DECODE compare les indicateurs en considérant deux NULL comme égaux. On suppose ici que ID_COMMANDE n’est jamais nul.

Cette démonstration compare tous les objets des deux états. Sur de gros volumes, lorsqu’un journal source identifie toutes les commandes concernées, mieux vaut exploiter ce ciblage que recalculer les contrôles sur toute la table.

La table COMMANDES_DETAIL_DELTA ne contient pas les données à injecter dans le QVD : elle fournit la liste des ID_COMMANDE à remplacer. Elle doit être préparée pour le lot courant afin de ne pas réutiliser les identifiants d’un lot précédent.

Préparer le nouveau snapshot, puis le valider après le QVD

Le nouvel état de référence est préparé séparément et associé au lot de chargement, par exemple avec un CREATE TABLE AS SELECT (CTAS), éventuellement fondé sur une lecture AS OF SCN. Le snapshot N-1 validé reste disponible tant que le nouveau QVD n’a pas été produit et contrôlé. Les données utilisées pour identifier les changements et celles extraites pour le QVD doivent correspondre au même état logique de la source. Si les données sources évoluent pendant le traitement, une fenêtre sans modification ou un état de lecture commun est nécessaire pour éviter que la détection et l’extraction Qlik ne portent sur des versions différentes. Dans Oracle, un SCN commun peut être utilisé avec des requêtes AS OF SCN, sous réserve notamment de disposer de l’historique UNDO nécessaire pendant toute la durée du traitement. Après validation, la référence bascule vers l’état utilisé pour construire le QVD ; en cas d’échec, elle reste inchangée.

5Reconstruire 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);

À ce stade, la table en mémoire contient tout l’ancien QVD sauf les commandes qui doivent être remplacées. Le champ ID_COMMANDE désigne la clé métier dans le QVD.

Le filtre WHERE NOT Exists(ID_COMMANDE_DELTA, ID_COMMANDE) empêche la lecture du QVD en mode optimisé. Il permet en revanche d’écarter toutes les lignes des commandes impactées, y compris lorsqu’une commande comporte plusieurs lignes. Ce coût local est à apprécier au regard du temps de traitement complet : dans notre benchmark, le DELTA reste nettement plus rapide que le FULL.

5.3 Charger depuis la base les versions courantes

CONCATENATE (COMMANDES_DETAIL)
LOAD
    ID_COMMANDE,
    ID_LIGNE,
    PRODUIT,
    REGION,
    CANAL_VENTE,
    QUANTITE,
    CA_HT,
    MARGE;

SQL SELECT
    ID_COMMANDE,
    ID_LIGNE,
    PRODUIT,
    REGION,
    CANAL_VENTE,
    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_PREP.qvd] (qvd);

Le QVD préparé est contrôlé avant de remplacer la version publiée. La validation du snapshot doit porter sur le même lot de données. L’orchestration doit conserver l’ancienne version et sa référence en cas d’échec, et ne considérer le nouveau lot comme validé qu’après publication cohérente du QVD et de sa référence.

6Comment le DELTA prend-il en compte les suppressions ?

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.

7Prévoir un mode FULL, ponctuel ou planifié

Le FULL permet de reconstruire intégralement le QVD depuis les données sources. Il peut être déclenché lorsque le QVD est absent, lorsqu’une incohérence impose une reconstruction, à la demande ou selon une périodicité définie. Il ne s’agit donc pas uniquement d’un mode de secours : un FULL régulier peut servir à réconcilier les données dans la durée.

Par exemple, on peut exécuter un DELTA du lundi au samedi et un FULL chaque dimanche, si la volumétrie et la fenêtre de traitement le permettent. La périodicité doit être adaptée au coût de la lecture complète en base et à la durée de reconstruction.

Le choix du mode peut être piloté par la planification du traitement et par un paramètre vForceFull permettant de déclencher une reconstruction à la demande. L’absence de QVD impose également une initialisation complète.

Après un FULL réussi, le snapshot de référence est réaligné sur les données publiées dans le QVD. Comme en DELTA, cette bascule n’intervient qu’après validation du chargement.

8DELTA ou MERGE : deux besoins différents

Le DELTA est particulièrement intéressant lorsque le système source permet d’identifier de façon fiable les objets à traiter et que 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 choix explicite de la stratégie de rechargement. Le préfixe MERGE fonctionne aussi lors d’un rechargement normal ; le Partial Reload est une possibilité, pas une obligation. Voir notre article consacré au MERGE.