Patrick - thanks for the presentation last week. 

After ruminating on the office hours a bit, I realized I’d love to see the “under the hood” of a dashboard or two. 

• What was the pre and post processing of data like? 
• How many data streams were brought together and how?
• How was the QA/QC done? 
• How long did the planning process take? 
• How long did review and approval take?
• How long did the tech development take?
• How many people worked on creating the dashboard?
• How was it documented?
• How will it be maintained?

Project X – What? We have a Dashboard now?

• Data was originally pulled directly from raw sources. No need to process anything. It is all there.

• Pulled from an integration engine that was orchestrating and processing several data streams, then dumped in a noSQL database.

• It was not. Dashboard pulled from whatever was there. If the numbers were increasing, it was working. Right?

• Probably went something like:
  • IT(a) – “They keep asking us a lot of questions about that data.” 
  • IT(b) – “Yeah, maybe if we made that dashboard they were asking about a last year, then they would not instant message us so much.”
  • IT(a) – “OK. I got this free tool I wanted to try out for our server monitoring, so I’ll just point that at their data and make some charts and tables. Then we can use it for our stuff too.”
  • IT(b) – “Good idea. You should go for it.”

• There was not much if any at all. It was internal and for a limited group. We can change whenever we have time.

•  Less than a week to get the tool installed and configured. Then another 2-3 weeks between other work to tryout the free tool and build-out a few things.
• By the way, there is no such thing a free tool. 

• Very few and none in a full-time capacity. System Admin pointed a new dashboard tool at some data and made some tables add charts that they thought would be good to go.

•  It was not.

• Ad hoc via email or instant message to the System Admin who built it. 

Project A – Complex data, public facing, lots of stakeholders (Disciplined and Formalized Approach)

• Over 6 complex raw data streams were in place, updating daily to dozens raw tables 
• Pre-processing – really starts at data collection…
  • Source/Collection QA - electronic questionnaires leveraged:
    • standardized case report forms
    • form skip-logic
    • coded values - data standards where applicable (ICD-10, LOINC, SNOMED)
    • Real-time data quality rules at data entry
  • Aggregate local data quality rules prior to transmission
• QA at Transmission
  • Data critical quality checks at transmission prior to central acceptance source ETL
• QA at Central Repository (daily ETL/automated jobs on data allowed in)
  • Raw tables to reporting tables with only active records (not full history of transactions)
  • Nightly data quality rules to flag suspect records or cross-data feed issues
• Post-Processing: Dashboard Specific Actions on Reporting Tables
  • PHI adaptations (date-shifting, surrogate ids, cell-size, etc.)
  • Custom flags (e.g., QA reviewed) and stratifications(e.g., age groups base on DOB)
  • Custom optimized view exclusively for the dashboard populated

• Initial development on staged and locked datasets to confirm transformations, counts, and calculations
• Iterative and real-time QA/QC once connected to sources that updated daily
  • Wild-type data helps find bugs & surfaces unforeseen data quality checks that were worked in (example valid date format check present initially, not an initial check for a date of birth 20 years in the future)

• High-level planning
  • Not more that few days of effort
  • Some minor budget/procurement delays
  • However, took few weeks to all the necessary stake holder inputs and projections

• Active stakeholder and SME involvement throughout the project
• Iterative reviews and approvals were ongoing
  • Weekly development meetings
  • Individual SME sessions
  • Cross-project focus groups

• 3.5-5 months

• 4.0 Subject Matter Experts
• 1.0 DBA/Data Analyst
• 1.0 Business Analyst
• 0.25 Project Manager
• 1.5 Dashboard Developer
• Multiple stakeholders and focus group participants

• Source Table Specifications
• Data Quality Rule specifications
• Business Rule Requirements
• Custom View Code Documentation
• Software Development Task Tracking (Jira)
• Code Repository (GitHub)
• Data Governance Documentation
• Dashboard Versioning & Change Control

• Change Requests and Bugs documented in Jira
• Data Governance to approve and prioritize changes/enhancements
  • Public and Private Versions of Same Dashboard
• Discipline Dashboard versioning and scheduled releases

• To think about the use cases from the perspective of the target audience members.
• Less is more
• Make information easy to find
• Label indicators in ways that do not make someone have to search for a definition

• https://www.theswimguide.org/
• https://www.airnow.gov/
• https://ephtracking.cdc.gov/DataExplorer/
• https://ejscreen.epa.gov/

• Not overloaded with data
• Most important indicators stand out, at top
• Good organization and use of spacing, grouping, formatting
• Use of tabs to allow more data to be shown without being too busy
• Data with indicator names that are easy to understand
• Dashboards with the data behind them being easy to download or access

• How to link a dashboard on a web page to my data files or how to upload my dashboard to a web page
• How to link integrate existing dashboards that are useful into the new dashboard that we are building to house our own data

• https://oehha.ca.gov/calenviroscreen
• https://azdhs.gov/preparedness/epidemiology-disease-control/environmental-health/environmental-public-health-tracking/index.php#indicators
• Chambers County, Texas (ArcGIS):
  https://montbelvieu.maps.arcgis.com/apps/dashboards/80511e2f638347228263cb563142adcc
• Williamson County, Texas (ArcGIS):
  https://experience.arcgis.com/experience/ae30cf23f70b40fda5a4804e7601eee9
• El Paso, Texas (Power BI):
  https://www.epstrong.org/results.php