Using Power BI for December 1
With the release of Phoenix and Power BI at the beginning of the 2019-20 school year, Technology Services is redesigning the December 1 process. The updated December 1 process should improve stakeholders' experience in at least three ways: First, the updated process provides greater visibility into which students will be counted and the barriers preventing students from being counted in the lead up to December 1. Second, the updated process requires less manual intervention and, as a result, is less susceptible to human error. Third, the updated process streamlines Reconciliation by automating many Reconciliation activities. This document describes the resources available in Power BI to assist with the December 1 process. It provides the steps for accessing those resources. And it details the steps for performing the December 1 Reconciliation. Note: The December 1 Reconciliation must be completed during the same school year as the December 1 count! Also note: This guide is intended for end users involved in the December 1 count, Technology Services personnel should consult https://ssdmo.atlassian.net/l/c/EogJRPpe .
Access the December 1 space within Power BI
Access the December 1 space within Power BI by visiting the Power BI production site at https://bi.ssdmo.org. From the homepage, click on the Special Education Data folder.
From the Special Education Data folder, click on the Ad-Hoc Reports folder.
Within the Ad-Hoc Reports folder, click on the December 1 folder to access the December 1 space.
Within the December 1 space are three folders and three KPIs. The December 1 Count KPI shows the number of students that would be included in the December 1 count if the count were completed today. The Priority Audit Issues KPI shows the number of students affected by one or more Dec 1 Priority audit issues. These students cannot be included in the December 1 count until the audit issues affecting them have been resolved. The Reconciliation Submission KPI shows the results of the latest attempt to upload a Reconciliation File and the date (MM/DD) on which that attempt occurred.
The Prospective Reports folder contains information to help during the lead up to the December 1 count. At the time of initial release, four reports are included in the Prospective Reports folder. However, additional reports can be included upon request. The Audit Issue Breakdown displays the number of students affected by each Dec 1 Priority audit issue. The Audit Issue Detail displays detailed information about the students included in the Audit Issue Breakdown. The Students by District report shows the number of students in each district that would be included in the December 1 count if that count were conducted today. The Student Detail displays detailed information about the students included in the Students by District report. For information on the Tentative Master - Student Detail and Tentative Master - Students By District reports, see the Completing the December 1 Count section below.
The Retrospective Reports folder contains information from the most recently completed December 1 count. At the time of initial release, one report is included in the Retrospective Reports folder. The Medicaid Reimbursement report displays student transportation information. However, Retrospective Report information should not be considered reliable until the December 1 count has been completed for 2019.
The Reconciliation folder contains reports for use during the December 1 Reconciliation process. More information about the Reconciliation process is available in the December 1 Reconciliation section below.
Completing the December 1 Count
Each year, at 8:00pm on December 1, an automated process will save a copy of the Phoenix reporting database. That process will also save information about the audit issues affecting students. This reporting and audit information for December 1 will be available in SSD's data warehouse until December 1 of the following year. The reporting and audit information provides data for items included in the Retrospective Reports folder.
When Student Data is ready for the December 1 count to be completed, submit a ticket to Technology Services to perform the December 1 count. Technology Services staff will activate an automated process to load the December 1 master list. Once the December 1 master list has been loaded, December 1 information for the most recently completed December 1 count will become available within the Power BI, Phoenix, Pupil Personnel reports. The Tentative Master - Student Detail and Tentative Master - Students By District reports help Student Data determine when they are ready to submit the ticket to complete the December 1 count. They provide a preview of the information that will be loaded into the December 1 master list. Note: These reports are only to be used after the automated process runs on December 1 but before Student Data submits the ticket to Technology Services.
December 1 Reconciliation - Must be completed during the same school year as the Dec. 1 count.
When Student Data is ready to begin the December 1 Reconciliation process, go to the Reconciliation folder within the December 1 space in Power BI.
The Reconciliation Download report contains a copy of the December 1 master list.
Having opened the Reconciliation Download report, click on the icon, and select Excel from the menu that downloads the December 1 master list as an Excel spreadsheet.
Once exported to Excel, the Reconciliation Download file functions like the Reconciliation template from years past. Green fields within the file are required fields. The Changed column within the file accepts three values: ADD, CHANGE, and DELETE. When you save the file after making your changes, make sure the file retains the name Reconciliation Download and includes a single worksheet named Reconciliation Download.
Once you have modified the Reconciliation Download file as needed, submit the file by copying the file to \\exeter\SPED Ad Hoc Reports\December 1.
Each night at 9:00pm, an automated process will attempt to upload the Reconciliation Download file from the \\exeter\SPED Ad Hoc Reports\December 1 directory. If Student Data has not saved a file named Reconciliation Download.xlsx in the \\exeter\SPED Ad Hoc Reports\December 1 directory, the Reconciliation Submission KPI in the December 1 space will show No File and the date (MM/DD) of the latest upload attempt.
If the automated process found a Reconciliation Download file in the appropriate directory but was unable to upload the file, the Reconciliation Submission KPI in the December 1 space will show Failed Load and the date (MM/DD) of the upload attempt. Usually, a failed load results when a required field has been left blank or a field has been filled in with data of the wrong type. A failed load can also result if the Reconciliation file has been named incorrectly or if the worksheet within the Reconciliation has been named incorrectly.
The automated process checks for several data quality errors after uploading a file. If any of these data quality checks fail, the Reconciliation Submission KPI in the December 1 space will show Data Error and the date (MM/DD) of the upload attempt. At the time of initial release, five data quality checks are performed when a Reconciliation file is uploaded: 1) Do any records have duplicate SSDIDs? 2) Do any records have duplicate State IDs? 3) Do any records have an SSDID that is not 6 characters in length? 4) Do any records have a State ID that is not 10 characters in length. 5) Does the SchoolYear column for any record not contain the current school year?
When a Reconciliation file has been uploaded, that file is available in the Reconciliation Submission report within the Reconciliation folder within the December 1 space in Power BI. You can export the Reconciliation Submission report to Excel for closer inspection using the same process you used to export the Reconciliation Download report in step 3 above. Note: If you wish export a past Reconciliation Submission, modify that file, and then, submit it for upload, you must ensure that you rename the file Reconciliation Download.xlsx. The worksheet within the file must also be named Reconciliation Download. Previously submitted Reconciliation files can also be found at \\exeter\SPED Ad Hoc Reports\December 1\Archive.
If the Reconciliation file failed any of the data quality checks, the records within the file that caused the checks to fail will appear on the Reconciliation Submission Errors report within the Reconciliation folder within the December 1 space in Power BI. The data quality problem with the record can be found in the rightmost column of the report.
For more information about recent attempts to upload a Reconciliation file, consult the Reconciliation Upload Log report within the Reconciliation folder within the December 1 space in Power BI. The report displays information about attempts to upload a Reconciliation file within the last 30 days.
If the Reconciliation file successfully uploads and passes all the data quality checks, the Reconciliation Submission KPI in the December 1 space will show Success and the date (MM/DD) that the process ran to indicate that the Reconciliation process is ready to be completed.
When you are ready to complete the Reconciliation process, submit a ticket to Technology Services requesting that they perform the December 1 Reconciliation. Technology Services personnel will activate an automated process. This process updates the December 1 master to reflect the mostly recently submitted Reconciliation file. ADD records are added to the master list. CHANGE records are modified within the master list. And DELETE records are removed from the master list. Once the December 1 master list has been updated, that information is archived with the information from past December 1 counts. The December 1 master list is cleared, and the Reconciliation Download and Reconciliation Submission reports no longer display any records. The Reconciliation process is now complete.