Przekształcenie arkuszy budżetowych z układu macierzy na tabelaryczny
Wszechobecność arkuszy kalkulacyjnych w firmach stawia je jako najbardziej popularne narzędzie do sporządzania budżetów.
Jednakże układy arkuszy planistycznych nie zawsze są adekwatne do potrzeb analizy w relacyjnych modelach danych. Rzecz bowiem w tym aby osoba wprowadzająca dane planistyczne miała formularz jak najbardziej intuicyjny, natomiast system informatyczny posiadał strukturę danych, która pozwala na efektywne uzyskiwanie informacji z relacyjnej bazy danych np. na potrzeby tworzenia raportów controllingowych zestawiając dane planistyczne z wykonaniem zasilanym z systemów ERP.
W celu zaprezentowania zmiany układu danych posłużę się przykładem. Dane z formularza budżetowego przekształcimy do układu odpowiedniego dla relacyjnej bazy danych.
Przykładowy formularz może wyglądać następująco:
rys. 1
Powyższy układ danych, w postaci macierzy, nie gwarantuje efektywnego uzyskiwania informacji z relacyjnej bazy danych.
Należy strukturę danych doprowadzić do postaci zaprezentowanej na rysunku obok /rys. 2/:
rys. 2
Dane z formularza budżetowego /rys.1/ można przenieść do bazy relacyjnej między innymi za pomocą Integration Services w ramach pakietu MS SQL Server. Jednakże nie zawsze mamy możliwość skorzystania z tej funkcjonalności. W niniejszym przykładzie zaprezentujemy jak można obsłużyć to zagadnienie za pomocą prostego skryptu SQL.
Zakładamy, że mamy już przeniesione dane do tabeli „Dane_zrodlowe” relacyjnej bazy danych w układzie formularza budżetowego /rys.1/:
CREATETABLE Dane_zrodlowe(Kod varchar(10),Opis varchar(100),Sty money, Lut money, Mar money,Kwi money, Maj money, Cze money,Lip money, Sie money, Wrz money,Paź money, Lis money, Gru money
Tabele wypełniamy danymi z formularza /rys. 1/ za pomocą skryptu:
INSERTINTO Dane_zrodlowe(Opis,Kod,Sty,Lut,Mar,Kwi,Maj,Cze,Lip,Sie,Wrz,Paź,Lis,Gru)VALUES('Amortyzacja','K1',100,100,100,100,100,100,100,100,100,100,100,100)INSERTINTO Dane_zrodlowe(Opis,Kod,Sty,Lut,Mar,Kwi,Maj,Cze,Lip,Sie,Wrz,Paź,Lis,Gru)VALUES('Zużycie materiałów','K2',20,40,30,20,40,30,20,40,30,20,40,30)INSERTINTO Dane_zrodlowe(Opis,Kod,Sty,Lut,Mar,Kwi,Maj,Cze,Lip,Sie,Wrz,Paź,Lis,Gru)VALUES('Usługi obce','K3',60,30,20,40,40,20,20,30,60,40,30,30)INSERTINTO Dane_zrodlowe(Opis,Kod,Sty,Lut,Mar,Kwi,Maj,Cze,Lip,Sie,Wrz,Paź,Lis,Gru)VALUES('Wynagrodzenia','K4',500,500,500,1000,500,500,1000,500,500,1000,500,1000)INSERTINTO Dane_zrodlowe(Opis,Kod,Sty,Lut,Mar,Kwi,Maj,Cze,Lip,Sie,Wrz,Paź,Lis,Gru)VALUES('Ubezpieczenia społeczne','K5',70,70,70,70,70,70,70,70,70,70,70,70)INSERTINTO Dane_zrodlowe(Opis,Kod,Sty,Lut,Mar,Kwi,Maj,Cze,Lip,Sie,Wrz,Paź,Lis,Gru)VALUES('Świadczenia na rzecz pracowników','K6',30,40,30,40,30,40,30,40,30,40,30,40)INSERTINTO Dane_zrodlowe(Opis,Kod,Sty,Lut,Mar,Kwi,Maj,Cze,Lip,Sie,Wrz,Paź,Lis,Gru)VALUES('Podatki i opłaty','K7',60,30,20,40,40,20,20,30,60,40,30,30)
Natomiast do przekształcenia struktury danych posłużymy się skryptem:
select pvt.kod 'Konto', kol.kol 'Miesiąc',case kol.kol_idwhen 3 then pvt.Stywhen 4 then pvt.Lutwhen 5 then pvt.Marwhen 6 then pvt.Kwiwhen 7 then pvt.Majwhen 8 then pvt.Czewhen 9 then pvt.Lipwhen 10 then pvt.Siewhen 11 then pvt.Wrzwhen 12 then pvt.Paźwhen 13 then pvt.Liswhen 14 then pvt.Gruelsenullend'Wartość'from Formularz pvt,( // KOLUMNY - Wyselekcjonowanie nazw kolumn i ich kodówselect c.name 'kol', c.column_id 'kol_id'fromsys.tables tinnerjoinsys.columns c .on c.object_id=t.object_idwhere t.name ='Dane_zrodlowe'and column_id>2) kol
Trudność polega na tym, aby nazwy kolumn (w naszym przykładzie są to nazwy miesięcy) oraz nazwy wierszy (kody pozycji kosztów) zamienić na wartości w tabeli wynikowej /rys. 2/.
Ponieważ nasz formularz ma strukturę macierzy i wiemy, że w kolumnie „Kod” jest zapisany identyfikator wiersza (tj. K1, K2, itp.) musimy jeszcze zidentyfikować kolumny formularza. Wyselekcjonowanie ich nazw i identyfikatorów zapewnia nam fragment skryptu 3. oznaczony komentarzem „KOLUMNY”, gdzie odpowiednio odwołujemy się do tabel systemowych zawierających metadane o strukturze tabel. Natomiast iloczyn kartezjański tabel „pvt” i „kol” po instrukcji from zwraca nam tyle rekordów w tabeli wynikowej ile jest wartości w formularzu budżetowym. Na koniec jeszcze instrukcją case wybieramy odpowiednią wartość dla miesiąca.
Ostatecznie otrzymujemy strukturę danych prezentowaną na rys. 2. Jest ona efektywna i elastyczna, a tworzenie zapytań proste.
Zastosowanie arkuszy kalkulacyjnych do budżetowania jest bardzo powszechne. Ich główne atuty w tym zakresie to: prostota i intuicyjność. Dlatego też jeszcze długo arkusz kalkulacyjny będzie podstawowym źródłem informacji planistycznych dla systemów informatycznych. Jednakże często jest tak, że przygotowanie całościowego budżetu firmy jest procesem złożonym i długotrwałym, nad którym pracuje wiele osób. W takiej sytuacji może okazać się, że arkusz jest niewystarczającym narzędziem do tego celu. Z pomocą przychodzą tu specjalizowane rozwiązania, które łączą prostotę arkusza kalkulacyjnego z zaawansowanymi metodami sporządzania budżetu, pracy grupowej oraz wielowymiarowymi narzedziami controlingowymi.
Roald Zub
Projektant systemów Business Intelligence
w BI-Pro Consulting Group Sp. z o.o.
Microsoft SQL Server Technology Specialist