Sdílet prostřednictvím

Dimenzionální modelování v Microsoft Fabric Warehouse: Načtení tabulek

  • Článek

Platí pro: Koncový bod sql Analytics a sklad v Microsoft Fabric

Poznámka:

Tento článek je součástí řady dimenzionálních modelů článků. Tato série se zaměřuje na pokyny a osvědčené postupy návrhu související s dimenzionálním modelováním ve službě Microsoft Fabric Warehouse.

Tento článek obsahuje pokyny a osvědčené postupy pro načítání tabulek dimenzí a faktů v dimenzionálním modelu. Poskytuje praktické pokyny pro Warehouse v Microsoft Fabric, což je prostředí, které podporuje mnoho funkcí T-SQL, jako je vytváření tabulek a správa dat v tabulkách. Takže máte úplnou kontrolu nad vytvářením dimenzionálních tabulek modelu a jejich načítáním s daty.

Poznámka:

V tomto článku se termín datový sklad týká podnikového datového skladu, který poskytuje komplexní integraci důležitých dat v rámci organizace. Naproti tomu samostatný termínový sklad odkazuje na sklad Infrastruktury, což je nabídka relační databáze SaaS (software jako služba), kterou můžete použít k implementaci datového skladu. Pro přehlednost se v tomto článku uvádí jako Fabric Warehouse.

Tip

Pokud jste nezkušení s dimenzionálním modelováním, zvažte tuto řadu článků, které jste v prvním kroku. Účelem není poskytnout kompletní diskuzi o návrhu dimenzionálního modelování. Další informace najdete přímo na široce přijímaný publikovaný obsah, například The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3. vydání, 2013) od Ralph Kimball a dalších.

Načtení dimenzionálního modelu

Načítání dimenzionálního modelu zahrnuje pravidelné spouštění procesu extrakce, transformace a načítání (ETL). Proces ETL orchestruje spouštění dalších procesů, které se obecně týkají přípravných zdrojových dat, synchronizace dat dimenzí, vkládání řádků do tabulek faktů a zaznamenávání dat a chyb auditování.

V případě řešení Fabric Warehouse můžete pomocí služby Data Factory vyvíjet a spouštět proces ETL. Proces může zpracovávat, transformovat a načítat zdrojová data do tabulek dimenzionálního modelu.

Konkrétně můžete:

  • Pomocí datových kanálů můžete vytvářet pracovní postupy pro orchestraci procesu ETL. Datové kanály můžou spouštět skripty SQL, uložené procedury a další.
  • Pomocí toků dat můžete vyvíjet logiku s minimem kódu pro příjem dat ze stovek zdrojů dat. Toky dat podporují kombinování dat z více zdrojů, transformaci dat a jejich následné načítání do cíle, jako je tabulka dimenzionálního modelu. Toky dat se vytvářejí pomocí známého prostředí Power Query , které je dnes dostupné v mnoha produktech Microsoftu, včetně Microsoft Excelu a Power BI Desktopu.

Poznámka:

Vývoj ETL může být složitý a vývoj může být náročný. Odhaduje se, že 60–80 % úsilí o vývoj datového skladu je vyhrazené pro proces ETL.

Orchestrace

Obecný pracovní postup procesu ETL je následující:

  1. Volitelně můžete načíst pracovní tabulky.
  2. Zpracování tabulek dimenzí
  3. Zpracování tabulek faktů
  4. Volitelně můžete provádět úlohy následného zpracování, jako je aktivace aktualizace závislého obsahu infrastruktury (například sémantický model).

Diagram znázorňuje čtyři kroky procesu ETL, jak je popsáno v předchozím odstavci.

Tabulky dimenzí by se měly zpracovat jako první, aby se zajistilo, že budou ukládat všechny členy dimenze, včetně těch, které byly přidány do zdrojových systémů od posledního procesu ETL. Pokud existují závislosti mezi dimenzemi, stejně jako u dimenzí outriggeru, měly by se tabulky dimenzí zpracovávat v pořadí závislostí. Například zeměpisná dimenze používaná dimenzí zákazníka a dimenze dodavatele by se měla zpracovat před ostatními dvěma dimenzemi.

Tabulky faktů je možné zpracovat po zpracování všech tabulek dimenzí.

Při zpracování všech dimenzionálních tabulek modelu můžete aktivovat aktualizaci závislých sémantických modelů. Je také vhodné poslat příslušnému personálu oznámení, aby je informovali o výsledku procesu ETL.

Data fáze

Přípravná zdrojová data můžou pomoct podporovat požadavky na načítání a transformaci dat. Zahrnuje extrakci zdrojových systémových dat a jejich načtení do pracovních tabulek, které vytvoříte pro podporu procesu ETL. Doporučujeme provést fázi zdrojových dat, protože můžou:

  • Minimalizujte dopad na provozní systémy.
  • Slouží k usnadnění a optimalizaci zpracování ETL.
  • Poskytněte možnost restartovat proces ETL, aniž byste museli znovu načítat data ze zdrojových systémů.

Data v pracovních tabulkách by neměla být nikdy zpřístupněna firemním uživatelům. Je relevantní pouze pro proces ETL.

Poznámka:

Když jsou vaše data uložená v Objektu Fabric Lakehouse, nemusí být nutné svá data ve skladu rozfázovat. Pokud implementuje architekturu medailonu, můžete zdroj dat získat z bronzové, stříbrné nebo zlaté vrstvy.

Doporučujeme vytvořit schéma ve skladu, pravděpodobně pojmenované staging. Pracovní tabulky by se měly co nejvíce podobat zdrojovým tabulkám z hlediska názvů sloupců a datových typů. Obsah každé tabulky by měl být odebrán na začátku procesu ETL. Mějte ale na paměti, že tabulky Fabric Warehouse nelze zkrátit. Místo toho můžete před načtením s daty odstranit a znovu vytvořit každou pracovní tabulku.

Alternativy virtualizace dat můžete zvážit také jako součást přípravné strategie. Můžete použít:

Transformovat data

Struktura zdrojových dat se nemusí podobat cílovým strukturám tabulek dimenzionálního modelu. Proces ETL tedy musí změnit tvar zdrojových dat tak, aby odpovídal struktuře tabulek dimenzionálního modelu.

Datový sklad musí také dodávat vyčištěná a vyhovující data, takže zdrojová data může být potřeba transformovat, aby byla zajištěna kvalita a konzistence.

Poznámka:

Koncept uvolňování paměti, uvolňování paměti se jistě vztahuje na datové sklady – proto se vyhněte načítání dat uvolňování paměti (nízké kvality) do tabulek dimenzionálního modelu.

Tady je několik transformací, které by proces ETL mohl provést.

  • Kombinování dat: Data z různých zdrojů je možné integrovat (sloučit) na základě odpovídajících klíčů. Například údaje o produktech se ukládají v různých systémech (jako je výroba a marketing), ale všechny používají společnou jednotku uchovávání zásob (SKU). Data lze také připojit, když sdílí společnou strukturu. Například prodejní data jsou uložená v několika systémech. Sjednocení prodeje z každého systému může vytvořit nadmnožinu všech prodejních dat.
  • Převod datových typů: Datové typy lze převést na datové typy definované v tabulkách dimenzionálního modelu.
  • Výpočty: Výpočty lze provést za účelem vytvoření hodnot pro tabulky dimenzionálního modelu. Například pro tabulku dimenzí zaměstnance můžete zřetězení křestní jména a příjmení, aby se vytvořilo celé jméno. Jako další příklad tabulky faktů prodeje můžete vypočítat hrubé výnosy z prodeje, což je součin jednotkové ceny a množství.
  • Detekce a správa historických změn: Změny lze detekovat a odpovídajícím způsobem ukládat v tabulkách dimenzí. Další informace naleznete v tématu Správa historických změn dále v tomto článku.
  • Agregovaná data: Agregace se dá použít ke snížení rozměrnosti tabulky faktů nebo ke zvýšení členitosti faktů. Například tabulka faktů prodeje nemusí ukládat čísla prodejních objednávek. Proto agregovaný výsledek, který seskupuje podle všech klíčů dimenzí, lze použít k uložení dat tabulky faktů.

Načtení dat

Tabulky ve službě Fabric Warehouse můžete načíst pomocí následujících možností příjmu dat.

  • COPY INTO (T-SQL):Tato možnost je užitečná, když zdrojová data tvoří soubory Parquet nebo CSV uložené v externím účtu úložiště Azure, jako je ADLS Gen2 nebo Azure Blob Storage.
  • Datové kanály: Kromě orchestrace procesu ETL můžou datové kanály zahrnovat aktivity, které spouštějí příkazy T-SQL, provádějí vyhledávání nebo kopírují data ze zdroje dat do cíle.
  • Toky dat: Jako alternativu k datovým kanálům poskytují toky dat prostředí bez kódu pro transformaci a vyčištění dat.
  • Příjem dat mezi sklady: Pokud jsou data uložená ve stejném pracovním prostoru, příjem dat mezi sklady umožňuje spojit různé tabulky skladu nebo tabulky lakehouse. Podporuje příkazy T-SQL, jako je INSERT…SELECT, SELECT INTOa CREATE TABLE AS SELECT (CTAS). Tyto příkazy jsou užitečné zejména v případech, kdy chcete transformovat a načítat data z pracovních tabulek v rámci stejného pracovního prostoru. Jedná se také o operace založené na sadě, což je pravděpodobně nejúčinnější a nejrychlejší způsob, jak načíst tabulky dimenzionálních modelů.

Tip

Úplné vysvětlení těchto možností příjmu dat včetně osvědčených postupů najdete v tématu Ingestování dat do skladu.

Protokolování

Procesy ETL obvykle vyžadují vyhrazené monitorování a údržbu. Z těchto důvodů doporučujeme protokolovat výsledky procesu ETL do nedimenzionálních tabulek modelu ve vašem skladu. Pro každý proces ETL byste měli vygenerovat jedinečné ID a použít ho k protokolování podrobností o každé operaci.

Zvažte protokolování:

  • Proces ETL:
    • Jedinečné ID pro každé spuštění ETL
    • Počáteční a koncový čas
    • Stav (úspěch nebo selhání)
    • Byly zjištěny všechny chyby.
  • Každá pracovní a dimenzionální tabulka modelu:
    • Počáteční a koncový čas
    • Stav (úspěch nebo selhání)
    • Vložené, aktualizované a odstraněné řádky
    • Konečný počet řádků tabulky
    • Byly zjištěny všechny chyby.
  • Další operace:
    • Počáteční a koncový čas sémantických operací aktualizace modelu

Tip

Můžete vytvořit sémantický model vyhrazený pro monitorování a analýzu procesů ETL. Doba trvání procesu vám může pomoct identifikovat kritické body, které můžou těžit z kontroly a optimalizace. Počet řádků vám umožní pochopit velikost přírůstkového zatížení při každém spuštění ETL a také pomoct předpovědět budoucí velikost datového skladu (a kdy vertikálně navýšit kapacitu prostředků infrastruktury, pokud je to vhodné).

Zpracování tabulek dimenzí

Zpracování tabulky dimenzí zahrnuje synchronizaci dat datového skladu se zdrojovými systémy. Zdrojová data se nejprve transformují a připraví na načtení do tabulky dimenzí. Tato data se pak shodují s existujícími daty tabulky dimenzí tím, že se připojí k obchodním klíčům. Potom je možné určit, jestli zdrojová data představují nová nebo upravená data. Pokud tabulka dimenzí používá pomalu se měnící typ dimenze (SCD) typu 1, změny se provádějí aktualizací existujících řádků tabulky dimenzí. Když tabulka použije změny typu SCD 2, platnost stávající verze vyprší a vloží se nová verze.

Následující diagram znázorňuje logiku používanou ke zpracování tabulky dimenzí.

Diagram znázorňuje tok, který popisuje, jak se nové a změněné zdrojové řádky načítají do tabulky dimenzí, jak je popsáno v následujícím odstavci.

Zvažte proces Product tabulky dimenzí.

  • Při přidání nových produktů do zdrojového systému se řádky vloží do Product tabulky dimenzí.
  • Při úpravě produktů se existující řádky v tabulce dimenzí aktualizují nebo vloží.
    • Když se použije SCD typu 1, aktualizace se provádějí na existujících řádcích.
    • Když se použije SCD typu 2, aktualizace vyprší platnost aktuálních verzí řádků a nové řádky, které představují aktuální verzi, se vloží.
    • Pokud se použije scD typu 3, dojde k procesu podobnému typu SCD 1, který aktualizuje existující řádky bez vložení nových řádků.

Náhradní klíče

Doporučujeme, aby každá tabulka dimenzí měla náhradní klíč, který by měl používat nejmenší možný datový typ celé číslo. V prostředích založených na SQL Serveru, která se obvykle provádí vytvořením sloupce identity, ale tato funkce není ve službě Fabric Warehouse podporovaná. Místo toho budete muset použít techniku alternativního řešení, která generuje jedinečné identifikátory.

Důležité

Pokud tabulka dimenzí obsahuje automaticky generované náhradní klíče, nikdy byste neměli provádět zkrácení a úplné opětovné načtení. Důvodem je to, že by zneplatnily data načtená do tabulek faktů, které používají dimenzi. Pokud tabulka dimenzí podporuje změny typu SCD 2 , nemusí být možné znovu vygenerovat historické verze.

Správa historických změn

Pokud tabulka dimenzí musí uchovávat historickou změnu, budete muset implementovat pomalu se měnící dimenzi (SCD).

Poznámka:

Pokud je řádek tabulky dimenzí odvozený člen (vložený procesem načítání faktů), měli byste zacházet se všemi změnami jako s pozdními příchozími podrobnostmi dimenze místo změny SCD. V tomto případě by se měly aktualizovat všechny změněné atributy a sloupec příznaku odvozeného člena nastavený na FALSE.

Je možné, že dimenze může podporovat změny typu SCD typu 1 nebo SCD typu 2.

SCD – typ 1

Při zjištění změn typu 1 typu SCD použijte následující logiku.

  1. Aktualizujte všechny změněné atributy.
  2. Pokud tabulka obsahuje datum poslední změny a datum poslední změny podle sloupců, nastavte aktuální datum a proces, který provedl změny.

SCD – typ 2

Při zjištění změn typu 2 SCD použijte následující logiku.

  1. Platnost aktuální verze vyprší nastavením sloupce koncového data platnosti na datum zpracování ETL (nebo vhodné časové razítko ve zdrojovém systému) a aktuální příznak na FALSE.
  2. Pokud tabulka obsahuje datum poslední změny a datum poslední změny podle sloupců, nastavte aktuální datum a proces, který provedl změny.
  3. Vložte nové členy, kteří mají sloupec platnosti počátečního data nastavenou na hodnotu sloupce platnosti koncového data (používá se k aktualizaci předchozí verze) a má příznak aktuální verze nastavený na TRUEhodnotu .
  4. Pokud tabulka obsahuje datum vytvoření a vytvoří se podle sloupců, nastavte aktuální datum a proces vložení.

SCD – typ 3

Při zjištění změn typu 3 typu SCD aktualizujte atributy pomocí podobné logiky pro zpracování typu SCD typu 1.

Odstranění členů dimenze

Dbejte na to, jestli zdrojová data indikují, že se členové dimenze odstranili (buď proto, že nejsou načteni ze zdrojového systému, nebo jsou označeny příznakem jako odstraněné). Odstranění byste neměli synchronizovat s tabulkou dimenzí, pokud nebyly členy dimenze vytvořeny omylem a neexistují žádné záznamy faktů, které s nimi souvisejí.

Vhodným způsobem, jak zpracovat odstranění zdroje, je zaznamenat je jako obnovitelné odstranění. Obnovitelné odstranění označí člen dimenze jako již aktivní nebo platný. Pro podporu tohoto případu by tabulka dimenzí měla obsahovat logický atribut s bitovým datovým typem, například IsDeleted. Aktualizujte tento sloupec pro všechny odstraněné členy dimenze na TRUE (1). Aktuální, nejnovější verze členu dimenze může být podobně označena logickou (bitovou) hodnotou ve IsCurrent sloupcích.IsActive Všechny dotazy generování sestav a sémantické modely Power BI by měly vyfiltrovat záznamy, které jsou obnovitelné odstranění.

Dimenze data

Dimenze kalendáře a času jsou zvláštní případy, protože obvykle nemají zdrojová data. Místo toho se generují pomocí pevné logiky.

Na začátku každého nového roku byste měli načíst tabulku dimenzí kalendářních dat, aby se její řádky rozšířily o určitý počet let dopředu. Mohou existovat další obchodní data, například data fiskálního roku, svátky, čísla týdnů, která se mají pravidelně aktualizovat.

Pokud tabulka dimenzí kalendářních dat obsahuje relativní atributy posunu, musí být proces ETL spuštěn denně, aby se aktualizovaly hodnoty atributu posunu na základě aktuálního data (dnes).

Doporučujeme, aby logika pro rozšíření nebo aktualizaci tabulky dimenzí kalendářních dat byla zapsána v T-SQL a zapouzdřena v uložené proceduře.

Zpracování tabulek faktů

Zpracování tabulky faktů zahrnuje synchronizaci dat datového skladu se zdrojovými systémovými fakty. Zdrojová data se nejprve transformují a připraví na načtení do tabulky faktů. Pro každý klíč dimenze pak vyhledávání určuje hodnotu náhradního klíče, která se má uložit do řádku faktů. Pokud dimenze podporuje scD typ 2, náhradní klíč pro aktuální verzi člen dimenze by se měl načíst.

Poznámka:

Náhradní klíč je obvykle možné vypočítat pro dimenze data a času, protože by měly používat YYYYMMDD nebo HHMM formátovat. Další informace najdete v tématu Kalendář a čas.

Pokud vyhledávání klíče dimenze selže, může to značit problém s integritou zdrojového systému. V tomto případě musí být řádek faktů stále vložen do tabulky faktů. Platný klíč dimenze musí být stále uložen. Jedním z přístupů je uložení speciálního členu dimenze (například Neznámý). Tento přístup vyžaduje pozdější aktualizaci, aby správně přiřadil hodnotu klíče skutečné dimenze, pokud je to známo.

Důležité

Protože Fabric Warehouse nevynucuje cizí klíče, je důležité, aby proces ETL při načítání dat do tabulek faktů zkontroloval integritu.

Dalším přístupem, který je relevantní v případě, že existuje jistota, že je přirozený klíč platný, je vložit nový člen dimenze a pak uložit jeho náhradní hodnotu klíče. Další informace naleznete v části Odvozené členy dimenze dále v této části.

Následující diagram znázorňuje logiku použitou ke zpracování tabulky faktů.

Diagram znázorňuje tok, který popisuje, jak se nové zdrojové řádky načítají do tabulky faktů, jak je popsáno v předchozích odstavcích.

Kdykoli je to možné, měla by se tabulka faktů načíst přírůstkově, což znamená, že se zjistí a vloží nová fakta. Strategie přírůstkového zatížení je škálovatelnější a snižuje zatížení zdrojových systémů i cílových systémů.

Důležité

Zvláště u velké tabulky faktů by měla být poslední možnost zkrátit a znovu načíst tabulku faktů. Tento přístup je nákladný z hlediska času zpracování, výpočetních prostředků a možného přerušení zdrojových systémů. Zahrnuje také složitost, když dimenze tabulky faktů používají SCD typ 2. Je to proto, že v době platnosti verzí člena dimenze bude potřeba provést vyhledávání klíčů dimenzí.

Doufejme, že můžete efektivně detekovat nová fakta tím, že se spoléháte na identifikátory zdrojového systému nebo časové razítko. Pokud například zdrojový systém spolehlivě zaznamenává prodejní objednávky, které jsou v sekvenci, můžete uložit nejnovější načtené číslo prodejní objednávky (označované jako horní mez). V dalším procesu můžete pomocí čísla prodejní objednávky načíst nově vytvořené prodejní objednávky a znovu uložit nejnovější číslo prodejní objednávky načtené pro použití dalším procesem. Je také možné, že sloupec data vytvoření se dá použít k spolehlivému zjištění nových objednávek.

Pokud se nemůžete spoléhat na zdrojová systémová data k efektivnímu zjišťování nových faktů, můžete se spolehnout na schopnost zdrojového systému provádět přírůstkové zatížení. Například SQL Server a Azure SQL Managed Instance mají funkci označovanou jako change data capture (CDC), která může sledovat změny jednotlivých řádků v tabulce. SQL Server, Azure SQL Managed Instance a Azure SQL Database mají také funkci označovanou jako sledování změn, která dokáže identifikovat řádky, které se změnily. Pokud je tato možnost povolená, může vám pomoct efektivně zjišťovat nová nebo změněná data v libovolné tabulce databáze. Do relačních tabulek, které ukládají klíče vložených, aktualizovaných nebo odstraněných záznamů tabulky, můžete také přidat triggery.

Nakonec můžete být schopni korelovat zdrojová data s tabulkou faktů pomocí atributů. Například číslo prodejní objednávky a číslo řádku prodejní objednávky. U velkých tabulek faktů ale může být velmi náročná operace, která detekuje nová, změněná nebo odstraněná fakta. Může být také problematické, když zdrojový systém archivuje provozní data.

Odvozené členy dimenze

Když proces načtení faktů vloží nový člen dimenze, označuje se jako odvozený člen. Například když se hotelový host přihlásí, bude požádán, aby se připojil k hotelovému řetězci jako členovi věrnosti. Číslo členství je vydáno okamžitě, ale podrobnosti o hostu nemusí následovat, dokud host neodesílají papírování (pokud někdy).

Vše, co je známo o členu dimenze, je jeho přirozeným klíčem. Proces načtení faktů musí vytvořit nový člen dimenze pomocí neznámých hodnot atributů. Důležité je, že musí nastavit IsInferredMember atribut auditu na TRUEhodnotu . Když se tak zpozdí příchozí podrobnosti, proces načtení dimenze může provést potřebné aktualizace řádku dimenze. Další informace naleznete v tématu Správa historických změn v tomto článku.

Aktualizace nebo odstranění faktů

Možná budete muset aktualizovat nebo odstranit data faktů. Například když se prodejní objednávka zruší nebo se změní množství objednávky. Jak je popsáno dříve při načítání tabulek faktů, je potřeba efektivně detekovat změny a provádět příslušné úpravy dat faktů. V tomto příkladu zrušené objednávky by se stav prodejní objednávky pravděpodobně změnil z možnosti Otevřít na Zrušeno. Tato změna by vyžadovala aktualizaci dat faktů, nikoli odstranění řádku. Pro změnu množství by byla nutná aktualizace míry množství řádku faktů. Tato strategie použití obnovitelného odstranění zachovává historii. Obnovitelné odstranění označí řádek jako neaktivní nebo platný a všechny dotazy na generování sestav a sémantické modely Power BI by měly vyfiltrovat záznamy, které jsou obnovitelné odstranění.

Pokud očekáváte aktualizace nebo odstranění faktů, měli byste do tabulky faktů zahrnout atributy (například číslo prodejní objednávky a číslo řádku prodejní objednávky), které vám pomůžou identifikovat řádky faktů, které chcete upravit. Nezapomeňte tyto sloupce indexovat, aby podporovaly efektivní operace úprav.

A konečně, pokud byla data faktů vložena pomocí speciálního členu dimenze (například Neznámý), budete muset spustit pravidelný proces, který načte aktuální zdrojová data pro takové řádky faktů a aktualizuje klíče dimenzí na platné hodnoty.

Související obsah

Další informace o načítání dat do skladu Fabric najdete v tématech: