Prisijunkite prie „Microsoft“
Prisijunkite arba sukurkite paskyrą.
Sveiki,
Pasirinkti kitą paskyrą.
Turite kelias paskyras
Pasirinkite paskyrą, kurią naudodami norite prisijungti.

"Excel 2013" arba naujesnėse versijose galite kurti duomenų modelius, kuriuose yra milijonai eilučių, tada atlikti efektyvią duomenų analizę pagal šiuos modelius. Duomenų modelius galima kurti naudojant arba Power Pivot papildinį, kad būtų galima palaikyti bet kokį "PivotTable", diagramų ir "Power View" vizualizacijų skaičių 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“. Šių "Excel" versijų skirtumas yra Excel.

Nors galite lengvai kurti didžiulius duomenų modelius Excel, yra kelios priežastys, kodėl to negalima padaryti. Pirma, dideli modeliai, kuriuose yra daug lentelių ir stulpelių, daugeliui analizių yra pernelyg dideli ir sudaro sudėtingas laukų sąrašas. Antra, dideli modeliai naudoja vertingą atmintį, neigiamai veikia kitas taikomąsias programas ir ataskaitas, kurios naudoja tuos pačius sistemos išteklius. Galiausiai „Microsoft 365“ " SharePoint Online" ir "Excel Web App" riboja failo Excel iki 10 MB. Darbaknygės duomenų modeliuose, kuriuose yra milijonai eilučių, gana greitai turėsite 10 MB limitą. Žr. Duomenų modelio specifikacija ir apribojimai.

Šiame straipsnyje sužinosite, kaip sukurti glaudžiai sukonstruotas modelį, su kurį būtų lengviau dirbti ir kuriame naudojama mažiau atminties. Atsižvelgiant laiko išmokti geriausios praktikos efektyviam modelio dizainui, atsipirks kelias už bet kurį kuriamą ir naudoti modelį, nesvarbu, ar peržiūrite jį "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

Duomenų modeliai Excel atminties analizės moduliu duomenims saugoti atmintyje. Variklis įdiegia galingus glaudinimo būdus, kad sumažintų saugyklos reikalavimus, sumažindamas rezultatų rinkinį, kol jis bus pradinio dydžio dalis.

Vidutiniškai galima tikėtis, kad duomenų modelis bus nuo 7 iki 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 nesunkiai 1 MB arba mažesnis. Faktiškai pasiektas glaudinimo laipsnis pirmiausia priklauso nuo unikalių reikšmių skaičiaus kiekviename stulpelyje. Daugiau unikalių reikšmių, tuo daugiau atminties reikia jas saugoti.

Kodėl kalbame apie glaudinimą ir unikalias reikšmes? Kadangi kuriant efektyvią modelį, kuris minimizuoja atminties naudojimą, svarbu glaudinti maksimizaciją, o paprasčiausias būdas tai padaryti – atsikratyti visų stulpelių, kurių jums iš tikrųjų nereikia, ypač jei tuose stulpeliuose yra daug unikalių reikšmių.

Pastaba: Atskirų stulpelių saugojimo reikalavimų skirtumai gali būti dideli. Kai kuriais atvejais geriau turėti kelis stulpelius su mažu unikalių reikšmių skaičiumi, o ne vienu stulpeliu su dideliu unikalių reikšmių skaičiumi. "Datetime" optimizavimo skyrius išsamiai aptars šį metodą.

Niekas nepralenkia neegzistuojančių stulpelių, kad būtų naudojama mažai atminties

Efektyviausias atminties stulpelis yra tas, kurį niekada neimportavote pirmoje vietoje. Jei norite sukurti veiksmingą modelį, peržiūrėkite kiekvieną stulpelį ir paklauskite savęs, ar jis padeda atlikti norimą analizę. Jei jis nėra arba nesate tikri, palikite jį. Jei jų prireiks, visada galėsite įtraukti naujų stulpelių vėliau.

Du stulpelių, kurie visada turėtų būti neįtraukti, pavyzdžiai

Pirmasis pavyzdys susijęs su duomenimis, kilusiais iš duomenų saugyklos. Duomenų sandėlyje dažnai galima rasti ETL procesų, kurie įkelia ir atnaujina duomenis į sandėlį, artefaktų. Įkeliant duomenis sukuriami stulpeliai, pvz., "kurti datą", "naujinimo data" ir "ETL vykdyti". Nė vienas iš šių stulpelių nėra reikalingas modelyje ir turi būti atžymėtas importuojant duomenis.

Antrasis pavyzdys apima pirminio rakto stulpelio nedingdant importuojant faktų lentelę.

Daugelyje lentelių, įskaitant faktų lenteles, yra pirminiai raktai. Daugumoje lentelių, pvz., kuriose yra klientų, darbuotojų arba pardavimo duomenų, norite, kad lentelės pirminis raktas būtų naudojamas kuriant ryšius modelyje.

Faktų lentelės skiriasi. Faktų lentelėje pirminis raktas naudojamas unikaliai identifikuoti kiekvieną eilutę. Nors tai būtina normalizavimo tikslais, jis mažiau naudingas duomenų modelyje, kuriame norite naudoti tik tuos stulpelius, kurie naudojami analizei arba lentelių santykiams nustatyti. Dėl šios priežasties importuojant iš faktų lentelės, jo pirminio rakto nėra. Pirminiai raktai faktų lentelėje naudoja milžiniškus tarpus modelyje, tačiau jie nėra naudingi, nes jų negalima naudoti kuriant ryšius.

Pastaba: Duomenų saugyklose ir kelių lygių 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 agreguojami 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 kryžminės nuorodos duomenims, saugomims kitose lentelėse, turėtų būti įtraukti į modelį, kad būtų galima palaikyti duomenų analizę. Stulpelis, į kurį norite neįtraukti, yra pirminis faktų lentelės stulpelis, kurį sudaro unikalios reikšmės, kurios yra tik faktų lentelėje ir niekur kitur. Kadangi faktų lentelės yra labai didelės, kai kurie didžiausi modelio efektyvumo pasiekimai gaunami neįtraukiant eilučių ar stulpelių iš faktų lentelių.

Kaip neįtraukti nereikalingų stulpelių

Efektyviuose modeliuose yra tik tie stulpeliai, kurių iš tikrųjų reikės darbaknygėje. Jei norite kontroliuoti, kurie stulpeliai įtraukti į modelį, turite naudoti "Power Pivot" papildinį lentelės importavimo vediklį, kad importuotumėte duomenis, o ne dialogo langą "Importuoti duomenis" Excel.

Kai paleidžiate lentelės importavimo vediklį, pasirenkate, kurias lenteles importuoti.

Lentelių importavimo vediklis „PowerPivot“ priede

Kiekvienai lentelei galite spustelėti mygtuką & filtras ir pasirinkti lentelės dalis, kurių jums tikrai reikia. Pirmiausia rekomenduojame panaikinti visų stulpelių žymėjimą, o tada patikrinti norimus stulpelius, apsvarstęs, ar jie reikalingi analizei.

Peržiūros rodinys lentelių importavimo vediklyje

O kaip filtruoti tik reikiamas eilutes?

Daugelyje lentelių įmonės duomenų bazėse ir duomenų saugyklose yra istorinių duomenų, sukauptų per ilgą laiką. Be to, galite rasti, kad jus dominamose lentelėse yra informacijos apie verslo sritis, kurių nereikia konkrečiai analizei atlikti.

Naudodami lentelės importavimo vediklį, galite filtruoti istorinius arba nesusijusius duomenis ir taip sutaupyti daug vietos modelyje. Toliau pateiktame paveikslėlyje datos filtras naudojamas tik eilutėms, kuriose yra dabartinių metų duomenų, išskyrus istorinius duomenis, kurių nereikia.

Filtravimo sritis lentelių importavimo vediklyje

Ką daryti, jei mums reikia stulpelio; ar vis tiek galime sumažinti vietos kainą?

Yra keletas papildomų būdų, kuriuos galite taikyti, kad stulpelis būtų geriau suglaudinimas. Atminkite, kad vienintelė stulpelio ypatybė, kuri paveikia glaudinimą, yra unikalių reikšmių skaičius. Šiame skyriuje sužinosite, kaip kai kuriuos stulpelius galima modifikuoti, kad būtų sumažintas unikalių reikšmių skaičius.

Datos ir laiko stulpelių modifikavimas

Daugeliu atvejų datos ir laiko stulpeliai užims daug vietos. Laimei, yra keletas būdų, kaip sumažinti šio duomenų tipo saugojimo reikalavimus. Metodai skirsis, atsižvelgiant į tai, kaip naudojate stulpelį, ir jūsų patogumo lygį kuriant SQL užklausas.

Datos ir laiko stulpeliuose yra datos dalis ir laikas. Kai paklausite 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? , minutėmis? , Sekundės? , milisekundes?

  • 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 parinktis, kaip elgtis su stulpeliu Datetime.

Visiems šiems sprendimams reikia modifikuoti SQL užklausą. Norėdami palengvinti užklausos modifikavimą, kiekvienoje lentelėje turėtumėte filtruoti bent vieną stulpelį. Išfiltruodami stulpelį, pakeičiate užklausų konstrukciją iš sutrumpinto formato (SELECT *) į sakinį SELECT, kuriame yra visiškai apibrėžtų stulpelių pavadinimų, kuriuos daug lengviau modifikuoti.

Pažvelkime į jums sukurtas užklausas. Dialogo lange Lentelės ypatybės galite pereiti į užklausų rengyklę ir peržiūrėti dabartinę SQL kiekvienos lentelės užklausą.

„PowerPivot“ lango juostelė, kurioje rodoma komanda Lentelės ypatybės

Lentelės ypatybėse pasirinkite Užklausų rengyklė.

Atidarykite užklausų rengyklę dialogo lange Lentelės ypatybės

Užklausų rengyklė SQL užklausą, naudojamą lentelei užpildyti. Jei importuodami išfiltravote bet kurį stulpelį, jūsų užklausoje yra visiškai apibrėžtų stulpelių pavadinimai:

SQL užklausa, naudojama duomenims nuskaityti

Priešingai, jei importavote visą lentelę, nepažymėsite jokių stulpelių ar netaikysite jokio filtro, matysite užklausą kaip "Pasirinkti * iš ", o tai bus sudėtingiau modifikuoti:

SQL užklausa naudojant numatytąją trumpesnę sintaksę

Užklausos SQL keitimas

Dabar, kai žinote, kaip rasti užklausą, galite ją modifikuoti, kad dar labiau sumažintumėte modelio dydį.

  1. Stulpeliuose, kuriuose yra valiutos arba dešimtainių duomenų, jei jums nereikia dešimtainių skaičių, naudokite šią sintaksę, kad atsikratyto dešimtainio skyriklio:

    "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šimtų, šimtų ir t. t.

  2. Jei turite datos ir laiko stulpelį, pavadintą dbo. Bigtable. [Datos laikas] ir jums nereikia dalies Laikas, naudokite sintaksę, kad atsikratyto laiko:

    "SELECT CAST (dbo. Bigtable. [Datos laikas] kaip data) AS [Datos laikas]) "

  3. Jei turite datos ir laiko stulpelį, pavadintą dbo. Bigtable. [Datos laikas] ir jums reikia tiek datos, tiek laiko dalių, naudokite kelis stulpelius SQL užklausoje, o ne viename stulpelyje Datetime:

    "SELECT CAST (dbo. Bigtable. [Datos laikas] kaip data ) AS [Datos laikas],

    datepart(hh, dbo. Bigtable. [Datos laikas]) kaip [Datos laikas valandos],

    datepart(mi, dbo. Bigtable. [Datos laikas]) kaip [Datos laiko minutės],

    datepart(ss, dbo. Bigtable. [Datos laikas]) kaip [Datos laiko sekundės],

    datepart(ms, dbo. Bigtable. [Datos laikas]) kaip [Datos laikas milisekundės]"

    Naudokite tiek stulpelių, kiek reikia, kad kiekviena dalis būtų saugoma atskiruose stulpeliuose.

  4. Jei jums reikia valandų ir minučių ir norite, kad jos būtų sujungtos kaip vieno laiko stulpelis, galite naudoti sintaksę :

    Timefromparts(datepart(hh, dbo. Bigtable. [Datos laikas]), datepart(mm, dbo. Bigtable. [Datos laikas])) kaip [Date Time HourMinute]

  5. Jei turite du datos ir laiko stulpelius, pvz., [Pradžios laikas] ir [Pabaigos laikas], ir jums iš tikrųjų reikia laiko skirtumo tarp jų per kelias sekundes, kai stulpelis vadinamas [Trukmė], pašalinkite abu stulpelius iš sąrašo ir įtraukite:

    "datediff(ss,[Start Date],[End Date]) as [Duration]"

    Jei naudojate raktažodį ms, o ne ss, trukmę gausite milisekundėse

DAX apskaičiuotųjų priemonių, 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 išvesti pagal kitą modelio stulpelį, o apskaičiuotos priemonės apibrėžiamos vieną kartą modelyje, bet įvertinamos tik tada, kai naudojamos "PivotTable" arba kitoje ataskaitoje.

Vienas atminties taupymo būdas yra pakeisti įprastus arba apskaičiuotus stulpelius apskaičiuotomis priemonėmis. Klasikinis pavyzdys yra Vieneto kaina, Kiekis ir Suma. Jei turite visus tris, galite sutaupyti vietos išlaikę tik du ir apskaičiuodami trečią naudodami DAX.

Kuriuos 2 stulpelius turėtumėte išlaikyti?

Aukščiau pateiktame pavyzdyje išsaugokite Kiekis ir Vieneto kaina. Šių dviejų reikšmių yra mažiau nei sumos. Norėdami apskaičiuoti sumą, įtraukite apskaičiuotąjį matą, pvz.:

"TotalSales:=sumx('Sales Table','Sales Table'[Vieneto kaina]*'Pardavimo lentelė'[Kiekis])"

Apskaičiuojami stulpeliai yra lyg įprasti stulpeliai, nes abu užims vietos modelyje. Priešingai, apskaičiuotos priemonės apskaičiuojamos skrenda ir užims tarpą.

Išvados

Šiame straipsnyje kalbėjome apie kelis būdus, kurie gali padėti sukurti atminties taupančių modelių. Būdas sumažinti duomenų modelio failo dydį ir atminties reikalavimus yra sumažinti bendrą stulpelių ir eilučių skaičių bei unikalių reikšmių skaičių kiekviename stulpelyje. Štai keli būdai, kuriuos mes apimame:

  • Žinoma, stulpelių šalinimas yra geriausias būdas sutaupyti vietos. Nuspręskite, kurių stulpelių jums tikrai reikia.

  • Kartais galite pašalinti stulpelį ir pakeisti jį lentelėje apskaičiuotu matu.

  • Gali reikėti ne visų lentelės eilučių. Lentelės importavimo vediklio eilutes galite filtruoti.

  • Apskritai, vieno stulpelio išardymas į kelias skirtingas dalis yra geras būdas sumažinti unikalių reikšmių skaičių stulpelyje. Kiekviena iš dalių turės nedidelį skaičių unikalių reikšmių, o bendra suma bus mažesnė už pradinį vieningą stulpelį.

  • Daugeliu atvejų taip pat reikia atskirų dalių, kad ataskaitose būtų galima naudoti kaip duomenų filtrus. Jei 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 taikėte formatavimą, kad paslėptumėte visus dešimtainius skaičius. Apvalinimas gali būti labai efektyvus mažinant skaitinio stulpelio dydį.

Dabar, kai atlikote tai, ką galite padaryti, kad sumažinsite darbaknygės dydį, apsvarstykite galimybę paleisti 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“

Reikia daugiau pagalbos?

Norite daugiau parinkčių?

Sužinokite apie prenumeratos pranašumus, peržiūrėkite mokymo kursus, sužinokite, kaip apsaugoti savo įrenginį ir kt.

Bendruomenės padeda užduoti klausimus ir į juos atsakyti, pateikti atsiliepimų ir išgirsti iš ekspertų, turinčių daug žinių.

Ar ši informacija buvo naudinga?

Ar esate patenkinti kalbos kokybe?
Kas turėjo įtakos jūsų įspūdžiams?
Paspaudus mygtuką Pateikti, jūsų atsiliepimai bus naudojami tobulinant „Microsoft“ produktus ir paslaugas. Jūsų IT administratorius galės rinkti šiuos duomenis. Privatumo patvirtinimas.

Dėkojame už jūsų atsiliepimą!

×