Kreiranje memorijskog efikasnog modela podataka pomoću programa Excel i programskog dodatka Power Pivot

U programu Excel 2013 ili novijim verzijama možete da kreirate modele podataka koji sadrže milione redova, a zatim da uradite moćnu analizu podataka sa ovim modelima. Modeli podataka mogu da se kreiraju sa ili bez Power Pivot programskog dodatka da bi podržalo bilo koji broj izvedene tabele, grafikona i Power View vizuelizacije u istoj radnoj svesci.

Napomena: Ovaj članak opisuje modele podataka u programu Excel 2013. Međutim, isto modeliranje podataka i Power Pivot funkcije predstavljeni u programu Excel 2013 važe i za Excel 2016. Postoji vrlo malo razlika između tih verzija programa Excel.

Iako možete lako da kreirate ogromne modele podataka u programu Excel, postoji nekoliko razloga koji to ne treba da uradite. Prvo, veliki modeli koji sadrže mnoštvo tabela i kolona imaju prekomerno ubistvo za većinu analiza i pravi listu za glomeze. Drugo, veliki modeli koriste vrednu memoriju, negativno utiču na druge aplikacije i izveštaje koji dele iste sistemske resurse. Na kraju, u Office 365, SharePoint Online i Excel Web App ograničava veličinu Excel datoteke na 10 MB. Za modele podataka radne sveske koji sadrže milione redova, brzo ćete naleteti na ograničenje od 10 MB. Pogledajte specifikacije i ograničenja modela podataka.

U ovom članku ćete saznati kako da napravite čvrsto napravljen model sa kojim je lakše raditi i koristi manje memorije. Korišćenje vremena za učenje najboljih praksi u efikasnom modelu će otplatiti put za bilo koji model koji kreirate i koristite, bilo da ga pregledate u programu Excel 2013, Office 365 SharePoint online, na Office Web Apps server ili u sistemu SharePoint 2013.

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

Koeficijenti kompresije i mašina za analitiku u memoriji

Ništa ne pobeрuje na nepostojećoj koloni za upotrebu niske memorije

Dva primera kolona koje uvek treba da se isključene

Kako isključiti nepotrebne kolone

Šta je sa filtriranjem samo potrebnih redova?

Šta ako nam je potrebna kolona; Da li i dalje možemo da smanjimo troškove prostora?

Izmena kolona sa vremenskim Etime

Izmena SQL upita

Korišćenje DAX izračunatih mera umesto kolona

Koje dve kolone treba da zadržite?

Zaključak

Srodne veze

Koeficijenti kompresije i mašina za analitiku u memoriji

Modeli podataka u programu Excel koriste mašinu za analizu memorije za skladištenje podataka u memoriji. Mašina sprovodi moćne tehnike komprimovanja da bi se smanjili zahtevi za skladištenje, skupilo skupa rezultat dok ne nastane njena originalna veličina.

U proseku, možete očekivati da će model podataka biti 7 do 10 puta manji od istih podataka na njegovom mestu. 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 zapravo je ostvaren prvenstveno zavisi od broja jedinstvenih vrednosti u svakoj koloni. Više jedinstvenih vrednosti, potrebno je više memorije da bi se skladištiti.

Zašto pričamo o komprimovanju i jedinstvenim vrednostima? Budući da je izgradnja efikasnog modela koji smanjuje potrošnju memorije sva u vezi sa maksimalnom komprimovanjem, a najlakši način da to uradite jeste da se otarasite bilo koje kolone koje vam zaista nisu potrebne, naročito ako te kolone uključujući veliki broj jedinstvenih vrednosti.

Napomena:  Razlike u zahtevima skladištenja pojedinačnih kolona mogu biti ogromne. U nekoliko slučajeva bolje je imati više kolona sa niskim brojem jedinstvenih vrednosti, a ne jednom kolonama sa velikim brojem jedinstvenih vrednosti. Odeljak "vremenske optimizacije podataka" se pokriva.

Ništa ne pobeрuje na nepostojećoj koloni za upotrebu niske memorije

Najveći stubičasti okvir sa memorijom – efikasnom je taj koji se nikada nije uvozi. Ako želite da napravite efikasan model, pogledajte svaku kolonu i zapitajte se da li doprinosi analizi koju želite da uradite. Ako nije ili niste sigurni, ostavite je. Možete uvek da dodate nove kolone kasnije ako su vam potrebne.

Dva primera kolona koje uvek treba da se isključene

Prvi primer se odnosi na podatke koji potiču iz skladišta podataka. U skladištu podataka uobičajeno je pronaći artifakte ETL procesa koji učitavaju i osvežavanje podataka u magacinu. Kolone kao što su "Kreiranje datuma", "Ažuriranje datuma" i "dugme IT Run" kreiraju se kada se podaci učitaju. Nijedna od ovih kolona nije potrebna u modelu i treba da bude deizabrana prilikom uvoza podataka.

Drugi primer uključuje opozivanje kolone primarnog ključa prilikom uvoza činjenice.

Mnoge tabele, uključujući tabele sa činjenicama, imaju primarne ključeve. Za većinu tabela, kao što su podaci koji sadrže kupce, zaposlene ili podatke o prodaji, želite primarni ključ tabele tako da možete da ga koristite da biste kreirali relacije u modelu.

Tabele "činjenica" se razlikuju. U tabeli činjenica, primarni ključ se koristi za jedinstveno identifikovanje svakog od njih. Iako je to neophodno za normalizaciju, to je manje korisno u modelu podataka gde želite samo one kolone koje se koriste za analizu ili da uspostave relacije između tabela. Iz tog razloga, kada uvozite iz tabele činjenica, ne uključujete primarni ključ. Primarni ključevi u tabeli činjenica konzumiraju ogromne količine prostora u modelu, ali ne pružaju korist, pošto se ne mogu koristiti za kreiranje relacija.

Napomena:  U skladištima podataka i višedimenzionalnim bazama podataka, velike tabele koje se čine iz najčešće numeričkih podataka se često nazivaju "činjenicama". Tabele "činjenice" obično uključuju poslovne performanse ili podatke o transakcijama, kao što su tačke prodaje i troška koje su zbirne i poravnate sa organizacionim jedinicama, proizvodima, segmentima za tržište, geografskim regionima, itd. Sve kolone u tabeli sa činjenicama koje sadrže poslovne podatke ili koje se mogu koristiti za unakrsno referenca podataka uskladištenih u drugim tabelama trebalo bi da budu uključene u model za podršku analizi podataka. Kolona koju želite da isključite je kolona primarnog ključa tabele činjenica, koja se sastoji od jedinstvenih vrednosti koje postoje samo u tabeli činjenica i nigde drugo. Budući da su tabele sa cinjenicama tako ogromne, neke od najvećih dobitaka u modelu modela potiču iz tabele "Redovi" ili "kolone".

Kako isključiti nepotrebne kolone

Efikasni modeli sadrže samo one kolone koje su vam potrebne u radnoj svesci. Ako želite da kontrolišete koje kolone su uključene u model, moraćete da koristite čarobnjak za uvoz tabele u programskom dodatku Power Pivot da biste uvezli podatke umesto dijaloga "Uvoz podataka" u programu Excel.

Kada pokrenete čarobnjak za uvoz tabele, izaberite koje tabele da uvezete.

Čarobnjak za uvoz tabele u programskom dodatku

Za svaku tabelu možete da kliknete na dugme "Pregled & filtera" i izaberete delove tabele koje vam zaista trebaju. Preporučujemo da prvo opozovite izbor u svim kolonama, a zatim nastavite da biste pronašli kolone koje želite, kada se razmotri da li su neophodne za analizu.

Okno za pregled u čarobnjaku za uvoz tabele

Šta je sa filtriranjem samo potrebnih redova?

Mnoge tabele u korporativnim bazama podataka i magacinima podataka sadrže istorijske podatke akumulirane tokom dugih perioda vremena. Pored toga, možda ćete otkriti da tabele koje ste zainteresovani za sadržaće informacije za oblasti preduzeća koja nisu potrebna za određenu analizu.

Pomoću čarobnjaka za uvoz tabele možete da filtrirate istorijske ili nepovezane podatke i tako uštedite puno prostora u modelu. Na sledećoj slici, filter za datum se koristi za preuzimanje samo redova koji sadrže podatke za trenutnu godinu, izuzimajući istorijske podatke koji nisu potrebni.

Okno za filtriranje u čarobnjaku za uvoz tabele

Šta ako nam je potrebna kolona; Da li i dalje možemo da smanjimo troškove prostora?

Postoji još nekoliko tehnika koje možete da uradite da biste kolonu napravili boljim kandidatom za komprimovanje. Imajte u vidu da je jedina karakteristika kolone koja utiče na komprimovanje broj jedinstvenih vrednosti. U ovom odeljku ćete saznati kako se neke kolone mogu izmeniti da bi se smanjio broj jedinstvenih vrednosti.

Izmena kolona sa vremenskim Etime

U mnogim slučajevima kolone DATETIME imaju puno prostora. Srećom, postoji nekoliko načina da se smanje zahtevi za skladištenje za ovaj tip podataka. Tehnika će se razlikovati u zavisnosti od toga kako koristite kolonu i vaš nivo udobnosti u pravljenju SQL upita.

Kolone DATETIME uključujući datum i vreme. Kada se zapitate da li vam je potrebna kolona, više puta postavite isto pitanje za kolonu DATETIME:

  • Da li mi je potreban deo za vreme?

  • Da li mi je potreban deo za vreme na nivou časova? minuta? Drugi? milisekundama?

  • Da li imam više kolona Datevremena zato što želim da uraиunam razliku između njih ili samo da prikuplja podatke po godišnjem nivou, mesecu, kvartalu itd.

Kako da odgovorite na svako od ovih pitanja, određuje opcije za rešavanje problema sa kolonom DATETIME.

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 možete da promenite izgradnju upita iz skraćenog formata (izaberite *) u naredbu SELECT koja sadrži potpuno odgovarajuća imena kolona, koja se znatno lakše menjaju.

Pogledajte 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.

Traka na PowerPivot prozoru koja prikazuje komandu „Svojstva tabele“

Iz svojstava tabele izaberite stavku Uređivač upita.

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

Uređivač upita prikazuje SQL upit koji se koristi za popunjavanje tabele. Ako ste filtrirali kolonu tokom uvoza, upit sadrži potpuno odgovarajuća imena kolona:

SQL upit korišćen za preuzimanje podatka

Nasuprot tome, ako ste uvezli tabelu u celini, bez poništavanja kolone ili primene filtera, videćete upit kao "izbor * od", što će biti teže izmeniti:

SQL upit koristeći podrazumevanu, kraću sintaksu

Izmena SQL upita

Sada kada znate kako da pronađete upit, možete da ga izmenite kako biste dodatno smanjili veličinu modela.

  1. Za kolone koje sadrže valutu ili decimalne podatke, ako vam nisu potrebni decimalne cifre, koristite ovu sintaksu da biste se otarasili decimala:

    "Izbor ROUND ([Decimal_column_name], 0)... .”

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

  2. Ako imate kolonu DATETIME koja se zove DBO. Velikatabela. [Vreme datuma] i nije vam potreban deo za vreme, koristite sintaksu da biste se otarasili vremena:

    "Izaberite stavku CAST (DBO. Velikatabela. [Vreme datuma] kao datum) kao [Datum vremena]) "

  3. Ako imate kolonu DATETIME koja se zove DBO. Velikatabela. [Vreme datuma] i potrebni su vam i delovi datuma i vremena, koristite više kolona u SQL upitu umesto jedne kolone DATETIME:

    "Izaberite stavku CAST (DBO. Velikatabela. [Vreme datuma] kao datum) kao [vreme datuma],

    DatePart (hh, DBO. Velikatabela. [Vreme datuma]) kao [vreme datuma],

    DatePart (mi, DBO. Velikatabela. [Vreme datuma]) kao [vreme vremena za datum],

    DatePart (SS, DBO. Velikatabela. [Vreme datuma]) kao [vreme vremena datuma],

    DatePart (MS, DBO. Velikatabela. [Vreme datuma]) kao [Datum vreme milisekundi] "

    Koristite koliko god kolona treba da uskladištite u zasebne kolone.

  4. Ako su vam potrebni časovi i minuti i preferirate da budu u jednoj koloni sa vremenskim kolonama, možete da koristite sintaksu:

    Timefromparts (hh, DBO. Velikatabela. [Vreme datuma]), DatePart (mm, DBO. Velikatabela. [Vreme datuma])) As [Datum vremena

  5. Ako imate dva stupca podataka, kao što je [vreme početka] i [vreme završetka], a ono što vam zaista treba je 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]) kao [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 na DAX jeziku za DAX Expression, možda već znate da se izračunate kolone koriste za korišćenje novih kolona na osnovu neke druge kolone u modelu, dok se izračunate mere definišu jednom u modelu, ali se proveravaju samo kada se koriste u Izvedena tabela ili drugi izveštaj.

Jedna tehnika čuvanja sećanja je da zamenite redovne ili izračunate kolone pomoću izračunatih mera. Klasični primer je Jedinična cena, količina i ukupno. Ako imate sva tri, možete da sačuvate prostor tako što ćete održati samo dva i izračunavate treći pomoću funkcije DAX.

Koje dve kolone treba da zadržite?

U gorenavedenom primeru zadržite količinu i jediničnu cenu. Ova dvojica imaju manje vrednosti od ukupnog zbira. Da biste izračunali zbir, dodajte izračunatu meru kao što je:

"TotalSales: = sumx (' tabela prodaje ', ' tabela prodaje ' [Jedinična cena] * ' tabela prodaje ' [Količina])"

Izračunate kolone su kao obične kolone u modelu. Nasuprot tome, izračunate mere se izračunavaju na letu i ne uzimaju prostor.

Zaključak

U ovom članku smo pričali o nekoliko pristupa koji vam mogu pomoći da napravite više memorijski model. Način da smanjite veličinu datoteke i zahteve za memorijski model podataka jeste da smanjite ukupan broj kolona i redova, kao i broj jedinstvenih vrednosti koji se pojavljuju u svakoj koloni. Evo nekih tehnika koje smo pokrili:

  • Uklanjanje kolona je naravno najbolji način da sačuvate prostor. Odlučite koje kolone vam zaista trebaju.

  • Ponekad možete da uklonite kolonu i da je zamenite izračunatom merama u tabeli.

  • Možda vam neće biti potrebni svi redovi u tabeli. Redove možete da filtrirate u čarobnjaku za uvoz tabele.

  • Generalno, rastavljenje jedne kolone na više odvojenih delova je dobar način za smanjenje broja jedinstvenih vrednosti u koloni. Svaki od njih imaće mali broj jedinstvenih vrednosti, a kombinovani zbir će biti manji od originalne jedinstvene kolone.

  • U mnogim slučajevima, potrebni su vam i različiti delovi koje možete da koristite u izveštajima. Kada je to prikladno, možete da kreirate hijerarhije iz delova kao što su časovi, minuti i sekunde.

  • Mnogo puta, kolone sadrže više informacija nego što su vam potrebne. Na primer, pretpostavimo da se kolona skladišti decimalnih brojeva, ali ste primenili oblikovanje da biste sakrili sve decimale. Zaokruživanje može biti veoma efektno u smanjenju veličine numeričke kolone.

Sada kada ste učinili ono što možete da biste smanjili veličinu radne sveske, razmotrite i pokretanje optimizator 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

Razvijte Office 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! Izgleda da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×