Oracle & AFIN.NET

Klasyczny pobór danych z poważnej bazy danych (tu Oracle w.    
Express Edition) a pobór danych przez Excela (Query) i    
automatyzacja w AFIN.NET

Film:
http://afin.net/webcasts/Demo_ProfiDatabaseUI_vs_AFIN.NET.swf

Na filmie pomyłkowo popełniłem próbę definicji i pobrania    
danych przez uniwersalny sterownik ODBC - jak widać, się nie    
udało - zadziałał dopiero natywny sterownik ODBC Oracle'a.


Porównanie funkcjonalności:

Oracle - nienajlepszy edytor zapytań - wszystko trzeba    
ręcznie. Co prawda jest 'Query Builder', ale interfejs jest tam    
już 'prawdziwie informatyczny'. Całą tabelę można pobrać    
dość łatwo, ale zapytanie z filtrem to już pół doktoratu -    
lepiej więc (kto zna) pisać SQL-ki.
Pobranie do Excela to już jednak koszmar. Eksport tylko jako csv,    
który, otwierany Excelem, każdorazowo trzeba od nowa ustawiać,    
przerabiać, itp.

Excel & Query
Działa. Przyjazny interfejs, nie trzeba znać SQLa, dane można    
przerabiać, jak się chce...
Nie można, co prawda, przesyłać skoroszytów z Query (i kilka    
innych wad), ale dane są bezpośrednio i szybko w arkuszu Excela.

AFIN.NET
Pobieramy z kwerendy definicję ciągu połączeniowego i SQLek -    
możemy go zresztą też wpisać ręcznie, albo skądś skopiować    
(tu, dla przykładu, 2-gie zapytanie SQL wykopiowałem z ...    
Oracle'a.)
I pyk - sto tabel odświeżone.

Wydajność ADO&AFIN.NET vs. Excel2010&PowerPivot

Temat:
"In Memory Analytics" - najnowszy trend w Business Intelligence..
Czy to jest efektywne?

Mamy bazę (tabelę) - 3 MILIONY REKORDÓW w formacie DBF oraz    
słownik excelowy 19 rekordów - standardowa sytuacja. Wszystkie    
dostępy zdefiniowane, bo nie będziemy tracić czasu na klikanie...

Chcemy tylko ODŚWIEŻYĆ DANE.

(Uwaga: Film jest długi i niewiele się dzieje - większość czasu    
to oczekiwanie na zasilenie danych - można przewijać...)

Film:
http://afin.net/webcasts/Demo_RefreshData_ADO&AfinNet_vs_Excel2010&PowerPivot.swf

Rezultaty:
ADO & AFIN.NET - 32 sekundy
Excel 2010 & PowerPivot - 110 sekund

Funkcje OLAP w AFIN.NET i w Excelu 2010 - porównanie

Film:
http://afin.net/webcasts/Demo_OlapFunctions_AfinNetVsExcel2010.swf


Dla porównania formułki pokazywanych funkcji:

Czysty Excel 2010:
=CUBEVALUE("stany";$B$4;$B5;C$4)

Prosty zapis, ale wymaga istnienia w arkuszu TRZECH(!) innych    
funkcji:
=CUBEMEMBER("stany";{"[DateYYYY].[Wszystkie].[2007]"\"[DateMM].[Wszystkie].[01]"})
=CUBEMEMBER("stany";"[a01].[Wszystkie].[1]")
=CUBEMEMBER("stany";"[Measures].[S_MW]")
Można dać je, oczywiście, do jednej formuły, ale wtedy ta    
formuła będzie bardzo długa i skomplikowana.

AFIN.NET (prosty import ustawień z tabeli przestawnej):
=GETDATAOLAP("C:\Program    
Files\AFIN.NET\Samples\Data\OLAP\FK3olap\stany.cub";"OCWCube";"OCWCube";"[Measures].[S_MW]";"[a01].[1],[DateYYYY].[2007],[DateMM].[01]";)
Czytaj: Wszystkie ważne parametry są w jednej, czytelnej, łatwo    
pobieralnej funkcji, w której można również sparametryzować...    
źródło OLAP, czyli np. wersję kostki (W funkcji Excelowej nie    
można).

AFIN.NET (Zdefiniowana księgowa miara biznesowa - to samo    
źródło danych):
=GETDATA("Financials.3.Cub";"mw/1";"2007";"01")
Czytaj: prostota do bólu.

To samo, ale z baaardzo rozbudowanym zapytaniem księgowym w AFQL:
=GETDATA("Financials.3.Cub";"mw/1+mw/2-sw/3+mw/4-sm/401+ow/4*";"2007";"01")
Czytaj: elastyczność zapytania księgowego - rownież do bólu.

Wnioski:
Cóż...jakby to powiedzieć... :)

Excel 2010

Łi kajndli inform ju, det EjfinDotNet łorks on Excel 2010 Beta.

Film potem.

Pierwsze uwagi nt. Excela 2010 - kaszanka :(
Nie ma guzika Office, a taki ładny był, fajnie współgrał z    
guzikiem Visty - nie wiadomo było, który jest który i po co oba.

Poprawili Query! (Ikonki się pokazują od razu, bo w E2007 nie).
I... tyle.

Nic w temacie funkcji bazodanowych, nic w temacie ułatwień    
dostępu...
Tylko trochę ładniejszy, bardziej 'kwadratowy'.
Czyli: WYSZUKAJ.PIONOWO() ładniej opakowane.

Ale to dopiero początek rozpracowywania.

Wielostopniowe przetwarzanie danych

Zadanie, dane i natchnienie:
(C) 2009 - Mariusz Jankowski (Dzięki!)
http://www.goldenline.pl/forum/excel-vba/1326580

Problem:
Do tej pory skupialiśmy się na pobieraniu danych z różnych    
źródeł i wklejaniu ich w arkusz w formie małoprzetworzonej, poza    
prostymi łączeniami tabel. Niekiedy jednak zachodzi konieczność    
znacznego przetworzenia danych, nawet, gdy wynikiem ma być mała,    
prosta tabela.
Zwykle robi się to poprzez napisanie skomplikowanego zapytania w    
języku SQL, tzw. zagnieżdżonego SQL, czyli zapytania, które    
najpierw buduje jakiś widok danych, a dopiero potem łączy go z    
inną tabelą lub innym widokiem.

Działa, ale:
1. Trzeba mieć narzędzie, żeby to napisać. Zwykłe edytory SQL    
nie oferują edycji zapytań zagnieżdżonych.
2. Trzeba umieć to napisać, a taki SQL może być bardzo    
skomplikowany.

Wyjściem jest (wielo-)stopniowe przetwarzanie danych, ale to z    
kolei oferują już tylko profesjonalne narzędzia DTS-owe (Data    
Transforming Systems). Trzeba je mieć, umieć włączyć (nie takie    
to czasami proste, serio), rozumieć, co te narzędzia do nas    
mówią... ech, skomplikowane :(

Albo (filmik):
http://afin.net/webcasts/Demo_ProcessingDataMultistep.swf

Opis:
1. Dane są w dwóch arkuszach jednego pliku Excela. Pobieramy je    
kwerendą, tworząc relację i, od razu, ranking (1.) zespołów    
według średnich wzrostów zawodników.
Tworzy się tabela w bazie Accessowej 'MyTable'
2. Tworzymy kwerendę z tej tabeli tworząc inną tabelę - kolejny    
ranking, tym razem ranking (2.) dywizji według maksymalnych    
średnich zespołów.
Tworzy się kolejna tabela 'MyTable2'
3. Tworzymy kolejną kwerendę, łączącą dane rankingu 2. z    
informacjami o zespołach, osiągających te średnie, czyli    
łączymy ranking 2. z rankingiem 1.
Powstałą tabelę pobieramy do arkusza.

Raport tworzy się natychmiast jednym kliknięciem myszy.
Nie napisałem ręcznie nic.

Konsolidacja pozioma, czyli jak z wielu kolumn zrobić tabelę przestawną.

Problem:
Tabela przestawna działa fajnie, gdy dane numeryczne znajdują się    
w jednej kolumnie, a obok, w tych samych wierszach, ale innych    
kolumnach znajduje się możliwie jak najwięcej atrybutów, tych    
danych dotyczących.
Ale nie zawsze tak jest. Czasami eksporty różnych systemów na    
siłę ustawiają nam dane w różnych kolumnach, a czasami...    
robimy to sami, bo tak w Excelu najłatwiej się dane wpisuje - w    
końcu arkusz Excela to fajna tabelka ;)

Filmik:
http://afin.net/webcasts/Demo_DataConsolidation.swf

Opis:

1. Najpierw kwerenda, wzbogacona o wymiary, które nas 'bolą':
Dni - bo dane w kolumnach, a my chcemy w jednej
Miesiące - bo dane w arkuszach, a my chcemy w jednym

2. Pobranie definicji kwerendy do szablonu AFIN.NET.IS

3. Parametryzacja definicji na dwa powyższe parametry

4. Skopiowanie w tyle wierszy, ile jest kombinacji parametrów
(Informatyk zauważy, że można to zrobić lepiej, czyli dużo    
krócej. Tu celem jest prostota, nie efektywność)

5. Goł!
Zero VBA, zero formuł - łatwe. Powtarzalne, szybkie, efektywne,    
bezbłędne...

Własna funkcja - VLOOKUP do plików zamkniętych

Problem:
http://www.goldenline.pl/forum/excel-vba/1303380/s/1

Filmik:
http://afin.net/webcasts/HowTo_CreateMyFunctionVLOOKUP.swf

Opis:
Excel czegoś nie potrafi? Zrób to sam w AFIN.NET.

Publikacja raportu na... grupie dyskusyjnej Google

Film:
http://afin.net/webcasts/Demo_PublishingOnGoogle.swf

Opis:
Opublikuj coś AFINEM!
(Coś mało ważnego, bo Cię Prezes okrzyczy ;) )
afinnet@googlegroups.com

Link do grupy (pliki na grupie mogą się zmieniać i być kasowane)
http://groups.google.pl/group/afinnet

Wnioski:
Jednym z koronnych "dowodów" na wyższość systemów Business    
Intelligence nad Excelem jest możliwość publikowania raportów w    
sieci rozległej, czyli Internecie.
Że serwer jest firmowy, że strasznie zabezpieczony, itp., itd.
Czy może być coś bardziej DOSTĘPNEGO i bardziej ZABEZPIECZONEGO    
od Google'a - tam pracuje nad tym ogromny sztab ludzi!
Tu grupa jest otwarta dla wszystkich, nawet nie trzeba być    
zalogowanym (co pokazano na filmie) żeby zarówno publikować, jak    
i przeglądać - normalnie oczywiście tak być nie może i Google    
to zapewnia.

Koszt takiego firmowego serwera publikacji raportow = 0 zł.

A publikacja jest prosta - jak to w AFIN.NET...

Funkcja GETDATAOLAP - Raportowanie z hurtowni danych OLAP

Film:
http://afin.net/webcasts/HowTo_InsertFunction_GETDATAOLAP.swf


Opis:
Załóżmy, ze posiadamy już tzw. wielowymiarową hurtownię danych    
OLAP.
Może ona być w wersji "profesjonalnej", czyli np. w MS SQL Server    
Analysis Services (SSAS) lub w plikach .cub, tj. wersji dla    
mniejszej firmy lub wersji prototypowej - nie ma to najmniejszego    
znaczenia - w obu przypadkach istnieje możliwość "zrzutu" danych    
do Excela w formie excelowej tabeli przestawnej, podłączonej    
bezpośrednio do zewnętrznego, OLAP-owego źródła danych.

Gdzie jest problem?
Właściwie nigdzie - tabela przestawna na zewnętrznym źródle    
danych działa bardzo dobrze - bardzo szybko i wygodnie.
Ale...
Gdy chcemy stworzyć raport - nasz własny raport - w którym    
poszczególne informacje mają znaleźć się w bardzo konkretnych    
miejscach naszego raportu - tabela przestawna staje się    
ograniczeniem!
Tabela ma jedno źródło danych, w tabeli nie wszystkie elementy    
widać naraz, tabeli nie da się ustawić w dowolny sposób, tabeli    
nie da się zmieścić w jednej komórce arkusza (a my chcemy mieć    
dane z różnych tabel bezpośrenio jedna pod drugą...), itp.
Prowizorycznym rozwiązaniem jest excelowa funkcja WEŹDANETABELI,    
ale ma ona wadę - tabela MUSI być otwarta, a dane MUSZĄ być w    
niej "widoczne" - Jest z tym problem.
Gdy tabel (takich źródeł danych) jest dużo, albo tabele są    
duże (np. wykraczają poza arkusz), funkcja WEŹDANETABELI staje    
się bezużyteczna - poza tym, trzeba pamiętać o otworzeniu    
wszystkich tabel...

Najczęściej stosuje się w tym celu dodatkowy program pakietu SQL    
Server - SQLS Reporting Services, ale stworzenie w nim jakichkolwiek    
definicji wymaga dużej wiedzy informatycznej (znajomość języka    
MDX), a stworzenie na jednym raporcie wielu źródeł danych stanowi    
duży problem.
Poza tym, raportów nie tworzy się tam tak łatwo i intuicyjnie,    
jak w Excelu...

AFIN.NET oferuje funkcję GETDATAOLAP, której działanie jest, w    
gruncie rzeczy, bardzo podobne do WEŹDANETABELI. Dane z tabeli    
pobiera się równie prosto - 'prawy przycisk myszy' / 'AFIN.NET...'    
/ 'Pobierz jako funkcję'...
I gotowe!
Ale funkcja GETDATAOLAP nie wymaga, żeby tabela przestawna była    
otwarta (a nawet w ogóle istniała) - tabela jest potrzebna    
wyłącznie do STWORZENIA FUNKCJI.

A parametryzowanie wartościami wymiarów - już standardowo, jak    
wszystkie funkcje w AFIN.NET

Parametryzowanie jedną wartością wielu funkcji, odwołujących    
się do różnych źródeł danych, różnych wymiarów kostek itp.    
- jak wyżej, bezproblemowo.

Funkcja GETDATAODBC - Uniwersalna funkcja bazodanowa

Film:
http://afin.net/webcasts/HowTo_InsertFunction_GETDATAODBC.swf

Opis:
Excel jest naprawdę bardzo sympatyczną i pełną możliwości    
tabelką.
Ale, w swoim standardzie, nie posiada funkcji, które potrafią    
sięgnąć "na zewnątrz", do innego, dowolnego środowiska    
bazodanowego, własnych skoroszytów Excela, hurtowni danych, itp.

W AFIN.NET funkcja GETDATAODBC() to potrafi - jest to uniwersalna    
funkcja do poboru dowolnych danych przez ADO/ODBC itp.

Jedynym problemem jest znajomość systemu zapisu jej argumentów:
ConnStr - tzw. ciąg połączenia bazodanowego
SQLStr - tzw. zdanie SQL - zapytanie do bazy danych

Ale tu w sukurs przychodzi MS Query - definiujemy kwerendę i jednym    
prostym ruchem "importujemy" jej ustwienia (tj. w/w parametry) do    
funkcji GETDATAODBC() jako jej gotowe argumenty. Działa!

Oczywiście, wszystko możemy robić ręcznie albo kopiować z    
biblioteki przykładów. Argumenty funkcji są tekstowe - możemy je    
dowolnie zmieniać (edytować), a także parametryzować    
wartościami w arkuszu.
Na filmie sparametryzowano w ten sposób ciąg połączenia    
bazodanowego do skoroszytu Excela; ponieważ jest używany w wielu    
funkcjach na arkuszu, nie ma sensu przechowywać go w postaci    
argumentu we wszystkich funkcjach - tu: jest w jednym miejscu jako    
parametr arkusza.

Funkcja GETDATALINK - alternatywa dla łącz międzyskoroszytowych

Film:
http://afin.net/webcasts/HowTo_InsertFunction_GETDATALINK.swf

Opis:
Film pokazuje, w jaki sposób wpisać i parametryzować funkcję    
zastępującą awaryjne łącza międzyskoroszytowe w arkuszu.


Pokazane są dwa sposoby:

1. Poprzez stworzenie fizycznego łącza do innego skoroszytu    
Excela, zamianę go na funkcję GETDATALINK, a nastepnie    
sparametryzowanie nazwą skoroszytu i adresem odwołania.

2. Poprzez użycie funkcji GETDATA (polski alias: DANE) z    
predefiniowanymi ustawieniami do prezentowanego, przykładowego    
modelu skoroszytów.
W tym przypadku wstępne otwieranie pliku-źródła nie jest    
wymagane.


Wnioski:

Koniec z problemami, związanymi z awaryjnością łącz Excela -    
funkcje AFIN.NET mogą je zastąpić i, dodatkowo, mogą być    
dowolnie parametryzowane.

Konsolidacja rejestrów z eksportami filtrowanymi

Problem:
Jak skonsolidować wiele rejestrów z wielu ustrukturalizowanych    
katalogów (system zabezpieczeń na poziomie katalogów), a    
następnie udostępnić wyniki konsolidacji różnym osobom o    
ograniczonych uprawnieniach?

Filmik:
http://afin.net/webcasts/Demo_RegisterConsolidation.swf

Opis:
Wiele katalogów, wiele plików
(w przykładzie: wszystkie pliki takie same)

1. Pokaz środowiska - organizacji katalogów oraz zawartości    
plików

2. Stworzenie kwerendy do pierwszego z plików oraz dodanie    
atrybutów dodatkowych, identyfikujących miejsce pliku w hierarchii    
oraz nazwę pliku (jako jego identyfikator)

3. Otwarcie odpowiedniego szablonu AFIN.NET i pobranie definicji    
kwerendy do programu

4. Parametryzacja programu wartościami parametrów, dot. katalogu    
(miejsca w hierarchii plików) oraz nazwy pliku, wyciągniętych do    
komórek arkusza programu

5. Testowe uruchomienie programu i przegląd wartości - filtrowanie    
wg dowolnych atrybutów

6. Uzupełnienie programu o część eksportową - dodanie wierszy    
eksportujących oraz, również, sparametryzowanie ich komórkami w    
arkuszu (tu: w pierwszym etapie, podczas nagrywania filmu,    
pomyliłem się i dodałem za mało atrybutów - dlatego dwa razy)

7. Przegląd gotowych plików eksportowych.
Oczywiście, pliki eksportowe można od razu zapisywać do    
odpowiednich katalogów, tak, aby każdy z ich użytkowników miał    
swój i tylko swój do wglądu.

Dystrybucję tego "systemu" można również zorganizować inaczej,    
bez wykonywania plików 'eksportowych': każdy z zainteresowanych    
użytkowników ma taki sam plik-program. Dane jednak pobierają się    
każdemu inaczej - tak, jak pozwalają na to ograniczenia    
dostępności na poziomie systemu plików. Gdzie nie ma dostępu -    
dane się nie pobiorą. Program nie zwróci żadnego błędu - brak    
będzie jedynie tych danych, do których dany użytkownik nie    
posiada uprawnień.

Konsolidacja budżetów z... Google'a (Google Docs)

Wszystko jest fajnie, jak budżety są w zasięgu naszej sieci    
lokalnej. Gdy nie są, może być problem - tym bardziej, gdy    
jeszcze są "mobilne", czyli twórca budżetu rzadko w ogóle do    
komputera siada (albo mało zna), robi na różnych komputerach,    
itp.
Tu rozwiązaniem jest Internet, ale, z kolei, ten ktoś rzadko ma    
swój komputer (poczta, itp.)

Rozwiązanie:
Wszyscy (ci mobilni) robią budżety w Google Docs.    
Pełne zabezpieczenia, udostępnianie (Użytkownik Google), arkusz    
bardzo przyzwoity, komunikacja on-line (można czatować w trakcie    
edytowania danych w arkuszu, wpis w arkuszu natychmiast widoczny    
przez innych), nic nie można zepsuć, itp.

Można też pliki wstępnie konsolidować, np. na poziomie jakiegoś    
obszaru działania reprezentantów handlowych (na filmie wstępnie    
konsoliduję Dept01-Dept03 w skoroszyty roczne) - bardzo fajnie    
działa funkcja (w arkuszu Google Docs)    
'=ImportRange(kod_skoroszytu, arkusz!obszar)'
Ma to sens, serio, trochę się pobawiłem.

Ale, co dalej?
Jak konsolidować pliki, tak, żeby były u nas na komputerze, w    
jednym arkuszu, w jednej tabeli przestawnej, itd.
Prosty zapis z GoogleDocs zapewnia "świeżość" plików, a    
dalej...

A dalej, to już standard AFIN.NET:
Otwieramy szablon, robimy kwerendę, pobieramy jej parametry do    
arkusza programu konsolidującego, parametryzujemy odpowiednio    
poszczególne jego wiersze (komendy), uruchamiamy program    
konsolidujący i... gotowe.

Filmik:
http://afin.net/webcasts/Demo_ConsolidateBudgetsFromGoogle.swf

Wnioski:
Jak ktoś mówi, że trzeba coś kupować ('Program do    
budżetowania', albo jakie 'BI'), żeby konsolidować budżety    
rozproszone, to nas robi w konia.

Konsolidacja plików Excela - przykład bardzo prosty

Co prawda, podobny przykład już był:
http://afin.net/webcasts/Demo_FileConsolidation1.swf
ale był trochę skomplikowany (kwerenda, itp.)

Jest więc NOWY szablon AFIN.NET, specjalnie dla celów konsolidacji    
plików Excela - wystarczy tylko podać katalog(i), nazwy plików,    
nazwy arkuszy do konsolidacji i, ewentualnie, zakres w arkuszach.

Filmik:
http://afin.net/webcasts/HowTo_ConsolidateExcelFiles.swf

Uwagi:
1. Działa na dowolnej ilości plików.
2. Muszą mieć, oczywiście, takie same nazwy kolumn, itp.
3. Przy okazji, w katalogu [AFIN.NET.Folder]/Temp tworzy się plik    
Accessa 'WorkBase.mdb' z gotową tabelą (gdyby np. ktoś inny    
chciał jej równolegle używać). Posiadanie Accessa nie jest    
wymagane!
4. Działa również w darmowej wersji testowej    
AFIN.NET

Jak wstawiać funkcje do różnych źródeł danych?

Filmik:
http://afin.net/webcasts/HowTo_InsertFunctions.swf

Opis:
Film pokazuje, jak można pracować z funkcjami w AFIN.NET.    
Możliwości jest mnóstwo:

1. Pierwsze 3 funkcje:
(GETDATA=Alias DANE) Użycie funkcji DANE do różnych źródeł    
danych
1.a. do plików typu DBF
1.b. do kostki OLAP - pliku .cub
1.c. do zamkniętego skoroszytu Excela (zewnętrznego)

oraz
2. Kolejne 3 funkcje:
(GETDATAODBC,GETDATAOLAP,GETDATALINK)
Jak wyżej w pkcie 1.(a,b,c), ale funkcje są stworzone ad hoc na    
bazie
a. kwerendy
b. tabeli przestawnej, bazującej na danych OLAP z pliku .cub
c. na podstawie łącza do pliku Excela


Proszę zwrócić uwagę na:

1. Możliwość parametryzacji funkcji  do  różnych źródeł    
danych według jednego schematu, czyli tak, jak robi to Excel - czy    
można bardziej elastycznie?

2. Możliwość użycia takich samych wartości wymiarów w    
różnych funkcjach, niezależnie od tego, czy pobierają dane z    
różnych baz relacyjnych, kostek OLAP, czy też łącz excelowych.    
Można oczywiście też parametrycznie pobierać dane z różnych    
baz tego samego typu - pełna dowolność!

3. W każdej chwili można również sprawić, że funkcje nie    
będą wyświetlały pobranych, czy też obliczonych, wartości, ale    
(po dodaniu ":" jako ostatni argument funkcji) będą wyświetlały    
swoje PEŁNE definicje, niezależnie z ilu parametrów arkuszowych    
złożone.
Wygodnie się szuka błędów w definicjach.

Hurtownia danych z punktu widzenia jej Administratora i Użytkownika

Filmik:
http://afin.net/webcasts/Demo_DataWarehouse_4Admin&4User.swf

Opis:
Film pokazuje sposób stworzenia i użytkowania Hurtowni Danych z    
punktu widzenia:
1. Administratora IT, tj. stworzenie procesu (na bazie szablonu    
AFIN.NET) zasilania w dane (tu prosty przykład zaciągnięcia    
jednej tabeli z bazy SQL Server Express), procesu przetworzenia    
danych do postaci 'analitycznej' danych (tu: stworzenie zapytania    
'ranking klientów względem wartości sumarycznej sprzedaży' do    
nowej tabeli) oraz przetestowanie działania procesu.
2. Użytkownika AFIN.NET / Excela, tj. wykonania (na bazie szablonu    
AFIN.NET) prostego procesu pobrania danych z Hurtowni Danych,    
stworzonej przez Administratora IT - pobierana jest tabela w formie    
podstawowej oraz druga tabela przetworzona. Oba procesy 'pobrania'    
danych wklejają uzyskane dane do skoroszytu analityka - na tych    
danych łatwo zbudować już dowolne raporty, np. tabelę    
przestawną lub opublikować je procedurą 'publikacja' AFIN.NET.

Ponieważ wszystko tworzone jest ad-hoc i dla celów pokazowych,    
obydwa procesy są maksymalnie uproszczone. Można jednak dowolnie    
je rozbudowywać - o stworzenie i obsługę odpowiedniej bazy (tu:    
accessowej) troszczy się AFIN.NET.

Zapraszamy do próby stworzenia takowej bazy we własnym zakresie i    
omówienie jej tutaj.

Wnioski:
Stworzenie i użytkowanie Hurtowni Danych jest w AFIN.NET dziecinnie    
proste. W jej rozbudowie pomoże firmowy informatyk, który ma    
zapewnione bezpieczeństwo danych transakcyjnych i możliwość    
odświeżania hurtowni w dowolnym momencie i w bardzo prosty    
sposób. Oczywiście, odświeżanie na tym poziomie można bardzo    
łatwo zautomatyzować, zlecając uruchomienie procesu np.    
codziennie o określonej godzinie.
Analityk ma zapewnioną aktualność danych oraz dane w formie,    
dzięki której raportowanie jest już kwestią nadania odpowiednich    
(własnych) formatów.
Nie jest też nic w stanie 'popsuć' w danych    
transakcyjnych.

Automatyzacja raportowania

Problem(y):
Jak wykonać 100 raportów standardowych?
Jak uwzględnić w nich, zadane z góry, parametry, np. rok i    
miesiąc, którego raporty dotyczą?
Jak opublikować każdy z raportów gdzie indziej?
Czy można automatycznie publikować do interfejsu Web?

Film:
http://afin.net/webcasts/HowTo_AutomatizeReports.swf

Opis:
1. Otwórz odpowiedni szablon AFIN.NET.IS i zapisz go w dowolnym    
miejscu.
2. Stwórz raport(y) i zapisz je na dysku.
Pamiętaj o wpisaniu parametrów (z odpowiednim nagłówkiem) w    
stałe miejsce we wszystkich plikach (arkusz, zakres komórek)
3. Zdefiniuj opcje publikacji każdego z raportów.
4. Zamknij raporty z zapisem zmian.
5. Otwórz (uaktywnij) arkusz programu automatyzującego
6. Dostosuj parametry programu
7. Uruchamiaj w dowolnym momencie z dowolnymi parametrami - program    
otworzy każdy z raportów, przeliczy go, opublikuje i zamknie -    
cały proces jednym kliknięciem myszy
8. Podaj użytkownikom adresy, gdzie publikują się raporty (tu:    
pliki Excela lub strony HTML).

Wnioski:
Problemy? Jakie problemy?

Hurtownia danych + dowolne raporty księgowe dla holdingu

Temat:
Hurtownia danych + dowolne raporty księgowe dla holdingu na    
podstawie zupełnie niestandardowych tekstowych wydruków    
hierarchicznych (nieimportowalnych ani do Excela, ani przez ODBC)

Filmik:
http://afin.net/webcasts/Demo_DataWarehouseFromManyTextExports.swf

Opis:
Firma ma 4 oddziały, tu nazwy CELOWO zmieniłem na 'AA', 'BB', 'CC'    
i 'DD'. Każdy z tych oddziałów na szczęście ma taki sam program    
księgowy. Te programy nie dają dostępu do bazy, ale produkują    
wydruki, tu: zestawienia obrotów i sald.
Wydruki te są bardzo trudne do obróbki. Pomijam fakt, że dane    
dot. konta są w różnych wierszach, a konkretnie jest wiersz konta    
i pod spodem wiersz wartości, to jeszcze, producent tejże FKi    
celowo(!) żeby trudniej było cokolwiek z tym zrobić, wstawia    
znaki typu CHR(8) i nie tylko w różne miejsca tekstu.

Takich plików mamy w tym przykładzie co miesiąc  dla każdej z    
firm - przychodzą mejlem, wrzucamy je do jednego katalogu,    
nazywając odpowiednio 'AA200502.txt', itd.

Film pokazuje, jak można to wszystko skonsolidować:
1. Pokaz środowiska, czyli jakie katalogi, jak wygląda tekst, itp.
Teksty są obcięte do ok. 200 wierszy, normalnie mają ok 2    
tysięcy wierszy, czyli konwersja trwa 30s*10 - ok. 5 minut. Jest to    
CAŁOŚĆ danych księgowych w zakresie obrotów i sald księgowych    
4 firm z 6-ciu miesięcy.

2. Konwersja plików tekstowych, która wszystkie pliki, jednym    
procesem, ładuje do tabel nowego pliku Accessa    
'AFIN.NET.ExportFile.mdb'
(Ten etap trwa stosunkowo długo, wspomniane powyżej 30 sekund -    
proszę przewijać filmik.)

3. Otwierany jest arkusz - program zasilający hurtownię, tzw. IS    
(na wzór SQL Server Information Services)

4. Prezentacja raportów, opartych na PEŁNEJ kostce OLAP.: tabela    
przestawna, wykres

5. Prezentacja raportu, OGRANICZONEGO tylko do danych firmy 'AA'    
(tzw partycja kostki) - tu po prostu mniejsza kostka od razu    
zapisywana w oddzielnym katalogu (gdzie np. system plików ogranicza    
dostęp) - można ją również np. wysyłać mejlem (tu pominięte)

6. Prezentacja dowolnego raportu księgowego w czystym arkuszu    
Excela za pomocą funkcji DANE()

Wnioski:
1. Stworzenie takiego modelu zajęło 1 dzień, z tego co najmniej    
pół poszło na odszyfrowywanie zagwozdek, zgotowanych przez    
producenta owych wydruków.
2. Nie ma wzoru pliku tekstowego, którego nie dało by się    
odczytać.
3. Odświeżenie całości procesu ładowania hurtowni danych    
księgowych holdingu to ok. 6 minut - dwa kliknięcia myszą.    
Oczywiście proces można automatycznie inicjować w nocy, czy też    
uruchamiać na życzenie.
Proces ten może być również dowolnie ograniczany, czyli np.    
tylko do jednego miesiąca (bo po co powtórnie konwertować    
obrotówki ze stycznia), czy też jednej firmy, np. gdy w któreś z    
firm coś tajemnie doksięgują w lutym i obroty na kontach w lutym    
i następnych miesiącach się zmienią.

Hierarchiczne pliki tekstowe jako źródło danych

Filmik:
http://afin.net/webcasts/Demo_AfinNetTC_HierarchizedText.swf

Opis:
Film pokazuje, jak z pliku tekstowego o informacji zapisanej    
'hierarchicznie', czyli w różnych wierszach (tu wiersz numeru    
konta NIE ODPOWIADA wierszowi, gdzie są dane numeryczne),    
sporządzić płaską tabelę danych.
I zapisać ją w dowolnej bazie danych (tu: w Accessie, ale Excel    
też jest opcją)

Film pokazuje CAŁY proces definicji (i, zdarzających się przy    
tym, pomyłek i poprawek) takiego odczytu.

Wnioski:
Jesli jakikolwiek system nie potrafi wyrzucić danych, jakich    
analityk sobie życzy, do jakieś bazy danych, Excela lub normalnego    
pliku tekstowego (csv albo inny - ze wszystkimi informacjami w    
jednym wierszu), a potrafi tylko do takiego 'wydruku' - nie przejmuj    
się - wszystko można odczarować.

I pliczek, żeby nie szukać:
|---------------------------------|
|Title1     Account Number        |
|---------------------------------|
|info1|info2|     debit|    credit| 
|---------------------------------|
|Record1    AccNo:100.01          |
|---------------------------------|
|a1   |b1   |   1000,00|      0,00| 
|---------------------------------|
|a1   |b2   |   2000,00|      0,00| 
|---------------------------------|
|Record2    AccNo:200.01          |
|---------------------------------|
|a2   |b2   |  10000,00|   1000,00|
|---------------------------------|
|Record3    AccNo:300.01          |
|---------------------------------|
|a2   |b1   |   3000,00|      0,00|
|---------------------------------|
|a1   |b1   |      0,00|    100,00|
|---------------------------------|
|a3   |b2   |      0,00|  12345,67|
|---------------------------------|

Jak posłownikować dane własnym słownikiem?

Natchnienie:
http://www.goldenline.pl/forum/sap/1108920

Film:
http://afin.net/webcasts/HowTo_CreateYourOwnAdHocDictionary.swf

Opis:
Jak pobrać dane z systemu ERP, np. dane faktur, i posłownikować    
(uszlachetnić) je, stworzonym ad-hoc, własnym słownikiem, przy    
czym słownik ma być łatwo dostępny i edytowalny, czytaj: ma być    
w Excelu
Tu: Dane faktur pobierane są z przykładowej, lokalnej bazy danych    
SQL Server Express, ale może to być dowolna baza produkcyjna: SQL    
Server, Oracle, DB2

Streszczenie filmu:

1. Otwieramy odpowiedni szablon AFIN.NET

2. Tworzymy kwerendę do danych faktur w bazie SQL Server

3. Pobieramy definicję połączenia do arkusza szablonu - naszego    
programu, przetwarzającego dane.

4. Tworzymy słownik.
Najlepiej wykorzystać do tego już istniejącą kwerendę do tabeli    
SQLS - w niej tworzymy unikalną listę nazw kontrahentów, która    
będzie jednocześnie kluczem głównym tabeli słownikowej, tu:    
pole 'Nazwa'
W słowniku tworzymy dwa nowe pola 'City' i 'Dealer'.

5. Zapisujemy słownik w pliku Excela - można użyć, oczywiście,    
dowolny inny, już istniejący, słownik.

6. Tworzymy kwerendę do tabeli słownika w pliku Excela i pobieramy    
z niej definicję połączenia

7. Uruchamiamy program testowo, aby stworzyć bazę Accessa,    
integrującą dwie, powyższe tabele (wpisanie 'stop' zatrzymuje    
program w odpowiednim miejscu)

8. Tworzymy kwerendę do bazy Accessa WorkBase.mdb, łączymy tabele    
relacją i pobieramy wszystkie dane do Exclea i, podobnie jak    
powyżej, pobieramy z niej definicję (już tylko zapytania) do    
kolejnego kroku naszego programu.

9. Uruchamiamy program, który realizuje wszystkie powyższe    
instrukcje, wklejając połączone dane do arkusza.

10. Tworzymy żądane analizy (tu: tabela przestawna i wykres    
przestawny) na połączonych danych. Analizy możemy modyfikować i    
odświeżać, a słownik edytować w sposób dowolny.

Wnioski:
1. Stworzenie własnego słownika w produkcyjnej bazie danych jest    
zwykle niemożliwe ze względów bezpieczeństwa bazy.
2. Kolejnym problemem jest trudność w zaimportowaniu do tej bazy    
danych z naszego słownika - tu jest to możliwe.
3. Architektura tego rozwiązania jest uniwersalna - to, że baza na    
coś nie pozwala, albo jest to w niej trudne, nie znaczy, że nie    
jest możliwe.

Konsolidacja danych z plików - prosty przykład (tu: pliki Excela)

Film:
http://afin.net/webcasts/Demo_FileConsolidation1.swf

To PROSTY przykład, jak skonsolidować dane z dowolnej ilości    
tabel z dowolnego systemu bazodanowego.
Tu na przykładzie arkuszy w kilku plikach Excela, ale to tylko    
przykład, tabele mogą być dowolne w dowolnym źródle danych.
MS Query poda nam właściwą definicję, niezależnie od typu    
źródła.


Streszczenie filmu:

1. Prezentacja danych: 3 pliki: File1.xls, File2.xls, File3.xls,    
zawierające cząstkowe dane, ale w tabelach (tu: arkuszach) o    
takiej samej strukturze.

2. Użycie szablonu AFIN.NET do konsolidacji danych
Wpisujemy listę nazw plików (najlepiej bez rozszerzeń) jako    
kolumnę parametrów

3. Tworzymy kwerendę do pierwszego pliku z naszej listy, pobieramy    
całą tabelę do konsolidacji i tworzymy DODATKOWE pole, będące    
nazwą pliku, z którego dane pobieramy, a które będzie też    
wyróżnikiem źródła danych w naszych dalszych analizach.

4. Po umieszczeniu kwerendy w nowym arkuszu, pobieramy z niej    
definicję źródła danych oraz tekst zapytania bazodanowego do    
odpowiednich pól arkusza szablonu.

5. PARAMETRYZUJEMY formuły, utworzone z powyższych definicji,    
odwołując się do kolumny 'A', gdzie wpisaliśmy parametry - nazwy    
plików do konsolidacji

6. Po zakończeniu parametryzacji kopiujemy formuły w dół,    
wzdłuż kolumny parametrów

7. Uruchamiamy program

8. Tworzymy analizy na skonsolidowanych danych - tu: prosta tabela    
przestawna.

Wnioski:
1. Całość procesu jest odświeżalna jednym kliknięciem myszy
2. Zakres plików można dowolnie zwężać lub poszerzać, dodając    
lub odejmując wiersze w arkuszu 'Program'.


Uwagi:
Powyższe zadanie jest dość typowym zadaniem w analizie danych,    
więc nie tylko AFIN.NET oferuje rozwiązanie tego problemu.

Polecam zapoznać się również z innymi rozwiązaniami, np. kolegi    
Przemka R.:
http://vbamania.blogspot.com/2009_04_26_archive.html

lub rozwiązaniem za pomocą rozbudowanego zapytania SQL:
http://afin.net/samples/lessons/SQL.10_Union/AFIN.NET.IS_SQL.Lesson10_mirror.htm

Hurtownia Danych OLAP

Czy zbudowanie od podstaw(!) OLAP-owej hurtowni danych jest trudne?

Pewnie tak - trzeba pobrać dane z różnych źródeł danych,    
zebrać je w jednej bazie (ETL), następnie je przekształcić do    
formy przyjaznej do analizy, a potem zaprojektować i wykonać    
moduł analizy wielowymiarowej OLAP, który oferuje natychmiastowy    
dostęp do dowolnych wymiarów naszej analizy i umożliwia łatwą    
eksplorację danych tabelą przestawną.

Spróbujmy!
http://afin.net/webcasts/Demo_DataWarehouseInMOLAP.swf


Streszczenie filmiku:
1. Otwieramy odpowiedni szablon AFIN.NET i dostosowujemy go da    
naszego zadania (tu: usuwamy niepotrzebne wiersze)

2. Wykonujemy kolejno szereg kwerend, pobierając do arkusze dane z    
różnych źródeł danych, tu:
1.1. SQL Server (wersja Express, serwer lokalny, dane faktur -    
'faktura')
1.2. Access (dane kontrahentów - 'odbiorca')
1.3. Excel (słownik regionów - 'region')
Kwerendy są proste, dla uproszczenia bez żadnych fitrów i    
uszlachetnień

3. Uruchamiamy program, budujący bazę danych, składającą się z    
3 wymienionych wyżej tabel - tu: plik Accessa WorkBase.mdb
Użycie 'stop' zatrzymuje program przed wykonaniem linii,    
dotyczącej budowy kostki - kostka nie jest jeszcze zaprojektowana.

4. Budujemy kostkę OLAP
4.1. Budujemy kwerendę, składającą dane z trzech tabel do jednej    
szerokiej tabeli wynikowej (widoku)
4.2. Uruchamiamy Excelowego kreatora modułów danych OLAP (W    
standardzie Excela od wersji 2000)
4.3. Projektujemy kostkę, najpierw miara, potem wymiary, następnie    
podajemy, gdzie ma zostać zapisany moduł danych (kostka OLAP, plik    
CUB) oraz gdzie ma zostać zapisana jego definicja (plik OQY)
Kostka OLAP również jest celowo najprostsza, jak być może: jedna    
miara, trzy wymiary, prosta hierarchia.

5. Dokonujemy korekt ścieżek - wskazujemy adresy kostki i jej    
definicji

6. Uruchamiamy cały program

7. Na stworzonej kostce budujemy raport tabeli przestawnej -    
otwieramy plik CUB pod Excelem

8. W celu pokazania automatyzacji całego procesu, otwieramy na    
chwilę plik Excela ze źródłową tabelą 'region' i dokonujemy w    
niej zmian - tu: zmieniamy przyporządkowanie miasta 'Warszawa' do    
innego regionu - tu: tworzymy nową pozycję słownikową -    
'Stolica'

9. Uruchamiamy ponownie program - odświeżamy kostkę oraz    
odświeżamy tabelę przestawną, gdzie natychmiast pojawia się    
nowy region.

10. Gotowe. Definicja kostki jest gotowa, kostkę można    
odświeżać dowolną ilość razy w dowolnym czasie, nawet, gdy    
raporty z niej są otwarte.

Wnioski:
Budowa procesu ETL - kilka minut, myszką
Projekt kostki OLAP - równie prosto i szybko
Odświeżanie naszej hurtowni - jedno kliknięcie myszą
Odświeżenie raportu - standardowo.


Wydajność:

Proces taki w następujących warunkach:
1. Dane dużej firmy dystrybucyjno-produkcyjnej urządzeń    
gospodarswa domowego - źródłowy plik tekstowy ok. 1.000.000    
wierszy, tj. dane sprzedaży z dokładnością do pozycji faktury
2. Dołączone słowniki - 5 sztuk, hierarchiczne, kilkaset-kilka    
tysięcy pozycji, format: pliki Excela
3. Z pełnych trzech lat, z dokładnością do dnia
4. Z dokładnością drążenia (drill-down) w kostce do pozycji    
faktury, czyli bez uproszczeń, ale, oczywiście, ze wszyskimi    
sumami pośrednimi itp.
5. Tworzona kostka miała 32 MB i działała bardzo sprawnie już    
pod Excelem XP

... trwał 3 minuty.

Kurs SQL

SQL... ha!, brzmi tajemnie...

Ale nie jest tak źle. Wszystko można wyklikać myszką, a jeżeli    
czegoś nie można (bo np. funkcji SQL nie) to ich zestaw jest    
wręcz żenująco  ;) mały - z punktu widzenia realnych potrzeb    
analityka.

Więc - do dzieła!
http://afin.net/samples/lessons/

Jak się z tego korzysta?
1. Wchodzimy w folder, zawierający daną lekcję, np.
SQL.1_Table/

2. Pojawiają się pliki:
[DIR] Parent Directory                      17-Aug-2009 13:11      -    

[XLS] AFIN.NET.IS_SQL.Lesson1.xls           17-Aug-2009 13:03    47k    

[HTM] AFIN.NET.IS_SQL.Lesson1_mirror.htm    17-Aug-2009 13:03    13k    

[DIR] AFIN.NET.IS_SQL.Lesson1_mirror_pliki/ 17-Aug-2009 13:03      -    


3. Działamy
3.1. Jeżeli chcemy POOGLĄDAĆ - klikamy plik z rozszerzeniem HTM
Tu klawisz "START" nie działa, ale można odczytać SQLa i    
zobaczyć efekt w kolejnym "arkuszu" (arkusze - na dole) oraz    
porównać z tabelą - źródłem danych "Copy of faktura" (ostatni    
"arkusz")

3.2. Jeżeli chcemy WYPRÓBOWAĆ, poeksperymentować, itp. - musimy    
zainstalować AFIN.NET
http://www.afin.net/downloads/AFIN.NET.Setup.exe
(nie boli - 2 MB, bezpieczne, zero "bałaganu")
Uruchamiamy wtedy AFIN.NET i ze strony (czyli z folderu powyżej)    
otwieramy plik XLS. Kliknięcie 'START' odświeża dane.

Zainstalowanie AFIN.NET dostarcza równocześnie dane przykładowe.
Wszystkie eksperymenty dozwolone.

Tutaj opis narzędzia do eksperymentów, czyli AFIN.NET.IS:
http://afin.net/articles/AfinNet_Article_AFIN.NET.IS_PL.htm

Tutaj biblioteka przykładów:
http://afin.net/samples/AFIN.NET.InformationServices/AFIN.NET.IS_SamplesLibrary.xls

Tutaj z kolei standardowy help MS dla JetSQL (niestety,    
nienajlepszy):
http://afin.net/excel/Pomoce/JETSQL40.CHM

A jak ktoś chce zobaczyć, jak SQLka można wyklikać myszką, to    
najlepiej w MS Query
http://afin.net/artykuly/gazetaprawna/9.%20MS%20Query.htm
Krok 3a pokazuje SQLa - można wykopiować.

Zapraszam do dyskusji!

Pulpit menedżerski

Zrób sobie pulpit menedżerski z kluczową informacją finansową    
dla szefa.

Analityk może go odświeżać i dowolnie modyfikować.
Klient informacji ma interfejs WEBowy.
http://afin.net/webcasts/Demo_CreateTheDashboard.swf


Przykład zastosowania:
Pulpit informacyjny AFINA
http://www.afin.net/index1_pl.htm

Czekam na pomysły!

Zrób sobie bilans...

http://afin.net/webcasts/Demo_CreateTheBalanceSheet.swf

Fajne to Google...

Hurtownia danych AFIN.NET - przykład ETL

Jak (za pomocą myszki, oczywiście) zbudować procedurę zasilania    
hurtowni danych danymi z różnych źródeł (tzw. proces ETL:    
'Extract, Transform, Load'), tu:

* SQL Server (v.Express-lokalny)
* SQL Server (v.pełna-przez Internet)
* MySQL (przez Internet)
* Excel 2007 (Pliki nie otwierają się w E2003)
* Plik tekstowy (tu: csv)

http://afin.net/webcasts/Demo_ETL1.swf

Czy można wyklikać myszką hurtownię danych?

Czyli, niezależną od systemów transakcyjnych, bazę danych,    
specjalizowaną dla celów analitycznych, odświeżaną    
automatycznie?
(tu: przykład prosty - jedna, szeroka tabela danych)

http://afin.net/webcasts/Demo_HowToCreateDataWarehouse.swf

Konsolidacja plików Excela, np. budżetów

Jak, w pół godziny, raz na zawsze, rozwiązać problem z    
pobieraniem danych z dowolnej ilości budżetów w Excelu / innych    
plików Excela i konsolidowaniem ich w tabeli przestawnej?

http://afin.net/webcasts/Demo_CreatePivotTableFromScatteredBudgetsInExcelFiles.swf

Film ok. 25 minut, 5,5 MB.

Uproszczenie zapytania bazodanowego

Jak stworzyć i uprościć bardzo skomplikowane zapytanie    
bazodanowe, by używać go w bardzo prostej funkcji w Excelu?

http://afin.net/webcasts/Demo_HowToSimplifyVeryComplicatedQuery.swf

Parametryzowane raporty z wielu plików Excela (łatwą funkcją!)

http://afin.net/webcasts/Demo_MyDesiredReportFromManyExcelExports.swf