kcigeospatial / MDOT-SHA-NPDES-Next-Gen

Code and issues related to the MDOT SHA NPDES Project. Project codes: Config = 31, Management = 32.
0 stars 0 forks source link

BMP Inspection Lifecycle for ETL #409

Open talllguy opened 5 years ago

talllguy commented 5 years ago

We've been using the FIRM field on the inspection FC to track the inspection lifecycle, e.g. submitted, deleted, etc.

In March 2019, we discussed adding a 'status' tracker field (see email below). I propose that we call that field a lifecycle tracker because there is another status field that is also in the queue.

Let's discuss the field values to add here.

From: John Shiu john.shiu@ecointegration.com Sent: Wednesday, March 13, 2019 11:03:11 PM To: Elliott Plack; Andrew Blowers Cc: Brent Reeves Subject: RE: IDDE Tables in NPDES Production for NPDES NG

Another thing that came up is that in the current sample data, the FIRM_NAME field seems to be a free text field. Before, that was used in BMP inspections as the corresponding version name and login (i.e. KCI_1, MES, etc.), and the scripts would use it to indicate DELETED, SUBMITTED, etc. statuses.

However, if we are going to use this as a free field instead with various firms sharing a single IDDE login, then I would need a new string field to be added to track the inspection status (maybe INSPECTION_STATUS). This should be initialized to a default value (let’s say, ‘NEW’) whenever an inspection is created. Then, I can update the status as needed using the scripts like we do with BMP inspections.

Please let me know if you have any questions or concerns with this addition.

Thanks! John

johnshiu commented 5 years ago

@talllguy, sure, we can add a lifecycle field. Let me know when you want to add it, and we can update the scripts/schema in production.

talllguy commented 5 years ago

@johnshiu Great. I included a proposal for it in the meeting invite for tomorrow to discuss the other status/tracker we're proposing in the SWMFAC widget scope with some overlaps here.

johnshiu commented 5 years ago

I reviewed the Excel document, and here are some initial thoughts/questions that we can discuss tomorrow.

  1. How do the assignments get filled out? In the SWM Facilities tool or directly in the geodatabase?
  2. How do those assignments get synchronized with the FLD_SWMFAC data? That data is versioned, so if we populated it via a source-to-field sync, that would only write to the default version. The child FIRM versions would probably need to get that data as well, but a rec/post process could 1.) produce conflicts that we can’t handle well with just ETL and 2.) the field version needs to stay in sync with the FIRM_QC version (e.g. the FIRM_QC version should exactly reflect what is in the FIRM version, otherwise the commit from field to source could break due to conflicts like edits on missing records.)
  3. It would probably be much easier to have the assignments stored in a separate layer that lives on its own and can be viewed in real-time. Perhaps a simple flattened SWMFAC layer with the inspection tracker fields is enough. Maybe inspections can only be triggered off of this SWMFAC assignments layer, while the FLD_* layers are only used for inventory editing.
  4. How does the data for the INSP_STATUS in the BMP inspections get updated? It sounds like it’s a ‘living’ thing, but the BMP inspections don’t get updated like inventory, they only get submitted.
  5. Does this get synced back to the tracker table upon a field-to-source submit, or on an AGOL sync? AGOL sync is ‘faster’ so you’ll see updates more regularly, but the inspections can still potentially be deleted/edited/etc. until they are fully submitted to source.
  6. How does the tracker table evolve over multiple collection seasons? Does the same tracker get edited or reset each season? I think it would have to be or else it would appear as though the SWMFAC was already inspected if it was done last season.
  7. Maybe it should be a 1:M relationship, with an additional ‘COLLECTION_SEASON’ field with a domain lookup that gets updated with additional seasons as we go along (e.g. Fall 2019, Spring 2020, etc.). Then, the BMP inspection should also include this field so that it can be matched back uniquely to the FACILITY_ID and COLLECTION_SEASON when updating the tracker statuses. This could just be complicating matters more than it needs to be if the inspection tracker does not need historical data.
talllguy commented 5 years ago

Great, I wish you had been on the first call yesterday but it started as more of a widget-scope session.

  1. Widget function is my assumption but that could also be done directly. We know that there is some list of assignments that lives outside of the database. This would aim to bring that back in.

  2. I regret not adding the ETL paths to the proposal. 😃

    • The assignment would come via the new 1:1 table as a one-way sync.
    • There is no need to sync the assignment back via QC. I am uncertain now that I think about it how the "Done" flag would be checked.
    • The inspection status be new in each inspection. So, every inspection they'd chose if it was still under construction. We haven't totally thought that through. Maybe show the current status on the SWMFAC? Automate it a bit? The S123 would need to hide just about everything for the bottom four statuses.
    • Let's minimize conflicts! We'll discuss tomorrow.
  3. Interesting idea! I like the thought of a simple SWMFAC for Inspection feature too because it could be filtered in each map. I also have been thinking we could improve performance by switching to map services if they're only using the web map for getting to an inspection. Feature services are slow. That could be another day though.

  4. The purpose is threefold: 1) track the obvious whether it has been inspected or not. 2) track whether a condition that made it not-inspectable in the past is now cleared or not. They want to track if consultants verify that a proposed BMP is still proposed. Right now there is no method to track that. 3) track when something is not inspectable rather than assuming no logged inspections = not inspected. If it wasn't accessible they want to know that.

  5. The lifecycle does not sync. It is just for ETL. The FIELD Insp Status should probably not update NPDES until it is finalized, in that case where something is deleted.

  6. also 7. We got into this a little the other day. It seems that there isn't a solid "season" but rather an ongoing operation. Something we want to discuss in more detail. We thought it would just be what it is, and the last record would be added. 1:M approach is more normalized but may handle syncing not being in sync with an assumed season. I do think there'd be sometimes where they'd want to know the previous inspection status more than one iteration in the past.

johnshiu commented 5 years ago

@talllguy, when you finish any remaining updates to the schema Excel document, please let me know and send it over. I can get started on the schema changes and ETL scripts.