Dimensionsmodellering i Microsoft Fabric Warehouse: Läsa in tabeller

Gäller för:✅ SQL-analysslutpunkt och lager i Microsoft Fabric

Den här artikeln innehåller vägledning och metodtips för att läsa in dimensions- och faktatabeller i en dimensionsmodell. Det ger praktisk vägledning för Warehouse i Microsoft Fabric, vilket är en upplevelse som stöder många T-SQL-funktioner, till exempel att skapa tabeller och hantera data i tabeller. Därför har du fullständig kontroll över att skapa dina dimensionsmodelltabeller och läsa in dem med data.

Läsa in en dimensionsmodell

Inläsning av en dimensionsmodell innebär att du regelbundet kör en ETL-process (Extract, Transform, and Load). En ETL-process samordnar körningen av andra processer, som vanligtvis handlar om att mellanlagra källdata, synkronisera dimensionsdata, infoga rader i faktatabeller och registrera granskningsdata och fel.

För en Fabric Warehouse-lösning kan du använda Data Factory för att utveckla och köra din ETL-process. Processen kan mellanlagra, transformera och läsa in källdata i dina dimensionsmodelltabeller.

Mer specifikt kan du:

• Använd datapipelines för att skapa arbetsflöden för att samordna ETL-processen. Datapipelines kan köra SQL-skript, lagrade procedurer med mera.
• Använd dataflöden för att utveckla logik med låg kod för att mata in data från hundratals datakällor. Dataflöden har stöd för att kombinera data från flera källor, transformera data och sedan läsa in dem till ett mål, till exempel en dimensionsmodelltabell. Dataflöden skapas med hjälp av den välbekanta Power Query-upplevelsen som är tillgänglig i dag för många Microsoft-produkter, inklusive Microsoft Excel och Power BI Desktop.

Orkestrering

Det allmänna arbetsflödet för en ETL-process är att:

1. Du kan också läsa in mellanlagringstabeller.
2. Bearbeta dimensionstabeller.
3. Bearbeta faktatabeller.
4. Du kan också utföra uppgifter efter bearbetning, till exempel att utlösa uppdateringen av beroende Infrastrukturinnehåll (till exempel en semantisk modell).

Dimensionstabeller bör bearbetas först för att säkerställa att de lagrar alla dimensionsmedlemmar, inklusive de som har lagts till i källsystemen sedan den senaste ETL-processen. När det finns beroenden mellan dimensioner, vilket är fallet med utriggardimensioner, bör dimensionstabeller bearbetas i beroendeordning. Till exempel bör en geografidimension som används av en kunddimension och en leverantörsdimension bearbetas före de andra två dimensionerna.

Faktatabeller kan bearbetas när alla dimensionstabeller har bearbetats.

När alla dimensionsmodelltabeller bearbetas kan du utlösa uppdateringen av beroende semantiska modeller. Det är också en bra idé att skicka en anmälan till relevant personal för att informera dem om resultatet av ETL-processen.

Mellanlagra data

Mellanlagring av källdata kan hjälpa dig att stödja krav på datainläsning och transformering. Det handlar om att extrahera källsystemdata och läsa in dem i mellanlagringstabeller som du skapar för att stödja ETL-processen. Vi rekommenderar att du mellanlagra källdata eftersom det kan:

• Minimera påverkan på driftsystemen.
• Används för att hjälpa till med och optimera ETL-bearbetning.
• Ge möjlighet att starta om ETL-processen utan att behöva läsa in data från källsystem igen.

Data i mellanlagringstabeller bör aldrig göras tillgängliga för företagsanvändare. Det är bara relevant för ETL-processen.

Vi rekommenderar att du skapar ett schema i lagret, eventuellt med namnet staging. Mellanlagringstabeller bör likna källtabellerna så nära som möjligt när det gäller kolumnnamn och datatyper. Innehållet i varje tabell bör tas bort i början av ETL-processen. TRUNCATE TABLE stöds för detta ändamål.

Du kan också överväga alternativ för datavirtualisering som en del av din mellanlagringsstrategi. Du kan använda:

• Spegling, som är en nyckelfärdig lösning med låg kostnad och låg latens som gör att du kan skapa en replik av dina data i OneLake. Mer information finns i Varför använda spegling i infrastrukturresurser?.
• OneLake-genvägar, som pekar på andra lagringsplatser som kan innehålla dina källdata. Genvägar kan användas som tabeller i T-SQL-frågor.
• PolyBase i SQL Server, som är en datavirtualiseringsfunktion för SQL Server. Med PolyBase kan T-SQL-frågor koppla data från externa källor till relationstabeller i en instans av SQL Server.
• Datavirtualisering med Azure SQL Managed Instance, som gör att du kan köra T-SQL-frågor på filer som lagrar data i vanliga dataformat i Azure Data Lake Storage (ADLS) Gen2 eller Azure Blob Storage och kombinera dem med lokalt lagrade relationsdata med hjälp av kopplingar.

Transformera data

Strukturen för dina källdata kanske inte liknar målstrukturerna för dina dimensionsmodelltabeller. Därför måste ETL-processen omforma källdata så att de överensstämmer med strukturen för de dimensionsmodelltabeller.

Dessutom måste informationslagret leverera rensade och överensstämmande data, så källdata kan behöva omvandlas för att säkerställa kvalitet och konsekvens.

Här är några transformeringar som ETL-processen kan utföra.

• Kombinera data: Data från olika källor kan integreras (sammanfogas) baserat på matchande nycklar. Till exempel lagras produktdata i olika system (t.ex. tillverkning och marknadsföring), men de använder alla en gemensam lagerhållningsenhet (SKU). Data kan också läggas till när de delar en gemensam struktur. Till exempel lagras försäljningsdata i flera system. En union av försäljningen från varje system kan skapa en supermängd av alla försäljningsdata.
• Konvertera datatyper: Datatyper kan konverteras till dem som definieras i dimensionsmodelltabellerna.
• Beräkningar: Beräkningar kan göras för att skapa värden för dimensionsmodelltabellerna. För en medarbetardimensionstabell kan du till exempel sammanfoga för- och efternamn för att skapa det fullständiga namnet. Som ett annat exempel för faktatabellen för försäljning kan du beräkna bruttoförsäljningsintäkter, vilket är produkten av enhetspris och kvantitet.
• Identifiera och hantera historiska ändringar: Ändringar kan identifieras och lagras på lämpligt sätt i dimensionstabeller. Mer information finns i Hantera historiska ändringar senare i den här artikeln.
• Aggregerade data: Sammansättning kan användas för att minska faktatabellens dimensionalitet och/eller för att öka detaljrikedomen för fakta. Tabellen försäljningsfakta behöver till exempel inte lagra försäljningsordernummer. Därför kan ett aggregerat resultat som grupperas efter alla dimensionsnycklar användas för att lagra faktatabelldata.

Läsa in data

Du kan läsa in tabeller i ett infrastrukturlager med hjälp av följande datainmatningsalternativ.

• KOPIERA TILL (T-SQL): Det här alternativet är användbart när källdata består av Parquet- eller CSV-filer som lagras i ett externt Azure Storage-konto, till exempel ADLS Gen2 eller Azure Blob Storage.
• Datapipelines: Förutom att samordna ETL-processen kan datapipelines innehålla aktiviteter som kör T-SQL-instruktioner, utföra sökningar eller kopiera data från en datakälla till ett mål.
• Dataflöden: Som ett alternativ till datapipelines ger dataflöden en kodfri upplevelse för att transformera och rensa data.
• Inmatning mellan lager: När data lagras på samma arbetsyta tillåter inmatning mellan lagerställen att olika lager- eller sjöhustabeller kan kopplas. Den stöder T-SQL-kommandon som INSERT…SELECT, SELECT INTOoch CREATE TABLE AS SELECT (CTAS). Dessa kommandon är särskilt användbara när du vill transformera och läsa in data från mellanlagringstabeller på samma arbetsyta. De är också uppsättningsbaserade åtgärder, vilket sannolikt är det mest effektiva och snabbaste sättet att läsa in dimensionsmodelltabeller.

Loggning

ETL-processer kräver vanligtvis dedikerad övervakning och underhåll. Därför rekommenderar vi att du loggar resultatet av ETL-processen till icke-dimensionella modelltabeller i ditt lager. Du bör generera ett unikt ID för varje ETL-process och använda det för att logga information om varje åtgärd.

Överväg att logga:

• ETL-processen:
  • Ett unikt ID för varje ETL-körning
  • Starttid och sluttid
  • Status (lyckad eller misslyckad)
  • Eventuella fel som påträffas
• Varje mellanlagrings- och dimensionsmodelltabell:
  • Starttid och sluttid
  • Status (lyckad eller misslyckad)
  • Rader infogade, uppdaterade och borttagna
  • Antal rader i sluttabellen
  • Eventuella fel som påträffas
• Andra åtgärder:
  • Starttid och sluttid för semantiska modelluppdateringsåtgärder

Bearbeta dimensionstabeller

Bearbetning av en dimensionstabell innebär att datalagerdata synkroniseras med källsystemen. Källdata transformeras först och förbereds för inläsning till dimensionstabellen. Dessa data matchas sedan med befintliga dimensionstabelldata genom att kopplas till affärsnycklarna. Det går sedan att avgöra om källdata representerar nya eller ändrade data. När dimensionstabellen tillämpar långsamt föränderlig dimensionstyp (SCD) typ 1, görs ändringar genom att uppdatera de befintliga dimensionstabellraderna. När tabellen tillämpar SCD typ 2 ändras den befintliga versionen och en ny version infogas.

Följande diagram visar logiken som används för att bearbeta en dimensionstabell.

Överväg processen för dimensionstabellen Product .

• När nya produkter läggs till i källsystemet infogas rader i dimensionstabellen Product .
• När produkter ändras uppdateras eller infogas befintliga rader i dimensionstabellen.
  • När SCD typ 1 gäller görs uppdateringar av de befintliga raderna.
  • När SCD typ 2 gäller görs uppdateringar för att förfalla de aktuella radversionerna och nya rader som representerar den aktuella versionen infogas.
  • När SCD-typ 3 tillämpas sker en process som liknar SCD typ 1 och uppdaterar de befintliga raderna utan att infoga nya rader.

Surrogatnycklar

Vi rekommenderar att varje dimensionstabell har en surrogatnyckel, som bör använda den minsta möjliga heltalsdatatypen. I SQL Server-baserade miljöer som vanligtvis görs genom att skapa en identitetskolumn, men den här funktionen stöds inte i Fabric Warehouse. I stället måste du använda en lösningsteknik som genererar unika identifierare.

Hantera historisk ändring

När en dimensionstabell måste lagra historiska ändringar måste du implementera en långsamt föränderlig dimension (SCD).

Det är möjligt att en dimension kan ha stöd för SCD-typ 1- och/eller SCD-typ 2-ändringar.

SCD-typ 1

När SCD-typ 1-ändringar identifieras använder du följande logik.

1. Uppdatera eventuella ändrade attribut.
2. Om tabellen innehåller datum för senast ändrad och senast ändrad av kolumner anger du det datum och den process som gjorde ändringarna.

SCD-typ 2

När SCD-typ 2-ändringar identifieras använder du följande logik.

1. Förfalla den aktuella versionen genom att ange kolumnen för slutdatumets giltighet till ETL-bearbetningsdatumet (eller en lämplig tidsstämpel i källsystemet) och den aktuella flaggan till FALSE.
2. Om tabellen innehåller datum för senast ändrad och senast ändrad av kolumner anger du det datum och den process som gjorde ändringarna.
3. Infoga nya medlemmar som har kolumnen för giltighet för startdatum inställt på kolumnvärdet för slutdatumets giltighet (används för att uppdatera den tidigare versionen) och har den aktuella versionsflaggan inställd på TRUE.
4. Om tabellen innehåller skapat datum och skapats av kolumner anger du det datum och den process som gjorde infogningen.

SCD-typ 3

När SCD typ 3-ändringar identifieras uppdaterar du attributen med hjälp av liknande logik som bearbetning av SCD-typ 1.

Borttagningar av dimensionsmedlem

Var försiktig om källdata anger att dimensionsmedlemmar har tagits bort (antingen för att de inte hämtas från källsystemet eller så har de flaggats som borttagna). Du bör inte synkronisera borttagningar med dimensionstabellen, såvida inte dimensionsmedlemmar har skapats i fel och det inte finns några faktaposter som är relaterade till dem.

Det lämpliga sättet att hantera källborttagningar är att registrera dem som en mjuk borttagning. En mjuk borttagning markerar en dimensionsmedlem som inte längre aktiv eller giltig. För att stödja det här fallet bör dimensionstabellen innehålla ett booleskt attribut med bitdatatypen , till exempel IsDeleted. Uppdatera den här kolumnen för alla borttagna dimensionsmedlemmar till TRUE (1). Den aktuella, senaste versionen av en dimensionsmedlem kan på liknande sätt markeras med ett booleskt värde (bit) i kolumnerna IsCurrent eller IsActive . Alla rapporteringsfrågor och Power BI-semantiska modeller bör filtrera bort poster som är mjuka borttagningar.

Datumdimension

Kalender- och tidsdimensioner är specialfall eftersom de vanligtvis inte har källdata. I stället genereras de med hjälp av fast logik.

Du bör läsa in datumdimensionstabellen i början av varje nytt år för att utöka raderna till ett visst antal år framåt. Det kan finnas andra affärsdata, till exempel räkenskapsårsdata, helgdagar, veckonummer som uppdateras regelbundet.

När datumdimensionstabellen innehåller relativa offsetattribut måste ETL-processen köras dagligen för att uppdatera förskjutningsattributvärden baserat på aktuellt datum (idag).

Vi rekommenderar att logiken för att utöka eller uppdatera datumdimensionstabellen skrivs i T-SQL och kapslas in i en lagrad procedur.

Processfaktatabeller

Bearbetning av en faktatabell innebär att datalagerdata synkroniseras med källsystemfakta. Källdata transformeras först och förbereds för inläsning till faktatabellen. För varje dimensionsnyckel avgör ett uppslag sedan surrogatnyckelvärdet som ska lagras i faktaraden. När en dimension stöder SCD typ 2 ska surrogatnyckeln för den aktuella versionen av dimensionsmedlemmen hämtas.

Om en dimensionsnyckelsökning misslyckas kan det tyda på ett integritetsproblem med källsystemet. I det här fallet måste faktaraden fortfarande infogas i faktatabellen. En giltig dimensionsnyckel måste fortfarande lagras. En metod är att lagra en särskild dimensionsmedlem (till exempel Okänd). Den här metoden kräver en senare uppdatering för att korrekt tilldela värdet för den sanna dimensionens nyckel när det är känt.

En annan metod, som är relevant när det finns förtroende för att den naturliga nyckeln är giltig, är att infoga en ny dimensionsmedlem och sedan lagra dess surrogatnyckelvärde. Mer information finns i Härledda dimensionsmedlemmar senare i det här avsnittet.

Följande diagram visar logiken som används för att bearbeta en faktatabell.

När det är möjligt bör en faktatabell läsas in stegvis, vilket innebär att nya fakta identifieras och infogas. En inkrementell belastningsstrategi är mer skalbar och minskar arbetsbelastningen för både källsystemen och målsystemen.

Förhoppningsvis kan du effektivt identifiera nya fakta genom att förlita dig på källsystemidentifierare eller tidsstämplar. När ett källsystem till exempel på ett tillförlitligt sätt registrerar försäljningsorder som är i följd kan du lagra det senaste försäljningsordernumret som hämtats (kallas högvattenstämpeln). Nästa process kan använda det försäljningsordernumret för att hämta nyskapade försäljningsorder och återigen lagra det senaste försäljningsordernumret som hämtats för användning i nästa process. Det kan också vara möjligt att en kolumn för att skapa datum kan användas för att på ett tillförlitligt sätt identifiera nya beställningar.

Om du inte kan förlita dig på källsystemdata för att effektivt identifiera nya fakta kanske du kan förlita dig på en kapacitet i källsystemet för att utföra en inkrementell belastning. Sql Server och Azure SQL Managed Instance har till exempel en funktion som kallas för CDC (Change Data Capture), som kan spåra ändringar i varje rad i en tabell. Dessutom har SQL Server, Azure SQL Managed Instance och Azure SQL Database en funktion som kallas ändringsspårning, som kan identifiera rader som har ändrats. När det är aktiverat kan det hjälpa dig att effektivt identifiera nya eller ändrade data i valfri databastabell. Du kanske också kan lägga till utlösare i relationstabeller som lagrar nycklar för infogade, uppdaterade eller borttagna tabellposter.

Slutligen kanske du kan korrelera källdata till faktatabellen med hjälp av attribut. Till exempel försäljningsordernummer och försäljningsorderradnummer. För stora faktatabeller kan det dock vara en mycket dyr åtgärd att identifiera nya, ändrade eller borttagna fakta. Det kan också vara problematiskt när källsystemet arkiverar driftdata.

Härledda dimensionsmedlemmar

När en faktainläsningsprocess infogar en ny dimensionsmedlem kallas den för en antagen medlem. När till exempel en hotellgäst checkar in uppmanas de att gå med i hotellkedjan som en lojalitetsmedlem. Ett medlemsnummer utfärdas omedelbart, men informationen om gästen kanske inte följer förrän pappersarbetet skickas in av gästen (om någonsin).

Allt som är känt om dimensionsmedlemmen är dess naturliga nyckel. Faktainläsningsprocessen måste skapa en ny dimensionsmedlem med hjälp av okända attributvärden. Viktigt är att det måste ange granskningsattributet IsInferredMember till .TRUE På så sätt kan dimensionens inläsningsprocess göra nödvändiga uppdateringar av dimensionsraden när den sena ankommande informationen hämtas. Mer information finns i Hantera historiska ändringar i den här artikeln.

Faktauppdateringar eller borttagningar

Du kan behöva uppdatera eller ta bort faktadata. Till exempel när en försäljningsorder avbryts eller en orderkvantitet ändras. Som vi beskrev tidigare för att läsa in faktatabeller måste du effektivt identifiera ändringar och utföra lämpliga ändringar av faktadata. I det här exemplet för den avbrutna ordern ändras förmodligen försäljningsorderstatusen från Öppna till Avbruten. Den ändringen skulle kräva en uppdatering av faktadata och inte borttagning av en rad. För kvantitetsändringen skulle en uppdatering av måttet för faktaradkvantitet vara nödvändig. Den här strategin för att använda mjuka borttagningar bevarar historiken. En mjuk borttagning markerar en rad som inte längre aktiv eller giltig, och alla rapporteringsfrågor och Power BI-semantiska modeller bör filtrera bort poster som är mjuka borttagningar.

När du förväntar dig faktauppdateringar eller borttagningar bör du inkludera attribut (t.ex. ett försäljningsordernummer och dess försäljningsorderradsnummer) i faktatabellen för att identifiera de faktarader som ska ändras. Se till att indexera dessa kolumner för att stödja effektiva ändringsåtgärder.

Slutligen, om faktadata infogades med hjälp av en särskild dimensionsmedlem (till exempel Okänd), måste du köra en periodisk process som hämtar aktuella källdata för sådana faktarader och uppdaterar dimensionsnycklar till giltiga värden.

Relaterat innehåll

Mer information om hur du läser in data i ett infrastrukturlager finns i:

• Mata in data i informationslagret
• Mata in data i ditt lager med hjälp av datapipelines
• Mata in data i ditt lager med copy-instruktionen
• Mata in data i ditt lager med Hjälp av Transact-SQL
• Självstudie: Konfigurera dbt för Infrastrukturlager