Excel BI, czyli magii Excela ciąg dalszy.

Chyba każdy doskonale wie, jak ważny jest stały rozwój. Ciągłe doskonalenie umiejętności i pozyskiwanie nowych, zdobywanie kolejnych doświadczeń, realizacja coraz to ciekawszych projektów czy wreszcie rozwijanie portfolio oferowanych usług – zarówno większość pracowników, jak i firm zdaje sobie sprawę, że to nieodzowne elementy rzeczywistości. Również Microsoft stale udoskonala, rozwija i dopieszcza swoje produkty – o efektach ich pracy można dyskutować, jest jednak sporo narzędzi, które firmie z Redmond udało się wprowadzić z sukcesem.

Jednym z takich narzędzi jest niewątpliwie arkusz kalkulacyjny Excel, doceniany przez wielu księgowych, statystyków, logistów, matematyków i innych specjalistów. W najprostszym ujęciu: Excel to przedstawiciel programów, których zadaniem jest przedstawienie zbiorów danych w postaci zestawu kolumn i wierszy, pozwalających na ich swobodną ich obróbkę, analizę i prezentację. Podstawowymi narzędziami takich aplikacji są, oczywiście, różnorodne funkcje matematyczne, statystyczne, finansowe, bazodanowe czy logiczne, które pozwalają na automatyzację przetwarzania danych oraz ich wizualizację przy pomocy w pełni funkcjonalnych i zaawansowanych pulpitów menadżerskich.

Obecnie trudno sobie wyobrazić firmę funkcjonującą bez przynajmniej jednego komputera, ale dzieje się to od niedawna. Jeszcze w latach 70. ubiegłego wieku komputery nie były tak powszechne, de facto można było je spotkać jedynie w domach entuzjastów. Pełnoprawne maszyny obliczeniowe były natomiast domeną centrów obliczeniowych, w których wykonywano analizy finansowe, prognozy czy podsumowania budżetu, a cała operacja trwała niekiedy wiele tygodni i zarezerwowana była jedynie dla firm, które mogły sobie pozwolić na obciążenie budżetu rzędu nawet kilkudziesięciu tysięcy dolarów.

Problemy i ograniczenia, jakie niósł taki stan rzeczy, zainspirował wówczas młodego studenta Harvard Business School, Daniela Bricklina, do podjęcia próby stworzenia komputerowego arkusza kalkulacyjnego. On i jego kolega z Massachusetts Institute of Technology, Bob Frankston, rok po podjęciu inicjatywy wypuścili pierwszy program obsługujący arkusze kalkulacyjne – VisiCalc, a tym samym zapoczątkowali burzliwy okres rozwoju oprogramowania tego typu. Nietrudno się domyślić, że pierwsze arkusze kalkulacyjne nie były tak skuteczne, czytelne i funkcjonalne, jak obecne (niewielka liczba dostępnych wierszy i kolumn do przechowywania danych, brak możliwości rysowania wykresów, brak systemu pomocy czy konieczność wyjścia do systemu operacyjnego w celu wydrukowania pliku). Jednak współczesne arkusze kalkulacyjne również borykają się z pewnymi ograniczeniami. Chociaż Excel wypuszczony przez firmę Billa Gatesa w 1985 roku do tej pory cieszy się sporą popularnością, a z każdą następną wersją jego możliwości są poszerzane, to jedną z jego głównych bolączek jest ograniczona możliwość przechowywanych danych – obecnie dane można przechowywać w 1 048 576 wierszach i 16 384 kolumnach. Liczby te na pierwszy rzut oka wydają się ogromne, istnieją jednak sytuacje, w których nawet takie możliwości są niewystarczające.

Idąc po lepsze…

Rynek ewoluował, a użytkownicy aplikacji mają coraz większe wymagania. Kiedy Microsoft zorientował się, że świat analizy, przetwarzania i wizualizacji danych zaczyna znikać za horyzontem, rozpoczął prace nad narzędziami, które znacząco poszerzyłyby możliwości ich arkusza. W taki właśnie sposób gigant z Redmond opracował szereg darmowych dodatków, które uzupełniły Excela o brakujące i potrzebne elementy, a o których istnieniu część użytkowników do tej pory nie wie. Od czasu premiery tych dodatkowych narzędzi sam Microsoft zaczął nazywać swój produkt Excelem BI. Co ciekawe: Microsoft opracował narzędzie spójne z Excelem, a noszące miano Power BI. W uogólnieniu: Power BI to zbiorcza nazwa dla szeregu aplikacji klasy Business Intelligence, w dużej mierze opartych na chmurze obliczeniowej.

Usługi i aplikacje BI Microsoftu pozwalają gromadzić, zarządzać, przetwarzać i analizować dane z różnych źródeł. Dlaczego dla korporacji Billa Gatesa był to tak ważny krok? Użytkownicy Excela narzekali na problemy przy importowaniu danych ze źródeł zewnętrznych (często import prostego pliku tekstowego wiązał się z monotonnymi naprawami danych, jak zmiana kropek na przecinki czy poprawne formatowanie dat). Dodatkowo czynności naprawcze wykonywane podczas importu trzeba było wykonywać za każdym razem, kiedy zaistniała konieczność aktualizacji tych danych (oczywiście VBA umożliwiało napisanie makr, które wykonywałyby takie czynności automatycznie, jednak wymagało to dość szerokiej wiedzy i posiadania sporych umiejętności), a sam proces aktualizacji trzeba było przeprowadzać ręcznie.

Wychodząc naprzeciw potrzebom: Power Query

Wychodząc naprzeciw niejako nowej rzeczywistości i, oczywiście, potrzebom użytkowników, Microsoft opracował dodatek o nazwie Power Query, należący do grupy ETL (Extract, Transform and Load), czyli narzędzi wspomagających proces pozyskiwania danych do baz danych, a stanowiących bardzo ważny element procesów Business Intelligence.

Power Query z definicji miał rozwiązać problemy z importem danych, które miał Excel – i to udało mu się świetnie. Dodatek pozwala na import danych z przeróżnych źródeł, zaczynając od najprostszych danych z tabeli lub z zakresu mieszczącego się bezpośrednio w arkuszu kalkulacyjnym, poprzez pliki tekstowe czy zewnętrzne bazy danych Oracle lub SQL Server, po dane z internetu lub skrzynki mailowej.

To jednak nie jedyna nowość wprowadzona przy okazji premiery Power Query. Wspomniałem wcześniej, że proces aktualizacji importowanych danych trzeba było wykonywać ręcznie – teraz aktualizacja odbywa się podobnie, jak w przypadku odświeżania wyświetlanej witryny internetowej. Dodatkowo Excel, przy wykorzystaniu Power Query, nie robi problemu podczas importu z pliku udostępnionego, na którym pracuje już inna osoba (w takiej sytuacji arkusz importuje dane z ostatniego zapisu pliku). Idąc dalej: firma z Redmond „zdjęła” ograniczenie przetwarzanych danych do nieco ponad jednego miliona wierszy: dzięki zastosowaniu dodatku można bez problemu zaimportować dane z pokaźnej bazy danych z nawet kilkoma milionami wierszy. Mało tego – Excel w takim wypadku pozwoli z nich również utworzyć tabelę przestawną, jednocześnie nie umieszczając danych fizycznie w arkuszu.

Podczas importu danych z zewnętrznych źródeł trzeba jednak liczyć się z innymi problemami: niepoprawnym formatem liczb czy dat albo z inną konfiguracją kolumn. Niekiedy może się też zdarzyć, że nie będzie potrzeby importu wszystkich danych, a jedynie jakiegoś konkretnego ich zakresu, dlatego Power Query w drugim kroku importu pozwala na dowolne przekształcenie importowanych danych, często bez konieczności znajomości nazwy używanej funkcji – interfejs graficzny pozwala na skorzystanie z funkcji jedynie przy użyciu myszy. Chociaż dla ekspertów pracy z Excelem może brzmieć jak fragment z książki sci-fi, to narzędzie Microsoftu jest na tyle rozbudowane, że pozwala osiągnąć zamierzony cel jedynie przy użyciu wbudowanych funkcji. Nie znaczy to jednak, że firma nie pomyślała o użytkownikach bardziej wymagających – jeśli jakiejś z funkcji brakuje, można ją bez problemu napisać w języku Power Query M (czyli funkcjonalnym języku z rozróżnianiem wielkości liter podobnym do F#), specjalnie do tego celu zaimplementowanym.

Po nawiązaniu połączenia z danymi źródłowymi i ich przekształceniu pozostaje jedynie ich wykorzystanie w raportach, wykresach czy pulpicie menedżerskim. Jak już wspomniałem, Microsoft umożliwił przetwarzanie danych z baz o wielkości nawet kilku milionów rekordów, pozostaje więc jedynie decyzja, czy importowane dane chcemy umieścić bezpośrednio w zeszycie arkusza kalkulacyjnego, czy też zestawić połączenie z nimi bez ich bezpośredniego kopiowania do Excela. Bez względu jednak na to, czy dane zostaną skopiowane do arkusza, czy zostanie jedynie zestawione połączenie ze źródłową bazą, Power Query umożliwia ich odświeżenie bez konieczności powtarzania wszystkich kroków – to, co zostało zrobione przy imporcie danych, zostanie automatycznie zastosowane podczas odświeżania, co oszczędza czas.

Power Pivot

Power Query to jednak znów nie jedyne narzędzie Microsoftu wypuszczone w formie dodatku do Excela, a nadające mu dumną nazwę Excel BI. Innym, ale równie ciekawym narzędziem jest Power Pivot, którego premiera odbyła się w 2010 roku. Narzędzie samo w sobie pozwala uczynić z importowanych danych swego rodzaju relacyjną bazę danych: poprzez ich import i przekształcanie danych pochodzących z różnych źródeł, a następnie powiązanie ich ze sobą relacjami właściwymi bazom danych, tworzy w ten sposób model danych. Z tak utworzonego modelu można, co jest normalne w Excelu, tworzyć tabele przestawne, które różnią się od standardowych tabel jedynie tym, że pozwalają na analizę danych z wielu źródeł jednocześnie.

Power Pivot jednak raczej nie sprawdzi się podczas zwykłej, codziennej pracy w celu uzupełniania danych w jednej tabeli. Ze względu na swoje cechy zdecydowanie bardziej przyda się analitykom, kontrolerom finansowym czy specjalistom Business Intelligence podczas cyklicznej analizy dużych zestawów danych z różnych źródeł (w tym również z dodatku Power Query, przy czym w tym wypadku zyskuje się dodatkowo możliwość przekształcenia danych lub ich filtrowania jeszcze przed przekazaniem do Power Pivot) czy budowania zaawansowanych pulpitów menadżerskich.

Power Pivot jest zdecydowanie szybszy, wykorzystuje bowiem silnik VertiPaq (xVelocity), umożliwiający kolumnowe składowanie danych i przetwarzanie ich w pamięci – operacja przetwarzania jest dużo wydajniejsza ze względu na brak konieczności pozyskiwania danych z dysku (dane znajdują się bowiem w pamięci RAM, co mocno upraszcza zasadę działania). Z wykorzystaniem silnika VertiPaq wiąże się także dużo wyższa kompresja danych (sięgająca nawet 90%) poprzez zmianę sposobu ich przechowywania. Dzięki temu przechowywany ma objętość 50MB, a nie 500MB, co najbardziej docenią osoby pracujące na plikach, w których liczba rekordów sięga maksimum Excela. Skoro już przy tym jesteśmy, to Power Pivot nie ma tego typu ograniczeń: umożliwia przechowywanie danych w dużo większej liczbie wierszy (szacunkowo jest to ok. 2 miliardów rekordów na jedną tabelę). Praca na danych jest również przyjemniejsza, jednak wymaga podstawowej znajomości języka angielskiego. Do swojego dodatku Microsoft zaimplementował funkcje w języku DAX (Data Analysis Expressions), których składnia jest podobna do składni funkcji znanych z Excela, z tą różnicą, że w Power Pivot nie są one tłumaczone na język polski (w praktyce znajomość języka angielskiego sprowadza się do wykorzystywania w dobieraniu nazw funkcji).

Power Map

Oba wymienione dodatki są bez wątpienia pomocne podczas pracy przy analizie danych, jednak gigant z Redmond poszedł krok dalej, udostępniając jeszcze jeden świetny dodatek do Excela, tym razem pomagający w prezentacji danych. Dodatek Power Map (lub 3D Map) został stworzony, jak nietrudno się domyślić, do prezentowania danych w ujęciu geolokalizacyjnym. Aby móc jednak skorzystać z możliwości tego narzędzia, należy wcześniej przygotować dane w formie tabeli (nie musi to być tabela strukturalna), z informacjami geolokalizacyjnymi (takimi, jak kraj, miasto, województwo czy współrzędne geograficzne). Dodatek w procesie geokodowania automatycznie zamieni te dane na współrzędne umieszczone na mapie całego globu, a rozmaite narzędzia w nim zaimplementowane pozwolą na dopracowanie ogólnego wyglądu prezentacji.

Dodatek nie pomoże w przetwarzaniu danych, stanowi jednak ciekawe narzędzie do ich analizy w ujęciu geograficznym. Z pomocą Power Map można tworzyć klipy wideo, które prezentują np. poziom sprzedaży danego produktu w ujęciu państwowym, wojewódzkim lub nawet z uwzględnieniem konkretnych placówek firmy, co na pewno zostanie docenione podczas prezentacji podsumowującej działalność przedsiębiorstwa.

Podsumowując, chociaż zaprezentowane narzędzia były wydawane jako dodatki do Microsoft Excel, nie znaczy to, że są one dostępne dla wszystkich. O ile Power Query jest częścią najnowszych wersji pakietu biurowego Microsoft, a w starszych wersjach można go całkowicie za darmo zainstalować z oficjalnej witryny firmy, o tyle Power Pivot ma już pewne ograniczenia. Użytkownicy wersji takich, jak Microsoft Office Professional (i Professional Plus) 2016, Professional Plus 2013 i samodzielnych aplikacji Microsoft Excel 2010, 2013 i 2016, także subskrybenci Microsoft 365 Pro Plus, E3 i E5 mogą się cieszyć tym narzędziem; użytkownicy subskrypcji Microsoft 365 w wersjach Education, University, Home, Personal, Small Business Premium i podobnych nie mają dostępu do tego dodatku, co nie powinno dziwić ze względu na sposób jego zastosowania i scenariusze, w których jest użyteczny.

Nieco inaczej sytuacja wygląda z dodatkiem Power Map – odnośnik do map trójwymiarowych powinien znajdować się na karcie „Wstawianie” programu Excel. Jeżeli w tej zakładce nie jest widoczna możliwość jego uruchomienia, należy wejść w opcje programu Excel, następnie w drzewku po lewej stronie w „Dodatki”, a następnie na samym dole, w polu „Zarządzaj:”, wskazać „Dodatki COM” i kliknąć „Przejdź”. Jedyne, co wówczas pozostanie, to zaznaczenie pozycji „Microsoft Power Map for Excel”, zatwierdzenie wyboru i radość z używania narzędzia do wizualizacji danych i dużego potencjału, jaki oferuje.