Crearea unui model de date eficient din punct de vedere al memoriei utilizând Excel și programul de completare Power Pivot

Î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ă Power Pivot program de completare pentru a accepta orice număr de rapoarte PivotTable, diagrame și vizualizări Power View din 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 să construiți cu ușurință modele de date uriașe în Excel, există mai multe motive pentru a nu face acest lucru. Mai întâi, modelele mari care conțin mulțimi de tabele și coloane sunt nejustificate pentru majoritatea analizelor și fac pentru o listă de câmpuri greoaie. În al doilea rând, modelele mari utilizează o memorie valoroasă, afectând negativ alte aplicații și rapoarte care partajează aceleași resurse de sistem. În cele din urmă, în Office 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 avea o limită de 10 MB destul de rapid. Consultați specificații și limite pentru modelul de date.

În acest articol, veți învăța cum să creați un model bine construit, cu care este mai ușor să lucrați și care utilizează mai puțină memorie. Timpul de învățare a celor mai bune practici în proiectarea eficientă a modelului va fi achitat pe drum pentru orice model pe care îl creați și îl utilizați, indiferent dacă îl vizualizați în Excel 2013, Office 365 SharePoint Online, pe un server Office Web Apps 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 Instrumentul de optimizare a dimensiunii registrului de lucru.

În acest articol

Rate de compresie și motorul de analiză în memorie

Nimic nu este mai bun decât o coloană care nu este existentă pentru utilizare scăzută a memoriei

Două exemple de coloane care ar trebui să fie întotdeauna excluse

Cum se exclud coloanele inutile

Ce se întâmplă cu filtrarea doar a rândurilor necesare?

Ce se întâmplă dacă avem nevoie de coloană; putem reduce în continuare costurile sale spațiale?

Modificarea coloanelor DateTime

Modificarea interogării SQL

Utilizarea măsurilor calculate DAX în loc de coloane

Ce două coloane trebuie să păstrați?

Concluzie

Linkuri utile

Rate 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 compresie pentru a reduce cerințele de stocare, micșorând un set de rezultate până când este o fracțiune din dimensiunea 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 la punctul său de origine. De exemplu, dacă importați 7 MO de date dintr-o bază de date SQL Server, modelul de date din Excel ar putea fi cu ușurință de 1 MO sau mai puțin. Gradul de compresie 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 mai necesară memoria pentru a le stoca.

De ce vorbim despre compresie și valori unice? Deoarece construirea unui model eficient care minimizează utilizarea memoriei este totul despre maximizarea compresiei, iar cea mai simplă modalitate de a face acest lucru este să eliminați orice coloane 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 coloanele individuale pot fi uriașe. În unele cazuri, este mai bine să aveți mai multe coloane cu un număr mic de valori unice, mai degrabă decât o coloană, cu un număr mare de valori unice. Secțiunea despre optimizările DateTime acoperă această tehnică în detaliu.

Nimic nu este mai bun decât o coloană care nu este existentă pentru utilizare scăzută 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ă creați un model eficient, uitați-vă la fiecare coloană și întrebați-vă dacă acesta contribuie la analiza pe care doriți să o efectuați. Dacă nu este sau nu sunteți sigur, lăsaț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 să fie întotdeauna excluse

Primul exemplu se referă la datele 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 precum "Creare dată", "actualizare dată" și "ETL rulare" sunt create atunci când datele sunt încărcate. 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 cheie primară atunci când importați un tabel fact.

Multe tabele, inclusiv tabelele fact, au chei primare. Pentru majoritatea tabelelor, cum ar fi cele care conțin date despre clienți, angajați 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 fact sunt diferite. Într-un tabel fact, cheia primară este utilizată pentru a identifica în mod unic fiecare rând. În timp ce este necesar în scopul normalizării, este mai puțin util într-un model de date în care doriți să utilizați doar acele coloane utilizate pentru analiză sau să stabiliți relații între tabele. Din acest motiv, atunci când importați dintr-un tabel fact, nu includeți cheia primară. Cheile primare într-un tabel fact 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 în bazele de date multidimensionale, tabelele mari care constă în majoritatea datelor numerice sunt adesea denumite "tabele fact". Tabelele fact includ, de obicei, performanțe de afaceri sau date despre tranzacții, cum ar fi punctele de date de vânzări și de cost care sunt agregate și aliniate la unități organizaționale, produse, segmente de piață, regiuni geografice etc. Toate coloanele dintr-un tabel fact care conțin date de afaceri sau care pot fi utilizate pentru a face referire la date încrucișate 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 fact, care constă din valori unice care există doar în tabelul fact și nicăieri altundeva. Deoarece tabelele de fapt sunt atât de uriașe, unele dintre cele mai importante câștiguri din eficiența modelului sunt derivate din excluderea rândurilor sau coloanelor din tabelele fact.

Cum se exclud coloanele inutile

Modelele eficiente conțin doar 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 în 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 tabel, selectați tabelele de importat.

Table Import Wizard în programul de completare PowerPivot

Pentru fiecare tabel, puteți să faceți clic pe butonul previzualizare & filtru ș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ă continuați să verificați coloanele pe care le doriți, după ce luați în considerare dacă sunt necesare pentru analiză.

Panoul Preview în Table import Wizard

Ce se întâmplă cu filtrarea doar a rândurilor 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 specifică.

Utilizând Expertul import tabel, puteți să filtrați datele istorice sau necorelate și, astfel, să salvați mult spațiu în model. În următoarea imagine, se utilizează un filtru de dată pentru a regăsi numai rândurile care conțin date pentru anul curent, excluzând datele istorice care nu vor fi necesare.

Panoul Filter în Table Import Wizard

Ce se întâmplă dacă avem nevoie de coloană; putem reduce în continuare costurile sale spațiale?

Există câteva tehnici suplimentare pe care le puteți aplica pentru a face o coloană un candidat mai bun pentru compresie. 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 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 nivelul de confort în construirea interogărilor SQL.

Coloanele DateTime includ o parte dată și o oră. Atunci când vă întrebați dacă aveți nevoie de o coloană, puneți aceeași întrebare de mai multe ori pentru o coloană DateTime:

  • Am nevoie de o parte din 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 să agregați datele după an, lună, trimestru etc.

Modul în care răspundeți la fiecare dintre aceste întrebări determină opțiunile pentru a face față coloanei DateTime.

Toate aceste soluții necesită modificarea unei interogări SQL. Pentru a simplifica modificarea interogărilor, ar trebui să filtrați cel puțin o coloană din fiecare tabel. Prin filtrarea unei coloane, modificați construcția interogărilor dintr-un format abreviat (selectați *) într-o instrucțiune SELECT care include numele de coloană complet calificate, care sunt mult mai ușor de modificat.

Să aruncăm o privire la interogările care sunt 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.

Panglică din fereastra PowerPivot care afișează comanda Table Properties (Proprietăți tabel)

Din Proprietăți tabel, selectați Editor interogare.

Deschideți Query Editor din dialogul Table Properties

Editorul de interogare afișează interogarea SQL utilizată pentru a popula tabelul. Dacă ați filtrat orice coloană în timpul importului, interogarea include nume de coloană complet calificate:

Interogarea SQL utilizată pentru a regăsi datele

În schimb, dacă ați importat un tabel în întregime, fără să debifați nicio coloană sau să aplicați niciun filtru, veți vedea interogarea ca "Selectare * from", care va fi mai dificil de modificat:

Interogarea SQL utilizând sintaxa implicită, mai scurtă

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.

  1. 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 și de fracțiuni de centi, înlocuiți 0 la 2. Dacă utilizați numere negative, puteți rotunji la unități, zeci, sute etc.

  2. Dacă aveți o coloană DateTime denumită dbo. Tabelmare. [Dată oră] și nu aveți nevoie de partea de timp, utilizați sintaxa pentru a elimina timpul:

    "Selectați exprimate (dbo. Tabelmare. [Dată oră] as date) AS [dată oră]) "

  3. Dacă aveți o coloană DateTime denumită dbo. Tabelmare. [Dată oră] și aveți nevoie de ambele părți de dată și oră, utilizați mai multe coloane în interogarea SQL în locul coloanei single DateTime:

    "Selectați exprimate (dbo. Tabelmare. [Dată oră] as date) AS [dată oră],

    datepart (HH, dbo. Tabelmare. [Dată oră]) ca [ore de lucru pentru dată],

    datepart (mi, dbo. Tabelmare. [Dată oră]) ca [minute de timp pentru dată],

    datepart (SS, dbo. Tabelmare. [Dată oră]) ca [dată secunde],

    datepart (MS, dbo. Tabelmare. [Dată oră]) as [dată oră milisecunde] "

    Utilizați câte coloane aveți nevoie pentru a stoca fiecare parte în coloane separate.

  4. Dacă aveți nevoie de ore și minute și le preferați împreună ca o coloană de timp, puteți utiliza sintaxa:

    Timefromparts (datepart (HH, dbo. Tabelmare. [Dată oră]), datepart (mm, dbo. Tabelmare. [Dată oră])) as [data Time Orăminut]

  5. Dacă aveți două coloane DateTime, cum ar fi [dată de început] și [oră de sfârșit] și ceea ce aveți nevoie este diferența de timp dintre ele în câteva secunde ca coloană denumită [durată], eliminați ambele coloane din listă și adăugați:

    "DateDiff (SS, [dată de început], [dată de sfârșit]) ca [durată]"

    Dacă utilizați cuvântul cheie MS în locul SS, veți primi durata în milisecunde

Utilizarea măsurilor calculate DAX în loc de coloane

Dacă ați mai lucrat cu limba de expresie DAX înainte, 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 dată în model, dar evaluate numai atunci când sunt utilizate într-un PivotTable sau alt raport.

O tehnică de economisire a memoriei este înlocuirea coloanelor regulate sau calculate cu măsuri calculate. Exemplul clasic este prețul unitar, cantitatea și totalul. Dacă aveți toate cele trei, puteți economisi spațiu, menținând doar două și calculând a treia persoană folosind DAX.

Ce două coloane trebuie 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 ca coloane regulate, deoarece ambele ocupă spațiu în model. În schimb, măsurile calculate sunt calculate în zbor și nu iau spațiu.

Concluzie

În acest articol, am vorbit despre mai multe abordări care vă pot ajuta să creaț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 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, bineînțeles, cea mai bună metodă de a economisi spațiu. Decideți 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ă în tabel.

  • Este posibil să nu aveți nevoie de toate rândurile dintr-un tabel. Puteți să filtrați rândurile din Expertul import tabel.

  • În general, destrămarea 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ă originală.

  • În multe cazuri, aveți nevoie și de părțile distincte pe care să le utilizați ca slicere în rapoarte. Atunci când este cazul, puteți să creați 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 formatarea 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 execuția Optimizatorul de dimensiune registru de lucru. Acesta analizează registrul de lucru Excel și, dacă este posibil, îl comprimă și mai mult. Descărcați Instrumentul de optimizare a dimensiunii registrului de lucru.

Linkuri utile

Specificațiile și limitele modelului de date

Descărcare pentru optimizarea dimensiunii registrului de lucru

Power Pivot: analize puternice de date și modelare de date în Excel

Extindeți-vă competențele Office
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×