Exceli ja Power Pivoti lisandmooduli abil tõhusa Mälukasutusega andmemudeli loomine

NB! :  See artikkel on masintõlgitud, vaadake lahtiütlust. Selle artikli ingliskeelse versiooni leiate aadressilt (viiteks).

Rakenduses Excel 2013 või uuem versioon, saate luua andmemudeleid, mis sisaldab miljoneid ridu ja tehke võimas Andmeanalüüs need mudelid suhtes. Andmemudelid loomist või ilma Power Pivot lisandmooduli samas töövihikus mõnda arvu PivotTable-liigendtabelid, diagramme ja Power View visualiseeringuid toetamiseks.

Märkus. : Selles artiklis kirjeldatakse Excel 2013 andmemudelite. Siiski samade andmete modelleerimine ja Power Pivoti funktsioonid rakenduses Excel 2013 kasutusele ka rakendada Excel 2016. On tõhus väike vahe nende Exceli versioonides.

Kuigi saate hõlpsalt koostamine suur andmemudelite Excelis, pole mitmel põhjusel juurde. Esimese, suure mudelid, mis sisaldavad suurel hulgal tabelite ja veergude on overkill enamik analüüsi ja oleks tülikas Väljaloend. Teine, suure mudelid kasutada väärtuslik mälu, negatiivset mõju rakendused ja aruandeid, mis on sama süsteemi ressursside jagamiseks. Lõpuks Teenusekomplektis Office 365 nii SharePoint Online ja Excel Web App piirata 10 MB Exceli faili mahtu. Töövihiku andmete mudelid sisaldavad miljoneid ridu, kuvatakse tekib 10 MB limiit päris kiiresti. Artiklist andmemudelite spetsifikatsioon ja limiidid.

Sellest artiklist saate teada, kuidas koostada sellise tiheda struktuuriga mudel, millega on hõlpsam töötada ja mis kasutab vähem mälu. Kui leiate artikli lugemiseks aega ja teete enda selgeks, kuidas mudeleid tõhusamalt koostada, on sellest edaspidi palju abi kõigi mudelite korral, mida loote ja kasutate – sõltumata sellest, kas vaatate mudelit rakenduses Excel 2013, Office 365 SharePoint Online’is, Office Web Apps Serveris või rakenduses SharePoint 2013.

Kaaluge töötab ka töövihiku mahu optimeerija. See analüüsib Exceli töövihikus ja võimalusel tihendab edasi. Laadige töövihiku mahu optimeerija.

Selle artikli teemad

Tihendussuhted ja selle sisse Mälusisene analüüsimootor

Mitte miski vähese mälukasutuse osas veergu puudub

Kaks näidet veergudest, mis tuleks alati välja jätta

Mittevajalike veergude välistamine

Kuidas filtreerimisega lihtsalt vajalike ridade?

Mida teha, kui peame veeru; saate siiski oma ruumi kuidagi vähendada?

Kuupäeva ja kellaaja veergude muutmine

SQL-päringu muutmine

Dax-i abil arvutatud mõõtude veergude asemel

Millised 2 veergu alles jätta?

Kokkuvõte

Seotud lingid

Tihendusmäärad ja mälusisene analüüsimootor

Exceli andmemudelid kasutavad andmete mälus talletamiseks mälusisest analüüsimootorit. See mootor kasutab talletusruumi vajaduste vähendamiseks võimsaid tihendustehnikaid, kahandades tulemikomplekti murdosani selle algsest mahust.

Keskmiselt võib eeldada, et andmemudel on 7–10 korda väiksem kui samad andmed oma lähtekohas. Kui impordite näiteks 7 MB andmeid SQL Serveri andmebaasist, võib Exceli andmemudeli maht olla kõigest 1 MB või veelgi vähem. Tegelik tihendusaste sõltub peamiselt igas veerus olevate ainuväärtuste arvust. Mida rohkem on ainuväärtusi, seda rohkem talletusruumi läheb nende jaoks vaja.

Miks me räägime siin tihendusest ja ainuväärtustest? Sellepärast, et minimaalse mälukasutusega tõhusa mudeli koostamisel on tihendamise maksimeerimisel ülimalt suur osa ning kõige lihtsam viis seda teha on eemaldada kõik veerud, mida te tegelikult ei vaja – eriti juhul, kui need veerud sisaldavad palju ainuväärtusi.

Märkus. : Üksikveergude mäluvajaduse erinevused võivad olla äärmiselt suured. Vahel on parem kasutada ühe paljude ainuväärtustega veeru asemel mitut veergu, millest igaüks sisaldab vähesel arvul ainuväärtusi. Kuupäeva ja kellaaja optimeerimise jaotises on seda nippi täpsemalt kirjeldatud.

Mitte miski ei ületa vähese mälukasutuse osas veergu, mida pole olemas

Kõige tõhusama mälukasutusega veerg on see, mille jätsite targu kohe importimata. Kui soovite koostada tõhusat mudelit, vaadake iga veergu hoolikalt ja küsige endalt, kas see on teie kavandatava analüüsi jaoks oluline või vajalik. Kui mitte (või kui te pole kindel), jätke veerg välja. Kui see hiljem siiski vajalikuks osutub, saate mudelisse alati veerge juurde lisada.

Kaks näidet veergudest, mis tuleks alati välja jätta

Esimene näide on seotud andmetega, mis pärinevad andmeaidast. Andmeaidas leidub sageli aidas andmeid laadivate ja värskendavate ETL-i protsesside artefakte. Andmete laadimisel luuakse näiteks veerud "loomiskuupäev", "värskendamiskuupäev" ja "ETL-i käitus". Neid veerge pole andmemudelis vaja ja andmete importimisel tuleks need valikust välja jätta.

Teise näite korral jäetakse faktitabeli importimisel välja primaarvõtme veerg.

Paljudel tabelitel (sh faktitabelitel) on primaarvõtmed. Enamiku tabelite korral (nt tabelid, mis sisaldavad kliendi-, töötaja- või müügiandmeid) läheb seda primaarvõtit vaja mudelis seoste loomiseks.

Faktitabelitega seevastu on teine lugu. Faktitabelis kasutatakse primaarvõtit iga rea kordumatuks tuvastamiseks. Normaliseerimise jaoks on see nõutav, ent andmemudelis, kus teil läheb veerge vaja üksnes analüüsimiseks või tabeliseoste loomiseks, on sellest vähem kasu. Seetõttu ärge kaasake andmete faktitabelist importimisel tabeli primaarvõtit. Faktitabeli primaarvõtmed võtavad mudelis enda alla tohutult ruumi ega paku mingeid eeliseid, kuna neid ei saa kasutada seoste loomiseks.

Märkus. : Andmeaitades ja mitmemõõtmelistes andmebaasides nimetatakse peamiselt arvandmetest koosnevaid suuri tabeleid sageli faktitabeliteks. Faktitabelid sisaldavad enamasti näiteks ettevõtte tegevusnäitajate või tehingutega seotud andmeid (nt müügi ja kulude andmepunktid), mis liidetakse ja viiakse vastavusse organisatsiooniüksuste, toodete, turusegmentide, geograafiliste piirkondade ja muuga. Kõik veerud faktitabelis, mis sisaldavad äriandmeid või mida saab kasutada muudes tabelites talletatud andmete ristviidete jaoks, tuleks andmeanalüüsi toetamiseks mudelisse kaasata. Veerg, mille võiksite välja jätta, on faktitabeli primaarvõtme veerg, mis koosneb ainult faktitabelis, mitte kuskil mujal eksisteerivatest ainuandmetest. Kuna faktitabelid on äärmiselt mahukad, toob faktitabelite ridade või veergude väljajätmine üldjuhul kaasa kõige suuremaid võite mudeli tõhustamisel.

Mittevajalike veergude välistamine

Tõhusa mudelite sisaldama ainult need veerud, mida peate tegelikult teie töövihikus. Kui soovite kontrollida, milliseid veerge kaasatud mudeli, siis on teil Power Pivoti lisandmooduli andmed importida tabeli impordiviisardi kasutamine , mitte Exceli dialoogiboksi "Importida andmed".

Tabeliimpordiviisardi käivitamisel saate valida, millised tabelid importida.

PowerPivoti lisandmooduli tabeliimpordiviisard

Igas tabelis võite klõpsata nuppu Eelvaade ja filter ning valida tabelist need osad, mida teil tõepoolest vaja läheb. Soovitame esmalt kõigi veergude valiku tühistada ja seejärel märkida ainult need veerud, mida tõesti soovite kaasata, olles esmalt hoolikalt järele mõelnud, kas neid veerge on analüüsi jaoks vaja või mitte.

Tabeliimpordiviisardi eelvaatepaan

Kuidas oleks lihtsalt vajalike ridade filtreerimisega?

Paljud ettevõtteandmebaaside ja andmeaitade tabelid sisaldavad ajaloolisi andmeid, mis on kogunenud väga pika aja jooksul. Lisaks võite leida, et tabelid, mis teid huvitavad, sisaldavad teavet ka selliste ärivaldkondade kohta, mis pole teie konkreetse analüüsi jaoks vajalikud.

Tabeliimpordiviisardi abil saate ajaloolised või asjakohatud andmed välja filtreerida, mis omakorda aitab mudelis ruumi kokku hoida. Järgmisel joonisel on kasutatud kuupäevafiltrit ainult jooksva aasta andmeid sisaldavate ridade toomiseks, jättes välja vanemad andmed, mida ei lähe vaja.

Tabeliimpordiviisardi filtripaan

Mida teha, kui veergu läheb vaja? Kas selle alla jäävat ruumi saab siiski kuidagi vähendada?

Selleks, et veergu oleks parem tihendada, saate üht-teist ära teha. Pidage meeles, et veeru ainus omadus, mis mõjutab tihendust, on ainuväärtuste arv. Sellest jaotisest saate teada, kuidas on võimalik mõnda veergu ainuväärtuste vähendamiseks muuta.

Kuupäeva ja kellaaja veergude muutmine

Kuupäeva ja kellaaja veerud võtavad sageli enda alla palju ruumi. Õnneks on selle andmetüübi talletusruumivajaduste vähendamiseks mitu võimalust. Kasutatav tehnika sõltub sellest, kuidas kavatsete veergu kasutada ning kui tuttav te SQL-päringute koostamisega olete.

Kuupäeva ja kellaaja veerud sisaldavad kuupäevaosa ja kellaaega. Kui küsite endalt, kas teil läheb veergu vaja, siis kuupäeva ja kellaaja veeru korral esitage sama küsimus endale mitu korda:

  • Kas mul läheb vaja kellaajaosa?

  • Kas mul läheb kellaajaosa vaja tunni täpsusega? minuti täpsusega? sekundi täpsusega? millisekundi täpsusega?

  • Kas mul on mitu kuupäeva ja kellaaja veergu, kuna soovin arvutada nende vahe, või soovin andmed lihtsalt aasta, kuu, kvartali jne alusel liita?

Teie vastustest neile küsimustele sõltub see, millised võimalused teil kuupäeva ja kellaaja veeruga töötamiseks on.

Kõik need lahendused nõuavad SQL-päringu muutmist. Selleks, et päringut oleks lihtsam muuta, peaksite igast tabelist vähemalt ühe veeru välja filtreerima. Veeru välja filtreerimisel muudate päringu konstruktsiooni lühendatud kujult (SELECT *) selliseks SELECT-lauseks, mis sisaldab täielikke veerunimesid, mida on märksa lihtsam muuta.

Heidame nüüd pilgu päringutele, mis on teie jaoks loodud. Dialoogiboksist Tabeliatribuudid saate ümber lülituda päringuredaktorile ja vaadata iga tabeli praegust SQL-päringut.

Ribbon in PowerPivot window showing Table Properties command

Klõpsake dialoogis Tabeliatribuudid nuppu Päringuredaktor.

Päringuredaktori avada dialoogiboks Tabeli atribuudid

Päringuredaktor näitab tabeli asustamiseks kasutatavat SQL-päringut. Kui filtreerisite mõne veeru importimisel välja, sisaldab teie päring täielikke veerunimesid:

SQL-päringu andmete toomiseks kasutada

Kui importisite tabeli tervikuna, ühtegi veergu valikust välja jätmata või filtreid rakendamata, kuvatakse päring kujul "Select * from", mida on keerulisem muuta:

SQL-päringu abil vaikimisi, lühemaks süntaks

SQL-päringu muutmine

Nüüd, kui teate, kuidas päringut leida, saate seda andmemudeli mahu edasiseks vähendamiseks muuta.

  1. Valuutat või kümnendandmeid sisaldavate veergude korral kasutage kümnendkohtade eemaldamiseks (kui te neid ei vaja) järgmist süntaksit:

    "SELECT ROUND([Kümnendveeru_nimi];0)… ."

    Kui peate teadma näiteks sentide arvu, kuid sendi murdosad pole teie jaoks olulised, asendage näites olev 0 arvuga 2. Negatiivsete arvude kasutamisel saate ümardada ühelisteni, kümnelisteni, sajalisteni jne.

  2. Kui teil on näiteks kuupäeva ja kellaaja veerg nimega dbo.Suurtabel.[Date Time] ja teil ei lähe kellaaja osa vaja, kasutage kellaajast vabanemiseks järgmist süntaksit:

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

  3. Kui teil on kuupäeva ja kellaaja veerg nimega dbo.Suurtabel.[Date Time] ja teil läheb vaja nii kuupäeva kui ka kellaaja osa, kasutage ühe kuupäeva ja kellaaja veeru asemel SQL-päringus mitut veergu:

    "SELECT CAST (dbo.Suurtabel.[Date Time] as date ) AS [Date Time];

    datepart(hh; dbo.Suurtabel.[Date Time]) as [Date Time Hours];

    datepart(mi; dbo.Suurtabel.[Date Time]) as [Date Time Minutes];

    datepart(ss; dbo.Suurtabel.[Date Time]) as [Date Time Seconds];

    datepart(ms; dbo.Suurtabel.[Date Time]) as [Date Time Milliseconds]“

    Kasutage nii palju veerge, kui teil iga osa eraldi veerus talletamiseks vaja läheb.

  4. Kui teil läheb vaja tunde ja minuteid ning eelistate neid ühe kellaajaveeruna koos näha, võite kasutada järgmist süntaksit:

    Timefromparts(datepart(hh; dbo.Suurtabel.[Date Time]); datepart(mm, dbo.Suurtabel.[Date Time])) as [Date Time HourMinute]

  5. Kui teil on kaks kuupäeva ja kellaaja veergu, näiteks [Alguskellaaeg] ja [Lõppkellaaeg] ning tegelikult läheb teil vaja hoopis nende vahet sekundites veeruna [Kestus], eemaldage mõlemad veerud loendist ja lisage:

    "datediff(ss;[Alguskellaaeg];[Lõppkellaaeg]) as [Kestus]"

    Kui kasutate märksõna ss asemel ms, saate kestuse millisekundites.

DAX-i arvutatud mõõtude kasutamine veergude asemel

Kui olete DAX-avaldiste keelega varem töötanud, siis teate ehk juba, et arvutatud veerge kasutatakse mudelis mõnel teisel veerul põhinevate uute veergude tuletamiseks; arvutatud mõõdud seevastu määratletakse mudelis üks kord ja väärtustatakse alles siis, kui neid kasutatakse PivotTable-liigendtabeli või mõnes muus aruandes.

Üks mälusäästmistehnika on tavaliste või arvutatud veergude asendamine arvutatud mõõtudega. Klassikaline näide on veerud Ühiku hind, Kogus ja Summa. Kui teil on kõik need kolm veergu, saate talletusruumi kokkuhoiu nimel kasutada üksnes kahte neist veergudest ja arvutada kolmanda veeru väärtused DAX-i abil.

Millised kaks veergu alles jätta?

Ülaltoodud näites võiksite alles jätta veerud Kogus ja Ühiku hind. Neis veergudes on vähem väärtusi kui veerus Summa. Veeru Summa arvutamiseks lisage arvutatud mõõt:

"TotalSales:=sumx("Müügitabel";"Müügitabel"[Ühiku hind]*"Müügitabel"[Kogus])"

Arvutatud veerud sarnanevad tavaliste veergudega vähemalt selles, et mõlemad võtavad mudelis enda alla päris palju ruumi. Arvutatud mõõdud seevastu arvutatakse jooksvalt ja need ei võta mudelis ruumi.

Kokkuvõte

Selles artiklis oli juttu mitmest lähenemisviisist, mis aitavad teil koostada tõhusama mälukasutusega andmemudelit. Andmemudeli failimahu ja mälunõuete vähendamiseks tuleks vähendada veergude ja ridade üldarvu ning igas veerus leiduvate ainuväärtuste arvu. Artiklis käsitleti muu hulgas järgmisi tehnikaid.

  • Veergude eemaldamine on ruumi kokkuhoiuks muidugi kõige parem lahendus. Mõelge järele, milliseid veerge teil tõesti vaja läheb.

  • Vahel võite veeru eemaldada ja asendada selle tabelis arvutatud mõõduga.

  • Teil ei pruugi kõiki tabeliridu vaja minna. Tabeliimpordiviisardis saate mittevajalikud read välja filtreerida.

  • Ühe veeru tükeldamine mitmeks erineva otstarbega osaks on enamasti hea viis veeru ainuväärtuste arvu vähendada. Igas osas on sel juhul väike arv ainuväärtusi ning kombineeritud veergude ainuväärtuste arv on väiksem kui algses ühtses veerus.

  • Sageli läheb teil erinevaid osi vaja ka selleks, et saaksite neid aruannetes tükelditena kasutada. Vahel saate osade põhjal luua hierarhiad (nt Tunnid, Minutid, Sekundid).

  • Sageli sisaldavad veerud rohkem teavet kui teil vaja läheb. Oletagem näiteks, et veerus on talletatud arvude kümnendkohad, kuid olete rakendanud kümnendkohad peitva vormingu. Ümardamine võib olla väga tõhus meetod arvväärtustega veeru mahu vähendamiseks.

Nüüd, kui olete lõpetanud, saate oma töövihiku mahtu vähendada, kaaluge töötab ka töövihiku mahu optimeerija. See analüüsib Exceli töövihikus ja võimalusel tihendab edasi. Laadige töövihiku mahu optimeerija.

Seotud lingid

Andmemudeli spetsifikatsioon ja limiidid

Töövihiku mahu optimeerija allalaadimine

PowerPivot: Võimas Andmeanalüüs ja andmete modelleerimine Excelis

Märkus. : Masintõlke lahtiütlus. Selle artikli tõlkis arvutisüsteem ilma inimese sekkumiseta. Microsoft pakub selliseid masintõlkeid, et inglise keelt mittekõnelevad kasutajad saaksid vaadata sisu Microsofti toodete, teenuste ja tehnoloogiate kohta. Kuna artikkel on masintõlgitud, võib see sisaldada sõnavara-, süntaksi- või grammatikavigu.

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×