Opprette en minnebesparende datamodell ved hjelp av Excel og PowerPivot-tillegget

Viktig: Denne artikkelen er maskinoversatt, se ansvarsfraskrivelsen. Du finner den engelske versjonen av artikkelen her som referanse.

I Excel 2013 eller nyere, kan du opprette datamodeller som inneholder flere millioner rader, og deretter utføre kraftig dataanalyse mot disse modellene. Du kan opprette datamodeller med eller uten Power Pivot tillegget for å støtte en hvilken som helst antall pivottabeller, diagrammer og visualiseringer av Power View i samme arbeidsbok.

Merknad: Denne artikkelen beskriver datamodeller i Excel 2013. Imidlertid gjelder samme datamodellering og PowerPivot-funksjoner som er satt inn i Excel 2013 også for Excel 2016. Det er effektivt liten forskjell mellom disse versjonene av Excel.

Selv om du kan enkelt bygge store datamodeller i Excel, finnes det flere grunner ikke til. Første, store modeller som inneholder multitudes over tabeller og kolonner er overkill for de fleste analyser, og foreta for en tungvint Feltliste. Andre, store modeller bruke opp verdifull minne, ha negativ påvirke andre programmer og rapporter som deler samme systemressurser. Til slutt i Office 365 både SharePoint Online og Excel Web App kan du begrense størrelsen på en Excel-fil til 10 MB. For datamodeller for arbeidsbok som inneholder flere millioner rader, oppstår grensen 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. Hvis du tar deg tid til å lære anbefalte fremgangsmåter i effektiv modellutforming, får du igjen for dette senere uansett hvilken modell du lager og bruker, enten du viser den i Excel 2013, Office 365 SharePoint Online, på en Office Online-server eller i SharePoint 2013.

Vurder også kjører optimering av arbeidsbokstørrelse. Analyseres Excel-arbeidsboken, og komprimerer om mulig ytterligere. Last ned optimering av arbeidsbokstørrelse.

I denne artikkelen

Komprimeringsforhold og Analysemotoren i minnet

Ingenting slår en ikke-eksisterende kolonne for lite bruk av minne

To eksempler på kolonner som alltid bør utelates

Utelate unødvendige kolonner

Hva med å filtrere bare de nødvendige radene?

Hva skjer hvis vi trenger kolonnen. kan vi likevel redusere minnebruken?

Endre Datetime-kolonner

Endre SQL-spørringen

Bruke DAX-beregnede mål i stedet for kolonner

Hvilke 2 kolonner skal du beholde?

Avslutning

Beslektede koblinger

Komprimeringsforhold og analysemotoren i minnet

Datamodeller i Excel bruker analysemotoren i minnet til å lagre data i minnet. Motoren bruker kraftige komprimeringsmetoder til å redusere lagringskrav og krymper et resultatsett til det er en brøkdel av sin opprinnelige størrelse.

Du kan forvente at datamodeller i gjennomsnitt blir sju til ti ganger mindre enn de opprinnelige dataene. Hvis du for eksempel importerer 7 MB med data fra en SQL Server-database, kan datamodellen i Excel enkelt bli 1 MB eller mindre. Komprimeringsgraden som faktisk oppnås, er primært avhengig av antallet unike verdier i hver kolonne. Jo flere unike verdier det er, desto mer minne kreves til å lagre dem.

Hvorfor snakker vi om komprimering og unike verdier? Fordi å bygge en effektiv modell som minimerer minnebruken, handler om å maksimere komprimeringen, og den enkleste måten å gjøre dette på er å bli kvitt alle kolonner du egentlig ikke trenger, særlig hvis disse kolonnene inneholder et stort antall unike verdier.

Merknad: Det kan være enorme forskjeller i kravene til lagringsplass for enkeltkolonner. I enkelte tilfeller er det bedre å ha flere kolonner med et lavt antall unike verdier enn én kolonne med et høyere antall unike verdier. Delen om Datetime-optimalisering tar for seg denne metoden i detalj.

Ingenting slår en ikke-eksisterende kolonne når det gjelder lite bruk av minne

Den mest minnebesparende kolonnen er den du ikke importerte i første omgang. Hvis du vil bygge en effektiv modell, ser du på hver kolonne og spør deg selv om den bidrar til analysen du vil utføre. Hvis den ikke gjør det eller du ikke er sikker, utelater du den. 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 har å gjøre med data som opprinnelig kommer fra et datalager. I et datalager er det vanlig å finne artefakter fra ETL-prosessene som laster og oppdaterer data på lageret. Kolonner som «create date», «update date» og «ETL run» opprettes når dataene lastes. Ingen av disse kolonnene trengs i modellen og bør utelates når du importerer data.

Det andre eksemplet dreier seg om å utelate primærnøkkelkolonnen ved import av en faktatabell.

Mange tabeller, inkludert faktatabeller, har primærnøkler. I de fleste tabeller, for eksempel de som inneholder data om kunder, ansatte eller salg, vil du beholde primærnøkkelen i tabellen, slik at du kan bruke den til å opprette relasjoner i modellen.

Faktatabeller er annerledes. I en faktatabell brukes primærnøkkelen til å identifisere hver rad unikt. Selv om dette er nødvendig for normaliseringsformål, er det mindre nyttig i en datamodell der du bare vil ha kolonnene som brukes til analyse eller til å opprette tabellrelasjoner. Derfor tar du ikke med primærnøkkelen for en faktatabell når du importerer fra den. Primærnøkler i en faktatabell tar enormt stor plass i modellen og gir ingen fordeler siden de ikke kan brukes til å opprette relasjoner.

Merknad: I datalagre og flerdimensjonale databaser kalles store tabeller som for det meste inneholder numeriske data, for faktatabeller. Faktatabeller omfatter vanligvis data om forretningsprestasjon og transaksjoner, for eksempel salgs- og kostnadsdatapunkt som aggregeres og justeres i forhold til organisasjonsenheter, produkter, markedssegmenter, geografiske områder og så videre. Alle kolonnene i en faktatabell som inneholder forretningsdata eller kan brukes til å kryssreferere til 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. Den inneholder unike verdier som bare finnes i faktatabellen og ingen andre steder. Siden faktatabeller er så store, kommer noen av de største gevinstene innenfor modelleffektivitet fra utelatelse av rader og kolonner fra faktatabeller.

Utelate unødvendige kolonner

Effektiv modeller inneholder bare de kolonnene som må faktisk i arbeidsboken. Hvis du vil kontrollere hvilke kolonner som er inkludert i modellen, må du bruke importveiviseren-tabellen i PowerPivot-tillegget til å importere dataene i stedet for dialogboksen "importere-Data i Excel.

Når du starter veiviseren for tabellimport, velger du tabellene du vil importere.

Veiviseren for tabellimport i PowerPivot-tillegget

Du kan klikke knappen Forhåndsvis og filtrer for hver tabell og velge delene av tabellen du virkelig trenger. Vi anbefaler at du først fjerner merket for alle kolonner og deretter merker av for kolonnene du vil ha, etter at du har vurdert om de er nødvendige for analysen.

Forhåndsvisningsruten i veiviseren for tabellimport

Hva med å filtrere bare de nødvendige radene?

Mange tabeller i bedriftsdatabaser og datalagre inneholder historiske data som er akkumulert over lange perioder. I tillegg oppdager du kanskje at tabellene du er interessert i, inneholder informasjon for deler av virksomheten som ikke trengs i din bestemte analyse.

Du kan bruke veiviseren for tabellimport til å filtrere ut historiske eller urelaterte data og dermed spare mye plass i modellen. På følgende bilde brukes et datofilter til å hente bare de radene som inneholder data for gjeldende år, og historiske data som ikke trengs, utelates.

Filtreringsruten i veiviseren for tabellimport

Kan vi gjøre noe annet for å redusere minnebruken hvis vi trenger kolonnen?

Det finnes noen flere metoder du kan bruke for å gjøre en kolonne til en bedre kandidat for komprimering. Husk at den eneste egenskapen ved kolonnen som påvirker komprimeringen, er antallet unike verdier. I denne delen lærer du hvordan enkelte kolonner kan endres for å redusere antallet unike verdier.

Endre Datetime-kolonner

Datetime-kolonner tar mye plass i mange tilfeller. Heldigvis er det mange måter å redusere behovet for lagringsplass for denne datatypen. Metodene varierer avhengig av hvordan du bruker kolonnen, og hvor fortrolig du er med å bygge SQL-spørringer.

Datetime-kolonner har en datodel og et klokkeslett. Når du spør deg selv om du trenger en kolonne, stiller du samme spørsmål flere ganger for en Datetime-kolonne:

  • Trenger jeg klokkeslettdelen?

  • Trenger jeg timer, minutter, sekunder og/eller millisekunder i klokkeslettdelen?

  • 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.

Svarene du har på disse spørsmålene, avgjør hvilke alternativer du har for å håndtere Datetime-kolonnen.

Alle disse løsningene krever endring av en SQL-spørring. Du kan gjøre spørringsendringen enklere ved å filtrere ut minst én kolonne i hver tabell. Når du filtrerer ut en kolonne, endrer du spørringskonstruksjonen fra et forkortet format (SELECT *) til en SELECT-setning som inneholder fullstendige kolonnenavn, som er mye enklere å endre.

La oss ta en titt på spørringene som opprettes for deg. Du kan bytte til redigeringsprogrammet for spørring fra dialogboksen Tabellegenskaper og vise gjeldende SQL-spørring for hver tabell.

Båndet i PowerPivot-vinduet med kommandoen Tabellegenskaper

Velg Redigeringsprogram for spørring fra Tabellegenskaper.

Åpne Redigeringsprogram for spørring fra dialogboksen Tabellegenskaper

SQL-spørringen som brukes til å fylle ut tabellen, vises i redigeringsprogrammet for spørring. Hvis du filtrerte ut en kolonne under importen, inneholder spørringen fullstendige kolonnenavn:

SQL-spørring som brukes til å hente data

Hvis du i stedet importerer en tabell i sin helhet, uten å fjerne merket for noen kolonner eller uten å bruke et filter, vises spørringen som «Select * from », som er vanskeligere å endre:

SQL-spørring der den kortere standardsyntaksen brukes

Endre SQL-spørringen

Nå som du vet hvordan du finner spørringen, kan du endre den for å redusere størrelsen på modellen ytterligere.

  1. Når det gjelder kolonner som inneholder valuta- eller desimaldata, bruker du følgende syntaks for å bli kvitt desimalene hvis du ikke trenger dem:

    «SELECT ROUND([Decimal_column_name],0)… .»

    Hvis du trenger øre, men ikke brøkdeler av øre, erstatter du 0 med 2. Hvis du bruker negative tall, kan du runde til enheter, ti, hundre og så videre.

  2. Hvis du har en Datetime-kolonne kalt dbo.Bigtable.[Date Time] og ikke trenger klokkeslettdelen, bruker du syntaksen til å fjerne klokkeslettet:

    «SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) »

  3. Hvis du har en Datetime-kolonne kalt dbo.Bigtable.[Date Time] og trenger både dato- og klokkeslettdelen, bruker du flere kolonner i SQL-spørringen i stedet for den ene Datetime-kolonnen:

    SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    Bruk så mange kolonner du trenger til å lagre hver del i en egen kolonne.

  4. Hvis du trenger timer og minutter og foretrekker å ha dem sammen i én klokkeslettkolonne, kan du bruke følgende syntaks:

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Hvis du har to datetime-kolonner, for eksempel [Start Time] og [End Time], og trenger tidsdifferansen mellom dem i sekunder som en kolonne kalt [Duration], fjerner du begge kolonnene fra listen og legger til følgende:

    «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, vet du kanskje at beregnede kolonner brukes til å utlede 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 metode er å erstatte regelmessige eller beregnede kolonner med beregnede mål. Det klassiske eksemplet er Unit Price, Quantity og Total. Hvis du har alle tre, kan du spare plass ved å beholde bare to og beregne den tredje ved å bruke DAX.

Hvilke to kolonner skal du beholde?

I eksemplet ovenfor beholder du Quantity og Unit Price. Disse to har færre verdier enn Total. Du beregner Total ved å legge til et beregnet mål, for eksempel slik:

TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])*

Beregnede kolonner er som vanlige kolonner i og med at begge tar plass i modellen. I motsetning til dette beregnes beregnede mål umiddelbart og tar ikke plass.

Konklusjon

I denne artikkelen har vi snakket om flere fremgangsmåter som kan være til hjelp når du vil bygge en mer minnebesparende modell. Du kan redusere filstørrelsen og minneplassen som kreves for en datamodell, ved å redusere antall kolonner og rader samt antallet unike verdier i hver kolonne. Her er noen metoder vi har sett på:

  • Å fjerne kolonner er selvsagt den beste måten å spare plass på. Finn ut hvilke kolonner du virkelig trenger.

  • Av og til 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.

  • Å dele opp én kolonne i flere distinkte deler er generelt en god fremgangsmåte for å redusere antallet unike verdier i en kolonne. Hver av delene har et lite antall unike verdier, og det samlede antallet er mindre enn i den opprinnelige enkeltkolonnen.

  • I mange tilfeller trenger du også distinkte deler som du vil bruke som slicere i rapportene. Når det er aktuelt, kan du opprette hierarkier fra deler som timer, minutter og sekunder.

  • Ofte inneholder kolonner mer informasjon enn nødvendig. La oss for eksempel si at en kolonne brukes til å lagre desimaler, men du har brukt formatering til å skjule alle desimalene. Avrunding kan være svært effektivt når det gjelder å redusere størrelsen på en numerisk kolonne.

Nå som du har gjort hva du kan redusere størrelsen på arbeidsboken, bør du vurdere å kjøre også optimering av arbeidsbokstørrelse. Analyseres Excel-arbeidsboken, og komprimerer om mulig ytterligere. Last ned optimering av arbeidsbokstørrelse.

Beslektede koblinger

Datamodell – spesifikasjoner og grenser

Nedlasting av Optimering av arbeidsbokstørrelse

PowerPivot: Kraftig dataanalyse og datamodellering i Excel

Merknad: Ansvarsfraskrivelse for maskinoversettelse: Denne artikkelen er oversatt av et datasystem i stedet for en oversetter. Microsoft tilbyr disse maskinoversettelsene slik at brukere som ikke snakker engelsk, får tilgang til innhold om Microsoft-produkter, -tjenester og –teknologier. Ettersom artikkelen er maskinoversatt, kan den inneholde feil i vokabular, syntaks eller grammatikk.

Utvid ferdighetene dine
Utforsk opplæring
Vær først ute med de nye funksjonene
Bli med i Office Insiders

Var denne informasjonen nyttig?

Takk for tilbakemeldingen!

Takk for tilbakemeldingen! Det høres ut som det kan være lurt å sette deg i kontakt med én av våre Office-kundestøtteagenter.

×