Miért használjunk mesterséges kulcsot a dátum dimenzióban?

A minap egy adattárház adatmodelljét kellett megterveznem, és ennek kapcsán kaptam egy kérdést: Miért használjunk mesterséges kulcsokat a dátum dimenzióban? Mielőtt azonban megválaszolnám a kérdést, ismerje meg a mesterséges kulcsok szerepét az adattárházban

Mesterséges kulcs

Az adattárház betöltése közben a forrásrendszerekből jövő természetes kulcsokat (számlaszám, cikkszám, stb) lecseréljük egy jelentés nélküli integer típusú mesterséges kulcsra (Surrogate key), és utána a mutatószámokat tartalmazó ténytáblákat és az őket leíró dimenziótáblákat ezzel a kulccsal kötjük össze. Miért tesszük mindezt? Jellemzően azért, hogy

• Elszakadjunk a forrásrendszerek kódjaitól, így egy forrásrendszer csere miatt nem kell alkalmazkodnunk a „régi" kódokhoz, vagy több forrásrendszer esetén nem kell gondolkodnunk azon, hogy melyik forrásrendszer vevőkódjait használjuk „etalonként" az adattárházban. Helyette minden beérkező vevőnek adunk mi egy saját belső mesterséges kódot.
• „ismeretlen" dimenzióelemeket vehessünk fel olyan ténytábla sorokhoz, amelyekben nincs kitöltve például a cikkszám, de az összforgalom megállapításához szükségünk van ezekre az úgynevezett árva (cikkhez nem kapcsolódó) sorokra is
• Hatékonysági okokból: A mesterséges kulcs egy egész szám ami, kevesebb helyet foglal, hatékonyabban indexelhető, mint egy szöveges mező
• De legfőképpen azért, hogy le tudjuk követni a dimenzióelemek változását. (Ha Gipsz Jakabnak, mint ügyfélnek megváltozik a családi állapota, akkor az adattárházból ki tudjuk mutatni azt, hogy mennyi volt Gipsz Jakab forgalma míg nőtlen volt és mennyi miután megnősült. Ehhez azonban Gipsz Jakabnak két állapotát (verzióját) kell tárolnunk (egy nőst és egy nőtlent) és ezt csak úgy tudjuk megoldani, ha Gipsz Jakab kap egy új azonosítót (vevőkódot) miután megnősült

De mi a helyzet a dátum dimenzióval?

A dátum dimenzió esetén nem kell attól tartanunk, hogy egy új forrásrendszer bevezetésekor újfajta dátumkódokat fogunk kapni, vagy egy másik forrásrendszer bevonásával teljesen eltérő dátumokkal kell majd dolgoznunk az adattárházban. De attól sem kell tartanunk, hogy egy adott nap, mondjuk január 10 egyszer csak átkerül február hónap alá és nekünk ki kell tudnunk mutatni az értékesítési forgalmat az adattárházból februárban, úgy hogy egyszer vegye bele január 10-ét, egyszer ne. (Ahogy Gipsz Jakab esetén ki kellett mutatni, hogy mennyi forgalmat generált míg nem volt házas és mennyit miután megházasodott.)

Első ránézésre tehát teljesen felesleges az adattárházban a dátum dimenzió kulcsát, a dátumot kicserélni egy jelentés nélküli egész számra. Sőt, a mesterséges kulcsok használata még megnehezíti az adattárház lekérdezését is, hiszen ha le akarom válogatni egy konkrét hónap forgalmát, akkor először ki kell keresnem a dimenziótáblából, hogy a hónap első illetve utolsó napjának mi a mesterséges kulcsa és ezután erre kéne szűrnünk a ténytáblában. Mutatom:

Select * from ténytábla where DatumKulcs between 131 and 160

Mennyivel egyszerűbb lenne a

Select * from ténytábla where DatumKulcs between '2008-01-01' and '2008-01-31'

Mindezek ellenére mégis azt mondom, hogy

Tegyünk mesterséges kulcsot az adattárház dátum dimenziójába

Az alábbiakban összeszedtem, hogy miért

• Hatékonyság: Hatékonyabban kezeli az SQL Server az egész számokat, mint a dátumokat (pl.: indexelés, helytakarékosság, joinolás, partícionálás...)
• A dátum dimenzióban is lehetnek olyan elemek, amelyek a naptárban nem szerepelnek. 13. hónap például nincs a normál naptárban, és ha a 13. hónapot szerepeltetni akarjuk az adattárházban, akkor bizony el kell szakadnunk a Gergely naptár kötöttségeitől, és saját naptárat kell kialakítanunk
• lehetnek olyan események, amelyekről valamilyen oknál fogva - rendszerint a forrásrendszer hibás működése miatt - nem tudjuk megmondani, hogy mikor történtek. Ha ezeket is szerepeltetni akarjuk az adattárházban, akkor be kell töltenünk őket egy ismeretlen dátumra (csakhogy ismeretlen dátum szintén nem létezik a naptárban)
• Az SQL Server 2005 Datetime adattípusa 1753. január 1-től indul, azaz korábbi dátumokat nem tudunk datetime adattípusban szerepeltetni. Ha tehát 1753 előtti dátumokat akarunk feldolgozni, akkor muszáj elszakadnunk a datetime adattípustól (SQL 2008-ban már van olyan dátum adattípus, ahol ez a korlát megszűnt)

Összefoglalva: használjunk mesterséges kulcsot a dátum dimenzió dátum kulcsa helyett. Elsősorban azért, hogy be tudjuk tölteni az adattárházba azokat a rekordokat is, amelyeknek nem tudjuk a dátumát, másodsorban pedig azért, mert hatékonyabban tárolja, hatékonyabban dolgozik az SQL szerver egész számokkal, mint dátumokkal.