Phase 2 – Current State Mapping Dashboard/Tools Level

[rebekkabry]

Phase 2: Mapping Current States

• [x] Dashboard Mapping (visual)

  • Use Dashboard List to Map

• [ ] Single Line Data Mapping ( most likely by dashboard or tool, because each Division does not have a dashboard to map back)

  • Example:
    • Signs and Markings (Issue)
      • Dataset 1 → AGOL (ArcGIS Online) → Analysis (Issue)
      • Dataset 1 → Open Data Portal → PowerBI (Issue)

• [x] Map each to division or workgroup

• [x] Transpose list/process into Excel chart for mapping a. List of sources/tools b. List of ETLs c. List of storage/clouds/databases d. List of Queries/Processing e. List of Transformation

[rebekkabry]

Miro Working Board.

https://miro.com/app/board/uXjVMB3l8KY=/

[rebekkabry]

6/2 - Meeting with Paige to review OPM reporting practices, templates, shared drives and measurables.

6/9 - Meeting with Paige to review pre-merger departments and link to current org chart, applications used and main functions/responsibilities for each department (division)

[rebekkabry]

Background

As part of the Project: TPW Data Ecosystem Map #12427 we are focusing on mapping the most known/frequently used reporting tools and organizational insight by legacy ownership; more specifically Service/Operations divisions.

[rebekkabry]

OCE

Pavement

[rebekkabry]

https://github.com/cityofaustin/atd-data-tech/issues/12427#issuecomment-1625740359

[rebekkabry]

Division

Mapping Checklist PM Level

• [x] Dev Team - In Progress with Charlie and Dev Team (John/Chai)
• [x] Finance
• [x] Human Resources
• [ ] Data & Technology Services
• [x] Performance Management
• [ ] Parking Enterprise
• [ ] Public Info PM
• [ ] Administrative Manager
• [ ] Legal Affairs
• [x] Street and Bridge Operations
• [x] District Maintenance
• [x] Utilities & Structures
• [x] Pavement Operations - In Progress with Michael and Paige
• [x] Urban Forestry
• [ ] Transportation Engineering
• [ ] Capital Services/ Project Delivery
• [ ] Active Transportation Engineering Services
• [ ] Vision Zero - Transportation
• [x] Signs and Markings - In Progress with Susanne
• [x] Arterial Management "Signals" - In Progress with Christina
• [x] Office of Special Events
• [x] Right of Way (ROW) Management - In Progress with Tracy
• [ ] Office of the City Engineer - In Progress with Michael and Paige, Angelo Johnson DM would like to be included
• [x] Sidewalks and Special Projects
• [ ] Community Services
• [ ] Emergency Management
• [ ] Logistics
• [ ] Capital Program Consultant Strategic Projects
• [ ] Public Info & Mktng PM Strategic Com & Outreach
• [ ] Smart Mobility/ TDM
• [ ] Long Range Planning
• [ ] Transportation Development Services
• [ ] Capital Program Conslt. Program & Project Development

Notes Appears best to approach mapping at the tool/dashboard level versus the division level; found that all divisions do not have direct tools or dashboards that support them.

Mapping Checklist Dashboard Level

• [ ] Use Dashboard List

[rebekkabry]

7/25/23 - Dev

https://github.com/cityofaustin/atd-data-tech/issues/12971#issuecomment-1650345970

Next Steps:

@rebekkabry

• [x] Review Knack applications and datasets. Transcribe into EcoSystem_Map.
• [x] Note divisions, core services and metrics being captured.

[rebekkabry]

Divisions Mapped To Work Group and Support Role

[rebekkabry]

Visio subscription in progress. Currently limited to 100 columns to map.

[rebekkabry]

MVP Legacy PW - Test Only - Need to Verify + Only Shows 1st 100 column of map (over 700+ columns)

[rebekkabry]

Single Line Drop Down of Eco-Map Framework

[rebekkabry]

Compiled List of Assets PDF - Not Complete

Currently Includes:

• Assets for arcgis datasets (map ID can be assumed based off ID) > Output DS and Output
• Open Data Portal datasets >Storage / DS
• Knack assets - datasets > Tool DB DS
• Knack assets - applications / DB > Tool DB
• Power BI assets - datasets > Output DS
• Power BI assets - dashboards/reports > Output
• Division managers > Division
• Assets for maximo > 3rd DB
• PW Sharepoint for PWStat > Storage and Storage > Folder
• SD23 Measures > Output
• File for data PW owners > MetaData and Notes

Pending:

1. [ ] knack tables > pending to confirm if details needed in current MVP
2. [ ] eCOMBS assets > datasets and reports
3. [ ] eCAPRIS assets > datasets and reports
4. [ ] banner assets > datasets and reports
5. [ ] Data and Technology Hub (Mobility DTS Site)
6. [ ] COA Micro assets > datasets and reports

[rebekkabry]

[### Single Line Drop Down of Eco-Map Framework with Compiled List Notes](### Single Line Drop Down of Eco-Map Framework with Compiled List Notes)

[rebekkabry]

Mock Up of Support Divisions Only

[rebekkabry]

Current Items In Progress

• [x] Get pending assets lists
• [x] Research alternative mapping software
• [x] Compile a map of sources
• [ ] Build queries where needed to keep dynamic - possibly via PowerBI and Knack

[rebekkabry]

Research Alternative Mapping Software Notes

Microsoft Visio: Microsoft Visio is a powerful diagramming tool that can be used to create a wide range of diagrams, including organizational charts. You can import data from Excel and use Visio's features to create expandable and collapsible organizational charts with ease.

Lucidchart: Lucidchart is a web-based diagramming tool that offers a user-friendly interface and extensive collaboration capabilities. It supports importing data from Excel, allowing you to create expandable or collapsible organizational charts and easily collaborate with your team.

OrgChart Now: OrgChart Now is a dedicated organizational chart software that allows you to import data from Excel and create interactive, expandable, or collapsible charts. It offers various customization options and features specifically designed for organizational chart creation.

SmartDraw: SmartDraw is another popular diagramming tool that enables you to import data from Excel and create expandable or collapsible organizational charts. It provides a wide range of templates and customization options to suit your specific needs.

Gliffy: Gliffy is a cloud-based diagramming tool that offers an intuitive interface and supports importing data from Excel. It allows you to create expandable or collapsible organizational charts and collaborate with your team in real-time.

Power BI DAX

[rebekkabry]

New Framework Model for Relational Mapping

Miro Board

TO DO

• [x] Create a master ID/Key List based off previous collection of data/framework
• [x] Create / compile a list of Keys or ID Maps
• [x] Import tables into PBI and document transpose notes for ID Maps and Keys
• [x] Create columns within tables mapping to ID Maps of the Relational Hierarchies

[rebekkabry]

Discussion of Models

• Need to Figure out a Schema
  • Advanced Look Up Tables
  • Tree Model
  • Individual Map > currently investigating with Charlie 9/7/23
    • After investigating, we will explore Star / Snowflake schema (example of above) - scheduled 9/11/23

[rebekkabry]

[rebekkabry]

Feedback from Management

• [x] Label Names
• [x] Consider Who (what audiences) will utilize
• [x] Visualization Concepts Explored (i.e; social network bubbles)

[rebekkabry]

[rebekkabry]

Star Schema Work Sessions

• Worked with Charlie to investigate with type of schema would best support the current design/future dynamic desires for the ecosystem mapping structure.

• Investigating star concept., needs to consider:

  • What is the most granular level of the mapping assets?
  • Once determined, what columns do we need/have/lookuptables for each item in the hierarchy list?

• [x] Upcoming meeting with Diana, Charlie, Amenity and Rebekka to review schema and discuss Knack as potential application to support project lifecycle.

  • Meeting Notes:
    • Diana agreed that Knack could support most of the functionality desired for the map including many-to-many relationships, handling additional IDs, creating multiple lookup tables to support the array of metadata for each asset group, ability to provide status reports of researches and script status.
    • Recommendation to work with App Experts and start building MVP in production sand box. Test concept and functionality.

• [x] Meeting with Karo, Charlie and Rebekka to review Knack to support MVP - scheduled 9/14/2023

• Reviewing how the investigating schemas align with the hierarchy levels and single line collapse (desired outcome of project)

Schema to Single Line Draw Out

PROS about this framework:

• ability to map simple and complex data pathways
• ability to collect and show variations of metadata for nodes across the pathways (changes to divisions, additional stakeholders, etc)
• most of the nodes are built on json enabled and easily "automated" for refresh needs.

[rebekkabry]

Discussion on Knack DTS Objects:

• Charlie, Diana, Karo, Rebekka

My thought is that we can hopefully allow non-primary instances to reference other instances.

My understanding is that currently primary instances are the only instances that can have related instances:

We want non-primary instances to also be able to have their own child records as well that way we can see where the data flows just by looking at the relationships

• However, in this example.. I believe it’s already doing what you want.

This dataset “Markings Work Orders” origins from a Knack object table

• Script pushed to AGOL ArcGIS Online (AGOL)
• Creates a “feature service” (a spatial dataset)
• This “feature service (this new dataset), is used in a web map application product

This current structure shows that. Because we made the (spatial data set = feature service) and instance, we can choose it as a Source instance thus allowing us to say what application it’s in. Those applications can be a PowerBi dashboard or other custom product. We’d just have to add it to the “Host Application’ list.

In addition to the current relationship structure, we will use to the test environment to review

• A standard where all primary assets have its non-primary and metadata associated; a standard format and form fields that ensure all assets have records associated. • Having all associated data path locations reflected. Example: Specific data/link to - Script pushed to AGOL ArcGIS Online (AGOL) referenced in the Details of Dataset Instances table. • Areas where alerts would be beneficial (Airflow, new datasets, etc.) • Tagging instances with "types". Potentially add columns to reference pathway location/level.

[rebekkabry]

DTS | Data and Technology Services Portal Knack review and model build out - in progress.

• [x] Follow up meeting with Karo set after model, tentative 10/11/23

[rebekkabry]

• [x] MVP Knack Build Out in progress
• [x] Civil Engineering, SBO, Transportation Services 1:1 mapping discussions in place > Report in Progress

[rebekkabry]

• [ ] Pending completion of remaining divisions
• [ ] Build out of matrix and summary reports in progress

[rebekkabry]

Updates:

• Presented DTS Ecosystem Summary Report and Recommendations 1/11/24 to DTS Leadership and Team Leads (Peggy, Amenity, Diana, Charlie and Karo)
• Closing issues related to Ecosystem Project
• Recommendations have been recorded #15414
  • Supporting Issues will be created to per Diana to assist in guidance and scope when addressed as a project TBD.