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 mengder av tabeller og kolonner overkill for de fleste analyser, og gir en tungvint feltliste. For det andre bruker store modeller opp verdifullt minne, noe som påvirker andre programmer og rapporter som deler de samme systemressursene negativt. Til slutt, i Microsoft 365, begrenser både SharePoint Online og Excel Web App størrelsen på en Excel-fil til 10 MB. For arbeidsbokdatamodeller som inneholder millioner av rader, vil du kjøre inn i grensen på 10 MB ganske raskt. Se spesifikasjonene og grensene for datamodellen.
I denne artikkelen lærer du hvordan du bygger en tett konstruert modell som er enklere å arbeide med og bruker mindre minne. Hvis du tar deg tid til å lære anbefalte fremgangsmåter i effektiv modellutforming, vil det lønne seg nedover veien for alle modeller du oppretter og bruker, enten du viser den i Excel, Microsoft 365 SharePoint Online, på en Office Online Server eller i SharePoint.
Vurder også å kjøre optimaliseringen for arbeidsbokstørrelse. Den analyserer Excel-arbeidsboken og komprimerer den om mulig ytterligere. Last ned optimalisering av arbeidsbokstørrelse.
I denne artikkelen
Komprimeringsforhold og analysemotoren i minnet
Datamodeller i Excel bruker analysemotoren i minnet til å lagre data i minnet. Motoren implementerer kraftige komprimeringsteknikker for å redusere lagringskravene, noe som krymper et resultatsett til det er en brøkdel av den opprinnelige størrelsen.
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 enkelt være 1 MB eller mindre. Graden av komprimering som faktisk oppnås, avhenger hovedsakelig av antall 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 det om komprimeringsmaksimering, og den enkleste måten å gjøre dette på er å kvitte seg med kolonner du egentlig ikke trenger, spesielt hvis disse kolonnene inneholder et stort antall unike verdier.
: Forskjellene i lagringskravene for individuelle kolonner kan være store. 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 Datetime-optimaliseringer dekker denne teknikken i detalj.
Ingenting slår en ikke-eksisterende kolonne for lite minnebruk
Den mest minnebesparende kolonnen er den du aldri importerte i utgangspunktet. Hvis du vil bygge en effektiv modell, kan 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, utelater du det. Du kan alltid legge til nye kolonner senere hvis du trenger dem.
To eksempler på kolonner som alltid skal utelates
Det første eksemplet er knyttet til data som kommer fra et datalager. I et datalager er det vanlig å finne artefakter av ETL-prosesser som laster inn og oppdaterer data i lageret. Kolonner som «opprett dato», «oppdateringsdato» og «ETL-kjøring» opprettes når dataene lastes inn. Ingen av disse kolonnene er nødvendig i modellen og bør fjernes når du importerer data.
Det andre eksemplet innebærer å 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 kunde-, ansatt- eller salgsdata, vil du ha primærnøkkelen for tabellen, 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 for å 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 fordel, da de ikke kan brukes til å opprette relasjoner.
: I datalagre og flerdimensjonale databaser kalles store tabeller som består av hovedsakelig numeriske data, ofte faktatabeller. Faktatabeller inkluderer vanligvis forretningsytelse eller transaksjonsdata, for eksempel datapunkter for salg og kostnader som aggregeres og justeres 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. Fordi faktatabeller er så store, er noen av de største gevinstene i modelleffektivitet avledet fra å 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, velger du hvilke tabeller som skal importeres.
For hver tabell kan du klikke forhåndsvisningsknappen & filter og velge de delene av tabellen du virkelig trenger. Vi anbefaler at du først fjerner alle kolonnene, og deretter fortsetter du for å kontrollere kolonnene du ønsker, etter at du har vurdert om de er nødvendige for analysen.
Hva med å filtrere bare de nødvendige radene?
Mange tabeller i firmadatabaser og datalagre inneholder historiske data akkumulert over lange perioder. I tillegg kan det hende at tabellene du er interessert i, inneholder informasjon for områder i virksomheten som ikke er nødvendige for din spesifikke analyse.
Ved hjelp av veiviseren for tabellimport kan du filtrere ut historiske eller ikke-relaterte data, og dermed spare mye plass i modellen. I illustrasjonen nedenfor brukes et datofilter til å hente bare rader som inneholder data for gjeldende år, unntatt historiske data som ikke er nødvendige.
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 noen kolonner kan endres for å redusere antall unike verdier.
Endre Datetime-kolonner
I mange tilfeller tar Datetime-kolonner mye plass. Heldigvis finnes det flere måter å redusere lagringskravene for denne datatypen på. Teknikkene varierer avhengig av hvordan du bruker kolonnen, og komfortnivået ditt når du bygger SQL-spørringer.
Datetime-kolonner inkluderer 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?
-
Trenger jeg tidsdelen på timenivået? referat? Sekunder? Millisekunder?
-
Har jeg flere Datetime-kolonner fordi jeg vil beregne forskjellen mellom dem, eller bare for å aggregere dataene etter år, måned, kvartal og så videre.
Hvordan du svarer på hvert av disse spørsmålene, bestemmer alternativene for å håndtere 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 ut 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. I dialogboksen Tabellegenskaper kan du bytte til redigeringsprogrammet for spørring og se gjeldende SQL-spørring for hver tabell.
Velg Redigeringsprogram for spørring fra Tabellegenskaper.
Redigeringsprogrammet for spørring viser SQL-spørringen som brukes til å fylle ut tabellen. Hvis du filtrerte ut en kolonne under import, inneholder spørringen fullstendige kolonnenavn:
|
Hvis du derimot importerte en tabell i sin helhet, uten å fjerne merket for en kolonne eller bruke et filter, ser du spørringen som «Velg * fra », noe som blir 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 å fjerne desimaler:
"VELG AVRUND([Decimal_column_name],0)... .”
Hvis du trenger cent, men ikke brøker av cent, erstatte 0 med 2. Hvis du bruker negative tall, kan du runde av til enheter, titalls, hundrevis osv.
-
Hvis du har en Datetime-kolonne med navnet dbo. Bigtable. [Dato/klokkeslett] og du ikke trenger Klokkeslett-delen, bruker du syntaksen for å bli kvitt klokkeslettet:
"SELECT CAST (dbo. Bigtable. [Date time] as date) AS [Date time]) "
-
Hvis du har en Datetime-kolonne med navnet dbo. Bigtable. [Dato/klokkeslett] og du trenger både dato- og klokkeslettdelene, bruker du flere kolonner i SQL-spørringen i stedet for den enkle Datetime-kolonnen:
"SELECT CAST (dbo. Bigtable. [Date Time] as date ) AS [Date Time],
datepart(hh; dbo. Bigtable. [Dato/klokkeslett]) som [Date Time Hours],
datepart(mi; dbo. Bigtable. [Dato/klokkeslett]) som [Date Time Minutes],
datepart(ss; dbo. Bigtable. [Dato/klokkeslett]) som [Date Time Seconds],
datepart(ms; dbo. Bigtable. [Dato/klokkeslett]) as [Date Time Milliseconds]"
Bruk så mange kolonner som du trenger for å lagre hver del i separate kolonner.
-
Hvis du trenger timer og minutter, og du foretrekker dem sammen som engangskolonne, kan du bruke syntaksen:
Timefromparts(datepart(hh, dbo. Bigtable. [Date Time]), datepart(mm, dbo. Bigtable. [Dato/klokkeslett])) som [Date Time HourMinute]
-
Hvis du har to datetime-kolonner, for eksempel [Starttidspunkt] og [Sluttidspunkt], og det du virkelig trenger, er tidsforskjellen mellom dem i sekunder som en kolonne kalt [Varighet], fjerner du begge kolonnene fra listen og legger til:
"datediff(ss,[Start date],[End Date]) as [Duration]"
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åket tidligere, vet du kanskje allerede at beregnede kolonner brukes til å avlede nye kolonner basert på en annen kolonne i modellen, mens beregnede mål defineres én gang i modellen, men evalueres bare når de brukes i en pivottabell eller annen rapport.
Én minnebesparende teknikk er å erstatte vanlige eller beregnede kolonner med beregnede mål. Det klassiske eksemplet er Enhetspris, Antall og Totalt. Hvis du har alle tre, kan du spare plass ved å vedlikeholde bare to og beregne den tredje ved hjelp av DAX.
Hvilke to kolonner bør du beholde?
Behold Antall og Enhetspris i eksemplet ovenfor. Disse to har færre verdier enn totalen. Hvis du vil beregne totalsum, legger du til et beregnet mål som:
"Totalt salg:=sumx('Salgstabell','Salgstabell'[Enhetspris]*'Salgstabell'[Antall])"
Beregnede kolonner er som vanlige kolonner ved at begge tar opp plass i modellen. Beregnede mål beregnes derimot umiddelbart og tar ikke plass.
Konklusjon
I denne artikkelen snakket vi om flere tilnærminger som kan hjelpe deg med å bygge en mer minneeffektiv modell. Måten å redusere filstørrelsen og minnekravene til en datamodell på, er å redusere det totale antallet kolonner og rader, og antall unike verdier som vises i hver kolonne. Her er noen teknikker vi har dekket:
-
Fjerning av kolonner er selvfølgelig den beste måten å spare plass på. Bestem 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 ut rader i veiviseren for tabellimport.
-
Generelt sett er det å dele opp én enkelt kolonne i flere distinkte deler en god måte å redusere antall unike verdier i en kolonne på. Hver av delene vil ha et lite antall unike verdier, og den kombinerte 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 desimaler. Avrunding kan være svært effektiv for å redusere størrelsen på en numerisk kolonne.
Nå som du har gjort det du kan for å redusere størrelsen på arbeidsboken, bør du også vurdere å kjøre optimalisering av arbeidsbokstørrelse. Den analyserer Excel-arbeidsboken og komprimerer den om mulig ytterligere. Last ned optimalisering av arbeidsbokstørrelse.
Beslektede koblinger
Datamodell – spesifikasjoner og grenser
