Zarządzanie danymi analitycznymi z poziomu Excela

Zarządzanie danymi w Excelu

Wojciech Gardziński, Krzysztof Rumiński

Współpraca: Paweł Wróblewski (analityk firmy Value Based Advisors Sp. z o.o.)

W poprzednich artykułach stwierdziliśmy, że

Excel jest łatwym, uniwersalnym, a przez to bardzo użytecznym narzędziem analitycznym
Efektywna gospodarka danymi oraz precyzyjna znajomość cech poszczególnych metod analitycznych oraz dostępowych, pozwalają łatwo komunikować się (i utrzymać nad tym kontrolę) z zewnętrznymi źródłami danych.

Pora odpowiedzieć na pytanie:

Czy możliwe jest zarządzanie danymi analitycznymi z poziomu Excela?

Odpowiedź niewątpliwie zdziwi, a już na pewno spowoduje gorące zaprzeczanie temu ze strony dostawców systemów analitycznych, ale …gdy przyjrzymy się pewnym faktom, gdy jasno zdefiniujemy sobie cele naszych działań analitycznych – odpowiedź brzmi „Do pewnego stopnia rozwoju, pod pewnymi warunkami, przy utrzymaniu niezbędnej dyscypliny – Tak.”

Hurtownia danych (HD) – aplikacja za miliony czy efektywna metoda informatyczno-analityczna?

Hurtownia danych z definicji to: oddzielona od systemów transakcyjnych, niezależna baza danych, specjalizowana dla dostarczania informacji analitycznej (biznesowej itp.)

Niewątpliwie:

Dane dla celów analitycznych POWINNY być zorganizowane tak, jak potrzebuje analityk, a nie tak, jak chce informatyk (o ich celach – w poprzednim odcinku cyklu). Wynika z tego, że MUSI powstać coś (aplikacja), która co jakiś czas lub na życzenie użytkownika, zaciągnie dane i je odpowiednio przetworzy do odpowiedniej, W TYM CELU POWSTAŁEJ, bazy danych.
Dane MUSZĄ być przetworzone i uporządkowane strukturalnie. Kostki OLAP, (powstały w tym celu format zapisu danych), nadają się do tego doskonale, ale nie jest to wymóg efektywności HD

Czy Excel to potrafi?

Sam Excel nie potrafi nic – bo jest to arkusz kalkulacyjny. Dysponuje on jednak narzędziami, które, po nabyciu odpowiedniej wiedzy, mogą w znacznym stopniu ułatwić, a niekiedy wręcz zastąpić, profesjonalne aplikacje HD.

Wielokrotnie opisywaliśmy metody pobierania danych przez ODBC do Excela – najprostsze narzędzie, program MS Query (http://www.infor.pl/pisma/katd.php?pismo=m48&P180=m48.2001.006.000000500) dodatkowo uczy tego, prowadząc niejako za rękę dociekliwego analityka po bazach danych. Bardziej wyrobieni korzystają w tym celu np. z Accessa. Funkcje SQL-owe, dostępne w Excelu (!) (konkretnie funkcja SQL.REQUEST) potrafią jednak o wiele więcej, niż tylko pobierać dane do arkusza. Spróbujmy!

Jak uruchomić funkcję? – (szczegółowy opis procedury: http://www.infor.pl/pisma/katd.php?pismo=m48&P180=m48.2001.008.000000700)

Wróćmy jednak do meritum. Jak, wykorzystać tę funkcję do tworzenia quasi-hurtowni danych (QHD)? Załóżmy, że dane posiadamy w plikach w formacie DBF, a QHD chcemy mieć w plikach formatu XLS, czyli plikach Excela (sic!) – jest to możliwe, a ponieważ NIC (oprócz pobierania z nich danych) nie będziemy z tymi plikami dalej robić – jest to nawet do jakiegoś stopnia (ograniczenie 65.000 wierszy arkusza) EFEKTYWNE!

Definicja problemu informatycznego:

Posiadamy bazę zestawienia obrotów i sald (obrotówka), wygenerowaną z programu FK. Plik jest w formacie DBF. Tabela zawiera pole ‘konto’ – przykład konta: 401009241200 oraz pola z wartościami sald winien i ma. Chcemy, aby dane, w naszej docelowej tabeli przestawnej, agregowały się do pozycji ‘Amortyzacja’, ‘Materialy’, itp.. Wiemy, że amortyzacja to wszystkie konta, rozpoczynające się od ‘401’. Stworzyliśmy nawet „słownik”, tj. plik Excela, w którym przyporządkowaliśmy odpowiednim kontom syntetycznym ich docelowe etykiety.

Rys1. Przykładowy słownik

Rys2. Przykładowa baza (tabela) danych

Aby osiągnąć nasz cel, musimy wykonać kilka ruchów (operacji), a to:

doprowadzić oba pliki do jednakowego formatu, najlepiej do jednej bazy danych (W formacie XLS bazą danych jest SKOROSZYT, tabele to nazwane obszary w kolejnych arkuszach)
wydzielić z obu plików pole, na którym potem utworzymy RELACJĘ (połączenie tabel). Tu konieczne jest wydzielenie (w obu tabelach kolejno) 3-znakowego, TEKSTOWEGO pola, np. o nazwie ‘konto1’.
stworzyć jedną „szeroką” tabelę, powstałą z połączenia bazy i słownika po relacji na polu ‘konto1’. Plik, zawierający tę tabelę będzie elementem naszej QHD. Elementów takich może być dowolna ilość.
zaciągnąć dane do arkusza, a następnie na tych danych zbudować tabelę przestawną

Warunki działania: Muszą być zainstalowane:

1. Program Microsoft Query

2. Funkcja SQL.REQUEST (Dodatek Excela o nazwie ODBC Add-In).

Przy stosowaniu wersji Excela „97” lub „2000” w przypadku instalacji pełnej oba warunki są spełnione.

W przypadku Excela w wersjach „XP” oraz „2003” funkcja musi być DOINSTALOWANA z dostępnego na stronach Microsoftu dodatku XLODBC.EXE. Dostępny jest on również w naszym dziale „Pliki do pobrania” na witrynie www.controlling.infor.pl .

3. W Panelu sterowania muszą być zarejestrowane źródła danych o nazwach: „Pliki programu dbase” oraz „Pliki programu Excel”

Szczegóły, dot. powyższych warunków oraz instrukcja JAK tego dokonać, znajduje się w artykułach, do których podano odnośniki (powyżej)

Załóżmy, że wszystkie nasze pliki znajdują się w katalogu C:\CiRZ\HD_Prz1

Tworzymy nowy skoroszyt Excela, a w nim wpisujemy funkcję (lub wszystkie kolejne funkcje, jedna pod drugą) SQL.REQUEST z następującymi argumentami:

1-szy krok:

=SQL.REQUEST("DSN=Pliki Programu DBase;DBQ=C:\CiRZ\HD_Prz1";;;
"SELECT LEFT(konto,3) AS konto1, * INTO czw IN "C:\CiRZ\HD_Prz1\chw"[Excel 5.0;] FROM st200401 WHERE konto LIKE '4%'";1)

2-gi krok:

=SQL.REQUEST("DSN=Pliki Programu Excel;DBQ=C:\CiRZ\HD_Prz1\chw.xls";;;
"SELECT LEFT(konto,3) AS konto1, * INTO slownikkont FROM slownikkont IN "C:\CiRZ\HD_Prz1\Slowniki"[Excel 5.0;] ";1)

3-ci krok:

=SQL.REQUEST("DSN=Pliki Programu Excel;DBQ=C:\CiRZ\HD_Prz1\chw.xls";;; "SELECT czw.*, slownikkont.opis INTO czw2 FROM czw, slownikkont WHERE czw.konto1=slownikkont.konto1";1)

Możemy wszystkie te funkcje wpisać w jeden arkusz

Ważne jest, żeby zostały wykonane (przeliczone) kolejno (od góry). Można to wymusić albo kilkakrotnym przeliczeniem arkusza albo kolejną „edycją” kolejnych funkcji (na kolejnych formułach [F2] i [Enter]). Na dysku zostanie utworzony plik – skoroszyt Excela o nazwie CHW.XLS (od „chwilowy”) – można go kasować – należy wręcz to zrobić przed odświeżaniem danych. Zawiera trzy arkusze: 1.: ‘czw’ - zawiera przepisane z bazy konta zespołu ‘4’ ; 2.: ‘slownikkont’ - zawiera przepisany do tego skoroszytu słownik, 3.: ‘czw2’ zawiera połączoną tabelę bazy danych ze słownikiem.

Kolejnym krokiem jest utworzenie nowego arkusza i utworzenie w nim kwerendy MS Query, która pobiera dane (całą tabelę) z arkusza (tabeli) ‘czw2’ ze skoroszytu CHW.XLS (źródło danych: Pliki programu Excel).

(Arkusz ten może być w tym samym skoroszycie – tak jest nawet wygodniej – tu utworzony został skoroszyt PROGRAM.XLS. Jest to typowy skoroszyt analityka – odseparowany od danych, ale stanowiący „centrum zarządzania tymi danymi” oraz stanowiący zarazem „zestaw analityczny”, tj. zbiór odpowiednio przygotowanych danych oraz narzędzi analitycznych z nimi powiązanych – tu jest to tabela przestawna)

Tak utworzona kwerenda jest z kolei podstawą utworzenia na niej tabeli przestawnej w kolejnym arkuszu:

Tak więc powstało narzędzie – W CAŁOŚCI W EXCELU, nie zawierające żadnych makr (!!!), które przy otwarciu tegoż skoroszytu, automatycznie przelicza funkcje, które tworzą na dysku nowy skoroszyt z przetworzonymi odpowiednio danymi. Wystarczy więc go otworzyć, odświeżyć kwerendę, a następnie odświeżyć tabelę przestawną.

TRZY KLIKNIĘCIA MYSZĄ !!!

Jak powyższe wykonać innymi metodami, gdyż ta niewątpliwie jest dość skomplikowana?

1. Sposób:

Można to wszystko (słownikować bazę) zrobić oczywiście ręcznie. Sposób bardzo pracochłonny i niewarty wzmianki, gdyby nie… stosowany w 90% przypadków. Excel, owszem, stwarza „pokusę wpisywania w nim danych” – warto jednak poznać jego niestandardowe możliwości.

2. Sposób:

Analitycy wykonują często słownikowanie, otwierając bazę pod Excelem i wpisując w kolejne kolumny obok niej funkcje wyszukaj(), INDEKS(), itp., kojarząc w ten sposób dane ze słownika z wartościami w tabeli. W tej metodzie zostaje jednak zaburzona pewna podstawowa zasada tworzenia repozytorium danych: dane i ich obróbka są integralnie powiązane ze skoroszytem – niemożliwe jest odświeżenie danych bez otwarcia tegoż. A gdy takich skoroszytów jest kilkadziesiąt, a rekordów w bazie kilkanaście tysięcy, zajmuje to bardzo (!) dużo czasu. Skoroszyty przeliczane kilka godzin nie stanowią tu rzadkości. Poza tym tworzą się odwołania międzyskoroszytowe, które przy najmniejszej zmianie układu analiz skutecznie psują nam cały, misternie stworzony, zestaw analityczny.

3. Sposób:

Bardziej doświadczeni analitycy otwierają bazę pod Excelem, „przenoszą” arkusz do skoroszytu ze słownikiem, zamykają go potem z zapisem, a następnie otwierają skoroszyt analityczny i dokonują powiązania tabel relacją w kwerendzie MS Query. Metoda skuteczna, ale wymaga każdorazowej edycji bazy w celu stworzenia pola łączącego (tu pole ‘konto1’) – można to co prawda zrobić w samej kwerendzie, ale to ją komplikuje. Zawodność programu MS Query powoduje potem duże komplikacje przy odświeżaniu danych i całą operację trzeba wielokrotnie na nowo definiować.

4. Sposób:

Operację łączenia tabel zleca się często informatykom, którzy najczęściej wykorzystują do tego program MS Access. Działa, ale wymaga każdorazowej interwencji informatyka oraz posiadania samego Accessa, co niekiedy stanowi dodatkowy, osobny problem.

Jak widać, Excel, sam w sobie, potrafi wszystkie te procedury zautomatyzować.

Dodatkowe zalety opisanej metody:

Tworzy jednocześnie element QHD – plik (skoroszyt) Excela z danymi, które mogą zostać wykorzystane nie tylko przez jednego użytkownika.
Opisana metoda NIE JEST ZALEŻNA od wielkości bazy danych – praktycznie równie szybko (kilka sekund) przetwarza dane w tabelach o kilku rekordach (wierszach), jak i tabelach z kilkudziesięcioma tysiącami rekordów. Słowniki mogą być wielokolumnowe, tzn. zawierać informację strukturalną. Tu np. można stworzyć dodatkowy słownik, opisujący dalsze człony konta, które niekiedy są tożsame np. z MPKami.
Opisana metoda jest BEZPIECZNA, tzn. bardzo trudno ją zepsuć. Sparametryzowanie funkcji wartościami tekstowymi powoduje jej niezależność np. od stanowiska na którym jest uruchamiana. Wyjątek: psuje się jedynie kwerenda, ale ona bezpośrednio do opisanej metody nie należy.
Opisana metoda KOJARZY UMIEJĘTNOŚCI analityka z umiejętnościami firmowych informatyków. Użyty w funkcjach język SQL (Structured Query Language) jest podstawowym językiem oprogramowania zapytań do dowolnych baz danych. Firmowi informatycy Excela zwykle nie znają, można się jednak spodziewać od nich chociaż podstawowej znajomości języka SQL.

Do czego można powyższą procedurę wykorzystać – poważniejsze zastosowania:

Opisane powyżej słownikowanie kont jest jednym z najprostszych zastosowań, ze względu chociażby na ograniczoną ilość kont w planie kont i dużą nad tym kontrolę, sprawowaną przez księgowych.

Nadaje się jednak doskonale również do słownikowania danych ze sprzedaży, a konkretnie tzw. rejestru pozycji faktur. Wielokolumnowe słowniki kontrahentów (region, miasto, grupa1, grupa2, …) oraz towarów/produktów (grupa1, grupa2, …, typ, rodzaj, kolor, ?) powodują możliwość stworzenia struktury analitycznej, porównywalnej z funkcjonalnością systemów OLAPowych. Znamy również przypadki, gdy tą metodą przygotowywane są dane, importowane do kostek OLAPowych (MS Query również to potrafi!) i włączane potem do profesjonalnych systemów analitycznych jako pełnoprawny komponent bazy danych.

Osobną zaletą jest również możliwość automatycznego atrybutowania wymiaru CZAS, tj. przyporządkowywania do dat, automatycznie wyliczanych wartości roku, kwartału, miesiąca, dekady czy dnia. Docelowa tabela przestawna ma wtedy możliwość prezentacji danych również w układzie struktury czasu – podstawowym wymiarze każdej profesjonalnej aplikacji analitycznej.