Setup Guide

Privacy Notice: When using this report, be aware that it is not intended to be public facing, as it contains PII and small cell sizes that may allow students to be identified.

---

Prerequisites

• This report requires states to be users of Generate or a CEDS Data Warehouse.

• Three years of data migrated into Generate for EDPass Files:

  • FS052 – Membership

  • FS002 – Children with Disabilities (School Age)

  • FS006 – Students with Disabilities (IDEA) Suspensions/Expulsions

  • FS143 – Children with Disabilities (IDEA) Total Disciplinary Removals

• The latest version of Power BI Desktop

• Access to the CEDS Data Warehouse

• VPN Access if required

Preparing CEDS Data Warehouse

The Significant Disproportionality report uses stored views of data in the Reporting Tables. These views are not standard but can be added to your instance of Generate’s semantic layer easily. This process does not require any server backups.

Generate Significant Disproportionality views were developed to represent the data used for Child Count, Membership, Placement, and Discipline data in C006 and C143 IDEA reports. These files were developed using Generate 12.3 released in early 2025 and they use the same logic for returning the IDEA submission report results. The views contain fact and dimension table ids that are joined with other fact and dimension tables within the data warehouse to return other data elements used within the BI report.

The Required views are:

Membership

vwSignificantDisproportionality_Membership

Description: All students that are enrolled in a school for the report year, are in grades ungraded, 'AE', and PK through 12 and are between the ages 3-21

Fields Returned:

• Fact.FactK12StudentCountId

• Fact.K12StudentId

• Fact.SchoolYearId

• Fact.LeaId

• LEAs.LeaIdentifierSea

• LEAs.LeaIdentifierNces

• Fact.RaceId

• Fact.IdeaStatusId

• Fact.GradeLevelId

• Fact.AgeId

• 1 AS MembershipStudentCount

ChildCount

C006

C143

StudentDetails

Placement

Adding the Views

1. Save the provided script views from the CIID Reports repo:

git clone https://github.com/CEDS-Collaborative-Exchange/CIID-Reports.git

1. Open SQL Management Studio (or similar tool).

2. Execute the script to add the views to your data warehouse.

3. If changes are made to field names or values, update the BI tool accordingly.

The logic in these views may need to be changed if the reporting requirements change for how child count, placement, or reports 006 and 143.

---

Connecting the report to the source

Remember to connect to the VPN if required by your network administrators.

1. Download the .pbix file from CEDS Collaborative Exchange’s GitHub folder.

https://github.com/CEDS-Collaborative-Exchange/CIID-Reports/blob/main/01)%20significant-disproportionality/Significant%20Disproportionality%20Report.pbix

The downloaded report contains cached test data. This test data was created by CIID for this report's production and is not intended to represent any actual state data.

1. Open the .pbix file in Power BI Desktop.

2. Click "Transform Data" in the home ribbon, then select "Transform Data" again to open Power Query.

Navigating Power Query in Excel for Data Transformation

1. In Power Query, the queries contain a list of tables and report measures essential to the report. Each table needs to be pointed to your CEDS Data Warehouse to ensure that the correct source data is used for the report. This includes each of the queries in the following folders:

   • Identification Tables

   • Discipline Tables

   • Placement Tables

   • Reference Table Tables

Connecting Power Query Tables to CEDS Data Warehouse

1. Click on each table, and then select the advanced editor.

Accessing the Advanced Editor for table customization.

1. Update each table's IP Address and Database name in the Advanced Editor. Tables to Update:

   • RDS ChildCount

   • RDS Membership

   • RDS.Placement

   • RDS.ChildCount_C006

   • RDS ChildCount_C143

   • RDS DimLeas

   • RDS DimRaces

   • RDS DimPeople

   • RDS DimSchoolYears

   • RDS DimGradeLevels

   • RDS DimAges

Updating IP addresses and database names in the Advanced Editor.

1. Click "Apply" then save the report after each query. Applying and saving after each query will allow you to troubleshoot easier if you run into issues with a single query. After refreshing, the data will be cached in the .pbix file.

2. Close Power Query.

---

Distribution

This report includes Student ID numbers and other personally identifiable information (PII) cached in the .pbix file. Ensure it is shared only with authorized individuals. Refer to your SEA’s policies and procedures before sharing.

While the .pbix can be sent to individuals with Power BI Desktop, CIID recommends that this report is distributed with internal state staff through an Online Power BI Workspace. Doing this ensures access can be limited to appropriate individuals, that the report cannot be edited, and provides multiple sharing options, such as embedding into a Teams Channel.

Online Power BI Workspace

1. Sign into office.com and select Power BI.

2. Create a new workspace.

Setting up a new workspace for productivity.

1. Upload the .pbix file to the workspace.

Screenshot of the Power BI service interface with a focus on the ‘Sig Dispro Test Workspace’.

After the report is uploaded in the workspace, the dashboard will be available to use and share by clicking on the .pbix file.

Screenshot of a digital workspace with reports ready for use.

1. Manage permissions by clicking "Share" and selecting the appropriate individuals.

Screenshot of the Significant Disproportionality Report in Power BI. Manage permissions by clicking 'Share' and selecting the appropriate individuals.

1. Type in the name of the person you want to share with. Then click the method you would like to send through.

Screenshot of the Significant Disproportionality Report in Power BI, showing the State Overview page and the 'Send link' dialog box, which allows users to share the report with colleagues via email, Teams, or by copying the link.

---

Refreshing Data

Administrators of the report can refresh the data in different ways:

Desktop .pbix file: Click the refresh button to view updated data.

If this report is being used in the workspace, after refreshing data, then click publish to allow authorized users to view the data.

Online Workspace: Go to the semantic model and click the refresh button.

Refreshing the Semantic Model in the Sig Dispro Test Workspace.

---

Report Year Selection Using Power BI Parameter

This report uses a Power BI parameter to control which years of data are shown. The year toggle set in the Power BI Service helps prevent the display of incomplete or missing data within the data warehouse, as the most recent IDEA reporting year may not yet be available for all required data depending on state submission timelines.

*When using the report, ensure that you understand which data has been migrated into Generate so the correct report year parameters may be chosen.

Available Parameter Options

Parameter Value
Latest 3 years	Loads the 3 latest years including the current year (from the data warehouse) for Membership, Childcount, and Discipline files.
Latest 3 years minus latest year	Includes the latest 3 years excluding the current year (from the data warehouse) for Membership, Childcount, and Discipline files.

1

Navigate to office.com.

2

Select 'Power BI' from the list of apps.

3

On the left menu bar, select 'Workspaces' -> select your workspace

4

Inside the workspace, select the ellipses next to the semantic model row.

5

Select 'Settings'

6

In the settings page, click the drop down for the 'Parameters' section.

7

Finally type either 'Latest 3 years' or 'Latest 3 years minus latest year' depending on desired year range. Click 'Apply'.