Vysoká škola ekonomická v Praze

Použití nástrojů Business Intelligence pro finanční a majetkovou analýzu municipalit

Martin Horký

Vysoká škola ekonomická v Praze Fakulta informatiky a statistiky Katedra informačních technologií Studijní program: Aplikovaná informatika Obor: Informační systémy a technologie

TÉMA DIPLOMOVÉ PRÁCE:


Diplomant: Bc. Martin Horký Vedoucí diplomové práce: Ing. Ota Novotný, Ph.D. Oponent diplomové práce: Ing. Petr Toth, Ph.D.

Školní rok 2007/2008

i


Martin Horký

Prohlášení Prohlašuji, že jsem diplomovou práci „Použití nástrojů Business Intelligence pro finanční a majetkovou analýzu municipalit“ zpracoval samostatně a že jsem uvedl všechny použité prameny a literaturu, ze kterých jsem čerpal.

V Praze dne 20. června 2008 ………………………………. Martin Horký

ii


Martin Horký

Abstrakt Diplomová práce se zabývá softwarovou podporou jedné z metod finanční a majetkové analýzy municipalit (FAMA), jejímž spoluautorem je Ing. Petr Toth, Ph.D. z Katedry veřejné správy a regionálního rozvoje Vysoké školy ekonomické v Praze. Tato metoda je v současné době realizována pomocí databázové aplikace Microsoft Access a dvou C++ aplikací ArisDestiller a Prognóza. Cílem práce je zhodnotit současnou programovou podporu metody a realizovat alternativní řešení pomocí nástrojů Business Intelligence. Na základě analýzy produkčních dat a uživatelských požadavků byl pro vybranou část metody realizován pilotní projekt v prostředí Microsoft SQL Serveru 2005. Pozornost byla věnována zejména nástrojům integračních služeb serveru pro práci se XML daty. Přínosem práce je zejména realizace programové podpory metody FAMA pomocí datového skladu, který jako hlavní zdroj dat využívá výkazy o hospodaření municipalit Ministerstva financí ČR ve formátu XML. První část práce se věnuje samotné metodě finanční a majetkové analýzy a současné programové podpory této metody. Druhá část práce obsahuje řešení pilotního projektu Business Intelligence. V závěrečném hodnocení jsou porovnány obě varianty řešení z několika různých hledisek.

Klíčová slova Finanční a majetková analýza municipalit, Business Intelligence (BI), datový sklad, dimenzionální analýza, OLAP, integrační služby, MS SQL Server, Excel, ProClarity

iii


Martin Horký

Abstract This master thesis deals with the software support of one Financial and Property Analysis of Municipalities (FAMA). The co-author of this method is Doctor Petr Toth from the Institute of Public Administration and Regional Development at University of Economics in Prague. The method is supported by Microsoft Access database application and two C++ applications ArisDestiller and Prognoza at present time. The aim of this thesis is to evaluate the present software support of Financial and Property Analysis and implement an alternative solution based on Business Intelligence tools. Chosen part of analysis is implemented in the pilot project using Microsoft SQL Server 2005 environment. The thesis focuses mainly on using integration tools for XML data. The contribution of the thesis is to implement data warehouse, which uses the municipalities‘annual bills in XML format from the database of Ministry of Finance of the Czech Republic. The Financial and Property Analysis method and the current software support are discussed in the first half of this thesis. The other half of the work is dedicated to the Business Intelligence pilot project. Both means of software support are reviewed and compared from various perspectives in the conclusion.

Keywords Financial and Property Analysis of Municipalities, Business Intelligence, Data Warehouse, Dimensional Analysis, OLAP, Integration Servises, MS SQL Server, Excel, ProClarity.

iv


Martin Horký

Poděkování Rád bych na tomto místě poděkoval vedoucímu práce Ing. Otu Novotnému, Ph.D. za vedení diplomové práce a za přínosné rady poskytnuté běhěm její tvorby. Dále bych chtěl poděkovat Ing. Petru Tothovi, Ph.D. za jeho všestrannou podporu a cenné rady zejména k metodice finanční a majetkové analýzy. Své poděkování bych chtěl vyjádřit i svým rodičům a přítelkyni Keiko za podporu ve studiu, trpělivost a vstřícnost, kterou mi během celého studia projevovali.

Martin Horký

v


Martin Horký

Obsah KAPITOLA 1 ÚVOD ..........................................................................................................1 1.1 CÍLE PRÁCE..................................................................................................................1 1.1.1 Vymezení tématu ..................................................................................................1 1.1.2 Současný stav........................................................................................................2 1.1.3 Cíle práce ..............................................................................................................2 1.2 STRUKTURA DIPLOMOVÉ PRÁCE .................................................................................2 1.2.1 Finanční a majetková analýza municipalit............................................................3 1.2.2 Současná programová podpora FAMA ................................................................3 1.2.3 Business Intelligence ............................................................................................4 1.2.4 Řešení FAMA pomocí nástrojů BI .......................................................................4 1.2.5 Zhodnocení variant ...............................................................................................5 1.3 VLASTNÍ PŘÍNOSY ........................................................................................................5 1.4 KOMU JE PRÁCE URČENA ............................................................................................5 KAPITOLA 2 FINANČNÍ A MAJETKOVÁ ANALÝZA MUNICIPALIT..................6 2.1 METODA FINANČNÍ A MAJETKOVÉ ANALÝZY .............................................................6 2.1.1 Časový rozměr analýz...........................................................................................7 2.2 STRATEGICKÁ ANALÝZA A PLÁNOVÁNÍ ......................................................................8 2.2.1 Standardní finanční analýza..................................................................................8 2.2.2 Finanční bonita .....................................................................................................9 2.2.3 Scoring ................................................................................................................10 2.2.4 Rating..................................................................................................................10 2.3 VSTUPNÍ ÚDAJE PRO FAMA OBCE ............................................................................10 2.3.1 Rozpočet .............................................................................................................11 2.3.1.1 Struktura druhového členění rozpočtové skladby.................................................................. 12

2.3.2 Majetek ...............................................................................................................14 2.3.2.1 Výpočet reprodukční hodnoty majetku (Rh) .......................................................................... 14

2.3.3 Dluhová služba a splátkový kalendář .................................................................15 2.3.4 Investice a privatizace.........................................................................................15 2.3.5 Počet obyvatel.....................................................................................................16

vi


Martin Horký

2.4 UKAZATELE FAMA ..................................................................................................17 2.4.1 Základní ukazatele ..............................................................................................17 2.4.2 Propočtové a podílové ukazatele ........................................................................19 2.5 ZDROJE DAT ...............................................................................................................24 2.5.1 Automatizovaný rozpočtový informační systém ARIS ......................................24 2.5.1.1 Webové rozhraní ARISu ....................................................................................................... 25

2.5.2 Přehled údajů FAMA a jejich zdroje ..................................................................26 2.6 ANALÝZA DAT ............................................................................................................26 2.6.1 Metody prognózování .........................................................................................26 2.6.1.1 Prognóza na základě výsledků retrospektivní analýzy .......................................................... 27 2.6.1.2 Prognóza na základě současné znalosti budoucího vývoje .................................................... 29

2.6.2 Formy grafické prezentace..................................................................................29 KAPITOLA 3 SOUČASNÁ PROGRAMOVÁ PODPORA FAMA .............................32 3.1 ÚVODEM.....................................................................................................................32 3.2 DATABÁZOVÁ APLIKACE FAMA 2005 .....................................................................32 3.2.1 Datový model......................................................................................................33 3.3 ZÍSKÁVÁNÍ DAT ..........................................................................................................36 3.3.1 Terénní sběr dat ..................................................................................................36 3.3.2 Automatizovaný sběr dat z ARISu .....................................................................37 3.4 DEFINICE UKAZATELŮ ...............................................................................................39 3.4.1 Základní finanční a majetkové ukazatele............................................................39 3.4.2 Propočtové a podílové ukazatele ........................................................................40 3.5 ANALYTICKÉ NÁSTROJE ............................................................................................41 3.5.1 Analytické možnosti aplikace FAMA 2005 .......................................................42 3.5.2 Aplikace Prognóza..............................................................................................45 3.6 NEDOSTATKY PROGRAMOVÉ PODPORY ....................................................................46 3.6.1 Funkční nedostatky .............................................................................................47 3.6.1.1 Fyzický datový model FAMA 2005 ...................................................................................... 47 3.6.1.2 Řízení přístupu k datům FAMA 2005 ................................................................................... 48 3.6.1.3 Ostatní nedostatky FAMA 2005............................................................................................ 49 3.6.1.4 Podpůrné aplikace ArisDestiller a Prognóza ......................................................................... 49

3.6.2 Technologická omezení ......................................................................................50

vii


Martin Horký

KAPITOLA 4 BUSINESS INTELLIGENCE.................................................................51 4.1 BUSINESS INTELLIGENCE ..........................................................................................51 4.1.1 Transakční a analytické systémy ........................................................................52 4.1.2 Význam datového skladu....................................................................................53 4.2 ARCHITEKTURA BI....................................................................................................54 4.2.1 Komponenty datové transformace ......................................................................55 4.2.2 Databázové komponenty.....................................................................................55 4.2.3 Analytické komponenty......................................................................................56 4.2.4 Nástroje pro koncové uživatele...........................................................................57 4.3 DATOVÝ SKLAD..........................................................................................................58 4.3.1 Požadavky na datový sklad.................................................................................59 4.3.2 Multidimenzionální model..................................................................................59 4.3.2.1 Implementace ........................................................................................................................ 61 4.3.2.2 Uspořádání faktových a dimenzionálních tabulek ................................................................. 63

4.4 DATOVÁ INTEGRACE .................................................................................................65 4.4.1 ETL procesy........................................................................................................65 4.4.2 Extrakce XML dokumentů .................................................................................66 4.4.2.1 Komponenta XML Source..................................................................................................... 66 4.4.2.2 Komponenta XML Task ........................................................................................................ 69

4.5 DODAVATELÉ NÁSTROJŮ BI......................................................................................69 4.5.1 Microsoft Analysis Services ...............................................................................71 4.5.2 ProClarity............................................................................................................71 4.5.3 Přehled analytických produktů ...........................................................................72 KAPITOLA 5 ŘEŠENÍ FAMA POMOCÍ NÁSTROJŮ BI...........................................74 5.1 ÚVODNÍ STUDIE..........................................................................................................74 5.1.1 Definování cílů a efektů BI.................................................................................75 5.1.2 Uživatelé BI ........................................................................................................75 5.1.3 Vymezení rozsahu řešení ....................................................................................76 5.1.3.1 Vybrané ukazatele ................................................................................................................. 76 5.1.3.2 Co není součástí projektu ...................................................................................................... 77

5.1.4 Analýza uživatelských požadavků......................................................................78 5.1.5 Návrh BI architektury .........................................................................................78

viii


Martin Horký

5.2 ANALÝZA DATOVÉHO SKLADU A ŘEŠENÍ BI .............................................................80 5.2.1 Dimenzionální analýza .......................................................................................80 5.2.1.1 Sledované ukazatele .............................................................................................................. 81 5.2.1.2 Sledované dimenze ................................................................................................................ 85 5.2.1.3 Vztah ukazatelů a dimenzí..................................................................................................... 87 5.2.1.4 Databázové tabulky ............................................................................................................... 88 5.2.1.5 Odhad velikosti databáze....................................................................................................... 91

5.2.2 Analýza datových zdrojů ....................................................................................92 5.2.2.1 Výkaz ARIS č. 40 – struktura rozpočtových příjmů.............................................................. 94

5.2.3 Návrh datového skladu .......................................................................................97 5.2.4 Datová transformace ...........................................................................................97 5.2.4.1 Postup plnění datového skladu .............................................................................................. 99 5.2.4.2 Technické a organizační zabezpečení integračních úloh ..................................................... 100 5.2.4.3 Zpracování XML výkazů..................................................................................................... 100 5.2.4.4 Import demografických ukazatelů ....................................................................................... 107 5.2.4.5 Výpočet finančních ukazatelů.............................................................................................. 108

5.2.5 Návrh OLAP kostky .........................................................................................110 5.2.6 Prezentační vrstva .............................................................................................112 5.2.6.1 Kontingenční tabulka........................................................................................................... 112 5.2.6.2 MS SQL Server 2005 .......................................................................................................... 114 5.2.6.3 Microsoft Office Excel 2007 ............................................................................................... 115 5.2.6.4 ProClarity ............................................................................................................................ 117

5.3 ZÁVĚRY A DOPORUČENÍ ..........................................................................................118 5.3.1 Zajištění provozu BI řešení...............................................................................119 5.3.2 Návrhy na rozšíření dimenzionálního modelu..................................................119 5.3.2.1 Odvětvové členění rozpočtové skladby ............................................................................... 119 5.3.2.2 Dynamická kategorizace ukazatelů ..................................................................................... 120

5.3.3 Data mining.......................................................................................................121 KAPITOLA 6 ZHODNOCENÍ ......................................................................................122 6.1 SWOT ANALÝZA .....................................................................................................122 6.1.1 Současná programová podpora.........................................................................122 6.1.2 Řešení pomocí nástrojů BI................................................................................123 6.2 SROVNÁNÍ OBOU PŘÍSTUPŮ......................................................................................125 6.2.1 Shrnutí...............................................................................................................126

ix


Martin Horký

KAPITOLA 7 ZÁVĚR ....................................................................................................128 7.1 VYUŽITÍ ZÁVĚRŮ TÉTO PRÁCE A DOPORUČENÍ.......................................................129 KAPITOLA 8 PŘEHLED LITERATURY A ZDROJŮ..............................................130 8.1 LITERATURA ............................................................................................................130 8.2 ZDROJE DOSTUPNÉ ON-LINE NA INTERNETU ..........................................................131 KAPITOLA 9 TERMINOLOGICKÝ SLOVNÍK........................................................132 9.1 PŘEJATÉ TERMÍNY ZE SLOVNÍKU ČSSI ..................................................................132 9.2 NOVÉ A NOVĚ VYMEZENÉ TERMÍNY ........................................................................136 KAPITOLA 10 PŘÍLOHY .............................................................................................138 10.1 OBSAH PŘILOŽENÉHO CD .....................................................................................138 10.2 REPREZENTATIVNÍ VÝBĚR OBCÍ ...........................................................................138

x


Martin Horký

Seznam obrázků Obrázek 1: Struktura řešení diplomové práce (diagram vlastní) ...........................................3 Obrázek 2: Pozice FAMA v rámci metodiky strategické analýza a plánování .....................8 Obrázek 3: Ukázka prvků bonity a způsob jejich výpočtu v okruhu P..................................9 Obrázek 4: Vstupní údaje pro finanční a majetkovou analýzu (diagram vlastní) ...............11 Obrázek 5: Ukázka druhového členění rozpočtové skladby (diagram vlastní) ...................12 Obrázek 6: Webové rozhraní pro přístup k údajům databáze ARIS....................................25 Obrázek 7: Struktura daňových příjmů města Berouna v roce 1999 ...................................29 Obrázek 8: Míra soběstačnosti města Beroun za roky 1999–2004 v porovnání .................30 Obrázek 9: Příjmy a výdaje města Beroun v letech 1999–2005..........................................30 Obrázek 10: Plánované prodeje majetku města Beroun v letech 2005–2007......................31 Obrázek 11: Hlavní nabídka aplikace FAMA 2005 ............................................................33 Obrázek 12: ERD databáze FAMA 2005 (vlastní úprava modelu) .....................................34 Obrázek 13: Číselník velikostni_skupiny, databáze FAMA 2005 ......................................34 Obrázek 14: Přehled zdrojů dat a jejich zpracování v programovém řešení FAMA...........36 Obrázek 15: Formulář MS Access pro terénní sběr dat.......................................................37 Obrázek 16: ArisDestiller 2005, dialogové okno pro import dat ........................................38 Obrázek 17: ArisDestiller, editace regulárních výrazů........................................................38 Obrázek 18: ArisDistiller 2005, definic základních finančních ukazatelů FAMA..............39 Obrázek 19: Tabulka typ_polozky_rozpoctu s číselníkem a definicí výpočtu základních finančních ukazatelů FAMA. Podle této definice agreguje data i ArisDestiller. ........40 Obrázek 20: Definice propočtových a podílových ukazatelů ve staré aplikaci FAMA 2003 (tabulka ukazatel). Ve verzi 2005 jsou definice implementovány přímo do SQL dotazů...........................................................................................................................41 Obrázek 21: Vztahy mezi nástroji programové podpory analýz (diagram vlastní).............42 Obrázek 22: Ukázka grafického výstupu v aplikaci FAMA 2005 pro ................................42 Obrázek 23: Plánované investice v roce 2004 a 2005 města Moravská Třebová................43 Obrázek 24: Ukazatele podle velikostních skupin aplikace FAMA 2005...........................44 Obrázek 25: Ukázka stínového standardu hodnota majetku na obyvatele ..........................45 Obrázek 26: Ukázka aplikace Prognóza. Vlevo jsou retrospektivní data (1999–2003), vpravo prognóza. .........................................................................................................46

xi


Martin Horký

Obrázek 27: Transformace dat ve znalosti pomocí nástrojů................................................51 Obrázek 28: Obecná koncepce architektury BI (zdroj: Novotný, Pour, Slánský, 2005).....54 Obrázek 29: Příklad struktury 4rozměrné OLAP kostky pro analýzu.................................60 Obrázek 30: Porovnání přístupu MOLAP a ROLAP ..........................................................62 Obrázek 31: Schéma hvězda – star schema (zdroj: Pirkl, 2004) .........................................64 Obrázek 32: Schéma vločka – snowflake schema (zdroj: Pirkl, 2004) ...............................64 Obrázek 33: Datová integrace (zdroj: Pirkl, 2004)..............................................................65 Obrázek 34: Podíl na trhu Business Intelligence .................................................................70 Obrázek 35: Architektura a vybrané produkty BI řešení (diagram vlastní) ........................79 Obrázek 36: Struktura databáze pro uložení zdrojových dat...............................................88 Obrázek 37: Přehled zdrojů vstupujících do datové transformace ......................................93 Obrázek 38: Model datového skladu (export z prostředí SQL Serveru) .............................97 Obrázek 39: Hlavní proces datové integrace. ......................................................................98 Obrázek 40: Postup plnění datového skladu (diagram vlastní) ...........................................99 Obrázek 41: Postup zpracování XML výkazů (diagram vlastní) ......................................101 Obrázek 42: XSL transformace XML výkazů...................................................................102 Obrázek 43: Kolekce schémat SQL Server 2005 ..............................................................106 Obrázek 44: Skupina integračních úloh.............................................................................107 Obrázek 45: Obsah úlohy „Výběr údajů pro reprezentativní výběr ..................................108 Obrázek 46: Data flow úloha „Výpočet základních finančních ........................................109 Obrázek 47: Ukazatele OLAP kostky................................................................................110 Obrázek 48: Hierarchie dimenzí „Územní klasifikace“ (vlevo) ........................................111 Obrázek 49: Použití jednotlivých dimenzí.........................................................................112 Obrázek 50: Kontingenční tabulka datového skladu .........................................................113 Obrázek 51: Jiné zobrazení stejné kontingenční tabulky datového...................................114 Obrázek 52: Ukázka reportovacího nástroje v prostředí MS SQL Serveru 2005..............115 Obrázek 53: Ukázka grafických možností nástroje MS Office Excel 2007 ......................116 Obrázek 54: Aplikace CubeSlice verze 9 – dialogové okno pro výběr .............................117 Obrázek 55: Ukázka prezentačních možností programu ProClarity .................................118

xii


Martin Horký

Seznam tabulek Tabulka 1: Základní finanční ukazatele FAMA ..................................................................18 Tabulka 2: Základní majetkové ukazatele FAMA...............................................................19 Tabulka 3: Ostatní základní ukazatele FAMA ....................................................................19 Tabulka 4: Ukazatele týkající se příjmů ..............................................................................20 Tabulka 5: Ukazatele spadající do skupiny příjmů a výdajů zároveň .................................20 Tabulka 6: Ukazatele týkající se kapitálových výdajů ........................................................21 Tabulka 7: Ukazatele vztahující se k majetku .....................................................................22 Tabulka 8: Ukazatele vztahující se k úvěrům......................................................................22 Tabulka 9: Výpočet dluhové služby ....................................................................................23 Tabulka 10: Přehled údajů FAMA a jejich zdroje...............................................................26 Tabulka 11: Modely trendů regresivní analýzy (zdroj: DP Trunečková, 2006) ..................28 Tabulka 12: Přehled tabulek databáze FAMA 2005............................................................35 Tabulka 13: Porovnání OLTP a OLAP................................................................................53 Tabulka 14: Obsah elementu radek .....................................................................................67 Tabulka 15: Obsah elementu polozka..................................................................................68 Tabulka 16: Obsah elementu velikost..................................................................................68 Tabulka 17: Přehled produktů velkých dodavatelů .............................................................70 Tabulka 18: Vybrané základní finanční ukazatele...............................................................76 Tabulka 19: Vybrané ostatní základní ukazatele .................................................................77 Tabulka 20: Vybrané podílové ukazatele ............................................................................77 Tabulka 21: Přehled vybraných produktů řešení BI ............................................................80 Tabulka 22: Odhad velikosti datového skladu.....................................................................91 Tabulka 23: Přehled zdrojů..................................................................................................93 Tabulka 24: Srovnání současné programové podpory a řešení pomocí nástrojů BI .........125

xiii


Martin Horký

Kapitola 1 Úvod Finanční a majetková analýza je základním východiskem pro tvorbu strategie nejen města, ale i regionů a krajů. Předmětem analýzy je sledování vývoje stavu rozpočtů a majetku municipalit v minulosti (retrospektivní pohled), ale i určení možných scénářů budoucího vývoje (prospektivní pohled). Motivací je poskytnout poklady pro kvalifikované rozhodování zastupitelů obcí o nových investicích nebo privatizaci majetku. Zájmem státu je, aby se obce České republiky rozvíjely a předcházelo se negativním dopadům plynoucím z nezodpovědného hospodaření zastupitelstev, které vedou např. k zadlužení nebo ztrátě majetku a ochromení dalšího rozvoje obce. Pro finanční a majetkovou analýzu jsou vybírány nejrůznější indikátory, které mají pomoci interpretovat hospodaření v minulosti a na základě očekávaných daňových výnosů, předpokládané míry inflace a dalších parametrů predikovat budoucí vývoj. Tato diplomová práce se zabývá softwarovou podporou jedné z metod finanční a majetkové analýzy (FAMA), jejímž spoluautorem je Ing. Petr Toth, Ph.D. z Katedry veřejné správy a regionálního rozvoje Vysoké školy ekonomické v Praze. Tato metoda je v současné době implementována v prostředí databázové aplikace Microsoft Access FAMA 2005.

1.1 Cíle práce 1.1.1 Vymezení tématu Předmětem diplomové práce je zhodnotit využití nástrojů Business Intelligence pro implementaci programové podpory finanční a majetkové analýzy na základě realizace pilotního projektu a závěry plynoucí z tohoto projektu porovnat se současným programovým řešením.

1


Martin Horký

1.1.2 Současný stav Současná programová podpora finanční a majetkové analýzy je realizována pomocí databázové aplikace MS Access „FAMA 2005 a aplikace „Prognóza“, a to podle předem definovaných indikátorů a na daném rozsahu a úrovni agregace kmenových dat. Výsledky analýzy jsou prezentovány v podobě předem připravených tabulkových a grafických sestav bez možnosti jejich změn. Podklady pro analýzy (údaje o hospodaření a majetek municipalit) jsou získávána extrakcí HTML stránek z Automatizovaného rozpočtového systému (ARIS).

1.1.3 Cíle práce Cílem této práce je zhodnotit dosavadní způsob získávání dat a použité nástroje pro jejich analýzu a realizovat alternativní řešení programové podpory finanční a majetkové analýzy municipalit pomocí nástrojů Business Intelligence. Na základě analýzy produkčních dat a uživatelských požadavků bude pro vybranou část metody FAMA realizován pilotní projekt v prostředí Microsoft SQL Server 2005. V závěrečném hodnocení budou porovnány obě varianty – současná programová podpora pomocí aplikace MS Access versus řešení pomocí nástrojů BI. V hodnocení bude pozornost věnována zejména validaci a aktualizaci zdrojových dat získaných z různých zdrojů.

1.2 Struktura diplomové práce Struktura řešení je vizuálně znázorněna na následujícím obrázku (Obrázek 1), v další části následuje popis obsahu jednotlivých kapitol.

2


Martin Horký

Obrázek 1: Struktura řešení diplomové práce (diagram vlastní)

1.2.1 Finanční a majetková analýza municipalit Cílem kapitoly je objasnit rozsah a původ analyzovaných dat metody finanční a majetkové analýzy municipalit a význam jednotlivých finančních a majetkových ukazatelů i v jiných typech analýz v rámci strategické analýzy a plánování. Pozornost bude věnována zejména údajům, které vstupují do analýzy, definici vybraných propočtových a podílových ukazatelů a formám grafické prezentace výsledků. Zmíněny budou rovněž metody retrospektivní analýzy a statistické modely trendových analýz.

1.2.2 Současná programová podpora FAMA Kapitola bude věnována analýze současné programové podpory metody FAMA, která je realizována pomocí databázové aplikace Microsoft Access a dvou C++ aplikací ArisDestiller a Prognóza.

3


Martin Horký

Bude popsáno, jakým způsobem probíhá získávání dat, jejich validace a extrakce pro potřeby analýz. Pozornost bude zaměřena zejména na identifikaci omezení a nedostatků i v perspektivě budoucích požadavků a dále problematice získávání dat z různých zdrojů a v různých formách.

1.2.3 Business Intelligence Obsahem kapitoly bude stručné představení technologie Business Intelligence (BI). Cílem kapitoly je vysvětlit architekturu BI, objasnit základní terminologii a představit techniky modelování datového skladu a význam datové integrace. Pozornost bude věnována zejména nástrojům pro práci s daty ve formátu XML v integračních službách MS SQL Serveru 2005. Závěr kapitoly bude věnován nejvýznamnějším produktům BI na trhu a zejména pak řešením Microsoft Analysis Services a aplikaci ProClarity.

1.2.4 Řešení FAMA pomocí nástrojů BI Druhá části diplomové práce se bude věnovat samotnému řešení finanční a majetkové analýzy pomocí nástrojů BI. Cílem kapitoly je představit pilotní projekt realizovaný v MS SQL Serveru 2005 jako alternativa k současné programové podpoře. V úvodní studii bude vymezen zejména rozsah pilotního projektu a specifikovány uživatelské požadavky. Vycházet se bude především z funkčnosti současné programové podpory. Na základě analýzy zdrojových systémů a požadavků na funkčnost bude navržen dimenzionální model datového skladu (specifikace ukazatelů a dimenzí) a implementovány integrační úlohy (datová transformace). Pro analýzy hospodaření municipalit jsou v současném programovém řešení využívána kmenová data z databáze ARIS ministerstva financí v HTML podobě. Stejná data lze ale získat i ve formátu XML, která budou využita v pilotním projektu. Součástí této kapitoly bude popis struktury zdrojových dat, zejména pak struktura XML výkazů č. 40 systému ARIS, které obsahují data rozpočtových příjmů, výdajů a financování. V závěrečné části budou na vybraném vzorku ukazatelů demonstrovány možnosti tabulkových a grafických výstupů v prostředí Office Excel a ProClarity.

4


Martin Horký

1.2.5 Zhodnocení variant Ze závěrečného hodnocení by mělo být zřejmé, jaké jsou zásadní rozdíly mezi oběma řešeními a jaké výhody/nevýhody by přineslo použití BI.

1.3 Vlastní přínosy Přínosem této diplomové práce by měla být zejména realizace programové podpory metody finanční a majetkové analýzy municipalit pomocí datového skladu, který jako hlavní zdroj dat bude využívat výkazy o hospodaření municipalit Ministerstva financí ČR ve formátu XML. Ze závěrečného srovnání současné programové podpory a řešení pomocí BI by mělo být zřejmé, v čem je řešení BI jiné a proč je vhodné techniky BI použít. Druhořadým přínosem by měla být analýza funkčních a datových omezení současné programové podpory pomocí aplikace MS Access a doporučení, jak tyto nedostatky odstranit.

1.4 Komu je práce určena Práce je primárně určena pro akademické pracovníky VŠE v Praze, kteří se zabývají metodou finanční a majetkové analýzy, ať už v pozici konzultantů metodiky nebo analytiků používající nástroje současné programové podpory. Práce je také zajímavá z pohledu použití technologie Business Intelligence pro finanční analýzy a zejména z hlediska práce se zdrojovými daty ve formátu XML v integračních službách MS SQL Serveru 2005.

5


Martin Horký

Kapitola 2 Finanční a majetková analýza municipalit 2.1 Metoda finanční a majetkové analýzy Finanční a majetková analýza (FAMA) je součástí analytických postupů programu rozvoje municipalit1. Předmětem analýzy je sledování vývoje stavu majetku a finančních prostředků v minulosti (retrospektivní pohled) a určit možné scénáře budoucího vývoje (prospektivní pohled). Analýza sleduje celou řadu aspektů: příjmy a výdaje municipality, investice, stav a vývoj zadlužení, vývoj počtu obyvatel, stav a vývoj majetku a další činnosti a ukazatele týkající se financování obce. Cílem finanční a majetkové analýzy je komplexnější postižení sociálně-ekonomického rozvoje územního celku než při klasické finanční analýze, která je obvykle zaměřena čistě na rozpočtové a úvěrové aspekty. Výsledky FAMA je možné prakticky využít při korekci investičních akcí, návrzích na úvěrování investic, pro rozvahu o nárocích na reprodukci majetku, ke stanovování silných a slabých stránek SWOT analýzy nebo pro tvorbu akčních plánů navržených v rámci strategického plánování. Veškeré klíčové ukazatele, které mají vliv na vývoj obce, by měly být součástí každého programu rozvoje jako nástroje plánování. Metodu FAMA lze také využít pro hodnocení jiných programů, zejména pak pro ex-ante a ex-post hodnocení projektů. Finanční a majetková analýza může být využívána i pro potřeby operativního řízení. Je koncipována jako analytický model, který lze pro konkrétní potřeby upravovat podle specifických potřeb výzkumu (variantní řešení různých trajektorií vývoje, porovnávání jednotlivých variant a výběr optimální varianty). Metodou finanční a majetkové analýzy, která je předmětem této práce, se zabývá Ing. Petr Toth, Ph.D. z Katedry veřejné správy a regionálního rozvoje Vysoké školy ekonomické

1

Finanční a majetková analýza lze použít i pro sledování klíčových ukazatelů hospodaření krajů. V této práci se však zaměříme pouze na oblast analýzy municipalit, která byla předmětem realizovaných softwarových nástrojů.

6


Martin Horký

v Praze. Většina uvedených teoretických poznatků vychází z podkladů a rešerší, které na této katedře vznikaly v průběhu prací na grantu „Financování obcí“ (č. 402/03/1235) v letech 2003–2005. Cílem tohoto grantu bylo zjištění podmínek financování dostatečně velkého a druhově reprezentativního vzorku obcí České republiky s jejich následnou analýzou. V rámci grantových úkolů vznikla také programová podpora pro sběr a analýzu dle této metody.

2.1.1 Časový rozměr analýz Při vypracovávání retrospektivní analýzy se vychází ze skutečných údajů o hospodaření za dané období, přičemž čím delší je zkoumané období, tím větší vypovídací hodnotu má analýza2. Vývoj ukazatelů může být ovlivněn faktory (vnitřními či vnějšími), které působí neopakovatelně či jednorázově (např. sloučení municipalit, větší změna katastru apod.). Takovéto faktory potom naruší plynulost a kontinuitu vývojových tendencí a tedy i použití výsledků analýzy. Prospektivní analýza nejčastěji vychází z retrospektivní analýzy nebo je také sestavována na základě znalostí o vývoji budoucnosti, tj. zejména na základě schváleného rozpočtu, předpokládaném čerpání dotací, prodej majetku nebo také očekávané míry inflace atd. Oba dva přístupy mají své výhody i nevýhody, nicméně podle dr. Totha se v čisté formě výhradně nepoužívá ani jeden z možných přístupů, ale často se volí vhodná kombinace obou. Některé prognózy nelze zcela dobře predikovat, protože vývoj hospodaření dané obci závisí, mimo jiné, např. na vyhlášených dotačních titulech vyšších územněsprávních celků včetně strukturálních fondů EU, mimořádných účelových dotací, ale i na aktivitě zastupitelstva nebo změnách zákonů. Takovéto situace se pak v rámci metody modelují pomocí více variant možného vývoje.

2

„V podmínkách České republiky platí, že jakoukoli retrospektivní analýzu obecních financí lze provést teprve až počínaje rokem 1993, kdy byl zaveden zcela nový systém daní a výrazné změny proběhly i v dotační politice státu.“ (Toth, 2005). Další významnou změnou v této oblasti bylo přejetí zákona č. 243/2000 Sb., o rozpočtovém určení výnosů některých daní územním samosprávným celkům a některým státním fondům (zákon o rozpočtovém určení daní), který nabyl platnosti 1. ledna 2001.

7


Martin Horký

Stanovení obecné tendence ve vývoji sledovaných ukazatelů je podmíněno získáním údajů za delší časový úsek, proto se provádí jak retrospektivní analýza, tak i analýza prospektivní.

2.2 Strategická analýza a plánování Metoda finanční a majetkové analýzy je součástí širších analytických metodik. Pro vyhotovování rozvojových programů menších územních celků se v praxi osvědčila zejména metodika strategické analýzy a plánování. Ta v sobě zahrnuje několik metod použitelných k hodnocení hospodaření municipalit včetně metody FAMA (Obrázek 2). V následujícím textu jsou jednotlivé metody stručně popsány3.

Obrázek 2: Pozice FAMA v rámci metodiky strategické analýzy a plánování (zdroj: Halouzka, publikace Toth, 2005 str. 213, diagram vlastní)

2.2.1 Standardní finanční analýza Standardní finanční analýza je jednou z nejčastějších forem analýz obecních rozpočtů používaných zejména pro úvěrování v bankách. Důraz se klade na zadlužení municipalit. Analýza je založena na posouzení dosavadního vývoje hospodaření (retrospektivní část) a

3

Jiné metody strategické analýzy a plánování pro své výpočty používají stejná nebo velmi obdobná data o hospodaření municipalit jako metoda FAMA. Pokud bude datový sklad pro podporu analýzy finančních a majetkových údajů navržen s tímto vědomím, bude možné jej využít i pro aplikaci jiných analytických metod.

8


Martin Horký

na určení budoucího vývoje rozpočtu v jednotlivých letech (prospektivní část). Metoda se nezabývá analýzou majetku, proto je v diagramu znázorněna jako podmnožina FAMA.

2.2.2 Finanční bonita Metoda finanční bonity municipality má užší záběr než finanční analýza. Je založena na hodnocení pouze předem daných parametrů hospodaření. Posuzování bonity obce vychází z druhové a odvětvové (funkční) struktury rozpočtové skladby, zohledňuje velikostní diferenciaci obcí ale i jejich funkční odlišnosti (např. obce s přenesenou působností). Z obsahového hlediska je finanční bonita rozdělena do tří okruhů: okruh P (příjmy), okruh V (výdaje) a okruh R (kvalita rozpočtového procesu a ostatní charakteristiky hospodaření). Právě okruhem R se finanční bonita liší od standardní finanční analýzy. Hlavním cílem finanční bonity je posouzení dlouhodobé stability, velikosti rozpočtu, efektivnosti a kvality hospodaření a respektování obecných trendů hospodaření municipalit v ČR. Metoda je založena na poměrových ukazatelích. Pro hodnocení bonity jsou určeny tzv. standardy, které slouží k poměřování hodnot jednotlivých obcí, tzv. benchmarking. Z dílčích prvků bonity se vyhodnocuje také tzv. agregátní bonita, což je sumarizace prvků bonity do souhrnného ukazatele, přičemž jednotlivé prvky jsou hodnoceny na základě porovnávání se standardem. Jednotlivé prvky bonity jsou agregovány na základě významu (váhy) ukazatelů.

Obrázek 3: Ukázka prvků bonity a způsob jejich výpočtu v okruhu P (zdroj: Halásek, Pilný, Tománek, 2002)

9


Martin Horký

2.2.3 Scoring Výchozí údaje pro stanovení scoringu municipality jsou vybrané výsledky finanční analýzy obvykle vztaženy k úvěrování. Na jejím základě se určí, zda bude obec v budoucnu schopna plnit své závazky vztahující se k čerpání návratných finančních zdrojů, tj. úvěrů. Scoring určitým způsobem umí stanovit kvalitu obecního rozpočtu. Pro jeho vyjádření se používá bodové hodnocení sledovaných hodnot finančních ukazatelů. Každý ukazatel má přitom danou váhu v celkovém hodnocení podle významu či důležitosti. Na základě scoringu se bodově ohodnotí jednotlivé subjekty, z nichž se následně sestaví žebříček, tzv. ranking. Tuto metodu v praxi využívají např. komerční banky k hodnocení kvality klienta, tedy hodnocení rizikovosti úvěru nebo-li stanovení pravděpodobnosti neplnění závazků.

2.2.4 Rating Součástí ratingu je vedle finanční analýzy řada dalších podrobnějších analýz, které se týkají ekonomického prostředí municipality, demografického vývoje nebo politického a administrativního systému municipality atd. Závěry analýz jsou prezentovány udělením známky, která má podobu písmen abecedy řazených do tzv. ratingové stupnice v pořadí od nejlepšího k nejhoršímu (např. Aaa, Aa+, Aa, Aa–, A až C–).

2.3 Vstupní údaje pro FAMA obce Podkladem pro finanční a majetkovou analýzu jsou údaje o vývoji klíčových ukazatelů v následujících šesti oblastech vztahujících se k dané municipalitě4:

rozpočet

majetek

dluhová služba a splátkový kalendář

investice a privatizace (prodeje majetku)

počet obyvatel

10


Martin Horký

Obrázek 4: Vstupní údaje pro finanční a majetkovou analýzu (diagram vlastní)

2.3.1 Rozpočet Obsahem rozpočtu municipality jsou její příjmy a výdaje a ostatní peněžní operace, včetně tvorby a použití peněžních fondů. Členění rozpočtů územních samospráv je v České republice stanoveno rozpočtovou skladbou, kterou upravuje vyhláška ministerstva financí5. Dle této vyhlášky jsou rozlišovány čtyři hlediska třídění příjmů a výdajů: hledisko odpovědnostní, druhové, odvětvové (dříve nazývané funkční) a konsolidační. Pro potřeby metody finanční a majetkové analýzy je využíváno druhové členění rozpočtů, ze kterého se vychází pro stanovování základních, propočtových a podílových ukazatelů (viz kapitola 2.4 Ukazatele FAMA). Dle tohoto hlediska se všechny příjmy a výdaje třídí podle příjmových a výdajových druhů. Nejvyššími jednotkami třídění jsou rozpočtové třídy, které se dále rozpadají na seskupení rozpočtových položek a ty dále na podseskupení rozpočtových položek. Nejnižší jednotou třídění jsou rozpočtové položky. Rozpad znázorňuje následující obrázek (Obrázek 5).

4

S obdobným rozsahem podkladových údajů pracuje také metoda finanční a majetkové analýzy krajů, která již ale není předmětem této práce. 5 Vyhláška č. 323/2002 Sb., o rozpočtové skladbě ve znění vyhlášky č. 568/2002 Sb., vyhlášky č. 484/2003 Sb., vyhlášky č. 440/2006 Sb., vyhlášky č. 233/2007 Sb. a vyhlášky č. 306/2007 Sb. Ministerstvo upravilo tuto problematiku dle §2 odst. 4 zákona č. 218/2000 Sb. o rozpočtových pravidlech a o změně některých souvisejících zákonů (rozpočtová pravidla), a dle §12 odst. 1 zákona č. 250/2000 Sb., o rozpočtových pravidlech územních rozpočtů. Aktuální rozpočtová skladba je dostupná on-line na adrese: http://www.mfcr.cz/cps/rde/xchg/mfcr/xsl/vyhlasky_32273.html

11


Martin Horký

Obrázek 5: Ukázka druhového členění rozpočtové skladby (diagram vlastní) Druhové členění rozpočtové skladby umožňuje stanovit základní ukazatele, které budou jednotné pro každou obec a budou vzájemně srovnatelné. Druhové členění bylo zvoleno také vzhledem ke skutečnosti, že všechny obce v České republice mají povinnost vést evidenci v tomto členění.

2.3.1.1 Struktura druhového členění rozpočtové skladby Každou peněžní operaci lze zařadit do některého ze tří okruhů: příjmy, výdaje a financování6. Pro názornost uvádíme přehled druhového členění rozpočtové skladby na úrovni tříd a seskupení. Příjmy

Třída 1 – daňové příjmy o 11 – Daně z příjmů, zisku a kapitálových výnosů o 12 – Daně ze zboží a služeb v tuzemsku o 13 – Daně a poplatky z vybraných činností a služeb o 15 – Majetkové daně o 17 – Ostatní daňové příjmy

Třída 2 – nedaňové příjmy o 21 – Příjmy z vlastní činnosti a odvody přebytků organizací s přímým vztahem

6

Úplné členění dle druhového rozpočtové skladby dostupné on-line na adrese: http://www.mfcr.cz/cps/rde/xchg/mfcr/xsl/vyhlasky_2169.html?year=2002#B

12


Martin Horký

o 22 – Přijaté sankční platby a vratky transferů o 23 – Příjmy z prodeje nekapitálového majetku a ostatní nedaňové příjmy o 24 – Přijaté splátky půjčených prostředků

Třída 3 – kapitálové příjmy o 31 – Příjmy z prodeje dlouhodobého majetku a ostatní kapitálové příjmy o 32 – Příjmy z prodeje akcií a majetkových podílů

Třída 4 – přijaté dotace o 41 – Neinvestiční přijaté dotace o 42 – Investiční přijaté dotace

Výdaje

Třída 5 – běžné výdaje o 50 – Výdaje na platy, ostatní platby za provedenou práci a pojistné o 51 – Neinvestiční nákupy a související výdaje o 52 – Neinvestiční transfery podnikatelským subjektům a neziskovým organizacím o 53 – Neinvestiční transfery a některé další platby rozpočtům o 54 – Neinvestiční transfery obyvatelstvu o 55 – Neinvestiční transfery do zahraničí o 56 – Neinvestiční půjčené prostředky o 57 – Neinvestiční převody Národnímu fondu o 59 – Ostatní neinvestiční výdaje

Třída 6 – kapitálové výdaje o 61 – Investiční nákupy a související výdaje o 62 – Nákup akcií a majetkových podílů o 63 – Investiční dotace o 64 – Investiční půjčené prostředky o 67 – Investiční převody Národnímu fondu o 69 – Ostatní kapitálové výdaje

Financování

Třída 8 – financování

13


Martin Horký

o 81 – Financování z tuzemska o 82 – Financování ze zahraničí o 89 – Opravné položky k peněžním operacím

2.3.2 Majetek Prostřednictvím majetku obec zabezpečuje uspokojování potřeb obyvatel zejména v oblasti veřejných statků (školy, nemocnice, infrastruktura), ale i soukromých statků (kino, koupaliště). Majetek je nástrojem pro zvyšování příjmů (pronájem, prodej majetku), zvyšování konkurenčního prostředí nebo ovlivňování urbanistického rázu obce. Základem majetkové evidence je jeho soupis a ocenění. Ocenění majetku je poměrně komplikované. Majetek je totiž evidován v rozvaze aktiv a pasiv v pořizovacích cenách, které často neodpovídají jeho skutečné (reprodukční či tržní) hodnotě a nezohledňuje míru opotřebení. To je způsobeno jednak výraznou změnou cen majetku v období přechodu ekonomiky z centrálně plánované k tržní a také tím, že obce majetek nemusí odepisovat. Veškeré informace o majetku včetně např. technické dokumentace je možné nalézt v pasportu majetku. Obce však většinou pasport majetku nevytvářejí, a proto je nutné jako zdroj informací pro finanční a majetkovou analýzu využít rozvahu aktiv a pasiv. Nicméně pro kvalifikovanou analýzu je nutné zjistit další informace šetřením přímo v dané obci. To se týká také souvislostí majetkové evidence s investičními záměry a záměry prodeje majetku.

2.3.2.1 Výpočet reprodukční hodnoty majetku (Rh) Kromě uvedených ukazatelů získaných z rozvahy aktiv a pasiv se pro účely majetkové analýzy provádí také výpočet tzv. reprodukční hodnoty majetku obce (Rh): Rh v r. X = (Hpoř. v r. X – H poř. po r. 1990) * W + H poř. po r. 1990 Reprodukční hodnotu majetku v roce X (Rh v r. X) získáme tak, že se od hodnoty majetku staveb (řádek 18 rozvahy aktiv a pasiv) se ve výchozím roce X (Hpoř v r.X) odečte hodnota staveb pořízená po roce 1990 – tu je zapotřebí získat odhadem ve spolupráci s příslušným městským či obecním úřadem. Výsledek se vynásobí proměnnou W (v současné době odhadováno W=4) a k výsledku se připočte hodnota staveb pořízená po r. 1990.

14


Martin Horký

Takto zjištěná reprodukční hodnota „redukovaného“ dlouhodobého majetku obce ve výchozím roce šetření se pak použije pro odhad vývoje majetku obce v dalších letech. Odhad budoucí hodnoty majetku obce např. v roce X+1 je pak získán jako součet vypočtené reprodukční hodnoty majetku v roce X a hodnoty investic Inv plánovaných na rok X+1 při odečtení plánovaných privatizací (prodeje) majetku Priv. Stejně se postupuje i v letech následujících. Reprodukční hodnota majetku obce v roce X+1: Rh v r. X+1 = Rh v r. X + Invv r. X+1 – Privv r.X+1 Potřeba výdajů na reprodukci obecního majetku v letech X+1 až X+n se vypočte jako % podílu z reprodukční hodnoty majetku obce v daném roce. Budeme uvažovat s potřebou reprodukce na námi stanovené úrovni (např. 2 %, 3 %, 4 %, 5 % z Rh). Pokud stavby (řádek 18 rozvahy aktiv a pasiv) obsahují stroje a zařízení, je nutné zjistit, zda je můžeme získat z řádku 24, eventuálně jinde.

2.3.3 Dluhová služba a splátkový kalendář Dluhovou službou se rozumí úroky, splátky jistin, směnky, splátky leasingu a při jednorázových splátkách splatných po uplynutí dohodnuté lhůty či poměrná část splátky za jeden rok. Sledování rozsahu a výše úvěrů, způsobu splácení jistin a úroků je důležité pro budoucí vývoj obecních financí. Pokud obec přijme úvěr, vzniká jí závazek, který bude muset dříve či později splatit věřitelům. Je proto důležité, aby úvěr pro municipalitu nepředstavoval v budoucích letech příliš velkou zátěž, která by mohla mít dopad na strukturu výdajů. Pro plánování splátek úvěru a pro přehled stavu zadlužení municipality je třeba mít zpracovaný splátkový kalendář. V rozpočtové skladbě je dluhové službě věnována pozornost v 8. třídě – financování a 5. třídě (úroky), z jejichž údajů se vychází pro finanční a majetkovou analýzu.

2.3.4 Investice a privatizace Investiční a privatizační programy (programy prodeje majetku) zpravidla vychází ze strategických cílů municipality a z rozvojových záměrů, které jsou často zpracovány do

15


Martin Horký

programů rozvoje obce nebo existují jako samostatné dokumenty. Při sestavování rozpočtu je třeba brát v úvahu pořizování investic a očekávané příjmy z prodeje nemovitostí. V případě investic se obec rozhoduje, zda bude rozšiřovat svůj stávající majetek (rekonstrukce, úprava) nebo bude investovat do novostavby a zjišťuje, zda na takovýto záměr bude mít dostatek finančních zdrojů. Pro zpracování investičních a privatizačních programů pro účely prospektivní finanční a majetkové analýzy se používá zápis v podobě tabulky, kde v řádcích jsou plánované investiční akce a ve sloupcích roky realizace jednotlivých aktivit. V průsečíku řádků a sloupců jsou potom uvedeny plánované sumy částek (v tis. Kč).

2.3.5 Počet obyvatel Ukazatel o počtu obyvatel, resp. vývoj počtu obyvatel, vypovídá o velikosti obce a slouží zejména pro vyhodnocení podílových ukazatelů FAMA. Pro zpracování prospektivní analýzy vychází odhad vývoje počtu obyvatel v budoucnosti z dosavadního vývoje (data alespoň za 5 let). Prognózu je vhodné konzultovat s odborníkem v oblasti demografie. Data o vývoji počtu obyvatel se pro účely FAMA získávají z evidence Českého statistického úřadu7.

7

Dostupné on-line na adrese: http://www.czso.cz

16


Martin Horký

2.4 Ukazatele FAMA Metoda finanční a majetkové analýzy municipalit8 pracuje s ukazateli, které lze rozdělit do dvou skupin:

základní ukazatele – odpovídají druhovému členění rozpočtové skladby (finanční ukazatele) a položkám rozvahy aktiv a pasiv (majetkové ukazatele)

propočtové a podílové ukazatele – vycházejí ze základních ukazatelů, které jsou různě kombinovány

Jak uvádí dr. Toth (2005) k metodě FAMA, v hospodaření municipalit neexistuje jednotnost a shoda, protože každá municipalita je zcela jedinečný subjekt. „Z tohoto pohledu je postup finanční a majetkové analýzy nutno chápat jako model, který se bude realizovat na základě místních podmínek. Jednotlivé ukazatele lze potom ubírat, přidávat či měnit.“

2.4.1 Základní ukazatele Základní ukazatele9 vycházejí z druhového členění rozpočtové skladby (viz tabulka Tabulka 1) a jsou v rámci metody stanoveny tak, aby při jakýchkoliv menších změnách rozpočtové skladby nemuselo vždy docházet k jejich předefinování. U každého ukazatele je v tabulce v prvním sloupci uvedeno číslo řádku, které je použito v definici propočtových a podílových ukazatelů (viz následující kapitola).

8

Podobné základní ukazatele jsou v rámci metody definovány i pro finanční a majetkovou analýzy krajů. FAMA krajů však není předmětem této práce. 9 Finanční ukazatele municipalit, které jsou pro analýzu veřejných rozpočtů klíčové, vlastně odpovídají Key Performance Indicators (KPIs).

17


Martin Horký

Tabulka 1: Základní finanční ukazatele FAMA10 Číslo řádku

Přiřazení položek dle platné druhové rozpočtové skladby

Název položky

1 2 3 4 5 6 7 8

DPFO ze závislé činnosti DPFO z vlastní činnosti DPPO bez obcí DPPO za obce DPH Daň z nemovitosti Místní poplatky Daňové příjmy celkem

9 10

Příjmy z vlastní činnosti 2111–2199 Neinvestiční dotace 4111–4199 (bez převodu z vlastních fondů) (odečíst podseskupení 413) Běžné (opakující se) příjmy – ř. 8 + 9 + 10 mezisoučet Ostatní příjmy 2210–2999 Kapitálové příjmy 3111–3999 (třída 3) z toho z prodeje dlouhodobého 3111–3119 majetku Kapitálové dotace 4211–4299

11 12 13 14 15 16 17

1111 1112–1119 1121 1122 1211 1511 1341–1349 1111–1999 (třída 1)

18

Příjmy celkem (po konsolidaci) ř. 11 + 12 + 13 + 15 Běžné výdaje (bez převodu z vlastních 5011–5999 fondů) (odečíst podseskupení 41311) Investiční výdaje 6111–6999

19 20 21 22

Výdaje celkem (po konsolidaci) Financování Příjmy z úvěrů a obligací Splátky úvěrů a obligací

23

Výsledek hospodaření

ř. 17 + 18 8111–8999 (třída 8) 8121+8123+8221+8223 8112+8114+8122+8124+8212+ 8214+8222+8224+5141+5142+ 5143+5149 ř. 16 – 19 (+ 20)12

Zdroj: Toth, 2005, aktualizováno dle nastavení databáze FAMA 2005

10

Tabulka 1: Základní finanční ukazatele FAMA vychází z přehledu uvedeného ve výstupu grantu „Financování obcí“ (Toth, 2005, str. 225) a je aktualizována do podoby, která odpovídá aktuálnímu nastavení programových nástrojů pro analýzy FAMA (ArisDestiller). Tabulka se oproti verzi v uvedené publikaci liší zejména v intervalech položek, kde pro další předpokládaný vývoj druhové rozpočtové skladby jsou uvažované všechny teoretické položky v rámci seskupení nebo třídy, i když v současné době nejsou dle vyhlášky definovány. Tj. např. místo původního výpočtu řádku č. 12 „Ostatní příjmy“ z položek č. 2100– 2482 se uvádí rozsah 2210–2999. Je to z důvodu vyšší pružnosti, v budoucnosti je možný vznik nové položky spadající do nedaňových příjmů. 11 Resp. podseskupení 534 (stejná hodnota na straně výdajové). 12 Výpočet výsledku hospodaření v aplikaci FAMA 2005 nezohledňuje financování. Definice výsledku financování je tedy pouze „ř. 16 – ř. 19“. Je to z toho důvodu, že nástroj pro import dat ze systému ARIS ArisDestiller neumí zpracovat výkaz 40–3 financování.

18


Martin Horký

Majetek je v rámci metody FAMA sledován prostřednictvím čtyř základních ukazatelů (viz Tabulka 2). Tabulka 2: Základní majetkové ukazatele FAMA Číslo řádku

Řádek rozvahy aktiv a pasiv

Položka

24

Stavby

18

25

Dlouhodobý hmotný majetek (součet řádků 16 až 25 rozvahy)

26

26

Majetek celkem (aktiva stálá i oběžná)

125 Zdroj: Toth, 2005, aktualizováno dle nastavení databáze FAMA 2005

Tabulka 3: Ostatní základní ukazatele FAMA Číslo Položka řádku 27

Zdroj údajů ČSÚ

Počet obyvatel obce

Zdroj: Toth, 2005

2.4.2 Propočtové a podílové ukazatele Význam propočtových a podílových ukazatelů je zejména zjednodušit a zpřehlednit indikátory hospodaření municipalit a umožnit jejich komparaci. Propočtové a podílové ukazatele mohou vypovídat např. o vybavenosti obce službami, bohatství atd. Relativizování ukazatelů se provádí nejčastěji k počtu obyvatel nebo k hodnotě majetku. Jednotlivé ukazatele lze rozdělit do následujících pěti skupin (dle DP Trunečkové, 2006):

ukazatele týkající se příjmů

ukazatele týkající se kapitálových výdajů

ukazatele spadající do skupiny příjmů a výdajů zároveň

ukazatele vztahující se k majetku

ukazatele vztahující se k úvěrům

19


Tabulka 4: Ukazatele týkající se příjmů Ukazatel Celkové příjmy na 1 obyvatele = celkové příjmy (16) / počet obyvatel (27)

Vlastní příjmy na 1 obyvatele = celkové příjmy (16) – kapitálové dotace (15) – neinvestiční dotace (10) – příjmy z úvěrů a obligací (21) / počet obyvatel (27) Daňové příjmy na 1 obyvatele = daňové příjmy celkem (8) / počet obyvatel (27) Míra soběstačnosti = celkové příjmy (16) – kapitálové dotace (15) – neinvestiční dotace (10) – příjmy z úvěrů a obligací (21) / příjmy celkem (16) * 100 [%] Míra závislosti na neopakovatelných příjmech = { 1 – (běžně se opakující příjmy (11)) / příjmy celkem (16) } * 100 [%]

Martin Horký

Význam ukazatele v analýze Ukazatel finanční síly obce. K určitému zkreslování při komparaci obcí může docházet v důsledku toho, že do celkových příjmů jsou zahrnuty i vnější zdroje jako jsou úvěry a dotace. Ukazatel slouží k porovnávání finanční síly obce s ostatními municipalitami. Vlastní příjmy jsou očištěné od úvěrů a dotací, ukazatel je tudíž vhodnější a přesnější než vyjádření celkových příjmů na obyvatele. Ukazatel vyjadřuje daňová výtěžnost obce v přepočtu na obyvatele, tj. příjmy z daní a místních poplatků přepočítaných na jednoho obyvatele. Ukazatel představuje procentní zastoupení vlastních příjmů v příjmech celkových. Vlastní příjmy vznikly očištěním celkových příjmů od dotací a úvěrů. Ukazatel informuje o míře závislosti rozpočtu obce na běžně se neopakujících příjmech. Jde tedy o závislost na ostatních příjmech, kapitálových příjmech a investičních dotacích. Mezi další ukazatele může patřit např. ukazatel běžné příjmy na obyvatele. Zdroj: Toth, 2005; tabulka vlastní.

Tabulka 5: Ukazatele spadající do skupiny příjmů a výdajů zároveň Ukazatel Význam ukazatele v analýze Ukazatel informuje o tom, jak je obec Míra krytí běžných výdajů = běžné výdaje (17) / běžně se opakující schopna krýt běžné výdaje z běžně se příjmy (11) * 100 [%] opakujících příjmů nezávisle na čerpání cizích prostředků, kapitálových příjmech a příjmech ostatních. Uvádíme obvykle v procentech. Ukazatel informuje do jaké míry je obec Míra krytí běžných výdajů z daňových příjmů13 schopna financovat běžné výdaje pouze z = daňové příjmy celkem (8) / běžné výdaje daňových příjmů. (17) * 100 [%] Zdroj: Toth, 2005; tabulka vlastní.

13

Ukazatel, který není uveden ve výstupu z projektu GAČR 402/03/1235 Financování obcí dr. Totha (2005), ale patří k dalším indikátorům hospodaření metody FAMA.

20


Martin Horký

Tabulka 6: Ukazatele týkající se kapitálových výdajů Ukazatel Význam ukazatele v analýze Ukazatel informuje o přebytku celkových Hrubá úspora = příjmy celkem (16) – běžné výdaje (17) příjmů nad běžnými výdaji rozpočtu, a tedy o prostředcích, které můžeme využít na financování investičních výdajů. Zprostředkovaně informuje o tom, jak je nezávislá na cizích zdrojích. Protože celkové příjmy obsahují také příjmy z externích zdrojů, které musíme splácet, nezle celý objem hrubé úspory použít na investice. Ukazatel informuje o objemu přebytku Čistá úspora = příjmy celkem (16) – běžné výdaje (17) rozpočtových příjmů nad běžnými výdaji, – splátky úvěrů a půjček (22) který může být celý využit na financování investic. Jedná se vlastně o hrubou úsporu sníženou o splátky úvěrů a půjček. Ukazatel informuje o rychlosti reprodukce a Investiční podíl = investiční výdaje (18) / celkové výdaje rozvoje majetku obce; vyjadřuje podíl (19) *100 [%] kapitálových výdajů na výdajích celkových. Udává kolik haléřů z jedné koruny celkových výdajů obec investuje. Pro zdravý rozvoj obce by se měl ukazatel pohybovat kolem 20 %, v ČR až kolem 30 % (Trunečková, 2006). Ukazatel vyjadřuje podíl finančních Míra samofinancování investic = hrubá nebo čistá úspora / investiční prostředků z hrubé nebo čisté rozpočtové výdaje (18) * 100 [%] úspory, použitelných na krytí investičních výdajů. Informuje do jaké míry je obec schopna Míra krytí kapitálových výdajů kapitálovými příjmy13 financovat investiční výdaje z těchto = kapitálové příjmy (13) / investiční příjmů, především z prodeje majetku. výdaje (18) * 100 [%] Informuje do jaké míry je obec schopna Míra krytí kapitálových výdajů z kapitálových dotací13 financovat investiční výdaje při čerpání = kapitálové dotace (15) / investiční cizích, nevratných prostředků. výdaje (18) * 100 [%] Míra krytí kapitálových výdajů z úvěrů Informuje do jaké míry je obec schopna a obligací13 financovat investiční výdaje při čerpání = příjmy z úvěrů a obligací (21) / cizích, vratných prostředků, tj. úvěrů. investiční výdaje (18) * 100 [%] Zdroj: Toth, 2005; tabulka vlastní.

21


Martin Horký

Mezi další ukazatele vztahující se k analýze krytí kapitálových výdajů mohou patřit také např. ukazatele míra krytí kapitálových výdajů z přebytku běžných příjmů, míra krytí kapitálových výdajů z prodeje majetku nebo míra krytí kapitálových výdajů z dotací a úvěrů.

Tabulka 7: Ukazatele vztahující se k majetku Ukazatel Význam ukazatele v analýze Ukazatel slouží k porovnávání úrovně Hodnota majetku na 1 obyvatele = hodnota majetku celkem (26) / počet vybavenosti obcí majetkem. Problémy obyvatel (27) vznikají v souvislosti s oceňováním majetku a s přechodem od direktivního na tržní hospodářství. Jedná se o ukazatel, který vyjadřující Běžně se opakující příjmy na 1 milion možnosti rozpočtu pečovat o majetek obce. majetku = běžně se opakující příjmy (11) / majetek Je nutno opět přihlédnout k problému celkem (26) oceňování majetku municipalit v pořizovacích cenách a k faktu, že majetek municipalit se neodepisuje. Při výpočtu ukazatele vycházíme z údajů o majetku z rozvahy, ale pro jeho přesnější vyjádření by mělo dojít k přepočtení hodnoty majetku na jeho reprodukční nebo tržní cenu. Zdroj: Toth, 2005; tabulka vlastní.

Tabulka 8: Ukazatele vztahující se k úvěrům Ukazatel Význam ukazatele v analýze Celkové roční splátky, tj. splátky úroků, Zadlužení obce = celkové roční splátky (22) / běžně se jistin, leasingů, emitovaných směnek atd. opakující příjmy (11) * 100 [%] z běžně se opakujících příjmů. Ukazatel vyjadřuje v procentech zatížení pravidelných příjmů obce splácením dluhu (v procentech). Může být také vypočítán jako podíl celkových ročních splátek na běžných výdajích. Dluhová služba jsou zaplacené úroky, Relativní dluhová služba = dluhová služba (22) / dluhová základna uhrazené splátky vydaných dluhopisů, (11) * 100 [%], upravený výpočet splátky jistin a splátky leasingu. Dluhová základna obsahuje daňové příjmy, nedaňové příjmy a dotace souhrnného finančního vztahu, tedy běžně se opakující příjmy. Ukazatel (relativní) dluhové služby je poměr dluhové služby k dluhové základně. Zdroj: Toth, 2005; tabulka vlastní.

22


Martin Horký

Výpočet relativní dluhové služby dle vyhlášky ministerstva financí Pro účely FAMA se vychází z definovaných základních finančních ukazatelů, nicméně pro hodnocení hospodaření obcí se běžně používá výpočet podle vyhlášky ministerstva financí14. Ukazatel dluhové služby přijala vláda ČR 14. dubna 2004 usnesením č. 346, o regulaci zadluženosti obcí. Uvedeným usnesením byl rovněž schválen postup Ministerstva financí České republiky při hodnocení zadluženosti obcí a krajů a definice a výpočet ukazatele dluhové služby. Tabulka 9: Výpočet dluhové služby Číslo Popis řádku

Výpočet

1

Daňové příjmy po konsolidaci

třída 1

2

Nedaňové příjmy (po konsolidaci)

třída 2

3

Přijaté dotace, finanční vztah

položky 4112+4212

4

Dluhová základna

ř. 1 + ř. 2 + ř. 3

5

Úroky

položka 5141

6

Splátky jistin a dluhopisů

položky 8xx2 a 8xx4

7

Splátky leasingu

položka 5178

8

Dluhová služba

ř. 5 + ř. 6 + ř. 7

9

Ukazatel dluhové služby

ř. 8 / ř. 4 * 100 [%] Zdroj: MF ČR, tabulka vlastní.

14

Výpočet ukazatele dluhové služby, MFČR http://www.mfcr.cz/cps/rde/xchg/mfcr/hs.xsl/ver_rozpocty_12197.html

23


Martin Horký

2.5 Zdroje dat Podklady pro finanční a majetkovou analýzu jsou získávána zejména dvěma způsoby:

terénní sběr

využití údajů z automatizovaného rozpočtového systému ARIS

Terénním sběrem jsou získávána data přímo od pracovníků obecních či městských úřadů. Místní samosprávy nicméně většinou poskytují data v nejrůznějších formátech (MS Word, Excel), v různém i neúplném členění a často s chybami, např. stejná rozpočtová položka je zařazena opakovaně k různým paragrafům odvětvového členění. Druhou možností, kde získat podklady pro základní ukazatele FAMA, je automatizovaný rozpočtový informační systém ARIS, který obsahuje kromě jiného data o hospodaření všech municipalit v České republice. Pro analýzu je klíčové zejména získání přesných a úplných základních finančních a majetkových ukazatelů za co možná nejdelší časové období. Vzhledem k tomu, že ARIS nabízí údaje od roku 2000 včetně, je někdy nezbytné pro získání delší časové řady tento zdroj kombinovat s údaji získanými z terénního sběru.

2.5.1 Automatizovaný rozpočtový informační systém ARIS Automatizovaný rozpočtový informační systém (ARIS) je databáze provozovaná ministerstvem financí, která zpracovává data v oblasti účetního a finančního výkaznictví všech rozpočtových a příspěvkových organizací, obcí, krajských úřadů a dobrovolných svazků obcí, státních finančních aktiv a státních fondů v ČR. Jeho cílem je poskytovat orgánům státní správy informace pro průběžné hodnocení plnění příjmů a výdajů veřejných rozpočtů a hospodaření rozpočtových a příspěvkových organizací, obcí a krajských úřadů. V rámci systému ARIS existuje také účelový registr organizací RARIS. Registr organizací vznikl pro potřebu řízení zpracování dat pro všechny organizace, které mají povinnost předávat data do systému ARIS. Tato povinnost se vztahuje také na municipality, které

24


Martin Horký

musí dodávat do systému data o svém hospodaření v požadované struktuře15. Díky tomu je možné podklady pro FAMA čerpat z tohoto informačního systému v jednotné podobě. Pro potřeby finanční a majetkové analýzy jsou z ARISu využívány tyto tři výkazy:

40–1 Výkaz o plnění příjmů a výdajů rozpočtových organizací – rozpočtové příjmy

40–2 Výkaz o plnění příjmů a výdajů rozpočtových organizací – rozpočtové výdaje

60 Rozvaha rozpočtových organizací

2.5.1.1 Webové rozhraní ARISu Data rozpočtového systému ARIS jsou volně dostupná i pro veřejnosti prostřednictvím webového rozhraní ARISWEB na adrese http://wwwinfo.mfcr.cz/aris/iarisorg/, a to komukoliv bez nutnosti autorizace či registrace (Obrázek 6).

Obrázek 6: Webové rozhraní pro přístup k údajům databáze ARIS (http://wwwinfo.mfcr.cz/aris/iarisorg/) Rozhraní ARISWEB spravuje ministerstvo financí. Obsahuje sadu webových formulářů, pomocí kterých lze definovat dotaz a získat požadovaná data. Výstupem jsou nejrůznější sestavy ve formátu HTML. Aplikace v současné době umožňuje výběry dat od roku 2000 včetně. Výkazy lze od roku 2005 získat rovněž ve formátu XML.

15

Tuto povinnost ukládá Vyhláška MF ČR č. 16/2001 Sb. ze dne 22. prosince 2000.

25


Martin Horký

×

ČSÚ

×

Základní finanční ukazatele Základní majetkové ukazatele Ostatní základní ukazatele – počet obyvatel Investiční a privatizační programy Další skutečnosti významné pro analýzu hospodaření (dluhová služba, programy rozvoje obcí)

Výkaz 60 rozvaha

Výkaz 40-2 rozpočtové výdaje

Údaje FAMA

Výkaz 40-1 rozpočtové příjmy

Tabulka 10: Přehled údajů FAMA a jejich zdroje Zdroje dat ARIS

Obecní nebo městský úřad dané municipality

2.5.2 Přehled údajů FAMA a jejich zdroje

×16 ×16

× ×

× × Zdroj: metoda FAMA, tabulka vlastní.

2.6 Analýza dat 2.6.1 Metody prognózování Jedním z hlavních cílů finanční a majetkové analýzy je prognóza předpokládaného vývoje základních ukazatelů do budoucna, tj. rozpočtových položek, majetku, privatizací (prodeje) a investic včetně úvěrové politiky. Existují dva možné způsoby, jak odhadovat podobu rozpočtu a ostatních sledovaných indikátorů hospodaření města:

na základě výsledků retrospektivní analýzy – využití znalosti vývoje rozpočtu v uplynulých letech

na základě současné znalosti předpokladů budoucího vývoje – například při odhadování výše neinvestiční dotace nelze vycházet čistě z výsledků retrospektivní

26


Martin Horký

analýzy, ale je třeba pracovat např. s materiály vlády, které schvalují výše těchto příjmů vzhledem k předpokládanému vývoji národního hospodářství.

2.6.1.1 Prognóza na základě výsledků retrospektivní analýzy Při sestavování prognózy budoucího vývoje se vychází ze znalosti retrospektivní analýzy. Pro potřeby finanční a majetkové analýzy byly předběžně zvoleny dva způsoby prognózování:

regresní analýza – výběr vhodného modelu trendu

růstové koeficienty – geometrický průměr

Regresní analýza Pro FAMA se používá metoda časových řad, která do určité míry vychází z teorie o regresním počtu. V této metodě vystupuje v roli vysvětlující (nezávislé) proměnné čas (v případě metody FAMA jednotlivé roky) a v roli závislé proměnné analyzovaná veličina (v našem případě základní ukazatele). S tímto přístupem popisu dynamiky souvisí i odhadování budoucího vývoje na základě určitých znalostí minulého chování. Při prognózování je nutné, aby byly jednotlivé sledované údaje skutečně srovnatelné, a to zejména ze těchto tří hledisek:

věcné hledisko – aktuální používaná rozpočtová skladba v jednotlivých letech a jejich vzájemná kompatibilita a správná konstrukce jednotlivých základních ukazatelů

prostorové hledisko – stejné geografické území, tj. rozdělení či sloučení municipality, výrazné změny katastru

hledisko časové srovnatelnosti – vychází se z délky rozpočtového období, čili z jednoho kalendářního roku

Nejužívanější metodou odhadu parametrů trendových funkcí je metoda nejmenších čtverců, která je použitelná v případě, že zvolená trendová funkce je lineární v parametrech, tj. jedná se např. o lineární regresní model. Předpovědi budoucích hodnot

16

V případě potřeby získat údaje za delší časové období, které již nejsou součástí databáze ARIS, je nutné požádat o spolupráci příslušný obecní či městský úřad.

27


Martin Horký

lineárního trendu se pak konstruují extrapolací trendové přímky, přičemž čím více dat do souboru vstupuje, tím přesněji lze odhadnout budoucí trend. Tabulka 11: Modely trendů regresivní analýzy (zdroj: DP Trunečková, 2006) Název modelu Obecná rovnice Lineární model

Y = a + b*X

Exponenciální model

Y = exp( a + b*X)

Logaritmický model

Y = a + b*Ln(X)

Zvolení nejvhodnějšího modelu trendu se provádí na základě několika sledovaných charakteristik a to p–value, F–test, korelační koeficient, index determinace a DurbinWatsonův test. Obecně se za nejvhodnější model trendu považuje model s nejvyšší hodnotou F–testu. (Trunečková, 2006).

Růstové koeficienty Druhým ze způsobů využití retrospektivní analýzy pro budoucí předpověď je stanovení konkrétního růstového koeficientu u jednotlivých základních ukazatelů. Násobením poslední

známé

skutečné

hodnoty

ukazatele

růstovým

koeficientem

získáme

předpokládanou hodnotu v roce následujícím. Opakovaným způsobem můžeme získat libovolně dlouhou časovou řadu. S počtem prognózovaných let však bude spolehlivost výsledků klesat. Při konstrukci růstových koeficientů budeme nadále vycházet z metody časových řad. Průměrné tempo růstu časové řady určuje geometrický průměr. Jinými slovy geometrický průměr je tempem růstu časové řady. Východiskem pro výpočet jsou koeficienty růstu, což je poměr hodnoty sledované veličiny v daném roce ku hodnotě předchozího roku. K = Vx / Vx–1 Pokud známe koeficienty růstu za jednotlivé sledované roky, vypočítáme jejich součin a odmocníme počtem sledovaných let. Výpočet geometrického průměru opakujeme s každým dalším přidaným rokem. (Trunečková, 2006).

28


Martin Horký

2.6.1.2 Prognóza na základě současné znalosti budoucího vývoje V případě určitých rozpočtových ukazatelů lze jako metodu určování budoucího vývoje zvolit metodu odhadu na základě současné znalosti budoucího vývoje. Jedná se především o předpovědi budoucího vývoje v oblasti daňové politiky a neinvestičních dotací. Lze také vycházet z vnitřních dokumentů obce, kdy si obec stanovuje vlastní investiční program na několik let dopředu či plánuje prodeje. V těchto případech je jistě nejpřesnější použít známé skutečnosti a zabudovat je do rozpočtu přímo bez zkoumání statistickými metodami. Ty se mohou značně odlišovat, neboť neberou v úvahu ostatní sledované skutečnosti a jevy související bezprostředně se sledovaným ukazatelem. (Trunečková, 2006).

2.6.2 Formy grafické prezentace V následujícím přehledu jsou vybrány nejpoužívanější typy grafické prezentace výsledků finanční a majetkové analýzy. Vedle výstupů ve formě tabulek se jedná zejména o zobrazení ve formě spojnicových grafů.

Obrázek 7: Struktura daňových příjmů města Berouna v roce 1999 (zdroj: DP Trunečková, 2006)

29


Martin Horký

Obrázek 8: Míra soběstačnosti města Beroun za roky 1999–2004 v porovnání s vybraným vzorkem měst (zdroj: DP Trunečková, 2006)

Obrázek 9: Příjmy a výdaje města Beroun v letech 1999–2005 (zdroj: DP Trunečková, 2006)

30


Martin Horký

Obrázek 10: Plánované prodeje majetku města Beroun v letech 2005–2007 (zdroj: Trunečková, 2006)

31


Martin Horký

Kapitola 3 Současná programová podpora FAMA 3.1 Úvodem Programovou podporu metody finanční a majetkové analýzy v současné době tvoří databázová aplikace „FAMA 2005“ a program Prognóza. Aplikace „FAMA 2005“ obsahuje podkladová data pro analýzy a její součástí jsou také nástroje pro sběr podkladů, jejich prohlížení a aktualizaci. Aplikace nabízí základní formy výstupu retrospektivní části analýzy. Program Prognóza slouží pro analýzy a prognózování budoucího vývoje, tedy pro realizaci prospektivní části metody. Program komunikuje s databází „FAMA 2005“, ze které získává potřebná data pro prognózování, a zpět do databáze ukládá namodelované varianty budoucího vývoje.

3.2 Databázová aplikace FAMA 2005 „FAMA 2005“ je databázová aplikace v prostředí Microsoft Access17, kterou tvoří databáze s údaji o obcích a sada formulářů, prostřednictvím kterých lze data prohlížet a aktualizovat. Aplikace vznikla v rámci grantu č. 402/03/1235 „Financování obcí“ v roce 2003, v současné době existuje její novější verze označovaná jako FAMA 200518.

17

Technologie MS Access pro realizaci programové podpory metody FAMA byla podle jejího autora diplomanta Karla Vostrého zvolena vzhledem k její dostupnosti. Prostředí MS Access je běžně dostupné každému uživateli v univerzitním prostředí. S databází zpravidla pracuje více lidí zároveň, je proto žádoucí, aby bylo možné aplikaci jednoduše replikovat a využívat na jakémkoliv počítači, což technologie MS Access nabízí. 18 Pokud nebude uvedeno jinak, bude se v dalším textu pod označením databáze „FAMA“ rozumět její nejnovější verze, tj. „FAMA 2005“.

32


Martin Horký

Obrázek 11: Hlavní nabídka aplikace FAMA 2005 Hlavní menu aplikace FAMA 2005 nabízí několik možností s následující funkčností (Obrázek 11):

Analýza obcí – prohlížení všech údajů dané obce (základní ukazatele, investiční a privatizační plány, úvěry apod.).

Zadávání dat – prohlížení a editace údajů o jednotlivých obcích včetně mazání, je zde také možné vložit novou obec.

Velikostní skupiny – možnost zobrazení obcí podle počtu obyvatel dle definovaných velikostních skupin

Obce v krajích – vyhledávaní obcí patřících do daného kraje

Ukazatele19 – detailnější statistické zpracování souboru obcí z pohledu různých ukazatelů

3.2.1 Datový model Datový model databáze FAMA (Obrázek 12) zachycuje stav k roku 2003, tedy první verzi databáze. Model komplexně pokrývá potřeby finanční majetkové analýzy, a to jak pro retrospekci, tak pro prognózování budoucích ukazatelů. Ve verzi z roku 2005 pouze přibyly do modelu dvě další tabulky: prognoza_poctu_obyvatel a plan_splatek.

19

Tato funkcionalita přibyla do aplikace v roce 2005. Ostatní možnosti vychází z první verze z roku 2003.

33


Martin Horký

Obrázek 12: ERD databáze FAMA 2005 (vlastní úprava modelu) V modelu databáze FAMA 2005 se pracuje se třemi číselníky:

velikostní skupiny obyvatel (tabulka velikostni_skupina) – kategorie jsou stanoveny v 10 intervalech (Obrázek 13).

Obrázek 13: Číselník velikostni_skupiny, databáze FAMA 2005

základní finanční ukazatele (tabulka typ_polozky_rozpoctu), odpovídá seznamu finančních ukazatelů metody FAMA včetně výpočtu (viz kapitola 2.4.1)

základní majetkové ukazatele (tabulka typ_majetku), odpovídá seznamu majetkových ukazatelů metody FAMA včetně výpočtu (viz kapitola 2.4.1)

Ve verzi aplikace z roku 2003 existoval také další číselník s definicemi pro propočtové a podílové ukazatele, které byly uloženy v tabulce ukazatel. Ve verzi z roku 2005 byla ale

34


Martin Horký

tabulka odstraněna a definice implementovány přímo do SQL dotazů jednotlivých výstupních sestav. V následujícím přehledu jsou všechny tabulky databáze (kromě výše uvedených číselníků) rozděleny podle jejich využití z hlediska retrospekce či prospekce na základě typů dat pro analýzy. U každé tabulky je v závorce uveden zdroj nebo způsob získávání dat20.

Tabulka 12: Přehled tabulek databáze FAMA 2005 Tabulky databáze FAMA 2005 Ukazatele (v závorce způsob jejich plnění) Retrospekce Prospekce rozpocet odhad_rozpoctu Rozpočet polozka_rozpoctu polozka_odhadu_rozpoctu (ArisDestiller) (Prognóza) majetek odhad_majetku Majetek (rozvaha aktiv a pasiv) (ArisDestiller) (Prognóza) investicni_program odhad_investice Investice investice_v_roce (terénní sběr) (nelze21) planovane_prodeje odhad_prodeje Privatizace prodeje_v_roce (nelze21) (terénní sběr) uver Dluhová služba a splatka splátkový kalendář – plan_splatek (nedokončená funkčnost) pocet_obyvatel prognoza_poctu_obyvatel Počet obyvatel (ČSÚ) (nedokončená funkčnost)

20

K aplikaci neexistuje žádná dokumentace ani definice entit a vztahů mezi nimi. Nelze tedy s jistotou říci, jaká je přesná role daných entit v rámci celého systému programové podpory finanční a majetkové analýzy. Uvedené závěry vycházejí z vlastních poznatků autora. 21 V programu Prognóza nelze tuto funkčnost využít. Aplikace totiž pracuje výhradně s datovými entitami, v rámci kterých se nerozlišuje mezi plánem dané municipality a prognózou analytika. Entity pro odhady v oblasti privatizací a investic se nikde pravděpodobně vůbec nepoužívají.

35


Martin Horký

3.3 Získávání dat Údaje pro databázi FAMA 2005 se v současné době získávají třemi způsoby:

terénním sběrem – pomocí formuláře MS Access „Sběr dat“ a aplikace „PretazeniDat“ pro zpracování a uložení dat v databázi FAMA

automatizovaně – ze zdrojů systému ARIS22 a s pomocí aplikace ArisDestiller pro výběr a uložení dat do databáze FAMA

editací přímo v aplikaci FAMA 2005 – data lze editovat pomocí předpřipravených formulářů a standardních nástrojů MS Access

Obrázek 14: Přehled zdrojů dat a jejich zpracování v programovém řešení FAMA (diagram vlastní)

3.3.1 Terénní sběr dat Základním způsobem získávání informací o obcích je sběr dat tzv. terénními průzkumy. Sběr dat se provádí pomocí formuláře MS Access „Sběr dat“ (Obrázek 15). Na formuláři se nachází osm záložek, kam se zadávají příslušné údaje: kontaktní osoba, počet obyvatel, rozpočet, investiční program, privatizační plán, majetek, úvěry, poznámky k vyplnění. Vývoje obecních rozpočtů a majetků se zadávají ve formě základních ukazatelů.

22

Automatizovaný rozpočtový informační systém ARIS, viz kapitola 2.5.1.

36


Martin Horký

Obrázek 15: Formulář MS Access pro terénní sběr dat Převod dat z formulářů z terénního sběru do databáze FAMA se provádí pomocí aplikace „PretazeniDat“23. Kontrola hodnot některých ukazatelů se provádí manuálně vůči databázi ARIS. Terénní způsob sběru dat byl v rámci řešení grantu č. 402/03/1235 „Financování obcí“ významným zdrojem informací. Byly osloveny obce a města z různých krajů České republiky a vyzvány, aby poskytly údaje o svém hospodaření. Dalším zdrojem informací o obcích byla a stále také jsou data zpracovaná studenty VŠE Praha v rámci svých seminárních prací. Údaje v databázi FAMA získaná tímto terénním způsobem jsou po převodu kontrolována. Chybějící data nebo opravy údajů jsou následně prováděny zejména na základě podkladů z rozpočtového informačních systému ARIS nebo webových stránek statistického úřadu24.

3.3.2 Automatizovaný sběr dat z ARISu Automatizovaný sběr dat pro databázi FAMA se provádí pomocí aplikace ArisDestiller25 ze zdrojů automatizovaného rozpočtového informační systému ARIS. ArisDestiller pracuje s výkazy územně samosprávných celků získaných z webových stránek ARISu ve formě HTML.

23

Autorem aplikace je Ing. Michal Wokoun. Program je napsán v jazyce C++. Na kontrole údajů v databázi se v rámci grantu podílelo několik řešitelů, zejména pak Ing. David Baštař, Ing. Petr Halouzka, Ing. Lucie Trunečkovou nebo Jakub Nevěřil. 25 Aplikaci ArisDestiller vyvinul Ing. Michal Wokoun v rámci grantového úkolu. 24

37


Martin Horký

Aplikace umí zpracovat tři výkazy:

Výkaz 40–1 o plnění příjmů a výdajů rozp. organizací – rozpočtové příjmy

Výkaz 40–2 o plnění příjmů a výdajů rozp. organizací – rozpočtové výdaje

Výkaz 60 – rozvaha rozpočtových organizací

Výkaz č. 40–3 financování aplikace ArisDestiller importovat neumí.

Obrázek 16: ArisDestiller 2005, dialogové okno pro import dat (zdroj: dokumentace k aplikaci) Výkazy je nutné ručně stáhnout z webu ARISu v podobě HTML souboru a uložit pod daným jménem do adresáře pro import výkazů. Aplikace provede parsování HTML dle definované struktury pomocí regulárních výrazů (viz Obrázek 17). Získaná data následně agreguje do podoby základních finančních a majetkových ukazatelů metody FAMA dle definice. Identifikace údajů probíhá na základě položek rozpočtové skladby, resp. řádků rozvahy aktiv a pasiv. Aplikace tedy provádí převod dat z velmi podrobného druhového členění na členění přehlednější, které uloží do databáze. Při změně definice výpočtu základních ukazatelů je nutné data importovat znovu.

Obrázek 17: ArisDestiller, editace regulárních výrazů pro parsování výkazů ve formátu HTML

38


Martin Horký

ArisDestiller nicméně neumí zpracovat výkaz 40–3 o plnění příjmů a výdajů rozpočtových organizací – financování, jehož data jsou nutná pro výpočet základních finančních ukazatelů příjmy z úvěrů a obligací a splátky úvěrů a obligací. Z tohoto důvodu je i upravena definice ukazatele „výsledek hospodaření“, která nezohledňuje financování, aby bylo možné provádět komparaci i mezi obcemi, která tato data obsahují z terénního sběru. Aplikace ArisDestiller komunikuje s databází Access pomocí ODBC datového zdroje. Kromě importu výkazů také umožňuje prohlížení již zadaných obcí v databázi FAMA. V roce 2005 bylo webové rozhraní systému ARIS rozšířeno o možnost získávání dat ve formě XML. ArisDestiller ani jiná aplikace programové podpory metody FAMA však takováto data neumí zpracovat.

3.4 Definice ukazatelů 3.4.1 Základní finanční a majetkové ukazatele Definice základních finančních ukazatelů je v databázi FAMA uložena v tabulce (číselníku)

typ_polozky_rozpoctu.

Definice

lze

změnit

prostřednictvím

aplikace

ArisDestiller (Obrázek 18).

Obrázek 18: ArisDistiller 2005, definic základních finančních ukazatelů FAMA

39


Martin Horký

Obsah tabulky typ_polozky_rozpoctu s definicemi všech základních finančních ukazatelů metody FAMA je vidět na následujícím obrázku (Obrázek 19).

Obrázek 19: Tabulka typ_polozky_rozpoctu s číselníkem a definicí výpočtu základních finančních ukazatelů FAMA. Podle této definice agreguje data i ArisDestiller. Podobným způsobem lze v aplikaci ArisDestiller editovat definice základních majetkových ukazatelů, které jsou v databázi uloženy v tabulce typ_majetku.

3.4.2 Propočtové a podílové ukazatele Definice propočtových a podílových ukazatelů byly ve staré verzi databázové aplikace FAMA 2003 uloženy v tabulce ukazatel ( Obrázek 20). Ve vzorcích pro výpočet jednotlivých ukazatelů jsou použity odkazy na základní ukazatele v daném roce. Význam jednotlivých zkratek byl následující:

m – majetek

po – počet obyvatel

pr – propočtový ukazatel, kde parametr funkce odpovídá řádku definice ukazatele z tabulky typ_polozky_rozpoctu (Obrázek 19)

cs – hodnota ukazatele splátky úvěrů a půjček

40


Martin Horký

Obrázek 20: Definice propočtových a podílových ukazatelů ve staré aplikaci FAMA 2003 (tabulka ukazatel). Ve verzi 2005 jsou definice implementovány přímo do SQL dotazů. Ve verzi aplikace FAMA z roku 2005 byla tabulka ukazatel odstraněna a definice implementovány přímo do SQL dotazů jednotlivých výstupních sestav. Oproti verzi 2003 se změnily některé chybné definice, např. míra závislosti na neopakovatelných příjmech, kdy nový výpočet je v původní notaci: (1 – (pr(11,0)/pr(16,0)) * 100, nebo investiční podíl, nově: pr(18,0)/pr(19,0). Vzhledem ke složitosti SQL dotazů je velmi obtížné ověřit, podle jakých vzorců výpočet probíhá.

3.5 Analytické nástroje Současnou programovou podporu pro analyzování hospodaření municipalit tvoří dva nástroje:

sestavy databázové aplikace FAMA 2005 – umožňují retrospektivní analýzu dané obce na základě ukazatelů

program Prognóza – nástroj pro prognózování budoucího vývoje hospodaření a majetku na základě hodnot retrospektivních ukazatelů a zvolené metody prognózování; data lze zpět zapsat do databáze.

41


Martin Horký

Obrázek 21: Vztahy mezi nástroji programové podpory analýz (diagram vlastní)

3.5.1 Analytické možnosti aplikace FAMA 2005 Aplikace FAMA 2005 umožňuje kromě prohlížení evidovaných údajů retrospektivně analyzovat obce z pohledu základních, propočtových a některých podílových ukazatelů (volba „Ukazatele“ v hlavní nabídce aplikace). Ukazatele pro danou municipalitu lze následně zobrazit v grafické podobě (Obrázek 22).

Obrázek 22: Ukázka grafického výstupu v aplikaci FAMA 2005 pro ukazatele města Uherské Hradiště (výstup aplikace FAMA 2005)

42


Martin Horký

Investiční a privatizační plány mohou být zobrazeny pomocí sloupcových grafů (Obrázek 23)26. V přehledu jsou uvedeny názvy jednotlivých akcí investičního, resp. privatizačního programu, včetně odpovídající finanční výše v daném roce.

Obrázek 23: Plánované investice v roce 2004 a 2005 města Moravská Třebová (výstup aplikace FAMA 2005) Kromě toho je možné zobrazit průměrné hodnoty některých ukazatelů hospodaření obcí podle dané velikostní skupiny (Obrázek 24). U těchto možností však nelze použít grafický výstup v podobě agregovaných ukazatelů pro danou skupinu obcí. Výstup je pouze tabulkový. Uživatel pak musí příslušná data přetáhnout do jiného programu např. MS Excel, kde z nich lze vygenerovat grafy.

26

Na obrázku jsou špatně zobrazeny jednotky finančních hodnot jednotlivých investic. Místo Kč patří tisíce Kč.

43


Martin Horký

Obrázek 24: Ukazatele podle velikostních skupin aplikace FAMA 2005 Jiné údaje o hospodaření municipalit je možné zobrazit pouze formou výpisu příslušných tabulek (úvěrování).

Stínové standardy hodnot ukazatelů Výběrem skupiny obcí podle dané velikostní kategorie lze získat průměrné hodnoty určitých ukazatelů, které lze poté použít pro komparaci. Těmto hodnotám reprezentujícím průměr v dané kategorii se říká stínové standardy a mohou být součástí analýzy retrospektivního vývoje dané municipality. Doposud totiž není možné při analýze vycházet z jakýchkoliv platných normativních standardů, ať už by se jednalo o schválení určitého pásma hodnocení nebo konkrétních norem. V případě hodnocení dluhové služby tyto normativní úrovně existují, v ostatních případech však nikoliv.

44


Martin Horký

Obrázek 25: Ukázka stínového standardu hodnota majetku na obyvatele (zdroj: Halouzka, Milerski, 2006)

3.5.2 Aplikace Prognóza Aplikace Prognóza27 poskytuje pokročilejší funkcionalitu pro modelování vývojových trendů v budoucnosti a přehlednější rozhraní pro práci s daty. Program komunikuje s databází FAMA 2005, ze které vybírá hodnoty základních, propočtových a podílových ukazatelů dané zkoumané obce. Uživatel na jejich základě může provádět prognózování volbou jedné z prognostických metod a namodelované trendy budoucího vývoje zpět zapsat do databáze. Data zobrazená v aplikaci lze exportovat do formátu MS Excel (xls).

27

Autorem aplikace je Ing. Michal Wokoun. Program je napsaný v jazyce C++.

45


Martin Horký

Obrázek 26: Ukázka aplikace Prognóza. Vlevo jsou retrospektivní data (1999–2003), vpravo prognóza. Sloupec „koeficient“ se používá pro odvození prospektivní analýzy na základě zvolené prognostické metody (aritmetický průměr, geometrický průměr, harmonický průměr, lineární regrese). Na základě zvolené metody se z retrospektivních dat vypočítá koeficient, ze kterého se odvodí hodnoty pro prospektivní roky. Na ukázce jsou hodnoty vyplněné za rok 1999–2003, hodnoty od roku 2004 jsou prognózovány na základě metody lineární regrese. Vypočítané hodnoty lze uložit zpět do databáze. Vývoj aplikace není zcela dokončen. Program neumí odhady prospekce metodou kvadratické regrese a nefungují odhady koeficientů u majetku. Exportovaná data ve formátu MS Excel jsou v textovém formátu, tzn. že pro další zpracování (výpočty, grafy) je hodnoty nutné převést do číselného formátu.

3.6 Nedostatky programové podpory Následující přehled obsahuje zjištění a nedostatky ve funkčnosti současné programové podpory metody finanční a majetkové analýzy.

46


Martin Horký

Nedostatky jsou rozděleny do dvou skupin. V první skupině nazvané „funkční nedostatky“ jsou uvedena zjištění, která jsou dána způsobem využít technologie a nástrojů MS Access, tedy vyplývají z datového modelu databáze nebo implementované funkčnosti jednotlivých aplikací. Druhá skupina obsahuje naopak nedostatky, které jsou dány samotnými technologickými omezeními zejména s ohledem na analytické možnosti. Do přehledu byly vybrány zejména klíčové oblasti z hlediska sběru a uchovávání dat a analýz hospodaření.

3.6.1 Funkční nedostatky 3.6.1.1 Fyzický datový model FAMA 2005 Nedostatky vyplývající z datového modelu databáze FAMA 2005:

Absence metadat – o údajích v databázi FAMA 2005 nejsou vedeny žádné informace, tj. metadata. Zejména se jedná o původ údajů (terénní sběr28 či ARIS – např. údaje o výnosech z privatizace mohou pocházet z rozpočtu nebo terénního sběru), datum jejich získání a jméno uživatele, který data zadal. Součástí metadat mohou být také podklady např. dokumenty získané z obecního či městského úřadu v elektronické verzi. Nejsou zaznamenány ani pozdější manipulace s daty, např. v rámci oprav dat z terénního sběru a tudíž nelze dohledat, kdo způsobil případnou chybu nebo zda nastala jiným způsobem.

Není možné předefinovat vzorce pro výpočet ukazatelů – základní ukazatele se v databázi uchovávají v agregované podobě dle daných definic. V případě, že dojde ke změně metodiky nebo se nalezne chyba v definici výpočtu ukazatelů či agregačních funkcích aplikace ArisDestiller, všechna data bude nutné znovu importovat v původní struktuře podrobného členění rozpočtové skladby. Ze stejného důvodu není rovněž možné přidat nový ukazatel.

28

V případě terénního sběru dat jsou informace o pracovníkovi, který sběr provedl, sice součástí formuláře „Sběr dat“, nicméně po nahrání dat do databáze FAMA nejsou tyto informace spojeny s každým jednotlivým údajem.

47


Martin Horký

Návrh řešení:

metadata – rozšířit datový model o další entity zachycující metadata (vazby na jednotlivé instance entit, jejichž metadata chceme sledovat) a rozšíření formulářových sestav pro import a editaci těchto dat

redefinice ukazatelů – rozšířit databázi o další fyzický datový model, který by implementoval

logickou

strukturu

zdrojových

dat

v původní

granularitě,

tj. v podobě získané z produkčních systémů (zejména výkazy ARIS). Tím by bylo zajištěno, že výchozí data pro výpočet agregovaných ukazatelů budou uložena v konzistentní podobě v jedné databázi a budou vždy k dispozici pro případný výpočet nových nebo redefinovaných ukazatelů.

3.6.1.2 Řízení přístupu k datům FAMA 2005

Není řízen přístup k datům – data v aplikaci FAMA 2005 lze libovolně měnit, je možné vkládat údaje o nové obci nebo měnit stávající data, aniž by se tyto operace řídily na základě uživatelských práv (např. právo plného přístupu, právo pouze pro čtení, právo pro editaci prognóz apod.).

Centralizace databáze – se soubory databáze FAMA může pracovat více lidí tím, že si jej každý nahraje do svého počítače. Může se tedy stát, že dva uživatelé nezávisle na sobě aktualizují data nebo jen provedou dílčí opravu údaje. V takovém případě není možné rozhodnout, která replikace databáze bude považována za poslední výchozí verzi.

Návrh řešení:

řízení přístupu – technologie Access umožňuje zamykání nebo ochranu tabulek/databáze. Přístup a manipulace s daty může být řízena na základě daných práv uživatele. Zdrojový kód jako makra, formulářové sestavy mohou být kompilovány do binární podoby (soubor MDE).

replikace databáze – prostředí Access nabízí nástroj pro replikaci databáze. Dvě a více instancí téže databáze lze synchronizovat, přičemž každá z nich obsahuje jiná data, resp. došlo ke změně obsahu databáze. Replikace se doporučují při maximálně

48


Martin Horký

5–10 uživatelích, záleží samozřejmě na rozsahu databáze a složitosti datového modelu29.

změna databázové platformy – prostředí MS Access může pracovat s daty různých databázových systémů samozřejmě včetně databáze MS SQL Serveru. Pokud by se datový model přesunul do databáze SQL Server (resp. jeho mini verze Microsoft SQL Server Desktop Engine) mohlo by se využít širších možností modelovacích nástrojů a nástrojů řízení přístupu. Na druhou stranu je nutné myslet na to, že změnou platformy se změní i způsob ukládání a tedy i práce s vloženými procedurami, triggery, dotazy i samotnými formuláři, které jsou v klasickém systému Access uloženy v databázi.

3.6.1.3 Ostatní nedostatky FAMA 2005

Výpočet hodnoty majetku před rokem 1990 – není jasné, zda vůbec a jakým způsobem se v aplikaci počítá reprodukční hodnota majetku, který byl pořízen před rokem 1990. Nicméně pro úplnost dodejme, že v databázi prozatím nejsou údaje o majetku před rokem 1990.

Uživatelská nepřívětivost – aplikace je v určitých ohledech neobratná a těžkopádná. Uživatel je vystaven dotazům systému (opakované zadávání kódů) nebo chybovým hláškám. Po otevření aplikace se zobrazí několik pracovních oknech. Ve výstupních sestavách nelze přehledně vidět, co všechno je zaznamenáno o dané obci (např. privatizační programy). U některých grafů chybějí popisky (pokud je jich více jako např. investiční akce), označení nebo měřítka a formát dat (jednotky vs. tisíce Kč).

3.6.1.4 Podpůrné aplikace ArisDestiller a Prognóza

ArisDestiller neumí import údajů z výkazu financování – aplikace ArisDestiller (nástroj pro import dat ze systému ARIS) neumí zpracovat výkaz č. 40–3 financování. Z tohoto důvodu je v aplikaci FAMA 2005 upravena definice ukazatele výsledek hospodaření takto: ř. 16 (příjmy celkem) – ř. 19 (výdaje

29

Jedno z mnoha řešení pro efektivní implementaci replikací od společnosti PCS, dostupné on-line na adrese: http://www.pcapps.com/Home/Services/MS_Access_Replication.aspx

49


Martin Horký

celkem). Definice by ale měla obsahovat také financování (ř. 20), tedy: ř. 16 – ř. 19 + ř. 20. Tato úprava je zřejmě z toho důvodu, aby bylo možné provádět komparaci mezi obcemi. Údaje o financování u jiných obcí pochází z terénního sběru dat.

V aplikaci Prognóza nelze vytvářet varianty investičních a privatizačních plánů – nelze vytvářet variantní řešení investičních a privatizačních programů, protože aplikace pracuje pouze s daty uloženými v tabulkách, které obsahují „skutečné“ částky (tabulky investice_v_roce, prodeje_v_roce) a nikoliv s daty variantních odhadů rozsahu investic a prodejů (tabulky odhad_investic a odhad_prodeje). Aplikace by tedy měla umožnit pouze číst z retrospektivních dat (skutečné hodnoty) nebo umožnit modelovat prospekci, ale tyto potom data ukládat do jiných tabulek s jinou sémantikou dat, jak by se očekávalo od aplikace Prognóza.

Návrh řešení:

dokumentace datového modelu – datový model by měl být doprovozen dokumentací, ve které by byly jednoznačně vymezeny významy jednotlivých modelovaných objektů reality implementovaných ve fyzickém datovém modelu (sémantika entit) a dále pak význam vztahů mezi těmito objekty. Nemohlo by se potom stát, že např. aplikace Prognóza umožňuje prognózovat budoucí vývoj prodejů a investic municipality, přičemž tato data ukládá do tabulek, které se tváří jako tabulky s retrospektivními údaji (skutečně investované částky či skutečně získané částky z prodejů).

3.6.2 Technologická omezení

MS Access není vhodný pro analýzy – MS Access obecně neposkytuje nástroje pro analýzu dat tohoto typu. Jednak z pohledu výkonu (zpracovávání velkého množství dat) a jednak z pohledu uživatelského. S výstupními sestavami lze jen manipulovat v rámci možností daných programátorem (výběr ukazatelů, období, sumáře). Nelze dynamicky měnit parametry pro výběr skupiny obcí, pro které se ukazatele agregují. Prostředí pro MS Access by mělo sloužit pouze pro operativní činnost nebo pro sběr dat, tedy jako nástroj pro zabezpečení transakčních dat.

50


Martin Horký

Kapitola 4 Business Intelligence 4.1 Business Intelligence Obecně se termínem Business Intelligence označuje proces transformace dat (údajů) na informace a převod těchto informací na znalosti sloužící k podpoře rozhodování. Tato přeměna se často ilustruje pomocí modelu datové pyramidy (Obrázek 27).

Obrázek 27: Transformace dat ve znalosti pomocí nástrojů Business Intelligence (zdroj: Pirkl, 2004) Základ transformace tvoří data, která představují jednoduchá fakta. Pokud k těmto datům přidáme nějakou souvislost, odkryjeme určité informace. Když k informacím přidáme také tvořivou inteligenci, získáme znalosti. Zobecněná znalost pak představuje moudrost.

Jednoduchými fakty mohou být např. záznamy o objemech jednotlivých nákupů zákazníků v obchodním domě. Přidanou souvislostí pak může být např. čas provedení nákupu, díky kterému zjistíme, že objem nákupů je vyšší v odpoledních hodinách, kdy se lidé vrací z práce. S použitím specializovaných nástrojů pro odhalování znalostí (Data Mining) pak můžeme z analyzovaných dat např. zjistit, že zákazník mužského pohlaví častěji kupuje pivo, pokud si zároveň zakoupí nějakou uzeninu.

51


Martin Horký

S ohledem na velké rozšíření těchto nástrojů v podnikové sféře je z mnoha definic výstižná také následující definice (Novotný, Pour, Slánský, 2005):

„Business Intelligence (BI) je sada postupů, procesů a technologií, jejímž cílem je účinně a účelně podporovat rozhodovací procesy ve firmě. Představuje komplex aplikací IS/ICT, které podporují analytické a plánovací činnosti podniků a organizací a jsou postaveny na specifických, tzv. OLAP (On-Line Analytical Processing) technologiích a jejich modifikacích.“

4.1.1 Transakční a analytické systémy Vývoj nástrojů Business Intelligence navazuje na rozvoj databázových technologií zejména v 70. a 80. letech, kdy vznikly databázové systémy sloužící pro zpracovávání a uchovávání operativních informací vznikajících v provozních systémech (skladové hospodářství, podnikové účetnictví, realizované zakázky, seznam dodavatelů apod.). Data jsou v těchto systémech ukládána do relačních databází, přičemž může i několikrát během dne docházet k jejich změnám. Protože se jedná o systémy pracující v reálném čase, jsou tyto označovány jako transakční – Online Transaction Processing (OLTP).

Transakční systémy však neobsahují dostatečné analytické nástroje pro práci s daty. Potřeba efektivních a flexibilních analýz a jejich oddělení od operačních systémů podnítilo v 90. letech rozvoj datových skladů (Data Warehouse), nástrojů Online Analytical Processing (OLAP) a data mining technik. Datový sklad představuje místo, kde jsou analyzovaná data uložena, koncept OLAP a technika data mining způsob práce s těmito daty. Tyto nástroje a techniky se souhrnně nazývají Business Intelligence.

S pomocí OLAP lze snadno získávat např. odpovědi na otázky typu „Jaký je procentuální podíl investičních výdajů k celkovým výdajům municipalit do 5000 obyvatel v Jihomoravském kraji?“. Využívají se přitom data z transakčních systémů, ze kterých se vytváří různé agregace a sumace, přičemž data lze analyzovat z různých pohledů,

52


Martin Horký

tzv. dimenzí. Samozřejmostí je dnes již možnost vizualizace výsledků jak v numerické, tak grafické podobě různých typů. Podle E. F. Codda, který v 80. letech přišel s koncepcí OLAP, je pro tuto metodu typické (Thomsen, 2002):

multidimenzionální koncept uložení i manipulace s daty

intuitivní manipulace s daty

práce s daty z heterogenních datových zdrojů – provádí se integrace dat

použití analytických metod – statistické přehledy, what-if analýzy

klient/server architektura

podpora multiuživatelského pohledu

ukládání výsledků OLAP mimo zdrojová data

dynamická manipulace s řídkými maticemi

zpracování chybějících hodnot

neomezený počet dimenzí a agregačních úrovní

Tabulka 13: Porovnání OLTP a OLAP OLTP

OLAP

Podpora transakcí – každodenní operace

Analýza i historických dat

Data uložena na úrovni transakcí

Integrace dat

Normalizovaný datový model

Denormalizovaný datový model Tabulka vlastní.

4.1.2 Význam datového skladu Paulraj Ponniah (Ponniah, 2001) definuje z funkčního hlediska datový sklad jako informační prostředí, které:

poskytuje integrovaný a úplný pohled na data,

usnadňuje využití údajů pro rozhodování (trendy, analýza časových řad),

podporuje proces rozhodování bez zatěžování transakčních systémů,

poskytuje konzistentní informace,

představuje flexibilní a interaktivní zdroj strategických informací (automatizace tvorby výkazů a zpráv).

53


Martin Horký

V typické architektuře datového skladu jsou údaje z primárních (transakčních) systémů nejprve transformovány do vhodné podoby pomocí specializovaných nástrojů (Extract, Transform and Load – ETL) a poté přeneseny do vlastního datového skladu.

4.2 Architektura BI Architekturu Business Intelligence tvoří tři základní oblasti (Obrázek 28). Výchozí částí pro komponenty BI jsou zdrojové systémy pracující v reálném čase, které uchovávají transakční data, jako např. Enterprise Resource Planning (ERP), Customer Relationship Management (CRM) nebo jakékoliv jiné zdrojové systémy (obchodní adresáře, katalogy apod.). Druhou oblastí jsou komponenty samotných nástrojů Business Intelligence, které zabezpečují zpracování dat, jejich uložení do datového skladu případně menšího datového tržiště a následně analýzu a zobrazení výsledků v prezentační vrstvě. Poslední třetí oblastí jsou pak koncoví uživatelé, kteří pracují s analytickými nástroji prezentační vrstvy; jsou to představitelné managementu nebo např. pracovníci analytického oddělení.

Obrázek 28: Obecná koncepce architektury BI (zdroj: Novotný, Pour, Slánský, 2005)

54


Martin Horký

Oblast nástrojů Business Intelligence, kterým se budeme věnovat v následujícím textu, tvoří čtyři základní vrstvy. Patří sem komponenty datové transformace, databázové komponenty, analytické komponenty a nástroje pro koncové uživatele. Nad architekturou komponent business intelligence stojí technologie pro zajištění kvality dat (řešení úplnosti, souladu, konsistence, přesnosti, unikátnosti a integrity dat) a nástroje pro správu metadat (správa informací o jednotlivých komponentách a datech).

4.2.1 Komponenty datové transformace Komponenty datové transformace zabezpečují kompletní proces načtení dat z transakčních systémů do datového skladu. Vrstva zahrnuje procesy Extraction, Transformation and Load (ETL) a Enterprise Application Integration (EAI), které zahrnují mnoho subprocesů, jako je výběr dat (extraction), transformace (ověření, čištění, integrace dat), načtení dat do datového skladu (loading), kontrola kvality, auditovaní, bezpečnost nebo zálohování a obnovu. Hlavním cílem komponent datové transformace je integrování dat z různých zdrojů do centrálního datového skladu. V prostředí MS SQL Serveru 2005 se komponenty datové transformace nazývají integrační služby. Celý proces integrace je velmi komplexní a časově nejnáročnější etapou implementace business intelligence.

4.2.2 Databázové komponenty Datový sklad (Data Warehouse, DW) – jedná se o základní komponentu databázové vrstvy. Datový sklad představuje centrální místo pro ukládání dat s možností jejich analytického zpracování pro podporu rozhodování. Datové sklady dnes představují jeden z nejvýznamnějších trendů v rozvoji podnikových informačních systémů.

Datové tržiště (Data Mart) – představuje podmnožinu datového skladu a je určeno pro potřeby omezeného okruhu uživatelů, např. oddělení, předmětných oblastí apod. Datová tržiště přispívají ke snížení doby počáteční implementace, nákladů a zlepšuje orientaci uživatelů v datech.

55


Martin Horký

Dočasné úložiště dat (Data Staging Area, DSA) – slouží k dočasnému uložení netransformovaných dat ze zdrojových systémů, aby nedocházelo k jejich velkému zatížení během transformace. V podstatě se jedná o jakýsi mezisklad. Data se ukládají ve stejné struktuře, jaká byla ve zdrojových systémech.

Operativní úložiště (Operational Data Store, ODS) – jedná se o aktualizované analytické databáze sloužící jako zdroj aktuálních dat z produkčních systémů dané vybrané oblasti. Výhodou je minimální doba odezvy pro zpracování. Na rozdíl od DSA jsou data však již konsolidovaná, konzistentní, subjektově orientovaná a někdy doplněná o agregace. Oproti datovému skladu obsahuje pouze aktuální data bez historie.

4.2.3 Analytické komponenty Nástroje této vrstvy umožňují zpřístupňovat a analyzovat údaje uložené v datovém skladu.

Reporting – je zaměřen na plánované nebo ad-hoc cílené dotazování komponent databázové vrstvy pomocí standardního rozhraní (např. SQL).

Systémy OLAP (Online Analytical Processing) – technologie, která umožňuje analytikům a managerům získat informace z dat rychle, konzistentně a interaktivně za pomoci široké škály pohledů. OLAP nabízí pokročilé a dynamické analytické úlohy. Jeho základem je pohled na analyzovaná data jako na vícerozměrnou matici, která se nazývá OLAP kostka (OLAP cube). Data v OLAP kostce jsou narozdíl od transakčních systémů předzpracována a agregována podle definovaných hierarchických struktur (dimenzí) a jejich kombinací. Jednotlivé prvky jsou přístupné vždy v průnicích jednotlivých dimenzí. Model reprezentující tento systém se nazývá multidimenzionální model.

Data mining (dolování dat) – nástroje data miningu umožňují odhalování předem neznámých skrytých vzorů a závislostí v existujících datových zdrojích na základě pokročilých matematických a statistických technik, strojového učení a umělé inteligence (neuronové sítě).

56


Martin Horký

Rozdíl mezi technologiemi OLAP a data miningu vysvětluje Petr Berka (Berka, 2003) na základě srovnání se statistikou: OLAP nástroje odpovídají konfirmační analýze, kdy se snažíme vyvrátit nebo potvrdit dříve formulovanou hypotézu. Naproti tomu data mining pomáhá odhalovat zajímavé souvislosti, podobně jako ve statistice explorační analýza. OLAP, shrnuje Petr Berka, „přináší odpovědi na konkrétní, přesně specifikované otázky, ale sám o sobě nic ,neobjevuje‘“.

4.2.4 Nástroje pro koncové uživatele Jedná se o reportingové a analytické nástroje, pomocí kterých uživatelé přistupují k údajům datového skladu. Často se jedná o nejrůznější portálové aplikace založené na webových technologiích, manažerské aplikace typu Executive Information Systems (EIS) a různé další aplikace, které lze ovládat bez znalosti struktury datového skladu nebo dotazovacího jazyka pro získávání dat. Kladen je důraz zejména na maximální přehlednost a jednoduchost ovládání. Z hlediska nabízených funkcí je možné tyto nástroje rozdělit do několika skupin:

Reportingové nástroje – umožňují tvorbu a distribuci statických reportů. Práce s reporty je omezenější než u analytických aplikací, ale nástroje umožňují vytvářet sestavy i nad složitými datovými strukturami.

Analytické nástroje – slouží pro analytickou práci s daty v datovém skladě, poskytují

možnost

úpravy

stávajících

výstupních

sestav

bez

znalosti

programovacích jazyků.

Manažerské přehledy (dashboards a scorecards) – jedná se o jednoduchou, přehlednou a na první pohled srozumitelnou vizuální reprezentaci analyzovaných sestav pro manažery v podobě různých semaforů, budíků a dalších grafických komponent. V rámci výstupů typu scorecards lze jednotlivým uživatelům nastavit zobrazování metrik, za které odpovídají, a umožnit tak průběžnou kontrolu.

57


Martin Horký

Executive Information Systems (EIS) – jedná se o softwarový nástroj pro manažery, pomocí nějž je možné klást na systém dotazy, aniž by uživatel musel znát programovací jazyk pro kladení dotazů nebo strukturu datového skladu. Základním požadavkem na tyto systémy je tedy snadné ovládání. Dotaz, který si uživatel vybere v EIS nástroji, je převeden do jazyka SQL a proveden standardním způsobem. Nevýhodou tohoto přístupu však je, že uživatel má k dispozici pouze určitý soubor připravených dotazů. Chce-li se zeptat na něco jiného, je nucen vyhledat pomoc programátora, který dotaz do systému doplní.

4.3 Datový sklad Datový sklad (Data Warehouse, DW) představuje technologické úložiště, kde jsou analyzovaná data uchovávána. Podle W. H. Inmona, který v 80. letech zformuloval koncept datového skladu, je datový sklad předmětně orientovaný, integrovaný, časově neměnný a stálý soubor dat, který poskytuje podklad pro rozhodování (Inmon, 2002).

Význam těchto čtyř základních charakteristik je následující:

předmětná orientace – v datovém skladu se zabýváme objekty jako zákazník, produkt nebo aktivita narozdíl od provozních systémů, které se zabývají operacemi a transakcemi, jako je např. faktura, vklad, objednávka apod. Údaje, které nejsou vhodné pro rozhodování, nejsou v datovém skladu obsaženy.

datová integrace – do datového skladu vstupují údaje z různých produkčních systémů (i externích), které je nutné nejprve integrovat. Např. datový sklad výrobního podniku bude vyžadovat integraci dat získaných z výrobních, obchodních a účetních systémů. Jedná se např. o sjednocení terminologie, granularity údajů, odstranění duplicit, kompletnost údajů, kódování apod.

časová neměnnost – údaje v datovém skladu odpovídají určitému časovému okamžiku, ve kterém byla data transformována z produkčního prostředí, a nadále se tato data nemění narozdíl od produkčního prostředí, kde dochází k přírůstkům, změnám, mazáním apod.

58


Martin Horký

stálost – dotazy, které na údaje v datovém skladu aplikují manažeři a analytici, nezpůsobí jejich změnu. Datový sklad je tedy v tomto směru stálý.

4.3.1 Požadavky na datový sklad R. Kimball shrnuje požadavky na datový sklad v několika bodech (Kimball, 2002):

dostupnost dat – datový sklad musí umožnit snadnější dostupnost firemních dat (jasné a intuitivní uspořádání, uživatelsky příjemná

analytická aplikace

s odpovídající dobou odezvy).

konzistence dat – datový sklad musí prezentovat data konzistentně (metadata, vysoká kvalita integrace).

bezpečnost dat – údaje v datovém skladu musí být zabezpečeny proti neoprávněnému nakládaní s daty.

zlepšování – nástroje datového skladu musí sloužit jako podklad pro zlepšování rozhodovacích procesů.

přijatelnost pro uživatele – datový sklad může být považován za úspěšný, pokud jej přijmou uživatelé a aktivně ho budou využívat.

připravenost na změny – datový sklad musí být přizpůsobivý a připravený na změny.

4.3.2 Multidimenzionální model Model datového skladu tvoří dva typy entit (tabulek): fakta a dimenze. Fakta představují údaje, která chceme analyzovat. Jsou často numerická nebo aditivní. V případě finanční a majetkové analýzy mohou být fakty např. jednotlivé finanční ukazatele či údaje o rozsahu infrastruktury, demografické veličiny atd. Dimenze zachycují úhel pohledu na sledované ukazatele. Pomocí nich omezujeme rozsah analyzovaných dat. Dimenzí může být např. čas (jednotlivé roky) nebo velikostní kategorie obcí. V řadě případů se jedná o statické číselníky.

59


Martin Horký

Základní multidimenzionální model má podobu n-rozměrné krychle. Pro jednoduchost si jej lze představit jako kostku, na kterou lze pohlížet z několika dimenzí (Obrázek 29). Prvky dimenzí jsou většinou uspořádány v hierarchické struktuře.

Obrázek 29: Příklad struktury 4rozměrné OLAP kostky pro analýzu prodejních dat (zdroj: www-306.ibm.com30)

Samotná práce s OLAP kostkou spočívá v jejím různém otáčení (pivot), provádění řezů (slice), výběru určitých částí (dice) a zobrazování různých agregovaných hodnot (ukazatelů). Velmi často lze hodnoty atributů sdružovat do hierarchií např. hierarchie státní správy a samosprávy (regiony soudružnosti → kraje → okresy/ORP → jednotlivé obce).

30

www-306.ibm.com/software/data/db2/db2olap/docs/v82docs/miner82/omugtfrm.htm?howitworks.htm

60


Martin Horký

Úrovní v hierarchii bývá obvykle více, zpravidla je v OLAP kostce obsažena časová dimenze kombinována s jinými dimenzemi ukazatelů. Hierarchie dimenzí se využívají při práci s OLAP kostkou při operacích roll-up (přecházení na hierarchicky vyšší, obecnější úroveň) a drill-down (přecházení na podrobnější pohled na údaje). Někdy se mluví o různých úrovních podrobnosti pohledu (granularity) na data. Operace drill-across provádí spojení dvou a více faktových tabulek se stejnou granularitou, drill-around provádí operaci podobnou jako drill-across, ale pro nelineární uspořádání. Na sledované ukazatele lze také aplikovat různé agregační funkce a podmínky, které rozšíří jejich vypovídací schopnost. Může se jednat například o sumace, podíly či minimální a maximální hodnoty.

4.3.2.1 Implementace Z hlediska fyzické implementace OLAP kostky existují v zásadě dva logické modely. Hlavní důvod pro různé implementace je především velká řídkost dat a jejich nestejnoměrné rozmístění (Berka, 2003):

hyperkrychle (hypercube) – jedná se o jednu velkou krychli obsahující nástroje pro práci s tzv. řídkými daty, výhodou je jednoduchá struktura a srozumitelnost pro uživatele

multikrychle (multicube) – jedná se o více navzájem propojených menších krychlí obsahujících jen několik dimenzí, výhodou je efektivní uložení dat.

Uložení údajů v OLAP kostce ale nepřináší jen výhody. Jak uvádí Petr Berka v publikaci Dobývání znalostí (Berka, 2003): „za rychlost přístupu k datům platíme zvýšenými nároky (a tedy i cenou) na datový server. Tyto nároky někdy vedou k tomu, že se místo ,čistého‘ řešení OLAP, založeného na datové krychli (někdy nazývaného MOLAP – multidimenzionální OLAP), použije tzv. ROLAP – relační OLAP založený na klasické relační databázi. V tomto druhém případě se dotazy OLAP převádí do klasických dotazů SQL.“.

61


Martin Horký

Obrázek 30: Porovnání přístupu MOLAP a ROLAP (zdroj: Berka, 2003, str. 38; diagram vlastní)

OLAP vícerozměrné matice mohou být implementovány pomocí nejrůznějších modelů. V zásadě existují dva přístupy MOLAP a ROLAP. Další jsou kombinací těchto dvou základních modelů:

Multi-dimensional OLAP (MOLAP) – hodí se pro aplikace, kde není vyžadována průběžná aktualizace analyzovaných dat. Výpočty souhrnů totiž vyžadují určitý čas a jejich generování vyžaduje zvýšené nároky na databázovou aplikaci. MOLAP je vhodný

pro

analýzu

historických

dat.

Data

i

agregace

jsou

uložena

v multidimenzionální databázi.

Relational OLAP (ROLAP) – přístup, který je vhodný pro zpracování velkého objemu transakčních dat. Základní rozdíl spočívá v tom, že model ROLAP nepředzpracovává data a neukládá je. Místo toho využívá funkcionality relačních databází a přímo vypočítává příslušné agregace pro danou dimenzi a zvolené atributy. Někdy jsou k těmto výpočtům vytvořeny další databázové tabulky (tabulky sumářů a agregací). Model ROLAP proto není vhodný pro analýzu rozsáhlých historických dat. Data a agregace jsou uložena v relační databázi.

62


Martin Horký

Hybrid OLAP (HOLAP) – jedná se kombinaci modelů MOLAP a ROLAP. Agregace jsou prováděna v multidimenzionální databázi zatímco s detailními daty se pracuje v relační databázi. HOLAP je rychlý pro získání sumárních informací, pomalejší pro detailní údaje.

Desktop OLAP (DOLAP) – jedná se o zjednodušenou verzi modelů MOLAP nebo ROLAP.

4.3.2.2 Uspořádání faktových a dimenzionálních tabulek Pro implementaci OLAP kostek se používají dva datové modely uspořádání faktových a dimenzionálních tabulek:

schéma hvězda (star schema) – preferované uspořádání, přehlednější pro uživatele, snadné definování hierarchií a rychlý přístup k datům

schéma sněhová vločka (snowflake schema) – méně přehledné, náročnější na údržbu, pracuje se s normalizovanými tabulkami dimenzí (referenční integrita), nutné pro napojitelnost dalších faktových tabulek (BUS architektura)

63


Martin Horký

Obrázek 31: Schéma hvězda – star schema (zdroj: Pirkl, 2004)

Obrázek 32: Schéma vločka – snowflake schema (zdroj: Pirkl, 2004)

64


Martin Horký

4.4 Datová integrace Cílem datové integrace je zabezpečení kompletního procesu transformace dat často z několika různých zdrojových provozních systémů do centrálního datového skladu (viz Obrázek 33). Hlavním nástrojem datové integrace jsou procesy Extraction, Transformation and Load (ETL), které kromě jiného zahrnují procesy výběru dat (extraction), transformace (ověření, čištění) a načtení dat do datového skladu (loading).

Obrázek 33: Datová integrace (zdroj: Pirkl, 2004)

4.4.1 ETL procesy Procesy ELT zahrnují mnoho subprocesů:

extrakce – výběr dat

transformace – ověření, čištění, integrace dat

loading – načtení dat do DW

kontrola kvality

auditovaní

bezpečnost

zálohování a obnova

Cílem procesů ETL je řešit problém kvality a čistoty dat (Data Quality and Cleaning). Jedná se zejména o sjednocení formátů dat (jednotky, granularita) a terminologie (stejné entity jsou v různých systémech nazývány jinak), doplnění nebo oprava chybějících nebo

65


Martin Horký

neúplných dat (NULL hodnoty), řešení duplicit, jednoznačnosti, tvorba číselníků (např. kategorie zboží) nebo kontrola referenční integrity ve zdrojových systémech. Součástí ETL procesů je také testování bezchybnosti jednotlivých transformačních úloh (připojení, přenos souborů, bezpečnost – práva), management umělých klíčů a další. Samostatnou problematikou je řešení problému měnících se dimenzí tzv. Slowly Changing Dimensions (SCD). Dimenze se totiž generují z údajů produkčního systému a mohou se časem měnit. Je proto nutné vyřešit způsob, jak bude zachována konzistence v datovém skladu.

Při vývoji ETL procedur je možné použít dva přístupy:

manuální vývoj – specifikace ETL procesů se realizuje přímo v některém z programovacích jazyků; výhodou je

zejména možnost neomezeně nakládat

s daty, nevýhodou vyšší náklady na vývoj a správu, nižší přehlednost a kvalita dokumentace.

použití specializovaných nástrojů – pro návrh datových toků se používají vizuální nástroje; výhodou tohoto přístupu je rychlost návrhu, přehlednost, možnost jednodušší správy a řada podpůrných funkcí (např. sdílení metadat, automatizované spouštění procedur, kontrola a řízení kvality dat a další).

4.4.2 Extrakce XML dokumentů V rámci datové integrace jsou zpracovávány údaje z různých produkčních systémů. Data jsou v databázích spravována pomocí různých technologií a v různých formátech. V rámci této diplomové práce budou analyzována data o hospodaření municipalit ve formátu XML. Integrační služby Microsoft SQL Serveru 2005 nabízejí pro etapu datové integrace řadu nástrojů, pomocí kterých lze s XML daty pracovat.

4.4.2.1 Komponenta XML Source XML data lze v prostředí integračních služeb zpřístupnit pomocí komponenty XML source (zdroj). Jsou k dispozici tři různé techniky přístupu k datům: umístění XML dokumentu v souboru na disku (flat file), XML dokument uložený v proměnné (např. výběr

66


Martin Horký

z databáze) nebo získání dat pomocí protokolu HTTP. SQL Server 2005 podporuje servisně orientovanou architekturu (Service Oriented Architecture, SOA), a tedy obsahuje nástroje pro interakci s webovými službami. Komponenta XML source zpracovává data do tabulkové výstupní struktury podobné jako je struktura získaná SQL dotazem z relační databáze. Pro každý XML element komponenta vygeneruje výstup, kde název sloupce odpovídá názvu elementu a hodnota pak údaji uzavřeném mezi počáteční a koncovou částí elementu. Na následujícím příkladu je ilustrován vztah mezi strukturou XML dokumentu a relační strukturou prezentovanou ve formě tabulky. <dokument> <prvniSloupec>Pondělí 15.50 <prvniSloupec>Středa 200

Tabulka 14: Obsah elementu radek prvniSloupec

druhySloupec

Pondělí

15.50

Středa

200

Jak je vidět z příkladu, kořenový element dokument se ve výstupní sestavě nijak neprojeví (slouží pouze jako hraniční element obsahu XML dokumentu) a element radek slouží jako logický kontejner jednotlivých hodnot, které sdružuje, podobě jako jednotlivé řádky v relační databázi sdružují hodnoty všech sloupců. Ekvivalentní zápis z hlediska interpretace pomocí komponenty XML source v integračních službách SQL Serveru 2005 je následující XML dokument: <dokument>

67


Martin Horký

V praxi se však setkáváme s XML dokumenty, jejichž struktura je daleko složitější. Uvažujme XML dokument, který reprezentuje katalog a jednotlivé položky v tomto katalogu: <polozka> Košile pánská 850 <delkaRukavu>dlouhé<delkaRukavu> 52 <polozka> Kalhoty pánské 1299 <delka>33 36

Komponenta XML source v takovémto případě vygeneruje výstupní „tabulku“ pro každý element XML dokumentu, který obsahuje další elementy.

Tabulka 15: Obsah elementu polozka. nazev

cena

polozka_id

Košile pánská

850

1

Kalhoty pánské

1299

2

Tabulka 16: Obsah elementu velikost. delkaRukavu

obvodKrku

dlouhé

52

delka

obvodPasu

polozka_id 1

33

36

2

Hierarchie XML dokumentu je zachycena pomocí nově vygenerovaných sloupců, které identifikují rodičovský element pro každého potomka, který není prázdným elementem. Pro další práci s daty v procesech ETL je nutné vytvořit jeden „tok dat“ reprezentovaný

68


Martin Horký

jednou „tabulkou“, proto je nutné obě sestavy sjednotit na základě vygenerovaných identifikačních čísel pomocí komponenty merge join. Konzistence Zpracování XML dokumentu komponentou XML source probíhá podle definice – XML schématu. Pokud dokument obsahuje strukturu, element nebo atribut, který není nebo neodpovídá schématu, není tato hodnota zpracována a zobrazena na výstupu. Na druhou stranu pokud XML dokument neobsahuje element, který je ve schématu definován, výstup bude tento sloupec obsahovat ale s nulovou hodnotou (NULL).

4.4.2.2 Komponenta XML Task Pro další práci s XML daty je v integračních službách SQL Serveru 2005 k dispozici tzv. XML task (úkol), který nabízí všechny základní operace s XML daty. S jeho pomocí lze provádět validaci XML vůči schématu (DTD není podporován), transformace (diff, merge a patch), aplikace šablon XSLT, navzájem porovnávat dva dokumenty a provádět dotazy technologií XPath. V případě, že pracujeme se složitými XML dokumenty, je pro další práci s daty v integračních službách vhodné tuto strukturu zjednodušit právě pomocí komponenty XML Task s použitím XSL šablon a XPath. Pomocí těchto technik vybereme z dokumentu pouze ty údaje, které nás zajímají, případně můžeme s těmito daty provést matematické, řetězcové nebo logické operace, čímž si v dalších krocích datové integrace výrazně usnadníme práci. Dále se data mohou zpracovávat pomocí ostatních komponent, transformovat jejich datové typy, provádět výpočty a výsledky ukládat do multidimenzionálního modelu datového skladu standardními způsoby jako v případě získání dat z jiných produkčních systémů, jako např. relačních databází nebo souborů MS Office Excel.

4.5 Dodavatelé nástrojů BI Trh s nástroji Business Intelligence za posledních 15 let stále roste a zdá se, že tento trend bude pokračovat i nadále. V návaznosti na růst trhu jako celku se rozšiřují i oblasti nasazení. Vedle těch doposud nejtypičtějších, jako jsou bankovní domy či obchodní řetězce, se oblast BI zaměřuje i na další firmy (střední a menší) a také na instituce. Výrazně

69


Martin Horký

se zvyšuje počet uživatelů, kteří požadují nadstavbové BI, MIS či EIS systémy (DP Dürfl, 2006). Mezi největší dodavatele OLAP aplikací patří v současné době společnost Microsoft s 31,6% podílem na trhu v roce 2006, dále Hyperios Solutions s 18,9 %, Cognos s 12,9 %, Business Object 7,3 %, MicroStrategy s 7,3 % a SAP s 5,8 %.

Obrázek 34: Podíl na trhu Business Intelligence v roce 2006 (zdroj: OLAP report31)

Tabulka 17: Přehled produktů velkých dodavatelů Produkt Dodavatel

Odkaz

SQL Server 2005

Microsoft

www.microsoft.com/sql

Oracle 10g

Oracle

www.oracle.com/database

DB2 Data Warehouse

IBM

www-306.ibm.com

Teradata

NCR

www.teradata.com

Adaptive Server IQ

Sybase

www.sybase.cz Zdroj: DP Hájek 2006.

31

http://www.olapreport.com/market.htm (citováno 3.5.2008).

70


Martin Horký

4.5.1 Microsoft Analysis Services Microsoft Analysis Services představuje skupinu služeb a nástrojů, které jsou integrovány v prostředí Microsoft SQL Serveru. První produkt pro analytické služby představil Microsoft v roce 1998 v SQL Serveru 7 pod názvem OLAP Services. Nástroje podporovaly MOLAP, ROLAP a HOLAP architektury, s daty se pracovalo pomocí dotazovacího jazyka Multidimensional Expressions (MDX) a s OLAP kostkami bylo možné pracovat i offline. Ve verzi SQL Serveru 2000 přibyla možnost analyzovat data pomocí technik data miningu, proto se změnil i název nástroje na Analysis Services 2000. Mezi největší změny patřila podpora parent-child dimenzí, technik Slowly Changing Dimensions (SCD) nebo virtuálních dimenzí. Dalšími změnami byla podpora bezpečnosti dimenzí, komunikace pomocí protokolu HTTP a další. V roce 2005 vydal Microsoft další verzi analytických služeb, jejichž architektura se velmi změnila zejména implementací modelovacího nástroje Unified Dimensional Model (UDM). Analytické služby SQL Serveru 2005 podporují databázové standardy v rámci Data Definition Language (DDL) a Data Manipulation Language (DML). Jedná se zejména o standard SQL, dotazovací jazyk pro OLAP kostky MDX a jazyk Data Mining Extensions (DMX) pro tzv. „dolování“ skrytých souvislostí v datech.

4.5.2 ProClarity ProClarity je název americké společnosti, která se se stejnojmenným produktem specializuje na oblast Business Intelligence. ProClarity je podle průzkumů jedním z nejpoužívanějším front-endů třetí strany pro analytické služby. Nástroje ProClarity jsou úzce propojeny se službami Microsoft Analysis Servisis. ProClarity je analytický nástroj, který nabízí jednoduché, výkonné a škálovatelné rozhraní pro plné využití služeb BI platformy Microsoft. Součástí ProClarity je analytický server ProClarity Analytics Server (PAS), který pracuje také s nástroji Microsoft Office Business Scorecard Manager, Microsoft Office Excel nebo Microsoft SharePoint Portal Server. ProClarity důsledně odstiňuje uživatele od technických aspektů jako je struktura datového skladu a poskytuje mu informace ve formě přehledných a srozumitelných pojmů, jako je

71


Martin Horký

ukazatel, dimenze, množina, filtr atd. Aplikace se ovládá prostřednictvím několika komponent: provádění analýz, zobrazování výsledků, uložení a sdílení pohledů. Specifikace dotazů na datový sklad a výstupní analýzy jsou intuitivní, vizualizované pomocí tabulkových schémat, různých typů grafů, rozpadového stromu a rozptylového grafu. V roce 2006 oznámil Microsoft akvizici ProClarity32. Microsoft chce být vnímán jako plnohodnotný dodavatel řešení Business Intelligence, ne pouze jako velká softwarová společnost, která dodává pouze některé BI moduly. Posledním produktem vydaným po akvizici je řešení PerformancePoint Server 2007, které v sobě kombinuje funkčnost aplikace ProClarity a Business Scorecard Manager od společnosti Microsoft.

4.5.3 Přehled analytických produktů V následující tabulce, která vychází z podkladů diplomové práce Vojtěcha Hájka33, jsou uvedeny další produkty pro OLAP a data mining významných společností. Přehled je seřazen podle podílu na trhu BI v roce 2006 (viz kapitola 4.5). Produkt

Dodavatel

Odkaz

Analysis Services, Excel, Office Web Components, Data Analyzer, SharePoint Portal Server,

Microsoft

www.microsoft.com

ProClarity Hyperion System 9 BI+ (Interactive Reporting, Financial Reporting, Enterprise Analytics, Essbase Analytics, Web Analysis,

Hyperion Solutions

www.hyperion.com

Essbase Spreadsheet Services Business Objects XI, Crystal

Business Objects

www.businessobjects.com

Reports, Crystal Enterprise Live

32

http://www.microsoft.com/presspass/press/2006/apr06/04-03ProClarityPR.mspx (citováno 31.4.2008). Hájek, Vojtěch: Implementace Business Intelligence v malé a střední firmě – JOLLY Trading s.r.o., diplomová práce, VŠE Praha, 2006.

33

72


Martin Horký

Office MicroStrategy OLAP Services, Report Services, Data Mining Services, SAP Services, Desktop,

MicroStrategy

www.microstrategy.com

SAS

www.sas.com

Cognos

www.cognos.com

Actuate

www.actuate.com

Office... SAS Web Report Studio, SAS Add-In for Microsoft Office, SAS Information Delivery Portal, SAS Information Map Studio and SAS Integration Technologies Cognos 8 BI Reporting, analysis, query, dashboards, scorecards, business event management, and data integration. iServer, e.Spreadsheet, e.Report Enterprise Business Intelligence (WebFOCUS) – Active Reports, Managed Reporting, Dashboard MIS DecisionWare

Information Builders MIS AG

www.informationbuilders.com

www.misag.com Zdroj: DP Hájek 2006, výběr upraven.

73


Martin Horký

Kapitola 5 Řešení FAMA pomocí nástrojů BI Cílem této práce je realizovat alternativní řešení pro podporu finanční a majetkové analýzy municipalit pomocí nástrojů Business Intelligence. Na základě analýzy zdrojových dat a uživatelských požadavků bude na vybrané části metody FAMA realizován pilotní projekt v prostředí Microsoft SQL Serveru 2005. Záměrem tohoto projektu je ukázat jiný způsob zpracování podkladů pro FAMA a demonstrovat analytické možnosti aplikací typu MS Office Excel a ProClarity tak, aby uživatelé současné programové podpory metody FAMA získali lepší představu o této technologii a mohli oba systémy porovnat.

5.1 Úvodní studie Obsah a struktura úvodní studie vychází z metodiky publikované v knize Business Intelligence: Jak využít znalostí ve vašich datech (Novotný, Pour, Slánský, 2004). Vzhledem k charakteru pilotního projektu jsou některé části úvodní studie doporučované v knize vynechány (harmonogram projektu, organizační struktura projektového týmu) nebo jejich obsah zestručněn. Obsah úvodní studie:

Definování cílů a efektů BI

Uživatelé BI

Vymezení rozsahu řešení

Analýza uživatelských požadavků

Návrh BI architektury (aplikační infrastruktura)

74


Martin Horký

5.1.1 Definování cílů a efektů BI Z hlediska dalšího možného využití nástrojů BI pro podporu finančních a majetkové analýzy je důležité prezentovat spíše možnosti této technologie než implementovat veškerou funkcionalitu, která je nutná pro plnohodnotnou analýzu. Specifikum tohoto pilotního projektu spočívá také v tom, že se realizací BI nesledují primárně ekonomické efekty, ale efekty vyplývající z technologie.

Cíle pilotního projektu

demonstrovat možnosti technologie BI

porovnat prezentační možnosti BI a současné programové podpory

umožnit práci s ukazateli FAMA pomocí aplikace MS Office Excel

připravit datový sklad tak, aby bylo možné další rozšiřování jeho funkcionality

Efekty pilotního projektu

integrační efekty – datová integrace heterogenních zdrojových systémů

řízení kvality dat

automatizace transformačních procesů

možnost aplikovat náročnější analýzy – odhalit hlubší a složitější vztahy v datech

rychlejší tvorba sestav a reportů

sledování vývojových trendů v čase z různých pohledů

5.1.2 Uživatelé BI Se systémem budou pracovat především pracovníci, kteří dnes používají současnou programovou podporu metody FAMA. Jedná se zejména o zaměstnance a studenty Katedry veřejné správy a regionálního rozvoje VŠE v Praze, nicméně mohou být mezi nimi také pracovníci veřejné správy, kteří metodu FAMA mohou použít pro přípravu strategických plánů. Z hlediska výběru produktů pro analytickou práci a reportování je nutné brát zřetel zejména na fakt, že uživatelé zpravidla nejsou zdatní v používání informačních technologií.

75


Martin Horký

5.1.3 Vymezení rozsahu řešení V rámci pilotního projektu budou zpracována data pouze v rozsahu vybraných základních finančních a podílových ukazatelů metody FAMA. Tyto ukazatele budou analyzovány pro 472 obcí, které byly vybrány jako reprezentativní vzorek municipalit prof. Reném Wokounem z Katedry regionálních studií VŠE v Praze (viz příloha A). Jedná se o výběr územních samosprávných celků, jejichž údaje o hospodaření poskytnou dostatečné množství dat pro přípravu nového zákona o rozpočtovém určení daní, na jehož přípravě se katedra podílí.

5.1.3.1 Vybrané ukazatele V rámci pilotního projektu byla vybrána taková množina základních finančních ukazatelů metody FAMA, na jejímž základě je možné demonstrovat různé techniky práce s daty. Jedná se o následujících 15 ukazatelů metody FAMA, které byly dále rozšířeny o dva demografické ukazatele: počet obyvatel muži a počet obyvatel ženy.

Tabulka 18: Vybrané základní finanční ukazatele Číslo řádku

Název ukazatele

1 2 3 7 8

DPFO ze závislé činnosti DPFO z vlastní činnosti DPPO bez obcí Místní poplatky Daňové příjmy celkem

9 10 11 12 13 15

Příjmy z vlastní činnosti Neinvestiční dotace (bez převodu z vlastních fondů) Běžné (opakující se) příjmy – mezisoučet Ostatní příjmy Kapitálové příjmy Kapitálové dotace

16

Příjmy celkem (po konsolidaci)

76


Martin Horký

Tabulka 19: Vybrané ostatní základní ukazatele Číslo řádku Ukazatel 27 Počet obyvatel obce – Počet obyvatel muži Počet obyvatel ženy – Tabulka 20: Vybrané podílové ukazatele Ukazatel Celkové příjmy na 1 obyvatele Daňové příjmy na 1 obyvatele

Vhledem k dostupnosti podkladů pro výpočet uvedených ukazatelů v systému ARIS budou zpracovány ukazatele za roky 2001 až 2007 včetně. Ostatní ukazatele finanční a majetkové analýzy, které nebyly zařazeny do pilotního projektu, by byly vyhodnocovány velmi podobnými technikami (extrakce z XML zdrojů, import do datového skladu, agregace v analytických službách MS SQL Serveru 2005). Jejich případné doplnění do datového skladu není náročné.

5.1.3.2 Co není součástí projektu V rámci metody FAMA se v analýze zpracovávají také další informace, které dokreslují kvalitu hospodaření municipalit. Jedná se o následující údaje: dluhová služba a splátkový kalendář a uskutečněné investice a prodeje majetku. Struktura datového modelu databáze FAMA 2005 však neumožňuje jednoznačně interpretovat jejich sémantiku (plánované plnění vs. uskutečněné plány, resp. plány do budoucna). Vzhledem k tomu, že tato data jsou zpracována pouze u zlomku obcí v rámci terénního sběru dat (viz kapitola 3.3.1), tj. asi u 30 municipalit, nebudou tyto údaje zahrnuty do řešení pilotního projektu. Pro úplnost také uveďme, že v rámci pilotního projektu nebudou implementovány techniky data miningu. Ty budou v závěru práce pouze zmíněny zejména z hlediska dalšího možného vývoje či rozšíření modelu datového skladu.

77


Martin Horký

5.1.4 Analýza uživatelských požadavků Definice uživatelských požadavků vycházejí především ze současné podoby programové podpory metody finanční a majetkové analýzy: databázové aplikace FAMA 2005 a analytického nástroje Prognóza.

Funkční požadavky

Uživatel bude mít možnost analyzovat hospodaření na základě vybraných ukazatelů metody FAMA.

Hospodaření bude možné analyzovat podle administrativního členění a podle velikostní skupiny dle počtu obyvatel municipality, přičemž data budou vztažena k stavu v roce 2007 v případě počtu obyvatel, resp. k stavu k 1.1.2008 v případě administrativního členění.

V případě, že nebude hodnota daného ukazatele známa nebo nebude ji možné vypočítat, v datovém skladu se budou tyto položky zobrazovat jako nevyplněné, nikoliv „0“ (záznam dané obce musí být v přehledu uveden).

Výstupní sestavy

Všechny dané ukazatele bude možné analyzovat pomocí daných kategorií dimenzí

Sestavy budou realizovány formou kontingenčních tabulek a grafů, které si bude moci každý uživatel modifikovat podle svých potřeb.

V aplikacích MS Office Excel a ProClarity budou vytvořeny ukázkové sestavy demonstrující nějaký analytický pohled na vývoj hospodaření municipalit.

5.1.5 Návrh BI architektury Předmětem návrhu BI architektury je zejména definice aplikační a technologické infrastruktury – specifikace BI aplikací, datového skladu (resp. tržišť) a dalších komponent. Podpora finanční a majetkové analýzy nástroji BI má svá specifika, která mají vliv na architekturu řešení:

Roční aktualizace – údaje jsou sledované v roční periodicitě v granularitě jednotlivých obcí. Podklady pro datový sklad se mění pouze jednou za rok (rozpočty municipalit, rozvahy aktiv a pasiv, statistiky ČSÚ apod.). Není tedy nutné

78


Martin Horký

opakovaně provádět integrační úlohy jak je tomu u dat z transakčních systémů v podnicích.

Dostupnost analýz – není nutné zajišťovat nepřetržitou dostupnost analýz např. formou webových technologií. Pro potřeby analytiků je postačující práce s offline OLAP kostkami v prostředí MS Office Excel.

Architektura zvoleného řešení včetně softwarových komponent je znázorněna na následujícím obrázku.

Obrázek 35: Architektura a vybrané produkty BI řešení (diagram vlastní)

Pro řešení pilotního projektu byla zvolena technika jednorázového vybudování datového skladu, a to v celém rozsahu specifikace bez realizace datových tržišť. Řešení aplikační infrastruktury je realizováno pomocí produktů Microsoft. Datový sklad bude realizován v prostředí MS SQL Serveru 2005, resp. pomocí jeho integračních a analytických služeb. Jako reportovací a analytický nástroj bude použit MS Office Excel a aplikace ProClarity.

79


Martin Horký

Tabulka 21: Přehled vybraných produktů řešení BI Komponenta BI

Produkt

Datová transformace (ETL procesy)

Integration Services SQL Server 2005

Datový sklad

SQL Server 2005

OLAP analytické komponenty

Analysis Services SQL Server 2005

Nástroje pro koncové uživatele

MS Office Excel 2007, ProClarity 6.3

Produkty společnosti Microsoft pro koncové uživatele byly vybrány zejména vzhledem k jejich velké rozšířenosti na akademické půdě VŠE – uživatelé s těmito produkty běžně pracují, příznivé jsou rovněž i licenční podmínky. Prostředí pro datovou integraci a samotný datový sklad (MS SQL Server) vychází ze zadání této diplomové práce.

5.2 Analýza datového skladu a řešení BI Předmětem analýzy datového skladu a řešení pilotního projektu jsou tyto části: 1. Dimenzionální analýza – vymezení analytických ukazatelů, dimenzí a jejich vzájemných vazeb 2. Analýza datových zdrojů – analýza produkčních a případných externích datových zdrojů (veřejných databází, příp. jejich technické zajištění) 3. Návrh datového skladu – návrh datového modelu 4. Datová transformace – návrh jednotlivých datových integračních úloh a pravidel transformace dat z produkčních databází do roviny BI řešení 5. Návrh OLAP kostky – návrh modelu OLAP kostky, implementace vybraných ukazatelů a dimenzí 6. Prezentační vrstva – návrh tabulkových a grafických výstupních sestav.

5.2.1 Dimenzionální analýza Cílem dimenzionální analýzy je navrhnout strukturu datového skladu tak, aby bylo možné údaje v něm uložené analyzovat v různém rozsahu z různých hledisek.

80


Martin Horký

V rámci modelování jsou rozlišovány dva typy entit: tabulka faktů a tabulka dimenzí. Tabulka faktů je primární entitou datového skladu a obsahuje údaje, které chceme sledovat, tj. například jednotlivé finanční ukazatele metody FAMA. Tyto údaje lze v různé míře agregovat a vybírat pomocí číselníků uložených v tabulkách dimenzí na základě vztahů mezi tabulkami (relační integrita). Tabulka dimenzí tedy určuje, z jakých hledisek lze data analyzovat.

5.2.1.1 Sledované ukazatele Návrh sledovaných ukazatelů je odvozen z rozsahu pilotního projektu a uživatelských požadavků. U specifikace každého ukazatele je uveden zdroj dat, způsob výpočtu a obor hodnot ukazatele. Navržené finanční ukazatele obsahují v názvu také odkaz na číslo příslušného řádku definice dle metody FAMA (viz kapitola 2.4.1). Například ukazatel „R01 DPFO ze závislé činnosti“ odpovídá řádku 1 definice. Sledované ukazatele:

DPFO ze závislé činnosti (finančně)

DPFO z vlastní činnosti (finančně)

DPPO bez obcí (finančně)

Místní poplatky (finančně)

Daňové příjmy celkem (finančně)

Příjmy z vlastní činnosti (finančně)

Neinvestiční dotace, bez převodu z vlastních fondů (finančně)

Běžné opakující se příjmy (finančně)

Ostatní příjmy (finančně)

Kapitálové příjmy (finančně)

Kapitálové dotace (finančně)

Příjmy celkem po konsolidaci (finančně)

Počet obyvatel (počet)

Počet obyvatel muži (počet)

Počet obyvatel ženy (počet)

Celkové příjmy na 1 obyvatele (finančně)

Daňové příjmy na 1 obyvatele (finančně)

81


Martin Horký

Název Identifikace Zdroj dat Výpočty Rozsah hodnot

DPFO ze závislé činnosti (finančně) R01 DPFO ze závislé činnosti Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 1111 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč

Název Identifikace Zdroj dat Výpočty

DPFO z vlastní činnosti (finančně) R02 DPFO z vlastní činnosti Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 1112–1119 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč

Rozsah hodnot


DPPO bez obcí (finančně) R03 DPPO bez obcí Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 1121 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč


Místní poplatky (finančně) R07 Místní poplatky Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 1341–1349 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč

Rozsah hodnot


Daňové příjmy celkem (finančně) R08 Daňové příjmy celkem Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 1111–1999 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč

82





Rozsah hodnot



Martin Horký

Příjmy z vlastní činnosti (finančně) R09 Příjmy z vlastní činnosti Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 2111–2199 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč Neinvestiční dotace bez převodu z vlastních fondů (finančně) R10 Neinvestiční dotace Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 4111–4199 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč Běžné se opakující příjmy (finančně) R11 Běžně se opakující příjmy Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ R8 Daňové příjmy celkem + ∑ R9 Příjmy z vlastní činnosti + ∑ R10 Neinvestiční dotace > 0 nebo NULL (hodnota není uvedena) V tisících Kč Ostatní příjmy (finančně) R12 Ostatní příjmy Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 2210–2999 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč Kapitálové příjmy (finančně) R13 Kapitálové příjmy Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 3111–3999 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč

83




Rozsah hodnot

Název Identifikace Zdroj dat

Výpočty Rozsah hodnot





Martin Horký

Kapitálové dotace (finančně) R15 Kapitálové dotace Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ položka rozpočtové skladby číslo 4211–4299 ze závěrečného účtu > 0 nebo NULL (hodnota není uvedena) V tisících Kč Příjmy celkem po konsolidaci (finančně) R16 Příjmy celkem po konsolidaci Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ∑ R11 Běžně se opakující příjmy + ∑ R12 Ostatní příjmy + ∑ R13 Kapitálové příjmy + ∑ R15 Kapitálové dotace > 0 nebo NULL (hodnota není uvedena) V tisících Kč Počet obyvatel (počet) Počet obyvatel ČSÚ, dokumenty MS Office Excel (2001.xls, 2002.xls, 2003.xls, 2004.xls, 2005.xls, 2006.xls, 2007.xls) ∑ počet obyvatel > 0 nebo NULL (hodnota není uvedena) Jednotky obyvatel Počet obyvatel muži (počet) Počet obyvatel muži ČSÚ, dokumenty MS Office Excel (2001.xls, 2002.xls, 2003.xls, 2004.xls, 2005.xls, 2006.xls, 2007.xls) ∑ počet obyvatel muži > 0 nebo NULL (hodnota není uvedena) Jednotky obyvatel Počet obyvatel ženy (počet) Počet obyvatel ženy ČSÚ, dokumenty MS Office Excel (2001.xls, 2002.xls, 2003.xls, 2004.xls, 2005.xls, 2006.xls, 2007.xls) ∑ počet obyvatel ženy > 0 nebo NULL (hodnota není uvedena) Jednotky obyvatel

84


Název Identifikace Rozsah hodnot




Martin Horký

Celkové příjmy na 1 obyvatele Celkové příjmy na 1 obyvatele Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ČSÚ, dokument MS Office Excel (2001.xls, 2002.xls, 2003.xls, 2004.xls, 2005.xls, 2006.xls, 2007.xls) ∑ R16 Příjmy celkem po konsolidaci / ∑ Počet obyvatel > 0 nebo NULL (hodnota není uvedena) V tisících Kč Daňové příjmy 1 obyvatele Daňové příjmy 1 obyvatele Výkaz ARIS č. 40, část: I. Rozpočtové příjmy ČSÚ, dokument MS Office Excel (2001.xls, 2002.xls, 2003.xls, 2004.xls, 2005.xls, 2006.xls, 2007.xls) ∑ R8 Daňové příjmy celkem / ∑ Počet obyvatel > 0 nebo NULL (hodnota není uvedena) V tisících Kč

5.2.1.2 Sledované dimenze V rámci pilotního projektu budou sledovány následující dimenze:

Roky

Územní klasifikace

Úrovně řízení

Velikostní skupiny

Dimenze „Roky“ Struktura Zdroj Výpočet

Možné hodnoty dimenze (prvky)

Identifikace

1. Roky dim_Roky 1.1. Rok rok ČSÚ, dokument MS Office Excel (soubory po jednotlivých letech 2001–2007) –

85


Dimenze „Územní klasifikace“ Struktura

Zdroj Výpočet

Dimenze „Úrovně řízení“ Struktura

Zdroj Výpočet

Dimenze „Velikostní skupiny“ Struktura Zdroj


Martin Horký

Identifikace

1. Územní klasifikace dim_AdministrativniCleneniCR 1.1. Region soudružnosti NUTS_2_Kod_regionu_soudruznosti 1.1.1. Kraj NUTS_3_Kod_kraje 1.1.1.1 Okres LAU_1_Kod_okresu 1.1.1.1.1 Obec LAU_2_Kod_obce ČSÚ, dokument MS Office Excel (soubor „Struktura území ČR k 1.1.2008“) Při zařazení dané municipality do hierarchie územní klasifikace se uplatňuje princip „Jako v současnosti“ – tedy obce jsou na základě kódu LAU 2 zařazeny podle stavu k 1.1.200834. Možné hodnoty dimenze Identifikace (prvky) 1. Úrovně řízení dim_AdministrativniCleneniCR 1.1. Kraj NUTS_3_Kod_kraje 1.1.1 ORP ORP_kod 1.1.1.1. OPOÚ OPOU_kod 1.1.1.1.1 Obec LAU_2_Kod_obce ČSÚ, dokument MS Office Excel (soubor „Struktura území ČR k 1.1.2008“) Při zařazení dané municipality do hierarchie úrovně řízení se uplatňuje princip „Jako v současnosti“ – tedy obce jsou na základě kódu LAU 2 zařazeny podle stavu k 1.1.200834.


Identifikace

1. Velikostní skupiny dim_VelikostniSkupiny_kategorie Velikostni_skupina_key 1.1. Skupina Definice 10 velikostních skupin dle metody FAMA: do 199 obyvatel 200–499 obyvatel 500–999 obyvatel 1000–1999 obyvatel 2000–4999 obyvatel 5000–9999 obyvatel 10 000–19 999 obyvatel

34

K 1. lednu 2008 došlo ke změně týkající se administrativního uspořádání republiky: k tomuto datu byly změněny kódy NUTS 3 u kraje Vysočina (z kódu CZ061 na CZ063) a Jihomoravského kraje (z kódu CZ062 na CZ064). Tato změna souvisí se změnou průběhu vzájemné hranice těchto krajů od 1.1.2005. (Zdroj: http://cs.wikipedia.org/wiki/CZ-NUTS.)

86


Martin Horký

20 000–49 999 obyvatel 50 000–99 999 obyvatel nad 100 000 obyvatel Určení velikostních skupin obcí na základě počtu obyvatel v roce 2007. Výpočet velikostní skupiny k danému roku je specifikováno z toho důvodu, aby se při určité úrovni agregace nestalo, že např. v roce 2005 je v dané skupině zahrnuto 8 obcí, ale v roce 2006 je to už 9 municipalit, protože jedna obec se v dalším roce již zařadila do jiné kategorie. Výsledky agregací finančních ukazatelů podle velikostních skupin by pak nebylo možné meziročně srovnávat.

Výpočet Poznámka

Úrovně řízení

Velikostní skupiny

DPFO ze závislé činnosti (finančně) DPFO z vlastní činnosti (finančně) DPPO bez obcí (finančně) Místní poplatky (finančně) Daňové příjmy celkem (finančně) Příjmy z vlastní činnosti (finančně) Neinvestiční dotace, bez převodu z vlastních fondů (finančně) Běžné opakující se příjmy (finančně) Ostatní příjmy (finančně) Kapitálové příjmy (finančně) Kapitálové dotace (finančně) Příjmy celkem po konsolidaci (finančně) Počet obyvatel (počet) Počet obyvatel muži (počet) Počet obyvatel ženy (počet) Celkové příjmy na 1 obyvatele (finančně) Daňové příjmy na 1 obyvatele (finančně)


Ukazatel / Dimenze

Roky

5.2.1.3 Vztah ukazatelů a dimenzí

× × × × × ×

× × × × × ×

× × × × × ×

× × × × × ×

×

×

×

×

× × × × × × × × × ×

× × × × × × × × × ×

× × × × × × × × × ×

× × × × × × × × × ×

Ze vzájemných vazeb mezi ukazateli a dimenzemi mimo jiné vyplývá, že není nutné vytvářet datová tržiště, pomocí kterých by se údaje v datovém skladu mohly analyzovat z různých hledisek. Nicméně datová tržiště mohou být vhodná v případě, že nám stačí

87


Martin Horký

analyzovat pouze určitou množinu dat nebo v určitých pohledech (výběrem dimenzí). Výpočty nad takovýmto menším modelem jsou potom rychlejší.

5.2.1.4 Databázové tabulky Na základě definovaných ukazatelů a dimenzí a analýzy datových zdrojů bylo navrženo celkem sedm tabulek: 1 tabulka faktů, 3 tabulky dimenzí a 3 tabulky pomocné pro transformaci dat ze zdrojových systémů (Obrázek 36).

Obrázek 36: Struktura databáze pro uložení zdrojových dat. TABULKY FAKTŮ fact_UkazateleFAMA [ObecICO] [int], [LAU_2_kod] [int], [Nazev_obce] [nchar](255), [Rok] [smallint], [R1_DPFO_ze_zavisle_cinnosti] [numeric](18, 2), [R2_DPFO_z_vlastni_cinnosti] [numeric](18, 2), [R3_DPPO_bez_obci] [numeric](18, 2), [R7_Mistni_poplatky] [numeric](18, 2),

88


[R8_Danove_prijmy_celkem] [numeric](18, 2), [R9_Prijmy_z_vlastni_cinnosti] [numeric](18, 2), [R10_Neinvesticni_dotace] [numeric](18, 2), [R11_Bezne_se_opakujici_prijmy] [numeric](18, 2), [R12_Ostatni_prijmy] [numeric](18, 2), [R13_Kapitalove_prijmy] [numeric](18, 2), [R15_Kapitalove_dotace] [numeric](18, 2), [R16_Prijmy_celkem_po_konsolidaci] [numeric](18, 2), [Celkovy_pocet_obyvatel] [int], [Pocet_obyvatel_muzi] [int], [Pocet_obyvatel_zeny] [int], [Velikostni_skupina_key] [int] TABULKA DIMENZÍ dim_AdministrativniCleneniCR [LAU_2_Kod_obce] [int], [LAU_2_Nazev_obce] [nchar](255), [LAU_2_Statut_obce] [nchar](255), [OPOU_kod] [int], [OPOU_nazev] [nchar](255), [ORP_kod] [int], [ORP_nazev] [nchar](255), [LAU_1_Kod_okresu] [nchar](255), [LAU_1_Nazev_okresu] [nchar](255), [NUTS_3_Kod_kraje] [nchar](255), [NUTS_3_Nazev_kraje] [nchar](255), [NUTS_2_Kod_regionu_soudruznosti] [nchar](255), dim_Roky [rok] [smallint] dim_VelikostniSkupiny_kategorie [Velikostni_skupina_key] [int], [Dolni_limit] [int], [Horni_limit] [int], [Velikostni_skupina] [nchar](40) POMOCNÉ TABULKY pom_DemografickeUdaje [Rok] [smallint], [LAU_2_kod] [int], [Celkovy_pocet_obyvatel] [int], [Pocet_obyvatel_muzi] [int], [Pocet_obyvatel_zeny] [int]

89

Martin Horký


Martin Horký

pom_PolozkyRozpoctoveSkladby [id] [bigint], [ObecICO] [int], [Rok] [smallint], [PolozkaRozpoctoveSkladbyKod] [smallint], [HodnotaUkazatele] [numeric](18, 2) pom_SeznamObci [Nazev_obce] [nchar](255), [LAU_2_kod] [int], [ICO] [int] Pomocné tabulky byly navrženy pro import reprezentativního výběru obcí (tabulka pom_SeznamObci), uložení položek rozpočtové skladby z transformovaných výkazů ARIS (tabulka pom_PolozkyRozpoctoveSkladby) a import demografických údajů (tabulka pom_DemografickeUdaje).

90


Martin Horký

5.2.1.5 Odhad velikosti databáze Tabulka 22: Odhad velikosti datového skladu Celkem Předpokládaná Předpokládaný Tabulka bytů pro celková velikost počet řádků záznam tabulky fact_UkazateleFAMA 6×int (6×4 B = 24 B) 3304 1×smallint (2 B) 389 B (472 obcí×7 1,22 MB 1×nchar (255 B) roků) 12×numeric(18) (12×9 B = 108 B) dim_AdministrativniCleneniCR 3×int (3×4 B = 12 B) 2562 B 6250 15,2 MB 10×nchar (10×255 B = 2550 B) dim_Roky 2B 7 0 MB 1×smallint (2 byty) dim_VelikostniSkupiny_kategorie 3×int (3×4 B = 12 B) 52 B 10 0 MB 1×nchar (40 B) 43750 pom_DemografickeUdaje 1×smallint (2 B) 18 B (6250 obcí×7 0,75 MB 4×int (4×4 B = 16 B) roků) pom_PolozkyRozpoctoveSkladby 1×bigint (8 B) 1×int (4 B) 28 B cca 1 500 000 40 MB 2×smallint (4 B) 1×numeric(18) (12 B) pom_SeznamObci 1×nchar (255 B) 263 B 472 0,1 MB 2×int (2×4 = 8 B) (včetně velikost indexů 200 % základu = Celková velikost databáze (včetně pomocných tabulek) 114,5 MB)

172 MB Celková velikost datového skladu (bez pomocných tabulek)

(včetně velikost indexů 200 % základu = 35,5 MB)

53 MB K uvedeným hodnotám je nutné přičíst také nároky na režii databázového systému, jako jsou dočasné soubory, logy, rollback a další. I přesto je objem analyzovaných dat vzhledem k náročnosti výpočtů prakticky zanedbatelný. Význam může mít pouze pro práci s offline OLAP kostkami, kdy je žádoucí její velikost co nejvíce zredukovat.

91


Martin Horký

5.2.2 Analýza datových zdrojů Cílem analýzy datových zdrojů je posoudit dostupnost zdrojů, ale i softwarových nástrojů pro zpracování dat (transformace, agregace atd.). Do procesu datové transformace vstupují následující data (Obrázek 37):

Výkazy ARIS – jedná se o hlavní zdroj údajů pro výpočet finančních ukazatelů ve formátu XML; výkazy obsahují hodnoty jednotlivých rozpočtových položek dané municipality v druhovém členění (více o struktuře dat v následující kapitole).

Administrativní členění ČR – kategorizace obcí dle administrativního členění bude určena na základě evidence Českého statistického úřadu, která je k dispozici ve formátu MS Office Excel (XLS); data obsahují jak členění dle územní klasifikace (regiony soudružnosti, kraje, okresy), tak podle úrovně řízení (kraje, ORP, OPOÚ).

Vývoj počtu obyvatel – počty obyvatel eviduje Český statistický úřad; podklady za celé sledované období (roky 2001 až 2007) jsou volně k dispozici na internetových stránkách úřadu ve formátu MS Office Excel (XLS); soubory obsahují data na úrovni každé obce, která je identifikována pomocí kódu obce, resp. LAU 2 kódu35.

Reprezentativní výběr obcí – datový sklad bude obsahovat pouze údaje dané množiny sídel; rozsah těchto obcí je vymezen taxativně v souboru MS Office Excel (XLS), který byl sestaven autorem této práce na základě reprezentativního vzorku 472 obcí provedeného prof. Wokounem.

35

Pro identifikaci municipalit používá ČSÚ několik kódů: „kód obce“, kód ZUJ a kód LAU 2. Tyto kódy jsou navzájem zřejmě identické. ZUJ (základní územní jednotka) představuje označení určitého území, které se používalo v minulosti. LAU 2 (Local Area Unit úrovně 2) souvisí s povinností vykazovat statistická data pro Evropský statistický úřad Eurostat v jednotkách srovnatelných v rámci celého společenství. ČSÚ ve svých výstupech uvádí jak jednotky ZUJ tak LAU 2, a to navzájem s totožnými hodnotami. Alespoň na vybraném vzorku municipalit nebyly nalezeny žádné nesrovnalosti. Definice jednotlivých kódů, které používá ČSÚ pro identifikaci municipalit, je uvedena v příloze A.

92


Martin Horký

Obrázek 37: Přehled zdrojů vstupujících do datové transformace V následující tabulce jsou uvedeny detailní informace o struktuře zdrojových dat. Tabulka 23: Přehled zdrojů Název

Formát

Výkazy ARIS36

XML

Administrativní členění ČR k 1.1.2008 soubor „strukturauzemi-cr-2008.xls“

XLS

Vývoj počtu obyvatel 2001.xls, 2002.xls, 2003.xls, 2004.xls, 2005.xls, 2006.xls, 2007.xls

XLS

Reprezentativní výběr obcí38 (seznam 472 municipalit)

XLS

Původ

Struktura dat

ARIS

Nástroje pro práci s daty XSLT, XPath

viz následující kapitola 5.2.2.1 ČSÚ kód obce, MS Office Excel název obce, kód OPOÚ37, název OPOÚ, kód ORP37, název ORP, kód okresu, název okresu, kód kraje, název kraje, kód regionu soudružnosti, název regionu soudružnosti ČSÚ kód obce, MS Office Excel počet obyvatel, počet mužů, počet žen, průměrný věk, průměrný věk mužů, průměrný věk žen prof. název obce, MS Office Excel Wokoun LAU 2 kód, IČO

36

Výkazy ve formátu XML jsou stažené pro každou jednotlivou obec a rozpočtový rok. Význam zkratek: OPOÚ – obec s pověřeným obecním úřadem, ORP – obec s rozšířenou působností. 38 Seznam vychází z dokumentu prof. Wokouna, který obsahoval jména obcí, jejich rozdělení dle krajů a úrovně řízení – ORP, resp. OPOÚ (viz příloha A). Aby bylo možné v datovém skladu jednoznačně identifikovat danou obec, rozšířil autor této práce daný seznam o dva identifikátory: kód obce (LAU 2) a IČO. Hledání identifikačních znaků probíhalo strojově v aplikaci PHP na základě seznamu obecních, městských a magistrátních úřadů České republiky, který pro účely této práce bezplatně poskytl ČSÚ. 37

93


Martin Horký

Pro úplnost dodejme, že všechny zdroje je možné zpracovat pomocí standardních nástrojů integračních služeb MS SQL Serveru 2005, včetně dokumentů ve formátu XML.

5.2.2.1 Výkaz ARIS č. 40 – struktura rozpočtových příjmů Mezi nejdůležitější zdroje datového skladu patří výkazy Automatizovaného rozpočtového informačního systému ARIS, který nabízí zejména dva výkazy, které tvoří podklad pro finanční a majetkovou analýzu:

výkaz č. 40 o plnění příjmů a výdajů rozpočtových organizací

výkaz č. 60 rozvaha rozpočtových organizací.

Oba výkazy lze získat z databáze ARIS ve formátu XML39. V rámci pilotního projektu budou zpracovávána pouze data z výkazu č. 40 o plnění příjmů a výdajů rozpočtových organizací. Jeden XML výkaz č. 40 obsahuje údaje o hospodaření za jedno účetní období (rok) pro jednu obec. Struktura výkazu je následující40: <aris:Vykaz> <aris:VykazID> <aris:VykazCislo>40 <aris:VykazNazev> Výkaz pro hodnocení plnění rozpočtů územních samospr.celků a DSO <aris:DruhRizeniKod>2 <aris:DruhRizeniNazev> Místně řízené organizace 00282481 <aris:OrganizaceNazev>MěÚ Rosice 20051231 <aris:KapitolaKod>700 <aris:KapitolaNazev> Kapitoly místních rozpočtů <aris:CastFormulare> 39

Výkazy je možné z webových stránek ARISu získat také ve formátu HTML stránek. Jejich struktura a rozsah dat je však odlišný. 40 Obdobnou strukturu má i výkaz č. 60 rozvaha aktiv a pasiv.

94


Martin Horký

<aris:CastFormulareID> <aris:CastFormularePoradi> 1 <aris:CastFormulareNazev> I. Rozpočtové příjmy <aris:BunkaFormulare> <aris:BunkaFormulareID> <aris:BunkaFormulareIDPrijmyX40> <aris:ParagrafRozpoctoveSkladby> <aris:ParagrafRozpoctoveSkladbyKod> 0 <aris:PolozkaRozpoctoveSkladby> <aris:PolozkaRozpoctoveSkladbyKod> 1111 <aris:SloupecFormulareCislo> 3 <aris:SloupecFormulareNazev> Výsledek od počátku roku <aris:HodnotaUkazatele>8016.82 <aris:BunkaFormulare> ... ... <aris:CastFormulare> ... ...

Výkaz č. 40 je definován pomocí hierarchie XSD schémat, které velmi podrobně specifikují sémantiku jednotlivých částí XML dokumentu. Hlavním schématem je schéma arisTypes.xsd41,

které

využívá

definice

dalších

importovaných

schémat

CoreComponentTypes, SpaceTypes, CommonTypes a BusinessTypes. 41

Schéma arisTypes.xsd je velice dobře dokumentované. Každá definice je doprovozena vysvětlujícím komentářem s odkazem na číselník nebo standard, který určuje rozsah hodnot příslušného elementu.

95


Martin Horký

Kořenovým elementem dokumentu je značka aris:Vykaz, který obsahuje identifikační hlavičku (element aris:VykazID) a jednotlivé části oddělené značkami aris:CastFormulare. Hlavička výkazu obsahuje kromě jiných identifikační údaje číslo výkazu (aris:VykazCislo) a IČO subjektu (bs:SubjektICO), na základě kterého lze jednoznačně určit příslušnost údajů v dokumentu konkrétní municipalitě. Jednotlivé části výkazu uzavírá element aris:CastFormulare. Výkaz č. 40 jich obsahuje celkem šest42: I. Rozpočtové příjmy II. Rozpočtové výdaje III. Financování IV. Rekapitulace příjmů, výdajů a financování a jejich konsolidace VI. Stavy a obraty na bankovních účtech IX. Přijaté dotace a půjčky ze SR Každá část výkazu obsahuje buňky (aris:BunkaFormular), které určují význam hodnoty (aris:HodnotaUkazatele) podle čísla položky druhového členění rozpočtové skladby (aris:PolozkaRozpoctoveSkladbyKod)43. Pro účely analýzy nás přitom zajímá pouze stav na konci roku, tedy element aris:SloupecFormulareCislo má hodnotu rovnu 344. Hodnoty jsou uváděny v tisících korun českých. Jeden XML výkaz č. 40 má průměrně kolem 30 000 řádků a velikost zhruba 1 MB. Pro potřeby pilotního projektu bude zpracovávána pouze první část (I. Rozpočtové příjmy), která obsahuje jednotlivé položky dle druhového členění rozpočtové skladby.

Dokumentace poslední verze schéma 1.0.1 z roku 2005 je k dispozici také on-line na adrese: http://wwwinfo.mfcr.cz/aris/xml_doc/schemas/v_1.0.1/doc/index.html a je také součástí přílohy této práce na CD. 42 Ve webovém rozhraní databáze ARIS jsou nabízeny ke stažení výkazy I, II a III a IX v HTML podobě. Třetí výkaz je označován jako „Pevné části“. 43 Výkaz také obsahuje identifikaci příslušného finančního toku pomocí odvětvového členění (element aris:ParagrafRozpoctoveSkladby). Znamená to tedy, že jednotlivá buňka nemusí obsahovat celkovou částku příslušné položky ve smysly druhového členění, ale pouze její část odpovídající odvětvovému (funkčnímu) využití. Celková částka odpovídající jedné položce rozpočtové skladby pak odpovídá součtu všech buněk z různými čísly paragrafů. 44 Výkaz kromě hodnot odpovídajících výsledku od počátku roku obsahuje také hodnoty na začátku roku a hodnoty po provedených rozpočtových změnách.

96


Martin Horký

5.2.3 Návrh datového skladu Datový model byl navržen s hvězdicovým uspořádání faktových a dimenzionálních tabulek (Obrázek 38). Hvězdicové schéma bylo zvoleno s ohledem na jednodušší implementaci datového skladu, ale především pak vzhledem k snadnějšímu rozšíření modelu o další tabulky v budoucnu.

Obrázek 38: Model datového skladu (export z prostředí SQL Serveru) Všechny

definované

ukazatele

jsou

uloženy

v jedné

faktové

tabulce

(fact_UkazateleFAMA), která je propojena se třemi dimenzionálními entitami. Při návrhu datového modelu byl brán v úvahu požadavek, aby analyzovaná data byla vztažena ke stavu nejnovějších dostupných dat, tj. ke stavu v roce 2007 v případě určení velikostních skupin obcí, resp. ke stavu k 1.1.2008 v případě administrativního členění. Jedná se tedy o princip kategorizace sledovaných ukazatelů způsobem „jako ve skutečnosti“. Nebylo tedy nutné řešit problematiku měnících se dimenzí – Slowly Changing Dimensions (SCD). Všechny modelované dimenze jsou statické.

5.2.4 Datová transformace Datový sklad je realizován pomocí sekvence integračních úloh (ETL procesů). Úlohy lze rozdělit do dvou vrstev. První vrstva transformačních úloh zpracovává data ze zdrojových

97


Martin Horký

systémů (především XML výkazy) a ukládá je do pomocných tabulek, kde jsou data připravena ve vhodné struktuře pro další zpracování. Úlohy ve druhé vrstvě pak data mapují na datový model datového skladu, údaje jsou agregovány na požadovanou úroveň granularity, je prováděno jejich čištění (zejména odstranění NULL hodnot), změna datových typů a aplikována integritní omezení.

Obrázek 39: Hlavní proces datové integrace. Celý proces datové integrace (Obrázek 39) se skládá z několika skupin úloh. Skupina „Inicializace databáze“ sdružuje jednoduché úlohy, které vytvoří tabulky datového skladu, nastaví integritní omezení, importují XSD schémata pro práci s výkazy ve formátu XML a naplní číselníky dim_VelikostniSkupiny_kategorie a dim_Roky. Nejnáročnější částí integračních služeb je skupina úloh, které zpracovávají výkazy ve formátu XML pomocí XSL transformací (viz následující kapitola). Navazující služby pak spadají do druhé vrstvy – jednotlivé úkoly naplňují datový sklad buď daty z externích zdrojů (administrativní struktura, demografické ukazatele), nebo

98


Martin Horký

z předpřipravených dat z transformace XML výkazů (výpočty základních finančních ukazatelů). Všechny hlavní části procesu datové integrace jsou propojeny pomocí toků typem „Complete“. Další skupina úloh je tedy provedena až po dokončení všech úloh v předchozím kroku. To je důležité zejména u řízení doby vykonání loop containeru „Příprava struktury faktové tabulky“, který musí čekat na dokončení obou předchozích úloh.

5.2.4.1 Postup plnění datového skladu Specifikou tohoto projektu BI je, že rozsah analyzovaných dat je dopředu známý. Ten je určen seznamem 472 obcí a časovým rozmezím roky 2001–2007. V rámci datové integrace byl zvolen takový přístup plnění skladu, jenž se odvíjí od předem známé struktury faktové tabulky, která se pak v následujících krocích plní hodnotami ukazatelů. Příprava této struktury je realizována pomocí úlohy „Uložení výběru obcí do pomocné tabulky“ (Obrázek 39), která importuje reprezentativní výběr municipalit v rozsahu název obce, LAU 2 kód a IČO. V následném kroku jsou pomocí loop containeru „Příprava struktury faktové tabulky“ vytvořeny ve faktové tabulce záznamy vycházející z uvedeného seznamu a časového rozsahu. Celkem je tedy předvyplněno 3304 záznamů (472 obcí × 7 roků).

Obrázek 40: Postup plnění datového skladu (diagram vlastní)

99


Martin Horký

Tento přístup byl zvolen ze dvou důvodů. Pro analýzy reprezentativního vzorku je důležité, aby v přehledech byly zastoupeny všechny obce. Pokud by z nějakého důvodu chyběla data některé obce (např. nevalidní XML výkaz, nebo chybějící demografické ukazatele), závěry analýz předpokládající daný vzorek obcí by nebyly korektní. Určující je tedy výběr obcí, nikoliv data, která o nich máme k dispozici. Druhým důvodem je možnost snadného rozšíření množiny sledovaných obcí. Do datového skladu jsou importována data o vývoji počtu obyvatel a administrativního členění pro všechny obce České republiky. Na tuto množinu dat je potom aplikována projekce na základě výběru obcí (identifikace podle LAU 2 kódu). Administrativní struktura je uložena v samostatné dimenzionální tabulce, údaje o vývoji počtu obyvatel jsou importovány přímo do faktové tabulky. Tento přístup umožňuje velice snadno rozšířit rozsah výběru obcí o další municipality jednoduše přidáním dalšího řádku do vstupního seznamu (soubor MS Office Excel) a stažením výkazů z webových stránek ARISu. Všechny ostatní potřebné údaje se automaticky získají z uvedených zdrojů a ukazatele spočítají aniž by bylo nutné měnit transformační úlohy datové integrace.

5.2.4.2 Technické a organizační zabezpečení integračních úloh V rámci návrhu integračních úloh pilotního projektu nebylo uvažováno propojení komponent Business Intelligence s komunikační infrastrukturou ICT, která by zabezpečovala

přístup

k datům

datového

skladu

pro

finanční

analytiky

např. prostřednictvím webových protokolů. Datový sklad a integrační úlohy byly realizovány pouze na osobním počítači autora.

5.2.4.3 Zpracování XML výkazů Zpracování XML výkazů z databáze ARIS je nejnáročnější částí datové integrace v pilotním projektu, a to jednak z hlediska objemu dat (více jak 2,9 GB), tak z hlediska technologického. Výkazy stažené z webového serveru databáze ARIS se v podobě XML souborů nejprve transformují pomocí XLS transformace do jednodušší struktury na úroveň jednotlivých

100


položek

rozpočtové

skladby

a

následně

importují

do

Martin Horký

pomocné

tabulky

pom_PolozkyRozpoctoveSkladby. Z ní jsou pak v posledním kroku vypočítány hodnoty jednotlivých ukazatelů a uloženy do datového skladu (Obrázek 41).

Obrázek 41: Postup zpracování XML výkazů (diagram vlastní) Stažení výkazů z databáze ARIS Výkazy č. 40, resp. č. 60 jsou strukturovány ve formátu XML, který lze z ARISu získat pomocí protokolu HTTP. Požadavek se realizuje modelem klient/server prostřednictvím aplikace vykazxml.pl dostupné na adrese http://wwwinfo.mfcr.cz/cgi-bin/aris/iarisxml/.

Skript vyžaduje následující tři parametry předané metodou GET nebo POST:

ico – identifikační číslo

obdobi – vstup ve formátu RRRR1200 (kde R = rok, jedná se vždy o výkaz daného roku k měsíci prosinec)

vykaz – identifikace výkazu, č. 40, resp. č. 60

Následující příklad dotazu GET vrátí XML dokument s výkazem č. 40 města Rosice za rok 2007: http://wwwinfo.mfcr.cz/cgibin/aris/iarisxml/vykazxml.pl?ico=00282481&obdobi=20071200&vykaz=40

101


Martin Horký

Tímto způsobem byly strojově získány všechny výkazy č. 40 za sledovaný vzorek 472 municipalit, tj. celkem 3304 výkazů. XML výkazy byly pro další práci v rámci integračních služeb uloženy ve formě textových souborů na pevném disku45. V integračních službách lze zpracovávat i data získaná protokolem HTTP, tedy např. přímo data poskytovaný webovými službami. Postup nejprve stáhnout data na pevný disk a následné je zpracovat byl zvolen zejména vzhledem k rozsahu dat a také dostupnosti webové aplikace, která vždy nemusí být přístupná. XSL transformace Výkazy uložené v XML souborech jsou transformovány pomocí XSL šablony v rámci dvou foreach kontejnerů seskupených do úlohy „Zpracování XML výkazů číslo 40 pomocí XSL transformace a uložení do pomocné tabulky“ (Obrázek 42).

Obrázek 42: XSL transformace XML výkazů

První foreach komponenta (levá) postupně čte všech 3304 XML výkazů v daném adresáři a pro každý takovýto soubor provede XSL transformaci, jejíž výsledek uloží opět ve formátu XML do souboru s jiným jménem. Pro transformaci se používá šablona styl-vykaz40.xsl, která z výkazu vybere pouze hodnoty závěrečného rozpočtu (stav na konci kalendářního roku) pro každou položku

45

Stažení dat bylo realizováno pomocí PHP/MySQL aplikace. Aplikace postupně generovala GET požadavek na server ARIS. Získané XML dokumenty byly zpracovány technikou datového streamu protokolu HTTP a uloženy do souboru na pevný disk. Generování dotazů probíhalo násobně z několika instancí aplikace. Stažení 3304 výkazů č. 40 vybraných municipalit o celkové velikosti 2,96 GB trvalo necelých 7 hodin.

102


Martin Horký

druhového členění rozpočtové skladby a záznamy vypíše ve formě zjednodušené struktury XML. <xsl:template match="/"> <xsl:param name="SubjectICO" select="aris:Vykaz/aris:VykazID/bs:SubjektICO" /> <xsl:param name="DatumMezinarodni" select="substring( aris:Vykaz/aris:VykazID/cmn:DatumMezinarodni, 1, 4)" /> <xsl:for-each select="aris:Vykaz/aris:CastFormulare"> <xsl:for-each select="aris:BunkaFormulare"> <xsl:if test="aris:BunkaFormulareID/aris:BunkaFormulareIDPrijmyX40/aris:SloupecFor mulareCislo = 3"> <xsl:apply-templates select="aris:BunkaFormulareID"> <xsl:with-param name="SubjectICO" select="$SubjectICO"/> <xsl:with-param name="DatumMezinarodni" select="$DatumMezinarodni"/> <xsl:with-param name="PolozkaRozpoctoveSkladbyKod" select="aris:BunkaFormulareID/aris:BunkaFormulareIDPrijmyX40/aris :PolozkaRozpoctoveSkladby/aris:PolozkaRozpoctoveSkladbyKod"/> <xsl:with-param name="HodnotaUkazatele" select="aris:HodnotaUkazatele"/> <xsl:template match="aris:BunkaFormulareID"> <xsl:param name="SubjectICO" /> <xsl:param name="DatumMezinarodni" /> <xsl:param name="PolozkaRozpoctoveSkladbyKod" /> <xsl:param name="HodnotaUkazatele" /> <SubjectICO> <xsl:value-of select="$SubjectICO" /> <xsl:value-of select="$DatumMezinarodni" /> <xsl:value-of select="$PolozkaRozpoctoveSkladbyKod" /> <xsl:value-of select="$HodnotaUkazatele" />

103


Martin Horký

Výsledný dokument má jednodušší strukturu vhodnou pro zpracování v relačním modelu. Následující ukázka představuje začátek výkazu města Rosice za rok 2007: <SubjectICO>00282481 2007 1111 9198.05 <SubjectICO>00282481 2007 1112 4066.99 <SubjectICO>00282481 2007 1113 563.54 ...

Šablona uvedená výše je zkrácená verze kompletní šablony, jenž implementuje transformaci všech tří částí, tj. i části II. Rozpočtové výdaje a části III. Financování, i když se tato data v datovém skladu nevyužívají. Je tedy možné provést výpočty všech ukazatelů metody FAMA, nejen části týkající se rozpočtových příjmů, ale také ukazatelů rozpočtových výdajů a financování všech sledovaných municipalit.

Výpočet ukazatelů Transformované XML dokumenty jsou pomocí druhé komponenty foreach postupně procházeny (zpřístupněné komponentou XML Source) a ukládány do pomocné tabulky pom_PolozkyRozpoctoveSkladby v totožné struktuře jako je struktura zjednodušeného výkazu46.

XSD schémata Transformace XML výkazů pomocí XSLT procesu nebo i samotný přístup ke XML datům je v integračních službách MS SQL Server 2005 možné pouze za předpokladu, že databáze disponuje znalostí o elementech použitých v dokumentech. Zpracovávání XML totiž

46

Analyzovaný vzorek 472 obcí za období 7 roků obsahuje celkem 1 048 914 záznamů.

104


Martin Horký

probíhá podle XML schématu. Pokud dokument obsahuje strukturu, element nebo atribut, který není nebo neodpovídá definici ve schématu, není tato hodnota zpracována a předána na výstup. Na druhou stranu pokud XML dokument neobsahuje element, který je ve schématu definován, výstup bude tento sloupec obsahovat ale s nulovou hodnotou (NULL). Abychom tedy mohli pracovat s dokumenty XML, je nejprve nutné importovat všechna schémata do prostředí databáze. XML výkazy obsahují elementy z několika jmenných prostorů definovaných sérií XSD schémat, které jsou navzájem hierarchicky provázány. Nejdůležitější elementy XML výkazů patří do jmenného prostoru aris, který je definován schématem arisTypes.xsd. Schéma obsahuje definice jednoduchých datových prvků Informačních systémů veřejné správy (ISVS, verze 1.0.2.) používaných pro výkaznictví a popis ekonomických subjektů. arisTypes.xsd schéma importuje následující schémata:

CoreComponentTypes.xsd

isvs_common.xsd, který importuje také schéma o CoreComponentTypes.xsd

isvs_space.xsd, který importuje také schémata: o CoreComponentTypes.xsd o isvs_common.xsd

isvs_business.xsd, který importuje také schémata: o CoreComponentTypes.xsd o isvs_common.xsd o isvs_space.xsd

Reprezentace těchto schémat se v databázi SQL Server nazývá kolekce (Schema Collection), která poté umožní pracovat se XML dokumenty a transformovat je pomocí XSLT. Nejprve je nutné kolekci vytvořit. To se provede příkazem create xml schema collection: declare @don xml select @don=bulkcolumn from openrowset(bulk 'c:\TEMP\CoreComponentTypes.xsd', single_blob) as s create xml schema collection dbo.collectionName as @don go

105


Martin Horký

Prvním importovaným schématem musí být schéma, které nepoužívá jiný jmenný prostor, tedy neimportuje žádné jiné schéma. Tímto základním schématem je schéma CoreComponentTypes.xsd. V následujících krocích se importují další schémata do stejné kolekce příkazem alter xml schema collection, a to v pořadí, aby nebyla porušena hierarchie jmenných prostorů47: declare @don xml select @don=bulkcolumn from openrowset(bulk 'c:\TEMP\isvs_common.xsd', single_blob) as s alter xml schema collection dbo.collectionName add @don go

Importovaná schémata je možné najít v záložce Programmability → Types → XML Schema Collection (viz Obrázek 43).

Obrázek 43: Kolekce schémat SQL Server 2005 (vlastní export z prostředí databáze) Kolekce schémat je nutné vytvářet pro transformaci XML výkazů, nicméně pro základní přístup k jakýmkoliv XML datům pomocí komponenty XML source není kolekce nutná. Komponenta dovolí na základě importovaného dokumentu vygenerovat příslušnou definici XSD automaticky a uložit ji do souboru na pevný disk.

47

Schémata musí obsahovat jednoznačné definice. Např. není povolen atribut processContents="lax", který dovoluje, aby byl příslušný element přítomen nebo nepřítomen, což je definice nejednoznačné podoby dokumentu. Hodnota atributu tedy musí být změněna např. na "skip". Tento problém se vyskytl u definice elementu Rozsireni (RozsireniStructure) ve schématu isvs_common.xsd.

106


Martin Horký

5.2.4.4 Import demografických ukazatelů Skupina úloh „Import demografických ukazatelů“ hlavního procesu datové integrace obsahuje několik úloh, které postupně načítají data za roky 2001 až 2007 ze zdrojů ČSÚ (formát XLS Excel) a ukládají je do pomocné tabulky pom_DemografickeUdaje (Obrázek 44).

Obrázek 44: Skupina integračních úloh pro import demografických ukazatelů (vlastní export z prostředí SQL Server 2005) V předposledním kroku je proveden výběr demografických dat v rozsahu sledovaných municipalit a údaje uloženy do faktové tabulky (Obrázek 45). Mapování výběru municipalit na demografické údaje probíhá na základě LAU 2 kódu obce a roku. Množiny z obou zdrojů jsou složeny pomocí operace left outher join, která zajistí výběr demografických ukazatelů jen sledovaných municipalit a zároveň bude zachována informace, kdy k některé municipalitě demografická data nejsou k dispozici.

107


Martin Horký

Obrázek 45: Obsah úlohy „Výběr údajů pro reprezentativní výběr obcí a uložení do tabulky faktů“ Poslední úlohou, která zpracovává demografická data je úloha „Určení velikostních skupin podle počtu obyvatel v roce 2007“ (Obrázek 44). Úloha je implementována pomocí SQL dotazu, který určí velikostní skupinu municipality na základě definice horního a dolního limitu v tabulce dim_VelikostniSkupiny_kategorie: SELECT LAU_2_kod, dim_VelikostniSkupiny_kategorie.Velikostni_skupina_key FROM fact_UkazateleFAMA LEFT JOIN dim_VelikostniSkupiny_kategorie ON Dolni_limit <= Celkovy_pocet_obyvatel AND Celkovy_pocet_obyvatel <= Horni_limit WHERE Rok = 2007

5.2.4.5 Výpočet finančních ukazatelů Výpočet základních finančních ukazatelů vychází z hodnot jednotlivých položek rozpočtové skladby, které jsou po XSL transformaci výkazů uloženy v pomocné tabulce pom_PolozkyRozpoctoveSkladby.

108


Martin Horký

Obrázek 46: Data flow úloha „Výpočet základních finančních ukazatelů FAMA – jednoduché řádky“ Samotný výpočet ukazatelů je realizován pomocí sady SQL dotazů, které vrací sumu daných položek dle definice. Následující dotaz například vrací hodnotu ukazatele „R10 Neinvestiční dotace“, který je definován jakou suma položek číslo 4111–4199 bez podseskupení 413 (tj. suma položek 4111–4129 + suma položek 4140–4199): SELECT ObecICO, Rok, SUM ( HodnotaUkazatele) as R10_Neinvesticni_dotace FROM dbo.pom_PolozkyRozpoctoveSkladby WHERE ( PolozkaRozpoctoveSkladbyKod BETWEEN 4111 AND 4129 ) OR ( PolozkaRozpoctoveSkladbyKod BETWEEN 4140 AND 4199 ) GROUP BY ObecICO, Rok

109


Martin Horký

Ostatní ukazatele, jejichž výpočet vychází z hodnot jiných ukazatelů, je realizován v rámci hlavního procesu datové integrace v poslední úloze „Základní finanční ukazatele mezisoučty“ (Obrázek 39).

5.2.5 Návrh OLAP kostky Nad modelem datového skladu (Obrázek 38) byla vytvořena OLAP kostka, která obsahuje všechny definované ukazatele a dimenze dle uživatelských požadavků a vymezení rozsahu řešení pilotního projektu. Základní finanční a demografické ukazatele doplňuje ukazatel „Počet obcí ve výběru“, který při analýze dat pomáhá zorientovat se v rozsahu sledovaného vzorku municipalit (Obrázek 47). Jako jediný ukazatel je jeho hodnota určena počtem různých kódů LAU 2 (agregační funkce DistinctCount48).

Obrázek 47: Ukazatele OLAP kostky

48

Agregační funkci DistinctCount není podporován v offline OLAP kostce.

110


Martin Horký

Další dva ukazatele jsou definovány na základě výpočtu hodnot jiných ukazatelů, jedná se o ukazatel „Daňové příjmy na obyvatele“ a ukazatel „Celkové příjmy na 1 obyvatele“. Výpočet hodnot ukazatelů je definován pomocí jazyka MDX po řadě: [Measures].[R08 Daňové příjmy celkem]/[Measures].[Počet obyvatel] [Measures].[R16 Příjmy celkem po konsolidaci]/[Measures].[Počet obyvatel]

Dimenze V OLAP kostce jsou definovány čtyři dimenze:

Roky

Velikostní skupiny


Úrovně řízení

Poslední dvě dimenze sestávají z množiny několika atributů, které jsou uspořádány do hierarchií (Obrázek 48).

Obrázek 48: Hierarchie dimenzí „Územní klasifikace“ (vlevo) a „Úrovně řízení“ (vpravo). Vztah mezi ukazateli a jednotlivými dimenzemi je definován pro každou dimenzi na základě jednotlivých referenčních integrit (Obrázek 49). Pro všechny agregace je určující granularita na úrovni obce (kód LAU 2).

111


Martin Horký

Obrázek 49: Použití jednotlivých dimenzí pro kategorizaci ukazatelů V analytických službách MS SQL Serveru 2005 nebyla na OLAP kostce definována žádná datová tržiště (Perspectives). Model pro uložení agregovaných dat byl zvolen MOLAP s 30% agregací.

5.2.6 Prezentační vrstva Cílem této kapitoly je představit základní reportovací a analytické nástroje tří systémů: prostředí MS SQL Serveru 2005, aplikace MS Office Excel 2007 a programu ProClarity 6.349. Úvodní část je věnována principům tzv. kontingenční tabulky (pivot table), která je základním sumarizačním nástrojem reportovacích a analytických aplikací.

5.2.6.1 Kontingenční tabulka Jedná se o základní sumarizační nástroj pro prezentaci údajů datového skladu, který lze najít ve většině reportovacích a analytických aplikací. Typickým představitelem je aplikace MS Office Excel. S kontingenční tabulkou (anglicky pivot table) lze kromě jiného data třídit, počítač a vytvářet odvozené tabulky se sumarizacemi. Podstatou práce s tabulkou (řečeno jazykem datových skladů) je výběr analyzovaných ukazatelů, dimenzí a filtrů, které určují rozsah zobrazených dat. Tento výběr je přitom realizován interaktivně pomocí myši z předem předpřipravených prvků. Uživatel vybírá

49

Pro účely analýz údajů v datovém skladu doporučuji zejména aplikaci MS Office Excel. Excel je jednak prostředí, které je běžně známé každému uživateli, a zároveň je to také analytická aplikace, která umí pracovat s tzv. offline OLAP kostkami

112


Martin Horký

jaké údaje budou zobrazeny ve sloupcích (ukazatele), pro jakou úroveň agregace v řádcích (dimenze) a v našem případě jaký časový okamžik (filtr). Tak jak uživatel specifikuje jednotlivé prvky tabulky, systém vypočítává příslušné agregace (Obrázek 50).

Obrázek 50: Kontingenční tabulka datového skladu FAMA v MS Office Excel 2007 (vlastní export) Jedná se o stejný princip, na jakém je založena multidimenzionální kostka popsaná v kapitole 4.3.2. Ať už je tato softwarová komponenta nazývána kontingenční tabulka, kontingenční kostka nebo datová kostka, vždy se jedná o stejný princip, a sice zobrazování sumarizovaných dat pro různé měnitelné pohledy. Následující obrázek představuje stejnou kontingenční tabulku, ale s vyměněnými prvky ve sloupcích a řádcích přičemž agregace dat je nastavena na úroveň okresu místo kraje (Obrázek 51).

113


Martin Horký

Obrázek 51: Jiné zobrazení stejné kontingenční tabulky datového skladu v MS Office Excel 2007 (vlastní export) Pomocí kontingenční tabulky lze vytvářet libovolné reporty podle potřeb. Pro použití kontingenční tabulky není nutné mít data uložená v datovém skladu. Podklady lze připravit v neagregované podobě do tabulky Excelu. Program tabulku dokáže zpracovat, data sumarizovat a nabídnout příslušné hodnoty dimenzí k určení prvků sloupců a řádků.

5.2.6.2 MS SQL Server 2005 Prostředí analytických služeb MS SQL Serveru 2005 sice nabízí poměrně dobrý reportovací nástroj, pomocí kterého lze ověřit správnost funkčnosti datové kostky, nicméně tento nástroj je vhodný spíše pro tvůrce datového skladu než pro koncové uživatele. Na následujícím obrázku (Obrázek 52) je ukázka agregovaného výstupu propočtových ukazatelů „Celkové příjmy na 1 obyvatele“ a „Daňové příjmy na 1 obyvatele“ pro sledované municipality Jihomoravského kraje v roce 2004. Výstup je setříděn vzestupně podle hodnoty prvního ukazatele. Na první pohled je nápadná pozice Brna i z hlediska

114


Martin Horký

téměř dvojnásobného objemu daňových příjmů na jednoho obyvatele než jaký mají všechna ostatní sledovaná sídla.

Obrázek 52: Ukázka reportovacího nástroje v prostředí MS SQL Serveru 2005

5.2.6.3 Microsoft Office Excel 2007 Daleko zajímavější výstupy z analytických dat nabízí poslední verze programu MS Office Excel 2007, která má oproti předchozí verzi z roku 2003 značně vylepšené reportovací nástroje, změn dostálo i uživatelské prostředí. Následující obrázek (Obrázek 53) ilustruje tabulkové i grafické srovnání hodnoty ukazatele „Celkové příjmy na 1 obyvatele“ sídel nad 100 tisíc obyvatel, tedy měst Brno, Olomouc, Ostrava a Plzeň v letech 2001–2007.

115


Martin Horký

Obrázek 53: Ukázka grafických možností nástroje MS Office Excel 2007 Offline OLAP kostka Pomocí aplikace MS Excel lze také pracovat s offline OLAP kostkou. Offline OLAP kostku lze vygenerovat přímo z prostředí MS Office Excel pomocí průvodce, ve kterém se specifikuje rozsah dat, která mají být do souboru uložena. Nejprve je nutné vytvořit kontingenční tabulku připojenou na analytický server. Průvodce se spustí výběrem položky Offline OLAP v menu Options → OLAP Tools (verze Excel 2007). Offline kostku je možné vygenerovat také pomocí specializovaného nástroje CubeSlice50 (Obrázek 54), který umožňuje výběr dalších prvků analytických služeb jako jsou definice klíčových indikátorů KPI nebo akce.

50

www.cubeslice.com

116


Martin Horký

Obrázek 54: Aplikace CubeSlice verze 9 – dialogové okno pro výběr rozsahu dat offline kostky

5.2.6.4 ProClarity ProClarity je jedním z nejrozšířenějších analytických nástrojů Business Intelligence. Data umožňuje zobrazit v řadě grafických formátů. Vedle standardních typů, které jsou známé pro MS Office Excel, umí ProClarity např. rozpadový strom (poskytuje rychlý přehled o složení agregátů) nebo rozptylový graf (bodový graf pro sledování korelace dvou ukazatelů). Z analytických funkcí poskytuje ProClarity izolaci, eliminaci, filtraci a seřazení podle hodnot ukazatele nebo zvýraznění výjimek apod. Definované sestavy s vybranými ukazateli, dimenzemi a formáty zobrazení lze uložit do tzv. briefing book – komponenty pro sdílení připravených pohledů, jejímž prostřednictvím je možné připravit uživatelské reporty. Následující graf (Obrázek 55) představuje vývoj hodnoty ukazatele definovaného v prostředí ProClarity jako prostý poměr hodnoty ukazatele „Daňový příjem na 1 obyvatele“ k hodnotě ukazatele „Celkový příjem na 1 obyvatele“. Jedná se tedy o podíl daňových příjmů z celkových příjmů na 1 obyvatele.

117


Martin Horký

Obrázek 55: Ukázka prezentačních možností programu ProClarity

5.3 Závěry a doporučení Rozsah analyzovaných dat v pilotním projektu představuje pouze část metody finanční a majetkové analýzy. Nicméně datový model je možné rozšiřovat o další ukazatele a dimenze. Zejména se jedná o majetkovou část analýzy (rozvaha aktiv a pasiv), jejíž podklady je možné získat z databáze ARIS a zpracovat obdobným způsobem jako výkazy o hospodaření.

Kontrola správnosti výpočtů Vypočítané hodnoty ukazatelů v datovém skladu byly porovnávány s hodnotami, se kterými pracuje databázová aplikace FAMA 2005. Ukazatele v současné programové podpoře generuje program ArisDestiller, který parsuje výkazy o hospodaření ve formátu HTML. Vzájemnou komparací hodnot byla u všech ukazatelů realizovaných v pilotním projektu potvrzena správnost výpočtů – hodnoty ukazatelů v obou systémech jsou si rovny.

118


Martin Horký

5.3.1 Zajištění provozu BI řešení Pilotní projekt byl realizován na osobním počítači autora. V případě, že se tento projekt bude využívat pro analýzy, bude nutné řešit zajištění provozu datového skladu a dostupnost dat. Jedním z možných řešení by bylo pořízení počítače (serveru) s programovým vybavením MS SQL Server, který by byl fyzicky umístěn např. na akademické půdě VŠE v Praze a připojen k síti Internet. Na serveru by byla nainstalována databáze pro účely uložení transakčních dat. Analytické služby serveru by zajišťovaly i vývojové prostředí pro tvorbu datového skladu. Server by poskytoval data prostřednictvím sítě Internet oprávněným uživatelům, kteří by pak mohli pracovat s aktuálními údaji, případně i analyzovat podkladová data v transakčních systémech. Výhodou tohoto řešení je, že by datový sklad byl stále fyzicky k dispozici pracovníkům katedry a na jeho rozšiřování by mohli postupně pracovat studenti v rámci svých školních prací. Serverem by mohl být lepší osobní počítač odpovídající požadavkům na provoz MS SQL Serveru 2005. Vzhledem k tomu, že cena za licenci nástrojů pro provoz a vývoj datového skladu se pro verzi Developer Edition pohybuje v řádech tisíců korun, mohlo by pořízení celého serveru včetně programového vybavení vyjít do 40 tisíc korun.

5.3.2 Návrhy na rozšíření dimenzionálního modelu Model datového skladu je možné rozšiřovat o další ukazatele a dimenze tak, jak postupně budou vznášeny požadavky vyplývající z potřeb finanční a majetkové analýzy. Kromě již zmíněných majetkových ukazatelů by bylo možné rozsah údajů o hospodaření rozšířit např. o údaje o bytové výstavbě, objem vyplácených sociálních dávek na dané úrovni agregace nebo také o odvětvové členění rozpočtové skladby.

5.3.2.1 Odvětvové členění rozpočtové skladby Pro analýzu hospodaření municipalit je často klíčové identifikovat, proč se daná obec v tomto či jiném ukazateli liší od standardu (např. průměru) v dané kategorii. Metoda

119


Martin Horký

FAMA pracuje s ukazateli, které jsou definovány na základě druhového členění rozpočtové skladby. Pokud by údaje o hospodaření byly doplněny o informaci, na jaký účel byla příslušná částka vynaložena nebo naopak získána, mohlo by to pomoci spolu s dalšími doplňujícími daty identifikovat příčiny rozdílů v hospodaření municipalit. Takovýto pohled na data odpovídá odvětvovému (funkčnímu) členění rozpočtové skladby, i když je jeho vypovídací hodnota omezena51. Tato data jsou součástí výkazu č. 40 databáze ARIS. Každá položka výkazu (ve smyslu druhového členění) je totiž doplněna číslem paragrafu (ve smyslu odvětvového členění). Datový model by se tedy mohl rozšířit o další faktovou tabulku, která by obsahovala dodatečné detailní informace vztahující se ke každé jednotlivé položce rozpočtové skladby.

5.3.2.2 Dynamická kategorizace ukazatelů V rámci pilotního projektu byly implementovány pouze vybrané finanční ukazatele, na které lze nahlížet pomocí čtveřice statických pevně určených dimenzí: územní klasifikace, úrovně řízení, velikostní skupiny a roky. Z hlediska pokročilých technik analýz hospodaření municipalit je ale často žádoucí kategorizovat obce na základě vypočtených hodnot ukazatelů v porovnání s ostatními obcemi v rámci sledované skupiny. Dimenzionální model by se mohl rozšířit např. o dimenzi „Kategorie míry soběstačnosti“, která by dynamicky rozlišovala municipality na základě vypočtených hodnot ukazatelů. Kategorizace by mohla probíhat na principu procentního porovnání s průměrem v dané sledované skupině (okres, kraj). Prvky dimenze by pak mohly být následující:

Velmi nízká míra soběstačnosti (hodnota o 101 a více % menší než průměr)

Podprůměrná míra soběstačnosti (hodnota o 20–100 % menší než průměr)

Průměrná míra soběstačnosti (tolerance ±20 % od hodnoty průměru)

Nadprůměrná míra soběstačnosti (hodnota o 20–100 % větší než průměr)

Vysoká míra soběstačnosti (hodnota o 101 a více % větší než průměr)

51

Není např. možné rozlišit jaké statky se za dané prostředky nakoupilo, zda to byla investice do infrastruktury nebo vybudování dětského hřiště.

120


Martin Horký

5.3.3 Data mining Účelem této kapitoly není popisovat možnosti technik data miningu, ale jen nastínit způsob, jakým by bylo možné data mining využít pro analýzu hospodaření obcí. Municipality by byly na základě hodnot daných ukazatelů pro každý rok zařazeny do kategorie, tedy prováděl by se jakýsi roční rating. Mezi kritérii, na základě kterých by se rating prováděl, by mohl být např. objem nevratných příjmů na obyvatele, počet podnikatelských subjektů na obyvatele, počet zdravotních ordinací na obyvatele, počet míst ve školních zařízeních na obyvatele a další ukazatele, které vypovídají nebo by mohly mít určitou vypovídající hodnotu o růstovém potenciálu daného sídla ve smyslu zvyšování kvality a rozsahu služeb, pracovního uplatnění atd. Rating by tedy vyjadřoval jakousi míru „zdraví“ daného sídla pomocí definované stupnice např. pomocí písmen A–G (obdobně jako u finančního ratingu), kde A bude nejlépe hodnocené město a stupeň G bude představovat sídlo s nejhoršími hodnotami sledovaných ukazatelů. Takto definovaný rating by byl zpracován pro každou municipalitu a každý sledovaný rok. Každá municipalita by tedy získala řadu stupňů, např. „E D C A A B A A B“, na základě kterých by bylo možné pomocí tzv. segmentace identifikovat města, která mohou mít stejné rozvojové problémy, jinými slovy která mají stejný nebo obdobný průběh ratingu. Na ty by se pak zaměřila pozornost při detailní analýze, ve které by se sledovaly hodnoty jednotlivých ukazatelů. Výsledkem segmentační analýzy by pak mohla být zobecněná znalost o příčinách rozvojových problémů těchto sídel.

121


Martin Horký

Kapitola 6 Zhodnocení Jedním z důležitých cílů této práce je zhodnotit současnou programovou podporou realizovanou v prostředí MS Office Access a porovnat ji s nástroji Business Intelligence. Výhody a nevýhody obou zmíněných přístupů jsou zpracovány formou SWOT analýzy. V závěru kapitoly jsou obě řešení porovnána v přehledné tabulce na základě několika klíčových oblastí, jako jsou možnosti datové integrace, prezentační a analytické nástroje nebo technologická náročnost implementace a provozu řešení.

6.1 SWOT analýza SWOT analýza obou přístupů programové podpory metody FAMA je zaměřena nejen na technologickou stránku řešení, ale také na otázky zajištění provozu nebo odborné nároky na personální zabezpečení obsluhy aplikací.

6.1.1 Současná programová podpora Silné stránky

Znalost uživatelského prostředí – aplikace Access patří mezi standardní Windows aplikace, jejíž prostředí je dobře známé běžným uživatelům.

Slabé stránky

Proprietární řešení aplikací Prognóza a ArisDestiller – aplikace jsou napsány v jazyce C++, jejich struktura, postupy transformace dat, definice agregačních funkcí apod. jsou proprietárním řešením, které jsou jednak skryté díky kompilaci programů do binárního kódu a jednak struktura algoritmů nemusí být jinému programátorovi zřejmá.

Omezené možnosti analýz – aplikace FAMA 2005 nebo Prognóza nabízí pouze předem definované tabulkové či grafické výstupy. Není možné vytvářet konsolidované datové sestavy, např. agregace vybraného ukazatele pro daný vzorek obcí nebo zadávat neúplně specifikované požadavky na analýzu.

122


Martin Horký

Omezené možnosti práce daty – v prostředí MS Access je práce s daty pocházejícími z různých systémů v různých formátech velmi limitována; omezená je i následná práce s nimi – transformace, výpočty apod. Prostředí nedisponuje robustními nástroji, které tento proces usnadní, a integrace se musí řešit jinými způsoby (pomocné tabulky, agregace a kontroly realizovat pomocí složitých SQL dotazů apod.).

Nedostatečné nástroje pro kontrolu kvality dat – žádná možnost řídit chybové stavy při importu dat jako je tomu v integračních službách MS SQL Serveru, omezené možnosti kontroly chybějících údajů (prázdné řádky, NULL hodnoty), kontroly datových typů (čísla vs. řetězce).

Možnosti a příležitosti technologie

Databáze Access je vhodná jako transakční systém – prostředí MS Access je vhodné spíše pro sběr a uchovávání dat zejména z terénního sběru. Stávající formulář pro terénní sběr by se mohl rozšířit o další ukazatele a používat pro terénní pracovníky. Databáze by pak sloužila jako jeden ze zdrojů pro datový sklad.

Nebezpečí a možná ohrožení

Programový kód aplikací ArisDestiller a Prognóza zná jen jejich autor – pouze autor těchto aplikací dokáže kvalifikovaně změnit funkcionalitu aplikací; v případě, že nebude k dispozici, bude časově a personálně obtížné aplikace modifikovat.

Chybovost v datech – výsledky analýz mohou být determinovány úrovní nekvality dat. ArisDestiller např. umožňuje chybný import výkazů ARIS v jednotkách korun, zatímco hodnoty ve výkazu jsou uvedeny v tisících. Další problematickou částí mohou být údaje získané během terénního sběru studenty, kteří jsou málo motivováni spolehlivě a pravdivě data zpracovat.

6.1.2 Řešení pomocí nástrojů BI Silné stránky

Robustní analytické nástroje – technologie BI jsou vhodné pro analytické operace jako jsou agregace podle daných kritérií, analýzy dat z různých hledisek, modelování, trendové analýzy atd.

123


Martin Horký

Široké možnosti zpracování data z různých zdrojů – možnost pracovat s daty z různých systémů (soubory, databáze, webové služby), různých formátů (Excel, XML, textové soubory) a datových typů.

Použití standardů – techniky a programové prostředí nástrojů BI jsou de facto oborovými standardy (např. integrační služby firmy Microsoft) a mezi specialisty poměrně dobře známé.

Silná kontrola kvality dat – možnost řídit chybové stavy a provést definované operace (uložení logu, zaslání emailu), implicitní kontrola datových typů mezi zdrojovým a cílovým úložištěm.

Slabé stránky

Nutný specialista pro tvorbu datového skladu – pro etapy návrhu a realizace datového skladu je nutný odborník na oblast BI a datové integrace.

Poměrně vysoké investice pro pořízení programového vybavení – licence MS SQL Serveru, ProClarity nebo Office Excel představují nezanedbatelný výdaj52.

Možnosti a příležitosti technologie

Robustní integrační nástroje pro zpracování různorodých dat – technologie BI umožňují snadno a spolehlivě pracovat s daty z různých systémů (soubory, databáze, webové služby), v různých formátech a datových typech. Datový sklad je možné dále rozšiřovat o další ukazatele, které jsou získány z různých zdrojů.

Nebezpeční a možná ohrožení

Nedostupnost analytických dat – v případě nevhodně zvolené architektury ICT pro přístup k analytickým datům, např. když jsou data distribuována pouze formou offline OLAP kostky, může dojít k nedostupnosti údajů. Je proto důležité pozornost zaměřit i na způsob poskytování přístupu k analytickým datům.

52

SQL Server lze nicméně pořídit i ve verzi Developer Edition za již příznivých finančních podmínek v řádech jednotek tisíc korun.

124


Martin Horký

6.2 Srovnání obou přístupů Následující přehled obsahuje srovnání obou přístupů programové podpory metody FAMA v několika klíčových oblastech (Tabulka 24). Současnou programovou podporou (levý sloupec tabulky) se rozumí trojice aplikací, tedy databázová aplikace FAMA 2005 v prostředí MS Access, program Prognóza pro zobrazování dat a prezentaci jednoduchých analytických sestav a nakonec aplikace ArisDestiller, která zpracovává výkazy ve formátu HTML a ukládá do databáze. Řešení pomocí nástrojů Business Intelligence představuje především programové nástroje použité způsobem jako v pilotním projektu. Tabulka 24: Srovnání současné programové podpory a řešení pomocí nástrojů BI Oblast

Současná programová podpora Zdrojová data a data určena pro analýzy jsou totožná a uložena v jedné databázi.

Architektura řešení

V daném řešení se rozlišují pouze data podle významu jednotlivých tabulek v databázi.

Řešení pomocí nástrojů BI Oblast zdrojových dat a analytických dat je oddělena. Každá oblast se řeší samostatně. Analytická data představují výběr nebo agregaci zdrojových dat často transformovaných do jiné podoby.

Omezené

Analytické možnosti (tvorba sestav, tabulkových a grafických výstupů, analytické nástroje apod.)

Možnost dodatečných změn funkcionality

Značné

Pro prezentační vrstvu se používají proprietární řešení (program Prognóza). Analytik nemůže měnit podobu výstupní sestavy – výběr ukazatelů, kategorií a vzájemné uspořádání je předem určeno programátorem.

Prezentační vrstva je od datové oddělena. Možnosti variability u aplikací BI jsou obecně velké. Aplikací, se kterými lze údaje v datovém skladu analyzovat, je celá řada.

Omezená

Poměrně dobrá

ArisDestiller a Prognóza jsou v podstatě proprietární aplikace, jejíž architekturu nejlépe zná jen autor. Bez podrobné dokumentace je změna funkčnosti náročná.

Komponenty BI představují standardní techniky, které jsou běžně v této oblasti používané. Změna funkcionality je tedy poměrně nenáročná.

125


Omezená

Značné

Prostředí MS Access nedisponuje robustními nástroji pro datovou transformaci, nicméně dovoluje přistupovat k různým formátům.

Technologie BI je založena na datové integraci (transformace, agregace, migrace, replikace, kontrola kvality dat)

Omezená

Značné

V současné podobě řešení je replikován celý soubor bez používání uživatelských práv nebo zamykání tabulek, i když to technologie umožňuje.

Část s transakčními daty v databázi je oddělena od části analytické (data v OLAP kostce). Přístup k OLAP službám lze řídit pomocí přístupových práv např. v rámci SQL Serveru, ke kterému lze přistupovat přes Internet.

Datová integrace (možnosti práce s různými typy dat z různých produkčních systémů)

Řízení přístupu k datům

Martin Horký

Zdrojová data a analytická nejsou oddělena (nerozlišují se)

Z uvedeného přehledu jednoznačně vyplývá, že současné řešení programové podpory metody FAMA není vhodné a existuje lepší alternativa v podobě technologie Business Intelligence. Základním problémem současného řešení je především to, že prostředí MS Access je používáno jak pro uložení zdrojových dat, tak pro následnou analytickou práci, pro kterou ale už není vhodný. Tento problém se autoři snažili vyřešit implementací speciální analytické aplikace Prognóza, která využívá data uložená v souborech MS Access a prezentuje je. Aplikace však umožňuje analyzovat data jen pomocí předem definovaných pohledů bez možnosti jejich změny, a tak logicky vznikají další uživatelské požadavky na úpravu funkčnosti nebo grafické prezentace ukazatelů.

6.2.1 Shrnutí Rozhodnutí, která technologie je vhodnější, není ani tak o porovnávání obou řešení jako o rozhodnutí o způsobu použití. MS Access může docela dobře plnit svoji funkci jako transakční systém, který slouží pro uložení dat z terénního sběru jako je tomu dosud. Používá normalizovaný datový model vhodný pro tento typ dat a nabízí poměrně široké možnosti připravit vhodné uživatelské

126


Martin Horký

prostředí pro zadávání a editaci dat. Navíc jsou údaje databáze možné jednoduše replikovat. Nástroje BI by se naopak měly využívat pro integraci všech uvažovaných zdrojových systémů pro použití v datovém skladu. Využila by se nabídka robustních nástrojů pro datové transformace, kontrolu datového toku a především denormalizovaný model datového skladu, který je vhodný pro agregovaná data a provádění náročných analytických výpočtů. Třetím pilířem řešení by byla aplikace specializovaná pouze na analýzu a prohlížení hodnot ukazatelů v datovém skladu, v jednodušším případě např. MS Office Excel, pro složitější dotazy pak např. ProClarity.

127


Martin Horký

Kapitola 7 Závěr Cíl diplomové práce realizovat pilotní projekt pro podporu finanční a majetkové analýzy pomocí nástrojů Business Inteligence se podařilo zcela naplnit. Pro vybraný rozsah finančních ukazatelů byl navržen model datového skladu, implementovány integrační úlohy pro import a transformaci zdrojových dat a vytvořena analytická OLAP kostka. Jako prezentační vrstva bylo použito prostředí MS Office Excel 2007 a aplikace ProClarity, ve kterých byly namodelovány ukázkové výstupy. Hlavní přínos pilotního projektu spočívá zejména v práci se zdrojovými daty ve formátu XML. V prostředí integračních služeb MS SQL Serveru 2005 byly implementovány transformační úlohy pro XML výkazy ze stejného produkčního systému ARIS, ze kterého jsou také HTML podklady zpracovávané programem ArisDestiller. Využití formátu XML přináší řadu výhod, mezi jinými především jednoznačně strukturovaný a identifikovatelný obsah a s tím spojenou eliminaci chyb způsobených během transformací. Jedním z dílčích přínosů této práce je také zhodnocení dosavadního způsobu získávání dat a použité nástroje pro analýzu a jejich porovnání s alternativním řešení pomocí nástrojů Business Intelligence. Současná programová podpora metody FAMA nástroji MS Access má podstatná omezení pro analytickou práci. Nedostatky by se daly rozdělit do dvou skupin. První skupinou jsou nedostatky, které jsou dány způsobem využít technologií tedy naprogramovanou funkčností. Mezi ně patří např. absence metadat nebo nemožnost předefinovat vzorce pro výpočet ukazatelů. Druhá skupina představuje naopak nedostatky, které jsou dány samotnými technologickými omezeními. Mezi ně patří např. absence analytických nástrojů pro práci s daty: automatizované agregované pohledy na data podle vybraných hledisek a nedostačující možnosti prezentační vrstvy. Na základě popsaných omezení a nedostatků lze soudit, že prostředí MS Access je pro analytickou práci nevhodné. Jeho využití by bylo vhodnější spíše do oblasti produkčních dat, tedy pro podporu sběru podkladových údajů pro analýzy spolu s příslušnými metadaty. Analytické operace by se měly stát výhradně doménou specializovaných nástrojů založených na technikách Business Intelligence

128


Martin Horký

7.1 Využití závěrů této práce a doporučení Závěry této práce mohou být využity pro další rozšiřování programové podpory metody FAMA. Realizovaný datový sklad se může rozšířit o další ukazatele a dimenze tak, aby zahrnoval celou metodu finanční a majetkové analýzy. Úlohy integračních služeb v pilotním projektu umí transformovat XML výkazy o hospodaření municipalit v celém jejich rozsahu, tedy včetně výdajové části a části financování. Změn by měla také dostát celá architektura programové podpory. Oddělit by se měla oblast produkčních systémů (výkazy ARIS, data z terénního sběru a údaje od externích subjektů jako je ČSÚ) a oblast analýz, která by měla data z produkčních systémů využívat, transformovat do vhodné struktury datového skladu a poskytnout prostředí pro robustní analytické nástroje. Programová podpora produkční části by mohla být realizována například i v prostředí MS Access s tím, že data by se mohla ukládat do jednotného systému databáze MS SQL Server. To by umožnilo přistupovat k datům prostřednictvím sítě Internet a sdílet data mezi více uživateli. V prostředí MS SQL Serveru by byla zároveň realizována podpora i druhé analytické části v podobě databáze pro přípravu zdrojových dat pro transformace do datového skladu a samotný model datového skladu.

129


Martin Horký

Kapitola 8 Přehled literatury a zdrojů 8.1 Literatura 1. Baštař, David: Finanční a majetková analýza města a obcí, diplomová práce. VŠE Praha, 2006. 2. Berka, Petr: Dobývání znalostí z databází. Academia Praha 2003. ISBN: 80-2001062-9 3. Dörfl, Jakub: Implementace Business Intelligence v malé firmě a praktické ověření v personální agentuře, diplomová práce. VŠE Praha 2006. 4. Fišerová Adéla: Komparace socioekonomické úrovně vybraných obcí, diplomová práce. VŠE Praha, 2004. 5. Hájek, Vojtěch: Implementace Business Intelligence v malé a střední firmě – JOLLY Trading s.r.o., diplomová práce. VŠE Praha, 2006. 6. Halásek, Pilný, Tománek: Určování bonity obcí. VŠB TU Ostrava, 2002. 7. Halouzka, Milerski: článek ke konferenci v Karpaczu. Polsko, 2006. 8. Inmon, W. H., What is a Data Warehouse (3. vydání). John Wiley & Sons, 2002. 9. Kimball, R., Ross, M.: The Data Warehouse Toolkit – second edition. Wiley Computer Publishing, New York, 2002. ISBN 0-471-20024-7. 10. Lacko, Luboslav: Datové sklady, analýza OLAP a dolování dat. Computer Press, Brno 2003. ISBN: 80-7226-969-0 11. Lacko, Luboslav: Business Intelligence v SQL Serveru 2005. Computer Press, Brno 2006. ISBN: 80-251-11110-5 12. Novotný, Pour, Slánský: Business Intelligence: Jak využít znalostí ve vašich datech. Grada 2004 13. Ponniah, Paulraj: Data Warehousing Fundamentals, John Wiley & Sons, 2001. ISBN: 0-471-41254-6. 14. Pirkl, David: Datové sklady a BI aplikace. Přednášky MFF UK Praha, říjen 2004. 15. Princl, Pavel: Návod k programu ARISDestiller, dokumentace k aplikaci FAMA 2005. VŠE Praha 2007.

130


Martin Horký

16. Toth, Petr a kolektiv: Financování obcí – sociálně ekonomický rozvoj systému, výstup z projektu GAČR 402/03/1235. Oekonomica, Praha 2005. ISBN: 80-2450957-1 17. Thomsen, E., OLAP Solutuions, Building Multidimensional Information Systems, 2. vydání, 2002. 18. Trunečková, Lucie: Finanční a majetková analýza města Berouna, diplomová práce. VŠE Praha, 2006 19. Vostrý, Karel: Programová podpora finanční a majetkové analýzy, diplomová práce. VŠE Praha 2003

8.2 Zdroje dostupné on-line na Internetu 1. An Introduction to SQL Server 2005 Integration Services, Microsoft, dostupné online na adrese: www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx, citováno 3.5.2008. 2. ČSÚ, Přehled vybraných číselníků z katalogu METIS ČSÚ, dostupné on-line na adrese: http://dw.czso.cz/pls/metis/TUCC_N.KATAL?obr=1440900, citováno 6.6.2008. 3. Microsoft Developer Network, on-line nápověda k produktu MS SQL Server 2005, http://msdn2.microsoft.com/en-us/library/. 4. ProClarity, popis produktu, ABMICO – Business Intelligence Solutions, dostupné on-line na adrese: http://www.ambica.cz/content/view/40/144/, citováno 12.6.2008. 5. Dokumentace poslední verze schéma arisTypes.xsd 1.0.1 (z roku 2005), dostupné on-line na adrese: wwwinfo.mfcr.cz/aris/xml_doc/schemas/v_1.0.1/doc/index.html. 6. Česká společnost pro systémovou integraci, terminologický slovík, dostupné online na adrese: www.cssi.cz. 7. Oficilní prezentace produktů Microsoft (SQL Server, Offce Excel), dostupné online na adrese: www.microsoft.cz. 8. The OLAP Report, dostupné on-line na adrese: www.olapreport.com.

131


Martin Horký

Kapitola 9 Terminologický slovník 9.1 Přejaté termíny ze slovníku ČSSI (Česká společnost pro systémovou integraci, www.cssi.cz)

Termín

Význam

BI

► Business Intelligence

BI řešení

BI řešení je konkrétní kombinace BI produktů a dalších pomocných aplikací. Zahrnuje také implementovaný ► datový sklad, nastavené ► datové pumpy a připravené ► multidimenzionální databáze.

Business Intelligence (BI)

Business Intelligence (BI) je sada postupů, procesů a technologií, jejímž cílem je účinně a účelně podporovat rozhodovací procesy ve firmě. Představuje komplex aplikací IS/ICT, které podporují analytické a plánovací činnosti podniků a organizací a jsou postaveny na specifických, tzv. ► OLAP (On-Line Analytical Processing) technologiích a jejich modifikacích.

Databáze datového Databáze datového skladu představuje množinu dat, která je uložena v relační struktuře. Data v databázi musí být konzistentní, skladu tzn. je získán vždy stejný výsledek při kladení dvou stejných dotazů do databáze. Databáze datového skladu musí umožnit kombinaci hledisek (► dimenzí), podle kterých uživatelé sledují důležité údaje (► ukazatele). Proto jsou tabulky v databázi datového skladu uspořádány v ► dimenzionálním modelu „Hvězda (► Star schéma)“, „Sněhová vločka (► Snowflake schéma)“. Data staging area (DSA)

Komponenta BI řešení, která slouží k dočasnému uložení dat extrahovaných ze zdrojových systémů. Data v DSA odpovídají zdrojové předloze a nejsou transformována.

Datové tržiště

Podmnožina ► datového skladu adresující potřeby specifických předmětných oblastí podniku nebo uspokojující potřeby jednotlivých částí organizace.

Datový sklad (DS)

Kopie transakčních dat speciálně strukturovaných pro dotazování a reportování. (Ralph Kimball). Předmětně orientovaná, integrovaná,

132


Martin Horký

časově proměnná, nevolatilní kolekce dat umožňující rozsáhlé analýzy. (Bill Inmon). Dimenze

Dimenze je hledisko, podle kterého jsou sledovány ► ukazatele. Dimenze je tvořena jednou nebo více tabulkami dimenzí (číselníky). Dimenze může mít různý počet ► úrovní a ► hierarchií.

Dimenzionální model

Dimenzionální model je speciální způsob uspořádání tabulek a jejich vazeb. V tomto modelu jsou tabulky rozděleny na dvě skupiny (tabulky faktů, tabulky dimenzí). Tabulky faktů obsahují záznamy s numerickými údaji pro jeden nebo více ukazatelů. Tabulky dimenzí obsahují záznamy popisující jednu nebo více reálných entit. V dimenzionálním modelu se vždy na jednu tabulku ukazatelů váže více tabulek dimenzí. Jsou dva základní typy dimenzionálních modelů. Prvním je uspořádání ve stylu hvězdy (Star schéma), druhým je uspořádání ve stylu sněhové vločky (Snowflake schéma).

Dolování dat

Je specifickým analytickým nástrojem ► Business Intelligence. Jeho podstatou je využití co největšího počtu různých technik k dosažení komplexního pohledu na analyzovaná data, protože s rostoucí složitostí datových struktur se popis jejich charakteristik stává stále obtížnější. Každá z technik dolování dat nabízí odlišný pohled na data, což zvyšuje pravděpodobnost, že budou objeveny nové vlastnosti a trendy v datech. Techniky dolování dat se od sebe rovněž odlišují zapojením lidského faktoru do analýz – některé techniky využívají interaktivního přístupu, zatímco jiné tento přístup nepředpokládají.

(data mining)

Dolování dat je založeno na vědních oborech jako jsou statistika, strojové učení, neuronové sítě, genetické algoritmy, rozhodovací stromy, atd. V praxi umožňuje například vyhledávání potencionálních klientů, odhalování podvodů, atd. Drill-down, drillup, slice&dice

Drill-down a drill up jsou operace, které umožňují prohlížet hodnoty libovolného ukazatele detailněji, tzn. z nižší úrovně ► dimenzí nebo obecněji, tzn. na vyšší úrovně dimenzí. Drill-down je přechod na nižší úroveň. Drill-up je přechod na vyšší úrovně. Operace slice&dice umožňuje kombinovat různé dimenze a ukazatele. Všechny tyto operace jsou podstatou nástrojů pro koncové uživatele (► Klientské BI nástroje).

ETL

ETL jsou nástroje, které umožňují propojit různé databáze a přenášet data mezi těmito databázemi. Přitom se provádějí operace jako jsou transformace datových typů a formátů, oprava dat, výpočet agregací, doplňování primárních klíčů, atd. Tyto nástroje umožňují vytvořit ► datové pumpy (data stages) tzn. definovat

(Extraction Transformation Load)

133


Martin Horký

zdrojové databáze, cílové databáze a nastavit transformační proces. Granularita

Granularita (zrnitost) je podrobnost uložených hodnot ► ukazatelů v ► tabulce faktů. Velká granularita znamená, že hodnoty ukazatelů v tabulce faktů jsou velmi podrobné (například odpovídají 1:1 hodnotám v produkčních databázích). Malá granularita znamená, že hodnoty ukazatelů v tabulce faktů jsou málo podrobné (tzn. při plnění této tabulky daty z produkčních databází se ukládají pouze agregace). Tabulka faktů obvykle obsahuje velmi podrobné hodnoty ukazatelů.

Hierarchie v dimenzi

Hierarchie v dimenzi je jeden možný způsob kombinace úrovní. V dimenzi je obvykle možné vytvořit více hierarchií, úrovně různě kombinovat, měnit jejich počet a pořadí. Například v dimenzi DATUM je možné vytvořit hierarchii rok-pololetí-měsíc-den nebo hierarchii rok-týden-den.

Klientské BI nástroje

Nástroje pro koncové uživatele business intelligence (E/U Applications) jsou aplikace, které umožňují zobrazovat výsledky výpočtů nad daty, filtrovat hodnoty, zvýrazňovat extrémní hodnoty, provádět operace � drill-down, drill-up, slice&dice, provádět jednoduché statistické výpočty, exportovat výsledky do internetových prohlížečů nebo tabulkových kalkulátorů. Tyto nástroje mohou být připojeny buď k ► multidimenzionálním databázím nebo přímo k ► datovému skladu. Nástroje pro koncové uživatele mohou být rozděleny na reportovací a analytické.

Metadata

Metadata jsou informace o datech v produkčních databázích tj. názvy tabulek, názvy atributů, datové typy, primární klíče, vazby, komentáře, atd.

Multidimenzionální Multidimenzionální databáze je množina data uložená speciálním způsobem. V této databázi jsou hodnoty ukazatelů přístupné pro databáze každou kombinaci prvků v dimenzích. Pro většinu kombinací prvků v ► dimenzích neexistují hodnoty ► ukazatelů, proto se tato databáze podobá řídké matici (sparse matrix). V multidimenzionální databázi jsou uloženy také agregace hodnot ukazatelů pro vyšší ► úrovně dimenzí. Multidimenzionální databáze je výrazně větší než klasická relační databáze, umožňuje ale rychlejší výpočty, především v důsledku předvypočítaných agregací. OLAP (On-Line Analytical Processing)

OLAP (On-Line Analytical Processing) je informační technologie založená především na koncepci ► multidimenzionálních databází. OLAP je základní technologie, na níž jsou založeny aplikace � business intelligence. Jejím hlavním principem je několikadimenzionální tabulka umožňující rychle a pružně měnit

134


Martin Horký

jednotlivé ► dimenze a měnit tak pohledy uživatele na modelovanou ekonomickou realitu. Technologickou implementací OLAP principů jsou MOLAP (Multidimensional OLAP) založená na zcela specializovaných databázích, ROLAP (Relational OLAP) využívající technologii relačních databází a HOLAP (Hybrid OLAP) kombinující výhody obou dvou předchozích technologií. OLAP server

OLAP server je technologie pro uložení a správu dat v multidimenzionálních databázích, pro jejich vytváření a plnění, obsahuje metody přístupu k datům, metody provádění výpočtů nad daty.

OLTP

OLTP znamená On Line Transaction Processing, tedy zpracování (datových) transakcí v reálném čase. Je to způsob práce většiny primárních informačních systémů.

Produkční databáze

Produkční databáze jsou veškeré databáze uchovávající data, které vytváří a spravují podnikové informační systémy. Data mohou být v různých formátech a uložena v různých typech databázových systémů atd.

Prvek úrovně v dimenzi

Prvek úrovně v dimenzi je konkrétní hodnota dané úrovně v ► dimenzi. Například prvkem úrovně sortimentní skupina v dimenzi sortiment může být skupina „obuv“.

Prvek v dimenzi

Prvek v dimenzi (popř. dimenzionální prvek) je konkrétní hodnota nejnižší úrovně, kterou je možné v ► dimenzi vymezit. Pojem prvek v dimenzi se používá v souvislosti s multidimenzionálními kostkami. Prvkem v dimenzi sortiment může být například konkrétní sortimentní položka „kožená bunda“ s evidenčním číslem 8593426723465.

Snowflake schéma

Sněhová vločka (Snowflake) je typ ► dimenzionálního modelu tvořeného více normalizovanými a nenormalizovanými tabulkami dimenzí. Normalizovaná tabulka popisuje pouze jednu reálnou entitu. Normalizované jsou například tabulky SORTIMENTNÍ POLOŽKA, SORTIMENTNÍ SKUPINA, DODAVATEL, které popisují entity sortimentní položka, sortimentní skupina a dodavatel. Tyto tabulky mohou tvořit � dimenze pojmenované jako dodavatelé a sortiment. Pokud dimenzionální model obsahuje jak normalizované tak i nenormalizované tabulky, potom je typem sněhová vločka.

Star schéma

Hvězda (Star) je typ ► dimenzionálního modelu tvořeného pouze nenormalizovanými tabulkami dimenzí. Nenormalizovaná tabulka popisuje více reálných entit. Například tabulka SORTIMENT, obsahující atributy název sortimentní položky, název sortimentní skupiny, název dodavatele, popisuje entity sortimentní položka,

135


Martin Horký

sortimentní skupina, dodavatel. Nad touto tabulkou je možné vytvořit dvě ► dimenze pojmenované jako dodavatelé a sortiment. Ukazatel

Ukazatel je sledovaný údaj v organizaci, například objem prodeje, počet reklamací, obrat atd.

Úroveň v dimenzi

Úroveň (Level) v ► dimenzi je stupeň podrobnosti, podle které je možné ukazatele sledovat. Například dimenze SORTIMENT má úrovně sortimentní nadskupina, sortimentní podskupina a sortimentní položka. Sortimentní skupina je nejobecnější úroveň, sortimentní položka je nejdetailnější úroveň.

9.2 Nové a nově vymezené termíny Převzato z DP Hájek, 2006 Termín

Význam

Agregovaná data

Vypočítaná a uložená data pro vyšší prvky hierarchie dimenzí. Značně urychlují přístup k těmto údajům. (www.adastra.cz53)

Data Mart

► Datové tržiště

Datová kostka

Jde o zobecnělé označení pro uspořádání dat v multidimenzionální podobě ► multidimenzionální databáze. Kostka představuje určitou problematiku (např. podnikový prodej), na niž lze pohlížet z mnoha stran. (Zdroj: www.ormbrno.cz.54)

Datová pumpa

Část ► BI řešení umožňující přenos a transformaci dat ze zdrojových systémů do ► databáze datového skladu. Datové pumpy se vytváří pomocí ► ETL nástrojů.

Datové tržiště

Podmnožina datového skladu adresující potřeby specifických předmětných oblastí podniku nebo uspokojující potřeby jednotlivých částí organizace. (Zdroj: Novotný, Pour, Slánský, 2005, str. 250.)

DSA

53 54

–

Data Jde o místo v architektuře ► datového skladu, kde se data ze

http://www.adastra.cz/default.asp?menu=technology/&page=slovnik_cz.html http://www.ormbrno.cz/slovnik.php

136


Staging Area

Martin Horký

zdrojových systémů zpracovávají a transformují do podoby dat definovaných dimenzionálním (cílovým) modelem. (Zdroj: Novotný, Pour, Slánský, 2005, str. 250.)

Tabulka dimenze

Tabulka v relační databázi dimenzionálního modelu obsahující popis prvků dimenzí. Její primární klíč je cizím klíčem do příslušných tabulek faktů. (Zdroj: www.adastra.cz.55)

Tabulka faktů

Tabulka v relační databázi dimenzionálního modelu, v které jsou uloženy hodnoty ukazatelů pro různé kombinace prvků dimenzí. (Zdroj: www.adastra.cz.55)

55

http://www.adastra.cz/default.asp?menu=technology/&page=slovnik_cz.html

137


Martin Horký

Kapitola 10 Přílohy 10.1 Obsah přiloženého CD

Původní databáze FAMA 2005, Prognóza a ArisDestiller (testovací data)

Reprezentativní výběr obcí (dokument DOC)

Zdrojová data pro datový sklad (XML výkaz 472 obcí)

Integrační úlohy SQL Serveru 2005

Software (SQL Server 2005 Data Mining Add-in Excel, CubeSlice)

Dokumentace XML schémat výkazů ARIS (HTML)

Paragrafy rozpočtové skladby (formát PDF)

Druhové třídění rozpočtové skladby (formát PDF)

10.2 Reprezentativní výběr obcí Analýza financování výkonu státní správy a samosprávy územních samosprávných celků, která poskytne relevantní množství dat pro přípravu nového zákona o rozpočtovém určení daní Typologie obcí a jejich výběr pro dotazníkové šetření – 2. verze Prof. RNDr. René Wokoun, CSc. (Národohospodářská fakulta VŠE, Středisko regionálních a správních věd) Charakteristika současného stavu: Informace a data dle zákona č. 243/2000 Sb., vstupující do stanovení podílu jednotlivých obcí na stanovených procentních částech celostátního hrubého výnosu daně z přidané hodnoty a daní z příjmů: 1) Kraj; 2) Okres; 3) Název obce; 4) Kód základní územní jednotky; 5) Počet obyvatel (k 1. 1. 2007); 6) Výměra katastrálních území obce (k 1. 1. 2007) [ha]; 7) Koeficienty postupných přechodů (Příloha č. 2 zákona č. 243/2000 Sb.); 8) Procentní podíl obce na výnosu daní podle § 4 odst. 1 písm. b) až f)zákona o rozpočtovém určení daní; 9) Počet zaměstnanců (k 1. 12. 2006);

138


Martin Horký

10) Procentní podíl obce na výnosu daně podle § 4 odst. 1 písm. i)zákona o rozpočtovém určení daní. Typologie obcí a jejich výběr pro dotazníkové šetření - výchozí body analýzy Města a obce jsou zařazeny do 14 velikostních kategorií dle členění v příloze č. 2 k zákonu č. 243/2000 Sb. Výchozí podklady pro analýzu počet obyvatel jednotlivých obcí, kategorizace obcí na 14 velikostních kategorií (platná v RUD do roku 2007), počet obyvatel. Mj. zjištění, na co byly prostředky vynakládány, v jakém rozsahu a zda jsou jejich hodnoty závislé na vybraných ukazatelích (počtu obyvatel, rozloze obce, nezaměstnanosti a dalších). Etapizace a metodika Dotazníkové šetření: Návrh výběru obcí bude na žádost SMO s touto organizací projednán. První etapa: Výběr obcí pro dotazníkové šetření. Metodika výběru obcí Z 6245 obcí byl vybrán dostatečně reprezentativní vzorek dle zvolených kritérií (územní, velikostní skupiny, funkční typologie, míra přenesené působnosti event. dle příslušnosti ke krajům). V základní analýze obdobně jako v dotazníkovém šetření jsou zastoupeny všechny kraje (NUTS III). Pro podrobnější šetření budou pro třetí fázi výzkumu vyčleněny města (kraje) Praha, Brno, Ostrava a Plzeň. Vybrány jsou všechny obce z rozšířenou působností. Výše uvedené obce a města jsou doplněny ještě dalšími obcemi. Při jejich výběru se přihlíželo zejména k tomu, aby: v celkovém souboru obcí bylo zachováno reprezentativní zastoupení všech velikostních kategorií správních obvodů, byl zachován celorepublikový poměr „příhraničních“ a „vnitrozemských“ obcí (příhraniční obce zde pro zjednodušení představovaly správní obvody obcí ležící na hranici ČR), byly zastoupeny i obce ležící v zázemí velkých měst (Prahy, Ostravy, Plzně a Brna), prostorové rozložení vybraných územních jednotek/správních obvodů bylo v podstatě rovnoměrné. Celkový vzorek oslovených obcí nebude větší než 500 obcí. Druhá etapa: Výběr obcí pro řízené rozhovory. Na základě našich zkušeností z obdobných výzkumů jsme přistoupili v této fázi ke zpřesnění výběru navržených obcí pro řízené rozhovory. Výchozí podmínky Počet okresů: 76 + Praha Počet obcí s rozšířenou působností (ORP): 205 + Praha

139


Martin Horký

Počet obcí s pověřeným obecním úřadem: 183 (mimo ORP); 388 (včetně ORP) Počet obcí: 6245 Výstupy Výběr do 500 obcí: Počet obcí s rozšířenou působností (ORP): všechny ORP, tj. 205 Počet obcí s pověřeným obecním úřadem: min. 100 Počet ostatních obcí: min. 100 Kriteria výběru Velikostní skupiny – zachovat 14 skupin Funkční typologie (návrh obytné, výrobní, smíšené – bude podrobně specifikováno) Zaměstnanost a nezaměstnanost Geografická poloha (vnitřní a periferní poloha, zázemí velkoměst apod.) Územní reprezentativnost Další kriteria

Výběr obcí pro dotazníkové šetření (podle stanovených kritérií) Při výběru obcí pro dotazníkové šetření byla respektována kriteria výběru včetně požadavku na zařazení všech obcí s rozšířenou působností (ORP; obce 3.typu). U jednotlivých kategorií jsou uvedena specifická výběrová kritéria. Nejdříve byla provedena inventarizace obcí s rozšířenou působností a pak byl proveden vývěr obcí s pověřeným obecním úřadem (OPOÚ; obce 2.typu). Při výběru obcí s pověřeným obecním úřadem (OPOÚ) byla aplikována následující kriteria: a) výběr cca 60% obcí z celkového počtu OPOÚ; b) výběr alespoň jednoho OPOÚ ze dvou sousedních okresů; c) zahrnout do výběru významná centra osídlení, s výraznou centrální funkcí a přirozeným spádovým územím; d) dodržení reprezentativnosti výběru (kvazireprezentativnosti) z pohledu: - polohy v rámci ČR (pohraničí, vnitřní periferie, exponované prostory v zázemí velkoměst, venkovská území s výraznou agrární činností apod.), - velikostních skupin, - funkční klasifikace (zúženo na základní typy – průmyslové, dopravní, lázeňské, obslužné apod.), - vytvořené a funkční přirozené spádové území, - na základě doporučení Svazu měst a obcí ČR byl mírně zvýšen počet zkoumaných nejmenších obcí v jednotlivých krajích, - do výběru byly zahrnuty obce, jež byly doporučeny na základě připomínkového řízení (např. senátorky V. Domšové, Sdružení místních samospráv). Vybrané obce s přeneseným výkonem státní správy, tj. všechny obce s rozšířenou působností, návrh na výběr obcí s pověřeným obecním úřadem (pro přehlednost jsou rovněž uvedeny všechny obce s pověřeným obecním úřadem) jsou uvedeny pro přehlednost po krajích. U ORP a OPOÚ jsou pro potřeby dalších analýz uvedeny odkazy na jejich internetové stránky.

140


Martin Horký

Jihočeský kraj (17 + 10 + 11 = 38) Obce s rozšířenou působností (počet 17): Blatná České Budějovice Český Krumlov Dačice Jindřichův Hradec Kaplice Milevsko Písek Prachatice Soběslav Strakonice Tábor Trhové Sviny Třeboň Týn nad Vltavou Vimperk Vodňany

Obce s pověřeným obecním úřadem (20): Bechyně České Velenice Hluboká nad Vltavou Horní Planá Lišov Mirotice Mirovice Mladá Vožice Netolice Nová Bystřice Nové Hrady Protivín Sezimovo Ústí Slavonice Suchdol nad Lužnicí Veselí nad Lužnicí Volary Volyně Vyšší Brod Zliv

Návrh na výběr OPOÚ (10): Bechyně, České Velenice, Hluboká nad Vltavou, Nové Hrady, Sezimovo Ústí, Slavonice, Veselí nad Lužnicí, Volary, Vyšší Brod, Zliv.

Ostatní obce – návrh (11): Horní Stropnice, Velešín, Křemže, Studená, Bernartice, Stachy, Jistebnice, Stožec, Želnava, Nákří, Černá v Pošumaví.

Jihomoravský kraj (21 + 8 + 18 = 47) Obce s rozšířenou působností (21): Blansko Boskovice Brno Břeclav Bučovice Hodonín Hustopeče Ivančice Kuřim Kyjov Mikulov Moravský Krumlov Pohořelice Rosice Šlapanice Slavkov u Brna Tišnov Veselí nad Moravou Vyškov Židlochovice Znojmo

Obce s pověřeným obecním úřadem (13): Adamov Bzenec Hrušovany nad Jevišovkou Ivanovice na Hané Klobouky u Brna Letovice Miroslav Rousínov Strážnice Velká nad Veličkou Velké Opatovice Vranov nad Dyjí Ždánice

Návrh na výběr OPOÚ (8): Adamov, Bzenec, Ivanovice na Hané, Letovice, Rousínov, Strážnice, Vranov nad Dyjí, Ždánice.

Ostatní obce – návrh (17): Bílovice nad Svitavou, Střelice (IČ:00282618), Těšany, Veverská Bítýška, Černá Hora, Lomnice, Drnholec, Velké Bílovice, Velká nad Veličkou, Mutěnice, Dambořice, Vracov, Svatobořice-Mistřín, Hodonice, Jevišovice, Miroslav, Skalka.

Karlovarský kraj (7 + 4 + 4 = 15) Obce s rozšířenou působností (7): Aš Cheb Karlovy Vary Kraslice Mariánské Lázně Ostrov Sokolov

Obce s pověřeným obecním úřadem (7): Horní Slavkov Chodov Kynšperk nad Ohří Loket Nejdek Toužim Žlutice

141


Martin Horký

Návrh na výběr OPOÚ (4): Chodov, Loket, Nejdek, Toužim.

Ostatní obce – návrh (4): Luby, Bochov, Pernink, Rotava.

Kraj Vysočina (15 + 10 + 14 = 39) Obce s rozšířenou působností (15): Bystřice nad Pernštejnem Havlíčkův Brod Humpolec Chotěboř Jihlava Moravské Budějovice Náměšť nad Oslavou Nové Město na Moravě Pacov Pelhřimov Světlá nad Sázavou Telč Třebíč Velké Meziříčí Žďár nad Sázavou

Obce s pověřeným obecním úřadem (11): Golčův Jeníkov Hrotovice Jaroměřice nad Rokytnou Jemnice Kamenice nad Lipou Ledeč nad Sázavou Počátky Polná Přibyslav Třešť Velká Bíteš

Návrh na výběr OPOÚ (10): Golčův Jeníkov, Jaroměřice nad Rokytnou, Jemnice, Ledeč nad Sázavou, Polná, Třešť, Velká Bíteš, Jiřice, Hrotovice, Počátky.

Ostatní obce – návrh (14): Bořetín, Dukovany, Jamné, Obyčtov, Rudolec, Zajíčkov, Jedlá, Bukov, Dobronín, Luka nad Jihlavou, Okřešice, Sobíňov, Štoky, Ujčov.

Královéhradecký kraj (15 + 10 + 8 = 33) Obce s rozšířenou působností (15): Broumov Dobruška Dvůr Králové nad Labem Hořice Hradec Králové Jaroměř Jičín Kostelec nad Orlicí Náchod Nová Paka Nové Město nad Metují Nový Bydžov Rychnov nad Kněžnou Trutnov Vrchlabí

Obce s pověřeným obecním úřadem(20): Červený Kostelec Česká Skalice Hostinné Hronov Chlumec nad Cidlinou Kopidlno Lázně Bělohrad Nechanice Opočno Police nad Metují Rokytnice v Orlických horách Smiřice Sobotka Svoboda nad Úpou Teplice nad Metují Třebechovice pod Orebem Týniště nad Orlicí Úpice Vamberk Žacléř

Návrh na výběr OPOÚ (10): Česká Skalice, Hostinné, Chlumec nad Cidlinou, Lázně Bělohrad, Opočno, Svoboda nad Úpou, Třebechovice pod Erebem, Úpice, Vamberk, Žacléř.

Ostatní obce – návrh (8): Běleč nad Orlicí, Velké Svatoňovice, Libňatov, Žernov, Přepychy, Adršpach, Chudenice – jde o typický „úlet“ WORDu (bohužel jsem to přehlédl) – jde o obec Chudeřice, Orlické Záhoří.

Liberecký kraj (10 + 6 + 9 = 25) Obce s rozšířenou působností (10):

142


Martin Horký

Česká Lípa Frýdlant Jablonec nad Nisou Jilemnice Liberec Nový Bor Semily Tanvald Turnov Železný Brod

Obce s pověřeným obecním úřadem (11): Český Dub Cvikov Doksy Hodkovice nad Mohelkou Hrádek nad Nisou Chrastava Jablonné v Podještědí Lomnice nad Popelkou Mimoň Nové Město pod Smrkem Rokytnice nad Jizerou

Návrh na výběr OPOÚ (6): Cvikov, Doksy, Hrádek nad Nisou, Lomnice nad Popelkou, Mimoň, Nové město pod Smrkem.

Ostatní obce – návrh (9): Višňová, Vyskeř, Mírová pod Kozákovem, Rynoltice, Příkrý, Harrachov, Jindřichovice pod Smrkem, Janův Důl, Karlovice (okres Semily).

Moravskoslezský kraj (22 + 4 + 8 = 34) Obce s rozšířenou působností (22): Bílovec Bohumín Bruntál Český Těšín Frenštát pod Radhoštěm Frýdek-Místek Frýdlant nad Ostravicí Havířov Hlučín Jablunkov Karviná Kopřivnice Kravaře Krnov Nový Jičín Odry Opava Orlová Ostrava Rýmařov Třinec Vítkov

Obce s pověřeným obecním úřadem (8): Fulnek Horní Benešov Město Albrechtice Osoblaha Příbor Studénka Vratimov Vrbno pod Pradědem

Návrh na výběr OPOÚ (4): Fulnek, Osoblaha, Studénka, Vrbno pod Pradědem.

Ostatní obce – návrh (8): Ludvíkov, Stará Ves nad Ondřejnicí, Pustá Polom, Hlubočec, Chotěbuz, Vražné, Dívčí Hrad, Vendryně.

Olomoucký kraj (13 + 4 + 7 = 24) Obce s rozšířenou působností (13): Hranice Jeseník Konice Lipník nad Bečvou Litovel Mohelnice Olomouc Prostějov Přerov Šternberk Šumperk Uničov Zábřeh

Obce s pověřeným obecním úřadem (7): Hanušovice Hlubočky Javorník Kojetín Moravský Beroun Němčice nad Hanou Zlaté Hory Návrh na výběr (4): Javorník, Kojetín, Moravský Beroun, Zlaté Hory.

Ostatní obce – návrh (7): Mikulovice, Velká Kraš, Lipová-lázně, Skorošice, Těšetice, Věžky, Víceměřice.

143


Martin Horký

Pardubický kraj (15 + 8 + 21 = 44) Obce s rozšířenou působností (15): Česká Třebová Hlinsko Holice Chrudim Králíky Lanškroun Litomyšl Moravská Třebová Pardubice Polička Přelouč Svitavy Ústí nad Orlicí Vysoké Mýto Žamberk

Obce s pověřeným obecním úřadem (11): Heřmanův Městec Choceň Chrast Chvaletice Jablonné nad Orlicí Jevíčko Lázně Bohdaneč Letohrad Nasavrky Skuteč Třemošnice

Návrh na výběr OPOÚ (9): Choceň, Chrast, Chvaletice, Jevíčko, Lázně Bohdaneč, Třemošnice, Heřmanův Městec, Nasavrky, Brněnec (IČ:00276464)..

Ostatní obce – návrh (21): České Libchavy, Vítějeves, Mladkov, Červená Voda, Morašice - IČ:00580678, Otradov, Čenkovice, Bučina, Dolní Dobrouč, Srch, Býšť, Choltice, Jamné nad Orlicí, Chroustovice, Řestoky, Orel, Prosetín, Přelovice, Pustina, Rohovládova Bělá, Zálší.

Plzeňský kraj (15 + 12 + 18 = 45) Obce s rozšířenou působností (15): Blovice Domažlice Horažďovice Horšovský Týn Klatovy Kralovice Nepomuk Nýřany Plzeň Přeštice Rokycany Stod Stříbro Sušice Tachov

Obce s pověřeným obecním úřadem (20): Bezdružice Bor Dobřany Holýšov Kašperské Hory Kdyně Manětín Město Touškov Nýrsko Planá Plánice Plasy Poběžovice Radnice Spálené Poříčí Staňkov Starý Plzenec Třemošná Všeruby Zbiroh

Návrh na výběr OPOÚ (12): Bor, Dobřany, Holešov- jde o město Holýšov, opět jsem přehlédl „opravu“ Wordu (IČ00253367), Kdyně, Manětín, Nýrsko, Planá, Plasy, Poběžovice, Radnice, Zbiroh, Staňkov.

Ostatní obce – návrh (18): Zvíkovec, Borovno, Srní, Osvračín, Nečtiny, Žinkovy, Břasy, Kout na Šumavě, Tymákov, Čilá, Dešenice, Dlouhá Ves, Hrádek – IČ 258725, Hradiště (okr. Plzeň-jih), Kladruby – IČ 00258822 (okres Rokycany), Kolínec, Hartmanice, Krsy.

Středočeský kraj (26 + 16 + 25 = 67) Obce s rozšířenou působností (26): Benešov Beroun Brandýs nad Labem-Stará Boleslav Čáslav Černošice Český Brod Dobříš Hořovice Kladno Kolín Kralupy nad Vltavou Kutná Hora Lysá nad Labem

144


Martin Horký

Mělník Mladá Boleslav Mnichovo Hradiště Neratovice Nymburk Poděbrady Příbram Rakovník Říčany Sedlčany Slaný Vlašim Votice

Obce s pověřeným obecním úřadem (29): Bělá pod Bezdězem Benátky nad Jizerou Březnice Čelákovice Hostivice Jesenice Jesenice Jílové u Prahy Kamenice Kostelec nad Černými lesy Kouřim Křivoklát Městec Králové Mníšek pod Brdy Mšeno Nové Strašecí Odolena Voda Pečky Rožmitál pod Třemšínem Roztoky Sadská Sázava Týnec nad Labem Týnec nad Sázavou Uhlířské Janovice Unhošť Úvaly Velvary Zruč nad Sázavou

Návrh na výběr OPOÚ (16): Benátky nad Jizerou, Čelákovice, Jílové u Prahy, Kostelec nad Černými lesy, Kouřim, Křivoklát, Mníšek pod Brdy, Mšeno, Nové Strašecí, Pečky, Rožmitál pod Třemšínem, Roztoky, Týnec nad Labe, Uhlířské Janovice, Velvary, Zruč nad Sázavou.

Ostatní obce – návrh (25): Zvoleněves, Skorkov, Plužná, Xaverov (u Benešova), Rabyně, Karlštejn, Koněprusy, Borek, Jenštejn, Panenské Břežany, Tuchoraz, Masojedy, Poříčany, Zvole, Lety, Řitka, Krakovec, Žiželice, Dřevčice, Dymokury, Chotěšice, Kačice, Kamenné Zboží, Libiš, Přišimasy.

Ústecký kraj (16 + 7 + 7 = 30) Obce s rozšířenou působností (16): Bílina Děčín Chomutov Kadaň Litoměřice Litvínov Louny Lovosice Most Podbořany Roudnice nad Labem Rumburk Teplice Ústí nad Labem Varnsdorf Žatec

Obce s pověřeným obecním úřadem (14): Benešov nad Ploučnicí Česká Kamenice Duchcov Jirkov Klášterec nad Ohří Krupka Libochovice Libouchec Postoloprty Šluknov Štětí Úštěk Vejprty Velké Březno

Návrh na výběr OPOÚ (7): Duchcov, Jirkov, Klášterec nad Ohří, Postoloprty, Šluknov, Úštěk, Vejprty.

Ostatní obce – návrh (7): Kovářská, Háj u Duchcova, Velký Šenov, Budyně nad Ohří, Velemyšleves, Žalany, Chabařovice.

Zlínský kraj (13 + 7 + 11 = 31) Obce s rozšířenou působností (13): Bystřice pod Hostýnem Holešov Kroměříž Luhačovice Otrokovice Rožnov pod Radhoštěm Uherské Hradiště Uherský Brod Valašské Klobouky Valašské Meziříčí Vizovice Vsetín Zlín

Obce s pověřeným obecním úřadem (12): Bojkovice Brumov-Bylnice Horní Lideč Hulín Chropyně Karolinka Koryčany Morkovice-Slížany Napajedla Slavičín Staré Město Uherský Ostroh

145


Martin Horký

Návrh na výběr OPOÚ (7): Bojkovice, Horní Lideč, Hulín, Napajedla, Slavičín, Staré Město, Uherský Ostroh.

Ostatní obce – návrh (11): Fryšták, Slušovice, Ostrožská Nová Ves, Valašská Senice, Karolinka, Nedašov, Boršice, Žalkovice, Vysoké Pole, Suchá Loz, Vlčnov. Celkové počty vybraných obcí: 472 1) Obce s rozšířenou působností: 205 2) Obce s pověřeným obecním úřadem: 107 3) Ostatní obce: 160 Stav k 20.5.2008, opraveny sporné položky.

146

Vysoká škola ekonomická v Praze

Recommend Documents