Muistia säästävän tietomallin luominen Excel- ja PowerPivot-apuohjelman luominen

Tärkeää: Tämä on artikkeli on käännetty koneellisesti. Lue vastuuvapauslauseke. Tämän artikkelin englanninkielinen versio on täällä .

Excel 2013: ssa tai sitä uudempi versio voit luoda tietomalleja, joka sisältää miljoonia rivejä ja suorita tehokas tietojen analysointi vastaan näistä malleista. Tietomallien voi luoda kanssa tai ilman Power Pivot -apuohjelman tukemaan saman työkirjan pivot-taulukoiden, kaavioiden ja Power View-visualisointien määrä.

Huomautus: Tässä artikkelissa Tietomallit Excel 2013: ssa. Kuitenkin saman tietomallien ja Power Pivot Excel 2013: n ominaisuuksiin koskevat myös Excel 2016. On tehokas little ero näissä Excel-versioissa.

Vaikka voit helposti luoda erittäin suuri tietomallien Excelissä, eivät on useita syitä. Ensimmäinen, suuri malleja, jotka sisältävät multitudes taulukoiden tai sarakkeiden on useimmissa analyysien overkill ja tee hankalaa kenttäluettelo. Toinen, suuri mallien käyttäminen arvokkaita muistia, vaikuttavia heikentää muut sovellukset ja raportteja, jotka jakavat samoja järjestelmäresursseja. Lopuksi Office 365: n SharePoint Onlinessa ja Excel Web Appissa rajoittaa 10 megatavua Excel-tiedoston kokoa. Työkirjan tietomallien, jotka sisältävät miljoonia rivejä kohtaat 10 megatavua melko nopeasti. Artikkelissa tietomallin määritykset ja rajoitukset.

Tässä artikkelissa kerrotaan, kuinka voit luoda tiiviisti rakennetun mallin, jota on helpompi käyttää ja joka vie vähemmän muistia. Tehokkaan mallisuunnittelun parhaiden käytäntöjen opettelusta on ajan mittaan hyötyä luomissasi ja käyttämissäsi malleissa, olipa käyttämäsi ohjelma Excel 2013 tai Office 365 SharePoint Online Office Online Server- tai SharePoint 2013 -ympäristössä.

Kannattaa myös työkirjan koon Optimointitoiminto. Analysoi Excel-työkirja, ja se, jos se on mahdollista pakkaa edelleen. Lataa työkirjan koon Optimointitoiminto.

Artikkelin sisältö

Pakkaussuhteet ja ladatun analytics-moduuli

Paras olematon sarake alhaisen muistin käytön kannalta

Kaksi esimerkkiä sarakkeista, jotka aina pois

Tarpeettomien sarakkeiden jättäminen pois

Voinko suodattaa vain tarvittavat rivit?

Entä jos annettava sarakkeen. Voit on edelleen sen kokoa pienentää?

Päivämääräkellonaika-sarakkeiden muokkaaminen

SQL-kyselyn muokkaaminen

DAX-KIELEN avulla laskettujen mittojen sarakkeiden sijaan

2 mitkä sarakkeet kannattaa säilyttää?

Tekemistä

Aiheeseen liittyvät linkit

Pakkaussuhteet ja analysointimoduuli

Excelin tietomallit tallentavat tiedot muistiin analysointimoduulilla. Moduuli käyttää tehokkaita pakkaustekniikoita tallennustilan tarpeen pienentämiseksi kutistaen tulosjoukon vain murto-osaan alkuperäisestä koostaan.

Tietomalli on keskimäärin 7-10 kertaa pienempi kuin samat tiedot lähtöpisteessään. Jos esimerkiksi tuot SQL Server -tietokannasta 7 megatavua tietoa, niin Excelin tietomallin koko voi hyvin olla alle 1 megatavua. Todellinen pakkausaste riippuu ensisijaisesti jokaisen sarakkeen yksilöllisten arvojen määrästä. Mitä enemmän yksilöllisiä arvoja on, sitä enemmän muistia tarvitaan niiden tallentamiseen.

Miksi pakkaaminen ja yksilölliset arvot ovat tärkeitä? Muistin käytön minimoivan tehokkaan mallin rakentamisessa on nimenomaan kyse pakkauksen maksimoinnista, ja helpoin tapa tehdä tämä on poistaa tarpeettomat sarakkeet, varsinkin jos sarakkeissa on paljon yksilöllisiä arvoja.

Huomautus: Yksittäisten sarakkeiden tallennustilan tarpeen väliset erot voivat olla suuret. Joissakin tapauksissa on parempi, että sarakkeita on useita ja niissä on vain pieni määrä yksilöllisiä arvoja, yhden, lukuisia yksilöllisiä arvoja sisältävän sarakkeen sijaan. Päivämäärän ja ajan optimoinnista kertovassa osiossa on lisätietoja tästä tekniikasta.

Olematon sarake on paras alhaisen muistin käytön kannalta

Muistia säästävin sarake on sellainen, jota ei ole tuotu. Jos haluat rakentaa tehokkaan mallin, tarkastele jokaista saraketta ja mieti, onko siitä hyötyä analyysin suorittamisessa. Jos siitä ei ole hyötyä tai et ole varma asiasta, älä tuo saraketta. Voit aina tarvittaessa lisätä uusia sarakkeita myöhemmin.

Seuraavassa on kaksi esimerkkiä sarakkeista, joita ei kannata tuoda.

Ensimmäinen esimerkki koskee tietovaraston tietoja. Tietovarastosta löytyy usein ETL-prosessien artefakteja, jotka lataavat ja päivittävät tietovaraston tietoja. Tietoja ladattaessa luodaan sarakkeita kuten "luo päivämäärä", "päivitä päivämäärä" ja "ETL-suoritus". Näitä sarakkeita ei tarvita mallissa, ja niiden valinta on poistettava tietoja tuotaessa.

Toinen esimerkki koskee Perusavain-sarakkeen pois jättämistä faktataulukkoa tuotaessa.

Monet taulukot, kuten faktataulukot, sisältävät perusavaimia. Taulukon perusavain on hyvä olla olemassa useimpien taulukoiden kohdalla, kuten sellaisten, jotka sisältävät asiakas-, työntekijä- tai myyntitietoja, jotta voit luoda mallissa yhteyksiä.

Tämä ei kuitenkaan koske faktataulukoita. Faktataulukon perusavaimella tunnistetaan kukin rivi yksilöllisesti. Perusavain on tarpeellinen normalisoinnissa, mutta siitä on vähemmän hyötyä tietomalleissa, joissa haluat vain analyysissä tai yhteyksien muodostamisessa käytettävät sarakkeet. Älä siis sisällytä perusavainta tuodessasi faktataulukon tietoja. Faktataulukon perusavaimet vievät mallissa suuren määrän tilaa, mutta niistä ei ole kuitenkaan mitään hyötyä, koska niiden avulla ei voi luoda yhteyksiä.

Huomautus: Tietovarastojen ja moniulotteisten tietokantojen suuria taulukoita, jotka koostuvat enimmäkseen numeerisista tiedoista, kutsutaan usein faktataulukoiksi. Faktataulukot sisältävät yleensä liiketoiminnan tehokkuus- ja tapahtumatietoja, kuten myynnin ja kustannusten arvopisteitä, jotka on koostettu ja yhdistetty esimerkiksi organisaatioyksikköihin, tuotteisiin, markkinasegmentteihin ja maantieteellisiin alueisiin. Kaikki faktataulukon sarakkeet, jotka sisältävät yritystietoja tai joiden avulla voidaan ristiviitata muiden taulukoiden tietoihin, olisi sisällytettävä malliin tietoanalyysin tukemiseksi. Perusavain-sarake kannattaa kuitenkin jättää faktataulukosta pois, koska se sisältää vain faktataulukossa esiintyviä yksilöllisiä arvoja. Faktataulukot ovat erittäin suuria, joten yksi suurimmista hyödyistä mallin tehokkuuden suhteen saadaan jättämällä niistä pois rivejä ja sarakkeita.

Tarpeettomien sarakkeiden jättäminen pois

Tehokas mallien sisältää vain sarakkeet, jota tarvitaan todella työkirjassasi. Jos haluat määrittää, mitkä sarakkeet sisältyvät mallia, sinun on ohjatun taulukon Tuonninmääritä PowerPivot-apuohjelman tietojen tuomiseen käyttää sen sijaan Excelissä "Tuo tiedot-valintaikkunan.

Aloittaessasi ohjatun taulukon tuontitoiminnon valitset tuotavat taulukot.

PowerPivot-apuohjelman ohjattu taulukon tuonti

Jokaisen taulukon kohdalla voit napsauttaa Esikatsele ja suodata -painiketta ja valita tarvitsemasi taulukon osat. Suosittelemme ensin poistamaan kaikkien sarakkeiden valinnan ja valitsemaan sitten haluamasi sarakkeet, joita mielestäsi tarvitaan analyysissä.

Ohjatun taulukon tuonnin esikatseluruutu

Voinko suodattaa vain tarvittavat rivit?

Monet yrityksen tietokantojen ja tietovarastojen taulukot sisältävät pitkien ajanjaksojen aikana kertyneitä historiatietoja. Lisäksi sinua kiinnostavat taulukot saattavat sisältää yrityksen sellaisten alueiden tietoja, joita ei tarvita analyysissäsi.

Ohjatun taulukon tuonnin avulla voit suodattaa historia- tai toisiinsa liittymättömät tiedot ja säästää näin paljon tilaa mallissa. Seuraavassa kuvassa on päivämääräsuodatin, jonka avulla haetaan vain ne rivit, jotka sisältävät kuluvan vuoden tietoja, ja jätetään pois tarpeettomat historiatiedot.

Ohjatun taulukon tuonnin suodatinruutu

Jos saraketta kuitenkin tarvitaan, voiko sen kokoa pienentää?

Sarakkeen pakkaamisominaisuuksia voi parantaa muutamilla lisätekniikoilla. Pidä mielessä, että ainoa pakkaamiseen vaikuttava sarakkeen ominaisuus on yksilöllisten arvojen määrä. Tässä osassa on lisätietoja siitä, miten joitain sarakkeita voi muokata yksilöllisten arvojen määrän vähentämiseksi.

PäivämääräKellonaika-sarakkeiden muokkaaminen

PäivämääräKellonaika-sarakkeet vievät usein paljon tilaa. Tämän tietotyypin tallennustilan tarvetta voi kuitenkin pienentää monella tavalla. Tekniikat vaihtelevat riippuen sarakkeen käyttötavasta ja osaamistasostasi SQL-kyselyjen luomisessa.

PäivämääräKellonaika-sarakkeet sisältävät päivämääräosan ja kellonajan. Kun mietit sarakkeen tarpeellisuutta, asiaa on mietittävä useaan kertaan PäivämääräKellonaika-sarakkeen kohdalla:

  • Onko Kellonaika-osa tarpeellinen?

  • Onko Kellonaika-osa tarpeellinen tunneissa? minuuteissa? sekunneissa? millisekunneissa?

  • Onko käytössäsi useita PäivämääräKellonaika-sarakkeita, koska haluat laskea niiden välisen eron, vai haluatko ainoastaan koota vuosi-, kuukausi-, neljännesvuosi- ja muita tietoja.

Näiden kysymysten vastaukset määrittävät PäivämääräKellonaika-sarakkeen käytön.

Kaikki ratkaisut edellyttävät muutoksia SQL-kyselyyn. Kyselyn muokkaaminen on helpompaa, jos suodatat jokaisessa taulukossa vähintään yhden sarakkeen. Suodattamalla sarakkeen voit muuttaa kyselyn rakenteen lyhenteestä (SELECT *) SELECT-lausekkeeksi, joka sisältää täydelliset sarakkeen nimet, joita on paljon helpompi muokata.

Seuraavaksi tarkastellaan sinulle luotuja kyselyitä. Taulukon ominaisuudet -valintaikkunassa voit vaihtaa kyselyeditoriin ja tarkastella kunkin taulukon nykyistä SQL-kyselyä.

PowerPivot-ikkunan valintanauha, jossa näkyy Taulukon ominaisuudet -komento

Valitse Taulukon ominaisuudet -kohdassa Kyselyeditori.

Avaa Kyselyeditori Taulukon ominaisuudet -valintaikkunassa.

Kyselyeditorissa näkyy SQL-kysely, jolla taulukon tiedot täytettiin. Jos suodatit tuonnin aikana jonkin sarakkeen, kysely sisältää täydelliset sarakkeen nimet:

Tietojen hakemiseen käytetty SQL-kysely

Jos taas toit koko taulukon ilman, että poistit minkään sarakkeen valintaa tai käytit suodatinta, kysely näkyy muodossa "Valitse * kohteesta", jota on vaikeampi muokata.

SQL-kysely, jossa käytetään lyhyempää oletussyntaksia

SQL-kyselyn muokkaaminen

Kyselyn löydettyäsi voit muokata sitä ja näin pienentää mallin kokoa lisää.

  1. Valuutta- tai desimaalitietoja sisältävissä sarakkeissa voit poistaa tarpeettomat desimaalit seuraavalla syntaksilla:

    "SELECT ROUND([Desimaalin_sarakkeen_nimi],0)… ."

    Jos tarvitset senttejä mutta et niiden murto-osia, korvaa 0 luvulla 2. Jos käytät negatiivisia lukuja, voit pyöristää yksiköihin, kymmeniin, satoihin jne.

  2. Jos PäivämääräKellonaika-sarakkeen nimi on dbo.Bigtable.[PäivämääräKellonaika] etkä tarvitse kellonaikaosaa, voit poistaa sen seuraavalla syntaksilla:

    "SELECT CAST (dbo.Bigtable.[Päivämäärä kellonaika] päivämääränä) AS [Päivämäärä kellonaika]) "

  3. Jos PäivämääräKellonaika-sarakkeen nimi on dbo.Bigtable.[PäivämääräKellonaika] ja tarvitset sekä päivämäärä- että kellonaikaosan, käytä SQL-kyselyssä useita sarakkeita yhden PäivämääräKellonaika-sarakkeen sijaan.

    "SELECT CAST (dbo.Bigtable.[Päivämäärä kellonaika] päivämääränä) AS [Päivämäärä kellonaika],

    datepart(hh, dbo.Bigtable.[Päivämäärä Kellonaika]) as [Päivämäärä Kellonaika Tunnit],

    datepart(mi, dbo.Bigtable.[Päivämäärä Kellonaika]) as [Päivämäärä Kellonaika Minuutit],

    datepart(ss, dbo.Bigtable.[Päivämäärä Kellonaika]) as [Päivämäärä Kellonaika Sekunnit],

    datepart(ms, dbo.Bigtable.[Päivämäärä Kellonaika]) as [Päivämäärä Kellonaika Millisekunnit]

    Käytä niin montaa saraketta kuin tarvitset kunkin osan tallentamiseksi eri sarakkeisiin.

  4. Jos tarvitset tunteja ja minuutteja ja haluat molemmat yhteen aikasarakkeeseen, voit käyttää seuraavaa syntaksia:

    Timefromparts(datepart(hh, dbo.Bigtable.[Päivämäärä Kellonaika]), datepart(mm, dbo.Bigtable.[Date Time])) as [Päivämäärä Kellonaika TuntiMinuutti]

  5. Jos käytössäsi on kaksi PäivämääräKellonaika-saraketta, kuten [Alkamisaika] ja [Päättymisaika], ja haluat vain nähdä niiden välisen aikaeron sekunteina [Kesto]-nimisenä sarakkeena, poista molemmat sarakkeet luettelosta ja lisää:

    "datediff(ss;[Alkamispäivä];[Päättymispäivä]) as [Kesto]"

    Jos käytät ms-avainsanan sijaan ss-sanaa, saat keston millisekunteina.

DAX-laskettujen mittojen käyttäminen sarakkeiden sijaan

Jos olet käyttänyt DAX-lausekekieltä aiemmin, saatat tietää, että laskettuja sarakkeita käytetään uusien sarakkeiden johtamisessa jonkin toisen mallissa olevan sarakkeen perusteella. Lasketut mitat taas määritetään mallissa kerran mutta arvioidaan vasta, kun niitä käytetään Pivot-taulukossa tai muussa raportissa.

Voit säästää muistia korvaamalla säännölliset tai lasketut sarakkeet lasketuilla mitoilla. Klassinen esimerkki on Yksikköhinta, Määrä ja Summa. Jos käytössäsi ovat kaikki kolme saraketta, voit säästää tilaa säilyttämällä niistä vain kaksi ja laskemalla kolmannen DAX-kaavalla.

Mitkä sarakkeet kannattaa säilyttää?

Yllä olevassa esimerkissä kannattaa säilyttää sarakkeet Määrä ja Yksikköhinta. Niissä on vähemmän arvoja kuin Summa-sarakkeessa. Laske summa lisäämällä laskettu mitta, kuten:

"TotalSales:=sumx(‘Sales Table’;’Sales Table’[Yksikköhinta]*’Sales Table’[Määrä])”

Lasketut sarakkeet vievät tilaa mallissa tavallisten sarakkeiden tapaan. Lasketut mitat taas lasketaan samantien, ja ne eivät vie tilaa.

Yhteenveto

Tässä artikkelissa on käsitelty useita tapoja muistia säästävän mallin rakentamiseen. Voit vähentää tietomallin tiedoston kokoa ja muistin tarvetta vähentämällä sarakkeiden ja rivien kokonaismäärää sekä sarakkeiden yksilöllisten arvojen määrää. Artikkelissa käsiteltiin mm. seuraavia tekniikoita:

  • Paras tapa säästää tilaa on sarakkeiden poistaminen. Päätä, mitkä sarakkeet ovat välttämättömiä.

  • Joskus voit poistaa sarakkeen ja korvata sen taulukossa lasketulla mitalla.

  • Et välttämättä tarvitse kaikkia taulukon rivejä. Voit suodattaa rivit ohjatussa taulukon tuonnissa.

  • Tavallisesti yhden sarakkeen jakaminen useaan erilliseen osaan on hyvä keino pienentää sarakkeen yksilöllisten arvojen määrää. Jokaisessa osassa on pieni määrä yksilöllisiä arvoja, ja niiden kokonaismäärä on alkuperäistä yhdistettyä saraketta pienempi.

  • Usein myös tarvitset erillisiä osia osittajina raporteissa. Tilanteen mukaan voit luoda hierarkioita osista kuten Tunnit, Minuutit ja Sekunnit.

  • Usein sarakkeet sisältävät enemmän tietoa kuin on tarpeen. Sarakkeeseen voi esimerkiksi olla tallennettu desimaalit, vaikka olet käyttänyt muotoilua, joka piilottaa desimaalit. Pyöristäminen voi olla tehokas keino pienentää numeerisen sarakkeen kokoa.

Nyt kun olet valmis, voit pienentää työkirjan kokoa, harkitse käytössä myös työkirjan koon Optimointitoiminto. Analysoi Excel-työkirja, ja se, jos se on mahdollista pakkaa edelleen. Lataa työkirjan koon Optimointitoiminto.

Aiheeseen liittyvät linkit

Tietomallin määritykset ja rajoitukset

Työkirjan koon Optimointitoiminto lataaminen

PowerPivot: Tehokas tietojen analysointi ja tietomallien luominen Excelissä

Huomautus: Konekäännöksestä ilmoittava vastuusvapauslauseke: Tämä artikkeli on käännetty tietokonejärjestelmällä, eikä kieliasiantuntija ole muokannut sitä. Microsoft tarjoaa nämä konekäännökset avuksi muille kuin englantia puhuville käyttäjille, jotta he saavat lisätietoja Microsoftin tuotteista, palveluista ja tekniikoista. Koska artikkeli on koneellisesti käännetty, se saattaa sisältää sanasto-, lauseoppi- ja kielioppivirheitä.

Kehitä taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×