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.

Elválasztó

Már készül a következő cikk. Kérjen értesítést a megjelenéséről itt.

|

6 Hozzászólás

datumbol kepzett kulcs

Erre a problemara szoktak datumbol kepzett, olvashato kulcsot is hasznalni pl.: 2009-02-09 = 20090209 Persze az egesz csak akkor er barmit is ha nem kerulhet a rendszerbe 20090230 :) amire mar lattam peldat ilyen megoldasnal..

Származtatott vagy jelentés nélküli mesterséges kulcs?

Teljesen igazad van. Az igazság az, hogy szerettem volna erről írni, de elfelejtettem. Megteszem most:

Amikor az idő dimenzió mesterséges kulcsát állítjuk elő akkor dönthetünk, hogy sima, folyamatosan növő egész számot használunk mesterséges kulcsként (pl 1,2,3, stb), vagy a dátumból állítjuk elő azt. Ez utóbbi esetben a 2008 szilveszterének mesterséges kulcsa például így nézne ki: 20081231 .

Hosszan lehetne érvelni egyik vagy másik megoldás mellett. Mi mindig YYYYMMDD alakú mesterséges kulcsot használunk, de ennek a módszernek is vannak limitációi:

1. Elsősorban azért használunk YYYYMMDD alakú mesterséges kulcsot, hogy közvetlenül a ténytáblából tudjunk dátumtól dátumig szűrésekkel lekérdezni. Vannak azonban olyan mesterséges kulcsok, mint például az ismeretlen dátumhoz tartozó -1 es kulcs, amit nem tudunk dátummá konvertálni, így minden egyes dátum szűrés előtt ki kell zárni a mínusz egyes sorokat.

2. Ha az Analyis Services adatbányász motorjának idősoros előrejelző (Time Series algorithm) algoritmusát lefuttatjuk egy olyan ténytáblán amely YYYYMMDD alakú mesterséges kulcsot tartalmaz, akkor az adatbányász algoritmus bizony elő fog állítani értékeket (forecast-ot) olyan dátumokra is, amelyek nem is léteznek. 20090131 után előrejelez 20090132-re, 33-ra, … adatokat. (Így keletkezhetett az általad írt 20090230-as dátum is)

A jelentés nélküli mesterséges kulcsokkal az a legfőbb probléma, hogy a ténytábla lekérdezéséhez mindig be kell kötni az idő dimenziótáblát is. Nem tudjuk lekérdezni közvetlenül a ténytáblát, mert például a 156-os hónaphoz mindig ki kell keresni az idő dimenzióból, hogy az melyik év melyik hónapja. Ez nem csak lekérdezéskor szívás, hanem például partícionáláskor is.

Összefoglalva: Kipróbáltam már mind a két módszert és a jelentés nélküli mesterséges kulccsal épített adattárház fejlesztése, karbantartása, lekérdezése egy konstans szívást eredményezett. Az idő dimenzió állandó bekötése csak néhány másodpercet rabolt el, de tette mindezt több ezerszer… Ahogy Te is, úgy én is a jelentéssel bíró YYYYMMDD alakú mesterséges kulcsot javaslom.

Kössz a kommentet.

datumbol kepzett kulcs

Es mit gondolsz a '9999-12-31' ismeretlen datumkent valo hasznalatarol? Kezelheto datumkent, ugyanakkor kizart (oke, scifi tanszek adattar kivetelevel), hogy valos datumkent az adattarhazba kerulhessen.. Udv.: Zsolt

Valós dátum veszélye az ismeretlen dátumnál

Szia Zsolt,

Jó, ha az ismeretlen elem (cikk, dátum, vevő, …) mesterséges kulcsa ugyanaz minden dimenziótáblában. Így egységesen lehet őket kezelni, és nem kell lekérdezés közben gondolkodni, hogy melyik az ismeretlen elem.

Miért veszélyes, hogy az ismeretlen elemnek valós dátumkulcsa van (pl.: 9999-12-31)? Mondok egy példát. Holdbéli lesz, de remélem érzékeltetni fogja a lényeget. Tegyük fel az a feladatod, hogy mutasd ki, hogy hogyan oszlott meg az értékesítés volumene a hét napjai között. (hány darabot értékesítetek hétfőn, kedden, stb.) Mit fogsz csinálni? Leszűröd a természetes kulcsot egy datepart függvénnyel és arra aggregálsz. Csakhogy az ismeretlen elemnek (pl.: 9999-12-31) is lesz napja, így az ismereten elemhez tartozó forgalom hozzá fog adódni mondjuk a hétfői forgalmi adatokhoz. Ami nem igaz.

Valós dátum veszélye az ismeretlen dátumnál

Persze, ezert kell kiszurni az eredmenyek kozul, ahogy kiszurod gondolom akkor is, amikor mondjuk az ismeretlen datum kodja 1. Peldadnal maradva az olyan "igazan mesterseges" kulcsra mint az 1 a DATEPART legalabb dob egy hatast (legalabbis nagyon remelem,nem valid datum, most nincs keznel sql server h megnezzem), tehat biztonsagi ovkent felfoghato [kizart h hulyeseg kerul az eredmenyek koze hiszen eldobja magat a datumkent nem ertelmezheto erteken]. Ergo az eredmenybol mindenkeppen ki kell szornod ha a datumot hordozo mesterseges kulcson akarsz datum fuggvennyel dolgozni. Roviden: tovabbra sem erzem porblemasnak, nyilvan merlegelni kell az elonyoket es a hatranyokat. Mas: multkor kiment a fejembol, most megirom, az altlam emlitett 2007.12.35 ugy kerult az adott rendszerbe, hogy nem volt semmilyen datum ellenorzes annak a programnak a feluleten amibol az adatok jottek es raadasul a datumokat nem datum tipusban taroltak hanem integerben: '20071235'. [Adat profilozas 101et is tarthatnal szerintem, foleg ugyfel oldalon, nem lenne szabad addig meg csak adatarhazra sem gondolniuk amig nem tudjak milyen minosegu adataik vannak egyaltalan.] Udv.: Zsolt

Adatprofilozás 101

Szia,

Az első üzleti intelligencia projekt, amin még 1997-ben dolgoztam 11 hónapig állt, mert ennyi időre volt szüksége a megrendelőnek, hogy rendbe rakja a forrásrendszereit. (legalább annyira, hogy azok adatai be lehessen tölteni az üzleti intelligencia rendszerbe.)

A megrendelőknek tartott adattárház projektvezető képzésen erről beszéltem és elmondtam nekik, amit Te is mondasz: Az adattárház vagy üzleti intelligencia rendszer bevezetése előtt célszerű legalább annyit tenniük, hogy előzetesen felmérik a forrásrendszereket. (Át is adtam nekik egy kb. 60 kérdést tartalmazó interjú forgatókönyvet, és egy adatprofilozáshoz használt mintafájlt) Ha pedig a szállítóknak tartok majd ilyen tanfolyamot, akkor nekik meg fogom mutatni, hogy hogyan kell a forrásrendszereiket megprofilozni. Sajnos az a tapasztalatom, hogy sokszor ők sem csinálják meg. (Ahogy mi sem csináltuk meg 12 éve és elég borsos árat fizettünk érte…)

Üdv, Attila

Szóljon hozzá!

Szabály: Legyen kedves, segítõkész és vállalja a nevét.
A mező tartalma nem nyilvános.
  • A web és email címek automatikusan linkekké alakulnak.
  • Engedélyezett HTML elemek: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • A sorokat és bekezdéseket automatikusan felismeri a rendszer.
ANTI SPAM
A robot regisztrációk elkerülésére.
Image CAPTCHA
Figyeljen a kis és nagybetűk használatára