Atmintį taupančio duomenų modelio kūrimas naudojant "Excel" ir "Power Pivot" papildinio versijas

Svarbu :  Šis straipsnis išverstas naudojant mašininį vertimą, žr. atsakomybės atsisakymą. Su šio straipsnio versija anglų kalba galite susipažinti čia .

"Excel 2013" arba naujesnėje versijoje, galite sukurti duomenų modelius, kuriuose yra milijonai eilučių ir tada efektyviai analizuoti duomenis pagal šiuos modelius. Galite sukurti duomenų modelius, su arba be Power Pivot įtraukti į tos pačios darbaknygės palaikyti bet koks kitas skaičius, "PivotTable", diagramomis ir vizualizacijomis Power View.

Pastaba : Šiame straipsnyje aprašoma duomenų modeliai "Excel 2013". Tačiau tų pačių duomenų modeliavimas ir "PowerPivot" funkcijos, įvedama programoje "Excel 2013" taip pat taikymas "Excel 2016". Nėra efektyviai skirtumas šiuos "Excel" versijose.

Nors jūs galite lengvai kurti didelius duomenų modeliai "Excel", yra kelios priežastys negali pasiekti. Pirma, didelius modelius, kuriuose yra minios lentelių ir stulpelių daugumai išėjimas, ir kad sudėtingos laukų sąrašas. Antra, didelio modelių išnaudoti vertingą atmintį, neigiamos įtakos kitų taikomųjų programų ir ataskaitų, bendrinti patį sistemos išteklių. Be to, "Office 365", "SharePoint Online" ir "Excel Web App" ribojamas "Excel" failą į 10 MB. Darbaknygės duomenų modelių, kuriuose yra milijonai eilučių, vykdysite į 10 MB limitas gana greitai. Rasite duomenų modelio specifikacija ir ribos.

Skaitydami šį straipsnį, sužinosite, kaip sukurti glaudžios struktūros modelį, su kuriuo yra lengviau dirbti ir sunaudojama mažiau atminties. Laikas, kurį praleisite mokydamiesi geriausios efektyvaus modelio kūrimo praktikos, atsipirks kuriant ir naudojant modelį, neatsižvelgiant į tai, ar peržiūrite jį „Excel 2013“, „Office 365 SharePoint Online“, „Office Web Apps Server“ ar „SharePoint 2013“ programose.

Mano taip pat veikia darbaknygės dydžio optimizatorius. "Excel" darbaknygės analizuoja ir, jei įmanoma, suglaudina toliau. Atsisiųskite darbaknygės dydžio optimizatorius.

Šiame straipsnyje:

Glaudinimo lygiai ir atminties analizės modulis

Niekas neužima mažiau atminties nei stulpelis

Du pavyzdžiai, kai stulpelių niekada nereikėtų

Kaip neįtraukti nereikalingų stulpelių

Ką reiškia filtruoti tik reikiamas eilutes?

Ką daryti, jei reikia stulpelį; Mes vis dar sumažinti užimamą vietą?

Stulpelių data ir laikas modifikavimas

SQL užklausos modifikavimas

DAX apskaičiuotų reikšmių vietoj stulpelių naudojimas

Kuriuos 2 stulpelius reikia pasilikti?

Sudarymo

Susiję saitai

Glaudinimo lygiai ir atminties analizės modulis

„Excel“ duomenų modeliai duomenims atmintyje saugoti naudoja atminties analizės modulį. Modulis įdiegia galingą glaudinimo funkciją, kad sumažintų reikalavimus saugyklai. Galutinis rinkinys sutraukiamas iki pradinio dydžio dalies.

Vidutiniškai galite tikėtis, kad duomenų modelis bus nuo 7 iki 10 kartų mažesnis nei tie patys duomenys pradinės būsenos. Pavyzdžiui, jei importuojate 7 MB duomenų iš „SQL Server“ duomenų bazės, duomenų modelis programoje „Excel“ gali nesunkiai užimti 1 MB ar mažiau vietos. Iš esmės pasiekiamas glaudinimo laipsnis priklauso nuo unikalių reikšmių skaičiaus kiekviename stulpelyje. Kuo daugiau unikalių reikšmių, tuo daugiau atminties reikia jiems saugoti.

Kodėl kalbame apie glaudinimą ir unikalias reikšmes? Kuriant efektyvų modelį ir siekiant sumažinti atminties naudojimą, svarbiausia yra didesnės glaudinimo galimybės, o lengviausias būdas tai padaryti yra atsikratyti visų nereikalingų stulpelių, ypač, jei šiuose stulpeliuose yra didelis kiekis unikalių reikšmių.

Pastaba : Saugyklos reikalavimai atskiriems stulpeliams gali būti labai skirtingi. Kai kurias atvejais geriau turėti kelis stulpelius su mažesniu unikalių reikšmių kiekiu, o ne vieną stulpelį su daugybe unikalių reikšmių. Skyriuje apie datos ir laiko optimizavimą rasite išsamesnį šio metodo paaiškinimą.

Niekas neužima mažiau atminties nei stulpelis, kurio nėra

Visų pirma, efektyviausiai atmintį naudoja tas stulpelis, kuris niekada nebuvo importuotas. Jei norite sukurti efektyvų modelį, peržiūrėkite kiekvieną stulpelį ir apsvarstykite, ar jis yra naudingas atliekant analizę. Jei naudos jis neteikia arba dėl to abejojate, panaikinkite tą stulpelį. Jei reikės, vėliau galėsite įtraukti naujų stulpelių.

Du pavyzdžiai, kai stulpelių niekada nereikėtų įtraukti

Pirmas pavyzdys susijęs su duomenimis, gautais iš duomenų saugyklos. Įprasta, kad duomenų saugyklose galima rasti ETL procesų artefaktų, kurie įkelia ir atnaujina duomenis saugykloje. Tokie stulpeliai kaip „kurti datą“, „atnaujinti datą“ ir „ETL vykdymas“ sukuriami, kai įkeliami duomenys. Nė vienas iš šių stulpelių nėra reikalingas modelyje ir importuojant duomenis reikėtų panaikinti jo pasirinkimą.

Antras pavyzdys susijęs su pirminio rakto stulpelio praleidimu importuojant faktų lentelę.

Daugelis lentelių, įskaitant faktų lenteles, turi pirminius raktus. Daugelyje tų lentelių, kuriose yra kliento, darbuotojo ar pardavimų duomenų, reikia lentelės pirminio rakto, kad galėtumėte jį naudoti kurdami modelio ryšius.

Faktų lentelės yra kitokios. Faktų lentelėje pirminis raktas naudojamas norint unikaliai atpažinti kiekvieną eilutę. Jis yra būtinas normalizavimo tikslais, tačiau ne toks naudingas duomenų modelyje, kuriame analizuodami norite naudoti tik tuos stulpelius arba sukurti lentelės ryšių. Štai kodėl importuojant iš faktų lentelės nereikėtų importuoti jos pirminio rakto. Faktų lentelėje esantys pirminiai raktai užima daugybę vietos modelyje, tačiau yra visiškai nenaudingi, nes jų negalima naudoti kuriant ryšius.

Pastaba : Duomenų saugyklose ir kelių dimensijų duomenų bazėse didelės lentelės su dažniausiai skaitiniais duomenimis paprastai laikomos faktų lentelėmis. Paprastai faktų lentelėse yra verslo veiklos ar operacijų duomenų, pvz., pardavimo ir kaštų duomenys, kurie kaupiami ir išdėliojami šalia organizacijos skyrių, produktų, rinkos segmentų, geografinių regionų ir kt. Visus faktų lentelės stulpelius, kuriuose yra verslo duomenų arba kuriuos galima naudoti duomenų kryžminėms nuorodoms kitose lentelėse, reikia įtraukti į modelį, kad būtų palaikoma duomenų analizė. Stulpelis, kurio nenorite įtraukti, yra pirminio rakto stulpelis faktų lentelėje, kurią sudaro unikalios reikšmės, esančios tik faktų lentelėje ir niekur kitur. Faktų lentelės yra labai didelės, todėl didžiausias modelio efektyvumas pasiekiamas neįtraukiant eilučių arba stulpelių iš faktų lentelių.

Kaip neįtraukti nereikalingų stulpelių

Efektyvios modelius, kuriuose yra tik tuos stulpelius, kurie iš tikrųjų jums reikės darbaknygėje. Jei norite kontroliuoti, kuriuos stulpelius įtrauktos į modelį, turėsite naudoti "PowerPivot" priedo, kad importuotumėte duomenis į lentelės importavimo vedlys , o ne "Importuoti duomenys" dialogo langas programoje "Excel".

Įjungę lentelių importavimo vediklį, pasirinkite, kurias lenteles norite importuoti.

Lentelių importavimo vediklis „PowerPivot“ priede

Kiekvienoje lentelėje galite spustelėti mygtuką Peržiūrėti ir filtruoti ir pasirinkti reikiamas lentelės dalis. Rekomenduojame pirmiausia panaikinti visų stulpelių žymėjimą, tada apgalvojus, kurių stulpelių reikia analizei, pažymėti visus reikiamus stulpelius.

Peržiūros rodinys lentelių importavimo vediklyje

Ką reiškia filtruoti tik reikiamas eilutes?

Daugybėje lentelių, esančių bendrose duomenų bazėse ir duomenų saugyklose yra retrospektyvinių duomenų, sukauptų per ilgą laikotarpį. Be to, gali būti, kad jus dominančiose lentelėse yra informacijos iš tų versto sričių, kurioms nėra būtina konkreti analizė.

Naudodami lentelių importavimo vediklį, galite išfiltruoti retrospektyvinius arba nesusijusius duomenis ir taip modelyje sutaupyti daug vietos. Kitame atvaizde matomas duomenų filtras, kuris naudojamas nuskaityti tik toms eilutėms, kuriose yra šių metų duomenų, neįtraukiant nereikalingų retrospektyvinių duomenų.

Filtravimo sritis lentelių importavimo vediklyje

Kaip sumažinti užimamą vietą, jei stulpelis vis tik yra reikalingas?

Yra keli papildomi metodai, kaip stulpelį paversti patogesniu objektu glaudinant. Atminkite, kad unikalių reikšmių skaičius yra vienintelė stulpelio ypatybė, turinti įtakos glaudinimui. Šiame skyriuje sužinosite, kaip modifikuoti kelis stulpelius ir sumažinti unikalių reikšmių skaičių.

Stulpelių Data ir laikas modifikavimas

Daugeliu atvejų stulpeliai Data ir laikas užima daug vietos. Laimei, yra daugybė būdų, kaip sumažinti saugyklos reikalavimus šiam duomenų tipui. Metodai skiriasi atsižvelgiant į tai, kaip naudojate stulpelį, ir komforto lygį kuriant SQL užklausas.

Stulpeliuose Data ir laikas yra datos dalis ir laikas. Jei svarstysite, ar jums reikalingas įprastas stulpelis, susidūrę su stulpeliu Data ir laikas, to paties savęs paklauskite kelis kartus:

  • Ar reikia laiko dalies?

  • Ar reikia laiko dalies valandų, minučių, sekundžių ar milisekundžių tikslumu?

  • Ar turite kelis stulpelius Data ir laikas, nes norite suskaičiuoti skirtumą tarp jų, ar tiesiog kaupiate duomenis pagal metus, mėnesius, ketvirčius ir pan.

Atsakymas į kiekvieną klausimą nustato jūsų stulpelio Data ir laikas tvarkymo parinktis.

Visiems šiems sprendimams reikia SQL užklausos modifikacijos. Kad būtų lengviau atlikti modifikaciją, turite išfiltruoti bent vieną stulpelį kiekvienoje lentelėje. Išfiltravę stulpelį, pakeiskite užklausos struktūrą iš sutrumpinto formato (SELECT *) į SELECT patvirtinimą, kuriame yra tinkami stulpelių pavadinimai, kuriuos pakeisti daug lengviau.

Peržiūrėkime užklausas, kurias jums sukūrėme. Dialogo lange Lentelės ypatybės galite perjunti užklausų rengyklę ir peržiūrėti dabartinę kiekvienos lentelės SQL užklausą.

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

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

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

Užklausos rengyklėje rodoma SQL užklausa, naudojama lentelei kurti. Jei importuodami išfiltruojate kurį nors stulpelį, užklausoje yra tinkami stulpelių pavadinimai:

SQL užklausa, naudojama duomenims nuskaityti

Priešingai, jei importuojate visą lentelę nepašalindami stulpelio žymėjimo ir netaikydami jokio filtro, matysite užklausą “Select * from ”, kurią bus sunkiau pakeisti:

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

SQL užklausos modifikavimas

Dabar, kai jau žinote, kaip rasti užklausą, galite ją modifikuoti ir dar labiau sumažinti modelio dydį.

  1. Jei stulpeliuose, kuriuose yra valiutos arba dešimtainiai duomenys, jums nebereikia dešimtainių duomenų, naudokite šią sintaksę ir pašalinkite šiuos dešimtainius duomenis:

    “SELECT ROUND([Dešimtainio_stulpelio_pavadinimas],0)… .”

    Jei jums reikia centų, tačiau ne cento dalių, 0 pakeiskite į 2. Jei naudosite neigiamus skaičius, galite apvalinti iki vienetų, dešimčių, šimtų ir pan.

  2. Jei turite stulpelį Data ir laikas, pavadintą dbo.Bigtable.[Date Time], ir jums nereikia dalies Laikas, naudokite šią sintaksę ir pašalinkite laiką:

    “SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “

  3. Jei turite stulpelį Data ir laikas, pavadintą dbo.Bigtable.[Date Time], ir jums reikia ir dalies Data, ir Laikas, naudokite kelis stulpelius SQL užklausoje, užuot naudoję vieną stulpelį Data ir laikas:

    “SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    Įrašydami kiekvieną dalį atskiruose stulpeliuose, naudokite tiek stulpelių, kiek reikia.

  4. Jei reikia valandų ir minučių ir pageidaujate jų viename stulpelyje, naudokite šią sintaksę :

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Jei turite du stulpelius Data ir laikas, pvz., [Start Time] ir [End Time], ir jums reikia laiko skirtumo tarp jų sekundėmis kaip stulpelyje [Duration], pašalinkite abu stulpelius iš sąrašo ir įtraukite:

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

    Jei vietoj raktinio žodžio ss naudosite ms, gausite trukmę milisekundėmis

DAX apskaičiuotų reikšmių naudojimas vietoj stulpelių

Jei anksčiau esate dirbę su DAX reiškinių kalba, tikriausiai jau žinote, kad apskaičiuotieji stulpeliai yra naudojami gaunant naujus stulpelius, pagrįstus kitais modelio stulpeliais, o apskaičiuoti matavimai modelyje apibrėžiami vieną kartą, tačiau vertinami tik tada, kai naudojami „PivotTable“ arba kitoje ataskaitoje.

Vienas iš atminties taupyme metodų yra įprastų arba apskaičiuotųjų stulpelių pakeitimas apskaičiuotais matavimais. Klasikinis pavyzdys yra su stulpeliais Vieneto kaina, Kiekis ir Suma. Jei turite visus tris, galite sutaupyti vietos pasilikdami du ir skaičiuodami trečią per DAX.

Kuriuos 2 stulpelius reikia pasilikti?

Anksčiau pateiktame pavyzdyje paliekami stulpeliai Kiekis ir Vieneto kaina. Juose yra mažiau reikšmių nei stulpelyje Suma. Norėdami apskaičiuoti Sumą, įtraukite apskaičiuotą matavimą, pvz.,:

“Pardavimosuma:=sumx(‘Pardavimo lentelė’,’Pardavimo lentelė’[Vieneto kaina]*’Pardavimo lentelė’[Kiekis])”

Apskaičiuotieji stulpeliai yra kaip įprasti stulpeliai ir abu užima modelio vietos. Palyginkite: apskaičiuoti matavimai skaičiuojami lengvai ir neužima vietos.

Išvados

Šiame straipsnyje aptarėme kelis metodus, kurie gali padėti sukurti efektyviau atmintį naudojantį modelį. Siekiant sumažinti failo dydį ir duomenų modelio atminties reikalavimus, reikia sumažinti bendrą stulpelių ir eilučių skaičių bei kiekviename stulpelyje rodomų unikalių reikšmių skaičių. Čia aptarėme kelis metodus:

  • Be abejo, stulpelių pašalinimas yra geriausias vietos taupymo būdas. Nuspręskite, kurių stulpelių jums tikrai reikia.

  • Kartais galite pašalinti stulpelį iš lentelės ir pakeisti jį apskaičiuotu matavimu.

  • Galbūt jums reikia ne visų eilučių lentelėje. Eilutes galite išfiltruoti naudodami lentelių importavimo vediklį.

  • Paprastai tariant, suskaidant vieną stulpelį į kelias skirtingas dalis galima lengvai sumažinti unikalių reikšmių skaičių stulpelyje. Kiekvienoje dalyje bus nedidelis unikalių reikšmių skaičius, o bendras skaičius bus mažesnis nei pradiniame sujungtame stulpelyje.

  • Daugeliu atvejų jums taip pat prireiks skirtingų dalių tam, kad ataskaitose galėtumėte naudoti jas kaip duomenų filtrus. Kai reikia, galite kurti hierarchijas iš tokių dalių kaip Valandos, Minutės ir Sekundės.

  • Daugeliu atvejų stulpeliuose yra daugiau informacijos, jei jums reikia. Pavyzdžiui, stulpelyje saugomi dešimtainiai duomenys, tačiau pritaikėte formatavimą, kad paslėptumėte visus dešimtainius duomenis. Apvalinimas gali būti labai naudingas siekiant sumažinti skaitinio stulpelio dydį.

Dabar, kai tai padarysite, jūs galite sumažinti savo darbaknygę, mano taip pat veikia darbaknygės dydžio optimizatorius. "Excel" darbaknygės analizuoja ir, jei įmanoma, suglaudina toliau. Atsisiųskite darbaknygės dydžio optimizatorius.

Susiję saitai

Duomenų modelio specifikacija ir apribojimai

Darbaknygės dydžio optimizatorius atsisiuntimas

"PowerPivot": Efektyvi duomenų analizė ir duomenų modeliavimas "Excel"

Pastaba : Mašininio vertimo atsakomybės atsisakymas: Šis straipsnis išverstas naudojant kompiuterinę sistemą be žmogaus įsikišimo. „Microsoft“ pateikia šiuos mašininius vertimus norėdama padėti anglų kalbos nesuprantantiems vartotojams perskaityti turinį apie „Microsoft“ produktus, paslaugas ir technologijas. Šis straipsnis išverstas mašininio vertimo būdu, todėl jame gali būti žodyno, sintaksės ar gramatikos klaidų.

Tobulinkite savo įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×