Rakenduses Excel 2013 või uuemas versioonis saate luua miljoneid ridu sisaldavaid andmemudeleid ja seejärel neid mudeleid võimsalt analüüsida. Andmemudeleid saab luua koos Power Pivot lisandmooduliga või ilma, et toetada kõiki samas töövihikus olevaid PivotTable-liigendtabeleid, diagramme ja Power View' visualiseeringuid.
Märkus.: Selles artiklis kirjeldatakse Excel 2013 andmemudeleid. Siiski on versioonis Excel 2013 kasutusele võetud andmemudelite ja Power Pivoti funktsioonid samad ka versioonis Excel 2016. Nende Exceli versioonide vahel on tegelikult vähe erinevusi.
Kuigi Excelis saab hõlpsalt luua suuri andmemudeleid, on selleks mitu põhjust. Esiteks on suured mudelid, mis sisaldavad palju tabeleid ja veerge, enamiku analüüside jaoks ülekoormatud ja moodustavad tülika väljaloendi. Teiseks kasutavad suured mudelid väärtuslikku mälu, mis mõjutab negatiivselt teisi rakendusi ja aruandeid, millel on samad süsteemiressursid. Lõpuks piiravad Microsoft 365 nii SharePoint Online kui ka Excel Web App Exceli faili maht 10 MB-ni. Miljoneid ridu sisaldavate töövihiku andmemudelite korral läheb 10 MB piirang üsna kiiresti. Vt Andmemudeli spetsifikatsioon ja limiidid.
Sellest artiklist saate teada, kuidas luua tihedalt koostatud mudelit, millega on lihtsam töötada ja mis kasutab vähem mälu. Tõhusa mudelikujunduse parimate tavade tundmaõppimiseks kulub iga loodud ja kasutatava mudeli eest ( olgu see siis Excel 2013-s, Microsoft 365 SharePoint Online'is, Office Web Apps Serveris või rakenduses SharePoint 2013) juba olemas.
Kaaluge ka töövihiku mahu optimeerimise funktsiooni kasutamist. See analüüsib teie Exceli töövihikut ja võimalusel tihendab seda. Laadige töövihiku mahu optimeerija alla.
Selle artikli teemad
Compression ratios and the in-memory analytics engine
Exceli andmemudelid salvestavad andmeid mälus oleva analüütikamootori abil. Mootor rakendab salvestusnõuete vähendamiseks võimsaid tihendustehnikaid, vähendades tulemuste kogumit, kuni see on murdosa algsest suurusest.
Keskmiselt võite eeldada, et andmemudel on lähtepunktis samadest andmetest 7–10 korda väiksem. Näiteks kui impordite SQL Server andmebaasist 7 MB andmeid, võib Exceli andmemudel olla kuni 1 MB. Tegelikult saavutatav tihendusaste sõltub eelkõige iga veeru kordumatute väärtuste arvust. Mida rohkem kordumatuid väärtusi, seda rohkem mälu on vaja nende talletamiseks.
Miks me räägime tihendamisest ja kordumatutest väärtustest? Kuna mälukasutust minimeeriva tõhusa mudeli loomine seisneb tihendamise maksimeerimises ja lihtsaim viis seda teha, on eemaldada veerud, mida te tegelikult ei vaja, eriti kui need veerud sisaldavad suurt hulka kordumatuid väärtusi.
Märkus.: Üksikute veergude talletusnõuete erinevused võivad olla väga suured. Mõnel juhul on parem kasutada mitut veergu, millel on väike arv kordumatuid väärtusi, mitte ühte veergu, millel on suur arv kordumatuid väärtusi. Kuupäeva ja kellaaja optimeerimise jaotis hõlmab seda tehnikat üksikasjalikult.
Miski ei peksa vähese mälukasutuse korral olematut veergu
Kõige tõhusam mälukasutusega veerg on veerg, mida te pole varem importinud. Kui soovite koostada tõhusa mudeli, vaadake igat veergu ja küsige endalt, kas see aitab kaasa analüüsile, mida soovite teha. Kui see pole nii või te pole kindel, jätke see välja. Vajaduse korral saate alati uusi veerge hiljem lisada.
Kaks näidet veergudest, mis tuleks alati välja jätta
Esimene näide on seotud andmetega, mis pärinevad andmelaost. Andmelaos on tavaline leida ETL-protsesside artefakte, mis laadivad ja värskendavad lattu andmeid. Andmete laadimisel luuakse veerud nagu "loomiskuupäev", "värskendamiskuupäev" ja "ETL-käitus". Neid veerge pole mudelis vaja ja need tuleks andmete importimisel tühistada.
Teises näites jäetakse faktitabeli importimisel primaarvõtme veerg välja.
Paljudel tabelitel, sh faktitabelitel, on primaarvõtmed. Enamiku tabelite (nt kliendi-, töötaja- või müügiandmeid sisaldavate tabelite) jaoks soovite tabeli primaarvõtit, et saaksite seda kasutada mudelis seoste loomiseks.
Faktitabelid on erinevad. Faktitabelis kasutatakse primaarvõtit iga rea kordumatuks tuvastamiseks. Normeerimiseks on see vähem kasulik andmemudelis, kus soovite analüüsimiseks või tabeliseoste loomiseks kasutada ainult neid veerge. Sel põhjusel ärge lisage faktitabelist importimisel selle primaarvõtit. Faktitabeli primaarvõtmed tarbivad mudelis tohutul hulgal ruumi, kuid ei too siiski kasu, sest neid ei saa kasutada seoste loomiseks.
Märkus.: Andmeladudes ja mitmedimensioonilistes andmebaasides nimetatakse enamasti arvandmetest koosnevaid suuri tabeleid sageli faktitabeliteks. Faktitabelid sisaldavad tavaliselt ärijõudluse või tehingu andmeid ,nt müügi ja kulu andmepunkte, mis on koondatud ja joondatud organisatsiooniüksuste, toodete, turusegmentide, geograafiliste piirkondade jne järgi. Andmeanalüüsi toetamiseks tuleks mudelisse kaasata kõik äriandmeid sisaldavad või teistes tabelites talletatavate ristviiteandmeteks kasutatavad faktitabeli veerud. Veerg, mille soovite välistada, on faktitabeli primaarvõtme veerg, mis koosneb kordumatutest väärtustest, mis on olemas ainult faktitabelis ja mujal. Kuna faktitabelid on nii suured, tuletatakse osa mudeli tõhususe suurimast kasust faktitabelite ridade või veergude väljajätmisest.
Mittevajalike veergude välistamine
Tõhusad mudelid sisaldavad ainult neid veerge, mida teil oma töövihikus tegelikult vaja läheb. Kui soovite määrata, millised veerud on mudelisse kaasatud, peate andmete importimiseks kasutama Power Pivoti lisandmooduli tabeliimpordiviisardit, mitte Exceli dialoogiboksi Andmete importimine.
Tabeliimpordiviisardi käivitamisel saate valida imporditavad tabelid.
Iga tabeli puhul saate klõpsata nuppu Eelvaade & Filter ja valida tabeli osad, mida tegelikult vajate. Soovitame esmalt kõik veerud tühjendada ja seejärel soovitud veerge kontrollida, kaaludes, kas need on analüüsi jaoks vajalikud.
Aga ainult vajalike ridade filtreerimine?
Paljud ettevõtteandmebaaside ja andmeladude tabelid sisaldavad pika aja jooksul kogunenud ajaloolisi andmeid. Lisaks võite leida, et tabelid, mis teid huvitavad, sisaldavad teavet nende ettevõtete kohta, mida pole teie konkreetse analüüsi jaoks vaja.
Tabeliimpordiviisardi abil saate ajaloolisi või mitteseotud andmeid välja filtreerida ja seega mudelis palju ruumi kokku hoida. Järgmisel pildil kasutatakse kuupäevafiltrit ainult jooksva aasta andmeid sisaldavate ridade toomiseks (v.a ajaloolised andmed, mida pole vaja).
Mida teha, kui vajame veergu; Kas me saame selle ruumikulu ikkagi vähendada?
Veeru tihendamiseks parema kandidaadi saamiseks saate kasutada mõnda muud võtet. Pidage meeles, et tihendamist mõjutava veeru ainus omadus on kordumatute väärtuste arv. Sellest jaotisest leiate teavet selle kohta, kuidas saab mõnda veergu kordumatute väärtuste arvu vähendamiseks muuta.
Kuupäeva- ja kellaajaveergude muutmine
Paljudel juhtudel võtavad veerud Kuupäev ja kellaaeg palju ruumi. Õnneks on selle andmetüübi salvestusruuminõuete vähendamiseks mitu võimalust. Tehnikad varieeruvad olenevalt sellest, kuidas te veergu kasutate, ja teie mugavuse tasemest SQL-päringute loomisel.
Kuupäeva- ja kellaajaveerud sisaldavad kuupäevaosa ja kellaaega. Kui küsite endalt, kas teil on veergu vaja, esitage sama küsimus veeru Kuupäev ja kellaaeg kohta mitu korda.
-
Kas ma vajan ajaosa?
-
Kas ma vajan ajaosa tundide tasemel? Minutit? Sekundit? Millisekundit?
-
Kas mul on mitu veergu Kuupäev ja kellaaeg, kuna soovin arvutada nende vahe või lihtsalt andmeid aasta, kuu, kvartali jne alusel liita.
Igale küsimusele vastamise viis määrab ära veeru Kuupäev ja kellaaeg käsitlemise võimalused.
Kõik need lahendused nõuavad SQL-päringu muutmist. Päringu muutmise lihtsustamiseks peaksite iga tabeli vähemalt ühe veeru välja filtreerima. Veeru filtreerimise abil saate muuta päringu konstruktsiooni lühendatud vormingust (SELECT *) SELECT-lauseks, mis sisaldab täielikke veerunimesid, mida on palju lihtsam muuta.
Vaatame teie jaoks loodud päringuid. Dialoogiboksis Tabeliatribuudid saate aktiveerida päringuredaktori ja vaadata iga tabeli praegust SQL-päringut.
Valige jaotises Tabeliatribuudid nupp Päringuredaktor.
Päringuredaktor kuvatakse tabeli asustamiseks kasutatav SQL-päring. Kui filtreerisite importimisel mõne veeru välja, sisaldab päring täielikke veerunimesid.
|
Kui aga importisite tabeli tervikuna ilma veergu tühjendamata või filtrit rakendamata, kuvatakse päring kujul "Vali * tabelist", mida on keerulisem muuta:
|
SQL-päringu muutmine
Nüüd, kui teate, kuidas päringut leida, saate seda muuta, et mudeli mahtu veelgi vähendada.
-
Kui teil pole kümnendkohti vaja, kasutage kümnendandmeid sisaldavate veergude korral kümnendkohtade eemaldamiseks järgmist süntaksit.
"SELECT ROUND([Decimal_column_name],0)... .”
Kui vajate sente, kuid mitte sente, asendage 0 arvuga 2. Kui kasutate negatiivseid arve, saate ümardada need ühikuteks, kümneteks, sadadesse jne.
-
Kui teil on veerg Kuupäev ja kellaaeg nimega dbo. Bigtable. [Kuupäev kellaaeg] ja te ei vaja osa Kellaaeg, kasutage kellaaja eemaldamiseks süntaksit:
"SELECT CAST (dbo. Bigtable. [Kuupäev kellaaeg] kuupäevana) AS [Kuupäev kellaaeg]) "
-
Kui teil on veerg Kuupäev ja kellaaeg nimega dbo. Bigtable. [Kuupäev kellaaeg] ja vajate nii kuupäeva- kui ka kellaajaosa, kasutage ühe kuupäeva- ja kellaajaveeru asemel SQL-päringus mitut veergu:
"SELECT CAST (dbo. Bigtable. [Kuupäev Kellaaeg] kuupäevana ) AS [Kuupäev Kellaaeg],
datepart(hh, dbo. Bigtable. [Kuupäev kellaaeg]) as [Kuupäeva kellaaja tunnid],
datepart(mi, dbo. Bigtable. [Kuupäev kellaaeg]) as [Kuupäeva-kellaaja minutid],
datepart(ss, dbo. Bigtable. [Kuupäev kellaaeg]) as [Kuupäeva ja kellaaja sekundid],
datepart(ms, dbo. Bigtable. [Kuupäev kellaaeg]) as [Date Time Milliseconds]"
Kasutage nii palju veerge, kui vaja, et talletada iga osa eraldi veergudesse.
-
Kui vajate tunde ja minuteid ning eelistate neid ühe kellaajaveeruna, saate kasutada süntaksit:
Timefromparts(datepart(hh, dbo. Bigtable. [Kuupäev Kellaaeg]), datepart(mm, dbo. Bigtable. [Kuupäev kellaaeg])) as [Date Time HourMinute]
-
Kui teil on kaks kuupäeva- ja kellaajaveergu, näiteks [Alguskellaaeg] ja [Lõppkellaaeg] ning vajate nendevahelist ajaerinevust sekundites veeruna [Kestus], eemaldage mõlemad veerud loendist ja lisage:
"datediff(ss,[Alguskuupäev],[Lõppkuupäev]) as [Duration]"
Kui kasutate ss-i asemel märksõna ms, kuvatakse kestus millisekundites
DAX-i arvutatud mõõtu kasutamine veergude asemel
Kui olete varem DAX-i avaldisekeelega töötanud, võite juba teada, et arvutuslikke veerge kasutatakse uute veergude tuletamiseks mõne muu mudeli veeru põhjal, arvutatud mõõdud aga määratletakse mudelis üks kord, kuid neid hinnatakse ainult PivotTable-liigendtabelis või mõnes muus aruandes.
Üks mälusäästmismeetod on tavaliste või arvutatud veergude asendamine arvutatud mõõtidega. Klassikaline näide on Ühiku hind, Kogus ja Kogusumma. Kui teil on kõik kolm, saate ruumi kokku hoida, säilitades ainult kaks ja arvutades DAX-i abil kolmanda.
Millised 2 veergu peaksite säilitama?
Säilitage ülaltoodud näites Kogus ja Ühiku hind. Nendel kahel on kogusummast vähem väärtusi. Kogusumma arvutamiseks lisage arvutatud mõõt, näiteks:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Arvutatud veerud on nagu tavalised veerud selles, et mõlemad võtavad mudelis ruumi. Arvutuslikud mõõdud arvutatakse hüvitusel ja need ei võta ruumi.
Kokkuvõte
Selles artiklis rääkisime mitmest lähenemisviisist, mis aitavad teil luua tõhusama mälukasutusega mudeli. Andmemudeli failimahu ja mälunõuete vähendamiseks saate vähendada veergude ja ridade koguarvu ning igas veerus kuvatavate kordumatute väärtuste arvu. Siin on mõned tehnikad, mida me käsitlesime.
-
Veergude eemaldamine on muidugi parim viis ruumi säästmiseks. Otsustage, milliseid veerge te tegelikult vajate.
-
Mõnikord saate veeru eemaldada ja asendada selle tabelis oleva arvutatud mõõduga.
-
Võimalik, et te ei vaja kõiki tabeliridu. Tabeliimpordiviisardis saate ridu välja filtreerida.
-
Üldiselt on ühe veeru tükeldamine mitmeks eristatavaks osaks hea viis veeru kordumatute väärtuste arvu vähendamiseks. Igal osal on väike arv kordumatuid väärtusi ja kogusumma on algsest ühendatud veerust väiksem.
-
Paljudel juhtudel on aruannetes tükelditena kasutamiseks vaja ka eri osi. Vajaduse korral saate luua hierarhiaid osadest nagu Tunnid, Minutid ja Sekundid.
-
Sageli sisaldavad veerud rohkem teavet, kui vajate. Oletagem näiteks, et veerus talletatakse kümnendkohti, kuid olete rakendanud kõigi kümnendkohtade peitmiseks vormingu. Ümardamine võib olla väga tõhus arvveeru suuruse vähendamisel.
Nüüd, kui olete töövihiku mahu vähendamiseks teinud kõik, mida saate, võiksite kasutada ka töövihiku mahu optimeerijat. See analüüsib teie Exceli töövihikut ja võimalusel tihendab seda. Laadige töövihiku mahu optimeerija alla.
Seotud lingid
Andmemudeli spetsifikatsioon ja limiidid
PowerPivot: võimas andmeanalüüs ja andmete modelleerimine Excelis
