Gør din dataanalyse mere effektiv ved at oprette relationer mellem forskellige tabeller. En relation er en forbindelse mellem to tabeller, der indeholder data: En kolonne i hver tabel er grundlaget for relationen. Som eksempel på, hvorfor relationer er nyttige, kan du forestille dig, at du sporer data til din virksomheds kundeordrer. Du kan spore alle dataene i en enkelt tabel med en struktur som denne:
| Kunde-id | Navn | NedsatTakst | Ordre-id | Bestillingsdato | Produkt | Antal | |
|---|---|---|---|---|---|---|---|
| 1 | Ashton | chris.ashton@contoso.com | ,05 | 256 | 2010-07-01 | Compact Digital | 11 |
| 1 | Ashton | chris.ashton@contoso.com | ,05 | 255 | 2010-03-01 | SLR Camera | 15 |
| 2 | Jaworski | michal.jaworski@contoso.com | ,10 | 254 | 2010-03-01 | Budget Movie-Maker | 27 |
Denne tilgang kan fungere, men kræver, at der lagres en masse redundante data såsom kunde-mailadressen for hver ordre. Det er billigt at lagre data, men hvis mailadressen ændres, er du nødt til at sikre dig, at du opdaterer hver eneste række for den pågældende kunde. En løsning til dette problem er at opdele dataene i flere tabeller og definere relationer mellem disse tabeller. Dette er den fremgangsmåde, der bruges i relationsdatabaser som SQL Server. F.eks. kan en database, som du importerer, repræsentere ordredata ved hjælp af tre relaterede tabeller:
Kunder
| [Kunde-id] | Navn | |
|---|---|---|
| 1 | Ashton | chris.ashton@contoso.com |
| 2 | Jaworski | michal.jaworski@contoso.com |
KundeRabatter
| [Kunde-id] | NedsatTakst |
|---|---|
| 1 | ,05 |
| 2 | ,10 |
Ordrer
| [Kunde-id] | Ordre-id | Bestillingsdato | Produkt | Antal |
|---|---|---|---|---|
| 1 | 256 | 2010-07-01 | Compact Digital | 11 |
| 1 | 255 | 2010-03-01 | SLR Camera | 15 |
| 2 | 254 | 2010-03-01 | Budget Movie-Maker | 27 |
Relationer findes i en datamodel – en, som du opretter eksplicit, eller en, som Excel automatisk opretter på dine vegne, når du importerer flere tabeller samtidig. Du kan også bruge Power Pivot-tilføjelsesprogrammet til at oprette eller administrere modellen. Du kan få mere at vide under Oprette en datamodel i Excel.
Hvis du bruger tilføjelsesprogrammet Power Pivot til at importere tabeller fra den samme database, kan Power Pivot registrere relationer mellem tabellerne baseret på de kolonner, der er i [parenteser], og kan gengive disse relationer i en datamodel, som det opbygger i baggrunden. Du kan finde flere oplysninger under Automatisk registrering og afledning af relationer i denne artikel. Hvis du importere tabeller fra flere kilder, kan du manuelt oprette relationer som beskrevet i Oprette en relation mellem to tabeller.
Kolonner og nøgler
Relationer er baseret på kolonner i hver enkelt tabel, der indeholder de samme data. Du kan f.eks. relatere en tabel med kunder til tabellen Ordrer , hvis hver tabel indeholder en kolonne, der gemmer et kunde-id. I eksemplet er kolonnenavnene de samme, men det er ikke et krav. Det ene kunne være Kunde-id og det andet Kundenummer, når blot alle rækkerne i tabellen Ordrer indeholder et id, der også gemmes i tabellen Kunder.
I en relationsdatabase er der flere typer nøgler. En nøgle er typisk en kolonne med specielle egenskaber. Hvis du forstår formålet med hver nøgle, kan det hjælpe dig med at administrere en datamodel med flere tabeller, der leverer data til en pivottabel, et pivotdiagram eller en Power View-rapport.
Selvom der findes mange typer nøgler, er disse de vigtigste i forhold til vores formål her:
- Primær nøgle: identificerer en række i en tabel, f.eks kunde-id i tabellen kunder .
- Alternativ nøgle (eller kandidatnøgle): en kolonne ud over den primære, der er entydig. Det kan f.eks. være, at en medarbejdertabel indeholder et medarbejder-id og et sygesikringsnummer, hvor begge er entydige.
- Fremmed nøgle: en kolonne, der refererer til en unik kolonne i en anden tabel, f.eks. Kunde-id i tabellen Ordrer , der refererer til Kunde-id i tabellen Kunder.
I en datamodel kaldes den primære nøgle eller den alternative nøgle den relaterede opslagskolonne. Hvis en tabel har både en primær og en alternativ nøgle, kan du bruge begge som basis for en tabelrelation. Der refereres til den fremmede nøgle som kildekolonnen eller blot kolonnen. I eksemplet er der defineret en relation mellem CustomerID i tabellen ( kolonnen) og CustomerID i tabellen Kunder (opslagskolonnen). Hvis du importerer data fra en relationsdatabase, vælger Excel som standard den fremmede nøgle fra én tabel og den tilsvarende primære nøgle fra en anden tabel. Men du kan bruge en hvilken som helst kolonne, der indeholder entydige værdier, som opslagskolonne.
Typer af relationer
Relationen mellem en kunde og en ordre er en en-til-mange-relation. Der kan til hver kunde være flere ordrer, men der kan ikke være flere kunder til en ordre. En anden vigtig tabelrelation er en til en. I eksemplet her har tabellen Kunderabatter , der definerer en enkelt rabatsats for hver kunde, en en-til-en-relation til tabellen Kunden.
Denne tabel viser relationerne mellem de tre tabeller (Kunder, Kunderabatter og Ordrer):
| Relation | Type | Opslagskolonne | Kolonne |
|---|---|---|---|
| Kunder-KundeRabatter | En-til-en | Kunder.Kunde-id | KundeRabatter.Kunde-id |
| Kunder-Ordrer | En-til-mange | Kunder.Kunde-id | Ordrer.KundeId |
Bemærk
Mange-til-mange-relationer understøttes ikke i en datamodel. Et eksempel på en mange til mange-relation er en direkte relation mellem Produkter og Kunder, hvor kunden kan købe mange produkter, og det samme produkt kan købes af mange kunder.
Relationer og ydelse
Når der er oprettet en relation, skal Excel typisk genberegne alle formler, der bruger kolonner fra tabeller i den nye relation. Afhængigt af mængden af data og kompleksiteten af relationerne kan behandlingen tage noget tid. Du kan finde flere oplysninger under Genberegne formler.
Flere relationer mellem tabeller
En datamodel kan have flere relationer mellem to tabeller. Hvis du vil oprette nøjagtige beregninger, skal Excel bruge én enkelt sti fra én tabel til den anden. Derfor kan der kun være én aktiv relation mellem hvert tabelpar ad gangen. Selvom de andre er inaktive, kan du angive en inaktiv relation i formler og forespørgsler.
I diagramvisning er den aktive relation en ubrudt linje og de inaktive er stiplede linjer. I AdventureWorksDW2012 indeholder tabellen DimDate en kolonne, DateKey, der er relateret til tre forskellige kolonner i tabellen FactInternetSales: OrderDate, DueDate og ShipDate. Hvis den aktive relation er mellem DateKey og OrderDate, er det standardrelation i formler, medmindre du angiver andet.
Krav til en tabelrelation
En relation kan oprettes, når følgende krav er opfyldt:
| Kriterier | Beskrivelse |
|---|---|
| Entydigt id for hver tabel | I hver tabel skal der være en kolonne, der entydigt identificerer hver række i den pågældende tabel. Denne kolonne kaldes ofte den primære nøgle. |
| Entydige opslagskolonner | Dataværdierne i opslagskolonnen skal være entydige. Kolonnen må med andre ord ikke indeholde dubletter. I en datamodel svarer null-strenge og tomme strenge til en tom værdi, som er en entydig dataværdi. Det betyder, at opslagskolonnen ikke kan indeholde flere null-værdier. |
| Kompatible datatyper | Datatyperne i kildekolonnen og opslagskolonnen skal være kompatible. Du kan finde flere oplysninger om datatyper under Datatyper, der understøttes i datamodeller. |
Ikke-understøttede databasefunktioner i Excel-datamodellen
I en datamodel kan du ikke oprette en tabelrelation, hvis nøglen er en sammensat nøgle. Du er også begrænset til at kunne oprette en-til-en-relationer og en-til-mange-relationer. Andre relationstyper understøttes ikke.
Sammensatte nøgler og opslagskolonner
En sammensat nøgle består af mere end én kolonne. Datamodeller kan ikke bruge sammensatte nøgler: En tabel skal altid have præcis én kolonne, der entydigt identificerer hver række i tabellen. Hvis du importerer tabeller, som har en eksisterende relation, der er baseret på en sammensat nøgle, ignorerer guiden Tabelimport i Power Pivot den pågældende relation, fordi den ikke kan oprettes i modellen.
Hvis du vil oprette en relation mellem to tabeller, og der er flere kolonner, der definerer den primære nøgle og den fremmede nøgle, er du nødt til først at kombinere værdier for at oprette en enkelt nøglekolonne, før relationen oprettes. Du kan gøre dette, inden du importerer dataene eller ved at oprette en beregnet kolonne i datamodellen ved hjælp af Power Pivot-tilføjelsesprogrammet.
Mange-til-mange-relationer
En datamodel kan ikke have mange-til-mange-relationer. Du kan ikke bare tilføje sammenføjningstabeller i modellen. Du kan dog bruge DAX-funktioner til at modellere mange-til-mange-relationer.
Self-Joins og løkker
Self-joins er ikke tilladt i en datamodel. En selv-joinforbindelse er en rekursiv relation mellem en tabel og sig selv. Self-joins bruges ofte til at definere overordnet/underordnet-hierarkier. Du kan f.eks. forbinde en Medarbejder-tabel til sig selv ved at lave et hierarki, der viser ledelseskæden i en virksomhed.
I Excel er det ikke muligt at oprette relationer i en projektmappe. Med andre ord er følgende sæt relationer ikke tilladt.
Tabel 1, kolonne a til Tabel 2, kolonne f
Tabel 2, kolonne f til Tabel 3, kolonne n
Tabel 3, kolonne n til Tabel 1, kolonne a
Hvis du forsøger at oprette en relation, der ville resultere i, at der blev oprettet en løkke, modtager du en fejlmeddelelse.
Automatisk registrering og afledning af relationer i Power Pivot
En af fordelene ved at importere data ved hjælp af Power Pivot-tilføjelsesprogrammet er, at Power Pivot nogle gange kan registrere relationer og oprette relationer i den datamodel, programmet opretter i Excel.
Når du importerer flere tabeller, registrerer Power Pivot automatisk eksisterende relationer mellem tabellerne. Når du opretter en pivottabel, analyserer Power Pivot dataene i tabellerne. Det registrerer mulige relationer, der ikke er defineret, og foreslår relevante kolonner, der kan medtages i disse relationer.
Registreringsalgoritmen bruger statiske data om værdierne og metadataene i kolonner til at oprette følgeslutninger om sandsynligheden for relationer.
- Datatyper i alle relaterede koloner skal være kompatible. Ved automatisk registrering er det kun datatyperne heltal og tekst, der understøttes. Du kan finde flere oplysninger om datatyper under Datakilder, der understøttes i datamodeller.
- Hvis relationen skal kunne registreres, skal antallet af entydige nøgler i opslagskolonnen være større end værdierne i tabellen på mange-siden. Med andre ord må nøglekolonnen på mange-siden af relationen ikke indeholde værdier, der ikke findes i opslagstabellens nøglekolonne. Antag f.eks., at du har en tabel med produkter med deres id'er (opslagstabel), og en salgstabel med salget for hvert produkt (mange-siden af relationen). Hvis dine salgsposter indeholder id'et for et produkt, der ikke har et tilsvarende id i tabellen Produkter, kan relationen ikke oprettes automatisk, men du kan muligvis oprette den manuelt. For at få Excel til at registrere relationen skal du først opdatere tabellen Produktopslag med id'erne for de manglende produkter.
- Sørg for, at navnet på nøglekolonnen på mange-siden svarer til navnet på i nøglekolonnen i opslagstabellen. Navnene behøver ikke at være nøjagtigt ens. I en virksomhed har du ofte variationer af navnene på kolonner, der grundlæggende indeholder de samme data: Medarb-id, Medarbejderid, Medarbejder-id EMP_ID osv. Algoritmen registrerer de navne, der ligner hinanden, og tildeler de kolonner, der har lignende eller nøjagtigt ens navne, højere sandsynlighed. Hvis du vil øge sandsynligheden for, at der oprettes en relation, kan du derfor prøve at omdøbe de kolonner i dataene, som du importerer, til noget, der ligner kolonnerne i de eksisterende tabeller. Hvis Excel finder flere mulige relationer, oprettes der ikke en relation.
Disse oplysninger kan gøre det nemmere at forstå, hvorfor ikke alle relationer registreres, og hvordan ændringer i metadata, f.eks. feltnavn og datatyper, kan forbedre resultaterne ved automatisk registrering af relationer. Du kan finde flere oplysninger i Fejlfinding i forbindelse med relationer.
Automatisk registrering for navngivne sæt
Relationer registreres ikke automatisk mellem navngivne sæt og relaterede felter i en pivottabel. Du kan oprette disse relationer manuelt. Hvis du vil bruge automatisk relationsregistrering, skal du fjerne hvert navngivne sæt og tilføje de individuelle felter fra det navngivne sæt og direkte til pivottabellen.
Afledning af relationer
I visse tilfælde sker en automatisk sammenkædning af relationer mellem tabeller. Det betyder f.eks., at hvis du opretter en relation mellem de første to sæt tabeller nedenfor, udledes det, at der findes en relation mellem de to andre tabeller, og der oprettes automatisk en relation.
Produkter og kategori - oprettet manuelt
Kategori og Underkategori - oprettet manuelt
Produkter og Underkategori - relationer udledes
Hvis relationer skal kunne sammenkædes automatisk, skal relationerne gå i én retning, sådan som det er vist ovenfor. Hvis f.eks. de første relationer var mellem Salg og Produkter og Salg og Kunder, udledes der ikke en relation. Dette skyldes, at relationen mellem Produkter og Kunder er en mange-til-mange-relation.