Ale nie wolno go kopiować ani drukować, chyba, że uzyska się od autorów odpowiednią, pisemną, zgodę. Taki, my autorzy, mamy kaprys. Prosimy o poszanowanie.
W odpowiednim czasie zostanie opublikowany w formie papierowej.
Tekst nie jest jeszcze gotowy – ale codziennie jest go więcej… Zapisz w ‘Ulubionych’ ;)
Podoba się? Zapraszamy na szkolenia.
Rozdział książki WG&KR „SQL w Excelu”
Wojciech Gardziński, Krzysztof Rumiński © 2010
Spis treści:
Współpraca z systemami transakcyjnymi i analitycznymi – zagadnienia organizacyjne
Współpraca Excela z bazami danych
Dane serwerów OLAP lub plikowych kostek OLAP
Porównanie przydatności poszczególnych baz danych w pracy analityka – tabela
Słowo-klucz – „Bezpieczeństwo”
Głównym celem IT, szczególnie w wydaniu korporacyjnym, jest bezpieczeństwo systemu. Składa się ono z kilku komponentów
Szanowny Czytelniku-analityku, pomyśl teraz: Czy, będąc na miejscu ludzi, pracujących w Dziale IT, cały czas w klimacie „oblężonej twierdzy” (bo albo hakerzy nam zagrażają, albo audytorzy), rozmawiałbyś chętnie z jakimiś analitykami, którzy ciągle chcą jakiś raportów, nie umieją SQL (a SQL zapewnia bezpieczeństwo – zapytania są zapisywane do logu, a w bazie danych da się ustawić zabezpieczenia, dotyczące trybu i zakresu zapytań, zadawanych do bazy), wszystko robią w ultra-uniwersalnych (czytaj: zawodnych) formułach Excela i makrach VBA, nie wiedzą, co to jest relacja w bazie danych („a my właśnie strzeżemy tu relacyjnej bazy danych”), itp.?
Korporacyjne działy IT, pilnując tak postrzeganego bezpieczeństwa, nie zauważają jednak, że często nie dopilnowują (bo i nie mają jak) faktycznego rozprzestrzeniania się informacji biznesowej – już przetworzonej. Każdy widział superzabezpieczone systemy transakcyjne oraz ich bazy danych i – równolegle – analityków, wysyłających gotowe sprawozdania finansowe pocztą elektroniczną, kopiujących raporty na pen-drive’y lub „biorących pracę do domu”.
Autorzy widzieli kiedyś sytuację, gdy użytkownik systemu, był zmuszony wysłać coś prze Internet (w ramach swoich obowiązków), posiadał jednak ograniczenia, praktycznie na wszystko: zapis na nośnik zewnętrzny, pocztę elektroniczną, w tym przez www i ftp. Ale IT nie przewidziało, że aplikacje biurowe, w tym Excel, posiadają okienko „otwórz plik” będące w istocie Eksploratorem Windows, z którego można zapisywać pliki w Internecie – użytkownik to wiedział i często korzystał.
Zapewnienie bezpieczeństwa systemu nie polega bowiem wyłącznie na bezpieczeństwie danych źródłowych, ale tego strzeże IT najbardziej – bo to najłatwiej sprawdzić.
Czyli, do powyższej listy, można dodać punkt:
Słowo-klucz – „Excel”.
Wystarczy przeanalizować oferty pracy dla analityków (controllerów) finansowych, żeby stwierdzić, że najbardziej poszukiwaną umiejętnością pracownika jest jego umiejętność pracy w arkuszu kalkulacyjnym, identyfikowanym jednoznacznie z konkretnym produktem Microsoftu – Excelem. Potwierdzają to oczywiście badania, dotyczące praktyki controllingowej w firmach.
Dlaczego Excel? Bo otwarty i uniwersalny, bo jest typowym programem-asystentem, czyli jest narzędziem, a nie „czarną skrzynką”, bo dostępny – jest wszędzie, bo znany – wszyscy potrafią stworzyć w nim raport, nawet jeśli wpiszą wartości ręcznie.
My, autorzy książki „SQL w Excelu”, choć doceniamy jego funkcjonalność i pozycję rynkową, nie twierdzimy jednak, że inne arkusze kalkulacyjne nie pełnią podobnej funkcji. W OpenOffice.org Calc również da się wykorzystywać funkcje SQL – wydaje się jednak, że w tym środowisku jeszcze nikt jeszcze „nie dorósł” do tego typu potrzeb.
Oczywiście, dla analityka, Excel nie jest celem samym w sobie, tylko uniwersalnym narzędziem pracy. Pełna kontrola nad plikiem, możliwość jego zapisu, otwarcia, wysłania pocztą, skopiowania zawartości, połączenia z innym plikiem, łatwy wydruk, ogromna ilość funkcji, ułatwień i coraz bardziej zaawansowanych narzędzi analitycznych sprawiają, że Excel sam w sobie jest dziedziną wiedzy. Szefowie ufają, że posiadanie tej wiedzy, automatycznie gwarantuje posiadanie umiejętności analitycznych.
Analityk więc wszystko, co dostaje, chce otworzyć w, znanym sobie i potężnym, środowisku analitycznym – Excelu (pewność dostępu). Wszystko chce tu też przetworzyć na informacje biznesową (poczucie kontroli). Dzieli się również ową informacją za pośrednictwem pliku Excela jako medium przekazu informacji (pewność komunikacji – wszyscy umieją otworzyć, poruszać się, wydrukować). „Wszystko” oznacza jednak próbę zawłaszczenia do Excela funkcjonalności z innych dziedzin informatyki, szczególnie funkcjonalności bazodanowej, która – nie trzeba chyba tego wyjaśniać – w zakresie przetwarzania informacji masowej jest tysiąckroć bardziej efektywna. Analitycy tego nie dostrzegają, bo brak im podstaw wiedzy bazodanowej, informatycy im w tym nie pomagają, bo są zajęci czym innym, szefowie wymagają informacji, więc na własną rękę rozwijane są systemy informacyjne, nie mające nic wspólnego z efektywnością przetwarzania informacji. Analitycy uczą się VBA (!), czyli programowania, ufając, że to rozwiąże im problemy bazodanowe – nie są do tego przygotowani, więc efekty ich pracy w większości przypadków tylko kryzys pogłębiają.
Analitycy chcą przetwarzać dane na informację biznesową. Ale Excel – to za mało.
Słowo-klucz – „Dostępność i wiarygodność informacji”
Byłem Kontrolerem Kosztów, Kontrolerem Finansowym, Kierownikiem Kontrolingu a teraz Dyrektorem Finansowym i nadal praca w Controllingu polega na tym aby to co dzieje się w firmie jako działania ludzi i maszyn przełożyć na liczby i vice versa tak aby Ci którzy mają podjąć decyzję podjęli ją na podstawie WYTŁUMACZALNYCH danych a nie na podstawie danych wyssanych z palca pomnożonych przez liczbę komarów na suficie.[1]
Decydenci potrzebują informacji i są w stanie dużo za nią zapłacić. Co otrzymują?
Z jednej strony utrzymują, niekiedy bardzo rozbudowane, działy analityczne, z drugiej zaś, widząc ich nieustanną walkę o informację w okresach raportowych, dają wiarę „szamanom” – firmom, które zapewniają „Kupcie nasz system – my zapewnimy wiarygodność, spójność i niski koszt informacji biznesowej. Niewielu jest w stanie odeprzeć miło i profesjonalnie brzmiące argumenty o „pulpitach decyzyjnych”, „jednej wersji prawdy, dostępnej w każdej chwili” i o stałej opiece, nie tylko informatycznej, ale, przede wszystkim, eksperckiej.
Niezależnie jednak od systemu, jaki posiada firma, sytuacja wygląda podobnie: to nie system informatyczny dostarcza informacji, tylko ów dział analiz, przy czym dziwną prawidłowością jest, że działy analityczne firm, wyposażonych „w specjalistyczne narzędzia analityczne”, zatrudniają więcej osób, niż podobne działy firm nie wyposażonych.
Informacja biznesowa to nie tabela przestawna i nie raport zdefiniowany – to aktualna, rzetelna, porcja informacji (liczb), zgodna z AKTUALNYM (sic!) zapotrzebowaniem. Zysk firmy inaczej prezentuje się dla banku, inaczej dla Urzędu Podatkowego.
Tylko, czy nasz system informatyczny, potrafi nam te informacje dać?
Wykształciło się wiele sposobów korzystania z danych systemu transakcyjnego (OLTP):
i.
Dziedzinowa – własna, wykonana przez analityków ze wsparciem IT
lub wyspecjalizowanej firmy zewnętrznej
(JAK TO ZROBIĆ SAMODZIELNIE? – w kolejnym rozdziale.)
ii. Przez IT. Dostosowana do potrzeb firmy, być może niezbyt efektywna, ale to najlepsze rozwiązanie z punktu widzenia analityka.
iii.
W ramach działu finansowego / controllingu / sprzedaży, sporządzona we
własnym zakresie przez analityków ze znajomością Accessa.
Bardzo często spotykany przypadek. Najczęściej są to pliki Accessa, zasilane
przez pobory danych, sterowane makrami Accessa lub VBA.
To „prawie” dobre rozwiązanie – minusem jest to, że aplikacja (czyli te makra)
są wbudowane w ów plik z danymi. Każdy problem z odczytem-zapisem danych, może
się odbić na pliku-hurtowni.
Może być również tworzona/zapisywana/uaktualniana własnym programem
zewnętrznym – to rozwiązanie zdecydowanie polecamy, jako najbezpieczniejsze i
najbardziej efektywne. Program może być napisany w dowolnym języku, byle
sterował danymi, wykorzystując język SQL. W przypadku wystąpienia błędu –
operacja się nie powiedzie, ale baza pozostanie nienaruszona.
i. Bez BI lub „klienta HD”. Bardzo rzadki przypadek – dostawcy hurtowni danych zawsze sprzedają razem z nią programy z niej raportujące (bo na tworzeniu raportów najlepiej się zarabia). Jeżeli mamy do niej dostęp z Excela przez ODBC jest to również najlepsze z możliwych rozwiązań.
ii.
Z BI lub „klientem HD” – programy te, różnych producentów, są różnej
jakości – wszystkie jednak w jakiś sposób udostępniają dane do importu do
Excela. Ich funkcjonalność wewnętrzną – pomijam – i tak wszyscy chcą od tych
programów jednego – eksportu do Excela.
I tak – jeżeli eksportują dane w plikach Excela (àpkt 5.a.)
Ale, jeżeli oferują bezpośredni dostęp do swojej bazy danych (bardzo rzadki
przypadek, choć tutaj nie ma przeciwwskazań, jak w punkcie 3.a.), jest to dobre
rozwiązanie.
Co prawda dane nie są zorganizowane tak, jak chcemy (bo wdrażała firma
zewnętrzna), ale łatwiej potrzeby informacyjne uzupełnić, niż tworzyć od nowa.
Technologia IMA nie przynosi, niestety, rewolucji w dziedzinie analizy danych. Założenie, że dane w pamięci operacyjnej przeliczają się szybciej, jest prawdziwe, ale jest też tylko częścią prawdy. Żeby dane w pamięci operacyjnej się przeliczały (szybko, czy wolno) trzeba je tam najpierw załadować, a, gdy są już załadowane, odświeżać. Hasło, które niewątpliwie jest nośne marketingowo, jest więc mylące.
Jako przykład podajmy bazę, składającą się z dwóch tabel – 100.000-czna (ilość rekordów) tabela transakcji i 100 rekordowy słownik tej tabeli. Np. informacja o mieście klienta dla tabeli transakcyjnej (relacja po kodzie klienta). Naszym celem jest sporządzenie tabeli-raportu, w której otrzymamy informacje [‘miasto’, ‘suma sprzedaży wszystkich klientów z tego miasta’]. Dane znajdują się dla ułatwienia na naszym dysku i są w formacie (pliku) Accessa. Tabela wyjściowa (raport) ma mieć 100 rekordów i zostać przesłana do Excela.
W architekturze ODBC (kwerenda z Excela) złączenia tabel dokonuje sterownik ODBC w momencie odświeżania kwerendy, dokonuje też zsumowania danych według miast, i pogrupowania ich według miast – tabela wynikowa ma 100 rekordów.
W architekturze IMA (np. Power Pivot Excela 2010) obie tabele są w całości (czyli 100.000 + 100 rekordów) transportowane do tymczasowej bazy w pamięci operacyjnej, zarządzanej przez tzw. IMA Server, czyli silnik bazodanowy Power Pivot. Tu tabele są łączone i pozostawiane w dyspozycji użytkownika. Ten może z tych danych zrobić tabelę przestawną, w której pogrupuje miasta i posumuje dane według miast – operacja ta odbędzie się rzeczywiście bardzo szybko (ułamek sekundy) – i to jest właśnie owa, reklamowana, „szybkość pracy” technologii IMA – robi wrażenie!
Gdy jednak dane trzeba odświeżyć, wszystkie operacje (oprócz sporządzania tabeli przestawnej, bo już jest) trzeba powtórzyć. A tu, zaczyna grać rolę czas!
I nawet, w tak przyjaznej dla IMA architekturze, jak opisana powyżej, czyli gdy serwer danych znajduje się na tej samej maszynie, co serwer IMA, okazuje się, że, przy parametrach określonych powyżej, odświeżanie kwerendy (ODBC) trwa ponad trzy razy krócej, niż w technologii IMA. Oczywiście daje się to zauważyć tylko przy dużych ilościach danych, ale do takich właśnie operacji obie technologie zostały stworzone.
W przypadku, gdy serwer danych byłby oddzielony od serwera IMA siecią lokalną (czyli nie na tej samej fizycznej maszynie), stosunek ten wyniósłby „kilkadziesiąt”, a w przypadku dostępu Internetem, nawet „kilka tysięcy” (na korzyść ODBC). Architektura klient-serwer naprawdę ma zalety.
Dlaczego tak się dzieje?
Żeby to zrozumieć, trzeba spojrzeć na to od strony celu – szybkiego działania tabeli przestawnej.
Czym jest tabela przestawna? Tabela przestawna, a właściwie jej zbiór danych, tzw. „pivot cache” jest obiektem danych wielowymiarowych, zdefiniowanych przez ową tabelę. Czyli – jest zmienny, bo tabela jest zmienna („obiekt o zmiennej geometrii”). Skoro jest zmienny – musi coś w trakcie przeliczać (zmieniać się) – to trwa, musi trwać. Przy dużych zbiorach danych, a tabela przestawna może mieć zewnętrzne źródło danych praktycznie dowolnej wielkości, jej przeliczanie trwa stosunkowo długo, dlatego, że musi odczytać dane z dysku (ODBC). Jeżeli działa na danych arkuszowych – odświeżenie jest natychmiastowe. Zauważono ten fakt już dość dawno i wymyślono technologię OLAP (dane wielowymiarowe) – tabela przestawna łączy się w tej technologii z tzw. kostką .CUB – plikiem, przechowującym dane wielowymiarowe – tabela jest w tym wypadku interfejsem źródła OLAP..
Obecnie, jako dalszy krok, stworzono technologię IMA – czyli tabelę przestawną, wyglądającą, jak arkusz danych, czyli dane utrzymywane są w pamięci operacyjnej. Umożliwia to bardzo szybkie przeliczenie tabeli, ale – jak wiadomo – wszystko ma swój koszt. Gdy chcemy dane odświeżyć – w przypadku ODBC – odczytywana jest tabela danych, a na jej podstawie odbudowywany moduł OLAP tabeli przestawnej (pivot cache). W przypadku IMA – jego silnik od razu (ale też przez to samo ODBC) zaczytuje dane z tabeli i odbudowuje wewnętrzny moduł, grupujący i agregujący dane (to jeszcze nie tabela przestawna) – trwa to o wiele dłużej, niż tylko odświeżenie danych. Potem, oczywiście, tabela przestawna na tym module działa błyskawicznie.
Ten moduł IMA to w gruncie rzeczy moduł OLAP, ale ze względów marketingowych, „nie wolno” używać słowa „OLAP” w kontekście IMA.
A technologia zapisu danych wielowymiarowych w Excelu w zewnętrznym pliku danych, tzw. kostce .CUB, jest znana w Excelu od 2000 r., ale, z powyższych, marketingowych, względów, wycofana w Excelach 2007 i 2010, z powodu „zbyt ubogiej funkcjonalności”.
i. Tabela przestawna wymienia dane z tym obiektem – nie kontaktuje się bezpośrednio z danymi – w PowerPiwot odświeżanie jest dwuetapowe i skomplikowane
ii. Dane tego obiektu nie obciążają arkusza – zapisywany skoroszyt Excela nie posiada sam w sobie tych danych – dane przechowywane są w pamięci operacyjnej systemu – oznacza to, że przesłanie skoroszytu nie powoduje przesłania danych. W Power Piwot nie jest możliwe przesłanie samych danych (dystrybucja obiektu wielowymiarowego) – nie jest możliwe również przesłanie skoroszytu z zapisanymi danymi tabeli przestawnej
iii. Nie ma możliwości grupowania danych według własnych ustawień, np. według hierarchii czasu ani tworzenia własnych grup w tabeli przestawnej.
iv. Odświeżanie danych w Power Pivot trwa dokładnie tyle samo czasu, co odświeżenie modułu OLAP (kostki .cub)
v.
Podłączanie danych jest bardziej skomplikowane, niż w przypadku kwerend
MS Query
Nasza – autorów (2010) – prognoza rozwoju tej technologii (IMA) nie jest jednak zbyt optymistyczna (dla użytkowników). Ponieważ obecnie do pamięci operacyjnej zasysa się dane transakcyjne w ogromnych ilościach, producenci szybko zauważą, że to nieefektywne. Stworzą więc dodatkową warstwę pośrednią dla serwera IMA, która będzie pozwalała mu się szybciej odświeżać, a sama będzie odświeżana w trybie administracyjnym, czyli… będzie hurtownią danych ROLAP dla serwera IMA (MOLAP). Wszystko więc wróci na swoje znane miejsce, ale klienci znowu będą musieli zapłacić za „nowa technologię”. Oczywiście wszystko może potoczyć się inaczej, ale już zaczynają się pojawiać informację o „oddzielaniu bazy danych od aplikacji” (to zasada obowiązująca w informatyce od ok. 20 lat) w najnowszych wersjach oprogramowania IMA.
W wielu przypadkach, niestety, żadna z metod odczytu danych, nie daje rezultatu, ze względu na zbytnią komplikacje logiczną zapisu danych w pliku tekstowym.
Rozwiązaniem może być użycie AFIN.NET.TextConverter (w pakiecie AFIN.NET – dostępnym na dysku). A dalej, gdy ze skomplikowanego pliku tekstowego powstanie normalna tabela w pliku, dostępnym bazodanowo, można ją dalej traktować jako, już poprawny, eksport z systemu (àpkt 5.a.)
Nie, nie i jeszcze raz nie! Excel NIE JEST bazą danych.
Nie zamierzamy tu promować Excela jako miejsca GROMADZENIA danych.
Ale może, a nawet powinien nią być w pewnych, ściśle określonych sytuacjach, a mianowicie – gromadzenie danych rozproszonych (nazwijmy to w skrócie „formularze”) oraz dane wymagające uzgodnień („medium komunikacji”).
Baza danych to nie tabela (analitycy często mylą) – to ogromna przestrzeń gromadzenia informacji. Nie jest ona zorganizowana tak, jak chciałby analityk – w dużych, tzw. „szerokich” tabelach. Tu nie ma formatów i wykresów – tu są tabele (w dużej ilości), relacje, rekordy, pola, indeksy, triggery, transakcje. Informatycy ukuli pojęcie „normalizacji bazy danych” – to akurat coś dokładnie odwrotnego, niż chcieliby analitycy. Informatycy dążą do właściwej organizacji danych, ich bezpieczeństwa, wielodostępowości, szybkości działania. Baza danych to rzecz tajemna.
Ale baza danych to źródło informacji – jak więc z niego korzystać?
Przyjrzyjmy się poszczególnym formatom (typom) baz danych z założeniem zamiaru stworzenia pośredniej warstwy danych do wykorzystania przez analityka – tzw. Hurtowni Danych Analityka (HDA)
Pliki Excela bywają bazą danych. Łatwo się tu dane wpisuje, łatwo poprawia, uzgadnia, przesyła. O łatwym formatowaniu, setkach funkcji, łatwości edycji nie wspominamy – gdyż wykracza to poza zakres tematyczny tej książki. Skupiamy się na skoroszycie Excela – jako pliku, w którym są dane. A rozróżnienie „plik Excela – źródło danych versus plik Excela – raport”, jest podstawowym zadaniem naszej książki.
Aby jednak korzystać z Excela jako źródła danych, trzeba co nieco wiedzieć o systemach bazodanowych. Odsyłamy więc Czytelnika na chwilę do Rozdziału „Relacyjna Baza Danych”.
Gdy już wiemy, co to jest tabela, skupmy się na tym, jak tabele organizować w Excelu.
Jeżeli chcemy z danego pliku korzystać „bazodanowo” należy danym zapewnić:
Baza danych w plikach Excela sprawdza się w powyższych zastosowaniach ze względu na unikalne jej zalety – 1. są to pliki, 2. są to pliki odczytywalne przez wszystkich, 3. zasady obsługi są powszechnie znane.
Żaden inny format baz danych takiej funkconalności nie oferuje. Ale baza ta ma również wady – trzeba precyzyjnie wiedzieć, jak ich unikać – precyzyjnie je wyliczamy.
Nie polecamy stosować plików Excela jako HDA. Pliki Excela są zawodne (czasami się uszkadzają) ale, przede wszystkim, mają ograniczoną pojemność (2^8 kolumn i 2^16 wierszy; w Excelu 2007 więcej, ale tam pojawiają się inne ograniczenia). Jest jeszcze jedna wada – odczyt zapis do plików Excela jest wolniejszy niż do pliku Accessa – przy dużych ilościach danych ma to znaczenie.
Ale Hurtownia Danych Analityka (HDA) w plikach Excela (XLS) ma pewien sens – sens wyłącznie dydaktyczny i testowy. Naucz się logiki HDA i zmień jej format na Accessa.
Dlaczego opisujemy format Excela 2007 jako odrębny punkt? Microsoft stworzył w wersji 2007 nowy format danych XLSX, nieodczytywalny przez poprzednie wersje (można pobrać program konwertujący). Nie jest też odczytywany przez ODBC. Stworzono specjalną technologię do odczytu danych z plików Office’a 2007: Microsoft.ACE.OLEDB.12.0 . Sterowniki są darmowe, ale nie wbudowane – trzeba je zainstalować.
W/w sterowniki nie obsługują wielu operacji, np. instrukcji DROP TABLE, wymagają dopilnowania ustawień ciągu połączeniowego (domyślnie ReadOnly=TRUE) i wydają się być jeszcze niedopracowane. Dodatkowo – całkowity brak zabezpieczeń, tzn. zabezpieczenia standardowe Excela, dyskwalifikują ten format.
Nie polecamy tworzenia HDA w plikach Excela 2007
Hurtownia Danych Analityka to zdecydowanie najlepsze rozwiązanie dla firm małej i średniej wielkości oraz do prototypowania rozwiązań dla firm dużych i wielkich.
Program MS Access może być wykorzystywany przez analityków według następujących scenariuszy użycia:
Tworzymy plik Accessa i oprogramowujemy w nim wszystkie procesy zarządzania HDA od pozyskiwania danych, poprzez ich wielostopniowa obróbkę kwerendami oraz komendami SQL sterowanymi makrami VBA. Plik taki zawiera w sobie ‘aplikację’ czyli nasz program, który staje się integralną częścią pliku.
Warunki:
· Posiadamy licencję MS Access
· Znamy MS Access w stopniu umożliwiającym pisanie w nim makr
· Zapewniamy bezpieczeństwo techniczne naszej bazy poprzez regularne tworzenie kopii zapasowych, wersjonowanie
· Nie zamierzamy przenosić aplikacji na lepszy serwer bazodanowy
Zalety:
· Posiadamy pełną kontrolę nad HDA
· Możliwość przeniesienia aplikacji naszej HDA na serwer bazodanowy SQLS
· Możliwość stosowania formularzy, czyli pozyskiwania danych od operatora, np. aktualizacja słowników
· Możliwość tworzenia raportów standardowych, czyli raportowanie Accessa
Wady:
· Wszystko musimy oprogramować – dotyczy to szczególnie algorytmów automatycznego pozyskiwania danych z plików z danymi
· Godzimy się na regularne odtwarzanie naszej bazy ze względu na jej awaryjność i brak rozdzielania bazy danych i aplikacji
· Akceptujemy maksymalną pojemność bazy danych na poziomie 2 GB
· Konieczność otwierania pliku w przypadku potrzeby uaktualnienia słownika lub uruchomienia jakieś procedury. Może to spowodować awarię pliku w przypadku równoczesnego łączenia się z bazą innych użytkowników.
Tworzymy plik Accessa, w którym tworzymy wyłącznie (sic!) tabele dołączone oraz kwerendy na nich. Jest to opcja wyłącznie dla baz małych oraz jedno- lub kilkustanowiskowego odczytu ze względu na automatyczne odświeżanie wszystkich tabel dołączonych przy każdym zapotrzebowaniu na dane. Posiadamy za to pewność, że dane zawsze są aktualne. Jest to niewątpliwie efektowne i nadaje się na prezentacje, ale praktyczne wykorzystanie byłoby bardzo uciążliwe.
Nie tworzymy żadnych plików Accessa w Accessie (dalej będziemy używać rozróżnienia: pliki .MDB, ale program Access), żadnych programów ani makr wewnątrz plików .MDB. Wszystkim zarządzamy poprzez zewnętrzny program SQLowy – program w dowolnym języku programowania lub w dowolnej aplikacji zewnętrznej, potrafiący wysyłać instrukcje do sterowników OLE DB, ODBC, ADO – wykonujące wszelkie operacje bazodanowe. Pliki .MDB są wykorzystywane wyłącznie jako „czysta” baza danych.
Warunki:
· Nie musimy posiadać licencji MS Access
· Znamy SQL nie tylko w zakresie DML (Data Manipulation Language – odczyt danych) ale też DDL (Data Definition Language – organizacja oraz zapis danych)
Zalety:
· Brak konieczności wersjonowania bazy – w każdej chwili możemy ją odtworzyć, więcej – za każdym razem zamierzamy ją lub jej część odtwarzać na podstawie plików źródłowych
· Możliwość użycia dowolnych formatów danych, zarówno na wejściu, jak i wyjściu – również plików .XLS
· Możliwość stosowania wielu plików danych – oznacza to możliwość rozbudowania HDA do praktycznie dowolnej wielkości (każdy plik .MDB <2 GB) – tzw. Data Mart-y (dziedzinowe wycinki HD)
· Łatwość przeniesienia bazy na serwer MS SQL Server – wystarczy zmienić parametr „connection string” HDA
Wady:
· Najpoważniejszym ograniczeniem jest konieczność znajomości SQL – wszelkie operacje na bazie są dokonywane z zewnątrz, więc musza być wykonywane precyzyjnie
· Niemożność stosowania funkcjonalności programu Access; formularzy, raportów, opcji zabezpieczeń, opcji zapewniania integralności bazy
Wikiedia:
DBF (ang. Data Base File) - jeden z pierwszych formatów pliku bazodanowego dla PC, polegający na sekwencyjnym składowaniu rekordów o niezmiennej budowie w jednym pliku. Dostęp do rekordu danych realizowany jest wg wybranego klucza (w tym naturalnego np. GO lub SKIP) bezpośrednio przez aplikację lub wiele aplikacji (brak samodzielnego silnika - każda z aplikacji musi zawierać swój własny silnik).
DBF… Nam, autorom, łza się w oku kręci – od tego rozpoczynaliśmy naszą przygodę z bazami danych… Ale teraz jest teraz i oceniamy bez sentymentów.
Stary i nieefektywny format bazy danych. Brak silnika bazodanowego powoduje całkowity brak zabezpieczeń – wszystkie stosowane (np. zmiana rozszerzenia pliku lub tzw. ”zamiana bajtów”) są łatwo omijalne. Pliki są odczytywane przez wszystkie programy i to jest największą jego zaletą (Excel 2007 „zapomniał” jak się czyta format DBF, bo wszystkie poprzednie wersje robiły to doskonale). Nie polecamy tworzyć HDA w plikach DBF, ale możemy się spotkać z tym formatem. Jest to alternatywa dla tych, którzy z jakiegoś powodu nie chcą Accessa. Pliki DBF, ze względna stałą długość rekordu, doskonale się pakują (współczynnik pakowania w .ZIP to 10-20%)
SQL Server (SQLS) to trzecia, najważniejsza baza danych silnika MS JET (wcześniej MSDE – Microsoft Database Engine). Należą do tej grupy: Excel, Access i SQL Server. Zaletą MS JET jest praktycznie identyczny SQL (typy pól, zakres komend, składnia).
Ma to dla nas, analityków, ogromne znaczenie – wszystko, co stworzymy prototypowo na Excelu, zimplementujemy na Accessie, możemy łatwo przenieść do poważnego środowiska bazodanowego MS SQL Server, praktycznie tylko zmieniając „connection string”.
Cechy SQL Server z punktu widzenia analityka są tożsame z cechami Accessa, z następującymi różnicami:
Warunki:
Zalety:
Wady:
Dlaczego Oracle i DB2 występują razem w naszych zestawieniach? Są to SZBD użytkowo podobne (tabela). Posiadają wersje darmowe, posługują się zmodyfikowanym SQL, Można je zainstalować lokalnie, ale raczej nikt tego nie robi ze względu na fakt, że nie wymieni z nikim ani danych, ani programów SQL dane przerabiających (inny SQL, przede wszystkim inne typy pól). Używane są przede wszystkim jako bazy transakcyjne. Inny SQL sprawia również kłopoty przy zapisie danych klauzulą INTO…IN… - inne typy pól nie pozwalają na bezpośredni zapis danych z tabeli w innym SZBD (stąd „-„ w tabeli poniżej ad. cechy „Prędkość O/Z”). Bardzo ograniczona liczba specjalistów. Wersje płatne są drogie.
Oba systemy dysponują tzw. „interfejsem BD”, czyli oddzielnym programem do edycji bazy, również w wersjach darmowych. Interfejs webowy Oracle jest jednak użytkowo nie do zniesienia – nie można np. przewijać zawartości tabel, wyświetlających tylko jeden ekran rekordów w tabeli. Można przeklejać dane ze schowka – tylko do 30 rekordów naraz. Import z zewnętrznej tabeli jest bardzo skomplikowany.
Interfejs DB2 ma z kolei tyle niedopracowań technicznych, że zrobienie w nim czegokolwiek graniczy z cudem. Chociaż ma więcej funkcji, niż Oracle-owy, nie ma praktycznego zastosowania.
Obu systemów nie polecamy do użycia jako HDA.
Rys. Oracle
Rys. DB2
Dlaczego razem? Oba wymienione SZBD są darmowe – to ich główna cecha, implikująca szereg dalszych: ogromne rzesze zdeklarowanych fanów (czytaj: specjalistów), wiele forów internetowych, książek i informacji. Każda z nich ma zwolenników i przeciwników przeciw drugiej, ale dla analityka nie ma to większego znaczenia.
MySQL przejęta została przez Oracle (ma wiele podobieństw do Oracle) – wielu specjalistów IT ma (na razie nieuzasadnione) obawy, że jego rozwój będzie hamowany przez wzgląd na wewnętrzną konkurencję.
Obie dysponują darmowymi interfejsami, są one lepsze, niż w „większych” bazach Oracle i DB2.
Interfejs MySQL jest jednak lepszy – czytelniejszy i praktyczniejszy.
Nie bez znaczenia jest również fakt częstego stosowania tych baz w zastosowaniach internetowych – ich popularność wynika właśnie z tego. To dla nas, analityków, bardzo dobra wiadomość – jeżeli potrzebujemy bazę danych do konsolidacji plików rozproszonych (czyli pochodzących od wielu rozproszonych użytkowników – nie ma lepszej alternatywy niż darmowa baza na serwerze internetowym. Koszt utrzymania takiej bazy jest pomijalny.
Bazy MySQL i PostgreSQL polecamy jako HDA
Rys. MySQL
Rys. PostgreSQL
Nazwijmy ją „IAB”. To nie jest relacyjna baza danych – to zewnętrzny, internetowy, serwer plików. Zastosowanie Excela, szczególnie w jego bazodanowym charakterze (czyli zbieranie danych i konsolidacja plików rozproszonych, uzgadnianie wersji) ma wiele wspólnego z funkcjonalnością IAB
Na temat dostępu do danych OLAP więcej w Rozdziale „Hurtownia Danych”
|
Cecha \ SZBD |
Excel E2007 |
Access |
DBF |
Tekst |
SQLS |
Oracle DB2 |
PostgreSQL MySQL |
|
Czy darmowa |
T |
T |
T |
T |
T/N |
T/N |
T |
|
Czy plikowa |
T |
T |
T |
T |
N |
N |
N |
|
Tabela jest [P]-plikiem [T]-tabelą w BD |
T |
T |
P |
P |
T |
T |
T |
|
Czy Internet |
N |
N |
N |
N |
T |
T |
T |
|
Specyficzny SQL |
N |
N |
N |
N |
N |
T |
T |
|
Zastosowanie [T]-OLTP [L]-lokalna BD [O]-preferowany wyłącznie odczyt [H]-HDA |
O,L |
L,H |
T,L |
O |
T,L,H |
T |
T,L,H |
|
Prędkość O/Z (1-3) |
2 |
3 |
2 |
2 |
1 |
1- |
1- |
|
Zabezpieczenie (1-3) |
2 |
2 |
1 |
1 |
3 |
3 |
3 |
|
Ogólna ocena (lokalna 1-3) |
2 |
3 |
2 |
1 |
3 |
1 |
2 |
|
Ogólna ocena (Internet 1-3) |
1 |
2 |
1 |
1 |
2 |
2 |
3 |
Wybrane, dalsze rozdziały – wkrótce.