Postadress: Besöksadress: Telefon:
Förbättring av tjänsteleverans för konsultbolag
Service Delivery Excellence for Consultant Companies
Martin Samuelsson, Alexander Törnvall
EXAMENSARBETE 2014
Datateknik
Postadress: Besöksadress: Telefon:
Detta examensarbete är utfört vid Tekniska Högskolan i Jönköping inom ämnesområdet Datateknik. Arbetet är ett led i den treåriga
högskoleingenjörsutbildningen.
Författaren svarar själva för framförda åsikter, slutsatser och resultat. Examinator: Inger Palmgren
Handledare: Inger Palmgren
Omfattning: 15 hp (grundnivå)
Abstract
Profitability for a consultant company is dependent of the actual consultant hours delivered by consultants in customer projects.
The company result will be affected if the consultant hours couldn’t be delivered because of illness or over-allocation.
In consultant company’s where consultants work with different projects and customers it can be hard to visualize and follow-up the actual forecast. This could affect the company’s result and also delay the service delivery in projects.
The hypothesis for this thesis is that visualization and follow-up of the actual result will reduce the risk for loss of income and delayed projects.
The authors in corporation with aRway AB have tested this hypothesis by developing a tool that visualizes the forecast and performance for consultants working at aRway AB.
This work have resulted in a report that describes the process for developing a solution that will help smaller consultant companies with delivery planning, forecasting and performance follow up. In the report is also a theoretical background included.
Outcome of this work is a tool for visualizing and follow up the company’s deliveries which has given the company an enhanced service delivery and increased result.
Sammanfattning
Lönsamheten för konsultbolag är beroende av antalet timmar konsulter arbetar vad gäller leveranser i kunduppdrag. Att timmar ej levereras på grund av sjukdom eller överbeläggning påverkar resultatet för bolaget. Konsultbolag där konsulter samma vecka arbetar mot olika kunder och uppdrag, kan ha svårt att synliggöra och följa upp leveranserna vilket kan resultera i inkomstbortfall och försenade leveranser.
Hypotesen för detta examensarbete är att visualisering och uppföljning minskar risken till inkomstbortfall och försenade leveranser.
Författarna har tillsammans med företaget aRway AB testat denna hypotes genom att utveckla ett verktyg för att visualisera prognos och utfall för konsulter på företaget.
Rapporten beskriver processen för framtagandet av en lösning för
leveransplanering, prognostisering och uppföljning för ett mindre konsultbolag. Rapporten innehåller även en teoretisk bakgrund till arbetet.
Resultatet av detta arbete är en lösning som används för att visualisera och följa upp företagets leveranser, vilket har gett en ökad leveransprestation och minskat inkomstbortfall.
Nyckelord
MashZone SQL-Server Leveransplanering Prognostisering Uppföljning T-SQLInnehållsförteckning
1
Inledning... 6
1.1 BAKGRUND OCH PROBLEMBESKRIVNING ... 6
1.1.1 aRway AB ... 6 1.2 SYFTE OCH MÅL ... 6 1.3 AVGRÄNSNINGAR ... 7 1.4 DISPOSITION ... 7 1.5 METOD ... 7
2
Teoretisk bakgrund ... 8
2.1 KONCEPTUELLA DATAMODELLER ... 82.1.1 Entity Relationship Model ... 8
2.2 MASHUP ... 10
2.3 ARISMASHZONE ... 11
2.3.1 Ansluta MashZone till databas ... 11
2.3.2 Dataström (datafeed) ... 13
2.3.3 Använda datakälla till presentationsvy ... 14
2.4 QBIS... 15 2.4.1 QBIS Tidsrapportering ... 15 2.4.2 QBIS Connect ... 15 2.5 DATABAS ... 16 2.5.1 Metadata ... 16 2.5.2 JDBC ... 16 2.5.3 Databashanterare ... 16 2.5.4 Relationsdatabas ... 17 2.5.5 T-SQL ... 18 2.5.6 Databastabeller ... 19 2.5.7 Vyer ... 23 2.5.8 Lagrade Procedurer ... 24
2.5.9 Common table expression ... 24
2.5.10 Parameter ... 25
2.5.11 SQL Server integration services ... 25
2.6 XML ... 27
3
Genomförande ... 28
3.1 BESKRIVNING AV NULÄGET ... 28
3.1.1 Planera beläggning ... 28
3.1.2 Rapportering av arbetad tid ... 28
3.1.3 Leveransansvariga ... 28
3.1.4 Följa upp leverans ... 29
3.2 IDENTIFIERA KRAV PÅ LÖSNING ... 29
3.2.1 Utfall föregående perioder ... 29
3.2.2 Prognos kommande perioder ... 29
3.2.3 Beläggningsplan per uppdrag kommande 6 veckor ... 29
3.2.4 Beläggningsplan per konsult kommande 6 veckor ... 29
3.2.5 Avvikelse utfall mot plan föregående period... 29
3.3 FÖRSTA ITERATIONEN ... 30
3.3.1 Registrera arbetad tid ... 30
3.3.2 Leveransplanering ... 31
3.3.3 Exportera Data (Manuellt) ... 32
3.4 ANDRA ITERATIONEN ... 38
3.4.1 Skapa databas i Microsoft SQL-Server... 38
3.4.2 Import av data från XML-filer till Datalager ... 38
3.4.3 Vyer mot datalager ... 39
3.4.4 Presentera data ... 41
3.5 TREDJE ITERATIONEN ... 42
3.5.1 Exportera Data (Automatiskt) ... 42
3.5.2 Automatisk uppdatering av data med hjälp av ”Jobb” ... 42
3.5.3 Ekonomisk prognos och uppföljning ... 43
3.5.4 Laddning av Prognosdata ... 43
3.5.5 Vyer mot datalager ... 45
3.5.6 Datakälla för prognos och utfall ... 49
3.5.7 Presentera data ... 50
4
Resultat ... 51
4.1 BESKRIVNING AV LÖSNING ... 51
4.2 FÖRBÄTTRING VID INFÖRANDE AV VISUALISERINGSVERKTYG ... 52
4.3 UPPKOMNA PROBLEM ... 53
4.3.1 Problem vid inläsning av data från QBIS ... 53
4.3.2 Aggregering på vecka och månad ... 53
5
Slutsats och diskussion ... 54
5.1 FRAMTIDA ARBETE ... 54
6
Referenser ... 55
7
Sökord ... 57
8
Bilagor ... 58
Figurförteckning
FIGUR 1 - EXEMPEL PÅ ENTITET 8
FIGUR 2 - EXEMPEL PÅ ATTRIBUTSMODELL 9
FIGUR 3- ALTERNATIVA NOTATIONER INOM ER. [25] 10 FIGUR 4 – EXEMPEL PÅ KRÅKFOTSNOTATION [7] 10
FIGUR 5 - EXEMPEL PÅ ER MODELL 10
FIGUR 6 - LOGISK LÖSNINGSMODELL FÖR MASHZONE [9] 11 FIGUR 7 - SKÄRMBILD PÅ DATABASKOPPLING FRÅN MASHZONE 12 FIGUR 8 - EXEMPEL PÅ DATA FEED I MASHZONE 13 FIGUR 9 - EXEMPEL PÅ EN DATAKÄLLA SOM KOPPLATS MOT EN TABELL I MASHZONE 14 FIGUR 10 - SKÄRMBILD PÅ TIDSRAPPORT I QBIS TID 15 FIGUR 11- EXEMPEL PÅ HUR EN VY KAN VARA DEFINIERAD 23 FIGUR 12 - EXEMPEL PÅ COMMON TABLE EXPRESSION 24
FIGUR 13 - EXEMPEL PÅ PARAMETER 25
FIGUR 14- EXEMPEL PÅ XML STRUKTUR: 27
FIGUR 15- SKÄRMBILD PÅ RAPPORTERING I QBIS TID 30 FIGUR 16 - FLÖDE FRÅN QBIS CONNECT TILL FILSTRUKTUR 32 FIGUR 17 - EXEMPEL PÅ XML-STRUKTUR FÖR CUSTOMER 33 FIGUR 18 - ATTRIBUTSMODELL FÖR CUSTOMER 34 FIGUR 19 - ENTITY RELATIONSHIP MODEL FÖR VIKTIGASTE XML-FILER 35 FIGUR 20 - INLÄSNIGN AV XML-FIL TILL MASHZONE 36 FIGUR 21- PRESENTATIONSGRÄNSNITT FRÅN ITERATION 1 36
FIGUR 22 - EXEMPEL PÅ SSIS FLÖDE 38
FIGUR 23- INFORMATIONSMODELL FÖR KRAVSTÄLLNING PÅ VYER 39
FIGUR 24 - VY FÖR MÅNAD 39
FIGUR 25 - VY FÖR VECKA 40
FIGUR 26 - PRESENTATIONSGRÄNSSNITT FÖR ANDRA ITERATIONEN 41 FIGUR 27 - SCHEMALÄGGNIGN AV JOBB I SQL-SERVER 42
FIGUR 28 - LADDNING AV PROGNOSDATA 44
FIGUR 29 - VY FÖR UTFALL (MÅNAD) 46
FIGUR 30 - VY FÖR PROGNOS (MÅNAD) 48
FIGUR 31 - FILTRERING AV INTERNA TIDSPOSTER 49 FIGUR 32 - DATAFEED FÖR PROGNOS MOT UTFALL 49 FIGUR 33 – PRESENTATIONSGRÄNSSNITT FÖR TREDJE ITERATIONEN 50 FIGUR 34 - ÖVERGRIPANDE BESKRIVNING AV LÖSNIGNEN 51 FIGUR 35 - SLUTGILTIGA PRESENTATIONSGRÄNSSNITTET FÖR VERKTYGET 52
1 Inledning
Detta examensarbete är det slutliga arbete som utförs på det treåriga
högskoleingenjörsprogrammet: Kommunikation och informationsteknik vid Tekniska Högskolan i Jönköping.
1.1 Bakgrund och problembeskrivning
Lönsamheten för konsultbolag är beroende av antalet timmar konsulter arbetar med leveranser i kunduppdrag. Det är därför viktigt att täcka upp leveranser om en konsult exempelvis skulle vara dubbelbokad eller sjuk.
Om det vid debitering per timme levereras färre timmar än planerat får det påverkan på företagets resultat, men det är även vid fast pris viktigt att leverera enligt prognos. Konsultbolag där konsulter på samma vecka arbetar mot olika kunder och uppdrag, kan ha svårt att synliggöra och följa upp helheten
Hypotesen för detta examensarbete är att visualisering och uppföljning minskar risken till inkomstbortfall och försenade leveranser.
Tillsammans med företaget aRway AB testat denna hypotes genom att utveckla ett verktyg för att visualisera prognos och utfall för konsulter på företaget.
1.1.1 aRway AB
aRway AB är ett konsultbolag med 20-30 anställda som arbetar med
verksamhetsutveckling och modellering av verksamheter. Kunderna som aRway arbetar med är främst stora svenska företag som har ett behov av att förstå sin komplexa verksamhet. aRway hjälper sina kunder med hjälp av värdsledande metoder och verktyg inom Enterprise Architecture.
Idag har de endast möjligt att mäta utfallet månad för månad, och det saknas möjlighet att se en övergripande vy över företagets prognos.
De har en kort horisont för planering, och utfallet blir känt först månaden efter fakturering, detta medför en stor osäkerhet och kan hindra att viktiga beslut tas i rätt tid. Planering av resurser sker idag med hjälp av Excel, där konsulterna får mål för kommande veckor och får kommentera om de anser att målen bör justeras.
1.2 Syfte och Mål
Syftet med arbetet är att utreda om visualisering och uppföljning minskar risken till inkomstbortfall och försenade leveranser.
Målet med arbetet är att ta fram ett verktyg för att stödja uppföljning - det ska vara enkelt att planera och följa upp kundleveranserna. Det kommer att innebära både förändringar i arbetsätt, organisation samt utveckling av verktyg.
1.3 Avgränsningar
För att utreda hypotesen kommer valen av verktyg och applikationer vara styrt av vad som idag används samt vilka licenser som finns inom företaget, det kommer exempelvis inte ske någon utvärdering av nytt tidsrapporteringssystem. Den nya lösningen kommer att anpassas till befintlig miljö.
Verktyget kommer därför att använda data från tidsrapporteringssystemet QBIS och resultatet ska visas i ARIS MashZone, om möjligt ska befintliga
databaslicenser användas.
Arbetet förutsätter att data är korrekt inskrivet i QBIS och att korrekt data har exporterats från QBIS. Grunddatat kräver ingen hantering av dubbletter, därför behöver ingen justering av grunddata göras.
Målet med arbetet är att ta fram en bas till uppföljningsverktyg som sedan företaget kan vidareutveckla för framtida behov.
1.4 Disposition
Teoretisk Bakgrund
Beskriver de tekniker och verktyg som används i arbetet, syftet med teoretiska bakgrunden är att underlätta för läsaren i kapitlen genomförande och resultat.
Genomförande
Beskriver genomförandet av arbetet, från beskrivning av nuläget och kravställning, till att följa utvecklingens tre iterationer.
Resultat
Beskiver den slutgiltiga lösningen, och även vissa av de större problemen (och dess lösningar) som uppkommit under arbetets gång.
Slutstats och diskussion
Här återfinns författarnas reflektioner och kommentarer kring om arbetet, samt förslag på fortsatta utvecklingsmöjligheter.
1.5 Metod
För att lyckas med arbetet är en bra relation med företaget viktigt, kravställningen kommer ske tillsammans med verksamheten och problem som uppstår
kommuniceras för att hitta lösningar tillsammans. Till stöd i arbetet kommer dels befintlig verktygens hjälpdokumentation, litteratur och internet att användas. I kravarbetet kommer företaget förtydliga sina mål med arbetet.
2 Teoretisk bakgrund
Teoretisk bakgrund beskriver de tekniker och verktyg som används i arbetet, syftet med teoretiska bakgrunden är att underlätta för läsaren i kapitlen genomförande och resultat.
2.1 Konceptuella datamodeller
En beskrivning av ett företags information på hög nivå kallas för konceptuell, eller begreppsmässig beskrivning och är en beskrivning av företaget som kan användas i många syften. Exempelvis kan en konceptuell modell användas för att analysera hur ett företag fungerar, eller för att designa en databas. När en databas skapas krävs förståelse för hur verksamhetens information relaterar till varandra. En vanlig standard för att beskriva konceptuella datamodeller är att skapa en Entity-Relationship-modell (ER-modell). [23]
2.1.1 Entity Relationship Model
Entity-relationship (ER) modellen introducerades 1976 av P. P Chen och är numera en standard kring att kommunicera datamodeller mellan designers, utvecklare och slutanvändare. Utan en gemensam standard tenderar de olika grupperna få olika uppfattningar om informationsstrukturen på företaget. [24] ER modellering utvecklades ursprungligen för att användas vid databasdesign, men används numera även i ett bredare perspektiv för att beskriva verksamheter. Koncept som är centrala i ER modellering:[24]
Entitet – Objekt inom verksamheten
Relationer – Hur förhåller sig verksamhetens begrepp till varandra Attribut – Beskriver entiteter och relationer
2.1.1.1 Entitet
En entitet är någonting som är av vikt för företaget att beskriva, en entitet kan exempelvis vara order, där entiteten beskriver den logiska representationen av informationen för en orders inom företaget. En entitet har både attribut, relationer och förekomster och kan representera både en fysisk sak eller en händelse. I ett produktregister är ett exempel på en entitet ”produkt” som är den logiska
representationen av en fysisk produkt. En produkt har ett antal attribut (ex. namn och artikelnummer), har även relationer till andra entiteter (ex. tillverkare) och i ett produktregister finns det förekomster av flera olika produkter. [6]
Figur 1 - Exempel på entitet
2.1.1.2 Attribut
En entitet definieras av beskrivande attribut, dessa attribut används även för att definiera den unika identiteten på en förekomst av en entitet, även kallat
primärnyckel [6].
Figur 2 - Exempel på attributsmodell
2.1.1.3 Relation
Genom att dra relationer mellan de identifierade entiteterna skapas en förståelse för hur de förhåller sig till varandra. Relationer beskrivs dels med hjälp av en textuell beskrivning samt med kardinalitet. Kardinalitet bestämmer hur två entiteter kan förhålla sig till varandra – en till många, många till många, etc. Det finns olika notationer för att beskriva en relation och dess kardinalitet, ur ett användarperspektiv är ofta kråkfotsnotationen populär. [7]
PRODUKT
Produkt ID is primary key for
Namn is describing for
Produktbeskri vning is describing for
Figur 3- Alternativa notationer inom ER. [23]
Kardinalitet specificerar två affärsregler, det minsta och största antalet av varje entitet som kan ingå i relationen. Minsta tillåtna kardinalitet bestämmer om
relationen är tvingande eller fri – kan A existera utan B. Största tillåtna kardinalitet bestämmer hur många relationer det kan finnas mellan två förekomster i varje entitet – för varje A kan det finnas en B, eller för varje A kan det finnas många B. [7]
Figur 4 – Exempel på kråkfotsnotation [7]
Figur 5 - Exempel på ER modell
Ovanstående bild kan utläsas som: En produkt tillverkas av en och bara en tillverkare, en tillverkare kan tillverka en eller flera produkter.
2.2 MashUp
En mashup är en webapplikation som i ett och samma grafiska interface (GUI) kan visa information från olika datakällor. Exempelvis kan foton kombineras med
Google maps genom att skapa en map mashup. Termen mashup härstammar ursprungligen från popmusiken när musiken från en låt kombineras med texten från en annan för att på så sätt skapa något nytt. [8]
2.3 ARIS MashZone
ARIS MashZone är en webbaserad applikation som tillåter användaren att analysera och visualisera data från olika källor. Datakällor kombineras till ett dataflöde (data feed) som sedan visualiseras med hjälp av ett gränssnitt för att skapa Mashups. [9]
Figur 6 - Logisk lösningsmodell för MashZone [9]
Ovanstående logiska modell visar hur datakällor (Data Sources) tillgängliggörs via dataflöden (Feeds) för att presenteras i presentationslagret (MashApp)
2.3.1 Ansluta MashZone till databas
För att ansluta MashZone till en databas används standardgränssnittet JDBC, för att detta ska fungera krävs tillgång till korrekta drivrutiner för JDBC samt
användaruppgifter för en användare med läsrättigheter till den aktuella databasen. Databasfrågorna kan vara komplexa, därmed även prestanda och tidskrävande, det är därför viktigt att öka upp cache tiden högre än vad som är standard vid vanliga databasfrågor.
För att lägga till en databasanslutning används administrationsmodulen av ARIS MashZone, med Database Connection fylls de fält i som presenteras när en ny databaskoppling skapas. [9]
2.3.2 Dataström (datafeed)
Dataström (datafeed) är en tabell som innehåller data som tillhandahålls till det grafiska presentationslagret, en dataström kan användas av flera
presentationsfunktioner (exempelvis tabeller och grafer). En dataström består av ett antal kolumner som innehåller data (text, datum eller tal), varje rad i en dataström har sitt ursprung i en eller flera datakällor (data source). En datakälla kan exempelvis vara en MS Excelfil eller en databas. När en datakälla har lästs in till en dataström går det att applicera operationer för att exempelvis konsolidera eller filtrera den data som kommer från olika datakällor, detta för att ge en större flexibilitet i användandet av data, utan att behöva modifiera källdata.
En ny dataström i MashZone skapas i ett dra-och-släpp gränssnitt där första steget är att välja typ av datakälla (CVS, MS Excel fil, XML-fil, Databas, Data feed, manuell data, ARIS PPM, wM Optimize, wM Business Events, Terracotta). När datakällorna har lagts till länkas de ihop med operationer för att sedan generera en slutgiltig output i form av en tabell som används av presentationsgränssnittet. [10]
2.3.3 Använda datakälla till presentationsvy
Datakällor används för att tilldela data till en tabell eller graf i presentationsvyerna i MashZone, där väljs vilka attribut som ska visas, hur de ska filtreras och
aggregeras. Det är även möjligt att ange standardvärden, hur värden ska aggregeras (summa, medelvärde, antal etc.).
2.4 QBIS
QBIS är en molntjänst som innehåller ett antal moduler för att stödja företag med bland annat projekthantering, tidsrapportering, CRM och ärendehantering [25]. aRway använder QBIS modul för tidsrapportering där konsulter registerar sin tid på kunduppdrag, men även för interna uppdrag.
2.4.1 QBIS Tidsrapportering
I Tidsrapporteringsmodulen QBIS Tid anger konsulter hur många minuter de spenderat på olika projektaktiviteteter under veckan. En veckorapport ska stängas varje fredag innan 12.00 och ska då innehålla samtliga aktiviteter vara registrerade. I exemplet nedan är en tidsrapport ifylld för en veckas internt arbete med
utbildning, tiden visar även hur många timmar som registrerats varje dag.
Figur 10 - Skärmbild på tidsrapport i QBIS Tid
2.4.2 QBIS Connect
QBIS Connect är en tjänst från QBIS för att koppla ihop och utbyta information mellan QBIS modulerna och företagets affärssystem. QBIS Connect
tillhandahåller ett gränssnitt som kan användas för att automatiskt synkronisera data från QBIS med övriga affärssystem. QBIS tillhandahåller även ett antal färdigutvecklade verktyg som kan användas för att exportera QBIS databastabeller som XML-filer. [25].
2.5 Databas
En databas är i grunden en samlingsplats för lagring av information.
Informationen är oftast organiserad, strukturerad och klassificerad vilket gör informationen lättillgänglig. Informationen i en databas är persistent vilket gör att den inte försvinner när programmet avslutas.
En databas måste nödvändigtvis inte vara lagrad och hanterad av en
databashanterare t.ex. SQL Server, Oracle eller IBM DB2, utan kan vara lagrad i en textfil eller ett Excel-dokument, en skillnad brukar dock vara att en traditionell databas även innehåller metadata. [14]
2.5.1 Metadata
Metadata ger information om annan typ av data, vilket kan vara information om hur stor en bild är, upplösning eller vilken typ av kamera som har använts för att ta bilden. [16]
2.5.2 JDBC
JDBC (Java database connectivity) är ett programmeringsgränssnitt som används för att få program skrivna i Java att kommunicera med databaser oavsett
databashanterare och operativsystem. [17]
2.5.3 Databashanterare
En databashanterare är ett program vars syfte är att hantera, modifiera och lagra information i databaser. [14]
2.5.3.1 Microsoft SQL Server
SQL Server 2008 R2 är i grunden ett databashanteringsverktyg som har en rad olika användningsområden. [14]
2008 släpptes SQL-Server 2008 och innehöll stora uppdateringar av bland annat i business intelligence sviten, (Analysis Services,Reporting Services samt Integration Services)[1]
2.5.3.2 Oracle
Oracle är det ledande och största databashanteringsverktyget. Den största skillnaden jämfört med Microsoft SQL Server är att Oracle använder sig av ett annat språk PL-SQL vilket är väldigt likt T-SQL.[18]
2.5.3.3 MYSQL
MYSQL är ett open source verktyg vilket mestadels används av mindre och mellanstora företag då både kostanden och inlärningskurvan är låg. Fördelen med MYSQL är att produkten är gratis.[3]
2.5.4 Relationsdatabas
I en relationsdatabas är all information lagrad och strukturerad i tabeller.
Tabellerna i sin tur knyts samman med hjälp av primära och främmande nycklar, därav benämningen relationsdatabas. Förhållandet kan vara en till(1:1) en till många(1:M) eller många till många(M:M)[14]
2.5.5 T-SQL
Transact structured query language är ett språk som används för att hämta och modifiera data från en databas. Det går att dela in funktionaliteten i tre olika delar:
Data definition language(DDL)
DDL används för att skapa och redigera objekt in en databas. Exempel kan vara skapa, modifiera, ta bort vyer, index tabeller, lagrade procedurer och andra objekt.
Data control language(DCL)
DCL relaterade kommandon används för att definiera vad för typ av kommandon en användare har rätt att exekvera och för vilka objekt.
Exempel kan vara att användaren endast har rätt att sätta in data i en tabell, men får läsa ut data från samtliga tabeller eller tvärt om.
Data manipulation language(DML)
DML kommandon används för att arbeta och underhålla med data från databasen. Exempel kan vara att hämta, sätta in, uppdatera eller ta bort data. [4]
2.5.6 Databastabeller
I en relationell databas är informationen organiserad och lagrad efter visst ämne. Det kan vara information om en kund, där kolumnerna i tabellen beskriver kunden vilket kan vara namn, adress och telefonnummer. Varje beskrivande rubrik har en motsvarande kolumn i tabellen och själva informationen är lagrad under rader.
Tabellen innehåller även nycklar där nycklarna är relationer till andra tabeller. [19]
Exempel på tabell: Tabell Activity
Tabell 1 - Exempeltabell: Activity
Activity_Id Customer_Id Activity_Name Work_Hours
1 1 Aktivitet 1 4
2 2 Aktivitet 2 6
Tabell Customer
Tabell 2- Exempeltabell: Customer
Customer_Id First_Name Last_Name Address
1 Martin Samuelsson Uppsalagatan 2
2 Alexander Törnvall Malmögatan 3
3 Sven Svensson Svenssongatan 4
2.5.6.1 Select
Select används för ange vilka kolumner som ska hämtas från tabellen i fråga. Formel 1 - SQL Select-sats
Select Activity_Name,Works_Hours From tblActivity
Resultat:
Tabell 3 - Resultat av Select-sats
Activity_Name Work_Hours
Aktivtet 1 4
2.5.6.2 Insert
För att kunna importera data in i en tabell används Insert.
Formel 2 - SQL Insert INSERT INTO tblActivity ( Activity_Name, Work_Hours ) VALUES ( 'Aktivitet 3', '8' ) Resultat:
Tabell 4 - Reslutat av insert-sats
Activity_Id Customer_Id Activity_Name Work_Hours
1 1 Aktivitet 1 4
2 2 Aktivitet 2 6
3 NULL Aktivitet 3 8
2.5.6.3 Update
För att modifiera befientlig data i en en tabell används update.
Formel 3 - SQL Update UPDATE tblActivity SET Customer_Id = '2'
WHERE Activity_Name = 'Aktivtet 3'
Resultat:
Tabell 5- Reslutat av update-sats
Activity_Id Customer_Id Activity_Name Work_Hours
1 1 Aktivitet 1 4
2.5.6.4 Delete
Vid radering av data används kommandot delete.
Formel 4 - SQL Delete DELETE FROM tblActivity
WHERE Activity_Name = 'Aktvitet3'
Resultat:
Tabell 6 - Resultat av delete-sats
Activity_Id Customer_Id Activity_Name Work_Hours
1 1 Aktivitet 1 4
2 2 Aktivitet 2 6
2.5.6.5 Join
För att kunna hämta data från fler tabeller som har en relation används joiner för att koppla ihop data via fältet där det finns en relation. Det finns flera olika typer av joiner nedan är ett antal listade:
Inner Join
Returnerar alla rader som har en träff i korresponderade kolumner.
Formel 5 - SQL Inner Join SELECT
c.First_Name, a.Activity_Time FROM
tblActivity a
INNER JOIN tblCustomer c
ON a.Customer_Id = c.Customer_Id
Resultat: Resultatet ger samtliga rader i Activity men endast Martin och Alexander då från Customer då Sven saknas I tabellen Activity.
Tabell 7- Resultat Inner Join
First_Name Work_Hours
Martin 4
2.5.6.6 Left Join
Returnerar alla rader i den vänstra tabellen där ingen träff sker i den högra tabellen blir värdet NULL.
Formel 6- SQL Left Join
Select customer.First_Name, activity.WorkHours from customer
left join activity
on customer.Customer_Id = activity.Customer_Id
Resultat:
Tabell 8- Resultat Left Join
First_Name Work_Hours
Martin 4
Alexander 6
Sven NULL
2.5.6.7 Right Join
Returnerar alla rader i den högra tabellen, där ingen träff sker i den vänstra tabellen sätts värdet till NULL.
Formel 7- SQL Right Join
Select customer.First_Name, activity.WorkHours from customer
Right join activity
on customer.Customer_Id = activity.Customer_Id
Resultat:
Tabell 9- Resultat Right Join
First_Name Work_Hours
Martin 4
Alexander 6
2.5.7 Vyer
En vy är en sparad fråga mot databasen. Genom att använda vyer går det att återanvända logik på ett enkelt sätt vilket minskar risken för felskrivningar som i sin tur kan generera fel eller oväntade resultat. [4]
CREATE VIEW v_Consultant_Profit AS BEGIN SELECT Consultant_Name, Consultant_Department, Working_Time, Price_Per_Hour, Day,
(Working_Time * Price_Per_Hour) AS Money_Per_Day FROM
Fact_Working_Hours fct INNER JOIN Dim_Consultant c
On fct.Consultant_ID = c.Consultant_ID END
2.5.8 Lagrade Procedurer
Procedurer används för att göra programmatiska transformeringar av data vilket tillskillnad från en vy ger fler möjligheter att modifiera och hämta data. En procedur ger även möjligheten att använda in-parametrar vilket möjliggör att skicka in värden till proceduren som därefter tar hänsyn till vid exekveringen av SQL-satsen.
Till skillnad från en vy exekveras en procedur med hjälp av
exekveringskommando exempel: exec procedur namn,@in_parameter [4]
2.5.9 Common table expression
Common table expressions är en form av temp-tabell som endast existerar under exekvering av SQL-koden. Tabellen kan i sin tur kan anropas vid följande sql-kommandon: Select, Insert, Update och Delete. Common table expressions kan med fördel användas i vyer för att göra en typ av modifiering som senare anropas i själva huvudfrågan.
I frågan nedan summeras först aktiviteter per dagar istället för timmar och stoppas därefter in i en common table expression tabell (cteActivityByHours). Tabellen joinas senare in i nästa fråga och tiden per timma summeras per månad för varje aktivitet.[21]
WITH
cteActivityByHours(ActivityID, Hours))
AS
(
SELECT ActivityID, sum(activity_time_hours/24) as Days
FROM tbl_Activity GROUPBY ActivityID ) SELECT ActivityName, left(time_id,6) AS YearMonth, sum(Hours) AS ActivityTime FROM Activity AS a
INNERJOIN cteActivityByHours AS at ON a.activity_ID= at.Activity_ID GROUP BY
ActivityName,
left(time_id,6) Order By ActivityName
Figur 12 - Exempel på Common Table Expression Resultat:
Tabell 10- Resultat av Common Table Expression
Activity_Name Work_Days per month
Aktivitet 1 0,25
2.5.10 Parameter
En parameter i SQL används för att dynamisk ändra villkoren på en fråga som ställs mot databasen. Parametrar kan ändras från exempelvis en rapport där användaren gör val i en lista, t.ex där resultatet ska täcka ett visst tidsintervall. Parametrar kan även sättas dynamiskt inne i en lagrad procedur där t.ex resultatet ska baseras på dagens datum.[22]
Nedan är ett exempel på hur resultatsetet dynamiskt levererar endast data baserat på dagens datum.
DECLARE @Date INT SET @Date = Getdate() SELECT *
FROM ProjectTime WHERE Date = @Date
Figur 13 - Exempel på parameter
2.5.11 SQL Server integration services
Integration services är ett verktyg för att extrahera, transformera och ladda(ETL) in data till t.ex. en databas. Laddningen kan göras från flera olika käll-system där integration services har komponenter för att sammanfoga data. [5,13]
2.5.11.1 Grundkomponenter
Control flow elements
Control flow elements är själva grundkomponenten i SSIS där en typ av uppgift ska utföras. Varje control flow elements består i sin tur av data flow elements där det finns olika typer av komponenter för at bearbeta data. Exempel på
komponenter kan vara Data Flow Task vilket används för att definiera samt sätta upp olika typer av transformeringar av data för en given källa.[13]
En annan komponent kan vara Execute SQL Task vilket används för att exekvera SQL kod, vilket kan användas från allt till att skapa upp en ny tabell, till att tömma en tabell på data innan ny data ska laddas in till en tabell.[13]
Andra komponenter kan vara Send Mail Task vilket används för att sända mail när en viss typ av uppgift har slutförts t.ex. om något har gått vid fel vid inladdning av data går det här att ange att ett mail ska skickas till ansvarig person. [13]
Data flow elements
Data flow elements är ett underobjekt till control flow elements där det ingår olika komponenter för att extrahera, modifiera samt ladda data från och till olika
2.5.11.2 Paket
Ett SSIS paket består av ett antal komponenter, data flow elements, control flow elements, anslutningar, variabler, parametrar och konfigurationer. Hela flödet sparas ner till en .dtsx fil som sedan kan exekveras med hjälp av ett SQL-jobb, kommandoprompt eller manuellt. Normalt sätt används ett paket per objekt, t.ex. ett paket för kund eller säljare. [13]
2.5.11.3 SSIS package store
SSIS-package store innebär att .dstx filerna sparas ner på SQL-servern där de lagras och kan exekveras med hjälp av SQL-jobb.[5]
2.6 XML
XML står för Extensible Markup Language och främst till för att förmedla metadatasturktur på ett strukturerat sätt, språket är även tänkt att vara läsligt för både datorer och människor. Till skillnad från HTML som är designat för att visa data är XML designat för att beskriva vad informationen är. [2]
En annan skillnad mellan HTML och XML är att i XML definierar sina egna taggar vilket kan vara <From>Alexander</From>, men HTML är styrd av fördefinierade taggar vilket kan vara <h1> som anger rubriktyp 1.[2]
Figur 14- Exempel på XML struktur:
XML har ett flertal olika användningsområden, nedan är ett antal användningsområden listade:
Konfigurationsfiler Hemsidor
Dokumenthantering Databaser
I SSIS(SQL Server integrations Services) används XML bland annat till att lagra konfigurationsinformation vilket t.ex. kan vara inloggningsinformation till en datakälla. [2]
3 Genomförande
Kartläggning och kravställning har tagits fram tillsammans med representanter från aRway i deras lokaler i Stockholm, utvecklingsarbetet har skett på distans. Utvecklingen har skett uppdelat i tre releaser, mellan varje release har det gjorts en demo diskussion kring förbättringsförslag.
3.1 Beskrivning av nuläget
3.1.1 Planera beläggning
Planeringsmässigt finns det en detaljerad plan för varje konsults nästkommande 6 veckor, som uppdateras i Excel av VD, med kommentarer på intranätet från konsulterna om planen avviker mot deras bild om kommande veckor. Detta medför mycket merarbete för konsulter och VD.
3.1.2 Rapportering av arbetad tid
Varje konsult på aRway måste innan veckan är slut rapportera in hur många timmar som levererats till vilka kunder och vilka projekt genom att registrera tiden på tilldelade aktiviteter. Denna tid registreras i tidsrapporteringssystemet QBIS, det är ett accepterat arbetssätt på aRway. Det förekommer även att konsulter måste rapportera i separat tidsrapporteringssystem för vissa kunder, i de fallen rapporteras tiden i både i QBIS och i kundens tidsrapporteringssystem.
3.1.3 Leveransansvariga
Den roll på företaget som ansvarar för planering och uppföljning av kundleveranser kallas leveransansvarig. De ansvarar bland annat för att:
Säkerställa reservresurser i händelse av t.ex sjukdom eller tillfälligt ökad arbetsbelastning
Säkerställa att leveransen till kund och därmed aRways debitering ligger i fas med beställning/planering
Omplanera aktiviteter vid arbetstidsbortfall (sjukdom, semester, etc) Ansvara för status- och avvikelserapportering till ledning samt kund enligt
fastställd rutin
Det är därmed dessa personer som har behov av att visallisera prognoser och utfall för att underlätta och effektivisera deras arbete.
3.1.4 Följa upp leverans
Uppföljningen på aRway var vid starten av projektet väldigt haltande, det finns ingen rimlig bedömning över hur stor intäkterna kommer vara innan fakturorna var utställda till kund.
Företaget visste först i mitten av nästkommande månad hur stor faktureringen blev för en månad, vilket ledde till att det blev svårt att hålla koll på det
ekonomiska läget. Detta på grund av att leveransansvariga för kunder måste gå in och kontrollera och korrigera fakturorna som kommer ut från
tidsrapporteringssystemet och detta kan inte göras innan månaden är stängt och all tid har rapporterats.
3.2 Identifiera krav på lösning
Följande högnivåkrav på lösningen identifierades tillsammans med företaget 1. Utfall föregående perioder
2. Prognos kommande perioder
3. Beläggningsplan per uppdrag kommande 6 veckor 4. Beläggningsplan per konsult kommande 6 månader 5. Avvikelse utfall mot plan föregående period
3.2.1 Utfall föregående perioder
Det skall vara möjligt att se utfall på föregående period (vecka/månad). Det är viktigt att utfallet inte bara går att utläsa i timmar, utan även i kronor.
3.2.2 Prognos kommande perioder
Det ska vara möjligt att se prognosen för kommande perioder (vecka/månad). Det är viktigt att prognosen inte bara går att utläsa i timmar, utan även i kronor
3.2.3 Beläggningsplan per uppdrag kommande 6 veckor
För kommande 6 veckor ska det vara möjligt att per uppdrag se vilka konsulter som är planerade.
3.2.4 Beläggningsplan per konsult kommande 6 veckor
För kommande 6 månader ska det vara möjligt att per uppdrag se vilka uppdrag en konsult är planerad på.
3.2.5 Avvikelse utfall mot plan föregående period
Det ska vara möjligt att jämföra utfall mot plan för föregående period (vecka och månad)
3.3 Första iterationen
Målen med första iterationen var att identifiera hur befintliga
tidsrapporteringssystemet (QBIS) kan användas för att skapa leveransplaner för konsulter. Det har tidigare gjorts försökt på aRway att använda QBIS till att planera beläggning för konsulter och uppdrag, men utan framgång på grund av att QBIS inte klarar av en ojämn beläggning (vecka för vecka) på konsulter, något som är vanligt för konsulter på företag som arbetar på parallella projekt hos olika kunder samtidigt. Målet med första iterationen är att identifiera ett sätt att planera beläggning för konsulter i QBIS, och att publicera data i MashZone
3.3.1 Registrera arbetad tid
Det fanns redan en rutin för att registrera arbetad tid i QBIS där konsulter senast 12.00 varje fredag ska ha fyllt i sin tidsrapport. Med tillgång till rapporterad data kan uppföljning på konsulter och uppdrag göras.
3.3.2 Leveransplanering
För att få till en korrekt uppföljning behöver både planeringsdata och utfall vara tillgängligt i uppföljningsverktyget MashZone. För att lösa detta diskuterades två olika alternativ:
Att fortsätta med planeringen i Excel Få in planeringen i QBIS
Efter diskussioner med aRway beslutades att sluta använda Excel och istället lägga in planeringen i QBIS, på grund av att i Excel skulle det kunna finnas många varianter av Excel och någon skulle få sitta och samla in data från alla konsulter och aggregera det. Genom att välja QBIS användes det befintliga systemet, vilket gör det smidigare för konsulter att få tillgång till denna vy i samma verktyg. Kraven på leveransplaneringen är att ha möjlighet att planera en konsult mot en kund på övergripande nivå 6 månader framåt, och närmaste 6 veckorna mot ett projekt hos kunden.
Ingen standardlösning för att lägga in leveransplaner i QBIS (exempelvis att använda en budget per aktivitet) lyckades tillgodose företagets krav på att ha en varierande beläggning per konsult och uppdrag.
Genom att för varje kund skapa ett nytt projekt för i QBIS, men använda det för att rapportera leveransplanering kan leveransansvariga registrera tid på dessa aktiviteter i konsulters tidsrapporter som ligger i framtiden. Därmed går det att få ut data för leveransplan på samma sätt som för utfall. För att inte påverka
faktureringen kommer dessa leveransplaneringsaktiviteter att ersättas med riktiga tidsrapporteringsaktiviteter av konsulten när denne rapporterar sin vecka.
3.3.3 Exportera Data (Manuellt)
För att göra analyser på leveransplaner och utfall behöver rapporterad data finnas tillgänglig i MashZone. För att få detta på plats undersöktes om det från QBIS var möjligt att med hjälp av rapporter exportera ut detta. Standardrapporterna som finns tillgängliga i QBIS undersöktes utan att nå önskat resultat.
För att kunna importera data från QBIS ställde det krav på att verktyget kunde leverera ett generellt supporterat fil-format och valet föll på att använda XML. För att lyckas med detta föll valet på att använda ”QBIS connect”, en tjänst från QBIS som möjliggör anrop mot deras servers med hjälp av standardiserade anrop och de tillhandahöll även en standardlösning ”QBISSaveAsXML” (se bild), ett GUI-verktyg som exporterar ut samtliga databastabeller från QBIS till XML-filer och placerar dem i en mapp på datorn.
3.3.4 Analysera XML Struktur från QBIS
För att förstå informationsstrukturen i QBIS analyserades XML-filerna för att identifiera vilka som innehöll viktig information.
3.3.4.1 Format på XML-filer
Då metadata är viktigt för att kunna identifiera data typ samt teckenlängd är XML ett ypperligt format att använda vid import av data. Nedan är hur till exempel kund lagras i XML-strukturen.
Figur 17 - Exempel på XML-struktur för Customer
Genom att analysera XML-strukturen skapades en attributmodell per XML-fil. Nedan syns exempel på attributsmodellen för kund.
Figur 18 - Attributsmodell för Customer
3.3.4.2 Hur XML-filer relaterar till varandra
För att förstå hur XML-filerna relaterar till varandra skapades en
informationsmodell för att koppla ihop XML-filer med främmande nycklar till varandra. Informationsstrukturen i XML-filerna används senare för att förstå vilka vyer som ska tas fram från SQL-databasen.
3.3.5 XML läsning (Mash Zone)
Datavyn i första iterationen var en ihopkoppling av xml-filer direkt från servern. Det finns standardlösning för att lägga in XML-filer i en Data Feed i Mash Zone, tyvärr var XML-filerna för stora vilket gjorde det omöjligt att koppla ihop i detta enkla verktyg.
Figur 20 - Inläsning av XML-fil till MashZone
3.3.6 Presentera data
I presentationsgränssnittet skapas vyer med tabeller och grafer som visualiserar den data som har tillgängliggjorts med hjälp av inläsningen av XML-filerna. I första iterationen var målet att undersöka om det var möjligt att presentera data från QBIS. Men eftersom att det redan upptäckts att datainläsningen var
begränsad och inte skulle uppfylla verksamhetens krav byggdes endast en enkel vy för att visa att det gick att visa något som läst ut ifrån QBIS.
3.3.7 Slutsatser från första iterationen
Slutsatser från första iterationen var att det var möjligt att exportera data från QBIS och presentera i MashZone, även om det inte var möjligt att gå direkt mot XML-filerna. För att lösa detta behövdes en lösning med en databas för att möjliggöra modifiering och aggregering av XML-filerna för att tillhandahålla snabbare analyser.
3.4 Andra iterationen
Målet andra iterationen var att med god prestanda visa tabeller och grafer i Mash Zone baserat på vyer från en databas.
Skapa databas i Microsoft SQL-Server Importera XML-filer till SQL-server Skapa vyer i SQL-databasen
Skapa presentationsgränssnitt i MashZone
Publicering av vyer för uppföljning från SQL server till MashZone
3.4.1 Skapa databas i Microsoft SQL-Server
För att hålla nere eventuella licenskostnader för lösningen undersöktes olika open source alternativ, men det framkom att det redan fanns Microsoft SQL Server 2008 R2 på företaget som användes för bland annat ekonomisystemet, den kunde användas istället för att sätta upp en ny server.
För att lagra informationen på servern skapades en ny databas upp på den
befintliga SQL-servern. Databasen användes sedan för import av data från QBIS, där för varje XML-fil skapades en motsvarande tabell i databasen.
3.4.2 Import av data från XML-filer till Datalager
För att ladda in data från QBIS till databasen i SQL-Server användes SQL Server Integration Services (SSIS), där denna iteration endast fokuserade på att ladda in data utan felhantering eller någon sepparat hantering av varje gränssnitt.
Detta uppnåddes genom att för varje xml fil bygga en komponent i SSIS som först läser XML-filen för att sedan exportera den till en tabell med motsvarande namn i databasen.
3.4.3 Vyer mot datalager
Vid kravställning på vyer från databasen konstaterades att det centrala objektet (faktatabellen Rapporterad tid) var Activity Time. Varje tidsrapporteringsaktivitet som görs i systemet skapar en ny rad i tabellen Activity Time. De dimensionerna som faktatabellen ska sorteras mot är konsult, kund, datum och projekt (se figur nedan)
Figur 23- Informationsmodell för kravställning på vyer
Kravet i första versionen var att presentera registrerade timmar för anställd på projekt, och kund aggregerat och summerat per månad samt vecka. För att uppnå detta skapades en vy i datalagret där MashZone kan läsa ifrån.
När en konsult skapar en tidsrapport rapporteras det som antal minuter per aktivitet i ett projekt, för att skapa en uppföljning på företaget behöver data
aggregeras och presenteras per konsult, kund, projekt. Vyn slår även ihop förnamn och efternamn i samma fält, konverterar till arbetade timmar. Två olika vyer har skapats, en som aggregerar per månad och en som aggregerar per vecka.
3.4.3.1 Vy för Månad
Utan att aggregera data per månad, blev det för många poster i MashZone, därför skapa des en vy för att aggregera data per månad. Data aggregeras för varje
konsult och avdelning.
CREATE VIEW [dbo].[vConsultantHoursMonth_Version0.7] AS
select
e.employee_first_name+' '+e.employee_last_name AS EmployeeName
,employee_department
,customer_name
,left(d.DateKey,6) As YearMonth
,SUM(fct.activity_time_minutes/60) AS ActivityTimeHours from ActivityTime fct
inner join Employees e
on fct.activity_time_employee_id = e.EMPLOYEE_ID
inner join ProjectActivities pa
on fct.activity_time_activity_id = pa.PROJECT_ACTIVITY_ID
inner join Projects p
on pa.project_activity_project_id = p.PROJECT_ID
inner join Customer c
on p.project_customer_id = c.CUSTOMER_ID
inner join Date d
on fct.activity_time_date = d.FullDate group by
e.employee_first_name+'
'+e.employee_last_name,employee_department,customer_name
,left(d.DateKey,6)
3.4.3.2 Vy för Vecka
I MashZone går det inte att ange datum för en vecka (år-vecka) utan verktyget behöver en faktisk dag(år-månad-dag) att utgå ifrån för att datumfiltrering ska fungera.
Efter kontakt med leverantören av MashZone som inte hade någon lösning på detta beslutades att i vyn aggregera data till måndagen i varje vecka så att den tid som rapporterats måndag-söndag läses ut som om den rapporterats på måndagen. När alla data ligger registrerad på måndagar är det möjligt att få uppföljning med vecko-intervall.
CREATE VIEW [dbo].[vConsultantHoursWeek_Version0.7] AS select EmployeeName, employee_department, customer_name, ActivityTimeHours, FullDate, project_name, project_activity_name FROM ( select
e.employee_first_name+' '+e.employee_last_name AS EmployeeName
,employee_department
,customer_name
,project_name
,project_activity_name
,CAST(CAST(d.CalendarYear as varchar)+cast(d.WeekOfYear as varchar) AS int) AS YearWeek
,sum(cast(fct.activity_time_minutes as numeric(10,2))/60) AS ActivityTimeHours from ActivityTime fct
inner join Employees e
on fct.activity_time_employee_id = e.EMPLOYEE_ID
inner join ProjectActivities pa
on fct.activity_time_activity_id = pa.PROJECT_ACTIVITY_ID
inner join Projects p
on pa.project_activity_project_id = p.PROJECT_ID
inner join Customer c
on p.project_customer_id = c.CUSTOMER_ID
inner join Date d
on fct.activity_time_date = d.FullDate group by e.employee_first_name+' '+e.employee_last_name ,employee_department ,customer_name ,project_name ,project_activity_name
,CAST(CAST(d.CalendarYear as varchar)+cast(d.WeekOfYear as varchar) AS int) )sub
inner join Date d
on sub.YearWeek = CAST(CAST(d.CalendarYear as varchar)+cast(d.WeekOfYear as varchar) AS int)
where DayOfWeek = 2 Figur 25 - Vy för Vecka
3.4.4 Presentera data
Möjlighet att se utfall (timmar) per kund och projekt fördelat per vecka. Även möjlighet att filtrera vyn för en specifik konsult.
Möjlighet att se fördelningen av timmarna på olika kunder
Möjlighet att följa upp på utfallet aggregerat på vecka och månad Separata flikar för aggregering på vecka eller månad
3.5 Tredje iterationen
Största förbättringarna i tredje iterationen:
Automatisk import av XML-filer till SQL-databas
Automatisk import av prognosdata till tabell för historisk prognosdata Möjlighet att jämföra plan med utfall
Möjliggöra att uppföljning på intäkter i kronor, inte bara timmar
Möjlighet att jämföra utfall med leveransplan (både kronor och timmar) ”Trafikljus” för att visa resultat av uppföljning (över eller under mål)
3.5.1 Exportera Data (Automatiskt)
Standardlösningen från QBIS för att exportera XML-filer krävdes att en
användare manuellt startade en applikation för att starta exporten av XML-filer. Från QBIS beställdes en uppgradering av QBISSaveAsXML, med kraven att det ska vara möjligt att med fördefinierade parametrar (autentiseringsuppgifter och destination för filerna) schemalägga en aktivitet på servern som exporterade XML-filer vid önskade tidpunkter.
Den uppdaterade versionen av QBISSaveAsXML schemalades att köra varje vecka, måndag och fredag, för att på så sätt ofta ha tillgång till aktuell data från QBIS.
3.5.2 Automatisk uppdatering av data med hjälp av ”Jobb”
För att kunna ladda data vid ett givet datum och tid användes SQL-serverns inbyggda scheduleringsverktyg. Där angavs att data ska laddas en gång i veckan efter att företagets planeringsmöten har ägt rum.
3.5.3 Ekonomisk prognos och uppföljning
I tidigare iterationer hade det endast varit möjligt att följa upp och planera en konsults antal timmar mot en kund (och projekt). För att klara av den ekonomiska prognosen och uppföljningen var det nödvändigt att räkna om timmarna till kronor. Det finns i QBIS flera nivåer där pris kan anges:
Pris/timme för konsulten Pris/timme för projektet Pris/timme för projektaktivitet
Pris/timme för projektmedlem (unikt för konsulten i ett unikt projekt) Pris/timme för projektaktivitetsutförare (unikt för varje konsult på varje
aktivitet)
En konsult på aRway kan ha olika pris, på olika aktiviteter i samma projekt, och olika konsulter kan ha olika priser för samma aktiviteter. Därför var det
nödvändigt att gå på det mest detaljerade alternativet där varje projektaktivitets pris är unikt kopplat till en konsult. Om det inte hade funnits ett bra stöd i QBIS för att hantera detta hade det tvingat fram ett valt av en grövre lösning.
3.5.4 Laddning av Prognosdata
För att kunna jämföra planen med utfall krävs möjligheten att få upp båda dessa i samma tabell/graf i presentationslagret, och om dessa ska komma från samma vy måste både leveransdata och utfall registreras på samma tidsrapport för konsulter. Det skulle innebära att en vanlig vecka där en konsult arbetat 40 timmar ska det vara 80 timmar rapporterade, vilket skulle medföra mycket brus och gör det ohållbart i längden.
För att kunna göra historiska uppföljningar av prognoser mot det faktiska utfallet skapades en ny databastabell där nästkommande veckas prognos sparas ner och datummärks med hjälp av en lagrad procedur. I proceduren sätts dynamiskt nästkommande veckas tidsspann till måndag till söndag.
-- ============================================= -- Author: Alexander Törnvall
-- Create date: 2014-02-08
-- Description: Load historic consultant hours forecast -- Changes:
-- =============================================
CREATE procedure [dbo].[Load_ConsultantHoursForecastHistory]
as begin
--Declare variables
declare @FirstDayNextWeek datetime declare @LastDayNextWeek datetime
--Set first day of next week
set @FirstDayNextWeek = DATEADD(WK,DATEDIFF(WK,0,GETDATE()),7)
--Set last day of next week
set @LastDayNextWeek = DATEADD(WK,DATEDIFF(WK,-6,GETDATE()),6)
--Inserts records into table ConsultantHoursForecastHistory
insertinto [dbo].[ConsultantHoursForecastHistory]
(
Employee_Id
, ProjectNameHistoric , ProjectActivity_Id , ProjectActivityNameHistoric , activity_time_Id , activity_time_minutes , activity_member_cost_per_hour , activity_member_price_per_hour , activity_time_date ) select e.EMPLOYEE_ID , e.employee_first_name+' '+e.employee_last_name AS EmployeeName , e.employee_department , c.CUSTOMER_ID , c.customer_name , p.PROJECT_ID , p.project_name , pa.PROJECT_ACTIVITY_ID , pa.project_activity_name , fct.ACTIVITY_TIME_ID , fct.activity_time_minutes , a.activity_member_cost_per_hour , a.activity_member_price_per_hour , fct.activity_time_date from ActivityTime fct
innerjoin Employees e
on fct.activity_time_employee_id = e.EMPLOYEE_ID
innerjoin ProjectActivities pa
on fct.activity_time_activity_id = pa.PROJECT_ACTIVITY_ID
innerjoin Projects p
on pa.project_activity_project_id = p.PROJECT_ID
innerjoin Customer c
on p.project_customer_id = c.CUSTOMER_ID
innerjoinDate d
on fct.activity_time_date = d.FullDate
leftjoin dbo.ActivityMembers a
on fct.activity_time_activity_id =
a.activity_member_activity_id
and fct.activity_time_employee_id =
a.activity_member_employee_id
where fct.activity_time_date between @FirstDayNextWeek and @LastDayNextWeek
end
3.5.5 Vyer mot datalager
3.5.5.1 Vy för Månad (utfall)
I den tredje iterationen var målet att även kunna se intäkt per timma och inte enbart tid. Vyn nedan ger möjlighet att följa upp rapporterad tid samt intäkter per timma.
Detta görs med hjälp av en vy innehållandes en common table expression där intäkt och rapporterad tid först aggregeras upp till timma för att i slutresultatet aggregeras och summeras upp per månad. Då begränsningar i
slutanvändarverktyget kräver att resultatet lagras på ett givet datum har all månades data placerats på den första dagen i månaden.
I föregående iteration skapades även en vy upp aggregerad per vecka, denna justerades i den tredje iterationen till att även innehålla intäkt per timma.
-- ============================================= -- Author: Alexander Törnvall
-- Create date: 2014-02-08
-- Description: Returns consultant hours by month.
-- Due limitation in end user tool all time for each month is added to the first day in month.
-- Changes:
-- =============================================
CREATE VIEW [dbo].[vConsultantHoursMonth] AS with cte as ( select EmployeeName,
EmployeeDepartment,CustomerName,ProjectName,ProjectActivityName,left(d.DateKey,6) As YearMonth,sum(ActivityTimeHours) as ActivityTimeHours,sum(ActivityMemberCost) as ActivityMemberCost,sum(ActivityMemberPrice) as ActivityMemberPrice
from
(
--Retrieves data on day level
select
e.employee_first_name+' '+e.employee_last_name AS EmployeeName,employee_department AS EmployeeDepartment,customer_name as CustomerName,project_name as
ProjectName,project_activity_name as
ProjectActivityName,fct.activity_time_date,sum(cast(activity_time_minutes as numeric(10,2))/60) AS ActivityTimeHours,sum(cast(activity_time_minutes as
numeric(10,2))/60) * isnull(activity_member_cost_per_hour,0) as ActivityMemberCost --sum total cost per hour by activity,sum(cast(activity_time_minutes as
numeric(10,2))/60) * isnull(activity_member_price_per_hour,0) as ActivityMemberPrice
--sum total price per hour by activity
from ActivityTime fct
inner join Employees e
on fct.activity_time_employee_id =
e.EMPLOYEE_ID
inner join ProjectActivities pa
on fct.activity_time_activity_id =
pa.PROJECT_ACTIVITY_ID
inner join Projects p
on pa.project_activity_project_id =
p.PROJECT_ID
inner join Customer c
on p.project_customer_id =
c.CUSTOMER_ID
left join dbo.ActivityMembers a
on fct.activity_time_activity_id = a.activity_member_activity_id and fct.activity_time_employee_id = a.activity_member_employee_id group by e.employee_first_name+' '+e.employee_last_name , employee_department , customer_name , project_name , project_activity_name
, fct.activity_time_date
)sub
inner join Date d
on sub.activity_time_date = d.FullDate group by EmployeeName , EmployeeDepartment , CustomerName , ProjectName , ProjectActivityName , left(d.DateKey,6) ) --Main select select EmployeeName , EmployeeDepartment , CustomerName , ProjectName , ProjectActivityName
, convert(datetime,convert(varchar(50),cast(YearMonth + '01' as datetime),21)) as FullDate --Convert YYYYMM to datetime format due end user application have low row limit, so all montly data needs to be stored on a actual date. , ActivityTimeHours , ActivityMemberCost , ActivityMemberPrice from cte
3.5.5.2 Vy för Månad (Prognos)
För att kunna följa upp historiskt lagda prognoser skapades i ett tidigare skede en tabell där nästkommande veckas prognos sparades ner och tidsstämplades. I tidigare iterationer har endast den aktuella prognosen varit tillgänglig med tanke på att all data varje veckas laddas om och då endast speglar den aktuella bilden.
Vyn nedan läser data från historiktabellen där de historiskt lagrade prognoserna lagras. Därefter aggregeras och summeras rapporterad tid samt intäkt per timma för varje veckas prognos ihop per månad, för att i sista skedet placeras på första dagen varje månad på grund av begränsningar i slutanvändarverktyget.
Beställaren vill även ha möjlighet att kunna följa upp prognos per vecka, därav skapades ytterligare en vy aggregerad per vecka.
-- ============================================= -- Author: Alexander Törnvall
-- Create date: 2014-02-08
-- Description: Returns forecasted consultant hours by week.
-- Due limitation in end user tool month needs to be stored on the first day in month -- Changes:
-- =============================================
CREATE VIEW [dbo].[vConsultantHoursMonthForecastHistory]
AS
--Common table expression is used in order to sum on lowest level
with cte as ( select EmployeeName , EmployeeDepartment , CustomerName , ProjectName , ProjectActivityName
, sum(cast(activity_time_minutes asnumeric(10,2))/60) as ActivityTime
, sum(cast(activity_time_minutes asnumeric(10,2))/60) * activity_member_cost_per_hour as
ActivityMemberCost
, sum(cast(activity_time_minutes asnumeric(10,2))/60) * activity_member_price_per_hour as
ActivityMemberPrice , activity_time_date from ( select isnull(e.employee_first_name+' '+e.employee_last_name,fct.EmployeeNameHistoric)as EmployeeName
, isnull(employee_department,fct.EmployeeDepartmentHistoric) as
EmployeeDepartment
, isnull(customer_name,fct.CustomerNameHistoric)as CustomerName
, isnull(project_name,fct.ProjectNameHistoric) as ProjectName
, isnull(project_activity_name,fct.ProjectActivityNameHistoric)as
ProjectActivityName
, activity_time_minutes
, activity_member_cost_per_hour
, activity_member_price_per_hour
, activity_time_date
from dbo.ConsultantHoursForecastHistory fct
leftjoin Employees e
on fct.Employee_Id = e.EMPLOYEE_ID
leftjoin ProjectActivities pa
on fct.ProjectActivity_Id = pa.PROJECT_ACTIVITY_ID
leftjoin Projects p
on fct.Project_Id = p.PROJECT_ID
leftjoin Customer c
on fct.Customer_Id = c.CUSTOMER_ID
innerjoinDate d
on fct.activity_time_date = d.FullDate )sub groupby EmployeeName , EmployeeDepartment , CustomerName , ProjectName , ProjectActivityName , activity_member_cost_per_hour , activity_member_price_per_hour , activity_time_date )
--End common table expression --Main select
select
EmployeeName
Converting back to datetime format due limitations in end user tool , ActivityTime as ActivityTimeHours , ActivityMemberCost , ActivityMemberPrice from (
--Aggregates and sum data on month level
select EmployeeName , EmployeeDepartment , CustomerName , ProjectName , ProjectActivityName
, sum(ActivityTime) as ActivityTime
, sum(ActivityMemberCost) as ActivityMemberCost
, sum(ActivityMemberPrice) as ActivityMemberPrice
, left(d.DateKey,6) as YearMonth
from cte fct
innerjoinDate d
on fct.activity_time_date = d.FullDate groupby EmployeeName , EmployeeDepartment , CustomerName , ProjectName , ProjectActivityName , left(d.DateKey,6) )sub
3.5.6 Datakälla för prognos och utfall
Två typer av datakällor för prognos och utfall har skapats, en för aggregering på månad och en för vecka. Dessa vyer används både för att följa upp resultatet, visa prognosen, men även att jämföra resultat med prognos.
På grund av radbegränsningar i MashZone måste inläsningen begränsas, genom SQL kommandot DATEADD vid inläsningen laddas endast data från senaste året in för månadsvyn och senaste 6 månaderna för veckovyn. Detta för att skapa en mer hanterbar mängd data.
Formel 8- SQL-sats för inläsning till MashZone
SELECT * FROM vConsultantHoursMonth
WHERE FullDate > DATEADD(year,-1,GETDATE()) AND Fulldate < GETDATE()
För att möjliggöra en jämförelse mellan prognos och utfall konkateneras dessa två datavyer ihop till en datakälla i MashZone. När båda datakällorna kombinerats till en gemensam, exkludera den interna tiden, den interna tiden identifieras genom att kunden är aRway AB eller aRway Development.
Figur 31 - Filtrering av interna tidsposter
3.5.7 Presentera data
Grafisk uppdatering av gränssnittet Jämföra plan med utfall
Uppföljning på intäkter i kronor, inte bara timmar
Möjlighet att jämföra utfall med leveransplan (både kronor och timmar) ”Trafikljus” för att visa resultat av uppföljning (över eller under mål)
4 Resultat
4.1 Beskrivning av lösning
Utvecklingsarbetet har resulterat i en lösning som bygger på QBIS Tid, QBIS Connect, Windows Task Scheduler, Microsoft SQL Server 2008 samt ARIS Mashzone.
Figur 34 - Övergripande beskrivning av lösningen
Registrera leveransplan och tidsrapportera
Leveransansvariga registrerar leveransplaner för varje konsult i QBIS Tid, och konsulter registrerar sin arbetade tid i samma verktyg.
Importera data till SQL server
Varje måndag och fredag startar Windows Task Scheduler ett jobb i QBIS
Connect som anropar QBIS Tid och hämtar hem samtliga datatabeller som XML-filer och strax där efter startas ett nytt jobb i Microsoft SQL Server som läser in XML-filerna med hjälp av SSIS-script.
Spara historisk prognos
För att inte leveransplaner (prognos) ska skrivas över när konsulter tidsrapporterar sparas dessa i en separat tabell. Varje tisdag kväll (samma dag som planeringsmöte för leveransansvariga) läses nästkommande veckas prognos in till prognos
historiken. Denna används sedan för att jämföra prognos mot utfall.
Publicera vyer från databasen
Från SQL-databasen publiceras ett antal vyer för prognos och utfall (med aggregering på vecka och månad) för att användas av MashZone.
Visualisera prognos och utfall
I MashZone skapas ett antal datakällor baserat på vyerna som publiceras från SQL-databasen. I MashZone visualiseras dessa datakällor i tabeller och grafer i ett antal olika tabbar (MashApps):
Uppföljning Vecka - Jämför prognos och utfall aggregerat på vecka Uppföljning Månad - Jämför prognos och utfall aggregerat på månad Prognos Månad – Prognos 6 månader framåt i tiden aggregerat på månad Prognos Vecka – Prognos 6 veckor framåt i tiden aggregerat på vecka Beläggningsplanering – Stöd för beläggningsansvarig att identifiera
konsulter utan full beläggning i prognosen
Leveransplanering – Stöd till konsulter att se sin leveransplanering framåt i tiden, vilka kunder och projekt de är planerade på.
Figur 35 - Slutgiltiga presentationsgränssnittet för verktyget
4.2 Förbättring vid införande av visualiseringsverktyg
Verktyget har införts framförallt till leveransansvariga som använder verktyget vid veckovisa avstämningar där de tillsammans följer upp och planerar bemanningen av kunduppdrag. Vid starten av arbetet kunde företaget först nästkommande månad se resultat för månaden och det saknades möjlighet till prognos annat än sålda timmar. Med hjälp av verktyget är det möjligt att redan dag ett i månaden se en prognos för månaden och därmed fylla eventuella luckor i beläggningen, vilket har gjort att ej debiterbara timmar har minskat.4.3 Uppkomna problem
Vid utveckling av verktyget har författarna stött på ett antal problem längs vägen och det har stundtals varit en uppgiven kamp. De flesta av problemen har relaterat till felaktiga inläsningar eller presentation av data, författarnas oerfarenhet kring dessa områden resulterade i långa felsökningar för att åtgärda problemen. Orsakerna till problemet har varierat från begränsningar och felkonfiguration av verktyg, till att användare inte följt angivna instruktioner.
4.3.1 Problem vid inläsning av data från QBIS
Under perioder har problem i den automatiska inläsningen av XML-filer
uppdagats, både på grund av att konsulter felaktigt har tidsrapporterat i förväg (för att använda som sin egen planering) och på grund av ett maxvärde på
kommentarfältet i tidsrapporten som hängde hela inläsningen av XML-filer under en månads tid för att fältet i SQL-databasen var fel dimensionerat.
4.3.2 Aggregering på vecka och månad
MashZone saknar möjligheten att filtrera och aggregera data per vecka, det är inte heller möjligt att använda standardiserade lösningar med veckotaggar. Lösningen på detta problem var att i SQL-vyn aggregera samtliga tidsposter på en vecka till måndagen i veckan. Detta ger möjlighet att i MashZone analysera resultat per vecka.
Eftersom att en vecka kan vara delad i tvåmånader (en tisdag kan vara sista dagen i en månad) skapades ytterligare en vy för att beskriva månader. Detta eftersom att tid som rapporterats på månadens första dagar kunde läggas in på veckans första dag som inte nödvändigtvis var i samma månad.
En vy som läste in samtliga tidsrapporteringsposter utan aggregering slog efter några månader i taket på antalet rader som MashZone klarar av att hantera, därför behövde data aggregeras per månad.
Den nya vyn kom inte bara runt problemet med antalet rader, analyserna blev även mycket snabbare på grund av att mängden data minskat drastiskt.
