Ustvarjanje podatkovnega modela, ki je učinkovit s pomnilnikom, s programom Excel in dodatkom Power pivot

V programu Excel 2013 ali novejši različici lahko ustvarite podatkovne modele, ki vsebujejo milijone vrstic, in nato izvedete učinkovito analizo podatkov s temi modeli. Podatkovne modele je mogoče ustvariti z dodatkom Power Pivot ali brez njega, če želite podpirati poljubno število vrtilnih tabel, grafikonov in ponazoritev funkcije Power View v istem delovnem zvezku.

Opomba: V tem članku so opisani podatkovni modeli v programu Excel 2013. Vendar pa so enake funkcije za modeliranje podatkov in funkcija Power pivot, ki so bile uvedene v programu Excel 2013, veljale tudi za Excel 2016. Med temi različicami Excela je dejansko majhna razlika.

Čeprav lahko preprosto ustvarite ogromne podatkovne modele v Excelu, obstaja več razlogov, ki jih ne želite. Prvič, veliki modeli, ki vsebujejo množice tabel in stolpcev, so za večino analiz nesmiselni in omogočajo okorni seznam polj. Drugi, veliki modeli uporabljajo dragoceni pomnilnik, negativno vplivajo na druge programe in poročila, ki imajo enake sistemske vire. Končno, v Office 365 tako SharePoint online in Excel Web App omejita velikost Excelove datoteke na 10 MB. Za podatkovne modele delovnega zvezka, ki vsebujejo na milijone vrstic, boste precej hitro zagnali omejitev 10 MB. Glejte specifikacije in omejitve podatkovnega modela.

V tem članku boste izvedeli, kako zgraditi tesno izdelan model, s katerim lahko lažje delate in uporabite manj pomnilnika. Če se želite naučiti najboljših praks v učinkovitem modelu modela, boste odplačali pot za vsak model, ki ga ustvarite in uporabite, ne glede na to, ali si ga ogledujete v programu Excel 2013, Office 365 SharePoint online, v Office Web Apps strežniku ali v SharePointu 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 optimiranje velikosti delovnega zvezka.

V tem članku

Razmerje stiskanja in mehanizem za analitiko v pomnilniku

Nič ne premaga neobstoječega stolpca za uporabo nizkega pomnilnika

Dva primera stolpcev, ki bi morali biti vedno izključeni

Kako izključiti nepotrebne stolpce

Kaj pa filtriranje le potrebnih vrstic?

Kaj, če potrebujemo stolpec; ali lahko še vedno zmanjšamo stroške vesolja?

Spreminjanje stolpcev» datetime «

Spreminjanje poizvedbe SQL

Uporaba izračunanih ukrepov DAX namesto stolpcev

Katere 2 stolpca morate obdržati?

Zaključek

Sorodne povezave

Razmerje stiskanja in mehanizem za analitiko v pomnilniku

Podatkovni modeli v Excelu uporabljajo mehanizem analitike v pomnilniku za shranjevanje podatkov v pomnilniku. Motor izvaja zmogljive tehnike stiskanja za zmanjšanje zahtev za shranjevanje, krčenje nabora rezultatov, dokler ni del prvotne velikosti.

V povprečju lahko pričakujete, da bo podatkovni model od 7 do 10 krat manjši od istih podatkov na izhodiščnem mestu. Če na primer uvažate 7 MB podatkov iz zbirke podatkov strežnika SQL Server, lahko podatkovni model v Excelu preprosto traja 1 MB ali manj. Stopnja stiskanja, ki je dejansko dosežena, je odvisna predvsem od števila enoličnih vrednosti v posameznem stolpcu. Več enoličnih vrednosti, je treba več pomnilnika shraniti.

Zakaj govorimo o stiskanju in enoličnih vrednostih? Ker je ustvarjanje učinkovitega modela, ki zmanjšuje porabo pomnilnika, vse o maksimiranju stiskanja in najpreprostejši način za to, da se znebite vseh stolpcev, ki jih ne potrebujete več, še posebej, če so v teh stolpcih številne enolične vrednosti.

Opomba:  Razlike v zahtevah shrambe za posamezne stolpce so lahko ogromne. V nekaterih primerih je bolje, če imate več stolpcev z nizkim številom enoličnih vrednosti in ne z enim stolpcem z velikim številom enoličnih vrednosti. Razdelek o optimizacijah za datetime zajema to tehniko podrobno.

Nič ne premaga neobstoječega stolpca za uporabo nizkega pomnilnika

Najbolj učinkovit stolpec v pomnilniku je tista, ki je niste nikoli uvozili na prvem mestu. Če želite ustvariti učinkovit model, si oglejte vsak stolpec in se vprašajte, ali prispeva k analizi, ki jo želite izvesti. Če to ne pomeni ali pa niste prepričani, jo izpustite. Če jih potrebujete, lahko dodate nove stolpce pozneje.

Dva primera stolpcev, ki bi morali biti vedno izključeni

Prvi primer se nanaša na podatke, ki izvirajo iz podatkovnega skladišča. V podatkovnem skladišču je običajno, da poiščete artefakte ETL procesov, ki nalagajo in osvežijo podatke v skladišču. Ko so podatki naloženi, se ustvarijo stolpci, kot je» ustvari datum «,» datum posodobitve «in» ETL Run «. Nobena od teh stolpcev ni potrebna v modelu in jo je treba preklicati, ko uvažate podatke.

Drugi primer vključuje opustitev primarnega stolpca, ko uvažate tabelo dejstev.

Številne tabele, vključno s tabelami dejstev, imajo primarne ključe. Za večino tabel, na primer tiste, ki vsebujejo podatke o kupcu, 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 identifikacijo posamezne vrstice. Medtem ko je to potrebno za namene normalizacije, je manj uporabno v podatkovnem modelu, kjer želite le stolpce, ki se uporabljajo za analizo, ali za ustvarjanje relacij tabele. Iz tega razloga, ko uvažate iz tabele dejstev, ne vključite njenega primarnega ključa. Primarni ključi v tabeli dejstev porabijo ogromne količine prostora v modelu, vendar ne zagotavljajo nobene koristi, ker jih ni mogoče uporabiti za ustvarjanje relacij.

Opomba:  V podatkovnih skladiščih in večdimenzionalnih zbirkah podatkov so velike tabele, sestavljene iz večinoma številskih podatkov, pogosto označene kot» tabele dejstev «. Tabele dejstev običajno vključujejo poslovne zmogljivosti ali podatke o transakcijah, kot so prodajne in stroškovne točke, ki so Združene in poravnane v organizacijske enote, izdelke, tržne segmente, geografske regije in tako dalje. Vsi stolpci v tabeli dejstev, ki vsebujejo poslovne podatke ali jih je mogoče uporabiti za navzkrižno sklicevanje na podatke, ki so shranjeni v drugih tabelah, morajo biti vključeni v model, če želite podpirati analizo podatkov. Stolpec, ki ga želite izključiti, je stolpec primarnega ključa tabele Fact, ki je sestavljen iz enoličnih vrednosti, ki obstajajo le v tabeli dejstev in nikjer drugje. Ker so tabele dejstev tako velike, so nekatere od največjih dobičkov v modelu učinkovitost izpeljane iz izključevanja vrstic ali stolpcev iz dejanskih tabel.

Kako izključiti nepotrebne stolpce

Učinkoviti modeli vsebujejo le tiste stolpce, ki jih boste dejansko potrebovali v delovnem zvezku. Če želite nadzorovati stolpce, ki so vključeni v model, boste morali uporabiti čarovnika za uvoz tabel v Dodatku Power pivot, da uvozite podatke in ne v pogovorno okno» Uvozi podatke «v Excelu.

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

Čarovnik za uvoz tabel v dodatku PowerPivot

Za vsako tabelo lahko kliknete gumb preview & filter in izberete dele tabele, ki jih res potrebujete. Priporočamo, da najprej počistite vse stolpce, nato pa nadaljujete s preverjanjem stolpcev, ki jih želite, in sicer po preučitvi, ali so zahtevane za analizo.

Podokno predogleda v čarovniku za uvoz tabel

Kaj pa filtriranje le potrebnih vrstic?

Številne tabele v zbirkah podatkov podjetij in podatkovnih skladiščih vsebujejo pretekle podatke, ki so bili nakopičeni v daljšem časovnem obdobju. Poleg tega boste morda opazili, da so tabele, ki vas zanimajo, informacije za območja podjetja, ki niso potrebna za določeno analizo.

S čarovnikom za uvoz tabele lahko filtrirate pretekle ali nepovezane podatke in tako shranite veliko prostora v modelu. Na tej sliki je datumski filter uporabljen za pridobivanje le vrstic, ki vsebujejo podatke za tekoče leto, razen zgodovinskih podatkov, ki jih ne potrebujete.

Podokno filtriranja v čarovniku za uvoz tabel

Kaj, če potrebujemo stolpec; ali lahko še vedno zmanjšamo stroške vesolja?

Obstaja nekaj dodatnih tehnik, ki jih lahko uporabite, če želite stolpec narediti kot boljšo kandidatko za stiskanje. Ne pozabite, da je edina značilnost stolpca, ki vpliva na stiskanje, število enoličnih vrednosti. V tem razdelku se boste naučili, kako lahko spremenite nekatere stolpce, da zmanjšate število enoličnih vrednosti.

Spreminjanje stolpcev» datetime «

V številnih primerih stolpci» datetime «trajajo 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 uporabite stolpec, in raven udobja v ustvarjanju SQL poizvedb.

Stolpci» datetime «vključujejo datumski del in uro. Ko se vprašate, ali potrebujete stolpec, lahko večkrat zastavite isto vprašanje 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 pa samo združiti podatke po letu, mesecu, četrtletju in tako dalje.

Odgovor na vsako od teh vprašanj določa 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 skrajšane oblike (SELECT *) v izjavo SELECT, ki vključuje popolnoma ustrezna 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 v urejevalnik poizvedbe in si ogledate trenutno poizvedbo SQL za vsako tabelo.

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

V razdelku lastnosti tabele izberite urejevalnik poizvedb.

Odprite urejevalnika poizvedb v pogovornem oknu »Lastnosti tabele«

V urejevalniku poizvedb je prikazana poizvedba SQL, ki se uporablja za zapolnitev tabele. Če ste filtrirali kateri koli stolpec med uvozom, vaša poizvedba vključuje popolnoma razvrščena imena stolpcev:

Poizvedba SQL, s katero so pridobljeni podatki

V nasprotnem primeru, če ste v celoti uvozili tabelo, ne da bi odstranili kateri koli stolpec ali uporabili poljuben filter, bo poizvedba prikazana kot» Izberi * od «, ki jo boste težje spreminjali:

Poizvedba SQL, ki uporablja privzeto, krajšo sintakso

Spreminjanje poizvedbe SQL

Zdaj, ko veste, kako poiščete poizvedbo, jo lahko spremenite, da dodatno zmanjšate velikost modela.

  1. Če za stolpce, ki vsebujejo valutne ali decimalne podatke, ne potrebujete decimalnih mest, uporabite to sintakso, da se znebite decimalnih mest:

    "Izberi KROG ([Decimal_column_name]; 0)... .”

    Če potrebujete centa, vendar ne ulomkov centov, zamenjajte vrednost 0 za 2. Če uporabljate negativne številke, lahko zaokrožite na enote, TENS, stotine itd.

  2. Če imate stolpec» datetime «z imenom» DBO «. Bigtable. [Datum časa] in ne potrebujete časa, uporabite sintakso, da se znebite časa:

    "Izberite CAST (DBO. Bigtable. [Datum] kot datum) kot [datumski čas]) «

  3. Če imate stolpec» datetime «z imenom» DBO «. Bigtable. [Datumski čas] in potrebujete datumske in časovne dele, uporabite več stolpcev v poizvedbi SQL namesto v enem stolpcu» datetime «:

    "Izberite CAST (DBO. Bigtable. [Datum ura] kot datum) kot [datum ura],

    DatePart (hh, DBO. Bigtable. [Datumski čas]) kot [datumska ura]

    DatePart (mi, DBO. Bigtable. [Datumski čas]) kot [datumske ure],

    DatePart (SS, DBO. Bigtable. [Datumski čas]) kot [čas za datum],

    DatePart (MS, DBO. Bigtable. [Datumski čas]) kot [datumski čas milisekund] "

    Uporabite toliko stolpcev, kot jih morate shraniti v ločenih stolpcih.

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

    Timefromparts (DatePart (hh; DBO. Bigtable. [Datumski čas]), DatePart (mm, DBO. Bigtable. [Datumski čas])) kot [datum HourMinute ure]

  5. Če imate dva stolpca» datetime «, na primer [začetni čas] in [končni čas] in kaj zares potrebujete, je razlika med njima v sekundah, ki se imenuje stolpec [trajanje], odstranite oba stolpca s seznama in dodajte:

    "DateDiff (SS; [začetni datum], [končni datum]) kot [trajanje]"

    Če uporabite ključno besedo MS namesto SS, boste dobili trajanje v milisekundah.

Uporaba izračunanih ukrepov DAX namesto stolpcev

Če ste že prej delali z jezikom za izražanje jezika DAX, morda že veste, da so izračunani stolpci uporabljeni za izpeljavo novih stolpcev, ki temeljijo na drugem stolpcu v modelu, medtem ko so izračunani ukrepi določeni enkrat v modelu, vendar so ovrednoteni le, če so uporabljeni v Vrtilne tabele ali drugo poročilo.

Ena tehnika varčevanja s pomnilnikom je zamenjava rednih ali izračunanih stolpcev z izračunanimi merili. 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 tretjega z uporabo storitve DAX.

Katere 2 stolpca morate obdržati?

V zgornjem primeru ohranite količino in ceno enote. Ta dva imata manj vrednosti kot seštevek. Če želite izračunati vsoto, dodajte izračunan ukrep, kot je:

"Supnaprodaja: = sumx (" tabela prodaje "," prodajna tabela "[cena enote] *" prodajna tabela "[količina])"

Izračunani stolpci so kot običajni stolpci, ki tako zavzamejo prostor v modelu. V nasprotju s tem so izračunani ukrepi izračunani na letenje in ne prenašajte prostora.

Zaključek

V tem članku smo se pogovarjali o več pristopih, ki vam bodo v pomoč pri ustvarjanju modela, ki je učinkovitejši od pomnilnika. Način, kako zmanjšati velikost datoteke in zahteve pomnilnika v podatkovnem modelu, je zmanjšati skupno število stolpcev in vrstic ter število enoličnih vrednosti, ki so prikazane v posameznem stolpcu. Tukaj je nekaj tehnik, ki smo jih zajeli:

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

  • Včasih lahko odstranite stolpec in ga zamenjate z izračunanim ukrepom v tabeli.

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

  • Na splošno je razstavljanje enega stolpca v več ločenih 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 prvotnega poenotenega stolpca.

  • V številnih primerih potrebujete tudi ločene dele, ki jih lahko uporabite kot razčlenjevalnike v poročilih. Po potrebi lahko ustvarite hierarhije iz delov, kot so ure, minute in sekunde.

  • Velikokrat stolpci vsebujejo več informacij, kot jih potrebujete tudi vi. 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 storili vse, kar lahko, da zmanjšate velikost delovnega zvezka, upoštevajte tudi, da zaženete orodje za optimiziranje velikosti delovnega zvezka. Ta analizira Excelov delovni zvezek in, če je mogoče, ga še dodatno stisne. Prenesite optimiranje velikosti delovnega zvezka.

Sorodne povezave

Specifikacije in omejitve podatkovnega modela

Prenos Optimizatorja velikosti delovnega zvezka

PowerPivot: zmogljive analize podatkov in podatkovni modeli v Excelu

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×