Hiányzó dimenzió elemek kezelése az adattárházban

Az adattárházak napi rendszeres feltöltése után ellenőrzéseket futtatok azért, hogy kiderítsem: lehet-e eltérés az adattárház és a forrásrendszerek adatai között. A minap ez az ellenőrző rutin küldött nekem egy e-mail-t, miben két fontos figyelmeztetés volt:

  1. Az adattárház és a forrásrendszer napi zárókészlete eltér
  2. A „JAV” vevőkód szerepel a Fact táblában, de a dimenzió táblában nem (készlet-tranzakciókban igen, de a vevőtörzsben nem)

A következő cikkben bemutatom, hogy milyen okok vezethetnek dimenzió elemek hiányára, hogyan lehet őket kiküszöbölni vagy automatikusan helyettesíteni, és milyen segítséget nyújt ehhez az SQL Server 2005 Integration Services (SSIS)

Kővári Attila 2006. október 1.

Mikor hiányzik egy dimenzió elem?

Egy dimenzió elem akkor hiányzik, ha a ténytáblában vannak olyan „árva” rekordok (orphan facts), amelyekhez nem kapcsolódnak dimenzió elemek. A példánál maradva a készlet-tranzakciókat tartalmazó fact táblában azok a sorok voltak árvák, amelyekben a „JAV” vevőkód szerepelt, mert a "JAV" vevőkód hiányzott a dimenzió táblából, és így nem tudott mihez kapcsolódni.

Miért baj, ha hiányzik egy dimenzióelem?

Mint azt a fenti példából is láthatjuk, a dimenzió elemek hiányának következtében az OLAP és/vagy az adattárház az eltér a forrásrendszertől. (Egy hierarchia mentén felösszegezve az adatokat (esetünkben a vevő hierarchia mentén) kevesebbet kapunk mintha a ténytábla összes sorát összeadtuk volna.

De, ha a fenti „JAV” vevőkódot automatikusan helyettesítettük volna egy alapértelmezett „ismeretlen vevő” nevű elemmel, és az „ismeretlen vevő” –t automatikusan felvettük volna a vevő dimenzióba, akkor az adattárház és a készletnyilvántartó rendszer adatai stimmeltek volna.

Dimenzió elemek hiányának okai

A dimenzió elemek hiányának alapvetően két különböző oka lehet. Az okok egyik csoportja technikai hibákra vezethető vissza, míg másik részük az üzleti folyamatok természetéből fakad.

Tipikus technikai okok:

  • Forrásrendszerek hibái: a leggyakoribb hibatípus, amely rendszerint a silány adatminőségre és az inkonzisztens működésre vezethető vissza
  • A dimenzió és Fact táblák betöltésének időbeli eltolódása: A dimenzió táblákat mindig hamarabb töltjük, mint a ténytáblákat, és ha a dimenzió táblák betöltése után olyan új tranzakciók keletkeztek, amelyekhez új dimenzió érték is tartozik, akkor az már nem fog megjelenni a dimenzió táblában
  • Betöltő programok hibái. Ilyen is előfordulhat, és leggyakrabban a helyettesítő (Surrogate) kulcsok generálása körül kell a hiba okát keresni

Helyettesítő kulcsok:

Nem tartozik közvetlenül a témához, de a hiányzó dimenzió elemek problémájának megértéséhez szükséges lesz tudnunk, hogy az adatpiacban a dimenzió elemek természetes kulcsa (natural key, business key, application key, …) helyett úgynevezett helyettesítő, vagy más néven mesterséges kulcsokat használunk. (Surrogate key) Például a 311001-es vevőkód helyett a 1-es ID-t, a 311002 helyett 2-est és így tovább. Ha új dimenzió elem érkezik, akkor az megkapja az 5-ös ID-t. A ténytáblák betöltésekor pedig a 311001-es természetes kulcsot kicseréljük 1-re, a 311002-őt 2-re és így tovább.

De kanyarodjunk vissza a kiinduló problémához. A példabeli hiány tipikusan technikai hiba. Keletkezésének hátterében az áll, hogy a „JAV” kódú vevőn lett a készlet mozgatva, de a „JAV” kódú vevőt nem vették fel a vevőtörzsbe, mert a vevő nem igazi vevő, hanem csak egy olyan fiktív vevő, amelyen a helyi informatika próbálta „JAVítani” készletnyilvántartó rendszer egy hibás tételét.

Üzleti folyamat természetéből származó okok

Előfordul, hogy a dimenzió elem hiánya nem technikai problémákra, hanem az üzleti folyamatok természetére vezethető vissza. Erre jó példa a hűségprogramok esete: A pénztáros egy új vevőkódot oszt ki a programban résztvevő vásárlónak, és a gépben már ezen az új vevőkódon rögzíti a vásárlást. A vásárlás után a vevőnek átadja a hűségprogramban való részvételhez szükséges nyomtatványt, amire rávezeti a vevő új vevőkódját. A vevő hazamegy, kitölti a nyomtatványt (név, cím, telefon, eltartottak száma, …) majd visszaküldi a megadott címre…

Amíg a vevő nem küldi vissza a nyomtatványt, addig az értékesítési tranzakció táblában egy olyan árva rekord csücsül, amelyikhez nem tartozik vevő a dimenziótáblában…

Eltérő okok, eltérő megoldások

Másként kell kezelni egy dimenzióelemet, ha tudjuk, hogy annak tulajdonságai (megnevezése, milyen csoportokba tartozik, milyen jellemzői vannak) rendelkezésünkre fognak állni majd valamikor, és másként, ha tudjuk, hogy az adott dimenzióelem tulajdonságai valamilyen technikai ok miatt sohasem fog megérkezni. (pl sohasem lesz a „JAV” vevő felvéve a vevőtörzsbe)

A klasszikus elmélet szerint az adattárházból sosem törlünk, és ennek következtében az adattárházba csak jó, konzisztens, helyes… adatok kerülhetnek. A hibás, hiányos adatokat először az üzemeltetésnek rendbe kell tennie, majd ezután tölthetők csak az adattárházba. Ha feltétel nélkül elfogadnánk az elméletet, akkor a hűségprogramot kínáló példacég értékesítési adatpiacába addig nem kerülhetne adat, amíg a vevő adatai meg nem érkeznek. Ez azt jelenti, hogy a példacég havi teljes forgalmát addig nem tudnánk elemezni, amíg be nem érkezik az összes igénylőlap (a vevőkódhoz tartozó névvel, címmel, …).

Módszerek a hiányzó dimenzióelemek kezelésére

A következőkben bemutatom mindazon módszereket, amelyek alkalmasak arra, hogy kezeljék a hiányzó dimenzióelemek okozta problémákat. A módszerek:

  1. Hiányzó dimenzióelemek hibalistára tétele
  2. Hiányzó dimenzióelemek helyettesítése egy alapértelmezett „ismeretlen” elemmel
  3. Hiányzó dimenzióelemek felvétele, mint „hiányos” vagy „késő” elemek

Hiányzó dimenzióelemek hibalistára tétele.

Talán a legegyszerűbb módszer, ha az árva rekordokat nem töltjük be a ténytáblába, hanem a hiányzó elemet hibalistára tesszük. A hibalista alapján az alaprendszerek üzemeltetői a hibákat az alaprendszerekben javítják, majd a következő betöltésekor a javított adatok automatikusan bekerülnek az adattárházba.

Hiányzó dimenzió elemek hibalistára tétele SSIS csomagokban

Hiányzó dimenzió elemek hibalistára tétele

A hibalista tartalmazza, hogy a dimenzió elem:

  1. Melyik ténytáblában bukkant fel
  2. Melyik dimenzióból hiányzik
  3. Melyik SSIS csomag fedezte fel a hiányt
  4. És egy szöveges leírást, ami arra utal, hogy mit kell tenni a probléma elhárításához.

A módszer előnye, - és bizonyos esetekben egyben hátránya is, - hogy alkalmazása esetén ellenőrizetlen, hibás rekordok nem kerülhetnek az adattárházba. Ez egyfelől előny, mert így biztosítható, hogy az adattárházba csak jó adatok kerülhetnek, másfelől hátrány, mert az adatok addig nem láthatók, amíg az üzemeltetés a hibát ki nem javítja. És tapasztalatból tudjuk, hogy az üzemeltetés nem fogja kijavítani...

Hiányzó dimenzióelemek helyettesítése egy alapértelmezett „ismeretlen” elemmel

Jobb megoldáshoz jutunk, ha a hiányzó dimenzióelemet helyettesítjük például egy -1 kódú, alapértelmezett „hiányzó” dimenzió elemmel. A módszer lényege, hogy minden dimenziótáblába felveszünk egy -1 kódú, ismeretlen, Unknown, stb… nevű dimenzió elemet, és ha a betöltés közben olyan dimenzió elemmel találkozunk a ténytáblában, amely nincs benne a dimenziótáblában, akkor annak természetes kulcsát automatikusan helyettesítjük az ismeretlen elemmel.

Módszerek:

  1. JOIN és COALESCE (TSQL)
  2. Lookup trasformation task (Integration Services)

JOIN és COALESCE

Az alább bemutatásra kerülő TSQL utasítás beszúrja az új rekordokat a ténytáblába úgy, hogy közben kikeresi a mesterséges kulcsokat a dimenzió táblából, és ha nem találja, akkor helyettesíti őket a -1-es (ismeretlen) kóddal.

Insert into FactErtekesites

SELECT

COALESCE( DimTime.DateCode, -1 ),

COALESCE( DimVevo.VevoCode, -1 ),

COALESCE( DimCikk.ProductCode, -1 ),

TempFactErtekesites.Ft

FROM TempFactErtekesites

LEFT JOIN DimCikk ON TempFactErtekesites.CikkID = DimCikk.CikkID

LEFT JOIN DimVevo ON TempFactErtekesites.VevoID = DimVevo.VevoID

LEFT JOIN DimTime ON TempFactErtekesites.DateID = DimTime.DateID

 

LOOKUP transformation task

Kihasználva, hogy adattárházunkat az Itegration Services-zel (SSIS) töltjük, lehetőségünk van arra, hogy a fenti célt, nevezetesen helyettesítő kulcsok keresését és hiányzó elemek történő pótlását a betöltés közben a memóriában végezzük el.

Megoldás az SSIS LookUp task-jával

  1. A dimenzió táblákat beolvassuk memóriába (Elég, sőt célszerű csak a természetes kulcs – helyettesítő kulcs párokat (natural key – surrogate key) beolvasni)
  2. Megkeressük a természetes kulcsok helyettesítő (surrogate) megfelelőit
    1. a. Ha megtaláljuk, kicseréljük
    2. b. Ha nem találjuk meg (error ág), akkor helyettesítjük pl. a -1 kóddal és kiírjuk hibalistára
  3. Beszúrjuk a fact táblába az összes sort.

Hiányzó dimenzióelemek helyettesítése egy alapértelmezett „ismeretlen” elemmel (SSIS)

 

Hiányzó dimenzióelemek helyettesítése egy alapértelmezett „ismeretlen” elemmel

A fenti módszerek hátulütője (mind a JOIN, mind LookUp transformation task), hogy a dimenzió elem nem hiányát örökre rögzítik. Azaz, ha később valamilyen oknál fogva megérkezik a dimenzió elem, az bekerül a dimenzió táblába, mint új elem, de hozzá nem fognak ténytábla sorok tartozni, mert azokat már átkódoltuk -1-esre (hiányzó elemre) és ezzel elmostuk, hogy valójában ki volt ő korábban.

(Persze ha újratöltenénk a ténytábla idevonatkozó sorait, akkor a -1-es kód helyett megkapná a dimenzió táblában szereplő megfelelő kódot)

Vannak azonban olyan betöltési módszerek is, amelyek segítségével úgy pótolhatjuk a hiányzó dimenzió elemet, hogy annak megérkezése után nem kell újra betölteni a ténytáblákat.

Hiányzó dimenzióelemek felvétele, mint „hiányos” elemek

Előfordul, hogy az üzleti folyamatok olyanok, hogy csak jóval a tranzakció keletkezése után érkeznek meg a ténytábla sorokhoz tartozó dimenzió elemek. (lásd a fenti hűségprogram példáját) Ebben az esetben nem helyettesíthetjük a Fact táblában szereplő dimenzió kulcsot egy alapértelmezett hiányzó elemmel, mert a dimenzió elem létezik, csak még nem ért ide!

Ha jellemző, hogy forrásrendszereink dimenzió elemei rendszeresen később érkeznek, mint a hozzájuk kapcsolódó tény sorok, akkor nincs mit tenni: A fact tábla soraiból is tölteni kell a dimenzió táblát.

iányzó dimenzióelemek felvétele, mint „hiányos” elemek  (SSIS)

 

Hiányzó dimenzióelemek felvétele, mint „hiányos” elemek

A folyamat leírása

  1. Tény (fact) adatok beolvasása
  2. A Ténytáblában szereplő természetes kulcsok kicserélése a dimenzió táblában szereplő mesterséges kulcsra. Ha nem találja meg a természetes kulcsot a dimenzió táblában akkor:
    1. Kiírja a hiányzó dimenzió elemet a MissingDimensionMembers táblába és
    2. Felveszi a hiányzó dimenzió elemet a dimenzió táblába (természetes kulccsal és csupa N/A megnevezéssel). Felvétel után a dimenzió elem megkapta új mesterséges kulcsát
    3. Kikeressük a dimenzió táblából a most beírt hiányzó elem mesterséges kulcsát
  3. A kicserélt mesterséges kulcsokat tartalmazó sort beírjuk a ténytáblába

Megjegyzés:

  1. Az első lookup task-ban még használhatjuk, de a másodikban már ki kell kapcsolnunk a gyorsítótár (cache) használatát, mert a dimenziótáblába történő írás után a cache nem frissül a automatikusan. (És a gyorsítótárban (cache-ben) nincs benne az újonnan felvett elem. A cache használatát a lookup task advanced fülén, az "Enable memory restriction" checkbox állítgatásával befolyásolhatjuk)
  2. Ha a bejövő ténytábla sorokban, ugyanaz a hiányzó dimenzióelem ezerszer szerepel, akkor azt az insert task ezerszer fel fogja venni a dimenziótáblába. Ezt úgy kerülhetjük el, ha az insert utasítás helyett egy olyan tárolt eljárást hívunk meg, amely ellenőrzi, hogy fel lett-e már véve egyszer a hiányzó dimenzióelem vagy nem. Ha nem akkor felveszi, egyébként nem.

Összefoglalás

Ha betöltés közben ismeretlen dimenzióelemekkel találkozunk, akkor az alábbi alternatívák közül választhatunk:

  1. az ismertetlen elemet tartalmazó rekordot nem töltjük be a ténytáblába, csak hibalistára tesszük, vagy
  2. az ismertetlen elemet tartalmazó rekordot úgy töltjük be a ténytáblába, hogy annak természetes kulcsát az alapértelmezett ismeretlen elem mesterséges kulcsára cseréljük, és az ismeretlen elemet hibalistára tesszük, vagy
  3. Az ismeretlen elemet felvesszük a dimenziótáblába és az ismertetlen elemet tartalmazó rekordot betöltjük a ténytáblába.

Technikailag mindhárom út járható, üzletileg azonban csak az utolsó kettő elfogadható...

Kővári Attila - BI projekt

Új hozzászólás