Kirjaudu sisään Microsoft-tilillä
Kirjaudu sisään tai luo tili.
Hei,
Käytä toista tiliä.
Sinulla on useita tilejä
Valitse tili, jolla haluat kirjautua sisään.

Excel 2013:ssa tai sitä uudemmassa versiossa voit luoda miljoonia rivejä sisältäviä tietomalleja ja analysoida näitä malleja tehokkailla tietojen analysoinnilla. Tietomalleja voi luoda apuohjelmalla tai ilman Power Pivot, joka tukee saman työkirjan kaikkia Pivot-taulukoita, kaavioita Power View visuaalisia visualisointeja.

Huomautus: Tässä artikkelissa kuvataan Excel 2013:n tietomalleja. Samat tietojen mallinnus- ja Power Pivot -ominaisuudet, jotka otettiin käyttöön Excel 2013:ssa, koskevat Excel 2016. Näiden versioiden välillä ei ole juurikaan eroa Excel.

Vaikka voit helposti luoda suuria tietomalleja Excel, siihen on useita syitä. Ensinnäkin suuret mallit, joissa on useita taulukoita ja sarakkeita, ovat ylikuormittavia useimmissa analyyseja varten, ja ne tekevät hankalasta kenttäluettelosta. Suuret mallit käyttävät arvokasta muistia, mikä vaikuttaa negatiivisesti muihin sovelluksiin ja raportteihin, joilla on samat järjestelmäresurssit. Lopuksi Microsoft 365 online SharePoint Excel sovellus rajoittaa Excel 10 megatavuun. Jos työkirjan tietomalli sisältää miljoonia rivejä, 10 Megatavun raja on melko nopeasti. Katso Tietomallin määritykset ja rajoitukset.

Tässä artikkelissa opit rakentamaan tiivisti rakennettavan mallin, jota on helpompi käyttää ja käyttää vähemmän muistia. Tehokkaiden mallien suunnittelun parhaiden käytäntöjen oppiminen vie aikaa minkä tahansa mallin luomiseen ja käyttöön riippumatta siitä, tarkasteletko sitä Excel 2013:ssa, Microsoft 365 SharePoint Onlinessa, Office Online Serverissä tai SharePoint 2013:ssa.

Voit myös käyttää työkirjan koon optimointityökalua. Se analysoi Excel-työkirjan ja pakkaa sen entistä pienempään kokoon, jos se on mahdollista. Lataa Työkirjan koon optimointityökalu.

Artikkelin sisältö

Pakkaussuhteet ja muistinsijainnit-ohjelma

Muistissa Excel tietomallit tallentavat tiedot muistiin muistissa muistissa. Ohjelma käyttää tehokkaita pakkaustekniikoita tallennustilavaatimusten pienentämistä, mikä pienentää tulosjoukkoa, kunnes se on murto-osa sen alkuperäisestä koosta.

Tietomallin voi keskimäärin olla 7–10 kertaa pienempiä kuin samat tiedot alkuperäpisteessään. Jos esimerkiksi tuot 7 Mt tietoja SQL Server tietokannasta, Excel voi olla enintään 1 Mt. Itse asiassa pakkaamisen aste riippuu pääasiassa kunkin sarakkeen yksilöllisten arvojen lukumäärästä. Mitä yksilölliset arvot ovat, sitä enemmän muistia tarvitaan niiden tallentamista varten.

Miksi kyse on pakkaamista ja yksilöllisiä arvoja varten? Koska muistin käyttöä minimoiva tehokas malli on suunniteltu pakkausten maksimoinnilla, ja helpoin tapa on poistaa sarakkeet, joita et tarvitse erityisesti, jos sarakkeissa on suuri määrä yksilöllisiä arvoja.

Huomautus: Yksittäisten sarakkeiden tallennusvaatimusten erot voivat olla suuria. Joissakin tapauksissa on parempi, että useita sarakkeita, joissa on pieni määrä yksilöllisiä arvoja, ei yhtä saraketta, jossa on suuri määrä yksilöllisiä arvoja. Datetime-optimoinnit-osiossa käsitellään tätä tekniikkaa yksityiskohtaisesti.

Mikään ei voi päihittää olemassa olevan sarakkeen muistin vähäisessä käytössä

Muistia säästävin sarake on sarake, jota et ole koskaan tuonut ensimmäiseksi. Jos haluat luoda tehokkaan mallin, tarkista kukin sarake ja kysy itseltäsi, tukeeko se analyysia, jonka haluat suorittaa. Jos näin ei ole tai et ole varma, jätä se pois. Voit lisätä uusia sarakkeita myöhemmin, jos tarvitset niitä.

Kaksi esimerkkiä sarakkeista, jotka on aina jätettävä pois

Ensimmäinen esimerkki liittyy tietoihin, jotka ovat peräisin tietovarastosta. Tietovarastosta löytyy usein ETL-prosessien artefakteja, jotka lataavat ja päivit ovat varastossa. Sarakkeet, kuten "luo päivämäärä", "päivityspäivä" ja "ETL-suoritus", luodaan, kun tiedot ladataan. Mitään näistä sarakkeista ei tarvita mallissa, ja sen valinta pitäisi olla pois, kun tuot tietoja.

Toisessa esimerkissä perusavainsarake on pois, kun faktataulukko tuodaan.

Monilla taulukoilla, myös faktataulukoilla, on perusavaimet. Useimmissa taulukoissa, kuten asiakas-, työntekijä- tai myyntitietoja sisältävissä taulukoissa, haluat taulukon perusavaimen, jotta voit käyttää sitä yhteyksien luomiseen mallissa.

Faktataulukot ovat erilaisia. Faktataulukossa perusavainta käytetään kunkin rivin yksilöintiin. Normalisointia varten se ei ole yhtä hyödyllinen tietomallissa, jossa haluat käyttää vain näitä sarakkeita analysointiin tai taulukoiden yhteyksien määrittämiseen. Tästä syystä älä sisällytä sen perusavainta, kun tuot tietoja faktataulukosta. Faktataulukon perusavaimet vievät mallissa valtavan määrän tilaa, mutta niillä ei kuitenkaan ole etua, sillä niitä ei voi käyttää yhteyksien luomiseen.

Huomautus: Tietovarastoissa ja monidimensioisista tietokannoista suurista taulukoista, joissa on enimmäkseen numeerisia tietoja, kutsutaan usein faktataulukoiksi. Faktataulukot sisältävät yleensä liiketoiminnan suorituskyvyn tai tapahtumatiedot, kuten myynti- ja kustannustietopisteet, jotka koostetaan ja tasataan organisaation yksiköiden, tuotteiden, markkinasegmenttien, maantieteellisten alueiden ja niin edelleen. Kaikki faktataulukon sarakkeet, jotka sisältävät yritystietoja tai joita voidaan käyttää muihin taulukoihin tallennettujen ristiviitetietojen ristiinviittaukseen, on sisällytettävä malliin tietojen analysointia varten. Sarake, jonka haluat jättää pois, on faktataulukon perusavainsarake, joka koostuu vain faktataulukossa ja ei muualla ole yksilöivästä arvosta. Koska faktataulukot ovat niin suuria, joitakin mallin tehokkuuden suurimpia etuja saadaan rivien tai sarakkeiden sulkemiseksi pois faktataulukoista.

Tarpeettomien sarakkeiden poissulkeminen

Tehokkaat mallit sisältävät vain ne sarakkeet, joita tarvitset työkirjassasi. Jos haluat hallita, mitkä sarakkeet sisältyvät malliin, sinun on tuotava tiedot Power Pivot -apuohjelmassa ohjatulla taulukon tuomistoiminnolla Tietojen tuominen -valintaikkunan sijaan Excel.

Kun käynnistät ohjatun taulukon tuonnin, voit valita tuotavat taulukot.

PowerPivot-apuohjelman ohjattu taulukon tuonti

Voit napsauttaa kunkin taulukon Esikatselu-&-painiketta ja valita haluamasi taulukon osat. Suosittelemme, että poistat ensin kaikkien sarakkeiden valinnan ja jatkat sitten haluamistasi sarakkeista sen jälkeen, kun olet harkitsenut, tarvitaanko niitä analyysia varten.

Ohjatun taulukon tuonnin esikatseluruutu

Entä jos suodatat vain tarvittavat rivit?

Monissa yrityksen tietokantojen ja tietovarastojen taulukoissa on historiatietoja, jotka on kertynyt pitkiä aikoja. Lisäksi saatat nähdä, että sinua kiinnostavat taulukot sisältävät tietoja liiketoiminnan alueista, joita ei tarvita tiettyä analyysiä varten.

Ohjatun taulukon tuonnin avulla voit suodattaa historiatiedot tai toisiinsa liittymättömät tiedot pois, mikä säästää paljon tilaa mallissa. Seuraavassa kuvassa päivämääräsuodatinta käytetään vain kuluvan vuoden tietoja sisältävien rivien noutamiseen lukuun ottamatta historiatietoja, joita ei tarvita.

Ohjatun taulukon tuonnin suodatinruutu

Entä jos saraketta tarvitaan Voimmeko edelleen pienentää sen tilakustannuksia?

On muutamia lisätekniikoita, joilla voit parantaa sarakkeen pakkaamista. Muista, että sarakkeen ainoa pakkaamista vaikuttava ominaisuus on yksilöllisten arvojen määrä. Tässä osassa opit, miten joitakin sarakkeita voidaan muokata yksilöllisten arvojen määrän vähentämiseksi.

Päivämäärä/aika-sarakkeiden muokkaaminen

Usein Datetime-sarakkeissa on paljon tilaa. Tämän tietotyypin tallennusvaatimuksia voi onneksi pienentää monella tavalla. Tekniikat vaihtelevat sen mukaan, miten käytät saraketta, ja käyttömukavuuden tasosi SQL luomiskyselyissä.

Päivämäärä/aika-sarakkeissa on päivämääräosa ja kellonaika. Kun kysyt itseltäsi, tarvitsetko sarakkeen, kysy sama kysymys useita kertoja Datetime-sarakkeelle:

  • Tarvitsenko aika-osan?

  • Tarvitsenko aikaosaa tuntitasolla? minuutteja? , Sekunnit? millisekunteina?

  • Onko minulla useita Datetime-sarakkeita, koska haluan laskea niiden välisen eron vai vain koota tiedot yhteen vuoden, kuukauden, vuosineljänneksen ja niin edelleen.

Se, miten vastaat näihin kysymyksiin, määrittää vaihtoehdot Datetime-sarakkeen käsittelemiseksi.

Kaikki nämä ratkaisut edellyttävät kyselyn SQL. Jotta kyselyn muokkaaminen olisi helpompaa, suodata ainakin yksi sarake jokaista taulukkoa varten. Suodattamalla sarakkeen muutat kyselyn rakenteen lyhennetystä muodosta (SELECT *) SELECT-lauseeksi, joka sisältää täysin hyväksytyt sarakenimet, joita on paljon helpompi muokata.

Katsotaanpa sinulle luotuja kyselyjä. Taulukon ominaisuudet -valintaikkunassa voit siirtyä Kyselyeditoriin ja tarkastella kunkin SQL ajankohtaista kyselyä.

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

Valitse Taulukon ominaisuudet -kohdassa Kyselyeditori.

Avaa Kyselyeditori Taulukon ominaisuudet -valintaikkunassa.

Kyselyeditori näyttää SQL kyselyn, jolla taulukko täytetään. Jos suodatit minkä tahansa sarakkeen tuonnin aikana, kysely sisältää täysin hyväksytyt sarakenimet:

Tietojen hakemiseen käytetty SQL-kysely

Jos sen sijaan toit taulukon kokonaisuudessaan poistamatta mitään saraketta tai lisäämättä mitään suodatinta, kysely on "Valitse * kohteesta", jota on vaikeampi muokata:

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

Kyselyn SQL muokkaaminen

Nyt kun osaat löytää kyselyn, voit muokata sitä mallin koon pienentämistä varten.

  1. Jos sarakkeissa on valuutta- tai desimaalitietoja ja et tarvitse desimaaleja, voit poistaa desimaalit tämän syntaksin avulla:

    "VALITSE PYÖRISTÄ([Decimal_column_name],0)... .”

    Jos tarvitset sentit, mutta et sentin murtolukuja, korvaa ne 0 2:lla. Jos käytät negatiivisia lukuja, voit pyöristää lukuja yksiköihin, kymmeniksi, sadoiksi jne.

  2. Jos sinulla on dbo-niminen Datetime-sarake. Bigtable. [Date Time] and you do not need the Time part, use the syntax to get rid of the time:

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

  3. Jos sinulla on dbo-niminen Datetime-sarake. Bigtable. [Päivämääräaika] ja tarvitset sekä Päivämäärä- että Kellonaika-osia, voit käyttää SQL-kyselyssä yhden Datetime-sarakkeen sijaan:

    "SELECT CAST (dbo. Bigtable. [Date Time] as date ) AS [Date Time],

    datepart(hh, dbo. Bigtable. [Päivämääräaika]) kuten [Päivämääräajan tunnit],

    datepart(mi, dbo. Bigtable. [Päivämääräaika]) kuten [Päivämääräajan minuutit],

    datepart(ss, dbo. Bigtable. [Päivämääräaika]) kuten [Päivämäärä sekunnit],

    datepart(ms, dbo. Bigtable. [Päivämääräaika]) as [Date Time Milliseconds]"

    Käytä niin monta saraketta kuin haluat tallentaa kunkin osan erillisiin sarakkeisiin.

  4. Jos tarvitset tunteja ja minuutteja ja pidät niitä yhdessä yhden kerran-sarakkeena, voit käyttää syntaksia:

    Timefromparts(datepart(hh, dbo. Bigtable. [Päivämääräaika]), päivämääräosa(mm, dbo. Bigtable. [Päivämääräaika])) as [Date Time HourMinute]

  5. Jos sinulla on kaksi päivämäärä/aika-saraketta, kuten [Alkamisaika] ja [Päättymisaika] ja tarvitset niiden välisen ajan eron sekunneissa sarakkeena nimeltä [Kesto], poista molemmat sarakkeet luettelosta ja lisää seuraavat tiedot:

    "datediff(ss,[Start Date],[End Date]) as [Duration]"

    Jos käytät avainsanaa ms ss:n sijaan, saat keston millisekunteina

DAX-laskettujen mitat sarakkeiden sijaan

Jos olet käyttänyt DAX-lausekkeen kieltä aiemmin, saatat jo tietää, että laskettuja sarakkeita käytetään uusien sarakkeiden riveihin mallin jonkin muun sarakkeen perusteella, kun taas lasketut mitat määritetään kerran mallissa, mutta niitä arvioidaan vain käytettäessä Pivot-taulukkoa tai muuta raporttia.

Yksi muistia säästävä tekniikka on korvata säännöllisiä tai laskettuja sarakkeita lasketuilla toimenpiteillä. Perinteinen esimerkki on Yksikköhinta, Määrä ja Yhteensä. Jos sinulla on kaikki kolme, voit säästää tilaa säilyttämällä vain kaksi ja laskemalla kolmannen DAX-käyttäen.

Mitkä kaksi saraketta kannattaa säilyttää?

Säilytä yllä olevassa esimerkissä Määrä ja Yksikköhinta. Näillä kahdella on vähemmän arvoja kuin summalla. Voit laskea summan lisäämällä lasketun mitan seuraavasti:

"Kokonaismyynti:=summa('Myyntitaulukko','Myyntitaulukko'[Yksikköhinta]*'Myyntitaulukko'[Määrä])"

Lasketut sarakkeet ovat tavallisia sarakkeita, jotka vievät tilaa mallista. Sen sijaan lasketut mitat lasketaan lennossa, eikä niitä tarvita tilaa.

Yhteenveto

Tässä artikkelissa esittelimme useita lähestymistapoja, joiden avulla voit luoda muistia säästävämman mallin. Voit pienentää tietomallin tiedostokokoa ja muistivaatimuksia pienentämällä sarakkeiden ja rivien kokonaismäärää ja kussakin sarakkeessa näkyvien yksilöllisten arvojen määrää. Seuraavassa on joitain tekniikoita, joita olemme katta lähettäneet:

  • Sarakkeiden poistaminen on paras tapa säästää tilaa. Valitse, mitä sarakkeita tarvitset.

  • Joskus voit poistaa sarakkeen ja korvata sen lasketulla mittayksiköllä taulukossa.

  • Et ehkä tarvitse kaikkia taulukon rivejä. Voit suodattaa pois rivejä ohjatussa taulukon tuomisessa.

  • Yleensä yksittäisen sarakkeen jako useisiin eri osiin on hyvä tapa vähentää yksilöllisten arvojen määrää sarakkeessa. Jokaisessa osassa on pieni määrä yksilöllisiä arvoja ja yhdistetty summa on pienempi kuin alkuperäinen yhdistetty sarake.

  • Monissa tapauksissa raporteissa on käytettävä myös erillisiä osia osittajien kanssa. Tarvittaessa voit luoda hierarkioita eri osista, kuten Tunnit, Minuutit ja Sekunnit.

  • Sarakkeissa on usein enemmän tietoja kuin niitäkin tarvitaan. Oletetaan esimerkiksi, että sarakkeeseen tallennetaan desimaaleja, mutta olet käyttänyt muotoilua kaikkien desimaalien piilottamiseen. Pyöristäminen voi olla hyvin tehokasta numeerisen sarakkeen koon pienentämiseen.

Nyt kun olet tehnyt kaiken, mitä voit pienentää työkirjan kokoa, harkitse myös Työkirjan koon optimointityökalun ajamista. Se analysoi Excel-työkirjan ja pakkaa sen entistä pienempään kokoon, jos se on mahdollista. Lataa Työkirjan koon optimointityökalu.

Aiheeseen liittyvät linkit

Tietomallin määritykset ja rajoitukset

Työkirjan koon optimointityökalu

PowerPivot: tehokas tietojen analysointi ja tietomallien luominen Excelissä

Tarvitsetko lisäohjeita?

Haluatko lisää vaihtoehtoja?

Tutustu tilausetuihin, selaa harjoituskursseja, opi suojaamaan laitteesi ja paljon muuta.

Osallistumalla yhteisöihin voit kysyä kysymyksiä ja vastata niihin, antaa palautetta sekä kuulla lisää asiantuntijoilta, joilla on runsaasti tietoa.

Oliko näistä tiedoista hyötyä?

Kuinka tyytyväinen olet käännöksen laatuun?
Mikä vaikutti kokemukseesi?
Kun valitset Lähetä, palautettasi käytetään Microsoftin tuotteiden ja palveluiden parantamiseen. IT-järjestelmänvalvojasi voi kerätä nämä tiedot. Tietosuojatiedot.

Kiitos palautteesta!

×