I Excel 2013 eller nyere kan du oprette datamodeller, der indeholder millioner af rækker, og derefter udføre effektive dataanalyser mod disse modeller. Datamodeller kan oprettes med eller uden det Power Pivot tilføjelsesprogram for at understøtte et vilkårligt antal pivottabeller, diagrammer og Power View-visualiseringer i den samme projektmappe.
Bemærk!: I denne artikel beskrives datamodeller i Excel 2013. Men de samme datamodellerings- og Power Pivot-funktioner, der blev introduceret i Excel 2013, gælder også for Excel 2016. Der er faktisk ikke meget forskel mellem disse versioner af Excel.
Selvom du nemt kan bygge enorme datamodeller i Excel, er der flere grunde til ikke at gøre det. For det første er store modeller, der indeholder mange tabeller og kolonner, overkvalificeret til de fleste analyser og udgør en besværlig feltliste. For det andet bruger store modeller værdifuld hukommelse og påvirker andre programmer og rapporter, der deler de samme systemressourcer, negativt. I Microsoft 365begrænser både SharePoint Online og Excel Web App størrelsen på en Excel-fil til 10 MB. For projektmappedatamodeller, der indeholder millioner af rækker, løber du ret hurtigt ind i grænsen på 10 MB. Se Specifikation og begrænsninger for datamodel.
I denne artikel lærer du, hvordan du opbygger en stramt konstrueret model, der er nemmere at arbejde med og bruger mindre hukommelse. Hvis du tager dig tid til at lære de bedste fremgangsmåder i effektivt modeldesign, kan du komme videre med alle modeller, du opretter og bruger, uanset om du får den vist i Excel 2013, Microsoft 365 SharePoint Online, på en Office Online Server eller i SharePoint 2013.
Overvej også at køre Workbook Size Optimizer. Det analyserer din Excel-projektmappe og komprimerer den yderligere, hvis det er muligt. Download Workbook Size Optimizer.
I denne artikel
Komprimeringsforhold og analyseprogrammet i hukommelsen
Datamodeller i Excel bruger analyseprogrammet i hukommelsen til at gemme data i hukommelsen. Programmet implementerer effektive komprimeringsteknikker for at reducere lagringsbehovet og reducerer et resultatsæt, indtil det er en brøkdel af dets oprindelige størrelse.
I gennemsnit kan du forvente, at en datamodel er 7 til 10 gange mindre end de samme data på oprindelsesstedet. Hvis du f.eks. importerer 7 MB data fra en SQL Server database, kan datamodellen i Excel nemt være 1 MB eller mindre. Graden af komprimering, der faktisk opnås, afhænger primært af antallet af entydige værdier i hver kolonne. Jo flere entydige værdier, jo mere hukommelse kræves der for at gemme dem.
Hvorfor taler vi om komprimering og unikke værdier? Da det at opbygge en effektiv model, der minimerer hukommelsesforbruget, handler om komprimeringsmaksimering, og den nemmeste måde at gøre det på er at fjerne kolonner, du egentlig ikke har brug for, især hvis disse kolonner indeholder et stort antal entydige værdier.
Bemærk!: Forskellene i lagringskrav for individuelle kolonner kan være enorme. I nogle tilfælde er det bedre at have flere kolonner med et lavt antal entydige værdier i stedet for én kolonne med et stort antal entydige værdier. Afsnittet om Datetime-optimeringer dækker denne teknik i detaljer.
Intet slår en ikke-eksisterende kolonne for lavt hukommelsesforbrug
Den mest hukommelseseffektive kolonne er den kolonne, du aldrig har importeret i første omgang. Hvis du vil oprette en effektiv model, skal du se på hver kolonne og spørge dig selv, om den bidrager til den analyse, du vil udføre. Hvis det ikke sker, eller du ikke er sikker, skal du udelade det. Du kan altid tilføje nye kolonner senere, hvis du har brug for dem.
To eksempler på kolonner, der altid skal udelades
Det første eksempel vedrører data, der stammer fra et datalager. I et datalagersted er det almindeligt at finde artefakter af ETL-processer, der indlæser og opdaterer data på lageret. Kolonner som "opret dato", "opdateringsdato" og "ETL-kørsel" oprettes, når dataene indlæses. Ingen af disse kolonner er nødvendige i modellen og bør fravælges, når du importerer data.
I det andet eksempel udelades kolonnen med den primære nøgle, når du importerer en faktatabel.
Mange tabeller, herunder faktatabeller, har primære nøgler. For de fleste tabeller, f.eks. dem, der indeholder kunde-, medarbejder- eller salgsdata, skal du bruge tabellens primære nøgle, så du kan bruge den til at oprette relationer i modellen.
Faktatabeller er forskellige. I en faktatabel bruges den primære nøgle til entydigt at identificere hver række. Selvom det er nødvendigt til normaliseringsformål, er det mindre nyttigt i en datamodel, hvor du kun vil have de kolonner, der bruges til analyse eller til at etablere tabelrelationer. Når du importerer fra en faktatabel, skal du derfor ikke medtage den primære nøgle. Primære nøgler i en faktatabel bruger enorme mængder plads i modellen, men giver ingen fordel, da de ikke kan bruges til at oprette relationer.
Bemærk!: I datalagre og flerdimensionelle databaser omtales store tabeller, der hovedsageligt består af numeriske data, ofte som "faktatabeller". Faktatabeller omfatter typisk forretningsdata eller transaktionsdata, f.eks. salgs- og omkostningsdatapunkter, der er aggregeret og justeret til organisatoriske enheder, produkter, markedssegmenter, geografiske områder osv. Alle kolonnerne i en faktatabel, der indeholder forretningsdata, eller som kan bruges til krydshenvisningsdata, der er gemt i andre tabeller, skal medtages i modellen for at understøtte dataanalyse. Den kolonne, du vil udelade, er den primære nøglekolonne i faktatabellen, som består af entydige værdier, der kun findes i faktatabellen og ikke andre steder. Da faktatabeller er så store, er nogle af de største gevinster i modellens effektivitet afledt af at udelukke rækker eller kolonner fra faktatabeller.
Sådan udelades unødvendige kolonner
Effektive modeller indeholder kun de kolonner, du rent faktisk skal bruge i projektmappen. Hvis du vil styre, hvilke kolonner der medtages i modellen, skal du bruge guiden Tabelimport i tilføjelsesprogrammet Power Pivot til at importere dataene i stedet for dialogboksen "Importér data" i Excel.
Når du starter guiden Tabelimport, vælger du, hvilke tabeller der skal importeres.
For hver tabel kan du klikke på knappen Eksempel & Filter og vælge de dele af tabellen, du virkelig har brug for. Vi anbefaler, at du først fjerner markeringen i alle kolonner og derefter fortsætter med at kontrollere de ønskede kolonner, når du har overvejet, om de er nødvendige for analysen.
Hvad med kun at filtrere de nødvendige rækker?
Mange tabeller i virksomhedsdatabaser og datalagre indeholder historiske data, der er akkumuleret over lange perioder. Desuden kan det være, at de tabeller, du er interesseret i, indeholder oplysninger om områder af virksomheden, der ikke er nødvendige for din specifikke analyse.
Ved hjælp af guiden Tabelimport kan du filtrere historiske eller ikke-relaterede data fra og dermed spare en masse plads i modellen. På følgende billede bruges et datofilter kun til at hente rækker, der indeholder data for det aktuelle år, med undtagelse af historiske data, der ikke er nødvendige.
Hvad nu, hvis vi har brug for kolonnen. kan vi stadig reducere dens plads omkostninger?
Der er et par ekstra teknikker, du kan anvende for at gøre en kolonne til en bedre kandidat til komprimering. Husk, at den eneste egenskab ved den kolonne, der påvirker komprimering, er antallet af entydige værdier. I dette afsnit lærer du, hvordan nogle kolonner kan ændres for at reducere antallet af entydige værdier.
Ændring af Datetime-kolonner
I mange tilfælde tager datetime-kolonner meget plads. Heldigvis er der en række måder at reducere lagringskravene til denne datatype på. Teknikkerne varierer afhængigt af, hvordan du bruger kolonnen, og dit komfortniveau i opbygningen af SQL-forespørgsler.
Datetime-kolonner indeholder en datodel og et klokkeslæt. Når du spørger dig selv, om du har brug for en kolonne, kan du stille det samme spørgsmål flere gange for en Datetime-kolonne:
-
Skal jeg bruge tid?
-
Skal jeg bruge tidsdelen på timeniveau? Minutter? Sekunder? Millisekunder?
-
Har jeg flere Datetime-kolonner, fordi jeg vil beregne forskellen mellem dem eller blot for at sammenlægge dataene efter år, måned, kvartal osv.
Hvordan du besvarer hvert af disse spørgsmål, bestemmer dine muligheder for at håndtere kolonnen Datetime.
Alle disse løsninger kræver ændring af en SQL-forespørgsel. For at gøre det nemmere at ændre forespørgslen skal du bortfiltrere mindst én kolonne i hver tabel. Ved at filtrere en kolonne ud ændrer du forespørgslens konstruktion fra et forkortet format (SELECT *) til en SELECT-sætning, der indeholder fuldt kvalificerede kolonnenavne, som er meget nemmere at ændre.
Lad os se nærmere på de forespørgsler, der er oprettet til dig. Fra dialogboksen Tabelegenskaber kan du skifte til Forespørgselseditor og se den aktuelle SQL-forespørgsel for hver tabel.
Vælg Power Query-editor under Egenskaber for tabel.
I Power Query-editor vises den SQL-forespørgsel, der bruges til at udfylde tabellen. Hvis du filtrerede en kolonne fra under importen, indeholder forespørgslen fuldt kvalificerede kolonnenavne:
|
Hvis du har importeret en tabel i sin helhed uden at fjerne markeringen i en kolonne eller anvende et filter, får du derimod vist forespørgslen som "Vælg * fra", hvilket vil være sværere at ændre:
|
Ændring af SQL-forespørgslen
Nu hvor du ved, hvordan du finder forespørgslen, kan du ændre den for yderligere at reducere størrelsen på din model.
-
Hvis du ikke har brug for decimaler i kolonner, der indeholder valuta- eller decimaldata, kan du bruge denne syntaks til at fjerne decimalerne:
"SELECT ROUND([Decimal_column_name],0)... .”
Hvis du har brug for cents, men ikke decimaler af cents, erstatte 0 med 2. Hvis du bruger negative tal, kan du afrunde til enheder, tiere, hundredvis osv.
-
Hvis du har en Datetime-kolonne med navnet dbo. Kan gøre det stort. [Dato klokkeslæt], og du behøver ikke at bruge tidsdelen, skal du bruge syntaksen til at fjerne klokkeslættet:
"VÆLG CAST (dbo. Kan gøre det stort. [Dato klokkeslæt] som dato) AS [Dato klokkeslæt]) "
-
Hvis du har en Datetime-kolonne med navnet dbo. Kan gøre det stort. [Dato klokkeslæt], og du skal bruge både dato- og klokkeslætsdelene, skal du bruge flere kolonner i SQL-forespørgslen i stedet for den enkelte Datetime-kolonne:
"VÆLG CAST (dbo. Kan gøre det stort. [Date Time] as date ) AS [Date Time],
datepart(hh; dbo. Kan gøre det stort. [Dato klokkeslæt]) as [Date Time Hours],
datepart(mi, dbo. Kan gøre det stort. [Dato klokkeslæt]) as [Date Time Minutes],
datepart(ss, dbo. Kan gøre det stort. [Dato klokkeslæt]) as [Date Time Seconds],
datepart(ms; dbo. Kan gøre det stort. [Dato klokkeslæt]) as [Date Time Millisekunder]"
Brug lige så mange kolonner, som du har brug for til at gemme hver del i separate kolonner.
-
Hvis du har brug for timer og minutter, og du foretrækker dem sammen som en enkelt kolonne, kan du bruge syntaksen :
Klokkeslætfradel(datodel(tt; dbo. Kan gøre det stort. [Dato klokkeslæt]), datepart(mm, dbo. Kan gøre det stort. [Dato klokkeslæt])) as [Date Time TimeMinute]
-
Hvis du har to datetime-kolonner, f.eks. [Starttidspunkt] og [Sluttidspunkt], og det, du virkelig har brug for, er tidsforskellen mellem dem i sekunder som en kolonne med navnet [Varighed], skal du fjerne begge kolonner fra listen og tilføje:
"dateiff(ss,[Startdato],[Slutdato]) som [Varighed]"
Hvis du bruger nøgleordet ms i stedet for ss, får du varigheden i millisekunder
Brug af beregnede DAX-mål i stedet for kolonner
Hvis du har arbejdet med DAX-udtrykssproget før, ved du måske allerede, at beregnede kolonner bruges til at udlede nye kolonner baseret på en anden kolonne i modellen, mens beregnede mål defineres én gang i modellen, men kun evalueres, når de bruges i en pivottabel eller en anden rapport.
En hukommelsesbesparende metode er at erstatte almindelige eller beregnede kolonner med beregnede mål. Det klassiske eksempel er Enhedspris, Antal og Total. Hvis du har alle tre, kan du spare plads ved kun at bevare to og beregne den tredje ved hjælp af DAX.
Hvilke 2 kolonner skal du beholde?
Bevar Antal og Enhedspris i eksemplet ovenfor. Disse to har færre værdier end totalen. Hvis du vil beregne totalen, skal du tilføje et beregnet mål som f.eks.:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Beregnede kolonner er som almindelige kolonner, hvor de begge optager plads i modellen. I modsætning hertil beregnes beregnede mål på farten og optager ikke plads.
Konklusion
I denne artikel talte vi om flere metoder, der kan hjælpe dig med at opbygge en mere hukommelseseffektiv model. En datamodels krav til filstørrelse og hukommelse kan reduceres ved at reducere det samlede antal kolonner og rækker og antallet af entydige værdier, der vises i hver kolonne. Her er nogle teknikker, vi har dækket:
-
At fjerne kolonner er naturligvis den bedste måde at spare plads på. Beslut, hvilke kolonner du virkelig har brug for.
-
Nogle gange kan du fjerne en kolonne og erstatte den med et beregnet mål i tabellen.
-
Du behøver muligvis ikke alle rækkerne i en tabel. Du kan filtrere rækker fra i guiden Tabelimport.
-
Generelt er det en god ide at opdele en enkelt kolonne i flere forskellige dele for at reducere antallet af entydige værdier i en kolonne. Hver af delene har et lille antal entydige værdier, og den samlede total vil være mindre end den oprindelige samlede kolonne.
-
I mange tilfælde skal du også bruge de entydige dele til at bruge som udsnit i dine rapporter. Når det er relevant, kan du oprette hierarkier ud fra dele som Timer, Minutter og Sekunder.
-
Mange gange indeholder kolonner flere oplysninger, end du også har brug for dem. Antag f.eks., at en kolonne gemmer decimaler, men du har anvendt formatering for at skjule alle decimaler. Afrunding kan være meget effektiv til at reducere størrelsen på en numerisk kolonne.
Nu hvor du har gjort, hvad du kan for at reducere størrelsen på din projektmappe, kan du overveje også at køre Optimering af projektmappestørrelse. Det analyserer din Excel-projektmappe og komprimerer den yderligere, hvis det er muligt. Download Workbook Size Optimizer.
Relaterede links
Specifikation og grænser for datamodel
Optimering af projektmappestørrelse
Power Pivot: Effektiv dataanalyse og datamodellering i Excel
