Phase 5: Form Review for Eco-System MetaData Automated Support and Best Practice Documentation

[rebekkabry]

Background

The ecosystem map has been built from a combination of pre-existing tools and processes. As a result the ability to edit these forms to support the automated connections and relationships built in the ecosystem mapping, will serve as the primary adjustments derived to aid in this project.

Objective Outcomes:

• [x] Review current forms
  • [x] Which fields are connected to the data we are looking to capture? For example:
  • [x] For Application Request: Form Accessed? Fields In Form? Which Fields correspond to the metadata and hierarchy needs?
  • [x] For DataSet Additions To Open Data Portal? Form Accessed? Fields In Form? Which Fields correspond to the metadata and hierarchy needs?
  • [ ] For Sharepoint Additions or Main Hub? (see Karo) Form Accessed? Fields In Form? Which Fields correspond to the metadata and hierarchy needs?
  • [ ] For Scripts and ETL process? Form Accessed? Fields In Form? Which Fields correspond to the metadata and hierarchy needs?
  • [x] For Reports/Dashboards? Form Accessed? Fields In Form? Which Fields correspond to the metadata and hierarchy needs?
  • [ ] For newly used 3rd party or City Wide Databases?
  • [x] What adjusts to current forms can be used to support metadata needs and relationship connections? Are new forms needed, or additional metadata requirements when creating?
  • [ ] What database or records is used to keep track of the City Wide Databases and tools being used, those outside of Knack, how do we have visibility? Only manual and by word-of-mouth?
• [ ] Document data standards, best department practice recommendations, desired goals (example: eliminate manual imports, etc)

For example: When an internal stakeholder is requesting a new dashboard or application?

• What forms are currently being utilized to make that request? > This would be an ideal area to review and possibly implement required information. Such as >

  • Which division(s) will be supported?
  • Who is requesting?
  • What tool or database is providing the information?
    • How do we receive the information being collected?
    • Is it true in its raw form or manually manipulated before being storage in house or used for reporting?
  • What answer or information is being desired from the report requested? What is the question being answered or asked? DO we currently have report or solution that provides this insight?

• Where in the current forms with edits or new forms, can we ensure each node might have:

  • division/manager/developer(s)/org chart
  • ETL in (how data gets there)
  • ETL out (how data gets to the next node)
  • other data like vendor/db type etc
  • the child node(s) in the data flow
  • the parent node(s) in the data flow

Schema to Single Line Draw Out

[rebekkabry]

Would like to work with Charlie and an app team member on this. Pending further discussion.

[rebekkabry]

Recommendations Brain-Storming

• Naming standard and renaming of tools and dashboards division_purpose_recurring Ex: ROW_ApplicationsActivity_Weekly

• Metadata requirements in forms Division / DB used/ DS linked/ Reason Solution for Output

• Consider Division Sandboxes for Building and Inactive Outputs, filter by date last updated since 2 years?

• Provides Standards and Best Practice Documentation for Project Managers and Data Owners and Stakeholders. for example: If a team used Sharepoint, provide information on current best practices and notes about process with DTS to have migrated into another storage option (one decided on by DTS executives - most likely Open Data Portal)

[rebekkabry]

as of 9/12/23 TO DO:

• [x] Speak with DTS executives
  • Spoke with Amenity and Diana. They approved they review and possible adjustments to form. Need to review current state with expert users and decide how best to move forward in the most automated/hands-off approach as possible.
• [x] Schedule meeting with work-session team members
  • [x] Meeting with Karo, Charlie, Rebekka, scheduled 9/14/23
  • Knack Questions:
    • [ ] Able set Status alerts/notifications at new dataset added, dataset not shown in Knack, new report added to any reporting list assets (see hierarchy list by Rebekka), new report not shown in Knack, ETL Airflow/FME script added, ETL airflow/script not added to Knack, ETL airflow script failed
    • [x] Able to pull records and sort in hierarchy relationship (if tagged and grouped)
    • [x] Able to add new IDs, keep existing relationship connections, add new relationship connections including previously existing IDs
    • [x] Knack Production Sandbox?
• [x] Review current requesting tools and products forms

• [x] Review current requesting tools and products forms

  • [x] Knack applications
  • [ ] Knack datasets
  • [ ] Airflow and FME
  • [ ] ETC
  • [ ] Decide best moving forward to 1) adjust forms or 2)current new form/link framework?
    • [ ] Consider auto status features available in Knack to set status on all applicable portions of the ecosystem map? Publication Log Reference
    • [ ] Can responsible teams be alerting when sections they own fail?

  (Schema to Single Line Draw Out

[rebekkabry]

Meeting Notes 9/14/23

• Attendees Karo, Charlie, Rebekka

Next Steps:

Objective:

• Test environment with group that is not currently in Knack (ones with ETLs?)
  • [x] Scheduled 9/21/23

Steps:

• What are the additional fields that have been added to the process that need to be added into Knack
• Build out and test
• Automation for maintenance moving forward, dynamic livable.
• Match column/objects in fields as much as possible OR consider another ETL
• Investigate instances/applications/dataset connections in Knack

Objects List?

• Applications
• DB
• DS
• Outputs
  • Need to find a way to merge the 2...then import from spreadsheet.
  • Set alerts for new records, much like the scripts .

[KaroEngstrom]

Karo Note 9/14/23

I mapped out the DTS Portal objects set-up:

Will also look at the primary sources/secondary sources model before our next meeting.

---

If I'm understanding this correctly, the "same" data comes through as Primary instance record (ex. Vehicle Detectors in Data Tracker) and then connects to a dataset record (Vehicle Detectors in Socrata) AND that connects to one or more (in this case, three) non-primary instance records, including that of the Socrata dataset:

Does this align with how you are envisioning being able to use the instance object?

[KaroEngstrom]

Adding this test template for adding your new items for the dataset object @rebekkabry Dataset_TestTemplate.csv

[rebekkabry]

In regards to the DTS Portal objects set-up:

• What’s the difference between “applications” and “environments”?
• What is the definition of “instance”?

In reference to the Github Comments:

• I am wondering if each “Instance”/”Application” should be also tagged with an hierarchy lookup table to assign each “Instance” mention a level and connected data references (such as dev, division, etc). Example below.

Current to Considering

Here is my understanding of what we have compared to what we are considering…

What we are considering? (rough draft mock up) Not sure if multiple templates are needed to capture it all, of if we can condense

[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.

[KaroEngstrom]

A thought after Friday's meeting: if you want to label the instances per their hierarchy level, assuming they are in a relatively clear hierarchy like Charlie's mapping above (primary parent > non-primary child > multiple non-primary grandchildren), we could use another column for levels to indicate primary, secondary, tertiary, quartenary, etc. The instances could be sorted by this level in Knack which would make it visually easier on that Dataset page to see the hierarchy. I realize Knack won't be the primary visualization platform.

[rebekkabry]

A thought after Friday's meeting: if you want to label the instances per their hierarchy level, assuming they are in a relatively clear hierarchy like Charlie's mapping above (primary parent > non-primary child > multiple non-primary grandchildren), we could use another column for levels to indicate primary, secondary, tertiary, quartenary, etc. The instances could be sorted by this level in Knack which would make it visually easier on that Dataset page to see the hierarchy. I realize Knack won't be the primary visualization platform.

I think that would be a great approach, ideal actually. The mock/model table will be ready this week.

[Charlie-Henry]

Makes sense! Thanks y'all!

[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]

[rebekkabry]

Applications Insights - based on the current "applications form"...

• Currently it contains more than “hosting ops applications.” (currently 79 records)
• Find a way to group the current applications. (Source: Knack Applications, Storage, 3rd party Server, Not in Production, Outputs: Online Web Application, Map)
• Primary Source does not mean origin of source data, it means the start of the data process In Knacks records.
• Applications ideally should only contain active in use data collecting or storage applications

Fields / Filters to Consider

1. Environment: contains Production

   What doesn’t make it to list show up on test or prototype list

2. Application Class Clean Up/BreakDown

   For OPSDB or 3rd DB; filter Application Class: Enterprise, Database - Other, Database Server, Web Application - Other, Device-Specific Firmware/Software, Enterprise Business System For OPSDB Local Fles; filter Application Class: Computer File For Knack Application Tool OPSDB; filter Application Class: Knack Application For OutputAccess; Filter Application Class: ArcGIS Online Web App, ArcGIS Online Feature Service For OutputAccess: note ArcGIS Online Feature Service has layers URL, how to include, metadata export available?

   3. Note some databases may need to be added manually for instance Hanson - SBO

Results/Impact

1. Allows set up for Hierarchy/Levels/Nodes based on role

   Roles: Operations Tools (3rd party, DTS Supported, CW), ETL in, Storage, Storage Access/Dataset Details, ETL out, Output, Output Access

2. Allows clarity at highest view of the role and type of asset

[rebekkabry]

• [x] MVP Build Out in Work Session Progress in Knack TEST | 07 SEP 2023 | DTS | Data and Technology Services Portal > current focus on Applications Form
• [x] Added Column "Ops Role"
• [x] Next meeting will focus on Environments insights and Datasets Node scheduled week 11/5
  • [ ] Environments: Possible separate columns, Production; Test containing associated pages
  • [x] Datasets; Next steps below.

[rebekkabry]

• [x] Datasets - investigate use of FME to pull from open data sources. Goal: automate new entry alerts, automate data collection, sync data, provide single reference point for each dataset reference, ability to link by 4x4 ID.
• [x] Meeting scheduled with Andrew to investigate week of 11/14

[rebekkabry]

In Progress

• [x] CTM to Knack Tool Discussion with Andrew and Charlie 11/15/23

I’m not going to move forward with FME to facilitate the dataset metadata into Knack at this moment. They advised the following: • FME does not (currently) write to Knack. • FME no direct integration with knack (may need a middle piece) • ETL from ArcGis from Knack (current)

• [x] 11/15/23 - create CTM form to Knack Mapping (Charlie has a script already in place for the ETL) updates to confirm what is needed for the Knack EcoSystem ETL

• [ ] 11/20/23 - Work-session to focus on adding role columns to all instances; API table consideration

[rebekkabry]

• [ ] Current implementation of ETL process CTM dataset into Knack (adding in test = red, black = production current, purple = current feature in test)

[rebekkabry]

GOAL of Knack Connections

current

proposed

[rebekkabry]

• [ ] Migrate Datasets into Knack - Determine process and method for added (new object table?)
• [x] Complete the dataset scripts for dataset
• [x] Compiled List Consideration

[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.