Oprette en hukommelses effektiv data model ved hjælp af Excel og Power Pivot-tilføjelsesprogram

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

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

Selvom du nemt kan opbygge enorme datamodeller i Excel, er der flere årsager til, at det ikke er muligt. Først er store modeller, der indeholder flere mangfoldigheder af tabeller og kolonner, overkill for de fleste analyser og gør til en besværlig Feltliste. For det andet bruger store modeller op til værdifuld hukommelse, og der er en negativ indvirkning på de andre programmer og rapporter, der deler de samme systemressourcer. Til sidst vil både SharePoint Online og Excel Web App begrænse størrelsen af en Excel-fil til 10 MB i Office 365. For projektmappedata modeller, der indeholder millioner af rækker, kan du hurtigt køre i 10 MB. Se specifikation og begrænsninger for data model.

I denne artikel lærer du, hvordan du opretter en tæt konstrueret model, der er nemmere at arbejde med og bruger mindre hukommelse. Hvis du tager tid til at få mere at vide om bedste fremgangsmåder i et effektivt model design, vil det betale sig at være på vej til en hvilken som helst model, du opretter og bruger, 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. Downloade projektmappens størrelses optimering.

I denne artikel

Komprimeringsforhold og analyse programmet i hukommelsen

Intet slår en ikke-eksisterende kolonne til brug med lavt hukommelsesforbrug

To eksempler på kolonner, der altid skal udelades

Sådan udelades unødvendige kolonner

Hvad ved filtrering af kun de nødvendige rækker?

Hvad nu, hvis du har brug for kolonnen. kan vi stadig reducere dens plads omkostninger?

Ændre DateTime-kolonner

Ændre SQL-forespørgslen

Brug af DAX-beregnede mål i stedet for kolonner

Hvilke to kolonner skal du beholde?

Konklusion

Relaterede links

Komprimeringsforhold og analyse programmet i hukommelsen

Datamodeller i Excel bruger analyse programmet i hukommelsen til at gemme data i hukommelsen. Motoren implementerer effektive komprimerings teknikker for at reducere kravene til lagerplads, hvilket formindsker 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å dens oprindelsessted. Hvis du for eksempel importerer 7 MB data fra en SQL Server-database, kan datamodellen i Excel nemt være 1 MB eller mindre. Komprimerings graden, der faktisk er opnået, afhænger primært af antallet af entydige værdier i hver kolonne. Jo flere entydige værdier, jo mere hukommelse er nødvendigt for at gemme dem.

Hvorfor taler vi om komprimering og entydige værdier? Da opbygning af en effektiv model, der minimerer hukommelsesforbruget, er alt om komprimerings maksimering, og den nemmeste måde at gøre det på er at slippe af de kolonner, du ikke har brug for, især hvis disse kolonner indeholder et stort antal entydige værdier.

Bemærk!:  Forskellene i lager kravene for individuelle kolonner kan være enorme. I nogle tilfælde er det bedre at have flere kolonner med et lille 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 til brug med lavt hukommelsesforbrug

Den mest hukommelses effektive kolonne er den, du aldrig har importeret første gang. 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 du ikke er sikker, kan du lade det være. 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 relaterer til data, der stammer fra et datalager. I et data warehouse er det fælles for at finde artefakter for ETL-processer, der indlæser og opdaterer data på lageret. Kolonner som "Opret dato", "opdateringsdato" og "ETL kører" 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.

Det andet eksempel medfører, at du udelader den primære nøglekolonne, når du importerer en faktatabel.

Mange tabeller, herunder faktatabeller, har primære nøgler. For de fleste tabeller, såsom dem, der indeholder kunde-, medarbejder-eller salgsdata, skal du have 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 at identificere hver række entydigt. Selvom det er nødvendigt for normaliserings formål, er det mindre nyttigt i en datamodel, hvor du kun vil have de kolonner, der bruges til at analysere eller oprette tabelrelationer. Derfor skal du ikke medtage den primære nøgle, når du importerer fra en faktatabel. Primære nøgler i en faktatabel bruger enorme mængder plads i modellen, men giver endnu ingen fordel, da de ikke kan bruges til at oprette relationer.

Bemærk!:  I datalager steder og multidimensionale databaser er store tabeller, der består af de mest numeriske data, ofte kaldes "faktatabeller". Faktatabeller inkluderer typisk virksomheds performance eller transaktionsdata, såsom salgs-og omkostningsdata punkter, der er samlet og justeret i organisatoriske enheder, produkter, markedssegmenter, geografiske områder osv. Alle kolonnerne i en faktatabel, der indeholder virksomhedsdata, eller som kan bruges til krydsreference data, 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, der består af entydige værdier, der kun findes i faktatabellen, og som ikke er i den anden ende. Da faktatabeller er så enorme, afledes nogle af de største gevinster i modellen effektivitet 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 skal medtages i modellen, skal du bruge guiden tabel import i Power Pivot-tilføjelsesprogram til at importere dataene i stedet for dialogboksen "Importér data" i Excel.

Når du starter guiden tabel import, skal du vælge, hvilke tabeller der skal importeres.

Guiden Tabelimport i tilføjelsesprogrammet PowerPivot

For hver tabel kan du klikke på knappen Vis & filter og vælge de dele af tabellen, du virkelig har brug for. Vi anbefaler, at du først fjerner markeringen af alle kolonner og derefter fortsætter med at markere de kolonner, du vil have, efter at du har overset, om de er nødvendige for analysen.

Ruden Vis i guiden Tabelimport

Hvad ved filtrering af kun de nødvendige rækker?

Mange tabeller i firmadatabaser og data warehouses indeholder historiske data, der er akkumuleret over lange tidsperioder. Du kan også finde ud af, at de tabeller, du er interesseret i, indeholder oplysninger om de områder i virksomheden, som ikke er nødvendige for din specifikke analyse.

Ved hjælp af guiden tabel import kan du filtrere historiske eller ikke-relaterede data og dermed gemme meget plads i modellen. I følgende billede bruges et datofilter til at hente de rækker, der indeholder data for det aktuelle år, undtagen historiske data, der ikke er nødvendige.

Filterrude i guiden til import af tabel

Hvad nu, hvis du har brug for kolonnen. kan vi stadig reducere dens plads omkostninger?

Der er en række ekstra teknikker, du kan anvende for at gøre en kolonne til en bedre ide til komprimering. Husk, at den eneste egenskab for den kolonne, 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.

Ændre DateTime-kolonner

I mange tilfælde tager DateTime-kolonner meget plads. Heldigvis er der flere måder, hvorpå du kan reducere lager kravene til denne datatype. Teknikerne varierer afhængigt af, hvordan du bruger kolonnen, og dit komfort-niveau i oprettelsen af SQL-forespørgsler.

DateTime-kolonner omfatter en dato og 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 klokkeslætsdelen?

  • Skal jeg bruge tids delen på time niveau? referat? Minut? millisekunder?

  • Har jeg flere DateTime-kolonner, fordi jeg vil beregne forskellen mellem dem eller blot at samle dataene efter år, måned, kvartal osv.

Hvordan du besvarer hvert af disse spørgsmål, bestemmer dine muligheder for at håndtere DateTime-kolonnen.

Alle disse løsninger kræver ændring af en SQL-forespørgsel. Hvis du vil gøre det nemmere at redigere forespørgslen, skal du filtrere mindst én kolonne i hver tabel. Hvis du filtrerer en kolonne, ændrer du forespørgsels opbygning fra et forkortet format (SELECT *) til en SELECT-sætning, der omfatter fuldt kvalificerede kolonnenavne, der er langt nemmere at ændre.

Lad os se nærmere på de forespørgsler, der er oprettet til dig. I dialogboksen Egenskaber for tabel kan du skifte til forespørgselseditor og se den aktuelle SQL-forespørgsel for hver tabel.

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

Fra egenskaber for tabel skal du vælge forespørgsels Editor.

Åbn Forespørgselseditor fra dialogboksen Egenskaber for tabel

I Forespørgselseditoren vises den SQL-forespørgsel, der bruges til at udfylde tabellen. Hvis du har filtreret en kolonne under importen, indeholder din forespørgsel fuldt kvalificerede 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 en kolonne eller anvende et filter, får du vist forespørgslen som "Vælg * fra", hvilket vil være sværere at ændre:

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

Ændre SQL-forespørgslen

Nu, hvor du ved, hvordan du kan finde forespørgslen, kan du ændre den for yderligere at reducere størrelsen på modellen.

  1. For kolonner, der indeholder valuta-eller decimal data, skal du bruge denne syntaks til at fjerne decimaler:

    "Vælg Afrund ([Decimal_column_name]; 0)... .”

    Hvis du har brug for procenten, men ikke brøkdele af cents, skal du erstatte 0 x 2. Hvis du bruger negative tal, kan du afrunde til enheder, hundredvis, hundredvis osv.

  2. Hvis du har en DateTime-kolonne med navnet dbo. Bigtable. [Dato klokkeslæt] og du behøver ikke klokkeslætsdelen, brug syntaksen til at slippe af klokkeslættet:

    "Vælg CAST (dbo. Bigtable. [Dato klokkeslæt] as dato) AS [dato klokkeslæt]) "

  3. Hvis du har en DateTime-kolonne med navnet dbo. Bigtable. [Dato klokkeslæt] og du skal have både dato-og klokkeslæts dele, bruge flere kolonner i SQL-forespørgslen i stedet for den enkelt DateTime-kolonne:

    "Vælg CAST (dbo. Bigtable. [Dato klokkeslæt] as dato) AS [dato klokkeslæt]

    DatePart (HH, dbo. Bigtable. [Dato klokkeslæt]) som [dato/klokkeslæt-timer]

    DatePart (MI; dbo. Bigtable. [Dato klokkeslæt]) som [dato klokkeslæt minutter]

    DatePart (SS, dbo. Bigtable. [Dato klokkeslæt]) som [dato klokkeslæt sekunder]

    DatePart (MS, dbo. Bigtable. [Dato klokkeslæt]) som [dato klokkeslæt millisekunder] "

    Brug lige så mange kolonner, som du har brug for til at gemme hver del i separate kolonner.

  4. Hvis du har brug for timer og minutter, og du foretrækker dem sammen som én tids kolonne, kan du bruge syntaksen:

    Funktionerne (DatePart (HH; dbo. Bigtable. [Dato klokkeslæt]), DatePart (mm, dbo. Bigtable. [Dato klokkeslæt])) som [dato klokkeslæt HourMinute]

  5. Hvis du har to DateTime-kolonner, som [Start klokkeslæt] og [sluttidspunkt], og hvad du virkelig har brug for, er tidsforskellen mellem dem i sekunder som en kolonne med navnet [duration], Fjern begge kolonner fra listen, og Tilføj:

    "DateDiff (SS, [Start dato], [slutdato]) as [varighed]"

    Hvis du brugernøgle ordet 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-udtryks sproget før, kan du allerede vide, at beregnede kolonner bruges til at aflede nye kolonner, der er baseret på en anden kolonne i modellen, mens beregnede mål defineres én gang i modellen, men evalueres, når de bruges i en Pivottabel eller anden rapport.

Én hukommelses besparende 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 bevare to og beregne det tredje, der bruger DAX.

Hvilke to kolonner skal du beholde?

I eksemplet ovenfor skal du holde antal og Enhedspris. Disse to har færre værdier end totalen. Hvis du vil beregne total, skal du tilføje en beregnet måling som:

"Samletsalg: = SUMX (' salgs tabel ', ' salgs tabel ' [enhedspris] * ' salgs tabel ' [antal])"

Beregnede kolonner ligner almindelige kolonner i, hvor begge optager plads i modellen. I modsætning hertil beregnes beregnede mål hurtigt, og der tages ikke plads.

Konklusion

I denne artikel kan vi talt om flere fremgangsmåder, der kan hjælpe dig med at opbygge en mere hukommelses besparende model. Den måde at reducere filstørrelsen og hukommelses kravene for en datamodel på er at reducere det overordnede antal kolonner og rækker samt antallet af entydige værdier, der vises i hver kolonne. Her er nogle teknikker, som vi har dækket:

  • Det er selvfølgelig bedst at fjerne kolonner for at spare plads. 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 har muligvis ikke brug for alle rækkerne i en tabel. Du kan filtrere rækker i guiden tabel import.

  • Generelt er det en god måde at reducere antallet af entydige værdier i en kolonne ved at opdele en enkelt kolonne i flere forskellige dele. Hver enkelt af delene har et lille antal entydige værdier, og den kombinerede total vil være mindre end den oprindelige samlede kolonne.

  • I mange tilfælde skal du også have de særskilte dele, der skal bruges som udsnit i dine rapporter. Når det er relevant, kan du oprette hierarkier fra dele som timer, minutter og sekunder.

  • Mange gange indeholder kolonner flere oplysninger, end du har brug for dem. Antag f. eks., at en kolonne indeholder 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 det, du kan gøre for at reducere størrelsen på din projektmappe, skal du overveje også at køre projektmappens størrelses optimering. Det analyserer din Excel-projektmappe og komprimerer den yderligere, hvis det er muligt. Downloade projektmappens størrelses optimering.

Relaterede links

Specifikation og grænser for datamodel

Overførsel af størrelse på projektmappe størrelse

Power Pivot: Effektiv dataanalyse og datamodellering i Excel

Udvid dine Office-færdigheder
Gå på opdagelse i kurser
Få nye funktioner først
Bliv Office Insider

Var disse oplysninger nyttige?

Tak for din feedback!

Tak for din feedback! Det lyder, som om det vil kunne hjælpe, hvis du bliver sat i forbindelse med en af vores Office-supportteknikere.

×