5 módszer a forrásrendszerek legyűjtésére és a legyűjtés ellenőrzésére

Az adattárház, adatpiac építések hőskorában az egyik kulcskérdés az volt, hogy vajon le tudjuk-e válogatni a forrásrendszerek adatait inkrementálisan, azaz meg tudjuk-e határozni azt az adatkört, ami az utolsó letöltés óta keletkezett. Ha a válasz „nem” volt akkor el kellett gondolkozni azon, hogy hogyan csökkentsük az áttöltendő adatok körét.

Azóta sokat változtak az alaprendszerek. Ma már majdnem mindegyikük valamilyen ismert adatbázis kezelőn fut, lehetőséget biztosítva a „közvetlen” lekérdezésre. A mai alaprendszerek tervezésekor már figyelembe veszik azt is, hogy azok adataira üzleti intelligencia rendszereket építenek majd.

Az alaprendszerek evolúciója tehát az inkrementális letöltések megvalósíthatóságának malmára hajtja a vizet. De nekünk még a „régi” rendszerek adataiból is kell adattárházat építenünk, és továbbra is kulcskérdés maradt: Hogyan határozhatjuk meg az utolsó letöltés óta keletkezett vagy megváltozott, esetleg törölt adatok körét?

Kővári Attila - 2006. július 15.

Jelen dokumentum célja, hogy számba vegye mindazon módszerek, amelyek segítségével leghatékonyabban tudjuk letölteni forrásadatainkat. Olyan kérdésekre keresi a választ, mit a „Hogyan tudjuk leválogatni csak azokat a rekordokat, amelyek megváltoztak?” vagy a „hogyan kerülhetjük ki a teljes állomány nap, mint napi leválogatását?”

Terjedelem

Az adattárházak forrásrendszereinek spektruma a szövegfájltól az Excel táblákon keresztül speciális „adatbázisokon” át a relációs adatbázisokig terjed. Minden rendszertípusnak meg van a maga adatleválogatási jellemzője. Mi most csak a relációs adatbázis alapú forrásrendszerek adatleválogatási módszereivel fogunk foglalkozni.

A dokumentumban csak azokat a módszereket mutatom be, amelyek maguk indítják az adatleválogatást (Pull modell) és nem írnak, nem hoznak létre új objektumokat a forrásrendszerekben. Léteznek megoldások, melyek alaprendszer oldalról indítják a leválogatást és közvetlenül a stage adatbázisba írnak (Push modell), és léteznek hibrid megoldások, melyek például az új, megváltozott, vagy törölt rekordokat triggerekkel delta táblákba gyűjtik, és utána az adattárházra bízzák, hogy onnan a friss adatokat elvegye és aztán kitakarítsa a táblát. (Hybrid modell)

 

Pull and Push extract models

 

Tranzakcionális replikációval, vagy a naplófájlok automatizált átvitelével (Log Shipping) megoldható, hogy az adatváltozásokat egy másik szerverre szinkronizáljuk, azaz ha forrásrendszerünkben keletkezik egy új rekord, akkor azt átmozgassuk egy másik adatbázisba. A módszer tehát elméletileg megadja annak lehetőségét, hogy a stage területre töltsük azokat a rekordokat, amelyek újak, megváltoztak, vagy törlődtek, de mivel hatékonyan csak SQL Serverek között működnek, illetve mivel még egy csomó nyitott kérdést tartogat számomra, ezért nem foglalkozom vele. Akit érdekel a téma, az utána olvashat itt: http://msdn2.microsoft.com/en-us/library/ms151784(d=ide).aspx

Máshogy kell eljárni, illetve más és más lehetőségünk van a törzsadatok, illetve a tranzakciók inkrementumainak meghatározására, ezért ezeket a módszereket - noha néha alkalmazhatóak mindkét adatkörre - szeparáltan tárgyalom.

Törzsadatok leválogatásának módszerei

A törzsadatok jellemzői, hogy elemszámai a tranzakciók számához képest elenyésző. A legnagyobb elemszámú törzsek tipikusan a vevő és termék állományok, melyek elemszáma általában néhány százezer, vagy kevesebb rekord.

Egy BI projekt megvalósításakor az alábbi törzsadat leválogatási módszerek jöhetnek számításba:

  1. Teljes áttöltés nap, mint nap
  2. Csak az élő törzsadatok áttöltése
  3. Inkrementális áttöltés (csak az új és megváltozott rekordok leválogatása)
    1. Érvényesség dátuma alapján
    2. Időpecsét (időbélyeg, timestamp) alapján
    3. Napló alapján

1. Teljes áttöltés módszer

Talán a legegyszerűbb és legbiztosabb módszer, ha minden egyes betöltéskor letöltjük a teljes törzsadat állományt. Sőt, ha törzstábláink csak néhány ezer rekordot tartalmaznak, akkor minden gondolkodás nélkül használhatjuk a teljes áttöltés módszerét, ugyanis nagy valószínűséggel gyorsabb és hibatűrőbb megoldást kapunk, mintha kidolgoznánk valamilyen inkrementális áttöltő eljárást.

Szintén le kell töltenünk a teljes törzsadat állományt, ha abból helyben nem tudjuk biztosan megállapítani, hogy mely rekordjai újak és melyek változtak meg.

2. Élő törzsadatok áttöltése

A most bemutatásra kerülő módszer nem az utolsó betöltés óta megváltozott adatokra szűkíti az áttöltendő adatkört, hanem azokra, amelyek élők. A módszer abból a hipotézisből indul ki, hogy csak azok a törzsadatok változhatnak, amelyek élők, vagy az utolsó betöltés pillanatában még élők voltak.

Egyes ERP rendszerek tárolják, hogy a törzsadatok mettől meddig érvényesek (aktívak, élők). Felhasználhatjuk ezt a mezőt a teljes törzsállomány szűkítésére és elegendő a most, illetve az utolsó betöltéskor érvényes törzsadatokat leválogatnunk, ha alaprendszerünk biztosítja, hogy az érvényesség dátumát csak úgy lehet megváltoztatni, hogy az ne írja felül a múltat. Találkoztam már olyan ERP rendszerrel, amely ugyan azt biztosította, hogy „nem élő” cikkre ne lehessen új tranzakciót rögzíteni, de azt már nem, hogy az érvényesség dátuma ne lehessen kisebb, mint az utolsó tranzakció dátuma.

A módszer használata nagy körültekintést igényel. Mindenképpen szükséges megvizsgálnunk, hogy forrásrendszereink hogyan kezelik az érvényesség dátumát. Ha nem vagyunk benne 100%-osan biztosak, hogy alaprendszerünk úgy kezeli, ahogy azt elvárjuk, akkor töltsük át inkább az egész állományt.

3. Inkrementális áttöltési módszerek

Az általam tárgyalt inkrementális áttöltési módszerek a megváltozott rekordokat mindig valamilyen dátum mező alapján különítik el. Alapelvük az, hogy csak azokat a rekordokat töltjük le, amelyek az utolsó sikeres betöltés óta keletkeztek, vagy megváltoztak.

a) Inkrementális áttöltés időpecsét (timestamp) alapján

Egyes alaprendszerek a törzsadat információk mellett azt is tárolják, hogy az adott törzsadat mikor lett felvéve és/vagy azt is, hogy az adott törzsadat mikor lett utoljára módosítva. E két plusz információ már elegendő ahhoz, hogy pontosan megállapítsuk: Melyek azok az új rekordok, amelyek az utolsó sikeres letöltés óta keletkeztek, vagy megváltoztak. Így pontosan meghatározható azon adatok köre, amelyeket le kell töltenünk.

Az időpecsét lehet adatbázis által karbantartott mező (TimeStamp adattípus), és alkalmazás által karbantartott egyszerű dátum mező (UtolsoModositasDatuma). A két adatleválogató módszert eltérő SQL szintakszissal kell megvalósítani, mert az SQL timestamp nem dátum típusú, de a leválogatás módszere (elmélete) azonos mindkét módszer esetén, ezért nem kezelem őket külön.

b) Inkrementális áttöltés naplók alapján

A mai ERP rendszerek egy része már nyújt olyan naplózási szolgáltatást, amely minden törzsadat változást dokumentál: Ki mikor mit hozott létre, módosított, vagy törölt. Így ezen napló bejegyzéseit felhasználhatjuk arra, hogy a teljes törzsállomány adatait leszűkítsük az új, megváltozott vagy törölt adatok körére.

Tranzakciók leválogatásának módszerei

A legnagyobb törzsállományok (például ügyféltörzs) is jellemzően csak néhány százezer rekordot tartalmaznak. Ezzel szemben a tranzakció állományok jellemzően több milliót, és nem ritka a napi több százezer új rekord sem. Értelemszerűen máshogy kell kezelnünk a törzsadatokat és máshogy a tranzakciókat. Míg az előbbit akár teljes egészében nap, mint nap letölthetjük az alaprendszerből, addig a tranzakció állományokat már nem. Még akkor sem, ha a forrásrendszereink nem biztosítanak nekünk lehetőséget az utolsó sikeres betöltés óta keletkezett rekordok elkülönítésére.

Tranzakciók leválogatásának módszerei:

  1. Adatkör szűkítése bizonylat dátuma alapján
  2. Automatikusan növő sorszám alapján (AutoIncrement)
  3. Inkrementális letöltés időpecsét (timestamp) alapján

Adatkör szűkítése bizonylat dátuma alapján

Ha forrásrendszerünk nem biztosít semmilyen lehetőséget, hogy meghatározzuk az utolsó sikeres letöltés óta keletkezett, megváltozott, vagy törölt adatok körét, (mert például nem tartalmaz időpecsétet), akkor nekünk kell kitalálni valamilyen eljárást, amely segítségével elkerüljük a teljes tranzakció állomány áttöltését.

A legelterjedtebb módszer a bizonylat dátuma alapján történő szűkítés. (Bizonylat dátumának fogom hívni azt a dátumot, amelyet az elemzésekhez felhasználunk. Egy számla esetében ez a teljesítés dátuma)

Tegyük fel, hogy tapasztalatból tudom, hogy termelési adatokat maximum három hónapra visszamenőleg módosíthatják. Ezt kihasználva létrehozhatok olyan leválogatási eljárást, amely csak az utolsó három hónap termelési adatait tölti le. Nem túl szofisztikált eljárás, de még mindig jobb, mint a teljes állomány nap, mint napi áttöltése.

A módszert tovább finomíthatjuk úgy, hogy mielőtt letöltenénk az adatokat, ellenőrizzük, hogy mely hónap összegei térnek el az adatpiac havi összegeitől, és csak attól a hónaptól kezdve töltjük le az adatokat, amitől eltér. Ma július van. Ellenőrzöm az összes hónapot visszamenőleg, és azt látom, hogy a májusi adatok 10 forinttal eltérnek (ennyi a különbség az adatpiac és az alaprendszer között). Ekkor újra leválogatom a május, június, július havi adatokat.

Az ellenőrzéssel finomított módszer csak akkor használható, ha tudjuk, hogy múltbeli adataink szerkezete csak úgy változhat, ha új sorok kerülnek a rendszerbe. Tegyük fel, hogy 100 Ft-ot értékesített két személy: A és B. Tegnap még úgy gondoltuk, hogy a két személy 50, 50 százalékkal járult hozzá a száz forint árbevételhez. Mára azonban kiderült, hogy rosszul tudtuk, mert a két személy a 100 Ft árbevételhez 60, 40 százalékkal járult hozzá. Ezt visszakönyveljük a rendszerbe. A totál összeg nem változott, de annak szerkezete már igen. És mivel összegeket hasonlítunk össze, ami mind a két rendszerben 100 forint, ezért nem töltjük le újra az adatokat. Pedig megváltoztak.

Automatikusan növő sorszám alapján (AutoIncrement)

Egyes alaprendszerek tranzakcióikat ellátják egy folyamatosan növő sorszámmal (Identity, sequence). Ha új tranzakció keletkezik, akkor annak sorszáma egyenlő lesz a tranzakció tábla addigi legnagyobb sorszáma + 1 értékkel.

A sorszám (Identity, sequence) mező alapú leválogatással csak az utolsó leválogatás óta keletkezett új tranzakciókat tudjuk detektálni. Nem fogjuk tudni kimutatni, hogy mely tranzakciók változtak meg, vagy mely tranzakciókat törölték az utolsó sikeres leválogatás óta. Így a módszer csak akkor használható, ha forrásrendszerünk nem töröl és nem update-el, hanem minden egyes változást, törlést új rekord létrehozásával valósít meg.

Inkrementális letöltés időpecsét (timestamp) alapján

Ahogy a törzsadatokat, úgy a tranzakciókat is leválogathatjuk az időpecsét módszer szerint, ha tranzakcióink tartalmaznak időpecsét (Timestamp) mezőt (mikor keletkezett vagy módosult az adott tranzakció)

Az időpecsét módszer egyetlen hátránya, hogy segítségével nem állapítható meg, hogy mely rekordok törlődtek az alaprendszerből. Ez a törzsadatok feldolgozásakor nem okoz problémát, hiszen a törzsadatokat dimenziótáblákba töltjük, és a dimenzió táblákat szinte sosem töröljük. A tranzakciók törlését azonban már nekünk is át kell vezetnünk az adattárházon (ha a törlés a rendes üzletmenet része)

Szerencsére a mai rendszerek már jellemzően fizikailag nem törölnek tranzakciót. Helyette sztornóznak vagy ellentétes előjelű tranzakcióval, és/vagy a sztornózott tranzakció megjelölésével.

Az időpecsét módszer tehát csak akkor használható, ha biztosak vagyunk abban, hogy forrásrendszerünk nem töröl. Ezt könnyen kitesztelhetjük, ha napi rendszerességgel leválogatjuk az új rekordokat egy táblába és egy-két hét elteltével megnézzük, hogy van-e különbség a két állomány között. Ha van, akkor

  1. forrásrendszerünk töröl, vagy
  2. forrásrendszerünk nem minden változás esetén update-eli a dátum mezőt.

Ha ezek bármelyike igaz, akkor sajnos le kell mondanunk az időpecsét alapú adatleválogatásról.

Az időpecsét módszer gyakorlati megvalósítása

Tegyük fel, hogy megvizsgáltuk forrásrendszerünket, és úgy gondoljuk, hogy annak adatait leválogathatjuk az időpecsét módszer megvalósításával. Már csak egy kérdés maradt: Hogyan?

Tételezzük fel, hogy minden nap az előző napi adatokat töltjük át. A betöltés július 9-én hajnali 4-kor indul. Ebben az esetben az előző nap július 8. Nulla óra nulla perctől július 9 nulla óra nulla percig tart. Tehát:

Set @Extract_Date_Start = '2006-07-08 00:00:00'
Set @Extract_Date_End = '2006-07-09 00:00:00'

 

Ebben az esetben az átöltést megvalósító SQL select a következőképpen nézhet ki:

Select Oszlop1, Oszlop2, … Oszlop n
From Forrástábla1
Where
Datum1 >= @Extract_Date_Start AND
Datum1 < @Extract_Date_End

Ha sikeres volt a leválogatás, akkor a következő letöltés start date-je az utolsó letöltés end date-jével lesz egyenlő.

Ellenőrzés

Egy leválogatás sikeres, ha a

  1. leválogatás során nem keletkezett hibaüzenet,
  2. és minden rekord átjött a forrásrendszerből.

Az 1. ellenőrzést könnyen megvalósíthatjuk, ha megnézzük az adatbetöltési naplót. (Az Integration Services rendelkezik beépített naplózási funkcióval, de célszerű ezt kiegészítenünk saját betöltési naplóval). Ha nincs benne hibaüzenet, akkor a letöltés sikerült.

Minden rekord átjött a forrásrendszerből?

Ha a letöltés sikerült, akkor igen nagy a valószínűsége annak, hogy az összes rekord átjött. Vannak azonban speciális esetek amikor előfordulhat, hogy bár a letöltés sikerült, mégsem jött át minden rekord.

Képzeljük el, hogy az UtolsoModositasDatuma mező alapján válogatjuk le adatainkat és a záró dátum előtt elindult egy tranzakció, mi még nem lett jóváhagyva (commit). Befejezzük a leválogatást, majd a tranzakció megkapja a commit-ot. Így ez a tranzakció nem kerül leválogatásra, bár az utolsó módosítás dátuma mező tartalma kisebb, mint a leválogatás záró dátuma és nem kerül bele a következő napi leválogatásba sem, mert a következő leválogatás induló dátuma nagyobb, mint a tranzakció UtolsoModositasDatuma.

Szumma szummárum hibaüzenet nincs, és egy rekord észrevétlenül kimaradt az adattárházból. Szerencsére ez ritkán fordul elő, mert általában

  1. a leválogatások órákkal a záró dátum után kezdődnek és a
  2. tranzakciók kicsik, így hamar megkapják a commit-ot.

Vannak azonban olyan rendszerek, amelyek csak órákkal az adatrögzítés után kezdenek „élni”. Ekkor kezdenek el számításokat futtatni, és ha a számítások eredményét fel kell használnunk, akkor bizony mi is belefuthatunk a fenti problémába. Ahhoz, hogy ezt elkerüljük minden letöltés után célszerű ellenőrizni, hogy

  1. Hány rekord van a forrásrendszerben (StartDate EndDate között)
  2. Hány rekord van a stage-ben (StartDate EndDate között)

Ha a két rekordszám egyezik, akkor a leválogatás sikerült. Ha nem akkor a leválogatást meg kell ismételni, és csak ezután kezdhetjük meg az adatok további feldolgozását

Kapcsolódó anyagok:

Kővári Attila - BI projekt

Új hozzászólás