Del via

Dimensionel modellering i Microsoft Fabric Warehouse: Indlæs tabeller

  • Artikel

Gælder for:✅ SQL Analytics-slutpunkt og warehouse i Microsoft Fabric

Bemærk

Denne artikel er en del af den dimensionelle modelleringsserie af artikler. I denne serie fokuseres der på vejledning og design af bedste praksis for dimensionel modellering i Microsoft Fabric Warehouse.

Denne artikel indeholder en vejledning og bedste praksis for indlæsning af dimensions- og faktatabeller i en dimensionel model. Den indeholder praktisk vejledning til Warehouse i Microsoft Fabric, som er en oplevelse, der understøtter mange T-SQL-funktioner, f.eks. oprettelse af tabeller og administration af data i tabeller. Så du har fuld kontrol over, hvordan du opretter tabeller med din dimensionelle model og indlæser dem med data.

Bemærk

I denne artikel henviser begrebet data warehouse til et virksomhedsdata warehouse, som leverer omfattende integration af vigtige data på tværs af organisationen. I modsætning hertil refererer det separate begreb warehouse til et Fabric Warehouse, som er en SaaS-relationsdatabase (software as a service), som du kan bruge til at implementere et data warehouse. For klarheds skyld nævnes sidstnævnte i denne artikel som Fabric Warehouse.

Tip

Hvis du er uerfaren med dimensionel modellering, kan du overveje denne serie artikler som dit første trin. Det er ikke hensigten at give en komplet diskussion om dimensionel udformning design. Du kan finde flere oplysninger i det publicerede indhold, der er vedtaget i vid udstrækning, f.eks . The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3. udgave, 2013) af Ralph Kimball og andre.

Indlæs en dimensionel model

Indlæsning af en dimensionel model omfatter periodisk kørsel af en ETL-proces (Extract, Transform og Load). En ETL-proces orkestrerer kørsel af andre processer, der generelt beskæftiger sig med midlertidige kildedata, synkronisering af dimensionsdata, indsættelse af rækker i faktatabeller og registrering af overvågningsdata og fejl.

For en Fabric Warehouse-løsning kan du bruge Data Factory til at udvikle og køre din ETL-proces. Processen kan fase, transformere og indlæse kildedata i tabellerne med din dimensionelle model.

Du kan specifikt:

  • Brug datapipelines til at oprette arbejdsprocesser for at orkestrere ETL-processen. Datapipelines kan udføre SQL-scripts, lagrede procedurer og meget mere.
  • Brug dataflow til at udvikle logik med lav kode til at indtage data fra hundredvis af datakilder. Dataflow understøtter kombination af data fra flere kilder, transformering af data og indlæsning til en destination, f.eks. en dimensionel modeltabel. Dataflow bygges ved hjælp af den velkendte Power Query-oplevelse , der er tilgængelig i dag på tværs af mange Microsoft-produkter, herunder Microsoft Excel og Power BI Desktop.

Bemærk

ETL-udvikling kan være kompleks, og udvikling kan være udfordrende. Det anslås, at 60-80 % af et data warehouse-udviklingsarbejde er dedikeret til ETL-processen.

Orkestrering

Den generelle arbejdsproces for en ETL-proces er at:

  1. Du kan også indlæse midlertidige tabeller.
  2. Behandl dimensionstabeller.
  3. Behandl faktatabeller.
  4. Du kan eventuelt udføre opgaver efter behandling, f.eks. udløse opdateringen af afhængigt Fabric-indhold (f.eks. en semantisk model).

Diagram, der viser de fire trin i ETL-processen som beskrevet i forrige afsnit.

Dimensionstabeller skal behandles først for at sikre, at de gemmer alle dimensionsmedlemmer, herunder dem, der er føjet til kildesystemer siden den sidste ETL-proces. Når der er afhængigheder mellem dimensioner, som det er tilfældet med udriggerdimensioner, skal dimensionstabeller behandles i rækkefølge efter afhængighed. En geografidimension, der bruges af en kundedimension og en kreditordimension, skal f.eks. behandles før de to andre dimensioner.

Faktatabeller kan behandles, når alle dimensionstabeller er behandlet.

Når alle dimensionsmodeltabeller behandles, kan du udløse opdateringen af afhængige semantiske modeller. Det er også en god idé at sende en meddelelse til relevante medarbejdere for at informere dem om resultatet af ETL-processen.

Fasedata

Midlertidige kildedata kan hjælpe med at understøtte krav til indlæsning og transformation af data. Det omfatter udtrækning af kildesystemdata og indlæsning af dem i midlertidige tabeller, som du opretter for at understøtte ETL-processen. Vi anbefaler, at du faser kildedata, fordi de kan:

  • Minimer indvirkningen på driftssystemer.
  • Bruges til at hjælpe med og optimere ETL-behandling.
  • Gør det muligt at genstarte ETL-processen uden at skulle genindlæse data fra kildesystemer.

Data i midlertidige tabeller bør aldrig gøres tilgængelige for virksomhedsbrugere. Det er kun relevant for ETL-processen.

Bemærk

Når dine data er gemt i et Fabric Lakehouse, er det muligvis ikke nødvendigt at placere dataene i data warehouse'et. Hvis den implementerer en medaljonsarkitektur, kan du hente dataene fra enten bronze-, sølv- eller guldlaget.

Vi anbefaler, at du opretter et skema på lageret, der muligvis kaldes staging. Midlertidige tabeller skal ligne kildetabellerne så tæt som muligt med hensyn til kolonnenavne og datatyper. Indholdet af hver tabel skal fjernes i starten af ETL-processen. TRUNCATE TABLE understøttes til dette formål.

Du kan også overveje alternativer til datavirtualisering som en del af din midlertidige strategi. Du kan bruge:

Transformér data

Strukturen af kildedataene ligner muligvis ikke destinationsstrukturerne i tabellerne med din dimensionelle model. Din ETL-proces skal derfor omforme kildedataene, så de passer til strukturen i tabellerne med den dimensionelle model.

Data warehouse'et skal også levere rensede og overensstemmende data, så kildedata skal muligvis transformeres for at sikre kvalitet og ensartethed.

Bemærk

Begrebet affald ind, affald ud helt sikkert gælder for data warehousing- derfor undgå at indlæse affald (lav kvalitet) data i din dimensionelle model tabeller.

Her er nogle transformationer, som DIT ETL-processen kan udføre.

  • Kombiner data: Data fra forskellige kilder kan integreres (flettes) baseret på matchende nøgler. Produktdata gemmes f.eks. på tværs af forskellige systemer (f.eks. produktion og marketing), men de bruger alle en fælles lagerenhed (SKU). Data kan også tilføjes, når de deler en fælles struktur. Salgsdata gemmes f.eks. i flere systemer. En forening af salget fra hvert system kan producere en delmængde af alle salgsdata.
  • Konvertér datatyper: Datatyper kan konverteres til dem, der er defineret i tabellerne med den dimensionelle model.
  • Beregninger: Der kan udføres beregninger for at oprette værdier for tabellerne med den dimensionelle model. For en medarbejderdimensionstabel kan du f.eks. sammenkæde for- og efternavne for at oprette det fulde navn. I forbindelse med faktatabellen for salg kan du som et andet eksempel beregne bruttoomsætningen for salg, som er produktet af enhedspris og antal.
  • Registrer og administrer historiske ændringer: Ændring kan registreres og gemmes korrekt i dimensionstabeller. Du kan få flere oplysninger under Administrer historiske ændringer senere i denne artikel.
  • Aggregerede data: Sammenlægning kan bruges til at reducere dimensionalitet i faktatabellen og/eller til at øge granulariteten af fakta. Tabellen med salgsoplysninger behøver f.eks. ikke at gemme salgsordrenumre. Derfor kan et aggregeret resultat, der grupperer efter alle dimensionsnøgler, bruges til at gemme faktatabellens data.

Indlæs data

Du kan indlæse tabeller i et Fabric Warehouse ved hjælp af følgende indstillinger for dataindtagelse.

  • KOPIÉR TIL (T-SQL): Denne indstilling er nyttig, når kildedataene omfatter Parquet- eller CSV-filer, der er gemt på en ekstern Azure-lagerkonto, f.eks . ADLS Gen2 eller Azure Blob Storage.
  • Datapipelines: Ud over at orkestrere ETL-processen kan datapipelines omfatte aktiviteter, der kører T-SQL-sætninger, udfører opslag eller kopierer data fra en datakilde til en destination.
  • Dataflow: Som et alternativ til datapipelines giver dataflows en kodefri oplevelse til at transformere og rense data.
  • Indtagelse på tværs af lagre: Når data gemmes i det samme arbejdsområde, gør indtagelse på tværs af lagre det muligt at forbinde forskellige lager- eller lakehouse-tabeller. Den understøtter T-SQL-kommandoer som INSERT…SELECT, SELECT INTOog CREATE TABLE AS SELECT (CTAS). Disse kommandoer er især nyttige, når du vil transformere og indlæse data fra midlertidige tabeller i det samme arbejdsområde. De er også set-baserede handlinger, hvilket sandsynligvis er den mest effektive og hurtigste måde at indlæse dimensionelle modeltabeller på.

Tip

Du kan få en komplet forklaring af disse indstillinger for dataindtagelse, herunder bedste fremgangsmåder, under Indfødning af data i lageret.

Logger

ETL-processer kræver normalt dedikeret overvågning og vedligeholdelse. Af disse årsager anbefaler vi, at du logfører resultaterne af ETL-processen til ikke-dimensionelle modeltabeller på dit lager. Du skal generere et entydigt id for hver ETL-proces og bruge det til at logføre oplysninger om hver handling.

Overvej at logge:

  • ETL-processen:
    • Et entydigt id for hver ETL-udførelse
    • Start- og sluttidspunkt
    • Status (lykkedes eller mislykkedes)
    • Eventuelle fejl, der er opstået
  • Hver tabel over midlertidige og dimensionelle modeller:
    • Start- og sluttidspunkt
    • Status (lykkedes eller mislykkedes)
    • Rækker indsat, opdateret og slettet
    • Endeligt antal rækker i tabellen
    • Eventuelle fejl, der er opstået
  • Andre handlinger:
    • Starttidspunkt og sluttidspunkt for opdateringshandlinger for semantiske modeller

Tip

Du kan oprette en semantisk model, der er dedikeret til overvågning og analyse af dine ETL-processer. Procesvarighed kan hjælpe dig med at identificere flaskehalse, der kan drage fordel af gennemgang og optimering. Rækkeantal kan give dig mulighed for at forstå størrelsen af den trinvise belastning, hver gang ETL'en kører, og hjælper også med at forudsige data warehouse'ets fremtidige størrelse (og hvornår Fabric-kapaciteten skal skaleres op, hvis det er relevant).

Behandl dimensionstabeller

Behandling af en dimensionstabel omfatter synkronisering af data warehouse-dataene med kildesystemerne. Kildedata transformeres først og forberedes til indlæsning i dimensionstabellen. Disse data matches derefter med de eksisterende data i dimensionstabellen ved at tilmelde sig forretningsnøglerne. Det er derefter muligt at afgøre, om kildedataene repræsenterer nye eller ændrede data. Når dimensionstabellen anvender dimensionstype 1, der ændrer sig langsomt, foretages der ændringer ved at opdatere de eksisterende rækker i dimensionstabellen. Når tabellen anvender ændringer af SCD type 2, udløber den eksisterende version, og der indsættes en ny version.

I følgende diagram vises den logik, der bruges til at behandle en dimensionstabel.

Diagram, der viser et flow, der beskriver, hvordan nye og ændrede kilderækker indlæses i en dimensionstabel, som beskrevet i følgende afsnit.

Overvej processen i Product dimensionstabellen.

  • Når der føjes nye produkter til kildesystemet, indsættes rækker i Product dimensionstabellen.
  • Når produkter ændres, opdateres eller indsættes eksisterende rækker i dimensionstabellen.
    • Når SCD-type 1 gælder, foretages der opdateringer til de eksisterende rækker.
    • Når SCD-type 2 gælder, foretages der opdateringer for at udløbe de aktuelle rækkeversioner, og nye rækker, der repræsenterer den aktuelle version, indsættes.
    • Når SCD-type 3 gælder, opstår der en proces, der ligner SCD-type 1, hvor de eksisterende rækker opdateres uden at indsætte nye rækker.

Erstatningsnøgler

Vi anbefaler, at hver dimensionstabel har en surrogatnøgle, som skal bruge den mindst mulige heltalsdatatype. I SQL Server-baserede miljøer, der typisk udføres ved at oprette en identitetskolonne, understøttes denne funktion ikke i Fabric Warehouse. I stedet skal du bruge en løsningsteknik , der genererer entydige id'er.

Vigtigt

Når en dimensionstabel indeholder automatisk genererede surrogatnøgler, bør du aldrig udføre en afkortning og fuld genindlæsning af den. Det skyldes, at det ville gøre de data, der indlæses i faktatabeller, der bruger dimensionen, ugyldige. Hvis dimensionstabellen understøtter ændringer af scd-type 2 , er det muligvis ikke muligt at genoprette de historiske versioner.

Administrer historiske ændringer

Når en dimensionstabel skal gemme historiske ændringer, skal du implementere en dimension, der langsomt ændrer sig.

Bemærk

Hvis dimensionstabelrækken er et udledt medlem (indsat af en proces til indlæsning af fakta), skal du behandle ændringer som sent ankomne dimensionsdetaljer i stedet for en ændring af scd. I dette tilfælde skal alle ændrede attributter opdateres, og den udledte kolonne med medlemsflag angives til FALSE.

Det er muligt, at en dimension understøtter ændringer af scd type 1 og/eller SCD type 2.

SCD-type 1

Når der registreres ændringer af SCD type 1 , skal du bruge følgende logik.

  1. Opdater eventuelle ændrede attributter.
  2. Hvis tabellen indeholder dato for seneste ændring og senest ændret af kolonner, skal du angive den aktuelle dato og proces, der har foretaget ændringerne.

SCD-type 2

Når der registreres ændringer af SCD type 2 , skal du bruge følgende logik.

  1. Udloebet den aktuelle version ved at angive kolonnen for gyldigheden af slutdatoen til ETL-behandlingsdatoen (eller et passende tidsstempel i kildesystemet) og det aktuelle flag til FALSE.
  2. Hvis tabellen indeholder dato for seneste ændring og senest ændret af kolonner, skal du angive den aktuelle dato og proces, der har foretaget ændringerne.
  3. Indsæt nye medlemmer, hvor kolonnen for gyldigheden af startdatoen er angivet til kolonneværdien for gyldigheden af slutdatoen (bruges til at opdatere den tidligere version), og hvor flaget for den aktuelle version er angivet til TRUE.
  4. Hvis tabellen indeholder oprettelsesdato og oprettet af kolonner, skal du angive den aktuelle dato og proces, der har foretaget indsættelserne.

SCD-type 3

Når der registreres ændringer af SCD type 3 , skal du opdatere attributterne ved hjælp af samme logik som behandling af SCD-type 1.

Sletninger af dimensionsmedlem

Vær forsigtig, hvis kildedata angiver, at dimensionsmedlemmer er blevet slettet (enten fordi de ikke hentes fra kildesystemet, eller fordi de er blevet markeret som slettet). Du bør ikke synkronisere sletninger med dimensionstabellen, medmindre dimensionsmedlemmerne blev oprettet ved en fejl, og der ikke er nogen faktaposter relateret til dem.

Den rette måde at håndtere kildesletninger på er ved at registrere dem som en blød sletning. En blød sletning markerer et dimensionsmedlem som ikke længere aktivt eller gyldigt. For at understøtte dette skal dimensionstabellen indeholde en boolesk attribut med bitdatatypen , f.eks IsDeleted. . Opdater denne kolonne for slettede dimensionsmedlemmer til TRUE (1). Den aktuelle, nyeste version af et dimensionsmedlem kan på samme måde være markeret med en boolesk (bit) værdi i kolonnerne IsCurrent eller IsActive . Alle rapporteringsforespørgsler og semantiske Power BI-modeller skal filtrere poster, der er bløde sletninger, fra.

Datodimension

Kalender- og tidsdimensioner er særlige tilfælde, fordi de normalt ikke har kildedata. De genereres i stedet ved hjælp af fast logik.

Du skal indlæse datodimensionstabellen i begyndelsen af hvert nye år for at udvide rækkerne til et bestemt antal år forude. Der kan være andre forretningsdata, f.eks. data om regnskabsår, helligdage og ugenumre, der skal opdateres regelmæssigt.

Når datodimensionstabellen indeholder relative forskydningsattributter, skal ETL-processen køres dagligt for at opdatere forskydningsattributværdier baseret på den aktuelle dato (i dag).

Vi anbefaler, at logikken til at udvide eller opdatere datodimensionstabellen skrives i T-SQL og indkapsledes i en lagret procedure.

Behandl faktatabeller

Behandling af en faktatabel omfatter synkronisering af data warehouse-dataene med fakta om kildesystemet. Kildedata transformeres først og forberedes til indlæsning i faktatabellen. For hver dimensionsnøgle bestemmer et opslag derefter den surrogatnøgleværdi, der skal gemmes i faktarækken. Når en dimension understøtter SCD-type 2, skal surrogatnøglen for den aktuelle version af dimensionsmedlemmet hentes.

Bemærk

Surrogatnøglen kan normalt beregnes for dato- og klokkeslætsdimensionerne, fordi de skal bruge YYYYMMDD eller HHMM formatere. Du kan få flere oplysninger i Kalender og klokkeslæt.

Hvis et dimensionsnøgleopslag mislykkes, kan det indikere et integritetsproblem med kildesystemet. I dette tilfælde skal faktarækken stadig indsættes i faktatabellen. En gyldig dimensionsnøgle skal stadig gemmes. En fremgangsmåde er at gemme et særligt dimensionsmedlem (f.eks . Ukendt). Denne fremgangsmåde kræver en senere opdatering for at tildele den sande nøgleværdi for dimensionen korrekt, når den er kendt.

Vigtigt

Da Fabric Warehouse ikke gennemtvinger fremmede nøgler, er det vigtigt, at ETL-processen kontrollerer, om der er integritet, når der indlæses data i faktatabeller.

En anden fremgangsmåde, der er relevant, når der er tillid til, at den naturlige nøgle er gyldig, er at indsætte et nyt dimensionsmedlem og derefter gemme dens surrogatnøgleværdi. Du kan få flere oplysninger under Udledte dimensionsmedlemmer senere i dette afsnit.

I følgende diagram vises den logik, der bruges til at behandle en faktatabel.

Diagram, der viser et flow, der beskriver, hvordan nye kilderækker indlæses i en faktatabel, som beskrevet i forrige afsnit.

Når det er muligt, skal en faktatabel indlæses trinvist, hvilket betyder, at der registreres og indsættes nye fakta. En strategi for trinvis belastning er mere skalerbar og reducerer arbejdsbelastningen for både kildesystemerne og destinationssystemerne.

Vigtigt

Især for en stor faktatabel bør det være en sidste udvej at afkorte og genindlæse en faktatabel. Denne fremgangsmåde er dyr med hensyn til procestid, beregningsressourcer og mulig afbrydelse af kildesystemerne. Det omfatter også kompleksitet, når dimensionerne i faktatabellen anvender SCD-type 2. Det skyldes, at dimensionsnøgleopslag skal udføres inden for gyldighedsperioden for versionerne af dimensionsmedlemmet.

Forhåbentlig kan du effektivt registrere nye fakta ved at stole på kildesystem-id'er eller tidsstempler. Når et kildesystem f.eks. pålideligt registrerer salgsordrer, der er i rækkefølge, kan du gemme det seneste salgsordrenummer, der er hentet (også kaldet det høje vandmærke). Den næste proces kan bruge dette salgsordrenummer til at hente nyoprettede salgsordrer og igen gemme det seneste salgsordrenummer, der er hentet til brug for den næste proces. Det kan også være muligt, at en kolonne med oprettelsesdato kan bruges til pålideligt at registrere nye ordrer.

Hvis du ikke kan stole på, at kildesystemdataene effektivt registrerer nye fakta, kan du være afhængig af kildesystemets funktionalitet til at udføre en trinvis belastning. SQL Server og Azure SQL Managed Instance har f.eks. en funktion kaldet CDC (Change Data Capture), som kan spore ændringer af hver række i en tabel. SQL Server, Azure SQL Managed Instance og Azure SQL Database har også en funktion, der kaldes ændringssporing, som kan identificere rækker, der er ændret. Når den er aktiveret, kan den hjælpe dig med effektivt at registrere nye eller ændrede data i en hvilken som helst databasetabel. Du kan muligvis også føje udløsere til relationstabeller, der gemmer nøgler til indsatte, opdaterede eller slettede tabelposter.

Endelig kan du muligvis korrelere kildedata til faktatabellen ved hjælp af attributter. F.eks. salgsordrenummeret og salgsordrelinjenummeret. Men for store faktatabeller kan det være en meget dyr handling at registrere nye, ændrede eller slettede fakta. Det kan også være problematisk, når kildesystemet arkiverer driftsdata.

Udledte dimensionsmedlemmer

Når en proces til indlæsning af fakta indsætter et nyt dimensionsmedlem, kaldes det et udledt medlem. Når en hotel gæst f.eks. tjekker ind, bliver de bedt om at tilmelde sig hotelkæden som loyalitetsmedlem. Et medlemsnummer udstedes med det samme, men oplysningerne om gæsten følger muligvis ikke, før papirarbejdet er indsendt af gæsten (hvis nogensinde).

Det eneste, der er kendt om dimensionsmedlemmet, er dens naturlige nøgle. Processen til indlæsning af fakta skal oprette et nyt dimensionsmedlem ved hjælp af ukendte attributværdier. Det er vigtigt, at den angiver overvågningsattributten IsInferredMember til .TRUE På den måde kan indlæsningsprocessen for dimensionen foretage de nødvendige opdateringer af dimensionsrækken, når de sent ankomne oplysninger hentes. Du kan få flere oplysninger under Administrer historiske ændringer i denne artikel.

Faktaopdateringer eller sletninger

Du skal muligvis opdatere eller slette faktadata. Når en salgsordre f.eks. annulleres, eller et ordreantal ændres. Som beskrevet tidligere til indlæsning af faktatabeller skal du effektivt registrere ændringer og udføre relevante ændringer af faktadataene. I dette eksempel for den annullerede ordre ændres salgsordrestatus sandsynligvis fra Åbn til Annulleret. Denne ændring kræver en opdatering af faktadataene og ikke sletning af en række. I forbindelse med mængdeændringen er det nødvendigt at opdatere målingen for antallet af faktarækker. Denne strategi med at bruge bløde sletninger bevarer historikken. En blød sletning markerer en række som ikke længere aktiv eller gyldig, og alle rapporteringsforespørgsler og semantiske Power BI-modeller bør filtrere poster, der er bløde sletninger, fra.

Når du forventer faktaopdateringer eller sletninger, skal du medtage attributter (f.eks. et salgsordrenummer og dets salgsordrelinjenummer) i faktatabellen for at hjælpe med at identificere de faktarækker, der skal ændres. Sørg for at indeksere disse kolonner for at understøtte effektive ændringshandlinger.

Hvis faktadata blev indsat ved hjælp af et særligt dimensionsmedlem (f.eks . Ukendt), skal du til sidst køre en periodisk proces, der henter aktuelle kildedata for sådanne faktarækker og opdaterer dimensionsnøgler til gyldige værdier.

Relateret indhold

Du kan få flere oplysninger om indlæsning af data i et Fabric Warehouse under: