Develop REST endpoints for SchoolAccreditations

[Brian Lewis]

No description provided.

[Brian Lewis]

Accreditation REST Endpoints

All endpoints relating to accreditations are of the form:

api/warehouse/accreditations[/<grouping>][/<groupValue>]?<content>[&report]

<grouping> represents the aggregatation applied to the data.
<grouping> can take these values:

school
district
authority

authoritygovt
schooltype
nation
table (the default - aggregation by district, authority, authoritygovt, schooltype)

As well as these values, accreditation data is grouped by InspectionResult ; ie Level 1, Level 2, Level 3, Level 4

api/warehouse/accreditations/school
api/warehouse/accreditations/district
api/warehouse/accreditations/authority
api/warehouse/accreditations/schooltype
api/warehouse/accreditations/nation
api/warehouse/accreditations/table
api/warehouse/accreditations

GroupValue specifies a particular value of the grouping to filter on:

e.g.
api/warehouse/accreditations/school/PNI001
api/warehouse/accreditations/district/CHK
api/warehouse/accreditations/authority/CDE
api/warehouse/accreditations/schooltype/K12

Content determines the shape of the returned data set and can take these values:

result - returns the overall inspection result (ie level) of the accreditation. This is calculated as described above.
byStandard - returns a record for each standard, showing the score and calculated result for that standard
performance - return a record for each level of result, showing the number of each criteria and standard within the group that attain that result. (This is based on the TmpPerformance... views that use the legacy accrreditations model)

'result' is the default; so that:

api/warehouse/accreditations

yields the same result as

api/warehouse/accreditations/table?result

Num vs NumThisYear

To measure progress towards accreditation, we need to take snapshots by year of the accreditation status of schools in that year. There are two ways we can look at this:
-- find the most recent accreditation inspection for each school up to and including the year ; ie cumulative
-- consider and report on only accreditation inspections conducted within the year.

In any year, it is important to only consider the most recent inspection for each school. Otherwise, results would be distorted by "doubling up" on some schools.

Warehouse tables presenting accreditation data use two fields: Num and NumThisYear.

Num is the number of schools meeting the selection criteria of the record, based on the most recent accreditation performed at that school up to and including the 'survey year' of the record.

NumThisYear is the number of schools matching the selection criteria of the record, that have had an accreditation performed in that year.

In the web dashboard, charts allow you to present either 'Evaluated In <year>' (ie NumThisYear) or 'Cumulative' (ie Num)

To illustrate, suppose a school has 2 accreditations A (Level 2) in 2015, and B (Level 3) in 2017

Then the school will be represented by these accreditations:

-----------Num-----------NumThisYear
2014 - -
2015 A A
2016 A -
2017 B B
2018 B -
2019 B -

Accumuating by result, we would have

Cumulative:

----------- L1 L2 L3 L4
2014
2015 1
2016 1
2017 1
2018 1
2019 1

In Year:

----------- L1 L2 L3 L4
2014
2015 1
2016
2017 1
2018
2019

Content

These options control the format of the data returned by the accreditations API.

result

• tabulates the overall result of the survey. the field InspectionResult holds the result level on each row.

byStandard

• Level results are shown by standard. The filed 'stnadrd' appears on each row.
  Not this view is always is 'report' format, that is, the 8 fields shown above are present on each record.

performance

• this view is a crosstabulation designed for specific, pre-exisitng reports. There is a row for each result level, and a column for each standard and criteria. The value for that column is the number of assessments in the group that achived the refenced result value in that criteria or standard.

Report Format

The option ?report denormalises the returned data by InspectionResult. That is, instead of the aggregation field InspectionResult, and two data fields (Num, NumThisYear);
report format has fields

Level1
Level2
Level3
Level4

Level1ThisYear
Level2ThisYear
Level3ThisYear
Level4ThisYear

This is a smaller dataset, and convenient for a banded report writer.
For 'cube' applications, (pivot tables, crossfilter tableau etc) use the normalised version of the data.

Note that report option cannot be used with 'performance' content option.

Examples

This section lists illustrative REST calls for accreditations.
Try these out (e.g. using Fidller, or pacific emis restest) to examine the results

api/warehouse/accreditations -- table view
api/warehouse/accreditations/table -- same as above

grouping by a particlar entity type

api/warehouse/accreditations/district
api/warehouse/accreditations/schooltype
api/warehouse/accreditations/authoritygovt
api/warehouse/accreditations/authority
api/warehouse/accreditations/school

within this, may be filtered to select a single item:

api/warehouse/accreditations/district/CHK
api/warehouse/accreditations/schooltype/K12
api/warehouse/accreditations/authoritygovt/G
api/warehouse/accreditations/authority/COG
api/warehouse/accreditations/school/PNI200

National totals:
api/warehouse/accreditations/nation

Any of these selections can be combined with the 3 possible content selectors:

api/warehouse/accreditations/district?byStandard
api/warehouse/accreditations/schooltype?byStandard
api/warehouse/accreditations/authoritygovt?performance
api/warehouse/accreditations/authority?result
api/warehouse/accreditations/school --- result is the default

As well, you can combine the 'report' option when using byStandard or result content:

api/warehouse/accreditations/district?byStandard&report
api/warehouse/accreditations/schooltype?byStandard&report
api/warehouse/accreditations/authority?result&report
api/warehouse/accreditations/school?report

‌

[Brian Lewis]

Documentation (above) resolves. Sql Deployed to pineapples_develop 24 9 2019