V Excel 2013 ali novejših različicah lahko ustvarite podatkovne modele, ki vsebujejo več milijonov vrstic in nato na podlagi teh modelov izvedete zmogljivo analizo podatkov. Podatkovne modele je mogoče ustvariti z dodatkom Power Pivot ali brez njega, da bi podpirali poljubno število vrtilnih tabel, grafikonov in ponazoritev funkcije Power View v istem delovnem zvezku.

Opomba: V tem članku so opisani podatkovni modeli Excel 2013. Vendar pa enake funkcije za modeliranje podatkov in Power Pivot, predstavljene v Excel 2013, veljajo tudi za Excel 2016. Med temi različicami zbirk podatkov ni nobene učinkovite Excel.

Čeprav lahko preprosto ustvarite ogromne podatkovne modele v Excel, obstaja več razlogov, zakaj tega ne želite narediti. Najprej so veliki modeli, ki vsebujejo več vrstic tabel in stolpcev, preobremenjujo večino analiz in so za otežen seznam polj. Drugi pa veliki modeli uporabljajo dragocen pomnilnik, kar negativno vpliva na druge aplikacije in poročila, ki uporabljajo enaka sistemska sredstva. V Microsoft 365 pa SharePoint Online Excel Spletni program omejujeta velikost datoteke Excel na 10 MB. Pri podatkovnih modelih delovnega zvezka, ki vsebujejo več milijonov vrstic, boste hitro omejitev 10 MB. Glejte Specifikacije in omejitve podatkovnega modela.

V tem članku boste izvedeli, kako ustvariti tesno zasnovan model, ki je preprostejši za delo in uporablja manj pomnilnika. Če si vzamete čas in se naučite najboljših praks pri načrtovanju modela, boste morali plačati vnaprej za vsak model, ki ga ustvarite in uporabljate, ne glede na to, ali si ga ogledujete v programu Excel 2013, Microsoft 365 SharePoint Online, v strežniku Office Web Apps Server ali v strežniku SharePoint 2013.

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 Excel mehanizem analitike v pomnilniku uporabljajo za shranjevanje podatkov v pomnilnik. Mehanizem uporablja zmogljive tehnike stiskanja za zmanjšanje zahtev shranjevanja in skrčenje nabora rezultatov, dokler ni le del izvirne velikosti.

V povprečju lahko pričakujete, da bo podatkovni model 7 do 10-krat manjši od istih podatkov na njenem mestu izvora. Če na primer uvažate 7 MB podatkov iz zbirke podatkov SQL Server, je podatkovni model v Excel lahko preprosto 1 MB ali manj. Stopnja doseženega stiskanja je odvisna predvsem od števila enoličnih vrednosti v vsakem stolpcu. Več kot je enoličnih vrednosti, več pomnilnika potrebujete za shranjevanje.

Zakaj govorimo o stiskanju in enoličnih vrednostih? Ker je pri gradnji učinkovitega modela, ki zmanjša porabo pomnilnika, vse to pomeni maksimiziranje stiskanja, pri tem pa se najlažje 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 imeti več stolpcev z nizko številom enoličnih vrednosti in ne enega stolpca z visokim številom enoličnih vrednosti. V razdelku Optimizacije datetime je ta tehnika podrobno opisana.

Nič ne premagati neobstoječ stolpec za nizko porabo pomnilnika

Najbolj učinkovit stolpec v pomnilniku je stolpec, ki ga sploh niste nikoli uvozili. Če želite ustvariti učinkovit model, si oglejte vsak stolpec in se vprašajte, ali prispeva k analizi, ki jo želite izvesti. Če ni ali niste prepričani, ga pustite. Nove stolpce lahko pozneje po potrebi dodate.

Dva primera stolpcev, ki bi morala biti vedno izključena

Prvi primer je povezan s podatki, ki izvirajo iz skladišča podatkov. V skladišču podatkov je pogosto treba poiskati artefakte procesov ETL, ki nalagajo in osvežujejo podatke v skladišču. Ko so naloženi podatki, so ustvarjeni stolpci, kot so »ustvari datum«, »datum posodobitve« in »ZAGON ETL«. V modelu ne potrebujete nobenega od teh stolpcev, zato pri uvozu podatkov ne bi smeli biti izbran noben od teh stolpcev.

V drugem primeru je pri uvozu tabele dejstev izpuščanje stolpca s primarnim ključem.

V številnih tabelah, vključno s tabelami dejstev, 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 relacij v modelu.

Tabele dejstev so drugačne. V tabeli dejstev je primarni ključ uporabljen za enolično prepoznavanje posamezne vrstice. Čeprav je ta funkcija potrebna za normaliziranje, je manj uporabna v podatkovnem modelu, v katerem želite uporabiti le tiste stolpce za analizo ali vzpostaviti relacije med tabelami. Zato pri uvozu iz tabele dejstev ne vključite primarnega ključa. Primarni ključi v tabeli dejstev porabijo ogromne količine prostora v modelu, a jim še ne nudijo 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 predvsem številske podatke, pogosto imenujemo tudi »tabele dejstev«. V tabele dejstev so po navadi podatki o poslovni učinkovitosti ali transakcijah, kot so podatkovne točke o prodaji in stroških, ki so združene in usklajene z enotami organizacije, izdelki, segmenti trga, geografskimi regijah itn. Vsi stolpci v tabeli dejstev, ki vsebujejo poslovne podatke ali ki jih je mogoče uporabiti za navzkrižno sklicevanje na podatke, shranjene v drugih tabelah, morate vključiti v model za podporo analizi podatkov. Stolpec, ki ga želite izključiti, je stolpec s primarnim ključem tabele dejstev, ki je sestavljen iz enoličnih vrednosti, ki obstajajo le v tabeli dejstev in nikjer drugje. Ker so tabele dejstva tako ogromne, so nekatere največje pridobitve v učinkovitosti modela izpeljane iz izključitev vrstic ali stolpcev iz tabel dejstev.

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 uporabiti čarovnika za uvoz tabel v dodatku Power Pivot, namesto pogovornega okna »Uvoz podatkov« v Excel.

Ko zaženete čarovnika za uvoz tabel, izberite tabele, ki jih želite uvoziti.

Čarovnik za uvoz tabel v dodatku PowerPivot

Za vsako tabelo lahko kliknete gumb Predogled & Filter in izberete dele tabele, ki jih zares potrebujete. Priporočamo, da najprej počistite vse stolpce in nato nadaljujete s preverjanjem, ali so stolpci, ki jih želite, ko se jih upošteva pri analizi.

Podokno predogleda v čarovniku za uvoz tabel

Kaj pa filtriranje le potrebnih vrstic?

Številne tabele v zbirkah podatkov podjetja in podatkovnih skladiščih vsebujejo zgodovinske podatke, ki so se zbrani v dolgih č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 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, ki vsebujejo podatke za trenutno leto, izključevanje zgodovinskih podatkov, ki jih ne potrebujete.

Podokno filtriranja v čarovniku za uvoz tabel

Kaj če potrebujemo stolpec; ali lahko še vedno zmanjšamo njegove stroške prostora?

Obstaja nekaj dodatnih tehnik, ki jih lahko uporabite, če želite, da je 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 stolpci »Datetime« vzeli 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 SQL poizvedbah.

Stolpci »Datetime« vključujejo del datuma in čas. Ko se vprašate, ali potrebujete stolpec, večkrat vprašaj 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 samo za združevanje podatkov po letu, mesecu, četrtletju itd.

Kako odgovorite na vsako od teh vprašanj določa vaše možnosti za delo s stolpcem Datetime.

Vse te rešitve morate SQL poizvedbo. Č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 kvalificirana imena stolpcev, ki jih je veliko lažje spreminjati.

Opišejo se poizvedbe, ki so ustvarjene za vas. V pogovornem oknu Lastnosti tabele lahko preklopite na urejevalnik poizvedb in si ogledate trenutno SQL poizvedbe za vsako tabelo.

Trak v oknu PowerPivot, ki prikazuje ukaz »Lastnosti tabele«

V oknu Lastnosti tabele izberite Urejevalnik poizvedb.

Odprite urejevalnika poizvedb v pogovornem oknu »Lastnosti tabele«

Urejevalnik poizvedb prikaže poizvedbo SQL, ki se uporablja za vnos tabele. Če ste med uvozom filtrirali kateri koli stolpec, poizvedba vključuje popolnoma kvalificirana imena stolpcev:

Poizvedba SQL, s katero so pridobljeni podatki

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

Poizvedba SQL, ki uporablja privzeto, krajšo sintakso

Modifying the SQL query

Zdaj, ko že znate poiskati poizvedbo, jo lahko spremenite in še bolj zmanjšate velikost modela.

  1. Če v stolpcih, ki vsebujejo podatke o valuti ali decimalnih mestih, 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, stotike itd.

  2. Če imate stolpec »Datetime« imenovan »dbo«. Bigtable. [Datumski čas] in dela Čas ne potrebujete, uporabite sintakso, da se znebite časa:

    "SELECT CAST (dbo. Bigtable. [Datumski čas] kot datum) AS [Datumski čas]) "

  3. Če imate stolpec »Datetime« imenovan »dbo«. Bigtable. [Datumski čas] in potrebujete oba dela za datum in čas, uporabite več stolpcev v poizvedbi za SQL in ne v enem stolpcu Datetime:

    "SELECT CAST (dbo. Bigtable. [Datumski čas] kot datum ) AS [Datumski čas],

    datepart(hh, dbo. Bigtable. [Datumski čas]) kot [ure datuma],

    datepart(mi, dbo. Bigtable. [Datumski čas]) kot [minute za čas datuma],

    datepart(ss, dbo). Bigtable. [Datumski čas]) kot [datumski čas sekund],

    datepart(ms, dbo. Bigtable. [Datumski čas]) as [Date Time Milliseconds]"

    Uporabite toliko stolpcev, kolikor želite shraniti vsak del v ločenih stolpcih.

  4. Če potrebujete ure in minute in jih želite imeti skupaj kot en časovni stolpec, lahko uporabite sintakso:

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

  5. Če imate dva stolpca datetime, na primer [Začetni čas] in [Končni čas], in v resnici potrebujete časovno razliko med njima v sekundah v stolpcu 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, boste trajanje dobili v milisekundah

Uporaba izračunanih ukrepov DAX namesto stolpcev

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

Eden od tehnik shranjevanja pomnilnika je zamenjava navadnih ali izračunanih stolpcev z izračunanimi meri. Klasični primer je Cena enote, Količina in Vsota. Če imate vse tri, lahko prihranite prostor tako, da ohranite le dve in izračunate tretjo s funkcijo DAX.

Kateri 2 stolpca bi morali obdržati?

V zgorajšem primeru ohranite količino in ceno enote. Ti dve vrednosti sta manjši od vrednosti v skupni vrednosti. Če želite izračunati skupno 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 zavlakajo v prostoru v modelu. Nasprotno pa se izračunane meje izračunajo s letenjem in ne zamihajo.

Zaključek

V tem članku smo govorili o več pristopih, ki vam lahko pomagajo ustvariti bolj pomnjeno učinkovit model. Zahteve za velikost datoteke in pomnilnik 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 postopkov, ki jih zajemamo:

  • Odstranjevanje stolpcev je seveda najboljši način za shranjevanje prostora. Odločite se, katere stolpce res potrebujete.

  • Včasih lahko stolpec odstranite in ga zamenjate z izračunano mero v tabeli.

  • Morda ne boste potrebovali vseh vrstic v tabeli. V čarovniku za uvoz tabel lahko filtrirate vrstice.

  • Na splošno je razdeljeva enega stolpca na več različnih delov dober način za zmanjšanje števila enoličnih vrednosti v stolpcu. Vsak od delov bo imel majhno število enoličnih vrednosti, skupna vsota pa bo manjša od izvirnega poenotenega stolpca.

  • V številnih primerih boste potrebovali tudi različne dele, ki jih boste v poročilih uporabili kot razčlenjevalnike. Po potrebi lahko ustvarite hierarhije iz delov, kot so Ure, Minute in Sekunde.

  • V stolpcih je veliko več informacij, kot jih potrebujete. Denimo, da so v stolpcu shranjene decimalne vejice, vendar ste za skrivanje vseh decimalnih mest uporabili oblikovanje. Zaokrožanje je lahko zelo učinkovito pri zmanjševanju velikosti številskega stolpca.

Ko ste naredili vse, kar ste lahko, da bi zmanjšali velikost delovnega zvezka, razmislite o tem, da bi zagnan 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

Ali potrebujete dodatno pomoč?

Razširite svoja znanja
Oglejte si izobraževanje

Vam je bila informacija v pomoč?

Kako ste zadovoljni s kakovostjo jezika?
Kaj je vplivalo na vašo izkušnjo?

Zahvaljujemo se vam za povratne informacije.

×