Del via

Dimensjonal modellering i Microsoft Fabric Warehouse: Last inn tabeller

  • Artikkel

Gjelder for:✅ SQL Analytics-endepunkt og Warehouse i Microsoft Fabric

Merk

Denne artikkelen er en del av dimensjonalmodelleringsserien med artikler. Denne serien fokuserer på anbefalte fremgangsmåter for veiledning og utforming knyttet til dimensjonal modellering i Microsoft Fabric Warehouse.

Denne artikkelen gir deg veiledning og anbefalte fremgangsmåter for innlasting av dimensjons- og faktatabeller i en dimensjonsmodell. Det gir praktisk veiledning for Warehouse i Microsoft Fabric, som er en opplevelse som støtter mange T-SQL-funksjoner, som å opprette tabeller og administrere data i tabeller. Så du har full kontroll over å opprette dimensjonale modelltabeller og laste dem inn med data.

Merk

I denne artikkelen refererer termdatalageret til et virksomhetsdatalager, som leverer omfattende integrering av kritiske data på tvers av organisasjonen. Derimot refererer det frittstående termlageret til et Fabric Warehouse, som er en programvare som en tjeneste (SaaS) relasjonsdatabase som du kan bruke til å implementere et datalager. For klarhet, i denne artikkelen sistnevnte er nevnt som Fabric Warehouse.

Tips

Hvis du er uerfaren med dimensjonal modellering, bør du vurdere denne serien med artikler ditt første trinn. Det er ikke ment å gi en fullstendig diskusjon om dimensjonal modelleringsutforming. Hvis du vil ha mer informasjon, kan du se allment publisert innhold, for eksempel Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3. utgave, 2013) av Ralph Kimball og andre.

Laste inn en dimensjonal modell

Innlasting av en dimensjonal modell innebærer regelmessig kjøring av en uttrekkings-, transformerings- og belastningsprosess (ETL). En ETL-prosess orkestrerer kjøring av andre prosesser, som vanligvis er opptatt av å sette opp kildedata, synkronisere dimensjonsdata, sette inn rader i faktatabeller og registrere overvåkingsdata og feil.

For en Fabric Warehouse-løsning kan du bruke Data Factory til å utvikle og kjøre ETL-prosessen. Prosessen kan fase, transformere og laste inn kildedata i dimensjonsmodelltabellene.

Spesielt kan du:

  • Bruk datasamlebånd til å bygge arbeidsflyter for å organisere ETL-prosessen. Datasamlebånd kan kjøre SQL-skript, lagrede prosedyrer og mer.
  • Bruk dataflyter til å utvikle lavkodelogikk til å innta data fra hundrevis av datakilder. Dataflyter støtter kombinasjon av data fra flere kilder, transformerer data og laster dem deretter inn til et mål, for eksempel en dimensjonal modelltabell. Dataflyter bygges ved hjelp av den velkjente Power Query-opplevelsen som er tilgjengelig i dag på tvers av mange Microsoft-produkter, inkludert Microsoft Excel og Power BI Desktop.

Merk

ETL-utvikling kan være kompleks, og utvikling kan være utfordrende. Det anslås at 60-80 prosent av et datalagerutviklingsarbeid er dedikert til ETL-prosessen.

Iverksetting

Den generelle arbeidsflyten for en ETL-prosess er å:

  1. Du kan eventuelt laste inn oppsamlingstabeller.
  2. Behandle dimensjonstabeller.
  3. Behandle faktatabeller.
  4. Du kan eventuelt utføre oppgaver etter behandling, for eksempel utløse oppdatering av avhengig stoffinnhold (for eksempel en semantisk modell).

Diagrammet viser de fire trinnene i ETL-prosessen som beskrevet i forrige avsnitt.

Dimensjonstabeller bør behandles først for å sikre at de lagrer alle dimensjonsmedlemmer, inkludert de som er lagt til kildesystemer siden forrige ETL-prosess. Når det er avhengigheter mellom dimensjoner, som tilfellet er med utriggerdimensjoner, bør dimensjonstabeller behandles etter avhengighet. En geografidimensjon som brukes av en kundedimensjon og en leverandørdimensjon, bør for eksempel behandles før de to andre dimensjonene.

Faktatabeller kan behandles når alle dimensjonstabeller behandles.

Når alle dimensjonale modelltabeller behandles, kan du utløse oppdateringen av avhengige semantiske modeller. Det er også lurt å sende et varsel til relevante ansatte for å informere dem om utfallet av ETL-prosessen.

Fasedata

Oppsamling av kildedata kan bidra til å støtte krav til innlasting og transformasjon av data. Det innebærer å trekke ut kildesystemdata og laste dem inn i oppsamlingstabeller, som du oppretter for å støtte ETL-prosessen. Vi anbefaler at du faser kildedata fordi det kan:

  • Minimer innvirkningen på driftssystemer.
  • Brukes til å hjelpe til med, og optimalisere, ETL-behandling.
  • Gi mulighet til å starte ETL-prosessen på nytt, uten å måtte laste inn data fra kildesystemer på nytt.

Data i oppsamlingstabeller bør aldri gjøres tilgjengelig for forretningsbrukere. Det er bare relevant for ETL-prosessen.

Merk

Når dataene lagres i et Fabric Lakehouse, er det kanskje ikke nødvendig å iscenesette dataene i datalageret. Hvis den implementerer en medaljongarkitektur, kan du hente dataene fra enten bronse-, sølv- eller gulllaget.

Vi anbefaler at du oppretter et skjema i lageret, muligens kalt staging. Oppsamlingstabeller bør ligne på kildetabellene så nært som mulig når det gjelder kolonnenavn og datatyper. Innholdet i hver tabell bør fjernes ved starten av ETL-prosessen. Vær imidlertid oppmerksom på at Fabric Warehouse-tabeller ikke kan avkortes. I stedet kan du slippe og gjenskape hver oppsamlingstabell før du laster den inn med data.

Du kan også vurdere alternativer for datavirtualisering som en del av oppsamlingsstrategien. Du kan bruke:

Transformer data

Strukturen til kildedataene ligner kanskje ikke på målstrukturene i dimensjonsmodelltabellene. Etl-prosessen må derfor omforme kildedataene slik at de samsvarer med strukturen i de dimensjonale modelltabellene.

Datalageret må også levere rensede og samsvarende data, så kildedata må kanskje transformeres for å sikre kvalitet og konsekvens.

Merk

Begrepet søppel inn, søppel ut gjelder absolutt for data lagring – derfor unngå å laste søppel (lav kvalitet) data i dimensjonale modelltabeller.

Her er noen transformasjoner som ETL-prosessen kan utføre.

  • Kombinere data: Data fra ulike kilder kan integreres (flettes) basert på samsvarende nøkler. Produktdata lagres for eksempel på tvers av ulike systemer (for eksempel produksjon og markedsføring), men alle bruker en felles lagerføringsenhet (SKU). Data kan også tilføyes når de deler en felles struktur. Salgsdata lagres for eksempel i flere systemer. En sammensetning av salg fra hvert system kan produsere et supersett av alle salgsdata.
  • Konverter datatyper: Datatyper kan konverteres til de som er definert i de dimensjonale modelltabellene.
  • Beregninger: Beregninger kan gjøres for å produsere verdier for de dimensjonale modelltabellene. For en dimensjonstabell for ansatte kan du for eksempel kjede sammen for- og etternavn for å produsere det fullstendige navnet. Som et annet eksempel, for salgsfaktatabellen, kan du beregne brutto salgsinntekter, som er produktet av enhetspris og antall.
  • Finn og administrer historiske endringer: Endring kan oppdages og lagres på riktig måte i dimensjonstabeller. Hvis du vil ha mer informasjon, kan du se Behandle historiske endringer senere i denne artikkelen.
  • Aggregerte data: Aggregasjon kan brukes til å redusere faktatabelldimensjonalitet og/eller øke detaljnivået til fakta. Salgsfaktatabellen trenger for eksempel ikke å lagre salgsordrenumre. Derfor kan et aggregert resultat som grupperes etter alle dimensjonstaster, brukes til å lagre faktatabelldataene.

Last inn data

Du kan laste inn tabeller i et Fabric Warehouse ved hjelp av følgende alternativer for datainntak.

  • KOPIER TIL (T-SQL): Dette alternativet er nyttig når kildedataene består av Parquet- eller CSV-filer som er lagret i en ekstern Azure-lagringskonto, for eksempel ADLS Gen2 eller Azure Blob Storage.
  • Datasamlebånd: I tillegg til å organisere ETL-prosessen, kan datasamlebånd inkludere aktiviteter som kjører T-SQL-setninger, utføre oppslag eller kopiere data fra en datakilde til et mål.
  • Dataflyter: Som et alternativ til datasamlebånd gir dataflyter en kodefri opplevelse for å transformere og rense data.
  • Krysslagerinntak: Når data lagres i samme arbeidsområde, kan krysslagerinntak bli med i forskjellige lager- eller lakehouse-tabeller. Den støtter T-SQL-kommandoer som INSERT…SELECT, SELECT INTOog CREATE TABLE AS SELECT (CTAS). Disse kommandoene er spesielt nyttige når du vil transformere og laste inn data fra oppsamlingstabeller i samme arbeidsområde. De er også settbaserte operasjoner, som sannsynligvis vil være den mest effektive og raskeste måten å laste inn dimensjonale modelltabeller på.

Tips

Hvis du vil ha en fullstendig forklaring av disse alternativene for datainntak, inkludert anbefalte fremgangsmåter, kan du se Innta data i lageret.

Logger

ETL-prosesser krever vanligvis dedikert overvåking og vedlikehold. Av disse grunnene anbefaler vi at du loggfører resultatene av ETL-prosessen til ikke-dimensjonale modelltabeller i lageret. Du bør generere en unik ID for hver ETL-prosess og bruke den til å logge detaljer om hver operasjon.

Vurder logging:

  • ETL-prosessen:
    • En unik ID for hver ETL-kjøring
    • Starttidspunkt og sluttidspunkt
    • Status (vellykket eller mislykket)
    • Det oppstod eventuelle feil
  • Hver oppsamlings- og dimensjonsmodelltabell:
    • Starttidspunkt og sluttidspunkt
    • Status (vellykket eller mislykket)
    • Rader satt inn, oppdatert og slettet
    • Antall siste tabellrader
    • Det oppstod eventuelle feil
  • Andre operasjoner:
    • Starttidspunkt og sluttidspunkt for oppdateringsoperasjoner for semantisk modell

Tips

Du kan opprette en semantisk modell som er dedikert til å overvåke og analysere ETL-prosessene dine. Prosessvarigheter kan hjelpe deg med å identifisere flaskehalser som kan ha nytte av gjennomgang og optimalisering. Radantall kan gi deg mulighet til å forstå størrelsen på den trinnvise belastningen hver gang ETL kjører, og også bidra til å forutsi den fremtidige størrelsen på datalageret (og når stoffkapasiteten skal skaleres hvis det er aktuelt).

Prosessdimensjonstabeller

Behandling av en dimensjonstabell innebærer synkronisering av datalagerdataene med kildesystemene. Kildedata transformeres først og klargjøres for innlasting i dimensjonstabellen. Disse dataene samsvarer deretter med eksisterende dimensjonstabelldata ved å koble til forretningsnøklene. Det er da mulig å avgjøre om kildedataene representerer nye eller endrede data. Når dimensjonstabellen bruker sakte skiftende dimensjonstype (SCD) type 1, gjøres endringer ved å oppdatere eksisterende dimensjonstabellrader. Når tabellen bruker SCD-type 2-endringer, er den eksisterende versjonen utløpt og en ny versjon settes inn.

Diagrammet nedenfor viser logikken som brukes til å behandle en dimensjonstabell.

Diagrammet viser en flyt som beskriver hvordan nye og endrede kilderader lastes inn i en dimensjonstabell, som beskrevet i avsnittet nedenfor.

Vurder prosessen for dimensjonstabellen Product .

  • Når nye produkter legges til i kildesystemet, settes rader inn i dimensjonstabellen Product .
  • Når produkter endres, oppdateres eller settes eksisterende rader i dimensjonstabellen enten inn.
    • Når SCD-type 1 gjelder, gjøres oppdateringer for de eksisterende radene.
    • Når SCD-typen 2 gjelder, blir oppdateringer gjort for å utløpe gjeldende radversjoner, og nye rader som representerer gjeldende versjon, settes inn.
    • Når SCD-type 3 gjelder, forekommer en prosess som ligner på SCD-type 1, og oppdaterer de eksisterende radene uten å sette inn nye rader.

Surrogatnøkler

Vi anbefaler at hver dimensjonstabell har en surrogatnøkkel, som bør bruke den minste mulige heltallsdatatypen. I SQL Server-baserte miljøer som vanligvis gjøres ved å opprette en identitetskolonne, støttes imidlertid ikke denne funksjonen i Fabric Warehouse. I stedet må du bruke en løsningsteknikk som genererer unike identifikatorer.

Viktig

Når en dimensjonstabell inneholder automatisk genererte surrogatnøkler, bør du aldri utføre en avkorting og full omlasting av den. Det er fordi det ville ugyldiggjøre dataene som lastes inn i faktatabeller som bruker dimensjonen. Hvis dimensjonstabellen støtter endringer av SCD-type 2 , kan det også hende at det ikke er mulig å generere de historiske versjonene på nytt.

Administrer historisk endring

Når en dimensjonstabell må lagre historiske endringer, må du implementere en sakte skiftende dimensjon (SCD).

Merk

Hvis dimensjonstabellraden er et utsatt medlem (satt inn av en faktainnlastingsprosess), bør du behandle eventuelle endringer som sene dimensjonsdetaljer i stedet for en SCD-endring. I dette tilfellet bør eventuelle endrede attributter oppdateres, og kolonnen for det utsatte medlemsflagget er satt til FALSE.

Det er mulig at en dimensjon kan støtte SCD type 1 og/eller SCD type 2 endringer.

SCD-type 1

Når SCD-type 1-endringer oppdages, bruker du følgende logikk.

  1. Oppdater eventuelle endrede attributter.
  2. Hvis tabellen inneholder siste endringsdato og sist endret av kolonner, angir du gjeldende dato og prosess som gjorde endringene.

SCD-type 2

Når scd type 2-endringer oppdages, bruker du følgende logikk.

  1. Utløp gjeldende versjon ved å angi sluttdato-gyldighetskolonnen til ETL-behandlingsdatoen (eller et passende tidsstempel i kildesystemet) og gjeldende flagg til FALSE.
  2. Hvis tabellen inneholder siste endringsdato og sist endret av kolonner, angir du gjeldende dato og prosess som gjorde endringene.
  3. Sett inn nye medlemmer som har kolonnen for gyldighet for startdato satt til kolonneverdien for sluttdatovaliditet (brukes til å oppdatere den forrige versjonen) og har gjeldende versjonsflagg satt til TRUE.
  4. Hvis tabellen inneholder opprettet dato og opprettet av kolonner, angir du gjeldende dato og prosess som gjorde innsettingene.

SCD-type 3

Når SCD-type 3-endringer oppdages, oppdaterer du attributtene ved hjelp av lignende logikk for behandling av SCD-type 1.

Slettinger av dimensjonsmedlem

Vær forsiktig hvis kildedata indikerer at dimensjonsmedlemmer ble slettet (enten fordi de ikke hentes fra kildesystemet, eller de er flagget som slettet). Du bør ikke synkronisere slettinger med dimensjonstabellen, med mindre dimensjonsmedlemmer ble opprettet ved en feil, og det er ingen faktaposter relatert til dem.

Den riktige måten å håndtere kildeslettinger på, er å registrere dem som en myk sletting. En myk sletting markerer et dimensjonsmedlem som ikke lenger er aktivt eller gyldig. Hvis du vil støtte denne saken, bør dimensjonstabellen inneholde et boolsk attributt med bitdatatypen , for eksempel IsDeleted. Oppdater denne kolonnen for alle slettede dimensjonsmedlemmer til TRUE (1). Den gjeldende, nyeste versjonen av et dimensjonsmedlem kan på samme måte være merket med en boolsk verdi (bit) i IsCurrent kolonnene eller IsActive kolonnene. Alle rapporteringsspørringer og Semantiske Power BI-modeller bør filtrere ut poster som er myke slettinger.

Datodimensjon

Kalender- og tidsdimensjoner er spesielle tilfeller fordi de vanligvis ikke har kildedata. I stedet genereres de ved hjelp av fast logikk.

Du bør laste inn datodimensjonstabellen i begynnelsen av hvert nye år for å utvide radene til et bestemt antall år fremover. Det kan være andre forretningsdata, for eksempel data for regnskapsår, helligdager, ukenumre som skal oppdateres regelmessig.

Når datodimensjonstabellen inneholder relative forskyvningsattributter, må ETL-prosessen kjøres daglig for å oppdatere attributtverdier for forskyvning basert på gjeldende dato (i dag).

Vi anbefaler at logikken for å utvide eller oppdatere datodimensjonstabellen skrives i T-SQL og innkapsles i en lagret prosedyre.

Behandle faktatabeller

Behandling av en faktatabell innebærer synkronisering av datalagerdataene med fakta om kildesystemet. Kildedata transformeres først og klargjøres for innlasting i faktatabellen. For hver dimensjonsnøkkel bestemmer et oppslag surrogatnøkkelverdien som skal lagres i faktaraden. Når en dimensjon støtter SCD type 2, skal surrogatnøkkelen for gjeldende versjon av dimensjonsmedlemmet hentes.

Merk

Vanligvis kan surrogatnøkkelen beregnes for dato- og klokkeslettdimensjonene fordi de skal bruke YYYYMMDD eller HHMM formatere. Hvis du vil ha mer informasjon, kan du se Kalender og klokkeslett.

Hvis et dimensjonsnøkkeloppslag mislykkes, kan det indikere et integritetsproblem med kildesystemet. I dette tilfellet må faktaraden fortsatt settes inn i faktatabellen. En gyldig dimensjonsnøkkel må fortsatt lagres. Én tilnærming er å lagre et spesielt dimensjonsmedlem (for eksempel Ukjent). Denne fremgangsmåten krever en senere oppdatering for å tilordne den sanne dimensjonsnøkkelverdien på riktig måte, når den er kjent.

Viktig

Fordi Fabric Warehouse ikke håndhever sekundærnøkler, er det viktig at ETL-prosessen kontrollerer integriteten når den laster inn data i faktatabeller.

En annen tilnærming, relevant når det er tillit til at den naturlige nøkkelen er gyldig, er å sette inn et nytt dimensjonsmedlem og deretter lagre surrogatnøkkelverdien. Hvis du vil ha mer informasjon, kan du se Utsatte dimensjonsmedlemmer senere i denne delen.

Diagrammet nedenfor viser logikken som brukes til å behandle en faktatabell.

Diagrammet viser en flyt som beskriver hvordan nye kilderader lastes inn i en faktatabell, som beskrevet i forrige avsnitt.

Når det er mulig, bør en faktatabell lastes inn trinnvis, noe som betyr at nye fakta oppdages og settes inn. En trinnvis belastningsstrategi er mer skalerbar, og den reduserer arbeidsbelastningen for både kildesystemene og målsystemene.

Viktig

Spesielt for en stor faktatabell bør det være en siste utvei å avkorte og laste inn en faktatabell på nytt. Denne tilnærmingen er dyr når det gjelder prosesstid, databehandlingsressurser og mulig forstyrrelse av kildesystemene. Det innebærer også kompleksitet når faktatabelldimensjonene bruker SCD-type 2. Det er fordi dimensjonsnøkkeloppslag må gjøres innenfor gyldighetsperioden for dimensjonsmedlemsversjonene.

Forhåpentligvis kan du effektivt oppdage nye fakta ved å stole på kildesystemidentifikatorer eller tidsstempel. Når et kildesystem for eksempel registrerer salgsordrer som er i rekkefølge på en pålitelig måte, kan du lagre det nyeste salgsordrenummeret som hentes (kjent som det høye vannmerket). Den neste prosessen kan bruke dette salgsordrenummeret til å hente nyopprettede salgsordrer, og lagre det nyeste salgsordrenummeret som hentes for bruk av neste prosess. Det kan også være mulig at en opprettingsdatokolonne kan brukes til å oppdage nye ordrer på en pålitelig måte.

Hvis du ikke kan stole på kildesystemdataene for effektivt å oppdage nye fakta, kan du kanskje stole på at kildesystemet kan utføre en trinnvis belastning. SQL Server og Azure SQL administrert forekomst for eksempel ha en funksjon kalt endre datahenting (CDC), som kan spore endringer i hver rad i en tabell. SQL Server, Azure SQL administrert forekomst og Azure SQL Database har også en funksjon kalt endringssporing, som kan identifisere rader som er endret. Når det er aktivert, kan det hjelpe deg å effektivt oppdage nye eller endrede data i en hvilken som helst databasetabell. Du kan også legge til utløsere i relasjonstabeller som lagrer nøkler for innsatte, oppdaterte eller slettede tabellposter.

Til slutt kan du kanskje koordinere kildedata til faktatabellen ved hjelp av attributter. Salgsordrenummer og salgsordrelinjenummer. For store faktatabeller kan det imidlertid være en svært dyr operasjon å oppdage nye, endrede eller slettede fakta. Det kan også være problematisk når kildesystemet arkiverer driftsdata.

Utsatte dimensjonsmedlemmer

Når en faktainnlastingsprosess setter inn et nytt dimensjonsmedlem, kalles det et utsatt medlem. Når en hotellgjest for eksempel sjekker inn, blir de bedt om å bli med i hotellkjeden som lojalitetsmedlem. Et medlemsnummer utstedes umiddelbart, men detaljene til gjesten kan ikke følge før papirene er sendt inn av gjesten (hvis noen gang).

Alt som er kjent om dimensjonsmedlemmet er den naturlige nøkkelen. Faktabelastningsprosessen må opprette et nytt dimensjonsmedlem ved hjelp av ukjente attributtverdier. Viktigere er at det må angi IsInferredMember revisjonsattributtet til TRUE. På den måten kan dimensjonsbelastningsprosessen foreta de nødvendige oppdateringene til dimensjonsraden når de sene ankommende detaljene hentes. Hvis du vil ha mer informasjon, kan du se Behandle historiske endringer i denne artikkelen.

Faktaoppdateringer eller slettinger

Det kan hende du må oppdatere eller slette faktadata. Når en salgsordre for eksempel kanselleres, eller et ordreantall endres. Som beskrevet tidligere for innlasting av faktatabeller, må du effektivt oppdage endringer og utføre nødvendige endringer i faktadataene. I dette eksemplet for den kansellerte bestillingen vil salgsordrestatusen sannsynligvis endres fra Åpne til Avbrutt. Denne endringen krever en oppdatering av faktadataene, og ikke slettingen av en rad. For endring av antall er det nødvendig med en oppdatering av mål for faktaradantall. Denne strategien for å bruke myke slettinger bevarer loggen. En myk sletting markerer en rad som ikke lenger aktiv eller gyldig, og alle rapporteringsspørringer og Semantiske Power BI-modeller bør filtrere ut poster som er myke slettinger.

Når du forventer faktaoppdateringer eller slettinger, bør du inkludere attributter (for eksempel et salgsordrenummer og linjenummeret for salgsordre) i faktatabellen for å identifisere faktaradene som skal endres. Pass på å indeksere disse kolonnene for å støtte effektive endringsoperasjoner.

Til slutt, hvis faktadata ble satt inn ved hjelp av et spesielt dimensjonsmedlem (for eksempel Ukjent), må du kjøre en periodisk prosess som henter gjeldende kildedata for slike faktarader og oppdaterer dimensjonstaster til gyldige verdier.

Relatert innhold

Hvis du vil ha mer informasjon om innlasting av data i et Fabric Warehouse, kan du se: