Office
Conectare

Crearea unui Model de date memorie eficientă utilizând Excel și de completare Power Pivot

Notă:  Dorim să vă oferim cel mai recent conținut de ajutor, cât mai rapid posibil, în limba dvs. Această pagină a fost tradusă automatizat și poate conține erori gramaticale sau inexactități. Scopul nostru este ca acest conținut să vă fie util. Vă rugăm să ne spuneți dacă informațiile v-au fost utile, în partea de jos a acestei pagini. Aici se află articolul în limba engleză , ca să îl puteți consulta cu ușurință.

În Excel 2013 sau versiuni mai recente, creați modele de date care conține milioane de rânduri și apoi efectua analize puternice de date pentru aceste modele. Modele 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 î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 cu ușurință generare modelele de date foarte mare din Excel, nu sunt mai multe motive la. În primul rând, mari modele care conțin multitudine de tabele și coloane sunt overkill pentru majoritatea analize și efectuați pentru o listă de câmpuri dificil. Al doilea rând, mari modele utilizați memoria valoros, care afectează negativ alte aplicații și rapoarte care au aceleași resurse de sistem. În cele din urmă, în Office 365, atât SharePoint Online și Excel Web App limita dimensiunea de fișier Excel la 10 MB. Pentru registrul de lucru modele de date care conțin milioane de rânduri, va rula în limita de 10 MB destul rapid. Consultați limitări și specificații pentru modele de date.

În acest articol veți învăța cum să construiți un model eficient cu care este mai ușor de lucrat și care utilizează mai puțină memorie. Dacă vă alocați timp să învățați despre cele mai bune practici în proiectarea eficientă a modelelor, veți vedea rezultatele mai târziu, pentru orice model pe care îl veți crea sau îl veți utiliza, indiferent dacă îl vizualizați în Excel 2013, Office 365 SharePoint Online, pe Office Web Apps Server sau în SharePoint 2013.

Luați în considerare, de asemenea, rulează Optimizatorul pentru dimensiunea registru de lucru. Vă analizează registrul de lucru Excel și dacă este posibil, se comprimă în continuare. Descărcați Optimizatorul pentru dimensiunea registru de lucru.

În acest articol

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

Nimic nu este mai bun o coloană care nu există pentru o utilizare scăzută a memoriei

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

Cum să excludeți coloanele care nu sunt necesare

Ce se întâmplă cu filtrarea doar la rândurile necesare?

Ce se întâmplă dacă avem nevoie de coloană; putem reduce în continuare spațiul ocupat de aceasta?

Modificarea coloanelor de tip dată/oră

Modificarea interogării SQL

Utilizarea măsurilor în loc de coloane calculate DAX

Ce 2 coloane care ar trebui să le păstrați?

Încheierea

Linkuri utile

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

Modelele de date din Excel utilizează motorul de analiză încorporat în memorie pentru a stoca date în memorie. Motorul implementează tehnici de compresie puternice 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 inițială.

În medie, vă puteți aștepta ca un model de date să fie de la 7 până la 10 ori mai mic decât aceleași date de la punctul 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 compresie atins efectiv depinde în principal de numărul de valori unice din fiecare coloană. Cu cât sunt mai multe valori unice, cu atât mai multă memorie este necesară pentru a le stoca.

De ce vorbim despre compresie și valori unice? Deoarece construirea unui model eficient care minimizează utilizarea memoriei presupune maximizarea compresiei, iar cea mai ușoară metodă de a face aceasta este să ștergeți coloanele de care nu aveți nevoie, în special dacă aceste coloane conțin un număr mare de valori unice.

Notă: Diferențele în ceea ce privește 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, decât o coloană cu un număr mare de valori unice. Secțiunea despre optimizările de dată și oră acoperă această tehnică în detaliu.

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

Coloana cea mai eficientă din punct de vedere al memoriei este cea pe care nici măcar nu ați importat-o. Dacă doriți să construiți un model eficient, priviți fiecare coloană și întrebați-vă dacă aceasta contribuie la analiza pe care doriți să o efectuați. Dacă nu contribuie sau dacă nu sunteți sigur, nu o includeți. Puteți oricând să adăugați coloane noi, ulterior, dacă aveți nevoie de ele.

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

Primul exemplu are legătură cu datele care provin dintr-un depozit de date. Într-un depozit de date, este un lucru obișnuit să găsiți artefacte ale proceselor ETL care încarcă și reîmprospătează datele din depozit. Coloane precum „data creării”, „data actualizării” și „rulare ETL” sunt create când se încarcă datele. Niciuna dintre aceste coloane nu sunt necesare în model și ar trebui să fie deselectate când importați datele.

Al doilea exemplu implică omiterea coloanei cheie primare, când importați un tabel faptic.

Multe tabele, inclusiv tabelele faptice, au chei primare. Pentru majoritatea tabelelor, cum ar fi cele care conțin date despre clienți, angajați sau de vânzări, veți dori cheia primară a tabelului, pentru a o utiliza la crearea de relații în model.

Tabelele faptice sunt diferite. Într-un tabel faptic, cheia primară este utilizată pentru a identifica în mod unic fiecare rând. Deși este necesară în scopuri de normalizare, este mai puțin utilă într-un model de date în care doriți numai acele coloane utilizate pentru analiză sau pentru a stabili relații în tabel. Din acest motiv, când importați dintr-un tabel faptic, nu includeți cheia sa primară. Cheile primare dintr-un tabel faptic consumă foarte mult spațiu din model și nu asigură niciun avantaj, pentru că nu pot fi utilizate pentru crearea de relații.

Notă: În depozitele de date și bazele de date multidimensionale, tabelele mari care conțin în majoritate date numerice sunt denumite deseori „tabele faptice”. Tabelele faptice includ de obicei informații despre operarea afacerii sau date despre tranzacții, cum ar fi date de vânzări și date despre costuri care sunt agregate și aliniate în unități organizaționale, produse, segmente de piață, regiuni geografice etc. Toate coloanele dintr-un tabel faptic care conțin date de afaceri sau care pot fi utilizate pentru a crea o referință încrucișată a datelor stocate în alte tabele ar trebui incluse în model, pentru a permite analiza datelor. Coloana pe care doriți să o excludeți este coloana cheii primare a tabelului faptic, care constă din valori unice care există numai în tabelul faptic și nicăieri altundeva. Deoarece tabelele faptice sunt atât de mari, unele dintre cele mai mari câștiguri în ce privește eficiența modelului sunt derivate din excluderea rândurilor sau coloanelor din tabelele faptice.

Cum să excludeți coloanele care nu sunt necesare

Modele eficient conține numai acele coloane care trebuie fapt din registrul de lucru. Dacă doriți să controlați ce coloane sunt incluse în modelul, va trebui să utilizați Expertul Import tabel în Power Pivot completare să importați datele , mai degrabă decât de caseta de dialog "Import date" în Excel.

Când porniți Table import Wizard, selectați ce tabele să importați.

Table Import Wizard în programul de completare PowerPivot

Pentru fiecare tabel, puteți să faceți clic pe butonul Preview & Filter și să selectați părțile din tabel de care aveți nevoie. Vă recomandăm mai întâi să debifați toate coloanele, apoi să continuați să bifați coloanele pe care le doriți, după ce decideți dacă sunt necesare pentru analiză.

Panoul Preview în Table import Wizard

Ce se întâmplă cu filtrarea doar la rândurile necesare?

Multe tabele din bazele de date de afaceri și depozitele de date conțin date istorice acumulate pe perioade lungi de timp. În plus, s-ar putea să aflați că tabele de care sunteți interesat conțin informații pentru porțiuni din activitate care nu sunt necesare pentru analiza specifică.

Utilizând Table Import Wizard, puteți să filtrați datele istorice sau neasociate și astfel să salvați mult spațiu în model. În următoarea imagine, este utilizat un filtru de date pentru a regăsi numai rândurile care conțin date pentru anul curent, excluzând datele istorice de care nu este nevoie.

Panoul Filter în Table Import Wizard

Ce se întâmplă dacă avem nevoie de coloană; putem reduce în continuare spațiul ocupat de aceasta?

Există câteva tehnici suplimentare pe care le puteți aplica pentru a face o coloană să devină un candidat mai bun pentru compresie. Rețineți că singura caracteristică a coloanei care afectează compresia este numărul de valori unice. În această secțiune, veți afla cum pot fi modificate anumite coloane pentru a reduce numărul de valori unice.

Modificarea coloanelor de tip dată/oră

În multe cazuri, coloanele de tip dată/oră ocupă mult spațiu. Din fericire, există câteva metode 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 pricepere în ce privește construirea interogărilor SQL.

Coloanele dată-oră conțin o parte de dată și o oră. Când vă întrebați dacă aveți nevoie de o coloană, puneți-vă aceeași întrebare de mai multe ori, pentru o coloană dată/oră:

  • Am nevoie de porțiunea de oră?

  • Am nevoie de porțiunea de oră la nivelul de oră, minute, secunde sau milisecunde?

  • Am mai multe coloane dată-oră deoarece vreau să calculez diferența dintre ele sau doar să agreg datele după an, lună, trimestru etc.

Modul în care răspundeți la aceste întrebări determină opțiunile dvs. pentru modul în care gestionați coloana de tip dată/oră.

Toate aceste soluții necesită modificarea unei interogări SQL. Pentru a facilita 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ării dintr-un format prescurtat (SELECT *) la o instrucțiune SELECT care include nume de coloană complet calificate, care sunt mult mai ușor de modificat.

Să aruncăm o privire asupra interogărilor care sunt create pentru dvs. Din caseta de dialog Proprietăți tabel, puteți să treceți la Query editor (Editor 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 Table Properties, selectați Query Editor.

Deschideți Query Editor din dialogul Table Properties

Query Editor afișează interogarea SQL utilizată pentru popularea tabelului. Dacă ați filtrat toate coloanele î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 vreun filtru, veți vedea interogarea ca „Select * from”, ceea ce va fi mult mai greu de modificat:

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

Modificarea interogării SQL

Știți cum să găsiți interogarea, deci puteți să o modificați pentru a reduce și mai mult dimensiunea modelului.

  1. Pentru coloanele care conțin valori monetare sau date zecimale, dacă nu vă trebuie zecimalele, utilizați această sintaxă pentru a renunța la zecimale:

    „SELECT ROUND([Nume_coloană_zecimală],0)… .”

    Dacă vă trebuie banii, dar nu și fracțiuni din aceștia, înlocuiți 0 cu 2. Dacă utilizați numere negative, puteți să le rotunjiți la unități, zeci, sute etc.

  2. Dacă aveți o coloană de tip dată-oră numită dbo.Tabelmare.[Dată Oră] și nu aveți nevoie de porțiunea Oră, utilizați sintaxa pentru a renunța la oră:

    „SELECT CAST (dbo.Tabelmare.[Dată oră] as date) AS [Dată oră])”

  3. Dacă aveți o coloană tip dată-oră numită dbo.Tabelmare.[Dată Oră] și vă trebuie atât partea Dată, cât și partea Oră, utilizați mai multe coloane în interogarea SQL, în loc de o singură coloană dată-oră:

    “SELECT CAST (dbo.Tabelmare.[Dată Oră] as date ) AS [Dată Oră],

    datepart(hh, dbo.Tabelmare.[Dată Oră]) as [Dată Oră Ore],

    datepart(mi, dbo.Tabelmare.[Dată Oră]) as [Dată Oră Minute],

    datepart(ss, dbo.Tabelmare.[Dată Oră]) as [Dată Oră Secunde],

    datepart(ms, dbo.Tabelmare.[Dată Oră]) as [Dată Oră Milisecunde]”

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

  4. Dacă aveți nevoie de ore și minute și preferați să apară împreună, ca o singură coloană de oră, puteți să utilizați sintaxa:

    Timefromparts(datepart(hh, dbo.Tabelmare.[Dată Oră]), datepart(mm, dbo.Tabelmare.[Dată Oră])) as [Dată Oră OrăMinut]

  5. Dacă aveți două coloane dată-oră, de exemplu, [Data de început] și [Data de sfârșit] și ceea ce vă trebuie este diferența de oră dintre ele în secunde, sub forma unei coloane numite [Durată], eliminați ambele coloane din listă și adăugați:

    „datediff(ss,[Data de început],[Data 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, s-ar putea să știți deja că coloanele calculate se utilizează pentru a deriva coloane noi bazate pe altă coloană din model, în timp ce măsurile calculate sunt definite o dată în model, dar sunt evaluate numai când sunt utilizate într-un raport PivotTable sau alt tip de raport.

O tehnică prin care se economisește memorie este înlocuirea coloanelor obișnuite sau calculate cu măsurători calculate. Exemplul clasic este Preț Unitar, Cantitate și Total. Dacă le aveți pe toate trei, puteți să economisiți spațiu prin păstrarea a două coloane și calcularea celei de a treia cu ajutorul DAX.

Care sunt cele două coloane pe care ar trebui să le păstrați?

În exemplul de mai sus, păstrați Cantitate și Preț Unitar. Acestea două au mai puține valori decât coloana Total. Pentru a calcula coloana Total, adăugați o măsurătoare calculată, precum:

„TotalVânzări:=sumx(‘Tabel Vânzări’,’Tabel Vânzări’[Preț unitar]*’Tabel Vânzări’[Cantitate])”

Coloanele calculate seamănă cu coloanele obișnuite, în sensul că ambele ocupă spațiu în model. În schimb, măsurătorile calculate sunt calculate pe loc și nu ocupă spațiu.

Concluzie

În acest articol, am vorbit despre câteva abordări care vă pot ajuta să construiți un model care să ocupe mai puțină memorie. Modul de a reduce dimensiunea fișierului și cerințele de memorie a modelului 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 despre care am vorbit:

  • Eliminarea coloanelor este, desigur, cea mai bună metodă de a economisi spațiu. Hotărâți de care dintre coloane aveți nevoie cu adevărat.

  • Uneori puteți să eliminați o coloană și să o înlocuiți în tabel cu o măsurătoare calculată.

  • S-ar putea să nu aveți nevoie de toate rândurile dintr-un tabel. Puteți să filtrați rândurile în Table Import Wizard.

  • În general, împărțirea unei singure coloane în mai multe părți distincte este o metodă bună de a reduce numărul de valori unice din coloană. Fiecare dintre părți va avea un număr mai mic de valori unice, iar totalul combinat va fi mai mic decât coloana originală unificată.

  • În multe cazuri, veți avea nevoie și de părțile distincte pe care să le utilizați ca slicere în rapoarte. Când este cazul, puteți să creați ierarhii din părți precum Ore, Minute și Secunde.

  • De multe ori, coloanele conțin mai multe informații decât este necesar. De exemplu, să presupunem că o coloană stochează zecimale, dar ați aplicat o formatare pentru a ascunde toate zecimalele. Rotunjirea poate fi foarte eficientă în reducerea dimensiunii unei coloane numerice.

Acum că ați făcut ceea ce se poate pentru a reduce dimensiunea registrului de lucru, luați în considerare, de asemenea, rulează Optimizatorul pentru dimensiunea registru de lucru. Vă analizează registrul de lucru Excel și dacă este posibil, se comprimă în continuare. Descărcați Optimizatorul pentru dimensiunea registru de lucru.

Linkuri utile

Specificațiile și limitele modelului de date

Descărcați Optimizatorul pentru dimensiunea registru 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.

×