Club Power BI

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Problème de performance lors du développement d'une colonne

  • 1.  Problème de performance lors du développement d'une colonne

    Posted 8 days ago
    Bonjour à tout le Club Power BI !


    J'ai rejoint cette communauté récemment suite à une formation reçue et à mes premiers tests en solitaire sur Power BI / Power Query !
    Je rencontre un problème de performance que je n'arrive pas à résoudre.
    Voici ce que je souhaite faire.

    J'ai une première table A avec trois colonnes :
    - un ID
    - un ID client
    - une date
    Cette table contient 30 000 lignes.

    J'ai une seconde table B plus volumineuse qui contient notamment :
    - un ID client
    - une date de début
    - une date de fin
    - une catégorie
    Cette table contient 300 000 lignes.

    Je souhaite ajouter dans la table A la catégorie de la table B en joignant les deux tables sur l'ID client et sur la date de la table A qui doit être comprise entre la date de début et la date de fin de la table B.

    Pour faire cela, j'utilise le code suivant :
    - jointure des deux tables sur l'ID client
    #"Merge" = Table.NestedJoin(#"Table A", {"client ID"}, #"Table B", {"client ID"}, "new_column", JoinKind.LeftOuter)
    - ajout d'une colonne personnalisée pour faire la sélection sur l'intervalle de date
    #"Date range selection" = Table.AddColumn(#"Merge", "custom", each let mydate= [date] in Table.SelectRows([new_column],each [start date] <= mydate and [end date] >= mydate))
    - développement de la nouvelle colonne
    #"Result" = Table.ExpandTableColumn(#"Date range selection", "custom", {"start date", "end date", "category"}, {"start date", "end date", "category"})

    Je ne rencontre aucun soucis particulier sur mes étapes 1 et 2. Par contre j'ai un soucis lors de l'étape 3 car pour chaque ligne de la table A, Power Query recharge toute la table B à chaque fois (qui fait 38 Mo).
    J'ai fait un test en conservant seulement 5 lignes sur la table A et je vois Power Query charger 5 x 38 = 190 Mo de données, cela prend 5 minutes. Donc si je fais ça sur ma table A initiale de 30 000 lignes, j'en ai pour 30 000 x 38 = 1 140 Go de chargement de données et 500 heures...
    Est-il possible de dire à Power Query de conserver en mémoire la table B lorsqu'il l'a chargée au lieu de la recharger à chaque ligne de la table A ?

    Merci par avance pour votre aide !



    ------------------------------
    Vivien
    ------------------------------


  • 2.  RE: Problème de performance lors du développement d'une colonne

    Gold Contributor
    Posted 8 days ago
    Bonjour @Vivien G

    Est-ce que l'utilisation de Table.Buffer pourrait aider dans ce cas?
    https://www.thebiccountant.com/speedperformance-aspects/

    Sinon tu pourrais partager un ficher échantillon pour mieux évaluer et comprendre la situation.

    Cordialement​

    ------------------------------
    David Gerard
    Business Intelligence Consultant
    Épinal
    ------------------------------



  • 3.  RE: Problème de performance lors du développement d'une colonne

    Silver Contributor
    Posted 8 days ago
    Bonjour,

    C'est une jointure sur une inégalité et la façon dont vous le faites entraîne une boucle d'exécution (complexité linéaire O(n) si vous êtes curieux).
    J'avais traité ce sujet dans un article de blog : https://blog.djeepy1.net/2016/12/27/slowly-changing-dimension-avec-power-bi-et-jointure-sur-une-inegalite/
    Je fais d'abord un Expand de la table - qui se fait en une opération O(1). - puis j'applique la condition de jointure pour ne garder que les lignes qui correspondent.

    Le Table.Buffer peut aider à éviter de recharger la table B mais je n'ai jamais réussi à le garantir à tous les coups (le diable est dans les détails de l'exécution du Mashup Engine).

    ------------------------------
    Jean-Pierre Riehl
    Practice Manager Data & AI @AZEO
    Co-Founder Club Power BI
    MVP Data Platform
    ------------------------------



  • 4.  RE: Problème de performance lors du développement d'une colonne

    Gold Contributor
    Posted 7 days ago
    Bonjour

    Pour compléter, utiliser la fonction Table.AddKey, permet d'ajouter un indes au sens "SQL" du terme.

    Il faut l'ajouter les 2 tables, avant la jointure.
    Vous devriez gagner.

    Il faut utiliser la réponse de @Jean-Pierre Riehl et ajouter cet index.

    Tenez nous informé.​

    ------------------------------
    GAUDFROY Guillaume
    Microsoft Data PlatForm MVP
    MCSA Power BI & Excel
    ------------------------------



  • 5.  RE: Problème de performance lors du développement d'une colonne

    Posted 7 days ago
    Bonjour à tous,

    Et merci beaucoup pour vos retours rapides, c'est très appréciable !

    J'ai tenté d'utiliser Table.Buffer dans un premier temps mais j'ai eu du mal à voir où le mettre pour que cela ait un effet.
    J'ai ensuite essayé de faire le développement de la colonne avant de réaliser la sélection des lignes sur l'intervalle de dates et cela a résolu immédiatement mon problème puisque le table B ne se charge désormais qu'une seule fois lorsque j'exécute la requête.
    Je retiens l'idée de l'ajout d'une clé via Table.AddKey, cela n'a pas eu d'impact particulier en termes de performance sur cet exemple précis après l'inversion de mes deux dernières étapes mais j'imagine que cela doit être d'un grand intérêt pour des volumes de données plus importants.

    Un grand merci à tous pour votre contribution !

    ------------------------------
    Vivien
    ------------------------------