Excelissä voit luoda tietomalleja, jotka sisältävät miljoonia rivejä, ja suorittaa sitten tehokkaita tietojen analyyseja näitä malleja vastaan. Tietomalleja voi luoda Power Pivot apuohjelman kanssa tai ilman sitä, mikä tukee saman työkirjan useita Pivot-taulukoita, kaavioita ja Power View -visualisointeja.
Vaikka voit helposti luoda valtavia tietomalleja Excelissä, siihen on useita syitä. Ensinnäkin suuret mallit, jotka sisältävät useita taulukoita ja sarakkeita, ovat ylikuormittavia useimmissa analyyseissä, ja ne tekevät hankalasta kenttäluettelosta. Toiseksi suuret mallit käyttävät arvokasta muistia, mikä vaikuttaa negatiivisesti muihin sovelluksiin ja raportteihin, joilla on samat järjestelmäresurssit. Lopuksi Microsoft 365 sekä SharePoint Online että Excel Web App rajoittavat Excel-tiedoston koon 10 megatavuun. Jos työkirjan tietomallit sisältävät miljoonia rivejä, 10 Mt:n raja on melko nopea. Katso Tietomallin määritykset ja rajoitukset.
Tässä artikkelissa kerrotaan, miten voit luoda tiiviisti rakennetun mallin, jota on helpompi käsitellä ja joka käyttää vähemmän muistia. Tehokkaiden mallien suunnittelun parhaiden käytäntöjen oppiminen kannattaa kaikissa luomissasi ja käyttämissäsi mallissa riippumatta siitä, tarkasteletko mallia Excelissä, Microsoft 365 SharePoint Onlinessa, Office Online Serverissä tai 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 optimointitoiminto.
Tämän artikkelin sisältö
Pakkaussuhteet ja muistissa oleva analytiikkamoduuli
Excelin tietomallit käyttävät muistinsisäistä analytiikkamoduulia tietojen tallentamiseen muistiin. Moottori käyttää tehokkaita pakkaustekniikoita tallennusvaatimusten pienentämiseksi ja pienentää tulosjoukkoa, kunnes se on murto-osa alkuperäisestä koostaan.
Tietomallin voi odottaa olevan keskimäärin 7–10 kertaa pienempi kuin samat tiedot sen alkuperäpisteessä. Jos esimerkiksi tuot 7 Mt tietoja SQL Server -tietokannasta, Excelin tietomalli voi olla enintään 1 Mt. Todellisuudessa saavutettu pakkausaste riippuu ensisijaisesti kunkin sarakkeen yksilöllisten arvojen määrästä. Mitä yksilöllisempiä arvoja, sitä enemmän muistia tarvitaan niiden tallentamiseen.
Miksi puhumme pakkaamisesta ja yksilöllisten arvojen arvosta? Koska tehokkaan muistin käyttöä pienentävän mallin luominen on pakkaamisen suurentamista, helpoin tapa on poistaa kaikki tarpeettomat sarakkeet, varsinkin jos sarakkeissa on suuri määrä yksilöllisiä arvoja.
Huomautus: Yksittäisten sarakkeiden tallennustarpeiden erot voivat olla valtavat. Joissakin tapauksissa on parempi, että on useita sarakkeita, joissa on pieni määrä yksilöllisiä arvoja, kuin yksi sarake, jossa on suuri määrä yksilöllisiä arvoja. Datetime-optimoinnit-osiossa käsitellään tätä tekniikkaa yksityiskohtaisesti.
Mikään ei voita olematonta saraketta vähäisen muistin käytön vuoksi
Muistia tehostavin sarake on sarake, jota et ole koskaan tuonut. Jos haluat luoda tehokkaan mallin, katso jokaista saraketta ja kysy itseltäsi, vaikuttaako se analyysiin, 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. Tietovarastossa on yleistä löytää ETL-prosessien artefakteja, jotka lataavat ja päivittävät tietoja varastossa. Sarakkeet, kuten "luo päivämäärä", "päivityspäivä" ja "ETL-suoritus", luodaan, kun tiedot ladataan. Mallissa ei tarvita mitään näistä sarakkeista, ja ne pitäisi poistaa, kun tuot tietoja.
Toisessa esimerkissä perusavainsarakkeen jättäminen pois tuotaessa faktataulukkoa.
Monissa taulukoissa, kuten faktataulukoissa, on perusavaimet. Useimmissa taulukoissa, kuten asiakas-, työntekijä- tai myyntitietoja sisältävissä taulukoissa, haluat taulukon perusavaimen, jotta voit luoda sen avulla yhteyksiä malliin.
Faktataulukot ovat erilaisia. Faktataulukossa perusavainta käytetään kunkin rivin yksilölliseen tunnistamiseen. Vaikka se on tarpeen normalisointia varten, se ei ole yhtä hyödyllinen tietomallissa, jossa haluat käyttää vain niitä sarakkeita, joita käytetään analysointiin tai taulukoiden yhteyksien määrittämiseen. Tästä syystä, kun tuot tietoja faktataulukosta, älä sisällytä sen perusavainta. Faktataulukon perusavaimet kuluttavat valtavasti tilaa mallissa, mutta niistä ei ole hyötyä, koska niitä ei voi käyttää suhteiden luomiseen.
Huomautus: Tietovarastoissa ja moniulotteisissa tietokannoissa enimmäkseen numeerisista tiedoista koostuvia suuria taulukoita kutsutaan usein faktataulukoiksi. Faktataulukot sisältävät yleensä liiketoiminnan suorituskyky- tai tapahtumatietoja, kuten myynti- ja kustannustietopisteitä, jotka on koottu ja kohdistettu organisaatioyksiköihin, tuotteisiin, markkinasegmentteihin ja maantieteellisiin alueisiin. Kaikki faktataulukon sarakkeet, jotka sisältävät yritystietoja tai joita voidaan käyttää muihin taulukoihin tallennettujen tietojen ristiviittaukseen, olisi sisällytettävä malliin tietojen analysointia varten. Sarake, jonka haluat jättää pois, on faktataulukon perusavainsarake, joka koostuu ainutkertaista arvoista, jotka ovat olemassa vain faktataulukossa ja muualla. Koska faktataulukot ovat niin valtavia, jotkin mallin tehokkuuden suurimmista voitoista johtuvat rivien tai sarakkeiden jättämisestä faktataulukoiden ulkopuolelle.
Tarpeettomien sarakkeiden jättäminen pois
Tehokkaat mallit sisältävät vain ne sarakkeet, joita todellisuudessa tarvitset työkirjassasi. Jos haluat hallita, mitkä sarakkeet sisältyvät malliin, sinun on tuotettava tiedot Power Pivot -apuohjelman ohjatun taulukon tuomisen avulla Excelin Tuo tiedot -valintaikkunan sijaan.
Kun käynnistät ohjatun taulukon tuonnin, valitset tuotavat taulukot.
Voit napsauttaa kunkin taulukon Esikatselu-& Suodatin-painiketta ja valita taulukon osat, joita todella tarvitset. Suosittelemme, että poistat ensin kaikkien sarakkeiden valinnan ja jatkat sitten haluamiesi sarakkeiden tarkistamista sen jälkeen, kun olet pohtinut, tarvitaanko niitä analyysiin.
Entäpä vain tarvittavien rivien suodattaminen?
Monet yritystietokantojen ja tietovarastojen taulukot sisältävät historiatietoja, jotka on kertynyt pitkäksi aikaa. Lisäksi saatat huomata, että sinua kiinnostavat taulukot sisältävät tietoja liiketoiminnan osa-alueista, joita ei tarvita tarkan analyysin yhteydessä.
Ohjatun taulukon tuomisen avulla voit suodattaa pois historiatiedot tai toisiinsa liittymättömät tiedot ja säästää siten paljon tilaa mallissa. Seuraavassa kuvassa päivämääräsuodatinta käytetään vain sellaisten rivien noutamiseen, jotka sisältävät kuluvan vuoden tietoja, lukuun ottamatta historiatietoja, joita ei tarvita.
Entä jos tarvitsemme sarakkeen; Voimmeko silti pienentää sen avaruuskustannuksia?
Voit käyttää muutamia lisätekniikoita, joiden avulla voit tehdä sarakkeesta paremman pakkausehdon. Muista, että pakkaamiseen vaikuttavan sarakkeen ainoa ominaisuus on yksilöllisten arvojen määrä. Tässä osassa kerrotaan, miten joitakin sarakkeita voidaan muokata yksilöllisten arvojen määrän vähentämiseksi.
Päivämääräsarakkeiden muokkaaminen
Monissa tapauksissa Datetime-sarakkeet vievät paljon tilaa. Onneksi tämän tietotyypin tallennusvaatimuksia voidaan pienentää monella tavalla. Tekniikat vaihtelevat sarakkeen käyttötavan ja SQL-kyselyiden luomisen mukavuustason mukaan.
Päivämääräsarakkeissa on päivämääräosa ja kellonaika. Kun kysyt itseltäsi, tarvitsetko sarakkeen, kysy sama kysymys useita kertoja Datetime-sarakkeelle:
-
Tarvitsenko aikaosan?
-
Tarvitsenko aikaosan tuntitasolla? pöytäkirja? Sekuntia? Millisekuntia?
-
Onko minulla useita Datetime-sarakkeita, koska haluan laskea niiden välisen eron tai vain koostaa tiedot vuoden, kuukauden, vuosineljänneksen ja niin edelleen mukaan.
Se, miten vastaat näihin kysymyksiin, määrittää vaihtoehdot Datetime-sarakkeen käsittelyyn.
Kaikki nämä ratkaisut edellyttävät SQL-kyselyn muokkaamista. Kyselyn muokkaaminen helpottuu suodattamalla pois vähintään yksi sarake jokaisessa taulukossa. Suodattamalla sarakkeen voit muuttaa kyselyn rakenteen lyhennetystä muotoilusta (SELECT *) SELECT-lausekkeeksi, joka sisältää täydelliset sarakenimet, joita on paljon helpompi muokata.
Tutustutaanpa kyselyihin, jotka on luotu puolestasi. Taulukon ominaisuudet -valintaikkunassa voit siirtyä kyselyeditoriin ja tarkastella kunkin taulukon nykyistä SQL-kyselyä.
Valitse Taulukon ominaisuudet -kohdassa Kyselyeditori.
Kyselyeditori näyttää taulukon täyttämiseen käytetyn SQL-kyselyn. Jos suodatit minkä tahansa sarakkeen tuonnin aikana, kysely sisältää täydelliset sarakenimet:
|
Jos sen sijaan toit taulukon kokonaisuudessaan poistamatta sarakkeen valintaa tai käyttämättä suodatinta, kysely näkyy muodossa "Valitse * kohteesta", jota on vaikeampi muokata:
|
SQL-kyselyn muokkaaminen
Nyt kun osaat etsiä kyselyn, voit muokata sitä ja pienentää mallin kokoa entisestään.
-
Jos et tarvitse desimaalilukuja valuutta- tai desimaalitietoja sisältävissä sarakkeissa, poista desimaalit tämän syntaksin avulla:
"VALITSE PYÖRISTÄ([Decimal_column_name],0)... .”
Jos tarvitset sentin, mutta et sentin murtolukuja, korvaa 0 luvulla 2. Jos käytät negatiivisia lukuja, voit pyöristää yksiköihin, kymmeniin, satoihin jne.
-
Jos sinulla on Päivämääräaika-sarake nimeltä dbo. Iso pöytä. [Päivämääräaika] etkä tarvitse Aika-osaa, käytä syntaksia ajan poistamiseen:
"SELECT CAST (dbo. Iso pöytä. [Päivämääräaika] päivämääränä) AS [Päivämääräaika]) "
-
Jos sinulla on Päivämääräaika-sarake nimeltä dbo. Iso pöytä. [Päivämääräaika] ja tarvitset sekä Päivämäärä- että Aika-osat, käytä SQL-kyselyssä useita sarakkeita yksittäisen Datetime-sarakkeen sijaan:
"SELECT CAST (dbo. Iso pöytä. [Päivämääräaika] päivämääränä ) AS [Päivämääräaika],
datepart(hh, dbo. Iso pöytä. [Päivämääräaika]) muodossa [Date Time Hours],
datepart(mi, dbo. Iso pöytä. [Päivämääräaika]) muodossa [Date Time Minutes]
datepart(ss, dbo). Iso pöytä. [Päivämääräaika]) muodossa [Päivämäärän aika sekuntia],
datepart(ms, dbo. Iso pöytä. [Päivämääräaika]) as [Date Time Milliseconds]"
Käytä niin monta saraketta kuin haluat tallentaa kukin osa eri sarakkeisiin.
-
Jos tarvitset tunteja ja minuutteja ja haluat käyttää niitä yhdessä kertasarakkeena, voit käyttää syntaksia:
Timefromparts(datepart(hh, dbo. Iso pöytä. [Päivämääräaika]), datepart(mm, dbo). Iso pöytä. [Päivämääräaika])) muodossa [Date Time HourMinute]
-
Jos sinulla on kaksi päivämääränaikasaraketta, kuten [Aloitusaika] ja [Päättymisaika], ja tarvitset niiden välisen aikaeron sekunteina sarakkeena nimeltä [Kesto], poista molemmat sarakkeet luettelosta ja lisää:
"datediff(ss;[Aloituspäivä],[Päättymispäivä]) muodossa [Kesto]"
Jos käytät avainsanaa ms ss:n sijaan, saat keston millisekunteina
DAX-laskettujen mittojen käyttäminen sarakkeiden sijaan
Jos olet käyttänyt DAX-lausekkeen kieltä aiemmin, saatat jo tietää, että laskettuja sarakkeita käytetään uusien sarakkeiden määrittämiseen mallin jonkin muun sarakkeen perusteella, kun taas lasketut mitat määritetään kerran mallissa, mutta ne arvioidaan vain, kun niitä käytetään Pivot-taulukossa tai muussa raportissa.
Yksi muistinsäästömenetelmä on korvata tavalliset tai lasketut sarakkeet lasketuilla mittayksiköillä. Perinteinen 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:n avulla.
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. Jos haluat laskea summan, lisää laskettu mittayksikkö, kuten:
"TotalSales:=sumx('Myyntitaulukko';'Myyntitaulukko'[Yksikköhinta]*'Myyntitaulukko'[Määrä])"
Lasketut sarakkeet ovat kuin tavallisia sarakkeita, jotka vievät tilaa mallissa. Lasketut mitat lasketaan sen sijaan lennossa, eivätkä ne vie tilaa.
Yhteenveto
Tässä artikkelissa puhuimme useista lähestymistavoista, joiden avulla voit luoda muistia säästävämmän mallin. Voit pienentää tietomallin tiedostokokoa ja muistivaatimuksia pienentämällä sarakkeiden ja rivien kokonaismäärää sekä kussakin sarakkeessa näkyvien yksilöllisten arvojen määrää. Seuraavassa on joitakin tekniikoita, joita käsittelimme:
-
Sarakkeiden poistaminen on tietenkin paras tapa säästää tilaa. Päätä, mitä sarakkeita todella tarvitset.
-
Joskus voit poistaa sarakkeen ja korvata sen lasketulla mittayksiköllä taulukossa.
-
Et ehkä tarvitse kaikkia taulukon rivejä. Voit suodattaa rivit pois ohjatussa taulukon tuomisessa.
-
Yleensä yksittäisen sarakkeen jakaminen useaan eri osaan on hyvä tapa vähentää sarakkeen yksilöllisten arvojen määrää. Jokaisessa osassa on pieni määrä yksilöllisiä arvoja, ja yhteenlaskettu summa on pienempi kuin alkuperäinen yhdistetty sarake.
-
Monissa tapauksissa tarvitset myös erilliset osat, joita voit käyttää raporteissa osittajiena. Voit tarvittaessa luoda hierarkioita esimerkiksi tunneista, minuuteistä ja sekunneista.
-
Sarakkeet sisältävät usein enemmän tietoja kuin tarvitset. Oletetaan esimerkiksi, että sarakkeeseen tallennetaan desimaalit, mutta olet käyttänyt muotoilua kaikkien desimaalien piilottamiseen. Pyöristäminen voi olla erittäin tehokasta numeerisen sarakkeen koon pienentämisessä.
Nyt kun olet tehnyt kaikkesi pienentääksesi työkirjan kokoa, harkitse myös Työkirjan koon optimointi -toiminnon suorittamista. Se analysoi Excel-työkirjan ja pakkaa sen entistä pienempään kokoon, jos se on mahdollista. Lataa Työkirjan koon optimointitoiminto.
Aiheeseen liittyvät linkit
Tietomallin määritykset ja rajoitukset
PowerPivot: tehokas tietojen analysointi ja tietomallien luominen Excelissä
