I Excel 2013 eller senare kan du skapa datamodeller som innehåller miljontals rader och sedan utföra kraftfulla dataanalyser mot dessa modeller. Datamodeller kan skapas med eller utan Power Pivot tillägg som stöder valfritt antal pivottabeller, diagram och Power View-visualiseringar i samma arbetsbok.
Obs!: I den här artikeln beskrivs datamodeller i Excel 2013. Samma datamodellerings- och Power Pivot-funktioner som introducerades i Excel 2013 gäller dock även för Excel 2016. Det är faktiskt liten skillnad mellan dessa versioner av Excel.
Även om du enkelt kan skapa stora datamodeller i Excel finns det flera anledningar till att inte göra det. För det första är stora modeller som innehåller många tabeller och kolumner overkill för de flesta analyser och gör en besvärlig fältlista. För det andra använder stora modeller värdefullt minne, vilket negativt påverkar andra program och rapporter som delar samma systemresurser. I Microsoft 365 begränsar både SharePoint Online och Excel Web App slutligen storleken på en Excel-fil till 10 MB. För arbetsboksdatamodeller som innehåller miljontals rader överskrider du gränsen på 10 MB ganska snabbt. Se Specifikationer och begränsningar för datamodeller.
I den här artikeln får du lära dig hur du skapar en tätt konstruerad modell som är lättare att arbeta med och använder mindre minne. Om du tar dig tid att lära dig metodtips i effektiv modelldesign kommer det att löna sig för alla modeller du skapar och använder, oavsett om du visar den i Excel 2013, Microsoft 365 SharePoint Online, på en Office Online Server eller i SharePoint 2013.
Överväg även att köra Workbook Size Optimizer. Den analyserar din Excel-arbetsbok och komprimerar den ytterligare om det är möjligt. Ladda ned Workbook Size Optimizer.
I den här artikeln
Komprimeringsförhållandet och analysmotorn i minnet
Datamodeller i Excel använder minnesanalysmotorn för att lagra data i minnet. Motorn implementerar kraftfulla komprimeringstekniker för att minska lagringskraven och krymper en resultatuppsättning tills den är en bråkdel av sin ursprungliga storlek.
I genomsnitt kan du förvänta dig att en datamodell är 7 till 10 gånger mindre än samma data vid ursprungspunkten. Om du till exempel importerar 7 MB data från en SQL Server databas kan datamodellen i Excel lätt vara högst 1 MB. Vilken komprimeringsgrad som faktiskt uppnås beror främst på antalet unika värden i varje kolumn. Ju mer unika värden, desto mer minne krävs för att lagra dem.
Varför talar vi om komprimering och unika värden? Eftersom att skapa en effektiv modell som minimerar minnesanvändningen handlar det om komprimeringsmaximering, och det enklaste sättet att göra det är att ta bort alla kolumner som du egentligen inte behöver, särskilt om dessa kolumner innehåller ett stort antal unika värden.
Obs!: Skillnaderna i lagringskrav för enskilda kolumner kan vara stora. I vissa fall är det bättre att ha flera kolumner med ett lågt antal unika värden i stället för en kolumn med ett stort antal unika värden. I avsnittet om Datetime-optimeringar beskrivs den här tekniken i detalj.
Inget slår en kolumn som inte finns för låg minnesanvändning
Den mest minneseffektiva kolumnen är den som du aldrig importerat från början. Om du vill skapa en effektiv modell kan du titta på varje kolumn och fråga dig själv om den bidrar till den analys du vill utföra. Om den inte gör det eller om du inte är säker kan du utelämna den. Du kan alltid lägga till nya kolumner senare om du behöver dem.
Två exempel på kolumner som alltid ska uteslutas
Det första exemplet gäller data som kommer från ett datalager. I ett datalager är det vanligt att hitta artefakter av ETL-processer som läser in och uppdaterar data i lagret. Kolumner som "skapa datum", "uppdateringsdatum" och "ETL-körning" skapas när data läses in. Ingen av dessa kolumner behövs i modellen och ska avmarkeras när du importerar data.
Det andra exemplet handlar om att utelämna primärnyckelkolumnen när du importerar en faktatabell.
Många tabeller, inklusive faktatabeller, har primärnycklar. För de flesta tabeller, till exempel de som innehåller kund-, personal- eller försäljningsdata, vill du ha tabellens primärnyckel så att du kan använda den för att skapa relationer i modellen.
Faktatabeller skiljer sig. I en faktatabell används primärnyckeln för att unikt identifiera varje rad. Även om det är nödvändigt för normalisering är det mindre användbart i en datamodell där du bara vill att kolumnerna ska användas för analys eller för att upprätta tabellrelationer. När du importerar från en faktatabell ska du därför inte ta med dess primärnyckel. Primärnycklar i en faktatabell tar upp enorma mängder utrymme i modellen, men ger ingen fördel, eftersom de inte kan användas för att skapa relationer.
Obs!: I datalager och flerdimensionella databaser kallas stora tabeller som till största delen består av numeriska data ofta för "faktatabeller". Faktatabeller innehåller vanligtvis affärsresultat eller transaktionsdata, till exempel försäljnings- och kostnadsdatapunkter som aggregeras och justeras efter organisationsenheter, produkter, marknadssegment, geografiska områden och så vidare. Alla kolumner i en faktatabell som innehåller affärsdata eller som kan användas för korsreferensdata som lagras i andra tabeller bör ingå i modellen för att stödja dataanalys. Den kolumn som du vill utesluta är primärnyckelkolumnen i faktatabellen, som består av unika värden som bara finns i faktatabellen och ingen annanstans. Eftersom faktatabeller är så stora härleds några av de största vinsterna i modelleffektiviteten från att utesluta rader eller kolumner från faktatabeller.
Så här exkluderar du onödiga kolumner
Effektiva modeller innehåller bara de kolumner som du faktiskt behöver i arbetsboken. Om du vill styra vilka kolumner som ingår i modellen måste du använda guiden Importera tabell i Power Pivot-tillägget för 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.
För varje tabell kan du klicka på knappen Förhandsgranska & Filter och välja de delar av tabellen som du verkligen behöver. Vi rekommenderar att du först avmarkerar alla kolumner och sedan fortsätter med att kontrollera de kolumner du vill ha, efter att ha övervägt om de krävs för analysen.
Hur är det med att filtrera bara de rader som behövs?
Många tabeller i företagsdatabaser och datalager innehåller historiska data som ackumulerats under långa tidsperioder. Dessutom kan det hända att tabellerna du är intresserad av innehåller information för områden i företaget som inte behövs för din specifika analys.
Med hjälp av guiden Tabellimport kan du filtrera bort historiska eller orelaterade data och på så sätt spara mycket utrymme i modellen. I följande bild används ett datumfilter för att endast hämta rader som innehåller data för det aktuella året, med undantag av historiska data som inte behövs.
Vad händer om vi behöver kolumnen; kan vi ändå minska dess utrymmeskostnad?
Det finns några ytterligare tekniker som du kan använda för att göra en kolumn till ett bättre förslag för komprimering. Kom ihåg att det enda som kännetecknar kolumnen som påverkar komprimeringen är antalet unika värden. I det här avsnittet får du lära dig hur vissa kolumner kan ändras för att minska antalet unika värden.
Ändra datumtidskolumner
I många fall tar Datumtid-kolumner mycket utrymme. Som tur är finns det ett antal sätt att minska lagringskraven för den här datatypen. Teknikerna varierar beroende på hur du använder kolumnen och din komfortnivå när du skapar SQL-frågor.
Datumtidskolumner innehåller en datumdel och en tid. När du frågar dig själv om du behöver en kolumn kan du ställa samma fråga flera gånger för en Datetime-kolumn:
-
Behöver jag tidsdelen?
-
Behöver jag tidsdelen på nivån för timmar? Minuter? Sekunder? Millisekunder?
-
Har jag flera Datumtid-kolumner eftersom jag vill beräkna skillnaden mellan dem eller bara för att aggregera data efter år, månad, kvartal och så vidare.
Hur du besvarar de här frågorna avgör vilka alternativ du har för att hantera kolumnen Datumtid.
Alla dessa lösningar kräver att en SQL-fråga ändras. För att göra frågeändringar enklare bör du filtrera bort minst en kolumn i varje tabell. Genom att filtrera bort en kolumn kan du ändra frågekonstruktionen från ett förkortat format (SELECT *) till ett SELECT-uttryck som innehåller fullt kvalificerade kolumnnamn, som är mycket enklare att ändra.
Låt oss ta en titt på de frågor som skapas åt dig. I dialogrutan Tabellegenskaper kan du växla till Frågeredigeraren och se den aktuella SQL-frågan för varje tabell.
Välj Power Query-redigeraren i Tabellegenskaper.
I Power Query-redigeraren visas den SQL-fråga som används för att fylla i tabellen. Om du filtrerade bort en kolumn under importen innehåller frågan fullständigt kvalificerade kolumnnamn:
|
Om du däremot importerade en tabell i sin helhet, utan att avmarkera någon kolumn eller använda något filter, visas frågan som "Välj * från ", vilket blir svårare att ändra:
|
Ändra SQL-frågan
Nu när du vet hur du hittar frågan kan du ändra den för att ytterligare minska storleken på modellen.
-
För kolumner som innehåller valuta- eller decimaldata använder du den här syntaxen för att ta bort decimaler om du inte behöver decimaler:
"SELECT ROUND([Decimal_column_name],0)... .”
Om du behöver cent men inte bråk av cent, ersätt 0 med 2. Om du använder negativa tal kan du avrunda till enheter, tiotal, hundratal osv.
-
Om du har en Datumtid-kolumn med namnet dbo. Bigtable. [Datumtid] och du behöver inte tidsdelen använder du syntaxen för att ta bort tiden:
"SELECT CAST (dbo. Bigtable. [Datumtid] som datum) SOM [Datumtid]) "
-
Om du har en Datumtid-kolumn med namnet dbo. Bigtable. [Datumtid] och du behöver både datum- och tidsdelarna använder du flera kolumner i SQL-frågan i stället för den enda Datumtid-kolumnen:
"SELECT CAST (dbo. Bigtable. [Datumtid] som datum ) SOM [Datumtid]
datepart(hh, dbo. Bigtable. [Datumtid]) som [Datumtidstimmar]
datepart(mi, dbo. Bigtable. [Datumtid]) som [Datumtidsminuter]
datepart(ss, dbo. Bigtable. [Datumtid]) som [Datumtidssekunder]
datepart(ms, dbo. Bigtable. [Datumtid]) som [Datumtid millisekunder]"
Använd så många kolumner som du behöver för att lagra varje del i separata kolumner.
-
Om du behöver timmar och minuter, och du föredrar dem tillsammans som en tidskolumn, kan du använda syntaxen:
Tidfromparter(datumdel(hh, dbo. Bigtable. [Datumtid]), datumdel(mm, dbo. Bigtable. [Datumtid])) as [Date Time HourMinute]
-
Om du har två datumtidskolumner, till exempel [Starttid] och [Sluttid], och vad du verkligen behöver är tidsskillnaden mellan dem i sekunder som en kolumn som heter [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 arbetat med DAX-uttrycksspråket tidigare kanske du redan vet att beräknade kolumner används för att härleda nya kolumner baserat på någon annan kolumn i modellen, medan beräknade mått definieras en gång i modellen, men utvärderas bara när de används i en pivottabell eller annan rapport.
En minnessparteknik ä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 DAX.
Vilka två kolumner ska behållas?
Behåll Antal och Enhetspris i exemplet ovan. Dessa två har färre värden än totalsumman. Om du vill beräkna summa lägger du till ett beräknat mått som:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Beräknade kolumner är som vanliga kolumner på så sätt att båda tar upp utrymme i modellen. Beräknade mått beräknas däremot direkt och tar inte plats.
Sammanfattning
I den här artikeln pratade vi om flera metoder som kan hjälpa dig att skapa en mer minneseffektiv modell. Sättet att minska filstorleken och minneskraven för en datamodell är att minska det totala antalet kolumner och rader och antalet unika värden som visas i varje kolumn. Här är några tekniker som vi har att ta upp:
-
Att ta bort kolumner är naturligtvis det bästa sättet att spara utrymme. Bestäm vilka kolumner du verkligen 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. Du kan filtrera bort rader i tabellimportguiden.
-
Att dela upp en kolumn i flera olika delar är i allmänhet ett bra sätt att minska antalet unika värden i en kolumn. Var och en av delarna har ett litet antal unika värden och den kombinerade summan blir mindre än den ursprungliga enhetliga kolumnen.
-
I många fall behöver du också de distinkta delarna som ska användas som utsnitt i rapporterna. När det är lämpligt kan du skapa hierarkier från delar som Timmar, Minuter och Sekunder.
-
Kolumner innehåller ofta mer information än du behöver också. Anta till exempel att en kolumn lagrar decimaler, men du har använt formatering för att dölja alla decimaler. Avrundning kan vara mycket effektivt för att minska storleken på en numerisk kolumn.
Nu när du har gjort vad du kan för att minska storleken på arbetsboken kan du även köra Optimeraren för arbetsboksstorlek. Den analyserar din Excel-arbetsbok och komprimerar den ytterligare om det är möjligt. Ladda ned Workbook Size Optimizer.
Relaterade länkar
Specifikationer och begränsningar för datamodeller
Optimerare för arbetsboksstorlek
PowerPivot: Kraftfull dataanalys och datamodellering i Excel
