2013 Excel või uuemates versioonides saate luua miljoneid ridu sisaldavaid andmemudeleid ja seejärel teha nende mudelite põhjal võimsaid andmeanalüüse. Andmemudeleid saab luua koos lisandmooduliga või ilma Power Pivot mis tahes arvu PivotTable-liigendtabelite, diagrammide ja Power View'i visualiseeringute toetamiseks samas töövihikus.
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 versioonide vahel on tegelikult vähe Excel.
Kuigi saate hõlpsalt luua suuri andmemudeleid Excel, on selleks mitu põhjust. Esiteks on suurtes mudelites, mis sisaldavad palju tabeleid ja veerge, enamiku analüüside jaoks liiga palju oskust ja need muudavad väljaloendi tülikaks. Teiseks kasutavad suured mudelid väärtuslikku mälu, mis mõjutab negatiivselt teisi rakendusi ja aruandeid, mis jagavad samu süsteemiressursse. Lõpuks Microsoft 365 nii SharePoint online Excel kui ka Excel Web App piirata Excel mahtu 10 MB-ni. Miljoneid ridu sisaldavate töövihiku andmemudelite puhul on 10 MB piirmäär üsna kiire. Vt andmemudeli spetsifikatsioon ja piirangud.
Selles artiklis kirjeldatakse, kuidas koostada tihedalt koostatud mudel, mida on lihtsam kasutada ja mis kasutab vähem mälu. Tõhusa Excel mudelikujunduse parimate tavade tundmaõppimiseks kuluvad kõik teie 2013 SharePoint 2013- ja Office Web Apps-serveris või SharePoint 201 Microsoft 365 SharePoint 3-
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
Tihendussuhted ja mälus analüüsimootor
Andmemudelid Excel mälus analüüsimootorit, et talletada andmed mällu. Mootor rakendab talletusnõuete vähendamiseks võimsaid tihendusmeetodeid, kahandades tulemikomplekti, kuni see on murdosa selle algsest suurusest.
Keskmiselt võib eeldada, et andmemudel on lähtepunktis 7–10 korda väiksem kui samad andmed. Näiteks kui impordite 7 MB andmeid SQL Server andmebaasist, võib Excel andmemudel olla kuni 1 MB. Tegelikult saavutatud tihendusaste sõltub peamiselt iga veeru kordumatute väärtuste arvust. Mida rohkem kordumatuid väärtusi, seda rohkem mälu on vaja neid talletada.
Miks räägime tihendamisest ja kordumatutest väärtustest? Kuna tõhusa mudeli loomine, mis minimeerib mälukasutuse, on tihendamise maksimeerimine ja lihtsaim viis selleks on vabaneda veergudest, mida te tegelikult ei vaja, eriti juhul, kui need veerud sisaldavad suurt hulka kordumatuid väärtusi.
Märkus.: Üksikute veergude salvestusruuminõuete erinevused võivad olla väga suured. Mõnel juhul on parem, kui teil on mitu veergu, mille kordumatute väärtuste arv on väike, mitte üks veerg, kus on suur arv kordumatuid väärtusi. Kuupäevaaja optimeerimise jaotis hõlmab seda tehnikat üksikasjalikult.
Mitte miski ei saa olla tühine veerg, kus mälukasutus on väike
Kõige mälusäästlikum veerg on see, mida te pole kunagi importinud. Kui soovite luua tõhusa mudeli, vaadake iga veergu ja küsige endalt, kas see aitab kaasa analüüsile, mida soovite teha. Kui see ei tööta või te pole kindel, jätke see välja. Vajaduse korral saate hiljem alati uusi veerge lisada.
Kaks näidet veergudest, mis tuleks alati välistada
Esimene näide on seotud andmetega, mis pärinevad andmelaost. Andmelaos on tavaline leida ETL-i protsesside artefakte, mis laadivad ja värskendavad andmeid laos. Andmete laadimisel luuakse veerud nagu "loo kuupäev", "värskenda kuupäev" ja "ETL-i käivitamine". Ükski neist veergudest pole mudelis vajalik ja need tuleks andmete importimisel tühistada.
Teises näites jäetakse faktitabeli importimisel välja primaarvõtme veerg.
Paljudel tabelitel (sh faktitabelid) on primaarvõtmed. Enamiku tabelite (nt klientide, töötajate või müügiandmete) puhul soovite tabeli primaarvõtit, et seda mudelis seoste loomiseks kasutada.
Faktitabelid on erinevad. Faktitabelis kasutatakse primaarvõtit iga rea kordumatuks tuvastamiseks. Kuigi see on normaliseerimiseks vajalik, on see vähem kasulik andmemudelis, kus soovite analüüsimiseks või tabeliseoste loomiseks kasutada ainult neid veerge. Seetõttu ei kaasata faktitabelist importimisel selle primaarvõtit. Faktitabeli primaarvõtmed kasutavad mudelis tohutul hulgal ruumi, kuid ei paku siiski mingit kasu, kuna neid ei saa kasutada seoste loomiseks.
Märkus.: Andmeladudes ja mitmetahulistes andmebaasides nimetatakse enamasti arvandmetest koosnevaid suuri tabeleid sageli faktitabeliks. Faktitabelid sisaldavad tavaliselt ärijõudluse või tehinguandmeid (nt müügi- ja kuluandmepunktid, mis on koondatud ja joondatud organisatsiooniüksuste, toodete, turusegmentide, geograafiliste piirkondade jne) järgi. Andmeanalüüsi toetamiseks tuleks mudelisse kaasata kõik faktitabeli veerud, mis sisaldavad äriandmeid või mida saab kasutada teistes tabelites talletatud andmete ristviiteks. Veerg, mille soovite välistada, on faktitabeli primaarvõtmeveerg, mis koosneb kordumatutest väärtustest, mis on olemas ainult faktitabelis ja mitte kuskil mujal. Kuna faktitabelid on nii suured, tuletatakse mudeli tõhususe suurimad kasvutulud faktitabelitest ridade või veergude välistamise kaudu.
Tarbetute veergude välistamine
Tõhusad mudelid sisaldavad ainult neid veerge, mida töövihikus tegelikult vaja läheb. Kui soovite kontrollida, millised veerud mudelisse kaasatakse, peate andmete importimiseks kasutama Power Pivoti lisandmooduli tabeliimpordiviisardit, mitte dialoogiboksi "Andmete importimine" Excel.
Tabeliimpordiviisardi käivitamisel saate valida, milliseid tabeleid importida.
Iga tabeli korral saate klõpsata nuppu & filter ja valida tabeli osad, mida vajate. Soovitame esmalt tühjendada kõik veerud ja seejärel kontrollida soovitud veerge, kaaludes, kas need on analüüsi jaoks vajalikud.
Kuidas on vaja filtreerida ainult vajalikke ridu?
Paljud ettevõtete andmebaaside ja andmeladude tabelid sisaldavad pika aja jooksul kogutud ajaloolisi andmeid. Lisaks võib juhtuda, et huvitud tabelid sisaldavad teavet ettevõtte alade kohta, mis pole teie konkreetse analüüsi jaoks nõutavad.
Tabeliimpordiviisardi abil saate filtreerida ajaloolisi või mitteseotud andmeid ning seega salvestada mudelisse palju ruumi. Järgmisel pildil kasutatakse kuupäevafiltrit ainult nende ridade toomiseks, mis sisaldavad praeguse aasta andmeid, välja arvatud ajaloolised andmed, mida pole vaja.
Mida teha, kui vajame veergu; kas saame selle ruumi maksumust veel vähendada?
Veeru tihendamiseks paremaks kandidaatimiseks saate kasutada veel mõnda muud tehnikat. Pidage meeles, et tihendamist mõjusa veeru ainus omadus on kordumatute väärtuste arv. Selles jaotises saate teada, kuidas mõnda veergu saab kordumatute väärtuste arvu vähendamiseks muuta.
Kuupäeva ja kellaaja veergude muutmine
Paljudel juhtudel võtab veerud Kuupäev ja kellaaeg palju ruumi. Õnneks on selle andmetüübi talletusnõuete vähendamiseks mitu võimalust. Meetodid olenevad veeru kasutamisest ja teie mugavuse tasemest päringute SQL.
Kuupäeva ja kellaaja veerud sisaldavad kuupäevaosa ja kellaaega. Kui küsite endalt, kas vajate veergu, küsige sama küsimust mitu korda veeru Datetime kohta.
-
Kas mul on vaja ajaosa?
-
Kas mul on vaja ajaosa tundide tasemel? , minutit? , Sekundid? , millisekundid?
-
Kas mul on mitu kuupäeva ja kellaaja veergu, kuna soovin arvutada nende vahe või koondada andmed aasta, kuu, kvartali jne järgi.
See, kuidas te nendele küsimustele vastate, määratleb veeru Datetime (Kuupäev ja kellaaeg) lahendamise võimalused.
Kõik need lahendused nõuavad päringu SQL muutmist. Päringute muutmise lihtsustamiseks peaksite igas tabelis välja filtreerima vähemalt ühe veeru. Veeru filtreerimise abil saate muuta päringute konstruktsiooni lühendatud vormingust (SELECT *) SELECT-lauseks, mis sisaldab täielikult kvalifitseeritud veerunimesid, mida on palju lihtsam muuta.
Vaatame teie jaoks loodud päringuid. Dialoogiboksis Tabeli atribuudid saate aktiveerida päringuredaktori ja vaadata iga tabeli SQL praegust päringut.
Valige tabeli atribuutides Päringuredaktor.
Päringuredaktor kuvab SQL tabeli asustamiseks kasutatava päringu. Kui filtreerisite importimise ajal mis tahes veeru välja, sisaldab päring täieliku kvalifikatsiooniga veerunimesid.
|
Kui impordite tabeli tervikuna, ilma ühtegi veergu märkimata või filtrit rakendamata, kuvatakse päring tekstina "Select * from " (Vali * from), mida on raskem muuta.
|
Päringu SQL muutmine
Nüüd, kui teate, kuidas päringut leida, saate seda mudeli mahu edasiseks vähendamiseks muuta.
-
Kui teil pole kümnendkohti vaja, kasutage komakohtadest vabanemiseks seda süntaksit.
"SELECT ROUND([Decimal_column_name];0)... .”
Kui vajate senti, kuid mitte senti murdosasid, asendage arv 0 arvuga 2. Kui kasutate negatiivseid arve, saate ümardada ühikute, kümnete, sadade jneni.
-
Kui teil on kuupäeva ja kellaaja veerg nimega dbo. Bigtable. [Kuupäev Kellaaeg] ja te ei vaja kellaajaosa, kasutage kellaajast vabanemiseks süntaksit.
"SELECT CAST (dbo. Bigtable. [Kuupäev kellaaeg] kuupäevana) AS [Kuupäev kellaaeg]) "
-
Kui teil on kuupäeva ja kellaaja veerg nimega dbo. Bigtable. [Kuupäev Kellaaeg] ja teil on vaja nii kuupäeva- kui ka kellaajaosasid, kasutage ühe kuupäevaaja veeru asemel SQL veeru "Kuupäev ja kellaaeg" asemel mitut veergu.
"SELECT CAST (dbo. Bigtable. [Kuupäev Kellaaeg] kuupäevana ) AS [Kuupäev Kellaaeg],
datepart(hh, dbo. Bigtable. [Kuupäeva kellaaeg]) as [Date Time Hours],
datepart(mi, dbo. Bigtable. [Kuupäeva kellaaeg]) as [Kuupäeva kellaaja minutid],
datepart(ss, dbo. Bigtable. [Kuupäeva kellaaeg]) as [Date Time Seconds],
datepart(ms, dbo. Bigtable. [Kuupäeva kellaaeg]) as [Date Time Milliseconds]"
Kasutage nii palju veerge, kui teil on vaja iga osa eraldi veergudes talletada.
-
Kui teil on vaja tunde ja minutit ning eelistate neid koos ühe veeruna, saate süntaksit kasutada järgmiselt.
Timefromparts(datepart(hh, dbo. Bigtable. [Kuupäev Kellaaeg]), datepart(mm, dbo. Bigtable. [Kuupäeva kellaaeg])) as [Date Time HourMinute]
-
Kui teil on kaks kuupäeva ja kellaaja veergu (nt [Alguskaeg] ja [Lõppkaeg]) ning tegelikult on vaja nende ajavahet sekundites veeruna [Kestus], eemaldage mõlemad veerud loendist ja lisage:
"datediff(ss;[Alguskuupäev];[Lõppkuupäev]) as [Kestus]"
Kui kasutate märksõna ms asemel ss, saate kestuse millisekundites
DAX-i arvutatud meetmete kasutamine veergude asemel
Kui olete varem DAX-i avaldisekeelega töötanud, võite juba teada, et arvutuslikud veerud on kasutatud mudeli mõne muu veeru põhjal uute veergude tuletamiseks, samal ajal kui arvutuslikud soovitud väärtused on mudelis määratletud üks kord, kuid neid hinnatakse ainult PivotTable-liigendtabelis või mõnes teises aruandes.
Üks mälusäästmise meetod on asendada tavalised või arvutatud veerud arvutatud meetmetega. Klassikaline näide on Ühiku hind, Kogus ja Kogusumma. Kui teil on kõik kolm, saate ruumi säästa, säilitades vaid kaks ja arvutades kolmanda DAX-i abil.
Milliseid kahte veergu peaksite alles hoidma?
Ülaltoodud näites säilitage kogus ja ühiku hind. Neil kahel on vähem väärtusi kui kogusummal. Kokkuvõtte arvutamiseks lisage arvutuslik mõõt:
"TotalSales:=sumx('Sales Table','Sales Table'[Ühiku hind]*'Müügitabel'[Kogus])"
Arvutatud veerud on nagu tavalised veerud, kus mõlemad võtavad mudelis ruumi. Seevastu arvutatakse arvutuslik arvutab kärbse ja ei võta ruumi.
Kokkuvõte
Selles artiklis kõnelesime mitmest lähenemisviisist, mis aitavad teil luua mälusäästlikuma mudeli. Andmemudeli failimahu ja mälunõuete vähendamiseks on vähendada veergude ja ridade koguarvu ning igas veerus kuvatavate kordumatute väärtuste arvu. Siin on mõned tehnikad, mida me hõlmasime.
-
Veergude eemaldamine on loomulikult parim viis ruumi säästmiseks. Otsustage, milliseid veerge te tegelikult vajate.
-
Mõnikord saate veeru eemaldada ja asendada selle tabelis arvutatud mõõtu.
-
Võimalik, et te ei vaja kõiki tabeli ridu. Tabeliimpordiviisardis saate ridu filtreerida.
-
Üldiselt on ühe veeru osadeks jagamine mitmeks eraldi osaks hea viis veerus kordumatute väärtuste arvu vähendamiseks. Igal osal on väike arv kordumatuid väärtusi ja kombineeritud kogusumma on väiksem kui algne ühendatud veerg.
-
Paljudel juhtudel on aruannetes tükeldina kasutamiseks vaja ka eri osi. Vajaduse korral saate luua hierarhiaid osadest nagu Tunnid, Minutid ja Sekundid.
-
Mitu korda sisaldavad veerud rohkem teavet, kui neid vajate. Oletagem näiteks, et veerus talletatakse kümnendkohti, kuid olete rakendanud vormingu kõigi kümnendkohtade peitmiseks. Ümardamine võib olla arvuveeru suuruse vähendamisel väga tõhus.
Nüüd, kui olete töövihiku mahu vähendamiseks teinud kõik, mida saate, võiksite käivitada ka töövihiku suuruse optimeerija. 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
