Oprette en hukommelseseffektiv datamodel ved hjælp af Excel og Power Pivot-tilføjelsesprogrammet

Vigtigt: Denne artikel er maskinoversat. Se ansvarsfraskrivelsen. Du kan finde den engelske version af denne artikel her til din orientering.

I Excel 2013 eller nyere, kan du oprette datamodeller, der indeholder millioner af rækker og derefter udføre effektiv dataanalyse mod disse modeller. Datamodeller kan oprettes med eller uden tilføjelsesprogrammet Power Pivot til at understøtte et vilkårligt antal pivottabeller, diagrammer og visualiseringer af Power View i den samme projektmappe.

Bemærk: I denne artikel beskrives datamodeller i Excel 2013. Dog gælder de samme datamodellering og Power Pivot-funktioner, der blev introduceret i Excel 2013 også for Excel 2016. Der er effektivt lille forskel mellem disse versioner af Excel.

Selvom du nemt kan opbygge enorme datamodeller i Excel, er der flere gode grunde til ikke at gøre det. For det første er store datamodeller med en masse forskellige tabeller og kolonner overdrevet til de fleste analyser, og de giver en uoverskuelig feltliste. For det andet bruger store modeller meget hukommelse, hvilket har en negativ virkning på andre programmer og rapporter, som deler de samme systemressourcer. Sidst, men ikke mindst begrænser både SharePoint Online og Excel Web App i Office 365 størrelsen af en Excel-fil til 10 MB. Hvis du bruger projektmapper, der indeholder datamodeller med flere millioner rækker, når du hurtigt grænsen på 10 MB. Se Specifikationer og begrænsninger for datamodeller.

I denne artikel kan du lære, hvordan du opbygger en stramt konstrueret model, som er nemmere at arbejde med, og som bruger mindre hukommelse. Det kan godt betale sig at bruge noget tid på at lære de bedste fremgangsmåder til effektivt modeldesign, og det vil du kunne mærke efterhånden i en hvilken som helst model, du opretter og bruger, uanset om du får vist den i Excel 2013, Office 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. Hent Workbook Size Optimizer.

Denne artikel indeholder

Komprimeringsforhold og i hukommelsen

Intet slår en ikke-eksisterende kolonne lavt hukommelsesforbrug

To eksempler på kolonner, som altid bør udelades

Sådan udelades unødvendige kolonner

Hvad med bare at filtrere de nødvendige rækker?

Hvad nu, hvis vi brug for kolonnen. kan vi stadig reducere mængden af plads den optager?

Redigere dato/klokkeslæt-kolonner

Redigere SQL-forespørgslen

Ved hjælp af DAX-beregnede mål i stedet for kolonner

Hvilke 2 kolonner skal du beholde?

Konklusion

Relaterede links

Komprimeringsforhold og analyseprogrammet i hukommelsen

Datamodeller i Excel anvender analyseprogrammet i hukommelsen til at lagre data i hukommelsen. Programmet anvender effektive komprimeringsteknikker til at reducere behovet for lagerplads ved at formindske et resultatsæt, indtil det fylder en brøkdel af den oprindelige størrelse.

I gennemsnit kan du forvente, at en datamodel bliver 7 til 10 gange mindre end de samme data i den oprindelige form. Hvis du f.eks. importerer 7 MB data fra en SQL Server-database, er det meget sandsynligt, at datamodellen kun fylder 1 MB eller mindre i Excel. Den faktisk opnåede komprimeringsgrad 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 lagre dem.

Hvorfor taler vi om komprimering og entydige værdier? Fordi oprettelsen af en effektiv model, der minimerer hukommelsesforbruget, handler om komprimeringsmaksimering, og den nemmeste måde at opnå det på er at fjerne kolonner, som dybest set ikke er nødvendige, især hvis de pågældende kolonner indeholder mange entydige værdier.

Bemærk: Forskellene i behovet for lagerplads for enkelte kolonner kan være enorme. I nogle tilfælde er det bedre at have flere kolonner med få entydige værdier end at have én kolonne med mange entydige værdier. Afsnittet om optimering af dato/klokkeslæt behandler denne teknik mere indgående.

Der er ikke noget, der slår en ikke-eksisterende kolonne, når det gælder lavt hukommelsesforbrug

Den mest hukommelseseffektive kolonne er den, som aldrig blev importeret til at starte med. Hvis du vil opbygge en effektiv model, skal du se på hver dokument og spørge dig selv, om det bidrager til den analyse, du vil udføre. Hvis dokumentet ikke er relevant, eller hvis du er i tvivl, skal du udelade det. Du kan altid tilføje nye kolonner senere, hvis der er brug for det.

To eksempler på kolonner, som altid bør udelades

Det første eksempel relaterer til data, som stammer fra et data warehouse. I et data warehouse findes der ofte redskaber fra ETL-processer, som indlæser og opdaterer data i det pågældende warehouse. Kolonner som f.eks. "dato for oprettelse", "dato for opdatering" og "ETL kørt" oprettes, når der indlæses data. Der er ikke brug for nogen af disse kolonner i modellen, og de bør fravælges, når du importerer data.

Det andet eksempel går ud på at udelade den primære nøglekolonne, når faktatabellen importeres.

Mange tabeller, herunder faktatabeller, har primære nøgler. I de fleste tabeller, f.eks. tabeller, 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 at identificere hver række entydigt. Mens det er nødvendigt til normaliseringsformål, er det mindre anvendeligt i datamodeller, hvor du kun skal bruge de kolonner, der bruges til analysen eller til at oprette tabelrelationer. Af den årsag skal du ikke medtage den primære nøgle, når du importerer fra en faktatabel. Primære nøgler i en faktatabel optager enormt meget plads i modellen, men giver ingen fordele, da de ikke kan bruges til at oprette relationer.

Bemærk: I data warehouses og flerdimensionelle databaser kaldes store tabeller, som hovedsageligt består af numeriske data, ofte for "faktatabeller". Faktatabeller indeholder typisk oplysninger om virksomhedens resultat eller transaktionsdata, f.eks. datapunkter for omsætning og omkostninger, som lægges sammen og knyttes til organisationsenheder, produkter, markedssegmenter, geografiske områder osv. Alle kolonnerne i en faktatabel, som indeholder virksomhedsdata, eller som kan bruges til krydsreference med data, som er lagret i andre tabeller, bør medtages i modellen til brug i dataanalysen. Den kolonne, du bør fravælge, er faktatabellens kolonne for den primære nøgle, som består af entydige værdier, som kun findes i faktatabellen og ingen andre steder. Fordi faktatabeller er så enorme, opnås nogle af de største fremskridt for modeleffektiviteten ved at udelade rækker eller kolonner fra faktatabeller.

Sådan udelades unødvendige kolonner

Effektive modeller indeholder kun de kolonner, som rent faktisk er nødvendige i projektmappen. Hvis du vil styre, hvilke kolonner der medtages i modellen, skal du benytte 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, skal du vælge, hvilke tabeller der skal importeres.

Guiden Tabelimport i tilføjelsesprogrammet PowerPivot

For hver tabel kan du klikke på knappen Vis og filtrer og vælge de dele af tabellen, der rent faktisk er brug for. Vi anbefaler, at du starter med at fjerne markeringen af alle kolonner og derefter tilvælger de kolonner, du vil medtage, efter at du har overvejet, hvorvidt de er nødvendige i analysen.

Ruden Vis i guiden Tabelimport

Hvad med bare at filtrere de nødvendige rækker?

Mange tabeller i virksomhedsdatabaser og data warehouses indeholder historiske data, som har ophobet sig over lang tid. Desuden kan du komme ud for, at de tabeller, du er interesseret i, indeholder oplysninger om områder i virksomheden, som ikke behøves til den aktuelle analyse.

Hvis du bruger guiden Tabelimport, kan du frasortere historiske eller ikke-relaterede data og derved spare en masse tid og plads i modellen. På det følgende billede er der brugt et datafilter til kun at hente rækker, som indeholder data fra indeværende år, og historiske data, der ikke er brug for, er udeladt.

Ruden Filter i guiden Tabelimport

Hvis nu der er brug for kolonnen, kan vi så stadig reducere den plads, den optager?

Der er nogle få andre teknikker, du kan anvende for at gøre kolonnen bedre egnet til komprimering. Husk, at det eneste ved kolonnen, der påvirker komprimeringen er antallet af entydige værdier. I dette afsnit kan du lære, hvordan nogle kolonner kan redigeres for at reducere antallet af entydige værdier.

Redigere Dato/klokkeslæt-kolonner

I mange tilfælde optager Dato/klokkeslæt-kolonner meget plads. Heldigvis er der en række metoder til at reducere denne datatypes behov for lagerplads. Teknikkerne afhænger af, hvordan du bruger kolonnen, samt hvor fortrolig du er med at oprette SQL-forespørgsler.

Dato/klokkeslæt-kolonner indeholder 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 Dato/klokkeslæt-kolonne:

  • Har jeg brug for klokkeslætsdelen?

  • Har jeg brug for klokkeslætsdelen på timeniveau? Minutter? Sekunder? Millisekunder?

  • Har jeg flere Dato/klokkeslæt-kolonner, fordi jeg vil beregne forskellen mellem dem eller blot inddele dataene efter år, måned, kvartal osv.?

Dine svar på disse spørgsmål afgør dine muligheder med Dato/klokkeslæt-kolonnen.

Alle disse løsninger kræver ændringer i en SQL-forespørgsel. Du kan gøre redigeringen af forespørgslen lettere ved at frafiltrere mindst én kolonne i hver tabel. Hvis du frasorterer en kolonne, ændrer du opbygningen af forespørgslen fra forkortet format (SELECT *) til en SELECT-sætning, der indeholder fuldstændige kolonnenavne, som er meget nemmere at redigere.

Lad os se på de forespørgsler, der oprettes til dig. I dialogboksen Egenskaber for tabel, kan du skifte til Forespørgselseditor og få vist den aktuelle SQL-forespørgsel for hver tabel.

Båndet i PowerPivot-vinduet, der viser kommandoen Egenskaber for tabel

Vælg Forespørgselseditor i Egenskaber for tabel.

Åbn Forespørgselseditor fra dialogboksen Egenskaber for tabel

Forespørgselseditor viser den SQL-forespørgsel, der anvendes til at udfylde tabellen. Hvis du har frafiltreret en kolonne under importen, indeholder forespørgslen fuldstændige kolonnenavne:

SQL-forespørgsel, som bruges til at hente dataene

Hvis du derimod har importeret en tabel i sin helhed uden at fjerne markeringen af nogen kolonne eller uden at anvende noget filter, får du vist forespørgslen som "Select * from ", som vil være mere besværlig af redigere:

SQL-forespørgsel, der bruger den kortere syntaks, som er standard

Redigere SQL-forespørgslen

Nu hvor du ved, hvordan du finder forespørgslen, kan du redigere for at reducere modellens størrelse yderligere.

  1. Brug den følgende syntaks til kolonner, som indeholder valuta- eller decimaldata, og hvor du ikke skal bruge decimalerne:

    "SELECT ROUND([Decimal_column_name],0)… ."

    Hvis du skal bruge ørene, men ikke decimalerne af ørene, skal du erstatte 0 med 2. Hvis du bruger negative tal, kan du afrunde til enheder, tiere, hundreder osv.

  2. Hvis du har en Dato/klokkeslæt-kolonne med navnet dbo.Bigtable.[Dato Klokkeslæt], og du ikke skal bruge delen Klokkeslæt, skal du bruge syntaksen til at udelade klokkeslættet:

    "SELECT CAST (dbo.Bigtable.[Dato klokkeslæt] as date) AS [Dato klokkeslæt]) "

  3. Hvis du har en Dato/klokkeslæt-kolonne med navnet dbo.Bigtable.[Dato Klokkeslæt], og du skal bruge både Dato og Klokkeslæt, skal du bruge flere kolonner i SQL-forespørgslen i stedet for den enkelte Dato/klokkeslæt-kolonnen:

    "SELECT CAST (dbo.Bigtable.[Dato Klokkeslæt] as date ) AS [Dato Klokkeslæt],

    datepart(hh, dbo.Bigtable.[Dato Klokkeslæt]) as [Dato Klokkeslæt Timer],

    datepart(mi, dbo.Bigtable.[Dato Klokkeslæt]) as [Dato Klokkeslæt Minutter],

    datepart(ss, dbo.Bigtable.[Dato Klokkeslæt]) as [Dato Klokkeslæt >Sekunder],

    datepart(ms, dbo.Bigtable.[Dato Klokkeslæt]) as [Dato Klokkeslæt Millisekunder]"

    Brug lige så mange kolonner, du skal bruge for at lagre hver del i separate kolonner.

  4. Hvis du skal bruge timer og minutter, og du foretrækker at have dem sammen i én klokkeslætskolonne, kan du bruge følgende syntaks:

    Timefromparts(datepart(hh, dbo.Bigtable.[Dato Klokkeslæt]), datepart(mm, dbo.Bigtable.[Dato Klokkeslæt])) as [Dato Klokkeslæt TimeMinut]

  5. Hvis du har to dato/klokkeslæt-kolonner, f.eks. [Starttidspunkt] og [Sluttidspunkt], og det, du skal bruge, er tidsforskellen mellem dem i sekunder i en kolonne med navnet [Varighed], skal du fjerne begge kolonner fra listen og tilføje:

    "datediff(ss,[Startdato],[Slutdato] as [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-udtrykssprog før, ved du muligvis allerede, at beregnede kolonner bruges til at aflede nye kolonner ud fra en anden kolonne i modellen, mens beregnede mål defineres én gang i modellen, men de evalueres kun, når de bruges i en pivottabel eller en anden rapport.

Én teknik til at begrænse hukommelsesforbruget er at erstatte almindelige eller beregnede kolonner med beregnede mål. Det klassiske eksempel er Pris pr. enhed, Antal og Total. Hvis du har alle tre, kan du spare plads ved blot at beholde to og beregne den tredje vha. DAX.

Hvilke to kolonner skal du beholde?

I eksemplet ovenfor skal du beholde Antal og Pris pr. enhed. Disse to indeholder færre værdier end Total. Hvis du vil beregne Total, skal du tilføje et beregnet mål som dette:

"TotalSales:=sumx(‘Sales Table’,’Sales Table’[Pris pr. enhed]*’Sales Table’[Antal])"

Beregnede kolonner er lige som almindelige kolonner på den måde, at de begge optager plads i modellen. Derimod beregnes beregnede mål løbende og optager ikke plads.

Konklusion

I denne artikel har vi gennemgået flere forskellige tilgange, som kan hjælpe dig med at opbygge en mere hukommelseseffektiv model. Metoden til at reducere filstørrelsen og kravene til lagerplads for en datamodel er at reducere det overordnede antal kolonner og rækker samt antallet af entydige værdier i hver enkelt kolonne. Her er nogle af de teknikker, vi har gennemgået:

  • Den bedste måde at spare plads på er selvfølgelig at fjerne kolonner. Beslut dig for, hvilke kolonner du reelt har brug for.

  • Nogle gange kan du fjerne en kolonne og erstatte den med et beregnet mål i tabellen.

  • Du behøver måske ikke alle rækkerne i en tabel. Du kan filtrere rækker fra i guiden Tabelimport.

  • Overordnet set er det en god ide at inddele en enkelt kolonne i flere adskilte dele for at reducere antallet af entydige værdier i en kolonne. Hver af delene vil have et lille antal entydige værdier, og det samlede antal vil være mindre end i den oprindelige samlede kolonne.

  • I mange tilfælde skal du også bruge de adskilte dele som udsnitsværktøjer i dine rapporter. Når det er relevant, kan du oprette hierarkier ud fra dele som Timer, Minutter og Sekunder.

  • I mange tilfælde indeholde kolonner flere oplysninger, end du skal bruge. Det kan f.eks. være, at en kolonne lagrer decimaler, men du anvender formatering, der skjuler alle decimalerne. Afrunding kan være meget effektivt til at reducere størrelsen på en numerisk kolonne.

Nu hvor du har lavet, hvad du kan reducere størrelsen af din projektmappe, kan du overveje at også kører projektmappe størrelse optimering. Analyserer din Excel-projektmappe, og hvis det er muligt komprimerer yderligere. Hente projektmappen størrelse optimering.

Relaterede links

Specifikation og grænser for datamodel

Workbook Size Optimizer-download

Power Pivot: Effektiv dataanalyse og datamodellering i Excel

Bemærk: Ansvarsfraskrivelse for maskinoversættelse: Denne artikel er blevet oversat af et computersystem uden menneskelig indgriben. Microsoft tilbyder disse maskinoversættelse for at hjælpe ikke-engelsktalende brugere til at kunne nyde indhold om Microsofts produkter, tjenester og teknologier. Da artiklen er maskinoversat, kan den indeholde forkerte ord eller syntaks- eller grammatikfejl.

Var disse oplysninger nyttige?

Fantastisk! Har du mere feedback?

Hvordan kan vi forbedre det?

Tak for din feedback!

×