Artykuł AFIN.NET:

Optymalna architektura pracy w AFIN.NET © AFIN 1995-2009

Wojciech Gardziński, Krzysztof Rumiński 2009.05

AFIN.NET + Optymalna architektura analiz AFIN.NET =

= efektywna analiza danych w środowisku Excela

Summary in English:

All the financial controllers work in Excel – it’s obvious and almost standarized. A standard analysis architecture, presented on the diagram (#1) is used in 90% of cases. A lot of problems following that is being resolved in two common ways: VBA automatization or Business Intelligence system implementation.

We discuss, what really happens when the BI system is implemented (#2) and what are the possible ways to resolve the real problems in reporting.

We also try to design the optimal analysis architecture (#3) and use it with and by AFIN.NET.

AFIN.NET & optimal analysis architecture AFIN.NET makes the data analysis in the Excel enviroment most effective (#4). It’s also ready to use.

Spis treści:

Standardowa architektura analiz. 1

Definicja problemu efektywnej analizy. 3

Standardowe rozwiązania problemu. 4

Rzeczywiste potrzeby analityków.. 5

Organizacja pracy. 6

AFIN.NET – pełne wsparcie! 8

Przykłady: 9

Samo posiadanie systemu informatycznego nie wystarczy. Ogrom włożonej pracy również. Nieprzemyślana automatyzacja (makra Visual Basic) nie poprawia sytuacji – nasze analizy dalej są awaryjne i bardzo czasochłonne.

Poznaj, zaprojektuj i zbuduj system swoich analiz w oparciu o przemyślaną, optymalną architekturę informatyczną. Przeanalizuj swoje potrzeby, a nie oferty dostawców rozwiązań informatycznych.

Jak to zrobić efektywnie, wykorzystując AFIN.NET?

Standardowa architektura analiz

Spójrzmy, spróbujmy zrozumieć i wyciągnijmy wnioski nt. powszechnie stosowanej architektury pracy w działach analitycznych firm.

Powyższy schemat obrazuje standardową architekturę przetwarzania danych dla celów analitycznych. Nie jest to specyfika polska – trend do tego rozwiązań jest powszechny, bo w pewien sposób wymuszony konstrukcją narzędzia – programu MS Excel.

Transakcyjne bazy danych są raczej niedostępne bezpośrednio, rzadko zdarza się możliwość importu tabeli bezpośrednio z systemu transakcyjnego. Jak radzą sobie analitycy? Tworzą różnego rodzaju eksporty/wydruki, które potem na siłę niekiedy importują do Excela.

Chociaż takie skoroszyty mają właściwie cechy danych źródłowych (aktualizują się, posiadają zwykle stałą strukturę, ale nie są poukładane, dane trzeba wyszukiwać, sumować, często poprawiać, a zawsze jakoś uszlachetniać, np. dodając kolumny lub słownikując oraz zawsze filtrować i poprawiać formaty liczbowe), użytkownicy Excela przetwarzają je na raporty makrami VBA lub tworzą nowe skoroszyty, z których tworzą setki, jeśli nie tysiące (przypadek rzeczywisty) łącz międzyskoroszytowych do tychże arkuszy-źródeł danych. Taka architektura pracy posiada wszelkie cechy tzw. ”piekła Excela” (Excel-hell), jest zaprzeczeniem wszelkiej racjonalności informatycznej, ale… działa, niekiedy latami. Działa, bo analitycy wyksztacili już w sobie pewien rodzaj autodyscypliny – wiedzą, że taki system jest awaryjny, więc, niekiedy dużym nakładem sił, pilnują spójności takiego systemu. Nikt, co prawda, nie jest niczego w tym systemie pewny, ale systemy takie działają… właściwie wszędzie. I, prawie wszędzie, są podstawą słusznej krytyki ze strony zarządów, które zrozumienie swoich problemów znajdują… u dostawców systemów Business Intelligence.

Definicja problemu efektywnej analizy

Problemów jest kilka, każdy poważny, jeżeli podejdziemy do niego od jednej tylko strony.

Bezpośredni dostęp do danych systemu transakcyjnego (OLTP)

Systemy transakcyjne nie służą do analiz – mają inne zadania: poprawność transakcji, bezpieczeństwo i wydajność systemu. Na straży tych zadań stoi IT i stać powinien. Analitycy, którzy chcą pobierać dane bezpośrednio z systemu OLTP, powinni pogodzić się z odmową lub znacznym ograniczeniem praw takiego dostępu – jest po prostu niebezpieczny dla systemu!

Eksport (lub poprawny eksport) danych z systemu OLTP

Każdy system posiada w chwili obecnej jakiś, lepszy lub gorszy, moduł eksportu danych. W niczym to jednak nie polepsza sytuacji analityka.

Większość oferuje tzw. „Eksport do Excela” raportu, który został stworzony w systemie. Czy to nam, analitykom wystarcza?

Nie, ponieważ jest on wykonany przez system w pewnym układzie. Jak wiadomo, każdy z użytkowników Excela chce dodać własne kolumny, poprzestawiać dane, sformatować je odpowiednio do własnych potrzeb, połączyć formułami w spójną całość, itp.

Pozostałe systemy eksport danych realizują albo w formie „Eksportu do csv” (który jest w rzeczywistości tym samym, co powyższy eksport do Excela”) lub w ogóle nie oferują takiego eksportu, ale umożliwiają zapis danych w formie pliku tekstowego, często „zaśmieconego” nagłówkami i niepotrzebnymi informacjami – często są to wydruki, których nie da się zaimportować do Excela (dane hierarchiczne).

W rzeczywistosci, problem jest jeszcze poważniejszy, gdyż, oprócz problemów z organizacją danych na takim wydruku, często pojawiają się problemy techniczne: nie mieści się w Excelu, obcina dane, źle formatuje wartości itp.

Rozbicie informacji biznesowej pomiędzy setki plików, często w różnych formatach bazodanowych

Ponieważ eksporty są zdarzeniami jednostkowymi, tzn. jest raport w systemie – otrzymujemy eksport do pliku, eksportów (plików) takich gromadzi się znaczna ilość. Każdy raport, nawet z tego samego systemu, wygląda inaczej, a każdy system zapisuje dane do innego formatu. Plików jest setki w kilku formatach, przerabiane skoroszyty zajmują wiele miejsca, gdyż każdy taki skoroszyt jest przerobioną wersją skoroszytu źródłowego – tworzy się więc dziesiątki wersji raportów w często niejednolitym formacie.

Techniczna awaryjność łącz międzyskoroszytowych w Excelu

Opisane systemy arkuszy połączone są setkami łącz, które tworzą sieć powiązań informacyjnych, pliki źródłowe są cyklicznie podmieniane, niekiedy przenoszone, a czasami wręcz wysyłane pocztą elektroniczną poza firmę i włączane z powrotem po wypełnieniu informacjami i powrocie do nadawcy. Nie ma możliwości utrzymania tego systemu w całości bez awarii – zdarza się to więc cały czas – okresy raportowe są dla analityków okresami walk o spójność takiego systemu.

Brak wiedzy użytkowników nt. możliwości przetwarzania danych, dostępności nawet standardowych opcji już posiadanych narzędzi

Może to zabrzmi nieprawdopodobnie, ale wiedza, że w ogóle można pobierać dane z innego systemu niż Excel, dotyczy… 5% populacji analityków. Poważniejsza świadomość bazodanowa, czyli np. wiedza, że importy takie można automatyzować, parametryzować, dane w trakcie takich procesów uszlachetniać i łączyć „w locie”, dotyczy już naprawdę bardzo nielicznych. Słowem, jednym z podstawowych problemów jest, po prostu, brak wiedzy.

Standardowe rozwiązania problemu

Rozwiązania są właściwie dwa:

Próba okiełznania powyżej opisanej sytuacji na własną rękę poprzez automatyzację (makra Visual Basic) i żelazną dyscyplinę pracy (tzw. systemy arkuszy)

Ponieważ opisane wyżej rozwiązania i ich wady są standardem, wszędzie próbuje się tym wadom zaradzić. Ponieważ systemów transakcyjnych nie można szybko zmienić ani unowocześnić – gdyż z jednej strony jest to „na rękę” ich producentom – klienci, mający problemy, często zamawiają dodatkowe raporty (U wielu dostawców oprogramowania jest to wręcz podstawowe źródło przychodów.), z drugiej strony – jest to ogromne przedsięwzięcie organizacyjne i „rewolucja” w firmie. Wszyscy też Excela znają – problemu często się więc nie dostrzega – traktuje się go jako sytuację normalną. Jako normalne traktuje się również „unowocześnianie” systemu poprzez pisanie makr VBA, przetwarzających dane. Analitycy, chętni do tworzenia takich usprawnień, stanowią 90% uczestników kursów VBA. Płacą, chcą się uczyć - i jak tu powiedzieć im, że nie tędy droga?

Zakup „profesjonalnego systemu analitycznego”, często klasy „Business Intelligence” za, niekiedy, bardzo duże pieniądze

Dostawcy systemów analitycznych, szczególnie drogich systemów „klasy Business Intelligence” (cokolwiek to znaczy) rozumieją powyższe problemy doskonale. Nie chcą widzieć zalet, bo… nie mieliby najmniejszych szans konkurować z Excelem na polu elastyczności systemu, czy też wygody i powszechności jego użytkowania. Konkurują więc… sztywnością. Mówią, że zapewnią „jedną wersję prawdy” (jakoby taka istniała – jako test, proszę podać wielkość zysku firmy – wszyscy liczą, a każdy inaczej), mówią, że firmą sterować będzie się za pomocą pulpitów menedżerskich, że wszystkie raporty będzie można drukować, publikować na portalach, zapisywać w dowolnym formacie i… eksportować do Excela.

Klienci, będąc pewni, że system będzie „współpracował” z Excelem, czasami kupują i wdrażają tego typu rozwiązania. Co otrzymują?

Na rysunku poniżej przedstawiono sytuację „rewolucji informatycznej”.

Stare systemy transakcyjne zastępuje się nowym, „zintegrowanym” (wdrożenie trwa rok, by często ledwie odtworzyć starą funkcjonalność), połączonym z systemem BI (często potocznie zwanym „Bi-ajem”). System BI oferuje wielką, również długo wdrażaną, wielowymiarową hurtownię danych OLAP, zasilaną w nocy - „przemyślaną”, „nowoczesną” i „w pełni zintegrowaną”. Dane można przeglądać wielowymiarowo, wchodzić wgłąb („drill-down), zwijać i rozwijać różne poziomy ich struktury. Piękne i niewątpliwie wartościowe, ale… dostępne również w Excelu.

Nie ma nic bardziej sztywnego od takowej hurtowni – nic (poza zawartością słowników, bo już nie ich strukturą) nie da się w niej zmienić bez długotrwałego (czytaj: drogiego), powtórnego wdrożenia lub interwencji informatyków. System BI produkuje raporty, które użytkownicy, przyzwyczajeni do normalnej, choć żmudnej, analizy, importują do Excela. I… stwierdzają, że, właściwie, znowu otrzymują raporty do przeróbek w Excelu, tyle, że z nowego systemu.

Po niedługim czasie staje się to zresztą koniecznością, gdyż rozwój firmy jest zawsze szybszy od jej systemu informacyjnego (tu: informatycznego), drogie usługi skutecznie hamują rozwój tego drugiego. Jedynym, kto nadąża, jest Excel, bo, choć nie jest bezpieczny, jest elastyczny.

Rzeczywiste potrzeby analityków

Co otrzymamy, gdy spojrzymy na sytuację od strony naszych rzeczywistych potrzeb?

Czego, tak właściwie, nam, analitykom, trzeba?

Niewątpliwie trzeba nam dostępu do danych.

Systemy transakcyjne posiadają bardzo skomplikowaną strukturę bazy danych. Setki, jeśli nie tysiące tabel, powiązanych relacjami – „znormalizowanych”, jak mówią informatycy – dla nich taka postać danych jest „normalna” (bo efektywna informatycznie). Analitycy potrzebują tabel szerokich, gdzie każda informacja numeryczna, tzw. „fakt”, posiada jak najwięcej atrybutów, tzw. „wymiarów”. Potrzebujemy tej informacji szybko, tzn. w gorących okresach musimy mieć możliwość odświeżania inormacji nawet co chwilę. Potrzebujemy wreszcie tej informacji w takim formacie, żeby łatwo i bezbłędnie nam „weszła” do Excela.

Z czego możemy śmiało zrezygnować?

Czy analitykowi zależy na bezieczeństwie? Analityk, aby być sprawnym, musi danymi „żonglować”: pobierać, przetwarzać, łączyć, dzielić, sortować, filtrować, grupować, formatować, itp. Elastyczność jest w pewnym sensie zaprzeczeniem bezpieczeństwa. To analityk i tworzona przez niego informacja biznesowa musi być „bezpieczna” (tajemnica danych), nie sam system.

Jak otrzymać to, z czego jednak nie chcemy zrezygnować innymi, dostępnymi, metodami?

Bezpieczeństwo danych może zapewnić system plików, tzn. dane pośrednie i analizy muszą być w katalogach, ogólnie niedostępnych. System plików w sieci firmowej zabezpieczy nam firmowy informatyk – to jego jedno z podstawowych zadań.
Denormalizację bazy (czyli łączenie tabel w szerokie tabele) zapewnia MS Query lub najprostsza kwerenda Accessa
Automatyczne zasilanie w dane – naprawdę proste (tylko nagrane!) makro potrafi uruchamiać (odświeżać) kwerendy, również kwerendy accessowe.

Jak powiązać wszystkie elementy w spójną całość?

To niewątpliwie problem najpoważniejszy. Gdy kupujemy „system”, organizację on przejmuje, my tylko za to płacimy. Jak więc osiągnąć efekt, ograniczając koszty do minimum?

Przede wszystkim trzeba ten system zaprojektować, czyli jakoś rozrysować, rozpisać na role, etapy i użyte technologie. Potem odpowiednio zautomatyzować, a na końcu – zabezpieczyć.

Wbrew pozorom jednak, nie jest to zadanie niewykonalne i każdy analityk, we współpracy z rozumiejącym jego potrzeby informatykiem, są w stanie to zrobić.

Jak zautomatyzować pracę, nie ograniczając naszej kontroli nad systemem?

Niektóre procesy, np. zasilanie w dane hurtowni, czyli warstwy pośredniej danych, powinno być automatyczne. Niektóre „standardowe” raporty (np. codzienny wykaz zaległości płatniczych dla poszczególnych handlowców) również. Najprościej – zaprogramowac start makra odświeżającego o konkretnej godzinie.

Organizacja pracy

Jak zorganizować swoją pracę, by była efektywna, spełniała powyższe potrzeby i, żeby nie wydać na to przysłowiowej góry pieniędzy?

Odpowiedź częściowo została udzielona powyżej – spójrzmy jednak na rysunek architektury optymalnej, co tu jest ważne?

Jeżeli jedynym (głównym) powodem zmiany systemu transakcyjnego jest jego nowoczesność i dostosowanie do Systemu „BI”, to nie warto go zmieniać. Trzeba tylko zacząć traktować jego eksporty/wydruki jako… jego bazę danych, udostępnioną na zewnątrz. Pliki eksportów/wydruków można zapisywać, nadając im nazwę z datą w nazwie, np. zsfk200904 (zestawienie sald programu FK, tu, miesięczne na 2009-04) lub nn20090430 (należności nierozliczone na dzień 2009-04-30) itp.

Pliki te, traktowane przez nasz system, zasilający hurtownię, jako tabele żródła danych, będą wtedy łatwo odszukiwane przez zapytania tego systemu. Polecamy grupowanie takich plików w katalogach rocznych z podkatalogami dziedzinowymi, np. ‘…\2009\FK\...’. Analityków uspokajamy – nie będziemy się do tych plików łączyć poprzez excelowe łącza międzyskoroszytowe, nie grozi nam więc żadne niebezpieczeństwo porwania tych łącz!

Następnie tworzymy warstwę pośrednią danych – najlepiej na początek prostymi kwerendami z poziomu Excela.

W drugim etapie, tworzymy plik Accessa, który (uwaga!) posiada wyłącznie ‘tabele połączone’ – żadnych własnych(!). Przecież nie chcemy się uczyć Accessa – my chcemy go tylko efektywnie wykorzystać. Na tych tabelach (ewentualnie) budujemy własne kwerendy lub programujemy makrami tworzenie nowych tabel (to już wymaga pewnej wiedzy o Accessie)

Kolejnym etapem może być (choć nie musi) profesjonalizacja stworzonego systemu analitycznego – przeniesienie bazy na platformę bazodanową SQL Server, Oracle Express lub np. MySQL. W małych firmach mogą to być bazy darmowe, w większych, które profesjonalne bazy danych już zwykle posiadają, będzie to kolejny (darmowy) moduł tej bazy.

AFIN.NET – pełne wsparcie!

AFIN.NET został stworzony właśnie jako zestaw narzędzi do rozwiązania powyższych problemów.

(Szczegółowy opis poszczególnych funkcjonalności na stronie www.afin.net)

Opisaną powyżej, „optymalną” architekturę, przeanalizujemy teraz bardziej szczegółowo.

Automatyzacja poboru i przetwarzania danych wspierana jest przez AFIN.NET.InformationServices (wszystkie małe ikony AFIN.NET w lewej i środkowej części rysunku oraz wsparcie „wklejania danych” dla raportów AFINA, nie korzystających z MS Query). Gdy pobór danych odbywa się poprzez eksport z systemu, a potem konwersję plików TXT – dodatkowego wsparcia udziela AFIN.NET.TextConverter, którego zadaniem jest tu, tylko i aż, doprowadzenie tekstu do formatu danych, dostępnego do dalszego odczytu przez SQL-owe procedury bazodanowe AFIN.NET.IS.

Automatyzacja obejmuje:

Pobór danych, łączenie pionowe (konsolidacja) oraz poziome (relacje BD) tabel, atrybutowanie, słownikowanie, filtrowanie, grupowanie, sumowanie, dzielenie według zadanych kryteriów oraz ewentualne, konieczne dostosowania formatów danych. Można stosować również wszelkie formuły matematyczno-logiczne w celu przetworzenia i dostosowania do specyficznych potrzeb (wszelkie komendy języka SQL)
W języku bazodanowym takie procesy nazywa się procesami ETL (Extract, Transform, Load) – AFIN.NET je upraszcza i czyni możliwymi do zastosowania BEZ konieczności posiadania specjalistycznych baz danych oraz ich komponentów.

Zasilanie kostek OLAP w SQL Server Analysis Services lub tzw. OLAP-owych kostek lokalnych – plików .cub
(tzw. usługi DTS – Data Transformation Services)
Wklejanie danych do skoroszytów analitycznych, stworzonych w Excelu. Skoroszyty takie, nie posiadając żadnych zewnętrznych łącz międzyskoroszytowych (ani odwołań do bibliotek MS Query!) są bytami zupełnie niezależnymi od stanowiska komputerowego, na którym się je tworzy, uruchamia lub na które się je przesyła.

Osobną kategorią wsparcia AFIN.NET dla procesów poboru danych są funkcje AFIN.NET, mogące dostać się do dowolnej zewnętrznej bazy danych (także hurtowni danych) i pobrać dane niezależnie od ich formatu (również z baz danych OLAP, serwerowych lub lokalnych)

Podsumowanie:

Wszystkie procesy przetwarzania danych, ich obróbki i zapisu do dowolnych baz analitycznych (baza danych, której celem jest wygoda użytkowania dowolnych analiz z poziomu Excela, np. powyżej opisana hurtownia danych) są możliwe do stworzenia w AFIN.NET i gotowe do implementacji w dowolnym środowisku informatycznym klienta.

AFIN.NET oferuje również funkcje, które bezproblemowo, szybko i łatwo dostarczają informację do dowolnych raportów użytkownika.

Business Intelligence w Excelu? Cokolwiek to znaczy, ale – TAK!