I Excel 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äggsprogrammet och har stöd för valfritt antal pivottabeller, diagram och Power View-visualiseringar i samma arbetsbok.
Du kan enkelt skapa stora datamodeller i Excel, men det finns flera skäl till att inte göra det. För det första är stora modeller som innehåller mängder av tabeller och kolumner överdrivet för de flesta analyser och utgör en besvärlig 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 begränsar både SharePoint Online och Excel Web App Excel-filens storlek till 10 MB i Microsoft 365. För arbetsboksdatamodeller som innehåller miljontals rader når du gränsen på 10 MB ganska snabbt. Se specifikationer och begränsningar för datamodellen.
I den här artikeln får du lära dig hur du skapar en nära konstruerad modell som är enklare att arbeta med och använder mindre minne. Att ta sig tid att lära sig bästa praxis för effektiv modelldesign kommer att löna sig på vägen för alla modeller du skapar och använder, oavsett om du visar den i Excel, Microsoft 365 SharePoint Online, på en Office Web Apps Server eller i SharePoint.
Överväg även att köra Workbook Size Optimizer. Den analyserar din Excel-arbetsbok och komprimerar den ytterligare om det är möjligt. Hämta Workbook Size Optimizer.
Artikelinnehåll
Komprimeringsförhållanden och den minnesinterna analysmotorn
Tänk om vi behöver kolumnen; Kan vi fortfarande minska utrymmeskostnaden?
Komprimeringsförhållanden och den minnesinterna analysmotorn
Datamodeller i Excel använder den minnesinterna analysmotorn för att lagra data i minnet. Motorn implementerar kraftfulla komprimeringstekniker för att minska lagringskraven, vilket minskar 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 ursprungspunkten. 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. Vilken komprimeringsgrad som faktiskt uppnås beror främst på antalet unika värden i varje kolumn. Ju fler unika värden, desto mer minne krä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 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 kolumnerna innehåller ett stort antal unika värden.
Obs
Skillnaderna i lagringskrav för enskilda kolumner kan vara enorma. I vissa fall är det bättre att ha flera kolumner med få unika värden än en kolumn med ett stort antal unika värden. Avsnittet om datetime-optimeringar beskriver den här tekniken i detalj.
Inget slår en obefintlig kolumn för låg minnesanvändning
Den mest minneseffektiva kolumnen är den som du aldrig importerade från början. Om du vill skapa en effektiv modell bör du titta på varje kolumn och fråga dig själv om den bidrar till den analys du vill utföra. Om det inte gör det eller om du är osäker kan du utelämna det. Du kan alltid lägga till nya kolumner senare om du behöver det.
Två exempel på kolumner som alltid ska undantas
Det första exemplet gäller data som kommer från ett informationslager. I ett informationslager är det vanligt att hitta artefakter av ETL-processer som läser in och uppdaterar data i lagret. Kolumner som "create date", "update date" och "ETL run" 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 är annorlunda. I en faktatabell används primärnyckeln för att identifiera varje enskild rad. Även om det är nödvändigt i normaliseringssyfte är det mindre användbart i en datamodell där du endast vill ha de kolumner som används för analys eller för att skapa tabellrelationer. Ta därför inte med primärnyckeln när du importerar från en faktatabell. Primärnycklar i en faktatabell tar upp enorma mängder utrymme i modellen, men ger ändå ingen nytta eftersom de inte kan användas för att skapa relationer.
Obs
I informationslager och flerdimensionella databaser kallas stora tabeller som huvudsakligen 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 till organisationsenheter, produkter, marknadssegment, geografiska regioner och så vidare. Alla kolumner i en faktatabell som innehåller affärsdata eller som kan användas för att korsreferera till data som lagras i andra tabeller bör ingå i modellen för att stödja dataanalys. Den kolumn som du vill undanta är primärnyckelkolumnen i faktatabellen, som består av unika värden som bara finns i faktatabellen och ingen annanstans. Eftersom faktatabellerna är så omfattande härleds några av de största vinsterna i modelleffektivitet från att rader eller kolumner utesluts från faktatabeller.
Så här utesluter 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 ska ingå i modellen måste du använda guiden Importera tabell i tilläggsprogrammet Power Pivot för att importera data i stället för i dialogrutan "Importera data" i Excel.
När du startar guiden Importera tabell väljer du vilka tabeller du vill importera.
För varje tabell kan du klicka på Förhandsgranska & filtrera 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 med att kontrollera de kolumner du vill använda, efter att ha övervägt om de behövs för analysen.
Hur filtrerar jag bara de rader som behövs?
Många tabeller i företagsdatabaser och informationslager innehåller historiska data som ackumulerats över långa tidsperioder. Dessutom kan det hända att tabellerna som du är intresserad av innehåller information för verksamhetsområden som inte krävs för din specifika analys.
Med 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.
Tänk om vi behöver kolumnen; Kan vi fortfarande minska utrymmeskostnaden?
Det finns ytterligare några tekniker som 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 Datetime-kolumner
I många fall tar Datetime-kolumner upp 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 hur bekväm du är med att skapa SQL-frågor.
Datetime-kolumner innehåller en datumdel och en tid. När du frågar dig själv om du behöver en kolumn bör du ställa samma fråga flera gånger för en datumkolumn:
- Behöver jag tidsdelen?
- Behöver jag tidsdelen på timnivå? , minuter? , sekunder? , millisekunder?
- Har jag flera Datetime-kolumner för att beräkna skillnaden mellan dem, eller bara för att aggregera data efter år, månad, kvartal och så vidare.
Hur du besvarar var och en av de här frågorna avgör vilka alternativ du har för att hantera kolumnen Datumtid.
För alla de här lösningarna måste du ändra en SQL-fråga. För att göra det lättare att ändra frågan bör du filtrera bort minst en kolumn i varje tabell. Genom att filtrera bort en kolumn ändrar du frågans konstruktion från ett förkortat format (SELECT *) till ett SELECT-uttryck som innehåller fullständigt kvalificerade kolumnnamn, som är mycket enklare att ändra.
Låt oss ta en titt på de frågor som skapas automatiskt. Från 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 någon 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, kommer du att se frågan som "Markera * 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å din modell.
- För kolumner som innehåller valuta- eller decimaldata kan du använda följande syntax för att ta bort decimaler:
"MARKERA AVRUNDA([Decimal_column_name],0)... .”
Om du behöver cent men inte bråkdelar 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. Stort bord. [Datum/tid] och du inte behöver tidsdelen, använder du syntaxen för att ta bort tiden:
"VÄLJ CAST (dbo. Stort bord. [Datum, tid], som datum), AS [Datum, tid]) " - Om du har en Datetime-kolumn med namnet dbo. Stort bord. [Date Time] 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 Datetime-kolumnen:
"VÄLJ CAST (dbo. Stort bord. [Datum/tid] som datum ) AS [datum/tid],
DatePart (HH, dbo. Stort bord. [datum/tid]) som [datum/tid, timmar],
DatePart(MI, dbo. Stort bord. [datum/tid]) som [datum/tid, minuter],
DatePart(ss, dbo. Stort bord. [datum/tid]) som [datum/tid sekunder],
DatePart(MS, dbo. Stort bord. [datum/tid]) som [datum/tid, millisekunder]"
Använd så många kolumner som behövs 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. Stort bord. [Datum/tid]), datumdel(mm, dbo. Stort bord. [Datum/tid])) som [Datum, tid, timmeminut] - Om du har två datetime-kolumner, till exempel [Starttid] och [Sluttid], och det du egentligen behöver är tidsskillnaden i sekunder som en kolumn med namnet [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åk 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 bara utvärderas 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 underhålla två och beräkna den tredje med DAX.
Vilka 2 kolumner ska du behålla?
I exemplet ovan behåller du Antal och Enhetspris. Dessa två har färre värden än summan. Om du vill beräkna totalsumman lägger du till ett beräknat mått så här:
"TotalSales:=sumx('Sales-tabell','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Beräknade kolumner liknar 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. Du kan minska filstorleken och minneskraven för en datamodell genom 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 gick igenom:
- 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 guiden Importera tabell.
- I allmänhet ä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 kommer att ha ett litet antal unika värden och den sammanlagda summan kommer att vara mindre än den ursprungliga enhetliga kolumnen.
- I många fall behöver du också de distinkta delarna för att kunna använda dem som utsnitt i rapporterna. När det är lämpligt kan du skapa hierarkier från delar som timmar, minuter och sekunder.
- Många gånger innehåller kolumner också mer information än du behöver. Anta till exempel att decimaler lagras i en kolumn, men att du har använt formatering för att dölja 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 för att minska storleken på arbetsboken kan du även köra Workbook Size Optimizer. Den analyserar din Excel-arbetsbok och komprimerar den ytterligare om det är möjligt. Hämta Workbook Size Optimizer.
Relaterade länkar
Specifikationer och begränsningar för datamodeller
PowerPivot: Kraftfull dataanalys och datamodellering i Excel