Opprette en minne effektiv data modell ved hjelp av Excel og Power pivot-tillegget

I Excel 2013 eller nyere kan du opprette data modeller som inneholder millioner av rader, og deretter utføre kraftig data analyse mot disse modellene. Data modeller kan opprettes med eller uten Power Pivot-tillegget for å støtte et hvilket som helst antall pivottabeller, diagrammer og Power View-visualiseringer i samme arbeids bok.

Obs!: Denne artikkelen beskriver data modeller i Excel 2013. De samme data modellerings-og Power pivot-funksjonene som ble introdusert i Excel 2013 gjelder også for Excel 2016. Det er effektivt liten forskjell mellom disse versjonene av Excel.

Selv om du enkelt kan bygge store data modeller i Excel, er det flere grunner til det. For det første er store modeller som inneholder multitudes av tabeller og kolonner, overkill for de fleste analyser, og gjør for en tungvint felt liste. For det andre kan store modeller bruke opp verdifullt minne, negativt påvirke andre programmer og rapporter som deler de samme system ressursene. Til slutt i Office 365 kan både SharePoint Online og Excel Web App begrense størrelsen på en Excel-fil til 10 MB. For arbeids bok data modeller som inneholder millioner av rader, kan du kjøre i grensen på 10 MB ganske raskt. Se spesifikasjon og begrensninger for data modell.

I denne artikkelen lærer du hvordan du lager en tett konstruert modell som er enklere å arbeide med og bruker mindre minne. Hvis du tar deg tid til å lære anbefalte Fremgangs måter i effektiv modell utforming, kan du betale ned på veien for enhver modell du oppretter 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øre optimaliseringen for arbeidsbokstørrelse. Den analyserer Excel-arbeidsboken og komprimerer den om mulig ytterligere. Last ned arbeids bok størrelses optimalisering.

I denne artikkelen

Komprimerings forhold og analyse motoren i minnet

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

To eksempler på kolonner som alltid skal utelates

Slik utelater du unødvendige kolonner

Hva med å filtrere bare de nødvendige radene?

Hva om vi trenger kolonnen; kan vi likevel redusere kostnads kostnaden?

Endre datetime-kolonner

Endre SQL-spørringen

Bruke DAX-beregnede mål i stedet for kolonner

Hvilke to kolonner du bør beholde?

Konklusjon

Beslektede koblinger

Komprimerings forhold og analyse motoren i minnet

Data modeller i Excel bruker minne analyse motoren til å lagre data i minnet. Motoren implementerer kraftige komprimerings teknikker for å redusere lagrings krav, og redusere et resultat sett til det er en brøk del av den opprinnelige størrelsen.

I gjennomsnitt kan du forvente at en data modell skal være 7 til 10 ganger mindre enn de samme dataene på det opprinnelige tidspunktet. Hvis du for eksempel vil importere 7 MB med data fra en SQL Server-database, kan data modellen i Excel enkelt være 1 MB eller mindre. Komprimerings graden 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? Siden bygging av en effektiv modell som minimerer minne bruken, handler om komprimerings maksimering, og den enkleste måten å gjøre det på, er å bli kvitt alle kolonner du ikke trenger, spesielt hvis disse Kol onnene inneholder et stort antall unike verdier.

Obs!:  Forskjellene i lagrings krav for individuelle kolonner kan være svært store. I noen tilfeller er det bedre å ha flere kolonner med et lite antall unike verdier i stedet for én kolonne med et stort antall unike verdier. Delen av datetime-optimaliseringer dekker denne teknikken i detalj.

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

Den mest minne effektive kolonnen er den du aldri importerte på det første stedet. Hvis du vil bygge en effektiv modell, kan du se på hver kolonne og be deg selv om den bidrar med den analysen du vil utføre. Hvis det ikke er sikkert, kan du la det være. 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 relatert til data som kommer fra et data vare hus. I et data lager er det vanlig å finne artefakter av ETL-prosesser som laster inn og oppdaterer data i lageret. Kolonner som «Opprett dato», "oppdaterings dato" og "ETL Run" opprettes når dataene lastes inn. Ingen av disse Kol onnene er nødvendige i modellen, og må være merket av når du importerer data.

Det andre eksemplet involverer å utelate primær nøkkel Kol onnen når du importerer en fakta tabell.

Mange tabeller, inkludert fakta tabeller, har primær nøkler. For de fleste tabeller, for eksempel de som inneholder kunde-, ansatt-eller salgs data, vil du ha tabellens primær nøkkel, slik at du kan bruke den til å opprette relasjoner i modellen.

Fakta tabeller er forskjellig. I en fakta tabell brukes primær nøkkelen til å identifisere hver rad entydig. Når det er nødvendig for normalisering, er det mindre nyttig i en data modell der du bare vil ha de Kol onnene som brukes til analyse eller til å fastsette tabell relasjoner. Derfor må du ikke inkludere primær nøkkelen når du importerer fra en fakta tabell. Primær nøkler i en fakta tabell bruker enormous mengder plass i modellen, men ikke gi noen fordeler, siden de ikke kan brukes til å opprette relasjoner.

Obs!:  I data lagre og flerdimensjonale databaser kalles store tabeller som består av mest numeriske data, ofte som "fakta tabeller". Fakta tabeller omfatter vanligvis forretnings ytelse eller transaksjons data, for eksempel data punkter for salg og kostnad som er aggregert og justert til organisasjons enheter, produkter, markeds segmenter, geografiske regioner og så videre. Alle Kol onnene i en fakta tabell som inneholder forretnings data, eller som kan brukes til kryss referanse data som er lagret i andre tabeller, bør være inkludert i modellen for å støtte data analyse. Kolonnen du vil utelate, er primær nøkkel Kol onnen i fakta tabellen, som består av unike verdier som bare finnes i fakta tabellen og ingen andre. Siden fakta tabeller er så store, er noen av de største gevinstene i modell effektivitet utledet fra å utelate rader eller kolonner fra fakta tabeller.

Slik utelater du unødvendige kolonner

Effektive modeller inneholder bare de Kol onnene som du faktisk trenger i arbeids boken. Hvis du vil kontrollere hvilke kolonner som er inkludert i modellen, må du bruke vei viseren for tabell import i Power pivot-tillegget for å importere dataene i stedet for Importer data-dialogboksen i Excel.

Når du starter vei viseren for tabell import, velger du hvilke tabeller som skal importeres.

Veiviseren for tabellimport i PowerPivot-tillegget

For hver tabell kan du klikke på knappen forhånds Vis & filter og velge de delene av tabellen du virkelig trenger. Vi anbefaler at du først fjerner alle Kol onnene, og deretter fortsetter du å merke av for Kol onnene du vil bruke, etter å ha tatt hensyn til om de er nødvendige for analysen.

Forhåndsvisningsruten i veiviseren for tabellimport

Hva med å filtrere bare de nødvendige radene?

Mange tabeller i bedrifts databaser og data lagre inneholder historiske data akkumulert over lange tids perioder. I tillegg kan det hende at tabellene du er interessert i, inneholder informasjon for områder av virksomheten som ikke kreves for den spesifikke analysen.

Ved hjelp av vei viseren for tabell import kan du filtrere ut historiske eller ikke-relaterte data, og dermed spare mye plass i modellen. I bildet nedenfor brukes et dato filter til å hente bare rader som inneholder data for gjeldende år, bortsett fra historiske data som ikke trengs.

Filtreringsruten i veiviseren for tabellimport

Hva om vi trenger kolonnen; kan vi likevel redusere kostnads kostnaden?

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

Endre datetime-kolonner

I mange tilfeller opptar datetime-kolonner mye plass. Heldigvis er det mange måter å redusere lagrings kravene for denne data typen på. Teknikkene vil variere avhengig av hvordan du bruker kolonnen, og komforts nivået i bygg SQL-spørringer.

Datetime-kolonner inkluderer en dato del og et klokkeslett. Når du blir spurt om du trenger en kolonne, bør du spørre det samme spørsmålet flere ganger for en DateTime-kolonne:

  • Trenger jeg tid-delen?

  • Trenger jeg tid-delen på time nivået? sekunder? 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 det enklere å endre spørringen, må du filtrere ut minst én kolonne i hver tabell. Ved å filtrere ut en kolonne endrer du spørrings konstruksjon fra et forkortet format (SELECT *) til en SELECT-setning som inneholder fullstendig kvalifiserte Kol onne navn, som er langt enklere å endre.

La oss ta en titt på spørringene som er opprettet for deg. I dialog boksen tabell egenskaper kan du bytte til redigerings programmet for spørring og se gjeldende SQL-spørring for hver tabell.

Båndet i PowerPivot-vinduet med kommandoen Tabellegenskaper

Velg redigerings program for spørringfra tabell egenskaper.

Åpne Redigeringsprogram for spørring fra dialogboksen Tabellegenskaper

Redigerings programmet for spørring viser SQL-spørringen som brukes til å fylle ut tabellen. Hvis du filtrerte ut en kolonne under import, inneholder spørringen fullt kvalifiserte Kol onne navn:

SQL-spørring som brukes til å hente data

Hvis du for eksempel importerte en tabell i sin helhet, uten å fjerne merket for en kolonne eller bruker et filter, vil du se spørringen som «Velg * fra», som blir 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. Hvis du ikke trenger desimalene for kolonner som inneholder valuta-eller desimal data, kan du bruke denne syn tak sen til å bli kvitt desimalene:

    "Velg AVRUND ([Decimal_column_name]; 0)... .”

    Hvis du trenger cent, men ikke brøker av cent, erstatter du 0 med 2. Hvis du bruker negative tall, kan du runde av til enheter, ti, hundre osv.

  2. Hvis du har en DateTime-kolonne kalt dbo. Bigtable. [Dato klokkeslett] og du trenger ikke tid-delen, bruker du syn tak sen til å bli kvitt tid:

    «Velg CAST (dbo. Bigtable. [Dato klokkeslett] AS dato) "

  3. Hvis du har en DateTime-kolonne kalt dbo. Bigtable. [Dato klokkeslett] og du trenger både dato-og klokkeslett deler, kan du bruke flere kolonner i SQL-spørringen i stedet for den eneste datetime-kolonnen:

    «Velg CAST (dbo. Bigtable. [Dato klokkeslett] AS date) AS [dato klokkeslett]

    DatePart (hh, dbo. Bigtable. [Dato klokkeslett]) som [dato tid timer],

    DatePart (mi, dbo. Bigtable. [Dato klokkeslett]) som [dato tid minutter],

    DatePart (SS, dbo. Bigtable. [Dato klokkeslett]) som [dato tid sekunder]

    DatePart (MS, dbo. Bigtable. [Dato klokkeslett]) som [dato tid millisekunder]

    Bruk så mange kolonner som du trenger for å lagre hver del i separate kolonner.

  4. Hvis du trenger timer og minutter, og du foretrekker dem samlet som én klokkeslett-kolonne, kan du bruke syn tak sen:

    Klokkeslettfradeler (DatePart (hh, dbo. Bigtable. [Dato klokkeslett]), DatePart (mm, dbo. Bigtable. [Dato klokkeslett])) som [dato tid HourMinute]

  5. Hvis du har to datetime-kolonner, for eksempel [Start tidspunkt] og [Slut tids punkt], og det du virkelig trenger, er tids forskjellen i sekunder som en kolonne kalt [Duration], fjerner du begge Kol onnene fra listen og legger til:

    "DateDiff (SS; [Start dato]; [slutt dato]) AS [Duration]"

    Hvis du bruker nøkkel ordet MS i stedet for SS, får du varigheten i millisekunder

Bruke DAX-beregnede mål i stedet for kolonner

Hvis du har jobbet med DAX Expression Language før, kan det hende du allerede vet 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 den brukes i en Pivottabell eller en annen rapport.

Én minne lagrings teknikk er å erstatte vanlige eller beregnede kolonner med beregnede mål. Det klassiske eksemplet er enhets pris, antall og total. Hvis du har alle tre, kan du spare plass ved å vedlikeholde bare to og beregne den tredje ved hjelp av DAX.

Hvilke to kolonner du bør beholde?

I eksemplet ovenfor kan du beholde antall og enhets pris. Disse to har færre verdier enn det totale antallet. Hvis du vil beregne total, legger du til et beregnet mål som:

"TotalSales: = sumx (' Sales Table", "Sales Table" [Unit Price] * "Sales Table" [Antall]) "

Beregnede kolonner er som vanlige kolonner i begge opptar plass i modellen. Beregnede mål beregnes i motsetning på fly og må ikke få plass.

Konklusjon

I denne artikkelen snakket vi om flere Fremgangs måter som kan hjelpe deg med å bygge en mer minne effektiv modell. Du kan redusere det totale antallet kolonner og rader, og hvor mange unike verdier som vises i hver kolonne, for å redusere fil størrelsen og minne kravene for en data modell. Her er noen teknikker vi har dekket:

  • Å fjerne spalter er selvfølgelig den beste måten å spare plass på. Bestemme 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 vei viseren for tabell import.

  • I tillegg er det en god måte å redusere antall unike verdier i en kolonne ved å dele én enkelt kolonne inn i flere forskjellige deler. Hver av delene vil ha et lite antall unike verdier, og den samlede Totalen vil være mindre enn den opprinnelige enhetlige kolonnen.

  • I mange tilfeller trenger du også de distinkte delene som skal brukes som slicere i rapportene. Når det passer, kan du opprette hierarkier fra deler som timer, minutter og sekunder.

  • Mange ganger inneholder kolonner mer informasjon enn du trenger dem også. Tenk deg for eksempel at en kolonne inneholder desimaler, men du har brukt formatering for å skjule alle desimalene. Avrunding kan være svært effektiv hvis du reduserer størrelsen på en numerisk kolonne.

Nå som du har gjort det du kan redusere størrelsen på arbeids boken, bør du vurdere også å kjøre optimalisering av arbeids bok størrelse. Den analyserer Excel-arbeidsboken og komprimerer den om mulig ytterligere. Last ned arbeids bok størrelses optimalisering.

Beslektede koblinger

Datamodell – spesifikasjoner og grenser

Last ned arbeids bok størrelse optimalisering

Power Pivot: kraftig dataanalyse og datamodellering i Excel

Bli bedre på Office
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.

×