U programu Excel 2013 ili novijim verzijama možete da kreirate modele podataka koji sadrže milione redova, a zatim da izvršite moćnu analizu podataka u odnosu na ove modele. Modeli podataka mogu da se kreiraju sa Power Pivot ili bez njih kako bi podržali bilo koji broj izvedenih tabela, grafikona i Power View vizuelizacija u istoj radnoj svesci.
Napomena: Ovaj članak opisuje modele podataka u programu Excel 2013. Međutim, isto modeliranje podataka i Power Pivot funkcije predstavljeni u programu Excel 2013 važe i za Excel 2016. Postoji efikasno malo razlika između ovih verzija programa Excel.
Iako lako možete da napravite ogromne modele podataka u programu Excel, postoji nekoliko razloga da to ne uradite. Prvo, veliki modeli koji sadrže višestruke tabele i kolone su prebrojani za većinu analiza i praviju se za neobjašnjenu listu polja. Drugo, veliki modeli koriste vrednu memoriju, što negativno utiče na druge aplikacije i izveštaje koji dele iste sistemske resurse. Na kraju, Microsoft 365 aplikacije SharePoint Online i Excel Web App ograničava veličinu Excel datoteke na 10 MB. Za modele podataka radne sveske koji sadrže milione redova, veoma brzo ćete naići na ograničenje od 10 MB. Pogledajte članak Specifikacija i ograničenja modela podataka.
U ovom članku ćete saznati kako da napravite zbijeno konstruisani model koji je lakši za rad i koji koristi manje memorije. Ako imate vremena da naučite najbolje prakse u efikasnom dizajnu modela, isplatiće se tokom puta za bilo koji model koji kreirate i koristite, bez obzira na to da li ga prikazujete u programu Excel 2013, Microsoft 365 SharePoint Online, na Office Web Apps serveru ili u sistemu SharePoint 2013.
Uzmite u obzir i pokretanje optimizatora veličine radne sveske. On analizira Excel radnu svesku i dodatno je komprimuje ako je to moguće. Preuzmite optimizator veličine radne sveske.
U ovom članku
Odnos komprimovanja i mašina za analitiku u memoriji
Modeli podataka u programu Excel koriste mašinu analitike u memoriji za skladištenje podataka u memoriji. Mašina primenjuje moćne tehnike komprimovanja radi smanjenja zahteva za skladište, smanjujući skup rezultata dok ne dobije deo originalne veličine.
U proseku možete očekivati da model podataka bude 7 do 10 puta manji od istih podataka u trenutku njihovog porekla. Na primer, ako uvozite 7 MB podataka iz SQL Server baze podataka, model podataka u programu Excel može lako da bude 1 MB ili manje. Stepen ostvarenog komprimovanja zavisi prvenstveno od broja jedinstvenih vrednosti u svakoj koloni. Što je više jedinstvenih vrednosti, potrebno je više memorije za njihovo skladištenje.
Zašto govorimo o komprimovanja i jedinstvenim vrednostima? Pošto je pravljenje efikasnog modela koji umanjuje iskorišćenost memorije sveo na maksimizovanje komprimovanja, a najlakši način da to uradite jeste da se rešite kolona koje vam zapravo nisu potrebne, naročito ako te kolone uključuju veliki broj jedinstvenih vrednosti.
Napomena: Razlike u zahtevima za skladištenje za pojedinačne kolone mogu da budu ogromne. U nekim slučajevima je bolje imati više kolona sa malim brojem jedinstvenih vrednosti, a ne jednu kolonu sa visokim brojem jedinstvenih vrednosti. Odeljak o optimizacijama datuma i vremena detaljno pokriva ovu tehniku.
Ništa ne nadmaši nepostojeću kolonu za nisku iskorišćenost memorije
Najefikasnija memorijski efikasna kolona je kolona koju nikada niste uvezli. Ako želite da napravite efikasan model, pogledajte svaku kolonu i zapitajte se da li ona doprinosi analizi koju želite da izvršite. Ako nije ili niste sigurni, izoštite ga. Uvek možete kasnije da dodate nove kolone ako su vam potrebne.
Dva primera kolona koje uvek treba izuzeti
Prvi primer se povezan sa podacima koji potiču iz skladišta podataka. U skladištu podataka je uobičajeno pronaći artefakte ETL procesa koji učitavaju i osvežavaju podatke u magacinu. Kolone kao što su "kreiranje datuma", "Datum ažuriranja" i "ETL pokretanje" kreiraju se kada se učitaju podaci. Nijedna od ovih kolona nije potrebna u modelu i trebalo bi da se opozove izbor prilikom uvoza podataka.
Drugi primer uključuje izostavljanje kolone primarnog ključa prilikom uvoza tabele sa činjenicama.
Mnoge tabele, uključujući činjenice tabele, imaju primarne ključeve. Za većinu tabela, kao što su one koje sadrže podatke o kupcu, zaposlenom ili prodaji, želećete primarni ključ tabele kako biste mogli da ga koristite za kreiranje relacija u modelu.
Tabele činjenica se razlikuju. U tabeli činjenica, primarni ključ se koristi za jedinstveno identifikovanje svakog reda. Iako je to neophodno u svrhe normalizacije, manje je korisno u modelu podataka gde želite da se koriste samo te kolone za analizu ili za uspostavljanje relacija među tabelama. Iz ovog razloga, prilikom uvoza iz tabele sa činjenicama, nemojte da uključite njen primarni ključ. Primarni ključevi u tabeli činjenica zauzimaju ogromne količine prostora u modelu, ali ne pružaju nikakve prednosti jer ih nije moguće koristiti za kreiranje relacija.
Napomena: U skladištima podataka i višedimenzionalnim bazama podataka velike tabele koje se sastoje od uglavnom numeričkih podataka često se nazivaju "tabele činjenica". Tabele činjenica obično uključuju poslovne podatke o performansama ili transakcijama, kao što su tačke podataka o prodaji i troškovima koje su grupisane i poravnate sa organizacionim jedinicama, proizvodima, segmentima tržišta, geografskim regionima i tako dalje. Sve kolone u tabeli činjenica koje sadrže poslovne podatke ili koje mogu da se koriste za unakrsno upućivanje na podatke uskladištene u drugim tabelama trebalo bi da budu uključene u model kako bi podržale analizu podataka. Kolona koju želite da izuzmete je kolona primarnog ključa tabele sa činjenicama, koja se sastoji od jedinstvenih vrednosti koje postoje samo u tabeli činjenica i nigde više. Pošto su tabele činjenica toliko velike, neke od najvećih dobitka u efikasnosti modela izvedene su iz isključivanja redova ili kolona iz tabela činjenica.
Kako da isključite nepotrebne kolone
Efikasni modeli sadrže samo kolone koje će vam zapravo biti potrebne u radnoj svesci. Ako želite da kontrolišete koje kolone su uključene u model, moraćete da koristite čarobnjak za uvoz tabele u programskom dodatku Power Pivot da biste uvezli podatke, a ne dijalog "Uvoz podataka" u programu Excel.
Kada pokrenete čarobnjak za uvoz tabele, birate koje tabele želite da uvezete.
Za svaku tabelu možete da kliknete & dugme Filtriraj i izaberete delove tabele koji su vam zaista potrebni. Preporučujemo da prvo opozovete izbor svih kolona, a zatim da nastavite sa proverom željenih kolona, nakon što razmislite o tome da li su potrebne za analizu.
Šta je sa filtriranjem samo potrebnih redova?
Mnoge tabele u korporativnim bazama podataka i skladištima podataka sadrže istorijske podatke akumulirane tokom dužeg vremenskog perioda. Pored toga, možda ćete primetiti da tabele koje vas zanimaju sadrže informacije za oblasti preduzeća koje nisu potrebne za vašu određenu analizu.
Pomoću čarobnjaka za uvoz tabele možete da filtrirate istorijske ili nepovezane podatke i na ovaj način uštedite mnogo prostora u modelu. Na sledećoj slici filter za datum se koristi za preuzimanje samo redova koji sadrže podatke za trenutnu godinu, isključujući istorijske podatke koji neće biti potrebni.
Šta ako nam je potrebna kolona; Da li još uvek možemo da mu smanjimo troškove prostora?
Postoji nekoliko dodatnih tehnika koje možete primeniti da biste kolonu učinili boljim kandidatom za komprimovanje. Ne zaboravite da je jedina karakteristika kolone koja utiče na komprimovanje broj jedinstvenih vrednosti. U ovom odeljku ćete saznati kako neke kolone mogu da se izmene da bi se smanjio broj jedinstvenih vrednosti.
Izmena kolona "Datum i vreme"
U mnogim slučajevima, kolone "Datum i vreme" zauzimaju mnogo prostora. Srećom, postoje brojni načini za smanjenje zahteva za skladištenje za ovaj tip podataka. Tehnike će se razlikovati u zavisnosti od toga kako koristite kolonu i nivo udobnosti u pravljenju SQL upita.
Kolone datuma i vremena uključuju deo datuma i vreme. Kada se zapitate da li vam je potrebna kolona, više puta postavite isto pitanje za kolonu Datum i vreme:
-
Da li mi treba vremenski deo?
-
Da li mi je potreban vremenski deo na nivou časova? Minuta? Sekundi? Milisekundi?
-
Da li imam više kolona "Datum i vreme" zato što želim da izračunam razliku između njih ili samo da grupišem podatke po godini, mesecu, kvartalu i tako dalje.
Način na koji odgovarate na svako od ovih pitanja određuje opcije za rad sa kolonom "Datum i vreme".
Sva ova rešenja zahtevaju izmenu SQL upita. Da biste olakšali izmenu upita, trebalo bi da filtrirate najmanje jednu kolonu u svakoj tabeli. Filtriranjem kolone menjate konstrukciju upita iz skraćenog formata (SELECT *) u SELECT izraz koji sadrži potpuno određena imena kolona, koja se mnogo lakše menjaju.
Hajde da pogledamo upite koji su kreirani za vas. U dijalogu Svojstva tabele možete da se prebacite na uređivač upita i vidite trenutni SQL upit za svaku tabelu.
U okviru Svojstva tabele izaberite stavku Uređivač upita.
Dijalog Uređivač upita prikazuje SQL upit koji se koristi za popunjavanje tabele. Ako ste filtrirali bilo koju kolonu tokom uvoza, upit sadrži potpuno određena imena kolona:
|
Nasuprot tome, ako ste uvezli tabelu u cenosti, bez opozivanja izbora bilo koje kolone ili primene filtera, videćete upit kao "Izaberite * iz ", što će biti teže izmeniti:
|
Izmena SQL upita
Sada kada znate kako da pronađete upit, možete da ga izmenite kako biste dodatno smanjili veličinu modela.
-
Za kolone koje sadrže podatke o valutama ili decimale, ako vam nisu potrebni decimale, koristite ovu sintaksu da biste uklonili decimale:
"SELECT ROUND([Decimal_column_name],0)... .”
Ako su vam potrebni centi, ali ne i razlomci centi, zamenite broj 0 sa 2. Ako koristite negativne brojeve, možete zaokružiti na jedinice, desetke, stotine itd.
-
Ako imate kolonu "Datum i vreme" pod imenom dbo. Bigtable. [Datum i vreme] i nije vam potreban deo "Vreme", koristite sintaksu da biste se rešili vremena:
"SELECT CAST (dbo. Bigtable. [Datum i vreme] kao datum) AS [Datum i vreme]) "
-
Ako imate kolonu "Datum i vreme" pod imenom dbo. Bigtable. [Datum i vreme] i potrebni su vam delovi datuma i vremena, koristite više kolona u SQL upitu umesto jedne kolone "Datum i vreme":
"SELECT CAST (dbo. Bigtable. [Datum i vreme] kao datum ) AS [Datum/vreme],
datepart(hh, dbo. Bigtable. [Datum i vreme]) kao [Datum i vreme],
datepart(mi, dbo. Bigtable. [Datum i vreme]) kao [Minuti datuma vremena],
datepart(ss, dbo. Bigtable. [Datum i vreme]) kao [Sekunde za datum/vreme],
datepart(ms, dbo. Bigtable. [Datum i vreme]) kao [Milisekuunde datuma vremena]"
Koristite koliko god kolona je potrebno za skladištenje svakog dela u zasebnim kolonama.
-
Ako su vam potrebni časovi i minuti, a želite da budu zajedno kao jednokratna kolona, možete da koristite sintaksu:
Timefromparts(datepart(hh, dbo. Bigtable. [Datum i vreme]), datepart(mm, dbo. Bigtable. [Datum i vreme])) kao [Date Time HourMinute]
-
Ako imate dve kolone datuma/vremena, kao što su [Vreme početka] i [Vreme završetka], a ono što vam je zaista potrebno je razlika u vremenu između njih u sekundama kao kolona pod imenom [Trajanje], uklonite obe kolone sa liste i dodajte:
"datumi datuma,[Datum početka],[Datum završetka]) kao [Trajanje]"
Ako koristite ključnu reč ms umesto ss, dobijate trajanje u milisekundama
Korišćenje DAX izračunatih mera umesto kolona
Ako ste ranije radili sa DAX jezikom izraza, možda već znate da se izračunate kolone koriste za izvedene nove kolone na osnovu neke druge kolone u modelu, dok se izračunate mere definišu jednom u modelu, ali procenjuju samo kada se koriste u izvedenoj tabeli ili drugom izveštaju.
Jedna tehnika za čuvanje memorije jeste zamena redovnih ili izračunatih kolona izračunatim merama. Klasični primer je Cena po jedinici, Količina i Ukupno. Ako ih imate sve tri, možete da uštedite prostor tako što ćete održati samo dva, a treću izračunati koristeći DAX.
Koje 2 kolone treba da zadržite?
U gorenavedenom primeru zadržite količinu i cenu po jedinici. Ove dve imaju manje vrednosti od ukupne vrednosti. Da biste izračunali ukupnu vrednost, dodajte izračunatu meru kao što je:
"UkupnaProdaja:=sumx('Tabela prodaje','Tabela prodaje'[Cena po jedinici]*'Tabela prodaje'[Količina])"
Izračunate kolone su kao obične kolone u koje obe zauzimaju prostor u modelu. Nasuprot tome, izračunate mere se izračunavaju u letu i ne zauzimaju prostor.
Zaključak
U ovom članku smo razgovarali o nekoliko pristupa koji vam mogu pomoći da napravite memorijski efikasniji model. Način da smanjite veličinu datoteke i zahteve memorije modela podataka jeste da smanjite ukupan broj kolona i redova i broj jedinstvenih vrednosti koje se pojavljuju u svakoj koloni. Evo nekih tehnika koje smo pokrili:
-
Uklanjanje kolona je naravno najbolji način da uštedite prostor. Odlučite koje kolone su vam zaista potrebne.
-
Ponekad možete da uklonite kolonu i zamenite je izračunatom merom u tabeli.
-
Možda vam neće biti potrebni svi redovi u tabeli. Redove možete da filtrirate u čarobnjaku za uvoz tabele.
-
Uopšte uzev, razdvajanje jedne kolone na više različitih delova predstavlja dobar način da smanjite broj jedinstvenih vrednosti u koloni. Svaki od delova će imati mali broj jedinstvenih vrednosti, a kombinovani zbir će biti manji od originalne objedinjene kolone.
-
U mnogim slučajevima su vam potrebni i različiti delovi za korišćenje kao module za sečenje u izveštajima. Kada je to prikladno, možete da kreirate hijerarhije od delova kao što su Časovi, Minuti i Sekunde.
-
Kolone mnogo puta sadrže više informacija nego što su vam potrebne. Na primer, pretpostavimo da kolona skladišti decimale, ali ste primenili oblikovanje da biste sakrili sve decimale. Zaokruživanje može biti veoma efikasno za smanjivanje veličine numeričke kolone.
Sada kada ste uradili ono što možete da biste smanjili veličinu radne sveske, razmotrite i pokretanje optimizatora veličine radne sveske. On analizira Excel radnu svesku i dodatno je komprimuje ako je to moguće. Preuzmite optimizator veličine radne sveske.
Srodne veze
Specifikacija i ograničenja modela podataka
Optimizator veličine radne sveske
Power Pivot: Moćna analiza podataka i modelovanje podataka u programu Excel
