Attachment L - NDECORETechnicalOverview_NCES.pdf

NDE CORE Data Explorer

Technical Overview

Platform Description:

The NAEP CORE Data Explorer is a web based statistical reporting application designed for NCES and the Department of Education for use in reporting results for sample-based survey assessments. The architecture consists of three tiers. The application operates on Microsoft IIS web servers, and was developed utilizing the Microsoft suite of application tools such as SQL Server, C# dot net, and Microsoft Visual Studio.

The data explorer is designed to produce tabular output, and graphical output in the form of charts and maps. Results can be viewed on screen in the user’s web browser, or exported to HTML, Word, Excel, or PDF.

FRONT END:

- A dot net application written as a SPA web application – single page app.

- Navigation between states in the SPA app are requested via routes in the url.

- The application is tested to run on Edge, Chrome, Safari, or FireFox.

- The programming tools used are:

o C# o Dot Net Framework 4.x .

o Javascript o CSS o HTML o Telerik Kendo controls o AngularJS with some updates to Angular 9 and 15 o SVG graphics + D3 graphics library o Visual Studio 2022 Pro o Aspose 3rd party DLL for Excel, Word, and PDF export capability.

- The front end receives JSON responses from a lower level middle tier which for this application is linked in, i.e., not data service calls to an HTTP end point.

- The front end supports the generation of charts (line, block, bar charts, and specialized charts for percentiles and achievement level style data), and maps for both the US and international markets. Charting and map support is available both inside the CORE data explorer, and externally via routed web URLS so that a chart or map could be contained inside another tool.

- The front end for the CORE data explorer supports internationalization (i.e., other languages), along with the other tiers acting in concert down to the data store.

- The front end also supports devices such as phones, tablets, and PCs alike by adjustable CSS to suit the target platform. The application is said to be “responsive.”

MIDDLE TIER

- A dot net application written as a compiled DLL using Dot Net Framework 4.x

- The programming tools are:

o C# o Microsoft SQL Client namespace o SQL Server Database using SQL 2012 or newer. The middle tier has been tested against SQL Server 2008, 2012, 2016, 2017, and 2019.

- The middle tier receives requests from the front end via API entry points and responds with JSON.

- The middle tier accesses the database for metadata which drives the interface (front end).

- The middle tier calls the calculation engine to generate results.

- The middle tier also provides all text for the front end. No string constants are compiled into the front-end application. In this way we assure that the data explorer is as data driven as possible.

- The middle tier is bound to the application as a DLL and is hosted under IIS.

COMPUTATION ENGINE

- Written in C#

- The computation engine is implemented as a DLL. It is not meant to be called directly, but instead communicates its results to the middle tier via API entry points.

- The computation engine generates dynamic SQL queries based on user input. At its heart, the data explorer is an ad hoc query generator.

- Ad hoc SQL queries read data tables of raw student cases on the SQL server itself and generate intermediate summed data tables. In this way student data is not read into web server memory case by case. Results are pre-computed into a smaller set to reduce load on the channel between the database and the computation engine.

- Once intermediate summed results are read into memory, final computation takes place and the results are assembled into an aggregate form for return to the caller.

- Results are computed for Percentile, Means, Row Percents, Achievement Levels, or standard deviations. All result statistics also get confidence intervals, standard errors, weighted N, Degrees of Freedom, and cell N computed.

- Result statistics not previously requested are stored in Caching data tables to be reused by future requests for the same data. The result cache is a 4-level operation:

o Requests for multiple years, jurisdictions, and statistics are broken up into smaller units and computed individually. This is the lowest level cache and is on disk in the database.

o Aggregate requests across years, jurisdictions and statistics are assembled into larger result sets and stored in a 2nd level cache on disk in the database.

o When the front end requests any metadata, the request is hashed, and the resulting JSON stored in a JSON cache on disk in the database for the front end. This is the 3rd level cache.

o Finally for the fastest performance and highest demand JSON results, the hashed results are stored in memory at the web server for the highest-level performance. This is the 4th level.

- In certain cases (like percentiles), a new query for a result not previously cached results in case data being read to the computation engine.

- The computation engine is a linked DLL which executes under the IIS process thread that invoked it.

Theory of Operation in Computing Results

The NDE CORE data explorer is coupled to the parameters of NAEP data. Initial data selection is made from a set of high-level selectors called dimensions. For NAEP these dimensions are:

- Subject

- Grade

- Year

- Sample

- Framework

- Dependent (continuous) variable

- Jurisdiction

- Independent variable

- Statistic

The purpose of the dimensions are to separate segments of the data which may not be appropriate to report together, such as subject and grade. Or to separate data which should not be trend tested together – such as when the structure of a test changes. In NAEP this break in trend manifests itself on trend charts as multiple data series on the same chart. Finally, dimensions such as jurisdiction and year allow the computation engine to break up the work in sub segments to produce smaller chunks of work product that might be reused by another user in a subsequent request.

For NAEP assessments, the input data is based on a sampling approach – i.e., not all students take all parts of the NAEP Assessment, so each computed score is an estimate. Therefore, the computation engine’s primary task is to compute scores based on sampled data, and then generate standard errors and degrees of freedom that demonstrate the sampled nature of the set.

The computation engine consists of 5 parts:

- The raw statistic computation: mean, percent, achievement level, percentile, or standard deviation.

- The significance test engine for testing trend between years, significance between jurisdictions, and significance between variables

- The gap test engine tests 2-part trend tests, such as trends between jurisdictions over time.

- Intermediate and aggregate result caching (caching tiers 1 and 2).

- A Regression analysis engine

The computation engine operates on the concept of a single central data store. Independent variables that might be reported for a student or teacher or school, and which exist in separate disparate data stores, can be coded as variable data, tied to student, teacher or school data sets with a key, and then reported.

Accessory Entry Points

In addition to the data explorer web front end, the NAEP Data Explorer makes access available to the middle tier and computation layers via a data service API, which is an HTTP end point service delivered in conjunction with the data explorer.

The data service is used by third party developers within the NAEP contract sphere to request computed results for charts and tables that support NAEP projects.

Requests are made using a query string style HTTP request, and the response is given as a series of data points (with significance output where requested), in the form of JSON.

Database Organization

The database for the data explorer is mostly a read-only environment, (with the exception of 3 caching tables to improve performance for repeat requests of ad-hoc data.)

The database itself consists of a series of tables which can be roughly grouped as follows:

- Dimensional data: variables, jurisdictions, subjects, frameworks, years and samples and statistics.

- Label data: Languages supported and LanguageText, Controls, and pages.

- Caching operations

- The data store: Cycles and student data tables, plus supporting data tables for achievement level scoring.

- Variable definitions and hierarchy: Dependents, Independents, GroupVar, GroupDef, Variables, and Values (the values each variable can take on)

- Misc. tables to support computation, and configurable settings for the data explorer stored in the NDESettings table.

The database structure is relatively flat, and not heavily dependent on OLTP operations supported in a relational database. The key structure for student data is a simple definition of a few columns – much like flat files or earlier forms of flat database models such as VSAM in the mainframe world. The data structures for student data support a key and blob model.

NDE Core Deployment Diagram

UI

<<HTTP>>

Client

Server

UI

<<HTTP>>

Client

Server

UI

<<HTTP>>

Client

Server

App Data

<<ADO.NET>>

Read/Write

Host

PC

Production Testing Stage

Unnamed Instance Unnamed Instance Unnamed Instance

Internet Client Zone Client Zone

Web Browser Google Chrome Microsoft Edge Mozilla Firefox

Tablet or Phone Production Testing Unnamed Instance

Unnamed Instance Internet Internet

Web Browser <<3rd Party Software>> Apple Safari Google Chrome

NCES Load Balancer

NCES Web Server Development Production Production

Host Server NCES Web Server A NCES Web Server B

Lab Zone Microsoft SQL Server 2017 & up

<<3rd Party Software>> C#

<<3rd Party Software>> Microsoft .NET Framework 4.x

<<3rd Party Software>> jQuery 3.x

<<3rd Party Software>> Telerik UI for ASP.NET AJAX

<<3rd Party Software>> Microsoft Windows Server 2019+

<<3rd Party Software>>

Microsoft Internet Information Services

NDE Web Application <<Developed Software>>

NDE Database Server Development Host Server Lab Zone

Microsoft SQL Server 2017 and up

NDE Core Application Flow Diagram