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_id
when 3 then pvt.Sty
when 4 then pvt.Lut
when 5 then pvt.Mar
when 6 then pvt.Kwi
when 7 then pvt.Maj
when 8 then pvt.Cze
when 9 then pvt.Lip
when 10 then pvt.Sie
when 11 then pvt.Wrz
when  12 then pvt.Paź
when 13 then pvt.Lis
when 14 then pvt.Gru
elsenull
end'Wartość'
from Formularz pvt,( // KOLUMNY - Wyselekcjonowanie nazw kolumn i ich kodów
select c.name 'kol', c.column_id 'kol_id'
fromsys.tables t
innerjoinsys.columns c .on c.object_id=t.object_id
where 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 jak np. system Prophix, który łączy prostotę arkusza kalkulacyjnego i zaawansowane metody sporządzania budżetu, pracy grupowej oraz wielowymiarowe narzedzia controlingowe.

 

Roald Zub

Projektant systemów Business Intelligence
w BI-Pro Consulting Group Sp. z o.o.
Microsoft SQL Server Technology Specialist