Surrogate key nebo chcete-li umělý klíč je termín, který se používá zejména ve faktových a dimenzních tabulkách v datových skladech jako primární klíč nad kterým je postaven index a také cizí klíče.
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 (business keys typicky v podobě guidů). 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.
Surrogate key jako primární klíč v datovém skladu
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 autoincrementem, 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
Jednotlivé varianty pak performují různým způsobem – pokusil jsem se provést test výkonu generování primárních klíčů různými způsoby v článku o SEQUENCE.
Proč je výhodné používat surrogate keys v datovém skladu?
Umělé primární a cizí 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.
Surrogate keys mají menší nároky na storage než guidy
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
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é.
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.
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
