ČESKÉ VYSOKÉ UČENÍ TECHNICKÉ V PRAZE Fakulta elektrotechnická
DIPLOMOVÁ PRÁCE
2013
Bc. Lukáš Kosina
ČESKÉ VYSOKÉ UČENÍ TECHNICKÉ V PRAZE Fakulta elektrotechnická Katedra počítačů
Integrace interního systému zdravotní pojišťovny
10. květen 2013
Vypracoval:
Bc. Lukáš Kosina
Vedoucí práce:
Ing. Viktor Černý
Prohlášení Prohlašuji, že jsem práci vypracoval samostatně a použil jsem pouze podklady uvedené v přiloženém seznamu. Nemám závažný důvod proti užití školního díla ve smyslu §60 Zákona č. 121/2000 Sb., o právu autorském, o právech souvisejících s právem autorským a o změně některých zákonů (autorský zákon).
Datum: 10. 5. 2013 ………………..……………………
Zadání diplomové práce (Originál v originálu diplomové práce, oboustranná kopie v kopii diplomové práce)
Abstrakt: Tato diplomová práce se zabývá konsolidací systémů ve zdravotní pojišťovně za použití technik stavby datových skladů. Summary: This thesis deals with the consolidation of systems in the health instance company by the techniques used in datawarehouses.
OBSAH KAPITOLA 1 - ÚVOD ......................................................................................................................................... 1 KAPITOLA 2 - MOŽNOST ODESLÁNÍ PODÁNÍ DO ZDRAVOTNÍ POJIŠŤOVNY................................ 3 2.1. OSOBNÍ PODÁNÍ ............................................................................................................................................ 3 2.2. PODÁNÍ POŠTOU ........................................................................................................................................... 3 2.3. PODÁNÍ PŘES PORTÁL ZDRAVOTNÍ POJIŠŤOVNY ........................................................................................... 3 2.4. PODÁNÍ POMOCÍ B2B ROZHRANÍ .................................................................................................................. 4 2.5. PODÁNÍ POMOCÍ DATOVÝCH SCHRÁNEK ...................................................................................................... 5 KAPITOLA 3 - SOUČASNÝ STAV ELEKTRONICKÝCH PODÁNÍ POMOCÍ ZABEZPEČENÉ ELEKTRONICKÉ KOMUNIKACE .................................................................................................................. 6 3.1. ELEKTRONICKÉ ZPRACOVÁNÍ DAT A ELEKTRONICKÁ KOMUNIKACE ............................................................. 6 3.2. ZABEZPEČENÁ ELEKTRONICKÁ KOMUNIKACE .............................................................................................. 6 KAPITOLA 4 - ELEKTRONICKÉ SLUŽBY ................................................................................................... 8 4.1. SYNCHRONNÍ SLUŽBY................................................................................................................................... 8 4.2. ASYNCHRONNÍ SLUŽBY ................................................................................................................................ 8 4.3. HYBRIDNÍ SLUŽBY ...................................................................................................................................... 12 KAPITOLA 5 - POPIS SYSTÉMŮ VE ZDRAVOTNÍ POJIŠŤOVNĚ ......................................................... 14 5.1. SERVER F5 (LOADBALANCER F5) .............................................................................................................. 15 5.2. B2B PROXY................................................................................................................................................ 16 5.3. TECHNOLOGICKÉ ADAPTÉRY ...................................................................................................................... 17 5.4. INTEGRAČNÍ PLATFORMA ........................................................................................................................... 17 5.4.1 Směrování služeb v integrační platformě ............................................................................................ 18 5.5. BPEL ......................................................................................................................................................... 19 5.6. ZÁKLADNÍ INFORMAČNÍ SYSTÉM ................................................................................................................ 20 KAPITOLA 6 - POPIS ZPRACOVÁNÍ PODÁNÍ ELEKTRONICKÝCH PODÁNÍ .................................. 21 6.1. SYNCHRONNÍ SLUŽBY................................................................................................................................. 21 6.2. ASYNCHRONNÍ A HYBRIDNÍ SLUŽBY ........................................................................................................... 21 6.2.1 Služby podané přes Portál zdravotní pojišťovny ................................................................................ 21 6.2.2 Služby podané přes B2B rozhraní ....................................................................................................... 21 KAPITOLA 7 - DATOVÝ SKLAD ................................................................................................................... 23 7.1. ARCHITEKTURA DATOVÉHO SKLADU.......................................................................................................... 24 7.1.1 Dvouvrstvá architektura ..................................................................................................................... 24 7.1.2 Třívrstvá architektura ......................................................................................................................... 25 7.1.3 Rozšířená třívrstvá architektura ......................................................................................................... 27 7.2. MOŽNOSTI UKLÁDÁNÍ DAT V DATOVÉM SKLADU ....................................................................................... 29 7.2.1 Relační datový sklad ........................................................................................................................... 30 7.2.2 Multidimenzionální datový sklad ........................................................................................................ 32 7.2.3 Uspořádání dat v datovém skladu v praxi .......................................................................................... 33 7.3. SCHÉMATA V MULTIDIMENZIONÁLNÍM DATOVÉM SKLADU ........................................................................ 36 7.3.1 Star schéma ........................................................................................................................................ 36
7.3.2 Snowflake schéma ............................................................................................................................... 38 KAPITOLA 8 - VYUŽITÍ INTEGRACE SYSTÉMŮ POMOCÍ ARCHITEKTURY DATOVÉHO SKLADU .............................................................................................................................................................. 42 8.1. SQL4SQL .................................................................................................................................................. 42 8.1.1 Generování skriptů pro datové pumpy................................................................................................ 44 8.1.2 Odmazávání starých podání ............................................................................................................... 46 8.2. STAGE OBLAST ........................................................................................................................................... 47 8.3. CORE OBLAST ............................................................................................................................................. 53 8.4. DATA MART OBLAST .................................................................................................................................. 57 KAPITOLA 9 - TESTOVÁNÍ ŘEŠENÍ ............................................................................................................ 61 KAPITOLA 10 - ZÁVĚR ................................................................................................................................... 63 10.1. SPLNĚNÉ CÍLE ZE ZADÁNÍ ......................................................................................................................... 63 10.2. NESPLNĚNÍ CÍLE ZE ZADÁNÍ...................................................................................................................... 63 PŘÍLOHA A
SEZNAM POUŽITÉ LITERATURY................................................................................... 64
PŘÍLOHA B
OBSAH PŘILOŽENÉHO CD ............................................................................................... 65
SEZNAM OBRÁZKŮ Obrázek 1 Schéma synchronní komunikace ....................................................................................................... 8 Obrázek 2 Schéma AS2 komunikace ................................................................................................................. 10 Obrázek 3 Zjednodušená komunikace pomocí AS2 protokolu ....................................................................... 12 Obrázek 4 Zjednodušená architektura serverů ............................................................................................... 14 Obrázek 5 Putování synchronních a asynchronních podání ........................................................................... 15 Obrázek 6 Komunikace pomocí AQ front ........................................................................................................ 18 Obrázek 7 Struktura podání v BPEL ................................................................................................................ 20 Obrázek 8 Reálná architektura datového skladu ............................................................................................. 34 Obrázek 9 Star Schema ...................................................................................................................................... 36 Obrázek 10 Prodej automobilů - star schéma .................................................................................................. 37 Obrázek 11 Snowflake schéma........................................................................................................................... 39 Obrázek 12 Prodej automobilů - snowflake schéma ........................................................................................ 40 Obrázek 13 Metadatová tabulka s objekty v DB.............................................................................................. 42 Obrázek 14 Metadatové tabulky pro generování skriptů ................................................................................ 45 Obrázek 15 Metadatová tabulka pro odmazávání záznamů ........................................................................... 46 Obrázek 16 B2B tabulky elektronického podání .............................................................................................. 48 Obrázek 17 Tabulky obsahující údaje o podání v Integrační platformě ....................................................... 50 Obrázek 18 Tabulky obsahující údaje o podání v systému BPEL .................................................................. 52 Obrázek 19 Tabulky obsahující údaje o podání v systémech ZIS .................................................................. 53 Obrázek 20 Číselníky v jádru integrace systémů ............................................................................................. 54 Obrázek 21 Tabulka obsahující záznamy o subjektech v jádru integrace ..................................................... 55 Obrázek 22 Tabulka obsahující záznamy o podání na B2B serveru v jádru integrace ................................ 55 Obrázek 23 Tabulka obsahující záznamy o podání na BPEL serveru v jádru integrace ............................. 56 Obrázek 24 Tabulka obsahující záznamy o podání na ZIS serveru v jádru integrace ................................. 57 Obrázek 25 Výstupní pohled Message_Overview ............................................................................................ 58 Obrázek 26 Výstupní pohled B2B_Message_Detail ......................................................................................... 59
SEZNAM TABULEK Tabulka 1 Srovnání vlastností datového skladu[2] .......................................................................................... 30 Tabulka 2 Nastavení datových pump ................................................................................................................ 46 Tabulka 3 Nastavení odmazávání jádra aplikace............................................................................................. 47
SEZNAM POUŽITÝCH ZKRATEK
Zkratka
Vysvětlení
AQ
Advanced Queuing
B2B
Business-to-business
B2B server
B2B Proxy spolu s databází, která ukládá informace o podáních na B2B Proxy.
BPEL
Business Process Execution Language
EKK
Elektronická komunikace s klienty
ELT
Extract-Load-Transform
ETL
Extract-Transform-Load
F5 BigIP IPF
Load balancer zdravotní pojišťovny Integrační platforma zajišťující přeposílání zpráv mezi systémy uvnitř pojišťovny
LDAP
Lightweight Directory Access Protocol
MDM
Message Disposition Notifications
PROD
Produkční prostřední na B2B serveru
SIMU
Simulační prostředí na B2B serveru
SOAP
Simple Object Access Protocol
WSDL
Web Service Description Language
WSS WSSAdapter XML ZIS
Web Services Security Adaptér uvnitř pojišťovny, který zajišťuje implementaci WSS eXtensible Markup Language Základní informační systém zdravotní pojišťovny
Kapitola 1 - Úvod Zdravotní pojišťovny se vždy potýkají s velkým množstvím podání od různých typů subjektů – např. zaměstnavatelů, soudních exekutorů, osob samostatně výdělečně činných, poskytovatelů zdravotních služeb a samozřejmě pojištěnců jako takových. Čím více pojištěnců zdravotní pojišťovna má, tím více podání dostává. S rostoucím počtem podání roste i snaha o elektronizaci a automatizaci celého procesu přijímání podání a jejich vyřizování. Zdravotní pojišťovna, která je použita jako model pro tuto diplomovou práci, patří mezi jednoho z největších poskytovatelů zdravotního pojištění v České republice. Protože počty podání, které tato pojišťovna přijme a vyřídí, se pohybují řádově výše než u ostatních pojišťoven, bylo nutné v historii začít budovat elektronické přijímání podání od všech typů subjektů, aby pojišťovna mohla vyřizovat všechna podání včas. Protože zdravotní pojišťovna během času řešila primárně problém přijímání a vyřizování velkého množství podání, vznikaly v pojišťovně mnohé systémy, které zmenšovaly množství ruční práce. Většina těchto systémů vznikala spíše jako oddělené funkční jednotky, než jako jeden velký fungující systém. Postupem času vznikaly požadavky na propojení dílčích systémů do jednoho celku, což vedlo k vzniku dalšího systému, který přeposílá podání mezi jednotlivými systémy podle předem nadefinovaných pravidel. Tyto jednotlivé systémy byly také vytvářeny různými dodavatelskými firmami, což velmi často vedlo k tomu, že nový dodavatel místo implementace nových funkcionalit do systému vytvořil na začátku systému vyhybku, která novou funkcionalitu oddělila od staré. Podání podle starých pravidel byla poslána původní cestou. Podání podle nových pravidel byla poslána nově vytvořenou cestou nového dodavatele. Většina nových požadavků na tyto systémy byla integrace nového systému a možnost přeposílání jeho zpráv. Proto se zprávy z jednotlivých systémů většinou nikdy nepotkají, i když se logicky jedná o stejné zprávy, pouze poslané z jiného zdroje. Je proto běžné v systémech pojišťovny, že dvě podání o stejném obsahu, která jsou odeslaná ze dvou různých systémů, jsou zaznamenávána na dvou různých místech. V nejhorším případě se stává, že systém podání z jednoho zdroje nezaznamenává vůbec, pouze je
1
přeposílá. V současné době je ve zdravotní pojišťovně asi 5 velkých systémových celků, mezi kterými putují elektronická podání. Tyto systémy mají své administrátory, kteří se převážně starají o chod jednotlivých serverů a řešení technických problémů, které správa systému přináší. Podání se předávají mezi těmito pěti systémy, většinou i v několika iteracích. Při předávání podání mezi systémy a při zpracování části podání systémem se velmi často stává, že se podání v systému zasekne a neposouvá se dále ke zpracování. Bohužel v současné době neexistuje žádný mechanismus, který by tato podání detekoval a informoval pracovníky zdravotní pojišťovny, zejména pracovníky monitoringu a elektronické komunikace. Tato diplomová práce se zabývá identifikací všech relevantních systémů, kde se vyskytují záznamy o průchodu elektronického podání. Dále se pokouší informace z těchto systémů shromáždit tak, aby bylo možné podání přes jednotlivé systémy sledovat a v případě jejich zaseknutí bylo možné toto zaseknutí zjistit dříve, než bude do zdravotní pojišťovny volat klient a stěžovat si, že jeho požadavek nebyl vyřízen.
2
Kapitola 2 - Možnost odeslání podání do zdravotní pojišťovny 2.1. Osobní podání Při osobním podání musí klient fyzicky dojít na přepážku pojišťovny a odevzdat buď médium se souborem (pokud se jedná například o předávání dávek vykázané péče zdravotnického zařízení) nebo vytištěné dokumenty. Od pracovníka pojišťovny dostane potvrzení přijetí dokumentu pouze na vyžádání – razítko s datem přijetí a podpis zaměstnance pojišťovny na donesenou kopii dokumentu. Z přepážek jsou materiály jedenkrát denně rozváženy na jednotlivá pracoviště kurýrem, kde je referenti ručně přepisují do koncových aplikací ZIS. Z popisu postupu předání je zřejmé, že tento způsob předávání dat je pomalý, neefektivní a zbytečně zatěžuje jak klienta, tak zaměstnance pojišťovny. Dokumenty jsou následně skenovány pro elektronickou archivaci a fyzicky archivovány po dobu stanovenou skartační lhůtou konkrétního dokumentu.
2.2. Podání poštou V případě předávání dat podání pomocí České pošty musí klient médium nebo dokumenty opatřit obálkou a odeslat na pobočku pojišťovny. Zásilka nejprve dorazí do podatelny pojišťovny, kde ji zaměstnanci zanesou do knihy přijaté pošty a připraví pro kurýra k rozvozu na jednotlivá pracoviště, kde jsou data opět ručně přepsána do koncových aplikací ZIS. Rozvoz pošty probíhá jednou denně. I tento způsob předávání dat je velmi neefektivní, zejména pro zbytečně dlouhou dobu mezi odesláním a zpracováním dat. Stejně jako v případě osobního podání jsou dokumenty skenovány a elektronicky i fyzicky archivovány.
2.3. Podání přes Portál zdravotní pojišťovny Portál zdravotní pojišťovny je webová aplikace ve správě pojišťovny a přístup je zabezpečen pomocí certifikátu nebo SMS/uživatelského jména/hesla. Samotné předání dat probíhá tímto způsobem: klient se přihlásí pomocí certifikátu nebo hesla/SMS do webové aplikace portálu pojišťovny. Pokud zastupuje více subjektů, zvolí subjekt, za který chce podání odeslat. Některé služby umožňují odeslat podání v datovém rozhraní (např. vyúčtování zdravotní péče), některé služby pouze ve formuláři (např. přehled o platbě
3
zaměstnavatele) a některé umožňují obojí (faktura za zdravotní péči), klient tedy dále buď vyplní formulář, nebo přiloží ze svého software vygenerovaný soubor. Po odeslání směřuje podání přímo do aplikace, ve které je zpracováváno. V některých případech probíhá zpracování automaticky, jinde je třeba, aby zaměstnanec pojišťovny podání zkontroloval a stiskem tlačítka potvrdil další zpracování, případně prověřil podání, která aplikace označí, že je třeba je zkontrolovat. V případě automatického zpracování může klient obdržet odpověď na své podání do 15 minut od odeslání požadavku. Oproti předchozím způsobům odesílání podání do pojišťovny je zabezpečená elektronická komunikace předem řízená – podání jsou tříděna podle jednotlivých úloh již na vstupu, nikoliv až po přijetí na centrální úložiště pojišťovny. Odpadá nutnost podání přepisovat, skenovat a fyzicky archivovat. Velkou výhodou tohoto způsobu předávání dat je, že klient má svá data v žurnálu podání stále k dispozici, může je zpětně prohlížet, tisknout a především může sledovat celý průběh zpracování podání.
2.4. Podání pomocí B2B rozhraní Předávání přes B2B kanál je pro klienta pojišťovny velmi komfortní způsob elektronického předávání dat. Komunikace probíhá přímo mezi softwarem klienta a pojišťovny bez nutnosti přihlašovat se do webového rozhraní služby, klient tak odešle všechny potřebné soubory stiskem jednoho tlačítka. Stejně jako u portálu jsou podání tříděna už na vstupu a automaticky nebo ručně zpracovávána přímo v koncové aplikaci pojišťovny. Odpověď na podání přichází po zpracování ze strany pojišťovny do software klienta, kde si ji klient může zobrazit, případně vytisknout, a to i zpětně. Protože odpadá nahrávání souborů/vyplňování formulářů ve webovém rozhraní, můžeme B2B komunikaci označit za nejrychlejší komunikaci ze všech uvedených způsobů.
4
2.5. Podání pomocí Datových schránek V případě zaslání podání do datové schránky, je takové podání přijato referenty, kteří centrálně zajišťují zpracování podání zaslaných do datových schránek, v aplikaci podání roztřídí a přidělí je ke zpracování koncovému referentovi, ten si v aplikaci podání převezme a zpracuje. Pokud se jedná o formuláře, musí je referent opět ručně přepisovat do koncové aplikace. Tento způsob komunikace je tedy kombinací elektronického a ručního zpracování – odpadá sice nutnost podání skenovat a fyzicky archivovat, ale data musí být zpracovávána ručním přepisováním.
5
Kapitola 3 - Současný stav elektronických podání pomocí zabezpečené elektronické komunikace 3.1. Elektronické zpracování dat a elektronická komunikace Protože elektronické vedení agendy přináší mnoho výhod, přistupuje na tento způsob správy a komunikace stále více subjektů. Jedním z hlavních aspektů je časová úspora – klienti i zaměstnanci mohou zpracovávat data mnohem rychleji než u papírové komunikace – není třeba dokumenty přepisovat, skenovat a v neposlední řadě ani fyzicky archivovat. Mnoho uživatelů využívá různé softwary, které umí automaticky zpracovat data do potřebného výstupu stiskem jednoho tlačítka. Možnost předat dokumenty elektronicky nebývá zpravidla časově omezena, odeslat data je možné kdykoliv bez nutnosti sledování fyzické dostupnosti pobočky nebo pracoviště. Podstatnou výhodou elektronické komunikace je rychlost přenosu zpráv, kdy jsou data doručena adresátovi během několika minut. Na rozdíl od klasických dokumentů elektronická data je možné editovat bez nutnosti přepisování celého dokumentu. Data jsou archivována elektronicky a díky tomu jsou jednoduše kdykoliv dostupná, s daty je možné dále pracovat a využívat je např. i pro rychlé statistiky a přehledy. Velkou výhodou je možnost hromadného odeslání dat, kdy s minimálními náklady lze oslovit velké množství uživatelů nebo klientů. Elektronická komunikace má ale i své nevýhody, občas se může v komunikaci objevit nevyžádaná pošta (spam), a pokud zkolabuje systém, jsou veškerá data nedostupná, při kolapsu hardwaru a absence zálohy dokonce i ztracena.
3.2. Zabezpečená elektronická komunikace Všechny zdravotní pojišťovny umožňují svým klientům předávání dat pomocí zabezpečené elektronické komunikace, což může být předávání pomocí webových aplikací – portálů, které u různých pojišťoven poskytují různé služby a formy zabezpečení nebo pomocí služeb B2B. Na rozdíl od ostatních způsobů předávání dat musí klient pro využívání zabezpečené elektronické komunikace splnit určité předpoklady, a to jak smluvně-právní, tak technické – musí vlastnit počítač vybavený připojením k internetu, operačním systémem, prohlížečem a plug-iny, které portál konkrétní pojišťovny vyžaduje,
6
vlastnit prostředky pro autentizaci – certifikát, případně mobilní telefon a v případě B2B služeb i software s B2B adaptérem. Jakmile klient uzavře smlouvu o využívání zabezpečené elektronické komunikace a jsou mu přiděleny prostředky pro přihlášení a podpis podání, může tento druh zabezpečené komunikace začít využívat. Komunikace je označována za zabezpečenou, protože klient musí při předávání dat prokázat svou identitu způsobem, který je stanovený ve smlouvě o využívání zabezpečené elektronické komunikace. Nejbezpečnější formou zabezpečení je klientský certifikát, přesto ale v poslední době patří mezi nejrozšířenější způsob autentizace a podpisu podání ve webových rozhraních portálů zdravotních pojišťoven kombinace přihlašovacího jména, hesla a případně potvrzovací SMS. Data pojišťovně jsou povinny předávat typově různé druhy subjektů, proto při uzavření smlouvy na straně pojišťovny dochází nejen ke kontrole identity klienta, ale i ke kontrole jeho oprávnění zastupovat určitý subjekt v roli (poskytovatel zdravotních služeb, zaměstnavatel, OSVČ, jiná zdravotní pojišťovna, soudní exekutor, státní instituce) – pokud se nejedná o statutárního zástupce, musí doložit plnou moc opravňující jej k zabezpečené elektronické komunikaci s pojišťovnou, kterou statutární zástupce podepsal. Pojišťovna pak přiřadí klientovi pouze ty subjekty a role, za něž je oprávněn data předávat – nemůže se tedy stát, že data bude zasílat neoprávněná osoba.
7
Kapitola 4 - Elektronické služby 4.1. Synchronní služby Synchronní služby jsou služby, u kterých se očekává odezva (odpověď) v řádech vteřin. Klient tedy obdrží na svůj dotaz okamžitě odpověď. Služby se využívají pro stručné dotazy do koncových aplikací (ověřování podle čísel pojištěnce, např. zda je k danému datu pojištěn a u které pojišťovny, zda je platný konkrétní průkaz pojištěnce, atd.). Tyto služby jsou dostupné voláním webových služeb protokolem SOAP a komunikace mezi klientem a službou probíhá SSL šifrovaným komunikačním kanálem. Ověření partnera a služby včetně směrování prochází přes B2B proxy. V případě synchronních služeb se ověřuje pouze přístupový certifikát klienta, data jako taková nejsou opatřena elektronickým podpisem. Každý partner má stanoven limit maximálního volání služby během daného časového období. Toto období se nastavuje defaultně pro všechny stejně, je však možné větším a významnějším partnerům tento limit zvýšit. Obrázek 1 znázorňuje zjednodušené schéma komunikace. Jednotlivé strany jsou odděleny pomocí B2B Interface, který je pevně definován ZP.
Obrázek 1 Schéma synchronní komunikace
4.2. Asynchronní služby Asynchronní služby se využívají při přenosu většího objemu dat, kdy zpracování podání trvá řádově minuty až desítky minut, nebo je obsah podání třeba zkontrolovat a ručně zpracovat v koncové aplikaci. Asynchronní komunikace vyžaduje stálé připojení k internetu a pevnou IP adresu – z tohoto důvodu menší poskytovatelé zdravotních služeb
8
nebo zaměstnavatelé využívají pro komunikaci hybridní služby (viz kapitola 4.3 Hybridní služby). AS2 (Applicability Statement 2 for Business Data Interchange Using HTTP; RFC 4130) vychází ze standardu EDIINT (EDI via Internet), což je standard určující proces výměny dat popisující posílání a příjem dat přes bezpečné připojení. Protokol AS2 umožňuje bezpečně a spolehlivě přenášet data s využitím internetu. Bezpečnosti je dosaženo použitím digitálních certifikátů, šifrováním komunikace mimo pojišťovnu a víceúrovňového ověření partnera, se kterým komunikace probíhá. Protokol AS2 je založen na standardu HTTP a zprávách S/MIME. Soubor, který se prostřednictvím protokolu odesílá, je zakódován jako standardní příloha AS2 zprávy. Zpráva přenášená protokolem AS2 je vždy podepsána elektronickým podpisem – na rozdíl od synchronních služeb, kde se ověřuje pouze podpis. Systémy komunikující přes AS2 požadují potvrzení o doručení zprávy (MDN). Po úspěšném ověření podpisu je odesláno oznámení o doručení, které je opět elektronicky podepsané. Oznámení o doručení MDN v B2B nenese žádnou informaci o úspěšnosti přijetí nebo zpracování zprávy v koncových systémech interního informačního systému pojišťovny. Protokol AS2 je v současnosti uznávaným standardem pro komunikaci mezi partnery (B2B), o čemž svědčí jeho implementace ve většině B2B řešení. AS2 je schopen přenést jakýkoliv typ dat. Vlastní přenos funguje následujícím způsobem (viz obrázek 2): data jsou na začátku procesu opatřena elektronickým podpisem data jsou pomocí HTTPS protokolu přenesena adresátovi na straně příjemce je komunikace rozšifrována ověření elektronického podpisu zaručuje integritu přenesených dat odesilateli je zaslána potvrzující zpráva MDN
Komunikace je zahájena vytvořením AS2 zprávy. AS2 zpráva se vytvoří z připravených dat (např. vyúčtování zdravotní péče – soubor dávek). Obsah souboru je
9
načten, podepsán privátním klíčem nazev_certifikátu.p12 z privátního úložiště a zabalen do mime hlaviček. Tato AS2 zpráva (podání) se pošle na URL adresu pojišťovny. Na straně pojišťovny se ověří validita AS2 zprávy, dále zda je partner, který odesílá zprávu, v systému a jestli je integrita zprávy v pořádku a obratem odešle podepsané MDN se stavem zpracování. Poté je zpráva zaslána dál, přes B2B řadič na IPF. Když IPF zpracuje požadavek, vrátí zpět do B2B odpověď zabalenou do AS2 a odešle jako odpověď na klientovu URL adresu a obdrží zpět MDN. URL adresu, na níž se doručují odpovědi, si nastavuje klient pomocí B2B služby k tomu určené – nastavuje styl komunikace (AS2/SOAP) a v případě AS2 komunikace i IP adresu pro doručování odpovědí z pojišťovny.
Obrázek 2 Schéma AS2 komunikace
Mohou nastat následující modely komunikace: klient – pojišťovna – klient: Původcem komunikace je klient, který odešle pojišťovně podání. Pojišťovna obratem doručí potvrzení o přijetí (MDN). Po přijetí podání kontroluje pojišťovna správnost požadavku, provede zpracování a odešle klientovi jednu nebo více odpovědí (většinou je zasílána informace, že bylo podání předáno ke zpracování, a po zpracování, že bylo zpracováno). Lhůta na zpracování podání není předem stanovena a závisí na způsobu zpracování dat v pojišťovně. Po odeslání odpovědi klientovi obdrží pojišťovna ze strany klienta potvrzení o přijetí odpovědi na podání. pojišťovna – klient: komunikaci zahajuje pojišťovna odesláním zprávy jednomu nebo více klientů. Zpravidla se jedná o informační službu, tedy rozesílání informací
10
klientům. Po odeslání podání obdrží pojišťovna ze strany klientů potvrzení o přijetí zprávy (MDN). klient – pojišťovna: komunikaci zahajuje klient odesláním podání, pojišťovna potvrdí pomocí MDN úspěšné přijetí zprávy. Pojišťovna na zprávu neodesílá žádnou odpověď. pojišťovna – klient – pojišťovna: komunikaci zahajuje pojišťovna odesláním zprávy směrem ke klientovi a obdrží ze strany klienta potvrzení o přijetí v podobě MDN. Klient odešle podle svých možností odpověď a pojišťovna pomocí MDN potvrdí přijetí podání. V případě nemožnosti doručit klientovi zprávu proběhnou další čtyři pokusy o doručení odpovědi v předem stanovených časových intervalech. Následně je zpráva uložena jako nedoručená, lze ji však ručně (na žádost klienta, zpravidla když pominou důvody, proč nemohla být doručena) znovu odeslat. Celý proces komunikace popisuje obrázek 3. AS2 Adaptér je komponenta pro komunikaci pomocí AS2 protokolu na klientské straně. Servery B2B a IPF jsou v pojišťovně.
11
Obrázek 3 Zjednodušená komunikace pomocí AS2 protokolu
4.3. Hybridní služby Alternativní rozhraní je určeno pro menší partnery, jejichž infrastruktura není nepřetržitě připojena k internetu nebo nemá pevnou IP adresu. Asynchronní komunikace je nahrazena čtyřmi synchronními službami: •
odeslání zprávy
•
dotaz do pojišťovny, zda jsou již připraveny odpovědi na odeslaná podání
(Získání seznamu zpráv) •
12
stažení odpovědi
•
potvrzení načtení odpovědi
Tento soubor čtyř služeb nahrazuje komunikaci asynchronním protokolem AS2 včetně potvrzení o převzetí zprávy (obdoba Message Delivery Notification), zabezpečený přenos využívá standardu WS-Security. K odeslání původně asynchronní zprávy využije klient zabezpečenou službu „Odeslání zprávy“ a zároveň po provedení úspěšného odeslání zprávy se dozví v odpovědi, zda tato zpráva byla správně odeslána (proces probíhá v rámci jedné relace). Potvrzení o úspěšném převzetí zprávy B2B je podobný asynchronnímu přenosu protokolem AS2 službou „Potvrzení o načtení zprávy“. Vyzvednutí zprávy provede klient dotazem na webovou službu „Získání seznamu zpráv“, kdy služba klientovi odpoví v rámci jedné relace identifikátorem zprávy k vyzvednutí. Po vyzvednutí každé zprávy klient potvrdí, že zprávu převzal (Potvrzení o načtení zprávy). Potvrzení proběhne zavoláním webové služby s předáním identifikátoru vyzvednuté zprávy. Pokud klient nepotvrdí převzetí vyzvednuté zprávy, nebude povoleno vyzvednout další zprávu stejného typu. V případě nepotvrzení předchozí zprávy bude odpověď na požadavek vyzvednutí obsahovat výzvu k potvrzení. Každá zpráva bude opatřena elektronickým podpisem, který bude při příjmu ověřen. Na úrovni SSL komunikace je přenesen přístupový certifikát, který je zkontrolován a otisk je převeden do HTTP hlaviček na přepínači F5 BigIP. Následující komponenta B2B Proxy kontroluje, zda má partner oprávnění odesílat tyto zprávy. Pokud proběhne autentizace partnera v pořádku, je jeho požadavek předán komponentě WSSAdapter, který zajistí zpracování požadavku (a případně předání dalším komponentám). Vyřízená odpověď je synchronně předána volající službě a zobrazena na vstupu služby.
13
Kapitola 5 - Popis systémů ve zdravotní pojišťovně
Obrázek 4 Zjednodušená architektura serverů
14
Obrázek 5 Putování synchronních a asynchronních podání
5.1. Server F5 (LoadBalancer F5) Server F5 – Big IP plní na vstupu funkci load balanceru – tedy zařízení, které se chová jako reverzní proxy a distribuuje přenos mezi aplikacemi na více serverů. Load balancery jsou využívány pro zvýšení kapacity (souběžní uživatelé) a spolehlivosti aplikací, zvyšují celkový výkon aplikací snižováním zátěže na jednotlivých serverech spojených s provozem a údržbou aplikací. Load balancery zajišťují spolehlivost a dostupnost sledováním "zdraví" aplikací a odesíláním požadavků pouze na ty servery a aplikace, které mohou na požadavky reagovat včas. V závislosti na konfiguraci mohou také zajišťovat, aby prioritní aplikace byly obsluhovány nejdříve a přebírají zpracování od náročných serverových cyklů. Load balancery jsou obecně rozděleny do dvou kategorií: Layer 4 a Layer 7. Layer 4 load balancery distribuují požadavky na základě údajů zjištěných v síťových protokolech a transportních vrstvách (IP, TCP, UDP). Layer 7 load balancery distribuují požadavky podle údajů zjištěných v protokolu aplikační vrstvy jako je HTTP. Žádosti jsou přijímány oběma typy load balancerů a jsou distribuovány na konkrétní server na základě nakonfigurovaného algoritmu. Některé standardní algoritmy jsou:
15
Round robin
Weighted round robin
Least connections
Least response time Layer 7 load balancery mohou dále distribuovat požadavky na základě údajů
specifických pro danou aplikaci, jako je záhlaví HTTP, soubory cookie, nebo údaje v rámci samotné zprávy, jako je například hodnota určitého parametru. V tomto případě využívaná F5 - BIG IP také obsahuje řadu bezpečnostních funkcí proti útokům, které nelze jinde v síti zachytit - od ochrany proti útokům typu DoS, přes cloaking (maskování) zdrojů po odfiltrování aplikačních útoků. Představuje komplexní řešení, které lze integrovat se všemi aplikacemi, nejen aplikacemi na bázi webových protokolů (HTTP/S). Poskytuje centralizované řešení pro všechny IP aplikace, včetně původních (starších) aplikací nebo nových aplikací typu VoIP, vše v jednom unifikovaném systému. Podle jeho funkčnosti jej proto řadíme do typu Layer 7.
5.2. B2B Proxy B2B proxy zajišťuje zabezpečení proti přetížení aplikačních serverů a IPF. Tato komponenta zabezpečuje komunikaci pro synchronní i asynchronní služby. Pokud není komponenta funkční nebo nemá spojení s LDAPem a databázi, nemůže probíhat komunikace partnera přes B2B – nedojde k ověření klienta – B2B Proxy totiž zabezpečuje B2B bránu a zajišťuje autentizaci partnera konzumujícího požadované služby na základě atributu CN a FINGERPRINT, které F5 přidá do hlavičky HTTP požadavku. Oba atributy jsou vyňaty z certifikátu, který partner posílá v rámci navázané SSL komunikace, a slouží k jeho autentizaci. Tyto unikátní identifikátory partnera jsou vyhledány v LDAPu a v případě, že je nalezený příslušný pár (cn, fingerprint), je komunikace přeposlána na příslušný vnitřní endpoint. V případě, že záznam v LDAP není nalezen, odešle B2B Proxy http odpověď „401 Unauthorized“. Je-li třeba omezit přístup některého uživatele nebo skupiny, je omezení přístupu realizováno přes vstupní aplikaci B2BProxy. Pro komunikujícího partnera je možné nastavit časový interval od – do a konstantu, která určí, do jaké míry bude komunikace s
16
partnerem zpomalena nebo omezena.
5.3. Technologické adaptéry •
AS2Adapter – adaptér podporující asynchronní komunikační protokol
•
WSSAdapter – adaptér pro přístup partnerů využívajících přístupu přes
hybridní služby k asynchronním službám •
HTTPSOAPAdapter – adaptér umožňující klientsky komunikovat s
vnějšími synchronními endpointy. Pro správný běh B2B Serveru se definují názvy technologických adaptérů, se kterými bude dál server komunikovat na straně IPF. Názvy specifikovaných technologických adaptérů jsou použity k nastavení konzumenta zprávy vkládané do AQ. Konzumentem je příslušný technologický adaptér na integrační platformě, který předává zprávu dalšímu procesu ke zpracování.
5.4. Integrační platforma Integrační platforma se skládá z následujících skupin objektů: •
Procesy implementované v jazyce BPEL
•
Balíky PL/SQL procedur a funkcí uložených v databázi
•
Datový model
Většina činností Integrační platformy (IPF) je implementována ve formě procesů v BPEL. Některé z nich využívají funkcionalitu implementovanou v PL/SQL (komplexnější operace nad datovým modelem apod.). Existují činnosti Integrační platformy, které jsou implementovány čistě jako PL/SQL uložené v databázi; takové činnosti se odehrávají výhradně na úrovni databáze, nikoli aplikačního serveru (např. směrování toku zpráv v AQ). Integrační platforma disponuje dvěma datovými modely: •
Integrační cache, v databázovém schématu ICACHE databáze BPEL;
Integrační cache obsahuje zejména provozní data (údaje o směrování komunikace s Portálem, údaje o směrování toku zpráv mezi IPF a ZISem, údaje o zpracovávaných
17
požadavcích atd.). •
Online centrální registr smluv, v databázovém schématu OLCRS databáze
BPEL Asynchronní fronty pro komunikaci mezi B2B Serverem a IPF jsou definovány v závislosti na příslušném prostředí. Databáze B2B prostředí SIMU a PROD jsou rozděleny odlišnými schématy (B2BS, B2BP) se společnou databázi ICACHE na integrační platformě. Propojení AQ front na dvou různých Oracle serverech popisuje obrázek 6.
Obrázek 6 Komunikace pomocí AQ front
5.4.1 Směrování služeb v integrační platformě Na vstupu mohou zprávy přicházet (od partnerů) z několika adaptérů, nebo mohou být zprávy na typy subjektů, subjekty a partnery odesílány. B2B Proxy provádí směrování zprávy podle konfiguračních tabulek v B2B databázi (společně označováno jako B2B server). V případě odesílání zprávy může být doplněna informace o příjemci zprávy. Směrování se definuje vždy při přidání nového technologického adaptéru, typu zprávy nebo typu subjektu, který bude zprávy posílat/konzumovat. Komunikace mezi IPF a ZIS probíhá pomocí tzv. AQ front. AQ fronty slouží pro vzájemnou asynchronní komunikaci mezi interními aplikacemi. Komunikace probíhá pomocí Oracle Net Services, HTTP (S), a SMTP. Vzhledem k tomu, že Oracle AQ fronty jsou realizovány v databázových tabulkách, poskytují všechny provozní výhody vysoké dostupnosti, škálovatelnosti a spolehlivosti. Jsou podporovány všechny standardní databázové funkce, jako je obnova, restart a zabezpečení, fronty také mohou být importovány i exportovány.
18
Díky tomu, že spolu aplikace nekomunikují přímo ale pomocí AQ front, nedochází v případě nedostupnosti některé z aplikací k zahazování zpráv. Podání se tedy řadí za sebou do front v pořadí, ve kterém přicházejí, a pokud je cílová aplikace nedostupná, čekají, až bude dostupná a v daném pořadí jsou následně vyřizována.
5.5. BPEL BPEL Process Manager je standardním produktem firmy Oracle. JSP aplikace sloužící k ovládání a správě ORACLE BPEL Process Manageru, což je framework pro vykonávání BPEL procesů. BPEL je jazyk určený pro orchestraci (řízení součinnosti více služeb) end-to-end toku služeb. BPEL je založen na XML, SOAP, a WSDL. BPEL Process Manager slouží pro návrh, nasazení a správu obchodních BPEL procesů. Klíčové prvky BPEL Process Manager jsou:
BPEL Server - server, na kterém jsou nasazeny BPEL procesy. Tyto procesy jsou navrženy vývojáři a obsahují Human Workflow (HW), adaptéry a notifikační služby.
BPEL Console - konzole, ze které jsou spouštěny, testovány a spravovány BPEL procesy. Jedná se o webové rozhraní pro správu, administraci, ladění nasazených BPEL procesů. Můžete také spravovat BPEL Domény z Control BPEL.
Dehydration Store – Databáze pro ukládání BPEL schémat. BPEL doména: umožňuje vývojáři nebo administrátorovi rozdělit jednotlivé
instance Oracle BPEL procesů do několika virtuálních BPEL sekcí, jako například dev, test atd., viz obrázek 7. Při instalaci je založena pouze výchozí doména „default“. Pomocí třídění procesů do domén může být jiný proces pro produkční prostředí nebo pro testovací prostředí. Při velkém množství procesů je možné další třídění – na synchronní služby, asynchronní služby, služby určené pro určité aplikace. BPEL také umožňuje sledovat aktivitu, změny a chyby i filtrovat a zobrazit jednotlivá podání, zda skončila chybou nebo stále běží, případně byla vyřízena a ukončena.
19
Obrázek 7 Struktura podání v BPEL
5.6. Základní informační systém ZIS je souhrnné označení pro koncové aplikace pojišťovny, v nichž probíhá samotné zpracování podání a procesů. Aplikace jsou centralizované a rozdělené podle oblastí agend, které jsou v jednotlivých aplikacích zpracovávány – zpracování faktur od poskytovatelů zdravotních služeb, zpracování a příjem dávek od poskytovatelů zdravotních služeb, evidence smluv poskytovatelů zdravotních služeb, poskytování součinnosti soudním exekutorům, evidence zaměstnavatelů, OSVČ, pojištěnců atd. Z technického hlediska je možné se na ZIS dívat jako na skupinu autonomních aplikací, které komunikují s IPF pomocí AQ front. Tyto aplikace mají podle vnitřních předpisů předepsané pouze tabulky pro ukládání stavů podání (logování), které musí být přístupné pro vnitřní audity.
20
Kapitola 6 - Popis zpracování podání elektronických podání 6.1. Synchronní služby Klient odešle požadavek na synchronní službu, F5 BigIP (load balancer) předá požadavek na server, kde na B2B Proxy proběhne autentizace klienta a kontrola oprávnění. Pokud je přístup klienta v pořádku ověřen, požadavek je předán na integrační platformu, která jej předá dále do cílové aplikace základního informačního systému. Po zpracování požadavku putuje odpověď stejnou cestou zpět, tedy ze základního informačního systému do integrační platformy, na B2B proxy, server F5 BigIP a nakonec je zobrazena klientovi.
6.2. Asynchronní a hybridní služby 6.2.1 Služby podané přes Portál zdravotní pojišťovny Klient podává podání přes Portál pojišťovny, tedy se přihlásí do webového rozhraní Portálu. Podání následně odchází na aplikační server Portálu, potom teprve na server F5 BigIP (load balancer), ten předá požadavek na některý ze serverů, B2B Proxy ověří autentizaci klienta a provede kontrolu oprávnění. Podání je předáno na AS2 Adaptér, B2B Řadič, B2B konektor a potom teprve na integrační platformu, která jej předává cílové aplikaci základního informačního systému pojišťovny. Po zpracování podání putuje odpověď stejnou cestou zpět, tedy ze základního informačního systému do integrační platformy, na B2B server, server F5 BigIP a nakonec na aplikační server Portálu. Klient se následně přihlásí do webového rozhraní Portálu a zobrazí si odpověď.
6.2.2 Služby podané přes B2B rozhraní Asynchronní: Klient odešle podání ze svého software (tedy se nepřihlašuje do webového rozhraní Pojišťovny), následně podání odchází na server F5 BigIP (load balancer), ten předá požadavek na některý ze serverů, B2B Proxy ověří autentizaci klienta a provede kontrola oprávnění. Podání je předáno na integrační platformu, která jej předává cílové aplikaci základního informačního systému pojišťovny. Po zpracování podání putuje odpověď stejnou cestou zpět, tedy ze základního informačního systému do integrační platformy, B2B Server, server F5 BigIP a do
21
software klienta, kde si ji klient zobrazí. Hybridní: Klient odešle podání ze svého software (tedy se nepřihlašuje do webového rozhraní Pojišťovny), následně podání odchází na server F5 BigIP (load balancer), ten předá požadavek na některý ze serverů, B2B Server ověří autentizaci klienta a provede kontrolu oprávnění. Podání je předáno na integrační platformu, která jej předává cílové aplikaci základního informačního systému pojišťovny. Po zpracování podání putuje odpověď stejnou cestou zpět, tedy ze základního informačního systému do integrační platformy, na B2B Server, server F5 BigIP a do software klienta, kde si ji klient zobrazí.
22
Kapitola 7 - Datový sklad V současné době si mnoho firem uvědomuje, že firemní systémy obsahují množství dat, která firma neumí využít. Z tohoto důvodu se v posledních deseti letech rozmohla disciplína Business Inteligence a datové sklady. Úkolem Business Inteligence je umožnit řídícím pracovníkům firmy získat klíčové údaje pro chod firmy. Datové sklady jsou základním stavebním prvkem Business Inteligence. Pojem datového skladu se objevil kolem roku 1990. Za zakladatele datového skladu jsou dnes považováni W. H. Inmon a R. Kimbal. Tito dva muži položili základy datovému skladu, podle kterých se dnes tvoří datové sklady ve velkých podnicích. Hlavními prvky datového skladu jsou: Integrovanost – datový sklad integruje data z více systémů do jednoho celku. To ovšem neznamená, že by s vytvořením datového skladu měly tyto systémy zaniknout. Datové sklady většinou pracují s extrakty relevantních dat ze systémů, ale aplikace (nebo uživatelé) stále pracují s původním systémem. Doména dat – data v datovém skladu jsou rozdělena podle logické souvislosti a ne podle aplikace, ze které data původně pochází. Historizace dat – datový sklad velice často slouží jako podklad pro zprávy pro management podniku. Tyto zprávy sledují tendenci růstu/poklesu sledované veličiny. Proto je nutné v datovém skladu udržovat nejen aktuální data, ale data historická za předchozí transformace. Velice často se také používá několik druhů historizací najednou. Datová čistota – data do datového skladu velice často pocházejí ze systémů, které obsahují množství chyb (např. ručně zadávané formuláře). Z tohoto důvodu je nutné data v datovém skladu transformovat, nebo případně vyhodit celé záznamy, pokud by závažně porušily pravdivost výstupů z datového skladu.
23
Datová trvanlivost – data nahraná do datového skladu by se již neměla moc měnit. Ve velkých datových skladech bývá většinou pravidlem, že jsou data nahrávána ve větších intervalech. Takto nahraný „balík“ dat se poté zpracuje celý najednou. Dochází tak k většímu využití databázového stroje a šetření času oproti malým a častým přepočtením dat.
7.1. Architektura datového skladu V následující části práce bych rád popsal základní rozdělení datového skladu. Martin Fowler ve svém článku Who Needs an Architect řekl: „Architektura je o podstatných věcech. Ať už je to cokoliv.“[1]. Proto si myslím, že je dobré pro začátek uvést, co myslím pod tímto pojmem. Architekturu v datovém skladu je podle mě možné rozdělit na dvě velké části. První částí je definování a dodržování domény datového typu pro celý sklad. Při návrhu datového skladu přicházíme do styku s informacemi z rozdílných aplikací. Tyto aplikace většinou navrhovali rozdílní lidé, proto není výjimkou, že tito lidé pojmenují stejné věci různými slovy. Je proto velice důležité, aby datový sklad unifikoval tyto názvy, pokud se používají ve více místech a zavedl jednotná pravidla, kdy se tyto názvy mohou použít a co přesně znamenají. Pokud budou všichni uživatelé datového skladu tyto pojmy a jejich definice znát, je pak velice nepravděpodobné, že by vznikaly problémy s komunikací jenom na základně rozdílných pojmenování jedné věci. Druhou důležitou věcí při navrhování datového skladu je rozdělení datového skladu do jednotlivých oblastí. Protože datové sklady jsou skoro vždy realizovány pomocí databáze, je dobré toto rozdělení udělat pomocí databázových prostředků, jako například schéma v Oracle, nebo databáze v Teradatě. Pro jednotlivé oblasti je také dobré definovat uživatele, kteří mají do dané oblasti přístup, a kteří mohou danou oblast upravovat. Na první pohled se toto rozdělení zdá zbytečné, protože nepřináší žádný praktický užitek, ale pokud se datový sklad rozroste, je správa takto rozdělených oblastí mnohem jednodušší, než jedna velká oblast pro všechna data.
7.1.1 Dvouvrstvá architektura První a nejjednodušší rozdělení datového skladu je rozdělení pomocí dvouvrstvé
24
architektury. Tato oblast obsahuje následující oblasti: Stage oblast Mart oblast Stage oblast slouží pro vstupní data, která se dostávají do datového skladu. Je dobré, aby se data v této oblasti uchovávala přesně tak, jak jsou ve zdrojových systémech, včetně datových typů i názvů. V této datové oblasti většinou neplatí architektonická pravidla pro názvy a datové typy, jako platí pro zbytek datového skladu. Data v této datové oblasti jsou uchovávána podle potřeby, ale většinou pouze po dobu dalšího načtení dat z aplikace. Do této datové oblasti se nahrávají data většinou pomocí ETL nebo ELT nástrojů. Tyto nástroje umožňují nahrávat data mezi heterogenními prostředími, nejčastěji pomocí tří typů připojení: nahrání ze souboru nahrání pomocí datové pumpy nahrání pomocí relačního připojení Mart oblast slouží pro výstupní extrakty, které dodává datový sklad. Data jsou ze Stage oblasti většinou transformována pomocí SQL skriptů, nebo ETL nástroje. ETL nástroj je pouze grafickou nadstavbou pro SQL skripty, nebo se využívá k postupnému spouštění a plánování následujícího běhu SQL skriptů. Pro tuto oblast už platí pravidla pro datové typy a pojmenování entit a jejich atributů podle metodik datového skladu. Uživatelé mají buď přímý přístup do datového skladu (power users), nebo se data pomocí ETL nástroje dostávají z datového skladu ven. Nebývá obvyklé, že by do datového skladu měly přístup přímo aplikace. Je to z toho důvodu, že datové sklady jsou většinou stavěny nad speciálními databázemi, které mají upravenou výpočetní architekturu. Tyto databáze většinou velice zatěžuje, pokud se do nich pouštějí sice malé dotazy, ale v malých časových intervalech. Toto chování je obvyklé pro aplikace, které si potřebují načítat data z datového skladu. Proto se těmto aplikacím data přenášejí do jiného systému, mimo datový sklad.
7.1.2 Třívrstvá architektura
25
Čím více se datový sklad rozrůstá, tím více se stává, že se některé věci při transformaci ze stage oblasti do mart oblasti počítají duplicitně. Je také nutné začínat si držet u určitých entit i historické informace. Z tohoto důvodu vznikla potřeba pro třetí vrstvu, která se vyskytuje mezi stage a mart oblastí. Této oblasti se nejčastěji říká jádro datového skladu (tzv. core). Data, která se přenášejí do jádra datového skladu, jsou pro organizaci důležitá – neplatí proto, že by se všechna data, která se nahrávají do stage oblasti, dostala do jádrové oblasti. Pro oblast jádra datového skladu platí většinou nejpřísnější metodiky a je nutné, aby jejich integrace do jádra datového skladu byla schválena oddělením, které má tuto agendu v organizaci na starosti. Při transformaci dat do jádra datového skladu se rozděluje transformace minimálně na dvě fáze. První fáze transformuje data ze stage oblasti, čistí je a převádí na jednotnou doménu. Tato fáze bývá technicky pro databázi nejnáročnější, protože se velice často stává, že data nejsou uložena ve stejných formátech a při spojování záznamů je nutné upravovat vstupní data. Záznamy, které porušují datovou kvalitu, jsou odstraněny, nebo jejich chyby jsou anulovány na defaultní hodnotu. Pokud by například záznamy o čtyřstopých motorových vozidlech obsahovaly záznam o motocyklu, je možné tento záznam vyřadit ze zpracování, nebo opravit na neznámé čtyřstopé motorové vozidlo. Toto rozhodnutí záleží konkrétním odběrateli dat. Při návrhu je nutné brát v potaz rozložení dat v databázi a správně využívání indexů. V praxi se často stává, že kvůli chybějícím nebo nesprávně navrženým indexům trvají transformační skripty několikanásobně déle, než je potřeba. Správným navržením indexu je možné zkrátit SQL dotaz z 56 minut na 10 vteřin. Druhou fází je fáze historická. Tato fáze probíhá mezi napočtenými daty z předchozí
26
fáze.
Jak
jsem
v úvodu
kapitoly
Datový sklad uvedl, některé entity v datovém skladu je nutné sledovat i zpětně do historie. Mezi dva hlavní typy historizace považuji historizaci „od – do“ a historizaci k datu. V datových skladech bývá obvyklé, že se data upravují, případně se vyřazují celé záznamy, pouze v první fázi zpracování. Toto odůvodnění bývá proto, že někteří odběratelé dat potřebují data dříve, než po konci historické transformace. Tito odběratelé většinou vytvoří své vlastní asynchronní větve, ve kterých používají stejných metrik výpočtu, jako se používá v první fázi hlavní transformace. Aby se nemohlo stát, že data po ukončení historické transformace budou rozdílná od těch, které si odběratelé vypočetli sami, je všechna validace a korekce pouze v první části transformace. Historizace „od – do“ se provádí vytvořením nové entity. Velice často je název historické entity shodný s podkladovou entitou až na předponu nebo příponu. Nová entita musí obsahovat primární klíč podkladové entity a dva sloupce s platností, v jakém období byl platný stav podkladové entity. Následují sloupce, které je u dané entity nutné historicky sledovat. Pokud se v podkladové entitě změnila hodnota některého ze sledovaných sloupců, uloží se celý stav do historické entity se zaznamenanou platností. Záznamy v historické entitě můžeme rozlišit na aktuální platné a historické. Aktuální platné záznamy jsou shodné se záznamy v podkladové entitě. Tyto záznamy mají podle metodiky konkrétního datového skladu označený konec platnosti na tzv. nekonečno (může být NULL hodnota, skutečné datum v budoucnosti…). U často měnících se entit v datovém skladu je dobré udržovat jejich denní stav. Nová, historická entita, má přidané pouze jedno pole. Toto pole je datem platnosti daného záznamu. Tato historizace má výhodu v tom, že je technicky jednodušší. Všechny záznamy z původní entity se převedou do historické s aktuálním datem platnosti. Protože se v datovém skladu může stát, že je nutné transformaci znovu opakovat, je praktické před plněním historické entity dát odmazání záznamů za aktuální datum, aby byla odmazána data z předchozí transformace, která běžela ve stejný den. Při první transformaci toto odmazání nic nedělá, ale při každé další není nutné brát v potaz stávající, nejspíše chybně napočtené, záznamy. U každého typu historizace je nutné počítat s tím, že je velice náročná na místo v databázi. Není proto dobré historizovat všechny entity, ale pouze ty vybrané. Je také
27
nutné u každé historické entity určit, po jakou dobu má být historizována a jak se bude provádět odmazávání starších záznamů.
7.1.3 Rozšířená třívrstvá architektura S rostoucím datovým skladem je nutné jednotlivé části třívrstvé architektury rozdělit na menší funkční celky, aby nedocházelo k omezování funkce datového skladu. V této kapitole bych chtěl popsat rozdělení, se kterým jsem se ve své praxi v datových skladech setkal. První rozdělení se týká stage oblasti na tři části: prestage, stage a poststage. Při nahrávání informací pomocí datových pump je přístup k těmto tabulkám pro uživatele omezen. Nahrávání probíhá v jedné transakci, takže jakýkoliv dotaz během nahrávání musí počkat, dokud transakce, která nahrává data, neskončí. Toto chování je nežádoucí, pokud uživatelé berou ve svých výstupních datech podklady ze stage oblasti a musí tedy na data čekat. Jednoduché řešení je přidat novou datovou oblast, prestage. Tato oblast slouží pouze pro nahrávání nových dat do databáze. Nahrávání dat do databáze je většinou velice pomalé, protože stroj, na kterém běhá datový sklad, bývá většinou několikanásobně výkonnější než stroje, které datovému skladu data poskytují. Proto se data nejdříve nahrají do prestage oblasti. Jakmile se všechna data naplní, přenesou se v rámci databáze do stage oblasti. Tento přenos je řádově rychlejší, než nahrávání z podkladových systémů. Přenos do stage oblasti se přenáší v rámci jedné transakce, takže není možné, aby uživatel skladu používal nekonzistentní data. Tyto transakce většinou trvají řádově sekundy, takže uživatel nezaznamená zamknutí podkladů. Nahrávání ze zdrojových systémů může trvat i jednotky hodin, kterých si již uživatelé všimnou. Poststage oblast slouží k nahrání dat potřebných pro jádro datového skladu. Důvodem vytvoření této oblasti je, že zdrojové systémy většinou nesynchronizují posílání vstupů do datového skladu s transformacemi datového skladu. Proto je nutné rozdělit systémy na důležité zdrojové systémy a ostatní. S důležitými zdrojovými systémy uzavírá sklad Service Level Agreement (dále jen SLA). SLA je pojem, který se využívá v ITIL metodice, která popisuje, jak by mělo fungovat oddělení, které poskytuje v organizaci služby. SLA se uzavírá mezi dvěma stranami, v našem případě je první stranou dodavatel dat (zdrojový systém) a druhou stranou datový sklad. Dodavatel dat se v SLA smluvně
28
zavazuje poskytovat data v potřebné kvalitě a ve sjednaném čase. Porušení SLA se poté řeší v rámci vnitřních metodik organizace. Ostatní zdrojové systémy nebývají pro datový sklad kritické, proto je možné dodání dat vynechat a počítat transformaci nad starými daty. Před začátkem transformace do jádra datového skladu se uzamknou všechny tabulky v stage oblasti a přenesou se do poststage oblasti. Během tohoto přenosu jsou všechny zdrojové tabulky uzamčené (většinou pro operace insert, delete a update, nikoliv pro select), takže není možné, aby byly přehrány. Po přenosu se zdrojové tabulky odemknou a je možné s nimi dále manipulovat. To zaručuje, že není možné, aby zdrojový systém nahrál nová data v době počítání transformace do jádra datového skladu a tyto informace byly zahrnuty v již započaté transformaci. Druhé rozdělení se týká jádra datového skladu. V jádru je umístěno největší množství dat, takže transformace jsou nejvíce časově náročné. Datový sklad slouží přes den pro uživatele, kteří se do něj dotazují, takže je obvyklé, že během doby, kdy v organizaci aktivně pracuje největší část zaměstnanců, slouží datový sklad převážně pro ně. Není proto technicky možné stihnout hlavní a historickou transformaci najednou. Proto se jádro datového skladu rozdělí na dvě části. První část obsahuje pouze přírůstky napočítané za poslední transformaci, druhá část obsahuje jádro datového skladu. Hlavní transformace probíhá ráno hned po dokončení nahrání dat ze všech důležitých systémů. Zde se odehrává největší očištění dat. Tato hlavní transformace bere data ze stage oblasti (nebo poststage oblasti, pokud jsou stage oblasti rozděleny) a plní pouze přírůstkovou část. Historická transformace probíhá ve večerních hodinách, kdy není zatížení skladu tak vysoké. Po historické fázi se plní výstupní oblast (dále data mart) daty. Toto řešení vede na datový sklad s daty se zpožděním D-2. To znamená, že data odebíraná z jádra datového skladu jsou dostupná dva dny po dodání do datového skladu. Data, které je nutné získat dříve (tedy D-1), se neintegrují do datového skladu, a putují asynchronně na hlavních transformacích rovnou ze stage oblasti (nebo přírůstkové oblasti jádra) do data mart oblasti. Existují i datové sklady, které dodávají data se zpožděním D-1. V tomto případě běží obě hlavní transformace za sebou v uživatelském okně (pokud tyto sklady mají uživatelské okno). Pro tyto sklady bývá velmi kritické definování služeb, které budou poskytovat a na které budou mít uzavřené SLA. Pokud se při transformaci datového skladu
29
vyskytne chyba, ať již v datech, nebo v transformačních skriptech, musí mít datový sklad připravený plán na to, jak chybu co nejrychleji odstranit. U datových skladů se zpožděním D-2 nebývají tyto chyby tak kritické, protože je možné je opravit následující den, aniž by se data na výstupu dostala později, než by měla.
7.2. Možnosti ukládání dat v datovém skladu Bez ohledu na to, na kolik vrstev je datový sklad rozdělen, je nutné si co nejdříve po vytvoření datového skladu říci, jak budou data v datovém skladu uložena. Datové sklady se proto dělí na dvě skupiny: Multidimenzionální datový sklad (podle R. Kimbala) Relační datový sklad (podle W. H. Inmnona) Relační
Multidimenzionální
Vysoký výkon
NE
ANO
Rychlejší na implementaci
NE
ANO
Podpora postupného rozvoje
ANO
NE
Robustnost
ANO
NE
Flexibilnost
ANO
NE
Škálovatelnost
ANO
NE
Tabulka 1 Srovnání vlastností datového skladu[2]
7.2.1 Relační datový sklad Relační datový sklad vychází z klasického návrhu databázového systémů podle tří normálních forem. 1. normální forma – všechny atributy relace jsou atomické. V praxi tato norma znamená, že v žádné tabulce nesmí existovat sloupec, jehož hodnoty by se skládaly z více hodnot. Typickým zástupcem je například adresa v databázi. Ta může, ale také nemusí, porušovat první normální formu, záleží na návrhu architekta dané adresy. Pokud budeme adresu používat pouze jako celek, je možné mít adresu vcelku včetně čísla popisného, orientačního, PSČ a dalších atributů, které si můžeme chtít u adresy udržet.
30
Pokud ale budeme potřebovat samostatně používat jednotlivé atributy adresy, je nutné adresu rozložit na potřebné členy, a poté s nimi dále pracovat. 2. normální forma – relace splňuje 1. normální formu a zároveň všechny atributy relace jsou závislé na celém primárním klíči. Tato norma je automaticky splněna, pokud existuje pouze jeden sloupec v primárním klíči. Pokud existuje více sloupců v primárním klíči, je důležité udržet tuto integritu. Pokud některý atribut relace tuto integritu porušuje, vede normalizace většinou na rozpad na více tabulek. 3. normální forma – relace splňuje 2. normální formu a zároveň žádný z atributů není transitivně závislý na primárním klíči. Tato norma bývá nejtěžší na pochopení podle definice, ale je jednoduché ji pochopit na příkladu. Mějme tabulku s atributy idOsoby, pozice a plat. Pro zjednodušení uvažujme, že plat je závislý pouze na pozici, a nepočítejme do něj žádné bonusy. Je tedy patrné, že tabulka není ve 3. normální formě, protože plat není závislý na idOsoby, ale na pozici. Normalizace relací při porušení této formy vede na rozpad relací do více relací. Existuje více normálních forem, které se používají při databázovém návrhu: BoyceCoddova, 4. normální forma, 5. normální forma atd… Tyto formy nejsou tolik časté při databázovém návrhu, nejčastější je navrhování podle 3. normální formy. Tato forma má několik výhod: snadná na zavedení a udržení v databázi neobsahuje redundantní data pokud je dobře dodržena metodikou pojmenování, zavádí do databázového systému jednu verzi pravdy Nevýhody této formy jsou: velký počet tabulek v databázi obtížnější orientace v databázi
31
těžší psaní dotazů pro získání relevantní odpovědi. Pojmy redundance a jedna verze pravdy jsou pro datový sklad velmi důležité, protože je nutné s nimi při návrhu skladu již dopředu počítat. Databázové sklady obecně pracují s velkým množstvím dat, proto mohou data při špatném návrhu a redundanci zbytečně narůstat. Často se ale v datovém skladu (i obecně při návrhu databázových systému) setkáváme s úmyslnou redundancí. Typickým zástupcem bývají tabulky obsahující jméno a příjmení osoby. Kdybychom se měli držet striktně 3. normální formy, měli bychom zavést číselníky jmen a příjmení a do tabulky osoby uvést pouze odkazy na tyto číselníky. Výhodou by bylo, že bychom se nemohli dopustit překlepu při zadávání jména nebo příjmení. Nevýhodou by v tomto případě ale bylo, že získat pouhý seznam osob z tabulky by znamenal k tabulce s osobami připojit také dva další číselníky, což by bylo zbytečně složité. Jedna verze pravdy je v datovém skladu velice důležitý pojem. Velmi často se ve velkých společnostech stává, že uvádějí stejné pojmy, ale myslí tím úplně něco jiného. Pokud jsou data v datovém skladu, je důležité udržet v datovém skladu pravidlo, že pokud se dva sloupce jmenují stejně, znamenají také to samé. Při dodržení tohoto pravidla pak není možné, aby se při výstupních reportech z datového skladu dostávala stejně pojmenovaná agregovaná data s různými hodnotami.
7.2.2 Multidimenzionální datový sklad Druhým
možným
způsobem
jak
stavět
datové
sklady
je
pomocí
multidimenzionálních tabulek. Tomuto přístupu se také říká architektura podle R. Kimbala. R. Kimbal přišel s tím, že datový sklad podle W. H. Inmnona je pro uživatele nepřehledný. Stavění skladu v 3. normální formě zvyšuje počet objektů v databázi. Vyšší počet objektů v databázi tvoří více práce pro orientaci v datovém skladu a také více práce pro vývoj skriptů pro získávání dat z datového skladu. Jeho přístup staví datové sklady více pro uživatele. Data předpočítává dopředu do tabulek bližších výstupním reportům. Tento přístup má pro datový sklad dvě velké výhody. První výhoda je rychlost tvorby datového skladu. Protože odpadá nutnost normalizovat objekty v datovém skladu, je čas strávený nad analýzou modelu datového skladu kratší, než kdyby se pro každý model musela dělat analýza, jestli je správně v 3. normální formě nebo není. Objekty se navrhnou podle používání rovnou pro uživatele
32
datového skladu a dále se více nemění. Pokud potřebuje jiný uživatel data z podobných podkladových tabulí o jiném obsahu, vytvoří se nový objekt, který uspokojí potřeby dalšího uživatele. Druhou výhodou je výkon skriptů spouštěných nad datovým skladem. Protože data jsou již dopředu napočítána, není nutné znovu spojovat tabulky k sobě, což je při výpočtech nejdražší operace. Tato vlastnost podstatně zkracuje čas strávený při počítání spojení a uživatelé získávají svá data rychleji, než kdyby byl sklad postaven v 3. normální formě. Hlavní nevýhodou takto postaveného datového skladu je nemožnost používat objekty pro více uživatelů, pokud se liší jejich potřeby pro použití a agregaci dat. Kvůli tomu je nutné velmi často přepočítávat podobné veličiny vícekrát a v datovém skladu se vytrácí princip jediné pravdy. Je tedy možné, že různí uživatelé budou získávat z datového skladu různé informace, které budou interpretovat ale jako jednu informaci, která se rozchází v hodnotách. Mezi další nevýhody takto postaveného datového skladu patří také nemožnost dále rozšiřovat objekty v datovém skladu. Protože jsou objekty velmi často předagregovány, je další rozvoj velice obtížný, protože je nutné brát v potaz potřeby konkrétního uživatele, pro kterého byl objekt vytvořen. Tito uživatelé se mohou postupem doby v datovém skladu měnit, proto je velice časté, že noví uživatelé nevědí, jak metriky, podle kterých objekty ve skladu vznikly, fungují a jestli je možné do nich přidat nová data, která by mohli využívat. Velmi často se v takto postavených datových skaldech stává, že se nové hodnoty k původním „přilepí“ jako nový objekt. Tyto dva objekty se poté v případě potřeby k sobě spojí. Tím ale zaniká princip multidimenzionálního skladu a přechází se pomalu k relačnímu datovému skladu.
7.2.3 Uspořádání dat v datovém skladu v praxi V praxi se málokdy stává, že by datový sklad fungoval pouze podle jedné architektury, jak ukazuje obrázek 8. To je dáno funkcí jednotlivých částí datového skladu popsaných v kapitole 7.1 Architektura datového skladu.
33
Obrázek 8 Reálná architektura datového skladu
Stage oblast přebírá data ze zdrojového systému. Proto se velmi často stává, že bývají části stage oblasti podle relačního modelu a další části podle multidimenzionálního. S tímto faktem se v praxi nedá nic dělat a je nutné jej akceptovat, protože datový sklad by v této oblasti neměl měnit fyzickou strukturu dat. Pokud datový sklad zachovává strukturu zdroje (ideálně i stejná pojmenování sloupců), je velice jednoduché se zdrojem komunikovat případné chyby na zdroji a jejich projevení se v datovém skladu. Kdyby datový sklad změnil strukturu dat, nebo případně pojmenování sloupců, je pak těžší komunikace se zdrojem, protože obě strany používají jiné pojmy pro stejné věci. To ztěžuje komunikaci a prodlužuje dobu opravy problémů. Jádro datového skladu většinou slouží jako základní stavební prvek datového skladu, do kterého je velice omezená možnost přímého přístupu. Protože slouží jako hlavní stavební prvek, je důležité, aby bylo jádro konzistentní a nedávalo falešné informace. Z tohoto důvodu se při navrhování jádra datového skladu používá relační architektura. Tato architektura zajišťuje, že se v jádře každá informace vyskytuje právě jednou a že se v datovém skladu nevyskytují žádné redundance. Tato vlastnost je také velice důležitá, protože v jádru datového skladu je umístěno nejvíce informací. Databázové stroje, na kterých se datové sklady staví, jsou nejdražší databázové stroje, jejichž upgrade se může vyšplhat i do stovek miliónů korun, takže není z ekonomického hlediska vhodné upgrade provádět příliš často. V jádru datového skladu se také většinou nepočítá jedna vlastnost objektu dvakrát, často se využívá předpočítaných hodnot ve více tabulkách. Pokud se toto
34
pravidlo dodržuje, odpadá jedna z nevýhod relačního datového skladu – malá výkonnost. Výstupní oblast datového skladu, data mart, bývá nejčastěji rozdělen pro jednotlivé uživatele (oddělení). Protože každý uživatel má od datového skladu jiné požadavky, které většinou potřebuje jednorázově a rychleji, je data martová oblast stavěna pomocí multidimenzionálního schématu. Uživatelé datového skladu velmi zřídka potřebují z datového skladu surová data. Většinou se jedná o výstupní charakteristiky, agregované hodnoty, které jim
pomáhají
při
rozhodnutí
ve firmě. Z tohoto
důvodu
se
multidimenzionální schéma přímo nabízí. Protože jsou použita stejná podkladová data pro stejné výpočty, minimalizuje se riziko „dvojí pravdy“. Tyto výstupy jsou také velice málo měnné, odpadá tedy nevýhoda postupného rozvoje. Pokud je nutné data jakkoliv rozšířit, zadavatel je velice často také uživatel, proto ví, jak s daty pracuje a je velice jednoduché dělat pro konkrétního uživatele úpravy. Protože jsou jednotlivé data marty rozděleny, odpadá starost o ovlivnění dat v jiném data martu. Aby bylo docíleno neovlivnitelnosti jednotlivých data martů, je nutné dodržovat pravidlo, že data mart nesmí nikdy odebírat data z jiného data martu. V praxi se toto pravidlo striktně dodržuje, ale často se stává, že jednotlivá oddělení potřebují spolu koexistovat a proto se změny na jedné straně musí projevit i na druhé straně. Z tohoto důvodu vznikly v datovém skladu super marty a shopy. Super mart je rozšíření data martu. Spolupracující data marty se vloží do jednoho super martu. Tento super mart obsahuje pouze data společná pro spolupracující data marty. Marty mají pořád zakázáno odebírat data mezi sebou. Jediná interakce, která mezi marty probíhá, je prostřednictvím super martu. Tato architektura je velice jednoduchá na vytvoření, ale jednotlivé marty většinou postupem doby sdílejí více dat a nakonec dojde k tomu, že zůstává pouze super mart bez vnitřního rozdělení, což bývá velice složité na údržbu, protože není předem jasné, kde všude se případná oprava dat může projevit. Z tohoto důvodu se rozvinul super mart do tzv. shopu. Shop sdílí data pro více výstupních uživatelů, ale jeho rozdělení bývá spíše podle logického rozdělení dat. Jednotlivé oblasti se proto nerozdělují podle uživatelů dat, ale podle funkce. Nejčastější dělení shopu je na vstupní a výstupní oblast. Pokud je potřeba, přidává se oblast obsahující záznamy o chybách a opravách dat. Architektura shopu je mnohem složitější než architektura super martu, ale protože jsou data lépe oddělená, je pro údržbu datového
35
systému mnohem jednodušší identifikovat případné dopady při nutné opravě dat. Cíloví uživatelé datového skladu také často neumí SQL jazyk pro získávání informací z datového skladu – z toho důvodu se využívá nějakého integračního nástroje, který data transformuje z databáze do přívětivější formy pro uživatele – např. cvs soubor oddělený středníkem. Tato forma je uživateli velmi oblíbená, neboť ji umí zpracovat kancelářský software. Kvůli tomuto omezení v mart oblast bývá také časté, že data v mart oblastech nejsou fyzicky uložena, ale využívá se pouze views nad zdrojovými objekty. Doba výpočtu výsledku v pohledu bývá stejná jako doba výpočtu pomocí SQL skriptu. A protože jsou data často reprezentována v jiné než SQL formě, je výhodné používat pohledy a šetřit tak místo v databázi. Tento přístup řeší velkou nevýhodu multidimenzionálního datového skladu – redundanci dat.
7.3. Schémata v multidimenzionálním datovém skladu 7.3.1 Star schéma
Obrázek 9 Star Schema
Star schéma (viz obrázek 9) je nejjednodušší schéma, které se používá v multidimenzionálním datovém skladu. Jedná se o denormalizované schéma, skládající se z dvou typů tabulek:
36
faktová tabulka tabulka dimenzí Faktová tabulka je nositelem informací ve star schématu. Velice často má primární klíč tvořený pouze z cizích klíčů do tabulek dimenzí. Dále tato tabulka obsahuje jednotlivé sledované údaje (metriky) pro daný fakt, který tabulka představuje. Tabulka dimenzí obsahuje údaje, které rozdělují tabulku faktů na jednotlivé části. Funkce těchto dimenzí je nejjednodušší vysvětlit na jednoduchém příkladu. Firma, která prodává auta po celé České republice, potřebuje mít informace o prodeji v krajích za jednotlivé měsíce. Schéma prodeje zobrazuje Obrázek 10.
Obrázek 10 Prodej automobilů - star schéma
Tabulka Prodej_Automobilu je v tomto příkladě faktová tabulka. U této tabulky lze sledovat kolik kusů se prodalo, kolik byly příjmy z prodeje a kolik z těchto příjmů činí zisk. Granularitu těchto záznamů určují tabulky dimenzí: Kraj, Mesic_V_Roce, a Znacka. Výhody star schématu:
37
jednodušší dotazování díky menšímu počtu tabulek k připojování rychlé agregace díky předagregovaných hodnotám jednoduchá struktura k pochopení zjednodušené reportování oproti normalizovaným strukturám většina OLAP nástrojů používá Star schéma jako vstup do datových kostek Nevýhody star schématu: složitější plnění kvůli nutnosti agregovat záznamy agregací se ztrácí původní význam záznamů omezení použití (pokud se změní způsob používání, je nutné přemodelovat i jednotlivé dimenze a faktovou tabulku)
7.3.2 Snowflake schéma
38
Obrázek 11 Snowflake schéma
Snowflake schéma(viz obrázek 11) je rozšíření star schématu. V tabulkách dimenzí ve star schématu dochází velice často k denormalizaci, která celé použití star schématu dělá složitým. Takové denormalizace je možné si všimnout na obrázku Obrázek 10 Prodej automobilů - star schéma. V tomto obrázku je schválně u tabulky „Značka“ použitý silně denormalizovaný tvar u sloupců ID_Znacky, Model, Rok_Vyroby. Je vidět, že by bylo vhodnější použít normalizovaný tvar pro tuto dimenzi, který by zlepšil čtení schématu a přitom odstranil redundantní hodnoty. Star schéma ale vyžaduje, aby tabulka pro jednotlivé dimenze byla pouze jedna. Pokud bychom normalizovali dimenzi rozložením na více tabulek, nejednalo by se již o star schéma, ale o snowflake schéma. Jednu z možných normalizací popisuje obrázek 12.
39
Obrázek 12 Prodej automobilů - snowflake schéma
Jak je vidět, že tabulka dimenzí „Znacka“ se změnila na „Automobil“ a přibyly dvě nové tabulky do dimenze Automobil: Model a Typ. Tato normalizace dimenze vedla k zavedení referenčních vazeb na model a typ, a také vedla k opakování modelu v tabulce Automobil. Je dobré si všimnout, že převod ze star schématu na snowflake schéma, se nijak nedotkne tabulky faktů. Výhody snowflake schématu:
zavádí normalizaci do tabulek dimenzí
umožňuje zavedením vazeb pomocí cizích klíčů hlídat referenční integritu záznamů
40
snížení opakování některých hodnot ve sloupcích v tabulkách dimenzí
pořád lze použít jako podklad pro kostky pro OLAP nástroje
umožňuje udržovat integritní omezení na jednotlivé dimenze pomocí cizích klíčů
Nevýhody snowflake schématu:
složitější na vytvoření na udržování
potřeba vyššího počtu tabulek pro jednu faktovou tabulku
složitější na orientaci
41
Kapitola 8 - Využití integrace systémů pomocí architektury datového skladu 8.1. SQL4SQL SQL4SQL je koncept generování SQL skriptů pomocí SQL jazyka. Tento koncept se využívá zejména u databází, které nenabízejí procedurální jazyky, jako například Oracle a PL/SQL. Protože jsem se ve své praxi datových skladů setkal především s těmito databázemi, chtěl bych tento koncept využít i zde v integraci systému. Výhody tohoto konceptu jsou: přenositelnost, protože se většinou využívá ANSI SQL, které se dá použít na skoro všech relačních databázích jednoduchost čitelnost Nevýhody: nutnost exportovat výsledek do souboru nutnost pouštět SQL skript z exportovaného výsledku Pro zavedení SQL4SQL je potřeba mít v systému zavedené metadatové tabulky, které obsahují informace o systému samotném. Tyto tabulky jsou vhodné nejen pro SQL4SQL, ale také pro údržbu samotného systému. Proto se vyplatí tyto metadatové tabulky zavádět co nejdříve, aby se jejich řešení pouze rozvíjelo. Velmi mnoho velkých systémů a datových skladů metadatová řešení zpočátku přehlížejí a jejich dodatečné postavení bývá obtížné a velmi drahé.
Obrázek 13 Metadatová tabulka s objekty v DB
Tabulka na obrázku 13 obsahuje jako primární klíč sloupce Database_Name se
42
jménem databáze, kde se objekt nachází, a Table_Name s jménem tabulky. Pokud nad touto tabulkou spustíme následující dotaz: select 'delete from '||trim(Database_Name)||'.'||trim(Table_Name)||';' from Tables where Database_Name = 'ktl_stage';
Získáme odpověď například takovouto: Column_1 ------------delete from ktl_stage.PBIPFPORTALMAP; delete from ktl_stage.B2B_Zprava;
Tato odpověď se může různit od programu pomocí kterého se budeme k databázi připojovat, ale vždy bude obsahovat řádky, které samy o sobě nejsou pro puštění vhodné. Protože integrace systému v ZP probíhá pomocí Oracle databáze, je nutné opravit dotaz tak, aby nevracel název sloupce a oddělovač. To provedeme pomocí příkazů: SET HEADING OFF; SET FEEDBACK OFF; SET TERMOUT OFF; SET ECHO OFF;
Dále je nutné zajistit, aby se výsledek dotazu nezobrazil v programu, pomocí kterého se připojujeme do databáze, ale do souboru. Toto nastavení se provádí pomocí příkazu SPOOL. Celý skript by poté mohl vypadat takto: SET HEADING OFF; SET FEEDBACK OFF; SET TERMOUT OFF; SET ECHO OFF;
43
SPOOL mydir/deleteScript.txt; select 'delete from '||trim(Database_Name)||'.'||trim(Table_Name)||';' from Tables where Database_Name = 'ktl_stage'; SPOOL OFF; Výsledek skriptu bude poté vypadat takto: delete from ktl_stage.PBIPFPORTALMAP; delete from ktl_stage.B2B_Zprava;
Jak je vidět, tento výstup se dá již použít jako skript k provedení. V integraci je tento způsob využit ke dvěma činnostem: Generování skriptů pro datové pumpy Odmazávání starých podání
8.1.1 Generování skriptů pro datové pumpy Datové pumpy se využívají při přenosu velkého množství dat mezi jednotlivými databázemi. Skoro všechny velké databáze mají nástroj, kterým se dají přenášet velké bloky dat rychleji, než sérii insertů do tabulky pomocí relačního připojení (Teradata – FastLoad, Oracle – Oracle Data pump). Oracle datové pumpy jsou nejrychlejším způsobem, jak přenést data mezi dvěma Oracle databázemi. Jejich použití má svá omezení, např. nelze přenášet data z verze 11g na verzi 10g, pokud při exportu dat neuvedeme, pro kterou verzi data exportujeme.
44
Obrázek 14 Metadatové tabulky pro generování skriptů
Tabulky Load_Stage_Control a Application_Source slouží pro generování skriptů pro import databáze. Obě tabulky jsou ručně udržované v oblasti dwktl_meta. Tabulka Application_Source obsahuje záznamy, odkud se budou odebírat data pro stage oblast. Sloupec APLIKACE obsahuje seznam aplikací, které se budou dodávat data do stage oblasti. Sloupec CONNECT_STRING obsahuje název databázového linku, ze kterého se budou data exportovat. Databázové linky jsou ručně iniciované při zakládání prostředí. SQL skript bude generovat příkazy pro databázové pumpy v následujícím formátu: impdp
user/password
TABLES
=
TableName
NETWORK_LINK
=
NetworkLinkName TABLE_EXISTS_ACTION = TRUNCATE [QUERY=“Query“]
Jako základní tabulka pro kontrolu nahrávání slouží Load_Stage_Control. K této tabulce se pomocí inner joinu připojí tabulka Application_Source. Dále se připojí pomocí cross joinu pohled Last_Load_Date. Tento pohled se generuje na konci generování příkazů pro datové pumpy. Obsahuje jeden sloupec – LOAD_DATE. Tento sloupec obsahuje jako konstantu datum posledního nahrávání dat a slouží pro omezení množiny, která se bude přenášet. Přepínač QUERY je nepovinný a slouží pro omezování přenosu tabulek s informacemi o podáních z důvodu, že tyto tabulky v současné době mají od 1 do 3TB a není proto možné je přenášet celé při každém přenosu. Pokud je vyplněné pole CONTROL_COLUMN, vloží se parametr QUERY, který bude obsahovat omezení: “{CONTROL_COLUMN}
<=
{LOAD_DATE}“.
{CONTROL_COLUMN}
bude
nahrazeno názvem sloupce, podle kterého se bude filtrovat. {LOAD_DATE} bude nahrazené konstantou, která bude zvolena tak, aby filtrovala podle pole naznačující poslední úpravy. Tabulka Load_Stage_Control je naplněna následujícími hodnotami:
45
Database_Name
Table_Name
load_stage
GM_Parter
load_stage
GM_Prijemce
Posledni_Zmena
B2B
load_stage
GM_Odesilatel
Posledni_Zmena
B2B
load_stage
GM_Zprava
Posledni_Zmena
B2B
load_stage
GM_Typ_Zpravy
B2B
load_stage
GM_Stav
B2B
load_stage
Control_Column
Aplikace B2B
PBIPFPORTALMAP Konec_Zprac_IPF
IPF
load_stage
GMB2BZPRAVA
IPF
load_stage
DOMAIN
BPEL
load_stage
CUBE_INSTANCE
Modify_Date
BPEL
load_stage
GMFKZURNAL
Datum
ZIS
Tabulka 2 Nastavení datových pump
8.1.2 Odmazávání starých podání Odmazávání starých podání slouží k udržování velikosti databáze, aby nenarůstala do nekonečna zrychlení výstupních dotazů redukcí velikosti zdrojových tabulek
Obrázek 15 Metadatová tabulka pro odmazávání záznamů
Odmazávání starých záznamů probíhá před každým nahráváním stage oblasti. Skript generující SQL je z důvodu optimalizace rozdělen na dvě části. První část obsahuje všechny tabulky, které mají vyplněné pole CONTROL_COLUMN. Pro tyto tabulky se generuje SQL příkaz ve tvaru: delete from {DATABASE_NAME}.{TABLE_NAME} where {CONTROL_COLUMN} < {DELETE_DATE};
Části {DATABASE_NAME}, {TABLE_NAME} a {CONTROL_COLUMN} jsou nahrazeny stejnojmennými hodnotami z tabulky Delete_Control. Část {DELETE_DATE}
46
je nahrazená konstantou SYSDATE – 6 měsíců. Tato konstanta hodnota byla stanovena jako kompromis s pracovníky EKK, kteří budou systém nejvíce používat. Druhá část dotazu obsahuje všechny tabulky, které nemají vyplněné pole CONTROL_COLUMN. Tyto tabulky se před každým loadem stage oblasti celé vymažou, protože jsou přebírány ze zdrojových systémů fullloadem. Pro tyto tabulky se generuje SQL příkaz ve tvaru: truncate table from {DATABASE_NAME}.{TABLE_NAME};
Části {DATABASE_NAME}, {TABLE_NAME} jsou nahrazeny stejnojmennými hodnotami z tabulky Delete_Control. Příkaz truncate je ekvivalentní s příkazem delete bez where podmínky. Jeho největším přínosem je rychlost. Při testování systému trvalo vymazání tabulky se 1,3 miliony záznamů pomocí příkazu delete 5 minut. Příkaz truncate zvládl tu samou operaci za 4 vteřiny. Tabulka Delete_Control je naplněna následujícími hodnotami: Database_Name
Table_Name
Control_Column
Aplikace
dwktl
C_Stav
B2B
dwktl
C_Typ_Zpravy
B2B
dwktl
B2B_Partner
B2B
dwktl
B2B_Message
Posledni_Zmena
B2B
dwktl
BPEL_Message
Time_Update
BPEL
dwktl
ZIS_Message
Time_Update
ZIS
Tabulka 3 Nastavení odmazávání jádra aplikace
8.2. Stage oblast Výběr prvků do stage oblasti byl nejtěžší částí mé práce. Protože nikdo v ZP nevěděl, kde všude se podání zaznamenává a každý administrátor aplikace věděl pouze informace o své aplikaci, byla stage oblast v průběhu vypracování mé práce několikrát modifikována.
47
Obrázek 16 B2B tabulky elektronického podání
Prvním serverem, ve kterém se podání zaznamenávají, je B2B Proxy server (zkrácené B2B). Na tomto serveru jsou zpracována všechna asynchronní podání, která se posílají jak pomocí portálu ZP, tak pomocí B2B. Jako nejdůležitější pro konsolidaci systémů byly vybrány tabulky GM_ZPRAVA, GM_PARTNER, GM_PRIJEMCE, GM_ODESILATEL, GM_TYP_ZPRAVY A GM_STAV. Tabulka GM_ZPRAVA obsahuje všechna podání, která projdou přes B2B. Při příjmu podání je automaticky vygenerován identifikátor, který se vloží do sloupce ID_ZPRAVA. Tento identifikátor je jediný, podle kterého se dají asynchronní podání dohledávat. Softwarové společnosti implementující software pro komunikaci přes B2B pro všechny typy subjektů velmi často implementují své vlastní identifikátory, které jsou vidět v odeslaných a přijatých asynchronních podáních. Tyto identifikátory se bohužel v současné době nikam nenaznamenávají, i když jsou velmi často klienty (uživateli software) zaměňovány za ID_ZPRAVA. Tato záměna znesnadňuje pozdější dohledávání podání v pojišťovně. U většiny sloupců v tabulce GM_ZPRAVA chybí jejich popis, nebo význam v dokumentaci a ani samotní administrátoři B2B nemají úplný přehled, co sloupce obsahují. Jako důležité sloupce, které budou dostačovat monitoringu, jsem identifikoval ID_ODESILATEL, ID_PRIJEMCE, ID_TYP_ZPRAVY, ID_STAV. DATUM_VLOZENI
48
a POSLEDNI_ZMENA slouží jako informace o stáří záznamu. Podle těchto sloupců se řídí přenos do aplikace. Tabulky GM_PRIJEMCE a GM_ODESILATEL slouží jako vazební tabulky pro dohledání, který lékař nebo zdravotnické zařízení elektronické podání odeslalo. Jejich význam mi bohužel z databázového návrhu není jasný, protože vztahy tabulek GM_PARTNER, kde jsou údaje o odesilateli, a GM_ZPRAVA jsou 1:1, takže by bylo možné tyto tabulky úplně odstranit. Jelikož se mi nepodařilo objasnit význam sloupců POSLEDNI_ZMENA a ID_PRIJEMCE_TYP, respektive ID_ODESILATEL_TYP, budou tyto tabulky dále ve zpracování odstraněny. Více v kapitole 8.3 Core oblast. Tabulka GM_PARTNER obsahuje údaje o odesílateli zprávy. V této tabulce se vyskytují všechny subjekty, které mají právo na odesílání pomocí portálu ZP, nebo pomocí rozhraní B2B. Tato tabulka není v současné době monitoringem využívána, protože monitoring nemá přímý přístup do podkladových tabulek B2B. V současné době neexistuje v ZP žádná služba, která by dokázala dohledat všechna podání poslaná přes rozhraní B2B. Podání poslaná přes portál ZP jsou dohledatelná jen díky rozšíření funkcionality portálu ZP. Tato funkcionalita je přístupná pouze administrátorům portálu ZP. Tabulka GM_TYP_ZPRAVY obsahuje údaje o službách, které jsou využívány přes B2B. Tato tabulka slouží jako referenční pro tabulku GM_ZPRAVA. Typem zprávy je v tomto případě myšlena služba volaná pomocí B2B nebo portálu ZP. Informace o typu dále slouží pro směrování v aplikaci Integrační platforma. Tabulka GM_STAV obsahuje typy stavů, do kterých se může podání dostat. V této tabulce se zobrazuje stav zpráv, takže je možné podle ní kontrolovat, jestli byla zpráva odeslaná již do interních systémů ZP, jestli byla zpráva přečtena subjektem, atd. Podle původního zadání měla být druhým kontrolním serverem Integrační platforma, která zajišťuje rozesílání jednotlivých zpráv do ZIS. Na tomto serveru se bohužel nejvíce podepsal fakt, že rozvoj byl veden více společnostmi a nové funkcionality systému jsou postaveny jako nový systém, který na vstupu filtruje podání na podání pro nový systém a ostatní. Podání pro nový systém zpracuje a zaloguje do svých nových tabulek, ostatní podání jsou poslána na starý systém. Během analýzy systémů jsem zjistil, že tento server sice přeposílá všechna podání, ale podání pro skupinu soudních exekutorů
49
jsou logována v jiných tabulkách než podání podaná přes portál ZP. Podání podaná pomocí B2B nejsou logována nikde. Tento problém jsem potřeboval vyřešit, protože pokud by byla podání podaná přes B2B zaznamenávaná do logů pouze na serveru B2B a poté až v Základním informačním systému, nemělo by smysl v práci pokračovat, neboť mezi nejčastější problémy patří zaseknutí zpracování podání mezi Integrační platformou a Základním informačním systémem. Z tohoto důvodu jsem většinu tabulek z Integrační platformy vyřadil ze zpracování, protože bylo nutné zjistit, zda existuje jiný systém, který obsahuje všechna podání. Po komunikaci s pracovníky z odboru Elektronické komunikace klientů jsem se rozhodl, že serveru Informační platformy využiji tabulku, do které se ukládají podání poslaná přes portál, PBIPFPORTALMAP.
Obrázek 17 Tabulky obsahující údaje o podání v Integrační platformě
Tabulka PBIPFPORTALMAP obsahuje velmi přehledně informace o všech podáních podaných přes portál. Její největší přínos je, že v sobě obsahuje seznam systémů v Základním informačním systému, do kterých bylo podání posláno. Bohužel ale neobsahuje službu, která byla původně volaná, proto je nutné přebírat z Integrační platformy ještě tabulku GMB2BZPRAVA. Ač se na první pohled zdá, že tabulky nemají spolu nic společného, opak je pravdou. Sloupec IDB2B tabulky GMB2BZPRAVA je podmnožinou hodnot sloupce ID_PODANI tabulky PBIPFPORTALMAP. Rozpor v datových typech je dán dodavatelem služby a historickým vývojem Integrační platformy. První dva znaky v obou případech je zkratka firmy, která službu implementovala. První
50
vznikla tabulka PBIPFPORTALMAP, která implementuje a zároveň monitoruje všechna podání napříč pojišťovnou. Tato tabulka obsahuje nejen elektronická podání, ale i podání v rámci komunikace jednotlivých útvarů ve zdravotní pojišťovně. Proto je ID_PODANI tvořeno jako textový datový typ. Postupem doby bylo nutné doimplementovat podání podaná přes portál ZP, vznikla tedy tabulka GMB2BZPRAVA. Ač název tabulky evokuje, že obsahuje údaje o podání poslané přes B2B, není tomu tak. Datový typ v IDB2B odpovídá zvolenému datovému typu v sloupci GM_ZPRAVA v tabulce GM_ZPRAVA na serveru B2B. Pro hodnoty sloupců ID_ZPRAVA (tabulka GM_ZPRAVA na B2B) a IDB2B (tabulka GMB2BZPRAVA) platí, že pokud je podání poslané přes portál ZP, tak jsou totožné, pokud je podání posláno jinak, tak se v tabulce GMB2BZPRAVA neobjeví. Jako náhradu za systém Integrační platformy jsem vybral systém BPEL. Tento systém slouží jako monitoring jednotlivých podání a obsahuje informace o všech elektronických podáních, která pojišťovnou projdou. Nejen elektronická podání od klientů, ale i vnitřní podání v rámci pojišťovny. Tento systém na první pohled provádí integraci všech systémů, bohužel ale neumožňuje napojit na sebe jiné systémy, které by jeho informací využívaly. Systém BPEL jako takový nezveřejňuje své tabulky s daty o podáních. Jediné, co zveřejňuje, je výstupní kostka pro report celkovým stavem. Proto jsem ze systému vybral jako důležité tabulky CUBE_INSTANCE a DOMAIN.
51
Obrázek 18 Tabulky obsahující údaje o podání v systému BPEL
Tabulka DOMAIN obsahuje údaje o tom, z jaké domény podání pochází. V současné době existuje v pojišťovně cca 20 různých domén. Pro sledování asynchronních podání je důležitá doména B2B-a (hodnota v poli DOMAIN_ID). Tato doména obsahuje všechna asynchronní podání podaná přes B2B a portál ZP a slouží tedy jako náhrada tabulky PBIPFPORTALMAP v systému Integrační platformy. Tabulka CUBE_INSTANCE obsahuje údaje o všech podání ve zdravotní pojišťovně. Systém BPEL zavádí vlastní identifikátor CIKEY, pomocí kterého se dají dohledávat další údaje o podání v rámci systém BPEL. Bohužel tento klíč je uměle generovaný a pro použití v integraci systému zcela nevhodný. Po schůzkách
52
s administrátory systému Integrační platformy jsem objevil závislost, podle které je možné určit v záznamu v tabulce CUBE_INSTANCE, o které podání se jedná. Identifikátor z B2B systému se vyskytuje v poli TITLE. Podání podaná přes rozhraní B2B mají v poli TITLE záznam tvořený pomocí 'B2B:' a identifikátoru z B2B systému. Podání podaná přes portál ZP mají v poli TITLE záznam tvoření pomocí 'Portal:' a identifikátoru z B2B systému. Tato poněkud nevhodná identifikace záznamů je jediná možná pro přiřazení záznamu na B2B serveru v záznamu na BPEL serveru, proto bude dál použita jako referenční při transformaci do jádra systému. Posledním článkem ve sledování podání jsou záznamy uložené v aplikacích, které spadají pod Základní informační systém. Tyto aplikace mají jednotné logování, bohužel rozdělené distribuovaně na jednotlivých aplikačních serverech.
Obrázek 19 Tabulky obsahující údaje o podání v systémech ZIS
Všechny aplikace v ZIS logují do jednotné struktury GMFKTZURNAL. Tato tabulka obsahuje jako primární klíč CORRIDID. Toto korelační ID se objevuje i v Integrační platformě, bohužel nikdo z administrátorů neví, co toto ID znamená a proč se používá. Pole REFERENCE obsahuje id přidělené ze systému B2B, takže bude použité jako ID, pomocí kterého budou záznamy k sobě párovány.
8.3. Core oblast Oblast jádra obsahuje vyčištěné informace o podání. První částí jádra je část s číselníky obsahující údaje o mapování zpráv a typu elektronických podání.
53
Obrázek 20 Číselníky v jádru integrace systémů
Tabulka C_MAPPING_TABLE table obsahuje seznam aplikaci v ZIS, které vyřizují jednotlivé služby. Tabulka C_MAPPING_TABLE je plněna pomocí tabulky PBIPFPORTALMAP a GMB2BZPRAVA. Tabulka GMB2BZPRAVA slouží jako základní tabulka. K této tabulce se připojí tabulka PBIPFPORTALMAP pomocí vnitřního joinu přes rovnost polí IDB2B a ID_PODANI. Z takto vytvořené množiny se vyberou distinktní dvojice sloupců SLUZBA a APLIKACE_ZIS, za aplikování podmínky, že APLIKACE_ZIS je vyplněná (není null). Pokud takto vybrané záznamy obsahují hodnoty SLUZEB, které jsou již v tabulce C_MAPPING_TABLE, jsou původní záznamy nahrazeny novými. Zbylé záznamy zůstávají. Tato tabulka bude také ručně udržována pracovníky elektronické komunikace. Tabulka C_JMENO_ZPRAVY obsahuje ručně udržované záznamy o pojmenování jednotlivých služeb. Tato tabulka slouží pouze pro výstupní vrstvu, aby bylo možné jednotlivá podání v případě nutnosti přejmenovat pro lepší orientaci. Tabulka C_STAV a C_TYP_ZPRAVY jsou číselníky, které se plně přebírají z B2B systému. Základní tabulka pro číselník C_STAV je GM_STAV. Základní tabulka pro číselník C_TYP_ZPRAVY je GM_ZPRAVY. Z těchto tabulek jsou přebírány všechny záznamy, které se přenášejí do jádra aplikace.
54
Obrázek 21 Tabulka obsahující záznamy o subjektech v jádru integrace
Tabulka
B2B_PARTNER
obsahuje všechny záznamy převzaté
z tabulky
GM_PARTNER. Z této tabulky se přebírají všechny sloupce vyjma sloupců CERTIFIKAT a SUBJEKT_INFO. Pole CERTIFIKAT obsahuje veřejnou část podpisového certifikátu subjektu. Tato informace je pro jádro integračního systému naprosto irelevantní, proto není přebírána. Pole SUBJEKT_INFO obsahuje informace o subjektu. Tyto informace nejsou z pohledu dohledávání důležité, protože všechny důležité informace pro dohledání podání jsou v polích ID_PARTNER, PARTNER, CN a CLIENT_ID. Toto pole je také nevhodného datového typu pro vyhledávání, CLOB.
Obrázek 22 Tabulka obsahující záznamy o podání na B2B serveru v jádru integrace
Tabulka B2B_MESSAGE obsahuje údaje o podáních na serveru B2B. Jako základní tabulka slouží tabulka GM_ZPRAVA. K této tabulce se zleva připojí tabulka GM_ODESILATEL přes rovnost polí ID_ODESILATEL. Dále se zleva připojí tabulka GM_PRIJEMCE přes rovnost polí ID_PRIJEMCE. Pole B2B_ID se přebírá z pole ID_ZPRAVA. Pole ID_PARTNER_ODESILATEL se vyplní polem ODESILATEL_FK, pokud se připojí tabulka ID_ODESILATEL. Pokud se tabulka nepřipojí, vloží se původní hodnota pole ID_ODESILATEL. Do pole ID_ODESILATEL_NALEZEN se vloží
55
hodnota 'Y', pokud se připojí tabulka ID_ODESILATEL, jinak se vloží hodnota 'N'. Pole ID_PARTNER_PRIJEMCE se vyplní polem PRIJEMCE_FK, pokud se připojí tabulka ID_PRIJEMCE. Pokud se tabulka nepřipojí, vloží se původní hodnota pole ID_PRIJEMCE. Do pole ID_PRIJEMCE_NALEZEN se vloží hodnota 'Y', pokud se připojí tabulka ID_PRIJEMCE. Jinak se vloží hodnota 'N'. Pole ID_TYP_ZPRAVY, ID_STAV, DATUM_VLOZENI, POSLEDNÍ_ZMENA a POTVRZENI_MDM se přebírají ze základní tabulky beze změny. Tabulka B2B_MESSAGE se doplňuje o nové záznamy.
Obrázek 23 Tabulka obsahující záznamy o podání na BPEL serveru v jádru integrace
Tabulka BPEL_MESSAGE obsahuje údaje o podáních na serveru BPEL. Jako základní tabulka slouží tabulka CUBE_INSTANCE. K této tabulce se pomocí inner joinu připojí tabulka DOMAIN přes rovnost polí domain_ref. Záznamy v tabulce DOMAIN se omezí na hodnoty, kde pole domain_id je rovno 'B2B-a'. Tyto záznamy obsahují všechna asynchronní podání poslaná do ZP. Takto získané hodnoty se rozdělí na dvě množiny. První množina obsahuje záznamy, které v poli TITLE obsahují řetězec 'B2B:'. Z těchto záznamů se do pole B2B_ID vkládají záznamy podle následujícího klíče: pokud podmnožina řetězce od pátého znaku dále obsahuje ':', tak vlož vše mezi 5. znakem a ':' (B2B:xxxxxxxxx:xxx) jinak vlož vše od pátého znaku do konce (B2B:xxxxxxxxx) Druhá množina obsahuje záznamy, které v poli TITLE obsahují řetězec 'PORTAL:'. Pro tyto záznamy se postupuje v extrakci hodnoty B2B_ID stejně, pouze se neberou záznamy od pátého znaku, ale od osmého znaku. Pole APLIKACE se vyplní pro záznamy, kde TITLE obsahuje řetězec 'B2B:', hodnotou 'B2B', pro záznamy, kde TITLE obsahuje řetězec 'PORTAL:', hodnotou 'Portal'.
56
Pro všechny záznamy se vyplní zbylá pole takto: pole SLUZBA polem PROCESS_ID, pole TIME_CREATED pole CREATION_DATE a pole TIME_UPDATED polem MODIFY_DATE základní tabulky.
Obrázek 24 Tabulka obsahující záznamy o podání na ZIS serveru v jádru integrace
Tabulka ZIS_MESSAGE obsahuje všechny záznamy o podáních ze základního aplikačního systému, které jsou zpracovány pro použití ve výstupní mart oblasti. Tato tabulka jako jediná bude plněna asynchronně. Důvodem je, že všechny systémy používají stejnou logovací strukturu, a proto kvůli šetření místa budou všechny nahrávány do jedné tabulky. Po každém loadu jedné z aplikací v ZIS se vytvoří pohled se zkratkou názvu tohoto systému. Tento bude použit jako základní tabulka pro transformaci. K němu se připojí tabulka GMFKZURNAL pomocí cross join. Do pole B2B_ID se vloží hodnota REFERENCE ze spojených tabulek a pole APLIKACE bude vyplněno hodnotou z vytvořeného pohledu, které bude obsahovat název systému, ze kterého bylo nahráváno. Takto se provede iterace pro všechny systémy.
8.4. Data mart oblast Výstupní mart, do kterého budou mít přístup uživatelé oddělení EKK a monitoringu, je tvořen dvěma hlavními pohledy nad jádrem aplikace.
57
Obrázek 25 Výstupní pohled Message_Overview
První pohled, Message_Overview, obsahuje seznam všech zpráv, které projdou přes B2B server a které jsou uvedené v mapovací tabulce C_Mapping_Table. Pokud není dohledaný název služby v tabulce C_Mapping_Table, tak se na výstupu neobjeví. Základní tabulka tohoto pohledu je B2B_Message. K té se pomocí inner join připojí číselníky C_Typ_Zpravy a C_Stav podle jejich primárního klíče. Dále se pomocí inner joinu připojí tabulka
C_Mapping_Table
přes
rovnost
polí
B2B_Zprava.typ_zpravy
=
C_Mapping_Table.sluzba. Dále se k základní tabulce připojí tabulka B2B_Partner pomocí svého primárního klíče ke sloupci ID_PARTNER_ODESILATE základní tabulky, ale pouze v případě, že hodnota v poli id_odesilatel_nalezen je rovna 'Y'. Jelikož příjemce zprávy je vždy buď sama pojištovna, BPEL, nebo IPF, nemá cenu do výstupního pohledu přidávat data o příjemci zprávy. Poslední dvě tabulky, které se pomocí left joinu připojí k základní tabulce, jsou BPEL_Message a ZIS_Message, obě tabulky se připojí přes své primární klíče. Jako sloupce z výše připojených tabulek jsou brány následující: B2B_Message.B2B_ID B2B_Message.ID_Stav C_Stav.Stav B2B_Partner.Partner B2B_Partner.Client_ID
58
C_Typ_Zpravy.Typ_Zpravy BPEL_Message.Info Podle výše uvedených sloupců se celý výsledek seskupí a přidají se k němu další dopočtené sloupce. BPEL_Status: Pokud je hodnota v poli B2B_ID tabulky BPEL_Message nalezena, vloží se hodnota 'BPEL_OK'. V ostatních případech se vloží hodnota 'BPEL job not found'. Sluzba_Status: Vloží se hodnota '{x}/{y} done', kde {x} je počet aplikací, které navrátily své výsledky do BPEL serveru a {y} je celkový počet aplikací. Pokud by se hodnoty {x} a {y} rovnaly, vloží se hodnota 'BPEL OK' Last_Update_Date:
Do
tohoto
B2B_Message.Posledni_Zmena,
pole
vloží
nejvyšší
hodnota
z
polí:
BPEL_Message.Time_Update,
ZIS_Message.Time_Update. Všechny výsledné hodnoty se seřadí podle pole hodnoty Last_Update_Date.
Obrázek 26 Výstupní pohled B2B_Message_Detail
Druhý výstupní pohled, B2B_Message_Detail, obsahuje detailní pohled na jednotlivá B2B podání. Jeho účelem je možnost zkontrolovat, jakými aplikacemi v ZIS podání již prošlo a kde se ještě čeká. Pokud není dohledaný název služby v tabulce C_Mapping_Table, tak se na výstupu neobjeví. Základní tabulka tohoto pohledu je B2B_Message. K té se pomocí inner join připojí číselníky C_Typ_Zpravy a C_Stav podle jejich primárního klíče. Dále se pomocí inner joinu připojí tabulka C_Mapping_Table přes
59
rovnost polí B2B_Zprava.typ_zpravy = C_Mapping_Table.sluzba. Dále se k základní tabulce připojí tabulka B2B_Partner pomocí svého primárního klíče ke sloupci ID_PARTNER_ODESILATE základní tabulky, ale pouze v případě, že hodnota v poli id_odesilatel_nalezen je rovna 'Y'. Poslední tabulkou, které se pomocí left joinu připojí k základní tabulce je tabulka a ZIS_Message. Jako sloupce z výše připojených tabulek jsou brány následující: B2B_Message.B2B_ID B2B_Message.ID_Stav C_Stav.Stav B2B_Partner.Partner B2B_Partner.Client_ID C_Typ_Zpravy.Typ_Zpravy C_Mapping_Table.Aplikace Dále se připojí dopočítaný sloupec Status_Aplikace takto – pokud je pro danou kombinaci B2B_ID a Aplikace dohledán záznam v tabulce ZIS_Message, vloží se hodnota 'OK'. V ostatních případech je vložena hodnota 'ERROR'.
60
Kapitola 9 - Testování řešení Celá práce byla vypracována v malých iteracích, kde bylo nutné výstup každé iterace ověřit u administrátorů jednotlivých aplikací. Samotné výstupy byly testovány oproti současnému grafickému výstupu v BPEL serveru. Tento server je v tuto chvíli jediné referenční řešení, které obsahuje všechna data k porovnávání výsledků. Výsledky aplikace s BPEL serverem se shodují pro servery B2B a BPEL. Pro aplikace v ZIS se shodují pouze pro jednu aplikaci, protože do více aplikací jsem na základně bezpečnostních politik pojišťovny nemohl získat přístup. Při testování datového přenosu pomocí datových pump jsem musel nahradit datové pumpy databázovými linky, které přenášely data mezi jednotlivými servery. Dále se ukázalo, že při zadání v pojišťovně jsem dostal přístupové údaje na špatný server, do kterého nesměřovala podání. Při opravě připojení ke správným serverům se při testování databázových linků vyskytl problém, že jednotlivé servery jsou od sebe odděleny pomocí firewallů, a proto není možné přenášet data mezi nimi. Data ze serverů BPEL a IPF je proto nutné nahrávat přes export do csv soubor a následně importovat pomocí SQL developera do příslušných tabulek ve stage oblasti. Při měření doby přenosu tabulek z B2B trvalo přenést cca 1,2 miliónů záznamů kolem 11 minut. Pokud budeme brát nejhorší možný výsledek, tak v IPF a BPEL serveru budou dvě aplikace na jedno podání, což činí 2,4 miliónů na každém serveru. Při vývoji jsem zjistil, že asynchronní podání tvoří asi 20% všech podání v IPF a BPEL. Při přičtení všech ostatních podáních vychází, že z IPF a BPEL serverů bude nutné přenést 12 miliónů záznamů. Celková doba přenosu by tedy činila tři hodiny. Ze statistik vytíženosti B2B vím, že denně přijde cca 56000 podání. To znamená 560000 podání na serverech BPEL, IPF a ZIS. Z toho lze spočítat, že nahrávání dat na základě odhadů bude trvat nejdéle 30 minut. Celá transformace na testovacích datech v tuto chvíli trvá 20 minut. Tato doba je způsobená tím, že data v IPF a BPEL serverech byla smazána kvůli místu, a proto v tuto chvíli typy servery poskytují kolem 2000 záznamů. S ohledem na velikost dat počítám, že doba celé transformace se bude pohybovat kolem 2 hodin a z tohoto důvodu bude celý load spouštěn každý den mezi 2 a 3 hodinou ranní, kdy je vytíženost na serverech nejmenší. Je nutné, aby celá transformace proběhla do 6 hodin, kdy začíná být provoz na serverech, a proto je zbytečné serverům odebírat výkon při počítání dat. Výstupní pohledy v tuto chvíli vrací všechny výsledky kolem 1 minuty. Při omezení na hledání podle odesílatele nebo
61
identifikátoru podání je doba vrácení výsledků 2-8 vteřin.
62
Kapitola 10 - Závěr V rámci této práce byl navrhnut a vypracován systém pro monitoring asynchronních podání ve zdravotní pojišťovně. Tento systém staví na principech datového skladu, proto je možné v případě potřeby rozšířit toto řešení na skutečný datový sklad, který bude pojišťovně podávat informace o její práci. Celá práce byla z počátku velmi často měněna, protože nikdo nevěděl, jestli bude možné všechny informace dohledat.
10.1. Splněné cíle ze zadání Identifikoval jsem na ve všech klíčových systémech tabulky, které obsahují důležité informace o asynchronních podáních. Naimplementoval jsem všechny klíčové části systému a ověřil jejich výstupy podle BPEL serveru, který je v tuto chvíli jediným systémem, který podobné informace obsahuje. Otestoval základní funkcionalitu včetně odhadu na dobu transformace. Na základě této doby jsem v zdravotní pojišťovně doporučil vhodnou dobu na spouštění celé transformace. Umožnil jsem všem osobám a systémům přístup do výstupní vrstvy aplikace. Tento přístup má práva omezena tak, aby nemohl nijak ohrozit běh celé aplikace. Částečně jsem ověřil rychlost přenosu mezi databázemi a na základě tohoto přenosu odhadl dobu pro přenos dat ze všech systémů.
10.2. Nesplnění cíle ze zadání Kvůli problémům s přístupovými právy a nastavení firewallů jsem nemohl plně ověřit rychlost a funkcionalitu datových pump.
63
PŘÍLOHA A
Seznam použité literatury
[1] FOWLER, M. Who Needs an Architect?, IEEE SOFTWARE Červenec/září 2003.
[2] PEDA, A. Enterprise Application Integration week3 Kimball vs Inmon Data Warehouse Designs, 2007
[3] BPEL Dokumentace
[4] BIG F5 Server Dokumentace
[5] Oracle Data Pump (expdp and impdp) in Oracle Database 10g
[6] Oracle Data Pump Overview
[7] Základní pojmy a teorie v oblasti datových skladů
[8] Technologie Data Warehousingu a Data Miningu
[9] Oracle Advanced Queuing by Example
[10] Introduction to Oracle Advanced Queuing
64
PŘÍLOHA B
Obsah přiloženého CD
readme.txt – popis obsahu CD models/ - adresář s modely jednotlivých datových oblastí pics/ - adresář s jednotlivými obrázky doc/ - text diplomové práce ve formátu doc a pdf
65
