U programu Excel možete da kreirate modele podataka koji sadrže milione redova, a zatim da u njima izvršite moćnu analizu podataka. Modeli podataka mogu se kreirati sa ili bez programskog dodatka Power Pivot kako bi podržali bilo koji broj izvedenih tabela, grafikona i Power View vizuelizacija u istoj radnoj svesci.
Iako lako možete da napravite ogromne modele podataka u programu Excel, postoji nekoliko razloga da to ne uradite. Za prvo, veliki modeli koji sadrže mnoštvo tabela i kolona suvišni su za većinu analiza i čine glomaznu listu polja. Drugo, veliki modeli koriste dragocenu memoriju, što negativno utiče na druge aplikacije i izveštaje koji dele iste sistemske resurse. Konačno, u sistemu Microsoft 365, i SharePoint Online i Excel Web App ograničavaju veličinu Excel datoteke na 10 MB. Za modele podataka radne sveske koji sadrže milione redova, veoma brzo ćete doseći ograničenje od 10 MB. Pogledajte specifikacije i ograničenja modela podataka.
U ovom članku ćete naučiti kako da napravite čvrsto konstruisan model sa kojim je lakše raditi i koji koristi manje memorije. Odvojite vreme da naučite najbolje prakse u efikasnom dizajniranju modela isplatiće se za svaki model koji kreirate i koristite, bilo da ga pregledate u programu Excel, usluzi Microsoft 365 SharePoint Online, na Office Web Apps serveru ili u sistemu SharePoint.
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
Ništa nije bolje od nepostojeće kolone za nisku iskorišćenost memorije
Šta ako nam je potrebna kolona; Možemo li i dalje smanjiti troškove prostora?
Odnos kompresije i analitička mašina u memoriji
Modeli podataka u programu Excel koriste analitiku unutrašnje memorije za skladištenje podataka u memoriji. Motor primenjuje moćne tehnike kompresije kako bi smanjio zahteve za skladištenje, smanjujući skup rezultata dok ne postane delić svoje originalne veličine.
U proseku možete očekivati da je model podataka 7 do 10 puta manji od istih podataka na mestu 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 komprimovanja koji se stvarno postiže prvenstveno zavisi od broja jedinstvenih vrednosti u svakoj koloni. Što je više jedinstvenih vrednosti, to je više memorije potrebno za njihovo skladištenje.
Zašto govorimo o kompresiji i jedinstvenim vrednostima? Zato što je izgradnja efikasnog modela koji smanjuje iskorišćenost memorije usmerena na maksimizovanje komprimovanja, a najlakši način da to uradite jeste da se rešite svih kolona koje vam zapravo nisu potrebne, posebno ako te kolone sadrže veliki broj jedinstvenih vrednosti.
Napomena
Razlike u zahtevima za skladište za pojedinačne kolone mogu biti ogromne. U nekim slučajevima, bolje je imati više kolona sa malim brojem jedinstvenih vrednosti umesto jedne kolone sa velikim brojem jedinstvenih vrednosti. Odeljak o optimizacijama datuma i vremena detaljno pokriva ovu tehniku.
Ništa nije bolje od nepostojeće kolone za nisku iskorišćenost memorije
Memorijski najefikasnija kolona je ona koju nikad niste uvezli. Ako želite da izgradite efikasan model, pogledajte svaku kolonu i zapitajte se da li ona doprinosi analizi koju želite da izvršite. Ako to nije slučaj ili niste sigurni, izostavite ga. Nove kolone uvek možete kasnije da dodate ako vam zatrebaju.
Dva primera kolona koje uvek treba izuzeti
Prvi primer se odnosi na podatke koji potiču iz skladišta podataka. U skladištu podataka se uobičajeno pronalaze artefakti ETL procesa koji učitavaju i osvežavaju podatke u skladištu. Kolone kao što su "datum kreiranja", "datum ažuriranja" i "ETL run" kreiraju se kada se podaci učitaju. Nijedna od ovih kolona nije potrebna u modelu i trebalo bi da bude opozvan kada uvezete podatke.
Drugi primer podrazumeva izostavljanje kolone primarnog ključa prilikom uvoza tabele sa činjenicama.
Mnoge tabele, uključujući tabele sa činjenicama, 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 sa činjenicama se razlikuju. U tabeli sa činjenicama, primarni ključ se koristi za jedinstveno identifikovanje svakog reda. Iako je neophodna u svrhe normalizacije, manje je korisna u modelu podataka u kom želite da se koriste samo one kolone za analizu ili uspostavljanje relacija između tabela. Iz ovog razloga, kada uvozite iz tabele sa činjenicama, nemojte uključivati njen primarni ključ. Primarni ključevi u tabeli činjenica troše ogromne količine prostora u modelu, ali ne pružaju nikakvu korist, jer se ne mogu koristiti za kreiranje odnosa.
Napomena
U skladištima podataka i višedimenzionalnim bazama podataka, velike tabele koje se sastoje uglavnom od numeričkih podataka često se nazivaju "tabelama sa činjenicama". Tabele sa podacima obično obuhvataju podatke o učinku ili transakciji poslovanja, kao što su tačke podataka o prodaji i troškovima koje se objedinjuju i poravnavaju sa organizacionim jedinicama, proizvodima, tržišnim segmentima, geografskim regionima i tako dalje. Sve kolone u tabeli sa činjenicama koje sadrže poslovne podatke ili koje se mogu koristiti za unakrsno upućivanje na podatke uskladištene u drugim tabelama treba da budu uključene u model radi podržavanja analize 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 drugde. Pošto su tabele sa činjenicama veoma velike, neki od najvećih dobitaka u efikasnosti modela proizilaze iz tabela sa činjenicama.
Kako da izuzmete nepotrebne kolone
Efikasni modeli sadrže samo one kolone koje će vam stvarno biti potrebne u radnoj svesci. Ako želite da kontrolišete koje su kolone 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 na dugme "Pregled & Filtriraj" i izaberete delove tabele koji su vam zaista potrebni. Preporučujemo vam da prvo opozovete izbor svih kolona, a zatim nastavite sa proverom željenih kolona nakon što razmotrite da li su neophodne za analizu.
Šta je sa filtriranjem samo neophodnih redova?
Mnoge tabele u poslovnim bazama podataka i skladištima podataka sadrže istorijske podatke akumulirane tokom dužeg vremenskog perioda. Pored toga, možete otkriti da tabele koje vas zanimaju sadrže informacije za oblasti poslovanja koje nisu potrebne za vašu određenu analizu.
Pomoću čarobnjaka za uvoz tabele možete da filtrirate istorijske ili nepovezane podatke i tako sačuvate dosta 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; Možemo li i dalje smanjiti troškove prostora?
Postoji nekoliko dodatnih tehnika koje možete primeniti da biste kolonu učinili boljim kandidatom za kompresiju. 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/vreme"
U mnogim slučajevima kolone sa datumom i vremenom zauzimaju dosta prostora. Srećom, postoji više načina da se smanje zahtevi za skladištem za ovaj tip podataka. Tehnike će se razlikovati u zavisnosti od načina na koji koristite kolonu i vašeg nivoa udobnosti u pravljenju SQL upita.
Kolone datuma i 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 deo?
- Da li mi je potreban vremenski deo na nivou časova? , minuta? , sekunde? , milisekunde?
- Da li imam više kolona "Datum" i vreme zato što želim da izračunam razliku između njih ili samo da bih sgrupisao podatke po godini, mesecu, kvartalu i tako dalje?
Način na koji ćete odgovoriti na svako od ovih pitanja određuje opcije za rad 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 konstrukciju upita iz skraćenog formata (SELECT *) u izraz SELECT koji uključuje potpuno određena imena kolona, koja je daleko lakše izmeniti.
Hajde da pogledamo upite koji su kreirani za vas. U dijalogu "Svojstva tabele" možete se prebaciti na uređivač upita i videti trenutni SQL upit za svaku tabelu.
Iz okna "Svojstva tabele" izaberite Uređivač upita.
Uređivač upita prikazuje SQL upit koji se koristi za popunjavanje tabele. Ako ste filtrirali neku kolonu tokom uvoza, upit sadrži potpuno određena imena kolona:
Nasuprot tome, ako ste uvezli tabelu u celosti, bez opoziva izbora bilo koje kolone ili primene filtera, videćete upit kao "Izaberi * iz ", što će biti teže da se izmeni:
|
|---|
Izmena SQL upita
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 podatke o valutama ili decimalne podatke, ako vam decimale nisu potrebne, koristite ovu sintaksu da biste uklonili decimale:
"SELECT ROUND([Decimal_column_name],0)... .”
Ako su vam potrebni centi, ali ne i razlomci centi, zamenite 0 sa 2. Ako koristite negativne brojeve, možete da zaokružite na jedinice, desetine, stotine itd. - Ako imate kolonu "Datum/vreme" koja se zove dbo. Veliki tabela. [Datum ili vreme] i da vam ne treba deo "Vreme", koristite sintaksu da biste uklonili vreme:
"IZABERI CAST (dbo. Veliki tabela. [Datum i vreme] kao datum) AS [Datum, vreme]) " - Ako imate kolonu "Datum/vreme" koja se zove dbo. Veliki tabela. [Datum Vreme] i treba da vam budu deo "Datum" i "Vreme", koristite više kolona u SQL upitu umesto jedne kolone "Datum/vreme":
"IZABERI CAST (dbo. Veliki tabela. [Datum Vreme] kao datum ) AS [Datum Vreme],
DatePart(hh, DBO. Veliki tabela. [Datum Vreme]) kao [datum, vreme, sati],
DatePart(mi, dbo. Veliki tabela. [Datum Vreme]) kao [datum, vreme, minuti],
DatePart(ss, DBO. Veliki tabela. [Datum Vreme]) kao [datum, vreme, sekunde],
DatePart(ms, dbo. Veliki tabela. [Datum Vreme]) kao [datum, vreme, milisekunde]"
Koristite onoliko kolona koliko vam je potrebno da biste svaki deo uskladištili u zasebne kolone. - Ako su vam potrebni časovi i minuti, a želite da budu zajedno kao jedna kolona vremena, možete da koristite sintaksu:
Timefromparts(datepart(hh, dbo. Veliki tabela. [Datum Vreme]), DatePart(mm, dbo. Veliki tabela. [Datum Vreme])) kao [Datum, Vreme, SatMinute] - Ako imate dve kolone sa datumom i vremenom, kao što su [Vreme početka] i [Vreme završetka], a ono što vam je zaista potrebno jeste vremenska razlika između njih u sekundama u obliku kolone koja se zove [Trajanje], uklonite obe kolone sa liste i dodajte:
"datediff(ss,[Datum početka],[Datum završetka]) AS [Trajanje]"
Ako koristite ključnu reč ms umesto ss, dobićete trajanje u milisekundama
Korišćenje DAX izračunatih mera umesto kolona
Ako ste ranije radili sa DAX jezikom za izražavanje, možda već znate da se izračunate kolone koriste za izvođenje novih kolona na osnovu neke druge kolone u modelu, dok se izračunate mere definišu jednom u modelu, ali se procenjuju samo kada se koriste u izvedenoj tabeli ili drugom izveštaju.
Jedna od tehnika za uštedu memorije jeste zamena redovnih ili izračunatih kolona izračunatim merama. Klasični primer je cena po jedinici, količina i ukupna vrednost. Ako imate sva tri, možete da uštedite prostor tako što ćete održavati samo dva, a treću izračunati pomoću jezika DAX.
Koje 2 kolone treba da zadržite?
U gorenavedenom primeru zadržite količinu i cenu po jedinici. Njih dve imaju manje vrednosti od ukupne vrednosti. Da biste izračunali ukupnu vrednost, dodajte izračunatu meru kao što je:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Izračunate kolone su poput standardnih kolona jer obe zauzimaju prostor u modelu. Nasuprot tome, izračunate mere se izračunavaju u pokretu i ne zauzimaju prostor.
Zaključak
U ovom članku smo govorili o nekoliko pristupa koji vam mogu pomoći da izgradite memorijski efikasniji model. Način da smanjite veličinu datoteke i zahteve za memoriju 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.
- Razdvajanje jedne kolone na više različitih delova obično je dobar način za smanjenje broja 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, različite delove ćete morati da koristite kao module za sečenje u izveštajima. Kada je to prikladno, hijerarhije možete da napravite od delova kao što su časovi, minuti i sekunde.
- Kolone često sadrže više informacija nego što je vama potrebno. Na primer, pretpostavimo da kolona skladišti decimale, ali ste primenili oblikovanje da biste sakrili sve decimale. Zaokruživanje može da bude veoma efikasno u smanjivanju veličine numeričke kolone.
Sada kada ste uradili sve što možete da biste smanjili veličinu radne sveske, razmotrite 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