CIID
Generate
CEDS

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

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 11.3 released in early 2024 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:

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

Adding the Views

  1. Save the provided script views. 

git clone https://github.com/CEDS-Collaborative-Exchange/CIID-Reports.git
https://github.com/CEDS-Collaborative-Exchange/CIID-Reports/tree/5bad8914cec558ab7428bad9fd0aab8f962b03a6/significant-disproportionality/SQL%20Views

  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.

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.

Screenshot of the Power Query interface in Microsoft Excel showing a list of expanded queries categorized under folders named Identification Tables, Discipline Tables, Placement Tables, and Reference Table Tables. Red arrows point to each folder indicating the need to connect these tables to a CEDS Data Warehouse for accurate report sourcing.
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

Screenshot of the Power Query interface in Microsoft Excel. The image shows expanded query folders named ‘Identification Tables’, ‘Discipline Tables’, ‘Placement Tables’, and ‘Reference Table Tables’. Red arrows emphasize the need to link these tables to a CEDS Data Warehouse for accurate report data sourcing.
Connecting Power Query Tables to CEDS Data Warehouse

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

A screenshot of a data management software interface. The user is instructed to click on each table and then select the ‘Advanced Editor’ option. Tables listed include ‘RDS ChildCount’, ‘RDS Membership’, and others.
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

A screenshot of the Advanced Editor in a database management software showing code for updating various tables (RDS ChildCount, RDS Membership, RDS.Placement, etc.) with new IP addresses and database names.
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.

A user interface displaying workspace creation options, including naming the workspace, adding a description, assigning it to a domain (optional), uploading an image (optional), and accessing advanced settings.
Setting up a new workspace for productivity.

  1. Upload the .pbix file to the workspace.

A user interface displaying the Power BI service. The ‘Sig Dispro Test Workspace’ is highlighted. Options include ‘+ New’, ‘Upload’, ‘Create app’, and ‘Manage access’. Below these options, there is a menu for uploading a file with sources like ‘OneDrive for Business’, ‘SharePoint’, and ‘Browse’. The user is prompted to upload a .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.

A user interface showing a list of files within the ‘Sig Dispro Test Workspace’. There are three files listed, named ‘Significant Disproportionality Report #1’, with the type specified as ‘Report’ and owned by ‘Sig Dispro Test Workspace’. The most recent file is highlighted, indicating it is selected. Above the list, there are options to create new content, upload, or get data. On the right side of the screen, workspace settings and more actions can be accessed.
Screenshot of a digital workspace with reports ready for use.

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

Power BI report showing the State Overview page with various charts and the 'Share' button highlighted for managing permissions.
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.

Power BI report showing the State Overview page with various charts and the 'Send link' dialog box for sharing the report.
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.
PreviousSignificant DisproportionalityNextSupport

Last updated