Warehouse support for Grid presentations

[Brian Lewis]

Add views into warehouse to use for grid presentations

[Brian Lewis]

For a banded report writer like Jasper, to produce a standard enrolment grid like this:

It is convenient to have the data "denormlaised so that one record of the data set corresponds to one row of the grid.

This query in commit 2acf2e1 prepares the data in that format; (see also similar denormalisation of gender in DSWarehouse)

Select 
a.authName as authName,
ee.Age as Age,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='GPRES' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as GPRES_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='GK' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as GK_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G1' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G1_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G2' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G2_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G3' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G3_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G4' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G4_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G5' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G5_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G6' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G6_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G7' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G7_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G8' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G8_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G9' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G9_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G10' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G10_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G11' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G11_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel ='G12' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G12_F,
SUM(CASE WHEN GenderCode = 'F' and ee.ClassLevel IN ('GPRES','GK','G1','G2','G3','G4','G5','G6','G7','G8','G9','G10','G11','G12') THEN COALESCE(ee.Enrol,0) ELSE 0 END) as Total_F,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='GPRES' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as GPRES_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='GK' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as GK_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G1' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G1_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G2' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G2_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G3' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G3_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G4' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G4_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G5' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G5_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G6' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G6_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G7' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G7_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G8' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G8_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G9' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G9_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G10' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G10_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G11' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G11_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel ='G12' THEN COALESCE(ee.Enrol,0) ELSE 0 END) as G12_M,
SUM(CASE WHEN GenderCode = 'M' and ee.ClassLevel IN ('GPRES','GK','G1','G2','G3','G4','G5','G6','G7','G8','G9','G10','G11','G12') THEN COALESCE(ee.Enrol,0) ELSE 0 END) as Total_M,
SUM(CASE WHEN ee.ClassLevel IN ('GPRES','GK','G1','G2','G3','G4','G5','G6','G7','G8','G9','G10','G11','G12') THEN COALESCE(ee.Enrol,0) ELSE 0 END) as Grand_Total
 from warehouse.enrol ee
 INNER JOIN dbo.Schools s ON s.schNo = ee.schNo
 INNER JOIN dbo.Authorities a ON a.authCode = s.schAuth
where surveyYear = 2017
GROUP BY ee.Age,a.authName
ORDER BY a.authName, ee.Age

prepares that data.

This workitem produces reusable views and stored procs so encapsulate this logic:

EnrolGrid Views

-- warehouse.EnrolGrid

-- warehouse.EnrolGridDistrict

-- warehouse.EnrolGridAuthority

-- warehouse.EnrolGridSchoolType

These 4 views denormalise warehouse.Enrol by gender and year of education. Year of education is derived from the class level. The column names are always the same, so they can be implemented as views.

GridMaker Stored Proc

To produce a grid that splits by class level in a general way - the class level names are defined in data, and may not always be in 1-1 correspondence to year of education - requires a stored proc

warehouse.GridMaker takes a number of parameters to control the aggregation , allow filtering, and select the data item to get aggregated:

-- exec warehouse.GridMaker                                 all years, totalled by school
-- exec warehouse.GridMaker 'D'                                         all years, totalled by district
-- exec warehouse.GridMaker 'A', 2014                                   year 2014, totalled by authority
-- exec warehouse.GridMaker 'T', 2015, 2017                             years 2015-17, totalled by school type
-- exec warehouse.GridMaker 'D', null, null, null, 'Rep'                all years, repeaters totalled by district
-- exec warehouse.GridMaker 'D', null, null, 'SchoolTypeCode=''PS'''    all years, totalled by district, filtered for Primary schools
-- =============================================
ALTER PROCEDURE [warehouse].[gridMaker]
    -- Add the parameters for the stored procedure here
    @groupBy nvarchar(30) = null        -- parameter to group by - use D for district, A for authority, T for school type otherwise School
    , @startYear int = null             -- if specified earliest year to report on
    , @endYear int = null               -- if not null, last year to report on; defaults to startYear if null
    , @filter nvarchar(max) = null      -- a where clause to execute against warehouse.enrol (EE) DimensionSchoolSurvey (DSS) before aggregating 
    , @dataItem nvarchar(20) = 'Enrol'  -- the field to accumulate - can be any of the data values on warehouse.Enrol
                                        -- Enrol, Rep, Trin, Trout, Boarders, Disab, PSA
...

@shraddha_tambe @ghachey I hope this may be useful in making reports a bit simpler and more reusable between sites?

[Brian Lewis]

cf Pull Request #307 for code

pushed to Miemis production and pineapples_develop

[Brian Lewis]

cf pull request #307; pull request #308 updates