Případová studie aplikace ETL procesů v bankovním prostředí

Bankovní institut vysoká škola Praha Katedra informačních technologií

Případová studie aplikace ETL procesů v bankovním prostředí Bakalářská práce

Autor:

Ondřej Spálenka informační technologie, správa informačních systémů

Vedoucí práce:

Praha

Ing. Michal Valenta, Ph.D.

duben, 2009

Prohlášení: Prohlašuji, že jsem bakalářskou práci zpracoval samostatně a s použitím uvedené literatury.

V Praze, 14. dubna 2009

Ondřej Spálenka

-i-

Anotace Práce v teoretické části pojednává o cestě dat obchodní organizací. Během této cesty se z dat stávají informace, z informací znalosti a ze znalostí nové obchodní příležitosti. Během této cesty jsou data neustále přesouvána a transformována ze systému do systému. Tato práce představuje různé technologické možnosti, jak data získávat a transformovat, s konkrétním zaměřením na fázi importu dat do databáze MS SQL. Annotation The work in the theoretical part discusses the journey of the data through commercial organizations. A data is changing to information during this journey and from information to knowledge and the knowledge is base of new business opportunities. During this journey the data is constantly moved and transformed form one system to another. This work presents various technological options for data access and transformation, with a particular focus on the stage of importing data into MS SQL database.

Klíčová slova ETL, datamining, Business Inteligence, MSSQL, datawarehousing, případová studie, bankovnictví

- ii -

Obsah Úvod .................................................................................................................................... 1 Metodika.............................................................................................................................. 2 1

2

3

Specifikace zadání ....................................................................................................... 3 1.1

Přehled architektury............................................................................................. 3

1.2

ETL...................................................................................................................... 4

1.3

Extraction ............................................................................................................ 4

1.4

Transformation .................................................................................................... 5

1.5

Load ..................................................................................................................... 6

1.6

Datový sklad ........................................................................................................ 6

1.7

Datamart .............................................................................................................. 8

1.8

Architektura ADM............................................................................................... 9

1.9

Datamining a Business Intelligence .................................................................. 11

1.10

Řízení cílených marketingových kampaní ........................................................ 13

Seznámení s technologií ............................................................................................ 14 2.1

SQL Server Information Services ..................................................................... 14

2.2

MS SQL Management Studio ........................................................................... 17

2.3

bcp.exe............................................................................................................... 18

2.4

BULK INSERT ................................................................................................. 19

2.5

Přímé připojení na externí datový zdroj ............................................................ 19

2.6

Import dat pomocí průvodce ............................................................................. 21

2.7

Import dat pomocí SQL skriptu......................................................................... 22

2.8

Reportovací nástroj MSSQL Reporting services............................................... 22

Možnosti realizace, konkrétní příklady ..................................................................... 23 3.1

Měření................................................................................................................ 23

3.2

Server................................................................................................................. 24

3.3

Výsledky měření................................................................................................ 25

3.4

Komentáře k jednotlivým metodám importu .................................................... 25

3.5

Paralelní zpracování .......................................................................................... 29

4

Užitečné postupy, nejlepší zkušenosti ....................................................................... 31

5

Zhodnocení ................................................................................................................ 34

6

Literatura ................................................................................................................... 35

7

Obsah přiloženého CD .............................................................................................. 36

- iii -

8

SQL skripty ............................................................................................................... 37 8.1

Skripty pro vytvoření prostředí a testovacích dat.............................................. 37

8.2

Skripty pro testování importu ............................................................................ 43

- iv -

Úvod Rozvoj výpočetní techniky v posledních letech přináší nové možnosti pro podniky a firmy. Výpočetní výkon dnešních počítačů a nízká cena datových úložišť umožňují zpracovávat a analyzovat velké objemy dat a hledat v nich dosud nepoznané závislosti a vztahy. Velké firmy potřebují pro udržení svého zisku a udržení kroku s konkurencí lépe poznat své klienty a nabídnout jim produkty, které budou klientům více vyhovovat. Jinak řečeno, potřebují vytvářet takové nabídky, které efektivněji přesvědčí klienty o kvalitách nabízených produktů. Stále častěji se v dnešní době setkáváme s pojmem datamining a to v různých oblastech. Hlavně velké společnosti, které disponují obrovským množstvím dat, aplikují dataminigové metody k hledání skrytých souvislosti a netriviálních závislostí v datech. Konkrétním příkladem takové aplikace v bankovnictví může být model predikující sklon klienta k odchodu (churn) či sklon klienta ke koupi určitého produktu (propensity-to-buy). Vytváření a přesné cílení marketingových kampaní vyžaduje rychle dostupná data a přesné analýzy. Kvalita výsledků není dána jen dostupnou technologií a výpočetním výkonem, ale také odbornými znalostmi a profesionalitou analytiků. Znalost dat je stejně významná jako jejich kvalita a technické znalosti jsou jen nástrojem pro obchodní úspěchy celé společnosti. Pro architekturu a budování datových skladů vzniklo nové odvětví informatiky – Datawarehousing (DWH). Analýzou dat a hledáním závislostí se zabývá Datamining (DM). Také se těmto systémům říká Systémy pro podporu rozhodování či Business Inteligence (BI). (Rys, 2009) Na základě mé osobní zkušenosti bude má práce v praktických příkladech vycházet z konkrétní situace v Československé obchodní bance. Předmětem této práce je popis a porovnání mechanismů a technik, které se ukázaly jako nejvhodnější či nejpohodlnější pro datovou a analytickou podporu marketingových kampaní. Pohybovat se budeme hlavně na platformě MS SQL a dalších produktech firmy Microsoft jako technologii, na které je Analytický datamart v ČSOB vybudován.

-1-

Metodika Předkládaná práce je logicky rozčleněná do tří oblastí. V teoretické části popisuji procesy, kterými putují data obchodní organizací. Data vznikají v primárních systémech, procházejí datovým skladem, specializovanými datamarty, na nichž je založena Business Inteligence, a končí marketingovým oslovením, které má, v případě úspěchu, za následek odezvu zpátky v primárním systému v podobě zaznamenaného prodeje. Ve druhé části popisuji nástroje vhodné pro ETL procesy na platformě Microsoft. V třetí, praktické části, uvádím konkrétní příklady užití jednotlivých metod založené na vlastních zkušenostech. V závěru této části hodnotím použitelnost jednotlivých metod pro různé typy úloh. Metody budu porovnávat z hlediska časové náročnosti na přípravu a spuštění procesu a náročnosti na strojový čas. Výsledkem je jednoduchý report, který zobrazí výsledky měření pomocí technologie MS SQL Reporting Services. Práce obsahuje datovou přílohu na CD, kde jsou zdrojové kódy použitých skriptů a reportů. Veškerá importovaná data jsou vygenerována náhodně. Rozhodl jsem se jednotlivé SQL skripty obsahující testované metody importu vložit i do textu této práce, aby zvídavý čtenář měl nejdůležitější části práce v jednom dokumentu..

-2-

1 Specifikace zadání 1.1

Přehled architektury

Obchodní společnosti obyčejně a banky obzvláště, ke své činnosti potřebují a zároveň při své činnosti produkují velké množství dat. Tato data vznikají v různých informačních systémech. Každá specifická oblast činností banky má svůj podpůrný informační systém. Jedná se o systémy jako zpracování účetních operací, přímé kanály (internetové bankovnictví), karetní operace, obchody s cennými papíry nebo prodej produktů dceřinných společností jako jsou penzijní fondy, stavební spoření či životní pojištění. Nemusí se jednat jen o ryze obchodní systémy, neopomenutelným systémem je též systém pro řízení vztahu s klientem (CRM). Všechny tyto datové zdroje jsou primární systémy pro než se používá termín OLTP (On-Line Transactional Processing) (Šanda, 1999). Všechny tyto systémy jsou zdrojem cenných dat. Ovšem nejsou to jen vnitřní systémy, které nás zajímají. Dalšími zdroji dat jsou veřejné databáze státních institucí jako je ČNB, která vydává kurzovní lístek a sazby pro obchodování s penězi na mezibankovním trhu, Ministerstvo spravedlnosti (jako správce obchodního a insolvenčního rejstříku1), Ministerstvo práce a sociálních věcí (jako správce registru adres a objektů v ČR) či Český statistický úřad. Dalším významným zdrojem mohou být různé komerčně dostupné databáze a analýzy. Provádění analýz přímo v databázích produkčních systémů není možné z mnoha důvodů. Takové systémy nebyly obyčejně projektovány s ohledem na analytické či reportovací potřeby. Nevhodně či chybně položený dotaz nad produkční databází může nepřiměřeně zatížit celý systém v době, kdy je požadována rychlá odezva na obchodních místech. Pro efektivní práci s tolika různými a různorodými zdroji je třeba mít k dispozici nějakou sjednocující vrstvu. Sjednocující vrstvou je datový sklad. Datový sklad – datawarehouse – je systém pro uchování dat ze všech výše uvedených zdrojů na jednom místě, tak, aby data byla dále dostupná dalším odběratelům. Odběratelé data nezískávají přímo dotazy do skladu, ale prostřednictvím tzv. datamartů – datových tržišť. (kap.1.7) Což je zejména v bankovnictví vhodná vlastnost, protože počet a diverzita OLTP systémů v bankách je vyšší než jiných podnicích. Příčiny jsou jednak v jisté konzervativnosti v oboru bankovnictví, která se projevuje v chladnějším přístupu k novým technologiím a jednak 1

zákon č. 182/2006 Sb. O úpadku a způsobech jeho řešení (insolvenční zákon)

-3-

poměrně širokou škálou nabízených produktů, kdy každá rodina bankovních a pojišťovacích produktů je zpracovávána v jiném OLTP systému.

1.2

ETL

ETL je zkratka pro Extraction, Transformation, Load – procesy, které tvoří základ hromadného zpracování dat a datawarehousingu (DW). Extraction znamená získání dat z externího zdroje. Transformation je fáze, ve které data upravíme do vyhovujícího formátu. Transformovaná data poté ve fázi Load uložíme do cílové databáze. V praxi se často používá také termín datové pumpy. Pro rozsáhlé systémy s desítkami i stovkami zdrojů existují pro ETL specializované nástroje. Předmětem této práce je naopak popis jak data zpracovávat i bez těchto nástrojů, za použití software standardně dodávaného s MS SQL ve verzi 2005. Obrázek 1 – Schéma ETL procesu

1.3

Extraction

Extrakce je fáze ve které získáváme přístup ke zdrojům. Technické kroky, které je třeba projít, jsou například určení společného úložiště dat, dostupnosti, získání přístupových oprávnění a nastavení systému časování či spouští pro automatického zpracování. Dále je třeba popsat strukturu dat a významy jednotlivých atributů abychom věděli, jaká máme data a co znamenají. Zdrojová data mohou být obyčejné textové soubory, data uložená v XML, relačních či objektových databázových strojích, ale i výstupy z tabulkového procesoru nějaké kancelářské aplikace. Důležité je u každého zdroje definovat dostupnost, kvalitu a obchodní význam jednotlivých položek. Nedílnou součástí extrakce je i ověření, zda data jsou v očekávané struktuře a kvalitě. Případné nedostatky jsou zaznamenány do logů a nejsou zahrnuty do zpracování od počátku. Data, která projdou kontrolami kvality jsou nahrána do oblasti nazývané obvykle stage. To bývá samostatný databázový stroj, vyhrazený právě pro transformační procesy.

-4-

1.4

Transformation

Pokud jsou zdrojová data v běžné tabulkové struktuře, můžeme je použít tak, jak jsou, případně aplikovat jednoduché transformace. Jednoduché transformace jsou změna datového typu, či normalizace stavových atributů. Je vhodné například pro časové a datumové2 hodnoty používat namísto (mnohdy používaného) textového řetězce datový typ datetime,

nebo podobný, který každý databázový stroj nabízí. Normalizací stavových

atributů nazývám transformaci párových hodnot typu „ANO/NE“, „Y/N“ či „0/1“ na jeden z těchto párů a ten používat ve všech tabulkách. Transformační procesy přesunují a upravují data, která jsme pomocí Extraction nahráli do tzv. stage oblasti, dále do cílové databáze. I v této fázi dochází ke kontrole kvality dat. Chyby kvality dat můžeme rozdělit do několika kategorií. Největší množství chyb vzniká tam, kde zdrojem dat je lidská obsluha. To jsou chyby jako chybějící data, překlepy, či jiné než očekávané hodnoty. Další skupinou jsou chyby vzniklé nedostatečnou referenční integritou zdrojového systému, jako jsou duplicity či chybějící kategorie či položky z číselníků. Duplicity se mohou objevit jak na úrovní celých řádků (dva totožné řádky), tak na úrovni primárních klíčů (dvě různá jména k jednomu rodnému číslu). Chyby mohou také vznikat pokud nějaké hodnoty odvozujeme z jiných. (Dušek, 2008). Příkladem může být konsolidaci adres. V České republice existuje veřejný registr UIR-ADR3, což je seznam všech objektů, které mají domovní číslo. Můžeme se pokusit porovnat každou adresu, kterou máme v našich systémech s tímto oficiálním registrem. Tam, kde se to podaří získáme jistotu, že adresa existuje, kde se to nepodaří, předáme seznam klientů s chybně zadanou adresou k dalšímu řešení. Pro zpracování každé chyby se definuje mechanismus jak se zachovat při výskytu chyby. Definované chování může být na škále od vydání varování do logu, který bude následně zpracován organizační složkou podniku zabývající se kvalitou dat, až po havarijní zastavení celého procesu. K zastavení však musí docházet opravdu velmi výjimečně. Rozhodnutí, jak se zachovat, je z jedné strany 2

Lingvistická poznámka:

V dokumentu je důsledně použito skloňování podstatného jména datum, jako by toto podstatné jméno bylo rodu mužského (datum, datumu, datumu,…), přestože je autorovi dokumentu známo, že toto podstatné jméno je rodu středního se skloňováním datum, data, datu, …. Důvodem pro tuto odchylku je možná záměna při správném skloňování podstatného jména datum s pomnožným podstatným jménem data ve významu strukturované informace, které je v informatice, a tudíž i v tomto dokumentu, často používáno 3

Více informací o UIR-ADR na http://forms.mpsv.cz/uir/popis/popis.jsp

-5-

omezeno požadavkem co nejrychlejšího zpracování a z druhé strany mít ve skladu v maximální kvalitě.

1.5

Load

V poslední fázi se importují data do cílových tabulek. V předchozích krocích jsme data připravili do struktur, které jsou sodné s cílovými tabulkami. Proces plnění začíná od nových hodnot ve faktových tabulkách tak po nové záznamy v dimenzích tak, aby byla i v průběhu plnění zachována referenční integrita.

1.6

Datový sklad

Datové sklady byly od počátku budovány jako systémy pro podporu rozhodování. Vybudování datového skladu je činnost dlouhodobá a náročná technicky i organizačně. Nejcitovanějšími teoretiky (i praktiky) datových skladů jsou R. Kimball a W. H. Inmon (Rys, 2009) (Šanda, 1999) (Macháčová, 2003). Podle historicky staršího přístupu W. H. Inmona je datový sklad budován jako komplexní systém, kde jsou data z primárních systémů ukládána do univerzálních struktur. Zdrojová data jsou rozložena, transformována a uložena do databáze. Pro představu, návrh takového skladu obchodní organizace, může mít jako hlavní tři pilíře domény reprezentující klienty, obchody (či kontrakty, smlouvy) a transakce. Pro tyto pilíře se používá termín fakta. (Kučera, 2001) K těmto faktovým tabulkám jsou pak připojeny dimenze. Nalezení optimálních dimenzí je předmětem návrhu skladu a v každé organizaci mohou být jiné. Všechny dimenze jedné domény, ať už přicházejí z jakéhokoliv zdroje, jsou ukládána do stejných tabulek. Nevýhodou tohoto přístupu je velká časová náročnost a rozsáhlost analytické části projektu vývoje datového skladu. Výhodou je, že všechna data organizace jsou transformována do jednotné struktury. Jednotná definice dimenzí umožňuje snadné budování datamartů, konsolidaci a reporting.

-6-

Obrázek 2 – Schéma datového skladu budovaného jako komplexní systém s univerzální strukturou

zdroj: Rys, s. 12

Oproti tomu, podle přístupu R. Kimbala, je datový sklad budován jako vrstva, která z každého provozního systému vytváří rovnou datamart. Tyto datamarty jsou přístupné na jednotné technologické platformě. Výhodou takto navržené architektury je dostupnost dat brzy po rozhodnutí o vybudování skladu. Nevýhodou tohoto „datamartového“ přístupu je nezkonsolidovanost jednotlivých zdrojových systémů a jejich dimenzí a opakované provádění té samé práce při analýze nových zdrojů. (Rys, 2009) Například osoba vedená v jednom systému se může vyskytovat i v jiném systému, ale bez konsolidace se nedozvíme, že je to fyzicky tatáž osoba, která se nám jeví jako dva různí klienti. Zdánlivě jednoduchá otázka „Kolik máme klientů?“ nemá rychlou odpověď. Pokud by organizace měla mnoho různorodých primárních systémů, tak hrozí riziko, že náklady ušetřené dříve při analýze se spotřebují na sjednocení celopodnikových dimenzí. Obrázek 3 – Schéma datového skladu budovaného jako soubor datamartů z produkčních systémů.

zdroj: Rys, s. 13

Pokud použijeme obrazné přirovnání jiného oboru, tak Kimbalův model vypadá jako vesnička z mnoha malých domků. Každý domek reprezentuje jeden zdrojový systém a -7-

mezi těmito systémy se prošlapávají spojovací cesty. To architekt přistupující k datovému skladu jako W. H. Inmon všechny tyto domky rozebere na cihly a vystaví z nich datovou katedrálu. V prvním případě, pokud budeme chtít získat seznam klientů musíme data získávat „dům od domu“, v případě „katedrály“ máme všechny klienty shromážděné na jednom místě. Způsob uložení dat ve skladu zaručuje, že co do skladu nahrajeme, to tam zůstane. Žádná data se neodstraňují, pokud má dojít ke změně nějakého záznamu, pak se u původního nastaví konec platnosti a založí se nový. Centrální datový sklad (CDS) zprovozněný v ČSOB v roce 2005 je postaven na technologii Teradata a architektonicky vychází z definice podle W. H. Inmona. Datový sklad je tedy vybudován jako integrovaný soubor dat s časovým rozlišením, určený pro pomoc v rozhodovacích procesech (Rys, 2009) (Šanda, 1999). Ovšem jeho struktura není zmražena ke dni spuštění, ale dále se vyvíjí, jak se objevují nové zdrojové systémy a přibývají obchodní požadavky. CDS ukládá data z primárních systémů v určité periodě (denní) do svých struktur a nad těmito strukturami jsou dále vytvářeny datamarty (datová tržiště) podle potřeby a definice jednotlivých business uživatelů. Data jsou zpracovávána ve denních cyklech. Primární systémy po ukončení pracovního dne a denní uzávěrce zpřístupní svá data ETL procesu datového skladu, ten je načte, pročistí, zkonsoliduje a poté spočítá datamarty tak, aby byly v pravý čas dostupné.

1.7

Datamart

Datamart je podmnožinou dat z datového skladu, není to však podmínkou. Může obsahovat i data z jiných zdrojů. (Rys, 2009) Datamart je v podstatě samostatná databáze, která je přizpůsobena potřebám odběratele dat lépe než samotné jádro datového skladu. Obsahuje data předpočítaná či transformovaná do jednodušší struktury. Při budování datamartu je obvyklou metodou denormalizace a redundance dat. Cílem je rychlá odezva na dotazy, Technologie datového skladu není stavěna na velké množství dotazů s velmi krátkou dobou odezvy. Datamart takový požadavek splnit může. Jiný datamart může být počítán týdně či měsíčně, ale jeho vytvoření bude výpočetně náročné. Může vznikat agregací miliónů záznamů v mnoha časových vrstvách. Příkladem datamartů může být například systém pro hodnocení výkonnosti poboček, datamart pro výpočet výnosů klienta přes všechny primární systémy banky, nebo datamart určený pro podporu a exekuci cílených marketingových kampaní.

-8-

1.8

Architektura ADM

Metody BI se začaly v ČSOB používat již před cca 8 lety, kdy vznikla první generace tzv. Analytického datamartu (ADM). To je několik let předtím, než začal vznikat Centrální datový sklad (CDS). ADM tedy neměl na počátku svého vzniku jako svůj hlavní zdroj datový sklad, ale sám načítal data z primárních systémů. Z těchto důvodů není ADM postaven čistě podle architektury W. H. Inmona, jehož přístup by vyžadoval, aby veškerá data pro datamart pocházela z datového skladu. Takový stav nenastává kromě již zmíněného historického důvodu také proto, že obchodní procesy vyžadují rychlou reakci a implementaci nového zdroje, analýzy či nějakého obchodního nápadu. Obrazně řečeno situace na frontě se mění každý den a implementace nového datového zdroje v CDS trvá několik týdnů až měsíců, ne všechny datové zdroje jsou schopny splnit požadavky CDS na kvalitu a ne všechno má smysl v CDS skladovat. Tato „AdHoc“ data jsou pro ilustraci například data získaná nějakým expertním výběrem, data získaná z veřejných zdrojů, jako je Český statistický úřad, či datová reprezentace nějaké nové myšlenky, která stojí za otestování. Architektura ADM je otázkou neustálého vývoje. V době svého založení v letech 2002 a 2003 probíhalo zpracování v měsíčních periodách. Data byla dodávána z primárních systémů prostřednictvím textových extraktů. V roce 2006 byly některé datové pumpy transformovány tak, aby načítaly data přímo z datamartu vytvořeného pro tyto účely v CDS.

-9-

Obrázek 4 – Schema datových zdrojů Analytického datamartu

V roce 2006 jsme zvolili přístup aby, pokud je to možné, byl jako zdroj dat používán CDS. To má několik výhod oproti dosavadním způsobu: 1) CDS provede kvalitní čištění a konsolidaci dat. 2) Provozováním vlastních ETL procesů nad největšími zdroji dat bychom duplikovali práci, kterou CDS umí lépe. 3) Z měsíčního zpracování jsme přešli, u zdrojů kde to má smysl, na denní periodicitu. ADM musí být dostatečně robustní, aby bylo s jeho pomocí možno rychle a přesně otestovat hypotézy a či odpovídat na nejrůznější dotazy, které business uživatele každodenně napadají. Správa takového datamartu vyžaduje speciální režim v přístupu k technologiím. Ačkoliv se jedná o poměrně velkou databázi (velikost databázových souborů přesahuje 1,5TB), správa databázového serveru je prováděna v režimu práce, který se nazývá End-User-Computing. To mimo jiné znamená, že datový analytik pracující s databází je zároveň i správcem a může provádět operace jako je vytváření nových databází, změna datového modelu, vytváření nových indexů z důvodů optimalizace zpracování apod. Taková flexibilita je potřebná pro rychlou odezvu na přicházející požadavky. Nese s sebou ovšem jistou míru rizika, která je se spojením rolí správce databáze a uživatel databáze spojena.

- 10 -

Na základě takto pojatého přístupu získáváme za cenu většího rizika větší akceschopnost. Takový přístup vyžaduje odvahu jít do rizika a mít podporu ve vedení společnosti. Tato podpora se dá získat dobrými obchodními výsledky.

1.9

Datamining a Business Intelligence

Pojem datamining, doslova „dolování dat“ lze definovat jako disciplínu, která se zabývá hledáním skrytých hodnotných informací ve velkých objemech dat. Tyto nalezené informace lze poté používat jako zdroje pro manažerská rozhodnutí na různých řídících úrovních. Pokud porovnáme tradiční reporting (jako zdroj pro rozhodování) a datamining, tak reporting se dívá vždy jen na to, co bylo. Datamining se snaží odhadovat co bude. Datamining je jednou částí oblasti, která se označuje Business Inteligence (BI). BI potřebuje znát odpovědi na otázky typu „Kde máme otevřít novou pobočku?“ nebo „Které klienty a s jakou nabídkou máme oslovit, abychom efektivně využili náklady?“ Abychom na tyto otázky mohli najít odpověď potřebujeme mít k dispozici spoustu dat ta přetvořit na informace a informace na vědomosti. Obrázek 5 – Schéma metodiky CRISP-DM 1.0

zdroj: http://www.crisp-dm.org/Process/index.htm

Pro proces analýzy a nalezení závislostí existuje vícero metodik. V roce 1999 byla publikována metodika CRISP-DM 1.0 (Cross Industry Standard Process for Data Mining)4

4

V roce 2006 byl zahájen proces upgrade metodiky na verzi 2.0

- 11 -

a stala se nejrozšířenější metodikou DM vůbec. Byla vypracována pracovní skupinou složenou ze zástupců průmyslu (DaimlerChrysler), statistiků (SPSS) a zástupců specialistů na velké databáze (NCR-Teradata). Vzniklý dokument podrobně popisuje jednotlivé kroky, které je třeba projít, aby se analytik dobral použitelných výsledků. Metody DM lze použít jak na obchodní úlohy, tak na řešení vědeckých či bezpečnostních problémů. Prvním krokem metodiky je Business Understandig, tj. porozumění zadání a datům v oblasti, kde se budeme pohybovat. Ve druhém kroku Data Understandig zjišťujeme a hledáne jaká data můžeme mít k dispozici. Máme-li datový sklad s popsanými dimenzemi a (fakty), máme část práce ušetřenou. Tato fáze se provádí v několika iteracích s první fází, abychom přesně porozuměli, co máme za data. Třetí fáze Data Preparation zabere 50 i více procent času na řešení celé úlohy. Při přípravě dat hledáme, která data použít, a která jsou zbytečná. Příprava dat znamená provádět takové operace jako je konsolidace, čištění a agregace. Připravená data pak vstupují do fáze „Modelování“, tato fáze také probíhá v bezprostřední iteraci s předchozím krokem. Modelování je založeno na hledání atributů jednotlivých pozorování, které nejlépe charakterizují úspěšná pozorování. Pro modelování se používá tzv. trénovaní vzorek. Tato trénovací skupina obsahuje i pozorování, u kterých nastal modelovaný jev a my se snažíme najít, .co mají tato úspěšná pozorování společného. Ve fázi evaluation pak ověříme výsledky na testovacím vzorku jiných klientů. Pokud má model dobré výsledky nastane fáze deployment. Ta zahrnuje výklad výsledků a implementaci do obchodních procesů. S implementací obchodních procesů mohou být potíže. Konkrétně při implementaci modelů propensity-to-buy5 to není jen technická práce, ale i práce s lidmi, které je třeba přesvědčit, že modely fungují a mohou jim pomoci v obchodní činnosti. Někdy může být interpretace modelu komplikovaná až nemožná, například pokud je použitou metodou modelování neuronová síť. V takovém případě se ukázalo jako vhodné vytvořit ještě jiný model založený například na interpretačně jednodušší regresní analýze. Po fázi publikování modelu a jeho integrace do obchodních procesů můžeme vstoupit poučeni do dalšího cyklu vytvořit nějaký další model.

5

Propensity-to-buy se překládá jako „sklon k nákupu“. Oslovování klientů s vysokým skóre sklonu k nákupu

zvyšuje úspěšnost oslovení oproti oslovení náhodně vybrané skupiny.

- 12 -

1.10 Řízení cílených marketingových kampaní Dalším krokem evolučního vývoje je transformace části ADM na datamart přímo podporující cílené marketingové kampaně6. Ten je budován jako standardní datamart nad datovým skladem s tím, že pro „AdHoc“ potřeby bude používat tzv. sandbox. Sandbox je nástroj, kterým lze zpřístupnit sílu datového skladu každému uživateli. Umožňuje definovat prioritu dotazů, takže práce uživatele přímo na produkčním stroji neohrozí pravidelné zpracování. Každý uživatel může testovat své hypotézy přímo nad daty CDS (na těmi, ke kterým má právo přístupu). Pokud se hypotéza ujme lze ji zařadit do pravidelného zpracování v příslušném datamartu. Výrazně se tímto postupem zkrátí doba od formulace hypotézy po její ověření. Obsluha cílených marketingových kampaní klade na datamart další náročné požadavky, kromě požadavku na maximální přesnost, který nikoho nepřekvapí, přichází i požadavek na častější než denní periodicitu zpracování dat. Některých vstupů, jako hovory z Callcentra či kontakt klienta z pobočkou, je vhodné znát dříve než druhý nebo třetí den.

6

Cílená marketingová kampaň je taková, kde je klient přímo oslovován s nabídkou produktu poštou,

telefonicky či osobně při jednání na obchodním místě. Naproti tomu existují masové kampaně, kde komunikačním kanálem jsou média (tisk, rozhlas, televize, internet).

- 13 -

2 Seznámení s technologií MS SQL 2005 je moderní systém pro řízení báze dat, který disponuje několika nástroji pro datové přenosy. Jakousi vlajkovou lodí, primárním systémem je SQL Server Information Services (SSIS)7. SSIS je komponenta MS SQL serveru ve verzi 2005 a 2008, nahradila komponentu Data Transformation Services (DTS), která byla integrována ve starších verzích MS SQL. Další možností je použít řádkovou utilitu bcp.exe nebo rozhraní MS SQL Management. Jednotlivé možnosti popíši v následujících podkapitolách.

2.1

SQL Server Information Services

SSIS je platforma pro budování procesů a automatizované zpracování datových toků. SSIS je velmi dobře použitelný i pro automatizovanou údržbu databází, aktualizaci OLAP datových kostek a další úkoly. Pro jednotlivé úkoly se vytvářejí tzv. balíčky (packages), které je možno vytvořit v grafickém prostředí nebo pomocí průvodce (wizard). Každý balíček obsahuje entity jako databázové spojení, proměnné, úkoly, propojení úkolů, které určují, pořadí zpracování úkolů a reagují na handlery v případě událostí a chyb. Obrázek 6 – Formulář z průvodce vytváření SSIS balíčků

7

Dokumentace: http://msdn.microsoft.com/en-us/library/ms141026(SQL.90).aspx

- 14 -

Průvodce analyzuje strukturu a názvy cílových tabulek a pokud najde vhodnou tabulku rovnou ji nabídne jako cílovou. Dialog skrývající se za volbou „Edit Mappings“ umožňuje pole tabulky přemapovat, pokud nám navržené mapování nevyhovuje.

Pokud bychom importovali data do nové tabulky, je třeba mít na paměti, že vytvořený SSIS balíček obsahuje pro vytváření tabulky pouze příkaz DROP TABLE

CREATE TABLE.

Preventivní příkaz

v balíčku obsažen není, a tudíž při opětovném spuštění import havaruje. Pokud

hodláme balíček spouštět opakovaně, je nejvhodnějším řešením jej po vytvoření upravit tak, aby proces „Execute SQL Task“ ověřil zda cílová tabulka existuje. Obrázek 7 – Grafické rozhraní BI Development Studia pro vytváření SSIS balíčků

Vývoj a údržba balíčků se provádí v prostředí MS Visual Studia8 integrovanou komponentou nazvanou Business Intelligence Development Studio. Ta umožňuje celý balíček ladit po jednotlivých krocích a sledovat hodnoty proměnných.

8

MS Visual Studio je software pro design a vývoj SW aplikací. Jedná se o framework do něhož lze

integrovat množství komponent podle potřeby (vývoj desktopových aplikací, různé programovací jazyky, šablony pro SW inženýrství, komponenty pro podporu týmové práce, modelování procesů, atd.)

- 15 -

Z hlediska ETL je nejdůležitějším procesem „Data Flow Task“, který je určen k přenosu dat z jednoho místa na druhé. Data Flow Task může při přenosu dat provádět celou sadu operací: od jednoduché změny datového typu, přes agregace, automatické vytváření dimenzí až po spouštění dataminigových modelů. Obrázek 8 – Data Flow Task

Definice balíčku je uložena jako XML soubor, buď v souborovém systému nebo jako objekt v databázi. Tento způsob uložení umožňuje vytvoření vlastního systému pro vytváření nebo úpravu balíčků. Ať už jako soubor, nebo jako databázový objekt je SSIS balíček velmi jednoduše přenositelný z vývojového prostředí na testovací a produkční. Takové činnosti je možno provést přes grafické rozhraní BI, nebo pomocí řádkové utility dtutil.exe9.

9

dtutil.exe je utilita spouštěná z příkazové řádky určená ke správě SSIS balíčků. Balíčky je možno

přesouvat, mazat či ověřovat jejich existenci. Utilita se například použije v případě automatizovaného přesunu balíčku mezi servery nebo v případě změny hesla pro připojení k databázi

- 16 -

Obrázek 9 – Grafické rozhraní DTExecUI pro spuštění SSIS balíčku

Pro spouštění balíčku je možné použít vývojové prostředí Visual Studia, založit úlohu v SQL Server Agent10, grafické rozhraní DTExecUI.exe či utilitu příkazové řádky dtexec.exe.

Při spuštění lze balíček parametrizovat. Parametry mohou být například

název serveru, ke kterému se má připojit nebo časové určení, za které období má požadovat data. Parametr je možné předat různými způsoby. Balíček si může parametry přečíst z databáze nebo lze předat celý parametrizační XML soubor, který může měnit prakticky každý uzel i atribut XML definice balíčku.

2.2

MS SQL Management Studio

SQL Server Management Studio11 je integrované grafické uživatelské rozhraní pro přístup, konfiguraci, správu a vývoj všech komponent MS SQL Serveru. Pro každodenní práci jsou nejužitečnější komponenty Object Explorer, Solution Explorer a Template Explorer. Object Explorer je panel, který zobrazuje v přehledné stromové struktuře všechny objekty a ovládací prvky databázového stroje. Procházení databází, spouštění úloh, správa naplánovaných úloh – to jsou všechno činnosti, které se pohodlně dají dělat s pomocí tohoto panelu. Panel Solution Explorer svou sílu ukáže, pokud potřebujeme mít ve svých

10

SQL Server Agent je služba operačního systému určená k provádění úkolů, které potřebují být spouštěny

pravidelně či automatizovaně bez zásahu člověka. Může se jednat jak o SQL příkazy spojené s údržbou databázového stroje či datovými pumpami, tak o příkazy pro operační systém. Úkol se může skládat z více kroků a je možné definovat zpracování chyb a jejich notifikaci různými kanály. 11


- 17 -

skriptech pořádek. Template Explorer je zdroj šablon skriptů, které oceníte ve chvíli, kdy si nemůžete vzpomenout na syntaxi nějakého příkazu či nastavení. Případně sem můžete uložit své často používané dotazy, jako např. dotaz do aplikačního logu. Za nedostatek považuji nepřítomnost technologie IntelliSense („našeptávač kódu“), který by v průběhu psaní SQL dotazu nabízel relevantní příkazy a databázové objekty. Tato technologie je implementována až ve verzi MS SQL 2008. Obrázek 10 – MS SQL Management Studio

2.3

bcp.exe

Utilita bcp.exe je program spustitelný z příkazové řádky, který používá API rozhraní Bulk Copy Program. bcp.exe12 určený ke kopírování dat mezi instancí MS SQL serveru a textovým souborem. Formát textového souboru je možno uživatelsky definovat. Utilita bcp může být použita pro import i export tabulky nebo pohledu. Lze exportovat i výsledek nějakého SQL dotazu zadaného přímo jako parametr příkazu. Pro import dat do MS SQL serveru je třeba mít vytvořen k datovému souboru tzv. formátovací soubor, což je prostý textový soubor definující oddělovače sloupců, řádků a datové typy. Formátovací soubor lze

12

Dokumentace: http://msdn.microsoft.com/en-us/library/aa337544(SQL.90).aspx

- 18 -

vytvořit interaktivně i automaticky13 pomocí bcp.exe. Formátovací soubor může být od verze MS SQL 2005 i v XML formátu. Obrázek 11 – Příklad použití utility bcp.exe

Utilita bcp.exe bývá považována za nejrychlejší způsob přenosu dat mezi MS SQL serverem a CSV souborem. Ověření pravdivosti této hypotézy je jedním z výsledků této práce.

2.4

BULK INSERT

BULK INSERT je příkaz jazyka T-SQL určený i importu textového souboru do tabulky. Má podobné parametry jako bcp.exe. Používá i stejnou syntaxi formátovacího souboru. Ve výkonu však překvapivě za bcp.exe zaostává, jak dokazují výsledky měření níže.

2.5

Přímé připojení na externí datový zdroj

Tato kapitola hovoří o přímém připojení k datovým zdrojům, které je otvíráno v průběhu zpracování SQL dotazu. Název jakéhokoliv objektu používaného v MSSQL serveru se skládá ze čtyř částí,, přičemž jen poslední je povinný a oddělují se tečkou: [[[server.][databáze].][vlastník objektu].]název objektu

Externí datové zdroje představují server. Mohou být trvalé (Linked server, OPENQUERY) nebo otvírané ad hoc (OPENDATASOURCE, OPENROWSET).

13

Automatické generování formátovacího souboru je použito při generování testovacích dat ve skriptech

03_GenerateDataSample01.sql

a 04_GenerateDataSample02.sql v datové příloze této práce.

- 19 -

2.5.1 Linked servers „Linked server14“ je pojmenovaný zástupce externího datového zdroje. Umožňuje připojit se k jinému datovému zdroji přímo v rámci běžícího SQL dotazu a pracovat s datovým zdrojem podobně jako bychom jej měli přímo na svém serveru. Tím zdrojem může být cokoliv od Oracle, přes MS Access, po textový soubor. Stačí, aby datový zdroj měl implementováno rozhraní ODBC15 nebo OLE DB. Pokud má datové rozhraní implementováno příslušné metody, je možné provádět i příkazy INSERT, UPDATE a DELETE či spouštět procedury, pokud vzdálený server takové věci umí. Obrázek 12 - Schéma nastavení připojených serverů připojených přes OLE DB

zdroj: URL: http://msdn.microsoft.com/en-us/library/ms188279(SQL.90).aspx ze dne 5. 2. 2009

2.5.2 Připojení pomocí OPENQUERY OPENQUERY16 je příkaz jazyka T-SQL umožňující spustit SQL dotaz na vzdáleném datovém zdroji. Externí datový zdroj musí být předem nadefinován jako „Linked server“. Tímto způsobem lze na propojeném serveru spustit jakýkoliv SQL dotaz. Pokud vrátí více datasetů, OPENQUERY pracuje pouze s prvním. Tento dataset se pak v SQL chová jako by to byla tabulka.

14 15 16

Dokumentace: http://msdn.microsoft.com/en-us/library/ms188279(SQL.90).aspx http://support.microsoft.com/kb/110093 Dokumentace: http://msdn.microsoft.com/en-us/library/ms188427(SQL.90).aspx

- 20 -

2.5.3 Připojení pomocí OPENROWSET OPENROWSET17 je funkce jazyka T-SQL, která umožňuje pracovat s externím datovým zdrojem, který je připojen přes OLE DB data source. Je to přístup podobný jako Linked server s tím, že vytvořené spojení je jednorázové. Funkce OPENROWSET je volána ve FROM části SQL příkazu jako by to byla běžná tabulka.

2.5.4 Připojení pomocí OPENDATASOURCE OPENDATASOURCE je metoda podobná předchozí, jen se pro připojení ke vzdálenému zdroji používá tzv. Connection String.

2.6

Import dat pomocí průvodce

Z prostředí MS SQL Management studio je možno spustit průvodce pro import dat. Tento průvodce vytvoří SSIS balíček, který je možno ihned uložit a spustit. Pokud balíček uložíme, lze jej později editovat prostřednictvím BI Development Studia, případně spustit s novými parametry pomocí DTExecUI.exe. Obrázek 13 – Ilustrace úvodního dialogu průvodce importem dat

17


- 21 -

2.7

Import dat pomocí SQL skriptu

Poslední metodou, kterou chci zmínit, je import pomocí řady příkazů INSERT. Pro tento spíše výjimečný způsob používám šablonu v programu MS Excel, která pomůže vytvořit nejen řadu příkazů INSERT, ale i příslušný příkaz CREATE TABLE pro vytvoření cílové tabulky (SQLINSERT_template.xls). Vytvořenou řadu příkazů je třeba přenést do okna Management studia a tam spustit.

2.8

Reportovací nástroj MSSQL Reporting services

Na závěr této kapitoly zmíním nástroj MS SQL Reporting services (MSRS), který jsem použil k zobrazení výsledků měření jednotlivých metod. MSRS je nástroj dodávaný jako doplněk k MS SQL serveru od verze 2000. Jedná se o webovou aplikaci určenou k vytváření a doručování reportů. Reporty mohou být publikovány přes web, emailem či uloženy do souborového systému, formát dokumentu je také variabilní. Od formátu HTML, přes dokument MS Excelu až po PDF. Reporty jsou definovány jazykem Report Definition Language (RDL), což je jazyk založený na XML. Hlavní prvky, které report obsahuje jsou: •

definice grafické podoby reportu,

•

definice polí ke zobrazení

•

parametry a připojení k databázi.

K vytvoření reportů se používá MS Visual Studio s příslušnou komponentou (stejně jako pro vytváření SSIS balíčků či jakéhokoliv jiného vývoje na platformě Microsoft). Obrázek 14 – Webové rozhraní k repotům v MS SQL Reporting Services

- 22 -

3 Možnosti realizace, konkrétní příklady 3.1

Měření

Hodnocení různých způsobů přesunu dat má mnoho stupňů volnosti. Rychlost zpracování není jediným kritériem. Dalšími kritérii může být uživatelské pohodlí, či použitelnost v daném prostředí. Pro měření času zpracování jednotlivých způsobů importu jsem vytvořil systém SQL skriptů. Tyto skripty jsou volány v cyklu pro lepší porovnatelnost výsledků. Každý skript zapisuje údaje o své činnosti do logovací tabulky a nad touto tabulkou je vytvořen pomocí technologie MS SQL Reporting Services jednoduchý report, který zobrazí průměrné hodnoty jednotlivých metod měření. Pomocí skriptů 03_GenerateDataSample01.sql a 04_GenerateDataSample02.sql

jsem postupně vytvořil čtyři soubory náhodných dat.

Pro technologická omezení Excelu a sady SQL INSERTů jsem použil sady menší. Tabulka 1 – Přehled testovacích dat sada

sloupců

řádků

objem

DataSample01 („krátká datová věta“)

8

1048576

172 MB

DataSample01 („krátká datová věta“)

8

16777216

2.73 GB

DataSample02 („dlouhá datová věta“)

32

524288

206 MB

DataSample02 („dlouhá datová věta“)

32

4194304

1.61 GB

Použitá metoda volání skriptů vnáší do měření vlastní chybu. Je způsobena programem isqlw.exe18,

který potřebuje nějaký čas na inicializaci v paměti počítače. Tento čas je

však zanedbatelný vzhledem k časům, které měříme. Další omezení metodiky se ukáže v případě, že bychom chtěli testovat zda a jaké bude zlepšení v případě spuštění procesů paralelně. Spouštět z běžícího skriptu paralelně procesy a pak čekat až všechny doběhnou nelze. Pro účely otestování, že paralelní zpracování je rychlejší jsem vytvořil balíček paralel.dtsx,

který ve čtyřech paralelních procesech zpracuje data metodou bcp a SSIS

Data Flow Task.

18

isqlw.exe

(SQL Query Analyzer) je program spustitelný jak s grafickým rozhraním, tak z příkazové

řádky. Je vhodný pro spouštění SQL skriptů uložených ve filesystému.

- 23 -

Pro hodnocení použitelnosti jednotlivých metod je obtížné stanovit objektivní měřítka. Mým měřítkem tedy bude má vlastní zkušenost a komentář, kde je která metoda použitelná. Laskavý čtenář nechť sám posoudí, která metoda bude pro něj použitelná.

3.2

Server

Konfigurace serveru použitého pro testování: •

4 dvoujádrové procesory Intel Xeon, 3,66MHz

•

Taktovací frekvence 3,66 MHz

•

Velikost paměti RAM: 16GB

•

Disková pole SAN s RAID5

•

OS: MS Windows Server 2003

•

Databáze: Microsoft SQL Server 2005 - 9.00.3042.00

- 24 -

3.3

Výsledky měření

Výsledky měření jednoduše zobrazí vytvořený report nad logovací tabulkou. Tabulka 2 – Průměrné doby potřebné k importu dat

skript

3.4

8 columns, data 1048576 rows, 172MB

8 columns, 16777216 rows, 2.73GB

32 columns, 524288 rows, 206MB

32 columns, 8 columns, 8 columns, 4194304 10000 rows 32768 rows rows, 1.61GB

13_ImportBCP.sql

15.01 s

244.42 s

15.82 s

124.59 s

-

-

14_ImportSSISBul kInsert.sql

28.62 s

256.50 s

28.99 s

141.76 s

-

-

15_ImportSQLBulk Insert.sql

15.90 s

245.50 s

16.51 s

132.26 s

16_ImportSSISDat aFlowTask.sql

17.59 s

128.47 s

25.09 s

132.50 s

-

-

19_ImportLinkedS erverXLS.sql

-

-

-

-

-

18.99 s

20_ImportLinkedS erverTXT.sql

147.15 s

130.50 s

-

-

-

-

21_ImportTXTinto OneTable.sql

101.14 s

253.13 s

-

-

-

-

23_ImportSQLINS ERT.sql

-

-

-

-

135.35 s

-

24_ImportOPENRO WSET_TXT.sql

15.67 s

241.79 s

16.61 s

134.54 s

-

-

25_ImportOPENDA TASOURCE_XLS.sql

-

-

-

-

-

12.99 s

26_ImportOPENRO WSET_MDB.sql

84.72 s

-

-

-

-

-

Komentáře k jednotlivým metodám importu

3.4.1 Import pomocí bcp.exe Použito ve skriptu: 13_ImportBCP.sql

Tato utilita byla podle očekávání nejrychlejší.

- 25 -

Moje vlastní hodnocení pohodlnosti práce je spíše zdrženlivé. Syntaxe formátovacího souboru je málo intuitivní. Pokud však uživatel má rád příkazovou řádku, je to přesně pro něj. Na potíže je možno narazit v případě importu dat v nějakém nezvyklém kódování.

3.4.2 BULK INSERT Použito ve skriptech: 14_ImportSSISBulkInsert.sql 15_ImportSQLBulkInsert.sql

V prvním případě je příkaz volán pomocí procesu SSIS ve druhém přímo v T-SQL. Import z T-SQL proběhl podle očekávání jen o málo pomaleji než import pomocí bcp.exe.

Import pomocí SSIS u malých souborů je pomalejší i při několikerém opakování

měření. Při měření na velkém objemu dat se ukázalo, že rozdíl mezi těmito dvěma metodami zůstal stejný (cca 10s). Usuzuji, že tento čas je spotřebován na zpracování konfiguračních souborů a tzv. validaci balíčků. Tento balíček jsem vytvořil tak, že demonstruje

možnosti

parametrizace.

BulkInsertTask01.dtsConfig

Existují

parametrizační

soubory

a BulkInsertTask02.dtsConfig, které jsou balíčku

předány při volání a ty nastavují zdroj a cíl přenosu bez nutnosti editace samotného balíčku.

3.4.3 SSIS Data Flow Task Použito ve skriptu: 16_ImportSSISDataFlowTask.sql

Data Flow Task má variabilitu výsledků nejzajímavější. Nejlépe dopadl při importu velkého objemu dat s málo sloupci. To byl téměř 2x rychlejší než jiné metody. U malých datových objemů je jen o málo pomalejší, než BCP. Podobně jako SSIS Bulk Insert Task i tento spotřebuje cca 10 s na inicializaci, ale poté předvede svou sílu. A to jsme testovali jen přesun dat bez jakékoliv konverze. Pokud bychom chtěli simulovat i další operace, které se s daty provádějí (transformace, konsolidace), Data Flow Task na to má nástroje. Za nevýhodu je možno považovat jistou přísnost ve věci konverze datových typů a mnoho práce při jakékoliv změně v definici zdroje i cíle.

3.4.4 Excel jako Linked Server Použito ve skriptu: 19_ImportLinkedServerXLS.sql

- 26 -

Načtení dat přímo z Excelu se může jevit jako velmi pohodlné, Excel je velmi častým místem pro ukládání menších dat. Potíž ovšem je, že Excel není databáze. Používá datové typy, ale každá buňka ve sloupci může mít jiný. Při importu to pak dopadne tak, že v příslušném poli není hodnota žádná. Běžný uživatel zpravidla nepozná, zda je v poli číslo nebo text, pokud text obsahuje pouze čísla. Při práci s Linked Servery lze použít uloženou proceduru sp_tables_ex19, kterou získáme seznam dostupných tabulek. Tabulka 3 - Seznam listů v dokumentu Excelu připojeném jako Linked Server (získaná spuštěním EXEC sp_tables_ex BP_Excel) TABLE_CAT TABLE_SCHEM TABLE_NAME List1$ List2$ List3$

TABLE_TYPE REMARKS TABLE TABLE TABLE

3.4.5 Textový soubor jako Linked Server Použito ve skriptech: 20_ImportLinkedServerTXT.sql 21_ImportTXTintoOneTable.sql

Pokud připojíme TXT soubory jako Linked server máme k dispozici všechny soubory s příponou TXT soubory v daném adresáři a můžeme s nimi pracovat, jako by to byly tabulky. MS SQL server přistupuje k datům prostřednictvím ovladače „File Text Driver“. Metadata možno definovat v souboru s názvem schema.ini20. Má oproti FMT souboru používané pro utilitu bcp přehlednější syntaxi. Informace obsahuje stejné: jaký je oddělovač záznamů a datové typy jednotlivých sloupců. Pro celý adresář je jeden společný soubor. Stejně jako v minulé kapitole můžeme seznam dostupných tabulek získat procedurou sp_tables_ex. Tabulka 4 - Seznam dostupných TXT souborů v adresáři (získaná spuštěním EXEC sp_tables_ex BP_txt) TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS BP_DataSample01#txt TABLE BP_DataSample02#txt TABLE

19


20

Dokumentace http://msdn.microsoft.com/en-us/library/ms709353.aspx

- 27 -

Pro import většího množství dat je tento způsob pomalý. Pokud není struktura dat TXT souboru popsána ve schema.ini považuje se soubor za tabulku s jedním širokým sloupcem. To jsem shledal jako užitečné při vyhledávání chyb ve velkých souborech. Tímto způsobem se dá načíst textový soubor, jehož otevření a čtení je pro jeho velikost komplikované.

3.4.6 SQL INSERT Použito ve skriptu: 23_ImportSQLINSERT.sql

To je metoda nejsrozumitelnější, nepotřebujeme k ní žádné speciální nástroje, pouze možnost spustit SQL skript. Není to metoda vůbec vhodná pro přenos většího než malého množství dat. Občas se hodí pro různé ad hoc úlohy, kdy je potřeba k nějakému malému množství identifikátorů dohledat v databázích další informace. Tato metoda je používaná v instalačních skriptech různých aplikací, kde se skládá i z dalších SQL příkazů vytvářejících celou databázi a INSERT se používá pro inicializaci číselníků a různých počátečních hodnot.

3.4.7 OPENROWSET Použito ve skriptech: 24_ImportOPENROWSET_TXT.sql 26_ImportOPENROWSET_MDB.sql

Každý z těchto souborů používá v rámci příkazu jiný způsob připojení . Ve skriptu pro import TXT souboru je použita volba „BULK“, která určuje, že jsou data nahrána stejným způsobem jako přes BCP. Rychlost zpracování dokazuje, že jsou data zpracována stejně efektivně. Příkaz má dva argumenty, jeden je cesta k TXT souboru s daty a druhý je cesta k FMT souboru. Připojení k databázi MS Access použije OLE DB ovladač. Podle očekávání to není příliš rychlý způsob zpracování, ale stále se pohybujeme v řádu minut. Výhodou možnosti přímého přenosu z MS Access nebo i jiných databází je již existující definice datových typů, tabulek i integritních omezení. To může usnadnit transformaci dat, protože bývá lépe zajištěna čistota dat.

3.4.8 OPENDATASOURCE Použito ve skriptu: 25_ImportOPENDATASOURCE_XLS.sql

- 28 -

Výsledek testu rychlosti tohoto způsobu připojení byl pro mne překvapením. V porovnání s Linked Serverem je tento způsob připojení o více než čtvrtinu rychlejší. Avšak co se týče rizika v kvalitě dat platí totéž co výše. Excel odhaduje datový typ v každém sloupci na základě hodnot v několika prvních řádcích, pokud se zmýlí, data jsou načtena chybně.

3.5

Paralelní zpracování

Úlohy, které musí zpracovat více vstupních souborů, je vhodné spouštět s využitím paralelních procesů. Na otestování paralelního zpracování je v datové příloze moji práce dostupný balíček s názvem Paralel.dtsx, který v cyklu načte data do cílové tabulky. Pro otestování paralelismu jsem zvolil dvě nejrychlejší metody importu. Nejprve pomocí bcp.exe

a poté s využitím Data Flow Tasku, který jsme používali už dříve.

Obrázek 15 – Rozvržení procesů v SSIS balíčku pro testování paralelního zpracování v cyklu

Pro otestování paralelního zpracování jsem vytvořil soubory čtvrtinové velikosti oproti souborům, které jsem používal k testování časové náročnosti výše. Tyto čtvrtinové soubory jsem pak současně importoval do cílové tabulky čtyřmi paralelními procesy. Tabulka 5 – Porovnání rychlosti paralelního a sériového zpracování 32 columns, 524288 rows, 206MB

32 columns, 4*131072 rows, 4*51MB

32 columns, 4194304 rows, 1.61GB

32 columns, 4*1048576 rows, 4*413MB

BCP

15.82 s

9.39 s

124.59 s

76.30 s

SSISDataFlowTask

25.09 s

8.75 s

132.50 s

56.59 s

- 29 -

Výsledky měření paralelního zpracování ukazují, že může být více než 2x rychlejší než sériové. Neexistuje žádný návod pro určení optimálního počtu vláken. Vše závisí na síle HW, které máme k dispozici, zatížení serveru dalšími úlohami, komplikovanosti transformací, které s daty provádíme, charakteru zdrojových dat a v neposlední řadě také na efektivitě. Abychom ladění nevěnovali dva dny práce a ve výsledku ušetřili několik minut.

- 30 -

4 Užitečné postupy, nejlepší zkušenosti Při rozhodování o tom, jakou metodu importu dat použít je třeba zvážit toto: •

standardy a zvyklosti v dané organizaci,

•

kvalitu dat,

•

objem dat

a •

periodicitu.

Podnikové standardy a uživatelská práva mohou některé postupy znemožnit, ale většinou existuje alternativa. Například ke spuštění utility bcp je třeba mít příslušné oprávnění ke spouštění programů procedurou xp_cmdshell. Ovšem to lze nahradit téměř stejně efektivním BULK INSERTem, které je navíc spustitelný z okna Editoru SQL. Pokud mají být datové přenosy součástí nějakého většího SW celku, považuji za nejvhodnější SSIS balíček. Jednak umožňuje snadné spouštění paralelních procesů a jednak je to silný nástroj pro takovéto účely Graf 1 – Grafické znázornění časové náročnosti importů malých datových objemů 35 30 13_ImportBCP

čas [s]

25

14_ImportSSISBulkInsert

20

15_ImportSQLBulkInsert 15

16_ImportSSISDataFlowTask

10

24_ImportOPENROWSET_TXT

5 0 8 columns, 1048576 rows, 172MB

32 columns, 524288 rows, 206MB

Zdroj: Tabulka 2

Graf 1 ukazuje, že režie, kterou balíček SSIS potřebuje pro své spuštění, může zabrat významnou část celkového času zpracování menších objemů dat. Pokud mají data charakter menších souborů, zvažoval bych, použít pro import metodu BCP, BULK INSERT nebo OPENROWSET. Ovšem v případě většího množství těchto souborů bych navrhoval vytvořit systém paralelního zpracování. - 31 -

Graf 2 – Grafické znázornění časové náročnosti importů velkých datových objemů 300 13_ImportBCP

250

BCP_paralel

čas [s]

200

14_ImportSSISBulkInsert 15_ImportSQLBulkInsert

150

16_ImportSSISDataFlowTask

100

DataFlowTask_paralel 24_ImportOPENROWSET_TXT

50 0 8 columns, 16777216 rows, 2,73GB

32 columns, 4194304 rows, 1,61GB

Zdroj: Tabulka 5

V Grafu 2 stojí za povšimnutí významné snížení časové náročnosti SSIS balíčku u „krátké datové věty“. To je úloha, která této metodě vyhovovala. U importu „dlouhé datové věty“ bylo testováno i paralelní zpracování, které též významně zkrátí celkovou dobu importu.

4.1.1 Ad Hoc importy Připojení Excelu touto metodou má své nevýhody v tom, že Excel není databáze, ale tabulkový procesor. Má „nedatabázový“ přístup k datovým typům, v každé buňce tabulky mohou být hodnoty jiného datového typu. Při importu dat na server je datový typ celého sloupce odhadnut podle hodnot v několika prvních řádcích21. Pokud je číselná hodnota uložena jako text, je ignorována a v cílové tabulce se objeví hodnota NULL. Variabilita možných chyb přicházejících v dokumentech Excelu je nepřeberná. Pro import dat z Excelu se ukázalo jako nejschůdnější buď data vyexportovat do TXT souboru a ten nahrát, nebo pomocí copy&paste zkopírovat do notepadu a zpět do prázdného čistého listu. Tímto postupem se nám podaří pročistit datové typy (čísla budou opravdu čísla). Také je možné data převést pomocí sady INSERT příkazů, pokud je množství záznamů v řádu stovek, maximálně tisíců. Manipulace s takovým souborem, pokud je příliš veliký je komplikovaná, hrozí riziko, že se vůbec nevejde do paměti SQL parseru. Pro jednorázové malé importy pro účely ad hoc analýz je to pohodlné.

21

Počet řádků, podle kterých e rozhoduje o datovém typu, lze nastavit zásahem do registů počítače. Více na:

http://support.microsoft.com/kb/194124/en-us. Tento způsob je ovšem použitelný málokde, neboť přístup do registrů operačního systému by měl být pro běžné uživatele zablokován.

- 32 -

4.1.2 Velké importy U velkých importů by základním principem měla být snaha o maximální automatizaci procesů. Platí, že čím méně lidských zásahů do souboru, tím menší riziko zanesení nové chyby. Pokud jsou importy ustálené a mění se minimálně, případně pokud je třeba pro implementaci změny splnit formální procesní pravidla organizace, je nejlepší navrhnout importy tak, aby byly pokud možno řiditelné pouze parametrizačním souborem. Parametrizace SSIS je více odolná vůči chybě než parametrizování SQL skriptu. Metody s přímým přístupem k datům, založené na BCP nebo BULK INSERT nemají žádné režijní náklady, ale hůře se parametrizují.

- 33 -

5 Zhodnocení Čtenář mohl v teoretické části této práce projít cestu dat od jejich získání z primárních systémů, přes transformaci ve znalosti až po transformaci těchto znalostí do nových obchodních příležitostí. Tyto příležitosti v případě obchodního úspěchu končí znovu v primárních systémech, čímž se Hegelovská spirála teze-antiteze-synteze uzavírá. Možnost rozšíření práce spočívá například v rozpracování tématu kvality dat a jeho automatizovaného zpracování. V této oblasti jednotlivé metody importu také nabízejí mnoho různých možností. V praktické části práce splnila zadání demonstrací implementací více než deseti metod nahrávání dat do MS SQL serveru. Hlavním cílem této práce bylo demonstrovat, jakým způsobem lze provádět importy dat a ETL procesy na platformě MS SQL. Operace s daty jsou základem Business Intelligence. Bez pevných základů by celé BI organizace stálo na písku. Má práce ukazuje, že platforma MS SQL umožňuje širokou variabilitu možností, jak se z různými datovými požadavky vypořádat.

- 34 -

6 Literatura DUŠEK, M.: Využití datových skladů pro podporu CRM, dipl. prac. ČZU Praha 2008, dostupný z WWW: http://martyx.net/files/0_czu/dp/xdusm101_T.doc ze dne 16.1.2009 HYNKOVÁ, Š.: Nasazení Business Inteligence v lékařských knihovnách, dipl. prac MU Brno 2006, http://is.muni.cz/th/110973/ff_b/bc_BI_cs.doc?lang=cs ze dne 20. 1. 2009 CHAPMAN, P., CLINTON, J., KERBER R. a kol. CRISP-DM 1.0 Step-by Step data mining guide, 2000, USA, dostupný z WWW: http://www.crisp-dm.org/CRISPWP0800.pdf ze dne 4.2.2009 INMON, W.H.: Building the Data Warehouse, Third Edition, USA, Wiley Computer Publishing, 2002. KRUTÝ, P. Informační systém pro podporu školících procesů firem, dipl. prac MU Brno 2008, http://is.muni.cz/th/50862/fi_m/text_prace.pdf ze dne 20. 1. 2009 KUČERA, M.: Dva způsoby budování datového skladu, IT Systems. 5/2001. ISSN 1802615X. Dostupný z WWW http://www.systemonline.cz/clanky/dva-zpusoby-budovanidatoveho-skladu.htm ze dne 5.3.2009 MACHÁČOVÁ, M.: Využití inteligentních nástrojů pro analýzu technologických dat, In: Sborník vědeckých prací Vysoké školy báňské – Technické univerzity Ostrava, řada hornickogeologická, ročník XLIX, 2/2003, Ostrava, ISBN 80-248-0562-6, ISSN 04748476, s.43-52. Dostupný z WWW: http://gse.vsb.cz/2003/XLIX-2003-2-43-52.pdf ze dne 31. 1. 2009 RYS, J.: Systémy pro podporu rozhodování na MS SQL 2005. dipl, prac. ČVUT, 2007. Dostupný z WWW: https://dip.felk.cvut.cz/browse/pdfcache/rysj_2007dipl.pdf ze dne 20. 1. 2009 ŠANDA, A.: Základní pojmy a teorie v oblasti datových skladů. Computerworld. 18/1999. ISSN 1210-9924. VÍTEK, L.: Systém pro extrakci, transformaci a nahrávání dat pro datový sklad, dipl. prac. ČVUT, https://dip.felk.cvut.cz/browse/pdfcache/vitekl2_2008dipl.pdf 2009

- 35 -

ze dne 27. 2.

7 Obsah přiloženého CD _obsah.txt _zadani.pdf 00_CreateFramework.sql 01_UniversalLoop.sql 02_SelectLOG.sql 03_GenerateDataSample01.sql 04_GenerateDataSample02.sql 12_ExportBCP.sql 13_ImportBCP.sql 14_ImportSSISBulkInsert.sql 15_ImportSQLBulkInsert.sql 16_ImportSSISDataFlowTask.sql 19_ImportLinkedServerXLS.sql 20_ImportLinkedServerTXT.sql 21_ImportTXTintoOneTable.sql 23_ImportSQLINSERT.sql 24_ImportOPENROWSET_TXT.sql 25_ImportOPENDATASOURCE_XLS.sql 26_ImportOPENROWSET_MDB.sql BP_DataSample.mdb BP_DataSample01.fmt BP_DataSample01.txt BP_DataSample01.xls BP_DataSample02.fmt BP_DataSample02.txt BP_DataSample02.xls logs schema.ini SQLINSERT_template.xls BP_Reports BP_Reports\BP_Report.rdl BP_Reports\BP_Report.rdl.data BP_Reports\BP_Reports.rptproj BP_Reports\BP_Reports.rptproj.user BP_Reports\BP_Reports.sln BP_Reports\reporting.rds BP_SSIS BP_SSIS\bin BP_SSIS\bin\BulkInsertTask.dtsx BP_SSIS\bin\DataFlowTask.dtsx BP_SSIS\bin\DataFlowTask01.dtsx BP_SSIS\bin\DataFlowTask02.dtsx BP_SSIS\bin\ExecuteWin32ProcessTask.dtsx BP_SSIS\bin\Paralel.dtsx BP_SSIS\BP.database BP_SSIS\BP.dtproj BP_SSIS\BP.dtproj.user BP_SSIS\BP.sln BP_SSIS\BulkInsertTask.dtsx BP_SSIS\BulkInsertTask01.dtsConfig BP_SSIS\BulkInsertTask02.dtsConfig BP_SSIS\DataFlowTask01.dtsx BP_SSIS\DataFlowTask02.dtsx BP_SSIS\ExecuteWin32ProcessTask.dtsx BP_SSIS\Paralel.dtsx BP_SSIS\Reporting.ds - 36 -

8 SQL skripty 8.1

Skripty pro vytvoření prostředí a testovacích dat

8.1.1 00_CreateFramework.sql /****************************************************************************************** * Script: Create log table, support procedures and functions * Name: 00_CreateFramework.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: ******************************************************************************************/ USE reporting begin try drop begin try drop begin try drop begin try drop begin try drop begin try drop begin try drop begin try drop

table [BP_LOG] end try begin catch end catch table [BP_RunID] end try begin catch end catch procedure [p_getRunID] end try begin catch end catch procedure [p_setRunID_parent] end try begin catch end catch procedure [p_Log] end try begin catch end catch function dbo.f_getTimeSuffix end try begin catch end catch function dbo.f_myLeft end try begin catch end catch table BP_ImportedTXT end try begin catch end catch

GO CREATE TABLE [BP_LOG]( [LineID] int IDENTITY(1,1) NOT NULL, [RunID] int NOT NULL, [RunID_parent] int NULL, [CallerID] [varchar](100) NULL, [Item] [varchar](50) NULL, [CallTime] [datetime] NULL, [TextValue] [varchar](1000) NULL, [NumberValue] float ) go CREATE TABLE [BP_RunID] ( [CallerID] [varchar](100) NOT NULL, [RunID] int NOT NULL ) go insert into [BP_RunID] select 'Global',0 go CREATE PROCEDURE [p_getRunID] (@r int OUTPUT ) AS set @r = 1+(select RunID from BP_RunID where [CallerID] ='Global') update BP_RunID set RunID = @r where [CallerID] ='Global' go CREATE PROCEDURE [p_setRunID_parent]( @CallerID varchar(100), @RunID int) AS delete from BP_RunID where [CallerID] = @CallerID+'.sql' insert into BP_RunID select @CallerID+'.sql', @RunID go CREATE PROCEDURE [p_Log] ( @RunID int = null , @CallerID varchar(100) = '', @Item varchar(50) = '', -- START, STEP, FINISH, PROFILING, WARNING etc @Note varchar(8000)= null, @Number float = null ) as /* USAGE: exec p_log null,'HelloWorld','START','First note' , 10 exec p_log 0,'Hello world','FINISH','Finish note ' */ declare @RunID_parent int set @RunID_parent = (select top 1 [RunID] from [BP_RunID] where [CallerID] = @CallerID)

- 37 -

-- pokud je procedura zavolána bez ID, tady si vymyslíme nové if @RunID is null EXECUTE [p_getRunID] @r=@RunID OUTPUT set @Item = upper (@Item) if (@Note is null and @Number is null) begin set @Note = '@@spid:' set @Number = @@spid end -- when called declare @CallTime datetime; set @CallTime = getdate() -- insert the row insert BP_LOG select @RunID, @RunID_parent, @CallerID,@Item,@CallTime,@Note,@Number go create FUNCTION dbo.f_getTimeSuffix() RETURNS varchar(30) AS BEGIN RETURN dbo.f_getTimeSuffix() END go create FUNCTION dbo.f_myLeft(@s varchar(8000), @n int) RETURNS varchar(8000) AS BEGIN RETURN left(@s, @n)+case when len(@s)>@n then '...' else '' end END GO create table BP_ImportedTXT (LineNum int identity(1,1) , line varchar(8000)) go

8.1.2 01_UniversalLoop.sql /****************************************************************************************** * Script: "Parent" script for calling tested subscripts * Name: 01_UniversalLoop.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: ******************************************************************************************/ USE reporting DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT exec p_log @RunID,'01_UniversalLoop.sql','START' /*FINISH*/ --declarations DECLARE @i int DECLARE @cmd varchar(1000) DECLARE @work_folder varchar(1000) DECLARE @server_name varchar(1000) DECLARE @params_other varchar(1000) DECLARE @script_name varchar(1000) DECLARE @data_description varchar(1000) -- name of dataset --set @data_description = '32 columns, 1048576 rows, 172MB I' set @data_description = '8 columns, 262144 rows, 103MB I' -- select tested method SET @script_name = '13_ImportBCP' --SET @script_name = '14_ImportSSISBulkInsert' --SET @script_name = '15_ImportSQLBulkInsert' --SET @script_name = '16_ImportSSISDataFlowTask' --SET @script_name = '19_ImportLinkedServerXLS' --SET @script_name = '20_ImportLinkedServerTXT' --SET @script_name = '21_ImportTXTintoOneTable' --SET @script_name = '23_ImportSQLINSERT' --SET @script_name = '24_ImportOPENROWSET_TXT' --SET @script_name = '25_ImportOPENDATASOURCE_XLS' --SET @script_name = '26_ImportOPENROWSET_MDB' SET @work_folder ='F:\CDS\Development\BP\' set @i = 5 ------------------------exec p_log @RunID,'01_UniversalLoop.sql','description', @data_description -- set parent parameter exec p_setRunID_parent @script_name, @RunID

- 38 -

-- main loop while @i>0 begin SET @params_other = '-S crsgaspe\i01 -E -o '+@work_folder +'logs\'+@script_name+ dbo.f_getTimeSuffix()+'.txt' set @cmd = 'isqlw -i '+@work_folder+@script_name+'.sql '+@params_other exec p_log @RunID,'01_UniversalLoop.sql','step' , @cmd, @i execute master..xp_cmdshell @cmd set @i=@i-1 end -- clean parent parameter exec p_setRunID_parent @script_name, 0 /*FINISH*/ exec p_log @RunID,'01_UniversalLoop.sql','FINISH' /*REPORT*/ select LineID,RunID,RunID_parent,CallerID,Item,CallTime ,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where (RunID = @RunID and Item in ('FINISH', 'START')) or ( RunID_parent = @RunID and Item='FINISH') order by LineID LineID RunID RunID_parent

CallerID

Item

01_UniversalLoop.sql

START

1299

375 NULL

1305

376

375 16_ImportSSISDataFlowTask.sql FINISH

1310

377


1315

378


1320

379


1325

380


1326

375 NULL

01_UniversalLoop.sql

FINISH

CallTime 11.4.2009 13:05 11.4.2009 13:05 11.4.2009 13:05 11.4.2009 13:06 11.4.2009 13:06 11.4.2009 13:06 11.4.2009 13:06

TextValue

NumberValue

@@spid: Duration (ms): Duration (ms): Duration (ms): Duration (ms): Duration (ms): @@spid:

8.1.3 03_GenerateDataSample01.sql /****************************************************************************************** * Script: creates table BP_DataSample01 with 8 columns and s^N rows * Name: 03_GenerateDataSample01.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: rowcount is defined by comment ******************************************************************************************/ USE reporting DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT exec p_log @RunID, '03_GenerateDataSample01.sql','START' DECLARE @CAS DATETIME SET @CAS = GETDATE() /**/ begin try drop table BP_DataSample01 end try begin catch end catch select LineNum = /*T24.A+T23.A+T22.A+T21.A+*/T20.A+T19.A+T18.A+T17.A+ T16.A+T15.A+T14.A+T13.A+T12.A+T11.A+T10.A+T9.A+ T8.A+T7.A+T6.A+T5.A+T4.A+T3.A+T2.A+T1.A , ID = abs(checksum(newid()) ) , col01 = cast (newid() AS varchar(36)) , col02 = abs(checksum(newid())) / 100000 , col03 = abs(checksum(newid())) / 10000.0 , col04 = cast (newid() AS varchar(36)) , col05 = cast (newid() AS varchar(36)) , col06 = convert(datetime,abs(checksum(newid()))/100000.0 + 18000) INTO BP_DataSample01 from (select A=0 union all select 1 ) T1 , (select A=0 union all select 2 ) T2

- 39 -

62 18826 21873 16953 18076 17750 62

, (select A=0 union all select 4 ) T3, (select A=0 union all select 8 ) T4 , (select A=0 union all select 16 ) T5, (select A=0 union all select 32 ) T6 , (select A=0 union all select 64 ) T7, (select A=0 union all select 128 ) T8 , (select A=0 union all select 256 ) T9, (select A=0 union all select 512 ) T10 , (select A=0 union all select 1024 ) T11, (select A=0 union all select 2048 ) T12 , (select A=0 union all select 4096 ) T13, (select A=0 union all select 8192 ) T14 , (select A=0 union all select 16384 ) T15, (select A=0 union all select 32768 ) T16 , (select A=0 union all select 65536 ) T17, (select A=0 union all select 131072 ) T18 , (select A=0 union all select 262144 ) T19, (select A=0 union all select 524288 ) T20 /* , (select A=0 union all select 1048576 ) T21, (select A=0 union all select 2097152 ) T22 , (select A=0 union all select 4194304 ) T23, (select A=0 union all select 8388608 ) T24 */ ORDER BY 1 exec p_log @RunID, '03_GenerateDataSample01.sql','STEP','@@rowcount' , @@rowcount --SELECT * FROM BP_DataSample01 /**/ declare @i int; set @i = DATEDIFF ( ms , @CAS , GETDATE() ) -- PRINT @i exec p_log @RunID, '03_GenerateDataSample01.sql','FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID LineID RunID CallerID 1332

382 03_GenerateDataSample01.sql

1333


1334


Item

CallTime 11.4.2009 13:34 START 11.4.2009 13:34 STEP 11.4.2009 FINISH 13:34

TextValue @@spid: @@rowcount Duration (ms):

NumberValue 107 1048576 9313

8.1.4 04_GenerateDataSample02.sql /****************************************************************************************** * Script: creates table BP_DataSample02 with 32 columns and s^N rows * Name: 04_GenerateDataSample02.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: rowcount is defined by comment ******************************************************************************************/ USE reporting DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT exec p_log @RunID, '04_GenerateDataSample02.sql','START' DECLARE @CAS DATETIME SET @CAS = GETDATE() /**/ begin try drop table BP_DataSample02 end try begin catch end catch select LineNum = /*T24.A+T23.A+T22.A+T21.A+T20.A+T19.A+*/T18.A+T17.A+T16.A+T15.A+T14.A+T13.A+ T12.A+T11.A+T10.A+T9.A+T8.A+T7.A+T6.A+T5.A+T4.A+T3.A+T2.A+T1.A , ID = abs(checksum(newid()) ) , col01 = cast (newid() AS varchar(36)) , col02 = cast(abs(checksum(newid()))/100000 as int) , col03 = cast(abs(checksum(newid()))/10000.0 as float) , col04 = cast (newid() AS varchar(36)) , col05 = cast (newid() AS varchar(36)) , col06 = convert(datetime,abs(checksum(newid()))/100000.0 + 18000) , col07 = convert(datetime,abs(checksum(newid()))/100000.0 + 25000) , col08 = right('abcdefgh', T3.A+T2.A+T1.A) , col09 = left ('abcdefghijklmnopqr', T4.A+T3.A+T2.A+T1.A+1) , col10 = 10000+T16.A+T15.A+T14.A+T13.A+T12.A+T11.A+T10.A+T9.A+T8.A+T7.A+T6.A+T5.A+T4.A+ T3.A+T2.A+T1.A , col11 = left ('abcdefghijklmnopqrstu', abs(checksum(newid()))/100000000) , col12 = left ('abcdefghijklmnopqrstu', abs(checksum(newid()))/100000000) , col13 = left ('abcdefghijklmnopqrstu', abs(checksum(newid()))/100000000)

- 40 -

, col14 = left ('abcdefghijklmnopqrstu', abs(checksum(newid()))/100000000) , col15 = left ('abcdefghijklmnopqrstu', abs(checksum(newid()))/100000000) , col16 = abs(checksum(newid()))/1000000.0 , col17 = abs(checksum(newid()))/1000000.0 , col18 = abs(checksum(newid()))/1000000.0 , col19 = abs(checksum(newid()))/1000.0 , col20 = abs(checksum(newid()))/1000.0 , col21 = substring('YN',T1.A+1,1) , col22 = substring('MF',T1.A+1,1) , col23 = SUBSTRING ('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345', abs(checksum(newid()))/100000000 , 20-(T4.A+T3.A+T2.A+T1.A )) , col24 = SUBSTRING ('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345', abs(checksum(newid()))/100000000 , abs(checksum(newid()))/300000000 ) , col25 = SUBSTRING ('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345', abs(checksum(newid()))/100000000 , T4.A+T3.A+T2.A+T1.A ) , col26 = (abs(checksum(newid()))/2000000000 ) * T13.A , col27 = (abs(checksum(newid()))/2000000000 ) * T13.A , col28 = convert(varchar(10),convert(datetime,abs(checksum(newid()))/100000.0 + 18000),120) , col29 = convert(varchar(10),convert(datetime,abs(checksum(newid()))/100000.0 + 25000),120) , col30 = convert(varchar(8),convert(datetime,abs(checksum(newid()))/100000.0 ),14) INTO BP_DataSample02 from (select A=0 union all select 1 ) T1 , (select A=0 union all select 2 ) T2 , (select A=0 union all select 4 ) T3, (select A=0 union all select 8 ) T4 , (select A=0 union all select 16 ) T5, (select A=0 union all select 32 ) T6 , (select A=0 union all select 64 ) T7, (select A=0 union all select 128 ) T8 , (select A=0 union all select 256 ) T9, (select A=0 union all select 512 ) T10 , (select A=0 union all select 1024 ) T11, (select A=0 union all select 2048 ) T12 , (select A=0 union all select 4096 ) T13, (select A=0 union all select 8192 ) T14 , (select A=0 union all select 16384 ) T15, (select A=0 union all select 32768 ) T16 , (select A=0 union all select 65536 ) T17, (select A=0 union all select 131072 ) T18 /* , (select A=0 union all select 262144 ) T19, (select A=0 union all select 524288 ) T20 , (select A=0 union all select 1048576 ) T21, (select A=0 union all select 2097152 ) T22 , (select A=0 union all select 4194304 ) T23, (select A=0 union all select 8388608 ) T24 */ ORDER BY 1 exec p_log @RunID, '04_GenerateDataSample02.sql','STEP','@@rowcount' , @@rowcount --SELECT * FROM BP_DataSample02 /**/ declare @i int; set @i = DATEDIFF ( ms , @CAS , GETDATE() ) exec p_log @RunID, '04_GenerateDataSample02.sql','FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID LineID RunID CallerID 1335


1336


1337


Item

CallTime 11.4.2009 13:54 START 11.4.2009 STEP 13:54 11.4.2009 13:54 FINISH

TextValue @@spid: @@rowcount Duration (ms):

NumberValue 58 262144 8936

8.1.5 12_ExportBCP.sql /****************************************************************************************** * Script: Export sample data to file using bcp.exe * Name: 12_ExportBCP.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME

- 41 -

DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @cmd varchar(1000) DECLARE @bcp_path varchar(1000) DECLARE @mode varchar(1000) DECLARE @table_fullname varchar(1000) DECLARE @table_name varchar(1000) DECLARE @export_path varchar(1000) DECLARE @server_name varchar(1000) DECLARE @params_export varchar(1000) DECLARE @params_FMT varchar(1000) DECLARE @logfile varchar(1000) DECLARE @workfolder varchar(1000) DECLARE @scriptname varchar(1000) DECLARE @db varchar(1000) /*CONSTANTS*/ SET @bcp_path = 'C:\progra~1\micros~2\90\tools\binn\bcp.exe' SET @server_name = 'CRSGASPE\i01,3537' set @workfolder = 'f:\CDS\Development\BP\' set @scriptname = '12_ExportBCP.sql' set @db = db_name() /**************************************************************************/ SET @table_name ='BP_DataSample02' /**************************************************************************/ set @logfile = 'BCP_'+dbo.f_getTimeSuffix()+'.txt' SET @export_path =''+@workfolder+@table_name+'.txt' SET @mode = 'out ' -- out|queryout SET @params_export = '-T -C ASC -t"|" -c -o '+@workfolder+'logs\'+@logfile SET @params_FMT = '-T -C ASC -t"|" -c -f '+@workfolder+@table_name+'.fmt' /*START*/ exec p_log @RunID,@scriptname,'START' exec p_log @RunID,@scriptname,'LOG' , @logfile SET @CAS = getdate() /*create FMT file*/ SET @cmd = @bcp_path+' '+@db+'..'+@table_name+' format "'+@export_path+'" -S '+ @server_name+' '+@params_FMT exec p_log @RunID,@scriptname,'COMMAND' , @cmd EXEC xp_cmdshell @cmd /*create data file*/ SET @cmd = @bcp_path+' '+@db+'..'+@table_name+' '+@mode+' "'+@export_path+'" -S '+ @server_name+' '+@params_export exec p_log @RunID,@scriptname,'COMMAND' , @cmd EXEC xp_cmdshell @cmd /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) -- PRINT @i exec p_log @RunID,@scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID LineID RunID CallerID 1338 1339 1340 1341 1342

Item

CallTime 11.4.2009 384 12_ExportBCP.sql START 13:57 11.4.2009 384 12_ExportBCP.sql LOG 13:57 11.4.2009 13:57 384 12_ExportBCP.sql COMMAND 11.4.2009 384 12_ExportBCP.sql COMMAND 13:57 11.4.2009 384 12_ExportBCP.sql FINISH 13:57

- 42 -

TextValue

NumberValue

@@spid:

79

BCP_20090411_135733.txt

NULL

C:\progra~1\micros~2\90\tools\...

NULL

C:\progra~1\micros~2\90\tools\...

NULL

Duration (ms):

9000

8.2

Skripty pro testování importu

8.2.1 13_ImportBCP.sql /****************************************************************************************** * Script: Import data to table using bcp.exe * Name: 13_ImportBCP.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: * Přepokládá se, že tabulka datový soubor i formátovací soubor mají stejný název * Předpokládá se existence cílové tabulky, cílová tabulka je před importem vyčištěna ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @cmd varchar(1000) DECLARE @bcp_path varchar(1000) DECLARE @mode varchar(1000) DECLARE @table_name varchar(1000) DECLARE @table_fullname varchar(1000) DECLARE @source_file varchar(1000) DECLARE @server_name varchar(1000) DECLARE @params_import varchar(1000) DECLARE @logfile varchar(1000) DECLARE @workfolder varchar(1000) DECLARE @scriptname varchar(1000) /*CONSTANTS*/ SET @bcp_path = 'C:\progra~1\micros~2\90\tools\binn\bcp.exe' SET @server_name = 'CRSGASPE\i01,3537' set @workfolder = 'f:\CDS\Development\BP\' set @scriptname = '13_ImportBCP.sql' /**************************************************************************/ SET @table_name ='BP_DataSample01' truncate table BP_DataSample01 /**************************************************************************/ set @logfile = 'BCP_'+dbo.f_getTimeSuffix()+'.txt' SET @table_fullname ='reporting..'+@table_name SET @source_file = @workfolder+@table_name+'.txt' SET @mode = 'in ' SET @params_import = '-T -f '+@workfolder+@table_name+'.fmt -o '+@workfolder+'logs\'+ @logfile /*START*/ exec p_log @RunID,@scriptname,'START' exec p_log @RunID,@scriptname,'LOG' , @logfile SET @CAS = getdate() /*load data file*/ SET @cmd = @bcp_path+' '+@table_fullname+' '+@mode+' "'+@source_file+'" -S '+@server_name+ ' '+@params_import exec p_log @RunID,@scriptname,'COMMAND' , @cmd EXEC xp_cmdshell @cmd /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) -- PRINT @i exec p_log @RunID,@scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID

LineID RunID CallerID 1343 1344 1345

Item

CallTime 11.4.2009 13:59 385 13_ImportBCP.sql START 11.4.2009 385 13_ImportBCP.sql LOG 13:59 385 13_ImportBCP.sql COMMAND 11.4.2009

- 43 -

TextValue

NumberValue

@@spid: BCP_20090411_135954.txt C:\progra~1\micros~2\90\tools\...

64 NULL NULL

1346

13:59 11.4.2009 14:00 Duration (ms):

385 13_ImportBCP.sql FINISH

15860

8.2.2 14_ImportSSISBulkInsert.sql /****************************************************************************************** * Script: Import data to table using SSIS with Bulk Insert process * Name: 14_ImportSSISBulkInsert.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: * Přepokládá se, že tabulka datový soubor i formátovací soubor mají stejný název * Předpokládá se existence cílové tabulky, cílová tabulka je před importem vyčištěna ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @cmd varchar(1000) DECLARE @exe_path varchar(1000) DECLARE @package_name varchar(1000) DECLARE @config varchar(1000) DECLARE @params_import varchar(1000) DECLARE @logfile varchar(1000) DECLARE @workfolder varchar(1000) DECLARE @scriptname varchar(1000) /*CONSTANTS*/ SET @exe_path = 'C:\progra~1\micros~2\90\DTS\Binn\DTExec.exe' set @workfolder = 'f:\CDS\Development\BP\' set @scriptname = '14_ImportSSISBulkInsert.sql' SET @package_name = @workfolder+'BP_SSIS\BulkInsertTask.dtsx' /**************************************************************************/ set @config = 'BP_SSIS\BulkInsertTask01.dtsConfig' truncate table BP_DataSample01 /**************************************************************************/ set @logfile = 'SSIS_'+dbo.f_getTimeSuffix()+'.txt' SET @params_import = ' /Conf '+@workfolder+@config+'> '+@workfolder+'logs\'+@logfile /*START*/ exec p_log @RunID,@scriptname,'START' exec p_log @RunID,@scriptname,'LOG' , @logfile SET @CAS = getdate() /*load data file*/ SET @cmd = @exe_path+' /F '+@package_name +' '+@params_import exec p_log @RunID, @scriptname,'COMMAND' , @cmd EXEC xp_cmdshell @cmd /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID LineID RunID CallerID 1347

386

1348

386

1349

386

1350

386

Item

CallTime 11.4.2009 14:02 14_ImportSSISBulkInsert.sql START 11.4.2009 14_ImportSSISBulkInsert.sql LOG 14:02 11.4.2009 14:02 14_ImportSSISBulkInsert.sql COMMAND 11.4.2009 14_ImportSSISBulkInsert.sql FINISH 14:02

- 44 -

TextValue

NumberValue

@@spid: SSIS_20090411_140209.txt

61 NULL

C:\progra~1\micros~2\90\DTS\Bi... NULL Duration (ms):

29123

8.2.3 15_ImportSQLBulkInsert.sql /****************************************************************************************** * Script: Import data to table using BULK INSERT * Name: 15_ImportSQLBulkInsert.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: * Přepokládá se, že tabulka datový soubor i formátovací soubor mají stejný název * Předpokládá se existence cílové tabulky, cílová tabulka je před importem vyčištěna ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @sql varchar(1000) DECLARE @table_name varchar(1000) DECLARE @table_fullname varchar(1000) DECLARE @source_file varchar(1000) DECLARE @workfolder varchar(1000) DECLARE @scriptname varchar(1000) /*CONSTANTS*/ set @workfolder = 'f:\CDS\Development\BP\' set @scriptname = '15_ImportSQLBulkInsert.sql' /**************************************************************************/ SET @table_name ='BP_DataSample02' truncate table BP_DataSample02 /**************************************************************************/ SET @table_fullname ='reporting..'+@table_name SET @source_file = @workfolder+@table_name+'.txt'

/*START*/ exec p_log @RunID,@scriptname,'START' SET @CAS = getdate() /*load data file*/ SET @sql = 'BULK INSERT '+@table_fullname+ ' FROM '''+@source_file+''' WITH (FORMATFILE='''+@workfolder+@table_name+'.fmt'' )' exec p_log @RunID,@scriptname,'COMMAND' , @sql EXEC (@sql) /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) -- PRINT @i exec p_log @RunID,@scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID

LineID RunID CallerID 2199 2200 2201

Item

CallTime 13.4.2009 583 15_ImportSQLBulkInsert.sql START 12:41 13.4.2009 12:41 583 15_ImportSQLBulkInsert.sql COMMAND 13.4.2009 583 15_ImportSQLBulkInsert.sql FINISH 12:42

TextValue @@spid: BULK INSERT reporting..BP_Data...

NumberValue 58 NULL

Duration (ms):

8.2.4 16_ImportSSISDataFlowTask.sql /****************************************************************************************** * Script: Import data to table using SSIS package with DataFlowTask Process * Name: 16_ImportSSISDataFlowTask.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note:

- 45 -

16780

* Přepokládá se, že tabulka datový soubor i formátovací soubor mají stejný název * Předpokládá se existence cílové tabulky, cílová tabulka je před importem vyčištěna ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @cmd varchar(1000) DECLARE @exe_path varchar(1000) DECLARE @package_name varchar(1000) DECLARE @params_import varchar(1000) DECLARE @logfile varchar(1000) DECLARE @workfolder varchar(1000) DECLARE @scriptname varchar(1000) /*CONSTANTS*/ SET @exe_path = 'C:\progra~1\micros~2\90\DTS\Binn\DTExec.exe' set @workfolder = 'f:\CDS\Development\BP\' set @scriptname = '16_ImportSSISDataFlowTask.sql' /**************************************************************************/ SET @package_name =@workfolder+'BP_SSIS\DataFlowTask02.dtsx' truncate table BP_DataSample01 /**************************************************************************/ set @logfile = 'SSIS_'+dbo.f_getTimeSuffix()+'.txt' SET @params_import = '> '+@workfolder+'logs\'+@logfile /*START*/ exec p_log @RunID,@scriptname,'START' exec p_log @RunID,@scriptname,'LOG' , @logfile SET @CAS = getdate() /*create data file*/ SET @cmd = @exe_path+' /F '+@package_name +' '+@params_import exec p_log @RunID, @scriptname,'COMMAND' , @cmd print @cmd EXEC xp_cmdshell @cmd /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) -- PRINT @i exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID /* select count (*) from BP_DataSample01 select count (*) from BP_DataSample02 */

LineID RunID CallerID 1351

387

1352

387

1353

387

1354

387

Item

CallTime 11.4.2009 16:13 16_ImportSSISDataFlowTask.sql START 11.4.2009 16_ImportSSISDataFlowTask.sql LOG 16:13 11.4.2009 16:13 16_ImportSSISDataFlowTask.sql COMMAND 11.4.2009 16_ImportSSISDataFlowTask.sql FINISH 16:13

TextValue

NumberValue

@@spid: SSIS_20090411_161321.txt

92 NULL

C:\progra~1\micros~2\90\DTS\Bi... NULL Duration (ms):

8.2.5 19_ImportLinkedServerXLS.sql /****************************************************************************************** * Script: Import data from Excel to table using Linked Server method * Name: 19_ImportLinkedServerXLS.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: * Přepokládá se, že tabulka datový soubor i formátovací soubor mají stejný název * Předpokládá se existence cílové tabulky, cílová tabulka je před importem vyčištěna

- 46 -

17466

******************************************************************************************/ /*ADD SERVER*/ EXEC sp_addlinkedserver 'BP_Excel' , 'Jet 4.0' , 'Microsoft.Jet.OLEDB.4.0' , 'f:\CDS\Development\BP\BP_DataSample01.xls' -, 'f:\CDS\Development\BP\BP_DataSample02.xls' , NULL , 'Excel 5.0' --List the tables avalilable in the linked server. -- EXEC sp_tables_ex BP_Excel go use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @scriptname varchar(1000) /*CONSTANTS*/ set @scriptname = '19_ImportLinkedServerXLS.sql' /**************************************************************************/ truncate table BP_DataSample01 --truncate table BP_DataSample02 /**************************************************************************/ /*START*/ exec p_log @RunID, @scriptname ,'START' SET @CAS = getdate() /*insert*/ insert into BP_DataSample01 select * from BP_Excel...List1$ --insert into BP_DataSample02 select * from BP_Excel...List1$ /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID go /*DISCONNECT */ exec sp_dropserver 'BP_Excel'

LineID RunID CallerID

Item

1355

388 19_ImportLinkedServerXLS.sql START

1356

388 19_ImportLinkedServerXLS.sql FINISH

CallTime TextValue 11.4.2009 16:16 @@spid: 11.4.2009 16:16 Duration (ms):

NumberValue

8.2.6 20_ImportLinkedServerTXT.sql /****************************************************************************************** * Script: Import data from txt to table using Linked Server method * Name: 20_ImportLinkedServerTXT.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: * Přepokládá se, že tabulka datový soubor i formátovací soubor mají stejný název * Předpokládá se existence cílové tabulky, cílová tabulka je před importem vyčištěna * Ve zdrojovém adresáři by měl být soubor schema.ini ******************************************************************************************/ /*ADD SERVER*/ EXEC sp_addlinkedserver 'BP_txt' , 'Jet 4.0' , 'Microsoft.Jet.OLEDB.4.0' ,'F:\CDS\Development\BP\' ,NULL ,'Text' --List the tables avalilabla in the linked server.

- 47 -

70 9216

-- EXEC sp_tables_ex BP_txt go use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @scriptname varchar(1000) /*CONSTANTS*/ set @scriptname = '20_ImportLinkedServerTXT.sql' /**************************************************************************/ --truncate table BP_DataSample01 truncate table BP_DataSample02 /**************************************************************************/ /*START*/ exec p_log @RunID, @scriptname ,'START' SET @CAS = getdate() /*insert*/ insert into BP_DataSample02 select * from BP_txt...BP_DataSample02#txt /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID go /*DISCONNECT */ exec sp_dropserver 'BP_txt'


Item

1357

389 20_ImportLinkedServerTXT.sql START

1358

389 20_ImportLinkedServerTXT.sql FINISH


NumberValue

8.2.7 21_ImportTXTintoOneTable.sql /****************************************************************************************** * Script: Import data from txt to one column table using Linked Server method * Name: 21_ImportTXTintoOneTable.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: pokud je nahrávaná tabulka uvedena ve schema.ini, tak to neprojde ******************************************************************************************/ EXEC sp_addlinkedserver 'BP_logs_txt' , 'Jet 4.0' , 'Microsoft.Jet.OLEDB.4.0' , 'F:\CDS\Development\BP\logs' -, 'F:\CDS\Development\BP' , NULL , 'Text' -- List the tables avalilable in the linked server. -- EXEC sp_tables_ex BP_logs_txt go use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @scriptname varchar(1000) /*CONSTANTS*/ set @scriptname = '21_ImportTXTintoOneTable.sql' /**************************************************************************/ truncate table BP_ImportedTXT /**************************************************************************/ /*START*/ exec p_log @RunID, @scriptname ,'START' SET @CAS = getdate()

- 48 -

141 60533

/*insert*/ insert into BP_ImportedTXT select * from BP_logs_txt...[BP_DataSample01#txt] /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID /*DISCONNECT */ go exec sp_dropserver 'BP_logs_txt'


Item

1365

394 21_ImportTXTintoOneTable.sql START

1366

394 21_ImportTXTintoOneTable.sql FINISH


NumberValue

8.2.8 23_ImportSQLINSERT.sql /****************************************************************************************** * Script: Import data using one INSERT statment for each row * Name: 23_ImportSQLINSERT.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: generated using SQLINSERT_template.xls ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @scriptname varchar(1000) /*CONSTANTS*/ set @scriptname = '23_ImportSQLINSERT.sql' /**************************************************************************/ truncate table BP_DataSample01 /**************************************************************************/ /*START*/ exec p_log @RunID,@scriptname,'START' SET @CAS = getdate() /*do it*/ begin try drop table BP_DataSample01_SQLINSERT end try begin catch end catch create table BP_DataSample01_SQLINSERT(/*lineID int not null identity(1,1) , */LineNum int, ID int, col01 varchar(50), col02 int, col03 varchar(50), col04 varchar(50), col05 varchar(50), col06 varchar(50)) insert into BP_DataSample01_SQLINSERT select 1, 1561232989, '657063F2-DBA6-462E-812BAA1005D8C281', 12839, '23184,759', '7BA1F78F-3807-4CF6-8D69-C5127BE7B64A', 'B332AF27-3ECA4CF2-9843-3E8E637F8B34', '26.3.1955 5:01' insert into BP_DataSample01_SQLINSERT select 2, 1572635532, '61B21B5A-6884-40CD-A7F8CA58F5725646', 2852, '172236,5759', '75F7817F-E9EB-4B89-A2EE-2382FCC5BB2D', '6F934481-421F4B80-902B-2546929DE13A', '21.12.1989 13:57' insert into BP_DataSample01_SQLINSERT select 3, 364532215, 'EF1246F0-A6E6-463A-B43260477DDBEA0A', 19317, '93808,535', '8DACA76F-7245-4A0D-9ABF-27C82D25487F', 'B362CCF3-946146E4-B431-F335B0252234', '26.6.2003 3:34' /*...*/ insert into BP_DataSample01_SQLINSERT select 9999, 1666194568, '3200A828-D07D-4591-BF80F1D2F51F6128', 8013, '205094,3362', '62F0925C-4A02-47D6-94EB-3B50ACDE51AE', '6F9195D9-F8334B96-8947-E8C49449E122', '16.11.1986 1:07' insert into BP_DataSample01_SQLINSERT select 10000, 642667779, 'FB020A3E-F680-470B-A5EE62869AC19197', 8681, '207912,9068', '9CAA3730-16EE-4657-92E5-3DBE3DBB5E8D', 'BC562E41-B63D4534-A37E-D4EC13F29357', '4.1.1976 3:05' /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i

- 49 -

102 99750

/*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID


Item

1363

393 23_ImportSQLINSERT.sql START

1364

393 23_ImportSQLINSERT.sql FINISH


NumberValue 105 95360

8.2.9 24_ImportOPENROWSET_TXT.sql /****************************************************************************************** * Script: Import data from TXT using OPENROWSET * Name: 24_ImportOPENROWSET_TXT.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @scriptname varchar(1000) /*CONSTANTS*/ set @scriptname = '24_ImportOPENROWSET_TXT.sql' /**************************************************************************/ truncate table BP_DataSample01 --truncate table BP_DataSample02 /**************************************************************************/ /*START*/ exec p_log @RunID, @scriptname ,'START' SET @CAS = getdate() /*insert data*/ insert into BP_DataSample01 SELECT A.* FROM OPENROWSET( BULK 'F:\CDS\Development\BP\BP_DataSample01.txt', FORMATFILE = 'F:\CDS\Development\BP\BP_DataSample01.fmt') AS A; /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID


Item

1367

395 24_ImportOPENROWSET_TXT.sql START

1368

395 24_ImportOPENROWSET_TXT.sql FINISH


NumberValue

8.2.10 25_ImportOPENDATASOURCE_XLS.sql /****************************************************************************************** * Script: Import data from TXT using OPENDATSOURCE * Name: 25_ImportOPENDATASOURCE_XLS.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME

- 50 -

94 16110

DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @scriptname varchar(1000) /*CONSTANTS*/ set @scriptname = '25_ImportOPENDATASOURCE_XLS.sql' /**************************************************************************/ truncate table BP_DataSample01 --truncate table BP_DataSample02 /**************************************************************************/ /*START*/ exec p_log @RunID, @scriptname ,'START' SET @CAS = getdate() /*insert data*/ insert into BP_DataSample01 SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=F:\CDS\Development\BP\BP_DataSample01.xls;Extended Properties=EXCEL 5.0')...[List1$] ; /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID

8.2.11 26_ImportOPENROWSET_MDB.sql /****************************************************************************************** * Script: Import data from MDB using OPENROWSET * Name: 26_ImportOPENROWSET_MDB.sql * Author: Ondřej Spálenka * Date: April 2009 * Project: Bakalářká práce BIVŠ * Note: ******************************************************************************************/ use reporting /*DECLARATIONS*/ DECLARE @CAS DATETIME DECLARE @RunID int;EXECUTE [p_getRunID] @r=@RunID OUTPUT DECLARE @scriptname varchar(1000) /*CONSTANTS*/ set @scriptname = '26_ImportOPENROWSET_MDB.sql' /**************************************************************************/ truncate table BP_DataSample01 --truncate table BP_DataSample02 /**************************************************************************/ /*START*/ exec p_log @RunID, @scriptname ,'START' SET @CAS = getdate() /*insert data*/ insert into BP_DataSample01 SELECT A.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'F:\CDS\Development\BP\BP_DataSample.mdb'; 'admin';'',BP_DataSample01) A /*FINISH*/ declare @i int; set @i = datediff ( ms , @CAS , getdate() ) exec p_log @RunID, @scriptname,'FINISH','Duration (ms):' , @i /*REPORT*/ select LineID,RunID,CallerID,Item,CallTime,TextValue=dbo.f_myLeft(TextValue,30),NumberValue from BP_LOG where RunID = @RunID


Item

1375

399 26_ImportOPENROWSET_MDB.sql START

1376

399 26_ImportOPENROWSET_MDB.sql FINISH

- 51 -


NumberValue 58 90766

- 52 -

Případová studie aplikace ETL procesů v bankovním prostředí

Recommend Documents