I Excel 2013 eller senare kan du skapa datamodeller som innehåller miljontals rader och sedan utföra kraftfull dataanalys mot dessa modeller. Datamodeller kan skapas med eller utan Power Pivot-tillägg för att stödja val av pivottabeller, diagram och Power View-visualiseringar i samma arbetsbok.
Obs!: I den här artikeln beskrivs datamodeller i Excel 2013. Samma datamodelleringsfunktioner och Power Pivot-funktioner som introducerades i Excel 2013 gäller dock även för Excel 2016. Det är i praktiken liten skillnad mellan dessa versioner av Excel.
Även om du enkelt kan skapa stora datamodeller i Excel kan det finnas flera orsaker till det. Till att börja med överartar stora modeller som innehåller många tabeller och kolumner för de flesta analyser och ger en krånglig fältlista. För det andra använder stora modeller värdefullt minne, vilket påverkar andra program och rapporter som delar samma systemresurser negativt. Slutligen kan du i Microsoft 365 både SharePoint Online och Excel Web App begränsa storleken på en Excel till 10 MB. För arbetsboksdatamodeller som innehåller miljontals rader kommer du att gå in i 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 uppbyggd modell som är enklare att arbeta med och använder mindre minne. Om du tar dig tid att lära dig de bästa metoderna för effektiv modelldesign kommer den att betala av 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ända analysmotorn i minnet för att lagra data i minnet. Motorn implementerar kraftfulla komprimeringsmetoder för att minska lagringskraven och minska en resultatuppsättning tills den är en bråkdel av dess ursprungliga storlek.
I genomsnitt kan du förvänta dig att en datamodell är 7 till 10 gånger mindre än samma data vid ursprunget. Om du till exempel importerar 7 MB data från en SQL Server-databas kan datamodellen i Excel lätt vara 1 MB eller mindre. Hur mycket komprimering som faktiskt uppnås beror främst på antalet unika värden i varje kolumn. Ju fler unika värden, desto mer minne behövs för att lagra dem.
Varför pratar 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 kolumner som du inte verkligen behöver, särskilt om de kolumnerna 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 beskriver vi 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 har importerat från början. Om du vill skapa en effektiv modell tittar du på varje kolumn och frågar dig själv om den bidrar till den analys du vill utföra. Om den inte gör det eller om du är osäker kan du lämna den. Du kan alltid lägga till nya kolumner senare om du behöver dem.
Två exempel på kolumner som alltid ska undantas
Det första exemplet gäller data som kommer från ett datalager. På 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 de här kolumnerna behövs i modellen och bör avmarkeras när du importerar data.
I det andra exemplet utelämnas kolumnen för primärnyckeln när en faktatabell importeras.
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 åt. I en faktatabell används primärnyckeln för att unikt identifiera varje rad. Även om det är nödvändigt för normaliseringsändamål är det mindre användbart i en datamodell där du bara vill använda de kolumnerna för analys eller för att skapa tabellrelationer. Därför ska inte dess primärnyckel inkluderas när du importerar från en faktatabell. Primärnycklar i en faktatabell tar upp stora mängder utrymme i modellen, men ger ingen förmån eftersom de inte kan användas för att skapa relationer.
Obs!: I datalager och flerdimensionella databaser kallas stora tabeller som består av numeriska data ofta för "faktatabeller". Faktatabeller innehåller normalt 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 korsreferenser mellan data 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. Eftersom faktatabellerna är så omfattande härleds några av de största vinsterna inom modelleffektivisering från att utesluta rader eller kolumner från faktatabeller.
Så här utesluter du onödiga kolumner
Effektiva modeller innehåller endast 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 tabellimportguiden 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 & 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 att kontrollera de kolumner du vill ha, efter att ha övervägt om de behövs för analysen.
Hur filtrerar du bara de nödvändiga raderna?
Många tabeller i företagsdatabaser och datalager innehåller historiska data som ackumulerats under lång tid. Dessutom kan det hända att de tabeller du är intresserad av innehåller information för de affärsområden som inte behövs för en specifik analys.
Med hjälp av guiden Importera tabell 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, exklusive historiska data som inte behövs.
Vad händer om vi behöver kolumnen; kan vi ändå minska kostnaderna för utrymme?
Det finns några ytterligare tekniker du kan använda för att göra en kolumn till en bättre kandidat för komprimering. Kom ihåg att den enda egenskapen för 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 upp mycket utrymme. Som tur är finns det ett antal olika sätt att minska lagringskraven för den här datatypen. Tekniken varierar beroende på hur du använder kolumnen, och hur bekväm du är med att skapa 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å timmar? , minuter? , Sekunder? , millisekunder?
-
Har jag flera Datetime-kolumner eftersom jag vill beräkna skillnaden mellan dem eller bara aggregera data efter år, månad, kvartal och så vidare.
Hur du svarar på var och en av de här frågorna avgör vilka alternativ du har för hantering av Datumtid-kolumnen.
Alla dessa lösningar kräver att en e-SQL ändras. Om du vill göra det enklare att ändra 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 helt kvalificerade kolumnnamn, som är mycket enklare att ändra.
Vi tar en titt på de frågor som har skapats åt dig. I dialogrutan Tabellegenskaper kan du växla till Frågeredigeraren och visa den aktuella SQL för varje tabell.
Välj Frågeredigeraren i Tabellegenskaper.
Frågeredigeraren visar den SQL 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 har importerat en tabell i sin helhet, utan att avmarkera några kolumner eller tillämpa ett filter, visas frågan som "Välj * från ", vilket är 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 kan du, om du inte behöver decimalerna, använda den här syntaxen för att få bort decimalerna:
"VÄLJ AVRUNDA([Decimal_column_name];0)... .”
Om du behöver cent men inte decimaler av cent ersätter du 0 med 2. Om du använder negativa tal kan du avrunda till enheter, tiotal, hundratal osv.
-
Om du har en Datetime-kolumn med namnet dbo. Bigtable. [Datum/tid] och du behöver inte tidsdelen. Använd syntaxen för att ta bort tiden:
"SELECT CAST (dbo. Bigtable. [Datumtid] som datum) AS [Datumtid]) "
-
Om du har en Datetime-kolumn med namnet dbo. Bigtable. [Datum/tid] och du behöver både datum- och tidsdelarna, använd flera kolumner i SQL i stället för den enda Datetime-kolumnen:
"SELECT CAST (dbo. Bigtable. [Datumtid] som datum ) AS [Datumtid],
datumdel(hh, dbo. Bigtable. [Datum/tid]) som [Datum/tid, timmar],
datepart(mi, dbo. Bigtable. [Datum/tid]) som [Datum tid minuter],
datumdel(ss, dbo. Bigtable. [Datum/tid]) som [Datum/tid sekunder],
datumdel(ms, dbo. Bigtable. [Datum/tid]) som [Datum/tids 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 föredrar dem tillsammans som en tidskolumn kan du använda syntaxen:
Timefromparts(datepart(hh, dbo. Bigtable. [Datumtid]), datumdel(mm, dbo. Bigtable. [Datum/tid])) som [Datumtid timmeMinute]
-
Om du har två datum/tid-kolumner, till exempel [Starttid] och [Sluttid], och om du verkligen behöver tidsskillnaden mellan dem i sekunder som en kolumn med namnet [Varaktighet], tar du bort båda kolumnerna i listan och lägger till:
"datediff(ss,[Startdatum],[Slutdatum]) som [Varaktighet]"
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 vet du kanske redan 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 endast när de används i en pivottabell eller annan rapport.
En metod som sparar minne ä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 underhålla två och beräkna den tredje med DAX.
Vilka 2 kolumner bör du behålla?
Behåll Antal och Enhetspris i exemplet ovan. De här två har färre värden än Total. Om du vill beräkna totalt 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 upp plats.
Sammanfattning
I den här artikeln har vi pratat 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 tar 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.
-
Generellt sett är det bra att dela upp en kolumn i flera distinkta delar för 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 kommer att vara 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 behövs kan du skapa hierarkier från delar som Timmar, Minuter och Sekunder.
-
Kolumner innehåller ofta mer information än vad du behöver också. Anta till exempel att en kolumn lagrar decimaler, men du har tillämpat 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å din arbetsbok kan du även köra Workbook Size Optimizer. 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
PowerPivot: Kraftfull dataanalys och datamodellering i Excel
