Prototype dashboard with data from current dashboards

[patrickm02L]

Based on the research from #9504 it makes sense to build out an MVP of to see how an Executive Dashboard might operate.

Using data from the following dashboards:

• Signs and marking
• MMC
• Vision Zero
• Capital Metro

We're looking to create a single dashboard that's an amalgamation of other dashboards so that other teams can continue to use what they've created while we reference their data.

Also need to follow up with points of contacts who maintain the dashboard data. Suzanne G. in Signs, Allison R. & Lance B., Xavier A., and someone from CapMetro.

Prototype miroboard

[Charlie-Henry]

Patrick and I met today and created a template for what these dashboards could look like.

Starting ideas for report tabs:

• Finance
• Vision Zero
• Operations

[patrickm02L]

Meeting with Charlie on 7/19/22 to go over functionality and approach to building out the dashboard. Will bring to the afternoon Dev Sync.

In between this note and then, Patrick still needs to make design refinements if there are any for Charlie to update in PowerBI templates that were created.

[Charlie-Henry]

Data sources for some ideas for each tab:

• Finance
  • Parking revenue (private socrata dataset currently)
  • Bond progress dashboard (currently a excel spreadsheet on OneDrive)
  • Finance data
    • task orders dataset
• Vision Zero dataset
  • Number of people and number per 100,000 residents who are killed or sustain serious injuries in traffic crashes (M.D.1)
  • Number of crashes resulting in fatalities or serious injuries caused by the top contributing behaviors (speeding, distracted driving, impaired driving, failure to yield) (M.D.2)
  • Number of fatalities and serious injuries on the High-Injury Network (M.D.3)
• Operations
  • Gridsmart cameras traffic volumes dataset
  • INRIX data for intersections and travel time. (May have an API but currently a manual process)
  • MMC dashboards?

[Charlie-Henry]

Chatted with Xavier about trying to calculate the VZ stats on the high injury network and top contributing behaviors. He supports adding some flags in the Socrata dataset, but he's concerned that there are some inconsistencies between what Socrata shows and what the VZ DB is showing.

He also shared his SQL that he is using to calculate the SD 23 measures. He's tracking them on Power BI using some files stored on his PC and updates them manually. High injury power BI

Here is the chart I can add now using Socrata data to our VZ tab from here

[Charlie-Henry]

Link to Power BI dashboard:

https://app.powerbigov.us/links/tQAnbdsvMB?ctid=5c5e19f6-a6ab-4b45-b1d0-be4608a9a67f&pbi_source=linkShare&chromeless=true

[patrickm02L]

@maccallump Please create a list of data from the divisions that you want.

Here's what we have so far...

• Bonds
• ROW
  • Permits
  • Time
  • Traffic Control Plans
• Signals
  • Work order data
• 311

[patrickm02L]

Meeting set for Charlie and Patrick to review datasets and nail down metrics on 8/1.

[patrickm02L]

Build out the following pages:

Vision Zero

Fatalities and Serious Injuries

• Fatalities
  • Year Fatalities Trend
  • Cumulative Fatalities
• Serious Injuries
  • Year Serious Injuries Trend
  • Cumulative Serious Injuries
• Types
  • Pedestrian
  • Bicycle
  • Motorcycle
  • Cars
  • Other

Include Time filters for all Dashboard pages: Drop down to change from weekly, monthly, last-five years.

Operations (2 pages)

Vehicles, Travel Time Volumes, Cap Metro Ridership, Trail Counter

Finance

Parking Revenue

Signs and Marking

Work orders, Asset Inventory

Bonds

Project Delivery

311

Types of Issues, Volume of Issues, Location, SR Stat

ROW

Permits, Time, Traffic Control Plans

(In Amanda, eventually available, but not easy at the moment)

[patrickm02L]

Things to ask @maccallump in Dashboard Review meeting:

How to approach datasets?

Do we want to only use the data in Socrata or should define what we want to show and find the datasets to make that visible?

• Could we create another place where the data lives?

What do the executives want to see?

• Guessing they need high level
• Individual divisions can drill down on their own

How do we handle transparency?

[patrickm02L]

Set up meeting with Signs and Marking (Susanne G. & Christina T.) to go over what data their capturing in their Power BI dashboard, and what data would help tell a story in the Executive Dashboard.

[patrickm02L]

Kate is going to take the lead with the storytelling for the different division pages that will be included in the dashboard.

[patrickm02L]

@katelunceford is working on prototyping Signs and Marking Dashboard in #10039

[patrickm02L]

Basics for monthly dashboard via @maccallump

1. Financial Report - Budget to Actual
2. Division highlight - 1st one SMD.
3. Current operational performance metrics
4. Capital Project Delivery overall update - quarterly?
5. Capital Project Highlight
   • 2016 Mobility Bond
   • 2018 Mobility Bond
   • 2020 Mobility Bond

[patrickm02L]

Put together a dashboard with Financial information through August, and signs and marking dashboard. A 2 page dashboard is alright for now.

[patrickm02L]

@Charlie-Henry look at ATD Financials spreadsheet EXP 5125 tab for the raw data, represent visually. Reach out to Crystal T.

[patrickm02L]

Update: New pages have been added in the Executive Dashboard prototype.

• ATD Expenses 2022
• ATD Revenue for July 2022

Better to represent the month-in-closed data rather than updating the data semi-daily.

@Charlie-Henry is still working with data Crystal T. provided to get this automated. There are challenges in microstrategy - specifically removing prompts from reports. @maccallump said that you can remove it from the script from the prompt - this happens when reports are being built.

Perhaps we can work with someone at the Budget office to see if there's a way to pull the data straight from the data warehouse.

[patrickm02L]

Things discussed in 09/13/22 Sync:

@Charlie-Henry

• Look at getting Revenue data month-to-month
• Move cards for Revenue pages to the left side.
• Try another way to represent what's in the metrics cards.

[Charlie-Henry]

I had some troubles getting some things to work like the border or shading the text to match the arrow, but here's another stab at the revenue page. Currently, I just have the FDUs selectable but we can make a lookup table to convert those to something more friendly.

https://app.powerbigov.us/links/tQAnbdsvMB?ctid=5c5e19f6-a6ab-4b45-b1d0-be4608a9a67f&pbi_source=linkShare

[patrickm02L]

@maccallump The ATD Revenue and Finance Pages on the Executive Dashboard are good to show at the next Executive meeting.

[patrickm02L]

Closing with the intention of adding more Pages to the dashboard which we'll add to the Epic #7700.