Power BI:ssä voi hyödyntää SQL-kannoissa olevaa dataa kahdella eri tekniikalla. Tauluja, näkymiä tai SQL-kyselyiden tuloksia voi joko tuoda (Import) Power BI -tietomalliin tai SQL-kantaan voi ottaa ns. suoran yhteyden (DirectQuery).

Tuonti vai suora yhteys

Tässä jutussa käydään lyhyesti läpi molemmat tekniikat.

1 Tuonti

Kun data tuodaan (import) Power BI:hin, se käytännössä kopioidaan kokonaisuudessaan tietomalliin eli PBI Desktop -tiedostoon ja lopulta julkaisemisen yhteydessä Power BI -pilvipalveluun.

Jos tietomalli toteutetaan tuomalla, tietomalliin voi yhdistää tietoja myös muista tietolähteistä, kuten tekstitiedostoista, Exceleistä, syöttämällä tauluja jne.

Tuonti-tyyppiselle tietojoukolle määritetään pilvessä ajastettu päivitys, joten data ei ole täysin reaaliaikaista. Päivitys voi tapahtua Pro-lisenssillä korkeintaan 8 kertaa vuorokaudessa ja Premium-lisenssillä jopa 48 kertaa vuorokaudessa.

2 Suora yhteys

Kun SQL-kantaan otetaan ns. suoran yhteys (DirectQuery), data ei kopioidu tietomalliin, vaan sitä luetaan tietokannasta reaaliaikaisesti raporteille.

Jos tietomallitiedosto toteutetaan suoraa yhteyttä käyttämällä, kaikkia PBI Desktopin ominaisuuksia ei voi hyödyntää eikä malliin voi yhdistää dataa muista tietolähteistä eikä syöttämällä.

Datan tuonti SQL-kannasta

Kun ryhdyt tuomaan SQL dataa komennolla Home > Get Data > SQL Server (Aloitus > Nouda tiedot > SQL Server), määritä ensin SQL Serverin nimi ja valitse, että suoritat tuonnin (import).

Jos haluat tuoda datan SQL-kyselyn avulla, määritä myös tietokannan nimi ja kopioi SQL-kysely Advanced options (Lisäasetukset) -osioon. Jos tuot datan valitsemalla haluamasi taulut, tietokannan nimeä ei vielä tarvitse määrittää eli voit valita sekä tietokannan että haluamasi taulut seuraavassa vaiheessa eli Navigator (Siirtymistoiminto) -ikkunassa.

Jokaisesta valitsemastasi taulusta muodostuu yksi kysely ja kukin kysely latautuu tietomalliin yhtenä tauluna ellet yhdistele tauluja jollakin tavalla kyselyeditorissa. Kun siirryt Power BI:n kyselyeditoriin Edit (Muokkaa) -toiminnolla, kaikki kyselyeditorin muokkaustoiminnot ovat käytettävissä eli voit tehdä rajoituksetta datalle mitä tahansa kyselyeditorissa tarjolla olevia muunnoksia, suodatuksia jne.

Kun lopulta tuot datan kyselyeditorissa olevalla Home > Close & Apply (Sulje ja ota käyttöön) -komennolla, tiedot kopioituvat SQL-kannasta Power BI -tiedostoon. Voit käyttää tiedon mallinnuksessa rajoituksetta kaikkia Power BI Desktopin ominaisuuksia ja voit ladata sekä yhdistellä tiedostoon dataa dataa myös muista tietolähteistä.

Kun Power BI Desktopissa suoritetaan päivitys Home > Refresh (Aloitus > Päivitä), uudet tiedot tuodaan vanhojen tilalle.

Kun tietomalli julkaistaan Power BI -palveluun tietojoukoksi, data voidaan ajastaa päivittymään pilvessä automaattisesti, mikäli SQL Serverille on asennettu Power BI Gateway -palvelu ja kyseisen SQL kannan käyttö on lisätty Gateway-palveluun sallituksi tietolähteeksi.

Tällä hetkellä valtaosa Power BI -tietomalleista on toteutettu tuontitekniikalla, sillä usein malleihin halutaan yhdistää dataa eri tietolähteistä.

Tietomallin luonti suoran yhteyden avulla

Kun valitset käyttöön suoran yhteyden (DirectQuery) ja olet siirtynyt kyselyeditorin puolelle muokkaamaan dataa, kaikkia kyselyeditorin toimintoja ei voi käyttää.

Otetaanpa esimerkki:

Jos yrität yhdistää kahta eri saraketta yhdeksi joko käyttämällä Transform > Merge Columns (Muunna > Yhdistä sarakkeet) tai Add Column >  Merge Columns (Lisää sarake > Yhdistä sarakkeet) -komentoa, saat komennon suorittamisen jälkeen ilmoituksen, ettei komentoa tueta DirectQuery-tilassa.

Kiellettyjä toimintoja ei siis ole himmennetty pois käyttöliittymästä ja valitettavasti toimintojen käytettävyys selviää vain kokeilemalla. Onneksi monet kielletyistä muokkaustoimenpiteistä voi kiertää luomalla uusia kaavasarakkeita. Esimerkiksi kahden eri sarakkeen voi yhdistää DirectQuery-tilanteessa Add Column > Custom Column (Lisää sarake > Mukautettu sarake) -komennolla uuden sarakkeen ja käyttämällä esimerkiksi kaavaa =[LastName]& ” ” & [FirstName].

Kun valitset Home > Close & Apply (Aloitus > Sulje ja ota käyttöön), tiedot eivät kopioidu malliin vaan mallista on reaaliaikainen yhteys tietokantaan.

DirectQuery-tilassa  alhaalla tilarivillä lukee DirectQuery ja PBI Desktopin käyttöliittymässä on vain kaksi välilehteä.

Malliin voi lisätä uusia kaavasarakkeita sekä mittareita (measures), mutta samaan malliin ei pysty lataamaan dataa muista tietolähteistä tai syöttämällä Home > Enter Data (Aloitus > Anna tiedot) -komennolla.

Jos yrität ladata tietoja muista tietolähteistä, sinulle annetaan mahdollisuus muuntaa tiedosto tuontitilaan (Import mode). Tiedoston voi muuntaa tuontitilaan myös napsauttamalla PBI Desktopin oikeassa alakulmassa näkyvää DirectQuery-tilaindikaattoria.

Jotta DirectQuery-tyyppinen tietojoukko toimii pilvessä, SQL Serverille on pitänyt konfiguroida Gateway-palvelu. Jos tietolähteenä on Azure SQL Database tai Azure SQL Data Warehouse, Gateway-palvelua ei tarvita.

DirectQuery-tilan käyttöä rajoittaa eniten se, ettei samaan malliin voi yhdistellä muuta dataa. Toivottavasti DirectQueryä kehitetään siten, että datan yhdistely tulisi mahdolliseksi.

Kun otat suoran yhteyden SQL Serverin Analyysipalvelimen kuutioon tai tabular-malliin, yhteyttä kutsutaan Live Connection -yhteydeksi. Tulen kirjoittamaan SQL Server Analyysipalvelimesta ja Power BI:stä oman juttunsa.

Lisätietoja:

• DirectQuery-tietolähteet sekä DirectQuery-tilaan liittyviä rajoituksia:
  https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/
• Data Gateway-palvelun asentaminen ja konfigurointi:
  https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/
• Jos käytät tietolähteenä SQL-kantaa, tutustu myös Query Folding -ominaisuuteen, jotta osaat optimoida kyselyissäsi olevien vaiheiden järjestyksen: https://devinknightsql.com/2016/07/03/power-bi-checking-query-folding-with-view-native-query/