Uwaga. Niniejszy tekst jest chroniony prawem autorskim. Można go czytać do woli, uczyć się i swoją wiedzę wykorzystywać do dowolnych celów, w tym do pracy ;).
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.
Graficzny edytor zapytań SQL na przykładzie MS Query
Raportowanie - efektywne i tanie
Wojciech Gardziński, Krzysztof Rumiński © 2010
Spis treści:
Środowisko informatyczne, w którym można i warto stosować MS Query
Przykład 2. „Ranking kontrahentów według wielkości sprzedaży”
Agregowanie i grupowanie danych
Podgląd zapytania w języku SQL
Przykład 3. Zaawansowana kwerenda – „Szeroka tabela”
Dane bezpośrednio do tabeli przestawnej
Dalsze przykłady – krok po kroku
Przykład: Kontrahenci vs Klienci (rekordy unikatowe)
Przykład: Kontrahenci tylko z Warszawy (o kryteriach jeszcze raz)
Przykład: Kontrahenci tylko z Warszawy, posiadający telefon (wiele kryteriów)
Przykład: „Ranking literek” (własne formuły w SQL)
Przykład: „Kalendarz płatności” (własne formuły obliczeniowe na różnych typach pól)
Przykład: Ranking miast według wielkości sprzedaży (relacja)
Przykład: Ranking regionów według wielkości sprzedaży (sprzężenie wielu tabel)
Przykład: Kryterium na agregacie
Przykład: Przyporządkowywanie danych do przedziałów
Przykład: MS Query jako pomocnik Excela i jego formuł
Przykład: MS Query jako pomocnik tabeli przestawnej
Przykład: MS Query jako pomocnik kreatora kostek OLAP
Przykład: Kwerenda parametryczna
Przykład: Faktury niezapłacone (inne typy relacji)
Przykład: Własny słownik (wstęp do tematu ‘Hurtownia danych’)
Przykład: Zapis danych do zewnętrznej tabeli
Przykład: Zmiana bazy lub formatu danych
O Query pisaliśmy już wielokrotnie. Niniejsze opracowanie stanowi „pełny konspekt”, stąd wiele informacji tutaj podanych może się wydać zbyt szczegółowymi, a opisana sytuacja może nigdy nie zaistnieć u Czytelnika. Przekaz również może wydać się nieco chaotyczny – raz pokazujemy, jak coś zrobić szybko, po to, by zaraz potem rozdrabniać się w szczegółach i w efekcie otrzymać… to samo. Bo rzeczywiście, łatwo tu otrzymać „szybki efekt”, trudno go jednak wdrożyć produkcyjnie. Osiągnijmy więc szybki efekt, pochwalmy się nim, zachęćmy do współpracy, zachwyćmy i… przystąpmy do trudnej realizacji naszego celu.
Środowisko informatyczne, w którym można i warto stosować MS Query
Warunkiem absolutnie koniecznym jest posiadanie programu MS Excel w wersji, co najmniej, ‘97’ (To nie jest pomyłka! To narzędzie ma już kilkanaście lat! A standard języka SQL został opublikowany w 1992 r.)
Posiadacze wcześniejszych wersji również go posiadają, ale pod inną nazwą: ‘Q+E’ i mniejszą funkcjonalnością.
Każda firma, każdy analityk dysponuje jakimiś danymi, przetwarza jakieś dane na raporty.
Jakie to są dane (specyfikacja)?:
- Tabele systemu transakcyjnego, udostępnione na zewnątrz,
np. pliki .DBF
To źle. To błąd informatyków – tak nie powinno być – to niebezpieczne dla bazy danych. Pomimo tego, że wygodnie pracuje się analitykom, powinni oni przejść na model, opisany w punkcie poniżej (pilne!) – nawet poprzez najprostszą replikację (kopiowanie) plików. - Jedna lub wiele tabel (widoków) systemu ERP, celowo(!)
i świadomie(!) udostępniona przez administratora systemu na zewnątrz
To zdecydowanie najlepszy model współpracy systemu ERP i Excela w zakresie wymiany danych. Dostęp realizuje się wtedy bezpośrednio do bazy danych przez odpowiedni sterownik ODBC (przykład 3.), ale baza transakcyjna pozostaje efektywna i bezpieczna. - Eksporty raportów lub danych do plików .CSV lub .TXT, .PRN,
inne pliki tekstowe
To, często, jedyna możliwa, proteza sytuacji, opisanej powyżej. Trzeba się przyzwyczaić lub próbować automatyzować (Są programy, które sterują systemami ERP, automatyzując eksporty danych). Pliki tekstowe są dostępne przez ODBC.
W wielu przypadkach jednak, pliki tekstowe są „hierarchiczne”, czyli dane o jednej operacji gospodarczej są np. w kilku wierszach – trzeba wtedy użyć specjalnego programu odkodowującego, czyli przywracającego strukturę „równej” tabeli. - Eksporty „do Excela” (przez dostawców systemów
analitycznych lub ERP nazywane „integracją z Excelem”)
To, uwaga, tylko mutacja poprzedniego punktu. Fakt, że dane system wyrzuca do Excela (zwykle zresztą jest to plik .CSV, tylko otwierany Excelem), w niczym nie przybliża nas do efektywnej pracy z danymi – i tak musimy je w Excelu filtrować, wzbogacać, przetwarzać, łączyć z innymi danymi.
W takim przypadku traktujemy ów eksport (czyli powstały i zapisany na dysku, nawet bez jego otwierania, plik Excela)… jako zwykłe źródło danych ODBC (przykład 1.) - Tzw. ‘Hurtownia danych’, czyli baza danych, grupująca i
przetwarzająca dane z systemu(ów) ERP w oddzielnej bazie, do której dostęp
jest identyczny, jak w punktach powyższych.
Współpraca z hurtownią danych wygląda identycznie, jak z bazą danych systemu ERP (bez punktu pierwszego tej listy). - Systemy własnych plików – np. system budżetowy w setkach
plików Excela, rozsianych po dysku w folderach o strukturze, odnoszącej
się do struktury organizacyjnej firmy.
Tu również warto stosować opisywane technologie, ale raczej nie przez MS Query. Tej tematyce poświęcono osobny rozdział.
Kiedy można? Kiedy się umie – raczej nie ma innych ograniczeń, poza zakazami szefów i działów IT, ale można zawsze tak zorganizować system przetwarzania informacji (patrz pkt 2. powyżej), żeby IT było zadowolone (dbają o bezpieczeństwo danych – mają o co!), a analitycy dysponowali danymi – to jednak zadanie, najtrudniejsze do wykonania w tym procesie.
Kiedy warto? Zawsze.
Przykład 1. Szybki efekt
Naszym celem jest najprostsze pobranie wartości z zewnętrznej bazy danych, tu: pliku Accessa. Kroki, które wykonamy, warto kilkakrotnie przećwiczyć, aby stały się dla nas naturalne – umiejętności te przydadzą nam się podczas definiowania skomplikowanych kwerend.
Start – uruchomienie.
MS Query uruchamia się z menu Excela:
W Excelu 2003 i wcześniejszych: Dane / Pobierz dane zewnętrzne / Nowa kwerenda bazy danych
W Excelu 2007 i 2010: Wstążka Dane / Pobierz Dane Zewnętrzne / Z Innych Źródeł / Używając Microsoft Query (jedno kliknięcie więcej, sekwencja: [Alt][D][D][N])
Wybór źródła danych
Uwaga! W tym oknie BARDZO WAŻNE jest odznaczenie pola Używaj kreatora kwerend – jest to narzędzie nieprzydatne. Pole tej opcji powinno być zawsze puste.
Wybór bazy danych
W przypadku programu MS Access bazą danych jest plik .MDB – dlatego mówi się, że Access to „plikowa baza danych”.
Wskazujemy plik bazy danych na dysku. Ciekawostka: Proszę zwrócić uwagę na archaiczny już, z dzisiejszego punktu widzenia, sposób wskazywania dysków i katalogów (MS Query jest starsze niż termin „folder” w Windows)
Wybór tabeli danych
W bazie danych Accessa tabele to obiekty wewnętrzne. Ta lista może zawierać również dużą ilość innych tabel, jak MSysAccessObjects i innych. Oznacza to, że wyświetlamy również tzw. „tabele systemowe” – można zdecydować o ich wyświetlaniu lub nie w Opcjach… (przycisk).
Tabela pojawia się w kwerendzie. Tabele w kwerendach się dodaje – można dodać ich wiele, ale o tym później. Na razie bazujemy na jednej tabeli – gdy omyłkowo, klikniemy drugi raz Dodaj, pojawi się komunikat, że ta tabela jest już w kwerendzie – nie należy jej dodawać powtórnie.
Zamykamy okno dodawania tabel (Używając Zamknij).
Wybór pól z tabeli danych
Wyboru pól z tabeli lub widoku danych możemy dokonać na kilka sposobów:
- Klikamy dwukrotnie gwiazdkę – wybiera wszystkie pola danej tabeli.
- Przeciągamy gwiazdkę na pole kwerendy
- Wybieramy poszczególne pola po kolei, albo klikając dwukrotnie, albo przeciągając
- Wybieramy dane pole z listy rozwijalnej, której możemy użyć w nagłówkach pól kwerendy
Widok kwerendy, zawierającej wszystkie dane (czyli wszystkie pola i wszystkie rekordy) z tabeli faktura.
Gdybyśmy teraz zwrócili dane do Excela (jak? – będzie za chwilę), otrzymalibyśmy tabelę w Excelu – taką samą, jak w pliku źródłowym. Ale nawet wykonanie takiej, pozornie bezsensownej, czynności, czyli importu danych z Excela do Excela przez Query, ma zalety, w porównaniu z otwarciem pliku normalną opcją Otwórz plik, szczególnie widoczne przy pracy grupowej na danym skoroszycie:
- Nie blokuje pliku-źródła do odczytu, ani też zapisu przez innych użytkowników
- Umożliwia odświeżanie ad hoc
- Pobiera tylko wybrany arkusz z pliku źródłowego
Proste kryterium
Dodamy teraz proste kryterium, aby pokazać fundamentalną zaletę tej metody – nie wszystkie dane musimy „ciągnąć do Excela”, żeby je przefiltrować – możemy je wyczyścić z danych zbędnych jeszcze PRZED (!) importem danych. To koronny przykład tzw. „architektury klient-serwer” – dalej napiszemy o tym więcej.
Klikamy na wartości, którą chcemy wyfiltrować i naciskamy ikonę filtra (lejek) – najprostszego filtra ‘wartość pola równa wartości zaznaczonej’
Efekt zastosowania kryterium – filtra danych
Gdyby zastosowanie filtra nie spowodowało zmniejszenia ilości rekordów, należy sprawdzić, czy włączona jest opcja autokwerendy (automatycznej kwerendy) – ikona „wykrzyknik ze strzałkami” (druga od prawej) – działa podobnie, jak przeliczanie automatyczne w Excelu – odświeża kwerendę po każdej zmianie jakiegokolwiek jej ustawienia. Autokwerendy nie należy stosować zawsze, ale, przy naszych tu, pierwszych krokach, jest ona jak najbardziej pożądana i pomocna.
Zwrot danych do Excela.
Gdy uznajemy, że kwerenda jest gotowa, wysyłamy (zwracamy) dane do Excela za pomocą bardzo charakterystycznej ikonki „otwarte drzwi”.
Miejsce docelowe danych
Określamy, gdzie mają zostać umieszczone…
Na razie, na obecnym etapie, dane umieszczamy w tabeli na arkuszu.
Umieszczenie danych w arkuszu ma pewną zaletę: Gdy są to nasze pierwsze kroki z ODBC, MS Query, dostępem do danych – chcemy mieć narzędzie do sprawdzania-kontroli naszych, niepewnych poczynań, a, ponieważ najlepiej znamy i czujemy się w Excelu, jest to najlepsze narzędzie do zapewnienia nam pewności poprawności naszych działań (filtry, funkcje, tabela przestawna, wykresy, itp.). Przy pierwszych krokach – zawsze do tabeli Excela.
Dane umieszczane są w Excelu.
Jeszcze raz zwracamy uwagę na fakt przefiltrowania rekordów PRZED ich wysłaniem do Excela.
Zapamiętajmy: Możemy pracować z dowolnie wielkimi bazami (widokami) danych, byle tylko WYNIK naszej kwerendy mieścił się w arkuszu Excela.
To ważna zaleta.
Gotowe!
Odświeżanie danych
Uzyskana w ten sposób kwerenda jest odświeżalna. Wystarczy ‘stanąć’ na obszarze danych (czyli na obiekcie kwerendy – kwerenda jest obiektem w arkuszu Excela) i wybrać Odśwież albo z paska narzędzi, albo pod prawoklikiem.
To kolejna fundamentalna zaleta:
Zapamiętajmy: Nie trzeba za każdym razem importować całości danych, by je filtrować w Excelu
(obojętnie, czy ręcznie, czy też za pomocą jakiegoś makra).
Przykład 2. „Ranking kontrahentów według wielkości sprzedaży”
Teraz przyjrzyjmy się wszystkim powyższym krokom jeszcze raz, powoli, na innym, nieco bardziej skomplikowanym przykładzie.
(Pewnych ekranów pośrednich, o których wiadomo, jakie będą i co należy kliknąć – już nie pokazujemy.)
Uruchamiamy MS Query, ale wybieramy teraz inne źródło danych – pliki Excela.
Może się to wydać dziwne – przecież łatwiej plik Excela po prostu otworzyć pod Excelem.
Mamy w tym jednak swój cel – prosimy o cierpliwość.
Excelowe źródło danych
Źródło danych ‘Pliki programu Excel’ „widzi” już na dysku tylko pliki Excela. Aby udostępnić sobie możliwość pobierania danych z plików ‘.xlsx’, ‘.xlsm’ należy doinstalować sterownik ‘Microsoft ACE’ – tu: widać, że jest on zainstalowany.
Źródło danych Pliki programu Excel jest również „plikowe”, tzn. żeby pobrać dane, musimy odnaleźć plik, w którym się znajdują.
W Excelowym źródle danych ważne jest również poznanie i zrozumienie pewnej zależności i wynikającej z tego terminologii:
Domyślnym ustawieniem w oknie Dodaj tabele jest ukrycie (nie pokazywanie) tabeli systemowych – w przypadku excelowego źródła danych może to powodować pewne zaskoczenie użytkownika – uruchamiamy Query, wybieramy źródło excelowe, pokazujemy plik, a tam… nie ma nic.
Na poniższym widoku są jednak jakieś tabele – to nazwane zakresy w pliku Excela. Po prostu, w arkuszu faktura wszystkie dane mają nazwę faktura. W plikach użytkownika nie będzie takiej sytuacji – ta lista będzie pusta!
Gdy zaznaczymy w Opcjach… opcję wyświetlania tabele systemowe, na liście pojawiają się dodatkowe obiekty: arkusze, oznaczone nazwą ze znakiem ‘$’ (dolar) na końcu. W terminologii ODBC tak właśnie oznacza się całe ARKUSZE Excela. W naszym przypadku, nazwany obszar Faktura w arkuszu Faktura to dokładnie to samo, co arkusz Faktura sam w sobie (oznaczony jako Faktura$) – w plikach Czytelników zapewne nie będzie obszarów nazwanych – będą więc wyłącznie arkusze z ‘$’ na końcu nazwy.
Wybór określonych pól
Teraz już nie wszystko nas interesuje, tzn. nie wszystkie pola tabeli faktura.
Teraz wybieramy tylko pola nas interesujące – tu: najpierw pole nazwa,
potem pole wart_net – tak, aby w kwerendzie pojawiły się tylko żądane kolumny.
Zapamiętajmy: Nie musimy pobierać danych ze wszystkich pól (kolumn) tabeli (widoku) – możemy pobrać tylko wybrane kolumny.
Dobrze jest umieszczać kolumny (pola) we właściwej kolejności i zwracać na to uwagę, gdyż kwerenda ma nam zwrócić dane do Excela – a tam zechcemy, być może, stworzyć wykres na tych danych – dobrze jest posiadać nazwy po lewej, a wartości po prawej stronie obszaru danych.
Więcej o kolejności kolumn w kwerendzie i jej zmianie – dalej w tekście.
Agregowanie i grupowanie danych
Aby dokonać agregacji danych (tu: sumowania), wystarczy kliknąć odpowiednią ikonę na pasku narzędzi
Widok przed zagregowaniem wartości
Efekt sumowania danych na polu wart_net z jednoczesnym pogrupowaniem danych według unikalnych wartości pola nazwa – wszystko odbyło się za pomocą jednego kliknięcia myszką:
Sortowanie
Sortowanie nie odbywa się jednak już na poziomie danych, które były (są nadal) w tabeli źródłowej – dane zostały już pogrupowane. Sortowanie malejąco narzucamy na wartościach sum dla każdego kontrahenta.
Widok przed posortowaniem wartości
Efekt sortowania rekordów według malejących sum częściowych:
Podgląd zapytania w języku SQL
Mała dygresja: Tak, to my napisaliśmy! (Dostępne po naciśnięciu ikonki ‘SQL’.)
Przypomnijmy: MS Query to graficzny edytor zapytań SQL.
Po wysłaniu danych do Excela jednak stwierdzamy, że efekt jest iście piorunujący!
Dane są pogrupowane, posumowane i posortowane po sumach – w locie!
Gdybyśmy zechcieli wykonać taki raport w Excelu, moglibyśmy zrobić to następującymi sposobami:
- Sporządzenie unikalnej list klientów / użycie funkcji
SUMA.JEŻELI / sortowanie sum w arkuszu
Wady: Stworzenie unikalnej listy kontrahentów w Excelu jest możliwe, ale trudne. Poza tym, za każdym razem trzeba wykonywać wszystkie operacje od nowa - Użycie opcji Sumy częściowe i sortowanie w arkuszu
Wady: Opcja Sumy częściowe jest jedną z najgorszych opcji Excela. Psuje plik źródłowy, dodaje zbędne rekordy, zmienia formaty, wymaga kopii danych, w żadnym wypadku nie jest odświeżalna. - Użycie tabeli przestawnej
Wada: Zmiana ilości wierszy powoduje konieczność ręcznej redefinicji zakresu tabeli. Poza tym, nowe dane powodują konieczność albo fizycznego przeklejania danych, albo budowy tabeli od początku.
A tu, w ODBC, prosto, szybko i odświeżalnie!
Szybki wykres
Naciśnięcie jeden raz jednego klawisza [F11] tworzy nam z miejsca gotowy wykres.
Gdyby policzyć fizyczne kliknięcia myszką – efekt ten jest do uzyskania w 17-20 kliknięć (zależy od wersji Excela i ustawionych opcji) – jest więc natychmiastowy. Co najważniejsze jednak, posiada wszystkie zalety efektywnej aplikacji raportowej.
Zapamiętajmy: Kwerenda MS Query to:
- Aktualne, odświeżalne w każdym momencie dane
- Raport, odseparowany od danych źródłowych
- Raport, pobierający tylko tyle danych, ile trzeba
Przykład 3. Zaawansowana kwerenda – „Szeroka tabela”
Stworzymy teraz widok biznesowy – tzw. „szeroką tabelę danych”
Analiza danych, jak wiadomo, jest wielokryterialna.
Serwerowa baza danych
W tym przykładzie, oprócz zadania ‘merytorycznego”, czyli złożenia wielu tabel do jednego widoku, pokazujemy również, że technologia ODBC pozwala na współpracę z danymi w tzw. serwerowych bazach danych. Cóż to takiego? Przy obecnym stanie technologii informatycznych, zdecydowana większość systemów transakcyjnych nie przechowuje danych w plikach, a przynajmniej nie w plikach, dostępnych przez użytkownika w systemie plików. Dane znajdują się na serwerze bazodanowym – specjalnym programie zarządzania bazą danych (RDBMS – Relational DataBase Management System), który zapewnia integralność oraz bezpieczeństwo danym, zarówno na poziomie technicznym (awarie, usunięcie plików), jak i dostępowym (system uprawnień dostępu do danych, niekiedy bardzo skomplikowany). Najczęściej spotykane, serwerowe bazy danych to MS SQL Server, Oracle Database, MySQL, DB2. Wszystkie one są dostępne za pomocą technologii ODBC.
Systemy takie posiadają administratorów (tzw. ‘DBA’) który udziela uprawnień w dostępie do danych. Najczęściej jest to największa przeszkoda w dostępie do danych, bo technicznych problemów raczej nie ma.
Wybieramy po prostu inne źródło danych – tu: MS SQL Server Express (SQLSE)
Wiele tabel w kwerendzie
Dane, w sensie logicznym, są dokładnie takie same, więc nasze działania podlegają identycznej procedurze: wybór bazy, wybór tabeli, dodanie kolejnych tabel.
Uwaga: na tym etapie NIE WOLNO wybierać żadnych pól do kwerendy!
Wybranie dowolnego pola spowoduje powstanie iloczynu kartezjańskiego tabel, czyli potężnie zwielokrotnioną liczbę rekordów. Dowiedz się więcej o iloczynie kartezjańskim tabel w relacyjnej bazie danych.
Relacje
Relacja to sprzężenie między dwoma (lub więcej) tabelami. W Excelu, protezą narzędzia odtwarzania relacji („dociągania” danych z innych tabel) jest funkcja WYSZUKAJ.PIONOWO – najpopularniejsza chyba funkcja Excela.
Tutaj – relacja na 5-ciu tabelach.
Po połączeniu tabel sprzężeniami (relacjami) możemy już kliknąć (dwukrotnie), po kolei, we wszystkie gwiazdki, we wszystkich tabelach, czyli wybrać do kwerendy wszystkie pola ze wszystkich tabel.
Efekt: Szeroka (bo zawierająca wszystkie pola ze wszystkich tabel) tabela wynikowa – tzw. widok danych. Ta tabela nie istnieje fizycznie w systemie bazodanowym, ale można do niej kierować zapytania dokładnie tymi samymi metodami, a nawet ją zapisać w pliku zewnętrznym (skomplikowane, o tym dalej)
Dane bezpośrednio do tabeli przestawnej
Tu pewna nowość – pobierane dane nie kierujemy do tabeli w arkuszu.
Tabela przestawna
Gotowa tabela przestawna wraz z wykresem przestawnym
Ominęliśmy etap arkusza! Pobraliśmy dane przez ODBC (bez ograniczeń wielkości bazy) i przekazaliśmy dane do obiektu wielowymiarowego, jakim jest PivotCache tabeli przestawnej (potocznie: tabela przestawna). Bez pośrednictwa arkusza, czyli bez jego ograniczeń, np. na ilość wierszy.
Zapamiętajmy:
W Excelu można analizować dane, których rozmiar nawet znacznie przekracza możliwości edycyjne Excela.
Dalsze przykłady – krok po kroku
Pierwsze trzy, powyższe przykłady stanowiły pewien przegląd możliwości kwerend. A właściwie, kroplę w morzu tych możliwości. Teraz poznajmy kolejne funkcjonalności na kolejnych przykładach. Aby uniknąć powtórzeń tych samych ekranów, na widokach będziemy pokazywać wyłącznie kluczowe momenty definicji kwerend.
Można wykorzystywać dowolne źródło danych, choć, prawdopodobnie, najmniej problemów, sprawi plik Accessa (jak w przykładzie 1.)
Przykład: Kontrahenci vs Klienci (rekordy unikatowe)
Chcąc uzyskać informację biznesową, trzeba, w wielu przypadkach, wybierać pomiędzy różnymi źródłami danych. Jednym z lepszych przykładów jest sporządzenie listy kontrahentów i listy klientów – często to taka sama lista, ale wyniku musimy być pewni.
Lista kontrahentów to zwykle, po prostu, pole nazwa z tabeli odbiorca (to już umiemy)
Lista klientów jednak to ci odbiorcy, którzy otrzymują od nas faktury – ich listę lepiej (pewniej) jest więc uzyskać z tabeli faktura. Ale tu lista odbiorców nie jest unikalna – odbiorcy się powtarzają – jeden odbiorca mógł wiele razy kupić i otrzymać fakturę.
Jak zapewnić unikalność listy wynikowej? W MS Query nie ma nic prostszego – w menu Widok / Właściwości kwerendy… .
Przykład: Kontrahenci tylko z Warszawy (o kryteriach jeszcze raz)
Podobny przykład zastosowania kryterium był już w przykładzie 1.
Zaznaczamy wartość, która interesuje nas jako kryterium, klikamy ikonę filtra i natychmiast otrzymujemy efekt. (widok przed dodaniem kryterium)
(widok przed dodaniem kryterium)
Dokładnie ten sam efekt można jednak otrzymać również w inny sposób – w tym przypadku akurat jest bardziej skomplikowany, ale, ogólnie, jest to sposób uniwersalny.
Specjalnie kursor umieszczono w losowym miejscu – całość procesu definicji nowego kryterium odbywa się z menu: Kryteria / Dodaj Kryteria… .
Mamy tu listy rozwijalne zarówno dotyczące pola, ale i OPERATORA. Tu użyliśmy operatora „jest równe”, ale jest tu wiele różnych operatorów, dotyczących wyboru danych. Wreszcie, wykorzystując operator ‘jest równe’ można skorzystać z dostępnej, pełnej listy dostępnych wartości, itp. – jest tu dużo możliwości.
(widok przed dodaniem kryterium)
Przykład: Kontrahenci tylko z Warszawy, posiadający telefon (wiele kryteriów)
Zadanie do wykonania: Dostarczyć szefowi listę telefonów kontrahentów z Warszawy.
Nic prostszego – dodajemy kolejne kryterium: menu: Kryteria / Dodaj Kryteria…
Kryterium ma dodatkowo ograniczać powyższe (czyli listę kontrahentów z Warszawy) – używamy więc operatora logicznego „i” (AND). Operator na polu TEL musi jednak być innego rodzaju, niż ‘jest równe’ – różni kontrahenci mają różne numery telefonów. Odpada również filtrowanie tych numerów operatorem ‘zaczyna się od…’ z wartością ‘22’ (numer kierunkowy do Warszawy) – obecnie nie jest to jednoznaczne z fizyczną lokalizacją w Warszawie; kontrahent też może dysponować wyłącznie telefonem komórkowym, itp.
Lista dostępnych operatorów jest jednak bogata i zawiera również operator ‘nie jest puste’ – to najlepszy wybór w tej sytuacji.
(widok przed dodaniem kryterium)
Ostateczny widok kwerendy:
Ciekawostka: proszę zwrócić uwagę na tekst kryterium (tylko w polskiej wersji Excela, tu niewidoczny): „(TEL) Is Nie Null” – śmieszny wynik tłumaczenia komendy SQL na język polski – Zdanie SQL w rzeczywistości jest jednak poprawne (Tel IS NOT NULL), bo… działa.
Na powyższym ekranie komentarza wymaga zawartość pól kwerendy:
Gdy szef zamawia listę kontrahentów i ich telefonów, to… oczekuje tylko tego. Czyli nie oczekuje wartości pozostałych pól tej tabeli. Usunięcie pól ulica, czy uwagi wydaje się oczywiste, ale pola miasto już nie. Bo na polu miasto jest filtr! Ale szef nie chce widzieć miasta – wszak widziałby tylko powtarzające się wartości ‘WARSZAWA’.
Otóż, w kwerendach, wybór do kwerendy pola, na którym założono filtr, nie jest konieczny. Warto na to zwrócić uwagę, gdyż, gdybyśmy chcieli wykonać taką operację w Excelu, musielibyśmy wpisać do arkusza wartości co najmniej trzech pól: nazwa, miasto i tel, po czym założyć Autofiltr na polu miasto i kolumnę z tym polem fizycznie ukryć – dużo pracy.
A tu, w ODBC, jest to niesłychanie proste.
Zapamiętajmy:
Wybór do kwerendy pola, na którym założono filtr, nie jest konieczny.
Przykład: „Ranking literek” (własne formuły w SQL)
Zadanie: Ranking literek według wielkości sprzedaży, czyli która litera, będąca początkową literą nazwy firmy, ma związek z największym wolumenem sprzedaży?
W naszej tabeli danych nie ma pole, które przechowywałoby pierwsze litery nazw kontrahentów. Ale w Query możemy sami je stworzyć!
W tym celu umieszczamy kursor na pustym nagłówku pustej kolumny na prawo od każdej kwerendy i piszemy formułę. W SQL, ale stosując separatory z narodowej wersji systemu – tu: autor musiał zastosować ‘;’ (średnik), chociaż separatorem w SQL jest ‘,’ (przecinek).
Gdy wpiszemy formułę i naciśniemy [Enter] kolumna natychmiast wypełnia się obliczonymi danymi – a nam przecież jeszcze daleko do eksportu danych do Excela.
Zapamiętajmy:
W MS Query (czyli de facto w języku SQL) możemy tworzyć praktycznie dowolne formuły obliczeniowe.
(widok przed obliczeniem wartości kolumny)
Polu, stworzonemu przez nas, musimy teraz nadać własną nazwę.
Pola obliczeniowe, nie nazwane przez nas, czyli bez narzuconej nazwy, pojawiają się w wyniku kwerendy jako „Expr1001” i kolejne: „Expr1002”, itd. Nadanie własnej nazwy jest w tym wypadku koniecznością. Klikamy dwa razy na nagłówek pola, wyświetlając okno ‘Edytuj kolumnę’ i nadajemy polu własną nazwę nagłówka.
Jak już wiemy, nie wszystkie pola w kwerendzie są wartościowe – ograniczamy więc ich ilość tylko do tych, na których nam zależy – tu: usuwamy kolumnę nazwa (menu: Edycja / Usuń kolumnę lub, po prostu, [Delete]). Nazwy kontrahentów nie są nam już potrzebne, poza tym psułyby wynik kwerendy niepotrzebnie rozdzielając wartość dla litery ‘A’ na kontrahentów ‘Anex’ i ‘Arex’. Poniżej widać właśnie taki stan kwerendy – już po usunięciu kolumny, a jeszcze przed zagregowaniem wierszy.
Posiadając już wybrane kolumny, wystarczy dane numeryczne zagregować (posumować funkcją SUMA) grupując po literach oraz wyniki posortować po polu numerycznym malejąco (jak w przykładzie 2.)
W przypadku tej kwerendy zdarza się również (niekiedy przypadkowo) sytuacja, w której, po przekazaniu danych do arkusza Excela, kolumna ‘Literka’ jest po prawej stronie danych numerycznych – powoduje to nieczytelność kwerendy oraz np. niemożność wykonania wykresu (patrz: przykład 1.)
W tej sytuacji należy podjąć następujące kroki:
- Powrócić do kwerendy (edytować kwerendę) i upewnić się, że kolejność pól w kwerendzie jest właściwa (nazwy po lewej, wartości numeryczne po prawej)
- Zwrócić dane do Excela (Uwaga: Po tej operacji nie widać jeszcze efektu)
- Pod prawoklikiem odszukać (obok opcji Edytuj kwerendę) opcję Właściwości kwerendy i w oknie właściwości kwerendy odznaczyć(!) pole wyboru: Zachowaj (…)/(…)/układ kolumn.
- Sama zmiana tej opcji nie przyniesie rezultatu – trzeba jeszcze dane odświeżyć (edycja kwerendy nie jest już konieczna)
Efekt końcowy:
Zaprezentowanych w naszym raporcie wartości próżno by szukać w danych źródłowych – tam ich po prostu nie ma. Aplikacja transakcyjna rządzi się innymi prawami – inaczej grupuje, nie przechowuje sum pośrednich (agregatów), itp. A my, ponieważ ich potrzebujemy, możemy je sobie łatwo, szybko i, co najważniejsze, odświeżalnie, odtworzyć. PRZED przesłaniem danych do Excela – Excel jest odciążony – nie musi liczyć wartości setek funkcji.
Przykład: „Kalendarz płatności” (własne formuły obliczeniowe na różnych typach pól)
„Kalendarz płatności” (zwany też czasami „Preliminarzem płatności”) to raport-plan, prezentujący planowany spływ gotówki z faktur w poszczególnych przyszłych okresach czasowych.
Ale – mamy problemy: w tabeli faktura mamy wartości netto i VAT, ale osobno – nie mamy wartości brutto, a takich wpłat oczekujemy. Nie mamy też informacji o latach, miesiącach, ani tym bardziej o np. kwartałach – mamy tylko daty z ziarnistością do dnia.
Ale mamy też Query i możliwość tworzenia w im formuł obliczeniowych i to dowolnych – na dowolnych typach pól!
Ograniczamy liczbę pól do niezbędnego minimum.
Ograniczamy (filtrujemy) rekordy tylko do tych faktur, które mają termin płatności. Faktury z niezaewidencjonowanym terminem to prawdopodobnie faktury gotówkowe.
Wpisujemy formuły obliczeniowe
Tu zastosowano następujące:
BRUTTO=wart_net+VAT
ROK=FORMAT(termin,’yyyy’) albo ROK=YEAR(termin)
KWARTAŁ= FORMAT(termin,’q’)
MIESIĄC=FORMAT(termin,’mm’) albo MIESIAC=MONTH(termin)
Otrzymujemy gotowy raport – zdecydowano, że wykorzystamy obliczenia funkcją FORMAT (funkcja ta dotyczy wyłącznie baz danych MS Access i MS SQL Server). Pamiętamy jeszcze o wymuszeniu sumowania rekordów – to na wypadek, gdyby zdarzyła się sytuacja, że wiele płatności przypadnie na jeden okres – edycja pola kwerendy i ustawienie funkcji agregującej na ‘SUMA’.
Tu już absolutnie żadna wartość nie pochodzi bezpośrednio z tabeli źródłowej – wszystkie zostały poważnie przetworzone na potrzeby naszego raportu.
Przykład: Ranking miast według wielkości sprzedaży (relacja)
Przed przystąpieniem do ćwiczeń, polecamy zapoznać się z pojęciem ‘Relacyjnej bazy danych’.
Nie zawsze wszystkie, potrzebne nam dane, są dostępne w jednej tabeli danych – aby tworzyć raporty, musimy tworzyć tzw. widoki danych z wielu tabel – w Excelu wykorzystuje się do tego celu funkcję WYSZUKAJ.PIONOWO.
Dodajemy dwie tabele do nowej kwerendy: faktura (tu mamy wartościowe dane o sprzedaży w polu wart_net) oraz odbiorca (tu dysponujemy polem miasto).
Pamiętamy o ograniczeniach, związanych z efektem „iloczynu kartezjańskiego” tabel (patrz: przykład 3.)
Tworzymy, a właściwie odtwarzamy, relację bazy danych. Dokonuje się tego poprzez proste przeciągnięcie myszą pola nazwa z tabeli faktura na pole nazwa w tabeli odbiorca.
Tak powstałe sprzężenie tabel traktujemy już dalej jako jedną tabelę danych. I, choć pole miasto występuje w innej tabeli, niż wart_net, ustawiamy te pola w kwerendzie obok siebie i tworzymy „Ranking miast według wielkości sprzedaży” dokładnie tak samo, jak tworzyliśmy „Ranking kontrahentów według wielkości sprzedaży” w przykładzie 2.
Na widoku, oprócz gotowego „Rankingu”, wyświetlono również tekst zapytania SQL – jest już bardzo skomplikowane. Proszę zwrócić uwagę na tzw. „nazwy kwalifikowane” – w przypadku użycia wielu tabel, nazwa pola jest zawsze poprzedzana nazwą tabeli, z której pole pochodzi i kropką.
Konieczność stosowania nazw kwalifikowanych [nazwa tabeli].[nazwa pola] dotyczy również tworzenia kryteriów i innych działań na kwerendzie. Tu przedstawiono przykładowe filtrowanie ‘po detalistach’.
Kwerenda, po zwróceniu do Excela, nie wymaga już żadnych funkcji. Wybiera, łączy, grupuje, sumuje i wybiera odpowiednie sumowania danych – „w tle” naszego Excela.
Przykład: Ranking regionów według wielkości sprzedaży (sprzężenie wielu tabel)
Czy można połączyć w zapytaniu więcej niż dwie tabele? Jak najbardziej!
Proszę zwrócić uwagę na konieczność użycia w tym zapytaniu tabeli odbiorca, pomimo, że nie chcemy z niej pobierać żadnych danych. Relacja może (tu musi) przechodzić przez tabelę pośrednią.
Tu, mała dygresja na temat wygenerowanego zapytania SQL – znając SQL, można je znacznie uprościć. Nie ma jednak takiej potrzeby – obie wersje działają tak samo.
Oryginalny tekst:
SELECT REGION.REGION, Sum(FAKTURA.WART_NET) AS 'Sum of WART_NET'
FROM `C:\SQL_w_Excelu\Dane\Sprzedaz`.FAKTURA FAKTURA, `C:\SQL_w_Excelu\Dane\Sprzedaz`.ODBIORCA ODBIORCA, `C:\SQL_w_Excelu\Dane\Sprzedaz`.REGION REGION
WHERE ODBIORCA.NAZWA = FAKTURA.NAZWA AND REGION.MIASTO = ODBIORCA.MIASTO
GROUP BY REGION.REGION
Skrót, dokonany przez osobę, znającą SQL:
SELECT r.region, SUM(f.wart_net) AS [Suma z WART_NET]
FROM faktura f, odbiorca o, region r
WHERE o.nazwa = f.nazwa AND r.miasto = o.miasto
GROUP BY r.region
Przykład: Kryterium na agregacie
Jak już stwierdziliśmy, nie zawsze interesuje nas informacja w formie, w jakiej została stworzona w naszym systemie transakcyjnym. Kto jest dla nas bardziej wartościowy – klient, który kupił raz za 50, czy drugi, który kupił 10 razy po 10? (W naszym przykładzie ‘BRONEX’ kupił 2 razy po 41 i 42,6.)
Opcja kryteriów na tabeli źródłowej nie sprawdza się – filtr „większe niż” działa na wartościach tabeli.
Query tworzy widoki danych – wirtualne tabele, dostępne do dalszych na nich działań, np. sortowania po wartości sum sprzedaży (przykład 2.). Równie dobrze można filtrować owe grupy:
Widok: Filtr na danych surowych (3 rekordy)
Widok: Filtr na danych zagregowanych (4 rekordy)
Przykład ten jest omówiony również w rozdziale „SQL”.
Przykład: Przyporządkowywanie danych do przedziałów
Analiza płatności wymaga przyporządkowania danych do przedziałów przeterminowania, najlepiej określonych przez nas. Nic prostszego.
Zamiast sztywnej daty #2005-04-01# zastosujemy, oczywiście, funkcję NOW.
Excel, na wyjściu, otrzymuje już dane poatrybutowane tzw. grupą przeterminowania.
Przykład: MS Query jako pomocnik Excela i jego formuł
Analitycy przyzwyczajeni są, że wszystkie obliczenia robią w Excelu, ale formuły kosztują. Kosztują czas, ryzyko błędu oraz fakt, że może nam zabraknąć wierszy w arkuszu.
Dlaczego więc nie pozwolić, aby tysiące operacji wykonane zostało, ZANIM dane się pojawią w Excelu?
Tu przykład operacji tekstowych – podziału kolumny pelna_naz1 na 2 kolumny (wartości przed i po spacji) oraz złączenia 2 kolumn w jedną – kod i miasto stają się jednym tekstem (proszę zwrócić uwagę na formułę, zabezpieczającą przed brakiem wartości w polu kod)
Operacja ta jest dla nas niezauważalna (działa w tle, podczas odświeżania kwerendy), nie obciąża Excela oraz jest powtarzalna, nie wymaga żadnych makr, formuł, kopiowania formuł, gdy dojdą nowe dane, itp.
To przykład – możliwe są prawie dowolne formuły.
Ciekawostka: W standardzie SQL nie ma funkcji MID(). Ale Query wykorzystuje tzw. silnik bazodanowy MS JET, czytaj „Accessa”, a w Accessie, tj. w VBA Accessa, jest funkcja MID() – więc możemy jej używać.
Przykład: MS Query jako pomocnik tabeli przestawnej
Modelowy przykład sporządzenia kwerendy dla celów tabeli przestawnej opisaliśmy już w przykładzie 3.
Przyjrzyjmy się jednak procesowi tworzenia takiego raportu jeszcze raz – krok po kroku.
Proces tworzenia raportu wygląda zwykle tak:
Myślimy – co by tu wybrać i posumować, żeby nam wyszła sprzedaż po regionach? Albo jeszcze lepiej: w rozbiciu na regiony i grupy towarowe?
Szukamy – po przeglądnięciu wielu tabel stwierdzamy, że posiadamy w tabelach pole wart_zl w tabeli pozycja, które zawiera informację o sprzedaży na pozycji faktury – tj. informację (tu pole nr_fakt), którą można odnieść na fakturę, dalej na odbiorcę i jeszcze dalej na region – oraz informację na polu ind_tow, którą z kolei można odnieść „w innym kierunku analitycznym” (informatycy mówią: „w innym wymiarze analizy”) na tabelę towar.
Ale my to wszystko wiemy i nam jest łatwo – a jak tego nie wiemy?
Trzeba rozpocząć od początku, tj. od zrobienia zwykłej kwerendy na tabeli pozycja.
Powyższe dane to dane przykładowe – nasz, przerabiany tu przykład to zaledwie 35 pozycji faktur. Pomimo tego, że danych jest tak mało, i tak jest ich dużo na ekranie i, żeby się czegoś z nich dowiedzieć, należy je przeanalizować tabelą przestawną. Więc – zwracamy dane do Excela.
Tu: pierwszy dylemat: czy do tabeli w arkuszu, czy do tabeli przestawnej?
Polecamy – do arkusza, nawet, gdy się w tym arkuszu nie zmieszczą. Lepiej dobrze poznać strukturę danych fragmentarycznych, niż godzinami błądzić po całości danych, nie mając prostego narzędzia sprawdzającego – w tym przypadku autofiltra. Oczywiście, gdy struktura danych będzie już dla nas znana, przyjdzie czas na rozwiązanie docelowe. A do wstępnego rozpoznania tabel i tak warto zastosować dodatkowy filtr.
Zwracamy więc dane do arkusza Excela i tworzymy na nich tabelę przestawną.
I tu – zaskoczenie! – tabela przestawna SAMA NAS INFORMUJE, że dane pochodzą z kwerendy i że będzie je można łatwo przez to odświeżać.
W Excelu 2003 i wcześniejszych też tak można, ale wymaga to naszej uwagi i działania.
Mianowicie, gdy tworzymy tabelę przestawną (nie musimy zaznaczać obszaru kwerendy – kreator tabeli sam go rozpoznaje, podobnie jak w E2007), w pokazanym oknie, Excel proponuje obszar komórek, np. A1:E36. Uwaga! Należy nacisnąć przycisk [F3] (wklej nazwę z listy nazw arkusza) – pokazuje nam się lista z nazwą kwerendy, należy ją wybrać i potwierdzić. Efekt jest taki sam, choć okno inaczej wygląda – tabela przestawna jako źródło danych będzie od tej pory używać obszaru całej kwerendy (bo identyfikowanego jej nazwą) – nie trzeba już pamiętać o rozszerzaniu zakresu tabeli przestawnej!
Proszę zwrócić uwagę na ubogą, bo pochodzącą tylko z jednej tabeli pozycja, listę pól danych
Ale – nie szkodzi! Przecież dane pochodzą z kwerendy, a kwerendę można modyfikować do woli! Wracamy do arkusza z kwerendą i pod prawoklikiem wybieramy Edytuj kwerendę.
Mało tabel? Zawsze możemy dodać kolejną. Brakuje nam informacji o kliencie, ale mamy dostęp do tabeli faktura, która po numerze faktury potrafi nam ją dostarczyć – dodajemy więc tabelę faktura.
Tworzymy sprzężnie (odtwarzamy istniejącą relację) między tymi tabelami, wybieramy (gwiazdką) wszystkie pola z tabeli faktura.
Po zwróceniu danych do Excela, wracamy do tabeli przestawnej. Wystarczy ją odświeżyć, by na liście pól tabeli przestawnej pojawiły się nowe pola – pełna tabela faktura do dyspozycji.
Na widoku pokazano efekt już po dodaniu jednego z pól tabeli faktura do tabeli przestawnej, a mianowicie zgrupowano faktury po klientach.
I tak dalej…
Brakuje nam nazw towarów i grup towarowych – dodajemy tabelę towar (relacja: towar.ind_tow=pozycja.ind_tow).
Brakuje miast i grup klientów – dodajemy tabelę odbiorca (relacja: faktura.nazwa=odbiorca.nazwa).
Brakuje regionalizacji – dodajemy tabelę region (relacja: odbiorca.miasto=region.miasto).
Krok po kroku budujemy pełną, poważną, tabelę przestawną, zawierającą dane z wielu tabel.
Efekt – to przykład nr 3.
A Query nam w tym bardzo pomaga.
Przykład: MS Query jako pomocnik kreatora kostek OLAP
Wbrew pozorom, tj. pozornej błahości tematu, jest to najlepszy edytor struktury tabeli (widoku), którą zamierzamy wykorzystać jako tabelę (widok) bazową dla Kreatora OLAP. Kto robił kostki w poważniejszych narzędziach, np. w sztandarowym MS SQL Server Analysis Services, ten wie, jak trudno PRZYGOTOWAĆ widok pod kostkę – nazwy pól praktycznie trzeba znać na pamięć. Owszem SQLS AS posiada wiele innych opcji, itp. ale trzeba pamiętać, że, aby się nauczyć, należy zaczynać od rzeczy prostych, gdzie, gdy coś się zrobi, to widać natychmiastowy efekt, można poprawiać, itp.
A jak to się robi w naszym przykładzie?
- Stwórz Szeroką tabelę jak w Przykładzie 3.,
- Dopisz do niej formuły własnych pól obliczeniowych, jak w przykładzie Kalendarz płatności, dołącz własne słowniki (np. własną semantykę pojęć biznesowych), jak w przykładzie Własny słownik.
- Wykonaj kostkę OLAP na podstawie sporządzonego widoku (Osobny rozdział o OLAP).
MS Query zapewnia niespotykaną wygodę pierwszych kroków w nauce OLAP.
Przykład: Kwerenda parametryczna
Kwerenda parametryczna – kwerenda, umożliwiająca użytkownikowi wpisanie wartości zadanego parametru i wykonanie kwerendy ten parametr uwzględniającej.
Tworzymy kwerendę z jednym kryterium. Ważne jest, żeby operatorem nie było „jest równe” (chociaż, oczywiście, może być, ale wtedy musimy wpisywać zawsze wartości równe danym, a to, w przypadku długich tekstów, bywa kłopotliwe)
Zamiast wartości kryterium, tu ‘A%’, wpisujemy tekst tzw. zachęty w nawiasach kwadratowych, czyli [Wzorzec nazwy klienta]. Tekst ten zostanie wyświetlony przy zapytaniu o parametr.
Przy próbie odświeżenia kwerendy albo jej zwrotu do Excela pojawia się okno dialogowe parametru kwerendy. Widok po zastosowaniu parametru.
Gdy dane są już w Excelu, możemy pójść jeszcze dalej w automatyzacji kwerendy.
Pod prawoklikiem, zaraz obok opcji Edytuj kwerendę, znajdziemy opcję Parametry.
Ustawiamy tak jak na rysunku, wskazując dowolną komórkę arkusza, gdzie chcemy parametryzować kwerendę (tu: komórka ‘i1’).
Możemy tę komórkę odpowiednio oznaczyć na arkuszu, żeby wszyscy wiedzieli, że, jak w żółte pole wpiszemy wzorzec nazwy klienta, NATYCHMIAST (sic!) nasza kwerenda odświeży się i zaprezentuje wyniki, zgodne z parametrem (tu: nazwy, kończące się na ‘X’).
Nie trzeba nawet pamiętać o odświeżaniu kwerendy – zrobi to sama przy każdej zmianie wartości parametru.
Parametryzowania niestety nie można stosować do tekstu źródła danych – dlatego, pomimo swojej dużej „widowiskowości” – jest stosowane niezwykle rzadko.
Przykład: Faktury niezapłacone (inne typy relacji)
Jak znaleźć w tabeli to, czego w niej nie ma?
(W domyśle: a my chcemy, żeby było i nam tego, z jakiś powodów, brakuje)
Firma wystawia faktury, załóżmy 19 sztuk. Kilka z nich zostało zapłacone gotówką, pozostałe są płacone albo nie. Kto wie, jaka jest praktyka zapłat, ten wie, jakie są problemy.
Tylko nieliczne faktury są płacone, tak jak powinny, reszta jest płacona albo częściami, albo jedna zapłata dotyczy wielu faktur, albo istnieje w ogóle jakieś inne rozwiązanie.
My chcemy znaleźć te faktury, do których w ogóle nie ma zapłat. Problem nieco upraszczamy – jeżeli pojawiła się chociaż jedna minimalna zapłata częściowa, znaczy, że zapłata jest. Ze względu na komplikację tematu, profesjonalne wykonanie tego zadania wymaga równie profesjonalnej wiedzy. Na razie wykonajmy „wstęp do tematu”.
Mamy problem: Gdy wyświetlimy tylko wszystkie faktury (tabela faktura)– mamy faktury, gdy wyświetlimy tylko zapłaty (tabela zaplata)– mamy DOKONANE zapłaty, gdy do kwerendy damy dwie tabele (faktura, zaplata) i je ze sobą połączymy po numerze faktury – mamy tylko ZAPŁACONE faktury. A my chcemy akurat te NIEZAPŁACONE.
Jak znaleźć to, czego nie ma?
Sporządźmy najpierw kwerendę z dwóch tabel, połączonych po numerze faktury i ograniczmy zestaw rekordów tylko do tych, które posiadają termin płatności:
Jak widać, brakuje tu kilku faktur – tych, które zostały zapłacone gotówką (czyli nie miały daty w polu termin), ale nie tylko.
Relacja to sprzężenie tabel – co się jednak dzieje, gdy rekord o danym nr_fakt istnieje w jednej tabeli, a nie istnieje w drugiej? W przypadku relacji 1:1 (tzw. INNER JOIN, jeden-do-jednego) rekordy ani jednej, ani drugiej tabeli nie są w ogóle pokazane, bo przecież faktura.nr_fakt (=’04/19’) <> zaplata.nr_fakt (=NULL). Ale można zmienić typ relacji!
Klikamy dwukrotnie na łącznik (łamana linia) między tabelami – pojawia się okno ustawień relacji. Wybieramy opcję tzw. relacji lewostronnej (LEFT JOIN, jeden-do-wielu), która określa, że relacja obowiązuje dla wszystkich rekordów z tabeli lewej (tu: faktura) oraz tych rekordów z tabeli prawej (tu: zaplata), dla których warunek równości wartości jest spełniony.
Na widoku pokazano wygląd kwerendy i okna już po zmianie typu relacji (naciśnięciu Dodaj)
Ciekawostka: W języku SQL istnieje jeszcze wiele innych typów relacji – są już niedostępne przez Query, ale też bardzo rzadko używane.
Aby wykonać nasze zadanie, brakuje jeszcze kilku kroków. Jeżeli szukamy faktur niezapłaconych, musimy ograniczyć zestaw rekordów tylko do tych, które nie mają wartości w tabeli zaplata, czyli np. nie mają numeru faktury. Dodajemy kryterium zaplata.nr_fakt IS NULL i usuwamy wszystkie pola tabeli zaplata z naszej kwerendy – nie jesteśmy w stanie wyświetlić zapłat, których nie było. Pozostawiamy tylko pola tabeli faktura, bo, w rzeczy samej, szukamy FAKTUR niezapłaconych.
Efektem jest jeden rekord – faktura nr ‘04/19’. Zwracamy dane do tabeli Excela. Gotowe.
Dokładnie w ten sam sposób szuka się – przykłady:
- tzw. dziur słownikowych, czyli wartości, które pojawiły się w danych przyrostowych (transakcjach), a które nie mają odpowiednika w tabeli słownikowej
- zmian w danych, np. które konta przybyły w planie kont w ostatnim okresie
- błędów. Jeżeli słownik nie jest automatycznie uaktualniany przez jakąś transakcję (nasze, excelowe słowniki nigdy nie są) tylko ręcznie, musimy mieć jakieś narzędzie kontroli spójności słownika – prosta kwerenda idealnie się do tego nadaje.
Przykład: Własny słownik (wstęp do tematu ‘Hurtownia danych’)
Idea własnego słownika we własnym systemie informacyjnym, opartym o wykorzystanie SQL w Excelu, została wyjaśniona w tej książce przy innej okazji. Tamże wyjaśniono również zmiany, jakie musza zostać wykonane w danych, aby system taki działał właściwie.
W niniejszym rozdziale skupiamy się na wykorzystaniu MS Query do tego celu.
Poniższy widok przedstawia model wykorzystania słownika zewnętrznego w kwerendzie.
Dysponujemy systemem bazodanowym, wykorzystującym jakąś bazę danych. Celowo nie precyzujemy tu ani systemu, ani bazy, gdyż jest to praktycznie obojętne (oczywiście albo jeśli dostępne przez ODBC, albo gdy eksporty z tych tabel dostępne przez ODBC). Tu: plik Accessa z naszym, znanym już przykładowym zestawem danych.
W tym systemie, nazwijmy go transakcyjnym, mamy dwie tabele: faktura i odbiorca. Co prawda mamy też pozostałe, ale tu nas one nie interesują. No, może, na pierwszy rzut oka, interesującą tabelą będzie tabela region, ale… ją też wyeliminujemy.
Nasz system sprzedaży „żyje”. Codziennie coś sprzedajemy, baza sprzedaży rośnie – dochodzą kolejne faktury (tabela faktura), co jakiś czas pojawiają się nowi odbiorcy (tabela odbiorca) i te dane są na pewno aktualne, bo system transakcyjny aktualizuje je na bieżąco.
Ale podział regionalny kraju, który dostawca tego systemu zapisał nam jako słownik – właśnie tabelę region – dawno stał się nieaktualny, np. dlatego że kiedyś dzieliliśmy regiony ze względu na regiony geograficzne, a teraz podział związany jest z osobami przedstawicieli handlowych lub jeszcze czymś innym, według życzenia. To życzenie zostało wyrażone ODRĘCZNYM rysunkiem prezesa, który na mapie Polski zakreślił swoje własne regiony.
Nic nam, analitykom, do tego, czy i jaki to ma sens – my mamy wykonać raport, uwzględniający nowy podział kraju.
Jakie problemy napotkamy w realizacji naszego zadania?
Na pewno trudność sprawią nam:
- Brak połączenia tabel faktura i odbiorca w systemie transakcyjnym. Mamy dostęp do obu tabel, ale do obu oddzielnie. Często rolę tabel wewnętrznych systemu transakcyjnego, pełnią excelowe lub tekstowe eksporty do plików – idea jednak dostępu i przetwarzania danych jest identyczna.
- Inny format bazodanowy tabeli słownikowej. Gdy dostaliśmy ów rysunek prezesa, natychmiast usiedliśmy i sporządziliśmy tabelę – słownik w… wiadomo, gdzie – w Excelu. Więc słownik jest plikiem Excela, a transakcyjna baza danych – nie (tu Access)
- Fizyczne miejsce przechowywania danych – baza transakcyjna na serwerze sieciowym, a nasz plik słownika (istotny, załóżmy, tylko dla nas) znajduje się na naszym komputerze. Plik ma nazwę SlownikRegionow.xls i zawiera arkusz region2.
Wszystkie powyższe problemy skutkują brakiem możliwości połączenia tabel relacją w jednej kwerendzie – o ile tabele systemu transakcyjnego można by połączyć w jednej kwerendzie, tak excelowa tabela słownikowa ma inny format – i jest to niemożliwe.
Ale – jest Query, Jest też i Excel, a Excel może przechować wiele arkuszy w jednym pliku, a jeden plik jest bazą danych już jednego, excelowego, formatu. A kwerend w jednym pliku Excela może być dowolna ilość.
Stwórzmy więc nowy plik Excela, mający trzy arkusze, bo mamy trzy tabele: faktura, odbiorca i region2.
- W pierwszym arkuszu – nazwijmy go ‘F’ – tworzymy kwerendę do bazy Accessa do tabeli faktura.
- W drugim arkuszu – nawijmy go ‘O’ – kwerendę do bazy Accessa do tabeli odbiorca
- W trzecim arkuszu – nazwijmy go ‘R’ – kwerendę do pliku Excela SlownikRegionow.xls do tabeli region2$ (pamiętamy, że arkusz jest dla ODBC tabelą z ‘$’ w nazwie tabeli – przykład 1.)
Powstał nowy plik Excela – zapiszmy go na dysku pod nazwą HurtowniaDanych.xls.
Tworzymy teraz jeszcze jeden plik Excela – już docelowy – plik z naszym raportem. W nim tworzymy od nowa kwerendę do… pliku Excela HurtowniaDanych.xls, w którym traktujemy trzy jego arkusze: tabele ‘F$’, ‘O$’ i ‘R$‘ jako trzy tabele systemu bazodanowego – łączymy je relacją od tabeli ‘R$’ przez ‘O$’ do ‘F$’, z tabeli F$ bierzemy pole wart_net, z tabeli ‘R$’ pole region i mamy raport o zupełnie nowej jakości – połączyliśmy własny słownik (na bazie odręcznego rysunku prezesa) z danymi żywego systemu bazodanowego.
Przykład ten ma ograniczenie – ilość wierszy arkusza Excela. Raczej jest więc to przykład bardziej modelowy, niż produkcyjny. Ale, jako prototyp, działa znakomicie – dzięki MS Query.
Przykład i wszystkie jego kolejne etapy przedstawiono na widoku powyżej.
Przykład: Zapis danych do zewnętrznej tabeli
Tu już wkraczamy w zakres wiedzy specjalistycznej SQL. MS Query – przypomnijmy – jest graficznym edytorem zapytań SQL. Ponieważ nie można graficznie przedstawić eksportu danych – nie ma też takiej opcji.
Ale, kto umie SQL albo przynajmniej zna jego podstawy – może więcej.
Aby zobaczyć, jak to działa, posłużmy się jeszcze innym, nie używanym do tej pory źródłem danych – plikami DBF. Dlaczego DBF? DBF to niewątpliwie baza plikowa – przechowuje dane w plikach o rozszerzeniu .DBF, ale jest również tzw. „bazą płaską”. „Baza płaska” to baza danych, w której jedna tabela to jeden plik. Tu, w samej bazie nie ma informacji o relacjach – tu są tylko dane.
Innym, dużo częściej obecnie używanym typem bazy płaskiej są… ?
Pliki tekstowe! Tak, to też baza danych dostępna przez ODBC!
Wracając do DBF-ów – jeżeli tabela to plik, to eksport tabeli do innej tabeli wymaga powstania nowego pliku na dysku.
Tworzymy kwerendę do bazy DBF (również dostępna w katalogu przykładów).
Uwaga. W najnowszych instalacjach Excela, na liście dostępnych źródeł danych, nie ma pozycji dotyczącej plików .DBF (zwykle o nazwie pliki Programu dBase lub dBase files).
Dodanie nowego źródła danych jest proste - wybieramy na liście nowe źródło danych, dalej nazywamy je jak chcemy, wybieramy sterownik Microsoft dBase Driver (Uwaga! Nie wolno pomylić z Microsoft Access dBase Driver!).
Do kwerendy wybieramy plik – tabelę faktura, wybieramy z niej wszystkie pola danych – widok, jak poniżej – wyświetlamy okno edycji zapytanie SQL.
Dalej, zaznaczamy całość zapytania (cały tekst w tym oknie), kopiujemy [Ctrl+C] i zamykamy to okno.
Następnie, z menu Plik programu MS Query, wybieramy opcję Wykonaj SQL.
Wyświetla się okno Wykonaj SQL. Wklejamy tekst zapytania SQL ze schowka [Ctrl+V].
Przed klauzulą FROM w zdaniu SQL wstawiamy interlinię (tu wstawiono 3 interlinie). Wpisujemy komendę wskazującą, dokąd mają zostać wybrane dane – wpisujemy „INTO kopia „ (Uwaga – ze spacją na końcu) – kopia to żądana przez nas nazwa nowej tabeli danych.
Gdy wykonamy instrukcję SQL, pojawi nam się komunikat „Instrukcja SQL wykonana poprawnie.”. Czyli – nowa tabela danych się stworzyła, tylko – pytanie – gdzie?
Ponieważ nie podaliśmy ścieżki własnej, tworzy się ona w katalogu domyślnym Excela, czyli prawdopodobnie tam, gdzie wskazuje okno „Otwórz plik” (zwykle: Moje dokumenty). Plik nazywa się KOPIA.DBF. Gdy podamy własną ścieżkę w postaci:
INTO kopia IN `C:\SQL_w_Excelu` [dbase IV;]
(Uwaga – apostrofy lewe, tj. nad tabulatorem) plik powstanie we wskazanym folderze.
Stworzone tak komendy SQL możemy zapisać (przycisk Zapisz w oknie)
Przykład: Zmiana bazy lub formatu danych
(Bazujemy na poprzednim przykładzie – cd.)
Potrafimy już zapisać dane w bazie płaskiej do innego pliku – tu: użyliśmy bazy .DBF – powstał nam nowy plik .DBF. W ten sposób można np. ograniczać wielkość plików.
A co się stanie, gdy jako źródło danych użyjemy bazy plikowej, ale nie płaskiej, np. Excela albo Accessa?
W Excelu, instrukcja „INTO kopia” doda w pliku źródłowym nowy arkusz kopia.
W Accessie (i serwerowych bazach danych) powstanie nowa tabela o tej nazwie.
Ale, jak wiadomo, analitycy pracują w Excelu i chcą mieć wszelkie dane w tym formacie.
Gdy chcemy zmienić jednocześnie format pliku (np. na format excelowy), wystarczy użyć
INTO kopia IN `C:\SQL_w_Excelu\MojNowySkoroszytExcela` [Excel 5.0;]
(Więcej informacji o sposobie zapisu różnych typów baz danych w składni ODBC znajduje się na witrynie www.connectionstrings.com ).
Podobnie postąpimy, gdy zechcemy zapisać dane z Excela do Excela, ale do innego pliku, niż źródło danych.
Zapamiętajmy:
MS Query potrafi odczytywać i zapisywać pliki danych, a nawet zmieniać w locie format danych!
Kreator kostek OLAP
W Excelu, w wersjach 2000, XP, 2003 dostępny jest kreator analiz wielowymiarowych OLAP, którego rezultatem działania są pliki .CUB – tzw. plikowe kostki OLAP.
Jest on dostępny właśnie przez MS Query. Menu MS Query: Plik / Utwórz moduł OLAP.
W niniejszej książce OLAPOWI w Excelu poświęcono osobny rozdział.
Czego MS Query nie potrafi?
Jak już wiemy, MS Query to narzędzie nad wyraz potężne i nad wyraz niedoceniane.
Nie jest jednak wolne od problemów technicznych. MS Query jest programem starym z punktu widzenia najnowszej technologii informatycznej. Wiąże się z tym kilka niedogodności – pewne rzeczy (oczywiście jeśli chodzi o edycję zapytań SQL i raporty) robi się tu trudno, a wiele jest wręcz niemożliwych do wykonania albo do praktycznego zastosowania.
MS Query ma problemy z działaniem, gdy:
- W nazwie folderu bazy plikowej znajduje się kropka, przecinek lub inny znak nietypowy (zastrzeżony w DOS). MS Query ma 15 lat i nie uwzględnia po prostu, normalnych już dzisiaj, wynalazków systemu plików. Próba dostępu do pliku w folderze …\Moje.Dane\... spowoduje błąd. Instalacja dodatkowego oprogramowania, np. nowszej wersji Excela, może ten błąd wyeliminować (ale nie musi).
- Uruchomi się kwerendę na innym komputerze, niż została
stworzona – to bardzo poważny błąd, który trzeba szczegółowo wyjaśnić:
Gdy stworzymy kwerendę na komputerze ‘A’, odwołującą się do danych na serwerze sieciowym w lokalizacji F:\Dane\, widocznej tak samo ze wszystkich komputerów w sieci (tzn. uprawnienia są takie same) i zapiszemy ją na dysku sieciowym, załóżmy w folderze F:\Raporty\ , otwierając ten plik na drugim komputerze ‘B’ nie będziemy mogli uruchomić kwerendy z powodu błędu innej lokalizacji dodatku MS Query na dyskach komputerów ‘A’ i ‘B’. Błąd jest nienaprawialny – nawet gdy wskażemy poprawną ścieżkę i uruchomimy jednak kwerendę na ‘B’, jej zapis na ‘B’ spowoduje kolejny tego typu błąd na komputerze ‘A’. (W przypadku identycznej (!) konfiguracji obu komputerów błąd może nie wystąpić.)
Wymiana danych w skoroszytach musi odbywać się w plikach pozbawionych kwerend.
Niemożliwym jest:
- Tworzenie kwerend, w których istnieje relacja jeden-do-wielu na więcej niż dwóch tabelach
- Parametryzowanie zapytania na poziomie źródła danych
- Powrót do graficznej edycji kwerendy, gdy raz kwerenda przeszła w tryb „nie-graficzny”. Co to jest? Gdy modyfikujemy SQL-a kwerendy ręcznie, w pewnym momencie Query informuje nas, że „nie może wyświetlić tej kwerendy graficznie”, tj. nie potrafi jej „narysować” – chociaż działać będzie. Znika wtedy widok tabel i kryteriów – pozostaje wyłącznie widok danych i możliwość ręcznej edycji zapytania SQL. Ale, gdy dojdziemy do tego etapu, to oznacza, że jesteśmy już zaawansowanymi jego użytkownikami. Autorzy tej książki życzą Czytelnikowi, żeby potrafił tworzyć i edytować kwerendy, niemożliwe do wyświetlenia graficznego.
- Dostęp do danych plikowych na dyskach sieciowych, gdy udział sieciowy nie jest zmapowany jako wolumin dyskowy, tj. nie można połączyć się z danymi w ścieżce \\serwer\..., ale, gdy zostanie zmapowany na np. „Q:\”, nie powinno być problemów.
Trudnym do zastosowania jest:
- Pionowe łączenie tabel (tzw. ‘UNION’)
UNION to łączenie tabel na zasadzie wstawienia wartości jednej tabeli o określonej strukturze do drugiej tabeli o takiej samej strukturze. Ponieważ nie można tego narysować, Query nie oferuje wsparcia. Ale, jeśli znasz SQLa, możesz tekst takiej komendy stworzyć w pliku .TXT, potem skopiować do okna Wykonaj SQL, uruchomić (sprawdzić działanie), a następnie zapisać do pliku – jest taka opcja w oknie Wykonaj SQL. Wtedy łatwo go (ten plik) ponownie otwierać i wykonywać. Ale, w tym przypadku, trzeba jasno stwierdzić, że są do tego celu lepsze narzędzia. - Łączenie tabel z różnych baz danych, a także różnych systemów bazodanowych. Ponieważ w terminologii bazodanowej dwa skoroszyty Excela to dwie bazy danych, jedynym sposobem na połączenie tabel z tych skoroszytów jest ręczna modyfikacja SQL lub stworzenie tzw. hurtowni danych.
- Automatyzacja zapisu – działa, gdy robimy to ręcznie, ale Query, gdy chcemy go używać produkcyjnie, nie pozwala na automatyzację.
Istnieją jednak sposoby ominięcia wszystkich, powyższych, ograniczeń. Zostały objaśnione w rozdziale „Hurtownia Danych”.
Co my właściwie zrobiliśmy?
- Odkryliśmy inny świat – jako analitycy, jesteśmy zamknięci
w arkuszu, jako miejscu naszej pracy. Pracujemy od lat, wykorzystując te
same funkcje (oczywiście, uczymy się nowych, ale wszystko według tej samej
zasady), gdy nam brakuje funkcjonalności, sięgamy po VBA.
A tu, mamy inny świat – istniejący od prawie 20 lat, świat, traktowany przez nas jako „świat informatyków” – niedostępny, nieznany, nieprzyjazny – a tu, wprost przeciwnie: DOSTĘPNY, PRZYJAZNY, BO ZNANY. Niewiele trzeba, żeby zacząć. - Uniezależniliśmy się – od pana administratora bazy danych, który nie chce nam zrobić raportu – sami go sobie zrobimy (!) – niech nam da tylko, wyeksportowaną do Excela albo do pliku tekstowego, tabelę danych.
- Zbliżyliśmy się – do problemów informatycznych. Już wiemy, dlaczego informatycy są tacy „zamknięci” – boją się. Boją się, że zepsujemy im to, za co oni odpowiadają. Gdy pokażemy, że wiemy, dokąd zmierzamy, rozmowa pójdzie łatwiej.
WYJDŹ ZA KRATY (siatkę arkusza Excela)!