Review 'Temp' views in warehouse

[Brian Lewis]

No description provided.

[Ghislain Hachey]

@softwords the Temp views were written to make the School Accreditation Reports easier. Note that the data for school accreditation version one is essentially the aggregated FORM B report data which is currently the only data available to the national department. We need to discuss how to best go forward with this as this next version of the school accreditation will now store all the granular survey data in XML also in the school inspection framework. Maybe the raw warehouse table could store the data as was recently discussed and we could have wrapper views for the FORM B school, national and district reporting?!

[Ghislain Hachey]

@softwords I lowered priority to Minor for this task as we need to work on the other missing pieces of the school accreditation SRS before this one. But if you manage to work something out before our next Skype call (please make a small slot in your Monday for this) to discuss current work and immediate priority then by all means, don't let me hold you back.

[Brian Lewis]

TempAgeSpecificEnrol; TempAgeSpecificEnrolDistrict

Comments:

-- when working directly on the population table, always be sure to specify a single population model; otherwise, you will get multiple population data. In the warehouse, it always uses the model flagged in PopulationModel table as the default model.

-- warehouse.EnrolAndPop already includes the default population numbers for each district (if district population available) or the national totals (ie district Null) if no district data. However, these are on different records to the enrol number; (warehouse.EnrolAndPop is a UNION query) soits output needs to be flattened into one record per district/age by a GROUP BY.

-- a couple of new views are added to do this:

-- warehouse.EnrolPopDistrict

-- warehouse.EnrolPopNation

These views are still normalised by Gender. As preiouvsly discussed, normalised data like this is usful for building pivot tables, and for use in other tools (Tableau etc) expecting to work with a "cube".

Banded Report writers (Jasper) are happier with the data denormalised on gender, ie having EnrolF, EnrolM, Enrol as separate fields on a single row.

So initiating a convention that where we have equivalent datesets normalised and denormalised by Gender, we append R for "Report" to the view name.

So we now have:

warehouse.Enrol , warehouse.EnrolR

warehouse.EnrolDistrict , warehouse.EnrolDistrictR

warehouse.EnrolNation , warehouse.EnrolNationR

We can have versions that include Population at the District and Nation level ( these then omit Classlevel disaggregation

warehouse.EnrolPopDistrict , warehouse.EnrolPopDistrictR

warehouse.EnrolPopNation , warehouse.EnrolPopNationR

TempAgeSpecificEnrol as currently defined:

SELECT [SurveyYear]
      ,[Age]
      --,[GenderCode]
      ,[DistrictCode]
      --,SUM(E1.[Enrol]) Enrollments
      ,SUM(CASE WHEN GenderCode = 'M' THEN E1.[Enrol] END) AS enrolM
      ,SUM(CASE WHEN GenderCode = 'F' THEN E1.[Enrol] END) AS enrolF
      ,SUM(E1.[Enrol]) AS enrol
      ,E2.popM
      ,E2.popF
      ,E2.popSum
  FROM [warehouse].[EnrolAndPop] AS E1
  INNER JOIN (SELECT [dID]
      ,[popYear]
      ,[popAge]
      ,[popM]
      ,[popF]
      ,[popSum]
  FROM [dbo].[Population]) AS E2 ON E1.SurveyYear = E2.popYear AND E1.Age = E2.popAge and E1.DistrictCode = E2.dID
  WHERE E1.Enrol IS NOT NULL AND Age > 4
  GROUP BY SurveyYear, Age, DistrictCode,E2.popM,E2.popF,E2.popSum

can be replaced by warehouse.EnrolPopNationR, or to produce a smaller recordset exactly matching the fields above:

 select 
  SurveyYear
  , Age
  , EnrolF, EnrolM, Enrol
  , PopF, PopM, Pop
  from warehouse.EnrolPopNationR
  order BY SurveyYear, Age

Similarly TempAgeSpecificEnrolDistrict can be replaced by warehouse.EnrolPopDistrictR, or:

 select 
  SurveyYear
  , DistrictCode
  , District
    , Age
  , EnrolF, EnrolM, Enrol
  , PopF, PopM, Pop
  from warehouse.EnrolPopDistrictR
  order BY SurveyYear, Age

[Brian Lewis]

Pull Request #310 includes SQl - deployed to pineapples_develop, FEDEMIS and MIEMIS production on 21 April 2019

@shraddha_tambe, please bear these views in mind for reporting needs....