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 izpolnjevanje tabele. Če ste med uvozom filtrirali kateri koli stolpec, poizvedba vključuje popolnoma določena imena stolpcev:

Če pa ste uvozili celotno tabelo, ne da bi počistili noben stolpec ali uporabili filter, bo poizvedba prikazana kot »Izberi * iz «, ki jo bo težje spremeniti:

Spreminjanje poizvedbe SQL

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

1. Če za stolpce, v katerih so podatki o valuti ali decimalnih mestih, decimalk ne potrebujete, uporabite to sintakso, da odstranite decimalke:
   "SELECT ROUND([Decimal_column_name],0)... .”
   Če potrebujete cente, ne pa tudi ulomkov centov, zamenjajte vrednost 0 z 2. Če uporabljate negativna števila, lahko zaokrožite na enote, desetice, stotine itd.
2. Če imate stolpec »Datetime« z imenom dbo. Velika tabela. [Datum in čas] in ne potrebujete dela »Čas«, uporabite sintakso, da odstranite čas:
   »IZBERI CAST (dbo. Velika tabela. [Date time] as date) AS [Date time]) "
3. Če imate stolpec »Datetime« z imenom dbo. Velika tabela. [Datum in čas] ter potrebujete oba dela za datum in čas, v poizvedbi SQL uporabite več stolpcev namesto enega stolpca »Datum/čas«:
   »IZBERI CAST (dbo. Velika tabela. [Date Time] as date ) AS [Date Time],
   Datepart(HH, dbo. Velika tabela. [Datum in čas]) as [Date Time Hours],
   Datepart(MI, dbo. Velika tabela. [Datum in čas]) as [Date Time Minutes],
   Datepart(ss, dbo. Velika tabela. [Datum in čas]) as [Date Time Seconds],
   Datepart(ms, dbo. Velika tabela. [Datum in čas]) as [Date Time Milliseconds]«
   Uporabite toliko stolpcev, kolikor jih potrebujete, da shranite vsak del v ločene stolpce.
4. Če potrebujete ure in minute in želite, da so skupaj v enem časovnem stolpcu, lahko uporabite sintakso:
   Timefromparts(datepart(hh, dbo. Velika tabela. [Date Time]), datepart(mm, dbo. Velika tabela. [Date Time])) as [Date Time HourMinute]
5. Če imate dva stolpca z datumom in časom, na primer [Začetni čas] in [Končni čas], in 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 trajanje dobili v milisekundah

Uporaba izračunanih mer DAX namesto stolpcev

Če ste že delali z jezikom DAX, morda že veste, da so izračunani stolpci uporabljeni za pridobivanje novih stolpcev, ki temeljijo na katerem koli drugem stolpcu v modelu, medtem ko so izračunane mere določene enkrat v modelu, ovrednotene pa so le, ko so uporabljene v vrtilni tabeli ali drugem poročilu.

Z varčevanjem s pomnilnikom lahko prihranite tudi zamenjavo navadnih ali izračunanih stolpcev z izračunanimi merami. Klasični primer so cena enote, količina in vsota. Če imate vse tri elemente, lahko prihranite prostor tako, da ohranite le dve, tretjega pa izračunate z jezikom DAX.

Katera 2 stolpca bi morali obdržati?

V zgornjem primeru ohranite polja »Količina« in »Cena enote«. Ti dve imata manj vrednosti kot skupaj. Če želite izračunati skupno vsoto, dodajte izračunano mero, podobno:

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

Izračunani stolpci so kot navadni stolpci, saj oba zavzame prostor v modelu. Nasprotno pa so izračunane mere izračunane takoj in ne zavzamejo prostora.

Zaključek

V tem članku smo govorili o več pristopih, s katerimi lahko ustvarite model, ki učinkovito izkoristi prostor pomnilnika. Velikost datoteke in zahteve pomnilnika podatkovnega modela lahko zmanjšate tako, da zmanjšate skupno število stolpcev in vrstic ter število enoličnih vrednosti, ki se pojavijo v vsakem stolpcu. Tukaj je nekaj tehnik, ki smo jih obravnavali:

• Odstranjevanje stolpcev je seveda najboljši način za varčevanje s prostorom. 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 uvažanje tabel.
• Na splošno je razdelitev enega stolpca na več različnih delov dober način za zmanjšanje števila enoličnih vrednosti v stolpcu. Vsak posamezen del bo imel manjše število enoličnih vrednosti, skupna vsota pa bo manjša od izvirnega poenotenega stolpca.
• V številnih primerih potrebujete tudi različne dele, ki jih lahko uporabite kot razčlenjevalnike v poročilih. Ko je ustrezno, lahko ustvarite hierarhije iz delov, kot so ure, minute in sekunde.
• Pogosto vsebuje stolpci več informacij, kot jih potrebujete. Denimo, da stolpec shranjuje decimalke, vi pa ste uporabili oblikovanje, s katerim ste skrili vsa decimalna mesta. Zaokroževanje je lahko zelo učinkovito pri zmanjševanju velikosti številskega stolpca.

Ko ste naredili vse, kar je v vaši moči, da bi zmanjšali velikost delovnega zvezka, 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.

Sorodne povezave

Specifikacije in omejitve podatkovnega modela

Optimizator velikosti delovnega zvezka

PowerPivot: zmogljive analize podatkov in podatkovni modeli v Excelu