Stvaranje memorijski učinkovitog podatkovnog modela pomoću programa Excel i dodatka PowerPivot

Važno :  Ovaj je članak strojno preveden. Pogledajteizjavu o odricanju od odgovornosti. Verziju ovog članka na engleskom potražite ovdje.

U programu Excel 2013 ili noviju verziju, možete stvoriti podatkovne modele koji sadrži milijune redaka i zatim izvodili naprednu analizu podataka na temelju tih modela. Podatkovni modeli mogu se kreirati sa ili bez Power Pivot dodatka za podršku bilo koji broj zaokretnim tablicama, grafikonima i u značajci Power View u istoj radnoj knjizi.

Napomena : U ovom se članku opisuju podatkovnih modela u programu Excel 2013. Međutim, iste podatke Modeliranje i Power Pivot značajke uvedene u programu Excel 2013 i primijeniti u programu Excel 2016. Postoji učinkovito mala razlika između ove verzije programa Excel.

Iako možete jednostavno izraditi velikog podatkovnih modela u programu Excel, postoji nekoliko mogućih razloga ne na. Prvi, veliki modela koji sadrže multitudes tablica i stupaca su overkill za većinu analize i provjerite naporan popis polja. Drugi, veliki modela pomoću koristan njegovih, negativno utjecaja na druge programe i izvješća koja se zajednički koristiti iste sistemske resurse. Naposljetku, u sustavu Office 365 SharePoint Online i Excel Web App ograničiti veličinu datoteke programa Excel od 10 MB. Za podatkovne modele radnih knjiga koje sadrže milijune redaka, naići ćete 10 MB ograničenja vrlo brzo. Potražite u članku Specifikacija i ograničenja modela podataka.

U ovom ćete članku saznati kako stvoriti uredno strukturiran model s kojim je lakše raditi i koji koristi manje memorije. Vrijeme koje ćete potrošiti na usvajanje korisnih naputaka za stvaranje učinkovitih modela kasnije će vam se uvelike isplatiti prilikom stvaranja i korištenja modela, bilo u programu Excel 2013, bilo u sustavima Office 365 SharePoint Online, Office Web Apps Server ili SharePoint 2013.

Uzmite u obzir i pokretanje alat za optimizaciju veličine radne knjige. Analizira radnoj knjizi programa Excel, a po mogućnosti sažima Dodatno. Preuzmite Alat za optimizaciju veličine radne knjige.

Sadržaj članka

Omjeri kompresije i modul u memoriji analytics

Ništa ne troši od nepostojećeg stupca manje memorije

Dva primjera stupaca koje uvijek treba izostaviti

Izostavljanje nepotrebnih stupaca

Što je s filtriranjem samo nužnih redaka?

Ako ipak Trebamo stupac. možemo li smanjiti njegovo opterećenje memorijskih resursa?

Izmjena stupaca s datumom i vremenom

Izmjena SQL upita

Korištenje DAX izračunatih mjera umjesto stupaca

Koja 2 stupca zadržati?

Zaključak

Srodne veze

Omjeri sažimanja i analitički modul u memoriji

Podatkovni modeli u programu Excel koriste analitički modul u memoriji za pohranu podataka u memoriji. Taj modul primjenjuje napredne tehnike kompresije radi smanjenja zahtjeva za pohranu, sažimajući skup rezultata dok njegova veličina ne iznosi samo djelić prvotne.

U prosjeku možete očekivati da podatkovni model bude od 7 do 10 puta manji od iste količine podataka u izvornom stanju i na prvotnom mjestu. Ako, primjerice, uvozite 7 MB podataka iz baze podataka sustava SQL Server, podatkovni model u programu Excel može sasvim lako biti velik 1 MB ili čak manje. Stvarni stupanj kompresije prvenstveno ovisi o broju jedinstvenih vrijednosti u svakom stupcu – što je više jedinstvenih vrijednosti, to je potrebno više memorije za njihovu pohranu.

Zašto spominjemo kompresiju i jedinstvene vrijednosti? Jer se stvaranje učinkovitog modela koji minimalno opterećuje memorijske resurse svodi na maksimiziranje kompresije, a najlakši je način postizanja toga ukloniti sve stupce koje zapravo ne trebate, posebno ako oni sadrže veliki broj jedinstvenih vrijednosti.

Napomena : Zahtjevi pojedinih stupaca vezani uz pohranu mogu se znatno razlikovati. U nekim je slučajevima bolje imati više stupaca s malim brojem jedinstvenih vrijednosti, nego jedan stupac s velikim brojem jedinstvenih vrijednosti. Ta je tehnika detaljno opisana u odjeljku o optimizacijama podataka datuma i vremena.

Ništa ne troši manje memorije od nepostojećeg stupca

Stupac koji najmanje opterećuje memoriju onaj je koji uopće niste ni uvezli. Želite li stvoriti učinkovit model, pogledajte svaki od stupaca i zapitajte se pridonosi li doista analizi koju želite provesti. Ako ne pridonosi ili niste sigurni pridonosi li, izostavite ga. Uvijek možete naknadno dodati nove stupce ako ćete ih trebati.

Dva primjera stupaca koje uvijek treba izostaviti

Prvi se primjer odnosi na podatke koji potječu iz skladišta podataka. Naime, u skladištu podataka često se susreću ostaci ETL procesa kojima se prenose i osvježavaju podaci u skladištu. Prilikom prijenosa podataka stvaraju se stupci poput “Datum stvaranja”, “Datum ažuriranja” i “Izvođenje ETL-a”. U modelu nije potreban nijedan od tih stupaca pa je uputno poništiti njihov odabir prilikom uvoza podataka.

Drugi se primjer tiče ispuštanja stupca primarnog ključa prilikom uvoza tablice činjenica.

Mnoge tablice, uključujući i tablice činjenica, sadrže primarne ključeve. Kod većine tablica, primjerice onih koje sadrže podatke o kupcima, zaposlenicima ili prodaji, trebat ćete primarni ključ tablice da biste pomoću njega mogli stvarati odnose u modelu.

Tablice činjenica nisu takve. U njima se primarni ključ koristi za jedinstvenu identifikaciju svakog retka. Pa iako je nužan za potrebe normalizacije, nije osobito koristan u podatkovnom modelu u kojemu želite samo stupce koji se koriste u analizi ili za uspostavljanje odnosa među tablicama. Upravo stoga prilikom uvoza iz tablice činjenica nemojte uvesti i njen primarni ključ. Primarni ključevi u tablici činjenica zauzimaju ogromnu količinu prostora u modelu, a ne nude nikakve prednosti jer se ne mogu koristiti za stvaranje odnosa.

Napomena : U skladištima podataka i višedimenzijskim bazama podataka velike tablice koje se uglavnom sastoje od brojčanih podataka često se nazivaju "tablicama činjenica". Tablice činjenica obično sadrže podatke o poslovnim performansama ili transakcijama, primjerice točke podataka o prodaji i troškovima prikupljene i raspoređene po organizacijskim jedinicama, proizvodima, tržišnim segmentima, zemljopisnim regijama i slično. Sve stupce u tablici činjenica koji sadrže poslovne podatke ili se mogu koristiti za unakrsno referenciranje podataka pohranjenih u drugim tablicama treba uvrstiti u model radi analize podataka. Stupac koje možete izostaviti je onaj primarnog ključa tablice činjenica koji se sastoji jedinstvenih vrijednosti koje postoje samo u tablici činjenica i nigdje drugdje. Tablice činjenica su ogromne, pa se najveći pomaci u unaprjeđenju učinkovitosti modela postižu upravo izostavljanjem redaka ili stupaca iz tablica činjenica..

Izostavljanje nepotrebnih stupaca

Učinkovito modelima sadržavati samo one stupce koji zapravo morat ćete u radnoj knjizi. Ako želite da biste odredili stupce koji se nalaze u modelu, morat ćete koristiti za uvoz tablica u dodatku Power Pivot dodatka za uvoz podataka umjesto u dijaloškom okviru "Uvoz podataka" u programu Excel.

Kad pokrenete čarobnjak za uvoz tablica, odabrat ćete koje tablice želite uvesti.

Čarobnjak za uvoz tablica u dodatku PowerPivot

Za svaku tablicu možete kliknuti gumb Pretpregledaj i filtriraj da biste odabrali dijelove tablice koje doista trebate. Preporučujemo da prvo poništite odabir svih stupaca, a zatim potvrdite stupce koje želite nakon razmatranja jesu li potrebni za analizu.

Okno pretpregleda u čarobnjaku za uvoz tablica

Što je s filtriranjem samo nužnih redaka?

Mnoge tablice u poslovnim bazama i skladištima podataka sadrže povijesne podatke prikupljene tijekom dužih vremenskih razdoblja. Možda ćete shvatiti i da tablice koje vas zanimaju sadrže informacije o područjima poslovanja koja nisu relevantna za provođenje vaše analize.

Pomoću čarobnjaka za uvoz tablica možete filtrirati povijesne i nevezane informacije i tako uštedjeti puno mjesta u modelu. Na sljedećoj slici prikazana je upotreba filtra datuma za dohvaćanje samo onih redaka koji sadrže podatke za tekuću godinu, bez nepotrebnih povijesnih podataka.

Okno filtra u čarobnjaku za uvoz tablica

Ako ipak trebamo stupac, možemo li smanjiti njegovo opterećenje memorijskih resursa?

Postoji nekoliko dodatnih tehnika kojima se povećava mogućnost kompresije stupca. Zapamtite da je jedina karakteristika stupca koja utječe na kompresiju broj jedinstvenih vrijednosti. U ovom ćete odjeljku naučiti kako je moguće izmijeniti neke stupce radi smanjenja broja jedinstvenih vrijednosti.

Izmjena stupaca s datumom i vremenom

U većini slučajeva stupci s datumom i vremenom znatno opterećuju memorijske resurse, no srećom postoji nekoliko načina kojima možemo smanjiti memorijske zahtjeve te vrste podataka. Tehnike se razlikuju u ovisnosti o načinima korištenja tog stupca i vašem znanju stvaranja SQL upita.

Stupci s datumom i vremenom sadrže datumski i vremenski dio. Kad se pitate trebate li stupac, za stupac s datumom i vremenom to si isto pitanje možete postaviti više puta:

  • Trebam li vremenski dio?

  • Trebam li vremenski dio na razini sati, minuta, sekundi ili milisekundi?

  • Imam li više stupaca s datumom i vremenom jer želim izračunati razliku između njih ili samo radi grupiranja podataka po godini, mjesecu, kvartalu i slično?

Vaš odgovor na ta pitanja određuje mogućnosti koje imate na raspolaganju u radu sa stupcem s datumom i vremenom.

Sva navedena rješenja podrazumijevaju izmjenu SQL upita. Da bi izmjena upita bila jednostavnija, možete iz svake tablice filtrirati barem jedan stupac. Time ćete promijeniti konstrukciju upita iz skraćenog oblika (SELECT *) u izjavu SELECT koja obuhvaća posve kvalificirane nazive stupaca koje je lakše mijenjati.

Pogledajmo koji su upiti stvoreni. Pomoću dijaloškog okvira Svojstva tablice možete se prebaciti u Uređivač upita i vidjeti trenutni SQL upit za svaku tablicu.

vrpca u prozoru dodatka powerpivot na kojoj je prikazana naredba svojstva tablice

U odjeljku Svojstva tablice odaberite Uređivač upita.

pomoću dijaloškog okvira svojstva tablice otvorite uređivač upita

U uređivaču upita prikazan je SQL upit koji se koristi za popunjavanje tablice. Ako ste tijekom uvoza filtrirali neke stupce, upit sadrži njihove posve kvalificirane nazive:

sql upit korišten za dohvaćanje podataka

Suprotno tome, ako ste uvezli kompletnu tablicu i niste poništili odabir nijednog stupca ni primijenili filtar, upit će biti prikazan kao “Select * from ”, što će biti teže izmijeniti:

sql upit koji koristi zadanu, kraću sintaksu

Izmjena SQL upita

Sad kad znate kako pronaći upit, možete ga izmijeniti da biste dodatno smanjili veličinu modela.

  1. Kod stupaca koji sadrže valutu ili decimalne podatke, ako ne trebate neke znamenke iza decimalnog zareza, uklonite ih pomoću ove sintakse:

    “SELECT ROUND([Decimal_column_name],0)… .”

    Ako trebate lipe, ali ne i dijelove lipa, zamijenite broj 0 brojem 2. Ako koristite negativne brojeve, možete zaokružiti na jedinice, desetice, stotice itd.

  2. Ako je naziv vašeg stupca s datumom i vremenom dbo.Bigtable.[Date Time] i ne trebate vremenski dio, uklonite ga pomoću ove sintakse:

    “SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “

  3. Ako je naziv vašeg stupca s datumom i vremenom dbo.Bigtable.[Date Time], a trebate datumski i vremenski dio, umjesto jednog stupca s datumom i vremenom u SQL upitu upotrijebite više stupaca:

    “SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    Upotrijebite koliko je god stupaca potrebno da biste svaki dio pohranili u zasebnom stupcu.

  4. Ako trebate sate i minute, a želite da budu navedeni zajedno u jednom stupcu s vremenom, upotrijebite sljedeću sintaksu:

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Ako imate dva stupca s datumom i vremenom, primjerice [Start Time] i [End Time], a zapravo trebate razliku između njih u sekundama prikazanu u stupcu naziva [Duration], s popisa uklonite oba stupca i dodajte sljedeće:

    “datediff(ss,[Start Date],[End Date]) as [Duration]”

    Ako umjesto ss upotrijebite ključnu riječ ms, dobit ćete trajanje u milisekundama.

Korištenje DAX izračunatih mjera umjesto stupaca

Ako ste već koristili jezik DAX izraza, možda već znate da se izračunati stupci koriste za izvođenje novih stupaca na temelju drugih stupaca u modelu, dok se izračunate mjere u modelu definiraju jednom, no računaju se samo prilikom njihova korištenja u zaokretnoj tablici ili nekom drugom izvješću.

Jedna od tehnika uštede memorijskog prostora je zamjena običnih ili izračunatih stupaca izračunatim mjerama. Tipičan su primjer stupci Jedinična cijena, Količina i Ukupno – ako imate sva tri, uštedjet ćete prostor ako zadržite samo dva, a treći računate pomoću DAX-a.

Koja dva stupca zadržati?

U gornjem primjeru zadržite Količinu i Jediničnu cijenu. U tim dvama stupcima manje je vrijednosti nego u stupcu Ukupno. Da biste izračunali Ukupno, dodajte izračunatu mjeru kao u sljedećem primjeru:

“TotalSales:=sumx(‘Sales Table’,’Sales Table’[Jedinična cijena]*’Sales Table’[Količina])”

Izračunati stupci nalik su običnim stupcima u tome što zauzimaju prostor u modelu. S druge strane, izračunate se mjere izračunavaju u hodu i ne zauzimaju prostor.

Zaključak

U ovom smo članku razmotrili nekoliko pristupa stvaranju modela koji su memorijski učinkovitiji. Moguće je smanjiti veličinu datoteke podatkovnog modela i veličinu potrebnih memorijskih resursa smanjenjem ukupnog broja stupaca i redaka te smanjenjem broja jedinstvenih vrijednosti u svakom stupcu. Evo nekih tehnika koje smo spomenuli:

  • Uklanjanje stupaca najbolji je način uštede memorijskih resursa. Odlučite koji su vam stupci doista potrebni.

  • Ponekad iz tablice možete ukloniti stupac i zamijeniti ga izračunatom mjerom.

  • Možda ne trebate sve retke u tablici. Možete ih filtrirati pomoću čarobnjaka za uvoz tablica.

  • Općenito je raščlamba jednog stupca na više različitih dijelova dobar način smanjenja broja jedinstvenih vrijednosti u stupcu. Svaki će dio sadržavati manje jedinstvenih vrijednosti, a kombinirani ukupni broj bit će manji nego u izvornom objedinjenom stupcu.

  • Često ćete trebati koristiti različite dijelove kao rezače u izvješćima. Kad to bude potrebno, možete iz dijelova poput sati, minuta i sekundi stvarati hijerarhije.

  • Stupci često sadrže puno više informacija nego što je potrebno. Pretpostavimo, na primjer, da su u stupcu pohranjeni decimalni brojevi, no primijenili ste oblikovanje kojim ste sakrili sve brojeve iza decimalnog zareza. Zaokruživanje može biti vrlo učinkovit način smanjenja veličine brojčanih stupaca.

Sad kad ste ih dodali što možete učiniti da biste smanjili veličinu radne knjige, razmislite o i pokrenuti alat za optimizaciju veličine radne knjige. Analizira radnoj knjizi programa Excel, a po mogućnosti sažima Dodatno. Preuzmite Alat za optimizaciju veličine radne knjige.

Srodne veze

Specifikacije i ograničenja podatkovnog modela

Preuzmite Alat za optimizaciju veličine radne knjige

Power Pivot: Napredna analiza i Modeliranje podataka u programu Excel

Napomena : Izjava o odricanju od odgovornosti za strojni prijevod: ovaj je članak preveo računalni sustav bez ljudske intervencije. Microsoft nudi strojne prijevode da bi korisnicima koji ne razumiju engleski omogućio čitanje sadržaja o Microsoftovim proizvodima, uslugama i tehnologijama. Budući da je preveden strojno, članak možda sadrži pogreške u vokabularu, sintaksi ili gramatici.

Proširite svoje vještine
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×