Start / Publikacje / Przekształcenie arkuszy budżetowych z układu macierzy na tabelaryczny

Nasi Klienci

Dziękujemy za zaufanie m. in.:

 

  • RawPlug
  • DB Schenker
  • Almi Decor
  • Mosty Łódź S.A.
  • GANT Development S.A.
  • Miejskie Wodociągi i Kanalizacja w Kędzierzynie-Koźlu Spółka z o.o.
  • Strzeleckie Wodociągi i Kanalizacja Sp. z o.o.
  • Moto-Profil Sp. z o.o.
  • PMR Ltd. Sp. z o.o.
  • Przedsiębiorstwo Energetyczne ESV S.A.
  • Przedsiębiorstwo Budowlane CIROKO Sp. z o.o.
  • Intakus S.A.
  • Korona
  • Luvena
  • Kakadu
  • Renevis
  • PWIK Bolesławiec
  • MPWiK Lubin
  • ChŚ PWiK
  • DSA Financial
  • Pomorski Uniwersytet Medyczny w Szczecinie
  • Armatura S.A.
  • Wspólnota PMM Sp. z o.o.
  • SSE Polska sp. z o.o.
  • Komsa Polska Sp. z o.o.
  • Grupa Grass Sp. z o.o.

Bezpłatne seminarium online.

  • Temat: Controlling przedsiębiorstw produkcyjnych z systemem PROPHIX
  • Data: wtorek, 26 września 2017 r.
  • Godzina: 11:00 - 12:00
  • Prowadzący: Ekspert BI-Pro

Zapisz się!

Bezpłatne seminarium online.

  • Temat:  Zarządzanie relacjami z klientem - CRM z systemem eDokumenty
  • Data: czwartek, 28 września 2017 r.
  • Godzina: 11:00 - 12:00
  • Prowadzący: Ekspert BI-Pro

Zapisz się!

Bezpłatne seminarium online.

  • Temat: Elektroniczne biuro, czyli wsparcie obsługi kancelaryjnej z systemem  eDokumenty
  • Data: piątek, 29 września 2017 r.
  • Godzina: 13:00 - 14:00
  • Prowadzący: Ekspert BI-Pro

Zapisz się!

Bezpłatne seminarium online.

  • Temat: Skuteczne raportowanie i analizy w systemie TARGIT
  • Data: wtorek, 3 października 2017 r.
  • Godzina: 11:00 - 12:00
  • Prowadzący: Ekspert BI-Pro

Zapisz się!

Bezpłatne seminarium online.

  • Temat: Elektroniczny obieg i dekretacja faktur z systemem eDokumenty
  • Data: czwartek, 5 października 2017 r.
  • Godzina: 11:00 - 12:00
  • Prowadzący: Ekspert BI-Pro

Zapisz się!

Bezpłatne seminarium online.

  • Temat: Controlling w przedsiębiorstwach komunalnych z systemem PROPHIX
  • Data: piątek, 6 października 2017 r.
  • Godzina: 11:00 - 12:00
  • Prowadzący: Ekspert BI-Pro

Zapisz się!

Bezpłatne seminarium online.

  • Temat: Konsolidacja finansowa i zarządcza z systemem PROPHIX
  • Data: 10 października 2017 r.
  • Godzina: 11:00 - 12:00
  • Prowadzący: Ekspert BI-Pro

Zapisz się!

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 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