79
Vysoká škola báňská – Technická univerzita Ostrava Hornicko – geologická fakulta Institut geoinformatiky DIPLOMOVÁ PRÁCE Ostrava 2007 Jaroslav Kunc
Vysoká škola báňská – Technická univerzita Ostrava Hornicko – geologická fakulta
Institut geoinformatiky
DIPLOMOVÁ PRÁCE
Ostrava 2007 Jaroslav Kunc
VYSOKÁ ŠKOLA BÁ ŇSKÁ – TECHNICKÁ UNIVERZITA OSTRAVA
Hornicko-geologická fakulta Institut geoinformatiky
TVORBA MULTIDIMENZIONÁLNÍ DATABÁZE PRO GIS STATISTIKY
Diplomová práce
Autor: Jaroslav Kunc Vedoucí diplomové práce: Doc. Dr. Ing. Jiří Horák
Ostrava 2007
Prohlašuji, že Celou diplomovou práci včetně příloh, jsem vypracoval samostatně a uvedl jsem všechny použité podklady a literaturu. Jsem byl seznámen s tím, že na moji diplomovou (resp. bakalářskou) práci se plně vztahuje zákon č.121/2000 Sb. - autorský zákon, zejména § 35 – využití díla v rámci občanských a náboženských obřadů, v rámci školních představení a využití díla školního a § 60 – školní dílo. Beru na vědomí, že Vysoká škola báňská – Technická univerzita Ostrava (dále jen VŠB-TUO) má právo nevýdělečně, ke své vnitřní potřebě, diplomovou práci užít (§ 35 odst. 3). Souhlasím s tím, že jeden výtisk diplomové práce bude uložen v Ústřední knihovně VŠB-TUO k prezenčnímu nahlédnutí a jeden výtisk bude uložen u vedoucího diplomové práce. Souhlasím s tím, že údaje o diplomové práci, obsažené v Záznamu o závěrečné práci, umístěném v příloze mé diplomové práce, budou zveřejněny v informačním systému VŠB-TUO. Rovněž souhlasím s tím, že kompletní text diplomové práce bude publikován v materiálech zajišťujících propagaci VŠB-TUO, vč. příloh časopisů, sborníků z konferencí, seminářů apod. Publikování textu práce bude provedeno v omezeném rozlišení, které bude vhodné pouze pro čtení a neumožní tedy případnou transformaci textu a dalších součástí práce do podoby potřebné pro jejich další elektronické zpracování. Bylo sjednáno, že s VŠB-TUO, v případě zájmu z její strany, uzavřu licenční smlouvu s oprávněním užít dílo v rozsahu § 12 odst.4 autorského zákona. Bylo sjednáno, že užít své dílo – diplomovou práci nebo poskytnout licenci k jejímu využití mohu jen se souhlasem VŠB-TUO, která je oprávněna v takovém případě ode mne požadovat přiměřený příspěvek na úhradu nákladů, které byly VŠB-TUO na vytvoření díla vynaloženy (až do jejich skutečné výše). V Ostravě dne 30.4.2007 Jaroslav Kunc plné jméno autora podpis autora Adresa trvalého pobytu diplomanta Brumov 1202 76 331 okr. Zlín
Anotace
Cílem práce bylo sestrojit multidimenzionální databázi pro GIS statistiky a ukázat
možnosti, které poskytuje pro analýzu dat s využitím programu SPSS. V teoretické části je
popsána struktura multidimenzionální databáze a typy datových modelů, vhodných pro
multidimenzionální databázi. Je zde vysvětlen pojem datová kostka a její využití. V další části
následuje pojednání o datových skladech a systémech OLAP a OLPT. V praktické části je
ukázán návrh datové struktury databáze, její tvorba v programu MS Access a naplnění
statistickými daty. Poslední část práce se zabývá importem dat do programu SPSS a tvorbou
OLAP reportů a statistických map, pomocí programu SPSS.
Klí čová slova: GIS, Multidimenzionální databáze, OLAP, SPSS
Annotation
The purpose of this thesis was to create a multidimensional database for GIS statistics
and to demonstrate possibilities, which are provided for data analysis with use of the SPSS
program. In theoretical part is described the structure of a multidimensional database and the
types of data models, suitable for multidimensional database. It is explained here the term data
cube and its application. Next part follows with a treatise about data warehouses and about
OLAP and OLPT systems. In practical part is showed the design of data structure of the
database, its creation in a MS Access program and filling up with statistical data. The last part
of this study is concerning with import of data to SPSS program and with creation of OLAP
reports and statistical maps, by SPSS program.
Keywords: GIS, Multidimensional database, OLAP, SPSS
Na tomto místě chci poděkovat p. Doc. Dr. Ing. Jiřímu Horákovi za cenné rady a ochotu podělit se o své zkušenosti.
Děkuji také své rodině a nejbližším, bez kterých by tato práce nikdy nevznikla, za jejich trp ělivost a podporu.
OBSAH
OBSAH SEZNAM ZKRATEK 1 ÚVOD................................................................................................................................................1 2 Současný stav....................................................................................................................................2
2.1 Evidence dat na ÚP a MPSV.........................................................................................................2 2.2 GIS statistiky .................................................................................................................................2 2.3 Uložení GIS statistik......................................................................................................................2
3 Popis dat............................................................................................................................................3 3.1 Data...............................................................................................................................................3 3.2 Popis agregovaných dat z evidence uchazečů o zaměstnání .........................................................3
3.2.1 List OKpráce........................................................................................................................4 3.2.2 List Ukazatele ......................................................................................................................4 3.2.3 List Popis .............................................................................................................................5
3.3 Geografická data z MPSV.............................................................................................................5 4 CO JE TO MULTIDIMENZIONALITA? ...................... ..............................................................7
4.1 Základy multidimenzionality .........................................................................................................7 4.2 Princip multidimenzionality ..........................................................................................................7 4.3 Multidimenzionální datový model .................................................................................................8
4.3.1 Normalizace.......................................................................................................................11 4.3.2 Hierarchie dimenzí.............................................................................................................11
4.4 Datový sklad................................................................................................................................11 4.5 OLTP vs. OLAP...........................................................................................................................12 4.6 Charakteristika OLAP.................................................................................................................13
5 Použitý SW.....................................................................................................................................14 5.1.1 MS Access .........................................................................................................................14 5.1.2 SPSS ..................................................................................................................................14
6 POSTUP PRACÍ ............................................................................................................................17 6.1 Návrh datové struktury multidimenionální databáze ..................................................................17
6.1.1 Vytvoření tabulek dimenzí.................................................................................................17 6.1.2 Export struktury do MS Access .........................................................................................21
6.2 Vytvoření databáze......................................................................................................................21 6.3 Naplnění databáze.......................................................................................................................22 6.4 Ruční naplnění dat ......................................................................................................................22
6.4.1 Tabulka DTAB_CAS.........................................................................................................23 6.4.2 Tabulka DTAB_EKONOMPROM....................................................................................23 6.4.3 Tabulka DTAB_OBEC......................................................................................................24 6.4.4 Tabulka FTAB_HODNOTA .............................................................................................26
6.5 Automatizovaný import dat .........................................................................................................26 6.5.1 Do tabulky FTAB_HODNOTA.........................................................................................27 6.5.2 Do tabulky DTAB_EKONOMPROM...............................................................................28
6.6 Import dat z MDD do SPSS.........................................................................................................28 6.7 Nastavení vah..............................................................................................................................34
6.7.1 Vytvoření základního souboru s daty ................................................................................34 6.7.2 Vytvoření souboru s váhami ..............................................................................................36 6.7.3 Definování váhy ................................................................................................................37
6.8 Spuštění OLAP kostek .................................................................................................................39 6.8.1 Ukázka DRILL-DOWN.....................................................................................................42 6.8.2 Ukázka vývoje míry nezaměstnanosti ...............................................................................42
7 TVORBA STATISTICKÝCH MAP ............................................................................................44 7.1 Syntaxe pro tvorbu mapových výstupů v SPSS............................................................................44 7.2 Spouštění syntaxe ........................................................................................................................45 7.3 Popis syntaxe...............................................................................................................................46
7.3.1 Popis základní části syntaxe ..............................................................................................46 7.4 Kartodiagramy ............................................................................................................................48
7.4.1 Bar chart - Sloupcový strukturní kartodiagram..................................................................48 7.4.2 Pie chart - Kruhový strukturní kartodiagram.....................................................................49
7.4.3 Graduated symbol - Kartodiagram odstupňovaných (gradujících) symbolů .....................51 7.5 Kartogramy .................................................................................................................................52
7.5.1 Individual values – Kartogram individuálních hodnot......................................................52 7.5.2 Range of Values – Kartogram zařazení do tříd..................................................................53 7.5.3 Dot density (Tečkový kartogram)......................................................................................54 7.5.4 Spojení více typů grafů (Multiple themes) ........................................................................55 7.5.5 Automatizace generování mapových výstupů v SPSS.......................................................56
8 ZÁVĚR ...........................................................................................................................................58 LITERATURA: .......................................................................................................................................60 SEZNAM TABULEK A OBRÁZK Ů .....................................................................................................61
Seznam tabulek......................................................................................................................................61 Seznam obrázků ....................................................................................................................................62
Seznam příloh...........................................................................................................................................64
SEZNAM ZKRATEK
DWH Data Warehouse
ČR Česká republika
ČSÚ Český statistický úřad
GIS Geografický informační systém
IS Informační systém
IS SZ Informačního systému služeb zaměstnanosti
JTSK Jednotná trigonometrická síť katastrální
OLAP On Line Analytical Processing
OLTP On Line Transaction Processing System – transakční systémy
MPSV Ministerstvo práce a sociálních věcí ČR
MDD Multidimenzionální databáze
SLDB 2001 Sčítání lidu, domů a bytů 2001
UIR-ADR Územně identifikační registr objektů a adres
UIR-ZSJ Územně identifikační registr základních sídelních jednotek
ÚP Úřad práce
VŠB – TUO Vysoká škola báňská – Technická univerzita Ostrava
ČUZK Český ústav zeměměřický a katastrální
ISVS Informační systémy veřejné správy
ISDP Informační systém o datových prvcích
1
1 ÚVOD
Tato diplomová práce pojednává o tvorbě multidimenzionální databáze, kterou by
mohly využívat úřady práce pro vkládání, skladování a výběry dat z evidencí úřadů práce a
pro tvorbu statistických analýz a mapových výstupů. Práce je rozdělena na několik částí.
V úvodu se zabývá teoretickým popisem tvorby datových skladů, multidimenzionální
databází a systémem OLAP. V dalších částí práce je popsán postup tvorby
multidimenzionální databáze, tvorba OLAP reportů a mapových výstupů v produktu SPSS.
Dále práce pojednává o GIS statistikách, jejich struktuře a možnosti využití
multidimenzionální databáze pro jejich ukládání. Z evidence uchazečů o zaměstnání, která je
vedena na úřadech práce se vybrané primární údaje a ukazatele agregují do statistik, které jsou
nazývány GIS statistiky. Tato data jsou pořizována pro popis a sledování stavu trhu práce a
můžeme je využívat pro analytické účely, statistické zpracování a také tvorbu statistických
map a jejich interpretaci. V současné době jsou údaje z GIS statistik na úřadech práce
zpracovávány pomocí programu MicroSoft Map, který je součástí MS Excel 97 a 2000.
Agregovaná data se ukládají do sešitů MS Excel, pomocí předem nadefinovaných
šablon. Pro jeden měsíc se mohou vygenerovat až 4 soubory s daty. S narůstajícím počtem
takto ukládaných souborů je stále složitější provádět výběry potřebných dat. Hlavním
problémem při zpracovávání těchto dat je časový faktor, konkrétně sledování vývoje určitého
jevu mezi dvěma daty. Tento problém lze řešit uložením dat do multidimenzionální databáze,
pomocí které lze snadno sledovat vývoj jevu pro libovolný časový interval.
Cílem práce je tedy vytvořit multidimenzionální databáze pro evidenci vybraných
ukazatelů situace na trhu práce s návrhem řešení zvlášť pro MPSV (s využitím programového
prostředí SPSS) a zvlášť pro úřady práce (s využitím MS Access).
Poslední část práce se zabývá studiem možností programu SPSS, pomocí kterého lze
vytvářet statistické mapy a automatizací jejich generování s využitím psaného kódu – syntaxe.
2
2 Současný stav
2.1 Evidence dat na ÚP a MPSV
V současnosti jsou data vytvářena a ukládána na úřadech práce pomocí programového
vybavení OKpráce od společnosti Oksystem s.r.o., které tvoří základní část Informačního
systému služeb zaměstnanosti (IS SZ). Systém byl postupně budován již od roku 1993 a je
určen pro pracovníky úřadů práce a MPSV (architektura klient/server) a pro veřejnost (portál
MPSV). Tento IS je provozován a využíván Správou služeb zaměstnanosti Ministerstva práce
a sociálních věcí (MPSV) v Praze, na 77 úřadech práce (ÚP) a na dalších 163 pobočkách
úřadů práce [9]. Data exportovaná z tohoto systému se označují jako GIS statistiky.
2.2 GIS statistiky
Základním účelem GIS statistik je poskytování vybraných dat k jistému datu
z evidence úřadu práce, agregovaných na úroveň územních jednotek (obce, případně obvody a
městské části), zahrnující počty volných míst, uchazečů o zaměstnání rozdělených do skupin,
například podle věku, doby evidence nebo dosaženého vzdělání. Údaje z GIS statistik slouží
pro konstrukci kartogramů a kartodiagramů mapující situaci na trhu práce na území
příslušného úřadu práce. GIS statistika se pravidelně připravuje počátkem měsíce a obsahuje
údaje k poslednímu dni předchozího měsíce [1]. Exportovaná data mohou být uložena až ve
čtyřech souborech XLS. Soubory obsahují primární údaje (např. počty osob) a ukazatele,
které se vypočítávají z primárních hodnot pro každou obec v okrese.
2.3 Uložení GIS statistik
Agregovaná data z GIS statistik se ukládají do sešitů MS Excel. Z důvodů
narůstajícího počtu takto ukládaných dat, jejich výběrů a sledování vývoje dat v čase, je nutné
najít nový a vhodnější způsob jejich uložení a zpracování. Do roku 2005 bylo možné provádět
export pouze jednoho souboru, který obsahoval 34 primárních údajů. Po roce 2005 bylo
možné využít k exportu další 3 typy šablon, což vedlo k nárůstu počtu dat. Tudíž pro jeden
měsíc se mohou vytvořit až 4 soubory s daty. Zpracovávání takto odděleně uložených dat je
při výběru údajů z jednotlivých souborů a sledování vývoje ukazatelů v čase komplikované. S
využitím multidimenzionální databáze a jejich vlastností, lze snadno sledovat vývoj ukazatele
v čase.
3
3 Popis dat
3.1 Data
Data, která jsou při tvorbě pilotní databáze zpracovávána, byla poskytnuta z Úřadu
práce v Bruntále, Mladé Boleslavi a z MPSV. K dispozici jsou data pro období od roku 1995
do roku 2006. Data pro část roku 2005 a pro počátek roku 2006 jsou z MPSV a je v nich
zahrnuta celá Česká republika.
3.2 Popis agregovaných dat z evidence uchaze čů o zaměstnání
Data se exportují pomocí aplikace PAexport nebo pomocí exportu zabudovaného
přímo v systému OKpráce a ukládají se do tabulky formátu MS Excel, s využitím předem
definovaných šablon.
Jedná se o primární údaje, které jsou agregovány z dat uložených v systému OKpráce
a jsou vztaženy k základním územním jednotkám. Tato primární data jsou zapotřebí pro
výpočet jednotlivých ukazatelů.
Při exportu lze data uložit až do 4 souborů. Struktura těchto souborů je definována
pomocí šablon ve formátu MS Excel. Níže v tabulce je uveden účel každé šablony. Všechny
šablony jsou určeny pro ukládání dat exportovaných ze systému OKpráce. Liší se zaměřením
primárních dat a skupinou vypočtených ukazatelů.
Tabulka č. 1 Popis šablon pro export z OKpráce
ŠablonaGIS0 Určena pro ukládání dat exportovaných ze systému
OKpráce a pro výpočet ukazatelů (původní).
ŠablonaGIS1 Ukazatele rizikových skupin, věková struktura.
ŠablonaGIS2 Ukazatele vzdělání a KZAM (klasifikace zaměstnání).
ŠablonaGIS3 Ukazatele doby evidence, volných pracovních míst –
vzdělání a KZAM.
Každý exportovaný soubor obsahuje celkově 4 listy. První list pro uložení primárních
dat z OKpráce, druhý pro výpočet ukazatelů, třetí pro definování hranic tříd, a poslední pro
popis dat. Ukázky dat uvedené níže jsou pro okres Bruntál.
4
3.2.1 List OKpráce V listu jsou jako první uvedeny informace o původu dat a měsíc vyhotovení. Ve
druhém řádku (záhlaví) jsou zkrácené názvy primárních údajů. Celkový počet primárních
údajů, které můžou být exportovány ze systému OKpráce, je kolem 150. Počet ukazatelů,
který byl vyexportován pro okres Bruntál je 83, protože byla použita pouze šablona
ŠablonaGIS0. V tomto listu jsou zaznamenávány pouze primární data (počty osob), nikoliv
statistické ukazatele, které přísluší daným konkrétním obcím. Celkový počet obcí, který je
platný k 1.1.2006 je v okrese Bruntál 68 a ke každé obci je přiřazen její kód. Níže na tabulce
č. 1 je vidět způsob uložení dat v listu.
Tabulka č.2: Obsah listu OKpráce
3.2.2 List Ukazatele I v tomto listu jsou taktéž uvedeny informace o původu dat a měsíc vyhotovení. Ve
druhém řádku jsou zkrácené názvy ukazatelů. V tomto listu je v jednotlivých buňkách
definován způsob výpočtu ukazatele.
Ukazatele jsou vypočítány z primárních dat listu OKpráce. Na obrázku č. 1 je
znázorněna ukázka výpočtu ukazatele míry nezaměstnanosti. Vzorec v hlavičce provádí
výpočet ukazatele pro obec Bruntál. Nejprve kontroluje, zda se neprovádí dělení nulou a poté
z nadefinovaných buněk listu OKpráce provede výpočet a výsledek zaokrouhlí na 1 desetinné
místo.
Obrázek č.1: Výpočet ukazatelů
5
Tabulka č.3: Obsah listu Ukazatelé
3.2.3 List Popis Zde jsou uvedeny metadata, tj. popisy dat a ukazatelů. Pro každý ukazatel je zde
uveden jeho název, způsob výpočtu a zkrácený název.
3.3 Geografická data z MPSV
Pomocí geografických dat jsou zobrazeny ukazatele, které popisují situaci na trhu
práce. Vektorová data, která jsou v projektu použita, jsou ve formátu MapInfo. Jedná se o data
pro celou Českou republiku. Základem jsou data získaná z ČUZK, vzniklá digitalizací map v
měřítku 1:50000. Data jsou aktuální k datu 31.12.2006. 1.1. 2007 byly provedeny změny
v územní struktuře a obec Sosnová, která spadala do územního členění okresu Bruntál, od
tohoto data spadá pod uzemní členění okresu Opava. Tato změna není v datech provedena.
V mapě jsou znázorněny hranice administrativních jednotek obcí každého okresu. Na
obrázku č. 2 je ukázána struktura atributů. Atributová tabulka obsahuje popisné atributy
geografických objektů, jako je název obce, kód obce a kód okresu.
Obrázek č.2: Ukázka geografických dat
Na obrázku č.3 je uvedena ukázka načtených dat pro okres Bruntál v programu MapInfo.
6
Obrázek č.3: Ukázka geografických dat v prostředí MapInfo
7
4 CO JE TO MULTIDIMENZIONALITA?
Co je to vlastně multidimenzionální databáze (MDD)? MMD je softwarový systém,
který je navržen pro výhodné a pružné uložení a vybírání velkých objemů dat, která jsou
navzájem vztažena, nahlížena a analyzována z různých perspektiv. Tyto perspektivy
nazýváme dimenze [11].
4.1 Základy multidimenzionality
Multidimenzionální databáze slouží jako podklad pro získávání sumarizovaných a
agregovaných informací. Hlavními prvky takovéto databáze je několik dimenzionálních
tabulek, umožňujících velmi rychle a pružně měnit jednotlivé pohledy uživatele na
modelovanou realitu. Díváme se na zkoumanou věc z několika pohledů či stran (dimenzí).
Čím více dimenzí máme k dispozici, tím je náhled detailnější a jeho vypovídající hodnota
pravdivější. Mezi výhody multidim. modelování patří hlavně návaznost na OLAP aplikace,
přidávání jednotlivých dimenzí a editace atributů v těchto dimenzích.
Multidimenzionalita umožňuje:
– Nové pohledy na data
– Vyhledávání zákonitostí
– Vyhledávání trendových charakteristik
– Rychlý přístup k velkým objemům dat
– Dobré schopnosti pro modelování a prognózy
– Analýzy dat
Dále je třeba zmínit i nevýhody, což je například vysoký nárok na kapacitu úložiště
nebo problémy při změně dimenzí.
Je to jeden z typů databázové technologie, který slouží především jako datová
základna pro dotazy.
4.2 Princip multidimenzionality
Na obrázku č.4 je znázorněn princip multidimenzionality. Ve žluté buňce může být
uložena hodnota (cena, počet kusů,…). Jsou to takzvaná souhrnná data a obvykle jsou
numerická a měřitelná. Atributy, které je reprezentují se tudíž nazývají numerické atributy.
Faktory, na základě kterých se může provádět analýza sumarizovaných dat, se nazývají
dimenze, které jsou reprezentovány dimenzionálními atributy. Výběrem specifických dimenzí
8
získává uživatel náhled na sumarizovaná data. Pro každou dimenzi je definován stav, ze
kterého se díváme na hodnotu. Například dimenze Čas a Geografie je možné definovat jako
víceúrovňové hierarchie s úrovněmi dny, týdny, měsíce, čtvrtletí, roky a města, státy.
Například na prodej konkrétního výrobku se díváme z určitého období daného roku, který je
určen lokalizací. Další dimenzí může být typ kupujícího (student, důchodce…).
Obrázek č.4: Multidimenzionalita [99
4.3 Multidimenzionální datový model
Podstatou přístupu k reprezentaci dat v MDD je multidimenzionální model dat. Data
se v něm místo v tabulce zobrazují pomocí vícerozměrných polí. V terminologii OLAP se
také hovoří o datových kostkách nebo multidimenzionálních kostkách [11].
Převážná většina údajů je organizovaná v relační databázi v dvojrozměrných relačních
tabulkách. Všechny řádky tabulek mají vztah k nějakému předmětu nebo události. Základním
prvkem je datová kostka – krychle. Datové modely jsou nejčastěji vyjádřené graficky pomocí
schématických vztahů. Existuje několik druhů datových modelů, vhodných pro tvorbu
multidimenzionální databáze:
1. hvězdy
2. vločky
3. krychle
9
Nejvíce používané schéma datového modelu je hvězdicové schéma.
Obrázek č.5: Hvězdicové schéma [10]
Hvězdicové schéma obsahuje tabulky dimenzí pro každou dimenzionální oblast
ve schématu. Jedna tabulka dimenze popisuje vlastnosti jedné a to jí příslušné dimenze.
Všechny multidimenzionální modely, které obsahují tabulky faktů a dimenzí, jsou varianty
hvězdicového schématu. V rámci tohoto schématu se tabulka, která se skládá z dimenzí a
číselných atributů, nazývá tabulka faktů. Sloupce tabulky faktů jsou pouze buď klíčové
položky nebo hodnoty. Všechny cizí klíče od odpovídajících tabulek dimenzí tvoří složený
primární klíč tabulky faktů. Parciální vztah faktorové tabulky je N:1.
Co je to granularita v tabulce faktů a tabulkách dimenzí? Granularita určuje úroveň
podrobnosti údajů – faktů, uložených ve fakt. tabulce. Je přímo závislá na úrovni podrobnosti
dimenzí [2]. Nízká granularita, nízká úroveň detailu, znamená nemožnost pracovat s
detailními daty, tj. podle dnů, jednotlivých produktů, prodejců apod. Naopak je to u vysoké
granularity, tedy vysoké úrovně detailu dat. Má také vyšší nároky na diskový prostor
datového skladu. Data by měla být uložena s nejvyšší možnou granularitou.
Datové kostky obsahují souhrnná data. Datová kostka se skládá jenom z těch
dimenzionálních atributů, které sdílejí všechny její číselné atributy. To znamená, že
dimenzionální atributy tvoří základ datové kostky. Pokud chceme získat informace z několika
datových kostek, mohou být tyto atributy propojeny na úrovni jedné nebo více společných
dimenzí, kdy vzniká souhvězdí [11]. Na obrázku č.6 je znázorněna tabulka faktů a z ní
vytvořená (multi)dimenzionální kostka, která má pouze dvě dimenze. První je výrobekID,
druhá obchodID.
10
Obrázek č.6: Dimenzionální krychle – 2 dimenze
Přidáním časové informace vznikne kostka se třemi dimenzemi a to tak, že do tabulky faktů
se přidá atribut datum. Viz. obrázek č.7.
Obrázek č.7: Multi - Dimenzionální krychle – 3 dimenze
Počet dimenzí je teoreticky libovolný. Některá programová prostředí dovolují až přes 70
dimenzí.
Protože MDD slouží především pro účely analýz, jsou s tímto modelem a navazujícími
systémy (především OLAP) spojeny specifické analytické postupy:
1. Drill-down je jedna z metod analýzy informací. Používá se pro
pronikání do hloubky, pro získání většího detailu. Proniká od vyšších
celků až na jednotlivé položky, ze kterých se tyto celky skládají.
Jedná se o pohyb v hierarchii dimenze dolů [11]. V praxi se metoda
projevuje přidáním atributu z dimenze do výstupu.
2. Roll - Up - Jde o protiklad metody Drill – Down. Pohybujeme se
strukturou dimenze vertikálně zdola nahoru k vyšším úrovním
agregace.
11
3. Pivoting umožňuje „otáčet“ datovou krychlí, tj. měnit úhel pohledu
na data na úrovni prezentace obsahu datového skladu [16].
4. Drill-across je spojení dvou a více faktorových tabulek se stejnou
granularitou [10].
4.3.1 Normalizace Hvězdicové schéma nemá normalizované tabulky. Jedna z hlavních vlastností
multidimenzionálního modelování je ta, že se používají normalizované tabulky s fakty spolu
s nenormalizovanými tabulkami dimenzí [7]. V podstatě jde o to, že všechny tabulky dimenzí
jsou napojeny k jedné tabulce faktů. Úplně normalizovaná dimenze je znázorněna níže na
obrázku č. 8. Normalizace usnadňuje pochopení vzájemných vazeb (relací) mezi objekty,
snižuje redundanci a zlepšuje dodržení konzistence a integrity dat. Nejdříve byly definovány 3
normální formy, nyní existuje i čtvrtá a pátá normální forma.
Obrázek č.8: Normalizovaná tabulka produkt
4.3.2 Hierarchie dimenzí Obvykle každá dimenze má svoji vlastní hierarchii, pomocí níž můžeme uskutečňovat
seskupování a členění. Příkladem může být časová dimenze, která má hierarchii Měsíc-
Kvartál-Pololetí-Rok. Čím jsme na nižší úrovni hierarchie dimenzí, tak získáváme více
podrobnější informace, a naopak, pokud jsme na vyšších úrovních [2].
4.4 Datový sklad
Datový sklad (Data Warehouse, dále „DWH“) slouží k zajištění rychlé dostupnosti dat
z různých oblastí (např. z rozsáhlé firmy) a k zjednodušení a zrychlení analytické či statistické
práce s daty. Výstupy z DWH podporují rovněž rozhodování na všech úrovních řízení. Velmi
známá definice datového skladu pochází od Billa Inmona [2]:
„Datový sklad je podnikově strukturovaný depozitář subjektově orientovaných,
integrovaných, časově proměnných, historických dat použitých na získávání informací a
podporu rozhodování. V datovém skladu jsou uložena atomická a sumární data.“
Subjektovou orientací se rozumí orientace na takový subjekt, podle kterého jsou data
v datovém skladu kategorizována [2]. Subjektem může být zákazník, dodavatel, zaměstnanec,
výrobek a podobně. Integrovanost nám představuje jednotnost a integrovanost datového
12
skladu. Je třeba zavést jednotnou terminologii, jednotné jednotky veličin. Ukládané údaje by
měla být konzistentní a důvěryhodné. Časovou variabilitu můžeme chápat jako sérii snímků,
z nichž každý reprezentuje určitý časový úsek [2].
DWH integruje data z provozních systémů a dalších externích zdrojů do datové
struktury a je zpravidla fyzicky i logicky oddělen od provozních systémů [5]. Data z
provozních systémů se převádějí do DWH, kde se po transformaci ukládají způsobem, který
vyhovuje analytickému a prezentačnímu zpracování výstupů.
DWH by měl být složen ze dvou vrstev. „Nultá“ vrstva obsahuje nevyčištěná data,
která se ve stanovených intervalech načítají z různých zdrojů. Následně se data čistí, integrují,
sumarizují a ověřuje se jejich správnost. Takto ověřená data se ukládají do „první“ vrstvy,
která je základem pro všechny typy výstupů z DWH [5].
4.5 OLTP vs. OLAP
Co je OLTP (On Line Transaction Processing)? Jsou to systémy, které automatizují a
sbírají transakce prostřednictvím výpočetních systémů [2]. Tyto systémy uchovávají záznamy
o jednotlivých uskutečněných transakcích a jsou nejčastěji realizovány pomocí v dnešní době
nejběžnější, tj. relační databázové technologie. Obvykle bývají tyto databáze menší a pracují s
daty řádek po řádku (přístupy po indexu, malý objem zpracovávaných
dat). OLTP systémy slouží především k plnění datových skladů v
pravidelných intervalech (denně, týdně, měsíčně).
Existují takzvané OLTP aplikace, což jsou aplikace, které se
snaží sbírat transakce a umožňují manipulaci s rozsáhlým počtem
datových záznamů ve stejném čase.
Pod názvem OLAP (On Line Analytical Processing) jsou
zahrnuté technologie, metody a prostředky, které umožňují ad-hoc
analýzu multidimenzionálních informací. OLAP umožňuje flexibilní
práci s údaji a analyzuje data z mnohých hledisek. Je to tedy
technologie pro zpracování dat z datového skladu, s využitím velkého
množství kladených dotazů. OLAP zpravidla pracuje s velkým
množstvím dat, řádově se objemy dat pohybují v Gb až Tb. OLAP
systémy většinou pracují na základě relačních databází.
Obrázek č.9: Multi – OLTP Vs. OLAP[15]
13
4.6 Charakteristika OLAP
Smyslem OLAP systémů je co nejrychleji poskytnout uživateli požadované agregace
dat, popřípadě výsledky analýz provedených právě nad těmito agregacemi.[15]. Mezi
standardní funkce OLAP, které může koncový uživatel využívat patří:
• Kalkulace a modelování napříč dimenzemi, skrz hierarchie.
• Analýza trendů během postupných časových period.
• Sestupování do nižších úrovní hierarchie (drill-down) – zvětšení detailu.
• Stoupání do vyšších úrovní hierarchie (drill-up) – zmenšení detailu.
• Spojení dvou a více faktorových tabulek se stejnou granularitou (drill-across) [10].
• Rotace pro porovnání v nových dimenzích příslušné oblasti (pivoting).
Umožňuje „otáčet“ datovou krychlí, tj. měnit úhel pohledu na data na úrovni
presentace obsahu datového skladu [16]. V jednu chvíli se na data můžeme dívat
z pohledu počtu prodaných kusů daného typu výrobku, a pomocí rotace datové
kostky (změny dimenzí) se na data můžeme dívat z hlediska celkové tržby
stejného typu výrobku v určitých lokalitách.
OLAP je implementovaný v prostředí klient/server a měl by poskytovat poměrně rychlou
odezvu na dotazy [4].
Základní pravidla pro OLAP:
1. Multidimenzionální konceptuální model
2. Transparentnost
3. Dostupnost dat
4. Stabilní výkonnost
5. Architektura klient/server
6. Podpora více uživatelů
7. Neomezené operace napříč dimenzemi
8. Flexibilní výstupy
9. Neomezené dimenze a úrovně agregací
14
5 Použitý SW
Pro zpracování jednotlivých kroků byla použita různá programová prostředí. Mezi
hlavní programy například patří MS Access, MS Excel, MapInfo, CASE Studio2, VBA a
SPSS.
Pro návrh datové struktury byl použit program CASE Studio2, pro vytvoření kódu
automatického importu dat do prostředí Visual Basic for Application, který je součástí MS
Access. K realizaci datové struktury a OLAP lze využít různých nástrojů a prostředí. Jedním
z programů, který obsahuje možnost vytvářet OLAP reporty je také program SPSS.
5.1.1 MS Access Toto prostředí bylo vybráno pro realizaci multidimenzionální databáze. Databáze
vytvořená v tomto prostředí slouží pro ukládání dat a pro import vstupních dat do systému
SPSS, ve kterém probíhá statistické vyhodnocování.
5.1.2 SPSS Toto programové prostředí má MPSV k dispozici a používá ho pro
statistické zpracování dat (i z GIS Statistik) a tvorbu statistických map. Z tohoto
důvodu byl tento program vybrán pro první vyzkoušení tvorby a práce s OLAP a
mutlidimenzionální databází.
SPSS je statistický systém doporučovaný pro aplikace ve vědě, marketingu,
personalistice a výzkumu, pro zpracování laboratorních měření a pro sumarizace dat
z velkých i menších databází různého typu.
Používá se pro:
- finanční analýzy
- tvorbu rozhodovacích modelů a analýzu i predikci časových řad.
- data mining
- manažerské analýzy.
Program se skládá celkem z jedenácti modulů. Mezi nejpoužívanější moduly patří:
Modul Base: Je základním modulem. Pomocí tohoto modulu lze nad importovanými
daty vytvářet grafy, provádět analýzy (OLAP, popisné statistiky), atd.
Modul Tables: Je zaměřen na tvorbu tabulek a obsahuje nástroje pro jejich editaci.
Modul Regression models: Slouží pro pokročilé vícerozměrné statistické metody.
15
Modul Exact tests: Tento modul je rozšířením standardních testů, např. Pearsonovým
testem nezávislosti, Fischerovým neparametrickým testem,
Kolmogorov - Smirnovovým testem atd.
Modul Maps: Slouží pro vytváření statistických map (resp. náhledů), jako jsou
kartogramy a kartodiagramy, s využitím základních statistických
charakteristik [14].
Data byla zpracována ve verzi programu č.14. Poslední vydaná verze programu je
verze č.15, ale příliš se neliší od své předchozí verze. Prostředí programu je na první pohled
podobné aplikaci Microsoft Excelale nepracuje s jednotlivými datovými listy, jako MS Excel.
SPSS se skládá z několika hlavních pracovních prostředí:
- DATA – Prostředí, ve kterém jsou vytvářena nebo zobrazována data, načtená
z různých datových zdrojů. V tomto prostředí jsou dvě záložky. Datový editor
(Data View) a editor proměnných (Variable View). V datovém editoru se zobrazují
načtená data a lze je zde editovat. Každý sloupec představuje jeden typ proměnné,
pro kterou se definuje jméno, datový typ, velikost pole, počet desetinných míst,
podrobnější popis, atd.. Buňky obsahují hodnoty dané proměnné.
Obrázek č.10: Editor dat - Data view
Pomocí záložky „Variable view“ se zobrazí popis atributů a zde lze měnit
vlastnosti proměnných a definovat nové proměnné. Jsou zde uvedeny definice
proměnných, jako je název, datový typ, velikost pole atd.. Každý řádek
reprezentuje jednu proměnnou a každý sloupec vlastnosti atributů.
Obrázek č.11: Definice dat - Variable view
16
- SYNTAX – V tomto prostředí lze pomocí příkazů data zpracovávat.
- OUTPUT – Prostředí pro zobrazování, modifikaci grafických výstupů a
informativních hlášení při procesech zpracovávání dat.
- SCRIPT – Pomocí tohoto prostředí se dají vytvářet skripty pro zpracování
některých úloh.
17
6 POSTUP PRACÍ
Pomocí principu a postupů vysvětlených v kapitole 3.3, byla vytvořena databáze a
posléze byla naplněna daty. Ze všeho nejdřív je nutné vytvořit návrh datové struktury
databáze. Pro vytvoření tohoto návrhu byl použit programový prostředek CASE Studio 2.
Následně byla databáze vygenerována v databázovém prostředí, v tomto případě MS Access a
naplněna daty. K naplnění tabulky faktů a dimenzionální tabulky ekonomických proměnných
byl vytvořen formulář pomocí programovém prostředí VBA for Application. Takto vytvořená
databáze byla dále využita prostředím SPSS pro tvorbu OLAP kostek a grafických výstupů.
6.1 Návrh datové struktury multidimenionální databá ze
Pomocí programu CASE Studio 2 byl vytvořen návrh datové struktury. Jako schéma
datového modelu multidimenzionální databáze bylo použito hvězdicové schéma. Vzhledem
k dostupným prostředím, bylo schéma aplikováno pro relační SŘBD.
6.1.1 Vytvoření tabulek dimenzí Bylo vytvořeno 5 dimenzionálních tabulek, mezi něž patří tabulka DTAB_CAS
(časová dimenze), DTAB_EKONOMPROM (dimenze ekonomické proměnné),
DTAB_OBEC (prostorová dimenze), DTAB_STRUKTURA (změny v územní struktuře),
DTAB_VYHOTOVIL (zadavatel dat) a tabulka s fakty FTAB_HODNOTA. Pomocí časové
dimenze se definuje časová osa, každý záznam v tabulce faktů (fakt) tak nese časovou
informaci. Pomocí dimenze ekonomické proměnné každému faktu přiřazujeme typ
ekonomické proměnné, který chceme sledovat. Prostorová dimenze zajišťuje každému faktu
lokalizaci k určitému území, tj. georeferencování. Pomocí tabulky DTAB_STRUKTURA se
zaznamenávají změny v územní struktuře. V tabulce DTAB_VYHOTOVIL jsou informace o
osobě, která provedla import dat.
Tabulka DTAB_CAS
Tabulka DTAB_CAS představuje časovou dimenzi. Každý záznam v časové dimenzi
reprezentuje jeden časový úsek, v tomto případě měsíc (nejnižší granularita). Měřítko časové
dimenze závisí na požadované přesnosti pohledu na data. Pro možnost podrobnějšího a
detailnějšího pohledu na data byla zvolena hierarchická struktura Rok – Pololetí – Kvartál -
Měsíc.
18
Dimenzionální tabulku čas tvoří pět atributů, ze kterých atribut ID_CAS je stanoven
jako primární klíč. Níže na obrázku jsou ukázány atributy tabulky s příslušnými datovými
typy. Datový typ každého atributu, mimo atribut Rok, byl nastaven na bajt. To proto, že každé
pole tabulky bude nabývat jen celočíselných hodnot.
Obrázek č.12: Tabulka čas
Jelikož se při importu dat do tabulky faktů neukládá identifikátor, který přísluší
konkrétnímu datu ale ukládá se rovnou datum (např. prosinec 2004), je nutná existence
převodní tabulky. Tato tabulka se jmenuje TAB_IDCAS a má dva atributy.
Obrázek č.13: Převodní tabulka TAB_IDCAS
Tabulka DTAB_EKONOMPROM
Tabulka dimenze ekonomických ukazatelů DTAB_EKONOMPROM se skládá z osmi
atributů. Prvním atributem je ID_EKONOMPROM a postupně pak NAZEVPROM,
ZPUSVYPOCTU, ZKRACNAZEV a VZDELANI.
Obrázek č.14: Tabulka ekonomických proměnných
Na tuto tabulku je dále napojena tabulka VZDELANI, která má tři atributy. Prvním
atributem je identifikátor, druhou označení vzdělání a třetím atributem je popis typu vzdělání.
Obrázek č.15: Tabulka vzdělání
19
Tabulka DTAB_OBEC
Typ této tabulky je opět dimenzionální. Na obrázku č. 16 jsou zobrazeny atributy této
tabulky. Datový typ identifikátoru IDKRAJE, ID_OKRESU a ID_OBCE byl nastaven jako
text. Podle Standardu ISVS – Katalogu jednoduchých datových prvků je datový typ kódu
okresu stanoven jako číslicové znaky, datový typ obce je definován jako číslicové znaky a
datový typ kódu kraje je definován jako celé číslo. Od 1.1.2007 je Katalog datových prvků
ISVS nahrazen Informačním systémem o datových prvcích (ISDP) [8].
Obrázek č.16: Tabulka obce
Při návrhu této dimenze a struktury tabulek je nutné vyřešit zaznamenávání změn v
územní struktuře, která není statická a dochází k její změnám. Tento problém lze vyřešit
atributem STRUKTURA, pomocí něhož jsou definovány jednotlivé typy územních struktur.
Pomocí atributů PLATNOST_OD a PLATNOST_DO je stanovena platnost územní struktury.
Jelikož k jednomu stavu územní struktury přísluší více obcí a jedna obec může spadat
do několika verzí územní struktury, je nutné realizovat vazbu M:N mezi tabulkou
FTAB_HODNOTA a tabulkou DTAB_OBEC. Je nutné vytvořit tabulku M:N, Která má
jeden atribut a to IDOBCE.
Obrázek č.17: Tabulka M:N
Tabulka FTAB_HODNOTA
Další tabulkou je tabulka faktů. Jak již bylo psáno v kapitole 2.3, tato tabulka
obsahuje identifikátory připojených dimenzionálních tabulek. Ke každé kombinaci těchto
identifikátorů může existovat údaj, který se zaznamenává do atributu HODNOTA (decimal).
20
Na obrázku č. 18 je znázorněna tabulka faktů, která obsahuje identifikátory dimenzí času,
lokality, ekonomická proměnné, pracovníka a dále obsahuje hodnoty ukazatelů. Další
proměnnou v tabulce faktů je POZNAMKA, sloužící pro záznam data vložení. Poslední
proměnnou je identifikátor pracovníka ID_PRAC, který provedl vložení nových dat nebo
editaci dat stávajících.
Obrázek č.18: Faktorová tabulka
Tabulka DTAB_VYHOTOVIL
Tato tabulka je složena ze čtyř atributů. Z atributu ID_PRAC, jejíž datový typ byl
nastaven na string z toho důvodu, že identifikátor osoby se může skládat jak z číslic, tak
z písmen. Dalšími atributy jsou JMENO, PRIJMENI a PRACOVISTE.
Obrázek č.19: Tabulka DTAB_VYHOTOVIL
Celkové navržené schéma je uvedeno na obr. č.20
Obrázek č.20: Schéma navržené databáze
21
6.1.2 Export struktury do MS Access Navržená struktura v programu CASE Studio 2 se pomocí funkce Generování Skriptu
převede na skript, který lze spustit v programu Access. Skript obsahuje stručný popis postupu,
jak jej lze v MS Access spustit. Na obrázku č. 21 jsou v záložce „Co generovat“ zobrazeny
možnosti pro generovaný skript s volbou výběru prvků, které si přejeme vygenerovat. Při
zaškrtnutí možnosti „Tabulky“ bude výsledný skript zahrnovat generování tabulek. Jestliže je
zaškrtnuta položka „Primární klíče“, provede se jejich generace. V opačném případě se
negeneruje žádný primární klíč. Dále budou generovány „Indexy“, „Referenční integrita“
(pokud ji podporuje cílová databáze) a „Pohledy“. V záložce „Jak generovat“ se volí způsob
generování referenční integrity. V záložce „Rozšířené“ jsou např. volby pro generování
komentářů k tabulkám či atributům. V poslední záložce „Seznam entit“ je možné vybrat
tabulky, které mají být vygenerovány.
Obrázek č.21: Nastavení exportu skriptu
6.2 Vytvo ření databáze
Vytvoření samotné databáze v MS Access se velmi ulehčilo pomocí skriptu,
generovaného v programu CASE Studio 2. Po založení prázdné databáze byl vytvořen nový
modul, do kterého byl načten vygenerovaný skript a posléze modul spuštěn. Po jeho spuštění
a proběhnutí se vytvoří namodelované tabulky a relace tak, jak se připravily v CASE Studiu.
Výsledná relace je ukázána na obr. č. 20.
22
6.3 Napln ění databáze
Naplnění databáze se provádělo daty z Úřadu práce v Bruntále a daty z Mladé
Boleslavi.
6.4 Ruční napln ění dat
Data pro roky 1995 – 1997 byla vložena ručně, pomocí funkce „Vložit“ a „Kopírovat
do schránky“. Systém vkládání dat byl po sloupcích a po měsících. Nejdříve do sloupce
HODNOTA byly vloženy hodnoty ukazatelů za jeden měsíc. Počet vkládaných záznamů pro
jeden měsíc je 71, což se rovná počtu obcí okresu Bruntál ke dni 1.1.1998. Ke každému
záznamu byl do sloupce ID_CAS přiřazen příslušný identifikátor času. To znamená, že pro
jeden měsíc byl vložen stejný identifikátor. Pro první měsíc bylo ID 1, pro druhá měsíc ID 2
atd. Do sloupce ID_HODNOTA bylo doplněno automatické číslo.
Obrázek č.22: Vkládání dat
Poté se do sloupce ID_EKONOMPROM vložily identifikátory ekonomických proměnných
v takovém pořadí, jak byly vyexportovány pro daný měsíc. Tím se zajistilo, že ke každé
hodnotě ekonomické proměnné byl přiřazen ten správný identifikátor ukazatele. Posléze se
stejným způsobem pro každý měsíc do sloupce ID_OBCE vložily identifikátory obcí.
23
6.4.1 Tabulka DTAB_CAS Do této tabulky byly vloženy časové údaje pro rok, pololetí, kvartál a měsíc. Jelikož
tato tabulka neobsahuje příliš rozsáhlá data, nejdříve byly v programu Excel vytvořeny
všechny sloupce zastupující prvky v časové hierarchii a jednorázově vloženy do tabulky
DTAB_CAS. Celkový počet záznamů je 168, což představuje data v rozmezí od roku 1995 do
2008.
Obrázek č.23: Naplnění tabulky DTAB_CAS
6.4.2 Tabulka DTAB_EKONOMPROM Do této tabulky se vložily názvy ukazatelů, způsob jejich výpočtu, identifikátory a
zkrácené názvy ukazatelů. Pomocí atributu POHLAVÍ se zaznamenávají údaje o pohlaví
(muž+žena, žena, muž), pro které se daný ukazatel vztahuje. Do proměnné VZDĚLÁNÍ jsou
opět k vybraným ukazatelům vloženy identifikátory nejvyššího dosaženého vzdělání
uchazečů. Celkový počet vložených záznamů je 74. Při vložení primárních dat a ukazatelů ze
zbylých tří šablon, vzroste počet záznamů přibližně na 280.
24
Obrázek č.24: Naplnění tabulky DTAB_EKONOMPROM
Na tuto tabulku je napojena tabulka VZDELANI, ve které jsou uloženy jednotlivé
typy dosaženého vzdělání a jejich popis. Níže v tabulce č. 4. je uveden přehled typů
dosaženého vzdělání a způsob, jakým jsou v databázi jednotlivé typy sloučeny. Pomocí této
tabulky je možné agregovat data podle stupně dosaženého vzdělání.
Tabulka č.4: Typy vzdělání
Vzdělání
ID Označení vzdělání Popis
0 A,B,C bez vzdělání(A), neúplné základní vzdělání(B), základní vzdělání, praktická škola jednoletá(C)
1 D,E,H,J nižší střední vzdělání(D), nižší střední odborné vzdělání (E), střední odborné vzdělání a výučním listem(H), střední nebo střední odborné vzdělání bez maturity(J)
2 K,L,M,N střední všeobecné (gymnázia K), úplné střední odborné vzdělání s vyučením i maturitou(L), úplné střední odborné vzdělání s maturitou (bez vyučení - M), vyšší odborné vzdělání(N)
3 R,T,V vysokoškolské bakalářské vzdělání(R), vysokoškolské magisterské vzdělání(T), vysokoškolské doktorské vzdělání(V)
6.4.3 Tabulka DTAB_OBEC V této tabulce jsou uloženy všechny obce okresu Bruntál pro roky 1995 – 2006 a
Mladé Boleslavi k datu 1.1.2005. Ke každé obci je přiřazen okres a kraj, pod jehož územní
25
vymezení daná obec spadá. Dále ke každé obci je přiřazen název obce s rozšířenou působností
a název obce s pověřeným úřadem, pod které daná obec rovněž spadá. Celkový počet
vložených záznamů je 329. Územní členění okresu Bruntál se v průběhu let měnilo. Od
1.1.2005 obce Huzová, Moravský Beroun a Norberčany nepatří do územního vymezení
Moravskoslezského kraje a okresu Bruntál [13].
Při geografických změnách územní struktury okresu, například k odloučení obce nebo
ke sloučení obcí, se do tabulky vloží všechny obce okresu, které spadají pod jeho působnost a
pomocí atributu STRUKTURA se zajistí odlišení od předchozího stavu. V atributech
PLATNOST_OD a PLATNOST_DO je uveden interval platnosti dané územní struktury.
V tabulce jsou uloženy tři verze územní struktury pro okres Bruntál. První – bruntal_1, platná
od 1.1.1995 do 31.12.2004, druhá – bruntal_2, platná od 1.1.2005 do 31.12.2006 (Odloučení
obcí Huzová, Moravský Beroun a Norberčany), třetí – bruntal_3, platná od 1.1.2007 do
31.12.2007 (odloučení obce Sosnová). Dále je do tabulky vložena jedna struktura pro okres
Mladá Boleslav – ml_boleslav1, jejíž platnost je od 1.1.2005 do 31.12.2006. V případě, že
obec zanikne, její záznamy zůstanou v tabulce faktů. Jestliže vznikne obec nová, např.
připojením ze sousedního okresu, je nutné provést import dat do tabulky faktů pro tuto obec
z předešlých let, protože po zařazení obce do nového okresu spadá i její minulost do nového
okresu.
Obrázek č.25: Naplnění tabulky DTAB_OBEC
26
6.4.4 Tabulka FTAB_HODNOTA Jak již bylo uvedeno výše, do této tabulky se vkládají hodnoty primárních klíčů
dimenzí a k nim příslušící hodnoty. Do proměnné POZNAMKA je uloženo systémové datum,
kdy byl vklad dat proveden. Data byla vkládána pomocí vytvořeného kódu v jazyce VBA a
formuláře Import.
Obrázek č.26: Naplnění tabulky FTAB_HODNOTA
6.5 Automatizovaný import dat
Pomocí vytvořených formulářů v prostředí MS Access je možné provést
automatizovaný import dat do tabulky faktů a dimenzionální tabulky
DTAB_EKONOMPROM.
Obrázek č.27: Formuláře pro import dat
27
6.5.1 Do tabulky FTAB_HODNOTA Pomocí aplikace VBA v prostředí MS Access byl vytvořen kód importu souborů
formátu xls „GIS statistika“ pro naplnění tabulky faktů. Kód je optimalizován pro strukturu
souborů xls, která je ukázána na obrázku č. 28.
Obrázek č.28: Struktura importovaného souboru
Po spuštění modulu se zobrazí dialogové okno pro výběr souboru pro import. Vybírá
se jeden soubor pro import. Filtr výběru je nastaven na zobrazení souborů s koncovkou *.xls.
Obrázek č.29: Dialogové okno pro import
28
Poté je nutné zadat identifikátor osoby, která vkládání provádí.
Obrázek č.30: Okno pro zadávání ID pracovníka
Po výběru jednoho ze souborů se zobrazí okno s informací o počtu importovaných
hodnot (řádků * sloupců).
Obrázek č.31: Informace o počtu vkládaných dat
Po potvrzení se provede import dat pro zvolený měsíc. Naplněná faktorová tabulka je
ukázána na obrázku č.26.
Importovaná data se pro každý měsíc ukládají postupně za sebou. Struktura kódu je
taková, že nejdříve jsou deklarovány proměnné a poté se v kódu zpracovává dialogové okno
pro výběr importovaného souboru. V dalším kroku zavede ovladač pro připojení souborů
formátu Excel a provede se připojení k vybranému souboru. Do definovaných polí se načtou
data z listů Okpráce, Ukazatelé a Popis. Následně se zjistí počty řádků a sloupců v každém
poli a počet ukazatelů v listu Popis. Dále se zjistí z hlavičky prvního listu časový údaj a
pomocí příkazu SQL INSERT INTO se vloží nejdříve hodnoty z listu OKpráce a následně
z listu Ukazatelé.
6.5.2 Do tabulky DTAB_EKONOMPROM Jako v předešlém importu se pomocí dialogového okna vybere soubor, jehož ukazatele
se mají importovat. Při importu se do tabulky nenačtou dva stejné ukazatele.
6.6 Import dat z MDD do SPSS
Import dat z databáze do programu SPSS se provádí pomocí menu FILE/Open
Database. Pomocí nabídky se vybere požadovaná databáze a připojí se požadovaný datový
zdroj.
29
Obrázek č.32: Import databáze do SPSS
Po načtení databáze program umožní výběr tabulek a atributů, které chceme
importovat.
Obrázek č.33: Výběr tabulek pro import
V následujícím okně se nastavují a specifikují vztahy mezi tabulkami. Propojení
vzniká tak, že ukazatelem myši uchopíme atribut jedné tabulky a přetáhneme jej na atribut
jiné tabulky. Pokud již nějaké relace (vztahy) existují v importované databázi, automaticky se
přenesou i zde.
30
Obrázek č.34: Stanovení vztahů tabulek
Nyní je zde možnost výběru kritérií importu pro jakékoliv atributy. Budeme-li
například chtít, aby se provedl pouze import hodnot pro rok 1995, nastavíme kritéria podle
obrázku č. 35.
Obrázek č.35: Stanovení kritérií importu
Po nastavení všech kritérií se požadovaná data naimportují do prostředí SPSS. Ukázka
naimportovaných dat je vidět na obrázku č. 36. Pomocí stanovených kritérií se načetly
vybrané proměnné a záznamy s hodnotami ekonomicky aktivních v obcích okresu Bruntál.
Proměnná HODNOTA obsahuje hodnoty ukazatele pro jeden měsíc, konkrétního roku, pro
danou obec.
31
Obrázek č.36: Ukázka naimportovaných dat
Datové typy importovaných proměnných jsou shodné s příslušnými datovými typy
v multidimenzionální databázi. Na obrázku č. 37 je ukázka importovaných dat z MDD bez
stanovení kritérií. Struktura importovaných dat je taková, že postupně za sebou se ukládají
záznamy s hodnotami jednotlivých ukazatelů. Jeden záznam představuje jeden měsíc.
Obrázek č.37: Ukázka naimportovaných dat
32
Ukázka importu dat z MDD pro územní strukturu platn ou k 1.1.2005 Stejně jako v předchozí kapitole se pomocí menu FILE/Open Database vybere požadovaná
databáze a připojí se požadovaný datový zdroj. Po výběru tabulek a atributů, zvolených pro
import je nutné nastavit kritéria importu. Pokud chceme provést import hodnot ukazatele
VMC (počet volných míst) pro územní strukturu platnou k 1.1.2005, kritérium STRUKTURA
se musí rovnat identifikátoru „bruntal_2“ a identifikátor ukazatele se musí rovnat hodnotě
„VMC“.
Obrázek č.38: Ukázka naimportovaných dat
Po importu se do SPSS Data editoru načtou hodnoty daného ukazatele pro zvolenou
datovou strukturu. Jelikož nebylo zadáno omezení z hlediska času, načetli se všechny hodnoty
ukazatele VMC pro zvolenou územní strukturu, včetně struktury území, která je platná po
31.12.2005. V tomto případě je nutné záznamy z novější datové struktury odstranit. To je
možné provést pomocí nabídky Select Cases, která ukázána na obrázku č. 39. Pomocí této
nabídky se nastaví záznamy, které chceme zachovat. V případě, že se budou importovat data
pro poslední platnou územní strukturu, není nutné provádět výběr záznamů, protože veškeré
importované záznamy odpovídají poslední platné územní struktuře.
33
Obrázek č.39: Nabídka Celest Cases
Výsledná importovaná data, pro zvolenou územní strukturu (bez odloučených obcí
Huzová, Moravský Beroun a Norberčany) je ukázána na obrázku č. 40.
Obrázek č.40: Výsledný import zvolené územní struktury
34
6.7 Nastavení vah
Při výpočtu průměrných hodnot ukazatelů pro větší územní celky (okresy), je potřeba
použít vážený průměr. Vážený průměr poskytuje charakteristiku souboru dat v případě, že
hodnoty v tomto souboru mají různou důležitost (váhu). Pro výpočet váženého průměru
potřebujeme hodnoty, jejichž průměr se má spočítat, a zároveň jejich příslušné váhy.
Například při určování průměrné hodnoty míry nezaměstnanosti okresu Bruntál je nutné, aby
hodnoty míry nezaměstnaností jednotlivých obcí byly ohodnoceny vahami.V tomto případě
budou jako váhy brány počty ekonomicky aktivních obyvatel. Pro přiřazení váhy hodnotám
proměnných je tedy nutné vytvořit 2 soubory s daty (formát SPSS). První, základní soubor
s hodnotami proměnné, jejíž vážený průměr se má vypočítat a druhý soubor s vahami, který
bude k základnímu připojen. V prostředí SPSS se váhy definují výběrem proměnné (sloupce),
jejíž hodnoty představují váhy pro jednotlivé záznamy.
6.7.1 Vytvoření základního souboru s daty Tento soubor se vytvoří pomocí importu dat z databáze MS Access (FILE/Open
Database). Po výběru zdrojové databáze se zvolí tabulky a proměnné, které chceme
naimportovat.
Obrázek č.41: Vytvoření základního souboru s daty
Při výběru proměnné je nutné přidat i tu proměnnou, která bude sloužit jako
propojovací klíč. V tomto případě se bude jednat o proměnnou ID_OBCE. V dalším kroku se
nastaví kritéria importu. Pokud se má spočítat vážený průměr míry nezaměstnanosti, vyberou
35
se z tabulky FTAB_HODNOTA pouze ty záznamy, které obsahují hodnoty ukazatele MN.
Nebo jestliže se má jednat o vážený průměr podílu absolventů, vyberou se pouze záznamy
s hodnotami ukazatele PCABS_U. Na obrázku č… je ukázáno nastavení kritérií importu. Při
zadávání kritérií se v poli „Expression1“ zadá proměnná ID_EKONOMPROM, v poli
„Relation“ se vybere vztah „=“ a v poli „Expression“ se zadá název ID ekonomického
ukazatele, jehož hodnoty je potřeba importovat.
Obrázek č.42: Nastavení kritérií pro import – základní soubor
Výsledný importovaný soubor bude vypadat takto:
Obrázek č.43: Vytvořený základní soubor
36
Nyní je nutné provést nastavení třídění záznamů. Kliknutím pravým tlačítkem myši na
název proměnné, podle níž se mají záznamy třídit se vybere možnost Sort Ascending.
Záznamy se setřídí vzestupně podle proměnné ID_OBCE.
Obrázek č.44: Ukázka třídění Sort Ascending
6.7.2 Vytvoření souboru s váhami Postup je obdobný jako u vytváření základního soubor s tím rozdílem, že do nabídky
kritérií se zadá název ukazatele, jehož hodnoty mají reprezentovat jednotlivé váhy. Opět se ze
zdrojové databáze zvolí tabulky a proměnné, které chceme naimportovat. I tento soubor musí
obsahovat klíčovou proměnnou ID_OBCE, pomocí níž se připojí k základnímu souboru. Níže
na obrázku č.. je ukázáno nastavení kritérií importu. Vybrány budou pouze ty záznamy, které
odpovídají názvu zvoleného ukazatele (UC).
Obrázek č.45: Nastavení kritérií pro import – základní soubor
37
Výsledný naimportovaný soubor je opět nutné setřídit vzestupně podle proměnné
ID_OBCE.
6.7.3 Definování váhy Pro přiřazení vah se používá menu „Weight Cases".
Obrázek č.46: Nastavení vah
V první řadě musíme načíst základní soubor, ke kterému se mají váhy připojit.
Přiřazení vah k jednotlivým záznamům konkrétní proměnné se provede přidáním nové
proměnné pomocí nabídky Data/Merge Files/Add Variables…
Obrázek č.47: Menu Add Variables…
Z nabídky se vybere externí soubor (ve formátu SPSS), který chceme připojit. Tento
externí zdroj dat musí obsahovat klíčovou proměnnou, pomocí které se má provést připojení
dat. Stejný klíč musí být také v datech, do kterých se mají nová data přidat (ID_OBCE).
Úspěšné přidání dat se provede tehdy, pokud oba dva soubory s daty, jak externí (soubor s
vahami), tak stávající mají stejné seřazení dat - vzestupné seřazení dat. Na obrázku č. 48 je
vidět nastavení parametrů pro připojení dat. Do pole „Excluded Variables“ se načtou
proměnné z externího souboru (soubor s vahami). V tomto případě to byly proměnné
38
ID_OBCE (vybrána jako klíčová proměnná), ID_CAS_DTAB, NAZEVOBCE, ROK
HODNOTA (přejmenována na VAHA) a ID_EKONOMPROM. Jelikož v nově vytvořeném
datovém souboru se nemůžou vyskytovat proměnné se stejným názvem, je nutné název
přidávané proměnné přejmenovat („Rename“). Do pole „New Working Data File“ se
přidávají proměnné, které mají být připojeny. V poli „Key Variables“ se definují klíčové
proměnné.
Obrázek č.48: Nastavení přidávání nové proměnné
Takto vypadají data po připojení externího souboru:
Obrázek č.49: Ukázka připojených vah
39
Z takto připravených dat je možné generovat OLAP reporty, pro libovolné časové a
územní umístění. V OLAP kostce bude možnost výběru pouze z toho ekonomického
ukazatele, pro který se vytvořil základní a externí soubor s váhami. Pomocí funkce ADD
CASES lze za jeden typ ukazatele (doplněného o váhu) připojit další typ ukazatele, který byl
například také doplněn o další proměnnou (váhu). U OLAP kostky vytvořené z těchto
ukazatelů by bylo možné vybírat mezi těmito ukazateli.
6.8 Spušt ění OLAP kostek
Realizace OLAP kostek se provádí tak, že se z menu (analyze/reports/OLAP Cubes)
spustí formulář, kde v poli Summary Variables (HODNOTA) se zadávají sloupce s daty a v
poli Grouping Variables se zadávají sloupce, které definují dimenze (obr.50). Dále je zde
možnost výběru statistik, které chceme spočítat (průměr, šikmost, špičatost…).
Obrázek č.50: Výběr dimenzí a faktů
Po zvolení potřebných statistik a potvrzení výběrů a nastavení se vygeneruje report OLAP
kostky.
Obrázek č.51: Report OLAP kostky
40
V SPSS Vieweru lze pomocí menu Edit/SPSS Pivot Table Object/Open vyvolat
nabídku k editaci vzniklých OLAP kostek. Tento nástroj se jmenuje Pivoting Trays a s jeho
pomocí lze měnit pohledy na zpracovaná data, upravovat řádky, sloupce, barvy, měnit pozice
dimenzí a ostatní vlastnosti celé tabulky.
Obrázek č.52: Pivoting Trays
Nyní lze pozorovat změny například pro proměnnou míra nezaměstnanosti. Jak lze
vidět na obrázku č. 53, v rozbalovacím menu je vybrána obec Bruntál a kód ekonomické
proměnné MN (Míra nezaměstnanosti). Na obrázku č. lze vidět vývoj této proměnné po dobu
tří let. Tabulku tvoří statistiky, které byly vybrány na začátku při definování vlastností OLAP
kostek.
Obrázek č.53: Ukázka report OLAP I.
Na obrázku č. 54 byla vybrána agregace podle měsíců a ne podle let. Hodnoty jsou
počítány pro stejný měsíc po dobu tří let. (proto N = 3). Pomocí rozbalovacích oken lze opět
měnit obce a ekonomické proměnné.
41
Obrázek č.54: Ukázka reportu OLAP II.
Na obrázku č. 55 lze vidět vygenerované OLAP kostky pro ukazatel PCABS_U (Podíl
absolventů v obci Bruntál pro uvedené roky). V pravé části obrázku je OLAP report bez
využití vah. Na reportu umístěném vlevo jsou již váhy zahrnuty ve výpočtu.
Obrázek č.55: Ukázka reportu OLAP III.
42
Při generování OLAP reportů pro více let a větší počet ukazatelů má počítač problémy se
zpracováním dat. Po určité době ukončí proces a napíše varování s možným řešením. Tímto
řešením je zvětšení počtu možných buněk pomocí příkazu set mxcells =
automatic. I po provedení tohoto příkazu se generování OLAP reportů nedokončí.
Řešením je redukce počtu importovaných ukazatelů na ty, které nás zajímají a tím tak
snížit počet zpracovávaných dat.
Obrázek č.56: Chybové hlášení
6.8.1 Ukázka DRILL-DOWN Na obrázku č. 57 je ukázán princip funkce Drill-down. Na levém obrázku je zobrazena
průměrná hodnota míry nezaměstnanosti v obci Bruntál pro roky 1995 až 1997. Na
prostředním obrázku je znázorněn posun po ose hierarchie směrem dolů, zvětšení detailu na
hodnoty míry nezaměstnanosti v prvních pololetích pro roky 1995 až 1997. Na pravém
obrázku jsme se posunuli na úroveň čtvrtletí.
Obrázek č.57: Ukázka funkce Drill-down
6.8.2 Ukázka vývoje míry nezaměstnanosti Na obrázku č. 58 je znázorněna ukázka vývoje ukazatele míry nezaměstnanosti pro
obec Bruntál v letech 1995 - 2005. Pro srovnání je uveden případ s využitím váženého
průměru a bez zavedení vah do výpočtu.
43
Obrázek č.58: Ukázka vývoje míry nezaměstnanosti
Pokud jsou všechny váhy stejné, je vážený průměr totožný s aritmetickým průměrem.
Při výpočtu váženého průměru míry nezaměstnanosti (MN), jsou jako váhy vstupující do
výpočtu brány počty ekonomicky aktivních obyvatel (EAC01). Počty aktivních obyvatel
v obci Bruntál se v letech od roku 1995 do roku 2005 příliš nemění. Od roku 1995 do konce
roku 2003 je jejich počet 9178. Od roku 2003 se jejich počet zvýšil na 9299. U malých obcí se
počty ekonomicky aktivních nemění téměř vůbec. Příkladem může být obec Krasov, kde se
počet ekonomicky aktivních v průběhu let 1995 až 2005 zvýšil ze 162 na 163. Z tohoto
důvodu, vypočítané vážené průměry pro jednotlivé roky se téměř neliší od průměrů
obyčejných. Malá změna se projeví v případě, že je vážený průměr počítán pro větší počet let.
44
7 TVORBA STATISTICKÝCH MAP
Modul Maps slouží pro vytváření statistických map. Lze využít šest typů tématických
map, rozdělených do dvou kategorií:
a) Kartodiagramy
- Sloupcový strukturní kartodiagram
- Kruhový strukturní kartodiagram
- Kartodiagram odstupňovaných (gradujících) symbolů
b) Kartogramy
- Kartogram individuálních hodnot
- Kartogram zařazení do tříd
- Tečkový kartogram s náhodným umístěním teček
Dále modul Maps nabízí kombinaci těchto šesti typů map v jednom výstupu.
7.1 Syntaxe pro tvorbu mapových výstup ů v SPSS
V programu SPSS lze téměř každou operaci prováděnou
pomocí grafického prostředí převést do psaného kódu – syntaxe.
Po spuštění SPSS se pomocí menu FILE/NEW/Syntax spustí
editor syntaxe. Editor je prostředí, v němž se pomocí příkazů
můžou zpracovávat data.
Syntaxe se ukládají do souborů, které mají příponu .SPS.
Syntaxe může být spuštěna na datový soubor, jež je aktivní
v datovém editoru, nebo na soubor, který je neaktivní a je dán svou cestou. Tento soubor se
po zadání cesty stane aktivním.
V editoru, který je ukázán na obrázku č. 59, se zadávají pomocí příkazů operace, které
mají být vykonány na datech (proměnných). Každý příkaz pro jeho úspěšné vykonání musí
být ukončen tečkou.
45
Obrázek č.59: Editor syntaxe
7.2 Spoušt ění syntaxe
V SPSS existují 2 typy příkazů:
1) Příkazy, které vyžadují ukončení operace.
Operace se zakončuje pomocí příkazu EXECUTE, který se zapisuje za konec
syntaxe. Mezi operace vyžadující ukončení syntaxe patří například práce
s proměnnýma a jejich hodnotami (Statistické výpočty, vytváření nových
proměnných, výběry záznamů atd.).
2) Příkazy, které nevyžadují ukončení operace.
Tyto příkazy nemusí být ukončeny značkou EXECUTE. Patří sem příkazy pro
nastavení výpočtů, popisovací příkazy, informační příkazy atd..
Spuštění syntaxe je možné provést čtyřmi způsoby:
1. All – Spustí se všechny příkazy v editoru.
2. Selection – Spustí se vybrané příkazy, může jich být v jednom
výběru několik.
3. Current – Spustí se příkaz, který je na řádku, kde je kurzor.
4. To End – Spustí se všechny příkazy, které jsou od aktuální polohy
kurzoru až po konec syntaxe.
46
Obrázek č.60: Spuštění syntaxe
7.3 Popis syntaxe
Celá syntaxe je složena z řady příkazů, pomocí nichž jsou definovány vlastnosti
mapového výstupu. Syntaxe je složena ze dvou základních částí. V první části syntaxe se
definují parametry pro napojení (bindování) geografických dat s hodnotami proměnných. Ve
druhé části syntaxe se definují typy mapových výstupů a jejich parametry. Modul Maps se
v kódu spouští pomocí příkazu MAPS. Lomítka v syntaxi oddělují jednotlivé příkazy.
7.3.1 Popis základní části syntaxe Modul MAPS nabízí tři možné typy spojení geografických dat a statistických dat
podle toho, jaká data a proměnné máme k dispozici.
První možností je využití příkazu GVAR. Příkaz GVAR vyžaduje jméno proměnné,
která bude identifikovat geografické území. Hodnoty této proměnné musí odpovídat
hodnotám v tabulce Geosetu.
/GVAR = VAR(varname) [VAR(varname)]
Druhou možností je využití příkazu XY. Propojení se bude realizovat pomocí
souřadnic X a Y. Statistická data musí obsahovat proměnné se souřadnicemi k lokalizaci
záznamů v mapě. Tento způsob se práci realizovat nedá, jelikož statistické data neobsahují
atributy se souřadnicemi X a Y.
/XY(varname)(varname)
Poslední možností jak připojit data k mapě je pomocí příkazu LOOKUP, který
rozšiřuje možnosti příkazu XY. Tento způsob připojení se používá v případě, kdy statistická
data obsahují například proměnnou s kódy obcí, ale zároveň chybí proměnné se souřadnicemi
X a Y, které reprezentují polohu kódů v mapě a dále použitý geoset neobsahuje údaje
o kódech obcí. Využívá se tabulka Lookup formátu MapInfo, která obsahuje souřadnice X, Y
nebo nás odkazuje na název proměnné (je i součástí geosetu), pomocí které lze propojení
realizovat. U geografických dat z MPSV je pro každý okres za referenční tabulku považován
soubor, který je pojmenován zkráceným názvem příslušného okresu a má koncovku .TAB.
/LOOKUP(varname)(filename)
47
V dalším kroku se zapisuje syntaxe pro vývěr vhodného Geosetu. Dále se zadává
název layeru, který se má z geosetu vybrat.
/GSET = "filename" [LAYER = "layer name"]
Pomocí příkazu /SHOWLABEL = AS_IS | NO | YES se zobrazují popisky mapy jako
jsou například názvy okresů.
Poté následuje příkaz /TITLE = {(DEFAULT)…}, pomocí kterého se nastavuje hlavní
titul mapy.
Příkaz GVMISMATCH dovoluje specifikovat maximální počet neshod, které budou
zobrazeny do chybové tabulky (parametr MAX). Parametr SKIP určuje počet chyb,
v propojení s mapou, přes které se ještě provede vykreslení mapy. Parametr CANCEL zruší
vykreslování mapy při první nalezené chybě.
/GVMISMATCH = (CANCEL) (MAX = {n}) (SKIP{n})
Obrázek č.61: Základní část syntaxe
48
7.4 Kartodiagramy
Jsou mapy s dílčími územními celky, do kterých jsou diagramy znázorněna statistická
data (absolutní hodnoty) [3].
7.4.1 Bar chart - Sloupcový strukturní kartodiagram Sloupcový strukturní kartodiagram se definuje pomocí základního příkazu BARMAP.
Umožňuje zobrazovat sloupce pro několik proměnných nebo pro kategorie rozdělené podle
určité proměnné. Pomocí sekvence příkazů popsaných níže, se definuje vzhled a vlastnosti
mapového výstupu.
VAR(V1) VAR(V2) – Proměnná pro individuální sloupce.
VAR(V1) BY VAR(V2) – Proměnná V1 bude rozdělena pomocí hodnot proměnné
V2. V2 může mít číslicový nebo znakový datový typ (Kategorizace veličiny V1 veličinou
V2).
SUM = functions – Funkce, pomocí které mají být data reprezentována v grafu.
HEIGHT – Tímto příkazem se definuje výška grafu. Defaultně je nastavena výška
0,64 cm.
INDSCALE – příkaz, který nabývá hodnot YES nebo NO. Pokud se zvolí YES, každý
další sloupec je odstupňován nezávisle na ostatních sloupcích. Jako defaultní je nastavena
hodnota NO.
LEGENDTITLE – Příkaz pro nastavení názvu legendy.
VISIBLE – Příkaz, pomocí něhož se dá zapnout či vypnout zobrazení celého grafu.
Tento příkaz spolu s příkazem LEGENDTITLE se vyskytuje u všech typů map, proto
nebudou dále uváděny.
Zde je ukázka mapového výstupu a syntaxe pro generování sloupcového kartodiagramu.
MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR' /SHOWLABEL = YES /TITLE ='PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro roky 1995-1997' MAX = 100 /BARMAP= VAR(HODNOTA) BY VAR(ROK) SUM=(MEAN) HEIGHT=0.7 INDSCALE=NO LEGENDTITLE ='Podil absolventu v okresu Bruntal pro roky:' EXE.
49
Obrázek č.62: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru – Sloupcový strukturní
kartodiagram
7.4.2 Pie chart - Kruhový strukturní kartodiagram Kruhový strukturní kartodiagram se definuje pomocí příkazu PIEMAP.
/PIEMAP = VAR(V1) BY VAR(V2) – V1 je proměnná, která bude roztříděna podle
proměnné V2. Datový typ proměnné V1 musí být numerický. Proměnná V2 může být
numerického nebo řetězcového charakteru.
SUM = (function name) – Funkce, pomocí které mají být data reprezentována v grafu.
[DIAMETER = {0.25}] – Příkaz, pomocí něhož se nastavuje průměr koláčů.
Defaultně je nastavena hodnota 0,25 palce (0,64cm) .
[GRADUATED = YES | NO] – Pokud je hodnota příkazu nastavena na YES,
průměry koláčů jsou odstupňovány podle celkové hodnoty, která reprezentuje konkrétní
koláč.
50
Obrázek č.63: Kruhový strukturální kartodiagram – GRADUATED = YES
Zde je ukázka mapového výstupu a syntaxe pro generování kruhového strukturního
kartodiagramu.
MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR' /SHOWLABEL = YES /TITLE ='PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro roky 1995-1997' MAX = 100 /PIEMAP=VAR(HODNOTA) BY VAR(ROK) SUM=(SUM) GRADUATED = NO LEGENDTITLE ='Podíl absolvent ů v okrese Bruntál pro roky:'. EXE.
Obrázek č.64: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru – Kruhový strukturní
kartodiagram
51
7.4.3 Graduated symbol - Kartodiagram odstupňovaných (gradujících) symbolů Kartodiagram stupňovaných symbolů umísťuje symboly dovnitř každého regionu.
Velikost symbolu je úměrná vypočtené hodnotě pro daný region.
/SYMBOLMAP = Var(varname) – Proměnná, jejíž hodnoty stanovují velikost
symbolu pro každý region.
SUM = (function name) – Funkce, pomocí které mají být data reprezentována v grafu.
Zde je ukázka mapového výstupu a syntaxe pro generování kartodiagramu
stupňovaných symbolů.
MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR' /SHOWLABEL = YES /TITLE = 'PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro rok 1995' MAX = 100 /SYMBOLMAP=VAR(HODNOTA) SUM=(MEAN) LEGENDTITLE = 'Podíl absolvent ů v okrese Bruntál'. EXE.
Obrázek č.65: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru – Kartodiagram stupňovaných
symbolů
52
7.5 Kartogramy
Jsou mapy s dílčími územními celky, do kterých jsou plošným způsobem znázorněna
statistická data (relativní hodnoty) [3].
7.5.1 Individual values – Kartogram individuálních hodnot Tento typ mapového výstupu používá pro každou individuální hodnotu různou barvu.
Regiony v mapě jsou znázorněny barvou, odpovídající průměru nebo modu z hodnot pro daný
region.
IVMAP = Var(varname) – Proměnná, která na základě svých hodnot přiřadí každému
regionu jedinečnou barvu. Pokud jsou si hodnoty rovny, přiřadí se regionům tatáž barva a do
legendy se napíše počet regionů, které jsou reprezentovány stejnou barvou..
SUM = (function name) – Pomocí tohoto příkazu se nastavuje způsob výpočtu
hodnoty proměnné, které má být přiřazena jedinečná barva. Pomocí parametru MODE bude
konečnou hodnotu reprezentovat modus z hodnot proměnné, pomocí parametru MEAN, pak
průměr z hodnot proměnné.
Zde je ukázka mapového výstupu a syntaxe pro generování kartogramu individuálních
hodnot.
MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR' /SHOWLABEL = YES /TITLE = 'PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro rok 1995' MAX = 100 /IVMAP=VAR(HODNOTA) SUM=(MODE) LEGENDTITLE = 'Podíl absolvent ů v okrese Bruntál'. EXE.
53
Obrázek č.66: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru – Kartogram individuálních
hodnot
7.5.2 Range of Values – Kartogram zařazení do tříd Kartogram zařazení do tříd rozděluje hodnoty proměnné do tříd a každý region podle
jeho hodnoty přiřadí do příslušné třídy. Na mapě jsou třídy reprezentovány gradujícím
barevným odstínem jedné barvy (od nižších hodnot po vyšší).
/ROVMAP = Var(varname) – Proměnná jejíž třídy jsou zobrazeny na mapě.
SUM = (function name) – Funkce, pomocí které se provede výpočet hodnot pro každý
region. Defaultně je nastavený parametr COUNT.
[DISTRIBUTION] = - Metoda použitá pro zařazení záznamů do tříd.
EQSIZE – Přiřazuje záznamy do tříd se stejným rozsahem.
EQCOUNT – Počet záznamů v jednotlivých třídách si je rovný.
NATBREAK – Stanovení tříd s využitím Janksovy optimalizační metody.
SD – Rozdělení do tříd na základě standardní odchylky.
CUSTOM – Dovoluje uživateli specifikovat vlastní rozsahy tříd.
[ALLOWEMPTY = YES | NO] – Příkaz, který dovoluje prázdné třídy.
[NUMRANGES = n] – Příkaz, který definuje počet tříd, které se mají vytvořit.
54
[XRANGE = (n,n) ["string value"]] – Příkaz, pomocí kterého uživatel definuje vlastní
třídy. Například XRANGE=(15,18) ‘Mladiství‘.
Zde je ukázka mapového výstupu a syntaxe pro generování kartogramu zařazení do tříd. MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR' /SHOWLABEL = YES /TITLE = 'PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro rok 1997' MAX = 100 /ROVMAP=VAR(HODNOTA) SUM=(MEAN) NUMRANGES = 5 DISTRIBUTION = NATBREAK L EGENDTITLE = 'Podíl absolvent ů v okrese Bruntál [%]'. EXE.
Obrázek č.67: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru – Kartogram zařazení do tříd
7.5.3 Dot density (Tečkový kartogram) V tečkovém kartogramu tečka představuje určitý počet jednotek. Tečky jsou
zobrazeny náhodně pro každý region.
/DOTMAP = Var(varname) – Proměnná jejíž hodnoty zatupují hustotu teček pro
každý region.
SUM = (function name) – Funkce, pomocí které se provede výpočet hodnot pro každý
region. Defaultně je nastavený parametr COUNT.
[VALUE1DOT = n] – Pomocí tohoto příkazu se nastavuje hodnota, která je
reprezentována jednou tečkou.
55
Zde je ukázka mapového výstupu a syntaxe pro generování tečkového kartogramu.
MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR' /SHOWLABEL = YES /TITLE ='PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro rok 1997' MAX = 100 /DOTMAP=VAR(HODNOTA) SUM=(MEAN) LEGENDTITLE ='Podíl absolvent ů v okrese Bruntál [%]'. EXE.
Obrázek č.68: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru – Tečkový kartogram
s náhodným umístěním teček
7.5.4 Spojení více typů grafů (Multiple themes) V SPSS lze vytvořit také multi mapový výstup – kombinaci typů kartogramů a
kartodiagramů. První část syntaxe se nemění, stejně jako u jednotlivých typů grafů se zde
definuje způsob spojení dat s mapou a název mapy. Druhá část je složena ze dvou nebo více
typů map, bez oddělení tečkou nebo příkazem EXE, který se umístí až na konec celé syntaxe.
Jako v předchozích výstupech se ve druhé části syntaxe definují proměnné se vstupními daty
jednotlivých typů kartogramů, jejich vlastnosti a popisky legend. Níže je ukázka syntaxe pro
tvorbu kartogramu zařazení do tříd v kombinaci se sloupcovým kartodiagramem.
MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='Default' /SHOWLABEL = YES
56
/TITLE = 'MÍRA NEZAM ĚSTNANOSTI A VOLNÁ MÍSTA, pro okres Bruntál, 1997' /BARMAP=VAR(VOLNA_MISTA) BY VAR(ROK) SUM=(SUM) INDS CALE=NO LEGENDTITLE = 'Po čet volných míst:' /ROVMAP=VAR(HODNOTA)SUM=(MEAN) DISTRIBUTION = NATBREAK LEGENDTITLE = 'Míra nezaměstnanosti v obcích [%]' EXE.
Po spuštění syntaxe je opět nutné výsledný mapový výstup upravit. Upravený mapový výstup je ukázán na obrázku č. 69.
Obrázek č.69: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru – Kartogram zařazení do tříd
se sloupcovým kartodiagramem
7.5.5 Automatizace generování mapových výstupů v SPSS Pomocí syntaxe je možné vytvořit šablony pro automatizovanou tvorbu mapových
výstupů. Lze vytvořit:
A) Jeden soubor formátu .SPS pro několik typů mapových výstupů.
V tomto souboru budou uloženy syntaxe pro určité typy kartogramů a kartodiagramů.
Po spuštění tohoto souboru se postupně vygenerují všechny mapové výstupy. Níže je
uveden příklad syntaxe, pro tento způsob tvorby statistických map. Po spuštění
syntaxe pomocí menu Run →All se v SPSS Vieweru vygenerují nadefinované typy
mapových výstupů.
MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR'
57
/SHOWLABEL = YES /TITLE = 'PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro rok 1997' MAX = 100 /ROVMAP=VAR(HODNOTA) SUM=(MEAN) NUMRANGES = 5 DISTRIBUTION = NATBREAK L EGENDTITLE = 'Podíl absolvent ů v okrese Bruntál [%]'. MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR' /SHOWLABEL = YES /TITLE = 'PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro rok 1995' MAX = 100 /SYMBOLMAP=VAR(HODNOTA) SUM=(MEAN) LEGENDTITLE = 'Podíl absolvent ů v okrese Bruntál'. MAPS /GVAR = VAR(ID_OBCE) /GSET = 'Okres Bruntál' LAYER='BR' /SHOWLABEL = YES /TITLE ='PODÍL ABSOLVENT Ů V OKRESE BRUNTÁL, pro roky 1995-1997' MAX = 100 /PIEMAP=VAR(HODNOTA) BY VAR(ROK) SUM=(SUM) GRADUATED = NO LEGENDTITLE ='Podíl absolvent ů v okrese Bruntál pro roky:'. EXE.
B) Pro každý typ mapového výstupu jeden soubor.
Pro každý typ kartogramu či kartodiagramu se vytvoří samostatný soubor se syntaxí.
Podmínkou úspěšného generování map je import potřebných proměnných z databáze
MS Access do datového editoru. Nejdříve je nutné si uvědomit, jaké typy mapových
výstupů mají být generovány a podle toho stanovit kritéria importu.
58
8 ZÁVĚR
Úkolem práce bylo sestrojit multidimenzionální databázi pro GIS statistiky a ukázat
možnosti, které poskytuje pro analýzu dat s využitím programu SPSS.
Nejdříve se práce zabývá teoretickým popisem multidimenzionality, tvory databáze,
fungováním datových skladů a OLAP systémů. Další část práce se zabývá popisem tvorby
databáze, její naplnění daty a import do prostředí SPSS. V poslední části je ukázána tvorba
OLAP reportů a statistických map pomocí programu SPSS.
Důležitým krokem při tvorbě databáze bylo co nejvhodněji navrhnout její strukturu.
Byla zvolena struktura typu hvězda, kde geografická dimenze byla z počátku z důvodu
normalizace dat složena ze tří tabulek (obec, okres, kraj), posléze však upravena na jednu
nenormalizovanou tabulku z důvodu co nejvhodnějšího záznamu změn v územních
strukturách okresů či krajů, tj. sloučení, odloučení nebo připojení obcí. Do takto
nenormalizované tabulky geografické dimenze jsou při každé změně v územní struktuře
okresu uloženy všechny stávající obce a označeny verzí struktury. Takovéto uložení dat zajistí
výběry a agregace dat na základě typu a platnosti územní struktury. Nevýhodou při importech
pro územní struktury z dřívějších období je, že se vyberou i záznamy s hodnotami ukazatelů
obcí, které spadají do novějšího územního členění.
Pro automatizovaný import dat do tabulky faktů byl vytvořen kód, pomocí jazyka
VBA, který umožňuje převést data z listů souborů MS Excel. Programový kód je vytvořen
pro jeden konkrétní typ souboru MS Excel. Pokud bude změněn formát ukládání GIS
statistik, je nutné tuto změnu zavést do algoritmu. Faktorová tabulka obsahuje 620 000
záznamů pro roky 1995 až 2005 a to jen pro okres Bruntál a z malé části pro okres Mladá
Boleslav.
Z takto vytvořené databáze je možné provádět importy dat do prostředí SPSS. Je
vhodné importovat jen ty atributy, které jsou potřebné pro další práci, protože s rostoucím
počtem importovaných dat, rostou také nároky na HW požadavky počítače.
Při generování OLAP reportů je rovněž vhodné importovat data pro jednu nebo
několik málo obcí a pro konkrétní ukazatel, jehož vývoj se má sledovat. Takové omezení
urychlí postup zpracování OLAP kostek, ale zároveň omezí realizaci OLAP operací, jako je
například pivoting.
V poslední kapitole práce je popsána tvorba mapových výstupů s využitím SPSS a
modulu Maps. K automatizaci generování statistických map se využívá psaný kód – syntaxe.
59
Nevýhodou takto vytvářených map je nutná následná úprava vygenerovaných statistických
map, protože syntaxe neobsahuje takové možnosti nastavení, jako v grafickém rozhraní. Po
vygenerování mapy je tedy nutná její úprava. Při opakovaném spuštění syntaxe se ve
vygenerovaném výstupu nezobrazí titul mapy, který byl v syntaxi nadefinovaný, ale zobrazí
se titulek s textem „Custom Map Title“. Tento problém je nevyřešen. Další nevýhodou je
absence měřítka, směrovky, tiráže a podtitulu. Výsledné mapy nelze charakterizovat jako
plnohodnotné mapové výstupy.
Vytvořená multidimenzionální databáze umožňuje ukládání dat z GIS statistik a může
sloužit jako výchozí zdroj dat pro generování OLAP reportů a statistických map v prostředí
SPSS.
60
LITERATURA:
[1] Horáková B. – Horák J. Nástroje prostorové analýzy trhu práce v činnosti úřadů práce. http://gis.vsb.cz/gacr_mtp/Clanky/SEC2002PAN.pdf
[2] Humphries Mark a kol.: Data warehousing – návrh a implementace. Computer Press, 2002, ISBN 80-7226-560-1, 257 stran.
[3] Kaňok, J. 1999. Tematická kartografie. Ostrava : Ostravská univerzita v Ostravě. 318 s. vyd. 1. ISBN 80-7042-781-7.
[4] Kočíková E.: Databázové systémy. Trenčianska Univerzita Alexandra Dubčeka v Trenčíne. www.kocikova.tnuni.sk/databazy.php
[5] Kraj Vysočina: Dokumenty odborů krajského úřadu.www.kr-vysocina.cz/vismo/zobraz_dok.asp?u=450008&id_org=450008&id_ktg=116382&archiv=0&p1=0&p2=&p3=
[6] Martin, D.: Geographic information systems and their socioeconomic applications. New York, Routledge, 1991, 182 s., ISBN 0-415-05698-5
[7] Matiaško K.: Databázové systémy. Žilinská universita, nakladatelství EDIS, 2002, ISBN 80- 7100-968-7, 367 stran.
[8] Ministerstvo informatiky České republiky: Vyhláška č. 469/2006 Sb., o informačním systému o datových prvcích. www.micr.cz/isvs/default.htm
[9] OKpráce: Základní část Informačního systému služeb zaměstnanosti. www.portal.oksystem.cz
[10] Pirkl D.: Tvorba datových skladů – Pohled zevnitř. Konference Datakon. Brno 2004. www.datakon.cz/datakon04/d04_it_pirkl.pdf
[11] Pokorný J.: Databázová abeceda. Science 1998, ISBN 8086083-02-0, 240 stran. [12] Pokorný J.: Konstrukce databázových systémů. Vysokoškolská skripta ČVUT, Praha
2004. [13] Portál veřejné správy České republiky. Obec Huzová.
www.portal.gov.cz/wps/portal/_s.155/696?kam=obec&kod=597414 [14] SPSS: Základní informace o produktu SPSS. www.spss.cz [15] Vítek: Datové sklady a OLAP 2002.
http://datamining.xf.cz/view.php?cisloclanku=2002102808 [16] Vítek: Uložení dat v OLAP systémech.
http://datamining.xf.cz/view.php?cisloclanku=2002102810
61
SEZNAM TABULEK A OBRÁZK Ů
Seznam tabulek
Tabulka č.1: Popis šablon pro export z OKpráce 3 Tabulka č.2: Obsah listu OKpráce 4 Tabulka č.3: Obsah listu Ukazatelé 5 Tabulka č.4: Typy vzdělání 24
62
Seznam obrázk ů
Obrázek č.1: Výpočet ukazatelů 4 Obrázek č.2: Ukázka geografických dat 5 Obrázek č.3: Ukázka geografických dat v prostředí MapInfo 6 Obrázek č.4: Multidimenzionalita [9] 8 Obrázek č.5: Hvězdicové schéma [9] 9 Obrázek č.6: Dimenzionální krychle – 2 dimenze 10 Obrázek č.7: Multi - Dimenzionální krychle – 3 dimenze 10 Obrázek č.8: Normalizovaná tabulka produkt 11 Obrázek č.9: Multi – OLTP Vs. OLAP [7] 12 Obrázek č.10: Editor dat - Data view 15 Obrázek č.11: Definice dat - Variable view 15 Obrázek č.12: Tabulka čas 18 Obrázek č.13: Převodní tabulka TAB_IDCAS 18 Obrázek č.14: Tabulka ekonomických proměnných 18 Obrázek č.15: Tabulka vzdělání 18 Obrázek č.16: Tabulka obce 19 Obrázek č.17: Tabulka M:N 19 Obrázek č.18: Faktorová tabulka 20 Obrázek č.19: Tabulka DTAB_VYHOTOVIL 20 Obrázek č.20: Schéma navržené databáze 20 Obrázek č.21: Nastavení exportu skriptu 21 Obrázek č.22: Vkládání dat 22 Obrázek č.23: Naplnění tabulky DTAB_CAS 23 Obrázek č.24: Naplnění tabulky DTAB_EKONOMPROM 24 Obrázek č.25: Naplnění tabulky DTAB_OBEC 25 Obrázek č.26: Naplnění tabulky FTAB_HODNOTA 26 Obrázek č.27: Formuláře pro import dat 26 Obrázek č.28: Struktura importovaného souboru 27 Obrázek č.29: Dialogové okno pro import 27 Obrázek č.30: Okno pro zadávání ID pracovníka 28 Obrázek č.31: Informace o počtu vkládaných dat 28 Obrázek č.32: Import databáze do SPSS 29 Obrázek č.33: Výběr tabulek pro import 29 Obrázek č.34: Stanovení vztahů tabulek 30 Obrázek č.35: Stanovení kritérií importu 30 Obrázek č.36: Ukázka naimportovaných dat 31 Obrázek č.37: Ukázka naimportovaných dat 31 Obrázek č.38: Ukázka naimportovaných dat 32 Obrázek č.39: Nabídka Celest Cases 33 Obrázek č.40: Výsledný import zvolené územní struktury 33 Obrázek č.41: Vytvoření základního souboru s daty 34 Obrázek č.42: Nastavení kritérií pro import – základní soubor 35 Obrázek č.43: Vytvořený základní soubor 35 Obrázek č.44: Ukázka třídění Sort Ascending 36 Obrázek č.45: Nastavení kritérií pro import – základní soubor 36 Obrázek č.46: Nastavení vah 37 Obrázek č.47: Menu Add Variables… 37
63
Obrázek č.48: Nastavení přidávání nové proměnné 38 Obrázek č.49: Ukázka připojených vah 38 Obrázek č.50: Výběr dimenzí a faktů 39 Obrázek č.51: Report OLAP kostky 39 Obrázek č.52: Pivoting Trays 40 Obrázek č.53: Ukázka report OLAP I. 40 Obrázek č.54: Ukázka reportu OLAP II. 41 Obrázek č.55: Ukázka reportu OLAP III. 41 Obrázek č.56: Chybové hlášení 42 Obrázek č.57: Ukázka funkce Drill-down 42 Obrázek č.58: Ukázka vývoje míry nezaměstnanosti 43 Obrázek č.59: Editor syntaxe 45 Obrázek č.60: Spuštění syntaxe 46 Obrázek č.61: Základní část syntaxe 47 Obrázek č.62: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru
– Sloupcový strukturní kartodiagram 49 Obrázek č.63: Kruhový strukturální kartodiagram – GRADUATED = YES 50 Obrázek č.64: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru
– Kruhový strukturní kartodiagram 50 Obrázek č.65: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru
– Kartodiagram stupňovaných symbolů 51 Obrázek č.66: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru
– Kartogram individuálních hodnot 53 Obrázek č.67: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru
– Kartogram zařazení do tříd 54 Obrázek č.68: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru
– Tečkový kartogram s náhodným umístěním teček 55 Obrázek č.69: Mapový výstup vygenerovaný pomocí syntaxe a upraven v editoru
– Kartogram zařazení do tříd se sloupcovým kartodiagramem 56
64
Seznam p říloh
Příl. 1 Zdrojový kód algoritmu pro import dat do tabu lky fakt ů
Příl. 2 Zdrojový kód algoritmu pro import ukazatelů do tabulky DTAB_EKONOMPROM
PŘÍLOHY
Příl. 1 Zdrojový kód algoritmu pro import dat do tabulky faktů
Private Sub P říkaz0_Click() 'deklarace promennych Dim iCount, mCount, i, j, k, l, u_k, u_l, r, pocr, pocs, u_pocr, u_pocs, f As Integer Dim vrecs, popis, ukazat, arr As Variant Dim ekonomprom(1 To 200) As String Dim idobce(1 To 200) As String Dim pocFld(1 To 1000) As String Dim strFileName, datum As String Dim rs, rs2, rs3 As ADODB.Recordset Dim cn As ADODB.Connection Dim fld As ADODB.Field Dim strSQL(1 To 10000) As String Set cn = New ADODB.Connection Dim db As Database Set db = CurrentDb() Dim dlg As FileDialog Set dlg = Application.FileDialog(msoFileDialogOpen) 'Nacteni OpenFileDialogu With dlg .ButtonName = "Otev ři" .InitialFileName = "*.xls" .Filters.Add "Soubory typu MS Excel", "*.xls" .Title = "Vybete soubor pro import" If .Show = -1 Then For Each arr In .SelectedItems 'MsgBox "The path is: " & arr strFileName = arr Next Else MsgBox "Stiskli jste tla čítko Storno" End If End With On Error GoTo chyba 'Spojeni se souborem MS Excel cn.Provider = "Microsoft OLE DB Provider for ODBC D rivers" cn.ConnectionString = "DRIVER={Microsoft Excel Driv er (*.xls)};DBQ=" & strFileName & ";" cn.Open Set rs = New ADODB.Recordset Set rs2 = New ADODB.Recordset Set rs3 = New ADODB.Recordset rs.CursorLocation = adUseClient rs2.CursorLocation = adUseClient rs3.CursorLocation = adUseClient rs.Open "SELECT * FROM [OKpráce$]", cn rs2.Open "SELECT * FROM [popis$]", cn rs3.Open "SELECT * FROM [Ukazatelé$]", cn vrecs = rs.GetRows popis = rs2.GetRows ukazat = rs3.GetRows 'Zadavani ID pracovnika id_pracovnika: id_prac = InputBox("Zadej ID pracovníka(osobní číslo)") If id_prac = "" Then
MsgBox ("Stiskli jste storno nebo jste nezadali ID pracovnika") GoTo id_pracovnika End If 'Pocet radku a sloupcu v listu OKPráce For k = 1 To (UBound(vrecs, 2)) If vrecs(0, k) = "Ostatní" Then GoTo skoc ElseIf vrecs(0, k) = "Okres celkem" Then GoTo skoc ElseIf vrecs(0, k) = " " Then GoTo skoc Else pocr = pocr + 1 End If Next skoc: For l = 2 To UBound(vrecs, 1) pocs = pocs + 1 Next 'Pocet radku a sloupcu v listu Ukazatelé For u_k = 1 To (UBound(ukazat, 2)) If ukazat(0, u_k) = "Ostatní" Then GoTo skoc2 ElseIf ukazat(0, u_k) = "Okres celkem" Then GoTo skoc2 ElseIf ukazat(0, u_k) = " " Then GoTo skoc2 Else u_pocr = u_pocr + 1 End If Next skoc2: For u_l = 2 To UBound(ukazat, 1) u_pocs = u_pocs + 1 Next 'Spocita pocet ukazatelu a vlozi je do pole For j = 3 To UBound(popis, 2) If popis(0, j) <> 0 Then iCount = iCount + 1 ekonomprom(iCount) = popis(0, j) End If Next For Each fld In rs.Fields x = x + 1 pocFld(x) = fld.Name Next 'Nalezeni podretezce datum startPos = InStr(pocFld(1), ", ") + 2 endPos = InStrRev(pocFld(1), "(") - 1 delkaDatumu = endPos - startPos datum = Mid(pocFld(1), startPos, delkaDatumu) For r = 1 To pocr
If vrecs(1, r) <> 0 Then idobce(r) = vrecs(1, r) End If Next 'Pocet zaznamu z tabulky OKpráce For j = 1 To pocs For i = 1 To pocr If i = 1 And j = 1 Then mm = vrecs(0, 0) End If poci = poci + 1 Next Next 'Pocet zaznamu z tabulky Ukazatelé For u_j = 1 To u_pocs For u_i = 1 To u_pocr u_poci = u_poci + 1 Next Next MsgBox "Vložili jste " & poci & " hodnot z listu OK práce (" & pocr & ": řádk ů, " & pocs & ":sloupc ů) a " & u_poci & " hodnot z listu Ukazatelé (" & u_pocr & ": řádk ů, " & u_pocs & ":sloupc ů)." 'Vlozi hodnoty z listu OKprace do tabulky faktu For j = 1 To pocs For i = 1 To pocr s = s + 1 strSQL(s) = "INSERT INTO FTAB_HODNOTA(ID_CA S, ID_EKONOMPROM, ID_OBCE, HODNOTA, POZNAMKA, ID_PRAC) Values('" & da tum & "','" & ekonomprom(j) & "','" & idobce(i) & "','" & vrecs(j + 1, i) & "', '" & Date & "', '" & id_prac & "' ) " Next Next For s = 1 To poci db.Execute (strSQL(s)) Next 'Vlozi hodnoty z listu Ukazatele do tabulky faktu For c = 1 To u_pocs For d = 1 To u_pocr u_s = u_s + 1 strSQL(u_s) = "INSERT INTO FTAB_HODNOTA(ID_ CAS, ID_EKONOMPROM, ID_OBCE, HODNOTA, POZNAMKA, ID_PRAC) Values('" & da tum & "', '" & ekonomprom(c + pocs) & "','" & idobce(d) & "','" & ukazat(c + 1, d) & "', '" & Date & "', '" & id_prac & "' ) " Next Next For u_s = 1 To u_poci db.Execute (strSQL(u_s)) Next MsgBox "Import dat prob ěhl úsp ěšně" db.Close rs.Close cn.Close konec: Exit Sub chyba: MsgBox Err.Number & " " & Err.Description End Sub
Příl. 2 Zdrojový kód algoritmu pro import ukazatelů do tabulky DTAB_EKONOMPROM
Private Sub P říkaz0_Click() 'deklarace promennych Dim iCount, mCount, i, j, k, l, u_k, u_l, r, pocr, pocs, u_pocr, u_pocs, f As Integer Dim popis, arr As Variant Dim strFileName, datum As String Dim rs As ADODB.Recordset Dim cn As ADODB.Connection Dim fld As ADODB.Field Dim strSQL(1 To 3000) As String Set cn = New ADODB.Connection Dim db As Database Set db = CurrentDb Dim dlg As FileDialog Set dlg = Application.FileDialog(msoFileDialogOpen) 'Nacteni OpenFileDialogu With dlg .ButtonName = "Otev ři" .InitialFileName = "*.xls" .Filters.Add "Soubory typu MS Excel", "*.xls" .Title = "Vybete soubor pro import" If .Show = -1 Then For Each arr In .SelectedItems 'MsgBox "The path is: " & arr strFileName = arr Next Else MsgBox "Stiskli jste tla čítko Storno" End If End With On Error GoTo chyba 'Spojeni se souborem MS Excel cn.Provider = "Microsoft OLE DB Provider for ODBC D rivers" cn.ConnectionString = "DRIVER={Microsoft Excel Driv er (*.xls)};DBQ=" & strFileName & ";" cn.Open Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open "SELECT * FROM [popis$]", cn popis = rs.GetRows 'Spocita pocet ukazatelu For j = 1 To UBound(popis, 2) If popis(0, j) <> "" Then iCount = iCount + 1 End If Next 'Spocita celkovy pocet vkladanych hodnot For a = 1 To UBound(popis, 2) For b = 1 To UBound(popis, 1) pocet = pocet + 1 Next Next
For l = 0 To UBound(popis, 1) pocs = pocs + 1 Next 'Vlozi hodnoty z listu Popis do tabulky DTAB_EKONOM PROM For b = 1 To iCount - 2 s = s + 1 strSQL(s) = "INSERT INTO DTAB_EKONOMPROM(ID_EKO NOMPROM, NAZEVPROM, ZPUSVYPOCTU, ZKRACNAZEV) Values('" & popis(0, b + 2 ) & "','" & popis(1, b + 2) & "','" & popis(2, b + 2) & "','" & popis(3, b + 2) & "' ) " Next For s = 1 To iCount - 2 db.Execute (strSQL(s)) Next MsgBox "Po čet importovaných záznam ů z listu Popis je:" & (iCount - 2) db.Close rs.Close cn.Close Exit Sub chyba: MsgBox Err.Number & " " & Err.Description End Sub
