Artykuł AFIN.NET:

AFIN.NET – Power Calculation © AFIN 1995-2006-2011

Wojciech Gardziński

Przeliczaj dane wielokrotnie szybciej

dzięki usłudze (usługom) przeliczania AFIN.NET!

(robocza nazwa „Przeliczanie AFIN.NET”, „AFIN.NET.PowerCalculation”)

Summary in English:

This article explains the author’s idea (2006) of effective calculation in Excel. The worksheet functions are pre-calculated, queries are optimized, packed and sent to the data servers in very specific way and the returned data are distributed among the worksheet functions as values.

This is very complicated but works. Many and many times faster than the normal Excel calculation.
This functionality called “AFIN.NET.PowerCalculation” works in AFIN.NET only.

Niniejszy artykuł jest przedstawieniem pewnej idei. Niniejszym, idea ta staje się publiczna. Wymagam jednak, aby każdy, kto jej użyje, uwzględnił moje prawa autorskie.

Spis treści:

Wstęp

A po co to?

„Przeliczanie AFIN.NET” jest wynikiem długoletniego doświadczenia autora w implementacji systemów controllingowych w firmach.

W informatyce analitycznej stosuje się do dzisiaj następujące architektury dostępu do danych:

1) Gruby/cienki serwer – gruby klient (klasyczne użycie Excela)

2) Gruby serwer – cienki klient (webowy klient systemu BI)

3) Gruby serwer – bardzo gruby serwer analityczny – gruby klient (tzw. IMA)

4) Gruby serwer – bardzo cienki klient (wyłącznie przeglądarka) lub gruby klient (Excel, OO) jako medium wymiany danych (Google BI)

Żadna z powyższych architektur NIE SPEŁNIA, wszystkich naraz, podstawowych cech dobrej aplikacji analitycznej, czyli:

1) Łatwa, intuicyjna obsługa w dobrze znanym interfejsie użytkownika

2) Dostęp do danych na dowolnym poziomie procesu przetwarzania danych

3) Łatwe wzbogacanie danych o własne słowniki

4) Wykorzystanie plików baz danych przy mniejszych wolumenach danych na równych prawach z danymi serwerowymi

5) Zadawanie zapytań detalicznych do serwera

Przeliczanie AFIN.NET jest więc próbą zapewnienia użytkownikowi Excela funkcjonalności, polegającej na połączeniu pełnej prędkości obliczeniowej serwerów bazodanowych z elastycznością Excela, jako podstawowego środowiska pracy analityka finansowego.

Idea

Użytkownik Excela pracuje w arkuszu (kalkulacyjnym), zna jego funkcjonalności jako interfejsu – potrafi w nim zdefiniować każdy raport. Problemem jest dostęp do aktualnych danych serwerów bazodanowych. Można go jednak rozwiązać – Excel dysponuje funkcjonalnością dostępu, poprzez ODBC, do dowolnej bazy, obsługującej ten standard, czyli, praktycznie, wszystkich formatów/typów baz danych. Może tego dokonać na kilka sposobów:

1) Poprzez import danych do arkusza, przetworzenie ich za pomocą makr VBA oraz połączenie „linkami” z raportem – opcja powszechnie stosowana, ale, z powodu swojej awaryjności, jest wysoce ryzykowna i nie jest polecana przez specjalistów baz danych. Ale taka jest praktyka.
Takie rozwiązanie nazywać będziemy „zapytaniami powiązanymi”

2) Poprzez kwerendy.
Excel od wielu lat udostępnia narzędzia do poboru danych zewnętrznych, np. Microsoft Query, ale również połączenie OLE DB, czy też, w najnowszej wersji Excela 2010, opcji Power Pivot. Dane można zwracać albo do tabel zwykłych, tj. normalnego arkusza Excela, lub do tabel przestawnych, tj. obiektów wielowymiarowych (OLAP w RAM). Takie rozwiązanie oferuje bardzo dużą prędkość realizacji pojedynczego zapytania dla praktycznie dowolnej ilości pobieranych danych. Są to tzw. „zapytania hurtowe”

Nie zapewnia jednak parametrycznego wyboru danych, czyli możliwości zadania zapytania w komórce B1, na podstawie parametru w komórce A1, czyli tzw. „zapytania detalicznego”.
MS Query ma taką możliwość (tzw. kwerenda parametryczna), ale wtedy jego definicja jest bardzo skomplikowana i może być wykorzystywana jedynie w bardzo prostych kwerendach. Tak więc duża ilość pobieranych danych, w przypadku potrzeby sporządzenia raportu w Excelu, jest raczej wadą, niż zaletą – 100 zapytań detalicznych wymaga ręcznego (nie można tego zautomatyzować) sporządzenia 100 kwerend! Problem ten próbuje obchodzić się tabelami przestawnymi i funkcjami WEŹDANETABELI(), ale wtedy traci się elastyczność rozwiązania.

3) Poprzez programowe użycie obiektów ADO.
Technologia ADO umożliwia stworzenie makra lub funkcji Excela, która, po zdefiniowaniu jej argumentów, połączy się z zewnętrzną bazą danych (argument ConnectionString), wyśle do bazy definicję zapytania w języku SQL (SQL) – w wyniku działania obiektu ADO stworzy tzw. zestaw rekordów (dla naszych potrzeb raportowych – jednoelementowy) i zwróci, jako wartość, do arkusza.
To również tzw. „zapytanie detaliczne” – o wiele lepsze, niż kwerenda parametryczna – jest niezależne od uruchomienia, a wręcz, nawet, posiadania programu MS Query i może obsługiwać dowolną ilość parametrów.

Podejście takie jest uniwersalne – możliwe jest odpytanie dowolnej bazy danych o dowolną wartość na dowolnym poziomie agregacji – SUPER!. Ma tylko jedną, jedyną, wadę – jest niczym innym, jak taką „osobną kwerendą” z każdej komórki Excela, a każdy użytkownik Excela – wiadomo – gdy tylko uzyska jedną wartość i to prostą funkcją Excela, zaraz kopiuje ją do setek innych komórek i… ma problem..

Problem polega na tym, że to, co do tej pory było zaletą, czyli uniwersalność zapytania, w połączeniu z drugą, również powyżej opisaną, zaletą kwerend, czyli możliwością sięgnięcia do zewnętrznej bazy danych… staje się wadą! Każda funkcja – pamiętamy – jest ich 100 (to taka mała ilość, ale tu użyta w kontekście „stosunkowo dużo”) łączy się, autoryzuje, tworzy zestaw rekordów, zwraca wartość, zamyka połączenie itd. – każda funkcja osobno!

Jednostkowy czas odświeżenia kwerendy, załóżmy, 1/10 sekundy (od 1/10 sekundy w przypadku danych w Accessie na dysku lokalnym, do ponad 10 sekund dla kwerendy internetowej lub dostępu do serwera bazodanowego przez Internet z autoryzacją dostępu), jest wprost (!) mnożony przez ilość takich funkcji! A funkcji może być tysiące… tu zakładamy, że jest ich „tylko” 100. Daje to, w najlepszym układzie, konieczność poświęcenia 10 sekund na przeliczanie się arkusza danych. A analitycy chcą przeliczać swoje arkusze co chwilę.
Excel, dodatkowo, owo „przeliczanie co chwilę” niejako wymusza, oferując, jako standardowe, tzw. przeliczanie automatyczne, czyli zmiana zawartości jednej komórki, powoduje przeliczenie wszystkich formuł i funkcji we wszystkich otwartych skoroszytach. Przełączenie się na „przeliczanie ręczne” niewiele tu pomaga, gdyż, owszem, niweluje problem z niechcianym przeliczaniem, ale w żaden sposób nie powoduje skrócenia powyższego czasu.

Jak więc połączyć zalety uniwersalności zapytań, tworzonych przez analityków w Excelu, z prędkością serwera bazodanowego, OMIJAJĄC (rozkładając na wiele zapytań) ów stały koszt transportu danych (w tym koszt autoryzacji)?

W świecie realnym problem rozwiązano już dawno – wszak po to właśnie są ogromne parkingi przed hipermarketami.

Klient, chcąc zrobić zakupy, zanim w ogóle wyjdzie z domu, robi listę zakupów na karteczce. Tworząc ją, absolutnie nie przejmuje się ich kolejnością, a nawet, miesza na owej karteczce działy hipermarketu, w których będzie owych produktów szukał.

Robi tzw. listę zapytań detalicznych – logikę tego przeanalizujmy na podstawie zakupu dwóch produktów – mleka, ale koniecznie musi być w litrowym kartonie i być produktem firmy X (zapytania detaliczne są, z reguły, bardzo precyzyjne) oraz skarpetek – koniecznie zielonych, do kostek i frotte.

Wsiada potem do samochodu, jedzie do hipermarketu, parkuje, wchodzi (duży koszt dostępu, w niektórych marketach musi się nawet „zalogować” specjalną „kartą”), ale, gdy tam już jest, do specjalnego, ogromnego, kosza wkłada dużą ilość różnego rodzaju produktów z różnych działów, które, fizycznie, oddalone są od siebie o kilka półek. Gdyby te same zakupy chciał zrobić w sklepie osiedlowym, straciłby sumarycznie jednak o wiele więcej czasu. Dostęp jednostkowy byłby, co prawda, odrobinę mniej kosztowny (mniejszy parking, bliżej do lady), ale

a) oferta byłaby uboższa (mleko tylko firmy Y),

b) z powodu uboższej oferty musiałby odwiedzić wiele sklepów (i tak nie kupiłby mleka i skarpetek w jednym sklepie),

c) odwiedzanie wielu sklepów pociągałoby za sobą dodatkowe koszty, np. korki na ulicach

d) gdyby sklep wymagał autoryzacji, byłoby to dużym utrudnieniem (duża ilość haseł do zapamiętania, pełny portfel kart stałego klienta, itp.).

e) do kasy stałby w każdym sklepie – zawsze krócej, niż ów jeden raz w hipermarkecie, ale zawsze sumarycznie dłużej.

Tak więc duży, ale jednostkowy, koszt dostępu, jest w przypadku hipermarketu, niwelowany poprzez pojemność naszego kosza na zakupy oraz możliwości przewozowych naszego samochodu. Pomimo tego, że wchodzimy do dużego hipermarketu, dalej mamy możliwość robienia zakupów z NASZEJ kartki, a nawet wymyślać je ad-hoc – czyli składania zapytań detalicznych.

Opłaca się robić zakupy hurtowo, mogąc kupować tylko te produkty, które chcemy.

Czyli – tak właściwie – my, w hipermarketach, robimy hurtowo zakupy detaliczne.

Hipermarkety rosną, jak grzyby po deszczu.

Realizacja

Problem

Dostępne na rynku systemy analityczne nie potrafią tak działać. Albo próbują przenieść nam cały hipermarket pod dom (IMA), albo próbują wymusić na nas z góry zamówienie (SIWZ) określonej listy produktów, których używamy, czy też chcemy dopiero użyć (standardowe BI) albo potrafią dostarczać nam produkty „działami” (kwerendy, tabele przestawne), czyli dowożą wszystkie produkty mleczne (byśmy mogli wybrać owo jedno, potrzebne nam, mleko) i cały dział skarpet (MSBI, operujący tzw. „dataset”-ami).

Więc – naprawdę, nie jest to dziwne – my, analitycy nie chcemy tak działać! Z dwojga złego wolimy pozaprzyjaźniać się z miłymi i niemiłymi paniami ze sklepów osiedlowych i wytworzyć siatkę powiązań – naszych potrzeb – czyli excelowych łącz międzyskoroszytowych. To nic, że sklepy osiedlowe zmieniają właścicieli, ofertę, kadrę sprzedawczyń, a nawet się fizycznie przenoszą – nic to – i tak takie działanie jest wygodniejsze, niż powyżej opisane działanie „specjalistyczne”. Oba podejścia są ryzykowne i kosztowne, nad tym drugim jednak jakoś panujemy.

DLATEGO LUDZIE UŻYWAJĄ EXCELA.

Co potrafimy?

Ale my, jednak, chcemy mieć lepiej! Przyjrzyjmy się więc naszemu problemowi informatycznemu pod kątem dostosowania go do praktyki naszych zakupów.

Pojedyncza funkcja ADO generuje (sposób generowania skomplikowanego zapytania przez funkcję opisana jest w innym artykule) dwa główne parametry dostępu do bazy danych i zapytania bazodanowego:

1) ConnectionString – tzw. „ciąg połączenia bazodanowego” – zestaw znaków, określający dostawcę usługi, lokalizację i nazwę bazy, parametry dostępu, itd.
Przykład:
DATABASE=afin_Sales;DRIVER={MySQL ODBC 3.51 Driver};OPTION=0;;PORT=0;SERVER=afin.vot.pl;

2) SQL – zwany też „query text” – tekst zapytania bazodanowego w języku SQL
Przykład:
SELECT SUM(faktura_0.WART_NET) AS 'Suma z WART_NET' FROM afin_Sales.faktura faktura_0 WHERE (faktura_0.NAZWA LIKE 'AREX' AND YEAR(DATA)=2005)

Funkcja z powyższymi argumentami WYMAGA dodatkowo autoryzacji, tj. dodatkowego dostarczenia informacji o użytkowniku i jego haśle – na razie jednak skupiamy się na logice zapytania – problem autoryzacji omówię w dalszej części.

Zapytanie takie, gdy już jest autoryzowane, jest wykonywane w ok. 1/3 sekundy – serwer jest oddalony o setki kilometrów, zapytanie przechodzi przez łącza wielu dostawców Internetu, punkty dostępu sieciowego, itp. Zapytań takich jest 100. Jednakże, w tym wypadku, „100” staje się synonimem wyrażenia „bardzo dużo”.

Jak to usprawnić?

Praktyka zapytań bazodanowych z Excela jest taka, że, zwykle, są one kierowane do maksymalnie kilku baz naraz, tj. z jednego raportu, ale, dla naszych potrzeb wyjaśnienia tematu, zakładam, że baza danych jest tylko jedna – zapytań do niej jest 100.

Gdy przyjrzymy się powyższej funkcji, stwierdzamy, że 1-szy jej argument jest stały dla wszystkich zapytań – zmienia się tylko argument drugi, czyli SQL.

W języku SQL możemy jednak budować zapytania tablicowe, obciążone jednostkowym kosztem dostępu… a o to nam chodzi!

Rozwiązanie

Stwórzmy więc zapytanie „hurtowe”. Ale skąd wziąć do niego owe 100 argumentów detalicznych?

Mają je przecież nasze funkcje! Są one jednak rozproszone po arkuszu, a nawet wielu arkuszach, czy też skoroszytach. Nie są też, w żaden sposób, uporządkowane, ani pogrupowane według baz, typów zapytań, itp. Normalnie – jak to w Excelu.

1) Przeliczmy arkusz testowo, ale funkcje, które się przeliczają, nie mają nam zwrócić wartości z bazy danych tylko… swoje argumenty.

2) Złóżmy te argumenty w jedną, wielką tablicę zapytań, posortujmy ją, pogrupujmy zapytania podobne ze względu na ów ConnectionString (w naszym przykładzie mamy tylko jedną grupę)

3) Sporządźmy zapytanie do bazy, przy czym argumenty tego zapytania będą wyglądały odpowiednio:

a. 1-szy argument – literalnie przepisany ConnectionString

b. 2-gi argument to tzw. SQL-wy „UNION”, czyli konstrukcja:
SELECT (…) FROM (…)
UNION ALL
SELECT (…) FROM (…)
UNION ALL …
(tu: pozostałe 98 członów, razem 100)

4) Wyślijmy zapytanie do bazy, ponosząc POJEDYNCZY koszt „transportowy” – łączenie, autoryzacja, przesył zapytania, wykonanie zapytania na serwerze (może wymagać np. oczekiwania na przydział mocy obliczeniowej serwera), przesył danych

5) Gdy otrzymamy odpowiedź – będzie nią tablica wartości – przypiszmy odpowiednie pozycje tej tablicy do pierwotnych wartości zapytań, przechowywane w innej tablicy

6) W przypadku konieczności złożenia zapytań do wielu serwerów bazodanowych, zapytania składamy grupowo (czy też „hurtowo”) do każdego z serwerów oddzielnie

7) Przeliczmy wszystkie funkcje Excela powtórnie, ale teraz już, algorytmy tych funkcji, gdy sporządzą nam swoje argumenty, nie odpytają, każda po kolei, zewnętrznej bazy danych, tylko ową tablicę, w której „odpowiedzi” na te zapytania już są.

W naszej terminologii zapytań, ten tryb pracy to „hurtowe zapytanie detaliczne”, czyli hurtowe (= szybkie) złożenie zapytania, składającego się z wielu zapytań detalicznych (= elastycznych).
Pojedynczy koszt jest tu połączony w wielokrotnym efektem, czyli zwielokrotniamy w ten sposób efektywność zapytań.

Architekturę przeliczania nazwaliśmy roboczo „convenient client architecture” („architektura wygodnego klienta”).

Proste?

Nie, niestety, jest to naprawdę bardzo skomplikowane, ale…

Działa!

(Tylko w AFIN.NET)

Algorytm tego przeliczania jest bardzo skomplikowany, wymaga eliminacji dosłownie setek problemów bazodanowych, organizacyjnych, informatycznych, itp.

Ale to jest już zrobione, a użytkownika to mało interesuje – funkcje wartości zwracają.

Efekty:

1) Natychmiastowe przeliczanie setek funkcji naraz.

2) Jednostkowa autoryzacja

3) Pełna kontrola nad każdym elementem procesu

4) Użytkownik może sobie definiować raporty, jakie mu się podobają, a sposób przeliczenia go nie interesuje.
Dla użytkownika ważne jest, aby były to DOBRE wartości. Są.

5) Użytkownik cały czas pracuje w najwygodniejszym narzędziu, jakie może sobie wymarzyć – arkuszu kalkulacyjnym.

Jednym słowem:

Efektywne przeliczanie w Excelu!