Kővári Attila Független BI tanácsadó, SQL Server MVP biprojekt.hu

1. Adattárház építése az SQL Server 2005 IntegrationServices (SSIS) segítségével Kővári Attila Független BI tanácsadó, SQL Server MVP www.biprojekt.hu Kovari.Attila@biprojekt.hu

2. Az előadásról • Kinek szól az előadás: • Akik már ismerik valamennyire az SSIS-t • Akik nem most hallanak először a BI-ról és az adattárházról • Az előadás célja • A legjobb módszerek bemutatása • Hogy Önök hatékony ETL folyamatokat valósítsanak meg az SSIS segítségével

3. Tematika • Az SSIS Gyors áttekintése • Hatékony ETL folyamat megvalósítása az SSIS segítségével • Teljesítmény-hangolás

4. Hol tartunk most? Üzleti igények meghatározása Architektúra- tervezés Eszköz- választás és telepítés Növekedés Dimenzionális modellezés Fizikai tervezés Adatbetöltők tervezése és fejlesztése Üzembe- helyezés, oktatás Projekt- tervezés Felhasználói felület /alk. tervezés Felhasználói felület /alk. fejlesztés Karbantartás Projektmenedzsment

5. SSIS áttekintés • Adatbetöltő eszköz, a MS ETL eszköze • Része az SQL Server 2005 programcsomagnak • Grafikus programozási interfész • Nem DTS!

6. Tematika Az SSIS Gyors áttekintése Hatékony ETL folyamat megvalósítása az SSIS segítségével Teljesítmény-hangolás

7. Hol tároljuk a package-eket?Adatbázis? Fájl rendszer? SSIS package store? • Érvek a fájlrendszer mellett • Könnyebb Source control alá helyezni egy fájlt, mint egy adatbázist • Könnyebb fájlt verziózni, mint adatbázist • Egyszerűbb menteni és visszaállítani, mint az msdb adatbázist • Egyszerűbben betölti a BI Studio az SSIS csomagokat fájlból, mint adatbázisból • Hierarchikusan rendezhetjük az SSIS csomagokat • Érvek az adatbázis mellett • A napi mentések során SSIS csomagjaink automatikusan mentődnek.

8. Készítsünk adatforrástól és hardver környezettől független SSIS csomagokat! • A minden package által használt beállításokat tegyük szeparált konfigurációs állományba (pl elérési utak) • Minden package ugyanabból a konfigurációs állományból olvassa ki a beállításokat! • Hol tároljuk a konfigurációs beállításokat? • XML konfigurációs állomány • Környezeti változó • Registry bejegyzés • Hívó package változójában • SQL Server • Best practice: Adatforrásonként, beállításonként egy XML fájl és Windows környezeti változók használata (Indirect XML Configuration file)

9. Naplózzuk az adattárház eseményeit! • Készítsünk naplót, hogy • Pontos képet kapjunk betöltési folyamataink eredményéről • Statisztikákat készíthessünk • Láthatóvá tegyük, hogy épp melyik folyamat fut • Futtatandó betöltések (SSIS csomagok, task-ok) szabályozása • Készítsünk háromszintű naplót (Job, Package, Task) lefúrási lehetőséggel • A task szintű naplózásra használjuk az SSIS beépített naplózási szolgáltatását (sysdtslog90 tábla)

10. Auditáljuk a beérkező rekordokat • A Derivedcolumntask segítségével könnyen hozzáadhatjuk a beérkező rekordokhoz, hogy • Melyik forrásrendszerből került be • Melyik Package töltötte • Milyen módon került be (BI Studióból, vagy job-ból? (interactiveMode) • Hogy került be? (hibaágon, vagy standard úton) • Ki töltötte be? • Mikori betöltéssel került be? • Az audit információk megkönnyítik a hibakeresést és a kézi javítást.

11. Építsünk dinamikus SSIS csomagokat • Egy ismételt betöltés során eldönti a package, hogy kell-e futnia vagy sem. Ne fusson újra, ha egyszer már sikeresen lefutott! • Használjunk feltételhez kötött végrehajtást (Expression and Constraint) • Napló alapján tárolt eljárás beírja a package változójába, hogy kell e futni vagy nem. A package innen kiolvassa és az annak megfelelő ágon fut. • Ne erre használjuk a disable=true beállítást, mert nem erre való!

12. Építsünk dinamikusan konfigurált csomagokat • Nem tudjuk beégetni az SSIS csomagba, hogy melyik napot kell letöltenie. • Paraméterezett lekérdezés, vagy az egész lekérdezés egy paraméter: • Select * from t where datum=? • „Select * from t where datum=2007-05-16” • Használjunk paramétert, ha lekérdezésünk hossza meghaladja a 4000 karaktert • Minden más esetben készítsük el magunk a teljes lekérdezést • Megj.: A DataFlow task nem konfigurálható át futásidőben, csak a package betöltésekor. (DTS tudta) -> Migration best practice: NE

13. Készítsünk Template package-et a fejlesztési munka gyorsítására • A Template package tartalmazza: • Konfigurációs állományok elérési útját • Naplózási funkciókat • Gyakran használt task-okat, connection menedzsereket, Standard változókat • Csomagok védelmi szintjét • ProtectionLevel=DontSaveSensitive • standard beállításokat • Tegyünk BreakPoint-ot az package OnPostExecute eseményére • Tegyünk szöveges megjegyzéseket a template package-be -> Mit kell majd átállítani, ha új package készül belőle

14. A Package-ek legyenek moduláris felépítésűek • 1 Package 1 táblát töltsön! • Dimenzió táblánként 1 package, ténytáblánként 1 package • Könnyebb fejleszteni, hibát javítani, futtatni, párhuzamosítani • Package-en belül használjunk container-eket • Párhuzamosíthatóak benne a folyamatok • Egyszerűbb nem futtatni (disable=true) • Használjunk fő package-eket a dimenziókat és ténytáblákat töltő package-ek összefogására

15. A Package-ek legyenek moduláris felépítésűek

16. Hogyan futassuk SSIS csomagjainkat fejlesztés közben? • BI Development stúdió • F5 (Start with debugging) • Ctrl F5 (Start without debugging) • Parancssor: DTExec.exe (vagy DTExecUI.exe) • Performancia teszteléshez használjuk a parancssort • Task-ok kikapcsolása: Disable=false (csak debug módban használható)

17. Tematika Az SSIS Gyors áttekintése Hatékony ETL folyamat megvalósítása az SSIS segítségével Teljesítmény-hangolás

18. Workflow engine Párhuzamos futtatást lehetővé tevő Task-okat, konténereket futtató workflow engine Teljesítménye SSIS szempontjából tekinthető adottságnak (teljesítménye az RDBMS-től, a hálózat sebességétől függ) Data Flow engie Speciális runtime task, ami lehetővé teszi a különböző rendszerek közti adatmozgatást Komponensei adatforrások, transzformációs eljárások, céladatbázisok Párhuzamosítható Az SSIS felépítése

19. Teljesítmény-hangolásPárhuzamosítsunk! Párhuzamosítsunk! Szedjük szét a forrásadatokat Fájlokat több fájlba Táblák adatát több szeletre (where feltétellel) Határozzuk meg, hogy hány folyamat fusson párhuzamosan Data flown-n kívül (Package-en belül): MaxConcurrentExecutables (-1 = (Logikai) processzorok száma + 2) Data flown-n belül: EngineThreads. Az alapértelmezett 5, ami egy multiprocesszoros gépen megnövelhető (Adatforrásoknak és aszinkron transzformációknak kell egy-egy thread)

20. Teljesítmény-hangolásIrányelvek Egyszerre a lehető legtöbb adatot olvassuk be a pipeline-ba Csak azokat amelyekre tényleg szükség van. Select * =  A lehető legkisebb helyigényű adattípust használjuk Kerüljük a teljes adathalmazon végzett transzformációkat (sort, aggregate) (ha tudjuk) Index: Betöltés szempontjából csak a dimenzió táblákra -> jobb lookup teljesítmény Használjunk SQL Server Destination-t OLE DB helyett Töltsünk üres táblába (Partícionálás)

21. Összefoglalás Építsen hatékony SSIS csomagokat! Ismerje meg alaposan az SSIS architektúráját Párhuzamosítson! Mérje a teljesítményt (Naplózzon) És használjon egy jól bevált adattárház építési metodológiát!

22. További információk • Integration Services: Performance Tuning Techniques • http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx • Project REAL: Business Intelligence ETL Design Practices • http://www.microsoft.com/technet/prodtechnol/sql/2005/realetldp.mspx • Blog bejegyzések: • Jammie Thomson, Marco Russo, Alberto Ferrari, Brian Knight írásai • Magyar nyelvű irodalom: • http://www.biprojekt.hu