ServiceTimeHeatMap_SQL_PowerQuery_PowerPivot
#Pupose The purpose of this project was to give the requesting department an ad-hoc report that could measure their service times to each respective customer in minutes. The requirements included;
- The report can be refreshed with one click (Accomplished)
- The data separate out erroneous outliers automatically (Accomplished)
- The tool be easy for managers to use (Accomplished)
- The results be visually digestible (Accomplished)
Steps to Create the Tool
Step 1
SQL Query written to draw data directly from data base
SELECT dbo.fact_TaskLifeCycle.YARD_ID, dbo.fact_TaskLifeCycle.CreatedTimeStamp, dbo.fact_TaskLifeCycle.AssignedTimeStamp, dbo.fact_TaskLifeCycle.PinnedTimeStamp, dbo.fact_TaskLifeCycle.CompletedTimeStamp, c3.dim_TaskType.Abbreviation_Lang1 AS Request_Release, c3.dim_Zone.Path_Lang1 AS Origin, dim_Zone_1.Path_Lang1 AS Destination, dbo.fact_TaskLifeCycle.Task_ID FROM dbo.fact_TaskLifeCycle INNER JOIN c3.dim_Zone ON dbo.fact_TaskLifeCycle.OriginZONE_ID = c3.dim_Zone.Zone_ID INNER JOIN c3.dim_Zone AS dim_Zone_1 ON dbo.fact_TaskLifeCycle.DestinationZONE_ID = dim_Zone_1.Zone_ID INNER JOIN c3.dim_TaskType ON dbo.fact_TaskLifeCycle.TaskType_ID = c3.dim_TaskType.TaskType_ID WHERE (NOT (dbo.fact_TaskLifeCycle.CompletedTimeStamp IS NULL)) AND (NOT (c3.dim_TaskType.Abbreviation_Lang1 = N'GATEIN')) AND (NOT (dbo.fact_TaskLifeCycle.CreatedTimeStamp IS NULL)) AND (dbo.fact_TaskLifeCycle.YARD_ID = 1) AND (dbo.fact_TaskLifeCycle.PinnedTimeStamp > CONVERT(DATETIME, '2021-01-01 00:00:00', 102)) OR (dbo.fact_TaskLifeCycle.YARD_ID = 2)
Step 2
Cleanse and format data with Power Query
Step 3
Use Excel Power Pivot to create relationships between the database tables and custom tables that assign locations to owners
Step 4
Conduct analysis to determine outliers and other descriptive statistics
Step 5
Create presentation to Stakeholders using statistics found in step #4 to determine final direction regarding outliers and statistical methodologies used See Service Report Presentation Power Point
Step 6
Create pivot table according to agreed upon specifications