Ustvarite podatkovni Model z zmogljivostjo pomnjenja, s programom Excel in Power Pivot dodatek

Pomembno : Besedilo članka je prevedeno strojno. Glejte zavrnitev odgovornosti. Angleško različico tega članka najdete tukaj .

V programu Excel 2013 ali novejšo različico, lahko ustvarite podatkovne modele, ki vsebujejo več milijonov vrstic in nato izvedite zmogljive analize podatkov glede na teh modelov. Podatkovni modeli lahko ustvarite z ali brez Power Pivot dodatek za podporo poljubno število vrtilne tabele, grafikoni in ponazoritvami, Power View v istem delovnem zvezku.

Opomba : V tem članku je opisana podatkovnih modelov v programu Excel 2013. Vendar pa isti modeliranje podatkov in Power Pivot značilnosti uveden v programu Excel 2013 veljajo tudi za Excel 2016. Je učinkovito majhno razliko med te različice Excela.

Čeprav lahko zgradi ogromno podatkovni modeli v Excelu, obstaja več razlogov ne do. Prvi, velike modeli, ki vsebuje množice tabel in stolpcev nadkolenski za večino analiz in da za zapleten seznam polj. Drugi, velike modeli porabiti pomembno pomnilnika, negativno vpliva na druge programe in poročila, ki imajo isto sistemska sredstva. Na koncu v storitvi Office 365 SharePoint Online in Excel Web App omejitev velikosti Excelove datoteke 10 MB. Za podatkovni modeli delovnega zvezka, ki vsebujejo več milijonov vrstic, boste naletite na omejitev 10 MB precej hitro. Glejte podatkovni Model specifikacije in omejitve.

V tem članku boste lahko prebrali, kako učinkovito ustvariti model, s katerim je lažje delati, hkrati pa ta zasede manj pomnilnika. Čas, ki si ga boste vzeli, da se boste seznanili z najboljšimi praksami učinkovitega načrtovanja modelov, se vam bo povrnil v prihodnosti, ko boste ustvarjali in uporabljali katere koli modele, ne glede na to, ali si jih boste ogledovali v programu Excel 2013, storitvi Office 365 SharePoint Online, v strežniku s programi Office Web Apps ali strežniku SharePoint 2013.

Lahko tudi zaženete Optimizator velikosti delovnega zvezka. Analizira Excelov delovni zvezek in po možnosti stisne še dodatno. Prenos Optimizatorja velikosti delovnega zvezka.

V tem članku

Razmerja stiskanja in mehanizem za analitiko v pomnilniku

Pomnilnik najmanj stolpci, ki ne obstajajo zasedejo

Dva primera stolpcev, ki se vedno izključiti

Kako izključiti odvečne stolpce

Kaj bi filtrirali le vrstice, ki potrebne?

Kaj, če potrebujete stolpca; lahko vseeno zmanjšam njegovo porabo prostora?

«Spreminjanje stolpcev» Datetime« »

Spreminjanje poizvedbe SQL

Uporaba DAX izračunane mere, namesto stolpcev

Katera 2 stolpca obdržati?

Zaključek

Sorodne povezave

Razmerja stiskanja in mehanizem za analitiko v pomnilniku

Podatkovni modeli za shranjevanje podatkov v pomnilnik v Excelu uporabljajo mehanizem za analitiko v pomnilniku. Mehanizem uporabi zmogljive tehnike stiskanja, s katerimi zmanjša zasedenost pomnilnika, saj nabor rezultatov stisne tako, da je njegova velikost znatno manjša od izvirne.

Podatkovni model je lahko v povprečju od 7–10-krat manjši od istih podatkov, ko so ti v izvirni obliki. Če na primer iz zbirke podatkov SQL Server uvažate 7 MB podatkov, bo podatkovni model v Excelu 1 MB ali celo manj. Stopnja stisnjenosti, ki je na koncu dosežena, je odvisna od števila enoličnih vrednosti v posameznem stolpcu. Več enoličnih vrednosti pomeni, da je za shranjevanje potrebnega več pomnilnika.

Zakaj govorimo o stiskanju in enoličnih vrednostih? Pri ustvarjanju učinkovitega modela, ki ne obremeni prekomerno pomnilnika, je pomembna maksimizacija stiskanja. To najlažje dosežete tako, da se znebite vseh stolpcev, ki jih ne potrebujete nujno, zlasti stolpcev, v katerih je veliko enoličnih vrednosti.

Opomba : Razlike, koliko zasedejo pomnilnik posamezni stolpci, so lahko lahko ogromne. V nekaterih primerih je bolje imeti več stolpcev z nižjim številom enoličnih vrednosti kot en stolpec z velikim številom enoličnih vrednosti. Ta tehnika je podrobno opisana v razdelku »Optimizacija podatkov »Datetime««.

Pomnilnik najmanj zasedejo stolpci, ki ne obstajajo

Stolpci, ki jih sploh ne uvozite, ne morejo zasesti pomnilnika. Če želite ustvariti učinkovit model, si oglejte vsak stolpec in se vprašajte, ali je nujno potreben za analizo, ki jo želite izvesti. Če ni ali pa če niste prepričani, ga ne vključite. Če ga boste pozneje potrebovali, ga lahko vedno dodate naknadno.

Dva primera stolpcev, ki vam jih ni treba nikoli vključiti

Prvi primer se nanaša na podatke, ki izvirajo iz skladišča podatkov. V skladišču podatkov so pogosto elementi postopkov ETL, ki nalagajo in osvežujejo podatke v skladišču. Ko so podatki naloženi, so ustvarjeni elementi, kot so na primer »datum nastanka«, »datum posodobitve« in »ETL run«. Nobeden od teh stolpcev ni potreben v modelu, zato pri uvažanju podatkov prekličite izbor teh stolpcev.

Drugi primer pa se nanaša na stolpec s primarnim ključem. Ko uvažate tabelo dejstev, ga lahko izpustite.

Številne tabele, med drugim tabele dejstev, imajo primarne ključe. Za večino tabel, kot so na primer tabele, ki vsebujejo podatke o stranki, zaposlenem ali o prodaji, boste primarni ključ tabele potrebovali zato, da lahko v modelu ustvarite relacije.

Tabele z dejstvi so drugačne. Primarni ključ v tabeli z dejstvi enolično identificira posamezno vrstico. Nujen je pri postopku normalizacije, manj potreben pa v podatkovnem modelu, pri katerem želite obdržati le tiste stolpce, ki jih potrebujete za analizo, ali če želite le ustvariti relacije tabele. Zato pri uvažanju iz tabele dejstev ne vključite primarnega ključa. Primarni ključi v tabeli dejstev porabijo veliko prostora v modelu, ne zagotavljajo pa nobene koristi, saj z njimi ni mogoče ustvariti relacij.

Opomba : V skladiščih podatkov in večdimenzionalnih zbirkah podatkov se velike tabele, sestavljene iz večinoma številčnih podatkov, pogosto imenujejo »tabele dejstev«. Tabele dejstev po navadi vsebujejo poslovno učinkovitost ali podatke o transakcijah, kot so podatkovne točke o prodaji in stroških, ki so zbrane in poravnane z organizacijskimi enotami, izdelki, tržnimi segmenti, geografskimi območji in tako naprej. Vsi stolpci v tabeli dejstev, v katerih so poslovni podatki, ali pa ki jih je mogoče uporabiti za navzkrižno sklicevanje na podatke, shranjene v drugih tabelah, je smiselno vključiti v model, saj ti podpirajo analizo podatkov. Stolpec, ki ga je smiselno izpustiti, je stolpec s primarnim ključem tabele v dejstev, ki je sestavljena iz enoličnih vrednosti, ki obstajajo le v tabeli dejstev in nikjer drugje. Ker so tabele dejstev tako velike, lahko pri učinkovitosti modela največ pridobite tako, da iz tabel dejstev izključite vrstice ali stolpce.

Kako izključiti odvečne stolpce

Učinkovit modeli imajo le stolpce, ki jih boste dejansko potrebujete v delovnem zvezku. Če želite nadzirati stolpce, ki so vključeni v modelu, boste morali uporabiti za uvažanje tabel v Dodatku Power Pivot dodatek za uvoz podatkov namesto pogovornem oknu »Uvoz podatkov« v Excelu.

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

Čarovnik za uvoz tabel v dodatku PowerPivot

Za vsako tabelo kliknite gumba za predogled in filtriranje ter izberite dele tabele, ki jih res potrebujete. Priporočamo, da najprej odstranite potrditvene oznake za vse stolpce. Nato pa potrjujte stolpce, ki ji želite in ki so pomembni za analizo.

Podokno predogleda v čarovniku za uvoz tabel

Zakaj ne bi filtrirali le vrstice, ki jih potrebujete?

Številne tabele v zbirkah podatkov podjetij in skladiščih podatkov vsebujejo zgodovinske podatke, ki so zbrani prek daljšega obdobja. Poleg tega boste morda ugotovili, da so v tabelah, ki vas zanimajo, informacije o poslovnih področjih, ki niso nujna za določeno analizo.

S čarovnikom za uvoz tabel lahko filtrirate zgodovinske in nepomembne podatke ter tako v modelu prihranite veliko prostora. V naslednji sliki s filtrom datuma pridobite le vrstice, v katerih so podatki za trenutno leto, brez zgodovinskih podatkov, ki ne bodo potrebni.

Podokno filtriranja v čarovniku za uvoz tabel

Kaj pa, če stolpec potrebujem? Ali lahko vseeno zmanjšam njegovo porabo prostora?

Na voljo je še nekaj dodatnih postopkov, s katerimi lahko stolpec bolje pripravite na stiskanje. Ne pozabite, da na učinkovitost stiskanja vpliva število enoličnih vrednosti v stolpcu. V tem razdelku boste prebrali, kako je mogoče nekatere stolpce spremeniti tako, da se zmanjša število enoličnih vrednosti.

Spreminjanje stolpcev »Datetime«

Stolpci »Datetime« v številnih primerih porabijo veliko prostora. Na srečo lahko za to vrsto podatkov uporabite različne načine, s katerimi zmanjšate porabo pomnilnika. Tehnike so različne in so odvisne od tega, kako uporabite stolpec in kako spretni ste pri ustvarjanju poizvedb SQL.

Stolpci »Datetime« vključujejo datumski in časovni del. Ko se vprašate, ali potrebujete nek stolpec, se za stolpec »Datetime« isto vprašanje vprašajte večkrat:

  • Ali potrebujem datumski del?

  • Ali potrebujem časovni del na ravni ur, minut, sekund, milisekund?

  • Ali imam več stolpcev »Datetime«, ker želim izračunati razliko med njimi, ali pa želim zgolj zbrati podatke po letu, mesecu, četrtletju in tako dalje?

Možnosti, ki jih imate za delo s stolpcem »Datetime«, so odvisne od tega, kako odgovorite na vsako od teh vprašanj.

Pri vsaki od teh rešitev je treba spremeniti poizvedbo SQL. Če si želite spreminjanje poizvedb olajšati, filtrirajte vsaj en stolpec v posamezni tabeli. S filtriranjem stolpca lahko spremenite sestavo poizvedbe iz okrajšane oblike (SELECT *) v izjavo SELECT, ki vključuje popolnoma določena imena stolpcev, ki jih je precej lažje spremeniti.

Oglejmo si poizvedbe, ki so ustvarjene za vas. V pogovornem oknu »Lastnosti tabele« lahko preklopite v urejevalnika poizvedb in si ogledate trenutno poizvedbo SQL za posamezno tabelo.

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

V pogovornem oknu »Lastnosti tabele« izberite možnost Urejevalnik poizvedb.

Odprite urejevalnika poizvedb v pogovornem oknu »Lastnosti tabele«

Urejevalnik poizvedb prikazuje poizvedbo SQL, s katero je izpolnjena tabela. Če ste med uvozom filtrirali katerega od stolpcev, poizvedba vključuje popolnoma določena imena stolpcev:

Poizvedba SQL, s katero so pridobljeni podatki

Če pa ste uvozili celotno tabelo, pri tem pa niste odstranili nobenega od stolpcev in niste uporabili nobenega filtra, bo poizvedba prikazana kot »Izberi * iz«, kar bo veliko težje spremeniti:

Poizvedba SQL, ki uporablja privzeto, krajšo sintakso

Spreminjanje poizvedbe SQL

Ker zdaj veste, kako poizvedbo poiščete, jo lahko še dodatno spremenite in tako zmanjšate velikost modela.

  1. Če pri stolpcih, v katerih so valuta ali decimalni podatki, decimalk ne potrebujete, se jih znebite s sintakso:

    »SELECT ROUND([Decimal_column_name],0)… .«

    Če potrebujete cente, ne potrebujete pa manjših enot, zamenjajte 0 z 2. Če uporabljate negativna števila, jih lahko zaokrožite na enote, desetice, stotice itd.

  2. Če imate stolpec »Datetime«, ki se imenuje »dbo.Bigtable.[Date Time]« in ne potrebujete časovnega dela, se časa znebite s sintakso:

    “SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “

  3. Če imate stolpec »Datetime«, ki se imenuje »dbo.Bigtable.[Date Time]«, in ne potrebujete datumskega in časovnega dela, v poizvedbi namesto enega stolpca »Datetime« uporabite v poizvedbi SQL več stolpcev :

    “SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    Če želite shraniti vsak del v ločenem stolpcu, uporabite toliko stolpcev, kolikor jih potrebujete.

  4. Če potrebujete ure in minute in želite, da so ti podatki skupaj v enem stolpcu s časom, lahko uporabite to sintakso:

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

  5. Če imate dva stolpca »datetime«, na primer [Start Time] in [End Time], potrebujete pa časovno razliko med njima, podano v sekundah in navedeno v stolpcu imenovanem [Duration], odstranite oba stolpca s seznama in dodajte:

    “datediff(ss,[Start Date],[End Date]) as [Duration]”

    Če namesto ključne besede »ss« uporabite »ms«, boste dobili trajanje podano v milisekundah

Uporaba mer, izračunanih jezikom izrazov DAX, namesto stolpcev

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

Prostor v pomnilniku lahko med drugim prihranite tako, navadne ali izračunane stolpce zamenjate z izračunanimi merami. Klasičen primer so »Cena enote«, »Količina« in »Skupno«. Če iamte vse tri, lahko prostor prihranite tako, da ohranite le dva, tretjega pa izračunate z jezikom DAX.

Katera dva stolpca obdržati?

V zgornjem primeru bi obdržali stolpce »Količina« in »Cena enote«. Ta imata manj vrednosti od stolpca »Skupno«. Če želite izračunati stolpec »Skupno«, dodajte izračunano mero, na primer:

“TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])”

Izračunani stolpci so zelo podobni navadnim stolpcem, saj vsi zasedejo prostor v modelu. Izračunane vrednosti pa so izračunane v danem trenutku in ne zasedejo prostora.

Zaključek

V tem članku smo govorili o več pristopih, s katerimi lahko ustvarite model, ki ne bo pretirano zasedel pomnilnika. Velikost datoteke in zasedenost pomnilnika s podatkovnim modelom lahko zmanjšate tako, da zmanjšate celotno število stolpcev in vrstic, hkrati pa zmanjšate še število enoličnih vrednosti, ki se pojavijo v posameznem stolpcu. Predstavili smo te tehnike:

  • Prostor najbolje prihranite tako, da odstranite stolpce. Odločite se, katere stolpce res potrebujete.

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

  • Morda ne potrebujete vseh vrstic v tabeli. Vrstice lahko s filtriranjem odstranite v čarovniku za uvoz tabele.

  • Na splošno velja: če en stolpec razdelite na več samosvojih delov, s tem znatno zmanjšate število enoličnih vrednosti v stolpcu. V vsakem od delov bo majhno število enoličnih vrednosti, skupna vsota pa bo manjša od izvirnega združenega stolpca.

  • V veliko primerih potrebujete tudi določene dele, ki so v poročilih uporabljeni kot razčlenjevalniki. Ko je to primerno, lahko iz delov, kot so »Ure«, »Minute« in »Sekunde« ustvarite hierarhije.

  • V stolpcih je pogosto več informacij, kot je to potrebno. Vzemimo za primer stolpec, v katerem so shranjene decimalke, vendar ste decimalke skrili tako, da ste uporabili oblikovanje. Zaokrožanje je lahko zelo učinkovit pristop k zmanjševanju velikosti številčnega stolpca.

Zdaj, ki ste ga opravili, kaj lahko zmanjšate velikost delovnega zvezka, lahko tudi zaženete Optimizator velikosti delovnega zvezka. Analizira Excelov delovni zvezek in po možnosti stisne še dodatno. Prenos Optimizatorja velikosti delovnega zvezka.

Sorodne povezave

Specifikacije in omejitve podatkovnega modela

Prenos Optimizatorja velikosti delovnega zvezka

Power Pivot: Zmogljive analize podatkov in podatkovni modeli v Excelu

Opomba : Strojni prevod – zavrnitev odgovornosti: Ta članek je bil preveden z računalniškim programom brez človeškega posredovanja. Microsoft skuša s strojno prevedenimi članki vsebino o Microsoftovih izdelkih, storitvah in tehnologijah približati osebam, ki ne razumejo angleščine. Ker je bil članek strojno preveden, so lahko v njem jezikovne, slovnične in pravopisne napake.

Razširite svoja znanja
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.

×