Setup Guide
Privacy Notice: This report is intended for public release. Please ensure that suppression thresholds and functionality are aligned with your state’s policy and all necessary quality assurance checks and testing are completed prior to publication.
Prerequisites
Preparing CEDS Data Warehouse
The Assessments 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's Assessment view was developed to represent the data used for academic achievement and assessment participation. 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.
Adding the Views
Save the provided script view below:
CREATE VIEW [RDS].[vwStateAssessments]
AS
SELECT
f.SchoolYearId
, f.K12StudentId
, f.SeaId
, f.LeaId
, ISNULL(lea.LeaIdentifierSea, 'MISSING') as LeaIdentifierSea
, ISNULL(lea.LeaOrganizationName, 'MISSING') as LeaOrganizationName
, f.K12SchoolId
, f.AssessmentId
, f.AssessmentRegistrationId
, f.AssessmentPerformanceLevelId
, accomodations.AssessmentAccommodationId
, f.K12DemographicId
, f.GradeLevelWhenAssessedId
, f.IdeaStatusId
, f.EconomicallyDisadvantagedStatusId
, f.EnglishLearnerStatusId
, f.FosterCareStatusId
, f.HomelessnessStatusId
, f.ImmigrantStatusId
, f.MigrantStatusId
, f.MilitaryStatusId
, f.PrimaryDisabilityTypeId
, race.RaceId
, f.FactK12StudentAssessmentId
, f.AssessmentCount
, f.AssessmentResultScoreValueRawScore
, ISNULL(proficiency.ProficiencyStatus, 'MISSING') as ProficiencyStatus
FROM rds.FactK12StudentAssessments f
inner join rds.DimLeas lea on f.LeaId = lea.DimLeaID
inner join rds.DimK12Schools sch on f.K12SchoolId = sch.DimK12SchoolId
left join (select distinct FactK12StudentAssessmentId, RaceId from rds.BridgeK12StudentAssessmentRaces) race on f.FactK12StudentAssessmentId = race.FactK12StudentAssessmentId
left join rds.BridgeK12StudentAssessmentAccommodations accomodations on f.FactK12StudentAssessmentAccommodationId = accomodations.FactK12StudentAssessmentAccommodationId
left join (
select distinct fact.K12StudentId, fact.SchoolYearId, fact.AssessmentPerformanceLevelId, fact.AssessmentId,
case
WHEN assmntPerfLevl.AssessmentPerformanceLevelIdentifier ='MISSING' THEN 'MISSING'
when CAST(SUBSTRING( assmntPerfLevl.AssessmentPerformanceLevelIdentifier, 2,1) as int ) >= CAST( tgglAssmnt.ProficientOrAboveLevel as int) THEN 'PROFICIENT'
when CAST(SUBSTRING( assmntPerfLevl.AssessmentPerformanceLevelIdentifier, 2,1) as int ) < CAST( tgglAssmnt.ProficientOrAboveLevel as int) THEN 'NOTPROFICIENT'
else 'MISSING'
end as ProficiencyStatus
from
rds.FactK12StudentAssessments fact
inner join RDS.DimAssessments assmnt on fact.AssessmentId = assmnt.DimAssessmentId
inner join RDS.DimGradeLevels grades on fact.GradeLevelWhenAssessedId = grades.DimGradeLevelId
inner join RDS.DimAssessmentPerformanceLevels assmntPerfLevl on fact.AssessmentPerformanceLevelId = assmntPerfLevl.DimAssessmentPerformanceLevelId
left join APP.ToggleAssessments tgglAssmnt ON tgglAssmnt.Grade = grades.GradeLevelCode and tgglAssmnt.Subject = assmnt.AssessmentAcademicSubjectEdFactsCode
AND tgglAssmnt.AssessmentTypeCode = assmnt.AssessmentTypeAdministeredCode
) proficiency on f.K12StudentId = proficiency.K12StudentId and f.SchoolYearId = proficiency.SchoolYearId
and f.AssessmentId = proficiency.AssessmentId
and f.AssessmentPerformanceLevelId = proficiency.AssessmentPerformanceLevelIdOpen SQL Management Studio (or similar tool).
Execute the script to add the views to your data warehouse.
If changes are made to field names or values, update the BI tool accordingly.
Connecting Report to Data Source
Remember to connect to the VPN if required by your network administrators.
Download the ".pbix" report file from the CEDS Collaborative Exchange:
Open the .pbix file in Power BI Desktop.
Under the Home tab, click the drop down to access the "Data source settings".
In the "Data source settings" panel, select the default data source then click "Change Source...".
In the "SQL Server database" panel, replace the "Server" and "Database" fields with your organization's credentials. Click 'OK' when finished.
Distribution
Ensure this report is shared only with authorized individuals. Refer to your agency's policies and procedures before sharing this report.
While the .pbix can be sent to individuals with Power BI Desktop, it is recommended this report is distributed through a Power BI Workspace. This ensures that access is limited to appropriate individuals, the report remains read-only, and multiple sharing options are available.
Setting up a Power BI Workspace
Sign into office.com and select Power BI in the top left menu bar.
Click "Workspaces" on the left menu bar.
Click "+ New Workspace" then fill out the required fields. Select "Apply" when finished.
To import the report into the newly created workspace, select "Import" then "Report or Paginated Report". Then select your .pbix file from the storage options listed.
The report is now uploaded into your workspace. You can manage permissions, data refresh, and distribution from this workspace.
Data Refresh
Administrators of the report can refresh the data in different ways:
Option 1: Refresh from Power BI Desktop
Open the .pbix file. In "Home" tab, click the refresh icon.
After refreshing, click "Publish" then select your Power BI workspace. Click "Select".
Click "Replace" if prompted.
Option 2: Refresh from Power BI Workspace
In your Power BI Workspace, click the refresh icon next to the semantic model option.
Changing Color Theme
Since the .pbix file is provided, your state can customize the report to match your state's color theme before embedding on an external site:
Open the .pbix file and navigate to the 'View tab'.
Next, click the 'Themes' dropdown. Then select the 'Customize current theme' option.
In the pop-up panel, change the primary colors to your state's theme colors and click 'Apply'.
Click save in the top left corner then publish your changes to your workspace.
Data Suppression
To protect individual privacy, all visuals in the reports have cell sizes under 10 suppressed. Suppressing one category may suppress another depending on the total counts. There are no drilldown features, as all personally identifiable data (PII) have been removed.
To adjust cell sizes, please refer to the Data Dictionary to identify the measures in the report that define suppression levels.
Last updated