Rakenduses Excel 2013 või uuemates versioonides saate luua miljoneid ridu sisaldavaid andmeid ja seejärel teha nende mudelite põhjal võimsaid andmeid. Andmeallikaid saab luua Power Pivot lisandmooduliga või ilma, et toetada sama töövihiku PivotTable-liigendtabelite, diagrammide ja Power View ' visualiseeringute arvu.
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 pole selles osas sisuliselt mingit vahet.
Kuigi Excelis saab hõlpsalt luua hiiglaslikke andmeid, on mitu põhjust. Esiteks, suured mudelid, mis sisaldavad hulgaliselt tabeleid ja veerge, lähevad enamikule analüüsidele üle ja muudavad kohmakate väljade loendi. Teiseks kasutavad suured mudelid väärtuslikku mälu, mis mõjutab negatiivselt muid rakendusi ja aruandeid, mis jagavad sama süsteemiressursse. Lõpuks, Office 365 nii SharePoint Online ' i kui ka Excel Web App piirata Exceli-faile mahuga 10 MB. Miljonid ridu sisaldavad töövihikus olevad andmed on väga kiiresti 10 MB piirini. Vt andmete mudeli spetsifikatsioon ja limiidid.
Sellest artiklist saate teada, kuidas luua tihedalt konstrueeritud mudeleid, mida on lihtsam kasutada ja mis kasutab vähem mälu. Võttes aega, et teada saada, kuidas efektiivse mudeli kujundusega häid tavasid kasutada, tasub teie loodavate ja kasutatavate mudelite tee ära, olenemata sellest, kas vaatate seda rakenduses Excel 2013, Office 365 SharePoint Online ' is, Office Web Apps serveris või SharePoint 2013.
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
Tihenduse suhtarvud ja In-Memory Analyticsi mootor
Excelis olevad andmetüübid kasutavad mälus andmete talletamiseks mälu analüütilist mootorit. Mootor rakendab võimsaid tihenduse meetodeid, et vähendada salvestusruumi, kahandades tulemit, kuni see on murdosa selle algsest suurusest.
Keskmiselt on võimalik, et andmete mudel on 7 – 10 korda väiksem kui samade andmete lähtekoht. Kui olete näiteks importinud 7 MB andmeid SQL serveri andmebaasist, võib Excelis olevate andmete mudel olla hõlpsalt 1 MB või väiksem. Tegelikult saavutatud tihendus 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 tihendamise ja kordumatute väärtustega? Kuna mälu kasutuse minimeerimiseks on vaja luua tõhus mudel, on kõige lihtsam compression maksimeerimine ja lihtsaim viis selle tegemiseks on vabaneda kõigist veergudest, mida te tegelikult ei vaja, eriti juhul, kui need veerud sisaldavad suurt hulka kordumatuid väärtusi.
Märkus.: Üksikute veergude salvestusruumi nõuete erinevused võivad olla väga suured. Mõnel juhul on parem, kui teil on mitu veergu väikese arvu kordumatute väärtustega, mitte ühe veeruga, millel on suur arv kordumatuid väärtusi. Datetime optimeerimise jaotis sisaldab seda tehnikat üksikasjalikult.
Pole midagi, mis ei ületaks olematut veergu väikese mälukasutust
Kõige parempoolsem veerg on see, mida te pole kunagi üldse importinud. Kui soovite luua efektiivse mudeli, vaadake iga veergu ja küsige endalt, kas see aitab kaasa analüüsile, mida soovite teha. Kui see ei ole või te pole kindel, jätke see välja. Vajadusel saate hiljem alati uusi veerge lisada.
Kaks näidet veergudest, mis tuleks alati välja jätta
Esimene näide puudutab andmeid, mis pärinevad andmete laost. Andmete laos on tavaline leida ETL protsesside artefakte, mis laaditakse ja värskendatakse lao andmeid. Andmete laadimisel luuakse veerud "Create Date", "Update Date" ("ETL Run"). Ükski nendest veergudest pole mudeli jaoks vajalik ning andmete importimisel tuleks need tühistamata jätta.
Teise näite puhul on tabeli importimisel primaarvõti veerust ära jäetud.
Paljudes tabelites (sh faktilistes tabelites) on esmased võtmed. Enamiku tabelite (nt klientide, töötajate või müügidokumentide) puhul soovite tabeli primaarvõti, et saaksite seda kasutada mudeli seoste loomiseks.
Asjaolu tabelid on erinevad. Tegelikult kasutatakse primaarvõti iga rea kordumatuks tuvastamiseks. Kui see on vajalik normaliseerimiseks, on see vähem kasulik, kui soovite kasutada ainult analüüsimiseks või tabeli seoste loomiseks kasutatavaid veerge. Sel põhjusel, kui impordite tabelist fakt, ei sisalda selle primaarvõtme võtit. Peamiseks võtmeks on tabelis suur hulk ruumi, kuid see ei anna eeliseid, sest neid ei saa kasutada seoste loomiseks.
Märkus.: Andmete ladudes ja mitmemõõtmeline andmebaasides viidatakse suurtes tabelites, mis koosnevad enamasti arvulistest andmetest, sageli "faktiliste tabelitena". Tegelikult sisaldavad tabelid ärijõudluse või tehingute andmeid (nt müügi-ja Cost-andmepunktid), mis on koondatud ja joondatud ettevõtte üksuste, toodete, turgude segmentide, geograafiliste piirkondade jne kaudu. Andmete analüüsi toetamiseks tuleks mudelisse kaasata kõik äriandmeid sisaldavad tabeli veerud, mida saab kasutada ka muudes tabelites talletatud andmete Ristviiteks. Veerg, mida soovite välistada, on tabeli primaarvõtme veerg, mis koosneb kordumatutest väärtustest, mis on olemas ainult tabelis fakt ja mitte kusagil mujal. Kuna faktilised tabelid on nii suured, on osa mudeli tõhususest suurimast kasumist tingitud sellest, et need ei sisalda ridu ega veerge.
Mittevajalike veergude välistamine
Efektiivsed mudelid sisaldavad ainult neid veerge, mida te töövihikus tegelikult vajate. Kui soovite määrata, millised veerud on mudelisse kaasatud, peate lisandmooduli Power Pivot kaudu kasutama tabeli importimise viisardit, et importida andmed Exceli dialoogiboksi "andmete importimine" asemel.
Kui käivitate tabeli importimise viisardi, saate valida, milliseid tabeleid importida.
Iga tabeli korral saate klõpsata nuppu eelvaate & filter ja valida selle tabeli osad, mida te tegelikult vajate. Soovitame esmalt tühjendada kõik veerud ja seejärel jätkata soovitud veergude kontrollimist, olles kaalunud, kas need on analüüsiks vajalikud.
Kuidas oleks ainult vajalike ridade filtreerimine?
Palju tabeleid ettevõtte andmebaasides ja andmete ladudes sisaldavad varasemaid andmeid, mis on akumuleeritud pikkade perioodide jooksul. Lisaks võite avastada, et teie huvistatud tabelid sisaldavad teavet selle kohta, milliste ärialaste jaoks pole teie konkreetset analüüsi vaja.
Tabeli importimise viisardi abil saate filtreerida ajaloolisi või mitteseotud andmeid ja seega salvestada mudelisse palju ruumi. Järgmises pildis kasutatakse kuupäeva filtrit, et tuua ainult need read, mis sisaldavad jooksva aasta andmeid, välja arvatud varasemad andmed, mida ei ole vaja.
Mida teha, kui me vajame veergu; Kas me saame endiselt vähendada selle ruumi hind?
On veel mõned lisavõtted, mida saab rakendada, et muuta kolonn paremaks kandidaadiks tihendamiseks. Pidage meeles, et tihendust mõjutava veeru ainus omadus on Kordumatute väärtuste arv. Selles jaotises saate teada, kuidas mõnda veergu saab muuta, et vähendada Kordumatute väärtuste arvu.
Datetime veergude muutmine
Paljudel juhtudel võtab datetime veerud palju ruumi. Õnneks on selle andmetüübi salvestusruumi nõuete vähendamiseks mitu võimalust. Meetodid sõltuvad sellest, kuidas te veergu kasutate, ja teie mugavuse taset SQL-päringute loomisel.
Datetime veerud sisaldavad kuupäeva osa ja kellaaega. Kui küsite ise, kas vajate veergu, küsige sama küsimuse kohta mitu korda datetime-veeru kohta.
-
Kas mul on vaja aega osa?
-
Kas mul on vaja ajavahemikku tundide tasemel? minutit? Sekundit? millisekundites?
-
Kas mul on mitu datetime veergu, kuna Soovin arvutada nende vahe või lihtsalt andmete summeerimine aasta, kuu, kvartali jne järgi.
Kõik need küsimused vastavad teie võimalustele määrata datetime-veeruga suhtlemise võimalused.
Kõik need lahendused nõuavad SQL-päringu muutmist. Päringu muutmise lihtsustamiseks peaksite igas tabelis filtreerima vähemalt ühe veeru. Veeru filtreerimisel saate muuta päringute ülesehitust lühendatult (SELECT *) SELECT-lauses, mis sisaldab täielikult kvalifitseeritud veergude nimesid, mida on palju hõlpsam muuta.
Vaatame teie jaoks loodud päringuid. Dialoogiboksi Tabeli atribuudid kaudu saate aktiveerida päringu redaktori ja vaadata iga tabeli praegust SQL-i päringut.
Valige tabeli atribuutide hulgast päring Editor.
Query Editor kuvab tabeli asustamiseks kasutatava SQL-päringu. Kui olete importimise ajal veeru välja filtreerinud, sisaldab teie päring täielikult kvalifitseeritud veergude nimesid.


Kui impordite tabeli tervikuna, ilma veergu märkimata või filtrit rakendamata, kuvatakse päring "select * from", mida on raskem muuta.
SQL-päringu muutmine
Nüüd, kui teate, kuidas päringut leida, saate seda muuta, et saaksite oma mudeli suurust veelgi vähendada.
-
Valuuta-või kümnendkoha andmeid sisaldavate veergude korral kasutage kümnendkohtade eemaldamiseks seda süntaksit, kui te ei vaja kümnendkohti.
"Vali VOOR ([Decimal_column_name]; 0)... .”
Kui teil on vaja senti, kuid mitte murdosa, asendage 0 arvuga 2. Kui kasutate negatiivseid numbreid, saate ümardada üksused, TENS, sajad jne.
-
Kui teil on datetime veerg nimega dbo. Suurtabel. [Date Time] ja te ei pea selleks aega, kasutage kellaaja vabanemiseks süntaksit.
"Vali CAST (dbo. Suurtabel. [Date Time] AS Date) AS [Date Time]) "
-
Kui teil on datetime veerg nimega dbo. Suurtabel. [Date Time] ja te vajate nii kuupäeva kui ka kellaaja osi, kasutage ühe datetime veeru asemel SQL-päringus mitut veergu.
"Vali CAST (dbo. Suurtabel. [Date Time] AS Date) AS [Date Time];
DatePart (hh; dbo. Suurtabel. [Date Time]) [Date Time Hours]
DatePart (MI, dbo. Suurtabel. [Date Time]) [Date Time minutit];
DatePart (SS, dbo. Suurtabel. [Date Time]) [kuupäeva aeg sekundites];
DatePart (MS, dbo. Suurtabel. [Date Time]) [Date Time millisekundit] "
Kasutage nii palju veerge, kui peate iga osa eraldi veergudes talletama.
-
Kui vajate tunde ja minuteid ning eelistate neid koos ühe ajaveeruna, saate kasutada süntaksit:
Timefromparts (DatePart (hh; dbo). Suurtabel. [Date Time]), DatePart (mm, dbo. Suurtabel. [Date Time])) [Date Time HourMinute]
-
Kui teil on kaks datetime veergu (nt [algusaeg] ja [lõpuaeg]) ning seda, mida tegelikult vajate, on nende vahelised erinevused mõne sekundi jooksul (nt [kestus]), eemaldage loendist mõlemad veerud ja lisage järgmine tekst:
"DateDiff (SS; [alguskuupäev]; [lõppkuupäev]) [kestus]"
Kui kasutate SS-i asemel märksõna MS, saate kestuse millisekundites.
DAX-i arvutatud mõõtude kasutamine veergude asemel
Kui olete varem kasutanud DAX-i avaldise keelt, võite juba teada, et arvutatud veerge kasutatakse mudeli mõne teise veeru põhjal uute veergude tuletamiseks, samas kui arvutatud mõõdud määratletakse mudelis, kuid neid hinnatakse ainult siis, kui seda kasutatakse PivotTable-liigendtabel või muu aruande.
Ühe mälu kokkuhoiu meetod on asendada arvutatud mõõtudega tavalised või arvutatud veerud. Klassikaline näide on ühiku hind, kogus ja kogusumma. Kui teil on kõik kolm, saate ruumi säästmiseks säilitada vaid kaks ja arvutada kolmanda, kasutades DAX-i.
Milliseid kahte veergu peaks alles hoidma?
Eeltoodud näites säilitage kogus ja ühiku hind. Nende kahe väärtus on väiksem kui kogusumma. Kogusumma arvutamiseks lisage arvutatud mõõt, näiteks:
"TotalSales: = sumx (" Sales table "," Sales table "[ühiku hind] *" Sales table "[Kogus])"
Arvutatud veerud on nagu tavalised veerud, kus mõlemad võtavad mudelis ruumi. Vastupidisel korral arvutatakse arvutatud meetmed lennu pealt ja need ei võta ruumi.
Kokkuvõte
Selles artiklis rääkisime mitmest lähenemisviisist, mis aitavad teil luua rohkem mälu-tõhusamat mudelit. Andmete mudeli failimahu ja mälu vähendamiseks saate vähendada veergude ja ridade koguarvu ning igas veerus kuvatavate Kordumatute väärtuste arvu. Siin on mõned tehnikad, mida me hõlmame.
-
Veergude eemaldamine on muidugi parim viis ruumi säästmiseks. Otsustage, milliseid veerge te tegelikult vajate.
-
Mõnikord saate veeru eemaldada ja asendada selle tabelis arvutatud mõõtega.
-
Te ei pruugi vajada kõiki tabeli ridu. Tabeli importimise viisardis saate ridu filtreerida.
-
Üldiselt on ühe veeru eraldamine mitmeks erinevaks osaks hea viis veeru Kordumatute väärtuste arvu vähendamiseks. Iga üks osa sisaldab väikese arvu kordumatuid väärtusi ja kombineeritud kogusumma on väiksem kui algne ühendatud veerg.
-
Paljudel juhtudel on teil vaja ka eri osi, mida soovite oma aruannetes Tükeldite kasutada. Vajadusel saate luua hierarhiaid sellistest osadest nagu tunnid, minutid ja sekundid.
-
Mitu korda sisaldavad veerud rohkem teavet, kui neid vajate. Oletagem näiteks, et veeru talletatakse kümnendkohad, kuid kõigi kümnendkohtade peitmiseks on rakendatud vorming. Ümardamine võib olla väga efektiivne arvulise veeru mahu vähendamisel.
Nüüd, kui olete teinud, mida saate töövihiku mahu vähendamiseks, kaaluge ka töövihiku mahu optimeerija käitamist. See analüüsib teie Exceli töövihikut ja võimalusel tihendab seda. Laadige töövihiku mahu optimeerija alla.