În Excel, puteți să creați modele de date care conțin milioane de rânduri, apoi să efectuați analize puternice de date în raport cu aceste modele. Modelele de date pot fi create cu sau fără programul de completare Power Pivot pentru a accepta orice număr de rapoarte PivotTable, diagrame și vizualizări Power View în același registru de lucru.
Deși puteți construi cu ușurință modele de date uriașe în Excel, există mai multe motive să nu faceți acest lucru. În primul rând, modelele mari care conțin o multitudine de tabele și coloane sunt exagerate pentru majoritatea analizelor și creează o listă de câmpuri greoaie. În al doilea rând, modelele mari utilizează memorie valoroasă, afectând negativ alte aplicații și rapoarte care partajează aceleași resurse de sistem. În sfârșit, în 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 atinge destul de repede 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, care este mai ușor de lucrat și utilizează mai puțină memorie. Acordarea unui timp pentru a afla cele mai bune practici în proiectarea eficientă a modelelor va da roade pe parcurs pentru orice model pe care îl creați și îl utilizați, indiferent dacă îl vizualizați în Excel, Microsoft 365 SharePoint Online, pe un server Office Web Apps sau în SharePoint.
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 instrumentul de optimizare pentru dimensiunea registrelor de lucru.
În acest articol
Nimic nu se compară cu o coloană inexistentă pentru utilizare redusă a memoriei
Ce se întâmplă dacă avem nevoie de coloană; Putem reduce în continuare costul spațiului?
Rapoartele de compresie și motorul de analiză în memorie
Modelele de date din Excel utilizează motorul de analiză în 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 ajunge la o fracțiune din dimensiunea sa originală.
În medie, vă puteți aștepta ca un model de date să fie de 7 până 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 ar putea avea cu ușurință 1 MB sau mai puțin. Gradul de comprimare obținut efectiv depinde în principal de numărul de valori unice din fiecare coloană. Cu cât sunt mai multe valori unice, cu atât este necesară mai multă memorie pentru a le stoca.
De ce vorbim despre compresie și valori unice? Deoarece construirea unui model eficient care minimizează utilizarea memoriei înseamnă maximizarea comprimării, iar cea mai simplă modalitate de a face acest lucru este să scăpați de coloanele de care nu aveți cu adevărat nevoie, mai ales dacă acele coloane conțin un număr mare de valori unice.
Notă
Diferențele între cerințele de stocare pentru coloane individuale pot fi foarte mari. În unele cazuri, este mai bine să aveți mai multe coloane cu un număr mic de valori unice decât o coloană cu un număr mare de valori unice. Secțiunea Optimizări ale datei și ora descrie în detaliu această tehnică.
Nimic nu se compară cu o coloană inexistentă pentru utilizare redusă a memoriei
Coloana cu cea mai eficientă memorie este cea pe care nu ați importat-o niciodată. Dacă doriți să construiți un model eficient, uitați-vă la fiecare coloană și întrebați-vă dacă aceasta contribuie la analiza pe care doriți să o efectuați. Dacă nu este sau nu sunteți sigur, ignorați-l. Puteți adăuga oricând coloane noi mai târziu, dacă aveți nevoie de ele.
Două exemple de coloane care ar trebui excluse întotdeauna
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 ale proceselor ETL care încarcă și reîmprospătează datele din depozit. Coloane, cum ar fi "data creării", "data actualizării" și "rularea ETL" sunt create atunci când se încarcă datele. Niciuna dintre aceste coloane nu este necesară în model și ar trebui să fie deselectată când importați date.
Al doilea exemplu implică omiterea coloanei cheii primare la importul unui tabel de informații.
Multe tabele, inclusiv tabelele de informații, au chei primare. Pentru cele mai multe tabele, cum ar fi cele care conțin date despre clienți, angajați sau vânzări, se recomandă 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 informații, cheia primară este utilizată pentru a identifica în mod unic fiecare rând. Deși este necesar în scopuri de normalizare, acest lucru este mai puțin util într-un model de date, unde doriți ca doar acele coloane să fie 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 dintr-un tabel de informații consumă o cantitate enormă 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 în mare parte din date numerice, sunt denumite adesea "tabele de fapte". Tabelele de date includ de obicei performanța firmei sau date despre tranzacții, cum ar fi puncte de date despre vânzări și costuri, care sunt agregate și aliniate la unități organizaționale, produse, segmente de piață, zone geografice etc. Toate coloanele dintr-un tabel de informații care conțin date de firmă sau care pot fi utilizate pentru a face referiri încrucișate la datele stocate în alte tabele ar trebui incluse în model pentru a sprijini 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 tabelele de informații sunt atât de mari, unele dintre cele mai mari câștiguri în eficiența modelului sunt derivate din excluderea rândurilor sau coloanelor din tabelele de informații.
Cum se exclud coloanele inutile
Modelele eficiente conțin numai acele coloane de care aveți nevoie î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 , în locul casetei de dialog "Import date" din Excel.
Atunci când porniți Expertul import tabele, selectați ce tabele să importați.
Pentru fiecare tabel, puteți să faceți clic pe butonul Examinare & filtrare și să selectați părțile de care aveți cu adevărat nevoie. Vă recomandăm să debifați mai întâi toate coloanele, apoi să verificați coloanele dorite, după ce ați verificat dacă sunt necesare pentru analiză.
Dar filtrarea doar a rândurilor necesare?
Multe tabele din bazele de date și depozitele de date de corporație 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 zone ale domeniului care nu sunt necesare pentru analiza dvs. specifică.
Utilizând Expertul import tabel, puteți să filtrați datele istorice sau necorelate și să economisiți astfel 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 datele pentru anul curent, excluzând datele istorice care nu vor fi necesare.
Ce se întâmplă dacă avem nevoie de coloană; Putem reduce în continuare costul spațiului?
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 pot fi modificate unele coloane pentru a reduce numărul de valori unice.
Modificarea coloanelor Datetime
În multe cazuri, coloanele DateTime ocupă mult spațiu. Din fericire, există câteva modalități 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 și de nivelul dvs. de confort la crearea interogărilor SQL.
Coloanele DateTime includ o parte de dată și o oră. Când vă întrebați dacă aveți nevoie de o coloană, adresați aceeași întrebare de mai multe ori pentru o coloană Datăoră:
- Am nevoie de partea oră?
- Am nevoie de partea de timp la nivelul de ore? , minute? , Seconds? , în milisecunde?
- Am mai multe coloane DateTime pentru că doresc să calculez diferența dintre ele sau pur și simplu să agreg datele după an, lună, trimestru etc.
Modul în care răspundeți la fiecare dintre aceste întrebări determină opțiunile pentru tratarea coloanei Dată/oră.
Toate aceste soluții necesită modificarea unei interogări SQL. Pentru a simplifica modificarea interogării, trebuie să filtrați cel puțin o coloană din fiecare tabel. Prin filtrarea unei coloane, modificați construcția interogării dintr-un format abreviat (SELECT *) într-o instrucțiune SELECT care include nume de coloane complet calificate, care sunt mult mai ușor de modificat.
Să examinăm interogările create pentru dvs. Din caseta de dialog Proprietăți tabel, puteți să comutați la editorul de interogare și să vedeți interogarea SQL curentă pentru fiecare tabel.
Din Table Properties (Proprietăți tabel), selectați Editor Power Query.
Editor Power Query afișează interogarea SQL utilizată pentru a popula tabelul. Dacă ați filtrat orice coloană în timpul importului, interogarea dvs. include nume de coloane complet calificate:
În schimb, dacă ați importat un tabel în întregime, fără să debifați nicio coloană sau să aplicați vreun filtru, veți vedea interogarea ca "Selectare * de la ", care va fi mai dificil de modificat:
|
|---|
Modificarea interogării SQL
Acum că știți cum să găsiți interogarea, puteți să o modificați pentru a reduce și mai mult dimensiunea modelului.
- Pentru coloanele care conțin monedă sau date zecimale, dacă nu aveți nevoie de zecimale, utilizați această sintaxă pentru a elimina zecimale:
"SELECT ROUND([Decimal_column_name],0)... .”
Dacă aveți nevoie de cenți, dar nu și de fracțiuni de cenți, înlocuiți 0 cu 2. Dacă utilizați numere negative, puteți rotunji la unități, la zeci, sute etc. - Dacă aveți o coloană Datetime denumită dbo. Bigtable. [Data și ora] Dacă nu aveți nevoie de partea Oră, utilizați sintaxa pentru a elimina ora:
"SELECT CAST (dbo. Bigtable. [Dată, oră] ca dată) AS [Dată, oră]) " - Dacă aveți o coloană Datetime denumită dbo. Bigtable. [Dată/Oră] dacă aveți nevoie de părțile Dată și Oră, utilizați mai multe coloane în interogarea SQL în locul coloanei unice DatăOră:
"SELECT CAST (dbo. Bigtable. [Dată, Oră] ca dată ) AS [Dată, oră],
Datepart(hh, dbo. Bigtable. [Dată/oră]) ca [Dată/Oră],
Datepart(mi, dbo. Bigtable. [Dată/oră]) as [Dată/oră, minute],
Datepart(ss, dbo. Bigtable. [Dată/oră]) AS [Dată, Timp, Secunde],
DATEPART(MS, dbo. Bigtable. [Dată/oră]) ca [Dată, Timp, 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. [Data Time]), datepart(mm, dbo. Bigtable. [Dată/oră])) as [Dată, OrăMinut] - Dacă aveți două coloane de dată și oră, cum ar fi [Oră de început] și [Oră de sfârșit] și ceea ce vă trebuie cu adevărat este diferența de timp în secunde dintre ele ca coloană numită [Durată], eliminați ambele coloane din listă și adăugați:
"datediff(ss,[Dată de început],[Dată de sfârșit]) as [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 loc de coloane
Dacă ați mai lucrat cu limbajul de expresii DAX, este posibil să știți deja că coloanele calculate sunt utilizate pentru a obține coloane noi pe baza altei coloane din model, în timp ce măsurile calculate sunt definite o singură dată în model, dar evaluate doar 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 Unit Price, Quantity, and Total. Dacă aveți toate trei, puteți economisi spațiu păstrând doar două și calculând-o pe a treia folosind DAX.
Care sunt cele 2 coloane pe care trebuie să le păstrați?
În exemplul de mai sus, păstrați Cantitatea și Prețul unitar. Acestea două au mai puține valori decât Totalul. Pentru a calcula Totalul, adăugați o măsură calculată cum ar fi:
"TotalSales:=sumx('Tabel vânzări','Tabel vânzări'[Preț unitar]*'Tabel vânzări'[Cantitate])"
Coloanele calculate sunt la fel de coloanele obișnuite, în sensul că 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 discutat despre mai multe abordări care vă pot ajuta să construiți un model mai eficient din punct de vedere al memoriei. Modalitatea de a reduce dimensiunea de fișier și cerințele de memorie ale unui model de date constă în reducerea numărului total de coloane și rânduri și a numărului de valori unice care apar în fiecare coloană. Iată câteva tehnici pe care le-am discutat:
- Eliminarea coloanelor este, desigur, cea mai bună modalitate de a economisi spațiu. Decideți de ce coloane aveți cu adevărat 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 elimina prin filtrare rândurile în Expertul import tabel.
- În general, împărțirea unei singure coloane în mai multe părți distincte este o bună modalitate 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 Orele, Minutele și Secundele.
- 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 formatare pentru a ascunde toate zecimalele. Rotunjirea poate fi foarte eficientă în 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 optimizatorului pentru dimensiunea registrelor de lucru. Acesta analizează registrul de lucru Excel și, dacă este posibil, îl comprimă și mai mult. Descărcați instrumentul de optimizare pentru dimensiunea registrelor de lucru.
Linkuri utile
Specificațiile și limitele modelului de date
Instrumentul de optimizare pentru dimensiunea registrelor de lucru
Power Pivot: analize puternice de date și modelare de date în Excel