search

cityofaustin / atd-data-tech

Austin Transportation Data & Technology Services
18 stars 2 forks source link

Project: ATD & PWD Bond Progress Dashboard 1.0 #8315

Closed atdservicebot closed 2 years ago

atdservicebot commented 2 years ago

An effort to centralize project delivery reporting for the 2020 Mobility Bond.

Proposed solution

Visualization Power BI dashboard with 10 tabs for each program/sub-program showing its spending and schedule data

Data management Cloud-hosted spreadsheet for manual data entry (either by Project Delivery or directly by project managers)

Should have

Questions to explore

Timeline

Resources

Request

Problem There isn't a good system for div managers to quickly and easily see the progress they are making on their spending goals for the 2020 bond, as compared to their baseline and their quarterly spend plan

Describe the solution you have in mind.

We'd like to create a dashboard that allows mgrs and supervisors to view up to date information on a regular basis, and not have to wait for static, end-of-month (or quarter) reports.

How will we know that our solution is successful?

The data is up-to-date, visually clear, accurate. Div mgrs actually use it.

Users

Project Delivery Division and managers over various buckets of 2020 bond funds, and maybe execs?

Sponsorship Amica B.

Describe any workarounds you currently have in place or alternative solutions you've considered.

We pull data from microstrategy for actual spend and actual obligations, and we have meetings with div mgrs to get schedule and planned spend amounts.

Screen Shot 2022-02-05 at 2 53 39 PM

Requested By Meredith Q.

Request ID: DTS22-103395

amenity commented 2 years ago

@johnclary and I are meeting with Amica and co next week, 2/2, to scope this.

amenity commented 2 years ago

2/2 Meeting Notes Attendees: Amica, Meredith, Fernando, Amenity, John, Charlie

Bond: 5 programs; 2 programs broken up further

Current visualization format works well

Need

Misc

Charlie-Henry commented 2 years ago

I created a pretty faithful re-creation of Meredith's excel reports in PowerBI. I added some slicers for picking which report you want to view and we can add some "bookmarks" for making this faster and for drilling down further into the work groups.

This is currently being fed by an Excel document on OneDrive. Meredith will need to refresh the report to see the changes on the spreadsheet. But she can open it and make changes using the desktop app pretty easily.

image

The spreadsheet has three tabs down by:

amenity commented 2 years ago

Rad, @Charlie-Henry! So is this actually pulling data from MicroStrategy!? 😀

Charlie-Henry commented 2 years ago

Thanks @amenity! It is currently just copy-pasted in data from MicroStrategy.

One idea would be to use the schedule email functionality. Or the REST API, if anyone has some experience using that.

johnclary commented 2 years ago

ooooh i did not know there was a REST API! intriguing....

amenity commented 2 years ago

@Charlie-Henry @johnclary - nice! Iwithout a dev? Leveraging the API sounds like a cleaner approach... Unless there's some Office 365 Outlook --> Sharepoint automagic that could update the spreadsheet via an email?

amenity commented 2 years ago

Notes from meeting today with Meredith and @Charlie-Henry

Changes requested now #8546

Future should-have

Misc

There’s an Excel extension for Microstrategy! But it’s messing up the spreadsheet

Charlie-Henry commented 2 years ago

Meredith, Amica, Fernando, and I met last week to review the dashboard. Changes are noted below

Changes:

Screen Shot 2022-04-11 at 5 21 32 PM
Charlie-Henry commented 2 years ago

Latest comments from Meredith:

  1. ✔️It’s the April column that I want to have be a different color. So whatever the current month is. Not the Actual Spend row.

  2. ✔️I would still want the Cumulative Baseline vs Actual and the Cumulative Planned vs Actual amounts to show up for the current month.

  3. ✔️In the final version, of course, we’d want the table to say “Actual Spend” instead of “Actual Spend Table Formatted.”

  4. ⭕ In this section, I’d prefer that instead of Total Baseline Spend, it says FY22 Baseline Spend. And Total Planned Spend to say FY22 Planned Spend.

    • For this, I changed "Total" to "FY". I couldn't find a way for these to include the selected Fiscal year value in the above check box.

  5. ✔️When trying to access the drop down for Program and Sub Program, other white boxes show up for filter, focus mode and more options. Can we make that stop? It makes it hard to actually get to the drop down list.

  6. ✔️ I like having the vertical grid lines! It looks good!

  7. The Program drop down won’t let me select more than one Program at a time.

    • Power BI does this strangely by allowing you to do this by holding down CTL to select multiple check boxes!

  8. When the Vision Zero Program is selected, 4 of the 5 Sub Program for Local Transit also appear in the drop down. It should only show Major Safety, Pedestrian Safety, Signal Safety and Speed Management.

    • I wasn't able to re-create this issue on my end, could you show me your steps for this one?

9.✔️ For the Vision Zero Sub Programs, can we change the order to Signal Safety, Pedestrian Safey, Major Safety, Speed Management?

  1. ✔️For the Local Transit Sub Programs, can we change the order to Local Transit Enhancement, Signal Priority, Signal Cabinet Security, Micro Mobility, Smart Mobility?
  2. When the Vision Zero Program is selected, it won’t let me select more than one Sub Program (unless all are selected. Like, I can’t pick just two.) But when the Local Transit Program is selected, it WILL let me select more than one Sub Program. Can we make the Vision Zero Sub Program list operate like the Local Transit Sub Program list?
    • Holding down CTL will allow you to select more than one check box
  3. ✔️Can we make this box transparent so that the line isn’t obscured? Or the box moves based on where the line is?
    • I made it transparent but it might get obstructed by the gridlines? I could move move it off of the chart to the top right corner?

13.Lastly, I’ve figured out a way to use a table to gather the spend plan data from the work groups (it’s easier to make sense of it in a table), but then use Power Querry to transform the data to a flat file, like that one that the spreadsheet uses to make the graphs. Can we rework the excel file that is used to include this?

image

Charlie-Henry commented 2 years ago

Next round of feedback from Meredith and changes from me: ✔️ - Addressed ⭕ - Issue or limitation

Charlie-Henry commented 2 years ago

I've also changed Power BI's data source to the new "Transformed" tabs that Meredith added to the spreadsheet. This includes data out to FY2028. If you want to display these years in the below table then first click on the table then use the filter pane to select those years: image

Charlie-Henry commented 2 years ago

Latest changes based on Meredith comments on 4/18:

  1. ✔️ On this current dashboard, I want only FY22 to be an option. In a few months, I’ll want to add FY23, and then when FY22 is over, I’ll want it to only be FY23. So this dashboard is only for the current year, or the current and next year. Never for more than that. I know there’s monthly baseline data entered now for later years, but that’s just to have for when we finally get to those years.

    • I used the filter pane in Power BI to remove the fiscal years beyond FY 23 from the selector. If you want to show or hide any year from this box just click on the FY selector area and use the options under "Filters on this visual" image

  2. ✔️ The chart at the bottom will be the same thing. I want there to be an FY21 column, then a column for each month of FY22, and that’s it. In a few months, we’ll add columns for each month of FY23.

    • This table only shows the current FY's monthly breakdown and the previous FY as a total. I'm pretty sure this is working as intended?

  3. ✔️ In the dropdown list for Programs, the programs are out of order again. It should be Bikeways, Vision Zero, Local Transit, Substandard Streets, Major Capital Improvements.

  4. ✔️ In the dropdown list for Sub Programs, the sub programs are out of order again. Under Vision Zero, it should be Signal Safety, Pedestrian Safety, Major Safety, and Speed Management. Under Local Transit it should be Local Transit Enhancement, Signal Priority, Signal Cabinet Security, Micro Mobility, Smart Mobility.

  5. ✔️ The amount allocated of $880,500,000 is the total amount ATD received in the 2016, 2018 and 2020 bonds. I changed the Total Amount for Program tab to include all 3 bonds, because I want to talk with you about adding a new tab to this dashboard for a similar but different slice of the data. Sorry I broke it.

    • Referencing the new data now. Total is $239 million for the 2020 bond work?

  6. ✔️A new tab! I have another version of these bond charts that looks at the entire life of the bond, not just the current fiscal year. The spend plans and baselines are by year instead of month, and there are 3 bonds I want to include. In addition to changing the Total Amount for Program tab on the excel sheet to reflect all three bonds, I’ve also added 4 new tabs: annual spend plan table, annual spend plan transformed, annual baseline table and annual baseline transformed. The new dashboard tab will use these new tabs on the excel sheet. And I don’t think we’ll need the chart at the bottom, but I reserve the right to change my mind later.

    • I gave this a stab only using the "Planned" and "Baseline" values for this chart. Also added a dropdown selector for the Bond program. Take a look under "Bond Programs" tab.
Charlie-Henry commented 2 years ago

Just sharing that Meredith used the dashboard during the 2020 bond quarterly meeting 💯

Screen Shot 2022-04-20 at 10 07 55 AM
Charlie-Henry commented 2 years ago

Talked to Meredith just now

johnclary commented 2 years ago

Just sharing that Meredith used the dashboard during the 2020 bond quarterly meeting 💯

Awesome!

Charlie-Henry commented 2 years ago

Latest:

Charlie-Henry commented 2 years ago

After completing John's requested changes to the Microstrategy script and migrating over to the new cloud URL, I think this epic is closed. We are now pulling in real-time spending and obligations for both ATD and PWD bonds ('16, '18, and '20)! Meredith is able to maintain the spend plan info, add additional programs, and select which fiscal year(s) to show based on a connected spreadsheet on my OneDrive.

Screen Shot 2022-08-25 at 2 41 00 PM Screen Shot 2022-08-25 at 2 40 51 PM
amenity commented 2 years ago

Fantastic work, @Charlie-Henry. 🙌🏻 📈

Misc. takeaway thoughts from 9/13/22 team meeting:

Cool features 😎

Future dreams

Charlie-Henry commented 2 years ago

Here are the links to the two reports we are using in microstrategy, for documentation's sake: 2020 bond: https://coa-prod.cloud.microstrategy.com:443/MicroStrategy/servlet/mstrWeb?evt=4058&src=Main.aspx.4058&visMode=0&reportViewMode=1&reportID=85A9E0A049F06D98AF1CF3BE8CDA9394&Server=ENV-279976LAIO1USE1&Project=Financial%20Services%20Analytics&Port=39321&share=1

all bonds: https://coa-prod.cloud.microstrategy.com:443/MicroStrategy/servlet/mstrWeb?evt=4058&src=Main.aspx.4058&visMode=0&reportViewMode=1&reportID=6B0DE57644C7C9912AAAE48392873233&Server=ENV-279976LAIO1USE1&Project=Financial%20Services%20Analytics&Port=39321&share=1