Power Queryn optimointi taulukon sarakkeita laajennettaessa

Yksinkertaisuuden ja helppouden ansiosta Power BI -käyttäjät voivat nopeasti kerätä tietoja ja luoda mielenkiintoisia ja tehokkaita raportteja älykkäiden liiketoimintapäätösten tekemiseksi. Lisäksi käyttäjät voivat helposti luoda huonosti toimivia kyselyitä. Näin käy usein, kun on olemassa kaksi taulukkoa, jotka liittyvät tapaan, jolla viiteavain liittää SQL-taulukoihin tai SharePoint-luetteloihin. (Tietueessa tämä ongelma ei koske ainoastaan SQL:ää tai SharePointia, ja sitä esiintyy monissa taustatietojen poimintatilanteissa, erityisesti silloin, kun rakenne on sulava ja mukautettava.) Tietojen tallentaminen erillisiin taulukoihin, joilla on yhteinen avain, ei myöskään ole mitään luontaisesti vialla – itse asiassa tämä on tietokannan suunnittelun ja normalisoinnin perusperiaatteet. Se on kuitenkin parempi tapa laajentaa suhdetta.

Katso seuraavaa esimerkkiä SharePoint-asiakasluettelosta.

Ja seuraava sijaintiluettelo, johon se viittaa.

Kun muodostat ensimmäisen kerran yhteyden luetteloon, sijainti näkyy tietueena.

Nämä ylimmän tason tiedot kerätään yksittäisen SharePoint-ohjelmointirajapinnan HTTP-kutsun kautta (metatietokutsu ohitetaan), mikä näkyy missä tahansa verkon virheenkorjauksessa.

Kun laajennat tietueen, näet kentät, jotka on liitetty toissijaisesta taulukosta.

Kun laajennat liittyviä rivejä taulukosta toiseen, Power BI:n oletustoiminta on kutsun luominen kohteeseen Table.ExpandTableColumn. Näet tämän luodussa kaavakentässä. Valitettavasti tämä menetelmä luo yksittäisen kutsun toiseen taulukkoon ensimmäisen taulukon jokaista riviä kohden.

Tämä lisää HTTP-kutsujen määrää yhdellä rivillä ensisijaisessa luettelossa. Tämä ei ehkä vaikuta kovin paljolta yllä olevasta viiden tai kuuden rivin esimerkistä, mutta tuotantojärjestelmissä, joissa SharePoint-luettelot saavuttavat satoja tuhansia rivejä, tämä voi johtaa merkittävään käyttökokemuksen heikkenemiseen.

Kun kyselyt saavuttavat tämän pullonkaulan, paras keino on välttää rivikohtainen kutsutoiminto käyttämällä perinteistä taulukkoliitosta. Tämä varmistaa, että toisen taulukon noutamiseen on vain yksi kutsu, ja loput laajennuksesta voivat ilmetä muistissa käyttämällä kahden taulukon välistä yhteistä avainta. Suorituskykyero voi olla joissakin tapauksissa valtava.

Aloita ensin alkuperäisestä taulukosta ja ota huomioon sarake, jonka haluat laajentaa, ja varmista, että sinulla on kohteen tunnus, jotta voit yhdistää sen. Viiteavain on yleensä nimetty samalla tavalla kuin sarakkeen näyttönimi, jonka kohteeseen on liitetty Tunnus . Tässä esimerkissä se on LocationId.

Lataa toissijainen taulukko ja varmista, että se sisältää tunnuksen, joka on viiteavain. Luo uusi kysely napsauttamalla Kyselyt-paneelia hiiren kakkospainikkeella.

Liitä lopuksi kaksi taulukkoa käyttäen vastaavia sarakkeiden nimiä, jotka vastaavat toisiaan. Löydät tämän kentän yleensä laajentamalla ensin sarakkeen ja etsimällä sitten vastaavat sarakkeet esikatselusta.

Tässä esimerkissä näet, että ensisijaisessa luettelossa LocationId vastaa toissijaisessa luettelossa Tunnusta . Käyttöliittymä nimeää tämän uudelleen nimellä Location.Id , jotta sarakkeen nimestä tulee yksilöivä. Seuraavaksi yhdistämme taulukot näiden tietojen avulla.

Kun napsautat hiiren kakkospainikkeella kyselypaneelia ja valitset Uusi kyselyiden>yhdistämiskyselyt>uusina, näet helpon käyttöliittymän, joka auttaa yhdistämään nämä kaksi kyselyä.

Valitse kukin taulukko avattavasta valikosta, niin näet kyselyn esikatselun.

Kun olet valinnut molemmat taulukot, valitse sarake, joka liittää taulukot loogisesti (tässä esimerkissä se on LocationId ensisijaisesta taulukosta ja Tunnus toissijaisesta taulukosta). Valintaikkunassa näytetään, kuinka monta riviä vastaavat kyseistä viiteavainta käyttämällä. Haluat todennäköisesti käyttää liitoksen oletuslajia (vasen ulkoliitos) tämäntyyppisille tiedoille.

Kun valitset OK , näkyviin tulee uusi kysely, joka on liitoksen tulos. Tietueen laajentaminen nyt ei tarkoita lisäkutsuja taustalle.

Näiden tietojen päivittäminen saa aikaan vain kaksi kutsua SharePointiin – yhden ensisijaiseen luetteloon ja toisen toissijaiseen luetteloon. Liitos suoritetaan muistissa, mikä vähentää merkittävästi SharePoint-kutsujen määrää.

Tätä lähestymistapaa voidaan käyttää kahdessa PowerQuery-taulukossa, joilla on vastaava viiteavain.

Muistiinpano

SharePoint-käyttäjäluettelot ja taksonomia ovat käytettävissä myös taulukoina, ja ne voidaan liittää täsmälleen yllä kuvatulla tavalla, jos käyttäjällä on riittävät oikeudet näiden luetteloiden käyttämiseen.