"Excel 2013" arba naujesnėje versijoje galite kurti duomenų modelius, kuriuose yra milijonai eilučių, ir atlikti efektyvią duomenų analizę pagal šiuos modelius. Duomenų modelius galima kurti su arba be Power Pivot papildinio, kad būtų palaikomas bet koks "PivotTable", diagramų ir "Power View" vizualizacijų skaičius toje pačioje darbaknygėje.
Pastaba: Šiame straipsnyje aprašomi „Excel 2013“ duomenų modeliai. Tačiau tas pats duomenų modeliavimas ir „Power Pivot“ funkcijos, pristatytos „Excel 2013“, taip pat yra taikomos „Excel 2016“. Šios "Excel" versijos iš esmės šiek tiek skiriasi.
Nors programoje "Excel" galite lengvai kurti didžiulius duomenų modelius, yra keletas priežasčių, kodėl to daryti negalima. Pirma, dideli modeliai, kuriuose yra daugybė lentelių ir stulpelių, daugumai analizių yra perpildos ir sukuria sudėtingą laukų sąrašą. Antra, dideli modeliai naudoja vertingą atmintį, o tai neigiamai veikia kitas programas ir ataskaitas, kurios naudoja tuos pačius sistemos išteklius. Galiausiai, „Microsoft 365“ "SharePoint Online" ir "Excel Web App" riboja "Excel" failo dydį iki 10 MB. Darbaknygės duomenų modeliuose, kuriuose yra milijonai eilučių, 10 MB limitą gausite gana greitai. Žr. Duomenų modelio specifikacija ir apribojimai.
Šiame straipsnyje sužinosite, kaip sukurti tvirtai sukonstruotą modelį, kurį būtų lengviau naudoti ir naudoti mažiau atminties. Skirdami laiko sužinoti geriausias efektyvaus modelio dizaino praktikas, praleisite pinigus už bet kokį kuriamą ir naudojamą modelį, nesvarbu, ar peržiūrite jį naudodami "Excel 2013", „Microsoft 365“ "SharePoint Online", Office Web Apps Server" ar "SharePoint 2013".
Apsvarstykite, ar nevertėtų paleisti darbaknygės dydžio optimizatoriaus. Jis išanalizuos „Excel“ darbaknygę ir, jei įmanoma, ją dar suglaudins. Atsisiųskite darbaknygės dydžio optimizavimo priemonę.
Šiame straipsnyje
Glaudinimo koeficientai ir atminties analizės modulis
"Excel" duomenų modeliai naudoja atminties analizės modulį duomenims atmintyje saugoti. Variklis taiko galingus glaudinimo būdus, kad sumažintų saugojimo reikalavimus, sumažindami rezultatų rinkinį, kol jis bus pradinio dydžio dalis.
Vidutiniškai galite tikėtis, kad duomenų modelis bus 7–10 kartų mažesnis už tuos pačius duomenis jo kilmės vietoje. Pavyzdžiui, jei importuojate 7 MB duomenų iš „SQL Server“ duomenų bazės, "Excel" duomenų modelis gali lengvai būti 1 MB arba mažesnis. Iš tikrųjų pasiektas glaudinimo laipsnis pirmiausia priklauso nuo unikalių reikšmių kiekviename stulpelyje skaičiaus. Daugiau unikalių reikšmių, tuo daugiau atminties reikia jų saugoti.
Kodėl kalbame apie glaudinimą ir unikalias reikšmes? Kadangi sukurti veiksmingą modelį, kuris sumažina atminties naudojimą, svarbiausia glaudinimo maksimizavimas, o paprasčiausias būdas tai padaryti – pašalinti tuos stulpelius, kurių jums iš tikrųjų nereikia, ypač jei tuose stulpeliuose yra daug unikalių reikšmių.
Pastaba: Atskirų stulpelių saugojimo reikalavimų skirtumai gali būti didžiuliai. Kai kuriais atvejais geriau turėti kelis stulpelius su mažu unikalių reikšmių skaičiumi, o ne vieną stulpelį su dideliu unikalių reikšmių skaičiumi. Skyrius apie datos ir laiko optimizavimą išsamiai paaiškina šią techniką.
Niekas nemuša neegzistuojančių stulpelių, kad būtų mažai atminties
Efektyviausias atminties stulpelis yra tas, kurio niekada neimportavote į pirmąją vietą. Jei norite sukurti veiksmingą modelį, peržiūrėkite kiekvieną stulpelį ir paklauskite savęs, ar jis prisideda prie norimos atlikti analizės. Jei jis nėra arba nesate tikri, palikite jį. Vėliau galėsite įtraukti naujų stulpelių, jei jų prireiks.
Du stulpelių, kurie visada turi būti neįtraukti, pavyzdžiai
Pirmasis pavyzdys susijęs su duomenimis, gautais iš duomenų saugyklos. Duomenų sandėlyje dažnai randami ETL procesų artefaktai, kurie įkelia ir atnaujina sandėlio duomenis. Stulpeliai, pvz., "create date" (sukūrimo data), "update date" (naujinimo data) ir "ETL run" (ETL vykdymas), sukuriami įkeliant duomenis. Nė vieno iš šių stulpelių nereikia modeliui, todėl importuojant duomenis jie turėtų būti atžymėti.
Antrajame pavyzdyje importuojant faktų lentelę praleistas pirminio rakto stulpelis.
Daugelis lentelių, įskaitant faktų lenteles, turi pirminius raktus. Daugelyje lentelių, pvz., kuriose yra klientų, darbuotojų ar pardavimo duomenų, jums reikės lentelės pirminio rakto, kad galėtumėte jį naudoti kurdami modelio ryšius.
Faktų lentelės skiriasi. Faktų lentelėje pirminis raktas naudojamas unikaliai identifikuoti kiekvieną eilutę. Nors tai būtina normalizavimo tikslais, tai mažiau naudinga duomenų modelyje, kuriame norite tik analizuoti naudojamus stulpelius arba nustatyti lentelių ryšius. Dėl šios priežasties importuodami iš faktų lentelės neįtraukite jo pirminio rakto. Faktų lentelės pirminiai raktai naudoja didžiulį modelio vietos kiekį, tačiau nesuteikia jokios naudos, nes jų negalima naudoti ryšiams kurti.
Pastaba: Duomenų sandėliuose ir kelių dimensijų duomenų bazėse didelės lentelės, kurias sudaro daugiausia skaitiniai duomenys, dažnai vadinamos faktų lentelėmis. Faktų lentelės paprastai apima verslo našumo arba operacijų duomenis, pvz., pardavimo ir išlaidų duomenų taškus, kurie sujungiami ir sulygiuojami su organizacijos vienetais, produktais, rinkos segmentais, geografiniais regionais ir t. t. Visi faktų lentelės stulpeliai, kuriuose yra verslo duomenų arba kurie gali būti naudojami kitose lentelėse saugomiems kryžminės nuorodos duomenims, turėtų būti įtraukti į modelį, kad būtų palaikoma duomenų analizė. Stulpelis, į kurį norite neįtraukti, yra faktų lentelės pirminio rakto stulpelis, kurį sudaro unikalios reikšmės, esančios tik faktų lentelėje, ir niekur kitur. Faktų lentelės yra tokios didelės, todėl kai kurie didžiausi modelio efektyvumo laimėjimai gaunami iš faktų lentelių neįtraukiant eilučių ar stulpelių.
Kaip neįtraukti nereikalingų stulpelių
Efektyviuose modeliuose yra tik tie stulpeliai, kurių jums iš tikrųjų reikės darbaknygėje. Jei norite valdyti, kurie stulpeliai yra įtraukti į modelį, turėsite naudoti "Power Pivot" papildinio lentelių importavimo vediklį, kad importuotumėte duomenis , o ne "Excel" dialogo langą Duomenų importavimas.
Kai paleidžiate lentelės importavimo vediklį, pasirenkate, kurias lenteles importuoti.
Kiekvienai lentelei galite spustelėti mygtuką Peržiūrėti & filtruoti ir pasirinkti lentelės dalis, kurių jums tikrai reikia. Rekomenduojame pirmiausia atžymėti visus stulpelius, tada toliau tikrinti norimus stulpelius atsižvelgiant į tai, ar jie reikalingi analizei.
O kaip filtruoti tik reikiamas eilutes?
Daugelyje įmonės duomenų bazių ir duomenų saugyklų lentelių yra ilgai kaupiamų istorinių duomenų. Be to, galite pastebėti, kad jus dominančiose lentelėse yra informacijos apie verslo sritis, kurių nereikia konkrečiai analizei atlikti.
Naudodami lentelių importavimo vediklį, galite filtruoti retrospektyvinius arba nesusijusius duomenis ir taip sutaupyti daug vietos modelyje. Toliau pateiktame paveikslėlyje datos filtras naudojamas gauti tik eilutes, kuriose yra dabartinių metų duomenys, išskyrus istorinius duomenis, kurių nereikės.
Ką daryti, jei mums reikia stulpelio; ar mes vis dar galime sumažinti jos kosmoso išlaidas?
Yra keletas papildomų būdų, kuriuos galite taikyti, kad stulpelis būtų geriau suglaudinta. Atminkite, kad vienintelė stulpelio savybė, turinti įtakos glaudinimui, yra unikalių reikšmių skaičius. Šiame skyriuje sužinosite, kaip galima modifikuoti kai kuriuos stulpelius, kad būtų sumažintas unikalių reikšmių skaičius.
Datos ir laiko stulpelių modifikavimas
Daugeliu atvejų datos ir laiko stulpeliai užima daug vietos. Laimei, yra keletas būdų, kaip sumažinti šio duomenų tipo saugyklos reikalavimus. Metodai skirsis, atsižvelgiant į tai, kaip naudojate stulpelį, ir nuo patogumo lygio kuriant SQL užklausas.
Datos ir laiko stulpeliai apima datos dalį ir laiką. Kai klausiate savęs, ar jums reikia stulpelio, užduokite tą patį klausimą kelis kartus stulpeliui Data ir laikas:
-
Ar man reikia laiko dalies?
-
Ar reikia laiko dalies valandų lygiu? Minučių? Sekundžių? Milisekundžių?
-
Ar turiu kelis datos ir laiko stulpelius, nes noriu apskaičiuoti skirtumą tarp jų arba tiesiog agreguoti duomenis pagal metus, mėnesį, ketvirtį ir t. t.
Kaip atsakysite į kiekvieną iš šių klausimų, nustatysite, kaip tvarkyti stulpelio Data ir laikas parinktis.
Visus šiuos sprendimus reikia modifikuoti SQL užklausą. Kad būtų lengviau modifikuoti užklausą, turėtumėte filtruoti bent vieną stulpelį kiekvienoje lentelėje. Filtruodami stulpelį pakeičiate užklausos struktūrą iš sutrumpinto formato (SELECT *) į sakinį SELECT, kuriame yra visiškai apibrėžtų stulpelių pavadinimų, kuriuos daug lengviau modifikuoti.
Peržvelkime jums sukurtas užklausas. Dialogo lange Lentelės ypatybės galite perjungti užklausų rengyklę ir peržiūrėti dabartinę kiekvienos lentelės SQL užklausą.
Dalyje Lentelės ypatybės pasirinkite Užklausų rengyklė.
Užklausų rengyklė rodo SQL užklausą, naudojamą lentelei užpildyti. Jei importuodami išfiltravote bet kurį stulpelį, jūsų užklausoje yra visiškai apibrėžtų stulpelių pavadinimų:
|
Tuo tarpu, jei importavote visą lentelę, nepažymėdami stulpelio ar nepritaikydami jokio filtro, pamatysite užklausą kaip "Pasirinkti * nuo ", o tai bus sunkiau modifikuoti:
|
SQL užklausos modifikavimas
Dabar, kai žinote, kaip rasti užklausą, galite ją modifikuoti, kad dar labiau sumažintumėte modelio dydį.
-
Jei stulpeliuose yra valiutos arba dešimtainių duomenų, jei dešimtainių skilčių nereikia, naudokite šią sintaksę, kad pašalintumėte dešimtainius skaičius:
"SELECT ROUND([Decimal_column_name],0)... .”
Jei jums reikia centų, bet ne centų trupmenų, pakeiskite 0 iš 2. Jei naudojate neigiamus skaičius, galite suapvalinti iki vienetų, dešimčių, šimtų ir t. t.
-
Jei turite stulpelį Data ir laikas, pavadintą dbo. Didelė lentelė. [Datos laikas] ir jums nereikia laiko dalies, naudokite sintaksę, kad atsikratytumėte laiko:
"SELECT CAST (dbo. Didelė lentelė. [Date time] as date) AS [Date time]) "
-
Jei turite stulpelį Data ir laikas, pavadintą dbo. Didelė lentelė. [Datos laikas] ir jums reikia datos ir laiko dalių, SQL užklausoje naudokite kelis stulpelius, o ne vieną stulpelį Datetime:
"SELECT CAST (dbo. Didelė lentelė. [Date Time] as date ) AS [Date Time],
datepart(hh, dbo. Didelė lentelė. [Datos laikas]) kaip [Datos laikas valandos],
datepart(mi, dbo. Didelė lentelė. [Datos laikas]) as [Date Time Minutes],
datepart(s, dbo. Didelė lentelė. [Datos laikas]) kaip [Datos laiko sekundės],
datepart(ms, dbo. Didelė lentelė. [Datos laikas]) kaip [Datos laiko milisekundės]"
Naudokite tiek stulpelių, kiek reikia norint kiekvieną dalį saugoti atskiruose stulpeliuose.
-
Jei jums reikia valandų ir minučių ir norite juos naudoti kartu kaip vienkartinį stulpelį, galite naudoti sintaksę:
Timefromparts(datepart(hh, dbo. Didelė lentelė. [Date Time]), datepart(mm, dbo. Didelė lentelė. [Date Time])) kaip [Datos laiko valandaMinute]
-
Jei turite du datos ir laiko stulpelius, pvz., [Pradžios laikas] ir [Pabaigos laikas], ir tai, ko jums tikrai reikia, yra laiko skirtumas tarp jų sekundėmis kaip stulpelis pavadinimu [Trukmė], pašalinkite abu stulpelius iš sąrašo ir įtraukite:
"datediff(s,[Start Date],[End Date]) as [Duration]"
Jei vietoj ss naudosite raktažodį ms, trukmę gausite milisekundėmis
DAX apskaičiuotų matų, o ne stulpelių naudojimas
Jei anksčiau dirbote su DAX išraiškos kalba, galbūt jau žinote, kad apskaičiuoti stulpeliai naudojami naujiems stulpeliams gauti pagal kurį nors kitą modelio stulpelį, o apskaičiuoti matavimai modelyje apibrėžiami vieną kartą, bet vertinami tik naudojant "PivotTable" ar kitoje ataskaitoje.
Vienas atminties taupymo būdas yra pakeisti įprastus arba apskaičiuotuosius stulpelius apskaičiuotuoju matu. Klasikinis pavyzdys yra Vieneto kaina, Kiekis ir Suma. Jei turite visus tris, galite sutaupyti vietos išlaikydami tik du ir skaičiuodami trečiąjį naudodami DAX.
Kuriuos 2 stulpelius reikia palikti?
Anksčiau pateiktame pavyzdyje palikite Kiekis ir Vieneto kaina. Šios dvi turi mažiau reikšmių nei Bendroji suma. Norėdami apskaičiuoti sumą, įtraukite skaičiavimų matą, pvz.:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Apskaičiuoti stulpeliai yra panašūs į įprastus stulpelius, kai abu užima vietos modelyje. Priešingai, apskaičiuoti matavimai yra skaičiuojami skristi ir neužima vietos.
Išvados
Šiame straipsnyje kalbėjome apie kelis būdus, kurie gali padėti sukurti atmintį taupesnį modelį. Duomenų modelio failo dydžio ir atminties reikalavimų mažinimo būdas yra sumažinti bendrą stulpelių ir eilučių skaičių bei unikalių reikšmių skaičių kiekviename stulpelyje. Štai keletas būdų, kuriuos mes apimame:
-
Stulpelių šalinimas, žinoma, yra geriausias būdas sutaupyti vietos. Nuspręskite, kurių stulpelių jums tikrai reikia.
-
Kartais galite pašalinti stulpelį ir pakeisti jį apskaičiuotuoju matu lentelėje.
-
Gali neprireikti visų lentelės eilučių. Lentelių importavimo vediklyje galite filtruoti eilutes.
-
Apskritai, norint sumažinti unikalių stulpelio reikšmių skaičių, patogu išskaidyti vieną stulpelį į kelias skirtingas dalis. Kiekvienoje iš dalių bus nurodytas nedidelis unikalių reikšmių skaičius, o bendra suma bus mažesnė nei pradinis vieningas stulpelis.
-
Daugeliu atvejų taip pat reikia skirtingų dalių, kad ataskaitose galėtumėte naudoti kaip duomenų filtrus. Kai reikia, galite kurti hierarchijas iš dalių, pvz., Valandos, Minutės ir Sekundės.
-
Daug kartų stulpeliuose yra daugiau informacijos, nei jums jų reikia. Pavyzdžiui, tarkime, kad stulpelyje saugomi dešimtainiai skaičiai, bet pritaikėte formatavimą, kad paslėptumėte visus dešimtainius skaičius. Apvalinimas gali būti labai efektyvus mažinant skaitinio stulpelio dydį.
Atlikę tai, ką galite, kad sumažintumėte darbaknygės dydį, apsvarstykite galimybę paleisti ir darbaknygės dydžio optimizavimo priemonę. Jis išanalizuos „Excel“ darbaknygę ir, jei įmanoma, ją dar suglaudins. Atsisiųskite darbaknygės dydžio optimizavimo priemonę.
Susiję saitai
Duomenų modelio specifikacija ir apribojimai
Darbaknygės dydžio optimizavimo priemonė
„PowerPivot“: galinga duomenų analizė ir duomenų modeliavimas „Excel“
