search

kcigeospatial / balt_co_ETL

This will provide a place to track internal issues around the Baltimore County NPDES NCT application ETL.
0 stars 0 forks source link

ETL - Update ETL Translation - NPDES.IMPERVIOUS_REM.STRU_NO_EPS often has nulls but is source for non-nullable MDE.AltBMPPoly.PROJECT_NAME #91

Open dhenry-KCI opened 6 years ago

dhenry-KCI commented 6 years ago

MDE.AltBMPPoly.PROJECT_NAME is a non-nullable text field with length of 25.

The ETL v8 excel file shows that NPDES.IMPERVIOUS_REM.STRU_NO_EPS is the source with no translation for MDE.AltBMPPoly.PROJECT_NAME. However many IMPERVIOUS_REM records have no STRU_NO_EPS (STRU_NO_EPS is the foreign key to DEPS Master, and many IMPERVIOUS_REM records simply do not exist in DEPS Master.) Additionally, NPDES.IMPERVIOUS_REM.STRU_NO_EPS is a double precision number field, not a text field, limiting the usefulness of this attribute field as a source for the PROJECT_NAME text field.

Workaround solution: Enter dummy value of -99 in NPDES.IMPERVIOUS_REM.STRU_NO_EPS.

Preferable solution: change ETL to use the first 25 characters from NPDES.IMPERVIOUS_REM.SITE_NAME, which will yield something much more similar to a "PROJECT_NAME" and will make more sense to Baltimore County users entering data into the NCT.

dhenry-KCI commented 6 years ago

@gerrykelly update ETL to use first 25 characters of NPDES.IMPERVIOUS_REM.SITE_NAME to populate MDE.AltBMPPoly.PROJECT_NAME for Impervious Removal Strategy

gerrykelly commented 6 years ago

@dhenry-KCI Updated at KCI (there are SITE_NAME nulls and for them I write 'No Project Name', I could instead leave AltBMPPoly.PROJECT_NAME null to continue to cause the append error at the county if that is a better solution)

dhenry-KCI commented 6 years ago

Confirmed in KCI. Ready for County Testing