U Excel 2013 ili novijoj možete stvoriti podatkovne modele koji sadrže milijune redaka, a zatim provesti naprednu analizu podataka u odnosu na te modele. Podatkovne modele moguće je stvoriti Power Pivot dodatkom za podršku bilo kojem broju zaokretnih tablica, grafikona i Power View vizualizacija u istoj radnoj knjizi.
Napomena: U ovom se članku opisuju podatkovni modeli u Excel 2013. No iste značajke modeliranja podataka i dodatka Power Pivot uvedene u Excel 2013 odnose se i na Excel 2016. Te verzije programa Excel učinkovito Excel.
Iako možete jednostavno izraditi velike podatkovne modele u Excel, postoji nekoliko razloga da to ne želite učiniti. Najprije su veliki modeli koji sadrže mnoštvo tablica i stupaca preveliki za većinu analiza te čine složen popis polja. Drugo, veliki modeli koriste vrijednu memoriju, što negativno utječe na druge aplikacije i izvješća koja zajednički koriste iste resurse sustava. Naposljetku, Microsoft 365 web-aplikaciji SharePoint i web-aplikaciji Excel web-aplikacije ograničavaju veličinu datoteke Excel na 10 MB. Za podatkovne modele radne knjige koji sadrže milijune redaka vrlo ćete brzo naleti na ograničenje od 10 MB. Pogledajte specifikacije i ograničenja podatkovnog modela.
U ovom ćete članku saznati kako izgraditi čvrsto konstruirani model s pomoću koji je lakše raditi i koristiti manje memorije. Uzimajući vrijeme za učenje najboljih praksi u učinkovitom dizajnu modela isplatit će se za svaki model koji stvorite i koristite, bez obzira na to gledate li ga u programu Excel 2013, Microsoft 365 SharePoint Online, na Office Web Apps poslužitelju ili u SharePoint 2013.
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 alat za optimizaciju veličine radne knjige.
Sadržaj članka
Omjeri kompresije i analitički modul u memoriji
Podatkovni modeli Excel koriste analitički modul u memoriji za pohranu podataka u memoriju. Motor implementira napredne tehnike kompresije radi smanjenja zahtjeva za pohranu, smanjujući skup 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 na mjestu njegova podrijetla. Ako, primjerice, uvozite 7 MB podataka iz baze podataka sustava SQL Server, podatkovni model u Excel može biti 1 MB ili manje. Stupanj stvarno ostvarene kompresije ovisi prvenstveno o broju jedinstvenih vrijednosti u svakom stupcu. Što su jedinstvene vrijednosti, potrebno je više memorije za pohranu.
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 zapravo nisu potrebne, osobito ako ti stupci sadrže velik broj jedinstvenih vrijednosti.
Napomena: Razlike u zahtjevima za pohranu za pojedinačne stupce mogu biti goleme. U nekim je slučajevima bolje imati više stupaca s malim brojem jedinstvenih vrijednosti, a ne jedan stupac s velikim brojem jedinstvenih vrijednosti. Odjeljak o optimizacijama datuma detaljno obuhvaća tu tehniku.
Ništa ne može nadmašiti ne postoji stupac za korištenje niske memorije
Stupac koji najviše učinkovito pamti jest onaj koji nikad 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, ostavite ga. Ako su vam potrebne, uvijek možete kasnije dodavati nove stupce.
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 "datum stvaranja", "datum ažuriranja" i "ETL pokretanje" stvaraju se prilikom učitavanja podataka. Nijedan od tih stupaca nije potreban u modelu i potrebno ih je poništiti prilikom uvoza podataka.
U drugom primjeru prilikom uvoza tablice činjenica izošćivanje stupca primarnog ključa.
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, želite primarni ključ tablice da biste ga mogli koristiti za stvaranje odnosa u modelu.
Tablice s činjenicama razlikuju se. U tablici činjenica primarni ključ koristi se za jedinstveno prepoznavanje svakog retka. Iako je to potrebno radi normalizacije, 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 ne uvrstite njegov primarni ključ. Primarni ključevi u tablici činjenica troše golemu količinu prostora u modelu, ali nemaju koristi jer se ne mogu koristiti za stvaranje odnosa.
Napomena: U podatkovnim skladištima i višedimenzionalnim bazama podataka velike tablice koje se sastoje od većinom numeričkih podataka često se nazivaju "tablice činjenica". Tablice činjenica obično obuhvaćaju poslovne performanse ili podatke o transakcijama, kao što su točke prodaje i podataka o troškovima koje se objedinjuju i poravnaju s organizacijskim jedinicama, proizvodima, tržišnim segmentima, zemljopisnim regijama i tako dalje. 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 uvršteni u model za podršku analizi podataka. Stupac koji želite izuzeti primarni je ključ stupca tablice činjenica, koji se sastoji od jedinstvenih vrijednosti koje postoje samo u tablici činjenica i nigdje drugdje. Budući da su tablice činjenica toliko velike, neki od najvećih dobitaka u učinkovitosti modela izvedeni su iz izuzetka redaka ili stupaca iz tablica činjenica.
Kako izuzeti nepotrebne stupce
Učinkoviti modeli sadrže samo one stupce koji su vam zapravo potreban u radnoj knjizi. Ako želite kontrolirati stupce obuhvaćene modelom, u dodatku Power Pivot morate koristiti čarobnjak za uvoz tablice da biste uvezli podatke umjesto dijaloškog okvira "Uvoz podataka" u programu Excel.
Kada pokrenete čarobnjak za uvoz tablice, odaberite koje tablice želite uvesti.
Za svaku tablicu možete kliknuti gumb Pretpregled & filtar pa odabrati dijelove tablice koje su vam zaista potrebne. Preporučujemo da najprije poništite sve stupce, a zatim nastavite provjeravati stupce koje želite nakon razmatranja jesu li potrebni za analizu.
Što je s filtriranje samo potrebnih redaka?
Mnoge tablice u korporativnim bazama podataka i podatkovnim skladištima sadrže povijesne podatke prikupljene tijekom duljeg vremenskog razdoblja. Osim toga, možda ćete otkriti da tablice koje vas zanimaju sadrže informacije za područja tvrtke koja nisu potrebna za vašu konkretnu analizu.
Pomoću čarobnjaka za uvoz tablice možete filtrirati povijesne ili nepovezane podatke, a time i uštedjeti mnogo prostora u modelu. Na sljedećoj se slici filtar datuma koristi za dohvaćanje samo redaka koji sadrže podatke za trenutnu godinu, osim povijesnih podataka koji nisu potrebni.
Što ako nam je potreban stupac; možemo li i dalje smanjiti njegovu cijenu prostora?
Postoji nekoliko dodatnih tehnika koje možete primijeniti da bi stupac bio bolji kandidat za sažimanje. Zapamtite da je jedina karakteristika stupca koji utječe na sažimanje broj jedinstvenih vrijednosti. U ovom ćete odjeljku saznati kako neke stupce možete izmijeniti da biste smanjili broj jedinstvenih vrijednosti.
Izmjena stupaca datetime
Stupci Datetime u mnogim slučajevima zadržavaju mnogo prostora. Srećom, postoji nekoliko načina na koje možete smanjiti preduvjete za pohranu za tu vrstu podataka. Tehnike će se razlikovati ovisno o tome kako koristite stupac, a razina udobnosti u SQL upita.
Stupci datetime obuhvaćaju dio datuma i vrijeme. Kada se zapitajte trebate li stupac, postavite isto pitanje više puta za stupac Datetime:
-
Je li mi potreban vremenski dio?
-
Je li mi potreban vremenski dio na razini sati? , minuta? , Sekunde? , milisekunde?
-
Imam li više stupaca datetime jer želim izračunati razliku između njih ili samo zbrojiti podatke po godini, mjesecu, tromjesečju i tako dalje.
Način na koji odgovarate na svako od tih pitanja određuje mogućnosti za rješavanje stupca Datetime.
Za sva ta rješenja potrebna je SQL upita. Da biste olakšali izmjenu upita, trebali biste filtrirati barem jedan stupac u svakoj tablici. Filtriranjem stupca mijenjate izgradnju upita iz skraćenog oblika (SELECT *) u izjavu 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 se prebaciti na uređivač upita i vidjeti trenutni upit SQL za svaku tablicu.
U odjeljku Svojstva tablice odaberite Uređivač upita.
Uređivač upita prikazuje upit SQL 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 "Select * from ", što će biti teže izmijeniti:
|
Izmjena upita SQL upita
Sada kada znate kako pronaći upit, možete ga izmijeniti da biste dodatno smanjili veličinu modela.
-
Ako vam za stupce koji sadrže valutne ili decimalne podatke nije potreban decimalni broj, upotrijebite ovu sintaksu da biste se riješili decimalnih brojeva:
"SELECT ROUND([Decimal_column_name],0)... .”
Ako su vam potrebne 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 Datetime pod nazivom dbo. Bigtable. [Datum vrijeme] i ne trebate dio Vrijeme, koristite sintaksu da biste se riješili vremena:
"SELECT CAST (dbo. Bigtable. [Datum vrijeme] kao datum) AS [Datum vrijeme]) "
-
Ako imate stupac Datetime pod nazivom dbo. Bigtable. [Datum vrijeme] i potrebne su vam i dijelovi Datum i vrijeme, koristite više stupaca u upitu SQL umjesto jednog stupca Datetime:
"SELECT CAST (dbo. Bigtable. [Datum vrijeme] kao datum ) AS [Datum vrijeme],
datepart(hh, dbo. Bigtable. [Datum vrijeme]) kao [Vrijeme datuma],
datepart(mi, dbo. Bigtable. [Datum vrijeme]) kao [Minute vremena datuma],
datepart(ss, dbo). Bigtable. [Datum vrijeme]) kao [Sekunde vremena datuma],
datepart(ms, dbo. Bigtable. [Datum vrijeme]) kao [Milisekunde vremena datuma]"
Koristite onoliko stupaca koliko je potrebno da biste svaki dio pohranjeli u zasebne stupce.
-
Ako su vam potrebne sate i minute, a želite ih zajedno kao jedan stupac vremena, možete koristiti sintaksu:
Timefromparts(datepart(hh, dbo. Bigtable. [Datum vrijeme]), datepart(mm, dbo. Bigtable. [Datum vrijeme])) kao [Datum vrijeme sataMinute]
-
Ako imate dva stupca datetime, kao što su [Vrijeme početka] i [Vrijeme završetka], a zapravo vam je potrebna vremenska razlika između njih u sekundama kao stupca naziva [Trajanje], uklonite oba stupca s popisa i dodajte sljedeće:
"datediff(ss;[Datum početka];[Datum završetka]) kao [Trajanje]"
Ako umjesto ss koristite ključnu riječ ms, trajanje ćete dobiti u milisekundama
Korištenje izračunatih mjera za DAX umjesto stupaca
Ako ste već radili na jeziku izraza DAX, 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ću 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 ukupni 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 kao obični stupci u tom oba prostora u modelu. Nasuprot tome, izračunate se mjere izračunavaju na letu i ne preusmjehu.
Zaključak
U ovom smo članku razgovarali o nekoliko pristupa koji vam mogu pomoći da izradite model koji je učinkovitiji u memoriji. Način smanjivanja veličine datoteke i memorije podatkovnog modela jest smanjenje ukupnog broja stupaca i redaka te broja jedinstvenih vrijednosti koje se pojavljuju u svakom stupcu. Evo nekoliko tehnika koje smo obuhvatili:
-
Uklanjanje stupaca, naravno, 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 potrebne svi reci u tablici. Retke možete filtrirati u čarobnjaku za uvoz tablice.
-
Općenito govoreći, razlamanje jednog stupca na više različitih dijelova dobar je način smanjivanja broja jedinstvenih vrijednosti u stupcu. Svaki dio ima mali broj jedinstvenih vrijednosti, a kombinirani će ukupni zbroj biti manji od izvornog objedinjenog stupca.
-
U mnogim slučajevima u izvješćima morate koristiti i zasebne dijelove kao rezače. Po potrebi možete stvarati hijerarhije iz dijelova kao što su Sati, Minute i Sekunde.
-
Stupci više puta sadrže više informacija nego što su vam potrebne. Pretpostavimo, primjerice, da stupac pohranjuje decimalne brojeve, ali ste primijenili oblikovanje da biste sakrili sve decimalne brojeve. Zaokruživanje može biti vrlo učinkovito 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 optimizatora veličine radne knjige. On analizira radnu knjigu programa Excel i dodatno je sažima ako je to moguće. Preuzmite alat za optimizaciju veličine radne knjige.
Srodne veze
Specifikacije i ograničenja podatkovnog modela
Alat za optimizaciju veličine radne knjige
PowerPivot: napredna analiza i modeliranje podataka u programu Excel
