U programu Excel možete stvoriti podatkovne modele koji sadrže milijune redaka, a zatim provesti naprednu analizu podataka na tim modelima. Podatkovni modeli mogu se stvarati s dodatkom Power Pivot ili bez njega da bi podržali proizvoljan broj zaokretnih tablica, grafikona i vizualizacija dodatka Power View u istoj radnoj knjizi.
Iako u programu Excel možete jednostavno izgraditi ogromne modele podataka, nekoliko je razloga da to ne učinite. Za početak, veliki modeli koji sadrže mnogo tablica i stupaca pretjerani su za većinu analiza i čine glomazan popis polja. Drugo, veliki modeli koriste dragocjenu memoriju, što negativno utječe na druge aplikacije i izvješća koja dijele iste resurse sustava. Naposljetku, u sustavu Microsoft 365 i SharePoint Online i Excel Web App ograničena su veličinu datoteke programa Excel na 10 MB. U slučaju podatkovnih modela radne knjige koja sadrži milijune redaka vrlo brzo ćete doći do ograničenja od 10 MB. Pogledajte specifikacije i ograničenja podatkovnog modela.
U ovom članku saznat ćete kako izgraditi čvrsto konstruiran model koji je jednostavniji za rad i zauzima manje memorije. Odvajanje vremena za učenje najboljih praksi za učinkovito dizajniranje modela isplatit će se kasnije svim modelima koje stvorite i koristite, bez obzira na to pregledavate li ga u programu Excel, sustavu Microsoft 365 SharePoint Online, na poslužitelju web-aplikacija Office Web Apps Server 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 alat za optimizaciju veličine radne knjige.
Sadržaj članka
Ništa nije bolje od nepostojećeg stupca za malo korištenja memorije
Što ako nam treba stupac; Možemo li i dalje smanjiti troškove prostora?
Omjeri kompresije i modul za analitiku u memoriji
Podatkovni modeli u programu Excel koriste modul za analitiku u memoriji za pohranu podataka u memoriju. Motor implementira snažne tehnike kompresije kako bi smanjio zahtjeve za skladištenjem, smanjujući skup rezultata dok ne postane djelić svoje izvorne veličine.
U prosjeku možete očekivati da će podatkovni model biti 7 do 10 puta manji od istih podataka na početnoj točki. Ako, primjerice, uvozite 7 MB podataka iz baze podataka sustava SQL Server, podatkovni model u programu Excel lako bi mogao biti 1 MB ili manje. Stvarno postignuti stupanj sažimanja prvenstveno ovisi o broju jedinstvenih vrijednosti u svakom stupcu. Što je više jedinstvenih vrijednosti, potrebno je više memorije za njihovu pohranu.
Zašto govorimo o kompresiji i jedinstvenim vrijednostima? Jer izgradnja učinkovitog modela koji minimizira korištenje memorije svodi se na maksimiziranje kompresije, a to je najjednostavnije učiniti tako da se riješite svih stupaca koji vam zapravo nisu potrebni, pogotovo 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 nego jedan stupac s velikim brojem jedinstvenih vrijednosti. U odjeljku o optimizacijama datuma i vremena detaljno je opisana ta tehnika.
Ništa nije bolje od nepostojećeg stupca za malo korištenja memorije
Memorijski najučinkovitiji stupac je onaj koji uopće niste uvezli. Ako želite izgraditi učinkovit model, pogledajte svaki stupac i zapitajte se pridonosi li on analizi koju želite provesti. Ako ne podržava ili niste sigurni, izostavite ga. Nove stupce možete poslije uvijek dodati ako vam zatrebaju.
Dva primjera stupaca koje je uvijek potrebno izuzeti
Prvi se primjer odnosi na podatke koji potječu iz skladišta podataka. U skladištu podataka 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 run" stvaraju se prilikom učitavanja podataka. Nijedan od tih stupaca nije potreban u modelu i njihov odabir treba poništiti prilikom uvoza podataka.
Drugi primjer obuhvaća izostavljanje stupca primarnog ključa prilikom uvoza tablice činjenica.
Mnoge tablice, uključujući tablice činjenica, imaju primarne ključeve. Za većinu tablica, poput onih koje sadrže podatke o kupcu, zaposleniku ili prodaji, potreban vam je primarni ključ tablice kako biste pomoću njega mogli stvoriti odnose u modelu.
Tablice činjenica jesu različite. U tablici činjenica primarni se ključ koristi za jedinstvenu identifikaciju svakog retka. Premda je potreban u svrhu normalizacije, manje je koristan u podatkovnom modelu u kojem želite da se za analizu ili za uspostavljanje odnosa između tablica koriste samo oni stupci. Zato prilikom uvoza iz tablice činjenica nemojte uvrstiti njezin primarni ključ. Primarni ključevi u tablici činjenica zauzimaju ogromne količine prostora u modelu, ali ne pružaju nikakvu korist jer se ne mogu koristiti za stvaranje odnosa.
Napomena
U skladištima podataka i višedimenzionalnim bazama podataka velike tablice koje se uglavnom sastoje od numeričkih podataka često se nazivaju "tablicama č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 su agregirane i usklađene s organizacijskim jedinicama, proizvodima, tržišnim segmentima, zemljopisnim regijama itd. Svi stupci tablice činjenica koji sadrže poslovne podatke ili se mogu koristiti za unakrsno povezivanje podataka pohranjenih u drugim tablicama moraju biti uvršteni u model radi podrške analizi podataka. Stupac koji želite izuzeti stupac je primarnog ključa tablice činjenica, koji se sastoji od jedinstvenih vrijednosti koje postoje samo u tablici činjenica i nigdje drugdje. Budući da su tablice činjenica goleme, neki od najvećih dobitaka u učinkovitosti modela rezultat su izuzimanja redaka ili stupaca iz tablica činjenica.
Isključivanje nepotrebnih stupaca
Učinkoviti modeli sadrže samo one stupce koji će vam zaista biti potrebni u radnoj knjizi. Ako želite odrediti koji će stupci biti uvršteni u model, 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, birate koje tablice želite uvesti.
Za svaku tablicu možete kliknuti gumb Pretpregled & Filtriraj te odabrati dijelove tablice koji su vam zaista potrebni. Preporučujemo da najprije poništite okvire za sve stupce, a zatim nastavite s provjerom željenih stupaca nakon što razmotrite jesu li potrebni za analizu.
Što je s filtriranjem samo nužnih redaka?
Mnoge tablice u korporacijskim bazama podataka i skladištima podataka sadrže povijesne podatke akumulirane tijekom dugih vremenskih razdoblja. Uz to, tablice koje vas zanimaju možda sadrže informacije o područjima poslovanja koja nisu potrebna za određenu analizu.
Pomoću čarobnjaka za uvoz tablica možete filtrirati povijesne ili nepovezane podatke i tako uštedjeti prostor u modelu. Na sljedećoj se slici koristi filtar datuma za dohvaćanje redaka koji sadrže podatke za tekuću godinu, osim povijesnih podataka koji neće biti potrebni.
Što ako nam treba stupac; Možemo li i dalje smanjiti troškove prostora?
Postoji nekoliko dodatnih tehnika koje možete primijeniti da biste stupac učinili boljim kandidatom za kompresiju. Imajte na umu da je jedina karakteristika stupca koja utječe na sažimanje broj jedinstvenih vrijednosti. U ovom ćete odjeljku saznati kako je moguće izmijeniti neke stupce radi smanjenja broja jedinstvenih vrijednosti.
Modifying Datetime columns
U mnogim slučajevima stupci s datumima i vremenima zauzimaju mnogo prostora. Srećom, postoji nekoliko načina na koje možete smanjiti zahtjeve za pohranom za tu vrstu podataka. Tehnike će se razlikovati ovisno o tome kako koristite stupac i koliko ste zadovoljni prilikom stvaranja SQL upita.
Stupci s datumom i vremenom obuhvaćaju dio datuma i vrijeme. Kada se upitate treba li vam stupac, postavite isto pitanje više puta za stupac Datum/vrijeme:
- 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, kvartalu itd.
Način na koji ćete odgovoriti na svako od tih pitanja određuje mogućnosti rada sa stupcem Datum/vrijeme.
Sva ta rješenja zahtijevaju modificiranje SQL upita. Da biste pojednostavnili izmjenu upita, filtrirajte barem jedan stupac u svakoj tablici. Filtriranjem stupca mijenjate konstrukciju upita iz skraćenog oblika (SELECT *) u naredbu SELECT koja obuhvaća pune nazive stupaca, koje je mnogo lakše izmijeniti.
Pogledajmo upite koji su stvoreni za vas. Iz dijaloškog okvira Svojstva tablice možete prijeći na uređivač upita i vidjeti trenutni SQL upit za svaku tablicu.
U Svojstvima tablice odaberite Uređivač upita.
Uređivač upita prikazuje SQL upit koji je korišten za popunjavanje tablice. Ako ste tijekom uvoza filtrirali neki stupac, upit će obuhvatiti pune nazive stupaca:
Nasuprot tome, ako ste uvezli cijelu tablicu, a da niste poništili nijedan stupac niti primijenili nijedan filtar, upit će vam se prikazivati kao "Odaberi * iz", što će biti teže izmijeniti:
|
|---|
Modifying the SQL query
Sada kada znate kako pronaći upit, možete ga izmijeniti da biste dodatno smanjili veličinu modela.
- Ako vam decimalni brojevi nisu potrebni za stupce koji sadrže valutne ili decimalne podatke, uklonite ih pomoću sljedeće sintakse:
"SELECT ROUND([Decimal_column_name],0)... .”
Ako su vam potrebni centi, ali ne i dijelovi centi, zamijenite 0 s 2. Ako koristite negativne brojeve, možete zaokružiti na jedinice, desetice, stotice itd. - Ako imate stupac Datum/vrijeme pod nazivom dbo. Velika tablica. [Datum i vrijeme] i nije vam potreban dio vremena, koristite sintaksu da biste se riješili vremena:
"ODABERI ULOGE (dbo. Velika tablica. [Date time] as [Date time]) " - Ako imate stupac Datum/vrijeme pod nazivom dbo. Velika tablica. [Datum i vrijeme] te su vam potrebni i dio Datum i Vrijeme, u SQL upitu koristite više stupaca umjesto jednog stupca Datum i vrijeme:
"ODABERI ULOGE (dbo. Velika tablica. [Date Time] as (Datum i vrijeme)) AS [Date Time],
DatePart(hh, dbo. Velika tablica. [Datum i vrijeme]) kao [Date Time Hours],
DatePart(MI, DBO. Velika tablica. [Datum i vrijeme]) kao [Datum, Vrijeme, Minute],
DatePart(SS, DBO. Velika tablica. [Datum i vrijeme]) kao [Datum, vrijeme, sekunde],
DatePart(MS, DBO. Velika tablica. [Datum i vrijeme]) kao [Datum i vrijeme milisekunde]"
Upotrijebite koliko god stupaca trebate da biste svaki dio pohranili u zasebne stupce. - Ako su vam potrebni sati i minute, a želite ih zajedno kao jedan vremenski stupac, možete koristiti sintaksu:
Timefromparts(datepart(hh, dbo. Velika tablica. [Date Time]), datepart(mm, dbo. Velika tablica. [Datum i vrijeme])) as [Date Time HourMinute] - Ako imate dva stupca s datumom i vremenom, npr. [Vrijeme početka] i [Vrijeme završetka], a zapravo vam je potrebna vremenska razlika u sekundama u obliku stupca pod nazivom [Trajanje], uklonite oba stupca s popisa i dodajte:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
Ako upotrijebite ključnu riječ ms umjesto ss, dobit ćete trajanje u milisekundama
Korištenje DAX izračunatih mjera umjesto stupaca
Ako ste već radili s jezikom DAX 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 procjenjuju samo kada se koriste u zaokretnoj tablici ili nekom drugom izvješću.
Jedna od tehnika uštede memorije jest zamjena običnih ili izračunatih stupaca izračunatim mjerama. Klasični je primjer Jedinična cijena, Količina i Ukupno. Ako imate sve tri, prostor možete uštedjeti tako da zadržite samo dva, a treću izračunate pomoću jezika DAX.
Koja 2 stupca zadržati?
U gornjem primjeru zadržite stavke Količina i Jedinična cijena. Te dvije stavke imaju manje vrijednosti od ukupnog zbroja. Da biste izračunali ukupni zbroj, dodajte izračunatu mjeru kao što je:
"TotalSales:=sumx('Tablica prodaje','Tablica prodaje'[Jedinična cijena]*'Tablica prodaje'[Količina])"
Izračunati stupci slični su običnim stupcima po tome što oba zauzimaju prostor u modelu. Nasuprot tome, izračunate mjere izračunavaju se u hodu i ne zauzimaju prostor.
Zaključak
U ovom smo članku govorili o nekoliko pristupa koji vam mogu pomoći u stvaranju memorijski učinkovitijeg modela. Način smanjenja veličine datoteke i memorijskih zahtjeva podatkovnog modela jest smanjenje ukupnog broja stupaca i redaka te broja jedinstvenih vrijednosti koje se pojavljuju u svakom stupcu. Evo nekih tehnika koje smo pokrili:
- Uklanjanje stupaca je, naravno, najbolji način uštede prostora. Odlučite koji su vam stupci zaista potrebni.
- Ponekad je moguće ukloniti stupac i zamijeniti ga izračunatom mjerom u tablici.
- Možda vam neće biti potrebni svi reci u tablici. Retke možete filtrirati u čarobnjaku za uvoz tablica.
- Razdvajanje jednog stupca na više različitih dijelova dobar je način smanjenja broja jedinstvenih vrijednosti u stupcu. Svaki od dijelova imat će manji broj jedinstvenih vrijednosti, a kombinirani zbroj bit će manji od izvornog objedinjenog stupca.
- U velikom broju slučajeva potrebni su vam i posebni dijelovi koje ćete koristiti kao rezače u izvješćima. Kada je to prikladno, hijerarhije možete stvarati od dijelova kao što su sati, minute i sekunde.
- Stupci često sadrže više informacija nego što vam je potrebno. Pretpostavimo, primjerice, da stupac pohranjuje decimalne brojeve, ali ste primijenili oblikovanje da biste sakrili sve decimale. Zaokruživanje može biti vrlo učinkovito u smanjivanju veličine numeričkog stupca.
Sada kada ste učinili sve što je u vašoj moći da smanjite 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 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