Clés génériques dans Qlik Sense : relier des données qui n’ont pas la même granularité

Certaines questions de modélisation n’ont pas de réponse propre dans un modèle en étoile classique : un budget saisi par catégorie face à des ventes par produit, une règle de sécurité du type « ce client OU ce produit », plusieurs tables de faits qui ne partagent pas les mêmes clés. La clé générique répond à ces trois cas avec un seul principe : une valeur de clé peut désigner un élément, un groupe d’éléments ou tous les éléments.

Cet article synthétise et adapte à Qlik Sense le Technical Brief « Generic Keys » d’Henric Cronström (Qlik, octobre 2012). Ses documents techniques restent des références : nous les relisons avec les outils d’aujourd’hui.

Le problème

Dans un modèle Qlik, une clé relie deux tables par valeur identique : le ProductID 3 des faits rejoint le ProductID 3 des produits, et rien d’autre. Ce fonctionnement bloque dans trois situations :

la sécurité en OU : un utilisateur voit tous les produits pour deux clients, et aussi tous les clients pour deux produits. La Section Access combine les champs de réduction par un ET ;
la granularité mixte : réel et budget sont concaténés dans une même table de faits, mais le budget est saisi à un niveau plus agrégé ;
les faits hétérogènes : ventes, visites et objectifs n’ont pas les mêmes clés, et les relier directement crée des références circulaires.

Attention à ne pas confondre avec la clé composite. Une clé composite concatène plusieurs clés pour en faire une seule. Une clé générique, souvent portée par un seul champ, contient des valeurs symboliques qui représentent plusieurs clés réelles. Henric Cronström le rappelle : la plupart des applications n’en ont pas besoin.

Le principe : une table de liens dimensionnelle

La table de faits ne pointe plus directement sur la dimension. Elle porte une clé générique, ici %ProductID, qui rejoint une table de liens dimensionnelle. Cette table traduit chaque symbole en produits réels : un symbole de produit vers un produit, un symbole de groupe vers chaque produit du groupe, et <ANY> vers tous les produits.

La table de liens traduit chaque symbole en produits réels.

La table de liens se construit dans l’éditeur de chargement de données, avec un LOAD par type de symbole :

ProductLink:
LOAD '<Product:' & ProductID & '>'    AS %ProductID, ProductGroupID, ProductID
RESIDENT Products;

CONCATENATE (ProductLink)
LOAD '<Group:' & ProductGroupID & '>' AS %ProductID, ProductGroupID, ProductID
RESIDENT Products;

CONCATENATE (ProductLink)
LOAD '<ANY>'                          AS %ProductID, ProductGroupID, ProductID
RESIDENT Products;

// The group key now lives in the link table only
DROP FIELD ProductGroupID FROM Products;

Deux règles en découlent. D’abord, la table des groupes se relie à la table de liens, pas à la table des produits : certaines lignes de faits concernent un groupe sans désigner de produit. Ensuite, chaque symbole doit indiquer à quel champ il se rapporte. Un groupe 1 et un produit 1 ne doivent jamais produire la même valeur. On l’obtient avec un préfixe (<Group:1>) ou avec une position dans une clé concaténée (1|<ANY>). Les deux notations se valent.

Le Data Manager ne propose pas ces chargements successifs. La visionneuse du modèle de données sert ensuite de contrôle : la table de liens doit s’intercaler entre les faits et les dimensions, sans clé synthétique.

Symboles inclusifs ou exclusifs

Un symbole de groupe peut être inclusif : il rejoint tous les éléments du groupe, comme ci-dessus. Il peut aussi être exclusif : il ne rejoint que le groupe, et aucun élément. Dans ce cas, <ANY> laisse place à <N/A>, ou à une valeur nulle côté ProductID.

Comparaison de deux tables ProductLink avec ProductID égal à 1 sélectionné : à gauche, en mode inclusif, la ligne 1|<ANY> rejoint le produit 1 et le budget du groupe reste associé à la sélection, ce qui convient aux autorisations ; à droite, en mode exclusif, la ligne 1|<N/A> n’a pas de ProductID et le budget du groupe est exclu, ce qui convient à un budget par groupe

Le même groupe, deux comportements à la sélection d’un produit.

Le choix dépend de l’usage. Pour une autorisation, le symbole doit être inclusif : une règle « tous les produits » doit bien rejoindre chaque produit, sinon la réduction écarte des données légitimes. Pour un budget par groupe, il doit être exclusif. À la sélection d’un produit, le budget du groupe n’a plus de sens : mieux vaut qu’il disparaisse.

Cas 1 : des autorisations en OU

Une réduction sur un seul champ, ou sur plusieurs champs combinés par ET, se fait sans clé générique. Le OU entre champs, lui, en demande une. Prenons une table de règles où DAVID voit les clients 5 et 7 pour tous les produits, ou les produits 6 et 7 pour tous les clients.

La table pont relie chaque transaction aux règles qui l’autorisent.

Première étape : éclater les règles avec SubField(). Deux appels dans le même LOAD produisent toutes les combinaisons produit-client.

AuthorizationTable:
LOAD USERKEY,
     If(Product = 'ANY', '<ANY>', Product) & '|' &
     If(Customer = 'ANY', '<ANY>', Customer) AS %AuthID;
LOAD Upper(USERID)               AS USERKEY,
     SubField(PRODUCT_ID, ';')  AS Product,
     SubField(CUSTOMER_ID, ';') AS Customer
FROM [lib://Security/Authorizations.xlsx]
(ooxml, embedded labels, table is Rules);

Deuxième étape : donner à chaque ligne de faits une clé AuthID au même format. Le client se trouve dans l’en-tête de commande ; ApplyMap() le récupère sans jointure.

OrderToCustomer:
MAPPING LOAD OrderID, CustomerID RESIDENT Orders;

OrderDetails:
LOAD *,
     ProductID & '|' & ApplyMap('OrderToCustomer', OrderID, 'NONE') AS AuthID
FROM [lib://Sales/OrderDetails.qvd] (qvd);

Troisième étape : la table pont. Générer toutes les combinaisons possibles la rendrait énorme. On part donc des transactions et on ne garde, par un LOAD précédent, que les clés présentes dans les règles.

AuthorizationBridge:
LOAD DISTINCT * WHERE Exists(%AuthID);
LOAD AuthID, AuthID AS %AuthID RESIDENT OrderDetails;

CONCATENATE (AuthorizationBridge)
LOAD DISTINCT * WHERE Exists(%AuthID);
LOAD AuthID, '<ANY>|' & ApplyMap('OrderToCustomer', OrderID, 'NONE') AS %AuthID RESIDENT OrderDetails;

CONCATENATE (AuthorizationBridge)
LOAD DISTINCT * WHERE Exists(%AuthID);
LOAD AuthID, ProductID & '|<ANY>' AS %AuthID RESIDENT OrderDetails;

CONCATENATE (AuthorizationBridge)
LOAD DISTINCT * WHERE Exists(%AuthID);
LOAD AuthID, '<ANY>|<ANY>' AS %AuthID RESIDENT OrderDetails;

Reste la Section Access, qui réduit sur USERKEY. En Qlik Sense, noms de champs et valeurs de la Section Access sont en majuscules, d’où le Upper() des deux côtés.

Section Access;
LOAD DISTINCT
    ACCESS,
    Upper(USERID) AS USERID,
    Upper(USERID) AS USERKEY
FROM [lib://Security/Authorizations.xlsx]
(ooxml, embedded labels, table is Rules);
Section Application;

Trois points propres à Qlik Sense. En client-managed, une ligne INTERNAL\SA_SCHEDULER en ADMIN reste nécessaire pour les rechargements planifiés. En SaaS, le format de USERID dépend du fournisseur d’identité ; la colonne USER.EMAIL peut le remplacer. Enfin, la réduction par QlikView Publisher, citée comme alternative dans le document d’origine, n’a pas d’équivalent direct : c’est la Section Access qui fait le travail.

Cas 2 : réel et budget dans une même table

Concaténer réel et budget dans une seule table de faits reste la méthode la plus simple pour les comparer. La difficulté tient aux niveaux de détail :

Axe	Réel	Budget
Géographie	`CustomerID`	`CountryID`
Produit	`ProductID`	`CategoryID`
Organisation	`SalesRepID`	`SalesUnitID`
Temps	jour	mois

Chaque axe reçoit une clé générique de la forme groupe|élément. Le réel la construit à partir de l’élément et récupère le groupe par ApplyMap(). Le budget met le symbole exclusif <N/A> à la place de l’élément.

LET vNA = '<N/A>';  // one single definition for the exclusive symbol

CustomerToCountry: MAPPING LOAD CustomerID, CountryID   RESIDENT Customers;
RepToSalesUnit:    MAPPING LOAD SalesRepID, SalesUnitID RESIDENT SalesReps;
ProductToCategory: MAPPING LOAD ProductID,  CategoryID  RESIDENT Products;

Facts:
LOAD Amount, OrderID, ShipperID,
     ApplyMap('CustomerToCountry', CustomerID, Null()) & '|' & CustomerID AS %CustomerID,
     ApplyMap('RepToSalesUnit', SalesRepID, Null())    & '|' & SalesRepID AS %SalesRepID,
     ApplyMap('ProductToCategory', ProductID, Null())  & '|' & ProductID  AS %ProductID,
     Num(MonthStart(OrderDate)) & '|' & Num(Floor(OrderDate))           AS %OrderDate,
     'Actual' AS Type
FROM [lib://Sales/ActualNumbers.qvd] (qvd);

CONCATENATE (Facts)
LOAD Amount,
     CountryID   & '|$(vNA)'                AS %CustomerID,
     SalesUnitID & '|$(vNA)'                AS %SalesRepID,
     CategoryID  & '|$(vNA)'                AS %ProductID,
     Num(MakeDate(Year, Month)) & '|$(vNA)' AS %OrderDate,
     'Budget' AS Type
FROM [lib://Sales/Budget.qvd] (qvd);

Chaque axe reçoit ensuite sa table de liens, construite comme plus haut mais avec des symboles exclusifs :

ProductLink:
LOAD CategoryID & '|' & ProductID AS %ProductID, CategoryID, ProductID
RESIDENT Products;

CONCATENATE (ProductLink)
LOAD DISTINCT CategoryID & '|$(vNA)' AS %ProductID, CategoryID, Null() AS ProductID
RESIDENT Products;

DROP FIELD CategoryID FROM Products;

Les tables de dimensions ne gardent pas la clé de leur groupe : elle vit dans la table de liens. Le champ Type sert de dimension dans une visualisation, ou de filtre dans une expression comme Sum({<Type={'Budget'}>} Amount).

Cas 3 : plusieurs tables de faits et une table de liens maîtresse

Quand les faits sont trop différents pour être concaténés, la clé générique se combine avec une table de liens maîtresse. L’exemple d’origine vient de l’industrie pharmaceutique : des ventes par conditionnement et par médecin, des visites de délégués, des objectifs mensuels.

Les faits ne portent qu’une clé maîtresse ; la table de liens fait le reste.

Chaque type de table a son rôle :

les dimensions portent tous les champs de sélection, sans clé de groupe ;
les tables de liens dimensionnelles utilisent des symboles inclusifs, pour que <ANY> rejoigne tous les éléments ;
les faits ne contiennent que la clé maîtresse et les mesures. Une partie de clé sans objet pour une table prend la valeur <ANY> ;
la table de liens maîtresse contient les clés génériques et la clé maîtresse, rien d’autre.

La clé maîtresse doit être définie à l’identique partout. Une table temporaire garantit cette cohérence :

SalesTmp:
LOAD *, %TerritoryID & '|' & %ProductID & '|' & %SalesRepID & '|' &
        %PhysicianID & '|' & %Date AS %MasterKey;
LOAD Sales,
     ApplyMap('PhysicianToTerritory', PhysicianID, Null())             AS %TerritoryID,
     ApplyMap('PackageToProduct', PackageID, Null()) & '|' & PackageID AS %ProductID,
     '<ANY>'                                                           AS %SalesRepID,
     PhysicianID                                                       AS %PhysicianID,
     Num(Month) & '|<ANY>'                                             AS %Date
FROM [lib://Pharma/Sales.qvd] (qvd);

Sales:
LOAD Sales, %MasterKey RESIDENT SalesTmp;

MasterLink:
LOAD DISTINCT %TerritoryID, %ProductID, %SalesRepID, %PhysicianID, %Date, %MasterKey
RESIDENT SalesTmp;

DROP TABLE SalesTmp;

Visites et objectifs suivent le même schéma et se concatènent à MasterLink, qui ne contient ainsi que les combinaisons existantes.

Quatre réglages pratiques

SET HidePrefix = '%'; masque aux utilisateurs tous les champs préfixés par %, donc les clés génériques.
Une variable par symbole (LET vAny = '<ANY>';, LET vNA = '<N/A>';) évite les écarts de saisie entre deux LOAD.
Les clés concaténées sont longues. AutoNumber() les remplace par des entiers sans perte d’information ; Qlik Sense propose aussi l’instruction AutoNumber en fin de script.
ApplyMap() récupère une clé absente de la table chargée sans passer par une jointure.

À retenir

Une clé, plusieurs sens. Une valeur de clé générique désigne un élément, un groupe ou tous les éléments.
Une table de liens entre faits et dimension. Elle traduit les symboles en valeurs réelles ; les groupes s’y rattachent, pas à la dimension.
Des symboles sans ambiguïté. Chaque symbole indique son champ, par préfixe ou par position.
Inclusif pour sécuriser, exclusif pour comparer. <ANY> pour les autorisations, <N/A> pour un budget agrégé.
Seulement les combinaisons utiles. Tables pont et tables de liens se chargent à partir des faits, avec DISTINCT et Exists().
À réserver aux vrais besoins. Dans la plupart des applications, un modèle en étoile simple suffit.

Essayer dans Qlik Sense

Ce script reprend les exemples de l’article avec les données des schémas, chargées en INLINE : il ne dépend d’aucun fichier ni d’aucune base. Collez-le dans l’éditeur de chargement de données d’une application vide, choisissez l’exemple avec la variable vExemple, puis rechargez.

// Les clés génériques dans Qlik Sense : script d'essai
// D'après « Generic Keys », Technical Brief d'Henric Cronström (Qlik, 2012), adapté par HeyData.
// Collez-le dans l'éditeur de chargement de données d'une application vide, choisissez
// l'exemple, rechargez, puis ouvrez la visionneuse du modèle de données.
SET vExemple = 1;   // 1 à 4

IF $(vExemple) = 1 THEN
    // Exemple 1 : une table de liens dimensionnelle (premier schéma)
    ProductGroups:
    LOAD * INLINE [
    ProductGroupID, ProductGroupName
    1, Bikes
    2, Accessories
    ];

    Products:
    LOAD * INLINE [
    ProductID, ProductName, ListPrice, ProductGroupID
    1, Road bike, 1200, 1
    2, City bike, 800, 1
    3, Helmet, 60, 2
    4, Bell, 15, 2
    ];

    ProductLink:
    LOAD '<Product:' & ProductID & '>'    AS %ProductID, ProductGroupID, ProductID
    RESIDENT Products;

    CONCATENATE (ProductLink)
    LOAD '<Group:' & ProductGroupID & '>' AS %ProductID, ProductGroupID, ProductID
    RESIDENT Products;

    CONCATENATE (ProductLink)
    LOAD '<ANY>'                          AS %ProductID, ProductGroupID, ProductID
    RESIDENT Products;

    DROP FIELD ProductGroupID FROM Products;

    Facts:
    LOAD * INLINE [
    %ProductID, Amount
    <Product:3>, 60
    <Group:1>, 2000
    <ANY>, 500
    ];
    // Sélectionnez ProductID = 3 : <Product:3> et <ANY> restent associées ; <Group:1> est exclue,
    // car le produit 3 appartient au groupe 2.
    // Sélectionnez ProductID = 1 : seules <Group:1> et <ANY> le restent.
END IF

IF $(vExemple) = 2 THEN
    // Exemple 2 : symbole inclusif ou exclusif (deuxième schéma)
    SET vSymbole = '<N/A>';   // remplacez par '<ANY>' pour la version inclusive

    Products:
    LOAD * INLINE [
    ProductID, ProductName, GroupID
    1, Road bike, 1
    2, City bike, 1
    ];

    ProductLink:
    LOAD GroupID & '|' & ProductID AS %ProductID, GroupID, ProductID
    RESIDENT Products;

    IF '$(vSymbole)' = '<ANY>' THEN
        CONCATENATE (ProductLink)
        LOAD GroupID & '|<ANY>' AS %ProductID, GroupID, ProductID
        RESIDENT Products;
    ELSE
        CONCATENATE (ProductLink)
        LOAD DISTINCT GroupID & '|<N/A>' AS %ProductID, GroupID, Null() AS ProductID
        RESIDENT Products;
    END IF

    DROP FIELD GroupID FROM Products;

    Facts:
    LOAD %ProductID, Type, Amount INLINE [
    %ProductID, Type, Amount
    1|1, Actual, 110
    1|2, Actual, 85
    1|$(vSymbole), Budget, 200
    ];
    // Sélectionnez ProductID = 1 : avec <N/A>, le budget du groupe est exclu ;
    // avec <ANY>, il reste associé.
END IF

IF $(vExemple) = 3 THEN
    // Exemple 3 : autorisations en OU (troisième schéma), sans Section Access
    AuthorizationTable:
    LOAD USERKEY,
         If(Product = 'ANY', '<ANY>', Product) & '|' &
         If(Customer = 'ANY', '<ANY>', Customer) AS %AuthID;
    LOAD Upper(USERID)               AS USERKEY,
         SubField(PRODUCT_ID, ';')  AS Product,
         SubField(CUSTOMER_ID, ';') AS Customer
    INLINE [
    USERID, PRODUCT_ID, CUSTOMER_ID
    ALAN, ANY, 1;2;3
    DAVID, ANY, 5;7
    DAVID, 6;7, ANY
    ];

    Orders:
    LOAD * INLINE [
    OrderID, CustomerID
    1, 5
    2, 9
    3, 7
    ];

    OrderToCustomer:
    MAPPING LOAD OrderID, CustomerID RESIDENT Orders;

    OrderDetails:
    LOAD *,
         ProductID & '|' & ApplyMap('OrderToCustomer', OrderID, 'NONE') AS AuthID
    INLINE [
    OrderID, ProductID, Amount
    1, 2, 100
    1, 6, 40
    2, 6, 75
    2, 3, 30
    3, 7, 55
    ];

    AuthorizationBridge:
    LOAD DISTINCT * WHERE Exists(%AuthID);
    LOAD AuthID, AuthID AS %AuthID RESIDENT OrderDetails;

    CONCATENATE (AuthorizationBridge)
    LOAD DISTINCT * WHERE Exists(%AuthID);
    LOAD AuthID, '<ANY>|' & ApplyMap('OrderToCustomer', OrderID, 'NONE') AS %AuthID RESIDENT OrderDetails;

    CONCATENATE (AuthorizationBridge)
    LOAD DISTINCT * WHERE Exists(%AuthID);
    LOAD AuthID, ProductID & '|<ANY>' AS %AuthID RESIDENT OrderDetails;

    CONCATENATE (AuthorizationBridge)
    LOAD DISTINCT * WHERE Exists(%AuthID);
    LOAD AuthID, '<ANY>|<ANY>' AS %AuthID RESIDENT OrderDetails;
    // Sélectionnez USERKEY = DAVID : la ligne 3|9 devient exclue, comme dans le schéma.
    // La Section Access de l'article ferait de cette sélection une réduction des données.
    // Ajoutez-la seulement sur une copie, avec une ligne ADMIN pour votre propre compte.
END IF

IF $(vExemple) = 4 THEN
    // Exemple 4 : deux tables de faits autour d'une table de liens maîtresse (quatrième schéma)
    PhysicianToTerritory: MAPPING LOAD * INLINE [
    PhysicianID, TerritoryID
    D210, T04
    D211, T05
    ];
    PackageToProduct: MAPPING LOAD * INLINE [
    PackageID, ProductID
    K31, P12
    K32, P13
    ];

    SalesTmp:
    LOAD *, %TerritoryID & '|' & %ProductID & '|' & %SalesRepID & '|' &
            %PhysicianID & '|' & %Date AS %MasterKey;
    LOAD Sales,
         ApplyMap('PhysicianToTerritory', PhysicianID, Null())             AS %TerritoryID,
         ApplyMap('PackageToProduct', PackageID, Null()) & '|' & PackageID AS %ProductID,
         '<ANY>'                                                           AS %SalesRepID,
         PhysicianID                                                       AS %PhysicianID,
         Num(MakeDate(Year, Month)) & '|<ANY>'                             AS %Date
    INLINE [
    PhysicianID, PackageID, Year, Month, Sales
    D210, K31, 2026, 1, 1200
    D211, K32, 2026, 1, 800
    ];

    TargetsTmp:
    LOAD *, %TerritoryID & '|' & %ProductID & '|' & %SalesRepID & '|' &
            %PhysicianID & '|' & %Date AS %MasterKey;
    LOAD Target,
         TerritoryID                           AS %TerritoryID,
         ProductID & '|<ANY>'                  AS %ProductID,
         '<ANY>'                               AS %SalesRepID,
         '<ANY>'                               AS %PhysicianID,
         Num(MakeDate(Year, Month)) & '|<ANY>' AS %Date
    INLINE [
    TerritoryID, ProductID, Year, Month, Target
    T04, P12, 2026, 1, 1000
    T05, P13, 2026, 1, 900
    ];

    Sales:   LOAD Sales, %MasterKey RESIDENT SalesTmp;
    Targets: LOAD Target, %MasterKey RESIDENT TargetsTmp;

    MasterLink:
    LOAD DISTINCT %TerritoryID, %ProductID, %SalesRepID, %PhysicianID, %Date, %MasterKey
    RESIDENT SalesTmp;
    CONCATENATE (MasterLink)
    LOAD DISTINCT %TerritoryID, %ProductID, %SalesRepID, %PhysicianID, %Date, %MasterKey
    RESIDENT TargetsTmp;

    DROP TABLES SalesTmp, TargetsTmp;
    // La clé de la vente D210 vaut T04|P12|K31|<ANY>|D210|46023|<ANY>, comme dans le schéma.
    // Les tables de liens dimensionnelles relieraient ensuite chaque %… à sa dimension.
END IF

Source

« Generic Keys », QlikView Technical Brief, Henric Cronström (alias « hic » sur Qlik Community), 2 octobre 2012, publié sur Qlik Community dans la rubrique QlikView Documents. Tout le mérite du contenu original lui revient ; l’adaptation à Qlik Sense et ses éventuelles erreurs sont de HeyData.

Cet article fait partie de notre relecture des Technical Briefs Qlik : découvrir la série et ses autres articles.