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

Jednoduchosť a jednoduchosť použitia, ktorá používateľom služby Power BI umožňuje rýchlo zhromažďovať údaje a vytvárať zaujímavé a výkonné zostavy na prijímanie inteligentných obchodných rozhodnutí, tiež umožňuje používateľom jednoducho vytvárať dotazy s nedostatočným výkonom. Táto situácia sa často vyskytuje, ak existujú dve tabuľky, ktoré súvisia v spôsobe, akým cudzí kľúč vytvára vzťah medzi tabuľkami SQL alebo zoznamami SharePointu. (V prípade záznamu nie je tento problém špecifický pre SQL alebo SharePoint a vyskytuje sa v mnohých scenároch extrakcie serverových údajov, najmä tam, kde je schéma plynulá a prispôsobiteľná.) Takisto nie je nič v poriadku s ukladaním údajov do samostatných tabuliek, ktoré zdieľajú spoločný kľúč – v skutočnosti ide o základný princíp návrhu databázy a normalizácie. Z toho však vyplýva, že vzťah je lepším spôsobom, ako vzťah rozbaliť.

Zoberme si nasledujúci príklad zoznamu zákazníkov SharePointu.

Nasledujúci zoznam umiestnení, na ktorý odkazuje.

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

Tieto údaje najvyššej úrovne sa zhromažďujú prostredníctvom jedného volania HTTP do rozhrania API SharePoint (ignoruje sa volanie metaúdajov), ktoré môžete zobraziť v ľubovoľnom webovom ladiaci nástroj.

Po rozbalení záznamu sa zobrazia polia spojené z sekundárnej tabuľky.

Pri rozbaľovaní súvisiacich riadkov z jednej tabuľky do druhej sa v predvolenom správaní služby Power BI vygeneruje volanie funkcie Table.ExpandTableColumn. Môžete to vidieť v poli vygenerovaného vzorca. Bohužiaľ, táto metóda generuje individuálne volanie do druhej tabuľky pre každý riadok v prvej tabuľke.

Zvýši sa tým počet volaní HTTP o jeden pre každý riadok v primárnom zozname. Nemusí sa to zdať ako veľa vo vyššie uvedenom príklade piatich alebo šiestich riadkov, ale v produkčných systémoch, kde zoznamy SharePointu dosahujú stovky tisíc riadkov, môže to spôsobiť výrazné zhoršenie výkonu.

Keď dotazy dorazia na toto kritické miesto, najlepšie je zabrániť správaniu volania na riadok pomocou klasického spojenia tabuľky. Tým sa zabezpečí, že na načítanie druhej tabuľky bude prebiehať len jedno volanie, a zvyšok rozšírenia sa môže vyskytnúť v pamäti pomocou spoločného kľúča medzi týmito dvoma tabuľkami. Rozdiel vo výkone môže byť v niektorých prípadoch obrovský.

Najskôr začnite s pôvodnou tabuľkou, poznačte si stĺpec, ktorý chcete rozbaliť, a zabezpečte, aby ste mali ID položky, aby ste sa s ňou mohli zhodovať. Cudzí kľúč sa zvyčajne nazýva podobne ako zobrazovaný názov stĺpca s pripojeným ID . V tomto príklade je to LocationId.

Po druhé, načítanie druhej tabuľky, uistite sa, že obsahuje ID, čo je cudzí kľúč. Kliknutím pravým tlačidlom myši na panel Dotazy vytvorte nový dotaz.

Nakoniec spojte dve tabuľky pomocou príslušných názvov stĺpcov, ktoré sa zhodujú. Toto pole zvyčajne nájdete tak, že najskôr rozbalíte stĺpec a potom vyhľadáte zodpovedajúce stĺpce v ukážke.

V tomto príklade môžete vidieť, že LocationId sa v primárnom zozname zhoduje s ID v sekundárnom zozname. Používateľské rozhranie ho premenuje tak, aby Location.Id , aby bol názov stĺpca jedinečný. Teraz tieto informácie použijeme na zlúčenie tabuliek.

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

Výberom jednotlivých tabuliek z rozbaľovacieho zoznamu zobrazíte ukážku dotazu.

Po výbere oboch tabuliek vyberte stĺpec, ktorý tabuliek spojí logicky (v tomto príklade je to LocationId z primárnej tabuľky a Id z sekundárnej tabuľky). V dialógovom okne sa zobrazí pokyn, koľko riadkov sa zhoduje s použitím tohto cudzieho kľúča. Pre tento typ údajov pravdepodobne budete chcieť použiť predvolený druh spojenia (ľavý vonkajší).

Vyberte položku OK a zobrazí sa nový dotaz, ktorý je výsledkom spojenia. Rozbalením záznamu teraz z toho nevyplývajú ďalšie volania na koncový server.

Výsledkom obnovenia týchto údajov budú len dve volania SharePointu – jedno pre primárny zoznam a druhé pre sekundárny zoznam. Pripojenie sa bude vykonávať v pamäti, čo výrazne zníži počet volaní do SharePointu.

Tento prístup možno použiť na ktorúkoľvek z dvoch tabuliek v režime PowerQuery, ktoré majú zodpovedajúci cudzí kľúč.