This repository provides a Chronic Absenteeism PowerBi Dashboard that is powered off of the the Ed-Fi ODS with AMT - Analytics Middle Tier. This dashboard provides row level security and filters data based on the users role. For example if a District Superintendent logs in then it will show data for all schools. If a School Principal is logged in he or she sees only data for his school. Subsequently a teacher sees only the students to the sections she teaches.
Supports:
- Ed-Fi Data Standard 3.0 and higher
- Ed-Fi ODS/API Technical Suite 3, version 3.0 and higher
We tried to make this Quick Start as easy as possible to demo.
Please click on this link and login with the users and roles provided below:
Report URL: PowerBi Chronic Absenteeism Dasboard
Password for all accounts is: Ed-Fi!sCool Users:
| Position Title | Name | Email/Username |
|---|---|---|
| Superintendent at Grand Bend ISD | David Wilson | [email protected] |
| Principal at Grand Bend High | Alisa Cameron | [email protected] |
| Principal at Grand Bend Middle | Marjorie Montoya | [email protected] |
| Principal at Grand Bend Elementary | Chadwick Garner | [email protected] |
| Teacher at Grand Bend Elementary | Janet Reid | [email protected] |
| Teacher at Grand Bend High | Russell Gomez | [email protected] |
| Teacher at Grand Bend High | Sara Preston | [email protected] |
| Teacher at Grand Bend High | Trent Newton | [email protected] |
To run this demo locally on your machine or on a virtual machine please follwo these steps:
If you already have PowerBi and an Ed-Fi ODS installed you can skip to the Setup section of this document.
For the quick start install please follow these steps:
1) Open a Windows PowerShell as and Administrator.
From the Windows Menu, search for PowerShell, right click on it, and select Run as Administrator
2) Run the automated installer by pasting this command in to the PowerShell window:
Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; Invoke-Expression ((New-Object System.Net.WebClient).DownloadString('https://github.com/Ed-Fi-Exchange-OSS/Chronic-Absenteeism-Dashboard/raw/main/install.ps1'))
3) Once everything has finished installing you should see PowerBi and the "Chronic Absenteeism Dashboard" open. *Continue on the how to use the dashboard section.
1) If PowerBi is not already open, proceed to open it and the "Chronic Absenteeism Dashboard" PBIX file.
2) On the top menu locate and Click on the "Modeling" option.
3) On the ribbon menu click on View as. Make sure you select "Dynamic Role" & "Other user". Use one of the emails below in the input for "Other user" and click the OK button.
- District Superintendent - [email protected]
- School Principal - [email protected]
- Teacher - [email protected]
4) After a few seconds the Dashboard will finish filtering the widgets to the role of the uset.
The following metrics were all calculated using PowerBI DAX (Data Analysis Expressions)
| Metric | PowerBi Table | DAX | SQL Equivalent Script |
|---|---|---|---|
| IsCHRAB | analytics ADA Yearly | IsCHRAB = (IF([ADA] <= 0.9,1,0)) | IsCHRAB.sql |
| IsCHRABFilter | analytics ADA Yearly | IsCHRABFilter = IF ( [IsCHRAB] = 1, True, False) | IsCHRABFilter.sql |
| IsCHRAB_OnTheVerge | analytics ADA Yearly | IsCHRAB_OnTheVerge = (IF([ADA] > 0.90 && [ADA] <= 0.95 ,1,0)) | IsCHRAB_OnTheVerge.sql |
| IsCHRABFilter_OnTheVerge | analytics ADA Yearly | IsCHRABFilter_OnTheVerge = IF ( [IsCHRAB_OnTheVerge] = 1, True, False ) | IsCHRABFilter_OnTheVerge.sql |
| CHRABRate | analytics ADA Yearly | CHRABRate = DIVIDE(SUM([IsCHRAB]),COUNT([StudentKey]),0). | CHRABRate.sql |
| CHRAB_OneTheVergeRate | analytics ADA Yearly | CHRAB_OneTheVergeRate = DIVIDE(SUM([IsCHRAB_OnTheVerge]),COUNT([StudentKey]),0) | CHRAB_OneTheVergeRate.sql |
| GradeLevelNumeric | analytics StudentSchoolDim | GradeLevelNumeric = SWITCH( [GradeLevel], "First grade", "01 - First grade", "Second grade", "02 - Second grade", "Third grade", "03 - Third grade", "Fourth grade", "04 - Fourth grade", "Fifth grade", "05 - Fifth grade", "Sixth grade", "06 - Sixth grade", "Seventh grade", "07 - Seventh grade", "Eighth grade", "08 - Eighth grade", "Ninth grade", "09 - Ninth grade", "Tenth grade", "10 - Tenth grade", "Eleventh grade", "11 - Eleventh grade", "Twelfth grade", "12 - Twelfth grade" ) | GradeLevelNumeric.sql |
| StudentIdentifier | analytics StudentSchoolDim | StudentIdentifier = (CONCATENATE(SELECTEDVALUE('analyticsStudentSchoolDim'[StudentFirstName]), CONCATENATE(" ",CONCATENATE(SELECTEDVALUE('analytics StudentSchoolDim'[StudentLastName]), CONCATENATE(" # ",SELECTEDVALUE('analytics StudentSchoolDim'[StudentKey]))))) | StudentIdentifier.sql |
| CalendarAndMonth | analytics ADA Monthly | CalendarAndMonth = [CalendarYear] & "-" & IF([Month] <10,"0",BLANK()) & [Month]. | CalendarAndMonth.sql |
| FullName | analytics ContactPersonDim | FullName = [ContactFirstName] & " " & [ContactLastName] | ConctactFullName.sql |
| StudentImage | analytics StudentSchoolDim | StudentImage = "https://district-website/images/students/" & [StudentKey] & ".png" | StudentImage.sql |
Copyright (c) 2021 Ed-Fi Alliance, LLC and contributors.
Licensed under the Apache License, Version 2.0 (the "License").
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
See NOTICES for additional copyright and license notifications.