Budapesti Műszaki- és Gazdaságtudományi Egyetem Villamosmérnöki és Informatikai Kar Elektronikai Technológia Tanszék
Szabó Péter
CASH FLOW ELEMZÉSI ALKALMAZÁS KÉSZÍTÉSE MICROSOFT BI ESZKÖZÖK HASZNÁLATÁVAL
KONZULENS
Pálfi Attila Dr. Szikora Béla BUDAPEST, 2011
Tartalomjegyzék Tartalomjegyzék ............................................................................................................. 3 Összefoglaló ..................................................................................................................... 6 Abstract............................................................................................................................ 7 1.
Bevezetés ................................................................................................................... 8
2.
A feladatkiírás pontosítása és részletes értelmezése ............................................. 9
3.
Irodalomkutatás, hasonló alkotások .................................................................... 11
4.
3.1.
Likviditás .......................................................................................................... 11
3.2.
Cash flow .......................................................................................................... 11
3.3.
Az EVA jelentősége ......................................................................................... 12
3.4.
Újítás a hasonló alkotásokhoz képest ............................................................... 13
3.5.
Adattárházak ..................................................................................................... 14
3.6.
Az OLAP kocka................................................................................................ 16
3.7.
A Microsoft Dynamics NAV felépítése és adatbázis struktúrája ..................... 17
3.8.
Az SAP Business One adatbázis struktúrája .................................................... 20
A tervezés és megvalósítás részletes bemutatása ................................................ 23 4.1.
Az adattárház struktúrájának kialakítása .......................................................... 24
4.2.
Áttöltő modul elkészítése Navision-höz ........................................................... 26
4.2.1.
A NAV vonatkozó tábláinak megismerése ............................................. 26
4.2.2.
Az ETL megtervezése, implementálása.................................................. 28
4.3.
Javaslat egy lehetséges ETL-re SBO-hoz ......................................................... 31
4.4.
SharePoint bekapcsolása a rendszerbe.............................................................. 36
4.4.1.
Specific Finder metódus létrehozása ...................................................... 38
4.4.2.
Finder metódus létrehozása .................................................................... 39
4.4.3.
Creator metódus létrehozása ................................................................... 40
4.4.4.
Delete metódus létrehozása .................................................................... 41
4.4.5.
Update metódus létrehozása ................................................................... 41
4.5.
Az elemzés paraméterezése .............................................................................. 42
4.6.
Az OLAP kocka felépítése ............................................................................... 43
4.6.1.
A kocka dimenzióinak kialakítása .......................................................... 44
4.6.2.
Az OLAP kocka felépítése ..................................................................... 45
4.7.
Áttöltés vezérlése SharePointból ...................................................................... 46
4.8.
Az elemzés elkészítése ..................................................................................... 49
4.9.
Közzététel SharePoint weben ........................................................................... 51
5.
Értékelés, továbbfejlesztési lehetőségek............................................................... 54
6.
Köszönetnyilvánítás ............................................................................................... 56
Irodalomjegyzék............................................................................................................ 57 Ábrajegyzék ................................................................................................................... 59 Táblázat jegyzék ........................................................................................................... 60 Függelék ......................................................................................................................... 61 A. Microsoft Dynamics NAV 5.0 telepítése Windows 7 operációs rendszerre ...... 61 B. Az SQL szerver konfigurálása a Microsoft Dynamics NAV 5.0-hoz ................ 62 B.1. A 4616-os követési jelző beállítása .................................................................. 62 B.2.
Az xp_ndo tárolt eljárások beállítása............................................................... 63
C. SharePoint Server 2010 telepítése Windows 7 operációs rendszerre ............... 68 C.1. Windows 7 konfigurációjának módosítása SharePoint 2010 telepítéséhez ...... 68 C.2. A SharePoint Server konfigurálása és hibaelhárítás ......................................... 71 D. Az adatbázis részletes bemutatása ....................................................................... 72
HALLGATÓI NYILATKOZAT Alulírott Szabó Péter, szigorló hallgató kijelentem, hogy ezt a szakdolgozatot meg nem engedett segítség nélkül, saját magam készítettem, csak a megadott forrásokat (szakirodalom, eszközök stb.) használtam fel. Minden olyan részt, melyet szó szerint, vagy azonos értelemben, de átfogalmazva más forrásból átvettem, egyértelműen, a forrás megadásával megjelöltem. Hozzájárulok, hogy a jelen munkám alapadatait (szerző(k), cím, angol és magyar nyelvű tartalmi kivonat, készítés éve, konzulens(ek) neve) a BME VIK nyilvánosan hozzáférhető elektronikus formában, a munka teljes szövegét pedig az egyetem belső hálózatán keresztül (vagy autentikált felhasználók számára) közzétegye. Kijelentem, hogy a benyújtott munka és annak elektronikus verziója megegyezik. Dékáni engedéllyel titkosított diplomatervek esetén a dolgozat szövege csak 3 év eltelte után válik hozzáférhetővé. Kelt: Budapest, 2011. 12. 08.
..................................................................... Szabó Péter
Összefoglaló A cash flow a vállalat egyik legfontosabb és legelterjedtebb pénzügyi mutatója, azonban értékét csak a kimutatásokból kapjuk meg, a vizsgált időszak letelte után. Számos egyéb pénzügyi mutató alapja a cash flow, így a vezetés érdekelt annak megfelelő szinten tartásában. A fő motiváció a munkám során egy olyan cash flow elemzési alkalmazás elkészítése volt Microsoft BI eszközök felhasználásával, amely segítségével a döntéshozóknak lehetőségük van a jövőbeni pénzáramlásokat, így a cash flow-t számukra kedvezően befolyásolni. Dolgozatomban először ismertetem a téma fontosságát, irodalmi kitekintést teszek, melyben megteremtem az elméleti alapot a megoldás elkészítéséhez. Ez után ismertetem a problémára általam készített megoldást. A megoldás során két fő szempontot vettem figyelembe: legyen az független az alkalmazott ERP-től és legyenek visszakövethetők benne az egyes tételek legalább az ERP szintjéig, de ha lehetséges, akkor a külső bizonylatokig. A feladat megoldása logikailag három lépésből áll: Ki kellett alakítani egy az alkalmazott vállalatirányítási rendszertől független adattárházat az elemzéshez szükséges adatok tárolására. Ezt követően fel kellett azt tölteni megfelelő mennyiségű információval az elemzéshez. Miután az adattárházban megvolt a kellő információ, ki kellett alakítani egy OLAP kockát az adattárházhoz, amely hatékonyan támogatja az elemzés megvalósítását. Ha az elemzés elkészült, a kész elemzést elérhetővé kellett tenni a döntéshozók felé, ehhez az Excel táblázaton kívül konfiguráltam a SharePoint vállalati portált, illetve annak Excel Web Access nevű szolgáltatását, hogy sima webes felületen keresztül, Excel kliensszerű felületen lehessen elérni az eredményeket. Végül a kész alkalmazást összevetettem a feladatkiírásban célként megfogalmazott követelményekkel. Megpróbáltam feltárni a rendszer gyenge pontjait, illetve javaslatokat tettem a rendszer jövőbeni továbbfejleszthetőségi irányvonalaira.
6
Abstract Cash flow is one of the most important financial measures for a company. Although it is used in a widely, it is usually measured during a specified, finite period of time, therefore we can get its figure only when we are not able to influence that. Shown its importance a huge range of other financial measures are based on it so the leaders and managers are interested to improve its standard. In this thesis I am going to develop a cash flow analysis application using Microsoft BI tools, which is assistance to the decision-makers during their further work to improve the companies’ measures. In the first part I introduce the knowledge, which is required to understand the contexts. After the theoretical introduction I guide through the designing process with my solution and specify developing reasons. During my solution I followed two main aspects: My tool must be independent from the ERP system that is the data source and it must keep all the identities used in the ERP to be able to look back the related documents. In keywords I needed to do the following steps: I had to design and implement an independent data warehouse and create ETL processes that fill it with relevant data When there was enough data for making the analysis I need to deploy an OLAP cube to the warehouse, which supports the clients to join and gain information for the analysis At least the final analysis has to be made available for managers and decisionmakers. For it I chose SharePoint, which is able to show Excel spreadsheets as a dynamic website with the help of Excel Web Access. This makes the results independent and portable. Finally I summarized my solution and suggested some aspects for further upgrading and developing.
7
1. Bevezetés A vállalat működésének egyértelmű célja gazdasági értékének növelése. Noha a gazdaság helyzetét számos pénzügyi mutatóval lehet jellemezni, egyik legalapvetőbb, leggyakrabban más elemzés alapjául szolgáló mutató a vállalat cash flow-ja. Egy pénzügyi mutatón túl azonban a cash flow szemléletes jelentéssel is bír. Ahogy Al Ehrbar is fogalmazott, a vállalat cash flow-ja áll a legközelebb a valósághoz, azt a legnehezebb tartósan befolyásolnia a könyvelésnek: Mindig emlékezzünk arra, hogy a készpénz a tény, a nyereség viszont csak egy vélemény. [1] Mindezekből világosan kiderül, hogy a cash flow fontos szerepet játszik a cégek helyzetének objektív megítélésében, ezáltal kedvező szinten tartása alapvető érdeke a menedzsment rétegnek és a tulajdonosi, részvényesi csoportnak is. Annak ellenére, hogy az egyik legfontosabb pénzügyileg, illetve értékteremtésben használt jellemzőről beszélünk, alkalmazását jelentősen megnehezíti, hogy egy adott múltbeli időszakról ad képet, így közvetlenül nincs lehetőségünk alakítani azt. Gondoljuk csak végig: Ahhoz, hogy megfelelően gyarapodjon a vállalatunk, jó cash flow-t kell kialakítanunk. A cash flow befolyásolásához pedig befolyásolni kell tudni az ügyfeleinket, amelyet gazdasági erőnknél fogva tudunk könnyebben sikerre vinni. Azt eldönteni, hogy a terveinknek megfelelően alakult-e a cash flow, csak a vizsgált időszak után tudjuk, ami azonban magában hordozza annak a lehetőségét, hogy nem jönnek az elvárt eredmények, így elmarad a növekedés vagy esetleg még csökken is a vállalkozásunk értéke. E paradoxon helyzet kibogozása alapvetően fontos szerepet játszik a probléma kezelésében. Ennek elméleti körüljárása után dolgozatom fő részében végig kalauzolom az olvasót egy olyan cash flow elemzési alkalmazás fejlesztése menetén, amely e problémára kíván megoldást kínálni, azaz már abban az állapotában kívánom megfogni, tervezni a vállalati pénzáramokat, amikor azokra még nem érvényes a számviteli törvények vasszigora, hanem a vállalatok közti tárgyalások, megbeszélések, ajánlattételek során valamelyes képlékenyebben kezelhetők. A tervezés támogatása mellett természetesen végig fontos hangsúlyt kap az információ kezelése, a vállalatirányítási rendszerbe kerüléstől egészen a menedzserhez érkező jelentésig.
8
2. A feladatkiírás pontosítása és részletes értelmezése A szakdolgozatom során egy fiktív vállalat, a Cronus Rt. pénzügyi adataira támaszkodva készítettem el egy egyedi megoldást, melynek célja a cash flow elemzése, annak kedvező irányba történő befolyásolásának megteremtése. Kezdésként részletesebben elmélyültem a vállalatok értékteremtési stratégiájában, az ott alkalmazott mutatószámok meghatározásában, az egyes kimutatások és a vállalat tényleges piaci értékének egymásra hatását, egymás befolyásolását tanulmányoztam. Ezen tanulmányaim során figyeltem meg, hogy a vállalat piaci értékében óriási szerepet játszik annak pénzeszköz tartaléka. A pénzeszköz tartalékot leginkább befolyásolni pedig a vállalat pénzáramainak tervezésével lehet. A pénzáramlás jellemzésére szolgáló számviteli mutató a cash flow. Mivel egy adott időszakról csak azt követően nyújt információkat, ezáltal csak visszajelzésként, terv-tény összehasonlításban használható. Munkámban ezen szerettem volna változtatni. Célként fogalmaztam meg a cash flow tervezhetővé tételének támogatását, ehhez nem csupán a lezárt vállalati folyamatokat vettem bele az elemzésbe, hanem egy olyan rendszert alakítottam ki, ahol figyelembe veszem az ajánlatokat és rendeléseket is, hiszen ezek esetében van még némi lehetőség a változtatásra, tervezésre, paraméterezésre. Az elemzés paramétereként alapvetően két dolog állítására van lehetősége a felhasználónak: beállíthatja, hogy mekkora toleranciával számoljon a rendszer az egyes pénzmozgások teljesítésekor illetve hogy mekkora a teljesítés valószínűsége. Természetesen ezek tetszőlegesen kombinálhatók, hiszen előfordulhat például olyan szituáció, hogy a partnerünk nem teljesíti a fizetési kötelezettségét. Ezt követően felszólítjuk, haladékot adunk neki, majd amennyiben továbbra sem törleszti a tatozását, behajtjuk annak bizonyos százalékát egy faktoring cégen keresztül. A legfontosabb motiváció a munkám során az volt, hogy egy széles körben alkalmazható megoldással álljak elő. Ennek szükséges feltétele a modularitás. A modulok minél inkább legyenek függetlenek egymástól. Alapvető célként fogalmazódott meg, hogy az adattárház ennek értelmében legyen strukturálisan független a használt vállalatirányítási rendszertől. Ennek biztosítása érdekében megvizsgáltam az SAP Business One rendszerének és a Microsoft Dynamics Navision-nek az adatbázis struktúráját.
9
Az ERP függetlenség mellett a másik vezérelv a lefúrhatóság és visszakövethetőség volt. Ennek érdekében egyrészt ahol lehetséges - és van értelme - hierarchikus dimenziókat alakítottam ki, másrészt az ERP-beli azonosítón túlmenően az egyes rekordokban szerepeltetem a hivatkozó külső dokumentumok azonosítóját is. Feladatom megoldásához Microsoft technológiákat használtam fel. A megvalósított alkalmazás középpontjában a vállalatirányítási rendszertől független, alkalmazásszerű adattárház áll, melyhez a szükséges adatáttöltő modult az Integration Service szolgáltatással készítettem el. Adatforrásként egyrészt a Navision-t használom, másrészt pedig SharePoint táblákat. Az SQL szerveren tárolt SharePoint táblák módosításához szükséges kapcsolókat C# nyelven implementáltam. A komponensek vezérléséhez a SharePoint felületet egészítetem ki egy ASP oldallal, ahonnét a felhasználó szabályozni tudja az adattárházba való áttöltést, beállíthatja az elemzés során használt paramétereket vagy újraépítheti az adattárház aktuális állapota szerint az OLAP kockát, amelyet az SQL szerver Analysis Service szolgáltatásával készítettem el. Ezután az elemzés elkészítéséhez Excelt használok kliensként, melyből rá tudunk kapcsolódni az OLAP kockára, így kinyerve abból a kívánt információt. Az elkészített elemzést Excelből tudjuk publikálni a SharePoint szervere, így az könnyedén megosztható kollégáinkkal. Az feladatom megoldása során a következő problémákkal kerültem szembe: 1. Ki kellett alakítanom a vállalatirányítási rendszer környezetet, melyben a feladatom el tudtam végezni. Ehhez fel kellett kutatni a megfelelő szoftvereket, valamint megfelelően be kellett konfigurálni azokat a sikeres együttműködéshez 2. Meg kellett tervezni, majd implementálni az adattárház logikai sémáját 3. A vállalatirányítási rendszer adatstruktúráját át kellett alakítanom egy, a számomra megfelelő adattárház struktúrává, amelybe be kellett töltenem a szükséges adatokat. Implementálnom kellett az ehhez szükséges áttöltő modulokat. 4. Fel kellett készíteni az adattárházat, hogy azon el tudjam végezni az elemzéseket, azaz fel kellet építeni rá egy OLAP kockát. 5. Adat konnektorokat kellett létrehozni a SharePointhoz, hogy az az egyéb adataitól elszigetelve, tetszőleges SQL táblában tudja tárolni a listákat 6. Az elemzés támogatásához kiegészítő funkciókat kellett implementálnom SharePointba 7. Az elemzés elkészítése 8. A SharePoint konfigurálása a publikálásra, az elkészült elemzés publikálása 10
3. Irodalomkutatás, hasonló alkotások 3.1. Likviditás A vállalkozások gazdálkodásának egyik legfontosabb elemzési területe a pénzügyi helyzet vizsgálata. A vállalkozás vezetése éppúgy érdekelt a pénzügyi helyzet naprakész ismeretében, mint a piac külső szereplői, a hitelezők, a szállítók, a befektetők vagy a versenytársak. A pénzügyi helyzet egyik legfontosabb irányelve a likviditás. Egy szervezet fizetőképességét, vagyis azt a képességét, hogy bármely pillanatban vissza tudja fizetni az ügyfelei pénzét, illetve fizetni tud ügyfelei szolgáltatásáért likviditásnak nevezzük. A likviditás a szervezetek egy állapota, amely azt a képességet fejezi ki, hogy van-e lehetőségük fizetni egy termékért, eszközért, vagy szolgáltatásért. Nem keverendő össze azzal, hogy szándékoznak-e ténylegesen fizetni. A likviditási szint fenntartásának ugyanúgy vannak költségei, mint annak, ha nem sikerül azt fenntartani. Például amennyiben készpénz tartalékot halmozunk fel, akkor számolnunk kell az elmaradt befektetés kieső hasznával, az úgynevezett alternatíva költséggel (opportunity cost), de amennyiben elveszítjük a likviditásunkat és likviditási hitelt kell felvennünk, akkor annak is megvannak a maga költségei. A likviditást gyakran tévesen használják még a nyereségesség mérőszámaként is, ez azonban téves, hiszen attól, hogy egy vállalat pillanatnyilag fizetésképtelen, még lehet, hogy nyereséges, illetve abból, hogy fizetőképes nem következik a nyereségessége.
3.2.
Cash flow [2] [3]
A pénzügyi helyzetének megítélésének másik legfontosabb irányelve a cash flow, mely gyakran összefolyik a likviditás fogalmával, pénzügyi értelemben azonban különbözőek. A cash flow nem más, mint a vállalat adott időszakban vett pénzáramai, vagyis a bejövő és a kimenő pénzáramok különbsége a vizsgált időszakban. A számviteli törvény pontosan leírja, szabályozza a cash flow kimutatás szerkezetét.1 A gyakorlatban több cash flow kimutatás is elterjedt, ezek számítási, származtatási módjukban térnek el egymástól. [4] Direkt cash flow: a direkt cash flow-t szokásos párba állítani a likviditási tervvel, hiszen ez a mérőszám áll legközelebb hozzá, ezzel vizsgálható az a legkönnyebben. A direkt 1
http://net.jogtar.hu/jr/gen/hjegy_doc.cgi?docid=A0000100.tv
11
cash flow nem más, mint a pénzösszeg napi változása. A nyitó pénzkészlet, az összes bevétel és összes kiadás egyenlegeként képezhető az adott időszak pénzügyi egyenlege. Indirekt cash flow: a direkt cash flow-nál elterjedtebb, a hatályos törvényi szabályzásoknak megfelelő mutató. Nem csupán a pénzeszközöket veszi figyelembe az egyenleg meghatározására, hanem a mérleg szerinti állapotváltozás alapján határozza meg a vállalati mutatót. Mérleg alap összefüggése változásra felírva: Azaz a pénzeszközök változása plusz a nem pénzeszközök változása egyenlő a saját források változása plusz a nem saját források változása. Ezen egyenlet átrendezésével kaphatjuk meg az összefüggést a pénzeszközök változására: Szabad cash flow: a szabad cash flow befektetői szemlélet mentén vizsgálja a vállalat pénzügyi helyzetét. A szabd cash flow meghatározására a két legáltalánosabban elfogadott módszer kidolgozása Mills és Copeland nevéhez fűződik. A szabad cash flow azt fejezi ki, hogy a vállalatnak a költségei fedezésén túl mennyi pénzeszköz áll a rendelkezésére szabad erőforrásként. A szabad cash flow (FCF) Mills szerinti megközelítésben az
ezzel szemben a Copeland szerintiben az 2
képlettel számítható ki. Ezek közül kiemelten a direkt cash flow-val foglalkozom dolgozatom elkészítése során.
3.3. Az EVA jelentősége A tényleges piaci értéket azonban nem mindig tükrözik a számviteli mutatók, mivel azok gyakran figyelmen kívül hagyják a mögöttes információkat. Az, hogy melyik számviteli mutatót alkalmazzuk, nem befolyásolja sem a vállalat pénzáramlását, sem pedig a gazdasági értékét. Mindig emlékezzünk arra, hogy a készpénz a tény, a nyereség viszont csak egy vélemény. [1]
2
Earnings Before Depreciation, Amortization and Deferred Taxes – Kereset az értékcsökkenés, amortizáció és az elhalasztott adók előtt
12
Tehát a pénzügyi mutatók nem feltétlenül segítenek abban, hogy a vállalat piaci erejéről teljes képet kapjunk. Például a Coca Cola sokáig piacvezető volt az üdítőital gyártók piacán, úgy, hogy csak középszerű volt vagyontermelésben. [5] Ezért teret nyert az úgynevezett EVA szemlélet, melyben egy vállalat gazdasági teljesítményét egy új mércével, a hozzáadott gazdasági értékkel (Economy Value Added) jellemzik. Az EVA-t az adózott üzleti eredmény és a tőkeköltség különbségeként definiálták. Mint már láttuk, a nettó üzleti eredmény pedig szoros összefüggésben áll a cash flow-val, így a cash flow befolyásolásával alakítani tudjuk a hozzáadott gazdasági értékét a vállalatunknak, ez pedig minden tulajdonos vágya.
3.4. Újítás a hasonló alkotásokhoz képest Hasonló alkalmazások után keresve a következő megállapításra jutottam: a legtöbb alkalmazás a cash-flow kimutatásból indul ki. Cash flow kimutatást azonban, mivel egy adott időszakról szól, csak múltbeli adatok alapján képesek elkészíteni a cégek, vagyis a múltbeli események hatásait tudják elemezni, de közvetlenül nem képesek a jövőjüket irányítani. Munkámban a cél az, hogy a likviditási tervet összekössem a direkt cash flow-val, ezáltal a cash flow tervezhetővé váljon. A cash flow nagyon pontos képet ad a vállalat pénzeszköz áramlásáról, azonban túl későn, mikor az eredmények elérhetőek, akkor már nincs lehetőség a pénzeszközök mozgásának arányáról dönteni.
1. ábra – A pénzügyi adatok befolásolhatósága és pontossága
13
Munkám során a cél az, hogy a cash flow kimutatásban jelenjenek meg az azt megelőző elemek is, így látszódjon abban a még irányítható, tervezhető folyamatok hatása is, ezáltal legyen lehetőségünk tudatosan befolyásolni azt.
3.5. Adattárházak [6] [7] Az adattárház az információtechnológia viszonylag frissen önálló életre kelt ága, mintegy egy évtizedes múltra visszatekintő területe. Rohamléptekkel, egyszerre több irányba fejlődő, kiforratlan és már bizonyított technológiák halmaza, kevés elismert szabvánnyal, sok eltérő szolgáltatással és termékkel. Nehéz rá jó definíciót adni. Bill Inmon, a téma egyik apostolának legtöbbet idézett definíciója így szól: "A data warehouse is a subject oriented, integrated, nonvolatile, and time variant collection of data in support of management's decisions.", vagyis tárgy orientált, integrált, tartós és idő független gyűjteménye az adatoknak, mely a vezetői döntéshozást támogatja. Jobban végiggondolva a definíciót, kellően világos képet kaphatunk az adattárházak miben létéről: Subject oriented (tárgy orientált): Az adattárházakat mindig valamilyen jól definiált céllal hozzuk létre, azok meghatározott adatok köré épülnek, például az értékesítési adatok köré. Integrated (integrált): Az előz pontban említettet tárgyorientált, adatvezérelt tervezéshez szorosan kapcsolódik az integráltság fogalma abban az értelemben, hogy az adattárházban tárolt adatokat egy az adott tárgyterületre jellemzően csoportosítva célszerű tárolni. Nonvolatile (nem illékony): Azaz alapelvárás az adattárházba betöltött adatokkal szemben, hogy azok változatlanok legyenek. Amennyiben a forrásrendszer mégis megváltoztatná azokat, abban az esetben megfelelő időbélyegekkel kell ellátni őket. Time variant (idő független): Az adatokat általában jelen pillanatban töltjük be az adattárházba, de az elemzéseket múltbeli forrásokra támaszkodva végezzük el, és abból következtetünk jövőbeli eseményekre. Ezért az adatokat időponttól függetlenül kell időszakokhoz tárolnunk, hogy abból a szükséges riportok könynyen elérhetők legyenek. Az adattárház műveletek alatt általában a következőket értjük: a) Adatkinyerés tranzakciós, vagy más rendszerből, pl.: a vállalatirányítási rendszerből b) A kinyert adatok átalakítása a beszámoló elkészítéséhez alkalmas formára.
14
c) A riportok, beszámolók elérhetővé tétele a döntéshozók számára. Az adattárház felépítése e céloknak megfelelően eltér az Adatbázisok című tárgyból tanultaktól. [8] Ebben az esetben az elsődleges szempont az adatok hatékony kinyerése az elemzések elkészítéséhez, azaz megengedhető némi redundancia a teljesítmény növeléséhez. Az adattárházakat általában csillag vagy hópihe sémában építik fel. Mindkét esetben az elemzés szempontjából fontos adatokat a ténytáblában helyezik el. Ide kerül be a vállalatirányítási rendszer törzs adatállományának megfelelő transzformáltja. Eköré, az úgynevezett dimenzió táblákba kerülnek be azon információk, melyek szerint a szűréseket, csoportosításokat elkészíthetik. Ezen táblákban, az ezek adatsémáiban, a köztük kapcsolatokban tér el egymástól a csillag és a hópihe séma. Csillagsémánál az elemzési dimenziók idegen kulccsal kapcsolódnak közvetlenül a ténytáblához az alábbi ábrán látható módon.
2. ábra – Csillagséma ER diagramja
Ezzel szemben hópihe séma esetében az egyes dimenziótáblák a természetes logikát követve egymással is kapcsolatban álnak, például az egyes üzletek városokban vannak, a városok megyékhez tartoznak, és így tovább. Ennek köszönhetően lehetőség nyílik az egyes táblák normalizálására, a redundancia csökkentésére. A hópihe séma ER (Entitás Relációs) diagramját a 3. ábra szemlélteti.
15
3. ábra – A hópihe séma ER diagramja
A témám feldolgozása során alapvetően csillag sémás adattárházat építettem fel. A csillag séma összehasonlítása a hópihével: Előnyei: egyszerű, intuitív adatmodell használata kevés join adatbázis műveletet igényel használata kevés tábla olvasását igényli könnyű megvalósíthatóság, a modell meta adatai (adatokat leíró adat) egyszerűek Hátrányai: aggregációk (összegek) képzése nehézkes nagy dimenziótáblák esetén a hierarchiakezelés nagyon lassíthatja a lekérdezéseket a dimenzióadatok tárolása redundáns
3.6. Az OLAP kocka A vállalatirányítás rendszerek Online transaction processing (OLTP) elven működnek, azaz fő rendező elvük a tranzakciók végrehajtása. A felhasználók, a programok apró, elemi mű-veletek sorozatát hajtják végre a tranzakciókban, ezáltal egyes adatbázis objektumok állapotát kérdezik le, frissítik egymástól függetlenül, konkurens módon. 16
Az elemzésekhez azonban ez a megközelítés nem megfelelő. Ilyen célokra az Online analytical processing (OLAP) megközelítés használatos. Az OLAP módszerek mindig magukba foglalják az adatok interaktív lekérdezését, valamint azok elemzését is. A felépített adat-tárház adatstruktúrája már úgy van megtervezve, hogy az a lehető leghatékonyabban segítse ezt a szemléletet, emiatt az adattárház és az OLAP fogalmak a gyakorlatban teljesen összefonódtak.
4. ábra – Az OLAP kocka 3 dimenziós reprezentációja
Az OLAP kocka egy adatszerkezet az adattárházban tárolt adatok elemzéséhez, csoportosításához. A kocka egyes dimenzióit az adattárház dimenzió táblái képzik, ezek szerint tudjuk beazonosítani a ténytáblában tárolt adatok egyes összetett részhalmazait. A kocka felépítését a 4. ábra tartalmazza. Gyakran tárolják el az egyes termékek a részhalmazok egyesítésekor, aggregálásukkor az egyes halmazok összesített mutatószámait. Így jelentős teljesítménynövekedés érhető el. A gyakorlatban természetesen több dimenziót is alkalmazhatunk, azonban ez ritkán indokolt és könnyen vezethet nehezen áttekinthető végeredményhez. A sok dimenzió szerinti analízis már inkább az adatbányászat területébe nyúlik át.
3.7. A Microsoft Dynamics NAV felépítése és adatbázis struktúrája A Dynamics NAV (más nevén Navision) a Microsoft ERP rendszere, kifejezetten kisés középvállalatok számára. A vállalat napi teendőinek ellátásához teljes körű segítséget nyújt, minden elvégzendő vállalati feladathoz rendelkezik megfelelő modullal. Mivel ezt használtam a szakdolgozatom elkészítéséhez, ezért ismertetném kicsit részletesebben is.
17
Főbb moduljai a pénzügy, kereskedelem, CRM, termelésirányítás, raktárkezelés, projekt menedzsment és humán erőforrás. A felhasználói felület felépítése és a megjelenő űrlapok nagyban hasonlítanak a QAD EA-ban és SAP Business One-ban megismertekre. A pénzügyi modulban állíthatók be a vállalat főkönyvi adatai, kezelhetők az eszközök és a bankszámlák, de itt kérdezhetők le a vállalat cash-flow és likviditási adatai is. A kereskedelem modulban találhatók meg a szállítói beszerzéseket és az értékesítést támogató modulok. Ezek egymáshoz rendkívül hasonlóak, hiszen a két műveletnél ugyanazon folyamatnak a két oldalán van a vállalat, ettől eltekintve a fogalmak hasonlóak: ajánlatok, megrendelések, visszaigazolások, számlák. A CRM rendszeren keresztül kerül kapcsolatba a termelés a vevőkkel, itt kerülnek megvalósításra azon funkciók, melyek az ügyféladatok kiértékelésében és az árképzésben támogatják a felhasználót. A termelésirányítási modulban nyílik lehetőségünk az agyagjegyzékek, a termékútvonalak, a kapacitás, az ütemezés, és a gyártási megrendelések létrehozására, karbantartására. A raktározás blokkjában a belső és külső árumozgatások összehangolása történik meg a tárolási költségek minimalizálása és a vevői kiszolgálás színvonalának növelése érdekében. A projektmenedzsment menüponton keresztül érhetők el az egyes projektek adatai, mint például az előrehaladása, költségei. A humán erőforrás modulban tarthatók karban az egyes dolgozók törzsadatai, mint a munkaidő, a munkabér, az alkalmazottakhoz kapcsolódó ráfordítások vagy a személyi jellegű költségek. A Navision által lefedett üzleti területek, folyamatok struktúráját az 5. ábra szemlélteti. Színes háttérrel pedig azok a részek láthatók, amelyeket a szakdolgozatomban felhasználtam.
18
Szállító
Vevő Keresés
Megkeresés
Ajánlat
Ajánlat Készletvizsgálat Megrendelés
Megrendelés
Szállítás
Termelés Projekt Szerviz
Anyag raktár
Raktár
Szállítás
Számlázás
Számla
Számlázás
Átutalás
Bank
Átutalás
Szállítás
Könyvelés
5. ábra – A Navision struktúrája
A NAV telepítésekor két féle mód közül választhatunk az adatok tárolására: Microsoft SQL Serveren vagy a háttértáron egy FDB fájlban tároljuk azokat. Jelen esetben természetesen az SQL Servert választottam, bár a feladat ugyanígy megoldható lett volna az adatok FDB fájlban való tárolásával is.3 Az adatok kinyerése előtt meg kellett azokat találnom, illetve ismerni azok tárolási logikáját az SQL Serveren. Ebben nagy segítségemre volt az Object Designer, amit a NAV Eszközök menüjéből érhetünk el. Ennek a programnak a futtatásával tudjuk megnézni, hogy az egyes menüpontok alatt elérhető adatok mely adatbázis táblában tárolódnak el. A szakdolgozatomban a következő Navision táblákat használtam: Szállítói és vásárlói fej- és sortáblák Szállítói főkönyvi táblák
3
Az MS SQL szerver minden olyan objektumot képes adatbázisként használni, ami rendelkezik OLE DB
API-val [15]
19
Vásárlói főkönyvi táblák A banki főkönyvi táblák A szállítói ajánlatok és megrendelések táblái A vevői ajánlatok és megrendelések táblái A szükséges táblák szerkezetének megismerése, tanulmányozása után elkészítettem a megfelelő áttöltő folyamatot, ami az adatokat – átalakítás után – az ERP adatbázisából az adattárházba másolja át. Az adattáblák részletekbe menő bemutatásával ezért az ETL4 bemutatásánál foglalkozom.
3.8. Az SAP Business One adatbázis struktúrája Az SAP Business One (SBO) az SAP integrált ügyviteli rendszere a KKV szektor (kisés középvállalatok) számára, mely az Dynamics NAV-hoz hasonlóan szintén magába foglalja a vállalati működés valamennyi funkcióját.
6. ábra – Az SBO funkciói
Az SBO az adatok tárolását minden esetben adatbázis kezelő rendszerben végzi. Erre a célra legszélesebb körben a Microsoft SQL Server-t alkalmazzák, köszönhetően annak jó skálázhatóságának valamint olcsó telepítési és üzemeltetési költségeinek. MS SQL mellett támogatott még az IBM DB2 és a Sybase is. SBO esetében is lehetőségünk nyílik az adatbázis objektumok vizsgálatára a programból. A Navision táblázatával ellentétben itt az állapotsoron kaphatunk információt az adatbázis tábláról és annak attribútumáról, ha a Nézet/Rendszerinformációk menüpontot
4
Extract Transfer Load (Kinyerés, Átalakítás, Betöltés): az áttöltő folyamat rövid elnevezése
20
bekapcsoljuk ezután részletes információt a táblára rákeresve a súgóban kaphatunk annak szerkezetéről, tartalmáról.
7. ábra – Rendszerinformációk megjelenése az állapotsoron
A Partnerek tárolása az SBO-ban három féle típussal történhet: szállító, vevő vagy érdeklődő. A partnerek törzsadatainak tárolása a Business Partner (OCRD) táblában történik. A Navision-nel ellentétben az összes üzleti partner egy táblában tárolódik el, abban azonban hasonlítanak, hogy ha ugyanazt a partnert vevőként és szállítóként is fel kívánjuk venni, akkor két törzsrekordot kell felvenni a rendszerbe, különböző típussal. Amennyiben a partner szállító (Vendor) vagy vevő (Customer) típussal van definiálva, lehetőség van a rendszerben számla felvételéhez. A számla tárolása egyrészt a számla iránya szerint eltérő, másrészt a számlafej- és számlasor-adatok is külön táblákban vannak eltárolva. A számlafej táblákban – függetlenül azok irányától – általános illetve kötelező számlaadatok tárolódnak, mint például a partner számlázási címe, a számla típusa (eszköz vagy szolgáltatás számla), kódja, a teljesítés dátuma, a nyomtatási, küldési státusza, teljes összeg, teljes ÁFA, stb. Ezzel szemben a számlasor-táblákban az egyes sorok adatai találhatók, mint a számla azonosítója, tétel azonosítója, mennyisége, egységára, sor összeg, stb. Az SBO-ban a kimenő számlák fejadatai az A/R5 Invoice (OINV) táblában találhatók, míg a hozzájuk kapcsolódó sor adatok az A/R Invoice – Rows (INV1) táblában helyezkednek el. Az OINV táblában megtalálható a partner azonosítója, az INV1 táblában pedig a számla azonosítója, mint idegen kulcs, így kapcsolva össze az adatokat a 8. ábra analógiája szerint.
Partner
Fej tábla
Sorok tábla
8. ábra- A parten, szálmafej és számlasor adatok kapcsolata
5
Accounts Receivable – követelt számla
21
A bejövő számlák kezelésének logikája teljesen megegyezik a kimenő számlákéval, csupán más táblákat használ a rendszer a tárolásukra. A bejövő számlafejeket az A/P6 Invoice (OPCH), a sorokat pedig az A/P Invoice - Rows (PCH1) táblában találhatjuk meg. Természetesen az SBO-ban is lehetőség van ajánlatok és rendelések kezelésére. A tárolás szisztémája megegyezik az előzőekben ismertetekkel a Sales Quotation (OQUT) táblában találhatók az eladási ajánlatok fej adatai, a Sales Quotation – Rows-ban (QUT1) pedig a soradatok. A vásárlási és eladási rendelés adatok ugyanígy tárolódnak az ORDR, RDR1, illetve az OPOR és a POR1 táblákban. A vásárlási ajánlatok tárolására, kezelésükre a rendszerben csak a 8.82-es minor release óta van lehetőség. A bejövő pénzügyi ügyletekről a bizonylatok feje az ORCT, sorai a RCT1-3, a kimenők pedig az OVPM és VPM1-3 a táblában találhatók meg, attól függően, hogy milyen módon történt a pénzmozgás (számla, csekk vagy utalvány).
6
Accounts Payable – fizetendő számla
22
4. A tervezés és megvalósítás részletes bemutatása A feladat megvalósítása során a legfőbb tervezési cél egy olyan rendszer kialakítása volt, amely önálló komponensszerű, azaz nem egy fajta vállalatirányítási rendszer moduljaként képes viselkedni, hanem egy attól független alkalmazásként. uc Felhasználói esetek
Adatok felv itele Nav ision-be
Adatok, paraméterek felv itele SharePoint-ba Adattárház inicializálása «extend» ETL indítása Felhasználó «extend»
Adatok áttöltése az adattárházba
OLAP kocka frissítése
Elemzés készítése
«include» Elemzés publikálása
9. ábra – Az elkészített alkalmazás use case diagramja
Az általam megvalósított megoldásban a felhasználónak a következő tevékenységek elvégzésére van lehetősége: A Navision teljes körű kezelése: Adatok felvitele a vállalatirányítási rendszerbe, a vállalati törzs és tranzakciós adatok kezelése Egyéb cash flow tételek felvitele SharePointba, illetve az elemzés főbb paramétereinek beállítása
23
Adatok áttöltése a különböző forrásokból az adattárházba. Ezt a Navision felületén van lehetősége kezdeményezni, illetve igény esetén indítható az SQL szerverről vagy az Intézőből is Az OLAP kocka felépítése/frissítése az adattárház aktuális állapotának megfelelően A kockára Excel kliensből rácsatlakozva a kockára, könnyedén elkészíthetők és megjeleníthetők a különböző kimutatások Végezetül nincs más hátra, mint az elkészült elemzések publikálása SharePointban Az alábbi ábra mutatja a megoldás komponenseinek egymáshoz fűződő viszonyát. Jól látható az adattárház központi szerepe. Mivel az egyik legfőbb célkitűzés a vállalatirányítási rendszertől való minél nagyobb fokú függetlenítés volt, ezért ezt prezentálandó, az adatok beolvasásához két rendszert használok: Dynamics Navisiont és SharePointot. További ERP-k becsatolása természetesen biztosított, csupán az áttöltő modulokat kellene elkészíteni hozzájuk, a lánc többi része nem szorul változtatásra. A későbbiekben a Navision-höz készített áttöltő modul részletes bemutatása után vázlatosan javaslatot teszek egy ETL példára SBO környezethez. NAV tételek
SharePoint paraméterek
Adattárház
OLAP kocka
Excel elemzés
Elemzés közzététele
SharePoint tételek
10. ábra – Az általam készíített megoldás komponenseinek kapcsolata
4.1. Az adattárház struktúrájának kialakítása A séma megtervezésével kezdtem el a munkát, ezzel is próbáltam kiküszöbölni, hogy az ERP-k adatbázisának bármilyen fokú ismerete befolyásoljon munkám során. Annak érdekében, hogy az adattárház strukturálisan független maradjon az adatforrásoktól, a benne tárolt adatok relációs sémáját kellő absztrakcióval kellett elkészíteni. A relációs séma tervezésekor leginkább az előzőekben már ismertetett csillag minta dominált, mivel a viszonylag kisszámú és kicsi kardinalitású dimenziótáblán még nem okoz
24
túl nagy teljesítmény csökkenést a hierarchia kialakítása, a séma felépítése viszont egyszerű. Document No Type
Time
Direction
ERP Entry Id
Fact
Percentage
Partner
Location
11. ábra – Az adattárház sémájának ER diagramja
Az előbbi ábrán látható a kialakított adattárház ER diagramja, melyen jól kivehető a csillag szerkezet, amelytől csak a Partner - Location kapcsolat esetén térek el. Ez utóbbi esetben a természetes logika is a hópihe elrendezésre való átváltást diktálja, hiszen a földrajzi elhelyezkedés a Partner attribútuma, ezért a ténytáblában történő becsatolás logikailag nem helyes, a Partner táblában történő tárolás pedig redundáns. Az elemzés elkészítéséhez a következő jellemzőket tárolom el az adatbázisba: Document No tábla A rekordra hivatkozó külső dokumentum azonosító száma található meg ebben a táblában, így az elemző azt adott esetben könnyebben megtalálhatja, nem szükséges ahhoz visszafúrnia a vállalatirányítási rendszerben. Time tábla Az idő dimenziótáblája. A dátum mellett egyéb aggregálást segítő attribútumok szerepelnek bent, mint például a hetek száma, fél- vagy negyedév. ERP Entry Id tábla A pénzmozgás vállalatirányítási rendszerben szereplő azonosítóját tárolom el ebben a dimenzióban, ezzel szintén a visszakereshetőséget segítve. Partner tábla A partner adatait tárolom itt, egy partnerhez egy sor tartozik. Ha egy partner szállító is és vevő is, akkor mindkét megfelelő attribútum értékbe bekerül az azonosítója. 25
Location tábla A földrajzi adatok tárolási helye. A Partner táblához annak Országkód mezőjén keresztül kapcsolódik, azt hierarchikusan tovább bontja. Percentage tábla A pénzmozgás valószínűségét tárolom itt, százalékos alakban. Az elemzés során fogom felhasználni, azt az egyes bizonylattípusokhoz rendelten. Direction tábla A pénz mozgásának irányát határozza meg, eszerint lehet kimenő és bejövő. Type tábla A rendszerben használt bizonylatok típusát mentem el itt. Az alap listát a SharePoint-os felületen bővítheti a felhasználó. Fact tábla Ez a tábla fog az OLAP kocka ténytáblájaként szolgálni. Itt tárolom el az egyes pénzügyi rekordok tényadatait, illetve a dimenziótáblák azonosítóit idegen kulcsként. Minden egyes rekordban eltárolom az összeget, és az esetleges hátralékot, valamint a teljesítés határidejét és a teljesítés dátumát. A táblák részletes jellemzését a függelék D pontja tartalmazza.
4.2. Áttöltő modul elkészítése Navision-höz Az áttöltő modul elkészítését a Microsoft Dynamics Navision vonatkozó adattábláinak szerkezetének megismerése, tanulmányozása előzte meg.
4.2.1. A NAV vonatkozó tábláinak megismerése Amint azt néhány gondolattal már felvázoltam az elméleti összefoglalóban is, a Navision-ben a partnerek adatait nem egy központi partner táblában találhatjuk meg, hanem kettéosztva a Vevők (Customer) és a Szállítók (Vendor) táblákban. Itt található meg a partner tábla szinten egyedi azonosító száma, mellyel az adatbázison belül hivatkozhatunk rá. Ezen felül az itt tárolt adatok közül a partner nevét és számlázási címét fogom felhasználni az áttöltéskor. A további dimenziók értelmezési tartományát nem az ERP-ből töltöm fel, ezért azokról majd az ETL-nél szólok részletesebben. A ténytábla adatai viszont főként a vállalatirányítási rendszerből kerülnek ki. A Navision esetében egészen pontosan öt csoportba érdemes sorolni az áttöltő folyamokat: szál-
26
lítói, vevői, banki főkönyvi adatok, valamint a még le nem zárult szállítói és vevői folyamatok bizonylatai. A szállítói számlák fej adatai a szállítói főkönyvi táblában (Vendor Ledger Entry), illetve a hozzá tartozó sor táblában (Detailed Vendor Ledg_ Entry) találhatók meg. A fej táblában találhatók meg rendre a számla azonosító száma, a partner azonosítója, a számlázási adatai, a fizetési határidő, a könyvelés ideje, stb., míg a sor táblában az egyes tételsorok részletes adatai találhatók meg. Ezekre nekem csak a számla végösszegének és hátralékának meghatározásában van szükségem. Az előjel konvencióra fontos figyelni, ugyanis a Navision-ben a szállítói számla összege pozitív előjellel szerepel, azonban a direkt cash flow szempontjából az kimenő, azaz negatív pénzáramlás. A szállítói számlákkal analóg módon tárolja a Navision a vevői számlákat is. Ebben az esetben is hierarchikus az adattáblák felépítése, vagyis létezik vevői számlafej (Cust_ Ledger Entry) és vevői számlasor (Detailed Cust_ Ledg_ Entry) tábla is. A vevő táblák egyéb oszlopai mind nevükben, mind pedig felhasználásukban megegyeznek a szállítói főkönyvi táblánál ismertetettekkel, az előjel megfordítását leszámítva persze, hiszen a vevői számla végösszege bevételt jelez előre a pénzmozgásban. A banki főkönyvi adatok tárolása azonban némiképp eltér az eddig ismertetettektől. Azokat ugyanis csak egy táblában, a Bank Account Ledger Entry táblában tárolja el a Navision. Természetesen a bankszámla kivonat esetében nincs értelme összegről beszélni, és a hátralék is új értelmet nyer általa. E két mező értéke azonos, mindkettőben a bankszámla egyenlege található. A bankszámlához nem tartoznak sor adatok sem. A szállítói ajánlatok és rendelések a Purchase Header fejtáblában és a Purchase Line sortáblában találhatók meg. A táblákban tárolt adatok teljesen hasonlóak a főkönyvi táblákban tároltakhoz, azonban fontos megjegyezni, hogy az SBO-val szemben a NAV egy táblában tárolja a rendeléseket és az ajánlatokat is. Ezért egy extra attribútumot használ ezek megkülönböztetésére Document Type néven. Mivel az ajánlat és a rendelés közvetlenül nem jelent még fizetési kötelezettséget, ezért természetesen a hátralék öszszege nulla. A vevői ajánlatok, rendelések tárolása a Sales Header és Purchase Line táblákban történik, teljesen azonos módon a hasonló szállítói bizonylatok adataival. Ezen áttekintés után úgy érzem, elegendő információval rendelkezem az áttöltő modul megvalósításához.
27
4.2.2. Az ETL megtervezése, implementálása SQL Server Business Intelligence Development Studio-val készítettem az áttöltőt, melynek state chart diagramját a 12. ábra mutatja. A Business Intelligence Development Studio egy Visual Studio alapú szerkesztő eszköz, melyet a Microsoft SQL Serverrel együtt telepíthetünk. Gyors és hatékony megoldást jelent áttöltő modulok létrehozására, az adat transzformációk könnyen megvalósíthatók a grafikus vezérlő felületen. Az áttöltés az adattárház inicializálásával kezdődik, az Init DWH.sql fájl futtatásával, amely ellenőrzi, a meta adatbázisban az adattárház tábláinak létezését és felkészíti azokat a teljes mentés megkezdésére. Amennyiben valamely tábla még nem létezne, akkor az ezt követően fog létrejönni a már ismertetett D függelékben leírtaknak megfelelően. A táblák létrehozásának sorrendje tetszőleges, az adatokkal való feltöltésük azonban nem az, ezért én annak sorrendjében hozom létre a táblatípusokat is: először a dimenzió és ideiglenes táblákat, végül a ténytáblát. Az elemzés paramétereit SharePoint SQL példányában tárolom, így is törekedve a komponens alapú tervezés betartására, ezért azokat is át kell töltenem a szakdolgozatom során használt adatbázisba. A helyes feltöltéshez először a dimenzió táblákat kell feltölteni adattal, majd csak ezután lehet feltölteni a ténytáblát is, abban ugyanis már vizsgálni kell minden egyes rekord esetén a külső kulcsok létezését is. Legvégül történik meg az OLAP kocka felépítése. Az egyes dimenzió táblák áttöltő moduljai alapvetően egyszerűek, a kisméretű értelmezési tartomány miatt, valamint a tesztadatok egyezősége miatt némelyik táblát elegendő az önálló laboratóriumi munkámból átmásolni. A legbonyolultabb folyamat a Partner tábla feltöltése. A Navision-ben két táblában találhatók meg a partnerek adatai, ahogy arról már szóltam: a vevői és a szállítói törzsadatok között, valamint ehhez hozzájönnek még azon partnerek, akiket SharePointban vettünk fel. Azon partnereknek, akik szállítóként és vevőként is kapcsolatban állnak a vizsgált vállalattal, mindkét NAV táblában szerepelnek, eltérő azonosítóval. Tehát az mentén nincs lehetőség az összecsatolásukra, így valamely sokkal bizonytalanabb adat mentén tudjuk elvégezni az összefogásukat, mint a címük vagy a nevük, ez azonban sajnos lehetőséget ad arra, hogy a hibásan begépelt adatok torzítják egyes részleteit az elemzésnek. A torz dimenzión belül azonban nem jelentkezik releváns szórás a probléma megoldásában, kezelésére fejlett adattisztítással van lehetőség.
28
sd ETL
Adattárház inicializálása
Dimenzió táblák létrehozása DimPercentage
DimTime
DimPartner
DimDocumentNo_
DimDirection
DimType
Bizonylat típus
DimERPEnteryId
Dimenziótáblák feltöltése Földraj zi tábla másolása
Időtábla feltöltése
Partner tábla feltöltése
TÍpus tábla feltöltése
Irány megadása
Ténytábla és a SharePoint temp létrehozása
SharePoint temp feltöltése
Ténytábla feltöltése Vev ői adatok betöltése
Rendelések betöltése
Szállítói adatok áttöltése
Aj ánlatok betöltése
Banki főkönyv i adatok betöltése
SharePoint adatok betöltése
Percentage tábla feltöltése
Fact tábla frissítése a paraméterek mentén
Kocka felépítése
12. ábra – Az elkészített áttöltő modul state-chart diagramja
29
A dimenziótáblák feltöltése után ideiglenesen átmásoltam a SharePoint SQL szerver példányában tárolt tényadatokat és elemzési paramétereket a szakdolgozat adatbázisába. Erre a külső kulcsok és számított értékek SQL Server Business Intelligence Development Studio-ban való könnyebb összekapcsolása miatt van szükség. A temporális táblák feltöltése után a tény tábla feltöltése következik. Itt a cash flow elemzéshez szükséges adatokon kívül – mint már említettem - a dimenziók kulcsait is be kell tölteni. A tényadatok a bizonylat összege és a hátralék. Ezen adatokat nagyon hasonló módon érem el a főkönyvi illetve pénzügyi adattáblákból: az egyes sor táblákból összesummázom az egy bizonylathoz tartozó sorösszegeket és hátralékokat, majd azokat a bizonylat azonosítón keresztül csatolom a dimenzióadatokhoz: Bizonylatösszeg kiszámítása (Természetesen a tábla- és attribútum név változhat): SELECT SUM([Amount (LCY)]) AS "Amount (LCY)", [Cust_ Ledger Entry No_], [Document No_] FROM [CRONUS Rt_$Detailed Cust_ Ledg_ Entry] GROUP BY [Cust_ Ledger Entry No_], [Document No_]
Hátralék kiszámítása: SELECT [Cust_ Ledger Entry No_], SUM([Debit Amount (LCY)] - [Credit Amount (LCY)]) AS "Remainder (LCY)" FROM [CRONUS Rt_$Detailed Cust_ Ledg_ Entry] GROUP BY [Cust_ Ledger Entry No_]
Ezeket követően kerül meghatározásra a bizonylat típusa, majd annak és összegének előjele alapján a hozzá tartozó pénzmozgás iránya. Legvégül pedig a dimenzióadatokat lecserélem a dimenziótáblában hozzájuk rendelt kulcsokhoz. A százalék tábla, noha dimenziótábla, a ténytábla feltöltése után adom hozzá az adatokat, azok megállapításához több más tábla értékére is szükség van. Természetesen ezeket ki lehetne nyerni a ténytábla használata nélkül is, azonban ahhoz rengeteg join műveletre lenne szükség, amely rendkívül erőforrás igényes. A ténytáblában viszont minden adat rendelkezésre áll egy rekordhoz, amire szükségünk van, ezért azok így megtakaríthatók. A ténytábla feltöltése után végzem el abban az egyes paraméterek szerinti módosításokat is szintén az előbbi gondolatmenet okán. Túl a paraméterek állításán, még néhány módosítást el kellett végeznem a tény táblában azért, hogy a demó adatbázis értelmesen használható legyen. A Dynamics NAV általam
30
használt példányában a munkadátum állandóan 2008. január 24, így csak ennek környékén enged bizonylatot felvenni. Ennek a problémának a kezelése érdekében létrehoztam egy beállítható paramétert, amely az elemzésben használni kívánt „mai nap” dátuma. Igyekeztem a frissítés során jól elkülöníteni azon parancsokat, amelyekre a tesztrendszer miatt van csak szükség, illetve az általánosan használandókat. A frissítő SQL parancsot (updateFact.sql) terjedelmi okokból itt nem részletezem, azonban a lényeges részéről készítettem egy pszeudo kódot, melyben zöld kiemeléssel megjelöltem azon elemeket, amelyeket a demó adatok követelnek meg, éles rendszerben azonban nem kellenek bele:
ha a határidő null // bankszámla kivonat határidő = könyvelési dátum ha a határidő > ma ha bankszámla kivonat // jövőbeli adat töröl egyébként ha a hátralék null // worst case megoldás hátralék = összeg ha van hátralék // súlyozni a valószínűséggel hátralék = összeg * valószínűség ha bejövő tervezett idő = határidő + bejövő_tolerancia ha kimenő tervezett idő = határidő + kimenő_tolerancia különben ha nem bankszámla kivonat // már szerepel bankszámlán töröl
A ténytáblán elvégzett szükséges változtatások után nincs más hátra, mint az áttöltés végeztével az OLAP kocka felépítése. Ez opcionális, a felhasználó dönthet a halasztásáról és csupán az adatok áttöltéséről indításkor.
4.3. Javaslat egy lehetséges ETL-re SBO-hoz Annak a célkitűzésnek, hogy az alkalmazásom független legyen a használandó vállalatirányítási rendszertől azt gondolom, kellőképpen eleget tettem. Ezt ebben a fejezetben prezentálni is szeretném. Az irodalomkutatás során úgy gondolom kellőképpen részletesen bemutattam az SBO szükséges tábláinak a struktúráját ahhoz, hogy egy közel működőképes ETL váz állhas-
31
son össze a fejemben. Ezen fejezetben egy az általam használandó megoldást ismertetek. Az ETL felépítésén úgy gondolom, hogy szükségtelen változtatni csupán azért, mert másik vállalatirányítási rendszert alkalmazok jelen esetben, sőt mivel mindkét ERP ugyanolyan adatbázis kezelőt alkalmaz, lehetőségünk nyílik az áttöltő fejlesztésének meggyorsítására a már meglévő modulok módosításával. Azok a részek, amik viszont mindenképp változtatást igényelnek, az egyes adat kinyerő funkciók, azaz az egyes dimenzió táblák és a ténytábla feltöltése a kellő adattal. A partner tábla feltöltését a NAV-os ETL-hez hasonlóan tervezem kialakítani, azzal a különbséggel, hogy most a partner adatok egyetlen táblában halálhatóak, a Business Partner (OCRD) táblában. A konkrét áttöltő folyamatban ismételten össze kellene fésülni valamilyen módon azon partnereket, akik szállítók és vevők is. Sajnos most sincs alkalom közvetlenül összekapcsolni azokat egy azonosító számmal, mert a CARD_CODE mező, ami a partner azonosítót tartalmazza, egyedi kulcs, így a két bejegyzésnél biztosan különböző, emiatt megint valamilyen alternatív módon kell megteremteni köztük a kapcsolatot, mint ügyfél név vagy számlázási cím alapján. Ha egy partnerhez csupán egy sor tartozik, akkor annak megfelelően beírom az azonosítóját a partner tábla megfelelő oszlopába (Szállító - ERPVendorId, Vevő – ERPCustomerId, Érdeklődő – SharePointId), illetve feltöltöm a sort a többi adattal, ezek helye úgy gondolom, hogy nem szorul különösebb magyarázatra. A többi dimenzió tábla feltöltése során nem jelentkezhet komolyabb probléma, hiszen azok többnyire az ERP-től független forrásból kapják meg a rekordhalmazukat. Ha kész a dimenziótáblák feltöltése, akkor megint következhet a temporális majd a ténytábláé. A temporális táblák feltöltése megint csak független a vállalatirányítási rendszertől, ezért abban az esetben nem igényel módosítást, ha a felhasználó az elemzési paraméterek egy részét továbbra is SharePoint környezetben viszi fel. Ezen azonban természetesen lehet változtatni, például azokat lehet egy fájlból beolvasni, vagy bármely más webes form-ból, ekkor természetesen módosítani kell az áttöltőjüket is. Szélsőséges esetben az elemzési adatok beleégethetők az alkalmazásba, ez azonban mindenképp kerülendő! Természetesen az SAP business One esetében is minden összeg kétféleképp kerül tárolásra a rendszerben. A bizonylat eredeti értékével és pénznemével, valamint a rendszer-
32
ben alapértelmezettként beállított pénznemre átváltott összeg formájában. Erre fontos odafigyelni, hogy a konzisztencia el ne vesszen. SBO környezetben a ténytábla kialakításánál az adatok áttöltése szintén nem válik el élesen a Navision-nél ismertetettől. Vevői számlák: A vevő számlát SBO-ban kimenő számlának, vagy A/R Invoice-nak nevezik. Mint arról az elméleti bevezetőben már szóltam néhány mondatban, az egyes számlákat itt is két külön táblában tárolja a rendszer az adatbázisban: külön a fejadatokat és a sor adatokat. A fejtáblából ki lehet nyerni a vevő kódját (CardCode), a fizetési határidőt (DueToDate), a számla típusát (nyitott/zárt) (InvntSttus) és a befizetett összeget (PaidSum). A számla végösszegét hasonlóan a Navision-ös megoldáshoz a számla sorokból lehet kinyerni. A számla végösszegének meghatározásához össze kell adni a sorainak az összegét, melyet az INV1 táblából nyerhetünk ki. Célszerű ezt az adatbázisból való kiolvasáskor az SQL SUM függvényével célszerű megtenni, nem pedig később, így az indexelés okozta teljesítménynövekedést még ki tudjuk használni. Az adattárházban azonban nem a fizetett összeget tartjuk nyilván, hanem a hátralékot. Ez egy fontos különbség a két ERP között, melyre oda kell figyelni az áttöltő megvalósításakor. Ennek következtében minden egyes számlához meg kell még határozni a hátralékot is, ami nem más, mint a számla összege mínusz a befizetett összeg. Végezetül nem maradt más dolgunk, mint a ténytábla egyes soraihoz hozzá kell csatolni a dimenzió táblák megfelelő értékeit, erre viszont most nem térnék ki, azt már ismertettem a Navision-ös ETL-nél. Szállítói számlák (A/P Invoice): A bejövő számlák, account payable (A/P) jelzővel ellátott táblákban találhatók meg, ha adatbázis szinten kezeljük az SBO-t. A ténytábla feltöltéséhez a számla fejének adatait az OPCH táblából olvashatjuk ki. A DocNum attribútum a számla azonosítója a rendszerben, erre biztosan szükségünk lesz, hiszen ennek segítségével tudjuk majd társítani a számlafejet a számlasorokkal. Ahogy a vevői számla esetében, úgy most is a CardCode mező tartalmazza a szállító egyedi kulcsát, idegen kulcsként, így arra is szükség lesz a dimenzió tábla bekapcsolásához. A számla határidejét a DocDueDate mező tartalmazza, az elemzés során ez fontos szerepet játszik, ezért át kell vennünk az adatbázisból. A számla rendszerbeli státuszát szintén a mező InvntSttus jelzi. A számla ellenértékeként kifizetett összeget a PaidSum illetve a PaidSumFc és PaidSumSc attribútumok tárolják. A számla végöszszegét a soradatok táblájából (PCH1) kell felgöngyölni, (a visszakövethetőség, lefúrha33
tóság mellett) itt vesszük hasznát a számla azonosítójának. Ezután minden adat a rendelkezésünkre áll a ténytábla feltöltéséhez és a dimenziótáblák becsatolásához. Fontosnak tartom megjelezni, hogy a pénzmozgás irányának meghatározásánál oda kell figyelni arra, hogy a számla összegének adatbázisban tárolt előjele utal-e a pénzmozgás irányára. Vevői rendelés: A vevői rendelés fejadatai a Sales Order (ORDR) táblában találhatók meg. A számlákkal teljesen analóg módon használjuk fel a DocNum mezőt a vevői rendelések soradataival (RDR1 tábla) való összekapcsoláshoz. A CardCode mezőt a partner meghatározásához, az InvntSttus-t pedig a bizonylat státuszának megállapításához. A rendelési bizonylat is rendelkezik egy tervezett teljesítési dátummal, amit szintén a DocDueDate attribútum tárol, az eddigiekhez hasonló módon. A PaidSum mezők itt is megtalálhatók, és a rendelés teljes összegét ismét a sortáblából (Sales Order Rows – RDR1) felgöngyölítéssel állíthatjuk elő. Azonban mivel csak rendelésről beszélünk, ezért fontos vizsgálni, hogy az milyen dátummal szerepel, illetve, milyen státusszal: nem archiválta-e azt egy hivatkozó számla? Amennyiben a hátralék nulla, akkor nem érdemes foglalkozni a rendeléssel, hiszen ahhoz vagy nem tartozik soradat, vagy pedig már ki van egyenlítve a követelésünk és készült róla számla. Ha aktív a rendelés, akkor pedig az elemzés során az összege az elemző által beállított valószínűséggel fog realizálódni a vállalat cash flow-jában. Vevői ajánlat: A vevői ajánlatok tárolási logikája teljesen megegyezik az eddig ismertetettekkel, természetesen azonban önálló táblákban találhatók meg az ajánlatok adatai. Ezek a Sales Quotation (OQUT) és a Sales Quotation Rows (QUT1) táblák. Vevői ajánlat esetén a legbizonytalanabb annak a direkt cash flow-ra gyakorolt hatása. Egyrészt bizonytalan az ajánlat elfogadása, másrészt az esetlegesen az alapján létrejövő megrendelés teljesítése. Ezen adatokat lehetősége van az elemzőnek mind időben, mind összegében különböző tartományokban vizsgálni. Az ETL továbbfejlesztésével bevonhatók a CRM modul adatai, illetve egyéb statisztikák is készíthetők a történelmi adatok alapján a vevő várható viselkedéséről, ezáltal az elemzés paramétereinek szórása tovább csökkenthető. Szállítói rendelés: A szállítói rendelés teljesen azonosan kezelendő a vevői rendeléssel, természetesen itt a szállítói táblákat kell használni az áttöltés során, azaz a Purchase Order (OPOR) és 34
Purchase Order Rows (POR1) táblákat kell adatforrásként definiálni. Az áttöltés során használandó attribútumok halmazát teljesen lefedik a vevői rendelésnél ismertetettek. Az elemzés során azonban tágabb értelemben is érdekes lehet a paraméterezése: egyrészt, amennyiben még nem zárult le a rendelés, akkor van még lehetőség kedvezőbb ár kialkudására, másrészt pedig a kifizetési időpont a határidőig (illetve szélsőséges esetben azon túl is) mozgatható. Szállítói ajánlat: A szállítói adatokat a 8.2-es verziótól van csak lehetőség az SBO-ban kezelni. Az általam használt referencia adatbázis leírás sajnos egy 2005-ös verzióhoz szól, a 8.2-es pedig 2011 augusztusában mutatkozott be. Azonban látatlanban is valószínűsíthető, hogy az áttöltés menete elég analóg az előzőekben ismertetetthez. Banki bizonylatok adatai: Mint az irodalmi bevezetőben már említettem, az SBO a Navision-től eltérő módon külön táblában kezeli a kimenő (OVPM illetve VPM1…3) és a bejövő (ORCT illetve RCT1…3) pénzügyi bizonylatokat. Ezen irányokon belül további három kategóriára bontja azokat a pénzmozgáshoz kötődő bizonylat fajtája szerint: számla (VMP1 ill. RCT1) csekk (VMP2 ill. RCT2) utalvány (VMP3 ill. RCT3). A pénzügyi bizonylatok az összes többi eddigi bizonylathoz hasonlóan szintén hierarchikus felépítésű, ahol az OVPM és ORCT táblák tartalmazzák a fejadatokat, a VPMx és RCTx táblák pedig a sor adatokat. A pénzügyi bizonylatoknál csakúgy, mint az előzőekben a dimenziótáblákhoz történő csatolás a már ismertetett mezőkön keresztül lehetséges. A banki költségek kezelésére célszerű felvenni az egyéb partnerek közé a bankot, így szemléletesebb elemzést kaphatunk. Mint a fentiekből is jól látszik, a Microsoft Dynamics NAV és az SAP Business Onenál is a bizonylatok adatai típus szerint különböző táblákban vannak eltárolva, azonban azok szerkezete részben üzleti kívánalmak miatt, részben a könnyű fejleszthetés érdekében, a hasonló adatok egyszerű mozgatása miatt homogén szerkezetű haladnak végig a vállalati folyamatok dokumentálása során. A Navision-höz tartozó ETL esetében ismertetett eljárásokat természetesen az SBO esetén is végre kell hajtani az OLAP kocka felépítése előtt, azonban azokat itt nem részletezném, ugyanis az előző fejezet végén megtekinthetők. 35
4.4. SharePoint bekapcsolása a rendszerbe A Microsoft SharePoint egy a Microsoft által a csoportmunka támogatására, fájlok megosztására és weboldalak közzétételére kifejlesztett szoftvercsalád, amely szorosan integrált a Microsoft Office program csomaggal, ám annak megléte nem előfeltétele a használatának. A SharePoint-webhelyek funkcionálisan ASP.NET 2.0 webes alkalmazások, melyeket az IIS használatával publikálunk, back end-ként az SQL Server adatbázist használva az adatok tárolására. Minden webhely tartalom adatai, mint például a dokumentumtárak és listák, egy SQL-adatbázisban vannak tárolva, melynek a neve alapértelmezés szerint „WSS_Content". Az elemzéshez felhasznált paramétereket a felhasználónak SharePointon keresztül van lehetősége megadni, illetve egyéb pénzügyi adatokat is felvehet egy SharePoint táblába, mely szintén bekerül az elemezendő tényadatok közé. Az adatok tárolásuk során listákba vannak szervezve, ezek leginkább a .NET rendszer data grid eleméhez hasonlóak, vagyis a felhasználó adatot vihet fel, és tárolhat bennük. Ezen listákat egyszerűen létrehozhatjuk és kezelhetjük a webes admin felületen keresztül, ekkor azonban nincs beleszólásunk az adatok tárolásába. A felhasználói adatok a már említett SQL adatbázisban az AllLists és az AllUserData táblákban tárolódnak el. Az AllLists táblában érhetőek el a lista fejadatai, illetve itt található az azonosítója, amelyen keresztül hozzá csatolhatók a megfelelő soradatok. Ez a fajta megoldás azonban teljesen ellentmond az eddig hangsúlyozott komponens alapú programfejlesztéssel, hiszen nemcsak hogy az összes általam használni kívánt adat beépül a rendszer mélyén lévő adattáblákba, de mindezt úgy teszi, hogy ott explicite semmi sem különbözteti meg bármely másik SharePointban tárolt adatelemtől. Ehhez a tárolási sémához való alkalmazkodás túl sok kompromisszumot és áldozatot igényelt volna, így más megoldást kerestem, ahol az adataimat szeparáltan tudom tárolni. Választásom végül a SharePoint adatait is tároló adatbázis példányra esett, ott azonban külön adatbázist hoztam létre a SharePointban tárolt, a feladatomban kezelt adatnak. A problémát tehát fordított sorrendben igyekeztem megoldani, azaz nem a webes lista a lényeges, hanem az elemek tárolása meghatározott adatbázis sémában, majd az onnét való elérésük, módosításuk SharePointból. Ezen cél megvalósítására a külső listák alkalmasak SharePointban. A külső lista létrehozásakor meg kell adni egy külső tartalmat, amit a webes felületen keresztül tudunk menedzselni.
36
Ahhoz azonban, hogy az adatbázist el tudjam érni SharePoint felületről, ahhoz létre kellett hozni egy Business Data Connectivity Model-t, ami átjáróként funkcionál az SQL Server és a Lista objektum között. cmp Business Data Connectiv ity Model SharePoint
SharePoint Lista
SQL Serv er
WSS_Content.AllLists
Kezelő metódusok Külső lista
Business Data Connectiv ity Model
SharePoint adatbázis
SQL utasítások Szakdoli adatbázis
13. ábra – A BDCM elhelyezkedése a SharePoint és az SQL szerver között
A Business Data Connectivity Model objektumot C# nyelven fejlesztettem le, ennek menetéről lesz szó a fejezet hátralévő részében. A felhasználónak egyes dimenzió adatok és további tényadatok felvitelére, valamint az elemzés paramétereinek beállítására van lehetősége a SharePointban. Részletesen csak az új törzsadat felvitelét segítő kapcsolat modell létrehozását mutatom be, a többié ezzel teljesen analóg, legfeljebb azokban ne került az itt használt minden funkció implementálásra. A SharePoint metódusok implementálásához ajánlott eszköz a Visual Studio. Itt készítettem el a Business Data Connectivity Model-t, amelyben azok megvalósításra kerülnek. Első lépésként létrehoztam egy új projektet a BDC modell osztályok tárolására. A projekt közvetlenül csak az SQL szerver felé tartalmaz kapcsolódó objektumokat, a SharePoint környezetnek pedig meghatározott interfészeket kínál fel a műveletek elvégzéséhez. Az SQL szerverhez való kapcsolódáshoz a projekthez hozzá kell adnunk egy új objektumot, melyet a LINQ to SQL Classes objektum template segítségével vettem fel, és azt konfiguráltam fel a megfelelő működéshez szükséges értékekkel. A template létrehozása után a projekthez létrejön egy DBML objektum, amiben osztályszerűen el lehet helyezni a használni kívánt SQL objektumokat. Jelen esetben a Server Explorer-ben csatlakoztam a lokális SharePoint SQL szerverhez, majd annak megfelelő tábláját egyszerűen felhúztam a munkaterületre. Ezt követően a Visual Studio létrehoz egy objek37
tumot, melynek a kiválasztott adatbázis táblával megegyező nevű és típusú attribútumai vannak. A későbbiekben természetesen minden olyan adatbázis táblához új objektumot kell létrehozni, amit el kívánunk érni az üzleti modellünkből. Az SQL objektum létrehozása után a BDC modellhez hozzá kell adnunk azt az entitást, ami az SQL objektumhoz kapcsolódik, ugyanakkor rendelkezik azokkal a metódusokkal, amelyeket elér és meghív a SharePoint. A modell entitáshoz az SQL adatkapcsoló osztállyal történő összekapcsoláshoz fel kell venni egy azonosítót, mely egy logikai azonosító lesz a modell fejlesztése során, azonban típusának meg kell egyeznie a tábla hivatkozott attribútumának típusával.
4.4.1. Specific Finder metódus létrehozása A specific finder metódus lényegében egy adatrekord megjelenítésére képes. Egy bemenete van, a kijelölt adatelem azonosítója, visszatérési értéke pedig a kulcsértékhez tartozó objektum. A modell entitáshoz a BDC Method Details fülön keresztül tudjuk felvenni, a legördülő listában a ReadItem menüpontot kiválasztva. Ez után a Method Details fülön megjelennek a metódus paraméterei és visszatérési értéke is. Ezeket testre kell szabni a megfelelő működés érdekében. A testre szabást a BDC Explorer fülön tudjuk elvégezni. Az itt megjelenő fastruktúrát kibontva eljuthatunk az automatikusan generált paraméterekhez. A bemenő paraméter a sor azonosítója az adatbázisban, ezért a TypeName property-t egész típusúra kell állítani, mint az adatbázis azonosítóját, valamint az Identifier property-t is be kell állítani az entitáshoz létrehozott logikai azonosítóra. Ezzel teremtjük meg a kapcsolatot a modell entitás és az SQL objektum között. A visszatérési érték beállítása ehhez hasonlóan történik. Annál is be kell állítani az automatikusan generált UserLine objektum TypeName property-jét SharePointUserLinera. Ezzel definiáltuk a visszatérési érték típusát, azonban ahhoz, hogy azt fel tudja dolgozni a SharePoint, hozzá kell az SQL objektum egyes attribútumait is. Ezt a generált objektumra jobb gombbal rákattintva új típus leíró hozzáadásával tehetjük meg. Az új típusleíró nevét és típusát a hozzá kapcsolandó SQL objektumváltozó értékére kell beállítani. Miután felvettük és konfiguráltuk az összes attribútumot, rendelkezésünkre áll minden objektum a metódus megvalósításához. Az entitás diagramján a metódus nevére duplán kattintva megnyílik az a C# service fájl, amiben a metódus tényleges törzsét implementálni tudjuk. Itt a függvénycsonkot ki kell
38
egészíteni az adatbázishoz való csatlakozással valamint az azon végzendő tényleges művelettel, aminek kódja alább látható: public static SharePointTables_UserLine ReadItem(int userLineId) { // Kapcsolódás az adatbázis szerverhez sp_szakdoliDataContext dataContext = new sp_szakdoliDataContext( "Data Source=EX600\\sharepoint;" + "Initial Catalog=sp_szakdoli;" + "Integrated Security=True" ); // Kiválasztott objektum adatainak lekérdezése SharePointTables_UserLine UserLine = (from userLineTable in dataContext.SharePointTables_UserLines.AsEnumerable().Take(20) where userLineTable.Id == userLineId select userLineTable).Single(); return UserLine; }
4.4.2. Finder metódus létrehozása Az előzőekben láthattuk, miként lehet egy elemet kiolvasni az adatbázisból és átadni azt megjelenítésre a SharePointban, azonban a gyakorlatban ez természetesen nem elégséges. Több elem listázására való a Finder metódus, amelyet a már ismertetett BDC Method Details fülön a legördülő menüben a ReadList opciót kiválasztva lehet hozzáadni az entitáshoz. A függvénycsonk és a visszatérési érték természetesen ebben az esetben is legenerálódik, azonban bemenő paraméter most nem, mert arra nincs is szükség a teljes tábla olvasásához. Az automatikusan generált visszatérési értéket testre kell ismételten szabnunk, azonban most már a UserLine lista objektumot kiválasztva a Visual Studio már önmaga létrehozza annak hierarchikus kialakítását. (A lista UserLine elemekből áll, amelyek egy SQL rekordra hivatkoznak, egy UserLine objektum pedig valójában egy struktúra, amelynek adattagjaiban a rekord attribútumai vannak.) Tehát nincs más dolgunk, mind megírni a függvény törzsét.
public static IEnumerable<SharePointTables_UserLine> ReadList() { // Kapcsolódás az adatbázis szerverhez sp_szakdoliDataContext dataContext = new sp_szakdoliDataContext( "Data Source=EX600\\sharepoint;" + "Initial Catalog=sp_szakdoli;" + "Integrated Security=True" ); // A lista lekérdezése IEnumerable<SharePointTables_UserLine> UserLineList = from userLineTable in dataContext.SharePointTables_UserLines.Take(20)
39
select userLineTable; return UserLineList; }
Ezen két metódus implementálása után már lehetőség van Debug módban kipróbálni, hogy SharePointból elérhető-e a BDC modell külső listaként, illetve az az elvárt működést valósítja-e meg. Fontos, hogy a későbbi újrafordítások során a SharePoint által használt példány nem frissül automatikusan, azt explicite frissítenünk kell.
4.4.3. Creator metódus létrehozása A Creator metódus átveszi a SharePoint felületen beadott adatokat, majd azokat eltárolja az adatbázisban. Visszatérési értéke a létrehozott új objektum. A BDC Model Details fülön az új elem létrehozását jelentő Create létrehozását kiválasztva már minden paraméter automatikusan legenerálódik, azok testre szabást nem igényelnek, csupán a függvénytörzset kell implementálni:
public static SharePointTables_UserLine Create( SharePointTables_UserLine newUserLine) { // Kapcsolódás az adatbázis szerverhez sp_szakdoliDataContext dataContext = new sp_szakdoliDataContext( "Data Source=EX600\\sharepoint;" + "Initial Catalog=sp_szakdoli;" + "Integrated Security=True" ); // Új objektum létrehozása SharePointTables_UserLine newUserLineItem = new SharePointTables_UserLine(); newUserLineItem.Name = newUserLine.Name; newUserLineItem.Amount__LCY_ = newUserLine.Amount__LCY_; newUserLineItem.Remainder__LCY_ = newUserLine.Remainder__LCY_; newUserLineItem.ClosedAtDate = newUserLine.ClosedAtDate; newUserLineItem.Document_No_ = newUserLine.Document_No_; newUserLineItem.Type = newUserLine.Type; // Az újonnan létrehozott objektum mentése az adatbázisba dataContext.SharePointTables_UserLines .InsertOnSubmit(newUserLineItem); dataContext.SubmitChanges(); return newUserLineItem; }
40
4.4.4. Delete metódus létrehozása A Delete metódus törli az adatbázisból az a sort, amelynek kulcsa megegyezik a paraméterül kapott értékkel. Az adatbázisból való törlést megvalósító metódus implementálása során ismét elegendő a legördülő listából kiválasztani a Delete opciót a megfelelő template betöltéséhez, melyben csupán a függvénytörzset kell kitölteni:
public static void Delete(int userLineId) { // Kapcsolódás az adatbázis szerverhez sp_szakdoliDataContext dataContext = new sp_szakdoliDataContext( "Data Source=EX600\\sharepoint;" + "Initial Catalog=sp_szakdoli;" + "Integrated Security=True" ); // Törlendő objektum megkeresése SharePointTables_UserLine UserLineToDelete = (from userLineTable in dataContext.SharePointTables_UserLines.AsEnumerable().Take(20) where userLineTable.Id == userLineId select userLineTable).Single(); // Rekord törlése az adatbázisból dataContext.SharePointTables_UserLines .DeleteOnSubmit(UserLineToDelete); dataContext.SubmitChanges(); }
4.4.5. Update metódus létrehozása Az Update metódus végzi el a kijelölt rekord frissítését a webes felületen megadott új értékek alapján, ezért bemenő paramétere az új attribútumok halmaza, valamint a módosítandó elem egyedi kulcsa. Az eddigiekhez képest valamelyest kevésbé kényeztet el minket a Visual Studio, ugyanis hiába generálja le a függvénytörzset és a modellezendő attribútumokat valamint állítja be rajtuk az Updater Field property-t, ugyanis a módosítandó objektum kulcsán nem állítja be a Pre-Updater Field property-t. E nélkül a módosítás során futás idejű hibával elszáll a programunk.
public static void Update(SharePointTables_UserLine userLine) { // Kapcsolódás az adatbázshoz sp_szakdoliDataContext dataContext = new sp_szakdoliDataContext( "Data Source=EX600\\sharepoint;" + "Initial Catalog=sp_szakdoli;" + "Integrated Security=True" ); // Módosítandó rekord beolvasása
41
var UserLineToUpdate = (from userLineTable in dataContext.SharePointTables_UserLines where userLineTable.Id == userLine.Id select userLineTable).Single(); // Attribútumok módosítása UserLineToUpdate.Name = userLine.Name; UserLineToUpdate.Amount__LCY_ = userLine.Amount__LCY_; UserLineToUpdate.Remainder__LCY_ = userLine.Remainder__LCY_; UserLineToUpdate.Document_No_ = userLine.Document_No_; UserLineToUpdate.Type = userLine.Type; UserLineToUpdate.ClosedAtDate = userLine.ClosedAtDate; // Változások visszamentése az adatbázisba dataContext.SubmitChanges(); }
A property módosítása után a függvénytörzset implementálva képes minden alapműveletet elvégezni a SharePoint a listánkon. Ehhez nincs más hátra, mint létrehozni SharePointban egy külső listát, amihez külső adattartalomként a most elkészített BDC modellt rendeljük hozzá.
4.5. Az elemzés paraméterezése Az előbb létrehozott Business Data Connectivity Model segítségével a külső lista már eléri és képes módosítani is az általam beállított SQL táblákat. Ezáltal a felhasználó a SharePoint felületén képes az elemzés paramétereinek vagy új pénzügyi adatoknak a felvételére. Például az alkalmazottak szerződésbe foglalt béreit előre fel lehet venni a meghatározott bérfizetési időpontokra. Az elemzés SharePoint webről vezérelhető paramétereit két csoportba osztottam: valószínűség típusú és tolerancia paraméterek. A felhasználónak lehetősége van minden bizonylattípushoz felvenni egy valószínűségi paramétert. Ez a pereméter fejezi ki, hogy az adott elemzés során mekkora valószínűséget rendelünk ahhoz az eseményhez, hogy a bizonylat által hivatkozott pénzmozgás ténylegesen realizálódni fog. Ezen paramétereket a SharePointTables_Probability táblában tárolom. Az áttöltés futásakor használja fel az alkalmazás e paramétert, a bizonylat összegét súlyozza vele, majd egy új attribútumként felveszi a ténytáblába, az elemzés során pedig dimenzióként használható fel a valószínűség százalékos értéke.
42
14. ábra – Elemzési paraméter beállítása SharePointban
A tolerancia típusú paramétert az egyes pénzmozgások irányához rendeltem hozzá. Segítségével worst case szemlélet vihető bele a pénzáramlás időbeli pozícionálásába. Ezen paramétereket a SharePointTables_Tolerance táblában tárolom. Az áttöltés futásakor használja fel az alkalmazás őket: a pénzmozgás tervezett teljesülésének dátumát tolja ki a tolerancia intervallumnak megfelelően. Ezen csoportok mellett kényszerűségből létrehoztam egy harmadik paramétert is. A tesztkörnyezetben ugyanis minden adat 2007-es illetve 2008-as, ezért létrehoztam egy Today táblát is, amely egyetlen sorral rendelkezik, melyben az elemzés szerinti aktuális dátumot tárolja. Éles rendszerben ez természetesen eltávolítható és el is távolítandó. A strukturáltabb megjelenítés érdekében lehetőségünk van úgynevezett webpart-ok létrehozására SharePointban, ezekbe lényegében, mint konténerbe pakolhatjuk be az egyes weblapokat, listákat. Dolgozatom során én is összeraktam néhányat a grafikus felületen, így csoportosítva a paraméter listákat és az elemzési tényadatokat külön oldalakra.
4.6. Az OLAP kocka felépítése Miután az adattárház struktúráját kialakítottam, feltöltöttem azt az elemzéshez szükséges adatokkal, valamint lehetővé tettem az elemzés paramétereinek SharePointból való módosítását, a következő lépés az volt, hogy az adatokat olyan módon kell összefogni, hogy abból elemzéseket lehessen készíteni. Mivel elemzőeszköznek Excelt választot-
43
tam, ezért adott volt a lehetőség, hogy az OLAP kockát Analysis Service projektként hozom létre Visual Studio-ban, mert az mind Excel-ben, mind pedig az SQL Serveren támogatva van. Analysis projektben is először adatkapcsolatot kell létrehozni a forrásként használni kívánt adatbázissal. Ez hasonló módon történik az ETL-nél megismerthez, vagyis itt is létrejön egy konnektor objektum a projekt és az adatbázis között, amely meghatározza a projektben elérhető táblákat, objektumokat, illetve az azokhoz való kapcsolódás során használt felhasználói jogosultságot. Ezen kapcsoló objektumon keresztül ezután létrehozhatunk egy nézet objektumot a projekthez, melyben az adatbázis táblái között további modell szintű kapcsolatokat, új kulcsokat hozhatunk létre, új attribútumokat származtathatunk már meglévőkből. Mindez azonban csak virtuális változás, csak ebben a projektben jön létre, a tényleges adatbázis nem módosul. Ezen objektumok felhasználása során figyelmeztet is a fejlesztő környezet, hogy köztük nincs adatbázis szintű reláció, ezért használatuk teljesítmény csökkenést eredményez(het).
4.6.1. A kocka dimenzióinak kialakítása A virtuális adatmodell definiálása után ahhoz, hogy a kockát felépíthessük, először létre kell hozni a hozzá tartozó dimenziókat, illetve a dimenziókon belüli hierarchikus adatszerkezeteket ki kell alakítani. A dimenziókat varázslóval hoztam létre. Ehhez meg kellett adni, hogy melyik tábla alapján akarom elkészíteni, a tábla mely mérőszámait akarom felhasználni, illetve azt is, hogy melyik tábla adatain értelmezett az a dimenzió. Ez utóbbit minden dimenzió esetében az adattárház ténytáblájára állítottam. A varázsló végigkattintgatása után létrejövő űrlapon tudtam beállítani azt egyes dimenziókban megjelenő objektumokat, azok hierarchiáját, illetve az abban létrejövő adatelemek közti relációkat, függőségeket. Az attribútumok között két féle relációt lehet létrehozni: merevet (rigid) vagy flexibilist (flexible). Mint ahogy arról már a virtuális változóknál is szóltam, itt is elsősorban a teljesítményre van hatása a választásnak, a merev kapcsolatok mentén létrejövő indexek állandóak, azok nem igényelnek folyamatos karbantartást, így kedvező hatással vannak a teljesítményre.
44
Kialakított hierarchia
Attribútumok közti relációk
Kész dimenzió
1. táblázat – A dimenziók létrehozásának lépései
4.6.2. Az OLAP kocka felépítése A dimenziók elkészítése után futtatható a kockát elkészítő varázsló. Ebben lehetőségünk van beállítani, hogy mely táblákat kezelje ténytáblaként a rendszer, azokon milyen méréseket végezzen el, valamint mely dimenziókat rendelje hozzá. A varázsló lefuttatása után ismét egy űrlapot kapunk, melynek első fülén a táblák közti kapcsolatok láthatók 15. ábra szerint. A többi fülön van lehetőségünk beállítani új számított értékeket a kockára, a feliratokhoz, értékek nevéhez új fordításokat definiálni, illetve böngészni a kockában. Miután megépítettem a kockát, valamint beállítottam az aggregációk mentén az egyes mérések típusát, már csak telepíteni kellett a kockát egy adatbázis kiszolgálóra. Mivel mind a vállalatirányítási rendszer adatbázisa, mind az adattárház és ezekből kifolyólag a kocka is csupán tesztelési, fejlesztési céllal lett létrehozva, különösebb terhelésnek nincsenek kitéve, ezért úgy döntöttem, hogy ezeket egy MS SQL Server példány fogja kiszolgálni. A szűk keresztmetszet úgyis a CPU illetve a memória, nem pedig az adatbázis szerver, mivel minden egy gépen fut jelenleg.
45
15. ábra – Az OLAP kocka relációs sémája
4.7. Áttöltés vezérlése SharePointból Miután az OLAP kocka is elkészült, akár el is kezdhetnénk az elemzést, azonban a rendszer mindennapi használata kicsit nehézkes lenne még. Ha részletesen végiggondoljuk, hogy hol is tartunk jelenleg, illetve hogy is lehetne elvégezni egy komplett áttöltést, akkor hamar arra az eredményre jutunk, hogy a megoldás alkalmazása nehézkes és túlságosan centralizált. A paraméterek beállításához be kell jelentkeznünk a SharePoint webes felületére. Az áttöltés elvégzésekor két lehetőség közül választhatunk: vagy a Visual Studio-t megnyitva futtatjuk a projektet, vagy pedig a projekt könyvtárában található dtsx kiterjesztésű állományt futtatjuk le, amely azonban magasabb fokú hozzáértést igényel. Mindkettőnél komoly negatívum, hogy be kell jelentkezni hozzá a szerverre, illetve arra a gépre, amelyen a projekt fejlesztve lett. Az adattárház aktualizálása után ahhoz, hogy az elemzést a megfelelő kockán hajtsuk végre, azt aktualizálni kell, szinkronizálni kell az adattárház aktuális adataival. Ezt a kockát létrehozó projektet megnyitva a Deploy paranccsal tudjuk megtenni. Ez azonban szintén szerencsétlen, ugyanis működéséhez megfelelő jogosultsággal kell rendelkeznünk a szerveren, valamint el kell érnünk a projektet is.
46
Jelenleg tehát valamennyi műveletet csak a szerveren végezhetjük el, minden felhasználónak hozzáférést kellene biztosítani hozzá, ami rengeteg management erőforrást felemészt, valamint biztonsági szempontból is aggályos. Helyette a következő megoldás mellett döntöttem: Mivel a paraméterek beállításához be kell jelentkeznie a felhasználónak SharePointba, ezért fejlesztettem hozzá egy olyan űrlapot, amelyről lehetőségük van irányítani az adattárház frissítését és az OLAP kocka elemzések előtti újra építését is. sd Adattárház/Kocka frissítése SharePoint
SQL szerver
Windows szerver
Felhasználó
Frissítés()
refreshDWH(int) opt
exec ET LnoCube()
[0] execute ET LnoCube.dtsx()
[1]
exec ET LwithCube()
execute ET LwithCube.dtsx()
16. ábra – Az adattárház illetve az OLAP kocka frissítése
Ennek megvalósításához az Integration Service két tulajdonságának ismeretére van szükség: egyrészt arra, hogy lehetőségünk van úgynevezett Analysis Services Processing Task létrehozására, amellyel tudjuk kezelni a az ETL-hez hasonlóan a kocka építést, másrészt pedig, hogy az Integration Service fájlok (jelen esetben DTSX projekt) futtatását kezdeményezhetjük az SQL szerveren keresztül is. Legelső lépésként kiegészítettem a korábban létrehozott áttöltő modult. Hozzáadtam még egy feladatot, amelyet úgy paramétereztem fel, hogy az elkészített kocka projektjének fájljaiból azt frissíteni tudja, amennyiben logikailag nem változott, csupán az adathalmaz. (Ha a kockát meg kívánjuk változtatni, pl. új dimenziót hozzáadni, akkor ter-
47
mészetesen ahhoz módosítani kell a fájlokat, amihez azokhoz megfelelő jogosultságok szükségesek.) Az ETL kiegészítését követően generáltam egy újabb DTSX projektet, immáron a kocka frissítésére is képes projektből. Ezen fájlok szerveren történő közvetlen futtatása helyett úgy döntöttem, hogy létrehozok az SQL szerveren egy tárolt eljárást, mely a kapott paraméter értékének függvényében meghívja valamelyik DTSX fájlt. Így elegendő ellenőrizni, hogy a felhasználó rendelkezik-e megfelelő jogosultságokkal a távoli eljárás futtatásához. Nem kell rendelkeznie hozzáféréssel az alkalmazás szerverhez, ahhoz a kérés mappelve érkezik be, a SYSTEM felhasználótól. A távoli eljárást az alábbi SQL parancs segítségével hoztam létre: CREATE PROCEDURE refreshDWH @buildCube int = 0 AS BEGIN TRANSACTION IF (@buildCube = 0) BEGIN exec xp_cmdshell 'dtexec /f "c:\Users\Saatetyi\Documents\Visual Studio 2008\Projects\szakdoga_etl\szakdoga_etl\ ETLnoCube.dtsx"'; END ELSE IF (@buildCube = 1) BEGIN exec xp_cmdshell 'dtexec /f "c:\Users\Saatetyi\Documents\Visual Studio 2008\Projects\szakdoga_etl\szakdoga_etl\ ETLwithCube.dtsx"'; END ELSE BEGIN PRINT 'Hibás paraméter'; END COMMIT
Az adattárház illetve a kocka frissítéséhez tehát elegendő a csupán meghívni a refreshDWH tárolt eljárást, és az a háttérben elvégzi a szükséges műveleteket. Végezetül nem maradt más hátra, minthogy el kellett készítenem azt az ASP lapot, amin keresztül a felhasználó kiválaszthatja, hogy csak az adattárház frissítését szeretné most elvégezni, vagy pedig amellett az OLAP kockát is újra fel szeretné-e építeni. Ehhez létrehoztam két választógombot és egy gombot. A választógombok értékétől függően gombnyomásra 0 vagy 1 paraméterrel fog meghívódni a távoli eljárás, a futás eredmé48
nyét pedig egy szövegdobozban jelenítem meg a weblapon. Az adatbázis-kapcsolat kezelésén kívül a lekérdezés Time out-ját a szerveren alapértékként beállított 15 másodpercről megnöveltem 120 másodpercre, ugyanis jelen konfiguráció mellett átlagosan 3040 másodperc alatt fut le egy frissítés:
SqlConnection dbConn = new SqlConnection( "Data Source=EX600;" + "Initial Catalog=szakdoli;" + "Integrated Security=True" ); string comand = "EXEC refreshDWH " + RadioButtonList.SelectedIndex.ToString(); dbConn.Open(); SqlCommand cmd = new SqlCommand(comand, dbConn); cmd.CommandTimeout = 120; // Timeout megnövelése az alap 15 másodpercről SqlDataReader reader = cmd.ExecuteReader(); TextBoxResult.Visible = true; while (reader.Read()) { TextBoxResult.Text += reader["output"].ToString() + Environment.NewLine; } dbConn.Close();
Az alkalmazásban a fenti kód természetesen hibakezeléssel is el van látva, ide azonban azt terjedelmi okok miatt nem másoltam be.
4.8. Az elemzés elkészítése Az elemzés elkészítéséhez rendelékezésünkre áll számos lehetőség. Ezek közül most a Microsoft termékeire koncentrálva az Excelt fogom alkalmazni. Egyrészt annak könnyű kezelhetősége mellett nyújtott magas szintű matematikai kelléktára, másrészt pedig az SQL szerverrel és a SharePoint környezettel való integrálhatóságának magas támogatottsága miatt. Első lépésként az adattárházban az áttöltés során már előkészített adatokat onnét ki kell nyerni és az Excelbe be kell tölteni azokat. Ehhez a szalagon az Adatok / Egyéb adatforrásból / Az Analysis Services szolgáltatásból, menüpontot kiválasztva új kapcsolatot létesíthetünk az előzőekben létrehozott OLAP kockával. Ekkor megjelenik a Kimutatás mezőlista, melyből tetszőlegesen behúzogathatjuk a ténytáblában tárolt adatcsoportokat, illetve hozzájuk rendelhetünk különböző dimenziókat. Ez utóbbiak lehetnek egyszerűek és hierarchikusak is. Természetesen az elemzések elkészítéséhez a teljes Excel paletta a rendelkezésünkre áll. Jelen példában én a következő elemzést végeztem el: Sorcímkeként definiáltam a módosított fizetési határidőt (ClosedAtDate), oszlopcímkeként pedig a bizonylat típusát (Document type). Végül a táblázatot feltöltöm tényadattal, 49
jelen esetben az összeggel, mivel az tartalmazza a teljesítés százalékos valószínűsége szerinti változását. Az elkészült eredmény kimutatást végül a Kimutatás diagram menüpont segítségével ábrázolom.
17. ábra – A kimutatás eszköztár a készített kimutatással
18. ábra – A kimutatás alapján készített grafikon
Az elemzés elkészítése után nincs más dolgom hátra, mint konfigurálni a rendszert a megosztás támogatásához.
50
4.9. Közzététel SharePoint weben Az elemzést az esetek számottevő többségében nem az fogja felhasználni döntéshozásra, aki készítette, ezért azokat valahogy elérhetővé kell tenni a döntéshozók, vezetők felé. Erre a célra a SharePointot választottam, amelyben a 2007-es verziójától kezdődően megtalálható az úgynevezett Excel Services, ami egy Excel bázisú adat és modellkezelő szolgáltatás a szerveren. A 2010-es verzióban ez még tovább fejlődött, és kiegészült az Excel kliens egy böngésző alapú változatával is. Ezen változtatások révén egy platform független, web alapú táblázatkezelőt kapunk a SharePointban, ami képes kezelni a megosztott Excel munkafüzeteket. [9] A SharePointban való megosztáshoz először is létre kell hozni a SharePoint szerveren egy Document Library-t, ami kezeli a feltöltött dokumentumokat, illetve elérhető Excelből is mentés közben. Ezt a nyitólapon a Libraries linkről tehetjük meg. Ekkor egyrészről a rendszer kilistázza a létező könyvtárakat, másrészt pedig a fenti menüben a Create gombbal létrehozhatunk új könyvtárat is. Létrehozáshoz válasszuk ki a listából a Document Library sablont és adjuk meg a könyvtár nevét. Ezt követően az elkészült könyvtárat szabhatjuk testre. Alapértelmezésként a benne tárolt elemek listáját láthatjuk, azonban a szerkesztés menüpont alatt felvehető web partként az Excel Web Access nevű bővítmény, amelyben betallózható az általunk kiválasztott fájl, amelyet olvasásra megjelenít Excel szerűen. A betallózás előtt azonban el kell menteni a munkafüzetet a Document Library-be. Ehhez a közzététel menüben a SharePointot kiválasztva a mentés helyéül már be tudjuk tallózni mappaként a Library-t.
51
19. ábra – Az előző diagram SharePointban Google Chrome alatt
A fenti diagram szemléletesen ábrázolja azt a gondolatmenetet, amely nyomán a feladat kiírása megfogalmazódott. A mögötte álló elemzés széles skáláját öleli fel a pénzügyi bizonylatoknak, az azokhoz rendelt különböző teljesítési valószínűséggel pedig precízen paraméterezhetővé válik az analízis, ezáltal pedig tervezhetővé a cash flow. Az általam nyújtott megoldás azonban absztrakcióját tekintve nem ragad meg ezen a szinten, előre nem specifikált pénzügyi elemzések, ad-hoc riportok is készíthetők, publikálhatók vele. Ezt bizonyítandó az alábbi SharePointban publikált elemzés például havi bontásban tartalmazza a bankszámla kivonatokból kinyerhető egyenleget, illetve az elemzési paraméterek alapján becslést ad a jövőbeli kimenő és bejövő pénzáramokra, valamint ezek alapján a várható egyenleg alakulására.
52
20. ábra – A várható kimenő és bejövő pénzáramlások havi bontásban
A SharePoint webre publikált elemzések már elérhetők bármely böngészőből kellő hitelesítés után, így azok könnyen hozzáférhető bárhol, bármikor, akár mobil eszközökről is.
53
5. Értékelés, továbbfejlesztési lehetőségek Mivel a dolgozatom egy demó adatbázison készítettem el, ilyen formán nem várható visszacsatolás a megrendelőtől, így az értékelést a tesztelés alapján tudom elkészíteni. Úgy vélem, a feladat specifikálásánál kitűzött célokat sikerült maradéktalanul kielégíteni. A fő tervezési célok az ERP függetlenség és a visszakövethetőség volt. Az adattárház logikai sémája úgy gondolom, kellőképp általánosra sikerült, ezáltal biztosítom annak a konkrétan alkalmazott vállalatirányítási rendszertől való függetlenségét. A dolgozatomban létrehozott Navisionhöz alkalmas ETL mellett gondolatban végig vezettem egy SBO-hoz tartozó létrehozását, a legapróbb adatbázis komponensek megvizsgálásával. E mentén úgy gondolom, hogy könnyedén létre lehetne hozni azt a gyakorlatban is a szükséges adatbázis hozzáférés birtokában. Az, hogy nem találtam ellentmondást az SBO-hoz való ETL létrehozásának végiggondolása közben nem jelenti azt, hogy annak implementálása azonnal hibátlan működéshez vezetne, azonban mindenképpen jó jel, ami némiképp igazolja az adattárház sémájánál hozott tervezési döntések helyességét. A visszakövethetőség úgy gondolom, nem szorul túlságosan precíz bizonyításra: az áttöltés kezdetétől elemzés publikálásáig elérhető minden azonosító, hivatkozás, mind az adattárházra vonatkozóan, mind pedig az ERP rendszerben használt kulcsok tekintetében. Sőt, azon még túl is mutat a külső bizonylatok azonosítóinak ERP-ből való átemelésével, így az elemzésből akár azonnal, egy lépésben visszafúrhatunk a papír alapú bizonylathoz. Az elvégzett feladatot nagyon hasznosnak találtam, sokat tanultam általa. Alaposabban megismertem a Microsoft BI eszközeinek funkciókészletét, illetve azok együttes működéséhez szükséges konfiguráció kialakításának menetét. Mivel sok rendszerrel csak most ismerkedtem meg, ezért az egyes komponensek minél jobb egymáshoz csiszolása mentén látok még fejlesztési lehetőséget az alkotásomban, javítható véleményem szerint a rendszer felhasználói oldalon tapasztalható válaszideje is. Jelen megoldás során minden komponens a hordozható gépemen futott, annak erőforrásait nagymértékben kihasználva, az néha szűkös erőforrásnak is bizonyult. A jövőben a 54
rendszer fizikai teljesítménye tovább javítható az egyes komponensek külön szerverekre való helyezésével. Ehhez az elkészült program úgy gondolom, kellőképp komponensekre bontott. Az üzleti felhasználhatóság szempontjából előrelépést jelentene, ha minél több vállalatirányítási rendszerhez létezne áttöltő modul, így a bevezetéshez elegendő lenne csak a szükséges telepítése modulok és konfigurálása. Az áttöltő modulok tekintetében fontos újítás lehet, ha a kötelezettség kielégítésének valószínűségét jelölő százalékos érték, ha SharePointon keresztül is, de valamilyen CRM folyamatban meghatározott adatforrásból kerülne bele a rendszerbe. Az elemzés során az Excel szinte végtelen lehetőséget biztosít számunkra, így ezen a területen is izgalmas fejlesztési terület kínálkozik, akár a tőzsde világából ismert technikai elemzés is érdekes adatokat szolgáltathat a vállalat fejlesztéséhez, értékének növeléséhez.
55
6. Köszönetnyilvánítás Ahhoz, hogy szakdolgozatom elkészülhessen, sokaktól kaptam segítséget. Köszönettel tartozom Pálfi Attilának, aki önálló laboratóriumi munkám és szakdolgozatom alatt ipari konzulensem volt és lehetővé tette, hogy az egyetemen megszerzett elméleti tudásom a gyakorlatba is átültethessem. Köszönettel tartozom még dr. Szikora Bélának azért, hogy tanárként és konzulensemként is támogatott.
Ezúton szeretném megköszönni dr. Könyves Tóth Mihály tanár úrnak, aki rendelkezésemre bocsájtotta azt az SBO adatbázis referenciát, amely alapján az elméleti összefoglaló valamint az ETL tervezet is elkészülhetett. További köszönettel tartozok családomnak és barátaimnak minden támogatásért, bátorításért, a nyugodt környezet biztosításáért.
56
Irodalomjegyzék
[1]
A. Rappaport, A tulajdonosi érték, Budapest: Aliena Kiadó, 2002.
[2]
Pénzügy Sziget, „Cash-flow,” 18. augusztus 2009. [Online]. Available: http://www.penzugysziget.hu/index.php?option=com_content&view=article &id=194&Itemid=76. [Hozzáférés dátuma: 16. november 2011].
[3]
Wikipedia, „Cash
flow,” 2. november 2011. [Online].
Available:
http://en.wikipedia.org/wiki/Cash_flow. [Hozzáférés dátuma: 16. november 2011]. [4]
Z. M. Dr. Kósa, „Gazdálkodási Információmenedzsment,” 7. február 2011. [Online].
Available:
http://doc.tmit.bme.hu/subject/vitma381/17-
cash_flow.ppt. [Hozzáférés dátuma: 16. november 2011]. [5]
S. Stewart és A. Ehrbar, EVA - kulcs az értékteremtéshez, Budapest: Panem Könyvkiadó Kft., 2000.
[6]
C. Sildó, „Adattárház összefoglaló,” augusztus 2004. [Online]. Available: http://scs.web.elte.hu/Work/DW/adattarhazak.htm. [Hozzáférés dátuma: 25. április 2011].
[7]
M. Whitehorn és K. Burns, „Best Practices for Data Warehousing with SQL Server
2008,”
július
2008.
[Online].
Available:
http://msdn.microsoft.com/en-us/library/cc719165(v=sql.100).aspx. [Hozzáférés dátuma: 5. május 2011]. [8]
S. Gajdos, Adatbázisok, Budapest: Műegyetemi Kiadó, 2006.
[9]
C. Holland, „Microsoft SharePoint 2010 Web Applications The Complete Reference
on
MSDN,”
2011.
[Online].
Available:
http://msdn.microsoft.com/enus/library/gg697767.aspx#ExcelServ_Capabilities. [Hozzáférés dátuma: 27. november 2011]. [10] Microsoft Terméktámogatás, „Error message when you install Microsoft Dynamics NAV 5.0 Service Pack 1,” 3. augusztus 2009. [Online]. Available: http://support.microsoft.com/kb/953610. [Hozzáférés dátuma: 8. október 2011.]. 57
[11] Plataan - Steven, „How to set trace flag 4616 in Sql Server?,” 11. június 2011.
[Online].
Available:
http://plataan.typepad.com/microsoftdynamics/2007/11/how-to-set-trac.html. [Hozzáférés dátuma: 8. október 2011.]. [12] L. Lohndorf-Larsen, „Basic SQL - Creating Extended Stored Procedure / xp_ndo.dll,”
5.
november
2008.
[Online].
Available:
http://blogs.msdn.com/b/nav_developer/archive/2008/11/05/basic-sqlcreating-extended-stored-procedure-xp-ndo-dll.aspx. [Hozzáférés dátuma: 8. október 2011.]. [13] MSDN - SP2010, „Setting Up the Development Environment for SharePoint 2010 on Windows Vista, Windows 7, and Windows Server 2008,” május 2010.
[Online].
Available:
http://msdn.microsoft.com/en-
us/library/ee554869.aspx. [Hozzáférés dátuma: 8. október 2011.]. [14] B. II Daugherty, „Configuration Failed when installing SP 2010 on Windows 7
(Failed
Big
time!),”
31.
március
2010.
[Online].
Available:
http://myspexp.com/2010/05/31/configuration-failed-when-installing-sp2010-on-windows-7-failed-big-time-3/#comment-55. [Hozzáférés dátuma: 8. október 2011.]. [15] R. A. Brealey és S. C. Myers, Modern vállalti pénzügyek, Budapest: Panem Kft., 2005. [16] Windows Dev Center, „Microsoft OLE DB,” [Online]. Available: http://msdn.microsoft.com/enus/library/windows/desktop/ms722784%28v=vs.85%29.aspx. dátuma: 15. november 2011].
58
[Hozzáférés
Ábrajegyzék 1. ábra – A pénzügyi adatok befolásolhatósága és pontossága....................................... 13 2. ábra – Csillagséma ER diagramja ............................................................................... 15 3. ábra – A hópihe séma ER diagramja .......................................................................... 16 4. ábra – Az OLAP kocka 3 dimenziós reprezentációja ................................................. 17 5. ábra – A Navision struktúrája ..................................................................................... 19 6. ábra – Az SBO funkciói .............................................................................................. 20 7. ábra – Rendszerinformációk megjelenése az állapotsoron ......................................... 21 8. ábra- A parten, szálmafej és számlasor adatok kapcsolata ......................................... 21 9. ábra – Az elkészített alkalmazás use case diagramja .................................................. 23 10. ábra – Az általam készíített megoldás komponenseinek kapcsolata ........................ 24 11. ábra – Az adattárház sémájának ER diagramja ........................................................ 25 12. ábra – Az elkészített áttöltő modul state-chart diagramja......................................... 29 13. ábra – A BDCM elhelyezkedése a SharePoint és az SQL szerver között ................ 37 14. ábra – Elemzési paraméter beállítása SharePointban ............................................... 43 15. ábra – Az OLAP kocka relációs sémája ................................................................... 46 16. ábra – Az adattárház illetve az OLAP kocka frissítése ............................................. 47 17. ábra – A kimutatás eszköztár a készített kimutatással .............................................. 50 18. ábra – A kimutatás alapján készített grafikon ........................................................... 50 19. ábra – Az előző diagram SharePointban Google Chrome alatt ................................ 52 20. ábra – A várható kimenő és bejövő pénzáramlások havi bontásban ........................ 53 21. ábra – A C++ 2005 SP1 hiányát jelző hibaüzenet .................................................... 61 22. ábra – A 4616-os követési jelző hiánya .................................................................... 62 23. ábra – A 4616-os követési jelző beállítása – 1. ábra ................................................. 62 24. ábra - A 4616-os követési jelző beállítása – 2. ábra ................................................. 63 25. ábra - A 4616-os követési jelző beállítása – 3. ábra ................................................. 63 26. ábra – Az xp_ndo tárolt eljárások hiányára felhívó hibaüzenet................................ 64 27. ábra – Távoli eljárás hozzáadása .............................................................................. 65 28. ábra – Az eljárás nevének és a DLL helyének beállítása .......................................... 66 29. ábra – Hozzáférési engedély beállítása ..................................................................... 67 30. ábra – Csak szerver platformra lehet telepíteni a SharePoint Servert ....................... 68
59
31. ábra – SharePoint telepítése IIS nélkül ..................................................................... 69 32. ábra – A telepítendő IIS komponensek listája .......................................................... 70
Táblázat jegyzék 1. táblázat – A dimenziók létrehozásának lépései .......................................................... 45 2. táblázat – Az adattárház struktúrája ............................................................................ 73
60
Függelék A. Microsoft Dynamics NAV 5.0 telepítése Windows 7 operációs rendszerre [9]
21. ábra – A C++ 2005 SP1 hiányát jelző hibaüzenet
Amennyiben a fenti hiba jelentkezik a Navision telepítése közben, akkor hiányzik a gépünkről a Microsoft Visual C++ 2005 Service Pack 1 (SP1) Redistributable Package (x86). Állítsuk le a NAV telepítőjét is telepítsük a szerviz csomagot. Fontos, hogy 64 bites operációs rendszeren is a 32 bites szerviz csomagot telepítsük.
61
B. Az SQL szerver konfigurálása a Microsoft Dynamics NAV 5.0-hoz B.1. A 4616-os követési jelző beállítása [10] A NAV telepítését követően az SQL szerverhez való csatlakozás közben a következő hibaüzenetet kapjuk:
22. ábra – A 4616-os követési jelző hiánya
A probléma elhárításához be kell állítani a T4616-os követési jelzőt (trace flag) az adatbázis kiszolgálón. 1. Ehhez futtatni kell az SQL Server Configuration Manager-t, amiben az SQL szerver tulajdonságainál tudjuk beállítani.
23. ábra – A 4616-os követési jelző beállítása – 1. ábra
2. Az Advanced fülön a Startup Parameters értékéhez hozzá kell adni a „-T4616” paramétert.
62
24. ábra - A 4616-os követési jelző beállítása – 2. ábra
3. Az elvégzett módosítások érvénybe lépéséhez újra kell indítani az adatbázis kiszolgálót, szükség esetén a Navision-t és a klienst is.
25. ábra - A 4616-os követési jelző beállítása – 3. ábra
B.2. Az xp_ndo tárolt eljárások beállítása [11] Miután beállítottuk a követési jelzőt és ismét megpróbálunk csatlakozni, újabb hibát kapunk:
63
26. ábra – Az xp_ndo tárolt eljárások hiányára felhívó hibaüzenet
A hiba elhárításához két tárolt eljárás használatát kell beállítani az adatbázis kiszolgálón. Ezek az xp_ndo_enumusersids és az xp_ndo_enumusergroups. Telepítésük hasonlóan történik, az egyetlen különbség csupán az eljárások nevében rejlik. A tárolt eljárások hozzáadása a következőképp történik: 1. A Navision telepítő lemezéről átmásoljuk az xp_ndo.dll fájlt az SQL szerver Binn könyvtárába. 64 bites operációs rendszer esetében természetesen az xp_ndo_x64.dll fájl használandó. A Binn könyvtár alapértelmezettként a következő útvonalon található: c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL \Binn\ 2. A Microsoft SQL Server Management Studio-t elindítva, a jobb oldali fában az Extended Stored Procedures csomópontra jobb gombbal kattintva tudunk új tárolt eljárást felvenni.
64
27. ábra – Távoli eljárás hozzáadása
3.
A felbukkanó ablakban az általános (General) fülön adjuk meg az eljárás nevét (xp_ndo_enumusersids) és az előbb bemásolt DLL fájl elérési útvonalát.
65
28. ábra – Az eljárás nevének és a DLL helyének beállítása
4.
A Jogosultságok (Permissions) fülön pedig a public felhasználónak adjunk végrehajtási jogot.
66
29. ábra – Hozzáférési engedély beállítása
5. Végezetül a fenti lépéseket megismételve adjuk hozzá a kiszolgálóhoz az xp_ndo_enumusergroups tárolt eljárást is. 6. Ezután indítsuk újra az adatbázis kiszolgálót. Ezen beállítások elvégzése után már használható MS SQL Server-rel a Dynamics Navision.
67
C. SharePoint Server 2010 telepítése Windows 7 operációs rendszerre [12] FIGYELEM: A SharePoint Server 2010 telepítéséhez 64 bites szerver operációs rendszer ajánlott!
C.1. Windows 7 konfigurációjának módosítása SharePoint 2010 telepítéséhez
30. ábra – Csak szerver platformra lehet telepíteni a SharePoint Servert
Amennyiben 64 bites operációs rendszeren próbálkozunk meg a telepítéssel, akkor is hibaüzenettel leáll a telepítés, hiszen csak szerver platformra lehet feltelepíteni a SharePointot. Megszokott fejlesztő környezetünkről azonban nem szívesen mondanánk le, Windows 7 alatt azonban nem is kell, elegendő néhány konfigurációs módosítást elvégeznünk, hiszen a kernel megegyezik a Windows Server 2008-éval, csupán máshogy van beállítva. Ezen beállításokon kíséri végig az olvasót e függelék. 1. lépés Az előkövetelmények telepítése után át kell másolni a telepítő fájlokat egy írható területre, majd ott ki kell bontani a setup.exe fájlt. Ezt parancssorból, a megfelelő mappába navigálni a következő paranccsal tehetjük meg: c:\SharePointFiles\OfficeServer /extract:c:\SharePointFiles
2. lépés A kicsomagolás után létrejön a Setup mappában több állomány is, számunkra ezek közül a config.xml a fontos, ugyanis ebben a fájlban tudjuk engedélyezni a SharePoint kliens operációs rendszerre való telepítését. A szükséges beállításhoz a
tag-ek közé be kell illeszteni a következő sort: 68
<Setting Id="AllowWindowsClientInstall" Value="True"/>
Ezután a Config fájl körülbelül így néz ki: <Package Id="sts"> <Setting Id="LAUNCHEDFROMSETUPSTS" Value="Yes"/> <Package Id="spswfe"> <Setting Id="SETUPCALLED" Value="1"/> <Setting Id="SERVERROLE" Value="SINGLESERVER"/> <Setting Id="USINGUIINSTALLMODE" Value="1"/> <Setting Id="SETUPTYPE" Value="CLEAN_INSTALL"/> <Setting Id="SETUP_REBOOT" Value="Never"/> <Setting Id="AllowWindowsClientInstall" Value="True"/>
3. lépés A konfigurációs fájl módosítása után, ha megpróbáljuk futtatni a telepítőt, akkor a következő hibaüzenetet kapjuk:
31. ábra – SharePoint telepítése IIS nélkül
Ahhoz, hogy futtatni tudjuk a SharePoint telepítőjét, telepíteni kell a szerver kiszolgálásához szükséges IIS (Internet Information Services) szolgáltatásokat. Az IIS telepítését a Vezérlőpult/Programok és szolgáltatások/Windows szolgáltatások be- és kikapcsolása útvonalon indíthatjuk el. Ehhez a következő komponensek előtti jelölőnégyzetet kell bepipálni:
69
32. ábra – A telepítendő IIS komponensek listája
A szükséges komponensek telepíthetők a következő scripttel is: start /w pkgmgr /iu:IIS-WebServerRole;IIS-WebServer;IISCommonHttpFeatures;^ IIS-StaticContent;IIS-DefaultDocument;IIS-DirectoryBrowsing;IISHttpErrors;^
70
IIS-ApplicationDevelopment;IIS-ASPNET;IIS-NetFxExtensibility;^ IIS-ISAPIExtensions;IIS-ISAPIFilter;IIS-HealthAndDiagnostics;^ IIS-HttpLogging;IIS-LoggingLibraries;IIS-RequestMonitor;IISHttpTracing;IIS-CustomLogging;IIS-ManagementScriptingTools;^ IIS-Security;IIS-BasicAuthentication;IIS-WindowsAuthentication;IISDigestAuthentication;^ IIS-RequestFiltering;IIS-Performance;IIS-HttpCompressionStatic;IISHttpCompressionDynamic;^ IIS-WebServerManagementTools;IIS-ManagementConsole;IISIIS6ManagementCompatibility;^ IIS-Metabase;IIS-WMICompatibility;WAS-WindowsActivationService;WASProcessModel;^ WAS-NetFxEnvironment;WAS-ConfigurationAPI;WCF-HTTP-Activation;^ WCF-NonHTTP-Activation
4. lépés
Miután befejeztük az IIS telepítését, már tudjuk telepíteni a SharePoint Server-t. Telepítésnél válasszuk ki a Standalone installációt. A telepítő befejezésekor elindul a SharePoint 2010 Products Configuration Wizard, ami elvégzi a szükséges beállításokat a web szerveren és létrehozza az adatbázist.
C.2. A SharePoint Server konfigurálása és hibaelhárítás [12] A varázsló futtatása során többször különböző hibajelzésekkel leállt, mielőtt sikeresen lefutott volna. A következő hibajelzésekkel találkoztam: System.IO.FileNotFoundException: A hiba oka a Windows Identity Foundation pack hiánya. Telepítésével és a varázsló újra futtatásával a hiba megoldható. Microsoft.SharePoint.Upgrade.SPUpgradeException: A hiba oka a Charts Controls hiánya. Telepítésével és a varázsló újra futtatásával a hiba megoldható.
71
D. Az adatbázis részletes bemutatása A dimenziótáblák nevükben a Dim előtagot kapják. Az egyes táblák a create_<>.sql fájllal generálhatók le. TÁBLANÉV DimDirection DimDirection DimDocumentNo DimDocumentNo DimERPEntryId DimERPEntryId DimPartner DimPartner DimPartner DimPartner DimPartner DimPartner DimPartner DimPartner DimPercentage DimPercentage DimTime DimTime DimTime DimTime DimTime DimTime DimTime DimTime DimTime DimTime DimType DimType DimLocation DimLocation Fact Fact Fact Fact Fact
OSZLOPNÉV DirectionId Type
MEGJEGYZÉS Elsődleges kulcs, autóinkremens A pénzáramlás iránya (INCOMING / OUTGOING) Id Elsődleges kulcs, autóinkremens DocumentNo A hivatkozott dokumentum azonosítója Id Elsődleges kulcs, autóinkremens ERPEntryId A sor azonosítója az ERP-ben PartnerId Elsődleges kulcs, autóinkremens Name A partner neve Counrty Számlázási ország City Számlázási város Type Partnertípus (VENDOR / CUSTOMER / BOTH / OTHER) ERPCustomerId Külső azonosító ERPVendorId Külső azonosító SharePointId Külső azonosító PercentageId Elsődleges kulcs, autóinkremens Percentage A pénzmozgás valószínűsége TimeId Elsődleges kulcs, autóinkremens Date Dátum DayNumberOfWeek A nap száma a héten DayNumberOfMonth A nap száma a hónapban DayNumberOfYear A nap száma az évben WeekNumberOfYear A hét száma az évben MonthNumberOfYear Hónap száma QuarterOfYear Negyedév SemesterOfYear Félév Year Év TypeId Elsődleges kulcs, autóinkremens Type A bizonylat típusa CountryCode Elsődleges kulcs, országkód Country Az ország neve Id Elsődleges kulcs, autóinkremens PartnerId Idegen kulcs, partnerazonosító ERPEntryId Idegen kulcs, ERP tételazonosító DueDateId Idegen kulcs, határidő azonosító ClosedAtDateId Idegen kulcs, lezárás dátuma azonosító
72
Fact
Amount (LCY)
Fact Fact
Remainder (LCY) Document No_
Fact Fact Fact
Percentage TypeId DirectionID
Bizonylat végösszege, saját pénznemben Hátralék, saját pénznem ben Idegen kulcs, külső dokumentum azonosító Idegen kulcs, valószínűségi százalék Idegen kulcs, típus azonosító Idegen kulcs, pénzáramlás irányának azonosítója
2. táblázat – Az adattárház struktúrája
73
