omis 661 – final project
TRANSCRIPT
JaganGurumurthy–Z1788917SethushankarSadasivasubramanian–Z1788022NORTHERNILLINOISUNIVERSITY|DEKALB,IL
OMIS661–DataAnalyticsProjectPROJECTREVIEWDOCUMENT
TableofContents
INTRODUCTION: 3
SUMMARYOFWORKDONE: 3
DATAMODELING: 3
ADVANCEDPOWERBIFEATURESINCORPORATED: 3
DATAVISUALIZATIONANDDASHBOARDS: 5
KEYINSIGHTSDRIVENFROMTHEDATA: 9
RECOMMENDATIONSTODOIT: 9
IMPROVEMENT(SUGGESTIONSONTHEPROJECT): 10
OMIS661–NIUDivisionofInformationTechnologyPowerBIProject
Introduction:InthisDataAnalyticsprojectweusedPowerBItodoourAnalysisonthedataprovidedbyNIUDivisionofInformationTechnology(DoIT).Atotalof91tableswereprovidedbytheDoITwith2yearsofdatainthem.WeexaminedthedataandidentifiedittobeprimarilyIncidentManagementandRequestFulfillmentData.WeincorporatedITILServiceStrategytoperformmeaningfulanalysisofthegivendata.
Summaryofworkdone:
• DivisionofInformationTechnology’s91iTrackertableswereimportedtoPowerBI.• Examinedthetablesandidentifiedkeytablesandrelationshipstocontinuewith
DataModelling.• Removedirrelevanttablesfromtherelationshipmap.• DatamanipulationandTransformationweredonetodoefficientanalysis.• Analyzedonhowthedatacouldbevisualized.• CreatedvisualizationandDashboardstogenerateusefulinsights.
DataModeling:
• Fromthe91tables,weidentified22mostimportanttablestoproceedwiththeanalysiseliminatingtheothertablesfromthemodel.
• CreatednewCalculatedcolumnstogeteffectiveresults.• Manipulatedthedatecolumntodisplayfiscalyeardata.• CreatedHierarchiestoenablebetterviewofdata(DepartmentHierarchy,Support
GroupHierarchy,ProjectHierarchy)
AdvancedPowerBIFeaturesIncorporated:
• DownloadedandimportedCustomPowerBIchartstocreatebetterVisualization.• RProgrammingwasusedtogenerateforecastingchart.• InfoGraphicDesigner.• ChicletSlicer.
TableRelationship:
SnapshotofDataManipulationdoneonTasktable:
DataVisualizationandDashboards:TicketTrends:
• FiscalYear14-15wasthebusiestyearintermsofnumberofticketsreceivedbytheDoIT.
• Amongthebothfiscalyears,AugustandJanuarymontharethebusiestonessincefallsemesterstartsinaugustinSpringsemesterinJanuary.
• PasswordResetticketscontributetothemajorchunkoftickets.
BuildingsandDepartmentwisetickets:
• Dusablehallaccountstomostnumberoftickets• DivisionofInformationTechnologydepartmentaccountstomaximumnumberof
tickets.ResolutionTrends1:Thefollowingdashboardshowsgeneralresolutionmetrics.
Resolutiontrend2:
• AsexpectedstudentsraisethemostnumberofticketsfollowedbystaffandnewApplicants.
Resolutiontrends3(SLARelated):
• ThefollowingDashboardshowswhichprioritytickettakeshowmuchtimetogetsolvedbytheITTeam.
CostInvolved:
• Workordercostingforallthethreeyears,2014beingtheyearNIUspentmostonworkorders.
EmployeeAnalysis:
• Bestandworstperformingemployeesataglance.
FutureTrends:ThefollowingdashboardwascreatedusingRscript.Theblueshadedregiontherightchartisthefuturetrendintickets.
KeyInsightsDrivenfromtheData:
• AugustistheBusiestMonthbyticketcountforboththeyearsfollowedbyJanuary.• PasswordResettopsthechartwithmaximumnumberofticketswereraisedfor
passwordresets.• Asexpected,ServiceRequestsandP4–1-dayresolutionarethemostraisedticket
priority.• FY15-16’sticketcountislessby10KcomparedtothepreviousFiscalYearasaresult
ofreducedpasswordresettickets.• KBArticleUsageisveryminimal(Around1%)• Aproceduralchangehasbeenidentifiedbetweenthetwofiscalyearsastheservice
requestswerebeingreceivedasP4incidentinthelatterfiscalyear.
RecommendationstoDoIT:
• Passwordrelatedissueaccountsto50%ofthetotalissuewhichshouldbeaddressedbyusinganautomatedOTPbasedpasswordresetsandeffectivelycommunicatingtheproceduresinplacestotheUsers.
• DatabeingcapturedintheTasktableisnotcomplete,DoITshouldfocusoncollectingasmuchdataaspossibleinordertohavean360degreeviewoftheITservicebeingprovided.(whichentityaccountstowhatpercentageofissues.)ForDoIT’sexpansionplansthisisverycrucial.
• KnowledgeBaseArticlesshouldberevisedasandwhenneededtoprovidefastsolutionstousers.
• Chatsupportwouldbeidealwaytoprovidequicksolutions.• DoITshouldconsidersendingmassemailsoftentoeducateUsersaboutwhat’s
happeningnewinNIUITspaceandeducateusersaboutproceduralchangeintheprocess(forexample:educateusersaboutpasswordresetoptionsbysendingaFYIemailtoeveryone)
Improvement(suggestionsontheproject):
• TheDataprovidedbyDoITcouldhavebeenbetterwithmorecompletedatasuchasSLA,TicketPendingtime,ITAssetManagementDataforustodomoremeaningfulanalysisandderiveinsights.
• PowerBIConnectivityfromoutsideNIUnetworkwasanissuefromthebeginning.UsingCiscoVPNdidnothelp.