Kreiranje memorijski efikasnog modela podataka pomoću programa Excel i programski dodatak Power Pivot

Važno : Ovaj članak je mašinski preveden, pogledajte odricanje odgovornosti. Pogledajte verziju ovog članka na engleskom jeziku ovde za referencu.

U programu Excel 2013 ili novijoj verziji, možete da kreirate modele podataka koja sadrži milione redova, i zatim vršili moćnu analizu podataka od ovih modela. Modeli podataka mogu biti kreirani sa ili bez Power Pivot programskog dodatka za podršku bilo koji broj izvedenim tabelama, grafikonima i Power View vizuelizacija u istoj radnoj svesci.

Napomena : Ovaj članak opisuje modela podataka u programu Excel 2013. Međutim, istu modeliranje podataka i Power Pivot funkcije uvedene u programu Excel 2013 takođe primeniti na Excel 2016. Postoji efikasno mala razlika između ove verzije programa Excel.

Iako možete lako napravi modeli veliki podataka u programu Excel, postoji nekoliko razloga ne do. Prvo, velikih modela koji sadrže mnogi tabela i kolona znala za većinu analize i za naporan liste polja. Drugo, veliki modeli koriste vredna memoriju, negativno uticaja na druge aplikacije i izveštaje koji dele istu sistemske resurse. Na kraju, u sistemu Office 365, SharePoint Online i programu Excel Web App da ograničite veličinu Excel datoteke da biste 10 MB. Za modele podataka radne sveske koje sadrže milione redova, naići ćete na ograničenje od 10 MB brzo. U članku ograničenja i specifikacije modela podataka.

U ovom članku ćete saznati kako da napravite dobro konstruisan model sa kojim se lakše radi i koji koristi manje memorije. Vreme koje ste uložili u izučavanje najboljih praksi za efikasno dizajniranje modela isplatiće se kasnije za svaki model koji kreirate i koristite, bilo da ga prikazujete u programu Excel 2013, sistemima Office 365 SharePoint Online, Office Web Apps Server ili SharePoint 2013.

Razmislite o pokrenut i optimizator veličine radne sveske. To analizira Excel radnu svesku i ako je moguće, komprimuje ga dodatno. Preuzimanje optimizatora veličine radne sveske.

U ovom članku

Koeficijenti kompresije i unutrašnji memorijski analitiku mašina

Nema ničeg boljeg od nepostojeće kolone za manju upotrebu memorije

Dva primera kolona koje bi uvek trebalo izuzeti

Kako isključiti nepotrebne kolone

Šta je sa filtriranjem samo potrebnih redova?

Šta ako nam je kolona; potrebna možete i dalje da smanjite uštedeli prostor?

Izmena kolona sa datumom i vremenom

Izmena SQL upita

Korišćenje DAX izračunatih mera umesto kolona

Koje 2 kolone bi trebalo zadržati?

Zaključak

Srodne veze

Koeficijenti kompresije i unutrašnji memorijski mehanizam za analitiku

Modeli podataka u programu Excel koriste unutrašnji memorijski mehanizam za analitiku za skladištenje podataka u memoriju. Mašina primenjuje moćne tehnike kompresovanja radi smanjenja zahteva skladištenja, smanjujući skup rezultata dok ne postane samo deo svoje originalne veličine.

U proseku možete očekivati da model podataka bude 7 do 10 puta manji od istih podataka u prvobitnom okruženju. Na primer, ako uvozite 7 MB podataka iz SQL Server baze podataka, model podataka u programu Excel bi sa lakoćom mogao biti 1 MB ili manji. Nivo zapravo dostignute kompresije prvenstveno zavisi od broja jedinstvenih vrednosti u svakoj koloni. Što su vrednosti više jedinstvene, više memorije je potrebno za njihovo skladištenje.

Zašto pričamo o kompresiji i jedinstvenim vrednostima? Zbog toga što efikasan model podataka koji smanjuje upotrebu memorije koristi povećanje kompresije, a najlakši način za postizanje toga jeste rešiti se svih kolona koje vam zapravo nisu potrebne, naročito ako te kolone obuhvataju puno jedinstvenih vrednosti.

Napomena : Razlika u zahtevima skladištenja za pojedinačne kolone može biti ogromna. U nekim slučajevima bolje je imati više kolona sa malo jedinstvenih vrednosti nego jednu kolonu sa puno jedinstvenih vrednosti. Odeljak o optimizaciji datuma i vremena detaljno pokriva tu tehniku.

Za manju upotrebu memorije nema ničeg boljeg od nepostojeće kolone

Memorijski najefikasnija kolona je ona koju niste ni uvezli. Ako želite da izgradite efikasan model, pogledajte svaku kolonu i zapitajte se da li ona doprinosi analizi koju želite da izvršite. Ako ne doprinosi ili niste sigurni da li doprinosi, izostavite je. Uvek možete dodati nove kolone kasnije ako vam budu potrebne.

Dva primera kolona koje bi uvek trebalo izuzeti

Prvi primer je povezan sa podacima koji potiču iz skladišta podataka. U skladištu podataka uobičajeno je pronalaženje objekata ETL procesa koji učitavaju i osvežavaju podatke u skladištu. Kolone kao „datum kreiranja“, „datum ažuriranja“ i „ETL pokretanje“ se kreiraju kada se podaci učitavaju. Nijedna od tih kolona nije potrebna u modelu i potrebno je da njihov izbor bude opozvan tokom uvoza podataka.

Drugi primer obuhvata izostavljanje kolone primarnog ključa tokom uvoza tabele sa činjenicama.

Mnoge tabele, uključujući i tabele sa činjenicama, imaju primarne ključeve. Za većinu tabela, kao što su one koje sadrže podatke o klijentima, zaposlenima ili prodaji, želećete primarni ključ tabele tako da ga možete iskoristiti za kreiranje odnosa u modelu.

Tabele sa činjenicama su drugačije. U tabeli sa činjenicama primarni ključ se koristi za jedinstvenu identifikaciju svakog reda. Iako je neophodan za normalizaciju, manje je koristan u modelu podataka gde želite samo one kolone koje se koriste za analizu ili za uspostavljanje odnosa tabele. Zbog toga, tokom uvoza iz tabele sa činjenicama, nemojte uključiti njen primarni ključ. Primarni ključ u tabeli sa činjenicama koristi ogromnu količinu prostora u modelu, a ne pruža neku korist, jer se ne može koristiti za kreiranje odnosa.

Napomena : U skladištima podataka i višedimenzionalnim bazama podataka, velike tabele koje se sastoje najvećim delom od numeričkih podataka se najčešće nazivaju „tabele sa činjenicama“. Tabele sa činjenicama najčešće obuhvataju poslovne performanse ili podatke o transakciji, kao što su tačke podataka prodaje i troškova koje se agregiraju i poravnavaju u organizacione jedinice, proizvode, segmente tržišta, geografske oblasti, itd. Sve kolone u tabeli sa činjenicama koja sadrži poslovne podatke ili koja se može koristiti za unakrsno upućivanje na podatke uskladištene u drugim tabelama bi trebalo da budu uključene u model radi podržavanja analize podataka. Kolona koju želite da isključite je kolona primarnog ključa tabele sa činjenicama, koja se sastoji od jedinstvenih vrednosti koje postoje samo u tabeli sa činjenicama i nigde više. Pošto su tabele sa činjenicama ogromne, neke od najvećih dobrobiti u efikasnosti modela se dobijaju isključivanjem redova ili kolona iz tabela sa činjenicama.

Kako isključiti nepotrebne kolone

Efikasnog modela sadrže samo one kolone koje stvarno treba u radnoj svesci. Ako želite da kontrolišete kolone koje su uključene u modelu, moraćete da koristite čarobnjak uvoz tabele u programskom dodatku Power Pivot programski dodatak da biste uvezli podatke , a ne u dijalogu "Uvoz podataka" u programu Excel.

Kad pokrenete čarobnjak za uvoz tabele, izabraćete koje tabele se uvoze.

Čarobnjak za uvoz tabele u programskom dodatku

Za svaku tabelu možete da kliknete na dugme „Pregled i filtriranje“ i izaberete delove tabele koji su vam zaista potrebni. Preporučujemo da najpre opozovete izbor svih kolona i zatim potvrdite izbor za željene kolone, nakon razmatranja da li su one potrebne za analizu.

Okno za pregled u čarobnjaku za uvoz tabele

Šta je sa filtriranjem samo potrebnih redova?

Mnoge tabele u bazama podataka i skladištima podataka preduzeća sadrže istorijske podatke akumulirane tokom dugih vremenskih perioda. Pored toga, možda shvatite da tabele koje vas zanimaju sadrže informacije za poslovne oblasti koje nisu potrebne za određenu analizu.

Uz pomoć čarobnjaka za uvoz tabele možete filtrirati istorijske ili nepovezane podatke i tako sačuvati mnogo prostora u modelu. Na sledećoj slici, filter datuma se koristi za preuzimanje samo onih redova koji sadrže podatke za trenutnu godinu, isključujući nepotrebne istorijske podatke.

Okno za filtriranje u čarobnjaku za uvoz tabele

Šta ako nam je kolona potrebna, možemo li i dalje da preduzmemo nešto kako bismo uštedeli prostor?

Postoji nekoliko dodatnih tehnika koje možete primeniti da bi kolona postala bolji kandidat za kompresiju. Zapamtite da je jedino obeležje kolone koje utiče na kompresiju broj jedinstvenih vrednosti. U ovom odeljku ćete saznati kako da izmenite neke kolone radi smanjivanja broja jedinstvenih vrednosti.

Izmena kolona sa datumom i vremenom

U mnogim slučajevima kolone sa datumom i vremenom zauzimaju mnogo prostora. Srećom, postoji mnogo načina da se smanje zahtevi skladištenja za taj tip podataka. Tehnike će se razlikovati u zavisnosti od toga kako koristite kolonu i od vašeg nivoa ugodnosti pri građenju SQL upita.

Kolone sa datumom i vremenom obuhvataju deo sa datumom i vreme. Kada se pitate da li vam je neka kolona potrebna, još bolje razmislite kad je u pitanju kolona sa datumom i vremenom:

  • Da li je potreban deo sa vremenom?

  • Da li je potreban deo sa vremenom na nivou sati, minuta, sekundi ili milisekundi?

  • Da li imam više kolona sa datumom i vremenom jer želim da izračunam razliku između njih ili samo radi agregacije podataka po godini, mesecu, kvartalu, itd.

Odgovori na ova pitanja utvrđuju opcije za rad sa kolonom sa datumom i vremenom.

Sva ova rešenja zahtevaju izmenu SQL upita. Da biste olakšali izmenu upita, trebalo bi da filtrirate bar jednu kolonu u svakoj tabeli. Filtriranjem kolone menjate konstrukciju upita od skraćenog formata (SELECT *) do SELECT naredbe koja obuhvata potpuno kvalifikovana imena kolona, koja su znatno lakša za izmenu.

Hajde da pogledamo upite koje smo kreirali za vas. Sa dijaloga „Svojstva tabele“ možete da se prebacite na uređivač upita i vidite trenutni SQL upit za svaku tabelu.

Traka na PowerPivot prozoru koja prikazuje komandu „Svojstva tabele“

U dijalogu „Svojstva tabele“ izaberite stavku Uređivač upita.

Otvaranje uređivača upita iz dijaloga „Svojstva tabele“

Uređivač upita pokazuje SQL upit koji je korišćen za popunjavanje tabele. Ako ste filtrirali kolone tokom uvoza, upit će obuhvatati potpuno kvalifikovana imena kolona:

SQL upit korišćen za preuzimanje podatka

U suprotnom, ako ste u celini uvezli tabelu, bez opozivanja izbora kolona ili bez primene filtera, videćete upit kao „Select * from “, koji se teže menja:

SQL upit koristeći podrazumevanu, kraću sintaksu

Izmena SQL upita

Sad kad znate kako da pronađete upit, možete ga još izmeniti da biste još više smanjili veličinu modela.

  1. Za kolone koje sadrže decimalne ili podatke o valutama, ako vam decimale nisu potrebne, koristite ovu sintaksu da biste se oslobodili decimalnih mesta:

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

    Ako su vam centi potrebni, ali ne i delovi centa, zamenite 0 sa 2. Ako koristite negativne brojeve, možete da zaokružite na jedinice, desetine, stotine, itd.

  2. Ako imate kolonu sa datumom i vremenom pod imenom dbo.Velikatabela.[Datum i vreme], a nije vam potreban deo sa vremenom, koristite ovu sintaksu a biste se oslobodili vremena:

    “SELECT CAST (dbo.Velikatabela.[Datum i vreme] as date) AS [Date time]) ”

  3. Ako imate kolonu sa datumom i vremenom pod menom dbo.Velikatabela.[Datum i vreme], a potrebni su vam i deo sa datumom i deo sa vremenom, koristite više kolona u SQL upitu umesto jedne kolone sa datumom i vremenom:

    “SELECT CAST (dbo.Velikatabela.[Datum i vreme] as date ) AS [Date Time],

    datepart(hh, dbo.Velikatabela.[Datum i vreme]) as [Date Time Hours],

    datepart(mi, dbo.Velikatabela.[Datum i vreme]) as [Date Time Minutes],

    datepart(ss, dbo.Velikatabela.[Datum i vreme]) as [Date Time Seconds],

    datepart(ms, dbo.Velikatabela.[Datum i vreme]) as [Date Time Milliseconds]”

    Koristite koliko god kolona je potrebno za skladištenje svakog dela u odvojene kolone.

  4. Ako su vam potrebni sati i minuti i želeli biste da budu zajedno u jednoj vremenskoj koloni, možete koristiti ovu sintaksu:

    Timefromparts(datepart(hh, dbo.Velikatabela.[Datum i vreme]), datepart(mm, dbo.Velikatabela.[Datum i vreme])) as [Date Time HourMinute]

  5. Ako imate dve kolone sa datumom i vremenom, na primer [Vreme početka] i [Vreme završetka], a ono što vam je zaista potrebno jeste vremenska razlika između njih u sekundama kao kolona pod imenom [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 izražavanja, 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, a procenjuju se samo kada se koriste u izvedenoj tabeli ili drugom izveštaju.

Jedna tehnika koja štedi memoriju je zamena običnih ili izračunatih kolona sa izračunatim merama. Klasični primeri su kolone „Cena jedinice“, „Količina“ i „Ukupno“. Ako imate sve tri kolone, prostor možete uštedeti održavanjem dve kolone i izračunavanjem treće uz pomoć jezika DAX.

Koje 2 kolone bi trebalo zadržati?

U navedenom primeru zadržite kolone „Količina“ i „Cena jedinice“. Te dve imaju manji broj vrednosti od kolone „Ukupno“. Da biste izračunali kolonu „Ukupno“, dodajte izračunatu meru kao što je:

“TotalSales:=sumx(‘Tabela prodaje’,’Tabela prodaje’[Cena jedinice]*’Tabela prodaje’[Količina])”

Izračunata kolona je slična običnoj po tome što zauzima prostor u modelu. Sa druge strane, izračunate mere računaju u pokretu i ne zauzimaju prostor.

Zaključak

U ovom članku, pričali smo o nekoliko pristupa koji vam mogu pomoći da izgradite model koji je memorijski više efikasan. Način za smanjivanje veličine datoteke i zahteva memorije modela podataka jeste smanjiti ukupni broj kolona i redova i broj jedinstvenih vrednosti koje se pojavljuju u svakoj koloni. Ovo su neke tehnike koje smo pokrili:

  • Uklanjanje kolona je naravno najbolji način za uštedu prostora. Odlučite koje su vam kolone zaista potrebne.

  • Ponekad možete ukloniti kolonu i zameniti je sa izračunatom merom u tabeli.

  • Možda vam ne budu potrebni svi redovi u tabeli. Redove možete filtrirati u čarobnjaku za uvoz tabele.

  • U suštini, razdvajanje jedne kolone na više različitih delova je dobar način za smanjivanje broja jedinstvenih vrednosti u koloni. Svaki deo će imati mali broj jedinstvenih vrednosti, a kombinovana ukupna vrednost će biti manja od originalne objedinjene kolone.

  • U mnogim slučajevima biće vam potrebini i različiti delovi koji se koriste kao moduli za sečenje u izveštajima. Ako bude pogodno, možete kreirati hijerarhije od delova kao što su sati, minuti i sekunde.

  • Često kolone sadrže više informacija nego što je potrebno. Na primer, pretpostavimo da kolona skladišti decimalna mesta, a vi ste primenili oblikovanje za skrivanje svih decimalnih mesta. Zaokruživanje može biti veoma efikasno u smanjivanju veličine numeričke kolone.

Sada kada ste uradili možete da smanjite veličinu radne sveske, razmotrite i pokretanje optimizator veličine radne sveske. To analizira Excel radnu svesku i ako je moguće, komprimuje ga dodatno. Preuzimanje optimizatora veličine radne sveske.

Srodne veze

Specifikacija i ograničenja modela podataka

Preuzimanje Optimizatora veličine radne sveske

Power Pivot: Moćna analiza podataka i modelovanje podataka u programu Excel

Napomena : Odricanje odgovornosti za mašinski prevod: Ovaj članak je preveo računarski sistem bez ljudske intervencije. Microsoft nudi ove mašinske prevode da bi pomogao korisnicima koji ne govore engleski da uživaju u sadržaju o Microsoft proizvodima, uslugama i tehnologijama. Budući da je članak mašinski preveden, može da sadrži greške u rečniku, sintaksi ili gramatici.

Unapredite veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Office Insider korisnicima

Da li su vam ove informacije koristile?

Hvala vam na povratnim informacijama!

Hvala za povratne informacije! Zvuči da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×