U Excel 2013 ili novijim, možete da kreirate modele podataka koji sadrže milione redova, a zatim da vršite moćnu analizu podataka sa ovim modelima. Modeli podataka mogu da se kreiraju sa programski dodatkom Power Pivot ili bez njega 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 Excel.
Iako u tabelama možete lako da izgradite ogromne modele Excel podataka, postoji nekoliko razloga za to. Pre svega, veliki modeli koji sadrže više brojeva tabela i kolona su preopterećeni za većinu analize i prave se za nebitnu listu polja. Drugi, veliki modeli koriste vrednu memoriju, što negativno utiče na druge aplikacije i izveštaje koji dele iste sistemske resurse. Na kraju, Microsoft 365 u aplikaciji SharePoint Online i Excel Web App ograničavaju veličinu datoteke Excel na 10 MB. Za modele podataka radne sveske koji sadrže milione redova, naići ćete na ograničenje od 10 MB veoma brzo. Pogledajte specifikaciju i ograničenja modela podataka.
U ovom članku ćete saznati kako da napravite veoma konstruisan model sa tekstom koji je lakši za rad i koji koristi manje memorije. Ako vremenom naučite najbolje prakse u efikasnom dizajnu modela, isplatiće se put za svaki 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 programu 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 komprimovanjem i mašina in-memorijske anatike
Modeli podataka u Excel koriste mašinu in-memorijske anatike za skladištenje podataka u memoriji. Mašina primenjuje moćne tehnike komprimovanja kako bi smanjila zahteve za skladište i smanjila skup rezultata sve dok ne bude deo originalne veličine.
Možete da očekujete da model podataka bude 7 do 10 puta manji od istih podataka u tački 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 manji. Stepen postizanje komprimovani teksta prevashodno zavisi od broja jedinstvenih vrednosti u svakoj koloni. Što su jedinstvenije vrednosti veće, potrebna je više memorije za skladištenje.
Zašto je to razgovor o komprimociji i jedinstvenim vrednostima? Pošto je za izgradnju efikasnog modela koji smanjuje korišćenje memorije sveo na uvećavanje komprimocije, a najlakši način da to uradite jeste da se rešite kolona koje vam zapravo nisu potrebne, naročito ako te kolone sadrže veliki broj jedinstvenih vrednosti.
Napomena: Razlike u zahtevima za skladištenje za pojedinačne kolone mogu da budu velike. U nekim slučajevima, bolje je da imate više kolona sa niskim brojem jedinstvenih vrednosti umesto jedne kolone sa visokim brojem jedinstvenih vrednosti. Odeljak o optimizacijama datuma ili vremena detaljno pokriva ovu tehniku.
Ništa ne premaši nepostojeću kolonu zbog niske memorije
Najefikasnija kolona je kolona koju nikada niste uvezli na prvom mestu. Ako želite da napravite efikasan model, pogledajte svaku kolonu i zapitajte se da li doprinosi analizi koji želite da izvršite. Ako nije ili niste sigurni, ostavite je. Uvek možete kasnije da dodate nove kolone ako su vam potrebne.
Dva primera kolona koje bi uvek trebalo izuzeti
Prvi primer se odnosi na podatke koji potiču iz skladišta podataka. U skladištu podataka uobičajeno je pronaći artefakte ETL procesa koji učitaju i osvežavaju podatke u skladištu. Kolone kao što su "kreiranje datuma", "datum ažuriranja" i "ETL pokretanje" kreiraju se kada se učitaju podaci. Nijedna od ovih kolona nije neophodna u modelu i trebalo bi da se poništi izbor prilikom uvoza podataka.
Drugi primer uključuje izostavljanje kolone primarnog ključa prilikom uvoza tabele sa činjenicama.
Mnoge tabele, uključujući činjenice, imaju primarne ključeve. Za većinu tabela, kao što su one koje sadrže podatke o klijentu, zaposlenom ili prodaji, želećete primarni ključ tabele da biste mogli da ga koristite za kreiranje relacija u modelu.
Tabele činjenica se razlikuju. U tabeli sa činjenicama, 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 između tabela. Iz ovog razloga, prilikom uvoza iz tabele sa činjenicama nemojte da uključite njen primarni ključ. Primarni ključevi u tabeli činjenice koriste veoma velike količine prostora u modelu, ali im i pored toga ne pružaju pogodnosti 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 sa činjenicama obično uključuju poslovne učinke ili podatke o transakcijama, kao što su tačke podataka prodaje i troškova koje se agregiraju i poravnaju sa organizacionim jedinicama, proizvodima, segmentima tržišta, geografskim regionima i tako dalje. Sve kolone u tabeli sa činjenicama koje sadrže poslovne podatke ili koje možete koristiti za unakrsno upućivanje na podatke uskladištene u drugim tabelama trebalo bi da budu uključene u model da bi se podržala analiza 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 sa činjenicama i nigde više. Pošto su tabele činjenica toliko velike, neki od najvećih dobitka u modelu efikasnosti izuzimaju redove ili kolone iz tabela činjenica.
Kako da isključite nepotrebne kolone
Efikasni modeli sadrže samo kolone koje su vam zapravo potrebne u radnoj svesci. Ako želite da kontrolišete koje kolone su uključene u model, morate da koristite čarobnjak za uvoz tabele u programski dodatak 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 na dugme Pregled & filtriranje i izaberete delove tabele koji su vam zaista potrebni. Preporučujemo da prvo poništite izbor svih kolona, a zatim nastavite sa proverom kolona koje želite, nakon što razmotrite da li su one neophodne za analizu.
Šta je sa filtriranjem samo potrebnih redova?
Mnoge tabele u korporativnim bazama podataka i skladištima podataka sadrže istorijske podatke stečene tokom dužeg vremenskog perioda. Pored toga, možete da otkrijete da tabele koje vas zanimaju sadrže informacije o oblastima preduzeća koje nisu neophodne za određenu analizu.
Pomoću čarobnjaka za uvoz tabele možete da filtrirate istorijske ili nepovezane podatke i tako uštedite mnogo prostora u modelu. Na sledećoj slici filter datuma se koristi za preuzimanje samo redova koji sadrže podatke za tekuću godinu, isključujući istorijske podatke koji neće biti potrebni.
Šta ako nam je potrebna kolona; da li i dalje možemo da smanjimo troškove prostora?
Postoji nekoliko dodatnih tehnika koje možete primeniti da biste kolonu načiniti boljim kandidatom za komprimovanje. Ne zaboravite da je jedina karakteristika kolone koja utiče na komprimovanje broj jedinstvenih vrednosti. U ovom odeljku saznaćete kako se neke kolone mogu izmeniti da biste smanjili broj jedinstvenih vrednosti.
Modifying Datetime columns
U mnogim slučajevima, kolone datuma i vremena zauzimaju dosta prostora. Srećom, postoji više načina da smanjite zahteve za skladištenje ovog tipa podataka. Tehnike će se razlikovati u zavisnosti od toga kako koristite kolonu i nivo udobnosti u izgradnji SQL upita.
Kolone datuma ili vremena uključuju deo datuma i vreme. Kada se zapitate da li vam je potrebna kolona, postavite isto pitanje više puta za kolonu Datum/vreme:
-
Da li mi je potreban vremenski period?
-
Da li mi je potreban vremenski period na nivou časova? , minuta? , Sekunde? milisekune?
-
Da li imam više kolona "Datum/vreme" zato što želim da izračunam razliku između njih ili da sakuim podatke po godini, mesecu, kvartalu i tako dalje.
Način na koji odgovarate na svako od ovih pitanja određuje opcije u vezi sa kolonom "Datum/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 izgradnju upita iz skraćenog formata (SELECT *) u SELECT izjavu koja uključuje potpuno kvalifikovana imena kolona, koja se mnogo lakše menjaju.
Hajde da pogledamo upite koji su vam napravljeni. U dijalogu Svojstva tabele možete da se prebacite na uređivač upita i vidite trenutni SQL za svaku tabelu.
Iz stavke Svojstva tabele izaberite Uređivač upita.
Uređivač upita prikazuje SQL koji se koristi za popunite tabelu. Ako ste filtrirali bilo koju kolonu tokom uvoza, upit sadrži potpuno kvalifikovana imena kolona:
|
Nasuprot tome, ako ste u potpunosti uvezli tabelu, bez izbora bilo koje kolone ili primene filtera, videćete upit kao "Izaberite * iz ", što će biti teže za izmenu:
|
Modifying the SQL query
Sada kada znate kako da pronađete upit, možete da ga izmenite da biste dodatno smanjili veličinu modela.
-
Za kolone koje sadrže valutu ili decimalne podatke, ako vam nisu potrebni decimale, koristite ovu sintaksu da biste se rešili decimala:
"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 da zaokružite na jedinice, desetke, stotine itd.
-
Ako imate kolonu "Datum/vreme" pod imenom "dbo". Bigtable. [Datum vremena] i nije vam potreban deo "Vreme", koristite sintaksu da biste se rešili vremena:
"SELECT CAST (dbo. Bigtable. [Datum vremena] kao datum) AS [Datum vremena]) "
-
Ako imate kolonu "Datum/vreme" pod imenom "dbo". Bigtable. [Datum vremena] i potrebni su vam delovi datuma i vremena i koristite više kolona u SQL umesto jedne kolone "Datum/vreme":
"SELECT CAST (dbo. Bigtable. [Datum vremena] kao datum ) KAO [Datum vremena],
datepart(hh, dbo. Bigtable. [Datum vremena]) kao [Datum vremena],
datepart(mi, dbo. Bigtable. [Datum vremena]) kao [Minuti datuma vremena],
datepart(ss, dbo. Bigtable. [Datum vremena]) kao [Sekunde datuma vremena],
datepart(ms, dbo. Bigtable. [Datum vremena]) kao [Date Time Milliseconds]"
Koristite koliko god kolona vam je potrebno da uskladištite svaki deo u zasebnim kolonama.
-
Ako su vam potrebni časovi i minuti, a više volite da su u jednoj vremenskoj koloni, možete da koristite sintaksu:
Timefromparts(datepart(hh, dbo. Bigtable. [Datum vremena]), datepart(mm, dbo. Bigtable. [Datum vremena])) kao [Date Time HourMinute]
-
Ako imate dve kolone datuma ili vremena, kao što su [Vreme početka] i [Vreme završetka], a zapravo vam je potrebna vremenska razlika između njih u sekundama kao kolona koja se zove [Trajanje], uklonite obe kolone sa liste i dodajte:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
Ako koristite ključnu reč ms umesto ss, dobijate trajanje u milisekunama
Korišćenje DAX izračunatih mera umesto kolona
Ako ste ranije radili na jeziku DAX izraza, možda već znate da se izračunate kolone koriste za izvedene nove kolone na osnovu neke druge kolone u modelu, dok su izračunate mere definisane jednom u modelu, ali se procenjuju samo kada se koriste u izvedenoj tabeli ili drugom izveštaju.
Jedna tehnika čuvanja memorije jeste zamena redovnih ili izračunatih kolona izračunatim merama. Klasični primer je Cena po jedinici, Količina i Ukupno. Ako imate sva tri prostora, možete da uštedite prostor tako što ćete zadržati samo dva, a izračunati i treću pomoću DAX datoteke.
Koje 2 kolone treba da zadržite?
U gorenavednom primeru zadržite vrednost "Količina" i "Cena 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 standardne kolone po tome što obe zauzimaju prostor u modelu. Nasuprot tome, izračunate mere se izračunava u hodu i ne zauzimaju prostor.
Zaključak
U ovom članku govorimo o nekoliko pristupa koji vam mogu pomoći da napravite memorijski efikasniji model. Da biste smanjili veličinu datoteke i zahteve memorije modela podataka jeste da smanjite ukupan broj kolona i redova, kao 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šteno, razdvajanje jedne kolone na više različitih delova je dobar način da smanjite broj jedinstvenih vrednosti u koloni. Svaki deo će imati mali broj jedinstvenih vrednosti, a kombinovani zbir će biti manji od originalne jedinstvene kolone.
-
U mnogim slučajevima će vam biti potrebni i različiti delovi da biste ih koristili kao module za sečanje u izveštajima. Kada je to prikladno, možete da kreirate hijerarhije iz delova kao što su Časovi, Minuti i Sekunde.
-
Kolone mnogo puta sadrže više informacija nego što su vam potrebne. Na primer, recimo da kolona skladišti decimale, ali ste primenili oblikovanje da biste sakrili sve decimale. Zaokruživanje može da bude veoma efikasno u 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
