Stvaranje memorijskog i učinkovitog podatkovnog modela pomoću programa Excel i dodatka Power pivot

U programu Excel 2013 ili novijim verzijama možete stvarati podatkovne modele koji sadrže milijune redaka, a zatim izvršiti snažnu analizu podataka u odnosu na ove modele. Podatkovni modeli mogu se stvarati pomoću dodatka Power Pivot ili bez njega da bi podržao bilo koji broj zaokretnih tablica, grafikona i vizualizacije značajke Power View u istoj radnoj knjizi.

Napomena: U ovom se članku opisuju podaci modeli u programu Excel 2013. Međutim, iste značajke modeliranja podataka i značajki dodatka Power pivot koje su uvedene u programu Excel 2013 primjenjuju se i na Excel 2016. Postoji učinkovita mala razlika između ovih verzija programa Excel.

Iako možete jednostavno stvoriti ogromne podatkovne modele u programu Excel, postoji nekoliko razloga zašto ne. Prvi, veliki modeli koji sadrže mnoštva tablica i stupaca nadjačavaju se za većinu analiza i čine ga velikim popisom polja. Drugi, veliki modeli koriste vrijednu memoriju, negativno utječu na druge aplikacije i izvješća koja dijele iste sistemske resurse. Konačno, u Microsoft 365, i SharePoint online i Excel Web App ograničavaju veličinu datoteke programa Excel na 10 MB. Za modele podataka radne knjige koji sadrže milijune redaka, vrlo brzo ćete pokrenuti ograničenje od 10 MB. Pogledajte specifikacije i ograničenja podatkovnog modela.

U ovom ćete članku naučiti graditi usko konstruirani model s kojim je jednostavnije raditi i koristi manje memorije. Uzimanje vremena da biste naučili najbolje prakse u učinkovitom modelu dizajna isplatiti će se niz cestu za bilo koji model koji kreirate i koristite, bez obzira na to gledate li ga u programu Excel 2013, Microsoft 365 SharePoint Online, na Office Web Apps poslužitelju ili u sustavu SharePoint 2013.

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

Omjeri kompresije i modul u memoriji analitičkih podataka

Ništa ne pobjeđuje nepostojeći stupac za nisku upotrebu memorije

Dva primjera stupaca koji se uvijek moraju isključiti

Isključivanje nepotrebnih stupaca

Što je s filtriranjem samo potrebnih redaka?

Što ako nam je potreban stupac; Možemo li još uvijek smanjiti cijenu prostora?

Promjena stupaca datuma

Izmjena SQL upita

Pomoću DAX izračunatih mjera umjesto stupaca

Koje 2 stupce trebate zadržati?

Zaključak

Srodne veze

Omjeri kompresije i modul u memoriji analitičkih podataka

Podatkovni modeli u programu Excel koriste motor analitičkih podataka u memoriji za pohranu podataka u memoriji. Motor se primjenjuje na snažne tehnike kompresije da bi se smanjile preduvjete za pohranu, čime se smanjuje skup rezultata dok ne bude djelić izvorne veličine.

U prosjeku možete očekivati da će podatkovni model biti 7 do 10 puta manji od istih podataka na njegovoj točki nastanka. Primjerice, ako uvozite 7 MB podataka iz baze podataka sustava SQL Server, podatkovni model u programu Excel mogao bi jednostavno biti 1 MB ili manje. Stupanj kompresije koji je zapravo postignut ovisi prvenstveno o broju jedinstvenih vrijednosti u svakom stupcu. Što je više jedinstvenih vrijednosti, potrebno je više memorije da biste ih pohranili.

Zašto govorimo o kompresiji i jedinstvenim vrijednostima? Budući da je stvaranje učinkovitog modela koji minimizira upotrebu memorije sve o maksimalizaciji kompresije, a to je najjednostavniji način da se riješite stupaca koji vam stvarno nisu potrebni, pogotovo ako ti stupci sadrže veliki broj jedinstvenih vrijednosti.

Napomena:  Razlike u preduvjetima za pohranu pojedinih stupaca mogu biti ogromne. U nekim slučajevima bolje je imati više stupaca s niskim brojem jedinstvenih vrijednosti, a ne jedan stupac s velikim brojem jedinstvenih vrijednosti. Odjeljak o optimizacijama Datumvremena obuhvaća ovu tehniku detaljno.

Ništa ne pobjeđuje nepostojeći stupac za nisku upotrebu memorije

Najučinkovitiji stupac memorije je onaj koji nikad niste uvezli na prvom mjestu. Ako želite stvoriti učinkovit model, pogledajte svaki stupac i zapitajte se pridonosi li analizi koju želite izvesti. Ako vam se ne sviđa ili niste sigurni, ostavite ga. Ako su vam potrebne, uvijek možete kasnije dodati nove stupce.

Dva primjera stupaca koji se uvijek moraju isključiti

Prvi se primjer odnosi na podatke koji potječu iz skladišta podataka. U skladištu podataka uobičajeno je pronalaženje artefakata ETL procesa koji učitava i osvježavaju podatke u skladištu. Kada se podaci učitavaju, stupci kao što su "stvaranje datuma", "Datum ažuriranja" i "ETL Run" stvaraju se. Ni jedan od ovih stupaca nije potreban u modelu i mora se poništiti odabir prilikom uvoza podataka.

Drugi primjer uključuje oostavljajući stupac primarnog ključa prilikom uvoza tablice činjenice.

Mnoge tablice, uključujući tablice činjenica, imaju primarne ključeve. Za većinu tablica, kao što su one koje sadrže podatke o kupcu, zaposlenicima ili prodaji, trebat će vam primarni ključ tablice da biste ga mogli koristiti za stvaranje odnosa u modelu.

Tablice činjenica su različita. U tablici činjenica primarni se ključ koristi za jedinstveno prepoznavanje svakog retka. Dok je potrebno za potrebe normalizacije, manje je korisno u podatkovnom modelu gdje želite samo one stupce koji se koriste za analizu ili za uspostavljanje odnosa između tablica. Iz tog razloga, prilikom uvoza iz tablice činjenica, nemojte uvrstiti primarni ključ. Primarni ključevi u tablici činjenica troše ogromne količine prostora u modelu, ali ne pružaju nikakve koristi jer se ne mogu koristiti za stvaranje odnosa.

Napomena:  U skladištima podataka i multidimenzionalnim bazama podataka velike tablice koje se sastoje od uglavnom numeričkih podataka često se nazivaju "tablice činjenice". Tablice činjenica obično obuhvaćaju poslovne performanse ili podatke o transakcijama, kao što su oznake podataka o prodaji i troškovima koje su objedinjene i usklađene s organizacijskim jedinicama, proizvodima, tržišnim segmentima, geografskim regijama i tako dalje. Svi stupci u tablici činjenica koji sadrže poslovne podatke ili koji se mogu koristiti za unakrsne reference podataka pohranjenih u drugim tablicama moraju biti uvršteni u model za podršku analizi podataka. Stupac koji želite isključiti predstavlja stupac primarnog ključa tablice Fact, koja se sastoji od jedinstvenih vrijednosti koje postoje samo u tablici činjenica i nigdje drugdje. Budući da su tablice činjenica tako ogromne, neke od najvećih dobitaka u modelu efikasnosti proizilaze iz isključivanja redaka ili stupaca iz tablica činjenice.

Isključivanje nepotrebnih stupaca

Učinkoviti modeli sadrže samo one stupce koje ćete zapravo trebati u radnoj knjizi. Ako želite kontrolirati koji su stupci 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, odaberite tablice koje želite uvesti.

Čarobnjak za uvoz tablica u dodatku PowerPivot

Za svaku tablicu možete kliknuti gumb Pretpregled & filtar i odabrati dijelove tablice koja vam je zaista potrebna. Preporučujemo da najprije poništite sve stupce, a zatim nastavite s provjerom stupaca koje želite, nakon što se uzme u obzir jesu li potrebne za analizu.

Okno pretpregleda u čarobnjaku za uvoz tablica

Što je s filtriranjem samo potrebnih redaka?

Mnoge tablice u bazama podataka i skladištima podataka sadrže povijesne podatke akumulirane u dugom vremenskom periodu. Uz to, možda ćete pronaći da tablice koje ste zainteresirani sadrže informacije za područja tvrtke koje nisu potrebne za vašu specifičnu analizu.

Pomoću čarobnjaka za uvoz tablica možete filtrirati povijesne ili nepovezane podatke, a time i uštedjeti puno prostora u modelu. Na sljedećoj slici filtar datuma koristi se za dohvaćanje samo redaka koji sadrže podatke za postojeću godinu, osim povijesnih podataka koji nisu potrebni.

Okno filtra u čarobnjaku za uvoz tablica

Što ako nam je potreban stupac; Možemo li još uvijek smanjiti cijenu prostora?

Postoji nekoliko dodatnih tehnika koje možete primijeniti tako da stupac bude bolji kandidat za kompresiju. Imajte na umu da je jedina karakteristika stupca koji utječe na kompresiju broj jedinstvenih vrijednosti. U ovom odjeljku saznat ćete kako se neki stupci mogu izmijeniti da bi se smanjio broj jedinstvenih vrijednosti.

Promjena stupaca datuma

U mnogim slučajevima stupci datuma uzimaju puno prostora. Na sreću, postoji nekoliko načina za smanjenje preduvjeta za pohranu za ovu vrstu podataka. Tehnike će varirati ovisiti o tome kako koristiti stupac te razinu udobnosti u gradnji SQL upita.

Stupci datumvremena obuhvaćaju dio datuma i vrijeme. Kada se zapitate treba li vam stupac, postavite isto pitanje više puta u stupcu Datumvremena:

  • Treba li mi vremenski dio?

  • Je li mi potreban vremenski dio na razini sati? minuta? Sekundi? milisekundama?

  • Imam li više stupaca datetime jer želim izračunati razliku između njih ili samo za zbrajanje podataka po godini, mjesecu, tromjesečju i tako dalje.

Kako odgovarate na sva ta pitanja, određuje se mogućnosti za rješavanje stupca datetime.

Za sva ova rješenja potrebna je izmjena SQL upita. Da biste jednostavnije izmijenili upit, trebali biste filtrirati najmanje jedan stupac u svakoj tablici. Filtriranjem stupca mijenjate konstrukciju upita iz skraćenog oblika (odaberite *) u naredbu SELECT koja sadrži potpuno kvalificirane nazive stupaca, koje su daleko jednostavnije za izmjenu.

Pogledajmo upite stvorene za vas. U dijaloškom okviru Svojstva tablice možete prijeći na uređivač upita i vidjeti sadašnji 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

Uređivač upita prikazuje SQL upit koji se koristi za popunjavanje tablice. Ako ste tijekom uvoza filtrirali bilo koji stupac, upit obuhvaća potpuno kvalificirane nazive stupaca:

SQL upit korišten za dohvaćanje podataka

Za razliku od toga, ako ste uvezli tablicu u cijelosti, bez poništavanja bilo kojeg stupca ili primjene bilo kojeg filtra, upit će se prikazati kao "Odaberite * iz", što će biti teže izmijeniti:

SQL upit koji koristi zadanu, kraću sintaksu

Izmjena SQL upita

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

  1. Ako za stupce koji sadrže valutu ili decimalni podaci nisu potrebni decimale, pomoću ove sintakse možete riješiti decimalnih mjesta:

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

    Ako vam je potreban cent, ali ne i razlomci centa, zamijenite 0 prema 2. Ako koristite negativne brojeve, možete zaokružiti na jedinice, desetke, stotine itd.

  2. Ako imate stupac Datumvremena koji se zove d-Bo. Bigtable. [Vrijeme datuma] i nije vam potreban vremenski dio, pomoću sintakse možete riješiti vremena:

    "Odaberite CAST (d. Bigtable. [Vrijeme datuma] kao datum) kao [vrijeme datuma]) "

  3. Ako imate stupac Datumvremena koji se zove d-Bo. Bigtable. [Vrijeme datuma] i potrebni su vam dijelovi datuma i vremena, pomoću više stupaca u SQL upitu, a ne u stupcu single DATETIME:

    "Odaberite CAST (d. Bigtable. [Vrijeme datuma] kao datum) kao [vrijeme datuma],

    DatePart (hh, Debo. Bigtable. [Vrijeme datuma]) kao [datumski vremenski sati],

    DatePart (mi, Debo. Bigtable. [Vrijeme datuma]) kao [Datum vremena minuta],

    DatePart (SS, Debo. Bigtable. [Vrijeme datuma]) kao [Datum vrijeme sekunde],

    DatePart (MS, gbo. Bigtable. [Vrijeme datuma]) kao [Datum vrijeme milisekundama] "

    Koristite onoliko stupaca koliko je potrebno za pohranu svakog dijela u zasebnim stupcima.

  4. Ako su vam potrebne sate i minute, a preferirate ih zajedno kao jedan vremenski stupac, možete koristiti sintaksu:

    Timefromparts (DatePart (hh, Debo. Bigtable. [Vrijeme datuma]), DatePart (mm, Debo. Bigtable. [Vrijeme datuma])) kao [datumski sat sata]

  5. Ako imate dva stupca datetime, kao što su [vrijeme početka] i [vrijeme završetka], a ono što vam je stvarno potrebno jest vremenska razlika između njih u sekundama kao stupca pod nazivom [trajanje], uklonite oba stupca s popisa i dodajte:

    "DateDiff (SS, [početni datum], [Datum završetka]) kao [trajanje]"

    Ako koristite ključnu riječ MS umjesto SS-a, prikazat će vam se trajanje u milisekundama

Pomoću DAX izračunatih mjera umjesto stupaca

Ako ste prije radili na jeziku DAX Expression, 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 jednom u modelu definiraju, ali se procjenjuju samo kada se koriste u Zaokretna tablica ili neko drugo izvješće.

Jedna tehnika spremanja pamćenja jest zamijeniti redovite ili izračunate stupce pomoću izračunatih mjera. Klasični je primjer Jedinična cijena, količina i ukupni zbroj. Ako imate sva tri, možete spremiti razmak zadržavanjem samo dva i izračunavanju trećeg korištenja DAX.

Koje 2 stupce trebate zadržati?

U gornjem primjeru zadržite količinu i jediničnu cijenu. Ova dva imaju manje vrijednosti nego ukupni zbroj. Da biste izračunali ukupan zbroj, dodajte izračunatu mjeru kao što su:

"TotalSales: = sumx (' Prodaja tablica ', ' Prodaja tablica ' [Jedinična cijena] * ' Prodaja tablica ' [količina])"

Izračunati stupci su kao uobičajeni stupci u kojima se nalaze i razmak u modelu. Nasuprot tome, izračunate se mjere na letenju i ne uzimaju razmak.

Zaključak

U ovom smo članku razgovarali o nekoliko pristupa koji će vam pomoći da izgradimo više memorije – učinkovit model. Način da smanjite veličinu datoteke i preduvjete za memoriju podatkovnog modela jest da smanjite ukupni broj stupaca i redaka te broj jedinstvenih vrijednosti koje se prikazuju u svakom stupcu. Evo nekih tehnika koje smo pokrili:

  • Uklanjanje stupaca svakako je najbolji način spremanja razmaka. Odlučite koje stupce zaista trebate.

  • Ponekad možete ukloniti stupac i zamijeniti ga izračunatom mjerom u tablici.

  • Možda vam nisu potrebni svi reci u tablici. U čarobnjaku za uvoz tablica možete filtrirati retke.

  • U principu, rastavljati jedan stupac u više zasebnih dijelova dobar je način za smanjivanje broja jedinstvenih vrijednosti u stupcu. Svaki od dijelova imat će manji broj jedinstvenih vrijednosti, a kombinirani ukupni zbroj bit će manji od izvornog jedinstvenog stupca.

  • U mnogim slučajevima potrebni su vam i različiti dijelovi kao rezači u izvješćima. Kada je to primjereno, možete stvarati hijerarhije iz dijelova kao što su sati, minute i sekunde.

  • Stupci više puta sadrže više informacija nego što vam je potrebno. Pretpostavimo, primjerice, da stupac pohranjuje decimalne oznake, ali ste primijenili oblikovanje da biste skrivali sve decimalne brojeve. Zaokruživanje može biti vrlo učinkovito pri smanjenju veličine numeričkog stupca.

Sada kada ste učinili sve što je moguće da biste smanjili veličinu radne knjige, razmotrite i pokretanje 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

Preuzimanje alata za optimizaciju veličine radne knjige

PowerPivot: napredna analiza i modeliranje podataka u programu Excel

Potrebna vam je dodatna pomoć?

Proširite svoje vještine korištenja sustava Office
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.

×