U programu Excel možete stvoriti podatkovne modele koji sadrže milijune redaka, a zatim izvoditi naprednu analizu podataka u odnosu na te modele. Podatkovne modele moguće je stvoriti s Power Pivot ili bez dodatka da bi podržavao bilo koji broj zaokretnih tablica, grafikona i vizualizacija dodatka Power View u istoj radnoj knjizi.
Iako u programu Excel možete jednostavno izgraditi ogromne podatkovne modele, postoji nekoliko razloga za to. Prvo, veliki modeli koji sadrže mnoštvo tablica i stupaca su preveliki za većinu analiza i čine složen popis polja. Drugo, veliki modeli koriste vrijednu memoriju, što negativno utječe na druge aplikacije i izvješća koja dijele iste sistemske resurse. Naposljetku Microsoft 365 sharePoint Online i Excel Web App ograničavaju veličinu datoteke programa Excel na 10 MB. Za podatkovne modele radne knjige koji sadrže milijune redaka, vrlo brzo ćete naići na ograničenje od 10 MB. Pogledajte specifikacije i ograničenja podatkovnog modela.
U ovom ćete članku saznati kako izgraditi dobro konstruirani model s pomoću koji je jednostavnije raditi i koristiti manje memorije. Uzimanje vremena za učenje najboljih praksi u učinkovitom dizajnu modela isplatit će se za svaki model koji stvorite i koristite, bez obzira na to pregledavate li ga u programu Excel, Microsoft 365 SharePoint Online, na Office Web Apps poslužitelju ili u sustavu SharePoint.
Razmislite i o pokretanju alata za optimizaciju veličine radne knjige. On analizira radnu knjigu programa Excel i dodatno je sažima ako je to moguće. Preuzmite optimizator veličine radne knjige.
Sadržaj članka
Omjeri kompresije i modul analitike u memoriji
Podatkovni modeli u programu Excel koriste analitički modul u memoriji za pohranu podataka u memoriju. Modul implementira snažne tehnike sažimanja kako bi se smanjili zahtjevi za pohranu, smanjivanje skupa rezultata dok ne bude dio izvorne veličine.
U prosjeku možete očekivati da podatkovni model bude 7 do 10 puta manji od istih podataka u njezinu izvoru. Ako, primjerice, uvozite 7 MB podataka iz baze podataka sustava SQL Server, podatkovni model u programu Excel može jednostavno biti 1 MB ili manje. Postignuti stupanj kompresije prvenstveno ovisi o broju jedinstvenih vrijednosti u svakom stupcu. Što je više jedinstvenih vrijednosti, potrebno je više memorije da bi se pohranili.
Zašto govorimo o kompresiji i jedinstvenim vrijednostima? Budući da je stvaranje učinkovitog modela koji minimizira korištenje memorije sve o maksimizaciji kompresije, a to je najjednostavniji način da se riješite stupaca koji vam nisu potrebni, osobito ako ti stupci sadrže velik broj jedinstvenih vrijednosti.
Napomena: Razlike u zahtjevima za pohranu za pojedinačne stupce mogu biti ogromne. U nekim je slučajevima bolje imati više stupaca s malim brojem jedinstvenih vrijednosti, a ne jedan stupac s visokim brojem jedinstvenih vrijednosti. Odjeljak o optimizacijama datuma i vremena detaljno opisuje tu tehniku.
Ništa ne može nadmašiti ne postoji stupac za korištenje niske memorije
Najučinkovitiji stupac memorije jest onaj koji nikada niste uvezli. Ako želite izgraditi učinkovit model, pogledajte svaki stupac i zapitajte se pridonosi li analizi koju želite izvesti. Ako nije ili niste sigurni, izostavite to. Ako su vam potrebni, nove stupce uvijek možete dodati kasnije.
Dva primjera stupaca koje je uvijek potrebno izuzeti
Prvi se primjer odnosi na podatke koji potječu iz podatkovnog skladišta. U podatkovnom skladištu uobičajeno je pronaći artefakte ETL procesa koji učitavaju i osvježavaju podatke u skladištu. Stupci kao što su "stvori datum", "datum ažuriranja" i "pokretanje ETL-a" stvaraju se prilikom učitavanja podataka. Nijedan od tih stupaca nije potreban u modelu i treba ga poništiti prilikom uvoza podataka.
Drugi primjer uključuje izostavu stupca primarnog ključa prilikom uvoza tablice činjenica.
Mnoge tablice, uključujući tablice činjenica, imaju primarne ključeve. Za većinu tablica, kao što su oni koji sadrže podatke o klijentu, zaposleniku ili prodaji, trebat ćete primarni ključ tablice da biste ga mogli koristiti za stvaranje odnosa u modelu.
Tablice činjenica su različite. U tablici činjenica primarni ključ koristi se za jedinstvenu identifikaciju svakog retka. Iako je potreban za normalizaciju, manje je koristan u podatkovnom modelu u kojem želite koristiti samo one stupce za analizu ili za uspostavljanje odnosa između tablica. Zbog toga prilikom uvoza iz tablice činjenica nemojte uvrstiti njegov primarni ključ. Primarni ključevi u tablici činjenica troše ogromne količine prostora u modelu, ali ne pružaju nikakvu korist jer se ne mogu koristiti za stvaranje odnosa.
Napomena: U podatkovnim skladištima i višedimenzionalnim bazama podataka velike tablice koje se sastoje od uglavnom brojčanih podataka često se nazivaju "tablice činjenica". Tablice činjenica obično obuhvaćaju podatke o poslovnim performansama ili transakcijama, kao što su točke podataka o prodaji i troškovima koje se objedinjuju i poravnavaju s organizacijskim jedinicama, proizvodima, tržišnim segmentima, geografskim regijama itd. Svi stupci u tablici činjenica koji sadrže poslovne podatke ili koji se mogu koristiti za unakrsnu referencu podataka pohranjenih u drugim tablicama trebali bi biti uključeni u model radi podrške analizi podataka. Stupac koji želite izuzeti je stupac primarnog ključa tablice činjenica, koji se sastoji od jedinstvenih vrijednosti koje postoje samo u tablici činjenica i nigdje drugdje. Budući da su činjenice tablice tako velike, neki od najvećih dobiti u učinkovitosti modela izvedeni su iz isključenja redaka ili stupaca iz tablica činjenica.
Kako izuzeti nepotrebne stupce
Učinkoviti modeli sadrže samo one stupce koji su vam potrebni u radnoj knjizi. Ako želite odrediti koji su stupci obuhvaćeni modelom, morat ćete koristiti čarobnjak za uvoz tablica u dodatku Power Pivot da biste uvezli podatke umjesto dijaloškog okvira "Uvoz podataka" u programu Excel.
Kada pokrenete čarobnjak za uvoz tablica, odaberite tablice koje želite uvesti.
Za svaku tablicu možete kliknuti gumb Pretpregled & filtar i odabrati dijelove tablice koji su vam zaista potrebni. Preporučujemo da najprije poništite sve stupce, a zatim nastavite provjeravati željene stupce nakon razmatranja jesu li potrebni za analizu.
Što je s filtriranjem samo potrebnih redaka?
Mnoge tablice u korporativnim bazama podataka i podatkovnim skladištima sadrže povijesne podatke prikupljene tijekom dužeg vremenskog razdoblja. Osim toga, možda ćete saznati da tablice koje vas zanimaju sadrže informacije za područja u tvrtki koja nisu potrebna za vašu konkretnu analizu.
Pomoću čarobnjaka za uvoz tablica možete filtrirati povijesne ili nepovezane podatke te tako uštedjeti mnogo prostora u modelu. Na sljedećoj se slici koristi filtar datuma za dohvaćanje samo redaka koji sadrže podatke za trenutnu godinu, osim povijesnih podataka koji neće biti potrebni.
Što ako nam je potreban stupac; možemo li i dalje smanjiti troškove prostora?
Postoji nekoliko dodatnih tehnika koje možete primijeniti da bi stupac bio bolji kandidat za sažimanje. Imajte na umu da je jedina karakteristika stupca koja utječe na sažimanje broj jedinstvenih vrijednosti. U ovom ćete odjeljku saznati kako se neki stupci mogu mijenjati da biste smanjili broj jedinstvenih vrijednosti.
Izmjena stupaca datuma i vremena
U mnogim slučajevima stupci datuma i vremena zauzimaju mnogo prostora. Srećom, postoji nekoliko načina za smanjenje zahtjeva za pohranu za tu vrstu podataka. Tehnike će se razlikovati ovisno o načinu na koji koristite stupac i razini udobnosti u izgradnji SQL upita.
Stupci datuma i vremena obuhvaćaju dio datuma i vrijeme. Kada se zapitajte trebate li stupac, postavite isto pitanje više puta za stupac Datum i vrijeme:
-
Trebam li dio vremena?
-
Trebam li dio vremena na razini sati? minuta? Sekunde? Milisekundi?
-
Imam li više stupaca Datetime jer želim izračunati razliku između njih ili samo zbrojiti podatke po godini, mjesecu, tromjesečju itd.
Način na koji odgovarate na ta pitanja određuje mogućnosti za rješavanje stupca Datum i vrijeme.
Za sva ta rješenja potrebna je izmjena SQL upita. Da biste olakšali izmjenu upita, trebali biste filtrirati najmanje jedan stupac u svakoj tablici. Filtriranjem stupca mijenjate izgradnju upita iz skraćenog oblika (SELECT *) u naredbu SELECT koja sadrži potpuno kvalificirane nazive stupaca, koje je mnogo lakše izmijeniti.
Pogledajmo upite koji su stvoreni za vas. U dijaloškom okviru Svojstva tablice možete prijeći u uređivač upita i vidjeti trenutni SQL upit za svaku tablicu.
U odjeljku Svojstva tablice odaberite Uređivač upita.
Uređivač upita prikazuje SQL upit koji se koristi za popunjavanje tablice. Ako ste filtrirali bilo koji stupac tijekom uvoza, upit sadrži potpuno kvalificirane nazive stupaca:
|
Nasuprot tome, ako ste u cijelosti uvezli tablicu bez poništavanja bilo kojeg stupca ili primjene filtra, upit ćete vidjeti kao "Odaberi * iz ", što će biti teže izmijeniti:
|
Izmjena SQL upita
Sada kada znate kako pronaći upit, možete ga izmijeniti da biste dodatno smanjili veličinu modela.
-
Ako vam decimale ne trebaju stupci koji sadrže valutne ili decimalne podatke, koristite ovu sintaksu da biste se riješili decimalnih mjesta:
"SELECT ROUND([Decimal_column_name],0)... .”
Ako su vam potrebni centi, ali ne i razlomci centa, zamijenite 0 s 2. Ako koristite negativne brojeve, možete zaokružiti na jedinice, desetke, stotine itd.
-
Ako imate stupac Datum i vrijeme pod nazivom dbo. To je velika maиa. [Date Time] i ne trebate dio Vrijeme, koristite sintaksu da biste dobili osloboditi od vremena:
"SELECT CAST (dbo. To je velika maиa. [Datum vrijeme] kao datum) AS [Datum vrijeme]) "
-
Ako imate stupac Datum i vrijeme pod nazivom dbo. To je velika maиa. [Datum vrijeme] i potrebni su vam i dijelovi datuma i vremena, koristite više stupaca u SQL upitu umjesto jednog stupca Datetime:
"SELECT CAST (dbo. To je velika maиa. [Datum vrijeme] kao datum ) AS [Datum vrijeme],
datepart(hh, dbo. To je velika maиa. [Datum vrijeme]) kao [Datum vrijeme sati],
datepart(mi, dbo. To je velika maиa. [Datum vrijeme]) kao [Minute datuma i vremena],
datepart(ss, dbo). To je velika maиa. [Datum vrijeme]) kao [datumsko vrijeme sekundi],
datepart(ms, dbo. To je velika maиa. [Datum vrijeme]) as [Date Time Milliseconds]"
Upotrijebite koliko god stupaca želite da svaki dio pohranite u zasebne stupce.
-
Ako su vam potrebni sati i minute, a želite ih zajedno kao jedan stupac, možete koristiti sintaksu:
Timefromparts(datepart(hh, dbo. To je velika maиa. [Datum vrijeme]), datepart(mm, dbo. To je velika maиa. [Datum vrijeme])) as [Date Time HourMinute]
-
Ako imate dva stupca datuma i vremena, kao što su [Vrijeme početka] i [Vrijeme završetka], a zapravo vam je potrebna vremenska razlika između njih u sekundama kao stupca pod nazivom [Trajanje], uklonite oba stupca s popisa i dodajte:
"datediff(ss,[Datum početka],[Datum završetka]) kao [Trajanje]"
Ako koristite ključnu riječ ms umjesto ss, trajanje ćete dobiti u milisekundama
Korištenje izračunatih mjera za DAX umjesto stupaca
Ako ste već radili s daX jezikom izraza, možda već znate da se izračunati stupci koriste za izvođenje novih stupaca na temelju nekog drugog stupca u modelu, dok se izračunate mjere definiraju jednom u modelu, ali se vrednuju samo kada se koriste u zaokretnoj tablici ili drugom izvješću.
Jedna tehnika spremanja memorije jest zamjena običnih ili izračunatih stupaca izračunatim mjerama. Klasični primjer su Jedinična cijena, Količina i Ukupni zbroj. Ako imate sva tri, možete uštedjeti prostor tako da zadržite samo dva, a treći računate pomoću DAX-a.
Koja biste dva stupca trebali zadržati?
U prethodnom primjeru zadržite količinu i jediničnu cijenu. Te dvije vrijednosti imaju manje vrijednosti od ukupnog zbroja. Da biste izračunali zbroj, dodajte izračunatu mjeru kao što je:
"UkupnaProdaja:=sumx('Tablica prodaje','Tablica prodaje'[Jedinična cijena]*'Tablica prodaje'[Količina])"
Izračunati stupci su poput običnih stupaca u kojem oba zauzimaju prostor u modelu. Nasuprot tome, izračunate se mjere izračunavaju u hodu i ne zauzimaju prostor.
Zaključak
U ovom smo članku razgovarali o nekoliko pristupa koji vam mogu pomoći da stvorite model koji učinkovitije koristi memoriju. Način smanjivanja veličine i memorije podatkovnog modela jest smanjivanje ukupnog broja stupaca i redaka te broja jedinstvenih vrijednosti koje se pojavljuju u svakom stupcu. Ovo su neke od tehnika koje smo obradili:
-
Uklanjanje stupaca najbolji je način uštede prostora. Odlučite koje stupce zaista trebate.
-
Ponekad možete ukloniti stupac i zamijeniti ga izračunatom mjerom u tablici.
-
Možda vam nisu potrebni svi reci u tablici. Retke možete filtrirati u čarobnjaku za uvoz tablica.
-
Općenito govoreći, razlamanje jednog stupca na više različitih dijelova dobar je način za smanjenje broja jedinstvenih vrijednosti u stupcu. Svaki dio ima mali broj jedinstvenih vrijednosti, a ukupni zbroj bit će manji od izvornog objedinjenog stupca.
-
U mnogim slučajevima potrebni su vam i različiti dijelovi koji će se koristiti kao rezači u izvješćima. Po potrebi možete stvarati hijerarhije iz dijelova kao što su Sati, Minute i Sekunde.
-
Stupci mnogo puta sadrže više informacija nego što vam je potrebno. Pretpostavimo, primjerice, da stupac pohranjuje decimale, ali ste primijenili oblikovanje da biste sakrili sve decimale. Zaokruživanja mogu biti vrlo učinkoviti u smanjivanju veličine numeričkog stupca.
Sada kada ste učinili sve što možete da biste smanjili veličinu radne knjige, razmislite i o pokretanju alata za optimizaciju veličine radne knjige. On analizira radnu knjigu programa Excel i dodatno je sažima ako je to moguće. Preuzmite optimizator veličine radne knjige.
Srodne veze
Specifikacije i ograničenja podatkovnog modela
Optimizator veličine radne knjige
PowerPivot: napredna analiza i modeliranje podataka u programu Excel
