În Excel 2013 sau o versiune mai recentă, puteți să creați modele de date care conțin milioane de rânduri, apoi să efectuați analize puternice de date împotriva acestor modele. Modelele de date pot fi create cu sau fără programul de completare Power Pivot pentru a accepta un număr de rapoarte PivotTable, diagrame și vizualizări 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 este mică.
Deși puteți construi cu ușurință modele imense de date î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 supracalificate pentru majoritatea analizelor și fac 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 au 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 limita de 10 MB destul de rapid. Consultați Specificații și limite pentru modelul de date.
În acest articol, veți afla cum să construiți un model strâns construit, care este mai ușor de lucrat cu și utilizează mai puțină memorie. A vă face timp pentru a afla cele mai bune practici în proiectarea eficientă a modelelor va fi mai potrivită 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 Optimizatorul pentru dimensiunea registrelor de lucru.
În acest articol
Rapoarte de comprimare și motorul de analiză a memoriei
Modelele de date din Excel utilizează motorul de analiză a memoriei 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 inițială.
Î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 poate 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 atat este necesară mai multă memorie pentru a le stoca.
De ce vorbim despre comprimare și valori unice? Deoarece construirea unui model eficient care minimizează utilizarea memoriei înseamnă maximizarea compresiei și cea mai simplă modalitate de a face acest lucru este să eliminați coloanele de care nu aveți nevoie, mai ales dacă acele coloane includ un număr mare de valori unice.
Notă: Diferențele dintre cerințele de stocare pentru coloane individuale pot fi imense. În unele cazuri, este mai bine să aveți mai multe coloane cu un număr mic de valori unice, nu o coloană cu un număr mare de valori unice. Secțiunea despre Optimizările datetime descrie în detaliu această tehnică.
Nimic nu bate o coloană inexistentă pentru utilizarea redusă a memoriei
Coloana cea mai eficientă din punct de vedere al memoriei 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 funcționează sau nu sunteți sigur, lăsați-l la o parte. Puteți adăuga oricând coloane noi mai târziu, dacă aveți nevoie de ele.
Două exemple de coloane care trebuie excluse întotdeauna
Primul exemplu se referă la date care provin dintr-un depozit de date. Într-un depozit de date, se întâmplă frecvent să găsiți artefacte ale proceselor ETL care încarcă și reîmprospătează datele în depozit. Coloanele, cum ar fi "create date", "update date" și "ETL run" sunt create atunci când se încarcă datele. Niciuna dintre aceste coloane nu este necesară în model și trebuie deselectată atunci când importați date.
Al doilea exemplu implică omiterea coloanei cheie primară atunci când se importă un tabel cu informații.
Multe tabele, inclusiv tabelele cu informații, 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 cu elemente informative sunt diferite. Într-un tabel fapt, 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 să fie utilizate doar acele coloane pentru analiză sau pentru a stabili relații între tabele. Din acest motiv, atunci când importați dintr-un tabel cu informații, nu includeți cheia sa primară. Cheile primare dintr-un tabel fapt consumă volume 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 constând în date numerice în mare parte sunt denumite adesea "tabele informative". Tabelele cu informații includ de obicei date despre performanța afacerii sau 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ță, regiuni geografice și așa mai departe. Toate coloanele dintr-un tabel fapt 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 accepta analiza datelor. Coloana pe care doriți să o excludeți este coloana cheie primară a tabelului cu informații, care constă din valori unice care există doar în tabelul de informații și în alt loc. Deoarece tabelele cu date sunt atât de imense, unele dintre cele mai mari câștiguri în eficiența modelului sunt derivate din excluderea rândurilor sau coloanelor din tabelele cu informații.
Cum se exclud coloanele inutile
Modelele eficiente conțin doar acele coloane de care veți avea 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 , nu caseta de dialog "Import date" din Excel.
Când porniți Expertul import tabel, selectați tabelele de importat.
Pentru fiecare tabel, puteți să faceți clic pe butonul Examinare & Filtru și să selectați părțile de tabel 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 luați în considerare dacă sunt necesare pentru analiză.
Ce se întâmplă cu filtrarea doar rândurile necesare?
Multe tabele din bazele de date corporative și din depozitele 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 să filtrați datele istorice sau neasociate și, astfel, să economisiți mult spațiu în model. În imaginea următoare, se utilizează un filtru de dată 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 coloana; putem reduce în continuare costul spațiului său?
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 dată-oră
În multe cazuri, coloanele Dată și oră ocupă mult spațiu. Din fericire, există mai multe 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 de confort în construirea interogărilor SQL.
Coloanele Dată și 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 partea de timp la nivelul orelor? Minute? Secunde? Milisecunde?
-
Am mai multe coloane Datetime, deoarece 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 pentru gestionarea coloanei Datetime.
Toate aceste soluții necesită modificarea unei interogări SQL. Pentru a facilita modificarea interogărilor, trebuie să filtrați cel puțin o coloană din fiecare tabel. Prin filtrarea unei coloane, modificați construcția interogării de la un format abreviat (SELECT *) la o instrucțiune SELECT care include nume de coloane complet calificate, care sunt mult mai ușor de modificat.
Să aruncăm o privire la 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 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 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 un filtru, veți vedea interogarea ca "Selectare * din ", ceea ce va fi mai dificil de modificat:
|
Modificarea interogării SQL
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 centi, dar nu de fracțiuni de cenți, înlocuiți 0 cu 2. Dacă utilizați numere negative, puteți rotunji la unități, zeci, sute etc.
-
Dacă aveți o coloană Datetime denumită dbo. Masă mare. [Dată oră] și nu aveți nevoie de partea Oră, utilizați sintaxa pentru a elimina ora:
"SELECT CAST (dbo. Masă mare. [Dată oră] ca dată) AS [Dată oră]) "
-
Dacă aveți o coloană Datetime denumită dbo. Masă mare. [Dată oră] și aveți nevoie de părțile Dată și Oră, utilizați mai multe coloane în interogarea SQL în loc de coloana dată-oră unică:
"SELECT CAST (dbo. Masă mare. [Dată Oră] ca dată ) AS [Dată oră],
datepart(hh, dbo. Masă mare. [Dată oră]) ca [Ore oră dată],
datepart(mi, dbo. Masă mare. [Dată oră]) ca [Minute oră dată],
datepart(ss, dbo. Masă mare. [Dată oră]) ca [Dată oră secunde],
datepart(ms, dbo. Masă mare. [Dată oră]) ca [Milisecunde dată oră]"
Utilizați oricâte coloane trebuie să stocați 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. Masă mare. [Dată Oră]), datepart(mm, dbo. Masă mare. [Dată oră])) ca [Oră oră orăminută]
-
Dacă aveți două coloane dată-oră, cum ar fi [Oră de început] și [Oră de sfârșit], iar diferența de timp dintre ele, în secunde, ca o coloană numită [Durată], eliminați ambele coloane din listă și adăugați:
"datediff(es,[Data de început],[Dată 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 lucrat cu limbajul de expresii DAX înainte, este posibil să știți deja că coloanele calculate sunt utilizate pentru a extrage coloane noi pe baza altei coloane din model, în timp ce măsurile calculate sunt definite o 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 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-o pe a treia utilizând DAX.
Ce 2 coloane ar trebui să 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 Total, adăugați o măsură calculată, cum ar fi:
"TotalVânzări:=sumx('Tabel vânzări','Tabel vânzări'[Preț unitar]*'Tabel vânzări'[Cantitate])"
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 vorbit 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 fișierului și cerințele de memorie ale unui model de date este să reduceți numărul total de coloane și rânduri și numărul de valori unice care apar în fiecare coloană. Iată câteva tehnici pe care le-am descris:
-
Eliminarea coloanelor este, desigur, cea mai bună modalitate de a economisi spațiu. Decideți de ce coloane aveți într-adevăr nevoie.
-
Uneori puteți să eliminați o coloană și să o înlocuiți cu o măsură calculată în tabel.
-
Este posibil să nu aveți nevoie de toate rândurile dintr-un tabel. Puteți filtra rândurile în Expertul import tabel.
-
În general, împă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 parte va avea un număr mic de valori unice, iar totalul combinat va fi mai mic decât coloana unificată originală.
-
În multe cazuri, aveți nevoie, de asemenea, de părți distincte de utilizat ca slicere în rapoartele dvs. Atunci când este cazul, puteți crea ierarhii din părți precum 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 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 era posibil 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 Optimizatorul pentru dimensiunea registrelor de lucru.
Linkuri utile
Specificațiile și limitele modelului de date
Optimizator dimensiune registru de lucru
Power Pivot: analize puternice de date și modelare de date în Excel
