Bankovní institut vysoká škola Praha Katedra matematiky, statistiky a informačních technologií
Návrh architektury Business Intelligence pro finanční instituci Bakalářská práce
Autor:
Pavel Růžička Obor IT, Správce IS
Vedoucí práce:
doc. Ing. Bohumil Miniberger, CSc.
Hodkovice nad Mohelkou
Duben 2014
Prohlášení: Prohlašuji, že jsem bakalářskou práci zpracoval samostatně a v seznamu uvedl veškerou použitou literaturu. Svým podpisem stvrzuji, že odevzdaná elektronická podoba práce je identická s její tištěnou verzí, a jsem seznámen se skutečností, že se práce bude archivovat v knihovně BIVŠ a dále bude zpřístupněna třetím osobám prostřednictvím interní databáze elektronických vysokoškolských prací.
V Hodkovicích nad Mohelkou dne 23. dubna 2014
Pavel Růžička
Poděkování Na úvod bakalářské práce bych rád poděkoval vedoucímu práce doc. Ing. Bohumilu Minibergerovi, CSc. za poskytnuté rady, věcné připomínky a především jeho trpělivost. Rovněž chci poděkovat svému zaměstnavateli, Hypoteční bance, a.s., za podporu během mého studia a především za důvěru, kterou do mě banka vložila tím, že mne pověřila vedením celého projektu implementace nástrojů Business Intelligence.
Anotace Bakalářská práce popisuje genezi práce s daty v konkrétní finanční instituci. Popisuje výchozí stav, důvody pro jeho změnu a především proces rozhodování banky. Významným krokem byla implementace reportingového portálu v prostředí MS Sharepoint. Další etapou bude projekt Analytické databáze, přičemž bakalářská práce se zabývá konceptem jejího návrhu. Při psaní bakalářské práce jsem se opíral především o vlastní pracovní zkušenosti. Jako hlavní architekt celého projektu jsem byl jak autorem naprosté většiny souvisejících materiál, tak jsem nesl i hlavní odpovědnost za všechna rozhodnutí. Cílem práce je na příkladu středně velké banky nabídnout pohled na způsob implementace nástrojů Business Intelligence a důvody pro volbu vhodného řešení.
Klíčová slova MS SQL Server, MS Sharepoint, MS Excel Services, Datový sklad, Analytická databáze, Datamining, DataMart, Metadata
Annotation Bachelor thesis describes the genesis of the work with data in concrete financial institution. It describes initial state reasons for the change, and especially the decision making process of the bank. An important step was the implementation of the reporting portal in MS Sharepoint. Next phase the project will be project of Analytical database. Bachelor thesis then deals with the concept of design. During the writing of my thesis, I relied primarily on my work experience. As chief architect of the whole project, I was both the author of the vast majority of related material, so I carried the main responsibility for all decisions. The aim of this work is on the example a medium-sized bank offer insight on way of implementing Business Intelligence tools and reasons for choosing a suitable solution.
Key words MS SQL Server, MS Sharepoint, MS Excel Services, Datawarehouse, Analytical database, Datamining, DataMart, Metadata
Obsah 1.
Úvod ................................................................................................................................ 1
2.
Představení banky ............................................................................................................ 2 Specifika banky: .................................................................................................................. 2
3.
Historický vývoj práce s daty v bance ............................................................................. 4 AppCentrum ........................................................................................................................ 4 Jednoduché DataMarty a pokročilí uživatelé (Power users) ............................................... 5 Odbor Marketingové analýzy (OMA) ................................................................................. 5 Proof-of-concept ................................................................................................................. 6 Růst činností OMA ............................................................................................................. 7
4.
Projekt Kompetenčního centra pro data .......................................................................... 8 Výchozí požadavky ............................................................................................................. 8 Analýza uživatelských požadavků .................................................................................... 10 Analýza požadavků vedení banky ..................................................................................... 12 Analýza požadavků OMA ................................................................................................. 13 Shrnutí hlavních požadavků .............................................................................................. 13 Model požadavků .............................................................................................................. 15
5.
Model celkové architektury ........................................................................................... 16 Použité vzory ..................................................................................................................... 16 Výsledný model ................................................................................................................ 17
6.
Reportingový portál ....................................................................................................... 20 Výchozí stav ...................................................................................................................... 20 Analýza požadavků ........................................................................................................... 21 Metadata ............................................................................................................................ 25 Problém IRM a MS Excel services (dále jako ES) ........................................................... 25 Knihovny ........................................................................................................................... 26 Uživatelé ........................................................................................................................... 26 Informační toky ................................................................................................................. 27 Přesun reportů mezi knihovnami Správa a Distribuce ...................................................... 27 Složky ................................................................................................................................ 28 Schéma řešení.................................................................................................................... 30 Další řešené oblast............................................................................................................. 30 Zhodnocení řešení ............................................................................................................. 30
7.
DataMarty ...................................................................................................................... 31 Datamining ........................................................................................................................ 31 Reporty .............................................................................................................................. 32 Slabá místa ........................................................................................................................ 33 Budoucnost DataMartů ..................................................................................................... 34
8.
Požadavky na Analytickou databázi (AD) .................................................................... 35 Souvislosti ......................................................................................................................... 35 Historizace......................................................................................................................... 37 Jedna pravda ...................................................................................................................... 37 Centralizace ....................................................................................................................... 37 Časové úspory ................................................................................................................... 38 Technologie ....................................................................................................................... 38
9.
Konceptuální model AD ................................................................................................ 38 Vývojové prostředí Sybase Power Designer ..................................................................... 38 Odlišnosti konceptuálního, fyzického modelu .................................................................. 39 Hlavní entity ...................................................................................................................... 40 Normalizovaný nebo denormalizovaný model? ................................................................ 42 Větvení a některé zajímavé entity ..................................................................................... 42 Identifikátory ..................................................................................................................... 44 Diagram targetu ................................................................................................................. 44 Pojmenování ...................................................................................................................... 45 Nižší úrovně celé architektury .......................................................................................... 45 Historizace dat ................................................................................................................... 46
10.
Srovnání AD s datovým skladem .................................................................................. 51 Základní rozdíly ................................................................................................................ 51 Shodné prvky .................................................................................................................... 51 Metodické zařazení modelu .............................................................................................. 52 Možnosti upgradu na datový sklad ................................................................................... 53
11.
Závěrečné hodnocení ..................................................................................................... 53 Stav projektu AD............................................................................................................... 53 Dosavadní postup .............................................................................................................. 54 Stav DataMartů ................................................................................................................. 55 Časová osa ......................................................................................................................... 55
12.
Použitá literatura ............................................................................................................ 56 Tištěné monografie............................................................................................................ 56 Elektronické monografie, webovská sídla, databáze ........................................................ 56
13.
Přehled pojmů a zkratek ................................................................................................ 59
14.
Seznam použitých obrázků, tabulek .............................................................................. 61
1. Úvod Cílem mé práce byl „Návrh analytické databáze v konkrétní bance“ K tomuto tématu jsem se přihlásil, protože se databázemi aktivně zabývám. Můj zaměstnavatel, Hypoteční banka, a.s. (dále jen HB nebo banka), se dynamicky rozvíjí, za posledních 5 let zdvojnásobil svoji bilanční sumu i zisk. Nicméně jak roste konkurence, banka se tomu musí přizpůsobit. Jednou z hlavních cest je stoupající význam datových analýz a jejich využívání v praxi, které by mohly být podle mého názoru přínosem i pro další rozvoj HB. Bakalářská práce nejprve popisuje vývoj banky v oblasti práce s daty. První kroky byly opravdu pozvolné. Zpočátku bylo obtížné změnit styl práce a najít důvody pro další rozvoj. Protože trendem bankovnictví jsou mimo jiné i datové sklady, HB dlouho zvažovala, zda si jej také vybuduje. Obavy z nákladů, nedostatku know-how, délky realizace apod. byly veliké. Proto jsme šli vlastní cestou v podobě datového skladu menšího rozsahu. Hlavní náplní datového skladu je analýza dat. Z toho důvodu v tomto kontextu, o něm hovoříme jako o Analytické databázi. V této BP se zabývám přípravnými kroky projektu a popisuji postup a modely jak toho dosáhnout. Před návrhem AD bylo důležitým milníkem vytvoření reportingového portálu v prostředí Microsoft Sharepoint. Díky spolupráci s řadou lidí jsem měl příležitost slyšet řadu názorů, absolvoval jsem desítky prezentací, seminářů a školení, které jsem pak zohlednil při konceptuálním návrhu DS. Přestože projekt AD je stále v začátcích, dle mého názoru jsem v jeho přípravných fázích nasbíral dostatek poznatků, o které je zajímavé se podělit s ostatními. Hlavním přínosem BP je popis hledání cesty a především důvodů pro realizaci projektu v oblasti Business Intelligence. Za klíčový faktor považuji finanční vyčíslení přínosů. Tím získáme jak přesvědčivé argumenty k prosazení projektu, tak stanovíme hlavní cíle.
2. Představení banky S tržním podílem okolo 30 % je banka největším poskytovatelem hypotečních úvěrů pro fyzické osoby v ČR. 100% akcionářem je ČSOB, a.s. patřící do belgické bankovní skupiny KBC. HB je monoproduktovou bankou se specializací na hypoteční úvěry. V minulosti poskytovala úvěry také podnikatelským subjektům, ale v posledních letech se soustředí pouze na fyzické osoby – občany. Ve spojení s hypotékou zprostředkovává i zřízení běžného účtu a doplňkového pojištění nemovitosti či úvěru (produkty ostatních členů skupiny ČSOB). Dlouhodobou ambicí banky je udržet pozici leadra trhu, průběžně zlepšovat vnitřní procesy a zvýšit úroveň vztahů s klienty. Banka v dalších letech nepředpokládá zásadní změny svého obchodního modelu.
Specifika banky: 1. Odlišný distribuční model Banka většinu úvěrů prodává přes distribuční sítě jiných bank (ČSOB, Poštovní spořitelna, ČMSS)1 a prostřednictvím sítě nezávislých externích poradců (tzv. brokerů). To sebou nese výraznou orientaci obchodních útvarů banky na podporu externí distribuční sítě. Ať už ve formě budování vztahů nebo velmi podrobného reportingu (řada KPI2). Distribuce přes externí poradce současně zvyšuje riziko odchodu klienta z banky. Důvodem občas bývá snaha externistů „přesouvat“ klienta mezi bankami za účelem získání provize. 2. Centralizovaná péče o klienta Klient je záhy po podpisu úvěrové smlouvy předán z pobočky na centrálu, se kterou na dálku komunikuje (e-mail, telefon, dopis) až do okamžiku splacení úvěru). Díky tomu je možné snadněji měřit efektivitu procesů banky a jejich dopadů na chování klienta. Nevýhodou je ztráta osobního kontaktu s klientem. O to větší význam mají nepřímé formy komunikace (call centrum, e-mail). Uvnitř banky roste také zájem o aplikaci matematických a statistických modelů vysvětlujících chování klientů (viz. Datamining). 1
Více informací viz. www.csob.cz; www.erasvet.cz; www.cmss.cz Key performance indicators (Klíčové ukazatele výkonnosti) – měřítka pro sledování efektivity významných procesů a cílů firmy. [4] 2
2
3. Neexistence vlastních zdrojů depozit České banky využívají pro financování hypoték dva hlavní zdroje. Hypoteční zástavní listy (HZL) a vklady z depozit svých klientů. Typickým investorem do HZL jsou fondy nebo klienti privátního bankovnictví3. Schopnost financovat se z depozit závisí na schopnosti banky přitáhnout levné vklady na viděnou. Na rozdíl od konkurence, HB nepřijímá vklady, ale financuje se výhradně pomocí HZL a bankovních půjček od ČSOB. Tím se v bance sice některé procesy výrazně zjednodušují, na druhé straně se tím zvyšují požadavky na likviditu banky včetně povinnosti pokrýt pasiva (půjčky, HZL) dostatečně kvalitními aktivy (bonitními úvěry klientům). 4. Outsourcing činností Vzhledem k majetkovému propojení přenáší banka některé své činnosti na mateřskou skupinu. Ať už jde o personálně náročné, ale procesně jednoduché aktivity (call centrum, scannovna, účetnictví) nebo naopak vysoce specializované činnosti typu řízení úvěrových rizik či řízení finančních zdrojů. Dlouhodobým trendem je outsourcing neklíčových aktivit a naopak osvojení si specializovaných dovedností. Banka tím zvyšuje svoji konkurenční výhodu a přitom nemusí rozšiřovat tým spolupracovníků. 5. Specializace na hypotéky Kromě zmíněného pojištění a běžných účtů banka nenabízí jiné produkty. Pro banku se tím snižuje výnosový potenciál klienta. Vzhledem k časté preferenci zákazníků nakupovat „všechny produkty v jedné bance“ je pozice banky vůči konkurenci o něco slabší. Na druhé straně, vysoce kvalifikovaný tým zaměstnanců je schopen zpracovat i méně obvyklé požadavky klientů. Přes provázanost s ČSOB je HB samostatnou právní entitou s vlastní řídící strukturou. V oblasti ICT/IT a práce s daty jí to poskytuje značný stupeň nezávislosti rozhodování.
3
V tomto kontextu uvažována nabídka pro klienty ČSOB, a.s. Jde o servis služeb pro soukromou movitou klientelu. Specifikem jsou služby osobních bankéřů, kteří spravují portfolio produktů (především investic) svých klientů. Ke každému klientovi je přistupováno individuálně (na rozdíl od retailových zákazníků).
3
Řada lidí v bance pracuje léta, což přináší výhodu hluboké znalosti uložených dat a jejich vztahu k procesům banky. Toto neformální a dosti rozšířené know-how výrazně usnadňuje budování všech datových modelů a zrychluje jejich implementaci. Zjednodušeně řečeno – je snadné se doptat, co který údaj znamená a jak jej interpretovat v širších souvislostech. Určitou nevýhodou je slabší znalost zpracování dat, která se omezuje na práci s MS Excel / MS Access.
3. Historický vývoj práce s daty v bance Banka dlouhodobě preferuje vývoj informačních systémů ve vlastní režii. Z hlediska databázových platforem jsou používána tato řešení: -
IBM Informix verze 12.104 (hlavní produkční systém)
-
Microsoft SQL Server 20085 (všechny ostatní aplikace).
Většina uživatelů nemá oprávnění přistupovat k produkčním databázím a ani k tomu nemají potřebné znalosti. Z pohledu využití dat lze vysledovat tyto etapy vývoje:
AppCentrum Do nedávné doby naprostá většina uživatelů k datům přistupovala prostřednictvím komponenty (interní) aplikace AppCentrum. Ta obsahuje seznam databázových skriptů obalených kódem v Delphi6. Uživatel si vybere extrakt (sestavu), na pozadí se spustí SQL dotaz a prostředí aplikace zobrazí výslednou tabulku. Tu lze uložit do formátu MS Excel / MS Access. Postupem času řada sestav nabobtnala do velkých rozměrů (desítky až stovky sloupců), což výrazně zpomaluje jejich vytvoření. Některé sestavy se dokonce spouštějí automaticky v pravidelných intervalech. Obvyklá granularita sestav je až na úroveň jednotlivých úvěrů. Data nejsou v sestavách nijak čištěna nebo kategorizována. Nejedná se tedy o klasický report, ale spíše zdroj pro jeho vytvoření. Uživatelé si musejí data sami upravit, vyčistit, doplnit o počítané ukazatele event. připojit data z jiných extraktů. Z důvodu historizace si řada z nich vytváří vlastní lokální 4
Databázový software prodávaný společností IBM Databázový software prodávaný společností Microsoft. Více informací na stránkách www.microsoft.cz 6 Programovací jazyk vyvinutý společností Borland. Jádrem je starší jazyk Pascal doplněný o objektové prvky a především grafické rozhraní. 5
4
archívy. Při další aktualizaci reportu je třeba všechny kroky opakovat. Vzhledem k pracnosti výpočtu některých sestav často uživatelé do staršího reportu přidávali jen nové výsledky (např. za poslední měsíc). Pokud došlo ke zpětné opravě dat, uživatelé v reportu již změnu nezachytí. Další otázkou jsou bezpečnostní rizika (ztráta či krádež dat), protože uživatel získává přehled o velmi detailních datech. Různí uživatelé často docházejí k odlišným výsledkům. Ať už z důvodu chybějící jasné metodiky výpočtu ukazatelů, individuálních schopností práce s MS Excel nebo prostě použili nevhodný datový zdroj. Protože sestavy data nijak neupravují, kladou minimální nároky na údržbu. Ovšem platí to i naopak. Pokud se něco v databázi změní, uživatelé nemají jistotu, zda se údaje stále počítají správně. Z výhod tohoto řešení lze zmínit aktuálnost dat, protože dotazy běží přímo nad produkčními systémy. Rovněž zvyk vytvářet sestavy s maximálním detailem záznamu umožňuje pokročilým uživatelům dohledat řadu souvislostí.
Jednoduché DataMarty a pokročilí uživatelé (Power users) Jak už bylo uvedeno dříve, protože AppCentrum poskytuje jen aktuální snímek, pro účely komplexnějších analýz si uživatelé začali vytvářet vlastní archívy historických dat. Nejčastěji jde o finanční analytiky nebo analytiky úvěrových rizik. Znalost práce s MS Excel / MS Access je u těchto uživatelů na slušné úrovni a postupně se objevují snahy osvojit si znalost T-SQL. Vhodným označení pro tuto skupinu uživatelů je termín „Power users“. Jejich požadavky lze popsat „chci kvalitní, snadno dostupná data, s možností snadného vytváření souvislostí, namísto přípravy dat a jednoduchého reportingu chci mít více času na analytickou práci“. S pomocí odboru IT se nedávno některé datové archívy formalizovaly a přesunuly do samostatných databází (platforma MS SQL Server). Ovšem využití je velmi slabé, know-how uživatelů nízké, proto tyto databáze nelze označit za skutečná datová tržiště (viz. DataMarty).
Odbor Marketingové analýzy (OMA) Původně OMA vznikl jako čistě analytický útvar se zaměřením na zkoumání pravděpodobnosti jevů v chování klientů, vývoje trhu úvěrů na bydlení, cenové politiky konkurence, produktů apod. 5
Pro účely analýzy dat postupem let vzniklo na 15 samostatných DataMartů (rovněž platforma MS SQL Server). Oproti databázím Power userů je úroveň zpracování dat o několik řádů propracovanější. Specifikům analýzy dat jsou věnovány kapitoly DataMarty a Datamining. V řadě klíčových oblastech řízení banky (např. řízení portfolia přeceňovaných úvěrů) se role OMA stala nenahraditelnou a z finančního hlediska velice efektivní. Postupem času se odbor díky své bohaté datové základně stal hlavním dodavatelem složitých analýz (nezřídka se zabývá i oblastí financí a rizik) a reportů pro vrcholový management.
Proof-of-concept V roce 2010 byla provedena podrobná analýza práce s daty v bance. Výsledkem bylo 32 FTE (ekvivalent práce zaměstnance na plný úvazek). I když informační hodnota získaných reportů/analýz byla hodnocena jako vyhovující, pracnost jejich vytváření, stav technologií, správa a sdílení know-how byly označeny za neuspokojivé. Důsledkem analýzy byl pokus seznámit se s technologiemi Business Intelligence (dále BI) prostřednictvím proof-of-conceptu7 (dále PoC). PoC byl realizován ve spolupráci s externí firmou na technologiích firmy Microsoft. Obsahoval tyto komponenty: -
Databázový model datového skladu
-
ETL proces (ručně psané scripty)
-
Nástroje8 firmy Microsoft: o Reporting services o Excel services o Analysis services o Power pivot (ve spojení s Excelem)
7
Proof-of-concept lze přeložit jako „důkaz předpokladu“. V širším smyslu pak jako realizaci výseče nějakého projektu. Cílem je osvojit si potřebné know-how, ověřit si vhodnost navrženého řešení či vyzkoušet potřebné technologie. Po vyhodnocení výsledků bývá PoC ukončen a případná realizace vlastního projektu začíná od začátku. 8 Sada aplikací dodávaných v rámci licence MS SQL Serveru sloužící pro různé formy vizuálního zobrazení dat v prostředí webového prohlížeče a propojení s MS Excel.
6
Objem dat byl omezen na několik základních entit. Počet uživatelů byl definován okruhem analytiků OMA a útvarů řízení financí a rizik. Závěry PoC byly nejednoznačné. Vzhledem k absenci know-how by bylo nutné najmout tým specialistů. Odhad celkových nákladů přesahoval potenciál úspor (FTE) a přínosy v kvalitě dat. Především v bance chyběl jasný sponzor změn.
Růst činností OMA S vlastní rychle rostoucí datovou základnou začala mezi lety 2010 – 2012 významně posilovat role OMA. Vedle analýz bylo od konce roku 2011 přirozeným vývojem převzetí velké části reportů obchodních útvarů. Standardem se staly soubory ve formátu MS Excel s uloženým ODBC9 připojením volajícím pohledy (view) uložené v databázích OMA. Uživatelé nemají oprávnění přistupovat k databázi, tj. aktualizace reportu je zcela v kompetenci analytiků OMA. Na rozdíl od AppCentra je uživatelům materiál doručován rovnou v podobě grafu nebo tabulky. Přesto se pro reporty využívá nejvýše 20 – 30 % dat uložených v DataMartech OMA. Většina požadavků
se
totiž
stále
opakuje.
Distribuční
kanály,
produkty,
fixace,
objem
nových/přeceněných úvěrů, odchodovost. Právě denormalizovaný způsob uložení dat tvorbu reportů výrazně zrychlil. Navíc odpadla nutnost data čistit. Převedení reportingu na OMA uživatelům umožnilo získat reporty zcela nového typu. Externích obchodníci se vyznačují vysokým procentem migrace mezi zaměstnavateli. Analýza jejich chování s pomocí sestav AppCentra byla mimořádně pracná. DataMarty tyto výpočty výrazně zjednodušily. Rovněž se výrazně zvýšila grafická úroveň reportů a sjednotila se metodika jejich výpočtu. OMA je přitom malým odborem – veškeré činnosti kolem dat zpočátku zajišťoval tým dvou později tří lidí. Centralizací činností do OMA však také začalo stoupat operační riziko banky (nezastupitelnost pracovníků, nedostatečná úroveň dokumentace). Nepříjemným důsledkem je 9
ODBC - Open Database Connectivity (známé spíš pod zkratkou ODBC) je standardizované softwarové rozhraní pro přístup k databázovým. Snahou ODBC je poskytovat přístup nezávislý na programovacím jazyku, operačním systému a databázovém systému [19]
7
zvyšující podíl reportingu a údržby datových modelů na pracovním fondu OMA. DataMarty byly navrženy za účelem tvorby komplexních analýz. Předpokládalo se, že analytik si vždy vybere vhodný DataMart, provede aktualizaci dat, event. jej rozšíří o nové údaje a vytvoří analýzu. Toto vše se stane jen občas a veškeré činnosti proběhnou najednou. Drobná nepřesnost v řádu pár úvěrů z tisíce nehraje roli. Naopak uživatelé reportů očekávají garantovanou dodávku svých reportů. Pokud report může ovlivnit jejich finanční hodnocení, netolerují ani nejmenší chybu. To vede k neustálým zásahům do DataMartů, nutnosti obratem v nich zachytit každou novinku či výjimku. Analytici tak neustále řeší řadu drobných požadavků na úpravy. Přes užitečnost DataMartů přece jen existují data, která v nich zachycena nejsou a z principu jejich koncepce se do nich nedají zakomponovat. Proto se OMA rozhodl pro postupnou přestavbu celé architektury.
4. Projekt Kompetenčního centra pro data Výchozí požadavky Výchozím faktorem pro naplnění strategického cíle banky byla formulace poslání OMA jako „Kompetenčního centra pro data“. OMA tento obecný pojem rozpracoval do kroků, které byly schvalovány různými orgány banky (Představenstvo, různé výbory). Ve skutečnosti nešlo o jeden konkrétní materiál, ale soubor dílčích kroků, které byly posuzovány samostatně a opakovaně měněny. Oficiálně se byl rozvoj BI zařazen do strategických cílů banky (na období 2014 – 2016) až koncem roku 2013. Samotný koncept byl schválen Představenstvem banky v lednu 2014, tj. v době, kdy již některé prvky architektury byly hotové (viz. Reportingový portál) nebo ve značném stádiu rozpracovanosti (viz. Konceptuální model AD). Součástí strategie IT se BI stalo až v březnu 2014. Většina důležitých rozhodnutí byla přitom neformálně přijímána již mnohem dříve. Např. v polovině roku 2012 byl Představenstvem projednán materiál rozvoje Kompetenčního centra pro data. 8
Materiál definoval tyto hlavní cíle: -
Od ostatních odborů banky převzít veškerý reporting (Platí pro případy, kde lze centralizací dosáhnout přidané hodnoty – např. snížením pracnosti, sdílením informací nebo vyšší kvalitou dat/zpracování.).
-
Výrazně zvýšit kvalitu analýz (Více souvislostí, rychlejší zpracování, nové úlohy, vyšší přesnost.).
-
Přispívat k lepší péči o klienty (Hledání závislosti v chování klienta, příprava a vyhodnocování marketingových kampaní. V současnosti se jednotlivé události (schválení úvěru, první / druhá … refixace) sledují odděleně. Cílem je sledovat celý životní cyklus klienta – nikoliv úvěru.).
-
Vytvoření procesní mapy (Schopnost měřit efektivitu dlouhodobých procesů – např. schválení úvěru, vymáhání pohledávky, vyjednávání o sazbách …) na principech metody Balance score card10.
-
Sdílení know-how a podpora ostatních odborů (školení, výměna zkušeností)
Banka nechce budovat skutečný datový sklad, proto OMA musí být schopen si získání/zpracování dat zajistit vlastními prostředky. Nepřipouští se možnost outsourcingu provozu aplikací, které by mohly být považovány za kritické. Rovněž je vyloučen přístup dodavatelů: -
K databázím banky
-
Klientským datům uloženým v jiném formátu (např. MS Excel)
-
K datům zpracovaným do podoby reportu poskytujícího komplexní informaci (např. vývojový graf)
Velikost týmu ani v budoucnu nepřesáhne 4 – 5 FTE. Objem provozních nákladů nesmí přesáhnout nižší jednotky miliónů korun ročně. Objem investic pak nižší jednotky miliónů v průběhu nejbližších tří let. Prostředky budou čerpány z rozpočtu odboru IT, který také bude spoluzodpovědný za jejich efektivní vynaložení.
10
Systém vyvážených ukazatelů výkonnosti podniku. Na výkonnost firmy je nahlíženo z různých perspektiv (finance, zákazník, interní procesy, zlepšování) [3]
9
Z pohledu operačních rizik bude kladen důraz na: -
Dokumentaci procesů
-
Dokumentaci dat (metadat)
-
Vzájemnou zastupitelnost pracovníků OMA
-
Bezpečnost dat (krádež, zneužití)
Analýza uživatelských požadavků Od předchozí analýzy (viz. Proof-of-concept) uplynula delší doba, proto bylo prvním krokem zmapování současného stavu, požadavků a očekávaní uživatelů. Realizace proběhla koncem roku 2012 (s revizí v polovině roku 2013) formou řízených diskusí. Dotazováni byli všichni uživatelé patřící mezi: -
Tvůrce reportů / analýz
-
Významnější odběratele reportů / analýz (zahrnuje především vedení firmy)
-
Interní dodavatele datových zdrojů (např. OIT)
U každého uživatele byly zjišťovány informace: -
Jaké datové zdroje používá (interní / externí dodavatel, cesta, čas, formát)
-
Jakým způsobem s nimi pracuje (čištění dat, kategorizace)
-
Vede-li si vlastní „archívy“ historických dat (proč, kolik, jak je využívá)
-
Jaké reporty / analýzy vytváří (v jakém formátu, četnost, účel, zdroje)
-
Komu reporty / analýzy / data dodává, event. od koho je odebírá (interní / externí odběratelé, četnost, cesta, formát)
-
Jaké materiály vytváří pouze pro svoji potřebu (např. pro kontrolní účely)
U každého bodu bylo důležité zjistit, co uživateli na současném stavu ne/vyhovuje, jak by si představoval optimální řešení a kolik času dané aktivitě věnuje. Pokud jej nic nenapadalo, pokládal jsem návodné otázky (co kdyby …).
10
Výsledky dotazování jsem shrnul SWOT analýzou: Tabulka 1 - SWOT analýza uživatelských požadavků
Silné stránky:
Slabé stránky:
Zkušení uživatelé dokáží využít sestavy
Vysoká pracnost, duplicita činností
k vytváření složitých analýz
Nové požadavky na sestavy zatěžují
Znalost dat = znalost detailu a
pracovníky OIT (dlouhé dodací lhůty)
souvislostí
Chybí „jedna“ pravda
On-line výstup (data přímo
Obvykle nízká (grafická i faktická)
z produkčních systémů)
kvalita výstupů Ztráta know-how při fluktuaci zaměstnanců Některá on-line data nejsou zase tak online (velké sestavy se generují řadu hodin) Většina uživatelů má nízkou znalost práce s MS Excel a neumí vytvořit složitější report
Příležitosti:
Hrozby:
Nové (webové) nástroje umožňují
Riziko ztráty / krádeže dat
přehlednější práci s daty a jejich sdílení
Obava uživatelů o snížení dostupnosti
Vyčištěná data ušetří analytikům práci
dat („ztratíme svých část práv“)
Centrální úložiště dat / reportů (možnost
Pokud uživatelé nebudou pracovat
sdílení materiálů)
s daty, časem ztratí část know-how
Pokročilí uživatelé ušetří čas strávený reportingem a budou se moci věnovat analýzám Uživatelé mají zájem se učit nové technologie (pokud zaškolení nepřesáhne pár dní) Zdroj 1- Vlastní zdroj
Pro zajímavost lze uvést i další zjištění. Např. jeden pracovník vytvářel každý týden zprávu o vývoji nových prodejů. Měsíčně tím strávil cca pět dní. Přitom většinu obsahu materiálu 11
prakticky nikdo nečetl. Veškerá data navíc byla k dispozici v DataMartech OMA (viz. DataMarty). Po převzetí materiálu by se pracnost zkrátila z dní na minuty.
Analýza požadavků vedení banky Dotazování probíhalo v několika úrovních a různých časových okamžicích. Ve výsledku tedy nevznikl jeden ucelený materiál, nýbrž celá řada. Do okruhu vedení banky pak řadím členy Představenstva, ředitele jednotlivých odborů. Specifickou skupinou byly pracovníci interního auditu. Tabulka 2 - SWOT analýza požadavků vedení
Silné stránky:
Slabé stránky:
OMA je vnímáno jako hlavní dodavatel
Reporty dodávané uživateli se výrazně
reportů a dat
liší kvalitou výstupu, přesností a
OMA dodává velice kvalitní výstupy
rychlostí dodávek Nejednotná interpretace dat V bance vzniká obrovské množství reportů, které neřízeně kolují a k jejichž vlastnictví se nikdo nehlásí Vysoká pracnost zpracování dat Zkušení analytici se zabývají více reporty než analýzou dat a návrhem doporučení Přílišné zaměření na analýzu historie místo predikci budoucího vývoje
Příležitosti:
Hrozby:
Data představují „bohatství“ firmy.
Nízký počet zkušených pracovníků
Vedení si vědomo jejich významu a věří,
představuje riziko ztráty know-how
že lepším využitím lze zvýšit výnosy a
Riziko krádeže / zneužití dat
snížit rizika.
Ztráta části know-how při odchodu
Zavést systém (ukazatele, metodiku)
pracovníků
sledování všech procesů Zdroj 2 - Vlastní zdroj
12
Analýza požadavků OMA Podobně jako v případě uživatelů jsem vytvořil SWOT analýzu požadavků týmu OMA: Tabulka 3- SWOT analýza OMA
Silné stránky:
Slabé stránky:
Rozsáhlé know-how (technologie, data,
Vysoká pracnost údržby DataMartů (kód
procesy)
neošetřený
Převzetím operativního reportingu bylo
optimalizace, nové požadavky řešeny
ušetřeno nejméně 7 FTE ostatních útvarů
dotazováním na produkci s nutností řady
Podpora dalšího rozvoje ze strany vedení
podmínek)
firmy
Duplikace datových činností
Zájem členů týmu rozvíjet aktivity
Nedostatečné
„Víme, co potřebujeme a k čemu to
(chybějící dokumentace)
využijeme.“
Pracnost analýz vyžadujících nová data Vzájemná
proti
chybám,
sdílení
nepropojenost
špatná
know-how
současných
datových modelů (chybí souvislosti) Příležitosti: Snížení
Hrozby: pracnosti
nových
analýz
Při současném stavu technologií rostoucí
(odpadne pracné získávání dat)
pracnost údržby Datamartů / reportů
S více data možnost kvalitnějších analýz
(odhad nákladů 1,5 FTE)
(nejméně 5 FTE) v podobě převzetí
Riziko
dalších reportů
pracovníků
S lepšími
nástroji
snížení
údržby
datových činností
ztráty
know-how
odchodem
(riziko
vyjádřeno
ekvivalentem 3 FTE) Rostoucí
náročnost
provozu
vede
k poklesu kapacit pro realizaci nových projektů Zdroj 3 - Vlastní zdroj
Shrnutí hlavních požadavků Na základě předchozích kroků a se zapojením připomínek vedení banky a dalších účastníků byly formulovány tyto základní požadavky:
13
Tabulka 4 - Shrnutí požadavků
Oblast
Efektivita práce
Požadavek
Zadavatel
Snížit pracnost aktualizace reportů
OMA
Vytvořit proces pro distribuci reportů
OMA
Snížit pracnost údržby datové základny
OMA
Dokumentovat databázové skripty, architekturu databází a samotná data za účelem přenositelnosti a Know-how
distribuce know-how Snížit riziko nezastupitelnosti Centralizovat a uchovat know-how za účelem zvýšení a udržení kvality
Bezpečnost dat
Zavést dokumentovaný proces schvalování přístupu
Vedení banky,
k datům
interní audit
podrobného popisu a možnosti vyhledávání
OMA Uživatelé Vedení banky
Zavést měřítka procesů, implementovat metodu BSC
Vedení banky
(odstranit chyby, rozdíly) Nahradit distribuované získání / zpracování dat centralizovanou architekturou Umožnit historizaci dat Vytvořit vzájemné souvislosti dat s událostmi, akcemi, stavy … Odstranit překážky (nedostatek času) na straně pokročilých uživatelů a zvýšit jejich know-how Analýza dat
interní audit,
Centralizovat standardní reporting Zvýšit kvalitu dat – zavést princip „jedné pravdy“
architektura
Vedení banky Vedení banky
Zpřístupnit reporty pomocí webového prohlížeče vč.
Datová
Vedení banky
Zvýšit ochranu reportů/dat proti krádeži
Vytvořit proces pro zálohování reportů / analýz / práv
Reporting
OMA
Posunout se od analýzy „co se stalo“ k „co a s jakou pravděpodobností se stane“ Zvýšit výnosnost portfolia úvěrů
Uživatelé OMA OMA OMA Vedení banky Vedení banky Vedení banky
Zdroj 4 - Vlastní zdroj
14
Zajímavostí je nepoměr počtu požadavků formulovaných vedením banky a uživateli. Z Tabulky 4 není hned patrné, které body jsou nejdůležitější. Proto jsme pro jednotlivé body provedli odhad přínosů (v mil. Kč). Bohužel nemohu uvádět konkrétní hodnoty, postup jejich výpočtu nebo detailní příklady pro každý bod. Pro názornost uvedu, že bilanční suma banky převyšuje 200 mld. Kč. Aktuální průměrné tržní11 sazby úvěrů se pohybují okolo 3 %. Představme si, kdyby banka lepšími metodami analýzy dat dokázala udržet stejnou odchodovost klientů (při změně sazby) a lepší distribucí marže zvýšit sazbu o 0,10 pcb. Dopad na roční zisk by činil 200 mil. Kč. Roční objem nově schválených hypoték v ČR se pohybuje okolo 150 mld. Kč (údaj za 2013). Pokud by banka například segmentací klientů (a následnou produktovou/marketingovou komunikací) dokázala zvýšit tržní podíl o 1 pcb., představuje to 1,5 mld. Kč nových úvěrů. Vyšší marže o 0,1 pcb. nebo 1 pcb. tržního podílu rozhodně nejsou ambiciózní cíle. Dokumentují však obrovský potenciál využití dataminingu. Podobně lze ocenit i další body. Např. riziko krádeže dat lze kalkulovat jako pravděpodobnost zneužití databáze klientů (externí obchodník by data mohl využít pro oslovení klientů s cílem převést hypotéku k jiné banky a získat provizi) a s tím související ztráta úrokové marže. Jistý benefit představují také časové úspory analytiků. Lze je vyjádřit jako náklady ušlé příležitosti. Např. pokud dnes tým dokáže vytvořit 5 analýz za určitou časovou jednotku, pomocí AD jich zvládne třeba 10. Datové vstupy do projektu produktové optimalizace tak nebudou k dispozici za 12 měsíců, ale již třeba za 3 měsíce. Což může mít přímý vliv na rychlost zvýšení tržního podílu o uvedený 1 pcb. resp. získanou marži z nových obchodů.
Model požadavků Na základě požadavků byl sestaven model požadavků (viz. Obrázek 1). Komponenty architektury jsou označeny bílé, zeleně pak požadavky, které jsou komponentou naplněny.
11
Za referenční sazbu je používán údaj zveřejňovaný serverem www.hypoindex.cz (poslední zveřejněný údaj 19.3.2014 za únor 2014 činil 3,01 %). Server počítá váženou (objemem podepsaných úvěrů) sazbu z údajů předaných poskytovali hypoték. Data reprezentují cca 97 % českého trhu.
15
Obrázek 1 - Model komponent a požadavků
Zdroj 5 - Vlastní zdroj
Z modelu je patrné, že nejvíce požadavků se nachází na konci celého datového procesu. Přitom z hlediska pracnosti nejde o nejnáročnější kroky. Jako nejobtížnější se jeví oblast zpracování dat.
5. Model celkové architektury Použité vzory Protože já ani žádný jiný kolega neměl potřebné know-how, vycházel jsem při návrhu jednotlivých komponent a vrstev z dostupné literatury. Jako vhodný model se jevil Obrázek 2, který přehledným způsobem zobrazuje jednotlivé komponenty BI.
16
Obrázek 2 - Hlavní komponenty BI
Zdroj 6 – Novotný, Pour, Slánský, 2005 [1]
Dalším úkolem bylo pochopit, jak na sebe komponenty navazují – např. pomocí Obrázku 3:
Výsledný model Po zjednodušení schémat a zapracování modelu požadavků byl zpracován obecný návrh hlavních částí celého systému.
Obrázek 3 - Vazby komponent BI
17 Zdroj 7 - Novotný, Pour, Slánský, 2005 [1]
Obrázek 4- Zjednodušený model architektury
Zdroj 8 - Vlastní zdroj
Protože hlavními odběrateli dat jsou sami analytici OMA, byla ze stávající architektury zachována existence DataMartů (viz. DataMarty). Naopak velmi žádoucí bylo změnit princip jejich plnění. V současnosti každý DataMart vzniká nezávisle na ostatních. Výsledkem je duplicita datových připojení a transformací. Optimálním řešením by bylo získat a zpracovat všechna data najednou. To znamená vytvořit natolik komplexní architekturu „targetu“, aby obsahoval data potřebná pro všechny DataMarty. Současně by se tím mohly vyřešit některé nedostatky původního konceptu (např. historizace záznamů, dodatečné změny záznamů aj.). Na straně využití stál také požadavek provozovat reportingový portál v prostředí MS Sharepoint (viz. Reportingový portál). Současnou podobu reportů (MS Excel) bylo žádoucí převést do prostředí webového prohlížeče a aktivních grafů. Na základě koncepce na obr. 3 jsem proto obecný návrh doplnil o konkrétní komponenty.
18
Obrázek 5 - Přehled uvažovaných komponent
Zdroj 9- Vlastní zdroj
Protože v modelu požadavků taková potřeba nebyla identifikována, byly vynechány prvky využívající data pro jiné aplikace (např. CRM), prvek EIA12 a operativní úložiště. Naopak byly přidány komponenty: -
„mirror“ neboli zrcadlo produkčních databází. Zajištění plnění této vrstvy zůstane v gesci OIT.
-
Pro názornost jsou zobrazeny jednotlivé vrstvy Datového skladu a tok dat pomocí ETL pumpy.
-
Propojení DataMarty se MS Sharepoint (např. s využitím dalších technologií13 firmy Microsoft)
-
Monitoring všech procesů.
-
Dokumentace databází pomocí CASE nástroje (viz. Sybase Power Designer)
12
Zkratka pro Enterprise Application Integration – řešení pro datovou integraci podnikových aplikací. [5] Nabízí se využití Analyses services (nástroj pro tvorbu OLAP kostek), Reporting Services (vytváření reportů), Performing Point Services (sledování KPI), Power Pivot (propojení s MS Excel) aj. Více informací na stránkách výrobce: https://www.microsoft.com/sqlserver/cs/cz/solutions-technologies/business-intelligence.aspx 13
19
-
Datová kvalita (resp. datový slovník)
Pro některé komponenty nebylo možné vhodnou technologii odhadnout dopředu. Na straně zdrojových databází stály především IBM Informix a Microsoft SQL Server. Od začátku jsem však nepředpokládal import všech dat. Proto jsem navrhl nejprve zmapovat velikost potřebných dat a vhodný způsob najít později. Částečně to souviselo i s očekávaným nákupem nových diskových polí, která by kromě nižších nákladů, vyšší rychlosti měla především zvýšit kapacitu. Pro projekt AD bylo předběžně alokováno 40 TB. Na straně Stage vrstev z licenčních důvodů předpokládám využití MS SQL Serveru (ve verzi 2012). Pro neznámou v podobě očekávané velikosti dat a zátěži nebylo rozhodnuto o technologii pro „Target“ a jednotlivé DataMarty. Opět předpokládám MS SQL Server, ale nelze vyloučit ani zakoupení jiné technologie (např. Sybase IQ14).
6. Reportingový portál Výchozí stav OMA v polovině roku 2012 spravoval již 60 obchodních reportů ve formátu MS Excel (s uloženým ODBC připojením do databází MS SQL Serveru). Prezentace dat je řešena pomocí kontingenčních tabulek a grafů, výjimkou nebyly reporty více připojeními (nejvíce 20 připojení). Soubory byly chráněny technologií Microsoft Office IRM [6], [7], [8] (Správa přístupových práv k informacím) na úrovni jednotlivých souborů. Základním principem je zašifrování souboru a přidělení uživatelských práv různé úrovně a platnosti. Při pokusu o otevření souboru je existence uživatele ověřena proti seznamu uživatelů vedených v Microsoft Active Directory15 a rozsahu práv uložených v souboru.
14
Databázová technologie firmy Sybase (nyní SAP). Specifikem je sloupcové uložení dat. Více informace viz. dokument na stránkách výrobce http://www.sybase.cz/uploads/SybaseIQ_15_2.pdf 15 Technologie adresářových služeb LDAP v prostředí MS Windows. Umožňuje řízení práv, síťovou instalaci softwaru, spravovat organizační strukturu firmy. [9]
20
Reporty byly jedenkrát týdně / měsíčně aktualizovány ručně či pomocí maker.
Analýza požadavků Cílem řešení bylo vytvoření prostředí pro distribuci a sdílení reportů. Odborem IT bude určeno využit platformu Microsoft Sharepoint (konkrétně verze MS Sharepoint Server 2010 Enterprise). Důvodem byla částečná zkušenost s tímto prostředím (banka MS Sharepoint využívá jako úložiště projektové dokumentace) a rovněž licenční politika Microsoftu (zvýhodněné ceny pro větší firmy). Na základě starších zkušeností byla jako dodavatel řešení vybrána firma z okruhu certifikovaných partnerů firmy Microsoft. Jméno dodavatel bohužel nemohu uvést. Vzhledem k prioritám (viz. Model požadavků) jsem se rozhodl realizovat jak požadavky vedení firmy, tak i některé další. O realizaci požadavků jsem rozhodoval sám. Návrhy řešení jsou pak výsledkem diskusí s dodavatelem. Část informací jsem čerpal úvodní analýzy (společný materiál), přičemž jsem text přizpůsobil finálnímu řešení a pozdějším úpravám. Tabulka 5 – MS Sharepoint - rozšíření požadavků
Požadavek
Upřesnění požadavku
Návrh řešení
Chceme zachovat
Přístup k dokumentům řídit pomocí práv ke
využívání IRM.
složkám. V bance je stanoven okruh kvalifikovaných osob, kteří již dříve definovali své požadavky na okruh citlivých dat (např. osobní údaje klienta, provize,
Zvýšit
Reporty budou
marže …). Pokud report některý z těchto údajů
ochranu
klasifikovány z hlediska obsahuje, Správce reportů (viz. Uživatelé) musí
reportů
citlivosti (význam
vyžádat posouzení materiálu z hlediska citlivosti.
případné ztráty dat)
Bude-li materiál označen za citlivý, osoba, který jej takto označila, bude schvalovat přístup každého nového uživatele a nejméně 1x ročně provádět audit nastavených práv.
Práva
uživatelů
se Platnost oprávnění ve staženém souboru vyprší po
mohou v čase měnit. 24 hodinách. Poté soubor stane navždy (pro 21
Systém práv
přidělování každého) nepřístupným. musí
být
dostatečně flexibilní
Změna práv uživatele se tak projeví nejpozději do druhého dne. Soubor je před stažením / otevřením zašifrován a práva jsou nastavena pouze uživateli, který report
Jediným
úložištěm v daném okamžiku otevřel.
reportů
bude
Sharepoint.
MS
Odesílání
souborů e-mailem nebo ukládání na disk není
Díky tomu není možné, aby soubor umístěný mimo MS Sharepoint otevřel někdo jiný (vč. Správce reportů). Uživatelé by si proto namísto souboru měli
žádoucí.
vzájemně posílat jen odkazy na umístění reportů v MS Sharepoint O práva bude žádáno pomocí interní softwarové Zavést dokumentovaný aplikace Danae, která uchová všechna potřebná proces
schvalování data. Práva budou schvalována kompetentními
přístupu k datům
osobami,
které
ponesou
důsledky
chybných
rozhodnutí. Lze označit jeden či více reportů a pomocí naprogramovaného doplňku spustit službu MS Excel services [10]. Ta zajistí otevření souboru (podporuje Snížit pracnost aktualizace reportů
pouze
formát
MS
Excel),
jeho
aktualizaci (pomocí uloženého ODBC připojení) a Updaty
lze
spouštět opětovné uložení. Přidanou funkcí je přejmenování
hromadně / jednotlivě / starší verze (přípona je tvořena datem vytvoření automaticky a)
souboru). Automatická aktualizace vyžaduje vyplnění metadat (viz. Metadata). Konkrétně údajů „Automatická aktualizace“ (ano / ne, výchozí je ne), „Frekvence“ (týden, měsíc, ad-hoc) a „Den aktualizace“ (1. den, 2. den …). Vychází se z českého kalendáře (týden 22
začíná v pondělí). Doplněk se v daných intervalech spouští sám. Zpráva o výsledku aktualizací je zapisována do logu. Úlohu bude vykonávat server Vytvořit proces
pro
distribuci reportů
analýz a
soubory, které splňují danou normu b).
sám upozornit uživatele MS Sharepoint jim na základě jimi definovaných na nový report
podmínek zašle e-mail s odkazem na nový report. Reporty se při aktualizaci sami historizují. Soubor je otevřen, uložen pod názvem doplněným o datem
proces pro reportů,
MS Sharepoint serveru. Je schopna aktualizovat
Server bude schopen Uživatelé si sami mohou nastavit tzv. notifikace.
Vytvořit zálohování
Služba MS Excel services [10] běží jako součást
jeho (předchozího) vytvoření a pak teprve proběhne Z kontrolních
důvodů aktualizace. nutno uchovávat starší Starší reporty jsou pomocí metadat označeny za verze reportů „historické“. Jejich standardní zobrazení v MS
práv
Sharepoint je pomocí „view“ potlačeno resp. jsou dostupné pomocí jiného „view“. V první fázi se předpokládá cca 50 uživatelů, s postupným
Počet uživatelů má vliv na cenu licencí, ale s kapacitou není problém. Výkon aplikace i hardwaru je dostačující (konzultováno s dalšími odděleními odboru IT).
Další
rozšířením na všechny
požadavky
zaměstnance. Celkem
MS Sharepoint rovněž podporuje využití uživatelů
až 500 osob.
či
skupin
uživatelů
definovaných
v Active
Directory stejně jako tvorbu vlastních skupin uživatelů. Oprávnění uživatelů ke konkrétnímu reportu se
Práva jsou řízena pomocí metadat nad každou
23
mohou lišit:
složkou s využitím sloupců:
pouze číst
Uživatelé – čtení
číst a editovat
Uživatelé – zápis
řídit práva
Uživatelé – správa V každém sloupci je možné z Active Directory vybrat uživatele / skupinu uživatelů. Dojde-li ke kolizi práv, platí nejnižší úroveň oprávnění (čtení). Uživatel vidí jen obsah, ke kterému má práva. Nastavení složky se dědí pro všechny dokumenty uložené ve složce (nikoliv podsložky) c)
Zdroj 10 - Vlastní zdroj
Poznámky: a)
Možnost automatické aktualizace reportů doposud nebyla implementována. Důvodem je
současná datová architektura, kdy nelze zajistit potřebnou dostupnost potřebných dat a updaty by se tak neprobíhaly řádně. b)
Služba MS Excel services [10] není schopna aktualizovat jiný zdroj než ten uložený ve
vlastnostech Datových připojení v daném souboru. Současně nepodporuje [11] některé běžně využívané vlastnosti MS Excel, a proto jsme řadu reportů museli přepracovat. Problémem byly především obyčejné tabulky (ES podporuje update pouze kontingenčních tabulek), nemožnost používání obrázků (např. šipek), vzorců volajících hodnoty v jiné části souboru nebo obsah zcela jiného souboru. c)
Původně se dědění zajišťovalo ručně. Ovšem s přibývajícím počtem reportů začalo docházet
k opomenutím při správě metadat. Proto byla funkčnost záhy rozšířena o automatické dědění. Nejde o vlastní chování MS Sharepoint, tj. dědění bylo nutné vynutit doplňkem. Je-li v metadatech složky zvoleno „Dědění metadat“, doplněk při každé editaci metadat složky ověří vybraná metadata podřízených souborů (nikoliv podsložek). Typickým příkladem je popis reportu, seznam oprávněných uživatelů nebo klasifikace z hlediska citlivosti. Pokud složka obsahovala např. 20 (obvykle historických) reportů, ruční aktualizace metadat byla opravdu pracná. 24
Metadata Metadata jsou jednou z hlavních předností MS Sharepoint proti klasickému souborovému ukládání na disku. Metadata si lze představit jako jakési štítky rozšiřující základní popisné vlastnosti souboru nebo složky. Při velkém počtu dokumentů a složek uživatelé des oceňují rychlé hledání a filtrování. Metadata také široce využíváme pro automatizaci ve formě naprogramovaných doplňků. Při implementaci reportingového portálu byla navržena řada různých metadat. Některé se časem přestaly používat a další naopak vznikly postupně. Některé stojí za pozornost: -
Citlivost reportu (Materiál je považován za důvěrný, žádosti o práva jsou posuzovány dalšími osobami.)
-
Značky (Nebo též „tagy“ – seznam hesel, kterými lze snadno popsat obsah každého materiálu.)
-
„Uživatelé – čtení“, „Uživatelé – zápis“, „Uživatelé – správa“ (Umožňují výběr uživatelů a skupin uživatelů z Active Directory či skupin definovaných v MS Sharepoint. Pomocí nich jsou vytvářena práva pro knihovnu Distribuce – viz. Knihovny)
-
Stav reportu (Určuje aktuálnost materiálu – aktuální / historický / neaktualizuje se. Zobrazení historických materiálů je standardně vypnuto a také se již pomocí doplňků neaktualizují.)
Problém IRM a MS Excel services (dále jako ES) MS Sharepoint dokáže dokumenty ve formátech MS Office 2007 (a vyšší) šifrovat pomocí IRM (jedinou výjimkou je nemožnost blokování tisku). Bylo proto nepříjemným překvapením, když ES, která je bez MS Sharepoint nepoužitelná, Microsoft vlastní technologii IRM nepodporuje. MS Excel services nedokáže zašifrovaný soubor otevřít (tudiž. ani aktualizovat a uložit) bez ohledu, zda účet používaný službou k souboru práva má či nikoliv. Prvním návrhem řešení bylo vytvoření doplňku, který zašifrovaný soubor otevře, odstraní IRM, uloží na nešifrované úložiště, následně zavolá ES, ta provede aktualizaci, doplněk soubor znovu otevře a obnoví IRM. Řešení se zdálo sice komplexní, ale složité. 25
Knihovny Výsledek bylo zjednodušení, kdy v MS Sharepoint vznikly dvě knihovny. První (s označením „Správa“) není chráněná IRM. Zde probíhají aktualizace reportů. Teprve poté jsou reporty zkopírovány do druhé knihovny (s označením „Distribuce“), kde už je IRM aktivován. Zdánlivě nelogická duplikace reportů má i další přínosy: -
Jedinečnost verzí (V Distribuci mohou uživatelé soubor změnit nebo i poškodit, ale update probíhá nad Správou, kde je vždy správná verze. Při přesunu dat ze Správy do Distribuce je chybná verze znovu nahrazena originálem.)
-
Bezpečnost (Pokud by uživatelé mohli upravit Datové připojení, mohli by do reportu podvrhnout vlastní kód. Protože však v Distribuci žádný skutečný update neprobíhá, úmyslná změna nemůže způsobit problém.)
-
Přehlednost (Správci reportů provádějí všechny změny nad svojí knihovnou. Teprve až je změna kompletní, proběhne hromadný přesun)
-
Hladké aktualizace (ES vyžaduje výhradní přístup k souboru. Tj. pokud by byl report současně otevřen některým z uživatelů, aktualizace by skončila chybou. Ve Správě je počet uživatelů omezen na Správce reportů, tj. riziko náhodného otevření souborů je minimální.)
Uživatelé Při analýze byly definovány dvě základní skupiny a dvě podskupiny uživatelů: -
Správci reportů (pracovníci OMA – mají přístup do všech sdílených složek, zajišťují aktualizaci reportů, nastavují přístupová práva, zakládají nové sdílené složky, řídí způsob zobrazení obsahu webů, zodpovídají za provedení klasifikace reportů z hlediska jejich citlivosti)
-
Běžní uživatelé (všichni pracovníci banky): o Řadový odběratelé reportů (v rámci sdílených složek mají přístup jen tam, kde jim jiní uživatelé nastaví práva) o Správci odborových složek (manažeři nebo jimi pověření pracovníci jsou navíc správci složek vlastní organizační složky) 26
Informační toky Mezi jednotlivými uživateli dochází k těmto informačním tokům: -
Běžní uživatelé žádají Správce reportů o nový report či jeho změnu (žádosti jsou řešeny mimo MS Sharepoint v rámci jiné interní aplikace banky)
-
Běžní uživatelé žádají Správce reportů o přístup k reportu (rovněž s použitím jiné aplikace)
-
Běžní uživatelé mohou ostatním uživatelům zpřístupnit vlastní materiály (viz. Složky)
-
Správci odborových složek chtějí zpřístupnit obsah složek / podsložek ostatním pracovníkům svého týmu event. jiným pracovníkům (viz. Složky)
-
Správci reportů žádají vybrané manažery o posouzení citlivosti reportů (rovněž jiná aplikace)
Přesun reportů mezi knihovnami Správa a Distribuce Oddělením uživatelů na dvě knihovny bylo nutné vyřešit přesun reportů. Přitom bylo třeba pamatovat na: -
Obsah ve Správě se považuje za sdílený a má charakter jediné pravdy. Tj. při přesunu se může přepsat jeho kopie v Distribuci.
-
Uživatelé si v Distribuci mohou upravovat reporty, ale pouze tím, že je uloží do své vlastní / odborové složky.
-
Při přesunu je třeba zajistit kontrolu všech metadat.
-
Při přesunu je třeba zajistit kontrolu všech práv a případně je změnit.
-
Přesun nesmí vyvolat sérii zbytečných notifikací.
Obecně je přesun zajišťován doplňkem, který označené složky (a jejich obsah) zkopíruje v poměru 1:1. Výběr složek pro přesun je nutné provést ručně. Nejsložitějším problémem byla kontrola práv pro každou složku. Ta jsou ve Správě definována metadaty. Samozřejmě práva lze definovat i klasickými vlastnostmi MS Sharepoint, ale protože do Správy mají přístup jen Správci reportů, tak tyto údaje nebylo možné použít. 27
Z pohledu změn lze očekávat situace: -
Uživateli bylo odebráno / přidáno / změněno právo
-
Celá složka byla přidána / odstraněna / přejmenována
Původně se celé nastavování provádělo ručně, ale pracnost byla tak veliká, že si záhy vynutila úpravu doplňku. Přitom jsme mohli využít existujících (a řádně vyplňovaných) metadat na každou složkou. Nově se doplněk chová tak, že nepřesouvá celý obsah najednou. Namísto toho nejprve porovná složky. Pokud se liší metadata, jeden sloupec po druhém zkopíruje. Tím je zabráněno nechtěné notifikaci uživatelů v případě pouhé změny metadat. Následně porovná obsah složek, v Distribuci odstraní soubory, které se proti Správě liší (tím je zajištěna jedna pravda) a doplní je novými / správnými. Uživatelům se proto doporučuje vypnout notifikaci výmazu souborů a ponechat jen notifikaci změn. Nakonec jsou zkontrolována práva definovaná metadaty ve Správě proti standardnímu řízení práv MS Sharepoint pro složku Distribuce. Kdo je ve složce uveden v metadatech například jako „Uživatelé – zápis“, je v Distribuci veden ve vlastnosti „Spravovat oprávnění“ jako osoba s právem editovat. Byl-li uživatel z metadat ve Správě odebrán, je při přesunu dat zrušeno jeho oprávnění v Distribuční knihovně. Interním procesem banky je stanoveno, že změna práv musí mít neodkladný účinek, tj. Správci reportů musejí po změně těchto metadat okamžitě provést přesun dat. Byla-li složka ve Správě přejmenována, v Distribuci je třeba to samé provést ručně.
Složky Veškerý obsah knihovny „Správa“ bude přesouván do knihovny „Distribuce“, proto se takto přesouvané složky považují za sdílené. Uspořádání složek / podsložek vychází ze zavedeného rozdělení reportů (Nové úvěry, Přeceňované úvěry, Aktivní porfolio, Produkty atd.). Protože je v distribuční knihovně obsah sdílených složek řízen Správci reportů, ostatní uživatelé nemají možnost uložené reporty změnit / odstranit. Respektive mohou mít právo editace, ale jejich úpravy nejsou trvalé (při další aktualizaci se obsah složky opět synchronizuje proti knihovně „Správa“. 28
Uživatelé občas potřebují reporty upravovat. Report je však zabezpečen pomocí IRM. Pokud by uživatelé soubor uložili mimo MS Sharepoint, po expiraci práv by se stal nedostupným. Proto byla pro každého uživatele vytvořena jeho osobní složka. Logické využití je jak pro osobní potřeby, tak pro uložení materiálů, které chce uživatel sdílet s ostatními. V tom případě musí uživatel (de facto majitel složky) mít možnost měnit oprávnění k podsložkám. Tím by se však otevřela vážná bezpečnostní mezera, protože uživatel by musel získat plná práva (úplné řízení) ke složce. Uživatel by si otevřel report ve sdílených složkách (soubor by se okamžitě zašifroval pomocí IRM – dle nastavení složky pouze čtení nebo čtení/editace), následně by jej uložil do své osobní složky (IRM by se tím odebralo) a poté by jej ze své složky opět otevřel. Dokument by se sice zašifroval, ale s nejvyšší úrovní zabezpečení. Uživatel by měl právo šifrování dokonce odebrat a s dokumentem libovolně nakládat. Proto byl vytvořen doplněk, který využívá metadat. Konkrétně polí „Uživatel - správa“. Osoby uvedené v tomto poli mají právo měnit metadata. Pokud majitel složky vytvoří podsložku, může změnit obsah sloupců „Uživatelé – čtení“ / „Uživatelé – zápis“. Uložením metadat se automaticky aplikují práva (jiný uživatel přidá / odstraní). Díky funkci dědění se metadata opakují ve všech podsložkách, dokud majitel složky vlákno nepřeruší. Na podobném principu fungují i složky jednotlivých odborů a oddělení. V poli „Uživatel – správa“ jsou obvykle manažeři, jimi pověření zástupci a samozřejmě členové vedení firmy.
29
Schéma řešení Obrázek 6 - Sharepoint - schéma řešení
Zdroj 11 - Zdroje vlastní
Další řešené oblast Součástí implementace byl také: -
návrh grafické podoby portálu vč. úvodní stránky (homepage)
-
vytvoření vhodných typů obsahu
-
vytvoření pohledů (view)
-
instalace / nastavení serverů
Popis těchto prvků není součástí bakalářské práce.
Zhodnocení řešení Implementovat reportingový portál jako první komponentu celé architektury se může neobvyklé. Zvlášť pokud se později může ukázat toto řešení jako nedostačující. Východiskem byl předpoklad, že budování celé architektury od návrhu Analytické databáze po implementaci nástrojů pro vizuální zobrazení a distribuci dat bude nějakou dobu trvat. Po 30
tuto banka získala řešení, které lze provozovat paralelně a navíc za relativně nízkých nákladů vyřešila řadu důležitých požadavků, především v oblasti bezpečnosti: -
Snížit pracnost aktualizace reportů
-
Vytvořit proces pro distribuci reportů
-
Zvýšit ochranu reportů/dat proti krádeži
-
Zavést dokumentovaný proces schvalování přístupu k datům
-
Vytvořit proces pro zálohování reportů / analýz / práv
V současnosti portál sice ještě není jediným místem, kde jsou uloženy reporty, ale jeho význam stále roste. 120 uložených materiálů představuje (odhadem) asi 60 % interních reportů banky.
7. DataMarty Datamining Ralph Kimball popisuje DataMart (datové tržiště) jako prezentaci dat z jednoho obchodního procesu [2]. Proti standardním datovým modelům s velmi atomickou strukturou mají DataMarty obvykle silně denormalizovanou strukturu uložení dat. To analytikům šetří čas při agregaci dat a získání pohledu přes jednotlivé dimenze. Ke koncepci procesně orientovaných databází jsme dospěli postupně. Hlavním impulsem byla implementace nástroje IBM SPSS Modeller16 umožňujícího identifikaci významných vazeb mezi pomocí statistických a matematických metod nebo algoritmů na rozpoznání opakujících se vzorů. Většina úloh se zaměřuje na pravděpodobnost výskytu jevu. Typicky firmu zajímá, s jakou pravděpodobností si klient produkt zakoupí, zda odejde či zůstane. Nástroj lze využít i pro popis chování klienta. Např. jak vypadá typický zákazník, který si pořídí daný produkt. Prakticky všechny metody vyžadují vytvoření zcela denormalizované datové věty. Je možné ji vytvořit přímo v Modelleru, ale tím naplno nevyužíváme výkon databázového serveru. Vhodnějším způsobem je nejprve vytvořit DataMart a Modeller využít pouze na hledání 16
IBM SPSS Modeller – dodavatel pro Českou republiku, firma ACREA CR, spol. s.r.o. Bližší informace viz. www.acrea.cz
31
vztahů. Protože obvykle předem nevíme, které údaje jsou významné, je nutné připravit jejich co nejširší množinu. Teprve poté nastupuje Modeller, aby v datech objevil skryté vztahy. Nejsou výjimkou DataMarty o 200 – 300 sloupcích, z nichž se nakonec jen desetina údajů ukáže jako významná. Výstupem metod bývá vyhodnocení pravděpodobnosti jevu v podobě skóre nebo kategorie (segment). Hodnoty jsou uloženy zpět do DataMartů. V bankovnictví jsou tato zjištění využívána pro stanovení sazby úvěru, zařazení klienta do kampaně, ocenění rizik, řízení likvidity apod. Pro splnění těchto požadavků jsou skóre/segmenty exportovány zpět do produkčních systémů. Během pár let vznikla celá řada DataMartů. Některé zahrnují novou produkci za poslední léta, jiné snímkují aktuální portfolio, klientské údaje, vyhodnocují výsledky kampaní, tržní pozici vůči konkurenci, riziková aktiva atd.
Reporty Denormalizovaná databázová struktura se také hodí pro vytváření jednoduchých reportů. Příklad dotazu do databáze MS SQL Server (v jazyce T-SQL) na objem nové produkce: SELECT
YEAR(Datum_podpisu) as Rok, SUM(Vyse_uveru) as Objem
FROM
dtm_Nova_produkce
WHERE
YEAR(Datum_podpisu) > 2010 and Stav_kontraktu = 'Podepsana_smlouva' AND Produkt = 'Hypotecni_uver' AND Distribucni_kanal = 'Vlastní_pobocky'
GROUP BY
YEAR(Datum_podpisu)
ORDER BY
YEAR(Datum_podpisu) DESC
Získat stejné údaje z produkčních databází by znamenalo prohledat tabulky: -
Produktů a v souvisejících číselnících nalézt vhodnou kategorii hypotečního úvěru
-
Stavy kontraktů (smlouva může být ve fázi přípravy, neschválena, odstoupena …). Navíc nejde o jednosměrný proces (žádost – schvalování – podpis …), ale u řady produktů se může workflow zpracování úvěru pozastavit/vracet zpět.
-
Obchodních činností, číselník distribučních kanálů (v tomto případě seznam vlastních poboček), data vyčistit o různé organizační změny. 32
-
Vlastností úvěru – zde se identifikuje částka, která se v průběhu procesu schvalování (i po něm) mohla měnit – nás zajímá poslední údaj platný ke dni podpisu.
Od uživatele by sestavení dotazu vyžadovalo pokročilou znalost SQL, databázového modelu a přidělení přístupových práv ke všem tabulkám (minimálně pro čtení). Nehledě na pracnost dotazu by získal i přístup k datům, která možná ani nemá právo vidět. DataMart je prostředkem, jak uživatelům zpřístupnit agregovaná data přehlednou formou. Se základní znalostí SQL pak uživatel zvládne vytvářet i jednoduché dotazy. Propojením s kontingenčními tabulkami v aplikaci Microsoft Excel lze dosáhnout téměř dimenzionálního zobrazení dat. Např. datum můžeme v MS Excel seskupit na celé dny, měsíce, roky. Podobným způsobem jde pracovat s číselnými formáty a vytvářet skupiny na základě textových hodnot. V současnosti se v reportingovém portálu nachází na 3000 souborů. Do DataMartů banky mají vedle analytiků OMA přístup i někteří uživatelé se základní znalostí T-SQL.
Slabá místa Přes řadu výhod má současná architektura DataMartů i několik nedostatků: -
Problémy s historizací (Řada záznamů se mění i dodatečně – při aktualizaci je nutno ověřovat platnost záznamu. Při přepisu dochází k porušení kontinuity vykázaných hodnot.).
-
Duplicita plnění (Každý DataMart se plní samostatně, obvykle zapouzdřenou procedurou. Údaje z produkčních databází jsou tak získávány opakovaně, čímž dochází k prodlužování doby zpracování a přetěžování serveru.)
-
Slabé vzájemné vazby mezi DataMarty (Agregací dat a nezávislým zpracováním se často snižuje možnost vytvářet další vazby. Např. pokud v úvěru figuruje více klientů najednou, kteří mají své další úvěry, DataMarty poskytují pohled na každý produkt a proces zvlášť. Nedávají však pohled na celý životní cyklus klienta a jeho produktů.).
-
Pracnost zpracování nových úloh (Pro každý nový proces je třeba vytvořit zcela nový Datamart. Veškeré importy, transformace, čištění dat je třeba napsat znovu bez ohledu to, co již mohlo být v minulosti použito pro jiný DataMart.).
33
-
Dimenzionální pohled (V denormalizované datové větě jsou na sobě všechny sloupce nezávislé.). Většina reportů nevzniká na denní bázi. Navíc uživatelé (mimo analytiků OMA) by se svými omezenými znalostmi T-SQL17 některé transformace nezvládli. Proto je vhodné do databáze přidat další sloupečky (týden/měsíc/čtvrtletí/rok, minulý/poslední uplynulý týden/měsíc/rok, rok YTD apod.). Zrychlí se tím tvorba dotazů, ovšem za cenu dalšího místa na disku. Navíc z pohledu MS SQL Serveru neexistuje vztah ani mezi tak základními údaji jako den – týden – měsíc – rok. Samozřejmě existují daleko složitější transformace: – Například zůstatky na různých typech účtů se pro každý proces mohou počítat jinak).
-
Denormalizace komplikuje tvorbu složitějších dotazů (Např. pokud zaměstnanec migroval mezi několika firmami, někde působil současně, jindy měl zcela pauzu a my chceme vyhodnotit jeho produkci po měsících/letech.).
-
Chybějící měřítka (Řadu výpočtů využívá vážených průměrů nebo i složitějších matematických funkcí. Chceme-li pomocí kontingenční tabulky vypočítat průměrnou sazbu za celé portfolio, je v databázi nutno nejprve vytvořit váhu – objem krát sazba. MS Excel totiž nepodporuje řádkové operace (pouze sloupcové). V kontingenční tabulce je tak nutné definovat počítané pole vzorcem: váha děleno objem. MS Excel si nejprve obě hodnoty sečte a teprve pak provede dělení. Výsledkem je vážený průměr sazby.
Budoucnost DataMartů Vzhledem k přednostem DataMartů při zpracování dataminingových úloh i tvorbě jednoduchých reportů se jeví výhodné tuto komponentu BI dále posílit a postupně slabá místa odstranit. Vybudování Analytické databáze umožní datové zdroje sjednotit. Namísto pěti čtení tabulky stavů kontraktu postačí jedno čtení. Data navíc budou historizovaná, což umožní snadné dohledání relevantní sady záznamu pro každý DataMart. Historizace zdrojů také vyřeší problémy s dodatečnými opravami.
17
Typ databázového jazyka využívaný Microsoft SQL Serverem
34
Analytická databáze by od začátku měla obsahovat všechny potenciálně zajímavé údaje, které se v aplikacích objevují, a to bez ohledu, zda jsou nyní data potřeba či nikoliv. V budoucnu se tak urychlí tvorba nových DataMartů. Princip historizace zdrojů navíc umožní získání správných údajů i zpětně po celou dobu života úvěru v portfoliu banky. Komplexnost historizované architektury usnadní i tvorbu složitých dotazů. Posledními úkoly jsou dimenzionální pohled a měřítka. Řešení se nabízí v podobě technologie OLAP18 pravděpodobně postavené na platformě produktů Microsoft [12]. Důvodem je výhodná cena softwaru (obvykle zdarma v rámci licence MS SQL Serveru). Uživatelé upřednostňující přístup z MS Excel by získali přehled o vazbách mezi jednotlivými dimenzemi. Protože v datové kostce probíhají i řádkové výpočty, OLAP by tak odstranil i problém složitého vytváření měřítek. Navíc by se tím otevřela možnost využití DataMartů pro zobrazení reportů v prostředí webového prohlížeče pomocí služeb Analyses Services nebo Performing Point Services.
8. Požadavky na Analytickou databázi (AD) Obecným východiskem byl model požadavků, přičemž pro AD byl dále rozpracován:
Souvislosti Je-li hlavním benefitem celé koncepce zvýšení schopnosti analýz se zaměřením na predikci trendů, je třeba jednotlivé údaje nesledovat samostatně, ale v širším kontextu. Znamená to nesledovat jednotlivé události v úvěru odděleně (např. úspěšnost schválení úvěru nebo úspěšnost změny sazby), ale zachytit všechny možné události najednou a zaměřit se na jejich kauzalitu. Na základě současných DataMartů jsem vybral klíčové entity, které AD musí bezpodmínečně obsahovat:
18
Zkratka pro Online Analytical Processing – druh technologie uložení dat v databázi. Základním cílem je dimenzionalita vztahů mezi daty podporující rychlejší analýzu dat. [12]
35
Tabulka 6 - Požadavky na AD
Entita Osoba
Popis
Širší požadavky
Klient, externí
Nadřízené organizační jednotky, firmy, adresy,
obchodník, zaměstnanec
finanční situace, demografická data Důležité smluvní údaje, popis produktů, údaje o
Soubor služeb, které byly
Úvěr
klientovi dodány
sazbách,
splátkových
plánech,
nákladech,
platbách. Nutno rozlišit pokračování úvěru (refinancování) stejně jako souběh více úvěrů.
Nemovitost
Objekt úvěru nebo
Adresa nemovitostí, popis a vybavení (např.
zajištění
domu, bytu), ocenění Klient požádal, zavolal, řešil …
Událost
Situace, která stojí za
Úvěr byl přeceněn, schválen …
pozornost
Události mohou být jednorázové, opakující se. Lze hodnotit jejich úspěšnost, třídit je. Úvěr je připravován, schválen, splácen, ukončen,
Stav
V jakém režimu se klient
vymáhán …
/ úvěr nachází
Klient či obchodní je nový, neaktivní … Stavy jsou krátkodobé nebo trvalé.
Kampaň
Marketingová komunikace Kdo úvěr / klient
Segmentace
Nejen ve fázi poptávky o úvěr, ale v průběhu celého vztahu i po něm může být klient (i obchodník) objektem marketingové komunikace. Každý prvek lze z různých hledisek klasifikovat.
/obchodní / nemovitost vlastně je?
Zdroj 12 - Vlastní zdroj
Ovšem analytiky zajímají hlavně údaje využitelné pro analýzu event. běžný reporting. Proto byly vynechány údaje jako např.: -
HZL (Přeceňovány na denní bázi, tj. při týdenních updatech nelze řešit pomocí AD.)
-
Účetnictví (Vysoká složitost, nízká přidaná hodnota.)
36
-
Transakce (Banka vede minimum běžných účtů, pohyby jsou bezcenné pro analýzu chování klienta.)
-
Podpisy uživatelů (Informace, kdo jaký záznam editoval, nemá valnou hodnotu. Výjimkou jsou informace k hodnocení úspěšnosti obchodníků nebo vyjednávacího týmu).
Obecnou zásadou je, že vše souvisí se vším. Proto nechceme posuzovat každý úvěr zvlášť, ale celé portfolio klienta najednou, jeho vazby na ostatní klienty, události, které nastaly a jaký vliv měly na jeho důležitá rozhodnutí (např. splatit úvěr). Údaje o finančním hospodaření banky nás zajímají jen omezeně, protože oblast finančního reportingu je příliš složitá a vyžadovala by velmi pracnou integraci účetních dat.
Historizace AD musí zahrnovat nejen současné údaje o klientovi či úvěru, ale rovněž události, které se již staly. Díky tomu bude možné snadno „odrolovat“ úvěr k údajům platným v době žádosti, schválení či jakékoliv změně sazby. Nahradí se tím současné roztříštěné plnění DataMartů, kdy jeden sleduje úvěry v době schvalování, další při změnách sazby, jiný zase míří na aktuální portfolio. Podobný náhled lze vytvořit i na bázi klientů či obchodníků.
Jedna pravda Téma částečně souvisí s historizací a částečně s datovou kvalitou. Každý údaje musí být zpracován transparentním způsobem a tato informace musí být dohledatelná uživateli. Ti jsou pak povinni tyto údaje respektovat a nevytvářet vlastní interpretaci dat (např. filtrováním hodnot podle jiných podmínek).
Centralizace AD musí obsahovat veškeré údaje, které jsou potřebné pro DataMarty, tak aby se vyloučila nutnost jejich separátního plnění. Současně architektura nesmí být natolik složitá, aby nebyla pochopitelná i pro pokročilé uživatele.
37
Časové úspory Pracnost plnění AD nesmí překročit současný provoz DataMartů. Z toho plyne nutnost nahradit ručně vytvářené procedury vhodným ETL nástrojem. Hlavními odběrateli dat zůstávají analytici. Protože nástroje pro analýzu dat vyžadují denormalizovaný model, bude architektura AD v maximální míře zjednodušena.
Technologie Tato oblast sice nebyla obsažena v modelu požadavků, ale byla definována odborem IT. Požadavkem bylo držet jednotnou architekturu. Pro databázi tak byla zvolena platforma MS SQL Server, pro dokumentaci CASE nástroj Sybase Power Designer. Ostatní technologie zůstaly volitelné v závislosti na potřebách AD a finančních možnostech.
9. Konceptuální model AD Vývojové prostředí Sybase Power Designer Sybase PowerDesigner [13] (dále PD) je CASE19 nástroj mj. umožňující návrh datových modelů založený na principu tří architektur (konceptuální, logické, fyzické) a modelování datových skladů. Důvodem použití CASE nástroje byly: -
potřeba přehledného vizuálního zobrazení všech modelů
-
jednoduchost úprav
-
snadné vytváření indexů, klíčů
-
možnost vygenerování skriptu pro vytvoření databáze
-
již existující zakoupené licence PD
-
průběžné vytváření dokumentace (popisy, poznámky)
-
cíl řídit architekturu databází pomocí modelů
19
CASE nástroje představují širokou skupinu programů pro datové modelování, UML, generování kódu a zpětné inženýrství. [14]
38
-
schopnost „zpětného inženýrství“ (automatizace mapování jiných databázi)
-
schopnost spolupráce více lidí
První zkušenost s PD byla velice pozitivní. V minulosti jsme při návrhu DataMartů kreslili schémata na papír, vše prakticky rovnou skriptovali, dokumentace vznikala v MS Excel nebo MS Word a každá změna znamenala pracnou úpravu. PD přinesl přehledné uživatelské prostředí a všechny tyto kroky spojil do jednoho nástroje. S výjimkou několika málo kroků nebylo při návrhu potřeba použít jiný nástroj.
Odlišnosti konceptuálního, fyzického modelu Fyzický model (dále jen FM) je obrazem objektů a jejich vazeb ve skutečné databázi. Zahrnuje jak databáze, tabulky a jejich sloupce, tak i klíče, indexy, procedury apod. FM rovněž respektuje specifika příslušné platformy (tj. by se lišil např. pro MS SQL Server a IBM Informix). K návrhu AD jsme přistupovali ve vyšší míře obecnosti. Počet databází či tabulek nebyl předem daný, natož aby byl znám jejich obsah. Proto jsme hned nevytvářeli fyzický model, nýbrž nejprve konceptuální model. Principem konceptuálního modelu (KM) je vytvořit zjednodušený model budoucí databáze v pojetí, jak je chápou uživatelé. Považuje-li uživatel za důležitou oblast klienta či úvěr, v KM je definujeme jako určitou oblast (entitu) budoucí databáze. Řízenou diskusí se pak jednotlivé oblasti dále upřesňují s rozpadem na jednotlivé entity. Často zpočátku nekreslíme vzájemné vazby mezi entitami (nebo jen velmi obecně), proto model zůstává relativně jednoduchý a tudíž pro uživatele snáze pochopitelný. V PD neplatí žádné oficiální pravidlo ve smyslu, zde končí KM a nyní již tvoříte FM. S rostoucí mírou poznání se přechází do většího detailu, začínají vytvářet vztahy, definovat sloupce budoucích tabulek. Výsledkem může být (a v našem případě i byl) KM již velmi blízký své fyzické podobě. PD dokonce obsahuje tzv. logickým model (dále LM), představující můstek mezi KM a FM. Není však stanoveno, že posloupnost KM LM FM je povinná. Podobně jako v mém případu lze postupným rozšiřováním KM logický model zcela vynechat. PD dokonce umí z KM vytvořit skript pro generování FM pro konkrétní typ databáze (v našem případě MS SQL Server 2008). V takovém případě PD dokonce převede navržené 39
vztahy mezi tabulkami (vč. dědičnosti) na systém jedinečných identifikátorů a navrhne i vhodnou strukturu vazebních tabulek. Změna KM na FM je v prostředí PD opravdu jednoduchá. Pominu-li kontrolu správnosti modelu (neexistují-li tabulky bez sloupců), hlavní rozdíly jsou často formální: Tabulka 7 - Odlišnosti KM a FM
Konceptuální model (označení v PD)
Fyzický model (MS SQL Server)
Entita (Entity)
Tabulka
Atribut (Attribute)
Sloupec
Vztah (Relationship)
Reference
Identifikátor (Identifier)
Primární klíč Index
Nemají ekvivalent
Pohled (view) Indexy Procedury
Zdroj 13 - Vlastní zdroj
Samozřejmě takto automaticky generovaná databáze nemusí ihned odpovídat našim představám. Nicméně stačí jen upravit model a přímo z PD strukturu databáze aktualizovat.
Hlavní entity V části 8. byly definovány tři hlavní entity - osoba (subjekt), úvěr, nemovitost a čtyři podpůrné entity - kampaň, segmentace, událost a stav. Neobvyklostí je chybějící entita produkt. Banka de facto nabízí jen jediný – úvěr, přičemž v rámci jedné úvěrové smlouvy se téměř všechny podmínky (v různý okamžik) mohou vzájemně kombinovat. Výsledkem dlouhá tabulka úvěrových smluv, kde jsou jednotlivé produktové parametry reprezentovány bitovými poli (ano/ne). Toto uspořádání jsem zvolil z důvodu obrovské variability změn na straně produktů (podmínky produktů se různě mění, přitom název zůstává stejný), tak i dlouhé životnosti úvěru (další služby lze sjednat i později). Nejsložitější otázkou bylo, jak definovat vztahy klíčových entit subjekt a úvěr. V jednom úvěru může v jednom či více okamžicích figurovat řada subjektů. Navíc se mohou měnit vlastnosti těchto subjektů. Např. obchodník, který úvěr sjednal, může v době jeho významné události (např. změna sazby) působit pod úplně jiným subjektem. Nebo s bankou již žádný
40
oficiální vztah nemá, ale přesto nelze vyloučit, že je nějakým způsobem v úvěru skrytě angažovaný (působí třeba jako finanční poradce klienta). Podobně složitý je dopad organizačního zařazení úvěru. V každém okamžiku musí být jasné, kdo je za úvěr zodpovědný. Informace navíc pocházejí z různých systémů a ne vždy jsou data vzájemně konzistentní. Nakonec
byla
zvolena
klasická
vztahová
tabulka
spojující
identifikátor
úvěru
s identifikátorem subjektu s doplněním role, ve které se subjekt na úvěru v dané době (viz. historizace) zainteresován. Lze předpokládat, že údržba tabulky bude druhou nejpracnější částí celé architektury. Naopak vztahy mezi úvěrem – nemovitostí či subjektem – nemovitostí jsou v čase velice stabilní a vztahové tabulky budou relativně (menší jednotky miliónů řádků) malé. Tabulky pro segmentace a kampaně sdružují informace z různých zdrojů a především slouží jako vlastní úložiště dat pro analytiky. Každou významnější entitu lze nějakým způsobem klasifikovat (segmentovat), stejně tak úvěr či subjekt může být předmětem kampaně. K oběma entitám je třeba evidovat řadu doplňkových informací, které v čase zastarávají (kdo si pamatuje parametry 10 let staré kampaně). Na druhé straně, tyto entity významně vstupují do života úvěru či subjektu. Úspěšná marketingová komunikace může o pár let později ovlivnit rozhodnutí klienta zůstat či odejít. Zkoumání kauzality těchto událostí je jedním z hlavních cílů celého projektu. Nejpracnější oblastí bylo zachycení událostí. V životě úvěru, subjektu, ale i nemovitosti neustále dochází k událostem. Ty mohou být dlouhodobé (např. příprava úvěru) nebo krátkodobé (reklamace). Získat tyto informace znamená zmapovat všechny zdrojové databáze a z obrovského množství (především systémových) událostí vybrat ty významné. Ty se navíc často rozpadají na procesy (tzv. workflow), ze kterých je třeba identifikovat klíčové milníky, změřit jejich délku, úspěšnost a pokusit se je přehledně zachytit ve zjednodušeném modelu AD. Záznamů je opravdu hodně, data mají různou kvalitu a systémy jsou vzájemně nekonzistentní. Události se opakují, v datech workflow jsou „díry“. Přitom z celkového pohledu jde o naprosto klíčovou entitu, na kterou je zavěšeno odměňování řady lidí. Přes podpis úvěru se hodnotí obchodníci, přes vyjednávání o sazbě úspěšnost 41
přecenění, podle vyřízených dotazů práce call centra. Bude tedy existovat tlak na vysokou přesnost výsledků.
Normalizovaný nebo denormalizovaný model? Např. R. Kimball preferuje dimenzionální model [2] opírající se o faktové tabulky a dimenze. Pro jednodušší model se hodí schéma hvězdy, pro složitější struktury (např. s bohatými číselníky) je vhodnější schéma vločky. Hlavní výhodou modelu je jednoduchost dotazování. Při každé změně dojde ve faktové tabulce k vygenerování nového řádku. Obsah dimenzionálních tabulek se přitom nemění. Nevýhodou může být obtížné zachycení složitějších vazeb. Rizikem je vyšší dynamika změn v datech, která způsobuje bobtnání faktových tabulky. Nakonec jsem navrhl model přísně dodržující 3NF. Struktura se tak stala jednodušší pro plnění databáze pomocí ETL nástrojů, protože jedna cílová tabulka je obvykle plněna nižším počtem zdrojových tabulek. Model lze rovněž snadno rozšiřovat o nové entity. V kombinaci se zvolenou metodou historizace (viz. historizace dat) bude hlavní nevýhodou obtížnější dotazování. Entity bude nutné spojovat nejen pomocí identifikátorů, ale také pomocí časových značek.
Navíc není vždy zaručeno, že v podřízené tabulce existuje související záznam.
Předpokládám, že nad vrstvou AD vznikne vrstva pohledů (views), které budou optimalizovány pro obvyklé úlohy (vlastnosti úvěru/klienta/nemovitosti v době schválení, dnes, v době významných událostí, měsíční snímek za poslední 1 – 3 roky). Tyto pohledy budou také základem pro import dat do cílových DataMartů. Ve výsledku byla zásada normalizace částečně porušena. Je-li hlavní cíl AD datamining (vyžadující denormalizaci), v řadě případů se ukázalo zbytečné tabulku členit na stále nižší úrovně nebo do samostatných číselníků. Technika je vhodná především pro tabulky s malou dynamikou změn, tj. kde nevzniká příliš historizovaných záznamů. V okamžiku, kdy další členění nepřináší přidanou hodnotu vyššího výkonu nebo přehlednosti, je vhodné větvení ukončit a datovou větu ponechat širokou.
Větvení a některé zajímavé entity Základem modelu jsou již zmíněné tři hlavní a čtyři podpůrné entity. Přes zvýšený stupeň denormalizace bylo nutné data každou ze tří hlavních entit rozdělit na řadu menších. Naopak pro podpůrné entity postačilo větvení na 2 – 4 tabulky. Ve finále vzniklo 73 entit.
42
U subjektu je dělení celkem klasické – organizační struktury, detaily právnických či fyzických osob, kontaktní údaje. Vzhledem k zaměření banky přibyl velký soubor finanční historie klienta zahrnující měsíční zůstatky na účtech, doložené příjmy výdaje apod. Ještě bohatší členění se týká úvěru. Vedle podmínek úvěrové smlouvy je řešena především sazba úvěru, splátkový plán, platby a účty. Celková sazba sdělená klientovi je ve skutečnosti součtem dílčích komponent, které vyjadřují náklady na zdroje, kapitál, rizika a různé typy marží. Do výpočtu navíc vstupuje řada parametrů, které je třeba uchovávat. Např. podmínky slev, délky platnosti sazeb, související ukazatele (např. sazba PRIBOR20). V AD je výsledkem vazba mezi úvěrem sazbou jejími komponentami slevami. Jak sazba, tak komponenty mají vlastní vazební tabulky pro parametry. Navíc v průběhu vyjednávání o sazbě se samotná sazba i její komponenty mohou výrazně lišit. Výsledkem je obrovské množství řádků v tabulkách, které jsou však důležité pro pozdější analýzu (např. co jsme klientovi nabídli, co akceptoval apod.). Splátkový plán si lze představit jako soubor parametrů, které vstupují do funkce generující seznam měsíčních splátek do konce zbývajícího života úvěru. Stejně jako u sazeb, zde dochází k četným změnám. V průběhu života úvěru vzniknou i stovky záznamů. Pro banku jde navíc o klíčovou informaci, protože dle splátkového plánu se řídí budoucí očekávané výnosy. Z toho plyne nutnost vytvářet různé hypotetické scénáře budoucího vývoje. Reakcí na tuto potřebu byl návrh speciální tabulky parametrů splátkového plánu, která obsahuje všechny informace platné v době jeho změny. Současně předpokládám vytvoření funkce, která tyto informace dokáže zpracovat (či některou z nich zaměnit), pro celou sadu úvěrů vygenerovat různé verze plánů a ty následně odrolovat k určitému datu a spočítat úrokový výnos, zůstatek jistiny apod. Úvěr má také přímou vazbu na platby a účty. Protože AD nemá sloužit pro standardní finanční reporting, jevilo se vhodné tuto agendu omezit. Ve výsledků banku zajímají pouze zůstatky klientských účtů (myšleno z účetního pohledu, nikoliv běžné účty) k ultimu měsíce. Samotné platby (transakce) byly zjednodušeny na naprosté minimum (např. čerpání do jiného peněžního ústavu). Vzhledem ke složitosti mapování tak nebyl vytvořen můstek mezi účty a jednotlivými platbami, ale obojí nezávisle na sobě vede přímo na entitu úvěr.
20
Prague interbank offer rate (Pražská mezibankovní nabídková sazba) – představuje průměr úrokových sazeb, za které by si české banky mohly vzájemně půjčit.
43
Poslední hlavní entita (nemovitosti) se naopak vyznačuje velkou jednoduchostí a denormalizací. Např. detaily o rodinných domech či bytech (počet místností, sociální zařízení, poloha apod.) byly převedeny do dlouhých datových vět, ačkoli v produkčních databázích se rozpadají do desítek dílčích tabulek. Tyto údaje nikdy neslouží pro reporting, ale datamining (např. segmentace), tj. denormalizace zde byla opravdu na místě.
Identifikátory Všechny tabulky AD obsahují jedinečný identifikátor ve tvaru ID_název_tabulky. Větvení probíhá směrem od hlavních entitních tabulek. Ty jsou velmi jednoduché. Např. tabulka subjekt obsahuje pouze dva sloupce. Jedním je vlastní klíč tabulky a druhým identifikátor používaný hlavní zdrojovou tabulkou v produkčním systému. Nižší úrovně obsahují vždy identifikátor nadřízené tabulky a svůj vlastní. Nejnižší úrovní jsou číselníky využívající identifikátor, který je jako cizí klíč používán i nadřízenou tabulkou.
Diagram targetu Obrázek 7 - Konceptuální model
Zdroj 14 - Zdroje vlastní
44
Pojmenování Vzhledem k jazykové (ne)vybavenosti velké části zaměstnanců byla jako základní jazyk zvolena čeština. V názvech databázových objektů je zakázáno používat interpunkční znaménka. Název tabulky by měl stručně charakterizovat entitu. Počet slov by neměl přesahovat tři až čtyři. U sloupců se tolerují delší názvy. Pro víceslovný název je jako oddělovač používáno podtržítko. První písmeno názvu je vždy velké. Pokud se název skládá z více odlišných prvků, je možné i uprostřed názvu použít velké písmeno. Pohledy (views) obsahují předponu „vw_“, procedury „pd_“, indexy „ix_“, dočasné tabulky „tm“.
Nižší úrovně celé architektury Tato část není předmětem bakalářské práce, proto bude popsána jen obecně. Na nejnižší úrovni stojí databáze produkčních systémů. Z nich budou data získávána pomocí týdenních snapshotů21. Výsledkem bude zrcadlo stavu produkčních dat k ultimu týdne či měsíce. Dodavateli pro tuto fázi budou ostatní oddělení odboru IT. K dodávce bude uzavřena interní SLA. Zrcadlo bude označeno jako Stage-0. Stage-0 bude kompletním obrazem všech tabulek produkčních databází. Pro účely plnění AD však postačí mnohem menší výseč. Můj expertní odhad je 300 - 400 tabulek. Tato data budou v poměru 1:1 přenášena pomocí ETL pumpy do Stage-1. Protože ve zdrojových tabulkách nelze (např. pomocí časové značky) identifikovat změněné řádky, nad Stage-1 vznikne další vrstva Stage-2, která bude uchovávat veškeré záznamy Stage-1 z prvního importu a veškeré budoucí změny Stage-1 (UPDATE, DELETE, INSERT). Podrobněji viz historizace. Stage-2 bude také zahrnovat vlastní data analytiků (např. záznamy o segmentaci klientů/úvěrů). Další vrstvy (Stage-3, 4 …) uvažuji jako pomocné můstky pro výpočet některých entit a současně jako zdroj dat pro vybrané reporty či analýzy. Nepovažuji je za součást finálního 21
Funkce databázového serveru umožňující vytvořit obraz databáze v určitém okamžiku.
45
modelu AD, protože nejde o data trvalého charakteru (při další aktualizaci se smažou a vytvoří znovu). Příkladem může být aktuální verze kompletního splátkového plánu. Údaje jsou důležité jak pro naplnění AD, tak i pozdější analýzy. Výpočet jistě bude časově velmi náročný (možná déle než 24 hodin), přitom informace je platná jen do příští aktualizace. Pro účely analýz může vzniknout i celá řada hypotetických variant. Dalším příkladem mohou být identifikované chyby v datech, které mohou posloužit jako seznam oprav pro administrativní pracovníky. Některé procesy zpracování úvěru se dělí na řadu kroků významných pouze pro okamžitý reporting (např. byla všechna oznámení zaslaná včas?). Nemají však význam pro datamining, tj. nebudou přenášena do AD. Další komponenty ETL procesů (kvalita dat, jejich transformace, slovník metadat, správa číselníků apod.) rovněž přesahují rozsah BP.
Historizace dat Jedním z hlavních problémů současné architektury DataMartů je nemožnost historizace záznamů. Pro AD to má tyto důsledky: -
procesní změny (Např. žádost o úvěr je přijata, posuzována, výsledkem je smlouva. V takovém případě je třeba zachytit jednotlivé události v čase od-do.)
-
zpětné opravy (Nejčastější příčinou je oprava chyb. Je třeba se správně rozhodnout, jakým způsobem se oprava projeví v datech. Jde kupříkladu o údaj, který se s ohledem na externí reporting nesmí měnit?)
-
parametrické změny (Např. se v průběhu schvalování úvěru mění jeho podmínky.)
-
změny v organizačních struktuře firmy (Celé útvary mohou být zrušeny, spojeny, přejmenovány. Podobně mohou migrovat zaměstnanci firmy, na čas z firmy odejdou (např. na mateřskou dovolenou), za čas se vrátí.)
Specifickým příkladem jsou záznamy o transakcích (např. pohyby na účtech), které se obvykle nemění. Historizaci bylo nutno řešit jak pro AD, tak i zdrojové extrakty. Již na začátku bylo zjištěno, že v tabulkách zdrojových databází prakticky neexistují časové značky nebo se na ně nelze spolehnout.
46
Obecně se nabízejí tyto varianty historizace: 1. Historie v základní tabulce 2. Historie od – do přímo v základní tabulce (pole Platnost_od a Platnost_do) 3. Historie v historické tabulce 4. Historie od – do v historické tabulce (pole Platnost_od a Platnost_do) Vzhledem k uvažované aktualizaci AD jedenkrát týdně byl pro obě pole Platnost_od a Platnost_do či variantu Datum zvolen jako dostačující datový typ DATE. Proti typu DATETIME s 8 bity má DATE velikost pouze 3 bity. U největších tabulek se předpokládá až 500 mil. řádků, což představuje úsporu cca 306 MB pro jeden sloupec. Nevýhodou DATE je chybějící podpora datového typu v MS Excel (v současnosti základní nástroj pro reporting v bance). MS Excel (alespoň pro verze 2010 a 2013) formát DATE považuje za textové pole. Je-li nad databází proveden dotaz, datum nutno konvertovat na DATETIME. Poté lze pole využít v kontingenčních tabulkách MS Excel vč. seskupování. Ostatní typy jako např. SMALLDATE nebo DATETIME2 nebyly vůbec uvažovány. V dalším textu uvažuji jednotné výchozí nastavení hodnot pole Platnost_do pro tzv. „neuzavřený“ záznam. Přitom byly zvažovány varianty: -
NULL hodnota (hodnota NULL v SQL představuje prázdný záznam)
-
'2199-12-31' nebo jiné datum z daleké budoucnosti
Nevýhodou NULL hodnoty jsou problémy při agregacích a spojování záznamů. NULL hodnota proti '2199-12-31' sice nezabírá místo na disku, ale ani na největší tabulce tato úspora netvoří více než 20 MB. Přes určité psychologické nevýhody se optimální variantou jeví '2199-12-31'. 1. Historie v základní tabulce Tento způsob jsem zvolil pro tabulky transakčního typu, ve kterých záznamy při aktualizaci zůstávají a očekávají se pouze přírůstkové změny (INSERT). Platnost je pak dána pouze datem události (Datum). V AD se takové tabulky prakticky nevyskytují s výjimkou: -
účetních zůstatků účtů klientů (předpokládá se stav k ultimu týdne/měsíce)
-
pohybů na vybraných účtech klientů (příchozí a odchozí transakce)
-
událostí úvěru (Zde však hodnota může být i relativní, protože např. událost 'Schválení úvěru' vyvolává změnu v tabulce stavů na 'Schválený úvěr', která platí již s časovou značkou Platnost_od/Platnost_do.) 47
2. Historie od – do přímo v základní tabulce Pravidlo se týká tabulek základního modelu, které historii udržují sami v sobě. Tabulka je doplněna o sloupce Platnost_od a Platnost_do. Toto řešení je použito pro 90 % všech tabulek v AD a Stage-2. Při prvním importu dat bude nutno vyřešit otázku, jak nastavit výchozí údaj pro Platnost_od. Nabízí se varianta v podobě dne prvního extraktu, nějakého starého data (např. '1900-01-01') nebo pokusu nalézt co nejsprávnější hodnotu. Na úrovni Stage je řešení jednoduché. Cílem časové značky je umožnit porovnání nového extraktu vůči předcházejícímu. Při prvním extraktu proto stačí všechny Platnost_od nastavit na dnešní datum a Platnost_do na '2199-12-31'. Tato vrstva bude ihned označena jako Stage2. O týden později bude získán další extrakt a uložen do vrstvy Stage-1. Následně proběhne porovnání tabulek napříč všemi sloupci (spojení MERGE). Smazané, upravené záznamy budou ukončeny (Platnost_do změněna na datum extraktu), nové záznamy přidány s časovou značkou '2199-12-31'. Při dalších aktualizacích se vždy obsah Stage-1 nejprve smaže, poté proběhne import dat do Stage-1 a nové porovnání se Stage-2. Jako do určité míry nadbytečný údaj je tabulka doplněna o typ změny (INSERT, UPDATE, DELETE). Protože některé zdrojové tabulky mohou obsahovat až 100 mil. řádků, pro účely MERGE spojení bude třeba záznamy rozdělit na dvě skupiny. První budou porovnány záznamy, kde se nepředpokládá možnost jejich aktualizace. Jde o dávno ukončené smlouvy nebo velmi staré události. Druhou katagorií jsou relativně čerstvé záznamy. Ne ve všech případech bude asi možné tyto kategorie identifikovat, ale významně se tím celý proces zkrátí. Poslední pomůckou bude omezení počtu MERGE spojení pouze na sloupce významné pro plnění AD. Pro samotnou AD je řešení mnohem složitější. Např. sazba úvěru se v čase mění a starší záznamy (byť s určitou chybovostí) je možné získat přímo z produkčních databází. Neexistuje žádný obecný model, takže při plnění targetu tak bylo nutné postupovat u každé tabulky jinak. Lze vysledovat tyto hlavní varianty: -
Ve Stage-2 existují jeden/více záznamů s časovou značkou. V targetu byla Platnost_od (někdy i Platnost_do) nastavena dle Stage-2.
-
Ve Stage-2 existuje více více záznamů bez časové značky. Pravděpodobně bude možné alespoň určit pořadí vzniku záznamů podle jejich identifikátoru (předpokládám přísné dodržování zásady rostoucí číselné řady). Platnost od – do bude nutno
48
posuzovat individuálně. Obvykle na základě nejstaršího a nejmladšího časového údaje v tabulce, někdy ve vazbě na některou událost. -
Ve Stage-2 existuje jediný záznam bez časové značky. Podobně jako v předchozím případě bude nutné nalézt související událost nebo alespoň nejstarší časový záznam v tabulce.
-
V případech, kdy klient ukončil smlouvu, splatil úvěr, odešel z banky apod. bude záznam zcela ukončen. Platnost_do pak bude nastavena na poslední známou událost.
Při aktualizacích se bude nutné vypořádat s případy, kdy v rámci jedno inkrementu získáme k jednomu ID více změn. Typickým příkladem jsou změny sazeb úvěrů. Klient vyjednával o nové sazbě, v pondělí si dohodl podmínky, v úterý získal protinávrh od konkurence a s námi vyjednal slevu. Ve středu přišel s novou žádostí a v pátek se vše teprve uzavřelo. Model historizace nepředpokládá možnost překrývání časových intervalů. Ve Stage-2 by ale šlo o 4 změny získané v jeden okamžik (neděle). Pomocí kombinace ID a časových hodnot v ostatních sloupcích se tedy provede historizace na úrovni dní. Pokud by se v jednom dni objevilo více záznamů, byl by použit ten poslední a ostatní zahozeny. Pro AD (tabulku Sazby) je naopak klíčový poslední záznam. Ovšem i ostatní údaje jsou cenné a slouží jako zápisy do tabulky Události s příznakem, že klient při změně sazby vyjednával, jakého typu vyjednávání (snížení sazby) a s jakým výsledkem (celková sleva). 3. Historie v historické tabulce Princip této varianty spočívá v odkládání všech historických dat do oddělené tabulky. Ta má stejnou strukturu jako základní tabulka. Navíc je obohacena o datum vložení záznamu. Při aktualizaci řádku základní tabulky je záznam nejprve vložen do historické tabulky a teprve potom se aktualizuje základní tabulka. Typické využití je pro zápisy s vysokou frekvencí změny. Např. burzovní data, kurzovní lístek apod. V tabulkách AD pro toto řešení zatím není vhodný kandidát. V typické bance by se takto odkládaly záznamy o transakcích. Protože však běžné účty téměř nevedeme, počet transakcí je tak nízký, že velikost tabulek nepředstavuje problém. Ovšem změní-li se v budoucnu frekvence aktualizace dat z týdenní na denní, otevírá se jedna z možností využití. Tím jsou hypoteční zástavní listy (dále HZL) resp. povinnost banky na denní bázi zajistit krytí každé emise vhodnými zástavami (tzv. krycím blokem). Např. nesplacená hodnota emise HZL činí 700 mil. Kč, kterými byly financovány úvěry v aktuální nesplacené jistině 1000 mil. 49
Kč. Klient navíc musí splacení úvěru zajistit vhodnou nemovitou zástavou. Banka investorům do HZL garantuje, že hodnota nesplacených jistin a hodnota zástav vždy o určité procento přesáhne nesplacenou hodnotu emise HZL. Při běžném fungování banky každý den přicházejí řádné splátky od klientů, občas někdo úvěr splatí předčasně, požádá o změnu objektu zajištění či banka část emise HZL odkoupí zpět. Proto je třeba na denní bázi přepočítávat poměry všech ukazatelů. Navíc banka musí být schopna celý výpočet doložit i zpětně. A právě pro tuto úlohu se ideálně hodí tento princip historizace. Důvodem je vysokých počet detailních záznamů (všechny úvěr – všechny objekty zajištění – aktuální dluh a řada souvisejících údajů o bonitě dlužníků/zástav), které až na občasnou kontrolu ze strany ČNB a auditu nemají jiné využití.
4. Historie od – do v historické tabulce V tomto případě základní tabulka obsahuje pouze aktuální údaje nebo jen omezenou množinu historických dat. Historické záznamy jsou (s časovou značkou v polích Platnost_od a Platnost_do) uloženy do historické tabulky. Princip odkládání dat je trochu podobný Stage-1 a Stage-2. Při aktualizaci základní tabulky jsou nejprve nalezeny řádky určené ke změně. Ty jsou pak zkopírovány (při požadavku na UPDATE) nebo přesunuty (pro DELETE) to historické tabulky. Teprve potom proběhne zápis do základní tabulky. Typickým zástupcem pro toto řešení nejsou transakční data, ale údaje se zvýšenou dynamikou změn. Např. ve fázi přípravy smlouvy se řada informací neustále mění. Ze zdrojových dat je možné získat více záznamů než je pro týdenní reporting nutné. Pro účely analýz pak obvykle postačí jen záznamy platné k důležitým milníkům (např. datu schválení či podpisu smlouvy). Další situací je období, kdy klientovi končí platnost (fixace) sazby a vyjednává o nové. Samotná sazba či splátka se skládají z mnoha komponent a není běžně potřeba vše skladovat. Samozřejmě není důvodem historizovat zcela bezcenné informace. Takové údaje by se ani v rámci ETL procesu neměly do AD dostat. Do AD patří sice i data s nízkým využitím, ale stále potenciálně důležitá. Základní tabulka je tak stane menší, tudíž její čtení bude probíhat rychleji. V rámci AD se tato varianta prozatím nepředpokládá. Dat není tolik a frekvence jejich čtení nízká. Není tedy nutné optimalizovat výkon. 50
10. Srovnání AD s datovým skladem Z pohledu standardní firmy lze AD považovat za datový sklad. Pokud bychom na AD nahlíželi perspektivou srovnatelně velkých bank, skutečný datový sklad by patrně vypadal trochu jinak.
Základní rozdíly Na základě vlastních zkušeností spatřuji tyto hlavní odlišnosti: -
Návrh AD začínal od definice targetu a cestami jeho naplnění. Nikoliv zmapováním zdrojů a následným návrhem vhodného modelu jejich uložení.
-
AD neobsahuje řadu dat transakčního charakteru (tj. například chybí vztah mezi zůstatky účtů a pohyby na účtu. Obě entity jsou AD obsaženy jen částečně a na sobě vzájemně nezávislé.).
-
Není vždy dodržena 3NF (event. schéma hvězdy či vločky). Naopak je preferována denormalizace.
-
Zcela pominuta je identifikace uživatelů (resp. osob, které pořídily záznam).
-
Data nejsou využívána jinými aplikacemi.
-
V AD neexistují produkty. Různé varianty jsou považovány pouze za parametr úvěrové smlouvy.
-
Klientské účty jsou zobecněny na účetní pohled (zůstatek k ultimu období).
-
Omezení počtu entit znamená i omezení počtu budoucích uživatelů reportů (např. chybí data o účetnictví, administrativě apod.).
-
Součástí projektu prakticky není žádný dopad na hardwarovou infrastrukturu.
Shodné prvky -
Komplexnost pojetí
-
ETL procesy (získání, transformace, uložení dat)
-
Princip historizace
51
Metodické zařazení modelu Kam finální model zařadit? Na jedné straně budou data ze zdrojových systémů nejprve získána, poté vyčištěna, upravena a uložena ve 3NF. V modelu lze identifikovat hlavní entitní tabulky stejně jako řadu referenčních tabulek. Na straně druhé jde nepochybně o větší řešení než běžný DataMart. Model rovněž nesplňuje parametry OLAP22 kostky. Vhodné řešení nabízí Obrázek 7, podle kterého lze náš model zařadit na druhou úroveň (označeno jako Floor 2). Obrázek 8- Informační pyramida
Zdroj 15 – Miniberger, 2010 [21].
22
OLAP krychle je způsob organizace dat umožňující dvojrozměrné tabulkové uspořádání. K datům lze přistupovat z různých hledisek (dimenzí) stejným způsobem. Odpadá tak nutnost spojování tabulek. Kostky jsou sice nevhodné pro snadnou úpravu dat, na druhé straně umožňují rychlý výpočet agregovaných ukazatelů. [20]
52
Možnosti upgradu na datový sklad Do budoucna se nabízí možnost rozšíření AD na skutečný DS. Pokud o tomto kroku vedení banky rozhodne, v první řadě bude nutné vyřešit lidské zdroje. Současný tým je tak malý, že nedokáže zajistit SLA na dodávku dat pro jiné systémy nebo více uživatelů. Rovněž by nebyl schopen znalostně pokrýt všechna specifika banky (např. povědomí o vedení účetnictví). Potenciálně nejobtížnější části: -
provázání účetních dat (především OPEX23), vytvoření hlavní knihy
-
zmapování celého procesu od pohybů na klientských účtech, jejich zaúčtování proti pohledávkám banky (splátkovým plánům, poplatkům), vyúčtování přeplatků, vratek, úročení běžných účtů apod.
-
integrace ostatních produktů banky (např. zaměstnaneckých spotřebitelských úvěrů, HZL, starých (novým klientům již nenabízených) běžných účtů)
-
požadavek na vyšší úroveň SLA by patrně přinesl nutnost optimalizace výkonu, vyšší normalizaci, možná i migraci na jinou databázovou platformu
Dle mého odhadu by zahrnutí těchto požadavků na DS vyžadovalo stejnou pracnost, jako vytvoření samotné AD.
11. Závěrečné hodnocení Stav projektu AD K dnešnímu dni se projekt AD nachází ve stavu: -
Je dokončen model targetu.
-
Je jasný způsob importu dat z produkčních databází do Stage-0.
-
Dokončen model zdrojových databází (Stage-1).
-
Víme, které tabulky Stage-1 jsou potřebné k naplnění k naplnění 98 % dat v targetu.
23
Zkratka pro operating expanse (provozní náklady). V bankovním sektoru zahrnuje především mzdy zaměstnanců, náklady pobočkové sítě, licence softwaru, telekomunikační služby apod. Nezahrnuje úrokové náklady (úroky na depozitech klientů), provize externím partnerům, většinu daní, náklady na rizikové úvěry a především odpisy investičních výdajů.
53
-
Máme nasazenou historizační komponentu za účelem pilotního importu 20 tabulek z produkčních databází do Stage-1 a Stage-2. Cílem je zjistit dynamiku změn v datech.
-
Připravujeme se na výběrové řízení ETL nástrojů.
-
Pokračujeme ve zvyšování know-how (např. školení Sybase Power Designeru).
Během roku 2014 počítáme s výběrem dodavatelů pro implementaci ETL nástrojů. Na podzim by měla začít samotná implementace. Ještě letos by měl být hotov slovník metadat, zmapovány číselníky a vytvořeno propojení z produkčních databází do Stage-0.
Dosavadní postup Prakticky až tato BP je prvním komplexním pohledem na dosavadní kroky banky. Ze zpětného pohledu asi nebylo jiné cesty. Přesto vidím několik slabších míst: -
Příliš času bylo stráveno rozvojem DataMartů, a to i v době, kdy bylo jasné, že rozšiřování některých modelů není perspektivní.
-
Implementovat hned na začátku reportingový portál bylo neobvyklé řešení. Vzhledem k vývoji a prioritám to byl rozumný krok. Protože však předběhl samotný model AD, nelze vyloučit, že se portál v budoucnu ukáže jako nedostačující. Vhodnějším postupem mělo být seznámení se s více technologiemi a výběr té optimální.
-
Pokračující nedostatek lidských zdrojů. Tým je malý, a tudíž existuje trvalé riziko nezastupitelnosti. Při rozsahu běžných povinností pak odchod jediného kolegy (a jeho náhrada) může znamenat zásadní zpomalení celého projektu.
Naopak za velmi pozitivní vnímám: -
Shoda na cílech, pro které AD budujeme.
-
Definice omezené velikosti AD (nestavíme datový sklad).
-
Podpora (nejen finanční) od vedení banky celému projektu.
-
Osobní angažovanost celého pracovního týmu a rostoucí podpora IT.
54
Stav DataMartů V současnosti je rozvoj DM omezen a úsilí směřuje k přípravě AD. Neznamená to úplný stop uživatelským požadavkům, ale jejich citlivou prioritizaci. Naštěstí DM jsou již natolik robustní, že realizace požadavků probíhá téměř okamžitě a těch odložených je naprosté minimum. Samotné plnění AD pravděpodobně začne počátkem roku 2015. Vedle hlavních entit počítáme s přednostním zpracováním právě odložených požadavků. Např. požadavek na DataMart pro sledování vazeb mezi klienty (nástroj pro odhalování podvodů) byl odložen právě proto, že datová struktura by téměř kopírovala některé entity AD (adresy, kontakty, vztahy mezi subjekty). Ještě letos by však mělo započít školení vybraných uživatelů s cílem umožnit jim plnohodnotné připojení k DataMartům.
Časová osa
Obrázek 9 - Časová osa
Na závěr shrnutí jednotlivých etap na časové ose: Zdroj 16 - Vlastní zdroj
55
12. Použitá literatura Tištěné monografie [1] Novotný O., Pour J., Slánský D. Business Intelligence Jak využít bohatství ve vašich datech. 1. Vydání. Praha: Grada Publishing, a.s., 2005. ISBN 80-247-1094-3 [2] Kimball R. The Data Warehouse Toolkit. 2. Vydání: Wiley Computer Publishing, 2002. ISBN 0-471-20024-7
Elektronické monografie, webovská sídla, databáze [3]. Balance Scorecard Institute Balance Scorecard. Balance Scorecard Institute [Online] [Citace: 11.3.2014] https://balancedscorecard.org/Resources/AbouttheBalancedScorecard/tabid/55/Default.aspx [4]. Uživatelé Wikipedia http://cs.wikipedia.org/w/index.php?title=Kl%C3%AD%C4%8Dov%C3%A9_ukazatele_v% C3%BDkonnosti&action=history. Klíčové ukazatele výkonnosti. Wikipedia [Online] 13.12.2013. [Citace: 13.3.2014] http://cs.wikipedia.org/wiki/Kl%C3%AD%C4%8Dov%C3 %A9_ukazatele_v%C3%BDkonnosti [5]. Uživatelé Wikipedia http://en.wikipedia.org/w/index.php?title=Enterprise_application_integration&action=history. Enterprise application integration. Wikipedia [Online] 9.3.2014. [Citace: 11.3.2014] http://en.wikipedia.org/wiki/Enterprise_application_integration [6]. Microsoft Technologie IRM. Microsoft [Online] [Citace: 10.2.2014] http://office.microsoft.com/cscz/powerpoint-help/technologie-irm-sprava-pristupovych-prav-k-informacim-v-systemumicrosoft-office-2007-HA010102918.aspx [7]. Microsoft
56
Použití technologie IRM. Microsoft [Online] [Citace: 10.2.2014] http://office.microsoft.com/cs-cz/sharepoint-foundation-help/pouziti-technologie-irm-spravapristupovych-prav-k-informacim-v-seznamu-nebo-knihovne-HA101790607.aspx [8]. Pilař Jan Bezpečnost dat v podobě AD RMS. Zive.cz [Online] 7.11.2013. [Citace: 10.2.2014] http://www.zive.cz/clanky/bezpecnost-dat-v-podobe-ad-rms/sc-3-a-171217/default.aspx [9]. Microsoft Active Directory Domain Services. Microsoft [Online] [Citace: 11.2.2014] http://msdn.microsoft.com/en-us/library/aa362244(v=vs.85).aspx [10]. Microsoft Začínáme se službami Excel services. Microsoft [Online] [Citace: 11.2.2014] http://office.microsoft.com/cs-cz/sharepoint-server-help/zaciname-se-sluzbami-excelservices-a-s-webovou-casti-excel-web-access-HA010377881.aspx [11]. Microsoft Rozdíly mezi používáním sešitu v prohlížeči a v aplikaci Excel. Microsoft [Online] [Citace: 11.2.2014] http://office.microsoft.com/cs-cz/sharepoint-server-help/rozdily-mezi-pouzivanimsesitu-v-prohlizeci-a-v-aplikaci-excel-HA010369179.aspx?CTT=5&origin=HA010377881 [12]. Microsoft Přehled technologie OLAP. Microsoft [Online] [Citace: 11.2.2014] http://office.microsoft.com/cs-cz/excel-help/prehled-technologie-olap-online-analyticalprocessing-HP010177437.aspx [13]. Sybase Czech Products Power Designer. Sybase Czech Products [Online] 7.10.2010. [Citace: 19.2.2014] http://www.sybase.cz/index.php?option=com_content&view=article&id=3&mid=24 [14]. Uživatelé Wikipedia
57
http://cs.wikipedia.org/w/index.php?title=CASE_n%C3%A1stroje&action=history. CASE nástroje. Wikipedia [Online] 2.2.2012. [Citace: 26.3.2014] http://cs.wikipedia.org/wi ki/CASE _n %C3%A1stroje [15]. Uživatelé Wikipedia http://cs.wikipedia.org/w/index.php?title=T%C5%99et%C3%AD_norm%C3%A1ln%C3%AD _forma&action=history. Třetí normální forma. Wikipedia [Online] 19.6.2013. [Citace: 24.3.2014] http://cs.wikipedia.org/wiki/T%C5%99et%C3%AD_norm%C3%A1ln%C3%AD_ forma [16]. Uživatelé Wikipedia http://cs.wikipedia.org/w/index.php?title=Service-level_agreement&action=history. Servicelevel agreement. Wikipedia [Online] 18.12.2013. [Citace: 24.3.2014] http://cs.wikipedia.org/wiki/Service-level_agreement [17]. Microsoft Co je Sharepoint [Online] [Citace: 28.3.2014] http://office.microsoft.com/cs-cz/sharepointfoundation-help/co-je-sharepoint-HA010378184.aspx [18]. Uživatelé Wikipedia http://cs.wikipedia.org/w/index.php?title=SWOT&action=history. SWOT. Wikipedia [Online] 28.11.2013. [Citace: 3.3.2014] http://cs.wikipedia.org/wiki/SWOT [19]. Uživatelé Wikipedia http://cs.wikipedia.org/w/index.php?title=Open_Database_Connectivity&action=history. ODBC. Wikipedia [Online] 10.3.2013. [Citace: 28.2.2014] http://cs.wikipedia.org/wiki/ Open_Database_Connectivity [20]. Uživatelé Wikipedia http://cs.wikipedia.org/w/index.php?title=OLAP_kostka&action=history. Wikipedia [Online] 1.10.2013. [Citace: 13.4.2014] http://cs.wikipedia.org/wiki/OLAP_kostka [21]. Doc. Ing. B. Miniberger, CSc.
58
Modelování a návrh datových skladů, [Prezentace pro studenty BIVŠ] 21.2.2014. [Citace: 12.4.2014]. Jako zdroj uvedena společnost IBM. Zdroj ověřen 13.4.2014, Ballard Ch., Farrell D., Gupta A., Mazuela C., Vohník S., Dimensional Modeling: In a Business Intelligence Environment, 2. vydání, IBM Redbooks 2012. SG24-7138-00 http://www.redbooks.ibm.com/abstracts/sg247138.html?Open
13. Přehled pojmů a zkratek V následující tabulce jsou uvedeny definice pojmů a zkratek používaných v této práci. Pojem
Zkratka
Popis
Třetí normální forma
3NF
Metodika pro návrh datové struktury. Základním principem je eliminace duplicit v tabulkách, nedělitelnost informace ve sloupci (tj. pro každou skupinu dat se snažíme vytvořit novou tabulku). Závislost je dána pouze pomocí primárních/cizích klíčů. [15]
Analytická databáze
AD
Hlavní téma této práce. Viz. kapitola 10. Srovnání AD s datovým skladem
Hypoteční banka, a.s.
banka
Finanční instituce, jejíž datová architektura je v této práci popisována.
Business Intelligence
BI
„Sada procesů, aplikací a technologií jejichž cílem je účelně podporovat rozhodovací procesy ve firmě. Podporují analytické a plánovací činnosti podniků a jsou postaveny na principech multidimenzionálních pohledů na podniková data.“ [1]
Bakalářská práce
BP
DataMart
Viz. kapitola 7. DataMarty
Datamining
Viz. kapitola 7. DataMarty, část Datamining
Extract – transform load
ETL
Pojem pro celý proces získávání dat z provozních databází a externích zdrojů, jejich následné vyčištění, kategorizace, výpočet nových hodnot a uložení do cílové databáze (v našem případě AD).
59
Full-time equivalent
FTE
V kontextu práce myšleno jako přepočtený stav zaměstnanců banky na hlavní pracovní úvazek.
Hypoteční zástavní listy
HZL
Viz. kapitola 9. Konceptuální model AD, části Historizace, bod 3.
Information Rights Management
IRM
Viz. kapitoly [6], [7], [8]
Kompetenční centrum pro data
KCD
Viz. kapitola 4. Projekt Kompetenčního centra pro data
Konceptuální model
KM
Viz. kapitola 9. Konceptuální model AD, Odlišnosti konceptuálního, fyzického modelu
MS Excel Services
ES
Produkt [10] společnosti Microsoft umožňující v prostředí MS Sharepoint aktualizaci souborů typu MS Excel.
MS Sharepoint
Aplikace Microsoftu pro vytváření webu, správu a sdílení dokumentů. Viz. kapitola [12]
Odbor Informační technologie
OIT
Organizační složka banky zodpovědná za správu IT/ICT
Odbor Marketingové analýzy
OMA
Organizační složka banky mj. zodpovědná analýzu dat, reporting
Service-level agreement
SLA
Smlouva mezi poskytovatelem IT služby a jejím uživatelem [16]
Sybase PowerDesigner
PD
Viz. kapitola 9. Konceptuální model AD, části Vývojové prostředí Sybase Power Designer
Stage
Viz. kapitola 9. Konceptuální model AD, části Nižší úrovně celé architektury
SWOT analýza
Metoda umožňující identifikovat silné/slabé stránky, příležitosti/hrozby. Základem je hodnocení jednotlivých faktorů a jejich rozdělení do výše uvedené matice. Viz. kapitola [18]
Target
Hlavní část AD. Viz. kapitola 9. Konceptuální model AD, znázornění v části Diagram targetu
60
14. Seznam použitých obrázků, tabulek Obrázek 1 - Model komponent a požadavků ............................................................................ 16 Obrázek 2 - Hlavní komponenty BI ......................................................................................... 17 Obrázek 3 - Vazby komponent BI ............................................................................................ 17 Obrázek 4- Zjednodušený model architektury ......................................................................... 18 Obrázek 5 - Přehled uvažovaných komponent ......................................................................... 19 Obrázek 6 - Sharepoint - schéma řešení ................................................................................... 30 Obrázek 7 - Konceptuální model .............................................................................................. 44 Obrázek 8- Informační pyramida ............................................................................................. 52 Obrázek 9 - Časová osa ............................................................................................................ 55
Tabulka 1 - SWOT analýza uživatelských požadavků ............................................................. 11 Tabulka 2 - SWOT analýza požadavků vedení ........................................................................ 12 Tabulka 3- SWOT analýza OMA ............................................................................................. 13 Tabulka 4 - Shrnutí požadavků................................................................................................. 14 Tabulka 5 – MS Sharepoint - rozšíření požadavků .................................................................. 21 Tabulka 6 - Požadavky na AD.................................................................................................. 36 Tabulka 7 - Odlišnosti KM a FM ............................................................................................. 40
61
