I Excel kan du oprette datamodeller, der indeholder millioner af rækker, og derefter udføre effektive dataanalyser af disse modeller. Datamodeller kan oprettes med eller uden tilføjelsesprogrammet Power Pivot for at understøtte et vilkårligt antal pivottabeller, diagrammer og Power View-visualiseringer i samme projektmappe.
Selvom du nemt kan bygge store datamodeller i Excel, er der flere grunde til ikke at gøre det. For det første er store modeller, der indeholder masser af tabeller og kolonner, for det samme for de fleste analyser, og de udgør en besværlig feltliste. For det andet bruger store modeller værdifuld hukommelse, hvilket påvirker andre programmer og rapporter, der deler de samme systemressourcer, negativt. Endelig begrænser både SharePoint Online og Excel Web App i Microsoft 365 størrelsen på en Excel-fil til 10 MB. For datamodeller i projektmapper, der indeholder millioner af rækker, vil du ret hurtigt løbe ind på grænsen på 10 MB. Se Specifikation og begrænsninger for datamodellen.
I denne artikel kan du se, hvordan du bygger en stramt sammenbygget model, der er nemmere at arbejde med og bruger mindre hukommelse. At tage sig tid til at lære de bedste fremgangsmåder inden for effektivt modeldesign vil betale sig hen ad vejen for enhver model, du opretter og bruger, uanset om du får den vist i Excel, Microsoft 365 SharePoint Online, på en Office Web Apps Server eller i SharePoint.
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.
Denne artikel indeholder
Intet slår en ikke-eksisterende kolonne for lavt hukommelsesforbrug
Hvad hvis vi har brug for kolonnen; Kan vi stadig reducere pladsomkostningerne?
Komprimeringsforhold og programmet til in-memory-analyse
Datamodeller i Excel bruger in-memory-analyseprogrammet til at gemme data i hukommelsen. Motoren implementerer kraftfulde kompressionsteknikker for at reducere opbevaringskravene og formindsker et resultatsæt, indtil det er en brøkdel af sin oprindelige størrelse.
I gennemsnit kan du forvente, at en datamodel er 7 til 10 gange mindre end de samme data på dens udgangspunkt. Hvis du f.eks. importerer 7 MB data fra en SQL Server-database, kan datamodellen i Excel nemt være 1 MB eller mindre. Den komprimeringsgrad, 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 til 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 alle kolonner, du reelt ikke har brug for, især hvis disse kolonner indeholder et stort antal entydige værdier.
Bemærk
Forskellene i lagringskrav for de enkelte 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 højt 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, du aldrig importerede i første omgang. Hvis du vil bygge en effektiv model, skal du kigge på hver kolonne og spørge dig selv, om den bidrager til den analyse, du vil udføre. Hvis det ikke virker, 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 datavarehus. I et datalager er det almindeligt at finde artefakter af ETL-processer, der indlæser og opdaterer data i lageret. Kolonner som "oprettelsesdato", "opdateringsdato" og "ETL-kørsel" oprettes, når dataene indlæses. Ingen af disse kolonner er nødvendige i modellen og skal fravælges, når du importerer data.
Det andet eksempel indebærer, at kolonnen med den primære nøgle udelades, 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 anderledes. I en faktatabel bruges den primære nøgle til entydigt at identificere hver række. Selvom det er nødvendigt af hensyn til normalisering, 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 optager enorme mængder plads i modellen, men giver ingen fordel, da de ikke kan bruges til at oprette relationer.
Bemærk
I datavarehuse og multidimensionelle databaser omtales store tabeller, der hovedsageligt består af numeriske data, ofte som "faktatabeller". Faktatabeller indeholder typisk virksomhedens ydelses- eller transaktionsdata, f.eks. salgs- og omkostningsdatapunkter, der er aggregeret og justeret efter organisationsenheder, produkter, markedssegmenter, geografiske områder osv. Alle de kolonner i en faktatabel, der indeholder forretningsdata, eller som kan bruges til at krydsreferere data, der er gemt i andre tabeller, skal medtages i modellen for at understøtte dataanalyse. Den kolonne, du vil udelade, er kolonnen med den primære nøgle i faktatabellen, som består af entydige værdier, der kun findes i faktatabellen og ikke andre steder. Da faktatabeller er så store, kommer nogle af de største gevinster i modeleffektivitet fra at udelukke rækker eller kolonner fra faktatabeller.
Sådan udelades unødvendige kolonner
Effektive modeller indeholder kun de kolonner, du rent faktisk har brug for i din projektmappe. Hvis du vil styre, hvilke kolonner der medtages i modellen, skal du bruge guiden Tabelimport i Power Pivot-tilføjelsesprogrammet til at importere dataene i stedet for dialogboksen "Importér data" i Excel.
Når du starter guiden Tabelimport, vælger du, hvilke tabeller du vil importere.
For hver tabel kan du klikke på knappen Vis & filtrer og markere de dele af tabellen, som du virkelig har brug for. Vi anbefaler, at du først fjerner markeringen af alle kolonner og derefter fortsætter med at markere de ønskede kolonner, når du har overvejet, om de er nødvendige for analysen.
Hvad med filtrering af kun de nødvendige rækker?
Mange tabeller i firmadatabaser og datavarehuse indeholder historiske data, der er akkumuleret over lange tidsperioder. Desuden kan du opleve, at de tabeller, du er interesseret i, indeholder oplysninger om områder i virksomheden, som ikke er nødvendige for din specifikke analyse.
Ved hjælp af guiden Tabelimport kan du filtrere historiske eller ikke-relaterede data og dermed spare en masse plads i modellen. På følgende billede bruges et datofilter til kun at hente rækker, der indeholder data for det aktuelle år, med undtagelse af historiske data, der ikke er nødvendige.
Hvad hvis vi har brug for kolonnen; Kan vi stadig reducere pladsomkostningerne?
Der er et par yderligere teknikker, du kan anvende til at gøre en kolonne til en bedre kandidat til komprimering. Husk, at den eneste egenskab ved kolonnen, der påvirker komprimering, er antallet af entydige værdier. I dette afsnit kan du se, hvordan nogle kolonner kan ændres for at reducere antallet af entydige værdier.
Ændring af Datetime-kolonner
I mange tilfælde optager Datetime-kolonner meget plads. Heldigvis er der en række måder, hvorpå du kan reducere lagerkravene for denne datatype. Teknikkerne varierer, afhængigt af hvordan du bruger kolonnen, og hvor tryg du er ved at opbygge SQL-forespørgsler.
Datetime-kolonner omfatter en datodel og et klokkeslæt. Når du spørger dig selv, om du har brug for en kolonne, skal du stille det samme spørgsmål flere gange for en Datetime-kolonne:
- Har jeg brug for tidsdelen?
- Har jeg brug for tidsdelen på timeniveau? , minutter? , sekunder? , millisekunder?
- Har jeg flere Datetime-kolonner, fordi jeg vil beregne forskellen mellem dem, eller blot for at aggregere dataene efter år, måned, kvartal osv.
Den måde, du besvarer hvert af disse spørgsmål på, afgør, hvilke muligheder du har for at håndtere kolonnen Dato/klokkeslæt.
Alle disse løsninger kræver ændring af en SQL-forespørgsel. Du bør gøre det nemmere at ændre forespørgsler ved at filtrere mindst én kolonne i hver tabel. Ved at filtrere en kolonne fra, ændrer du forespørgselskonstruktionen 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 oprettes for dig. Fra dialogboksen Egenskaber for tabel kan du skifte til Forespørgselseditor og se den aktuelle SQL-forespørgsel for hver tabel.
Vælg Power Query-editor under Tabelegenskaber.
Power Query-editor viser den SQL-forespørgsel, der bruges til at udfylde tabellen. Hvis du har filtreret en kolonne ud under importen, indeholder forespørgslen fuldt kvalificerede kolonnenavne:
Men hvis du importerede en tabel i sin helhed uden at fjerne markeringen af nogen kolonne eller anvende noget filter, får du 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 at reducere størrelsen på modellen yderligere.
- Hvis kolonner, der indeholder valuta- eller decimaldata, ikke har brug for decimaler, kan du bruge denne syntaks til at fjerne decimalerne:
"VÆLG AFRUND([Decimal_column_name],0)... .”
Hvis du har brug for cent, men ikke brøkdele af cent, skal du erstatte 0 med 2. Hvis du bruger negative tal, kan du afrunde til enheder, tiere, hundreder osv. - Hvis du har en Datetime-kolonne med navnet dbo. Stor tabel. [Dato og klokkeslæt], og du ikke har brug for delen Tid, kan du bruge syntaksen til at fjerne klokkeslættet:
"VÆLG CAST (dbo. Stor tabel. [Dato/klokkeslæt] som dato) AS [Dato/klokkeslæt]) " - Hvis du har en Datetime-kolonne med navnet dbo. Stor tabel. [Dato/klokkeslæt], og du skal bruge både dato- og klokkeslætsdelen, skal du bruge flere kolonner i SQL-forespørgslen i stedet for den enkelte Datetime-kolonne:
"VÆLG CAST (dbo. Stor tabel. [Date Time] as date ) AS [Date Time],
DatePart(hh, dbo. Stor tabel. [Dato og klokkeslæt]) som [Dato og klokkeslæt timer],
DatePart(MI, dbo. Stor tabel. [Dato og klokkeslæt]) som [Dato og klokkeslæt minutter],
DatePart(ss, dbo. Stor tabel. [Dato og klokkeslæt]) som [Dato/klokkeslæt sekunder],
DatePart(ms, dbo. Stor tabel. [Dato og klokkeslæt]) som [Dato og klokkeslæt millisekunder]"
Brug lige så mange kolonner, 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 samlet som én tidskolonne, kan du bruge syntaksen:
Timefromparts(datepart(hh, dbo. Stor tabel. [Dato/klokkeslæt]), DatePart(mm, dbo. Stor tabel. [Dato og klokkeslæt])) som [Dato, Tid, TimeMinut] - Hvis du har to datetime-kolonner, f.eks. [Starttidspunkt] og [Sluttidspunkt], og det, du virkelig skal bruge, er tidsforskellen mellem dem i sekunder som en kolonne med navnet [Varighed], skal du fjerne begge kolonner fra listen og tilføje:
"datediff(ss,[Startdato],[Slutdato]) som [Varighed]"
Hvis du bruger nøgleordet ms i stedet for ss, får du varigheden i millisekunder
Brug af DAX-beregnede 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 andre rapporter.
En hukommelsesbesparende teknik 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 vedligeholde to og beregne den tredje ved hjælp af DAX.
Hvilke 2 kolonner skal du beholde?
I eksemplet ovenfor skal du beholde Antal og Enhedspris. Disse to har færre værdier end totalen. Hvis du vil beregne totalen, skal du tilføje en beregnet måling som f.eks.:
"TotalSales:=sumx('Salgstabel','Salgstabel'[Unit Price]*'Salgstabel'[Antal])"
Beregnede kolonner er ligesom almindelige kolonner på den måde, at de begge optager plads i modellen. Beregnede mål derimod beregnes i undervejs og fylder ikke.
Konklusion
I denne artikel talte vi om flere metoder, der kan hjælpe dig med at bygge en mere hukommelseseffektiv model. Du kan reducere kravene til filstørrelsen og hukommelsen for en datamodel 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 dækkede:
- At fjerne spalter 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 en beregnet måling 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 at nedbryde en enkelt kolonne i flere særskilte dele en god måde at reducere antallet af entydige værdier i en kolonne på. Hver af delene har et lille antal entydige værdier, og den kombinerede total bliver mindre end den oprindelige samlede kolonne.
- I mange tilfælde har du også brug for de forskellige dele til at bruge som udsnit i dine rapporter. Når det er relevant, kan du oprette hierarkier ud fra dele som f.eks. Timer, Minutter og Sekunder.
- Mange gange indeholder kolonner også flere oplysninger, end du har brug for. Antag f.eks., at en kolonne gemmer decimaler, men du har anvendt formatering for at skjule alle decimalerne. 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, bør du overveje også at køre Workbook Size Optimizer. 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
Power Pivot: Effektiv dataanalyse og datamodellering i Excel