I Excel kan du opprette datamodeller som inneholder millioner av rader, og deretter utføre kraftig dataanalyse mot disse modellene. Datamodeller kan opprettes med eller uten Power Pivot-tillegget for å støtte et hvilket som helst antall pivottabeller, diagrammer og Power View-visualiseringer i samme arbeidsbok.
Selv om du enkelt kan bygge store datamodeller i Excel, er det flere grunner til ikke å gjøre det. For det første er store modeller som inneholder store mengder tabeller og kolonner overkill for de fleste analyser, og utgjør en tungvint feltliste. For det andre bruker store modeller verdifullt minne, noe som har en negativ innvirkning på andre programmer og rapporter som deler de samme systemressursene. I Microsoft 365 begrenser både SharePoint Online og Excel Online størrelsen på en Excel-fil til 10 MB. Når det gjelder arbeidsbokdatamodeller som inneholder millioner av rader, vil du støte på en grense på 10 MB ganske raskt. Se Datamodellspesifikasjoner og -grenser.
I denne artikkelen lærer du hvordan du bygger en tett konstruert modell som er enklere å arbeide med og bruker mindre minne. Å ta seg tid til å lære anbefalte fremgangsmåter for effektiv modellutforming vil lønne seg på veien for alle modeller du oppretter og bruker, enten du viser den i Excel, Microsoft 365 SharePoint Online, på en Office Web Apps Server eller i SharePoint.
Vurder også å kjøre optimaliseringen for arbeidsbokstørrelse. Den analyserer Excel-arbeidsboken og komprimerer den om mulig ytterligere. Last ned optimaliseringen for arbeidsbokstørrelse.
I denne artikkelen
Ingenting slår en ikke-eksisterende kolonne for lavt minnebruk
Hva om vi trenger kolonnen; Kan vi fortsatt redusere plasskostnadene?
Komprimeringsforhold og analysemotoren i minnet
Datamodeller i Excel bruker analysemotoren i minnet til å lagre data i minnet. Motoren implementerer kraftige kompresjonsteknikker for å redusere lagringsbehovet, og krymper et resultatsett til det er en brøkdel av sin opprinnelige størrelse.
I gjennomsnitt kan du forvente at en datamodell er 7 til 10 ganger mindre enn de samme dataene på opprinnelsesstedet. Hvis du for eksempel importerer 7 MB data fra en SQL Server-database, kan datamodellen i Excel lett være 1 MB eller mindre. Graden av komprimering som faktisk er oppnådd, avhenger først og fremst av antallet unike verdier i hver kolonne. Jo flere unike verdier, jo mer minne kreves for å lagre dem.
Hvorfor snakker vi om komprimering og unike verdier? Fordi det å bygge en effektiv modell som minimerer minnebruken, handler om komprimeringsmaksimering, og den enkleste måten å gjøre dette på, er å kvitte seg med alle kolonner du egentlig ikke trenger, spesielt hvis disse kolonnene inneholder et stort antall unike verdier.
Obs!
Forskjellene i lagringskrav for individuelle kolonner kan være enorme. I noen tilfeller er det bedre å ha flere kolonner med et lavt antall unike verdier i stedet for én kolonne med et høyt antall unike verdier. Delen om optimalisering av dato/klokkeslett dekker denne teknikken i detalj.
Ingenting slår en ikke-eksisterende kolonne for lavt minnebruk
Den kolonnen som bruker mest minne, er den du aldri importerte i utgangspunktet. Hvis du vil bygge en effektiv modell, bør du se på hver kolonne og spørre deg selv om den bidrar til analysen du vil utføre. Hvis det ikke gjør det eller du ikke er sikker, kan du utelate det. Du kan alltids legge til nye kolonner senere hvis du trenger dem.
To eksempler på kolonner som alltid bør utelates
Det første eksemplet er knyttet til data som kommer fra et datavarehus. I et datalager er det vanlig å finne artefakter av ETL-prosesser som laster inn og oppdaterer data i lageret. Kolonner som «opprettingsdato», «oppdateringsdato» og «ETL-kjøring» opprettes når dataene lastes inn. Ingen av disse kolonnene er nødvendige i modellen, og det bør ikke velges når du importerer data.
Det andre eksemplet handler om å utelate primærnøkkelkolonnen når du importerer en faktatabell.
Mange tabeller, inkludert faktatabeller, har primærnøkler. For de fleste tabeller, for eksempel de som inneholder data om kunder, ansatte eller salg, trenger du tabellens primærnøkkel slik at du kan bruke den til å opprette relasjoner i modellen.
Faktatabeller er forskjellige. I en faktatabell brukes primærnøkkelen til å identifisere hver rad unikt. Selv om det er nødvendig for normaliseringsformål, er det mindre nyttig i en datamodell der du vil at bare de kolonnene skal brukes til analyse eller til å etablere tabellrelasjoner. Når du importerer fra en faktatabell, må du derfor ikke inkludere primærnøkkelen. Primærnøkler i en faktatabell bruker enorme mengder plass i modellen, men gir ingen fordeler siden de ikke kan brukes til å opprette relasjoner.
Obs!
I datalagre og flerdimensjonale databaser blir store tabeller som for det meste består av numeriske data, ofte referert til som «faktatabeller». Faktatabeller inneholder vanligvis forretningsytelses- eller transaksjonsdata, for eksempel salgs- og kostnadsdatapunkter som er aggregert og justert etter organisasjonsenheter, produkter, markedssegmenter, geografiske områder og så videre. Alle kolonnene i en faktatabell som inneholder forretningsdata eller som kan brukes til å kryssreferere data som er lagret i andre tabeller, bør inkluderes i modellen for å støtte dataanalyse. Kolonnen du vil utelate, er primærnøkkelkolonnen i faktatabellen, som består av unike verdier som bare finnes i faktatabellen og ingen andre steder. Siden faktatabeller er så store, er noen av de største gevinstene i modelleffektivitet avledet av å ekskludere rader eller kolonner fra faktatabeller.
Slik utelater du unødvendige kolonner
Effektive modeller inneholder bare de kolonnene du faktisk trenger i arbeidsboken. Hvis du vil kontrollere hvilke kolonner som er inkludert i modellen, må du bruke veiviseren for tabellimport i Power Pivot-tillegget til å importere dataene i stedet for dialogboksen «Importer data» i Excel.
Når du starter veiviseren for tabellimport, kan du velge hvilke tabeller som skal importeres.
For hver tabell kan du klikke Forhåndsvis & Filtrer-knappen og velge de delene av tabellen som du virkelig trenger. Vi anbefaler at du først fjerner merket for alle kolonnene, og deretter fortsetter med å kontrollere kolonnene du vil bruke, etter å ha vurdert om de er nødvendige for analysen.
Hva med å filtrere bare de nødvendige radene?
Mange tabeller i bedrifters databaser og datalagre inneholder historiske data akkumulert over lange tidsperioder. I tillegg kan det hende at tabellene du er interessert i, inneholder informasjon for områder av virksomheten som ikke er nødvendig for den bestemte analysen.
Ved hjelp av veiviseren for tabellimport kan du filtrere ut historiske eller ikke-relaterte data, og dermed spare mye plass i modellen. I bildet nedenfor brukes et datofilter til å hente bare rader som inneholder data for gjeldende år, og unntatt historiske data som ikke trengs.
Hva om vi trenger kolonnen; Kan vi fortsatt redusere plasskostnadene?
Det finnes noen flere teknikker du kan bruke for å gjøre en kolonne til en bedre kandidat for komprimering. Husk at den eneste egenskapen til kolonnen som påvirker komprimering, er antall unike verdier. I denne delen lærer du hvordan enkelte kolonner kan endres for å redusere antallet unike verdier.
Endre dato/klokkeslett-kolonner
I mange tilfeller tar dato/klokkeslett-kolonner opp mye plass. Heldigvis finnes det en rekke måter å redusere lagringskravene for denne datatypen på. Teknikkene varierer avhengig av hvordan du bruker kolonnen og hvor komfortabel du er med når du bygger SQL-spørringer.
Dato/klokkeslett-kolonnene inneholder en datodel og et klokkeslett. Når du spør deg selv om du trenger en kolonne, kan du stille det samme spørsmålet flere ganger for en Datetime-kolonne:
- Trenger jeg tidsdelen?
- Må jeg ha tidsdelen på timenivå? , minutter? , sekunder? , millisekunder?
- Har jeg flere dato/klokkeslett-kolonner fordi jeg vil beregne forskjellen mellom dem eller bare aggregere dataene etter år, måned, kvartal og så videre?
Hvordan du svarer på hvert av disse spørsmålene, bestemmer alternativene for behandling av Datetime-kolonnen.
Alle disse løsningene krever endring av en SQL-spørring. Hvis du vil gjøre spørringsendring enklere, bør du filtrere ut minst én kolonne i hver tabell. Ved å filtrere bort en kolonne endrer du spørringskonstruksjonen fra et forkortet format (SELECT *) til en SELECT-setning som inneholder fullstendige kolonnenavn, som er langt enklere å endre.
La oss ta en titt på spørringene som er opprettet for deg. Fra dialogboksen Tabellegenskaper kan du bytte til redigeringsprogrammet for spørring og se gjeldende SQL-spørring for hver tabell.
Velg Power Query-redigering fra Tabellegenskaper.
Power Query-redigering viser SQL-spørringen som brukes til å fylle ut tabellen. Hvis du filtrerte ut en kolonne under import, inneholder spørringen fullstendig kvalifiserte kolonnenavn:
Hvis du derimot importerte en tabell i sin helhet, uten å fjerne merket for noen kolonne eller bruke et filter, vil du se spørringen som «Velg * fra », som vil være vanskeligere å endre:
|
|---|
Endre SQL-spørringen
Nå som du vet hvordan du finner spørringen, kan du endre den for å redusere størrelsen på modellen ytterligere.
- Hvis du ikke trenger desimaler for kolonner som inneholder valuta- eller desimaldata, kan du bruke denne syntaksen til å bli kvitt desimalene:
«VELG AVRUND([Decimal_column_name],0) ... .”
Hvis du trenger cent, men ikke brøkdeler av cent, erstatter du 0 med 2. Hvis du bruker negative tall, kan du runde av til enheter, tiere, hundre og så videre. - Hvis du har en Datetime-kolonne som heter dbo. Stor tabell. [Dato klokkeslett] og du ikke trenger klokkeslettdelen, bruker du syntaksen til å bli kvitt klokkeslettet:
«VELG ROLLEBESETNING (dbo. Stor tabell. [Dato klokkeslett], som dato) AS [Datoklokkeslett]) " - Hvis du har en Datetime-kolonne som heter dbo. Stor tabell. [Datoklokkeslett] og du trenger begge dato- og klokkeslettdelene, kan du bruke flere kolonner i SQL-spørringen i stedet for én enkelt Datetime-kolonne:
«VELG ROLLEBESETNING (dbo. Stor tabell. [Dato klokkeslett] som dato ) AS [Dato og klokkeslett],
DatePart(hh, dbo. Stor tabell. [Dato og klokkeslett]) som [Dato og klokkeslett, Timer],
DatePart(mi; dbo. Stor tabell. [Dato og klokkeslett]) som [dato tid minutter],
DatePart(ss, dbo. Stor tabell. [Dato og klokkeslett]) som [dato og klokkeslett sekunder],
DatePart(ms, dbo. Stor tabell. [Dato og klokkeslett]) som [Dato, klokkeslett, millisekunder]»
Bruk så mange kolonner du trenger for å lagre hver del i separate kolonner. - Hvis du trenger timer og minutter, og du foretrekker dem sammen som én tidskolonne, kan du bruke syntaksen :
Timefromparts(datepart(hh, dbo. Stor tabell. [Dato klokkeslett]), DatePart(mm, dbo. Stor tabell. [Dato og klokkeslett])) som [Dato, Klokkeslett, TimeMinutt] - Hvis du har to dato/klokkeslett-kolonner, for eksempel [Starttidspunkt] og [Sluttidspunkt], og det du egentlig trenger er tidsforskjellen mellom dem i sekunder som en kolonne kalt [Varighet], fjerner du begge kolonnene fra listen og legger til:
"datediff(ss,[Startdato],[Sluttdato]) som [varighet]"
Hvis du bruker nøkkelordet ms i stedet for ss, får du varigheten i millisekunder
Bruke DAX-beregnede mål i stedet for kolonner
Hvis du har arbeidet med DAX-uttrykksspråk tidligere, vet du kanskje allerede at beregnede kolonner brukes til å avlede nye kolonner basert på andre kolonner i modellen, mens beregnede mål defineres én gang i modellen, men evalueres bare når de brukes i en pivottabell eller en annen rapport.
Én minnebesparende teknikk er å erstatte vanlige eller beregnede kolonner med beregnede mål. Det klassiske eksemplet er Enhetspris, Antall og Totalsum. Hvis du har alle tre, kan du spare plass ved å beholde bare to og beregne den tredje ved hjelp av DAX.
Hvilke 2 kolonner bør du beholde?
I eksemplet ovenfor beholder du Antall og Enhetspris. Disse to har færre verdier enn totalsummen. Hvis du vil beregne totalen, kan du legge til et beregnet mål som:
"TotalSales:=sumx('Salgstabell','Salgstabell'[Enhetspris]*'Salgstabell'[Antall])"
Beregnede kolonner er som vanlige kolonner ved at begge tar opp plass i modellen. Beregnede mål beregnes på farten og tar ikke plass.
Konklusjon
I denne artikkelen snakket vi om flere tilnærminger som kan hjelpe deg med å bygge en mer minneeffektiv modell. Du kan redusere filstørrelsen og minnekravene til en datamodell ved å redusere det totale antallet kolonner og rader og antallet unike verdier som vises i hver kolonne. Her er noen teknikker vi dekket:
- Fjerning av kolonner er selvsagt den beste måten å spare plass på. Bestem deg for hvilke kolonner du virkelig trenger.
- Noen ganger kan du fjerne en kolonne og erstatte den med et beregnet mål i tabellen.
- Du trenger kanskje ikke alle radene i en tabell. Du kan filtrere bort rader i veiviseren for tabellimport.
- Å dele opp en enkelt kolonne i flere distinkte deler er generelt sett en god metode for å redusere antall unike verdier i en kolonne. Hver av delene vil ha et lite antall unike verdier, og den samlede summen vil være mindre enn den opprinnelige enhetlige kolonnen.
- I mange tilfeller trenger du også de distinkte delene for å bruke dem som slicere i rapportene. Når det er aktuelt, kan du opprette hierarkier fra deler som Timer, Minutter og Sekunder.
- Mange ganger inneholder kolonner mer informasjon enn du trenger dem også. Anta for eksempel at en kolonne lagrer desimaler, men du har brukt formatering for å skjule alle desimalene. Avrunding kan være svært effektivt når du skal redusere størrelsen på en numerisk kolonne.
Nå som du har gjort det du kan for å redusere størrelsen på arbeidsboken, bør du vurdere å også kjøre optimaliseringen for arbeidsbokstørrelse. Den analyserer Excel-arbeidsboken og komprimerer den om mulig ytterligere. Last ned optimaliseringen for arbeidsbokstørrelse.
Beslektede koblinger
Datamodell – spesifikasjoner og grenser