Skapa en minneseffektiv datamodell med Excel och Power Pivot-tilläggsprogrammet

Viktigt!: Den här artikeln är maskinöversatt, se ansvarsfriskrivningen. Den engelska versionen av den här artikeln finns här för din referens.

I Excel 2013 eller senare kan du skapa datamodeller som innehåller flera miljoner rader och utför kraftfulla dataanalyser mot dessa modeller. Datamodeller kan skapas med eller utan tillägget Power Pivot som stöd för valfritt antal pivottabeller, diagram och Power View-visualiseringar i samma arbetsbok.

Obs!: I den här artikeln beskrivs datamodeller i Excel 2013. Samma datamodeller och Power Pivot-funktionerna i Excel 2013 gäller emellertid också för Excel 2016. Det finns effektivt lilla skillnaden mellan följande versioner av Excel.

Även om du kan enkelt skapa stora datamodeller i Excel, finns det olika anledningar inte till. Första stora modeller som innehåller multitudes tabeller och kolumner är overkill för de flesta analyser och göra för en komplicerade Fältlista. Andra stora modeller förbruka värdefullt minne, negativt påverkar andra program och rapporter som delar samma systemresurser. Slutligen i Office 365 begränsa SharePoint Online och Excel Web App storleken på en Excel-fil till 10 MB. För datamodeller i arbetsboken som innehåller flera miljoner rader, kan du stöta på 10 MB-gränsen ganska snabbt. Finns i datamodellen specifikationer och begränsningar.

I den här artikeln får du lära dig att skapa en smidigt konstruerad modell som är lättare att arbeta med och använder mindre minne. Om du tar dig tiden att lära dig de bästa sätten att effektivt skapa modeller kommer det att betala sig i framtiden för alla modeller du skapar och använder, vare sig du visar dem i Excel 2013, Office 365 SharePoint Online, på en Office Online-server eller i SharePoint 2013.

Du kan också köra Workbook Size Optimizer. Den analyserar Excel-arbetsboken och komprimerar det om möjligt ytterligare. Hämta Workbook Size Optimizer.

I den här artikeln

Komprimeringsgrader och motorn i minnet analytics

Inget kräver än en kolumn som inte finns mindre minne

Två exempel på kolumner som alltid ska uteslutas

Så här utelämnar du onödiga kolumner

Går det att filtrera bara de nödvändiga raderna?

Vad händer om vi behöver kolumnen. går det fortfarande att minska utrymmeskravet?

Ändra datum / tidskolumner

Redigera SQL-frågan

Använda DAX-beräknade mått i stället för kolumner

Vilka 2 kolumner ska du behålla?

Ingående

Relaterade länkar

Komprimeringsgrader och minnesanalysmotorn

För datamodeller i Excel används minnesanalysmotorn för att lagra data i minnet. Motorn tillämpar kraftfulla komprimeringstekniker för att minska lagringsutrymmet och krymper en resultatmängd till en bråkdel av dess ursprungliga storlek.

I genomsnitt kan du förvänta dig att datamodellen blir mellan 7 och 10 gånger mindre än samma data på ursprungsplatsen. Om du t. ex. importerar 7 MB data från en SQL Server-databas kan datamodellen utan problem bli 1 MB eller mindre. Den faktiskt uppnådda komprimeringsgraden beror först och främst på antalet unika värden i varje kolumn. Ju fler unika värden, desto mer minne krävs för lagring.

Varför pratar vi om komprimering och unika värden? Eftersom maximal komprimering är det absolut viktigaste när det gäller att bygga en effektiv modell som minimerar minnesanvändningen, och det lättaste sättet att uppnå det är att ta bort kolumner som inte behövs, särskilt om kolumnerna innehåller ett stort antal unika värden.

Obs!: Skillnaderna i lagringsutrymme för individuella kolumner kan vara enormt. I vissa fall är det bättre att ha flera kolumner med få unika värden än en kolumn med många unika värden. I avsnittet om optimering av datum/tidskolumner finns utförlig information om den här tekniken.

Inget kräver mindre minne än en kolumn som inte finns

Den kolumn som sparar allra mest minne är den som du inte ens importerat. Om du vill skapa en effektiv modell behöver du granska varje kolumn och ställa dig frågan om den bidrar till den analys du vill utföra. Om den inte gör det, eller om du är osäker, struntar du i den. Du kan alltid lägga till nya kolumner i efterhand om du behöver.

Två exempel på kolumner som alltid ska uteslutas

Det första exemplet handlar om data som kommer från datalager. I ett datalager är det vanligt med rester av ETL-processer som läser in och uppdaterar data i lagret. Kolumner med tidsstämplar som "skapad", "uppdaterad" och "ETL-körning" skapas när data läses in. Ingen av kolumnerna behövs i modellen och du bör avmarkera dem när du importerar data.

Det andra exemplet handlar om att utesluta primärnyckelkolumnen när du importerar en faktatabell.

Många tabeller har primärnycklar, även faktatabeller. För de flesta tabeller, t. ex. sådana som innehåller data om kunder, anställda eller försäljning, behöver du primärnyckeln för att kunna skapa relationer i modellen.

Med faktatabeller är det annorlunda. I en faktatabell används primärnyckeln för att unikt identifiera varje rad. Den behövs för normalisering, men är mindre användbar i en datamodell där du bara vill ha de kolumner som används för analys eller för att upprätta tabellrelationer. Därför ska du inte ta med primärnyckeln när du importerar en faktatabell. Primärnycklar i en faktatabell tar upp extremt mycket utrymme i modellen, men tjänar inget syfte eftersom de inte kan användas för att skapa relationer.

Obs!: I datalager och flerdimensionella databaser kallas stora tabeller som framförallt utgörs av numeriska data ofta för "faktatabeller". Exempel på faktatabeller kan vara verksamhetsresultat eller transaktionsinformation, t. ex. datapunkter för försäljning och kostnader som summeras och sorteras efter organisationsenhet, produkt, marknadssegment, geografiskt område, o.s.v. Alla kolumner i en faktatabell som innehåller affärsdata, eller som kan användas för att korsreferera data som lagrats i andra tabeller, måste tas med i modellen för att möjliggöra dataanalys. Den kolumn som ska utelämnas är primärnyckelkolumnen i en faktatabell, som utgörs av unika värden som bara existerar i faktatabellen och ingen annanstans. Eftersom faktatabeller är så pass stora, får man de största effektivitetsvinsterna för datamodeller genom att utelämna rader eller kolumner från faktatabeller.

Så här utelämnar du onödiga kolumner

Effektiv modeller innehåller de kolumner som du behöver faktiskt i arbetsboken. Om du vill styra vilka kolumner som ingår i modellen, måste du använda guiden Importera tabell för i Power Pivot-tilläggsprogrammet att importera data i stället för dialogrutan "Importera Data" i Excel.

När du startar tabellimportguiden väljer du vilka tabeller du vill importera.

Tabellimportguiden i tilläggsprogrammet PowerPivot

För varje tabell kan du klicka på knappen Förhandsgranska och filtrera och markera de delar av tabellen som du verkligen behöver. Vi rekommenderar att du först avmarkerar alla kolumner och sedan kryssar för de kolumner du vill ha, efter att ha kontrollerat om de är nödvändiga för analysen.

Fönstret för förhandsgranskning i tabellimportguiden

Går det att filtrera bara de nödvändiga raderna?

Många tabeller i företagsdatabaser och datalager innehåller historikdata som samlats in under lång tid. Det kan också visa sig att de tabeller du är intresserad av innehåller information om områden inom företaget som inte är relevanta för din specifika analys.

Med tabellimportguiden kan du filtrera bort historikdata och irrelevanta data och på så sätt spara mycket utrymme i modellen. På följande bild används ett datumfilter för att hämta endast de rader som innehåller data för innevarande år, och utelämna historiska data som inte behövs.

Filterfönstret i tabellimportguiden

Går det fortfarande att minska utrymmeskravet för en nödvändig kolumn?

Det finns några ytterligare sätt att skapa bättre förutsättningar för komprimeringen av en kolumn. Kom ihåg att de enda egenskaperna hos en kolumn som påverkar komprimeringen är antalet unika värden. I det här avsnittet får du lära dig hur du kan ändra vissa kolumner så att antalet unika värden minskar.

Ändra en datum/tidskolumn

Datum/tidskolumner tar ofta upp mycket utrymme. Som tur är går det att minska lagringsutrymmet för den här datatypen på flera olika sätt. Hur du går till väga beror på hur kolumnen används och hur van du är vid att skapa SQL-frågor.

Datum/tidskolumner innehåller en datumdel och en tidsdel. När du funderar över om du behöver en kolumn, ställer du samma fråga flera gånger för en datum/tidskolumn:

  • Behöver jag tidsdelen?

  • Behöver jag tidsdelen i timmar? Minuter? Sekunder? Millisekunder?

  • Har jag flera datum/tidskolumner för att jag vill beräkna skillnaden mellan dem, eller bara för att sammanställa data enligt år, månad, kvartal o.s.v.

Svaren på de här frågorna avgör vilka alternativ du har för att hantera datum/tidskolumnen.

Alla lösningar kräver att du ändrar en SQL-fråga. För att underlätta ändringen av frågan bör du filtrera bort minst en kolumn i varje tabell. Genom att filtrera bort en kolumn ändrar du frågekonstruktionen från ett förkortat format (SELECT *) till ett SELECT-uttryck som innehåller fullständiga kolumnnamn som är mycket lättare att ändra.

Ni tittar vi på de frågor som skapas åt dig. Från dialogrutan Tabellegenskaper kan du växla över till Frågeredigeraren, där den aktuella SQL-frågan för varje tabell visas.

Menyfliksområdet i PowerPivot-fönstret med kommandot Tabellegenskaper

Gå till Tabellegenskaper och välj Frågeredigeraren.

Öppna Frågeredigeraren från dialogrutan Tabellegenskaper

Frågeredigeraren visar SQL-frågan som användes för att fylla i tabellen. Om du filtrerade bort kolumner vid importen innehåller frågan fullständiga kolumnnamn:

SQL-fråga som användes för att hämta data

Om du däremot har importerat en hel tabell utan att avmarkera kolumner eller tillämpa filter, visas frågan som "Select * from", och är svårare att redigera:

SQL-fråga med kortare standardsyntax

Redigera SQL-frågan

Nu vet du hur du hittar frågan och kan redigera den för att minska storleken på modellen.

  1. Om du har kolumner med valutadata eller decimaltal och inte behöver decimalerna, kan du ta bort dem med följande syntax:

    "SELECT ROUND([Decimal_kolumn_namn],0)… ."

    Om du vill ha ören, men inte decimaler av ören, ersätter du 0 med 2. Om du använder negativa tal kan du avrunda till enheter, tiotal, hundratal o.s.v.

  2. Om du har en datum/tidskolumn som heter dbo.Bigtable.[Date Time] och du inte behöver tidsdelen, kan du ta bort tidsdelen med följande syntax:

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

  3. Om du har en datum/tidskolumn som heter dbo.Bigtable.[Date Time] och du behöver både datum- och tidsdelen använder du flera kolumner i SQL-frågan istället för bara datum/tidskolumnen:

    "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]”

    Använd så många kolumner du behöver för att lagra varje del i en separat kolumn.

  4. Om du behöver timmar och minuter och föredrar att ha dem tillsammans i en enda tidskolumn kan du använda följande syntax:

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

  5. Om du har två datum/tidskolumner, t. ex. [Starttid] och [Sluttid] och vill ha tidsskillnaden mellan dem i sekunder i kolumnen [Varaktighet], tar du bort båda kolumnerna från listan och lägger till:

    “datediff(ss,[Start Date],[End Date]) as [Duration]”

    Om du använder nyckelordet ms i stället för ss, får du varaktigheten i millisekunder

Använda DAX-beräknade mått i stället för kolumner

Om du har använt DAX-uttryck tidigare, kanske du redan känner till att beräknade kolumner används för att härleda nya kolumner baserat på en annan kolumn i modellen, medan beräknade mått definieras en gång i modellen men beräknas bara om de används i en pivottabell eller någon annan rapport.

En minnesbesparande teknik är att ersätta vanliga eller beräknade kolumner med beräknade mått. Det klassiska exemplet är Enhetspris, Antal och Summa. Om du har alla tre kan du spara utrymme genom att bara behålla två och beräkna den tredje med hjälp av DAX.

Vilka två kolumner ska du behålla?

I exemplet ovan behåller du Antal och Enhetspris. De har färre värden än Summa. För att beräkna Summa, lägger du till ett beräknat mått, till exempel:

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

Beräknade kolumner och vanliga kolumner har det gemensamt att båda tar upp utrymme. Beräknade mått däremot, beräknas efter hand och tar inte upp något utrymme.

Sammanfattning

I den här artikeln har vi tagit upp flera tekniker som kan hjälpa dig att skapa en minneseffektiv modell. Om du vill minska filstorleken och minneskraven för en datamodell behöver du minska det totala antalet kolumner och rader och antalet unika värden i varje kolumn. Här är några av teknikerna som vi har gått igenom:

  • Det bästa sättet att spara utrymme är förstås att ta bort kolumner helt. Bestäm dig för vilka kolumner du faktiskt behöver.

  • Ibland kan du ta bort en kolumn och ersätta den med ett beräknat mått i tabellen.

  • Du kanske inte behöver alla rader i en tabell. Med tabellimportguiden kan du filtrera bort rader.

  • Ett bra sätt att minska antalet unika värden i en kolumn är ofta att dela upp en kolumn i flera specifika delar. Varje del får ett litet antal unika värden och det sammanlagda antalet blir mindre än i den ursprungliga enhetliga kolumnen.

  • Det är också vanligt att du behöver använda de specifika delarna som utsnitt i rapporter. Vid behov kan du skapa hierarkier utifrån delar som t. ex. timmar, minuter och sekunder.

  • Kolumner innehåller ofta mer information än vad som är nödvändigt. Anta till exempel att en kolumn innehåller decimaler, men du har valt att tillämpa formatering som döljer alla decimaler. Avrundning kan vara ett effektivt sätt att minska storleken på en numerisk kolumn.

Nu när du har gjort vad du kan minska storleken på din arbetsbok kan du prova att även köra Workbook Size Optimizer. Den analyserar Excel-arbetsboken och komprimerar det om möjligt ytterligare. Hämta Workbook Size Optimizer.

Relaterade länkar

Specifikationer och begränsningar för datamodeller

Hämta Workbook Size Optimizer

PowerPivot: Kraftfull dataanalys och datamodellering i Excel

Obs!: Ansvarsfriskrivning för maskinöversättning: Den här artikeln har översatts av ett datorsystem utan mänsklig inblandning. Microsoft erbjuder dessa maskinöversättningar för att hjälpa icke engelskspråkiga användare att ta del av information om Microsofts produkter, tjänster och tekniker. Eftersom artikeln är maskinöversatt kan den innehålla fel i ordval, syntax och grammatik.

Utöka dina kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×