Integration build failing due to Fact Discipline package

Date
11-30-2015
Level
Level 4
Jitbit #

Summary
The Fact Discipline package keeps failing in Integration because it cannot find the BehaviorTypeId column. This error only occurs in Integration.
Impacted
This impacted the integration build.
Service Impact
Service was not impacted since this failure was on integration.
Incident Detail
Looking at the logs, it shows that on 1/12/2015 the FactDiscipline dtsx was updated to replace the use of the BehaviorCategoryTypeId for the BehaviorCategory(Description from type table). On 3/3/2015, this package was override by merging of the Core package, where BehaviorCategory was changed back to BehaviorcategoryTypeId. This package was later updated on 3/11/2015 to set property to validate external metadata to false. This was the only change and the column in question remained ; BehaviorCategoryTypeId.
On 1/13/2015, the Stored Procedure 0010-Create DTSXFactDiscipline.sql was updated to replace the column in the temp_FactStudentDiscipline table create statement from BehaviorcategoryTypeId to Behaviorcategory( a string field), allowing the use of the description from the type table instead of the Id. This file was next updated on 10/5/2015 in an attempt to fix missing periodkeys at the school level historical. The updates made were not reverted back to match the reverted changes in the ETL package. The build is failing on integration because the Stored procedure is not in sync with the code in the ETL package.
It was also noticed that when the packages were using the BehaviorCategoryTypeId the calculation were not accurate because BehaviorCategoryTypeId 4 in the ODS is not 'School code of conduct', when the package is specifically looking for BehaviorCategoryTypeId = 4 which should be 'School code of conduct'.

Technical Detail/Fix
The FactDiscipline package was updated to use the Description instead of the typeIds, the Stored procedure was also updated to be in sync with the ETL package; to look up the Description of 'School code of conduct' instead of BehaviorCategoryTypeId = 4. Both package and stored procedure are in sync and calculations now appear more accurate. In addition to this fix in the Core repo, a new script had to be created in the Apps repo to reflect a new number for the FactDiscipline stored procedure in order for it to be created correctly on the DashboardDW database.
Creation date: 12/30/2015 12:20 PM ()      Updated: 12/30/2015 12:32 PM ()