Track Inspection ETL - Extract-Transform v6
- zhinan.zhang (Unlicensed)
Track Inspection ETL Extract-Transform
Author : Tom Kennedy
Last Update : June 5, 2016
The following functionality covers the Extract-Transform portion of the TAV ETL process. This functionality is called by the Track Module screen “Inspection Creation Based on Track Assessment Vehicle (TAV) Data” and based on a sample data source of “Mont-Joli (12.0-188.8) June 12, 2014 with Import Mapping Tags” known here as the Sample Data Source. The compliment to this specification is the document “Track Inspection ETL – Load”.
The ETL ET process is designed and developed across two iterations labeled as versions (V1, V2). We do not begin development on the next iteration until the previous one is complete.
Extract/Transform V1: Load Perfect Data
Assume no need to look for error/warnings within the datasheet;
Expect all transformations to pass;
Functionality
o ALL Defects and ALL Tie Counts are assigned to
o Triggered by user completing Tab 1 of the Inspection Creation Based on Track Assessment Vehicle (TAV) Data screen;
o Read data from the data source based on sections marked in yellow in the Sample Data Source;
o Load into data objects for use in the Load portion of the ETL process;
o For any lookup related data, transform it from native values (e.g. “CN Rail”) to RSIG values (e.g. “21”) via caching and database lookups;
Extract/Transform V2: Search for Errors and Load Perfect Data
Functionality added to V1;
Look for two type of errors/warnings that will be detailed in specs:
o Catastrophic (e.g. Section missing);
o Regular (e.g. no valid transformation from native data to RSIG data);
Extract/Transform V2: Search for Errors and Load Perfect Data
Functionality added to V1;
Look for two type of errors/warnings that will be detailed in specs:
o Catastrophic (e.g. Section missing);
o Regular (e.g. no valid transformation from native data to RSIG data);
If a Catastrophic Error is found then the error/warning collection stops and the Catastrophic Error, along with any Regular Error/Warnings collected are returned to the Load process;
If a Regular Error/Warning is found then the data collection continues;
Data collection will stop once the first Catastrophic Error is found or all data is read, whichever comes first;
Returned data objects should include an indication if 1. Any Errors are found (Catastrophic/Regular); 2. Any Warnings found (Regular); 3. Transformed data;
FYI, the following is covered in the Load Process document, but in summary:
o Load Process
§ If at least one Error and/or Warning is returned then RSIG Error/Warning Window is shown with each Error / Warning using the standard functionality (each with an official Event ID. In the case of Warnings, the ability to Ignore);
§ Upon Close of Error/Warning Window if at least one Error was reported, or at least one Warning was reported and not acknowledged, then message indicated Load attempt is stopped unsuccessfully and functionality ceases;
Data Load
Data Transformation Pattern
Many of the TAV data values need to be transformed into a value recognized by RSIG (eg Railway, Subdivision, etc). This handled the exact same way:
The TAV data value is checked to see if that value is already in a cache dedicated to that data type (e.g. TAV Subdivision cache, Track Track Type, etc). We do not want to hit the database for hundreds of repeating values;
o If it is found then the RSIG value from that cache is used instead of the TAV data;
o If it not found then the appropriate RSIG database table is checked
§ If that is not found then a Regular Error is noted and this cel is skipped;
· The error message will be specific each individual data type for tracking purposes;
· The message will be context sensitive
o Naming what data could not be loaded;
o Naming who to contact. The contact will be a System Parameter, retrieved by the common routine to retrieve a specific System Parameter (in this case ATTR4385);
§ If it found then that RSIG value will be used instead of the TAV data;
· That TAV data and RSIG value is added to the cache specific to that location type;
Lead RSI [R700100]
#1, Inspection, GR263 --> Lead RSI Stakeholder Individual, with a role of "InspGenLeadRSI"
o GR263 --> Lead RSI Stakeholder Individual, with a role of "InspGenCoordRSI" (mandatory)
Version Number comparison [R700105 begins here:]
#1A, Version number – Mike to confirm the exact column on the new verion number in the spreadsheet;
Decimal with a format of MajorRelease.MinorRelease, initial value is 1.0;
o Major represents structural changes where our current software is not compatable with the latest changes. Note if/when this happens we will need to support two versions, e.g. 2.1 and 3.1, but not before 2, during the transition. In that case our latest version that we support would be 2.1;
o Minor version number represents data differences only, which the user can get by on;
If our official Release ID is not setup, or is not in a format of #.# then
o Catastrophic Error, no need to continue, Event ID 352355;
o Silent Event 807855, Parameter Where {0} - Our TAV version - System Attribute ATTR4402
o Stop processing
For the following Major/Minor releases, if there is NO value in the spreadsheet Version # then we can ignore the comparison as along as it is submitted in 2015. We do this by NOT checking the version control if our Official version is 1.0. We have a transition period here with spreadsheets out there that already exist before the version number update. However, after 2015, then a blank value is considered a Major # release comparison fail.
First Major Release Compare
o Compare the number before the decimal point (e.g. 2.3 = 2) for the spreadsheet and for our official version System Parameter ATTR4402.
o If the TAV version is LESS than our Official version then immediately stop with a catastrophic error, no other checks need be done, with an Event ID of 352570, Parameters {0} TAV Version from source document; {1} = Our TAV version - System Attribute ATTR4402
§ Also fire silent Event ID 807870, Parameters {0} - Author As as First Name + " " + Last Name, {1} TAV Version from source document; {2} = Our TAV version - System Attribute ATTR4402
§ We will need to support the current and the last version. Example we have versions 1.0, 1.1, 2.0, 2.1, 2.2, 3.0., 3.1, 4.0, 4.1
· So we would support all of the 3.* and 4.* (3.0., 3.1, 4.0, 4.1), but not before 3.* (NOT 1.0, 1.1, 2.0, 2.1, 2.2). In this example our officially last supported version is 3.0)
Minor Release Compare Compare the TAV spreadsheet
o If the First Major Release Compare passed for confirmation of no major structural updates, then lets look at the minor (data update) release
o Compare the number after the decimal point (e.g. 2.3 = 3) for the spreadsheet and for our official version System Parameter ATTR4402.
o If they are not equal then show a warning, but allow to continue, with an Event ID of 352560, Parameters {0} TAV Version from source document; {1} = Our TAV version - System Attribute ATTR4402
§ Also fire silent Event ID 807860, Parameters {0} - Author As as First Name + " " + Last Name, {1} TAV Version from source document; {2} = Our TAV version - System Attribute ATTR4402
The Error/Warnings of above appear in our usual RSIG Error/Warning dialog, not as messages;
[R700105 ends here]
Visit Rows [R700110]
Each row represents a Visit in a single inspection. During the Load process the user may choose to create a Component A inspection, in which case they will be asked what backup B or C Inspection they want to create in case all of the Runs do not cover official Plan Component A inspections. In this case the row here may be split into a Visit in one inspection and a Visit in another (or more extremes). Whether that happens is not relevant at this point;
Each row is loaded starting with Row 8 and continuing until
o “TIE COUNT SUMMARY” is found. If ANY non data-value is found in Column B as the row are read then a Catastrophic error is collected. This is covered in the V2, and related, functionality below;
o If a blank row is found (Columns C, D, E and F are blank) then the row is skipped; (eg sample sheet – row 14)
Visit Row: #2 Start Date [R700120] (mandatory)
o For now assume YYYY-MM-DD and good date (checked in V2);
o This date will be the Start and Stop date of the Visit and by extension Start / Stop Date of each: Logic Check, but NOT the Defect Check (each row has that), and NOT the Tie Count (each row has that);
o Also, all of the Date fields in each of the Visit Rows will be examined
§ Earliest date is the Inspection Start Date;
§ Latest date is the Inspection Stop Date;
Visit Row: #3 Railway [R700130] (mandatory)
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR499. As is always the case in the pattern please remember to do cache the values and check the cache first, we cannot afford to hit the database for every lookup especially since so many values are repeated. If you find the record, which you always should, then substitute the TAV data with TR499, TR158_RAILWAY_ID for send-up for loading. If it cannot be found then add the error event to the collection (Event ID 276100, {0} = Railway value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Visit Row: #4 Subdivision [R700140] (mandatory)
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR497.
§ If found then replace TAV data with TR497, TR360_INF_SUBDIVISION_ID;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276110, {0} = Subdivision value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
§ Note: We’ll have to come back and make adjustments here for Spurs, but do the above for now
§ Load process will use Subdivision (#4), Mile Start (#7) and Mile End (#8) to create a location (GR261) (Type “LocSubdivisionMulti”) against the Visit
· Note: We’ll have to come back and make adjustments here for Spurs, but do the above for now
Visit Row: #5 TAV [R700150] (mandatory)
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR485, LEGACY_SYSTEM_CD
§ If found then replace TAV data with TR485, TRACK_ASSESS_VEHICLE_CD for GR427, TR485_TRACK_ASSESS_VEHICLE_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276120, {0} = TAV value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Visit Row: #6 Track [R700160] (mandatory)
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR486.
§ If found then replace TAV data with TR486, TRACK_TYPE_CD for GR427, TR486_TRACK_TYPE_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276130, {0} = Track Type value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Visit Row: #7 Start Mileage [R700170] (mandatory)
o Mileage Start (assume it’s there in later versions, described below, we will check for these and other mandatory field)
o Note it needs to be to four decimal points;
o Load process will use Subdivision (#4), Mile Start (#7) and Mile End (#8) to create a location (GR261) against the Visit
§ Note: We’ll have to come back and make adjustments here for Spurs, but do the above for now
Visit Row: #8 End Mileage [R700180] (mandatory)
o Mileage End (assume it’s there in later versions, described below, we will check for these and other mandatory field)
o Note it needs to be to four decimal points;
o Load process will use Subdivision (#4), Mile Start (#7) and Mile End (#8) to create a location (GR261) against the Visit (GR264)
§ Note: We’ll have to come back and make adjustments here for Spurs, but do the above for now
Visit Row: #9 Comment [R700190]
o Will be assigned to the Memo field for the Visit (GR264 à GR000 à GR000, ACTIVITY_MEMO_TXT
Visit Row: #9A TC Inspector1 [R700200] (mandatory)
o Same idea for #9A through #11, only the Roles change
o Versions below will verify when values should be there (eg always #9A, if #11, then has to be #10 and #9A, etc) along with various other checks (eg no repeating values on the same row);
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR498.
§ If found then replace TAV data with TR498, TR152_TC_INSPECTOR_ID;
o Created as Responsible RSI #1 against the Visit (Stakeholder Individual with Role of “InspRuleRespRSI”)
Visit Row: #10 TC Inspector2 [R700210]
o Same idea for #9A through #11, only the Roles change
o Versions below will verify when values should be there (eg always #9A, if #11, then has to be #10 and #9A, etc) along with various other checks (eg no repeating values on the same row);
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR498.
§ If found then replace TAV data with TR498, TR152_TC_INSPECTOR_ID;
o Created as Responsible RSI #2 against the Visit (Stakeholder Individual with Role of “InspRuleRespRSI2”)
Visit Row: #11 TC Inspector3 [R700220]
o Same idea for #9A through #11, only the Roles change
o Versions below will verify when values should be there (eg always #9A, if #11, then has to be #10 and #9A, etc) along with various other checks (eg no repeating values on the same row);
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR498.
§ If found then replace TAV data with TR498, TR152_TC_INSPECTOR_ID;
o Created as Responsible RSI #3 against the Visit (Stakeholder Individual with Role of “InspRuleRespRSI3”)
Visit Row: #12 Railway Inspector [R700230]
o Same idea for #12 through #14, only the Roles change
o Versions below will verify when values should be there along with various other checks (eg no repeating values on the same row);
o This is free form text that will be transferred as-is. The Load program will have the user identify which Railway person in RSIG this is for;
o Created as Responsible Railway #1 against the Visit (Stakeholder Individual with Role of “InspVisitRailway1”)
Visit Row: #13 Railway Inspector [R700240]
o Same idea for #12 through #14, only the Roles change
o Versions below will verify when values should be there along with various other checks (eg no repeating values on the same row);
o This is free form text that will be transferred as-is. The Load program will have the user identify which Railway person in RSIG this is for;
o Created as Responsible Railway #2 against the Visit (Stakeholder Individual with Role of “InspVisitRailway2”)
Visit Row: #14 Railway Inspector [R700250]
o Same idea for #12 through #14, only the Roles change
o Versions below will verify when values should be there along with various other checks (eg no repeating values on the same row);
o This is free form text that will be transferred as-is. The Load program will have the user identify which Railway person in RSIG this is for;
o Created as Responsible Railway #3 against the Visit (Stakeholder Individual with Role of “InspVisitRailway3”)
Condition Summary section is comprised of four specific rows with three columns of integer values. All four rows need to be there. Each row will eventually feed into the custom table GR428 which is a child of the Main Inspection. This data will display in a datagrid in a custom TAV tab on the Main Inspection screen:
Condition Summary Row: #15 Non-Compliant Conditions: [R700300] (mandatory)
o Condition 1 of 4: “Non-Compliant Conditions” and integer values in the next three columns;
o Versions below will confirm that this particular condition exists and that the next three columns are integers of at least value 0. Also that the four rows are in the specific order and placement expected;
o Creates a GR428 value with the primary key value of “CondTypeNonComp” with the first column being assigned to GR428, TOTAL_FOUND_QTY, second column being assigned to GR428, TOTAL_PROTECTED_QRT and the third column being assigned to GR428, TOTAL_FEET_QTY;
Condition Summary Row: #16 Non-Compliant Conditions: [R700310]
o Condition 2 of 4: “Conditions for Review” and integer values in the next three columns;
o Same idea as #15, except Primary Key is “CondNonComp”;
Condition Summary Row: #17 Non-Compliant Conditions: [R700320]
o Condition 3 of 4: “OIC Non-Compliant Conditions” and integer values in the next three columns;
o Same idea as #15, except Primary Key is “CondTypeOICNonCom”;
Condition Summary Row: #18 Non-Compliant Conditions: [R700330]
o Condition 4 of 4: “OIC Concerns” and integer values in the next three columns;
o Same idea as #15, except Primary Key is “CondNonComp”;
Condition Summary Row: #19 Feature Summary: Bridge [R700400]
o Feature 1 of 6: “Bridge” and integer values in the next five columns;
o Versions below will confirm that this particular feature exists and that the next five columns are integers of at least value 0. Also that the six rows are in the specific order and placement expected;
o Creates a GR429 value with the primary key value of “FeatureTypeBridge” with the first column being assigned to GR429, TRACK_NBR, second column being assigned to GR429, WALKED_NBR, the third column being assigned to GR429, MAIN_TRACK_NBR, the fourth column being assigned to GR429, NON_MAIN_TRACK_NBR and the fifth column being assigned to GR429, OTHER_TRACK_NBR;
Condition Summary Row: #19 Feature Summary: Crossing [R700410]
o Feature 2 of 6: “Crossing” and integer values in the next five columns;
o Same idea as R700400, except Primary Key is “FeatureTypeCrossing”;
Condition Summary Row: #19 Feature Summary: Culvert [R700420]
o Feature 3 of 6: “Culvert” and integer values in the next five columns;
o Same idea as R700400, except Primary Key is “FeatureTypeCulvert”;
Condition Summary Row: #19 Feature Summary: Derail [R700430]
o Feature 4 of 6: “Derail” and integer values in the next five columns;
o Same idea as R700400, except Primary Key is “FeatureTypeDerail”;
Condition Summary Row: #19 Feature Summary: Derail [R700440]
o Feature 5 of 6: “Derail” and integer values in the next five columns;
o Same idea as R700400, except Primary Key is “FeatureTypeRailCross”;
Condition Summary Row: #19 Feature Summary: Derail [R700450]
o Feature 6 of 6: “Turnout” and integer values in the next five columns;
o Same idea as R700400, except Primary Key is “FeatureTypeTurnout”;
Condition Details are the many defects that are collected over the series of Runs and eventually will become part of the GR430 section of each Inspection created. There are two type of Defects which are Non-Compliant Conditions and Concerns:
o There will be 30 columns of data transferred for each row [R700600 begins here]:
§ First section represents whether the defect is a Non Compliant Condition (in the “NON-COMPLIANT CONDITIONS” section) or a Concern (in the “CONCERNS” section). Non Compliance is mapped to GR428, NON_COMPLIANT_IND = 1 while Concerns is mapped as GR428, NON_COMPLIANT_IND = 0
§ 30 of the columns of data are represented by columns #20 through #45; [R700600 ends here]
Condition Details Row: # 20a/#46a Subdivision (mandatory) - will either be the subdivision or the spur identified in the Runs section. As in the Runs section spurs are prefixed with 'Spur-" and both subdivision and spur must be
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR497.
§ If found then replace TAV data with TR497, TR360_INF_SUBDIVISION_ID;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276110, {0} = Subdivision value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
§ Note: We’ll have to come back and make adjustments here for Spurs, but do the above for now
§ Load process will use Subdivision (#4), Mile Start (#7) and Mile End (#8) to create a location (GR261) (Type “LocSubdivisionMulti”) against the Visit
· Note: We’ll have to come back and make adjustments here for Spurs, but do the above for now
Condition Details Row: #20/#46 Start Mileage (mandatory) – note 4 decimal points - maps to GR430, MILE_POINT_FROM_NBR [R700610]
Condition Details Row: #21/#47 Start Latitude (not mandatory) – note 6 decimal points - maps to GR430, START_LATITUDE_GIS [R700620]
Condition Details Row: #22/#48 Start Longitude (not mandatory) – note 6 decimal points - maps to GR430, START_LONGITUDE_GIS [R700630]
Condition Details Row: #23/#49 Maximum Mileage (not mandatory) – note 4 decimal points - maps to GR430, MAXIMUM_MILEAGE_NBR [R700640]
Condition Details Row: #24/#50 Maximum Latitude (not mandatory) – note 6 decimal points - maps to GR430, MAXIMUM_LATITUDE_GIS [R700650]
Condition Details Row: #25/#51 Maximum Longitude (not mandatory) – note 6 decimal points - maps to GR430, MAXIMUM_LONGITUDE_GIS [R700660]
Condition Details Row: #26/#52 End Mileage (mandatory) – note 4 decimal points - maps to GR430, MILE_POINT_TO_NBR [R700670]
Condition Details Row: #27/#53 End Latitude (not mandatory) – note 6 decimal points - maps to GR430, END_LATITUDE_GIS [R700690]
Condition Details Row: #28/#54 End Longitude (not mandatory) – note 6 decimal points - maps to GR430, END_LONGITUDE_GIS [R700700]
Condition Details Row: #29/#55 Track Type (mandatory) – same logic as found for Track R100160 except use Event ID 276135 versus 276130 [R700710]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR486.
§ If found then replace TAV data with TR486, TRACK_TYPE_CD for GR430, TR486_TRACK_TYPE_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276135, {0} = Track Type value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #29a/55a Track ID (mandatory)
§ No transformation. Data is either free text or a list selection value in source software
Condition Details Row: #30/#56 Facility (not mandatory) [R700710]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR489, LEGACY_SYSTEM_CD
§ If found then replace Facility data with TR489, TRACK_FACILITY_TYPE_CD for GR430, TR489_TRACK_FACILITY_TYPE_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276170, {0} = Facility value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #31/#57 (Defect) Code (not mandatory) [R700720]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR489, LEGACY_SYSTEM_TXT
§ If found then replace Defect data with TR489, TOOL_CRITERIA_ID for GR430, TR430_TRACK_DEFECT_ID;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276170, {0} = Defect Code value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #32/#58 (Condition) Type (not mandatory) [R700730]
o Needs to be transformed to a RSIG value
§ "OIC" in Non-Compliant Condition section to “CondTypeOICNonCom” (#17 "OIC Non-Compliant Conditions"), but "OIC" in Concerns section to “CondTypeOICConcrn” (#18 "OIC Concerns”);
§ "Non-Compliant Conditions" to "CondTypeNonComp" (#15 Non-Compliant Conditions);
§ "Conditions for Review" to “#16 Conditions for Review" (CondNonComp)”
§ Assigned eventually to TR487_TRACK_CONDITION_TYPE_CD
§ Any other value results in an error (Event ID: 276180, {0} = Condition Type value from TAV sheet, {1} = Excel Cel eg B21, {1} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #33/#59 Maximum Speed (optional) – note 4 decimal points - maps to GR430, MAXIMUM_MILEAGE_NBR [R700740]
Condition Details Row: #34/#60 Units (of measure) (not mandatory) [R700750]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR490, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR489, UNIT_OF_MEASURE_CD for GR430, TR490_UNIT_OF_MEASURE;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276190, {0} = Unit of Measure value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #35/#61 (Defect) Length (mandatory) – maps to GR430, DEFECT_LENGTH_NBR [R700760]
Condition Details Row: #36/#62 Freight (Maximum) Speed (mandatory) - maps to GR430, FREIGHT_MAXIMUM_SPEED_NBR [R700770]
Condition Details Row: #37/#63 Passenger (Maximum) Speed (mandatory) - maps to GR430, PASSENGER_MAXIMUM_SPEED_NBR [R700780]
ONLY Freight OR Passenger, not both need to have a value [R700785]
Condition Details Row: #38 LRT/#64 (Maximum) Speed (not mandatory) - maps to GR430, LIGHT_RAIL_MAXIMUM_SPEED_NBR [R700790]
Condition Details Row: #39/#65 Freight Class (mandatory) [R700800]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR491, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR491, FREIGHT_CLASS_CD for GR430, TR491_FREIGHT_CLASS_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276200, {0} = Freight Class value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #40/#66 Passenger Class (mandatory) [R700810]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR492, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR492, PASSENGER_CLASS_CD for GR430, TR492_PASSENGER_CLASS_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276210, {0} = Passenger Class value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #41/#67 LRT Class (not mandatory) [R700820]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR493, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR493, LRT_CLASS_CD for GR430, TR493_LIGHT_RAIL_CLASS_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276220, {0} = LRT Class value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #42/#68 Protected (mandatory) [R700830]
o maps to GR430, PROTECTED_IND as Yes = 1, No = 0. Validation checks in future versions [R700830]
Condition Details Row: #43/#69 Alignment (not mandatory) [R700840]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR494, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR494, ALIGNMENT_CD for GR430, TR494_ALIGNMENT_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276220, {0} = Alignment value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Condition Details Row: #44/#70 Date [R700850] (mandatory)
o For now assume YYYY-MM-DD and good date (checked in V2);
o Becomes Start and Stop Date of the Defect Check GR430 –> GR000 à GR000, DATE_START_DTE / DATE_STOP_DTE
Condition Details Row: #45/#71 Comment [R700860] (optional) – Comment to Railway - GR430, COMMENT_TO_RAILWAY_TXT
Condition Details Row: #45a / #71a Action (Conditionally Mandatory) - Action and Action Date must both be blank or both have data. Action is comments about the on-site defect resolution. This column is to be blank in all files until the new letters process and the track business process has been modified. Currently defects resolved in the field DO NOT show in the Idb file - they are manually removed by the RSI in 1 Stop before the Idb sheet is created. With the new letter process, defects resolved in the field will be no longer be removed but instead will have an Action and Action Date in the Idb sheet.
o Once in place, these defects would be treated as if they have a satisfactory corrective action (Action text) applied.
Condition Details Row: #45b / #71b Action Date (Conditionally Mandatory) - Action and Action Date must both be blank or both have data. Action Date is date the on-site defect was resolved. This column is to be blank in all files until the new letters process and the track business process has been modified. Currently defects resolved in the field DO NOT show in the Idb file - they are manually removed by the RSI in 1 Stop before the Idb sheet is created. With the new letter process, defects resolved in the field will be no longer be removed but instead will have an Action and Action Date in the Idb sheet.
o Once in place, these defects would be treated as if they have a satisfactory corrective action (Action text) applied and the corrective action date = Date provide in the Action Date column.
Tie Counts are the counting of ties and noting their characteristics. In RSIG, Tie Counts, which is the new Sub Custom “Tie Counts” (GR431), will always be assigned to the first Visit Run record. Normally Component A have Tie Counts and the user is warned if they are not there. Conversely, if they are there for a Component B or C, then there is a warning. We will handle this as one warning (Event ID: 370300)
o There will be 13 columns of data transferred for each row
Tie Counts Details Row: # 100a Subdivision (mandatory) - will either be the subdivision or the spur identified in the Runs section. As in the Runs section spurs are prefixed with 'Spur-" and both subdivision and spur must be
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR497.
§ If found then replace TAV data with TR497, TR360_INF_SUBDIVISION_ID;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276110, {0} = Subdivision value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
§ Note: We’ll have to come back and make adjustments here for Spurs, but do the above for now
§ Load process will use Subdivision (#4), Mile Start (#7) and Mile End (#8) to create a location (GR261) (Type “LocSubdivisionMulti”) against the Visit
· Note: We’ll have to come back and make adjustments here for Spurs, but do the above for now
Tie Counts Row: #100 Mileage (mandatory) – note 4 decimal points - maps to GR431, MILE_POINT_FROM_NBR [R700910]
Tie Counts Row: #101 Track Type (mandatory) – same logic as found for Track ID R100160 except use Event ID 276138 versus 276130 [R700920]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR486.
§ If found then replace TAV data with TR486, TRACK_TYPE_CD for GR431, TR486_TRACK_TYPE_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276138, {0} = Track Type value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Tie Counts Details Row: #101a Track ID (mandatory)
§ No transformation. Data is either free text or a list selection value in source software
Tie Counts Details Row: #102 Alignment (mandatory) [R700930]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR486.
§ If found then replace TAV data with TR494, ALIGNMENT_CD for GR431, TR494_ALIGNMENT_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276240, {0} = Alignment value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Tie Counts Row: #103 % Defective (mandatory) - maps to GR431, DEFECT_PERCENT_NBR [R700940]
Tie Counts Row: #104 Freight (Maximum) Speed (mandatory) - maps to GR431, FREIGHT_MAXIMUM_SPEED_NBR [R700950]
Tie Counts Row: #105 Passenger (Maximum) Speed (mandatory) - maps to GR431, PASSENGER_MAXIMUM_SPEED_NBR [R700960]
Tie Counts Row: #106 LRT (Maximum) Speed (not mandatory) - maps to GR431, LIGHT_RAIL_MAXIMUM_SPEED_NBR [R700970]
Tie Counts Row: #107 Freight Class (mandatory) [R700980]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR491, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR491, FREIGHT_CLASS_CD for GR431, TR491_FREIGHT_CLASS_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276205, {0} = Freight Class value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Tie Counts Row: #108 Passenger Class (mandatory) [R700990]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR492, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR492, PASSENGER_CLASS_CD for GR431, TR492_PASSENGER_CLASS_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276215, {0} = Passenger Class value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Tie Counts Row: #109 LRT Class (not mandatory) [R701000]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR493, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR493, LRT_CLASS_CD for GR431, TR493_LIGHT_RAIL_CLASS_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276225, {0} = LRT Class value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Tie Counts Row: #110 Rail Type (mandatory) [R701010]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR495, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR495, RAIL_TYPE_CD for GR431, TR495_RAIL_TYPE_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276235, {0} = Rail Type value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Tie Counts Row: #110 Tie Type (mandatory) [R701020]
o Needs to be transformed to a RSIG value using the Data Transformation Pattern based on TR496, LEGACY_SYSTEM_CD
§ If found then replace Defect data with TR496, TIE_TYPE_CD for GR431, TR496_TIE_TYPE_CD;
§ If not found add to Regular Error collection and move on to the next cel (Event ID: 276220, {0} = Rail Type value from TAV sheet, {1} = Cel issue found in, eg B21; {2} = via RSIG Common Function retrieve System Parameter for parameter ATTR4385)
Tie Counts Row: #111 Comment [R701030] (optional) – Comment to Railway - GR431, COMMENT_TO_RAILWAY_TXT
Generic Errors: Mandatory Field is missing [R701100] When a mandatory field has no value. Parameter passed is the cel that is missing the value. Which Event depends on what section the error was found: Header = 276250, Runs = 276255, Condition Type Summary = 276265, Feature Summary = 276270, Non-Compliant Conditions=276275, Concerns=276280,Tie Counts = 276285
Generic Errors: Wrong Data Type [R701110] When a field has the wrong type (eg date field has text). Parameter passed is the cel that is missing the value. Which Event depends on what section the error was found: Header = 276300, Runs = 276305, Condition Type Summary = 276310, Feature Summary = 276315, Non-Compliant Conditions=27632, Concerns=276330, Tie Counts = 276335. This replaces the previously generic Event 276700.
Catastrophic Errors
Anytime a Catastrophic Error occurs, then the Error should be added to the collection of all of the errors and warning collected so far and the analysis should stop. Catastrophic Errors should never happen.
Catastrophic Error: Cannot Open File [R701200]
Cannot open the file for analysis (Event ID: 276300)
Catastrophic Error: Section Missing [R701210]
As each row of the file is read, the following mandatory sections should be found: “HEADER”, “RUNS”, “TIE COUNT SUMMARY”, “CONDITION TYPE SUMMARY”, “FEATURE SUMMARY”, “BIP CONDITIONS”, “NON-COMPLIANT CONDITIONS”, “CONCERNS”, “TIE COUNTS”
If any of those sections are missing, then the this triggers Event ID 276310 (Parameter {0} = Section Name)
For UAT we only need to test the first section (“HEADER”), but for production all sections need to be tested;
Error / Warning Validations – Multiple Iterations:
At this point only up to V3 work above is the priority
V2: Error/Warning Validations – Transformation Errors
Error in transforming data related to TR485 – TR499;
Few specific errors: 1. Subdivision not on file; 2. TAV Not on file;
V3: Error/Warning Validations – Initial Minimum
Missing Sections (each);
Few specific errors for walkthroughs;
V4: Further Error/Warning Validations -
Duplicate:
o File name already uploaded (warning);
o File contents already uploaded (error);
Missing details
o Mandatory columns that are missing (each marked above as ‘(mandatory)’)
o Header row 3
o At least one Visit Run;
o At least one Non Compliant Condition or Concerns
§ Warning if both Non-Compliant and Concerns have a Count of 0 (Event ID: 370310)
§ Warning if Non-Compliant Count > 0, but Concerns Count = 0 (Event ID: 370320)
§ Warning if Non-Compliant Count = 0, but Concerns Count > 0 (Event ID: 370330)
o Four sections in Condition;
o Six specific rows in Feature;
Bad data;
o Visit Row Date not YYYY-MM-DD;
o Visit Row Date out of range with other dates or unreasonable;
Business Rules
o Special cases for combo of Railway and RSI values in Visit Runs;
§ No Tie Counts should have 0’s in the Tie Count Summary
§ No repeating RSI #1/#2/#3
§ One of the Runs have Lead RSI in it;
§ No repeating Railway personnel
§ Cannot have blanks (#1 and #3 but no #2 for RSI and Railway)
§ Four Conditions rows #15-#18 all have integers (at least 0) for columns Total Found, Total Protected, Total Feet
§ Conditions “OIC Concerns” is one row after “OIC Non-Compliant Conditions” which is seven rows after “Conditions for Review” which is is two rows after “Non-Compliant Conditions”
§ Feature section
· Rows in order “Bridge”, “Crossing”, “Culvert”, “Derail” , “Rail Crossing” and “Turnout”;
· Each row has a positive integer value for the next five columns that represent “Total”, “Walked”, “Main Track”, “Non Main Track”, “Other Track”
§ Any mileage greater than 4 decimal points;
§ Any GIS
· outside of -180 to 180;
· greater than 4 decimal points;
§ #43 Protected is Yes/No
§ Tie Count – Percentage 0 to 100