Expectedly high count of candidates in warehouse exams data

[Ghislain Hachey]

All exams for 2017-21 were loaded. Bringing this up in a pivot table shows some dubious numbers.

Looking at exam “Reading Grade 3 - English” as example but the same is true for all exams. I would expect a total of 504 candicates across all achievement levels in 2018 I get 6048. The same issue can be observed increasingly more in more recent years.

I do not observe this in the [warehouse].[ExamSchoolResultsTyped] data, only in the [warehouse].[ExamTableResultsTyped]

[Ghislain Hachey]

Note those same inflated numbers observed in the pivot table can be seen in the EMIS online dashboard as well.

[Brian Lewis]

@ghachey In the [warehouse].[ExamSchoolResultsTyped] you have the aggregations at Exam, Standard Benchmark and indicator level all in the same table. The field Recordtype tells you which of these levels it is, and Key and Description relate to the particular item.

‌

e.g. RecordType = Indicator; then Key is Indiccator code, description = indicator description

RecordType = Benchmark , key = benchmark code, description = benchmark description

REcordtype = Standard key = Standard Code

RecordType = Exam is a bit different becuase there is only one record at this level with Key = Exam Code

‌

Note that ExamId and ExamCode are on every record for the exam.

So to make a meaningful pivot from this structure:

put RecordType as a page field; and put Key as a Row field.

In the special case to show exam totals, as you are trying to do above, set RecordType = Exam as a page field. Then you will have only one record in each aggregation group in the rows, so now you should get the correct candidate counts.

‌

[Ghislain Hachey]

@Brian Lewis content in your comment was already pretty well understood. While it is not showing the ‘Exam’ filter in the pivot I did filter in the SQL query where clause otherwise I am getting +5,000,000 rows (which is also questionable.)

I maintain that I think there is an issue with [warehouse].[ExamTableResultsTyped] not with [warehouse].[ExamSchoolResultsTyped]. One only has to look at the dashboard online and look at the candidate numbers by hovering over the chart bars to see this. For example, look at the one below with nearly 12,000 candidates in ‘Developing’ only. All the exams have around 1,000 candidates in total. And the dashboard gets the data from [warehouse].[ExamTableResultsTyped].

‌

[Brian Lewis]

fix(exams): inccorect accumulation of ExamTableResultsTyped

Resolves #1180

→ <<cset 85d11dc599fb>>

[Brian Lewis]

Resolved as above, sql changes applied in production and data fixed up by rerunning warehouse.buildexamResults

‌