Closed susannegov closed 6 months ago
This Power BI report is used for at-a-glance retrieve performance metrics for the Signs and Markings division in Transportation and Public Works.
The two sources come from a combination of work orders submitted in the Signs and Markings Operations app and a geospatial database of roadway signs and markings assets. The 311 CSRs are collected from daily reports of the 311 CSRs in Motorola.
On a daily basis between the hours of 1AM-11AM CST, the data gets fed into an ETL pipeline using FME, geodatapusher, and Airflow. The data is pulls from Austin's Open Data Portal (ODP) and the Enterprise Data Portal (EDP).
Signs and Markings division
These datasets require SSO with a City of Austin employee credentials.
These datasets are open for public use.
Contractor Markings Work Orders and In-House Markings Work Orders - Records of work orders from the Signs and Markings Operations web application. In-House work orders include work orders submitted by requesters in the Transportation and Public Work department and 311 CSRs. Contractor Work Orders are work orders created by the Signs and Markings division and assigned to contractors.
Short Line and Short Line Dates - Geospatial assets of short line markings assets. Short Line include crosswalks, stop lines, and yield lines.
Specialty Line and Specialty Line Dates - Geospatial assets of specialty markings and raised pavement markings. Specialty markings include delineators, curbs, raised pavement marking lane separators, and concrete domes.
Specialty Point and Specialty Point Dates - Geospatial assets of specialty markings. Specialty markings include arrows, word legends, parking space boundaries, raised pavement markings (RPM), symbols, and other specialty markings
Roadway Sign Assets - Geospatial assets of roadway signs. This dataset contains records of street sign assets installed and maintained from June 2018 to present.
DEFINE
VAR HIN = UNION(
ROW("HIN","Yes"),
ROW("HIN","No")
)
VAR TotalMonth = UNION(
ROW("Month",6),
ROW("Month",12),
ROW("Month",18),
ROW("Month",24),
ROW("Month",36),
ROW("Month",48)
)
VAR HINMonth = ADDCOLUMNS(
CROSSJOIN(HIN,TotalMonth), "Condition",
SWITCH(
TRUE(),
AND([HIN] = "Yes",[Month] <= 6),"New",
AND([HIN] = "Yes",[Month] <= 18), "Good",
AND([HIN] = "Yes",[Month] <= 24), "Fair",
AND([HIN] = "No",[Month] <= 12), "New",
AND([HIN] = "No",[Month] <= 36), "Good",
AND([HIN] = "No",[Month] <= 48), "Fair",
"Poor"
)
)
EVALUATE
HINMonth
ORDER BY
[HIN] DESC,
[Month] ASC
Making an About - Metrics page
Percentage is calculated by the total number of open CSRs and closed late CSRs divided by the total CSRs. The CSRs are filtered by the Created Date for Fiscal Year. CSRs are closed from the Knack ETL integration to Motorola. When the work order gets closed in the work order, the 311 CSR status also gets modified. Late SRs considers both open and closed late SRs.
Percentage is calculated using In-House Work Orders (WO): 100% - (On Time WO) / (Late WO) On Time is less than or equal to 30 days. Late is over 30 days. The In-House Work Orders are filtered by the Active Date for Fiscal Year.
The Active Date is the Created Date unless there is a Remove Hold date on the work order. If there is a remove hold date on the work order, that is used instead.
The number of days active calculates the number of days active. If the Work Order status is "Needs to be Issued" or "Issued", the date difference is between the active date and today's date. Otherwise, the date difference would be between the active date and the completed date.
The total SBO Work Orders is filtered by the distinct non blank counts of Markings Work Order IDs. The Requester is ATSD or SBO. The Subtype is Overlay or Sealcoat. The Work Order status is not On hold and it is not Cancelled.
Fair or Better Condition is a performance metric used to determine how often a signs and markings asset has been maintained. The condition of the asset is determined by how many months ago an asset has been maintained.
Assets that are within the High Injury Network (HIN) follow a different condition metric compared to assets that do not live in the HIN.
The Install and Maintain value looks at the distinct count of markings short line IDs in the dates table where the short line type is crosswalk and the short line "School is "Yes". It also filters dates where work was removed.
The percent calculate the value measure above divided by the grand total measure of school crosswalks.
The grand total value ignores date filtering
Power BI quick reference pages are done in Power BI and published
Make a description for each measure and create QRG Quick Reference Guide hand-out for stakeholders
https://app.powerbigov.us/groups/b73ea40f-8b44-4aa9-9794-7a87b43e5246/reports/42584f87-6367-46d6-88d4-f4bb62c8db88/ReportSection
https://github.com/cityofaustin/atd-data-tech/issues/12178#issuecomment-1542766011