Skapa en minnes effektiv data modell med Excel och Power Pivot-tilläggsprogrammet

I Excel 2013 och senare versioner kan du skapa data modeller som innehåller miljon tals rader och sedan utföra kraftfulla data analyser mot dessa modeller. Data modeller kan skapas med eller utan Power Pivot-tillägget för att stödja ett obegränsat antal pivottabeller, diagram och Power View-visualiseringar i samma arbets bok.

Obs!: I den här artikeln beskrivs data modeller i Excel 2013. Men samma data modellerings-och Power Pivot-funktioner i Excel 2013 gäller för Excel 2016. Det är mycket skillnad mellan dessa versioner av Excel.

Även om det är enkelt att skapa stora data modeller i Excel finns det flera orsaker till det. För det första är stora modeller som innehåller mängder av tabeller och kolumner overkill för de flesta analyser och en besvärlig fält lista. För det andra kan stora modeller använda värdefullt minne som påverkar inte andra program och rapporter som har samma system resurser. I Office 365 begränsar både SharePoint Online och Excel Web App storleken på en Excel-fil till 10 MB. För arbets boks data modeller som innehåller miljon tals rader får du snabbt 10 MB stor gräns. Se specifikation och begränsningar för data modeller.

I den här artikeln får du lära dig hur du skapar en väl konstruerad modell som är lättare att arbeta med och som använder mindre minne. Att ta reda på de bästa metoderna i effektiv modell design kommer att betala intill alla modeller som du skapar och använder, oavsett om du visar dem i Excel 2013, Office 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 arbets bokens storleks optimering.

Artikelinnehåll

Komprimerings förhållanden och motorn i minnet

Inget slår en kolumn som inte finns för att få ont om minne

Två exempel på kolumner som alltid ska uteslutas

Utelämna onödiga kolumner

Vad sägs om att filtrera bara de rader som behövs?

Vad händer om kolumnen behövs? kan vi fortfarande minska sin utrymmes kostnad?

Ändra datetime-kolumner

Ändra SQL-frågan

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

Vilka två kolumner ska du behålla?

Sammanfattning

Relaterade länkar

Komprimerings förhållanden och motorn i minnet

Data modeller i Excel använder motorn i minnet för att lagra data i minnet. Motorn implementerar kraftfulla komprimerings metoder för att minska lagrings kraven och krymper en resultat uppsättning tills den är en bråkdel av dess ursprungliga storlek.

I genomsnitt kan du förvänta dig att en data modell är 7 till tio gånger mindre än samma data vid dess ursprung. Om du till exempel importerar 7 MB data från en SQL Server-databas kan data modellen i Excel vara 1 MB eller mindre. Komprimerings graden beror huvudsakligen på antalet unika värden i varje kolumn. De unika värdena, desto mer minne behövs för att lagra dem.

Varför pratar vi om komprimering och unika värden? Eftersom det är enklare att bygga en effektiv modell som minimerar minnes användningen är allt mer om komprimering, och det enklaste sättet att göra det är att ta bort alla kolumner som du inte behöver, särskilt om de kolumnerna innehåller ett stort antal unika värden.

Obs!:  Skillnaderna i lagrings kraven för enskilda kolumner kan vara mycket 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. Det här avsnittet innehåller detaljerad information om hur du använder datetime-optimeringar.

Inget slår en kolumn som inte finns för att få ont om minne

Den mest effektiva kolumnen är den som du aldrig har importerat första gången. Om du vill skapa en effektiv modell kan du titta på varje kolumn och be dig själv att få den analys du vill utföra. Om det inte är det eller om du inte är säker kan du lämna det. 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 är kopplat till data som härstammar från ett data lager. I ett data lager är det vanligt att hitta artefakter i ETL-processer som läser in och uppdaterar data i distributions lagret. Kolumner som "Skapa datum", "uppdatera datum" 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.

Det andra exemplet inbegriper att utesluta primär kolumnen när du importerar en fakta tabell.

Många tabeller, inklusive fakta tabeller, har primär nycklar. För de flesta tabeller, till exempel personer som innehåller kund-, medarbetar-eller försäljnings data, ska tabellens primär nycklar användas för att skapa relationer i modellen.

Fakta tabeller är olika. I en fakta tabell används primär nycklarna för att identifiera varje rad. När det behövs för normalisering är det mindre användbart i en data modell där du bara vill använda de kolumner som används för analys eller för att etablera tabell relationer. Av den anledningen bör du inte ta med primärt. Primär nycklar i en fakta tabell förbrukar mycket utrymme i modellen och ger ännu ingen förmån, eftersom de inte kan användas för att skapa relationer.

Obs!:  I data lager och flerdimensionella databaser kallas stora tabeller som mest numeriska data ofta till "fakta tabeller". Fakta tabeller innehåller vanligt vis företags prestanda eller transaktions data, till exempel försäljnings-och kostnads data punkter som aggregeras och justerats efter organisatoriska enheter, produkter, marknads segment, geografiska områden och så vidare. Alla kolumner i en fakta tabell som innehåller affärs data eller som kan användas för kors referens data som lagras i andra tabeller bör ingå i modellen för data analys. Kolumnen som du vill exkludera är primär kolumnen i fakta tabellen, som består av unika värden som bara finns i fakta tabellen och utan någon annan. Eftersom fakta tabeller är mycket stora kommer vissa av de största fördelarna med modell effektivitet att utgå från att utesluta rader och kolumner från fakta tabeller.

Utelämna onödiga kolumner

Effektiva modeller innehåller bara de kolumner som du faktiskt behöver i din arbets bok. Om du vill styra vilka kolumner som ska ingå i modellen måste du använda guiden Importera tabell i Power Pivot-tilläggsprogrammet för att importera data i stället för i dialog rutan "Importera data" i Excel.

När du startar guiden Importera tabell väljer du vilka tabeller du vill importera.

Tabellimportguiden i tilläggsprogrammet PowerPivot

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 att kontrol lera vilka kolumner du vill använda, efter att du funderar på om de är obligatoriska för analysen.

Fönstret för förhandsgranskning i tabellimportguiden

Vad sägs om att filtrera bara de rader som behövs?

Många tabeller i företags databaser och data lager innehåller historiska data som samlats in under långa tids perioder. Dessutom kanske de tabeller du är intresse rad av innehåller information för företags områden som inte behövs för din specifika analys.

Med hjälp av guiden Importera tabell kan du filtrera historik eller ej relaterade data, och därför spara mycket utrymme i modellen. I följande bild används ett datum filter för att hämta endast rader som innehåller data för det aktuella året, exklusive historiska data som inte behövs.

Filterfönstret i tabellimportguiden

Vad händer om kolumnen behövs? kan vi fortfarande minska sin utrymmes kostnad?

Det finns några andra metoder som du kan använda för att göra en kolumn till bättre. Kom ihåg att den enda egenskapen i kolumnen som påverkar komprimering är antalet unika värden. I det här avsnittet får du lära dig hur du kan ändra vissa kolumner för att minska antalet unika värden.

Ändra datetime-kolumner

I många fall tar det väldigt mycket utrymme i datum/tid-kolumner. Som tur är finns det flera olika sätt att minska lagrings kraven för den här data typen. Metoderna varierar beroende på hur du använder kolumnen och din komfort nivå när det gäller att bygga SQL-frågor.

Datetime-kolumner innehåller en datum del och en tid. När du blir tillfrågad om du behöver en kolumn kan du fråga samma fråga flera gånger för en datetime-kolumn:

  • Behöver jag Time-delen?

  • Behöver jag Time-delen på antalet timmar? protokoll? SEK? millisekunder?

  • Har jag flera datetime-kolumner eftersom jag vill beräkna skillnaden mellan dem, eller bara för att samla in data per år, månad, kvartal och så vidare.

Hur du besvarar de här frågorna avgör vilka alternativ som gäller för kolumnen DateTime.

Alla de här lösningarna kräver ändring av en SQL-fråga. För att göra det lättare att ändra frågor bör du filtrera bort minst en kolumn i varje tabell. Genom att filtrera bort en kolumn ändrar du Frågedesign från ett förkortat format (Välj *) till ett SELECT-uttryck som innehåller fullständigt kvalificerade kolumn namn, som är mycket enklare att ändra.

Låt oss ta en titt på frågorna som skapas åt dig. I dialog rutan tabell egenskaper kan du växla till Frågeredigeraren och se den aktuella SQL-frågan för varje tabell.

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

Välj Frågeredigerarenfrån tabell egenskaper.

Öppna Frågeredigeraren från dialogrutan Tabellegenskaper

Frågeredigeraren visar den SQL-fråga som används för att fylla tabellen. Om du har filtrerat bort en kolumn under importen innehåller frågan fullständigt kvalificerade kolumn namn:

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

Om du däremot har importerat en tabell i sin helhet utan att avmarkera någon kolumn eller tillämpa något filter visas frågan som "Välj * från", vilket blir svårare att ändra:

SQL-fråga med kortare standardsyntax

Ändra SQL-frågan

Nu när du vet hur du hittar frågan kan du ändra den för att minska storleken på modellen.

  1. Använd den här syntaxen för kolumner som innehåller valuta-eller decimal data om du inte behöver decimalerna:

    "Välj avrunda ([Decimal_column_name]; 0)... .”

    Om du behöver cent men inte bråktal av cent ersätter du 0 med 2. Om du använder negativa tal kan du avrunda till enheter, till och med hundratals.

  2. Om du har en datetime-kolumn med namnet dbo. Bigtable. [Date time] och du behöver inte Time-delen, Använd syntaxen för att ta bort tiden:

    "Välj CAST (dbo. Bigtable. [Date time] as date) AS [date time]) "

  3. Om du har en datetime-kolumn med namnet dbo. Bigtable. [Date time] och du behöver både datum och tid kan du använda flera kolumner i SQL-frågan i stället för den enda kolumnen datetime:

    "Välj CAST (dbo. Bigtable. [Date time] as date) AS [date time],

    DatumDel (hh, dbo. Bigtable. [Datum/tid]) som [datum tid timmar],

    DatumDel (mi; dbo. Bigtable. [Datum/tid]) som [datum tid minuter],

    DatumDel (SS, dbo. Bigtable. [Datum/tid]) som [date time Seconds],

    DatumDel (MS, dbo. Bigtable. [Datum/tid]) som [datum tid millisekunder] "

    Använd så många kolumner som behövs för att lagra varje del i separata kolumner.

  4. Om du behöver timmar och minuter, och du vill att de ska vara tillsammans som en tids kolumn, kan du använda syntaxen:

    Tidfråndelar (DatumDel (hh, dbo. Bigtable. [Date time]), DatumDel (mm, dbo. Bigtable. [Datum/tid])) as [datum tid HourMinute]

  5. Om du har två datetime-kolumner, till exempel [Start tid] och [slut tid] och vad du behöver är tids skillnaden mellan dem i sekunder som en kolumn med namnet [duration], tar du bort båda kolumnerna från listan och lägger till:

    "DateDiff (SS, [start datum], [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 ett DAX Expression-språk förut kanske du redan vet 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 utvärderats bara när de används i ett Pivottabell eller annan rapport.

En minnes besparings teknik är att ersätta vanliga eller beräknade kolumner med beräknade mått. Det klassiska exemplet är enhets pris, antal och total summa. Om du har alla tre kan du spara utrymme genom att behålla bara två och beräkna det tredje som använder DAX.

Vilka två kolumner ska du behålla?

I exemplet ovan, Behåll antal och enhets pris. De här två har färre värden än summan. Om du vill beräkna summan lägger du till ett beräknat mått som:

"Total försäljning: = SUMX (' Sales tabell ', ' Sales Table ' [enhets pris] * ' försäljnings tabell ' [antal])"

Beräknade kolumner är som vanliga kolumner i både för att ta upp utrymme i modellen. Beräknade mått beräknas dock inte och ta inte utrymme.

Sammanfattning

I den här artikeln har vi talat om flera metoder som kan hjälpa dig att skapa en mer minnes effektiv modell. Sättet att minska fil storleken och minnes kraven för en data modell ä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 täckt:

  • Att ta bort kolumner är självklart att spara utrymme. Bestäm vilka kolumner du 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 tabellen import.

  • Att dela upp en kolumn i flera olika delar är generellt ett bra sätt att minska antalet unika värden i en kolumn. Alla delar får ett fåtal unika värden och den sammanlagda summan blir mindre än den ursprungliga enhetliga kolumnen.

  • I många fall behöver du också de distinkta delarna för att använda dem som utsnitt i dina rapporter. Vid behov kan du skapa hierarkier från delar som timmar, minuter och sekunder.

  • Många gånger innehåller kolumnerna mer information än vad du också behöver. 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 när du vill minska storleken på en numerisk kolumn.

Nu när du har gjort vad du kan göra för att minska arbets bokens storlek kan du också använda funktionen optimera storlek för arbets böcker. Den analyserar din Excel-arbetsbok och komprimerar den ytterligare om det är möjligt. Ladda ned arbets bokens storleks optimering.

Relaterade länkar

Specifikationer och begränsningar för datamodeller

Nedladdning av arbets boks storleks optimering

PowerPivot: Kraftfull dataanalys och datamodellering i Excel

Utöka dina Office-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.

×