DARTS : Student Census Documentation

Information for building the Census tables on February 15^th, August 15^th and October 15^th

• (#1) The first thing that must be done is to check that the student extract and the student archive have run in CSRPT. (For more details see the Daily Extract Documentation). The student archive (UMSTUARC) must be finished before the census tables can be built. You should not wait for the email from the table checking app engine or run this SQL code.

• (#2) If UMSTUEXT, UM_STUCRSTAB and UMSTUARC have all run, then you should check the headcount and course credit hours (units) by running Student Profile Report. In CSRPT, navigate to University of Maine System > Records and Enrollment > Student Report Home.  Click on the Student Profile Report and select a run control ID or add a new run control ID (it can be any short descriptive string).
  • 1) Run "Archive" with the grid option checkbox on the run control page.  The "Term" is the current term. Click the radio button for "Archive". The "As of Date" must be the census date (today's date). Check the box for "Calculate Units Using Grid Values".
  • 2) Run "Archive" without the grid option selected. The "Term" is the current term. Click the radio button for "Archive". The "As of Date" must be the census date (today's date). Uncheck the box for "Calculate Units Using Grid Values".
    
    Here is an example with the grid option selected and the source set to archive. You should wait to run the second report until the status of the first report changes to "Processing". File type is 'Web' and the format is 'PDF'. Since this program uses archive table, it may take a long time to run it.
    
• (#3) Compare the numbers on these two reports. You can use Ultra Compare or simply print out the report. If they match, then build the census table. 
  
  
• (#4) Navigate to University of Maine System > Records and Enrollment > Census Table Builder. (You don't have to log in as BATCH.)
  
  
  • 1) Enter the census date in edit box. The correct term should now appear on the run control page as in this example:
  • 2) Run UM_STCENSUS and UM_STUCRSCEN (Do Not run UM_STURETENT at this time). Don't change the settings for Type and Format. (It may take up to 10 minutes for each job.)
         
    
    

• (#5) When they have completed, test the census table by running another Student Profile Report with the source set to census as in the following:
  • 1) Run "Census" with the grid option checkbox on the run control page.  The "Term" is the current term. Click the radio button for "Census". Check the box for "Calculate Units Using Grid Values".
  • 2) Run "Census" without the grid option selected. The "Term" is the current term. Click the radio button for "Census". Uncheck the box for "Calculate Units Using Grid Values".
    

• (#6) Compare the numbers on these two reports. You can use Ultra Compare or simply print out the report. If they match, then go to next step. 
  
  
• (#7) If all is well, then Navigate to University of Maine System > Records and Enrollment > Census Table Builder.
  • 1) Enter the '10/15/2009' in edit box. The term '1010' should now appear on the run control page. This is for student retention, so the date and term is always '10/15/2009' and '1010'. 
    
    
  • 2) Now run UM_STURETENT which will create the table PS_UM_RETENTIONFSB from the term '1010' to the most recent term in PS_UM_STDNT_COHORT. (It may take up to 15 minutes.)
    
    

• (#8)  Open File Explorer and go to the "userparam" share on wv-o-ums-etl1.uad.maine.edu.  Open the file "CensusCapture_Parameters.txt" and change the value for $$Strm to the current term value.  Remember to save the file before proceeding. Next sign on to Informatica and go to Data Integration.  Proceed to "Explore" then to CS Census Data Capture and CS Production.  Right click on "CSI001_Census" and choose run from the menu.  When this is complete you will have a complete backup of the UM_STUD_CENS_VW, UM_STD_ENR_C_VW, and UM_CNS_RPT_VW from census day on SQL Server,  This is a temporary solution until a true data mart is created.
  
  
  • (#8a) **For Summer census only**    Open File Explorer and go to the "userparam" share on wv-o-ums-etl1.uad.maine.edu.  Open the file "CS_IPEDS_MT(Year).txt" and change the value for $$IPEDSYr to the current year.  Remember to save the file before proceeding. Go to CS Census Data Capture and CS IPEDS Datamarts.  Right click on "CS_IPEDS_MT" and choose run from the menu.  When this is complete you will have a updated the CS_Multi_Term_Tbl on SQL Server,  This is a temporary solution until a true data mart is created.

• (#9) Next create the CSV files for the student census and student course census. The student csv creator is an SQR that will need to be run for each of the institutions. In CSRPT, navigate to University of Maine System > Records and Enrollment > Stdnt Rpt File Creation Page > Single Term CSV File Creator. 
  
  
  • 1) Enter the current term. Enter the census date (today's date) in the "As of Date" box. Select the radio button for "All Records". Use the institution radio buttons to select a institution for each of your seven runs. Do NOT change the setting even if the format says 'PDF'. Here is an example:
    
    (#9 continued)
    
  • 2) After the job completes, save as type 'all files' and change the file extension back to csv. Check the number of data for each institution. File name will be 'UMS0x_Student_Extract_1910 (term changes) .csv'. Upload these 7 files to Detail Enrollment Reports folder (Create new term folder if it has not been created.)
  • *The cross-listed course report requirements have not been applied to this file.*  Notify the institutions who offer cross-listed course that the credit hours on the file is not applying the reporting logic.

• (#10) Navigate to Main Menu >  Reporting Tools > Query > Query Manager in CSRPT.
  • 1) Schedule the query, UMS_DTS_STUDENT_CLASSES_XL, for each campus. Change the institution and STRM and run. Make sure that you have 'XLS' format for the output. Delete the comment lines above column headers and save the file as Excel (example: UMS01_Student_Classes_1920.xlsx).
  • 2) These Excel files should be uploaded to Detail Enrollment Reports folder. In the later section, we will run the same data with SQR to create csv files. 
    
    

• (#11) Navigate to University of Maine System > Records and Enrollment > Student Report Home and run the following 4 reports as PDF. Put the current term at the end of the file name. Move all 4 reports to the Aggregate Enrollment Reports folder. (Create new term folder if it has not been created.)
  • Student Residence Report  → Save as "Student Residence Report (term)"
  • Student Plan Report → Save as "Student Primary Plan Report (term)"
  • Duplicate Registration Report → Save as "Student Multiple Registration Report (term)"
    
    
• (#12) We will refresh Power BI reports now.
  
  • 1) The dataset for Power BI is on Power BI Service. Go to https://app.powerbi.com/ to log into Power BI Service. Click the workspace icon that is on the left side column. Find 'Census-DSIT-Data' workspace. (You can click this link (Census-DSIT-Data workspace) to go to the workspace as well.)
    
    
  • 2) The refresh icon will show up when you hover over the mouse to the name of dataset  (The "Type" says dataset.). Refresh the dataset by clicking the icon. It may take up to 5 minutes.

           These PBI reports will be automatically refreshed once the dataset is refreshed.

• • • Student Profile Report
    • Student Summary Report
    • First Time Student Report 
    • Term Comparison Report 
    • Student Tuition Report 
    • Distnace Education Report
    • Class Unit Location Report

• • 3) **Spring and Summer only**
     Go to DSIT-UMS Reports workspace  on Power BI Service.  
    
    • For Spring Census: Refresh the dataset for IPEDS Fall Enrollment Report (census) by clicking the "refresh now" icon. After finish refreshing the data, click the report (the one with blue icon) and check if new data is loaded. IPEDS fall enrollment is not going to be available in fall census as cohort table has to be updated. 
      
    • For Summer Census: Make sure that CS_Multi_Term_Tbl in the SQL Server is successfully updated on the step #8.  Refresh the dataset for IPEDS 12 month enrollment (census) by clicking the "refresh now" icon.  After finish refreshing the data, click the report (the one with blue icon) and check if new data is loaded. 

• (#13) There is one more Power BI report that needs to be refreshed manually. Follow the instructions on Power BI Census Report page to refresh Student Multiple Term Report. 
  
  
• (#14) Check data in Power BI reports :
  • 1) Go to Power BI Census-DSIT-Report App and view each report by selecting the new term. 
  • 2) Run the query, UMS_DTS_CROSSLIST_EMPLID, in CSRPT to get the credit hours from cross-listed courses. Download the data to Excel and create a pivot table to calculate the gain/ loss hours for each institution. The total of gain and loss should be zero for System total. The Student Profile Report produced in the step #5 above does not reflect the cross-listed hours. If you manually add the gain/loss to the numbers on the report from step #5, the credit hours should be the same on the Student Profile Report from Power BI. If the credit hours from Power BI reports do not match with manual calculations, we are in trouble. Check the data on ps_um_cns_rpt_vw.
    
  • 3) Save the Excel file (query results and the pivot table) on Detail Enrollment Reports folder. File name is UMS_DTS_CROSSLIST_EMPLID (term).xlsx

• (#15)  Navigate to University of Maine System > Records and Enrollment >Student Report Home and run 2 SQR reports
  
  • UM Enrollment Census Rpts : It will create 19 files. They should be uploaded to Aggregate Enrollment Reports folder.
    
    
  • Miscellaneous Census Rpts : It will create 10 files. 
    
    • SR_CLASS_SECTION_LIST_(term) should be uploaded to Detail Enrollment Reports folder
    • Student_Classes file for each campus (example: UMS01_Student_Classes_1910.CSV) should be uploaded to Detail Enrollment Reports folder. The data is the same as Student_classes files which were created as Excel with query in the previous step, but we keep both format for these files.
    • The rest of 2 files should be uploaded to Aggregate Enrollment Reports folder. 
  • Below are the names of the 21 files in the Aggregate Enrollment Reports  folder.

• (#16) This is a temporary step, but freeze the data for ps_scc_pers_biog table in CSRPT. The name for summer23 was ps_scc_pers_biog_2330.
• (#17) Once the reports have all been run and added to the drive folder, send an email using the Enrollment Reports Folder Users list in the drive which has been updated using the Enrollment Reports Folder Checklist & Process. Please make sure that DBA team (usit-dba@maine.edu) is included on the email list.
  Sample email wording is below. If there were issues with the census, changes to the Drive organization, or other issues or changes, please indicate them as well. If you get an error message when you hit "send" bottom, you can click "Share with x people: view" option on the message box or go back to the access list on Enrollment Reports folder.