Surrogate key a jeho role v datovém skladu

Poslední aktualizace:

Surrogate key nebo chcete-li umělý klíč je termín, který se používá zejména v datových skladech. V produkčních databázích zdrojových systémů se s tímto termínem nemusíme vůbec setkat jednoduše proto, že umělé klíče není potřeba generovat. Vystačíme si tam s přirozenými primárními klíči. U datových skladů je velmi žádoucí vytváření umělých klíčů z mnoha důvodů o kterých se dočtete dále.

Co je to Surrogate key

Umělé klíče jsou primární a cizí klíče, které jsou generovány až na úrovni datového skladu místo toho, aby se převzaly ze zdrojového systému (business key). Takovým prímárním (business) klíčem na zdroji může být např. číslo zákazníka, číslo objednávky, produktu atp. Možností jak vygenerovat surrogate key je několik:

  • IDENTITY – možnost kdy je klíč generován automaticky, jde o číselný formát začínající od nuly. Datovými typy může být INTEGER (TINYINT, SMALLINT, INT, BIGINT) nebo NUMERIC, případně DECIMAL
  • SEQUENCE – další možností jak generovat primární – surrogate key je přes vytvoření objektu typu sekvence v databázi. Stejně jako IDENTITY můžete použít datový typ typu INT, NUMERIC nebo DECIMAL. Objekt typu sekvence může poskytnout pořadová čísla na vyžádání
  • NONSEQUENTIAL GUID – další možnost je vygenerovat si jedinečný identifikátor typu nesekvenčního GUIDu datového typu UNIQUEIDENTIFIER pomocí funkce NEWID()
  • SEQUENTIAL GUID – sekvenční GUID lze vygenerovat pomocí T-SQL funkce NEWSEQUENTIALID()
  • Vlastní řešení – poslední možností je použít nějakého generátoru primárních klíčů případně vytvořit vlastní logiku

Proč je výhodné používat surrogate keys v datovém skladu?

1) Umělé klíče jsou nezávislé na změnách ve zdrojových systémech

Při budování datového skladu je dobré mít na paměti, že jde o dlouhodobou a strategickou záležitost, která může a dost možná bude přesahovat životnost nějakého zdrojového systému. Stejně tak není dobré vytvářet mezi datovým skladem a primárním systémem závislosti.

2) Surrogate keys mají menší nároky na storage

Co se týče typu umělého klíče, využívám většinou IDENTITY A SEQUENCE. Osobně nejsem zastáncem GUID surrogate keys z výkonostních důvodů:

  • UNIQUEIDENTIFIER = 16 bytes
  • INT = 4 bytes
  • BIGINT = 8 bytes

3) Optimalizace joinů pomocí surrogate keys

Pro datové sklady je klíčové, aby byla jeho architektura optimalizována hlavně pro dotazování.  K tomu využíváme různá techniky jako typ schématu (star schema, snowflake schema), struktura faktových a dimenzních tabulek apod. Jedním z vlivů, které působí na výkonnost datového skladu je i struktura referenční integrity – mj. primárních a cizích klíčů. Surrogate keys typu IDENTITY a SEQUENCE jsou obzvláště výhodné.

4) Možnost využívat SCD (Slowly changing dimensions)

Pokud využijeme jako primární klíč v datovém skladu natural nebo business key z primárního systému komplikujeme si možnost zavedení historizace záznamů = aplikace SCD (typ 2 a více) tzv. slowly changing dimension. Principem historizace je vytvoření nového záznamu v datovém skladu v případě, že dojde ke změně nějakého atributu na zdrojovém systému.

5) Neznámá hodnota a Surrogate key

V primárním systému pravděpodobně najdete neznámou hodnotu jako “NULL. V precizně udělaných datových skladech a datamartech by se NULL neměla vyskytovat. Přes Surrogate klíče je vhodné NULL hodnoty nahradit. Využítí umělých klíčů v datovém skladu má v tomto směru další výhodu, NULL hodnoty můžete dál rozlišovat na:

  • Prázdná hodnota, která je chybovým stavem (-1)
  • Prázdná hodnota, která je povolenou hodnotou (-2)

Zajímavý text na téma Surrogate klíčů můžete najít na webu Kimball Group přímo od Ralpha Kimballa zde

Rate this article

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Category: MS SQL Server Tags: ,

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.