Document Ops Consulting Report Generation Process

[rebekkabry]

Background

In efforts to support Ops Consulting reporting and DTS automation efforts, we are conducting in-depth discovery meetings with Ops Consulting Dave A. to review the portions of the report generation process that is manual.

Goals

• to document the data sources and methodology used to generate reports
• map the data points to Datahub dataset asset
• assist with Power BI generation and set up

Approach

• bi-weekly discovery meetings with Dave A. and Ops Consulting to map Report List and Sources
• during off week, consult with Data Science and investigate solutions
• hope to address workgroup each discovery meeting.

WorkGroups Documented:

• [x] DM 6/26/2024
• [x] Pavement Ops 7/15/2024
• [x] UER 9/3/2024
• [x] Urban Forestry - moved over to Transportation Ops, they are dropping out in FY25. as of 9/2/24
• [x] Bridge Repair and Maintainence 9/3/24
• [ ] Service Plan Completion Overlay
• [ ] Concrete Repairs and Construction
• [ ] Special Projects
• [ ] Sidewalks

[rebekkabry]

DM

Current Ops Consulting Monthly Performance Measures

WorkGroup	Measure	Parimeter	Data Type	Visual Type	DTS Solution Notes
DM	Customer Service Requests Received	by month	Number	Card	see metadatadm1
DM	Customer Service Requests FYTD	by month for year	Number	Card	see metadatadm1
DM	311 Requests (Customer Service Requests by Type)	by month	Number	Bar	see metadatadm1
DM	of Potholes Repaired on Time	by month	Percentage	Card	see metadatadm2
DM	Potholes Repaired	by month for year	Number	Card	see metadatadm2
DM	Alley Work Orders	by month	Number	Card	see metadatadm3
DM	Alley Work Orders	FYTD	Number	Card	see metadatadm3
DM	Total Potholes Repaired	by month for year	Number	Bar	see metadatadm2
DM	Alley Requests Completed - FYTD	FYTD	Number	Line	see metadatadm3
DM	Sq Yards of Surface Repairs	by month	Number	Card	see metadatadm4
DM	Linear Feet of Guard Rails	by month	Number	Card	In Progress
DM	Washes Downtown	by month	Number	Card	In Progress
DM	Sq Feet of Sidewalks Cleaned Downtown	by month	Number	Card	In Progress
DM	Square Yard of Surface Repair Completed	FYTD	Number	Bar	see metadatadm4
DM	Downtown Sidewalks Cleaned (square feet)	FYTD	Number	Bar	In Progress
DM	Gallons of Water Needed for Downtown Sidewalks	FYTD	Number	Bar	In Progress
DM	Unit 6050: Street On-Call and Call-Back	FYTD	Dollars	Line	Confirming which budget, Data Science support to add into existing if able.
DM	Unit 6060: North District Routine Maintainence	FYTD	Dollars	Line	Build off TPW Finance Dashboard?
DM	Unit 6070: Central District Routine Maintainence	FYTD	Dollars	Line	Build off TPW Finance Dashboard?
DM	Unit 6090: South District Routine Maintainence	FYTD	Dollars	Line	Build off TPW Finance Dashboard?
DM	Unit 7080: Downtown Enhancement Maintainence	FYTD	Dollars	Line	Build off TPW Finance Dashboard?

metadatadm1 ✔️ found in datahub pull

**Maximo Datapoints:** 
- WONUM
- STATUS
- WORKTYPE
- DESCRIPTION
- ACTSTART
- ACTFINISH
- PROBLEMCODE
- FAILURECODE
- ORIGRECORDCLASS
- ORIGRECORDID
- CLASSSTRUCTUREID
- PERSONGROUP

consolidation for DATA BY FAILURE CLASS metadatadm1

o   Broken Branches 
o   Debris/Obstruction/Blockage
o   Investigate Work Request 
o   Other 
o   Pavement Failure 
o   Pothole 
o   Sidewalk
o   Utility Cut Work 

metadatadm2 ✔️ found in datahub pull

- WONUM
- STATUS
- DESCRIPTION
- REPORTDATE
- ACTSTART
- ACTFINISH
- WOPRIORITY
- PROBLEMCODE
- FAILURECODE
- CLASSSTRUCTUREID
- PERSONGROUP
-->ADD PRORITY RULES (1-3 = 48 hours, 4 = 24hours from Report Date to Act Finish? ❓ 

metadatadm3 ✔️ found in datahub pull

WONUM
PARENT
STATUS
WORKTYPE
DESCRIPTION
LOCATION
REPORTDATE
STATUSDATE
FAILDATE
CHANGEDATE
TARGCOMPDATE
ACTSTART
ACTFINISH
COA_CREATEDATE
RESPONDBY
ESTDUR
ACTLABHRS
ACTLABCOST
ACTTOOLCOST
WOPRIORITY
PROBLEMCODE
CALCPRIORITY
FAILURECODE
HASFOLLOWUPWORK
ORGID
SITEID
WOCLASS
ONBEHALFOF
ORIGRECORDCLASS
ORIGRECORDID
CLASSSTRUCTUREID
WORKORDERID
PERSONGROUP
WOGROUP
ACTINTLABCOST
ACTINTLABHRS
ROWSTAMP
CSR311NUMBER
CTM_SEGMENT_ID

Follow Ups and Notes

• [x] Does [dashboard](TPW Finances Dashboard - Power BI (powerbigov.us)) have the foundation of what is used for your DM notes? Could we offered a layered option to support those illustration references?
• [x] Does the Urban Trail dataset look useful?
• [x] Get consolidate 311 worksheet from Dave.
• [x] Any feedback on TPW Finance Dashboard recommendations to add layered option?
• [x] Review mapping during next Reports Mapping discussion
• [x] Start with Pavement next meeting.
• [ ] Request query syntax for 311 SBO requests, SBO DIST Potholes, SBO Alleys, SBO Surface Repairs completed and confirm metadata points capturing all.

[rebekkabry]

Metadata to SyntaxQuery DataPoints

MetadataDM1 - SR Received

total_rec
Column Binding
csr311number
srreportdate
srstatus
srworkgroup
wonum
wostatus
woworkgroup
Aggregation
wodescription
woreportdate
problemcode
failureclass
srdescription
wldescription
srnumber
probdescription
srdetaillongdesc
map_page
woactualfinish
hours_diff
wldetaillongdesc
fulladdress
serviceaddress
prob_area
srprobdescription
srticketuid
worklogid
srpriority
woworkgroupdesc
saddresscode

MetadataDM2 - Potholes

wonum
wodescription
wostatus
srno
srdescription
classstructureid
wopriority
wopersongroup
woreportdate
srstatus
duration_1
woactualfinish
woactualstart
Aggregation
Aggregation_1
Aggregation_2
hours_diff
Aggregation_3
Aggregation_4
Aggregation_5
Aggregation_6
Column Binding_1
Column Binding_2
srreportdate
completedlate
complatedlate123
potholereqopen
potholereq123open
completedlatepercent
complate123percent
woownerlead
wosuperintendent
crewid
incomingnoncritical
regiondistrict
map_page

MetadataDM3 - Alleys

wonum
wodescription
wostatus
classstructureid
wopriority
woreportdate
duration
woactualfinish
woactualstart
hours_diff
woownerlead
wosuperintendent
crewid
woduedate
completedontime
wocreatedate
Aggregation
completedlate
other
compcafstatus
serviceaddress
citizenname
problemcode
failureclass
probdescription
failclassdescription
councildist
woworkgroup  (distn, dists, distc)