Ustvarjanje podatkovnega modela, ki učinkovito izkoristi prostor pomnilnika, s programom Excel in dodatkom Power Pivot

V Excelu lahko ustvarite podatkovne modele z milijoni vrstic in nato izvedete zmogljivo analizo podatkov s temi modeli. Podatkovne modele je mogoče ustvariti z dodatkom Power Pivot ali brez njega, da podpirajo poljubno število vrtilnih tabel, grafikonov in ponazoritev Power View v istem delovnem zvezku.

Čeprav lahko v Excelu brez težav ustvarite ogromne podatkovne modele, obstaja več razlogov, zakaj tega ne želite. Prvič, veliki modeli, ki vsebujejo številne tabele in stolpce, so pretirani za večino analiz in ustvarijo okoren seznam polj. Drugič, veliki modeli porabijo dragocen pomnilnik, kar negativno vpliva na druge aplikacije in poročila, ki si delijo iste sistemske vire. V okolju Microsoft 365 tako SharePoint Online kot Excel Web App omejita velikost Excelove datoteke na 10 MB. Pri podatkovnih modelih delovnih zvezkov, ki vsebujejo na milijone vrstic, boste hitro dosegli omejitev 10 MB. Oglejte si specifikacije in omejitve podatkovnega modela.

V tem članku boste izvedeli, kako ustvarite tesno zgrajen model, s katerim je lažje delati in porablja manj pomnilnika. Če si vzamete čas in se naučite najboljših praks za učinkovito oblikovanje modela, se bo vsak model, ki ga ustvarite in uporabljate, izplačalo, ne glede na to, ali si ga ogledujete v Excelu, Microsoft 365 SharePoint Onlineu, strežniku Office Web Apps Server ali SharePointu.

Razmislite o tem, da bi zagnali tudi optimizator velikosti delovnega zvezka. Ta analizira Excelov delovni zvezek in, če je mogoče, ga še dodatno stisne. Prenesite optimizator velikosti delovnega zvezka.

V tem članku

• Kompresijska razmerja in mehanizem za analizo v pomnilniku

• Nič ni boljšega kot neobstoječ stolpec za nizko porabo pomnilnika

  • Dva primera stolpcev, ki ju je treba vedno izključiti
  • Kako izključiti nepotrebne stolpce

• Kaj pa filtriranje le potrebnih vrstic?

• Kaj, če potrebujemo stolpec; Ali lahko še vedno zmanjšamo stroške prostora?

  • Spreminjanje stolpcev »Datetime«
  • Spreminjanje poizvedbe SQL

• Uporaba izračunanih mer DAX namesto stolpcev

  • Katera 2 stolpca bi morali obdržati?

• Zaključek

• Sorodne povezave

Kompresijska razmerja in mehanizem za analizo v pomnilniku

Podatkovni modeli v Excelu za shranjevanje podatkov v pomnilnik uporabljajo mehanizem za analizo v pomnilniku. Motor uporablja zmogljive tehnike stiskanja za zmanjšanje zahtev po shranjevanju in krčenje nabora rezultatov, dokler ni delček njegove prvotne velikosti.

V povprečju lahko pričakujete, da bo podatkovni model 7 do 10-krat manjši od istih podatkov na prvotni točki. Če na primer uvažate 7 MB podatkov iz zbirke podatkov strežnika SQL Server, bi lahko podatkovni model v Excelu vseboval 1 MB ali manj. Stopnja stisnje, ki je dejansko dosežena, je odvisna predvsem od števila enoličnih vrednosti v vsakem stolpcu. Več enoličnih vrednosti, več pomnilnika potrebujete za shranjevanje.

Zakaj govorimo o stiskanju in enoličnih vrednostih? Ker gre za ustvarjanje učinkovitega modela, ki minimizira uporabo pomnilnika, predvsem maksimiziranje stiskanja, to najlažje pa storite tako, da se znebite stolpcev, ki jih v resnici ne potrebujete, še posebej, če ti stolpci vključujejo veliko število enoličnih vrednosti.

Nič ni boljšega kot neobstoječ stolpec za nizko porabo pomnilnika

Stolpec, ki izkoristi največ prostora za pomnilnik, je tisti, ki ga sploh niste uvozili. Če želite zgraditi učinkovit model, si oglejte vsak stolpec in se vprašajte, ali prispeva k analizi, ki jo želite izvesti. Če ne deluje ali niste prepričani, ga izpustite. Nove stolpce lahko pozneje kadar koli dodate, če jih potrebujete.

Dva primera stolpcev, ki ju je treba vedno izključiti

Prvi primer se nanaša na podatke, ki izvirajo iz podatkovnega skladišča. V skladišču podatkov je pogosto najti artefakte procesov ETL, ki naložijo in osvežijo podatke v skladišču. Stolpci, kot so »datum ustvarjanja«, »datum posodobitve« in »zagon ETL«, so ustvarjeni, ko so podatki naloženi. Noben od teh stolpcev ni potreben v modelu, zato ob uvozu podatkov ne bi smeli biti izbrani.

Drugi primer vključuje izpustitev stolpca s primarnim ključem pri uvozu tabele dejstev.

Številne tabele, vključno s tabelami dejstev, imajo primarne ključe. Za večino tabel, na primer tabele, ki vsebujejo podatke o stranki, zaposlenih ali prodaji, boste želeli primarni ključ tabele, s katerim boste lahko z njimi ustvarjali odnose v modelu.

Tabele z dejstvi so drugačne. V tabeli dejstev je primarni ključ uporabljen za enolično identifikacijo vsake vrstice. Čeprav je to potrebno za normaliziranje, je manj uporabno v podatkovnem modelu, kjer želite za analizo ali vzpostavitev relacij tabele uporabiti le tiste stolpce. Zato pri uvozu iz tabele dejstev ne vključite primarnega ključa. Primarni ključi v tabeli dejstev porabijo ogromno prostora v modelu, vendar ne zagotavljajo nobene koristi, saj jih ni mogoče uporabiti za ustvarjanje odnosov.

Kako izključiti nepotrebne stolpce

Učinkoviti modeli vsebujejo le tiste stolpce, ki jih boste dejansko potrebovali v delovnem zvezku. Če želite nadzorovati, kateri stolpci so vključeni v model, morate podatke uvoziti s čarovnikom za uvažanje tabel v dodatku Power Pivot in ne s pogovornim oknom »Uvoz podatkov« v Excelu.

Ko zaženete čarovnika za uvažanje tabel, izberete tabele, ki jih želite uvoziti.

Za vsako tabelo lahko kliknete gumb »Predogled & Filter« in izberete dele tabele, ki jih resnično potrebujete. Priporočamo, da najprej počistite vse stolpce in nato nadaljujete s preverjanjem želenih stolpcev, ko razmislite, ali so potrebni za analizo.

Kaj pa filtriranje le potrebnih vrstic?

Številne tabele v zbirkah podatkov podjetja in podatkovnih skladiščih vsebujejo zgodovinske podatke, zbrane v daljših časovnih obdobjih. Poleg tega boste morda ugotovili, da tabele, ki vas zanimajo, vsebujejo informacije za področja poslovanja, ki jih ne potrebujete za določeno analizo.

S čarovnikom za uvažanje tabel lahko filtrirate zgodovinske in nepovezane podatke in tako prihranite veliko prostora v modelu. Na spodnji sliki je datumski filter uporabljen za pridobivanje samo vrstic, ki vsebujejo podatke za tekoče leto, razen zgodovinskih podatkov, ki jih ne potrebujete.

Kaj, če potrebujemo stolpec; Ali lahko še vedno zmanjšamo stroške prostora?

Obstaja nekaj dodatnih tehnik, ki jih lahko uporabite, da stolpec postane boljši kandidat za stiskanje. Ne pozabite, da je edina značilnost stolpca, ki vpliva na stiskanje, število enoličnih vrednosti. V tem razdelku boste izvedeli, kako lahko spremenite nekatere stolpce in tako zmanjšate število enoličnih vrednosti.

Spreminjanje stolpcev »Datetime«

V številnih primerih stolpci »Datetime« zavzamejo veliko prostora. Na srečo obstaja več načinov za zmanjšanje zahtev za shranjevanje za to vrsto podatkov. Tehnike se razlikujejo glede na način uporabe stolpca in raven udobja pri ustvarjanju poizvedb SQL.

Stolpci »Datetime« vsebujejo datumske dele in čase. Ko se vprašate, ali potrebujete stolpec, večkrat zastavite isto vprašanje za stolpec »Datetime«:

• Ali potrebujem časovni del?
• Ali potrebujem časovni del na ravni ur? , minute? , sekunde? , milisekunde?
• Ali imam več stolpcev »Datetime«, ker želim izračunati razliko med njimi, ali pa želim le združiti podatke po letih, mesecih, četrtletjih in tako naprej?

Način, kako odgovorite na vsako vprašanje, določa vaše možnosti obravnavanja stolpca »Datetime«.

Vse te rešitve zahtevajo spremembo poizvedbe SQL. Če želite poenostaviti spreminjanje poizvedbe, morate filtrirati vsaj en stolpec v vsaki tabeli. Če filtrirate stolpec, lahko spremenite strukturo poizvedbe iz okrajšave (SELECT *) v izjavo SELECT, ki vključuje popolnoma določena imena stolpcev, ki jih je veliko lažje spreminjati.

Oglejmo si poizvedbe, ki so bile ustvarjene za vas. V pogovornem oknu Lastnosti tabele lahko preklopite na urejevalnik poizvedb in si ogledate trenutno poizvedbo SQL za vsako tabelo.

V razdelku Lastnosti tabele izberite Urejevalnik poizvedb.

Urejevalnik poizvedb prikaže poizvedbo SQL, ki je bila uporabljena za izpolnitev tabele. Če ste med uvozom filtrirali kateri koli stolpec, poizvedba vključuje popolnoma določena imena stolpcev:

Če pa ste uvozili tabelo v celoti, ne da bi počistili kateri koli stolpec ali uporabili kateri koli filter, bo poizvedba prikazana kot »Izberi * iz«, kar bo težje spremeniti:

Spreminjanje poizvedbe SQL

Zdaj, ko veste, kako poiskati poizvedbo, jo lahko spremenite in tako dodatno zmanjšate velikost modela.

1. Če za stolpce, ki vsebujejo podatke o valuti ali decimalnih številkah, uporabite to sintakso, da se znebite decimalk:
   "SELECT ROUND([Decimal_column_name],0)... .”
   Če potrebujete cente, ne pa tudi delcev centov, zamenjajte 0 z 2. Če uporabljate negativna števila, lahko zaokrožite na enote, desetine, stotine itd.
2. Če imate stolpec »Datetime« z imenom dbo. Velika miza. [Datum in čas] in ne potrebujete dela Čas, uporabite sintakso, da se znebite časa:
   »SELECT CAST (dbo. Velika miza. [Datum in čas] kot datum) AS [Datum in čas]) "
3. Če imate stolpec »Datetime« z imenom dbo. Velika miza. [Date Time] in potrebujete oba dela Datum in Čas, uporabite več stolpcev v poizvedbi SQL namesto enega stolpca Datetime:
   »SELECT CAST (dbo. Velika miza. [Datum in čas] kot datum) AS [datum in čas],
   datepart(hh, dbo. Velika miza. [Datum in čas]) kot [Datum Čas Ure],
   datepart(mi, dbo. Velika miza. [Datum in čas]) kot [Datum, Čas, Minute],
   datepart(ss, dbo. Velika miza. [Datum in čas]) kot [Datum Čas Sekunde],
   datepart(ms, dbo. Velika miza. [Datum in čas]) kot [Datum Čas Milisekunde]"
   Uporabite toliko stolpcev, kot jih potrebujete, da vsak del shranite v ločene stolpce.
4. Če potrebujete ure in minute in jih raje združite kot en časovni stolpec, lahko uporabite sintakso :
   Timefromparts(datepart(hh, dbo. Velika miza. [Datum in čas]), datepart(mm, dbo. Velika miza. [Datum in čas])) kot [Datum Čas UraMinuta]
5. Če imate dva stolpca z datumom in časom, na primer [Začetni čas] in [Končni čas], in resnično potrebujete časovno razliko med njima v sekundah kot stolpec z imenom [Trajanje], odstranite oba stolpca s seznama in dodajte:
   "datediff(ss;[Začetni datum];[Končni datum]) kot [Trajanje]"
   Če namesto ss uporabite ključno besedo ms, boste dobili trajanje v milisekundah

Uporaba izračunanih mer jezika DAX namesto stolpcev

Če ste že delali z izraznim jezikom DAX, morda že veste, da se izračunani stolpci uporabljajo za pridobivanje novih stolpcev na podlagi drugega stolpca v modelu, medtem ko so izračunane mere določene enkrat v modelu, vendar so ovrednotene le, če so uporabljene v vrtilni tabeli ali drugem poročilu.

Ena od tehnik varčevanja s pomnilnikom je zamenjava navadnih ali izračunanih stolpcev z izračunanimi merami. Klasičen primer je Cena enote, Količina in Skupaj. Če imate vse tri, lahko prihranite prostor tako, da ohranite le dva in tretjega izračunate z jezikom DAX.

Katera 2 stolpca morate obdržati?

V zgornjem primeru ohranite Količina in Cena na enoto. Ta dva imata manj vrednosti kot skupaj. Če želite izračunati skupno mero, dodajte izračunano mero, na primer:

"TotalSales:=sumx('Tabela prodaje','Tabela prodaje'[Cena na enoto]*'Tabela prodaje'[Količina])"

Izračunani stolpci so podobni običajnim stolpcem, saj oba zavzemata prostor v modelu. Nasprotno pa se izračunane mere izračunajo sproti in ne zavzamejo prostora.

Zaključek

V tem članku smo govorili o več pristopih, ki vam lahko pomagajo zgraditi pomnilniško učinkovitejši model. Način za zmanjšanje velikosti datoteke in pomnilniških zahtev podatkovnega modela je zmanjšanje skupnega števila stolpcev in vrstic ter števila enoličnih vrednosti, ki se prikažejo v vsakem stolpcu. Tukaj je nekaj tehnik, ki smo jih pokrili:

• Odstranjevanje stolpcev je seveda najboljši način za prihranek prostora. Odločite se, katere stolpce resnično potrebujete.
• Včasih lahko odstranite stolpec in ga zamenjate z izračunano mero v tabeli.
• Morda ne boste potrebovali vseh vrstic v tabeli. Vrstice lahko filtrirate v čarovniku za uvoz tabel.
• Na splošno je ločevanje posameznega stolpca na več ločenih delov dober način za zmanjšanje števila enoličnih vrednosti v stolpcu. Vsak od delov bo imel majhno število edinstvenih vrednosti, skupna vsota pa bo manjša od prvotnega poenotenega stolpca.
• V mnogih primerih potrebujete tudi ločene dele, ki jih boste uporabili kot razčlenjevalnike v poročilih. Po potrebi lahko ustvarite hierarhije iz delov, kot so ure, minute in sekunde.
• Stolpci velikokrat vsebujejo več informacij, kot jih potrebujete. Recimo, da stolpec shranjuje decimalke, vendar ste uporabili oblikovanje, da skrijete vse decimalke. Zaokroževanje je lahko zelo učinkovito pri zmanjševanju velikosti številskega stolpca.

Zdaj, ko ste naredili vse, kar je v vaši moči, da zmanjšate velikost delovnega zvezka, razmislite tudi o zagonu orodja za optimiziranje velikosti delovnega zvezka. Ta analizira Excelov delovni zvezek in, če je mogoče, ga še dodatno stisne. Prenesite optimizator velikosti delovnega zvezka.

Sorodne povezave

Specifikacije in omejitve podatkovnega modela

Optimizator velikosti delovnega zvezka

PowerPivot: zmogljive analize podatkov in podatkovni modeli v Excelu