This is the user guide to using the Pacific EMIS Data Warehouse for powerful and flexible data analysis. The data warehouse is a collection of SQL tables and views that the user can connect to using Excel or other Analysis or Statistics software and conduct further analysis on the data. It can be useful to to ad-hoc reporting, custom reports, advanced research all using a single source of truth of high quality data.

The tool the most commonly used by people is typically Excel. Here is how to connect to the data warehouse using Excel. Click on Data → From Other Sources as shown below

Enter the information to connect to your EMIS. The server address could be something like “fedemis.doe.fm” or “data.pss.edu.mh,14332” (if you are not on a default SQL Server port). Then enter the credentials. All EMISes should have a dbreadonly user provided to you by your data staff along with a password.

In the next step, make sure you select the correct database (e.g. fedemis, miemis, etc.) as there could be other databases on the server you are connecting to. You will see all views and tables of the database. Focus on the ones with owner “warehouse” as outlined below.

Similarly, the tables with owner “warehouse” more near the bottom of the list.

Let's select the EdLevelER table as shown below as example.

You then need to give the connection a name and feel free to save the password since this is a read only user. Then click on Finish.

Finally, decide how you want to import the data. The default is fine to learn. Then Click Ok.

It will then load the table into Excel for you to further analyse. The whole data table can be reference from the dropdown as outlined below in red and you can rename that to your liking which becomes handy if you start pulling yourself many tables and views.

There are many tables and views in the data warehouse. Some are more for advanced data analysts, database administrators and developers. Some or more for data managers, IT staff and even management staff. Two types of notes will be used throughout the tables and views documentation below.

The Pacific EMIS and therefore the data warehouse offers a very wide range of possible disaggregation. In particular there is one group of disaggregation they will repeatedly come back. Any data that is available by school will also automatically be available to aggregate by all the following:

• Local Electorate (Ward in RMI, Region/District/Electoral Division/Zone in FSM, )
• National Electorate (Constituency in RMI, State in FSM,
• District (Atoll / Island in RMI, State in FSM, Province in Solomon Islands)
• Island (Island/Municipality in FSM)
• Region
• Authority
• Authority Type
• Authority Group (School Type in RMI/FSM)
• School Type (School Level in RMI/FSM)

So if you are in RMI and want to do an aggregation by what you know as Atoll and Island you would use the data in the District column and aggregate on that. It can be renamed in your own excel or word report and is already renamed for you in the web app and PDF reports.

When working with data it is hard to avoid technical terminologies completely. Even as a data analysis it will be important to learn some database terminology.

One such important concept is “normalized” vs “denormalized”. Let's explain this in a simple way as these terms are used throughout the following sections while explaining the data in the data warehouse.

Let's use the following example to demonstrate this. We can say that this data is normalized by Gender since there is a Gender column and the gender data itself is also in the rows of data.

But the same data could be expressed in another way, by having the data denormalized by Gender. In this following case we have less rows of data and the Male vs Female is expressed with two new columns.

In what way the data is expressed often depends on what the user wants to do with it and with what tools. Typically, analysis tools such as Excel pivot tables you would be better off to have it normalized giving you more flexibility in your analysis. You will find data in both format in the warehouse giving advanced users more choice and flexibility.

This section provides first minimum of technical background and then detailed information about the data warehouse. Each tables and views are discussed (i.e. what data can be found inside), provides some useful tips on how it could be used and often provides a sample excel workbook with example analysis.

The following subsection are organized by type of data supported by the EMIS (i.e. Enrolments, Flows, Special Education, School Accreditation, etc.) and not in the order in which they come in the warehouse itself.

The following tables and views provides enrolments data in various aggregations.

A view that provides enrollments, repeaters, transfers in, transfers out, boarders, students with disability, dropouts, pre-school attenders totals and all the available schools disaggregation, Class Level, Age and Gender.

A sample workbook how this table can be pulled in excel and analysis done in the data is included below.

enrolments-by-school.xlsx

This is similar to the data above in that it contains enrollment data. However, the data is packaged with different aggregations targeted at computing some of the most common key indicators (i.e. NER, GER). In this table we have enrollments, repeaters, intakes and population with the ability to disaggregate by gender and education levels (i.e. ECE, Primary, Secondary).

The workbook below shows some sample analysis. This workbook does a bit more than simple pivot tables and charts on the data but also computes new data using formulas.

education-level-enrolment-ratios.xlsx

This table is similar to the warehouse.EdLevelER above but instead of prodiving disaggregation by education levels it can disaggregate by class levels (i.e. Grade 1, Grade 2, etc.) which can then be used to produce some other key indicators (i.e. NIR, GIR)

class-level-enrolment-ratios.xlsx

Lowest level table in the warehouse holding disability data down to the school level by class level and gender.

Similar to warehouse.Disability, this table contains disability information aggregated by Gender, Class Level, District, Authority, School Type.

This is the foundation for special education student data. It can be used directly and supports the highest flexibility and disaggregation power.

Similar to table.enrolSpEd with special education data (i.e. Environment, Disability, EnglishLearner, Ethnicity) but it does not disaggregate down to the School level, only by

• Education Level
• Gender
• Age
• Authority
• District
• AuthorityGovt
• SchoolType
• Region

Use this view for higher level statistics about special education data across any of the above. For example of how to use this warehouse view refer to the same file attached in warehouse.enrolSpEd

The following analysis workbook makes use of all the above tables and views and provides some examples of analysis that can be done with the data.

special-education-analysis.xlsx

Many views are essentially the same but offering different aggregations. For example,

• warehouse.AccreditationAuthority
• warehouse.AccreditationAuthorityGovt
• warehouse.AccreditationAuthorityGovt
• warehouse.AccreditationDistrict
• warehouse.AccreditationNation
• warehouse.AccreditationSchool

All offer the same data but doing the analysis by authority, authority government (public/private), district (Atolls, State, Provinces) and down by individual schools. The data enables analysis at a highest level (i.e. the accreditation status (i.e. Level 1, Level 2, Level 3, Level 4). The example workbook below does some analysis by nation, district (Atolls, State, Provinces) and historical.

school-accreditation-analysis.xlsx

All the views starting with “AccreditationByStandard” are essentially the same but offering different aggregations. For example,

• warehouse.AccreditationByStandardAuthority
• warehouse.AccreditationByStandardAuthorityGovt
• warehouse.AccreditationByStandardDistrict
• warehouse.AccreditationByStandardNation
• warehouse.AccreditationByStandardSchool
• warehouse.AccreditationByStandardTable

All offer the same data but doing the analysis by authority, authority government (public/private), district (Atolls, State, Provinces) and down by individual schools. The data enables analysis at the standard level (i.e. the number of schools that achieve a specific level (i.e. Level 1, Level 2, Level 3, Level 4) for each of the individual school evaluation standards and classroom observations. The example workbook below does some analysis by nation and district (Atolls, State, Provinces).

school-accreditation-standard-performance-analysis.xlsx

This view is designed to provide a familiar data format for FSM/RMI. It is similar to the table warehouse.Accreditations but already links with bestSurvey and dimensionSchoolSurvey to provide users everything in a single “table”. With this view users can pull all data for school accreditation like it was in the classic school accreditation analysis spreadsheet, but with the added benefit of more consistent data from the EMIS, historical analysis (not before possible), and more powerful aggregations.

school-accreditations-classic-analysis.xlsx

This view is designed for pivot table use to provide maximum analysis flexibility. It supports:

• disaggregation by school or key school attributes
• calculation of percentages and 'nominal' Level at standard and criteria level
• analysis of results by sub-criteria
• disaggregation by overall inspection result
• time series

TODO sample spreadsheet

This section provides details on the remaining tables and views and only recommended reading for the most advanced data analysts and even software developers contributing to this project. The following sub-sections are organized like the previous section but contain the remaining tables most data managers will not require to use directly.

Lowest level table in warehouse holding enrolment related data. This covers the “enrolment” data collection in survey forms:

• enrol
• repeaters
• transfers in
• transfers out
• boarders
• disability (aggregated)
• dropouts
• preschool attenders (aggregated)

The data is normalized by Gender, separates Estimates and Actuals. The table warehouse.tableEnrol aggregates this data up to District, Authority, SchoolTypeCode and is used as the basis for higher level aggregations of any of these enrolment data items.

Same of warehouse.enrol except this table aggregates this data up to District, Authority, SchoolTypeCode and is used as the basis for higher level aggregations of any of these enrolment data items.

This is one of the core tables to make up the Data Warehouse's School Accreditation data.

by inspID and rank, shows those criteria scoring in that rank. e.g. the data looks like

ie: the count column is 1 when the corresponding criteria score equals the Rank of that record. the score column is the criteria score when that score equals the Rank of that record

by inspId and Standard, the calculated Result for that standard.

This is one of the core tables to make up the Data Warehouse's School Accreditation data.

This is the denormalized view of all the school accreditations by criteria similar to how the excel spreadsheets was organized in the legacy School Accreditation excel workbook analysis tool before the EMIS was used to manage the data.

Consolidates each standard by result level.

Consolidates performance data

Consolidates the overall result of inspections. Filter on InspectionType to restrict to accreditations

Some tables are “special” in the sense that they don't hold the core data about something but they hold some other important information such as the best possible survey available for each school, the best latest (or best) school inspection. They provide a number of powerful tools to “complete” your data and make it of higher quality. Those tables will be discussed here.

This table shows, for each survey year, school and inspection type the most recent matching inspection up to that year. This allows aggregations across inspections without duplicating a school. Also makes possible cumulative time-lines on inspections which normally would not necessarily happen every year for every school.

Aggregated tables, then views are built on these foundations. Using SurveyDimensionId from warehouse.BestSurvey allows us to retrieve aggregation dimensions (i.e. District, Authority, etc.) for each school. Use warehouse.BestInspection to ensure that a school represented nomore than once in any aggregation.

There are many views that end with the letter “R”. Those are the same as their counterpart view/table but denormalized also by gender (or some other useful denormalization.) For example, let's look at the view warehouse.EnrolAuthority. It looks like the following. The gender data is normalized, meaning it is in the rows (and not in the columns).

There is another counterpart view that is essentially the same data. The view is called warehouse.EnrolAuthorityR (same as above but with added “R” at the end. It looks like the following image. Note how the gender is now in the column instead of in the rows.