V Excelu lahko ustvarite podatkovne modele z milijoni vrstic in nato izvedete zmogljivo analizo podatkov s temi modeli. Podatkovne modele lahko ustvarite z dodatkom Power Pivot ali brez, da podpirajo poljubno število vrtilnih tabel, grafikonov in ponazoritev funkcije Power View v istem delovnem zvezku.
Čeprav lahko v Excelu preprosto ustvarite ogromne podatkovne modele, obstaja več razlogov, da tega ne naredite. Najprej so veliki modeli, ki vsebujejo več vrstic tabel in stolpcev, preoberni za večino analiz in se uporabljajo za zapleten seznam polj. Drugič, veliki modeli uporabljajo dragocen pomnilnik, kar negativno vpliva na druge aplikacije in poročila, ki si delijo ista sistemska sredstva. Na koncu Microsoft 365 SharePoint Online in Excel Web App omejita velikost Excelove datoteke na 10 MB. Pri podatkovnih modelih delovnega zvezka, ki vsebujejo več milijonov vrstic, boste zelo hitro naletili na omejitev 10 MB. Glejte Specifikacije in omejitve podatkovnega modela.
V tem članku se boste naučili ustvariti tesno zgrajen model, s katerega boste lažje delovali in po uporabi manj pomnilnika. Če si boste vzame čas za informacije o najboljših praksah pri učinkovitem oblikovanju modela, boste morali plačati za vsak model, ki ga ustvarite in uporabite, ne glede na to, ali si ga ogledujete v Excelu, storitvi Microsoft 365 SharePoint Online, strežniku Office Web Apps Server ali v 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
Razmerja stiskanja in mehanizem analitike v pomnilniku
Podatkovni modeli v Excelu uporabljajo mehanizem analitike v pomnilniku za shranjevanje podatkov v pomnilnik. Mehanizem izvaja zmogljive tehnike stiskanja za zmanjšanje zahtev za shranjevanje, krčenje rezultata, dokler ni del svoje prvotne velikosti.
V povprečju lahko pričakujete, da bo podatkovni model od 7 do 10-krat manjši od istih podatkov na točki izvora. Če na primer uvažate 7 MB podatkov iz zbirke podatkov strežnika SQL Server, je podatkovni model v Excelu lahko na preprost način 1 MB ali manj. Dejansko dosežena stopnja stiskanja je odvisna predvsem od števila enoličnih vrednosti v vsakem stolpcu. Več enoličnih vrednosti je potrebnih za shranjevanje več pomnilnika.
Zakaj govorimo o stiskanju in enoličnih vrednostih? Ker se pri ustvarjanju učinkovitega modela, ki minimizira uporabo pomnilnika, vse gre za maksimizacijo stiskanja, to pa najlažje naredite tako, da se znebite stolpcev, ki jih ne potrebujete, še posebej, če ti stolpci vključujejo veliko število enoličnih vrednosti.
Opomba: Razlike v zahtevah za shranjevanje za posamezne stolpce so lahko ogromne. V nekaterih primerih je bolje, da imate več stolpcev z majhnim številom enoličnih vrednosti in ne enega stolpca z visokim številom enoličnih vrednosti. Razdelek o optimizacijah Datetime podrobno zajema to tehniko.
Nič ne izboljša neobstojenega stolpca za uporabo nizkega pomnilnika
Stolpec, ki najbolj učinkovito deluje s pomnilnikom, je stolpec, ki ga nikoli niste uvozili. Če želite ustvariti učinkovit model, si oglejte posamezne stolpce in se vprašajte, ali ta prispeva k analizi, ki jo želite izvesti. Če ni ali niste prepričani, pustite to možnost. Nove stolpce lahko kadar koli dodate pozneje, če jih potrebujete.
Dva primera stolpcev, ki bi morala biti vedno izključena
Prvi primer se nanaša na podatke, ki izvirajo iz skladišča podatkov. V podatkovnem skladišču je pogosto treba najti artefakte procesov ETL, ki podatke nalagajo in osvežujejo v skladišču. Stolpci, kot so »ustvari datum«, »datum posodobitve« in »ZAGON ETL«, so ustvarjeni, ko so naloženi podatki. V modelu ne potrebujete nobenega od teh stolpcev, zato pri uvozu podatkov ne bi morali biti izbrani.
Drugi primer vključuje izpustitev stolpca s primarnim ključem pri uvozu tabele de fact.
V številnih tabelah, vključno s tabelami de fact, so primarni ključi. V večini tabel, na primer tistih, ki vsebujejo podatke o stranki, zaposlenem ali prodaji, boste želeli primarni ključ tabele, tako da ga lahko uporabite za ustvarjanje odnosov v modelu.
Tabele de factov so drugačne. V tabeli de factov se primarni ključ uporablja za enolično prepoznavanje vsake vrstice. Čeprav je to potrebno za normalizacijo, je manj uporabno v podatkovnem modelu, v katerem želite uporabiti le tiste stolpce za analizo ali vzpostaviti relacije med tabelami. Zato pri uvozu iz tabele de factov ne vključite primarnega ključa. Primarni ključi v de fact tabeli porabijo ogromne količine prostora v modelu, vendar ne zagotavljajo nobene koristi, saj jih ni mogoče uporabiti za ustvarjanje odnosov.
Opomba: V podatkovnih skladiščih in večdimenzijskih zbirkah podatkov se velike tabele, ki vsebujejo večinoma številske podatke, pogosto imenujejo »podatkovne tabele«. V tabele de factov so po navadi podatki o poslovni uspešnosti ali transakcijah, kot so podatkovne točke o prodaji in stroških, ki so združene in poravnane z enotami organizacije, izdelki, segmenti trga, geografskimi območji itd. Vsi stolpci v tabeli de fact, ki vsebujejo poslovne podatke ali ki jih je mogoče uporabiti za navzkrižno sklicevanje na podatke, shranjene v drugih tabelah, morajo biti vključeni v model za podporo analize podatkov. Stolpec, ki ga želite izključiti, je stolpec s primarnim ključem v tabeli de fact, ki je sestavljen iz enoličnih vrednosti, ki obstajajo le v tabeli de fact in nikjer drugje. Ker so tabele de factov tako ogromne, so nekatere največje pridobitve v učinkovitosti modela izpeljane iz izključevanje vrstic ali stolpcev iz tabel de fact.
Kako izključiti nepotrebne stolpce
Učinkoviti modeli vsebujejo le tiste stolpce, ki jih dejansko potrebujete v delovnem zvezku. Če želite nadzorovati, kateri stolpci so vključeni v model, boste morali za uvoz podatkov namesto pogovornega okna »Uvoz podatkov« v Excelu uporabiti čarovnika za uvoz tabel v dodatku Power Pivot .
Ko zaženete čarovnika za uvoz tabel, izberite 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, potem ko ste preverili, 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šem časovnem obdobju. Poleg tega boste morda našli, da tabele, ki vas zanimajo, vsebujejo informacije za področja poslovanja, ki jih ne potrebujete za vašo posebno analizo.
S čarovnikom za uvoz tabel lahko filtrirate zgodovinske ali nepovezane podatke in tako prihranite veliko prostora v modelu. Na spodnji sliki je datumski filter uporabljen za pridobivanje le vrstic s podatki za trenutno leto, razen zgodovinskih podatkov, ki jih ne potrebujete.
Kaj če potrebujemo stolpec; ali lahko še vedno zmanjšamo njegove stroške prostora?
Obstaja nekaj dodatnih tehnik, ki jih lahko uporabite, da stolpec 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 nekatere stolpce spremenite, da zmanjšate število enoličnih vrednosti.
Spreminjanje stolpcev »Datetime«
V številnih primerih so za stolpce »Datetime« potrebnega veliko prostora. Na srečo obstaja več načinov za zmanjšanje zahtev za shranjevanje za ta podatkovni tip. Tehnike se razlikujejo glede na to, kako uporabljate stolpec, in od ravni udobja pri ustvarjanju poizvedb SQL.
Stolpci »Datetime« vključujejo del datuma in čas. Ko se sprašujete, ali potrebujete stolpec, zastavite isto vprašanje večkrat za stolpec »Datetime«:
-
Ali potrebujem časovni del?
-
Ali potrebujem časovni del na ravni ur? Minut? Sekund? Milisekundah?
-
Ali imam več stolpcev Datetime, ker želim izračunati razliko med njimi ali le združiti podatke po letu, mesecu, četrtletju itn.
Način, kako odgovorite na vsako od teh vprašanj, določa vaše možnosti za obravnavanje stolpca »Datetime«.
Vse te rešitve zahtevajo spremembo poizvedbe SQL. Če želite poenostaviti spreminjanje poizvedbe, filtrirajte vsaj en stolpec v vsaki tabeli. S filtriranjem stolpca spremenite zgradbo poizvedbe iz okrajšane oblike (SELECT *) v izjavo SELECT, ki vključuje popolnoma določena imena stolpcev, ki jih je veliko lažje spreminjati.
O poglejmo poizvedbe, ki so ustvarjene za vas. V pogovornem oknu Lastnosti tabele lahko preklopite v urejevalnik poizvedb in si ogledate trenutno poizvedbo SQL za vsako tabelo.
V razdelku Lastnosti tabele izberite Urejevalnik poizvedb.
V urejevalniku poizvedbe je prikazana poizvedba SQL, ki se uporablja za izpolnjevanje tabele. Če ste med uvozom filtrirali kateri koli stolpec, poizvedba vključuje popolnoma določena imena stolpcev:
|
Če pa ste tabelo uvozili v celoti, ne da bi počistili kateri koli stolpec ali dodali 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, da še bolj zmanjšate velikost svojega modela.
-
Če v stolpcih, ki vsebujejo valute ali decimalne podatke, ne potrebujete decimalnih mest, uporabite to sintakso, da se znebite decimalnih mest:
"SELECT ROUND([Decimal_column_name], 0)... .”
Če potrebujete cente, vendar ne ulomkov centov, zamenjajte 0 z 2. Če uporabite negativna števila, lahko zaokrožite na enote, desetke, stotine itd.
-
Če imate stolpec »Datetime« z imenom »dbo«. Velik zasuh. [Date Time] and you do not need the Time part, use the syntax to get rid of the time:
"SELECT CAST (dbo. Velik zasuh. [Date time] as date) AS [Date time]) "
-
Če imate stolpec »Datetime« z imenom »dbo«. Velik zasuh. [Date Time] in potrebujete oba dela datuma in časa, zato v poizvedbi SQL namesto enega stolpca Datetime uporabite več stolpcev:
"SELECT CAST (dbo. Velik zasuh. [Datum ura] kot datum ) AS [Datumski čas],
datepart(hh, dbo. Velik zasuh. [Datumski čas]) kot [datumski čas ur],
datepart(mi, dbo. Velik zasuh. [Datumski čas]) as [Date Time Minutes],
datepart(ss, dbo. Velik zasuh. [Datumski čas]) kot [datumski čas sekund],
datepart(ms, dbo. Velik zasuh. [Datumski čas]) as [Date Time Milliseconds]"
Uporabite toliko stolpcev, kolikor želite shraniti vsak del v ločenih stolpcih.
-
Če potrebujete ure in minute in jih želite imeti skupaj kot en časovni stolpec, lahko uporabite sintakso:
Timefromparts(datepart(hh, dbo. Velik zasuh. [Date Time]), datepart(mm, dbo. Velik zasuh. [Datumski čas])) as [Date Time HourMinute]
-
Če imate dva stolpca datetime, na primer [Začetni čas] in [Končni čas], in v resnici potrebujete časovno razliko med njima v sekundah kot stolpec z imenom [Trajanje], odstranite oba stolpca s seznama in dodajte:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
Če namesto ss uporabite ključno besedo ms, bo trajanje prikazano v milisekundah.
Uporaba izračunanih mer DAX namesto stolpcev
Če ste že delali z jezikom izraza DAX, morda že veste, da so izračunani stolpci uporabljeni za izpeljavo novih stolpcev na podlagi drugega stolpca v modelu, medtem ko so izračunane mere določene enkrat v modelu, vendar ocenjene le, če so uporabljene v vrtilni tabeli ali drugem poročilu.
Ena tehnika varčevanja s pomnilnikom je zamenjava običajnih ali izračunanih stolpcev z izračunanimi merami. Klasični primer so Cena enote, Količina in Vsota. Če imate vse tri, lahko prihranite prostor tako, da ohranite le dva dela in izračunate tretjo s tehnologijo DAX.
Katera 2 stolpca bi morali obdržati?
V zgornjem primeru ohranite »Količina« in »Cena enote«. Ti dve vrednosti sta manj vrednosti kot skupna vrednost. Če želite izračunati vsoto, dodajte izračunano mero, kot je:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Izračunani stolpci so kot navadni stolpci, ki zavlacajo prostor v modelu. Nasprotno pa se izračunane mere izračunajo s piko navznoti in ne količino prostora.
Zaključek
V tem članku smo govorili o več pristopih, s katerimi lahko ustvarite model, ki bo bolj učinkovito za pomnilnik. 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 prikažejo v vsakem stolpcu. Tukaj je nekaj tehnik, ki smo zajeti:
-
Odstranjevanje stolpcev je seveda najboljši način za shranjevanje prostora. Odločite se, katere stolpce resnično potrebujete.
-
Včasih lahko stolpec odstranite in ga zamenjate z izračunano mero v tabeli.
-
Morda ne boste potrebovali vseh vrstic v tabeli. Vrstice lahko filtrirate v čarovniku za uvoz tabele.
-
Na splošno je razdvajanje enega stolpca na več različnih delov dober način za zmanjšanje števila enoličnih vrednosti v stolpcu. Vsak od delov bo imel manjše število enoličnih vrednosti, skupna vsota pa bo manjša od izvirnega enotnega stolpca.
-
V mnogih primerih potrebujete tudi različne dele, ki jih želite uporabiti kot razčlenjevalnike v poročilih. Po potrebi lahko ustvarite hierarhije iz delov, kot so Ure, Minute in Sekunde.
-
Večkrat so v stolpcih več informacij, kot jih potrebujete. Denimo, da so v stolpcu shranjene decimalke, vendar ste uporabili oblikovanje za skrivanje vseh decimalnih mest. Zaokroževanje je lahko zelo učinkovito pri zmanjševanju velikosti številskih stolpcev.
Zdaj, ko ste naredili vse, kar lahko, da bi zmanjšali velikost delovnega zvezka, priporočamo, da zaženete 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
