Programoje "Excel" galite kurti duomenų modelius, apimančius milijonus eilučių, tada pagal šiuos modelius atlikti efektyvią duomenų analizę. Duomenų modelius galima kurti su "PowerPivot" priedu arba be jo, kad toje pačioje darbaknygėje būtų palaikomas bet koks "PivotTable", diagramų ir "Power View" vizualizacijų skaičius.
Nors programoje "Excel" galite lengvai sukurti didžiulius duomenų modelius, yra kelios priežastys, kodėl to nereikia. Pirma, dideli modeliai, kuriuose yra daugybė lentelių ir stulpelių, daugumai analizių yra per dideli ir sudaro 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" tiek "SharePoint Online", tiek "Excel Web App" apriboja "Excel" failo dydį iki 10 MB. Darbaknygės duomenų modeliuose, kuriuose yra milijonai eilučių, gana greitai pasieksite 10 MB limitą. Žr. duomenų modelio specifikacija ir apribojimai.
Šiame straipsnyje sužinosite, kaip sukurti sandariai sukonstruotą modelį, su kuriuo lengviau dirbti ir kuris naudoja mažiau atminties. Skiriant laiko išmokti geriausių efektyvaus modelio kūrimo praktikų, atsipirks bet koks sukurtas ir naudojamas modelis, nesvarbu, ar peržiūrite jį "Excel", "Microsoft 365 SharePoint Online", "Office Web Apps" serveryje ar "SharePoint".
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 optimizatorių.
Šiame straipsnyje:
Niekas neprilygsta neegzistuojančiam stulpeliui, kai mažai naudojama atmintis
Ką daryti, jei mums reikia stulpelio; Ar vis tiek galime sumažinti jo vietos kainą?
Suspaudimo laipsnis ir atminties analizės variklis
"Excel" duomenų modeliai naudoja atminties analizės modulį, kad saugotų duomenis atmintyje. Variklis įgyvendina galingus suspaudimo metodus, kad sumažintų saugojimo reikalavimus, sumažindamas rezultatų rinkinį, kol jis yra pradinio dydžio dalis.
Galite tikėtis, kad duomenų modelis bus vidutiniškai 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 būti 1 MB arba mažesnis. Faktinis suglaudinimo laipsnis pirmiausia priklauso nuo unikalių reikšmių skaičiaus kiekviename stulpelyje. Kuo daugiau unikalių reikšmių, tuo daugiau atminties reikia joms saugoti.
Kodėl kalbame apie glaudinimą ir unikalias vertybes? Kuriant efektyvų modelį, kuris sumažina atminties naudojimą, reikia glaudinti maksimizavimą, o paprasčiausias būdas tai padaryti – atsikratyti nereikalingų stulpelių, ypač jei tuose stulpeliuose yra daug unikalių reikšmių.
Pastaba
Atskirų kolonų laikymo 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. Skyriuje apie datos ir laiko optimizavimą išsamiai aprašomas šis metodas.
Niekas neprilygsta neegzistuojančiam stulpeliui, kai mažai naudojama atmintis
Labiausiai atmintį naudojantis stulpelis yra tas, kurio niekada neimportavote. Jei norite sukurti efektyvų modelį, pažiūrėkite į kiekvieną stulpelį ir paklauskite savęs, ar jis prisideda prie norimos atlikti analizės. Jei ne arba nesate tikri, palikite jį. Vėliau visada galėsite įtraukti naujų stulpelių, jei jų prireiks.
Du stulpelių, kurių visuomet nereikia įtraukti, pavyzdžiai
Pirmasis pavyzdys susijęs su duomenimis, gautais iš duomenų saugyklos. Duomenų sandėlyje įprasta rasti ETL procesų artefaktų, kurie įkelia ir atnaujina duomenis sandėlyje. Stulpeliai, pvz., "sukūrimo data", "naujinimo data" ir "ETL vykdymas", sukuriami įkėlus duomenis. Nė vienas iš šių stulpelių modelyje nereikalingas ir importuojant duomenis jo žymėjimas turėtų būti panaikintas.
Antrame pavyzdyje pirminio rakto stulpelio praleidimas importuojant faktų lentelę.
Daugelyje lentelių, įskaitant faktų lenteles, pirminiai raktai yra. Daugumai lentelių, pvz., tose, kuriose yra klientų, darbuotojų ar pardavimo duomenų, reikės pirminio lentelės rakto, kad galėtumėte jį naudoti modelio ryšiams kurti.
Faktų lentelės yra skirtingos. Faktų lentelėje pirminis raktas naudojamas unikaliai identifikuoti kiekvieną eilutę. Nors tai būtina normalizavimo tikslais, ji yra mažiau naudinga duomenų modelyje, kuriame analizei naudojami tik stulpeliai arba lentelių ryšiams nustatyti. Dėl šios priežasties, importuodami iš faktų lentelės, neįtraukite pirminio rakto. Faktų lentelės pirminiai raktai užima labai daug vietos modelyje, tačiau neteikia jokios naudos, nes jų negalima naudoti kuriant ryšius.
Pastaba
Duomenų saugyklose ir kelių dimensijų duomenų bazėse didelės lentelės, sudarytos daugiausia iš skaitinių duomenų, dažnai vadinamos "faktų lentelėmis". Faktų lentelėse paprastai pateikiami verslo našumo arba operacijų duomenys, pvz., pardavimo ir išlaidų duomenų elementai, kurie yra agreguojami ir sulygiuojami pagal organizacijos vienetus, produktus, rinkos segmentus, geografinius regionus ir t. t. Visi faktų lentelės stulpeliai, kuriuose yra verslo duomenų arba kurie gali būti naudojami kitose lentelėse saugomiems duomenims kryžmiškai nurodyti, turi būti įtraukti į modelį, kad būtų palaikoma duomenų analizė. Stulpelis, kurį norite išskirti, yra pirminio rakto faktų lentelės stulpelis, sudarytas iš unikalių reikšmių, kurios yra tik faktų lentelėje ir niekur kitur. Faktų lentelės yra labai didelės, todėl didžiausias modelio efektyvumo padidėjimas gaunamas iš faktų lentelių neįtraukiant eilučių ar stulpelių.
Kaip neįtraukti nereikalingų stulpelių
Efektyviuose modeliuose yra tik tie stulpeliai, kurių iš tikrųjų reikės darbaknygėje. Jei norite kontroliuoti, kurie stulpeliai bus įtraukti į modelį, norėdami importuoti duomenis turėsite naudoti "Power Pivot" papildinio lentelių importavimo vediklį , o ne "Excel" dialogo langą "Importuoti duomenis".
Kai paleidžiate lentelių importavimo vediklį, pasirenkate, kurias lenteles importuoti.
Kiekvienai lentelei galite spustelėti mygtuką Peržiūra & Filtruoti ir pasirinkti lentelės dalis, kurių tikrai reikia. Rekomenduojame pirmiausia atžymėti visus stulpelius, tada patikrinti norimus stulpelius, svarstę, ar jie reikalingi analizei.
O kaip filtruoti tik būtinas eilutes?
Daugelyje įmonės duomenų bazių ir duomenų saugyklų lentelių yra istorinių duomenų, sukauptų per ilgą laiką. Be to, galite pastebėti, kad jus dominančiose lentelėse yra informacijos apie verslo sritis, kuri nėra būtina konkrečiai analizei atlikti.
Naudodami lentelių importavimo vediklį galite filtruoti istorinius arba nesusijusius duomenis ir taip sutaupyti daug vietos modelyje. Toliau pateiktame paveikslėlyje datos filtras naudojamas norint nuskaityti tik eilutes, kuriose yra šių metų duomenys, išskyrus istorinius duomenis, kurie nebus reikalingi.
Ką daryti, jei mums reikia stulpelio; Ar vis tiek galime sumažinti jo vietos kainą?
Yra keletas papildomų metodų, kuriuos galite taikyti, norėdami sukurti stulpelį tinkamesnį glaudinimui. Atminkite, kad vienintelė stulpelio savybė, turinti įtakos glaudinimui, yra unikalių reikšmių skaičius. Šiame skyriuje sužinosite, kaip modifikuoti kai kuriuos stulpelius, kad sumažėtų unikalių reikšmių skaičius.
Datetime stulpelių modifikavimas
Daugeliu atvejų datos ir laiko stulpeliai užima daug vietos. Laimei, yra keletas būdų, kaip sumažinti šio tipo duomenų saugojimo reikalavimus. Būdai skirsis, atsižvelgiant į tai, kaip naudojate stulpelį ir jūsų patogumo lygį kuriant SQL užklausas.
Datetime stulpeliuose yra datos dalis ir laikas. Kai klausiate savęs, ar jums reikia stulpelio, kelis kartus užduokite tą patį klausimą stulpelyje Datetime:
- Ar man reikia laiko dalies?
- Ar man reikia laiko dalies valandų lygiu? , minutes? , sekundės? , milisekundžių?
- Ar turiu kelis datos/laiko stulpelius todėl, kad noriu apskaičiuoti skirtumą tarp jų, ar tiesiog agreguoti duomenis pagal metus, mėnesį, ketvirtį ir t. t.
Tai, kaip atsakysite į kiekvieną iš šių klausimų, nustatys jūsų darbo su stulpeliu Datetime parinktis.
Visiems šiems sprendimams reikia modifikuoti SQL užklausą. Kad užklausų modifikavimas būtų lengvesnis, kiekvienoje lentelėje turite filtruoti bent po vieną stulpelį. Iš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.
Susipažinkime su užklausomis, kurios sukurtos jums. Dialogo lange Lentelės ypatybės galite įjungti užklausų rengyklę ir peržiūrėti kiekvienos lentelės dabartinę SQL užklausą.
Lentelės ypatybėse pasirinkite Užklausų rengyklė.
Užklausų rengyklė rodo SQL užklausą, naudojamą lentelei užpildyti. Jei importuodami išfiltravote bet kurį stulpelį, užklausoje yra visiškai apibrėžtų stulpelių pavadinimų:
Tačiau jei importavote visą lentelę, nepanaikinę jokio stulpelio žymėjimo ir netaikę jokio filtro, užklausa bus rodoma kaip "Pasirinkti * iš ", kurią 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į.
- Stulpeliams, kuriuose yra valiutos arba dešimtainių duomenų, jei dešimtainių skaič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 į 2. Jei naudojate neigiamus skaičius, galite suapvalinti iki vienetų, dešimčių, šimtų ir t. t. - Jei turite stulpelį "DateTime", pavadintą dbo. Bigtable. [Datos laikas] ir jums nereikia laiko dalies, naudokite sintaksę, kad atsikratytumėte laiko:
"SELECT CAST (dbo. Bigtable. [Data laikas] kaip data) AS [Data laikas]) " - Jei turite stulpelį "DateTime", pavadintą dbo. Bigtable. [Data ir laikas] jums reikia datos ir laiko dalių, SQL užklausoje naudokite kelis stulpelius vietoj vieno stulpelio Date/time:
"SELECT CAST (dbo. Bigtable. [Date Time] as Date ) AS [Date Time],
DatePart(HH, DBO. Bigtable. [Data, laikas]) kaip [Data, laikas, valandos],
DatePart(MI, DBO. Bigtable. [Data, laikas]) kaip [Data, laikas, minutės],
DatePart(ss, dbo. Bigtable. [Data, laikas]) kaip [Data, laikas, sekundės],
DatePart(MS, DBO. Bigtable. [Data, laikas]) kaip [Data, laikas, milisekundės]"
Naudokite tiek stulpelių, kiek reikia, kad kiekvieną dalį išsaugotumėte atskiruose stulpeliuose. - Jei jums reikia valandų ir minučių ir norite jų kartu naudoti kaip vieną laiko stulpelį, galite naudoti šią sintaksę:
Timefromparts(datepart(hh, dbo. Bigtable. [Date Time]), DatePart(mm, dbo. Bigtable. [Data, laikas])) kaip [Data, laikas, valandos minutė] - Jei yra du datos/laiko stulpeliai, pvz., [Pradžios laikas] ir [Pabaigos laikas], o jums iš tikrųjų reikia laiko skirtumo tarp jų sekundėmis kaip stulpelio pavadinimu [Trukmė], pašalinkite abu stulpelius iš sąrašo ir įtraukite:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
Jei naudojate raktažodį ms vietoj ss, gausite trukmę milisekundėmis
DAX apskaičiuotųjų matų naudojimas vietoj stulpelių
Jei anksčiau dirbote su DAX išraiškos kalba, galbūt jau žinote, kad apskaičiuojamieji stulpeliai naudojami naujiems stulpeliams išvesti, remiantis kokiu nors kitu modelio stulpeliu, o apskaičiuotieji matai apibrėžiami vieną kartą modelyje, bet įvertinami tik tada, kai naudojami "PivotTable" ar kitoje ataskaitoje.
Vienas iš atminties taupymo būdų yra įprastų arba apskaičiuotų stulpelių pakeitimas apskaičiuotais matais. Klasikinis pavyzdys yra Vieneto kaina, Kiekis ir Bendroji suma. Jei turite visus tris, galite sutaupyti vietos palikdami tik du ir apskaičiuodami trečią naudodami DAX.
Kuriuos 2 stulpelius turėtumėte palikti?
Aukščiau pateiktame pavyzdyje palikite parinktis Kiekis ir Vieneto kaina. Šių dviejų reikšmių yra mažiau nei bendroji suma. Norėdami apskaičiuoti bendrą sumą, įtraukite apskaičiuotąjį matą, pvz.:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Apskaičiuojamieji stulpeliai yra panašūs į įprastus stulpelius, nes abu užima vietą modelyje. Priešingai, apskaičiuotos priemonės apskaičiuojamos skrendant ir neužima vietos.
Išvados
Šiame straipsnyje kalbėjome apie kelis metodus, kurie gali padėti sukurti efektyvesnį atminties modelį. Norint sumažinti duomenų modelio failo dydį ir atminties poreikį, reikia sumažinti bendrą stulpelių ir eilučių skaičių bei kiekviename stulpelyje rodomų unikalių reikšmių skaičių. Štai keletas mūsų aptartų metodų:
- Stulpelių pašalinimas, žinoma, yra geriausias būdas sutaupyti vietos. Nuspręskite, kurių stulpelių jums tikrai reikia.
- Kartais galite pašalinti stulpelį ir pakeisti jį lentelėje esančiu apskaičiuotuoju matu.
- Lentelėje gali reikėti ne visų eilučių. Eilutes galite filtruoti lentelių importavimo vediklyje.
- Apskritai vieno stulpelio skaidymas į kelias skirtingas dalis yra geras būdas sumažinti unikalių reikšmių stulpelyje skaičių. Kiekviena dalis turės nedidelį unikalių reikšmių skaičių, o bendra suma bus mažesnė nei pradinis vieningas stulpelis.
- Daugeliu atvejų kaip duomenų filtrus ataskaitose taip pat reikia naudoti skirtingas dalis. Jei reikia, galite sukurti hierarchiją iš dalių, pvz., valandų, minučių ir sekundžių.
- Dažnai stulpeliuose yra daugiau informacijos, nei jums reikia. Pvz., tarkime, stulpelyje saugomos dešimtainės skiltys, bet pritaikėte formatavimą, kad paslėptumėte visas dešimtaines skiltis. Apvalinimas gali būti labai efektyvus mažinant skaitinio stulpelio dydį.
Dabar, kai padarėte viską, ką galite, kad sumažintumėte darbaknygės dydį, 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 optimizatorių.
Susiję saitai
Duomenų modelio specifikacija ir apribojimai
Darbaknygės dydžio optimizatorius
„PowerPivot“: galinga duomenų analizė ir duomenų modeliavimas „Excel“