Certica Products

CRL Provisioning

Updated Mar 24, 2021

CRL Provisioning 

    1. Find a similar district (same state and SIS) to model

    use crl_summary

    select * from district where State_Code = '[state code]' and SIS = '[new district sis]'        

    order by ncesleaid

    2. Configure Data Agent in Data Gateway Manager.  Add Customer variables.  

    To find a [state] district ID, use: http://nces.ed.gov/ccd/districtsearch/ - It is also the first 7 digits on the state school number for CA.  I believe it's true for Texas as well.

    3. Add data uploads

        a. In SSMS, find similar district's data uploads and their acquisition data source id

        b. Find new district's acquisition data source id

        c. Insert new data uploads

        d. In Data Gateway Manager, refresh new Data Source page and review new Data Uploads

        e. Run new Data Acquisition (it sometimes takes 15 minutes to start)

        f. Review results.  Was it successful? How long did it take?

        g. Based on elapsed time, determine appropriate start time for new daily acquisition and set its schedule.  *Contact Martin to get acquisition time.*

    4. Control table configuration

        a. In SSMS, configure ETL Control

        Object:  StoredProcedure [dbo].[uspLoad_ETL_Control_Table_Data]  on DATA_GATEWAY_ETL db

        b. Configure ETL Parameter

        Object:  StoredProcedure [dbo].[uspLoad_ETL_Parameter_Table_Data] on DATA_GATEWAY_ETL db

        c. Configure Rule Variables

        Object:  StoredProcedure [dbo].[uspINS_RULE_VARIABLES] on DATA_GATEWAY_ETL db

        d. For discipline scorecards, check that the FIGHTING_CODE and BULLYING_CODE variable is set correctly. 

        use CRL_SUMMARY

        select * from CERTIFY_RULE_VARIABLE where NCESLEAID = '[new district ncesleaid]' and RuleVariable = 'BULLYING_CODE'

        --use the queries below get the district's fighting and bullying code.  if the rule variable above is not the same, update the rule variable in certify_rule_varaible

        select * from [0642450_PWRSCHL_999_incident_lu_code] 

        where code_type = 'behaviorcode'

        select * from [4816530_SKYWARD_999_PUB_DIS_OFFENSE] where[DIS-OFFENSE-LDESC] like '%fight%'

        select CD, CO from [0600028_AERIES_999_ads] where CO like '%fight%'

        select * from [0623160_AERIES_999_xrf] where tc1 = 'ADS' and de1 like '%fight%'

        select * from [4838700_ESCHOOLP_999_DISC_INCIDENT_Code] where DESCRIPTION like '%fight%'

        select * from [0627810_INFCAMP_999_BehaviorType] where name like '%fight%'

        select * from [4800274_QMLATIV_999_DiscplineReasonCode] where Description like '%fight%'

        e. Check english_lang variable is set correctly:

            select * from [4832280_ESCHOOLP_999_regtb_language] where description like '%English%'

            select PRIMARYLANGUAGE, COUNT(*) 

            from [0604740_PWRSCHL_999_students] 

            group by PRIMARYLANGUAGE

            order by COUNT(*) desc

            select * from [5504050_SKYWARD_999_PUB_LANGUAGE] where [LANGUAGE-LDESC] like '%English%'

            select * from [0600013_AERIES_999_cod] where DE like '%English%' and FC = 'HL' and TC = 'STU' (always appears to be 00)

            select homePrimaryLanguage, COUNT(*) 

            from [0600028_INFCAMP_999_IDENTITY]

            group by homePrimaryLanguage

            order by COUNT(*) desc

             (always appears to be 00)

        f. If customer is TX ESCHOOLP, populate crl_Code_combination with Valid_ADA_Codes_Per_Calendar.

        Use this query to find the calendar values.


        FROM [DATA_GATEWAY_STAGING] .[dbo].[4844150_ESCHOOLP_999_reg_entry_with] reg WITH (NOLOCK) 

        INNER JOIN [DATA_GATEWAY_STAGING].[dbo].[4844150_ESCHOOLP_999_reg_cal_dates] cal WITH (NOLOCK) ON reg.CALENDAR = cal.CALENDAR AND cal.SCHOOL_YEAR = 2019

        WHERE reg.SCHOOL_YEAR = 2019 AND cal.SCHOOL_YEAR = 2019



        Examples of how to  request this data from customer and populate CRL_CODE_COMBINATION is included in the attached document ADA-ELIGIBILITY-CODE.

    5. Data Pre-Load - Run ETL

        a. Check ETL Control table that all jobs status are not 'In Process' (if configuration done over more than 1 day).  Update to 'Completed' as necessary.Data Pre-Load - Run ETL

        b. Check ETL Control table that all jobs status are not 'In Process' (if configuration done over more than 1 day).  Update to 'Completed' as necessary.

        c. update etl_control set job_start = job_start - 1 where ncesleaid = '0629580'

--re-run the ETL process - do not do this when other ETLs could be running.  Otherwise, use the post ETL script in Data Gateway like {call uspSubmitRunEtlRequest('%DG_DB_NAME%', %ACQ_RUN_ID%, '%CUSTOMER_NAME%', '%CUSTOMER_ID%', '%CUSTOMER_STATE%')}' 


                    @DG_DBNAME = 'DATA_GATEWAY',  -- do not change

                    @NCESLEAID = 'xxxxxxx',       -- enter the 7 digit NCESLEAID for the District

                    @ETL_STEP = 3,                     -- do not change

        @RunETL = 1,                         -- enter the number of days to consider, If @RunETL = 0 then do not run ETL. The ETL process will run only if there is less than 8 hours between tables, and the last table is less than X days old, and no Staging tables are empty for this District.

        @RunObservation = 1  -- 1=Yes, 0 = No  

        If Skyward fails, you may need to change the Student Programs in ETL control to Completed from In Process - Set the date back and try again.

    6. Observation Schedules and Notifications

            a. Create Customer in Certify NextGen: https://certify.certicaconnect.com/Customers

                1. Add Customer Information

                2. Add Information Systems

                3. Add Projects/Observations

             b. Import Schools in NextGen using template:


    9. Review the scorecard.  If any rule violations stand out as being too big, use this query to look at similar districts in same state and sis:


    SELECT cre.NCESLEAID, d.District_Name, cre.StudentNumber, cre.SchoolNumber, cre.IsExcluded, cre.ReasonExcluded, cre.StartDate, cre.EndDate, cre.ChangedBy, cre.LastUpdate 


    inner join district d on cre.ncesleaid = d.ncesleaid 

    and d.state_code = 'WI' 

    and d.sis = 'SKYWARD'                     --use all caps: AERIES,INFCAMP

    and d.ETL_LOAD_STATUS = 'NEW'

    order by CertifyRule

    If a similar district has the stand-out rule excluded, we may want to exclude it for this district too

    On rule: DEM 010-0351 - If a student Alias Name is populated, then both the Alias First Name and Alias Last Name must be populated. 

    This rule is turned off for all Aeries districts, because it is not a problem for them.  Aeries automatically pops these for them when it builds the CALPADS extract

    So if this is a Aeries district, turn off this rule for them:

    DECLARE @vNCESLEAID varchar(100) = '[ncesleaid]', 

            @vCertifyRule VARCHAR(100) = '010-0351', 

            @vReasonExcluded VARCHAR(100) = 'Does not apply to Aeries Districts',

            @dStartDate datetime = convert(date,'[date:MM/DD/YYY]'),

            @vChangedBy VARCHAR(100) = 'ahernandez'

    select * from CERTIFY_RULE_EXCEPTION

    where certifyrule = @vCertifyRule


    begin tran




    (@vNCESLEAID,@vCertifyRule,'ALL','ALL',1,@vReasonExcluded,@dStartDate, NULl, @vChangedBy, GETDATE())

    --commit tran

    --rollback tran

    10. If ESCHOOLP or SKYWARD customer, you likely need to add records for ABSENCE_CODE in ETL_PARAMETER table.

    Use these queries to find the absence codes. Re-run etl.


    select distinct attendance_code 

    from [4807590_ESCHOOLP_999_ATT_STU_DAY_TOTALS]

    where view_type = 'STATE'


    select distinct [AAT-ID],[AAT-LDESC] from [4819170_SKYWARD_999_PUB_ATND_ABSENCE_TYPE]

    where [SCHOOL-YEAR] = '2021'


    and [AAT-EXC-UNEXC-TAR-OTH] in ('E','U')

    • After Scorecard Review is completed let Martin know to add the new district to the notification tracking xls

    • On the day before the scorecard review with the customer has been scheduled, in Certify, add users from provisioning document.  

    • After Scorecard Review add District Admins as Certify Administrators in SalesForce.

After Scorecard Review add CRL as a Hosted Implementation in SalesForce.



(48.6 KB)

Previous Article Communication Planning - Email Lists in SalesForce
Next Article CRL Scorecard Availability