Artykuł AFIN.NET:
AFIN.NET.InformationServices © AFIN 2002-2009
Wojciech Gardziński, 2009.05
Pełna automatyzacja wszelkich procesów zasilania w
dane i przetwarzania ich dla celów własnego systemu informacyjnego w AFIN.NET
Summary in English:
AFIN.NET.Information.Services
(AFIN.NET.IS) is a tool for making simple programs, simulating professional
SSIS (SQL Server Information Services) procedures. You can make a very
complicated, multilined SQL-based program to extract data from original
databases, transform it to desired type of files (filling up a sophisticated
data warehouse), connect to your own dimension tables and run the OLAP cube
creating process based on a ready .oqy
file (OLAP cube definition file, created formerly by the Excel Cube Wizard).
All the processes are described in an Excel sheet and are runable by one mouse
click or the shortcut [Ctrl+Q]. The best way to learn how it works – use an
AFIN.NET.IS sample.
www.afin.net/samples/AFIN.NET.InformationServices/
Co potrafi AFIN.NET.InformationServices?
(Korzyści dla użytkownika)
- Pobierać (bardzo szybko) dowolne dane zewnętrzne
do arkusza Excela
- Przetwarzać dowolne dane zewnętrzne, w tym
również odświeżać, modyfikować oraz zapisywać dane w dowolnym formacie
bazodanowym
- Zasilać plikowe kostki OLAP – pliki
.cub (tzn. odświeżać lokalne hurtownie danych lub ich części)
- Wykonywać szereg działań na skoroszytach Excela,
np. przeliczać i publikować je wsadowo (tzn. programowalnie)
- Wiele więcej. Patrz:
http://afin.net/samples/AFIN.NET.InformationServices/AFIN.NET.IS_SamplesLibrary.xls
Interfejs użytkownika
Interfejsem narzędzia
AFIN.NET.InformationServices (AFIN.NET.IS) jest arkusz Excela z ustawionymi,
predefiniowanymi nazwami kilku obszarów w tym arkuszu oraz przycisk „Start”,
dzięki któremu można łatwo uruchomić program.
Co potrafi AFIN.NET.IS?
(szczegóły techniczne)
- Wykonać dowolną ilość poleceń krok po kroku
- Składać dowolne zapytania w języku SQL (SQL MS
Jet) do dowolnych baz danych ODBC i OLE DB, np.
- SELECT… - wybór danych celem wklejenia ich do
arkusza Excela
- SELECT…INTO… – wybór danych do innej tabeli w
danej bazie danych
- SELECT…INTO…IN… – wybór danych do innej tabeli
w innym systemie bazodanowym (np. pobór danych z pliku
.txt i umieszczenie ich w bazie Accessa), dowolne przetwarzanie
danych, zgodnie z możliwościami języka SQL
- SELECT… FROM tabela1 INNER JOIN tabela2
(Odtwarzanie relacji bazy danych, tworzenie szerokich tabel)
- UPDATE… modyfikacje zawartości tabel
- CREATE (ALTER, DROP) TABLE… - zdalne operacje
na tabelach
- Operacje na kolumnach, składanie kolumn,
wszystkie funkcje SQL, formuły obliczeniowe (w tym funkcje VBA),
sortowanie, grupowanie danych, własne warunkowe atrybutowanie danych
- Wklejać dane zewnętrzne do arkusza Excela bez
użycia kwerend MS Query
- Zarządzanie procesem dowolnej ilości tego typu operacji
w jednym miejscu i za pomocą jednego przycisku startowego
- Dowolne parametryzowanie zapytań, np. wskazanie
bazy źródłowej wartością w arkuszu (niemożliwe w MS Query)
- Brak operacji języka VBA – brak błędów
związanych z makrami
- Brak błędów przy przesyłaniu lub przenoszeniu skoroszytu
z kwerendami
- Obsługa kwerend internetowych
- Wykonywać wszelkie komendy Shell (~DOS)
- Kopiowanie plików
- Wszelkie operacje na systemie plików
- Sterowane parametrami z arkusza
- Wykonywać operacje na skoroszytach Excela
- Programowe przeliczanie skorosztów w zadanej
kolejności
- Publikacja danych ze skoroszytów sterowana
programem
- Odświeżać plikowe kostki OLAP, tzw. pliki .cub na bazie istniejącej kwerendy definiującej
kostkę, tj. pliku .oqy
Jak rozpocząć pracę?
(najprostszy przykład)
Poniższy przykład jest
gotowy do uruchomienia:
Plik: ‘[AFIN.NET.Folder]\Samples\Cases\TheVeryFirstStep_AFIN.NET.IS.xls’
|
Source |
Query |
ResultRange |
RangeName |
TimeInfo |
|
Driver={Microsoft
Excel Driver (*.xls)}; DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInExcel.xls |
SELECT *
FROM faktura |
[]Data1!A1 |
_Data1Range |
0,953 |
- Source
Jest to tzw. „ciąg połączenia bazodanowego” (ang. ‘connection string’), może być podany w stylu ODBC: ‘Driver=…’, w stylu ODBC DSN: ‘DSN= …’ lub w stylu OLE DB: ‘Provider=…’
Określa podstawową bazę danych danej operacji bazodanowej. Oprócz tego, bazę danych można określać w samym zapytaniu SQL (patrz poz. ‘Query’)
np. ‘Driver={Microsoft Excel Driver (*.xls)}; DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInExcel.xls’ - Query
Jest to tzw. ‘zapytanie’ bazodanowe (ang. ‘query text’) – łańcuch znaków, stanowiący ‘komendę do wykonania na bazie danych’, zrozumiały dla sterownika ODBC lub OLE DB, napisany w języku SQL (MS Jet SQL + funkcje VBA), np. ‘SELECT * FROM faktura’, gdzie ‘faktura’ to tabela w pliku ‘SalesInExcel.xls’ - ResultRange
Miejsce w arkuszu, gdzie zostaną wklejone dane, w formacie:
[NazwaSkoroszytu.xls]NazwaArkusza!AdresKomórki, np.
’[MójSkoroszyt.xls]Dane!A1’ lub, gdy ma to być skoroszyt, w którym jest napisany program IS, można zapisać to nie podając jego nazwy w „[ ]”, np.
’[]Data1!A1’. Polecenia przetwarzające dane w bazie zewnętrznej, niewklejające danych do arkusza, nie wymagają podawania tego parametru. - RangeName
Nazwa, jaka ma zostać (każdorazowo) nadana wklejonym danym. Ułatwia to np. znacznie współpracę z tabelą przestawną, opartą na tej nazwie (nie na regionie arkusza) – nazwa, przypisana do danych, zmienia swój zakres wraz ze zmianą ilości danych. Nazwa musi być zgodna z ‘konwencją nazewnictwa Excela’, tu: ’_Data1Range’
Parametr niewymagany. - TimeInfo
Informacja o czasie wykonywania danej komendy, każdorazowo uaktualniana przez program, tu: ‘0,953’ [sekundy]
Opisywana linia, wykonując
komendę SQL ‘SELECT * FROM faktura’
pobiera wszystkie (*) dane z tabeli ‘faktura’,
w źródle danych, określonym ciągięm tekstowym ‘Driver={Microsoft Excel Driver (*.xls)};
DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInExcel.xls’,
tzn. ze źródła danych excelowego (plik Excela), ze skoroszytu SalesInExcel.xls,
umieszczonego w folderze ‘[AFIN.NET.Folder]\Samples\Data\ODBC’,
gdzie ‘[AFIN.NET.Folder]’ oznacza
sciężkę instalacji systemu AFIN.NET, np. ‘C:\Program
files\AFIN.NET’.
Pobranie danych następuje do
skoroszytu, w którym jest uruchamiany program, do arkusza ‘Data1’, do obszaru, rozpoczynającego się w lewym, górnym rogu
komórką ‘A1’. Po wklejeniu danych,
obszarowi danych zostaje nadana nazwa ‘_Data1Range’.
Jak wprowadzać definicje?
Programowanie przetwarzania
danych w SQL jest niewątpliwie domeną informatyków – dlatego
użytkownicy hurtowni danych kojarzą ten proces z długotrwałymi wdrożeniami,
specyfikacjami itp.
W AFIN.NET.IS wszystko jest
jawne, gdyż definiowane w arkuszu Excela. Komendy SQL, przetwarzające dane –
również. Ale, przykłady też! Specjalny skoroszyt przykładowy ‘AFIN.NET.IS_SamplesLibrary.xls’ dostarcza mnóstwo przykładów, jak wykonać
podstawowe, ale nie tylko, komendy. W większości przypadków wystarczy po prostu
wykopiować przykładowy tekst takiej komendy, przekleić do własnego „programu”,
zmodyfikowac ustawienia dot. ścieżek dostępu i … gotowe.
W trudniejszych i bardziej
skomplikowanych przypadkach, zawsze można wezwać na pomoc informatyka, który
podstawy SQL niewątpliwie zna.
Zawsze w odwodzie pozostają
konsultanci AFIN.NET.
Jak parametryzować definicje poszczególnych linii programu?
W AFIN.NET.IS wszelkie
definicje procesu przetwarzania danych są tworzone w arkuszu Excela, a
konkretnie w wartościach tekstowych w odpowiednich miejscach tego arkusza.
Wiele z nich jest stałych, np. definicja sterownika ODBC, w przykładzie ‘Driver={Microsoft Excel Driver (*.xls)};’, ale większość zależy od parametru, np. ścieżki
położenia pliku ‘DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInExcel.xls’ lub np. tylko jej fragmentu ‘DBQ=[AFIN.NET.Folder]\Rok2008\SalesInExcel.xls’
- tu np. chętnie sparametryzowalibyśmy rok, ponieważ w folderze ‘Rok2008’ skoroszyt ‘SalesInExcel.xls’ zawiera dane z 2008
roku, itp.
Można to zrobić zwykłą formułą
Excela. Najpierw należy cały tekst na taką formułę zamienić (znak ‘=’ na
początku formuły, a cały tekst w podwójnym cudzysłowiu):
=”Driver={Microsoft Excel Driver (*.xls)};DBQ=[AFIN.NET.Folder]\Rok2008\SalesInExcel.xls”
Gdy zechcemy posiadać
parametr roku np. w komórce ‘E1’ w
arkuszu sterującym, wpisujemy tam ‘2008’, a powyższę formułę wiążemy z
wartością w ‘E1’ tak:
=”Driver={Microsoft Excel Driver (*.xls)};DBQ=[AFIN.NET.Folder]\Rok”&$E$1&”\SalesInExcel.xls”
Gotowe. Zmiana parametru w
E1 np. na ‘2009’ zmieni formułę,
która oblicza ciąg tekstowy źródła danych i dane pobierają się z folderu ‘…\Rok2009’.
Takie parametry można nadać
dowolnym fragmentom wszystkich, występujących wartości, również zapytaniom w
SQL oraz np. komendom Shell (DOS)
Przykład bardziej skomplikowany
” Tworzymy kostkę OLAP z wielu plików DBF”
(W białych wierszach
komentarze i opis dot. linii poniżej komentarza)
|
Source |
Query |
|
Komenda
DOSowa |
Skasuj
plik tymczasowej bazy Accessowej |
|
DOS |
Del
[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb |
|
|
Skasuj
plik docelowej kostki OLAP – plik .cub |
|
DOS |
Del [AFIN.NET.Folder]\Samples\Data\OLAP\StandardSalesCube\StandardSalesCube.cub |
|
Czekaj… |
…1 sekundę (aby procesy DOSowe zdążyły się zakończyć) |
|
wait |
00:00:01 |
|
|
Skopiuj
pustą, tymczasową bazę Accessową z wzorca, znajdującego się w folderze
wzorców AFIN.NET |
|
DOS |
copy
[AFIN.NET.Folder]\Templates\AccessBaseTemplate.mdb
[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb |
|
wait |
00:00:03 |
|
|
|
|
Wskazanie
źródła danych ODBC, tu: tymczasowej bazy Accessowej, przed chwilą utworzonej
(skopiowenej) z wzorca |
SQL-owa
definicja tabeli StandardSalesTable
z następującymi polami (kolumnami): State,
City, CG1_Level1, CG1_Level2,
CG2_Level1, CG2_Level2, CustomerName,
DocId, PG_Level1, PG_Level2, ProductName, Price, Date, Quantity, Volume – wraz z określeniem typów tych pól |
|
Driver={Microsoft
Access Driver (*.mdb)};DBQ=[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb |
CREATE
TABLE StandardSalesTable ([Country] TEXT(20), [State] TEXT(20), [City]
TEXT(20), [CG1_Level1] TEXT(20), [CG1_Level2] TEXT(20), [CG2_Level1]
TEXT(20), [CG2_Level2] TEXT(20), [CustomerName] TEXT(20), [DocId] TEXT(10),
[PG_Level1] TEXT(20), [PG_Level2] TEXT(20), [ProductName] TEXT(20), [Price]
DOUBLE, [Date] DATE, [Quantity] DOUBLE, [Volume] DOUBLE) |
|
Wait |
00:00:01 |
|
|
|
|
Wskazanie
źródła danych ODBC, tu: bazy danych typu DBF, czyli wskazanie katalogu z
plikami DBF |
Pobierz
dane z pliku (tabeli) DBF i umieść go w tymczasowej bazie Accessowej, tu:
tabela ‘region’ z pliku ‘region.dbf’ |
|
Driver={Microsoft
dBase Driver (*.dbf)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInDBF |
SELECT *
INTO region IN
'[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb' FROM region |
|
|
j.w. tabela ‘odbiorca’ |
|
Driver={Microsoft
dBase Driver (*.dbf)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInDBF |
SELECT *
INTO odbiorca IN
'[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb' FROM odbiorca |
|
|
j.w. tabela ‘faktura’ |
|
Driver={Microsoft
dBase Driver (*.dbf)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInDBF |
SELECT *
INTO faktura IN
'[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb' FROM faktura |
|
|
j.w. tabela ‘pozycja’ |
|
Driver={Microsoft
dBase Driver (*.dbf)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInDBF |
SELECT * INTO
pozycja IN
'[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb' FROM pozycja |
|
|
j.w. tabela ‘towar’ |
|
Driver={Microsoft
dBase Driver (*.dbf)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInDBF |
SELECT *
INTO towar IN '[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb' FROM towar |
|
|
|
|
Wskazanie
tymczasowej bazy Accessowej |
Połącz
wszystkie powyższe tabele: regio, odbiorca, faktura, pozycja, towar w jedną
szeroką tabelę tymczasową ‘WideTable’,
posługując się kluczami relacji, określonymi tu w sekcji ‘WHERE …’ |
|
Driver={Microsoft
Access Driver (*.mdb)};DBQ=[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb |
SELECT
region.*, odbiorca.*, faktura.*,
pozycja.*, towar.* INTO
WideTable FROM region, odbiorca, faktura, pozycja, towar WHERE region.miasto=odbiorca.miasto AND
odbiorca.nazwa=faktura.nazwa
AND faktura.nr_fakt=pozycja.nr_fakt
AND pozycja.indeks_tow=towar.indeks_tow |
|
|
|
|
|
Wstaw dane
z tabeli ‘WideTable’ do tabeli ‘StandardSelesTable’, dodając do niej
dodatkowe pole ‘Country’ z domyślną
wartością ‘PL’; niektóre pola
(nieużywane) wypełniane są stałymi, domyślnymi wartościami, np. pole ‘PG_Level1’ ma wartość ‘All’ |
|
Driver={Microsoft
Access Driver (*.mdb)};DBQ=[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb |
INSERT
INTO StandardSalesTable SELECT 'PL' AS [Country], region AS [State],
region_miasto AS [City], 'All' AS [CG1_Level1], gr_odb AS [CG1_Level2], 'All'
AS [CG2_Level1], 'All' AS [CG2_Level2], odbiorca_nazwa AS [CustomerName],
faktura_nr_fakt AS [DocId], 'All' AS [PG_Level1], grupa AS [PG_Level2], towar
AS [ProductName], cena_jedn AS [Price], data AS [Date], ilosc AS [Quantity],
wart_zl AS [Volume] FROM WideTable |
|
|
|
|
wait |
00:00:01 |
|
|
|
Powyższa
i poniższa część tabeli to jedna i ta sama tabela definicji AFIN.NET.IS, z tym,
że wyłącznie w celu uzyskania miejsca dla szerszych opisów, w powyższej nie
pokazano kolumn ‘ResultRange’ i ‘RangeName’, gdyż dla powyższych
definicji i tak pozostawałyby puste. W definicjach poniżej – mają one
znaczenie, więc je pokazano.
|
Source |
Query |
ResultRange |
RangeName |
|
Źródło:
tymczasowa baza Accessowa |
Pobierz
wszystko z tabeli ’StandardSalesTable’ |
Wklej dane
w bieżącym
skoroszycie do arkusza ‘Data1’ do
komórki ‘A1’ (tu: nie podano, domyślnie:
‘A1’) |
Nazwij
obszar danych: ’_Range1’ |
|
Driver={Microsoft
Access Driver (*.mdb)};DBQ=[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb |
SELECT *
FROM StandardSalesTable |
[]Data1 |
_Range1 |
|
|
|
|
|
|
Na
podstawie tak określonego źródła danych… |
…oraz na
podstawie definicji kostki, zapisanej we wskazanym pliku
.oqy… |
Utwórz
kostkę OLAP – plik .cub we wskazanej lokalizacji |
|
|
Source_DSN="Driver={Microsoft
Access Driver (*.mdb)};DBQ=[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.InformationServices\Temp\WorkBase.mdb" |
OQY=[AFIN.NET.Folder]\Samples\Data\OLAP\PredefinedCubeDefinitions\StandardSalesCube.oqy |
CUB=[AFIN.NET.Folder]\Samples\Data\OLAP\StandardSalesCube\StandardSalesCube.cub |
|
W taki sposób można stworzyć
lub odświeżyć (tworząc ją od nowa) kostkę OLAP – plik .cub
z 5 osobnych plików typu DBF, odtwarzając relacje pomiędzy tabelami w tych
plikach – jednym naciśnięciem przycisku ‘Start’
Sumaryczny czas takiego
procesu – mniej niż 10 sekund.
Dalsze kroki
Programu AFIN.NET.IS najlepiej
nauczyć się go wykorzystując jeden z wielu przykładów AFIN.NET.IS, dostępnych
na:
www.afin.net/samples/AFIN.NET.InformationServices