Power Query: Datan lataaminen Power Pivotiin

Power Query: Datan lataaminen Power Pivotiin
<- Kyselyoppaan sisällysluettelo
<- Edellinen juttu Seuraava juttu ->

Tämä kyselyoppaan juttu liittyy poikkeuksellisesti vain Excelin Power Queryyn, ei Power BI Desktop -ohjelmaan.

Ohjeessa oletetaan, että sinulla on kokemusta Power Pivotista ja olet ladannut tai linkittänyt tietoja tietomalliin Power Pivotin omilla komennoilla.

Jutussa kerrotaan miten suoritat latauksen Power Queryllä. Lisäksi artikkelissa annetaan hyviä ohjeita, miten vältät Power Queryn ja Power Pivotin yhteiskäyttöön liittyvät ongelmat.

Power Pivotin ja Power Queryn yhteensopivuus

Jos olet laatinut tietomalleja ja käyttänyt Power Pivotin kanssa Power Queryä, olet mahdollisesti törmännytkin yhteensopivuusongelmiin, jotka voivat pahimmillaan aiheuttaa taulun joutumisen ns. read-only tilaan, jonka jälkeen taulussa olevaa dataa ei enää pysty päivittämään.

could not be refreshed

Mikäli taulu joutuu read-only –tilaan, se pitää Excel 2013 versiossa poistaa tietomallista ja ladata uudelleen. Tämä ei ole hankala operaatio yksinkertaisessa tai vasta aluillaan olevassa tietomallissa. Mutta taulun poistaminen tietomallista, johon on luotu runsaasti laskettuja sarakkeita ja kenttiä sekä erilaisia pivot-taulukoita ja –kaavioita, voi olla työläs operaatio.

Onneksi edellä mainittu read-only ongelma on mahdollista välttää. Olen itse toteuttanut lukuisia hyvin toimivia tietomalleja ilman minkäänlaisia read-only ongelmia, kun olen noudattanut tässä artikkelissa mainittuja ohjeita.

Excel 2016 -versiossa ei ole lukkiutumisongelmaa, sillä Excel estää sellaisten toimintojen käytön, jotka voivat aiheuttaa read-only -tilanteen.

5 ohjetta ongelmien välttämiseen

Ohje 1

Käytä tietomallissa vain yhtä lataustekniikkaa. Lataa tiedot joko Power Pivotin tuonti- ja linkitystoiminnolla TAI Power Query -kyselyinä. Älä käytä molempia sekaisin samassa tietomallissa.

Tietyissä tilanteissa sinun ei tarvitse pohtia, kumpaa käyttäisit. Esimerkiksi omilla palvelimilla oleviin SharePoint-ympäristöihin tallennettujen Power Pivot -työkirjojen päivitys voidaan ajastaa vain, jos tiedot on tuotu Power Pivotin omilla komennoilla. Power Query –kyselyiden ajastettu päivitys toimii tällä hetkellä vasta Power BI for Office 365 –pilvipalvelussa. Molemmissa ympäristöissä ajastettu päivitys toimii vain tietyille tietolähteille, kuten SQL-kannoille. (Pari viikkoa sitten on julkaistu ensimmäinen kolmannen osapuolen tuote, jolla voi suorittaa monipuolisemmin automaattisia päivityksiä. Lisätietoja: http://www.powerpivotpro.com/2015/02/introducing-power-update/).

Entä jos olet ladannut tauluja Power Pivotilla ja huomaat tarvitsevasi yksittäisen taulun tuonnissa Power Queryn monipuolisia muokkausoperaatioita? Tällöin suosittelen käyttämään Power Queryä erikseen ennen Power Pivotiin lataamista. Tuota siis Power Queryllä muokattu Excel-luettelo, jonka lataat Power Pivotin tuontikomennolla tietomalliin.

[Huomio 10/2015: En ole vielä testannut riittävän kattavasti, voiko Excel 2016 -versiossa tulla ongelmia, jos tietomalliin ladataan tietoja sekä Power Pivotin komennoilla että Power Queryn avulla.]

Ohje 2

Jos käytät Power Queryä, varmistu ennen kyselyn lataamista tietomalliin, että kyselyn nimi on sellainen, jota haluat käyttää taulun nimenä tietomallissa ja että nimen muuttamiselle ei ole myöhemmin tarvetta.

Ohje 3

Jos käytät Power Queryä tiedon lataamisessa, tee kaikki ”rakenteelliset” muutokset tauluihin vain Power Queryssä!

Älä käytä seuraavia toimintoja Power Pivotin puolella lainkaan:

  • taulukon ominaisuuksien muuttaminen (table properties),
  • taulukon poistaminen,
  • taulukon nimen vaihtaminen (pyri välttämään tätä myös Power Queryn puolella, sillä joudut korjaamaan kaavat, taulujen väliset yhteydet ja pivotit, joissa kyseistä taulua on käytetty),
  • sarakkeen poistaminen, nimeäminen tai sarakkeen tietotyypin vaihtaminen.

Voit kyllä lisätä Power Pivotissa laskettuja sarakkeita ja kenttiä (mittareita), muotoilla sarakkeita ja määrittää esimerkiksi lajittelujärjestyksiä ja lisätä hierarkioita.

Excel 2016 -versiossa et enää pysty vahingossa tekemään muutoksia Power Pivotissa, jos olet ladannut tiedot Power Queryllä. Muokkausyritykset antavat alla olevan ilmoituksen.

ilmoitus

Ohje 4

Testaa säännöllisesti, että datan päivitys toimii. Jutun lopussa linkkejä ohjeisiin, joista on hyötyä, jos datan päivitys ei enää onnistu.

Ohje 5

Tallenna tiedosto usein ja tee siitä mallia rakentaessasi päivittäin myös muutamia varmuuskopioita.

Tämä ohje ei niinkään liity yhteiskäyttöön, sillä Power Pivot kaatuu silloin tällöin, käytitpä Power Queryä tai et. Ja joissakin harvinaisissa poikkeuksen poikkeustilanteissa kaatuminen näyttää aiheuttavan tiedoston korruptoitumisen. Olen itse joutunut muutaman kerran palaamaan aiempaan varmuuskopioversioon, kun tietomalli on korruptoitunut Excelin kaatumisen seurauksena.

Valmiin mallin käyttäminen aiheuttaa harvemmin kaatumistilanteita. Virhetilanteet liittyvät yleensä mallin muokkaamiseen ja luontiin liittyviin operaatioihin ja tapahtuvat useammin 32-bittisessä Excelissä.

Miksi käyttäisit Power Queryä lataamisessa?

Jos Power Queryllä voi ennen tietomalliin lataamista tuottaa Excel-muotoiset aineistot ja ladata lopputulokset tietomalliin Power Pivotin omilla komennoilla, miksi ylipäätään lataisit tietoa suoraan Power Querystä, jos se voi mahdollisesti aiheuttaa ongelmia?

Syy 1

Power Query tuottaa tuloksena Excel-taulukoita, joiden rivimäärä rajoittuu noin miljoonaan riviin. Mikäli Power Query –kysely tuottaa enemmän rivejä, ne pitää ladata Power Querystä suoraan tietomalliin.

Syy 2

Power Pivotin omiin tuontikomentoihin liittyy yksi merkittävä rajoitus: tietolähteen tyyppiä ei voi muuttaa. Jos olet alun perin tuonut tiedot tekstitiedostosta tai Excel-tiedostosta, et pysty vaihtamaan tietolähteeksi tietokantaa. Tai päinvastoin. Tietolähteen tyypin vaihtaminen vaatii kyseisen taulun poistamisen tietomallista ja uudelleen latauksen. Isommissa tietomalleissa taulun poistaminen voi aiheuttaa suuren työn.

Käyttämällä Power Queryä saat toteutettua Power Pivot tietomallin ja tietolähteen väliin uuden kerroksen. Power Query -kyselyssä voi kopioida M-kielisen skriptin tilalle toisen skriptin eli pystyt käytännössä vaihtamaan tietolähteen Power Pivotin sitä huomaamatta. Laskettuja sarakkeita, kenttiä ja pivot-taulukoita ei tarvitse luoda uudelleen tai muokata kunhan huolehdit, että muutettu kysely lataa Power Pivotiin saman nimiset sarakkeet kuin alkuperäisessä kyselyssä oli.

tietolähte power query power pivot

Lataaminen eri versoissa

Lataaminen Excel 2013 ja 2016 –version Power Pivotiin

Excel 2013 ja 2016 –versiossa latauksen voi suorittaa helposti Power Querystä komennolla Home > Close & Load > Close & Load To (Aloitus > Sulje ja lataa > Sulje ja lataa kohteeseen) -komennolla eikä se vaadi lisäoperaatioita Power Pivotin puolella.

close & load to

Ladatun kyselyn voi päivittää joko Power Pivotin puolelta tai Excelissä kyselypaneelista. Jos teet kyselyyn muutoksia Power Queryssä, sinun ei yleensä tarvitse tehdä mitään erityisiä toimenpiteitä Power Queryn puolella kunhan et vaihda kyselyn nimeä tai esimerkiksi sellaisten kenttien nimiä, jotka vaikuttavat taulujen välisiin yhteyksiin, laskettuihin sarakkeisiin, mittareihin tai pivot-taulukoihin.

Lataaminen Excel 2010 –version Power Pivotiin

Power Query –kyselyn lataaminen 2010-version tietomalliin on työläämpi operaatio ja vaatii erityistä huolellisuutta, jotta kaikki toimii kunnolla. Suosittelen välttämään Excel 2010 & Power Query kombinaatiota!

Varmistaudu ennen kyselyn latausta seuraavista ja mieti vielä kolmannen kerran, sillä kaikki muutokset jälkeenpäin aiheuttavat ylimääräistä työtä:

  • kyselyn nimi on sellainen, jota haluat käyttää taulun nimenä Power Pivotin tietomallissa,
  • sarakkeiden nimet ovat kunnossa,
  • sarakkeiden tietotyypit ovat varmasti oikein (Excel 2010:ssä tietotyypin vaihtaminen jälkeenpäin ei välttämättä edes onnistu).

Excel 2010 –versiossa ei tueta suoraa latausta, joten Power Queryn Close & Load > Close & Load To (Aloitus > Sulje ja lataa > Sulje ja lataa kohteeseen) –ikkunasta puuttuu rasti Load to Data Model (Lataa tietomalliin). Valitse siis latauksen yhteydessä vaihtoehto Only Create Connection (Luo vain yhteys) ja Load (Lataa), jolloin kyselyn tulos ei vielä lataudu mihinkään.

Siirry tämän jälkeen Power Pivot puolelle ja tuo tiedot Power Pivotiin seuraavasti:

  1. Valitse Design > Existing Connections (Rakenne > Aiemmin luodut yhteydet) ja valitse Workbook Connections –listalta se Power Query –kysely, jonka tuloksen haluat tuoda Power Pivotiin.excel 2010 existing connections
  2. Hyväksy Open (Avaa) –komennolla. Testaa tämän jälkeen yhteys (test connection) ja etene kuten etenisit tuodessasi tietoja Power Pivotin komennoilla. Älä kuitenkaan tee ohjatussa toiminnossa enää mitään sarakkeiden poistoja, suodatuksia tai uudelleen nimeämisiä kuten normaalisti tekisit. Kaikki kyselyyn liittyvät operaatiot pitää tehdä Power Queryn puolella (Ohje 3).

Kun olet tuonut tiedot Power Pivotiin, näet kyseisen kyselyn Existing Connections (Aiemmin luodut yhteydet) –ikkunassa kahteen kertaan. Ikkunan alaosassa näkyy alkuperäinen Workbook Connections –yhteys, joka on luotu Power Queryssä. Ikkunan yläosassa näkyy PowerPivot Data Connections yhteys, joka tuo datan Power Pivotiin.

Power Query kyselyn päivitys Excel 2010 –versiossa

Kyselyn voi päivittää vain Power Pivot –ikkunassa, ei Excelin puolella näkyvän paneelin avulla.

Power Query kyselyn muuttaminen Excel 2010 –versiossa

Kun teet muutoksia Power Query –kyselyyn, Power Pivotin tietojen päivitys lakkaa toimimasta ja saat Power Pivotissa virheilmoituksen päivityksen yhteydessä. (Jos käyt Power Query Editorissa ja poistut Close & Load -painikkeella, käynti vastaa kyselyn muuttamista! Jos siis käyt vain katsomassa kyselyä muuttamatta sitä, sulje Power Query ikkuna Close & Load -komennon sijaan ikkunan sulkemisrastilla, jottet joudu suorittamaan alla kuvattuja ikäviä toimenpiteitä.)

Vaikka Power Pivotin päivitysvirheilmoituksessa väitetään, että joudut poistamaan yhteyden ja luomaan sen uudelleen, Excel 2010 –versiossa on tähän ratkaisu! Erikoista, sillä vastaava ilmoitus Excel 2013 –versiossa tarkoittaa todellakin sitä, että taulu pitää poistaa ja ladata uudelleen.

virheilmoitus Excel 2010ssä

Suorita Excel 2010 –versiossa seuraava toimenpide, jonka jälkeen taulun pystyy taas päivittämään.

  1. Avaa Existing Connections (Aiemmin luodut yhteydet) –ikkuna ja valitse ikkunan alaosasta Workbook Queries –luettelosta kyseinen kysely ja valitse Open (Avaa). Kopioi ikkunassa oleva yhteysmerkkijono leikepöydälle ja sulje ikkuna.
    kopioi yhteysmerkkijono
  2. Avaa Existing Connections (Aiemmin luodut yhteydet) –ikkuna uudelleen ja valitse saman niminen ikkunan yläosassa PowerPivot Data Connections –listalla näkyvä kysely ja valitse Edit (Muokkaa).
  3. Tyhjennä ikkunassa näkyvä yhteysmerkkijono ja liitä tilalle se yhteysmerkkijono, jonka kopioit ja testaa sen toiminta Test Connection –painikkeella.
    test connection
  4. Valitse Save (Tallenna).
  5. Sulje ikkunat ja kokeile taulun päivitystä. Nyt sen pitäisi toimia.

Jos saat tämän jälkeen edelleen päivityksen yhteydessä virheilmoituksen, jossa mainitaan sarakkeiden puuttumisesta, käy Design > Table Properties (Rakenne > Taulun ominaisuudet) –ikkunassa ja rastita mukaan mahdolliset uudet kyselyyn tulleet sarakkeet. Jos kyselystä puolestaan on poistunut sarakkeita, riittää että käyt kyseisessä ikkunassa ja poistut, jonka jälkeen päivityksen pitäisi taas toimia.

Mutta eihän tämä 2010-version kanssa temppuilu tosiaan mukavaa ole, joten en suosittele eikä Microsoftkaan suosittele Excel 2010 PowerPivot & Power Query –yhdistelmää. Huomaa, että tällaista työkirjaa ei myöskään pysty konvertoimaan yhdellä komennolla suoraan 2013-työkirjaksi.

Lisätietoja

 

<- Kyselyoppaan sisällysluettelo
<- Edellinen juttu Seuraava juttu ->

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s