Technical Details

Current Release Version: 2023.1.2

Release Date: 18 Aug 2023 

Report Location: PowerBI / Apps / SPS Projects

Security Roles: All SPS Leadership, Program Directors, and Admissions

Requested Features

Current Cycle

Release 2023.1.3 [September 2023]

Features to Complete

• Data model
  • Include additional data points
    
    • HBCU Fellow (By Decision)
    • CU Employee
    • CONSIDER: How did they hear about us
      • This is dependent on how well the data that exists can be normalized
  • Build pipeline to automate import of Slate export to CEREPORTS
    • Only remaining step is the actual import of the data file to the warehouse (still must manually run the script)
• Report
  • Personal
    • Filters: CU employee, HBCU (when data exists in model)
    • Improve categorization of citizenship, ethnicity, veteran status flags (Yes/No vs. 0/1)
  • Show overall totals by default, but allow comparison of two years through a year selector
  • Button to switch between US, International, and combined views when looking at regional details
    • E.g., on the personal demographics page, switch focus to US so the map can be isolated to US and states; hide US from International view

Features to Consider

• Add the average job length to the Jobs page
• Is there a way to surface serial applicants in the counts? The applicant counts are based on distinct PID, so multiple apps are getting suppressed currently.

Features Ready for Release

• Not started

Previous Cycles

Release 2023.1.2 [August 2023]

• SQL-ONLY
  • Update the procedure to combine "Committed Deferral" cases into the "Committed" counts.
  • The current country, city, state is returning multiple rows; need to isolate to a single row from the ADDR table by type code
• Add cereports.dbo.CTRY_COUNTRY_REGION data to support filtering on broader regions
  • Use the country code for the citizenship country
  • Include filter on all pages
• Add Application and Enrollment Term filters to each of the pages to allow breaking out by specific term instead of AY only

Release 2023.1.1a [July 2023]

• Removed hidden filter on latest_job for Jobs tab on the Total Work Experience visual

Release 2023.1.1 [May 2023]

• Report
  • Filters: Rejected (all three tabs)

Release 2023.1.0 [April 2023]

• Data model
  • Include Matriculated and Graduated as Application Status values for filtering
• Slate export scheduled daily and copied to the H: drive for import
• AIS Insights
  • Outcomes visual needs to be sorted properly
• Remove the BETA graphics
• Report
  • Resize chiclets to include new statuses
  • Personal
    • Filter: Veteran status

Release 2023.0.5 [February 2023]

• BUG: Fix issues with duplicate fields in the underlying Power Query

Release 2023.0.4 [January 2023]

• Refresh the JSON data from Slate
• Add the same note and logic for fraud removals from the ASR to all pages
• Update SQL procedure to return lookup table details from the source material instead of using Power Query in the PBI
  • PROC: a2_demo_fact_pbi
  • Point the PBI tables to the new SQL output and remove the Power Query transformations

Release 2022.0.3 [May 2022]

• Refactor the procedure to use the new a1_admissions_summary view to ensure consistency between ASR and this report
• Clean out previous bookmarks from the original report; add a new one for the Insights page
• Report
  • Added Academic Year trend visual to each page

Release 2022.0.2 [May 2022]

• Report
  • All Pages
    • Allow for an Academic Year selector to group values
    • Sort Unsubmit, Submit, Admit, Commit buttons by funnel position
    • Added Modality as a calculated field and included in the filters
  • AIS Insights Infographic
    • Create a page to allow a closer look at the pipeline in relation to U.S. residents (domestic and international)
    • Initial filters and narrative should focus on the Online programs
    • Added text label to the total count indicator to improve readability
• Data model
  • Move Application Status to a lookup table so it can be applied universally on any page

Release 2022.0.1 [May 2022]

• Report
  • All Pages
    • Default to a school-wide view of all data elements
    • Allow filtering by program(s) and degree type for segmented view
    • Show full counts of applicants at the top
    • Add button to toggle between Top 5-20 and All views
    • Stack student status in the bar charts to show the split between FT/PT
  • Jobs
    • Employer, overall jobs, current jobs
    • Total work experience, current job work experience
      • Convert job months to years and group into ranges (e.g., less than one year, 1 year, 2 years, 3 years, 4 years, etc.)
      • Clean up the year ranges to cover less than one year, and group greater than 10 ranges
  • Schools
    • Undergraduate institution and major, graduate institution and major
    • Undergraduate, graduate gpa
      • Round and group GPAs into ranges
  • Personal
    • Citizenship, current country, state, city
    • Ethnicity, age at application
• Data model
  • Build underlying relational data model to support demographic data points per person in Slate and SIS
    • Sourced from SIS
      • Citizenship Status (Domestic/International)
      • Citizenship Country
      • Gender, Race/Ethnicity, Veteran Status
      • Columbia Alumnus
      • Current Country, City, State
      • Age (at time of application submit)
    • Sourced from Slate
      • APPL_SLATE
        • Student Status (application)
      • Demographics_JSON
        • Undergraduate and graduate institutions (normalized)
        • Employers and jobs (normalized)
          • Flag for latest employer and job distinction
        • Work experience, per job and total (months)