În Excel 2013 sau ulterior, puteți să creați modele de date care conțin milioane de rânduri, apoi să efectuați analize puternice asupra acestor modele. Modelele de date pot fi create cu sau fără programul de completare Power Pivot pentru a acceptă orice număr de vizualizări PivotTable, diagrame și Power View în același registru de lucru.
Notă: Acest articol descrie modelele de date din Excel 2013. Dar aceleași caracteristici de modelare a datelor și Power Pivot introduse în Excel 2013 se aplică, de asemenea, pentru Excel 2016. Diferența efectivă dintre aceste versiuni de Excel.
Deși puteți construi cu ușurință modele mari de date Excel, există mai multe motive să nu faceți acest lucru. Mai întâi, modelele mari care conțin multitudine de tabele și coloane sunt supraagloberate pentru majoritatea analizelor și fac o Listă de câmpuri greoaie. Apoi, modelele mari utilizează memorie valoros, afectând negativ alte aplicații și rapoarte care partajează aceleași resurse de sistem. În sfârșit, Microsoft 365, atât SharePoint Online, cât și Excel Web App limitează dimensiunea unui fișier Excel la 10 MB. Pentru modelele de date de registru de lucru care conțin milioane de rânduri, veți ajunge foarte rapid la limita de 10 MB. Consultați Specificațiile și limitele modelului de date.
În acest articol, veți afla cum să construiți un model bine construit, cu care să lucrați mai ușor și care utilizează mai puțină memorie. Dacă vă faceți timp să învățați cele mai bune practici în proiectarea eficientă a modelului, veți plăti mai bine pentru orice model pe care îl creați și îl utilizați, indiferent dacă îl vizualizați în Excel 2013, Microsoft 365 SharePoint Online, pe un Office Web Apps Server sau în SharePoint 2013.
De asemenea, luați în considerare rularea optimizatorului pentru dimensiunea registrelor de lucru. Acesta analizează registrul de lucru Excel și, dacă este posibil, îl comprimă și mai mult. Descărcați Workbook Size Optimizer (Optimizatorul pentru dimensiunea registrelor de lucru).
În acest articol
Rapoarte de comprimare și motorul de analiză în memorie
Modelele de date din Excel utilizează motorul de analiză din memorie pentru a stoca date în memorie. Motorul implementează tehnici puternice de comprimare pentru a reduce cerințele de stocare, micșorând un set de rezultate până când acesta este o fracțiune din dimensiunea sa inițială.
În medie, vă puteți aștepta ca un model de date să fie de la 7 la 10 ori mai mic decât aceleași date din punctul său de origine. De exemplu, dacă importați 7 MB de date dintr-o bază de date SQL Server, modelul de date din Excel poate avea cu ușurință 1 MB sau mai puțin. Gradul de comprimare obținut de fapt depinde în principal de numărul de valori unice din fiecare coloană. Cu cât sunt mai unice valorile, cu atât este necesară mai multă memorie pentru a le stoca.
De ce vorbim despre comprimare și valori unice? Deoarece crearea unui model eficient care minimizează utilizarea memoriei înseamnă maximizarea comprimării, iar cea mai simplă metodă de a face acest lucru este să eliminați coloanele de care nu aveți nevoie, mai ales în cazul în care acele coloane conțin un număr mare de valori unice.
Notă: Diferențele de cerințe de stocare pentru coloanele individuale pot fi foarte mari. În unele cazuri, este mai bine să aveți mai multe coloane cu un număr scăzut de valori unice, mai degrabă decât o coloană cu un număr mare de valori unice. Secțiunea despre Optimizările datăoră descrie în detaliu această tehnică.
Nimic nu face mai mare decât o coloană care nu există, pentru o utilizare redusă a memoriei
Coloana cea mai eficientă din punct de vedere al memoriei este cea pe care nu ați importat-o de la bun început. Dacă doriți să construiți un model eficient, uitați-vă la fiecare coloană și întrebați-vă dacă contribuie la analiza pe care doriți să o efectuați. Dacă nu le are sau nu sunteți sigur, lăsați-le acolo. Puteți adăuga oricând coloane noi mai târziu, dacă aveți nevoie de ele.
Două exemple de coloane care ar trebui să fie întotdeauna excluse
Primul exemplu se referă la date care provin dintr-un depozit de date. Într-un depozit de date, este obișnuit să găsiți artefacte cu procesele ETL care încarcă și reîmprospătează datele din depozit. Coloanele, cum ar fi "creare dată", "dată actualizare" și "Rulare ETL" sunt create atunci când sunt încărcate datele. Niciuna dintre aceste coloane nu este necesară în model și ar trebui să fie deselectată atunci când importați date.
Al doilea exemplu implică omiterea coloanei cheii primare atunci când se importă un tabel de informații.
Multe tabele, inclusiv tabelele de date, au chei primare. Pentru majoritatea tabelelor, cum ar fi cele care conțin date despre client, angajat sau vânzări, veți dori cheia primară a tabelului, astfel încât să o puteți utiliza pentru a crea relații în model.
Tabelele de date sunt diferite. Într-un tabel de date, cheia primară este utilizată pentru a identifica în mod unic fiecare rând. Deși este necesar pentru normalizare, este mai puțin util într-un model de date în care doriți doar acele coloane utilizate pentru analiză sau pentru a stabili relații între tabele. Din acest motiv, atunci când importați dintr-un tabel de informații, nu includeți cheia sa primară. Cheile primare din tabelul de informații consumă cantități enorme de spațiu în model, dar nu oferă niciun beneficiu, deoarece nu pot fi utilizate pentru a crea relații.
Notă: În depozitele de date și bazele de date multidimensionale, tabelele mari formate din date numerice sunt denumite adesea "tabele de date". Tabelele de date includ de obicei performanțele firmei sau datele despre tranzacții, cum ar fi vânzările și punctele de date despre cost, care sunt agregate și aliniate cu unitățile organizaționale, produsele, segmentele de piață, regiunile geografice etc. Toate coloanele dintr-un tabel de informații care conțin date de firmă sau care pot fi utilizate pentru a face referire încrucișată la datele stocate în alte tabele ar trebui să fie incluse în model pentru a susține analiza datelor. Coloana pe care doriți să o excludeți este coloana cheii primare din tabelul de informații, care constă în valori unice care există doar în tabelul de informații, nu în alt loc. Deoarece datele tabelelor sunt atât de mari, unele dintre cele mai mari câștiguri în eficiența modelului provin din excluderea rândurilor sau coloanelor din tabelele de date.
Cum se exclud coloanele inutile
Modelele eficiente conțin numai acele coloane de care veți avea nevoie de fapt în registrul de lucru. Dacă doriți să controlați ce coloane sunt incluse în model, va trebui să utilizați Expertul import tabel din programul de completare Power Pivot pentru a importa datele, mai degrabă decât caseta de dialog "Import date" din Excel.
Atunci când porniți Expertul import tabel, selectați tabelele pe care le importați.
Pentru fiecare tabel, puteți să faceți clic pe butonul Previzualizare & a filtrului și să selectați părțile din tabel de care aveți nevoie. Vă recomandăm să debifați mai întâi toate coloanele, apoi să verificați coloanele dorite, după ce luați în considerare dacă acestea sunt necesare pentru analiză.
Ce se întâmplă cu filtrarea numai a rândurilor necesare?
Multe tabele din baze de date corporative și depozite de date conțin date istorice acumulate pe perioade lungi de timp. În plus, este posibil să descoperiți că tabelele care vă interesează conțin informații pentru zonele din firmă care nu sunt necesare pentru analiza dvs. specifică.
Utilizând expertul Import tabel, puteți filtra datele istorice sau neascorelate și astfel puteți economisi mult spațiu în model. În imaginea următoare, un filtru de date este utilizat pentru a regăsi doar rândurile care conțin date pentru anul curent, excluzând datele istorice care nu vor fi necesare.
Ce se întâmplă dacă avem nevoie de coloană; Mai putem reduce costurile sale de spațiu?
Există câteva tehnici suplimentare pe care le puteți aplica pentru a face o coloană un candidat mai bun pentru comprimare. Rețineți că singura caracteristică a coloanei care afectează comprimarea este numărul de valori unice. În această secțiune, veți afla cum se pot modifica unele coloane pentru a reduce numărul de valori unice.
Modificarea coloanelor Datetime
În multe cazuri, coloanele Datetime preiau mult spațiu. Din fericire, există câteva moduri de a reduce cerințele de stocare pentru acest tip de date. Tehnicile vor varia în funcție de modul în care utilizați coloana, precum și de nivelul de confort în construirea SQL suplimentare.
Coloanele Datăoră includ o parte de dată și o oră. Atunci când vă întrebați dacă aveți nevoie de o coloană, adresați aceeași întrebare de mai multe ori pentru o coloană Datetime:
-
Am nevoie de partea de timp?
-
Am nevoie de o parte de timp la nivelul orelor? , minute? , Secunde? , milisecunde?
-
Am mai multe coloane Datetime pentru că doresc să calculez diferența dintre ele sau doar pentru a agrega datele după an, lună, trimestru și așa mai departe.
Modul în care răspundeți la fiecare dintre aceste întrebări determină opțiunile dvs. pentru gestionarea coloanei Datetime.
Toate aceste soluții necesită modificarea unei interogări de SQL date. Pentru a simplifica modificarea interogărilor, ar trebui să filtrați cel puțin o coloană din fiecare tabel. Filtrând o coloană, modificați construirea interogării dintr-un format abreviat (SELECT *) într-o inserție SELECT care include nume de coloane complet calificate, care sunt mult mai ușor de modificat.
Să vedem interogările create pentru dumneavoastră. Din caseta de dialog Proprietăți tabel, puteți să comutați la editorul de interogare și să vedeți interogarea SQL tabel pentru fiecare tabel.
Din Proprietăți tabel, selectați Editor interogare.
Editorul interogare afișează interogarea SQL interogare utilizată pentru a popula tabelul. Dacă aveți o coloană filtrată în timpul importului, interogarea include nume de coloane complet calificate:
|
În schimb, dacă ați importat un tabel în întregime, fără a debifa nicio coloană sau a aplica niciun filtru, veți vedea interogarea ca "Selectare * din ", ceea ce va fi mai dificil de modificat:
|
Modifying the SQL query
Acum că știți cum să găsiți interogarea, o puteți modifica pentru a reduce și mai mult dimensiunea modelului.
-
Pentru coloanele care conțin date monetare sau zecimale, dacă nu aveți nevoie de zecimale, utilizați această sintaxă pentru a elimina zecimalele:
"SELECT ROUND([Decimal_column_name],0)... .”
Dacă aveți nevoie de bani, dar nu de fracții de bani, înlocuiți 0 cu 2. Dacă utilizați numere negative, aveți posibilitatea să rotunjiți la unități, zeci, sute etc.
-
Dacă aveți o coloană Datetime numită dbo. Bigtable. [Dată] și nu aveți nevoie de componenta Timp, utilizați sintaxa pentru a elimina ora:
"SELECT CAST (dbo. Bigtable. [Dată] ca dată) AS [Oră de dată]) "
-
Dacă aveți o coloană Datetime numită dbo. Bigtable. [Data] și aveți nevoie atât de părțile Dată și Oră, utilizați mai multe coloane în interogarea SQL în locul coloanei Datăoră unică:
"SELECT CAST (dbo. Bigtable. [Dată] ca dată ) AS [Ora datei],
datepart(hh, dbo. Bigtable. [Ora]) ca [Ore de oră de dată],
datepart(mi, dbo. Bigtable. [Ora]) ca [Minute oră de dată],
datepart(ss, dbo. Bigtable. [Ora]) ca [Secunde oră de dată],
datepart(ms, dbo. Bigtable. [Ora]) ca [Timp de dată milisecunde]"
Utilizați oricâte coloane aveți nevoie pentru a stoca fiecare parte în coloane separate.
-
Dacă aveți nevoie de ore și minute și le preferați împreună ca o singură coloană de timp, puteți utiliza sintaxa:
Timefromparts(datepart(hh, dbo. Bigtable. [Ora datei]), datepart(mm, dbo. Bigtable. [Ora datei])) as [Date Time HourMinute]
-
Dacă aveți două coloane datăoră, cum ar fi [Oră de început] și [Oră de sfârșit], iar ceea ce vă trebuie de fapt este diferența de timp dintre ele în secunde, sub numele de coloană, denumită [Durată], eliminați ambele coloane din listă și adăugați:
"datediff(is,[Data de început],[Data de sfârșit]) ca [Durată]"
Dacă utilizați cuvântul cheie ms în loc de ss, veți obține durata în milisecunde
Utilizarea măsurilor calculate DAX în locul coloanelor
Dacă ați mai lucrat cu limbajul de expresii DAX înainte, este posibil să știți deja că s-au utilizat coloanele calculate pentru a deduce coloane noi pe baza altor coloane din model, în timp ce măsurile calculate sunt definite o dată în model, dar evaluate numai atunci când sunt utilizate într-un raport PivotTable sau în alt raport.
O tehnică de economisire a memoriei este înlocuirea coloanelor obișnuite sau calculate cu măsuri calculate. Exemplul clasic este Preț unitar, Cantitate și Total. Dacă le aveți pe toate trei, puteți economisi spațiu menținând doar două și calculând a treia utilizând DAX.
Ce 2 coloane ar trebui să păstrați?
În exemplul de mai sus, păstrați Cantitate și Preț unitar. Acestea două au mai puține valori decât total. Pentru a calcula Total, adăugați o măsură calculată precum:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Coloanele calculate sunt la fel ca coloanele obișnuite, în care ambele ocupă spațiu în model. În schimb, măsurile calculate sunt calculate pe loc și nu ocupă spațiu.
Concluzie
În acest articol, am menționat mai multe abordări care vă pot ajuta să construiți un model mai eficient din punct de vedere al memoriei. Modul de a reduce dimensiunea fișierului și cerințele de memorie ale unui model de date este de a reduce numărul general de coloane și rânduri și numărul de valori unice care apar în fiecare coloană. Iată câteva tehnici pe care le-am acoperit:
-
Eliminarea coloanelor este, desigur, cel mai bun mod de a economisi spațiu. Decideți de ce coloane aveți nevoie.
-
Uneori, puteți să eliminați o coloană și să o înlocuiți cu o măsură calculată din tabel.
-
Este posibil să nu aveți nevoie de toate rândurile dintr-un tabel. Puteți filtra rânduri în Expertul import tabel.
-
În general, despărțirea unei singure coloane în mai multe părți distincte este o modalitate bună de a reduce numărul de valori unice dintr-o coloană. Fiecare dintre părți va avea un număr mic de valori unice, iar totalul combinat va fi mai mic decât coloana unificată inițială.
-
În multe cazuri, aveți nevoie și de părți distincte pentru a le utiliza ca slicere în rapoarte. Atunci când este cazul, puteți crea ierarhii din părți, cum ar fi Ore, Minute și Secunde.
-
De multe ori, coloanele conțin mai multe informații decât aveți nevoie de ele. De exemplu, să presupunem că o coloană stochează zecimale, dar ați aplicat formatări pentru a ascunde toate zecimalele. Rotunjarea poate fi foarte eficientă pentru reducerea dimensiunii unei coloane numerice.
Acum, că ați făcut tot ce puteți pentru a reduce dimensiunea registrului de lucru, luați în considerare și rularea optimizator pentru dimensiunea registrelor de lucru. Acesta analizează registrul de lucru Excel și, dacă este posibil, îl comprimă și mai mult. Descărcați Workbook Size Optimizer (Optimizatorul pentru dimensiunea registrelor de lucru).
Linkuri utile
Specificațiile și limitele modelului de date
Power Pivot: analize puternice de date și modelare de date în Excel
