Optimalizácia doplnku Power Query pri rozbaľovaní stĺpcov tabuľky

Jednoduchosť a jednoduchosť použitia, ktorá umožňuje používateľom služby Power BI rýchlo zhromažďovať údaje a generovať zaujímavé a výkonné zostavy na inteligentné obchodné rozhodnutia, tiež umožňuje používateľom ľahko generovať slabo výkonné dotazy. Často sa to vyskytuje, keď existujú dve tabuľky, ktoré súvisia spôsobom, akým cudzí kľúč súvisí SQL tabuliek alebo SharePoint zoznamov. (Pre záznam, tento problém nie je špecifický pre SQL alebo SharePoint, a vyskytuje sa v mnohých backend dát extrakcie scenáre, najmä tam, kde schéma je plynulá a prispôsobiteľná.) Nie je tiež nič zlé na ukladaní údajov v samostatných tabuľkách, ktoré zdieľajú spoločný kľúč — v skutočnosti je to základný princíp návrhu a normalizácie databázy. Ale to znamená lepší spôsob, ako rozšíriť vzťah.

Uveďte nasledujúci príklad SharePoint zoznamu zákazníkov.

Primárny SharePoint zoznam zákazníkov.

A nasledujúci zoznam miest, na ktorý odkazuje.

Sekundárne SharePoint zoznam zákazníkov.

Pri prvom pripojení k zoznamu sa umiestnenie zobrazí ako záznam.

Záznamy primárnej polohy.

Tieto údaje najvyššej úrovne sa zhromažďujú prostredníctvom jediného hovoru HTTP na SharePoint API (ignorujúc hovor metaúdajov), ktorý môžete vidieť v ľubovoľnom webovom ladiacom nástroji.

Jeden HTTP hovor vo webovom ladiaci nástroji.

Keď rozbalíte záznam, zobrazia sa polia spojené zo sekundárnej tabuľky.

Polia spojené zo sekundárnej tabuľky.

Pri rozbalení súvisiacich riadkov z jednej tabuľky do druhej je predvoleným správaním služby Power BI generovať hovor na Table.ExpandTableColumn . Môžete to vidieť v poli generovaného vzorca. Bohužiaľ, táto metóda generuje individuálne volanie do druhej tabuľky pre každý riadok v prvej tabuľke.

Jednotlivé hovory na druhý stôl.

Tým sa zvýši počet volaní HTTP o jeden pre každý riadok v primárnom zozname. To sa nemusí zdať ako veľa vo vyššie uvedenom príklade piatich alebo šiestich riadkov, ale vo výrobných systémoch, kde SharePoint zoznamy dosahujú stovky tisíc riadkov, to môže spôsobiť značnú degradáciu skúseností.

Keď dotazy dosiahnu túto prekážku, najlepším zmiernením je vyhnúť sa správaniu hovoru na riadok pomocou klasického spojenia tabuľky. Tým sa zabezpečí, že na načítanie druhej tabuľky bude iba jeden hovor a zvyšok rozšírenia sa môže vyskytnúť v pamäti pomocou spoločného kľúča medzi týmito dvoma tabuľkami. Výkonnostný rozdiel môže byť v niektorých prípadoch obrovský.

Najprv začnite s pôvodnou tabuľkou, s nádychom stĺpca, ktorý chcete rozbaliť, a uistite sa, že máte identifikáciu položky, aby ste ju mohli priradiť. Cudzí kľúč je zvyčajne pomenovaný podobne ako zobrazovaný názov stĺpca s priloženou identifikáciou. V tomto príklade je to locationid.

Názov cudzieho kľúča.

Po druhé, načítať sekundárnu tabuľku, uistite sa, že obsahuje id, ktorý je cudzí kľúč. Kliknutím pravým tlačidlom myši na panel Dotazy vytvorte nový dotaz.

Načítať sekundárnu tabuľku cudzím kľúčom Id.

Nakoniec sa pripojte k dvom tabuľkách pomocou príslušných názvov stĺpcov, ktoré sa zhodujú. Toto pole môžete zvyčajne nájsť najprv rozbalením stĺpca a následným vyhľadením zodpovedajúcich stĺpcov v ukážke.

Zodpovedajúce stĺpce v ukážke.

V tomto príklade sa zobrazuje, že locationid v primárnom zozname zodpovedá identifikácii v sekundárnom zozname. Používateľské rozhranie ho premenuje na Location.Id, aby bol názov stĺpca jedinečný. Teraz použite tieto informácie na zlúčenie tabuliek.

Kliknutím pravým tlačidlom myši na panel dotazov a výberom položky Nový dotaz > kombinovať > zlúčiť dotazy ako nové, zobrazí sa priateľské používateľské rozhranie, ktoré vám pomôže kombinovať tieto dva dotazy.

Na kombinovanie dotazov použite zlúčiť dotazy ako nové.

Výberom každej tabuľky z rozbaľovacieho výberu zobrazíte ukážku dotazu.

Ukážka zlúčených dotazov.

Po výbere oboch tabuliek vyberte stĺpec, ktorý logicky spája tabuľky (v tomto príklade je to identifikácia polohy z primárnej tabuľky a identifikácia zo sekundárnej tabuľky). V dialógovom okne sa dozviete, koľko riadkov sa zhoduje s použitím cudzieho kľúča. Pravdepodobne budete chcieť použiť predvolený druh spojenia (ľavý vonkajší) pre tento typ údajov.

Zlúčiť ľavý vonkajší druh spojenia.

Vyberte tlačidlo OK a zobrazí sa nový dotaz, ktorý je výsledkom spojenia. Rozšírenie záznamu teraz neznamená ďalšie hovory na backend.

Výsledok ľavého vonkajšieho spojenia.

Obnovenie týchto údajov bude mať za následok iba dva hovory na SharePoint — jeden pre primárny zoznam a jeden pre sekundárny zoznam. Spojenie sa vykoná v pamäti, čím sa výrazne zníži počet hovorov na SharePoint.

Tento prístup je možné použiť pre všetky dve tabuľky v PowerQuery, ktoré majú zodpovedajúci cudzí kľúč.

Poznámka

SharePoint zoznamy používateľov a taxonómia sú tiež prístupné ako tabuľky a môžu byť spojené presne tak, ako je popísané vyššie, za predpokladu, že používateľ má primerané oprávnenia na prístup k týmto zoznamom.