Excelissä voit luoda miljoonia rivejä sisältäviä tietomalleja ja analysoida tietoja tehokkaasti näiden mallien pohjalta. Tietomalleja voidaan luoda Power Pivot -apuohjelman kanssa tai ilman sitä, jotta ne tukevat useita Pivot-taulukoita, kaavioita ja Power View -visualisointeja samassa työkirjassa.
Vaikka Excelissä voi luoda helposti valtavia tietomalleja, on useita syitä, miksi niitä ei tehdä. Ensinnäkin suuret mallit, joissa on useita taulukoita ja sarakkeita, ovat ylivoimaisia useimmille analyyseille ja tekevät kenttäluettelosta hankalan. Lisäksi suuret mallit käyttävät arvokasta muistia, mikä vaikuttaa negatiivisesti muihin sovelluksiin ja raportteihin, jotka käyttävät samoja järjestelmäresursseja. Lisäksi Microsoft 365:ssä sekä SharePoint Online että Excel Web App rajoittavat Excel-tiedoston koon 10 megatavuun. Miljoonia rivejä sisältävien työkirjatietomallien kohdalla 10 megatavun raja täyttyy melko nopeasti. Katso Tietomallin määritykset ja rajoitukset.
Tässä artikkelissa opit rakentamaan tiiviin mallin, jota on helpompi käyttää ja joka vie vähemmän muistia. Tehokkaan mallisuunnittelun parhaiden käytäntöjen opettelu kannattaa myöhemmin tulostaa minkä tahansa luomasi ja käyttämäsi mallin kohdalla riippumatta siitä, tarkasteletko mallia Excelissä, Microsoft 365 SharePoint Onlinessa, Office Web Apps Serverissä vai SharePointissa.
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ö
Mikään ei voita olematonta saraketta, kun muistin käyttö on vähäistä
Entä jos tarvitsemme sarakkeen; Voimmeko vielä alentaa sen tilakustannuksia?
Pakkaussuhteet ja muistissa oleva analytiikkamoduuli
Excelin tietomallit tallentavat tietoja muistiin käyttämällä muistissa olevaa analysointimoduulia. Moottori käyttää tehokkaita pakkaustekniikoita tallennustarpeen vähentämiseksi ja kutistaa tulosjoukkoa, kunnes se on murto-osa alkuperäisestä koostaan.
Voit olettaa, että tietomalli on keskimäärin 7–10 kertaa pienempi kuin sama tieto alkuperäpisteessään. Jos tuot esimerkiksi 7 Mt tietoja SQL Server -tietokannasta, tietomallin koko Excelissä voi helposti olla 1 Mt tai vähemmän. Todellinen pakkausaste määräytyy ensisijaisesti kussakin sarakkeessa olevien yksilöllisten arvojen määrän mukaan. Mitä enemmän arvoja on, sitä enemmän muistia niiden tallentamiseen tarvitaan.
Miksi kyse on pakkauksesta ja yksilöllisistä arvoista? Tehokkaan ja muistin käyttöä minimoivan mallin rakentamisessa on kyse pakkaamisen maksimoinnista, ja helpoin tapa tähän on poistaa sarakkeet, joita ei oikeastaan tarvita, varsinkin jos nämä sarakkeet sisältävät suuren määrän yksilöllisiä arvoja.
Huomautus
Yksittäisten sarakkeiden tallennusvaatimusten välillä voi olla suuria eroja. Joissakin tapauksissa on parempi käyttää useita sarakkeita, joissa on vain vähän yksilöllisiä arvoja, kuin yhdessä sarakkeessa, jossa on suuri määrä yksilöllisiä arvoja. Päivämäärän ja ajan optimointeja käsittelevässä osassa käsitellään tätä tekniikkaa yksityiskohtaisesti.
Mikään ei voita olematonta saraketta, kun muistin käyttö on vähäistä
Muistia säästävin sarake on se, jota et ole koskaan tuonut. Jos haluat rakentaa tehokkaan mallin, tarkastele jokaista saraketta ja mieti, vaikuttaako se haluamaasi analyysiin. Jos näin ei ole tai et ole varma, jätä se pois. Voit aina lisätä uusia sarakkeita myöhemmin, jos tarvitset niitä.
Kaksi esimerkkiä sarakkeista, jotka tulee aina jättää pois
Ensimmäinen esimerkki liittyy tietovarastosta peräisin oleviin tietoihin. Tietovarastossa löytyy usein ETL-prosessien artefakteja, jotka lataavat ja päivittävät tietoja varastossa. Sarakkeet, kuten luontipäivä, päivityspäivä ja ETL-suoritus, luodaan, kun tiedot ladataan. Mallissa ei tarvita mitään näistä sarakkeista, ja niiden valinta on poistettava, kun tuot tietoja.
Toisessa esimerkissä perusavainsarake jätetään pois, kun faktataulukkoa tuodaan.
Monilla taulukoilla, myös faktataulukoilla, on perusavaimet. Useimmissa taulukoissa, kuten niiden, jotka sisältävät asiakas-, työntekijä- tai myyntitietoja, tarvitset taulukon perusavaimen, jotta voit käyttää sitä yhteyksien luomiseen mallissa.
Faktataulukot ovat erilaisia. Faktataulukossa perusavaimen avulla yksilöidään kukin rivi. Vaikka se on tarpeen normalisointia varten, se ei ole yhtä hyödyllinen tietomallissa, jossa haluat käyttää vain kyseisiä sarakkeita analyysissa tai taulukoiden yhteyksien muodostamisessa. Tästä syystä älä sisällytä perusavainta, kun tuot tietoja faktataulukosta. Faktataulukon perusavaimet vievät valtavan määrän tilaa mallissa mutta niistä ei ole mitään hyötyä, koska niitä ei voi käyttää yhteyksien luomiseen.
Huomautus
Tietovarastoissa ja monidimensioistietokannoissa suuria, pääasiassa numeerisista tiedoista koostuvia taulukoita kutsutaan usein faktataulukoiksi. Faktataulukot sisältävät yleensä liiketoiminnan suorituskyky- tai tapahtumatietoja, kuten myynnin ja kustannusten arvopisteitä, jotka on koottu ja kohdistettu esimerkiksi organisaation yksiköiden, tuotteiden, markkinasegmenttien tai maantieteellisten alueiden mukaan. Kaikki faktataulukon sarakkeet, jotka sisältävät yritystietoja tai joita voidaan käyttää muihin taulukoihin tallennettujen tietojen ristiviittauksessa, tulisi sisällyttää malliin tietojen analysoinnin tueksi. Pois jätettävä sarake on faktataulukon perusavainsarake, joka koostuu yksilöllisistä arvoista, jotka ovat vain faktataulukossa eivätkä mistään muualta. Koska faktataulukot ovat niin suuria, yksi suurimmista parannuksista mallien tehokkuudessa saadaan rivien tai sarakkeiden jättämisestä pois faktataulukoista.
Tarpeettomien sarakkeiden jättäminen pois
Tehokkaat mallit sisältävät vain ne sarakkeet, joita työkirjassa todella tarvitaan. Jos haluat hallita malliin sisällytettäviä sarakkeita, sinun on tuotava tiedot käyttämällä PowerPivot-apuohjelman ohjattua taulukon tuontitoimintoa Excelin Tietojen tuominen -valintaikkunan sijaan.
Kun käynnistät ohjatun taulukon tuonnin, valitset tuotavat taulukot.
Voit napsauttaa kunkin taulukon Esikatselu & Suodatin -painiketta ja valita taulukosta oikeasti tarvitsemasi osat. Suosittelemme, että poistat ensin kaikkien sarakkeiden valinnat ja valitset sitten haluamasi sarakkeet, kun olet pohtinut, tarvitaanko niitä analyysissa.
Entä vain välttämättömien rivien suodattaminen?
Monet yritystietokantojen ja tietovarastojen taulukot sisältävät historiatietoja, jotka ovat kertyneet pitkältä ajalta. Lisäksi saatat huomata, että sinua kiinnostavat taulukot sisältävät tietoja liiketoiminnan osa-alueista, joita ei tarvita omassa analyysissasi.
Ohjatun taulukon tuonnin avulla voit suodattaa historiatiedot tai toisiinsa liittymättömät tiedot ja säästää siten paljon tilaa mallissa. Seuraavassa kuvassa päivämääräsuodattimella noudetaan vain kuluvan vuoden tietoja sisältävät rivit lukuun ottamatta historiatietoja, joita ei tarvita.
Entä jos tarvitsemme sarakkeen; Voimmeko vielä alentaa sen tilakustannuksia?
Voit tehdä sarakkeesta sopivan pakkaamisen muutamalla lisätekniikalla. Muista, että ainoa sarakkeen ominaisuus, joka vaikuttaa pakkaamiseen, on yksilöllisten arvojen määrä. Tässä osassa kerrotaan, miten joitakin sarakkeita voi muokata yksilöllisten arvojen määrän vähentämiseksi.
Päivämäärä/aika-sarakkeiden muokkaaminen
Usein päivämäärä/aika-sarakkeet vievät paljon tilaa. Onneksi tämän tietotyypin tallennustarvetta voidaan vähentää monin eri tavoin. Tekniikat vaihtelevat sen mukaan, miten käytät saraketta, ja sen mukaan, kuinka mukavasti luot SQL-kyselyitä.
DateTime-sarakkeissa on päivämääräosa ja kellonaika. Kun mietit, tarvitsetko sarakkeen, esitä sama kysymys useita kertoja Datetime-sarakkeelle:
- Tarvitsenko aikaosan?
- Tarvitsenko aikaosan tuntitasolla? , minuuttia? , sekunnit? , millisekunteja?
- Onko minulla useita päivämäärä/aika-sarakkeita, koska haluan laskea niiden välisen eron vai vain koostaa tiedot esimerkiksi vuoden, kuukauden tai vuosineljänneksen mukaan?
Vastaustapa kuhunkin näistä kysymyksistä määrittää päivämäärä/aika-sarakkeen käsittelyvaihtoehdot.
Kaikki nämä ratkaisut edellyttävät SQL-kyselyn muokkaamista. Voit helpottaa kyselyn muokkaamista suodattamalla pois vähintään yhden sarakkeen jokaisesta taulukosta. Kun suodatat sarakkeen pois, muutat kyselyn rakenteen lyhennetystä muodosta (SELECT *) SELECT-lausekkeeksi, joka sisältää täydelliset sarakkeiden nimet, joita on helpompi muokata.
Tarkastellaan sinulle luotuja kyselyjä. Taulukon ominaisuudet -valintaikkunassa voit siirtyä kyselyeditoriin ja nähdä kunkin taulukon nykyisen SQL-kyselyn.
Valitse Taulukon ominaisuudet -kohdassa Kyselyeditori.
Kyselyeditori näyttää taulukon täyttämiseen käytetyn SQL-kyselyn. Jos olet suodattanut jonkin sarakkeen pois tuonnin aikana, kysely sisältää täydelliset sarakkeiden nimet:
Jos toit taulukon kokonaisuudessaan poistamatta minkään sarakkeen valintaa tai käyttämättä mitään suodattimia, kysely näkyy muodossa "Valitse * kohteesta", jota on vaikeampi muokata:
|
|---|
SQL-kyselyn muokkaaminen
Nyt kun tiedät, miten löydät kyselyn, voit muokata sitä ja pienentää mallin kokoa entisestään.
- Jos et tarvitse valuutta- tai desimaalitietoja sisältävien sarakkeiden desimaalit, poista desimaalit seuraavan syntaksin avulla:
"SELECT ROUND([Decimal_column_name],0)... .”
Jos tarvitset sentit mutta et sentin murto-osia, korvaa 0 kahdella. Jos käytät negatiivisia lukuja, voit pyöristää yksiköihin, kymmeniin, satoihin jne. - Jos sinulla on dbo-niminen Datetime-sarake. Iso taulukko. [Päivämäärä ja aika] etkä tarvitse aikaosaa, voit poistaa kellonajan syntaksin avulla:
"VALITSE CAST (dbo. Iso taulukko. [Päivämäärä ja aika] as päivämäärä) AS [päivämäärä ja aika]) " - Jos sinulla on dbo-niminen Datetime-sarake. Iso taulukko. [Päivämäärä ja aika] ja tarvitset sekä päivämäärä- että aikaosat, käytä SQL-kyselyssä useita sarakkeita yhden päivämäärän ja ajan sarakkeen sijaan:
"VALITSE CAST (dbo. Iso taulukko. [Päivämäärä ja aika] päivämääränä ) AS [päivämäärä ja aika],
DatePart (hh, dbo. Iso taulukko. [Päivämäärä ja aika]) muodossa [Päivämäärä ja aika Tunnit],
DatePart (mi, dbo. Iso taulukko. [Päivämäärä ja aika]) muodossa [Päivämäärä ja aika minuutit],
DatePart (ss, dbo. Iso taulukko. [Päivämäärä ja aika]) muodossa [Päivämäärä ja aika sekuntia].
DatePart (MS, DBO. Iso taulukko. [Päivämäärä ja aika]) muodossa [Päivämäärä ja aika millisekunnit]"
Käytä niin monta saraketta kuin tarvitset kunkin osan tallentamiseen eri sarakkeisiin. - Jos tarvitset tunteja ja minuutteja ja haluat ne yhdessä aikasarakkeessa, voit käyttää seuraavaa syntaksia:
Timefromparts(datepart(hh, dbo. Iso taulukko. [Date Time]), DatePart (mm, dbo. Iso taulukko. [Päivämäärä ja aika])) muodossa [Päivämäärä, Kellonaika, TuntiMinuutti] - Jos sinulla on kaksi päivämäärän ja ajan saraketta, kuten [Alkamisaika] ja [Lopetusaika], ja tarvitset niiden välisen aikaeron sekunteina sarake nimeltä [Kesto], poista molemmat sarakkeet luettelosta ja lisää:
"datediff(ss;[Alkamispäivä];[Päättymispäivä]) muodossa [Kesto]"
Jos käytät avainsanaa ms asemesta ss, saat keston millisekunteina
Laskettujen DAX-mittojen käyttäminen sarakkeiden sijaan
Jos olet käyttänyt DAX-lausekekieltä aiemmin, saatat jo tietää, että laskettujen sarakkeiden avulla johdetaan uusia sarakkeita mallin jonkin muun sarakkeen perusteella, kun taas lasketut mitat määritetään mallissa kerran, mutta vain silloin, kun niitä käytetään Pivot-taulukossa tai muussa raportissa.
Yksi muistinsäästötekniikka on korvata tavalliset tai lasketut sarakkeet lasketuilla mitoilla. Klassinen esimerkki on Yksikköhinta, Määrä ja Summa. Jos sinulla on kaikki kolme, voit säästää tilaa säilyttämällä vain kaksi ja laskemalla kolmannen DAX-kielellä.
Mitkä 2 saraketta kannattaa säilyttää?
Säilytä yllä olevassa esimerkissä määrä ja yksikköhinta. Näillä kahdella on vähemmän arvoja kuin summalla. Jos haluat laskea kokonaissumman, lisää laskettu mittayksikkö, kuten:
"TotalSales:=sumx('Myynti-taulukko','Myynti-taulukko'[Yksikköhinta]*'Myyntitaulukko'[Määrä])"
Lasketut sarakkeet ovat kuin tavallisia sarakkeita, koska molemmat vievät tilaa mallissa. Lasketut mitat sen sijaan lasketaan lennossa, eivätkä ne vie tilaa.
Yhteenveto
Tässä artikkelissa puhuimme useista lähestymistavoista, joiden avulla voit rakentaa muistia säästävän mallin. Voit pienentää tiedostokokoa ja tietomallin muistivaatimuksia vähentämällä sarakkeiden ja rivien kokonaismäärää sekä kussakin sarakkeessa näkyvien yksilöllisten arvojen määrää. Tässä on joitain menetelmiä, joita olemme käyneet läpi:
- Sarakkeiden poistaminen on tietysti paras tapa säästää tilaa. Päätä, mitä sarakkeita todella tarvitset.
- Joskus voit poistaa sarakkeen ja korvata sen taulukossa olevalla lasketulla mittayksiköllä.
- Et ehkä tarvitse kaikkia taulukon rivejä. Voit suodattaa rivit pois ohjatussa taulukon tuonnissa.
- Yleisesti ottaen yksittäisen sarakkeen pilkkominen useisiin erillisiin osiin on hyvä tapa vähentää yksilöllisten arvojen määrää sarakkeessa. Kussakin osassa on pieni määrä yksilöllisiä arvoja, ja yhteenlaskettu summa on pienempi kuin alkuperäinen yhdistetty sarake.
- Usein tarvitset myös erillisiä osia käytettäväksi osittajina raporteissasi. Tarvittaessa voit luoda hierarkioita osista, kuten Tunnit, Minuutit ja Sekunnit.
- Usein sarakkeissa on enemmän tietoja kuin tarvitaan. Oletetaan esimerkiksi, että sarakkeeseen tallennetaan desimaalit, mutta olet käyttänyt muotoilua, joka piilottaa kaikki desimaalit. Pyöristäminen voi olla erittäin tehokas keino pienentää numeerisen sarakkeen kokoa.
Nyt kun olet tehnyt voitavasi työkirjan koon pienentämiseksi, harkitse myös työkirjan koon optimointityökalun käyttämistä. 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ä