Introduction

The Analytics Middle Tier source code contains extensive unit testing of the views, utilizing a custom framework for loading and refreshing test data on all supported Data Standards against a SQL Server database and a Postgres database. These unit tests serve the dual purposes of exploring the required data during development and providing regression tests for safely refactoring views and correcting bugs.

All views created by the core Ed-Fi Analytics development team have a complete set of unit tests that cover:

• Output columns
• Where clause conditions
• Join clause conditions

The development team encourages community contributors to develop similar unit tests for their views. The following sections provide a walkthrough of how to add unit tests for a simple use case, as well as detailed information about the framework architecture for those who would like to know more.

With the recent addition of having test cases execute against both SQL Server and Postgres, after downloading the Analytics Middle Tier you should review the Analytics Middle Tier Test project README and  this page in Tech Docs that mentions the additional setup and caveats to testing against Postgres in addition to SQL Server.

Adding a State Education Agency Dimension

As a concrete example, let's introduce a dimensional view that provides the names of all state education agencies in the Ed-Fi ODS database. The query is quite simple, and works on all supported Ed-Fi Data Standards:

select
    EducationOrganization.EducationOrganizationId as StateEducationAgencyKey,
    EducationOrganization.NameOfInstitution as StateEducationAgencyName
from
    edfi.StateEducationAgency
inner join
    edfi.EducationOrganization
on
    StateEducationAgency.StateEducationAgencyId = EducationOrganization.EducationOrganizationId

Test Case Queries

Identifying Test Cases

In addition to the obvious "happy path" test case where the view returns a state education agency, there is one negative test case: ensure that an Education Organization that is not a State Education Agency is not returned by the view. What about going in reverse? A State Education Agency is defined as an instance of an Education Organization in the Ed-Fi Data Model; therefore, there cannot be a State Education Agency record without an Education Organization record. The reverse case is unnecessary.

Following the unit test principle of asserting one thing at a time, let us split the positive case in two: one test to ensure that we get the expected Key value and a second test for the Name value. Finally, there should be a test of the overall structure, giving us four total tests per Data Standard:

1. Validate the columns and data types (structure).
2. Validate the StateEducationAgencyKey for a state education agency.
3. Validate the StateEducationAgencyName for a state education agency.
4. Ensure that an EducationOrganization that is not a StateEducationAgency does not appear in the results.

Adding the Test Cases to the Project

In your IDE, expand the EdFi.AnalyticsMIddleTier.Tests  project and add a folder called StateEducationAgencyDim  under TestCases. In here, create configuration files with the test cases. These can either be XML or JSON files (JSON files are more compact, while XML tend to be easier to read because they support line breaks in the query definition).

Let us define EducationOrganizationId = 123456 as the valid state agency with name "Sample" and 654321 as an organization that is not a state agency. Below are the file definitions for each of the test cases. Note the file naming convention, which clearly expresses the intent of the test. The next section will show insertion of matching sample data.

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
    <DBMS>Any</DBMS>
    <ControlDataInsertion>
    </ControlDataInsertion>
    <Query>
        SELECT StateEducationAgencyKey
        FROM analytics.StateEducationAgencyDim
        WHERE StateEducationAgencyKey = 123456;
    </Query>
    <Result>
        <StateEducationAgencyKey>123456</StateEducationAgencyKey>
    </Result>
</TestCase>

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
    <DBMS>Any</DBMS>
    <ControlDataInsertion>
    </ControlDataInsertion>
    <Query>
        SELECT StateEducationAgencyName
        FROM analytics.StateEducationAgencyDim
        WHERE StateEducationAgencyKey = 123456;
    </Query>
    <Result>
        <StateEducationAgencyName>Sample</StateEducationAgencyName>
    </Result>
</TestCase>

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
    <DBMS>Any</DBMS>
    <ControlDataInsertion>
    </ControlDataInsertion>
    <Query>
        SELECT COUNT(1) AS CountValue FROM analytics.StateEducationAgencyDim WHERE StateEducationAgencyKey = 654321;
    </Query>
    <Result>
        <CountValue>0</CountValue>
    </Result>
</TestCase>

Sample Data Insertion

The test framework loads a clean database, with no records, for each test execution. Therefore, sample data need to be added, again using embedded configuration files. Because the supported Ed-Fi Data Standards can represent the same information in different ways, test data are created for each data standard version, although the insert scripts may end up being identical.

For each supported Data Standard version, create two files with the following naming convention:

1. 0000_NAMEOFTHEVIEW_Data_Load.xml 
2. 0001_NAMEOFTHEVIEW_should_match_column_dictionary.xml 

The first file will have INSERT statements for all data required for the test cases, and the second one defines the expected output column names and types. In this trivial case, the scripts are identical for Data Standards v3.1 and v3.2a, but slightly different for version 2.2. The output structure, however, will always be the same for all supported Data Standards.

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
  <DBMS>Any</DBMS>
  <ControlDataInsertion>
      insert into edfi.EducationOrganization (
          EducationOrganizationId,
          NameOfInstitution
      )
      values (
          123456,
          'Sample'
      ), (
          654321,
          'Not a state agency'
      );
      insert into edfi.StateEducationAgency (
        StateEducationAgencyId
      )
      values (
        123456
      )
  </ControlDataInsertion>
</TestCase> 

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
  <DBMS>Any</DBMS>
  <ControlDataInsertion>
		insert into edfi.EducationOrganization (
		    EducationOrganizationId,
		    NameOfInstitution,
		    StateOrganizationId
		)
		values (
		    123456,
		    'Sample',
		    'Value is required but is irrelevant to this test'
		), (
		    654321,
		    'Not a state agency',
		    'asdfghjk'
		);
		insert into edfi.StateEducationAgency (
    		StateEducationAgencyId
		)
		values (
    		123456
		)
  </ControlDataInsertion>
</TestCase> 

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
    <DBMS>Any</DBMS>
    <ControlDataInsertion>
    </ControlDataInsertion>
    <Query>
        SELECT COLUMN_NAME AS ColumnName,
        DATA_TYPE AS DataType
        FROM information_schema.columns
        WHERE table_schema = 'analytics'
        AND table_name = 'StateEducationAgencyDim'
        ORDER BY ORDINAL_POSITION ASC;
    </Query>
    <Result>
        <ColumnName>StateEducationAgencyKey</ColumnName>
        <DataType>nvarchar</DataType>
    </Result>
    <Result>
        <ColumnName>StateEducationAgencyName</ColumnName>
        <DataType>nvarchar</DataType>
    </Result>
</TestCase>

Here are the new XML files we've added, as seen from within Visual Studio 2019:

C# Object

Next, we must create a "Plain Old C# Object" (POCO) in order to map the query results into the C#-based unit test framework. Create a file named StateEducationAgencyDim.cs under Classes and add properties for each expected column.

namespace EdFi.AnalyticsMiddleTier.Tests.Classes
{
    public class StateEducationAgencyDim
    {
        public string StateEducationAgencyKey { get; set;  }
        public string StateEducationAgencyName { get; set;  }
    }
}

Write the Test Cases in C#

The final step is to write the C# test cases, utilizing the test case framework to load the sample data, execute the test, and verify the results. The framework is built on NUnit3 and the Shouldly assertion library.

Create file When_querying_the_StateEducationAgencyDim_view.cs  under Dimensions.

The class must be abstract, must inherit from When_querying_a_view, and needs to include some standard boilerplate code as shown below:

using EdFi.AnalyticsMiddleTier.Tests.Classes;
using NUnit.Framework;
using Shouldly;

namespace EdFi.AnalyticsMiddleTier.Tests.Dimensions
{
    public abstract class When_querying_the_StateEducationAgencyDim_view : When_querying_a_view
    {
        protected const string TestCasesFolder = "TestCases.StateEducationAgencyDim";

        protected (bool success, string errorMessage) Result;

        [OneTimeSetUp]
        public void PrepareDatabase()
        {
            DataStandard.PrepareDatabase();
        }

        [OneTimeSetUp]
        public void Act()
        {
            Result = DataStandard.LoadTestCaseData<LocalEducationAgencyDim>($"{TestCasesFolder}.{DataStandard.DataStandardFolderName}.0000_StateEducationAgencyDim_Data_Load.xml");
            Result.success.ShouldBeTrue($"Error while loading data: '{Result.errorMessage}'");

            Result = DataStandard.Install();
            Result.success.ShouldBeTrue($"Error while installing Base: '{Result.errorMessage}'");
        }
    }
}

Next add the test case for the structure:

[Test]
public void Then_view_should_match_column_dictionary()
{
    (bool success, string errorMessage) testResult = DataStandard.RunTestCase<TableColumns>($"{TestCasesFolder}.{DataStandard.DataStandardFolderName}.0001_StateEducationAgency_should_match_column_dictionary.xml");
    testResult.success.ShouldBe(true, testResult.errorMessage);
}

The two happy-path cases should be grouped together in a class that inherits from the new class. To understand this organizational pattern, simply read the class and method signatures out loud:

1. Given education organization 123456
2. When querying the StateEducationAgencyDim
3. Then (it) should have State Agency Key (actual value in the config file)

public class Given_education_organization_123456 : When_querying_the_StateEducationAgencyDim_view
{
    private string _caseIdentifier = "123456";

    public Given_education_organization_123456(TestHarness dataStandard) => SetDataStandard(dataStandard);

    [Test]
    public void Then_should_have_StateEducationAgencyKey()
    {
        (bool success, string errorMessage) testResult = DataStandard.RunTestCase<StateEducationAgencyDim>($"{TestCasesFolder}.{_caseIdentifier}_should_have_StateEducationAgencyKey.xml");
        testResult.success.ShouldBe(true, testResult.errorMessage);
    }

    [Test]
    public void Then_should_have_StateEducationAgencyName()
    {
        (bool success, string errorMessage) testResult = DataStandard.RunTestCase<StateEducationAgencyDim>($"{TestCasesFolder}.{_caseIdentifier}_should_have_StateEducationAgencyName.xml");
        testResult.success.ShouldBe(true, testResult.errorMessage);
    }
}

And now, a class for the negative test case:

public class Given_education_organization_654321 : When_querying_the_StateEducationAgencyDim_view
{
    private string _caseIdentifier = "654321";

    public Given_education_organization_654321(TestHarness dataStandard) => SetDataStandard(dataStandard);

    [Test]
    public void Then_should_return_zero_records()
    {
        (bool success, string errorMessage) testResult = DataStandard.RunTestCase<CountResult>($"{TestCasesFolder}.{_caseIdentifier}_should_not_be_a_StateEducationAgency.xml");
        testResult.success.ShouldBe(true, testResult.errorMessage);
    }
}

Compile and Run the Tests

At this point, we can run the tests to confirm that the view works as intended. Results from Visual Studio 2019: nine tests passed and five failed.

Digging into one of the failures reveals the following message:

 Then_view_should_match_column_dictionary
   Source: When_querying_the_StateEducationAgencyDim_view.cs line 30
   Duration: 120 ms

  Message: 
    Shouldly.ShouldAssertException : success
        should be
    True
        but was
    False
    
    Additional Info:
        The expected data set does not match the one received from the database.
    Xml Diff Result:
    <xd:xmldiff version="1.0" srcDocHash="13467357853539592381" options="IgnoreChildOrder IgnoreComments IgnoreNamespaces IgnorePrefixes IgnoreWhitespace " fragments="no" xmlns:xd="http://schemas.microsoft.com/xmltools/2002/xmldiff">
      <xd:node match="1">
        <xd:node match="1">
          <xd:node match="2">
            <xd:change match="1">int</xd:change>
          </xd:node>
        </xd:node>
      </xd:node>
    </xd:xmldiff>
  Stack Trace: 
    When_querying_the_StateEducationAgencyDim_view.Then_view_should_match_column_dictionary() line 33

Admittedly, this is a little obscure. When you look deep into the XML diff result, you see <xd:change match="1">int</xd:change> , indicating column 1 is an integer and something else was expected. 

Up above in file 0001_StateEducationAgency_should_match_column_dictionary.xml, the StateLocalEducationAgencyKey column is assumed to be a string — whereas the view is returning the EducationOrganizationId, which is an integer. Which is correct?

Because this column represents a relational label that should not be used in any arithmetic calculations, treating the integer as a string is appropriate. Therefore the tests are right: they've found a bug! Change the query to cast the EducationOrganizationId  to nvarchar and re-run the tests. Outcome: they all pass! Problem solved, bug found and resolved. 

Test Framework Architecture

The following table lists the main components, which are described in detail in the following sections.

Entity View C# Classes

The test framework uses Dapper to query the views and map their results into an appropriate entity view object. Each view under test requires a corresponding Plain Old C# Object definition (POCO). Two additional classes, CountResult and TableColumns, provide cross-cutting support for test cases.

If a numeric column can be null then you must to set the data type as nullable. In the following example, NumericGradeEarned is nullable in the view result, and therefore the C# type is decimal? .

public class StudentSectionGradeFact
{
     public int StudentKey { get; set; } //(int, not null)
     public int SchoolKey { get; set; } //(int, not null)
     public string GradingPeriodKey { get; set; } //(nvarchar(92), null)
     public string StudentSectionKey { get; set; } //(nvarchar(544), null)
     public string SectionKey { get; set; } //(nvarchar(482), null)
     public decimal? NumericGradeEarned { get; set; } //(decimal(9,2), null)
     public string LetterGradeEarned { get; set; } //(nvarchar(20), null)
}

Some examples of the use of classes:

(bool success, string errorMessage) testResult 
	= _dataStandard.RunTestCase<TableColumns>($"{TestCasesFolder}.0001_ContactPersonDimension_should_match_column_dictionary.xml");

(bool success, string errorMessage) testResult 
	= _dataStandard.RunTestCase<CountResult>($"{TestCasesFolder}.{_caseIdentifier}_should_return_one_record.xml");

(bool success, string errorMessage) testResult 
	= _dataStandard.RunTestCase<DateDimension>($"{TestCasesFolder}.{_caseIdentifier}_calendarQuarter_should_be_1.xml");

Test Case Files

The unit test framework uses a configuration file structure to define both sample data as well as queries and their expected results. This file can be written in XML or JSON. While JSON is more compact, the core development team generally uses XML for better readability. The file can contain these data elements:

If you are unsure of how to format the expected Result for the Query, you can try running the Query command manually in SQL Server Management Studio with either FOR XML  or FOR JSON  appended to the query, as shown below:

<Result>
	<CalendarQuarter>3</CalendarQuarter>
</Result>

"Result":[{"CalendarQuarter":3}]

Sample test data files:

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
  <DBMS>Any</DBMS>
  <ControlDataInsertion>
  </ControlDataInsertion>
  <Query>
    SELECT CalendarQuarter 
    FROM analytics.DateDimension 
    WHERE DateKey=20190401;
  </Query>
  <Result>
    <CalendarQuarter>2</CalendarQuarter>
  </Result>
</TestCase>

{
  "DBMS": "Any",
  "ControlDataInsertion": "",
  "DropControlData": "",
  "Query": "SELECT CalendarQuarter \n FROM analytics.DateDimension \nWHERE DateKey=20190401;",
  "Result": [
    {
      "CalendarQuarter": "2"
    }
  ]
}

Test Classes

Test classes contain an abstract class for each view, and concrete classes for each scenario. Using the concept of "Given... When... Then" found in Behavior Driven Development, each "Given" (input condition) should have its own concrete class inheriting from the abstract class. The abstract class name contains the "When" clause. This inverts the order to "When... Given", which simplifies the structure of the tests. Finally, each Test should contain one assertion and its name will be something like "Then_should_xzy".

Abstract class

In this class we define common methods and tests for all test cases. There is a set of basic methods to create:

To run test cases, the summary uses two XML / JSON files in Act and Then_view_should_match_column_dictionary.

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
  <DBMS>Any</DBMS>
  <ControlDataInsertion>
    SET IDENTITY_INSERT edfi.schooltype ON;
    INSERT INTO edfi.schooltype (SchoolTypeId, CodeValue, Description, ShortDescription, Id, LastModifiedDate, CreateDate ) 
		( SELECT TOP 1 '2', 'Regular', 'Regular', 'Regular', 'FA037C41-5BAE-4077-B48D-3C2308185F37', 
			'Jun 19 2015 11:41AM', 'Jun 19 2015 11:41AM' WHERE NOT EXISTS ( SELECT 1 FROM edfi.schooltype WHERE schooltypeId = 2 ) );
    SET IDENTITY_INSERT edfi.schooltype OFF;
  
	INSERT INTO edfi.CalendarEventDescriptor (CalendarEventDescriptorId, CalendarEventTypeId ) 
		( SELECT TOP 1 '686', '1' WHERE NOT EXISTS ( SELECT 1 FROM edfi.CalendarEventDescriptor 
		  WHERE CalendarEventDescriptorId = 686 AND CalendarEventTypeId = 1 ) );
    ...
  </ControlDataInsertion>
</TestCase>

SELECT [COLUMN_NAME] AS [ColumnName], 
       [DATA_TYPE] AS [DataType]
FROM [information_schema].[columns]
WHERE [table_schema] = '<Schema>'
      AND [table_name] = '<View_Name>'
ORDER BY [ORDINAL_POSITION] ASC;

SELECT [COLUMN_NAME] AS [ColumnName], 
       [DATA_TYPE] AS [DataType]
FROM [information_schema].[columns]
WHERE [table_schema] = '<Schema>'
      AND [table_name] = '<View_Name>'
ORDER BY [ORDINAL_POSITION] ASC
FOR XML PATH('Result');

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
	<DBMS>Any</DBMS>
	<ControlDataInsertion>
	</ControlDataInsertion>
	<Query>
    	SELECT COLUMN_NAME AS ColumnName,
    			DATA_TYPE AS DataType
    	FROM information_schema.columns
    	WHERE table_schema = 'analytics'
    		AND table_name = 'DateDimension'
    	ORDER BY ORDINAL_POSITION ASC;
	</Query>
	<Result>
		<ColumnName>DateKey</ColumnName>
		<DataType>varchar</DataType>
	</Result>
	<Result>
		<ColumnName>Date</ColumnName>
		<DataType>datetime</DataType>
	</Result>
	<Result>
		<ColumnName>Day</ColumnName>
		<DataType>int</DataType>
	</Result>
	<Result>
		<ColumnName>Month</ColumnName>
		<DataType>int</DataType>
	</Result>
	<Result>
		<ColumnName>MonthName</ColumnName>
		<DataType>nvarchar</DataType>
	</Result>
	<Result>
		<ColumnName>CalendarQuarter</ColumnName>
		<DataType>int</DataType>
	</Result>
	<Result>
		<ColumnName>CalendarQuarterName</ColumnName>
		<DataType>varchar</DataType>
	</Result>
	<Result>
		<ColumnName>CalendarYear</ColumnName>
		<DataType>int</DataType>
	</Result>
</TestCase>

Test Fixture Class

To test a view, you must consider a set of cases and rows to prove that everyone passes the test, and, most importantly, to prove that the test fails if something is different in a view. In some cases, this requires more than one test class to cover test cases of success and exception. The first step is to identify possible points of error on a view query if the original script was changed or does not correspond with the current version.

You must populate data with at least one row that works correctly. Then, you need to add data rows that should not be returned. If one of those rows are returned, it is because some condition is failing or is missing. That is our control data.

When developing tests, pay attention to points that could change the view behavior. Some examples of conditions that do not break the view, but could change the result if they are changed:

• IsNull replacement: if a column uses an ISNULL function call (or COALESCE) to handle null values and replace it with a default value. We can validate if the value is correctly changed. For that, you need a row that has null that column.
• Join conditions: some joins can use a list of conditions with and. If you comment out a condition, the view, in some cases would be failed. You need enough data to check to join conditions combinations.
• Where filter: if the view uses a where you need data to simulate that view return data if you comment out or remove that filter.

It is recommended to write at least one test case for each of the columns in the table. Additionally, each test case must verify only one item (e.g., row, column) at a time. The framework allows you to compare queries with multiple rows and columns, but for control purposes, it is recommended to keep it restricted in the test cases.

Below are some examples of test cases:

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
  <DBMS>Any</DBMS>
  <ControlDataInsertion>
  </ControlDataInsertion>
  <Query>
    SELECT count(1) as CountValue
	FROM analytics.DateDimension
	WHERE DateKey=20190401;
  </Query>
  <Result>
    <CountValue>1</CountValue>
  </Result>
</TestCase>

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
  <DBMS>Any</DBMS>
  <ControlDataInsertion>
  </ControlDataInsertion>
  <Query>
    SELECT COUNT(1) AS CountValue
    FROM analytics.UserStudentDataAuthorization
    WHERE UserKey='11324' AND StudentKey='218269';
  </Query>
  <Result>
    <CountValue>0</CountValue>
  </Result>
</TestCase>

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
  <DBMS>Any</DBMS>
  <ControlDataInsertion>
  </ControlDataInsertion>
  <Query>
    SELECT LocalEducationAgencyCharterStatus
    FROM analytics.LocalEducationAgencyDimension
    WHERE LocalEducationAgencyKey = 528530;
  </Query>
  <Result>
    <LocalEducationAgencyCharterStatus></LocalEducationAgencyCharterStatus>
  </Result>
</TestCase>

<?xml version="1.0" encoding="utf-8" ?>
<TestCase>
  <DBMS>Any</DBMS>
  <ControlDataInsertion>
  </ControlDataInsertion>
  <Query>
    SELECT CalendarQuarter
    FROM analytics.DateDimension
    WHERE DateKey=20190401;
  </Query>
  <Result>
    <CalendarQuarter>2</CalendarQuarter>
  </Result>
</TestCase>

So, a Test Fixture class will look like:

[TestFixture]
public class Given_<test_scenario>
    : When_querying_the_<base abstract class>_view
{
    protected override TestHarness _dataStandard => TestHarness.DataStandardX;
    private string _caseIdentifier = "<test case prefix>";
    
    [Test]
    public void Then_should_return_one_record()
    {
        (bool success, string errorMessage) testResult =
           _dataStandard.RunTestCase<CountResult>($"{TestCasesFolder}.{_caseIdentifier}_should_return_one_record.xml");
		//For this case, test result should be true.
        testResult.success.ShouldBe(true, testResult.errorMessage);
    }
...
}