ETL Extension Guidelines - Creating a New Metric

This section is a walkthrough lab covering the Ed-Fi ETL Application development necessary for adding a new metric calculation to the Ed-Fi Dashboards. The lab covers adding new extension tables for the ODS (using RoundhousE), creating new ETL Readers and their associated SQL queries, adding new ETL Translators, and adding SpecFlow coverage to test the new calculation.

For this lab example, we'll create a new School Transportation Method metric. The School Transportation Method metric shows the percentage of students who take the bus to and from school.

A high-level overview of the steps follows:

Step 1. Complete Prerequisites

The following are steps you should take before starting this lab:

Complete the setup instructions the ETL Developers' Guide - Installation section. This lab uses the Glendale ODS v3.2 dataset.
Understand the fundamentals of the SpecFlow language (see http://www.specflow.org/).
Understand the fundamentals of RoundhousE (see https://github.com/chucknorris/roundhouse/wiki).
Install the SpecFlow language editor for Visual Studio 2013 or 2015 (see http://www.specflow.org/getting-started/).
Add the new School Transportation Information metrics to the metric CSVs using the MCT (see Managing Metrics with the Metrics Configuration Tool).

Step 2. Add Extension Tables to ODS and Sample Data to Glendale Dataset

To add the new extension tables and sample data for the Glendale dataset, add new scripts to the RoundhousE database script folders.

Navigate in File Explorer to where you have cloned the code from GitHub.
Navigate to the "Database\Scripts" folder.
Navigate to the \Ods_3.0\up folder. Scripts in the \up folder are only executed by RoundhousE on the database once.
To create the extension.StudentSchoolTransportationInformation and extension.TrasportationMethodDescriptor tables in the ODS, add a SQL script named YYYYMMDDHHmm - Add Student Transportation Extension Tables.sql with the content below.
Replace YYYYMMDDHHmm with the current timestamp.

By convention, RoundhousE will execute the scripts in order by their prepended numbering. The ETL scripts in the Database folder are prepended with YYYYMMDDHHmm. This naming convention resolves any script merge/ordering conflicts if multiple developers are adding update scripts to the same database.

Scripts added to the \Ods_3.0 folder will always run on a custom or development ODS.

YYYYMMDDHHmm - Add Student Transportation Extension Tables.sql

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'extension')
BEGIN
	EXEC('CREATE SCHEMA extension')
END

/****** Object:  Table [extension].[StudentSchoolTransportationInformation]    Script Date: 8/3/2016 4:58:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [extension].[StudentSchoolTransportationInformation](
	[StudentUSI] [int] NOT NULL,
	[SchoolId] [int] NOT NULL,
	[ToTransportationMethodDescriptorId] [int] NOT NULL,
	[ToCodeInfo] [nvarchar](50) NULL,
	[FromTransportationMethodDescriptorId] [int] NOT NULL,
	[FromCodeInfo] [nvarchar](50) NULL,
 CONSTRAINT [PK_StudentSchoolTransportationInformation] PRIMARY KEY CLUSTERED 
(
	[StudentUSI] ASC,
	[SchoolId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [extension].[TransportationMethodDescriptor]    Script Date: 8/3/2016 4:58:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [extension].[TransportationMethodDescriptor](
	[TransportationMethodDescriptorId] [int] NOT NULL,
 CONSTRAINT [PK_TransportationMethodDescriptor] PRIMARY KEY CLUSTERED 
(
	[TransportationMethodDescriptorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [extension].[StudentSchoolTransportationInformation]  WITH CHECK ADD  CONSTRAINT [FK_StudentSchoolTransportationInformation_School] FOREIGN KEY([SchoolId])
REFERENCES [edfi].[School] ([SchoolId])
GO
ALTER TABLE [extension].[StudentSchoolTransportationInformation] CHECK CONSTRAINT [FK_StudentSchoolTransportationInformation_School]
GO
ALTER TABLE [extension].[StudentSchoolTransportationInformation]  WITH CHECK ADD  CONSTRAINT [FK_StudentSchoolTransportationInformation_Student] FOREIGN KEY([StudentUSI])
REFERENCES [edfi].[Student] ([StudentUSI])
GO
ALTER TABLE [extension].[StudentSchoolTransportationInformation] CHECK CONSTRAINT [FK_StudentSchoolTransportationInformation_Student]
GO
ALTER TABLE [extension].[StudentSchoolTransportationInformation]  WITH CHECK ADD  CONSTRAINT [FK_StudentSchoolTransportationInformation_TransportationMethodDescriptor] FOREIGN KEY([ToTransportationMethodDescriptorId])
REFERENCES [extension].[TransportationMethodDescriptor] ([TransportationMethodDescriptorId])
GO
ALTER TABLE [extension].[StudentSchoolTransportationInformation] CHECK CONSTRAINT [FK_StudentSchoolTransportationInformation_TransportationMethodDescriptor]
GO
ALTER TABLE [extension].[StudentSchoolTransportationInformation]  WITH CHECK ADD  CONSTRAINT [FK_StudentSchoolTransportationInformation_TransportationMethodDescriptor1] FOREIGN KEY([FromTransportationMethodDescriptorId])
REFERENCES [extension].[TransportationMethodDescriptor] ([TransportationMethodDescriptorId])
GO
ALTER TABLE [extension].[StudentSchoolTransportationInformation] CHECK CONSTRAINT [FK_StudentSchoolTransportationInformation_TransportationMethodDescriptor1]
GO
ALTER TABLE [extension].[TransportationMethodDescriptor]  WITH CHECK ADD  CONSTRAINT [FK_TransportationMethodDescriptor_Descriptor] FOREIGN KEY([TransportationMethodDescriptorId])
REFERENCES [edfi].[Descriptor] ([DescriptorId])
GO
ALTER TABLE [extension].[TransportationMethodDescriptor] CHECK CONSTRAINT [FK_TransportationMethodDescriptor_Descriptor]
GO

Navigate to the Ods_3.0\permissions folder and edit the 0001 - edfiPService.sql script to allow read access on the extension schema.

By convention, RoundhousE will execute scripts in the \permissions folder every time on the database.

Edit to 0001 - edfiPService.sql

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'edfiPService')
BEGIN
    CREATE USER [edfiPService]
END
GO

ALTER USER [edfiPService] WITH
    LOGIN = [edfiPService],
    DEFAULT_SCHEMA = [edfi]
GO

GRANT SELECT ON SCHEMA::[edfi] TO [edfiPService]
GRANT SELECT ON SCHEMA::[dbo] TO [edfiPService]
GRANT SELECT ON SCHEMA::[extension] TO [edfiPService]

IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'ods')
BEGIN
    GRANT SELECT ON SCHEMA::[ods] TO [edfiPService]
END

IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'interop')
BEGIN
    GRANT SELECT ON SCHEMA::[interop] TO [edfiPService]
END

Navigate to the Database\Ods_3.0_Glendale\up folder and add the SQL script YYYYMMDDHHmm - Add Student School Transportation Information Records.sql with the content below. This script will add sample data to the Glendale development database for our integration tests.

This script must be timestamped after the "YYYYMMDDHHmm - Add Student Transportation Extension Tables.sql" script we created above to ensure the scripts run in the correct order. Any scripts added to the \Ods_3.0_Glendale folder will execute against the Glendale development database.

YYYYMMDDHHmm - Add Student School Transportation Information Records.sql

INSERT INTO [edfi].[Descriptor]
           ([Namespace]
           ,[CodeValue]
           ,[ShortDescription]
           ,[Description]
           ,[Id]
           ,[LastModifiedDate]
           ,[CreateDate])
     VALUES
           ('uri://example.org/TransportationMethodDescriptor'
           , 'Bus'
           , 'Bus'
           , 'Bus'
           , 'DE28831E-0502-45D4-BE81-081E39034DB5'
           , GETDATE()
           , GETDATE())

INSERT INTO [extension].[TransportationMethodDescriptor]
           ([TransportationMethodDescriptorId])
     VALUES
           (SCOPE_IDENTITY())


INSERT INTO [edfi].[Descriptor]
           ([Namespace]
           ,[CodeValue]
           ,[ShortDescription]
           ,[Description]
           ,[Id]
           ,[LastModifiedDate]
           ,[CreateDate])
     VALUES
           ('uri://example.org/TransportationMethodDescriptor'
           , 'Driven'
           , 'Driven'
           , 'Driven'
           , '59070163-3B30-4CAC-A045-A7E8C508772E'
           , GETDATE()
           , GETDATE())

INSERT INTO [extension].[TransportationMethodDescriptor]
           ([TransportationMethodDescriptorId])
     VALUES
           (SCOPE_IDENTITY())


INSERT INTO [edfi].[Descriptor]
           ([Namespace]
           ,[CodeValue]
           ,[ShortDescription]
           ,[Description]
           ,[Id]
           ,[LastModifiedDate]
           ,[CreateDate])
     VALUES
           ('uri://example.org/TransportationMethodDescriptor'
           , 'Walk/Bike'
           , 'Walk/Bike'
           , 'Walk/Bike'
           , '40030062-0438-4A6E-99EC-00C4878E6198'
           , GETDATE()
           , GETDATE())

INSERT INTO [extension].[TransportationMethodDescriptor]
           ([TransportationMethodDescriptorId])
     VALUES
           (SCOPE_IDENTITY())

DECLARE @busTransportationDescriptorId INT
DECLARE @drivenTransportationDescriptorId INT
DECLARE @walkBikeTransportationDescriptorId INT

SELECT @busTransportationDescriptorId = d.DescriptorId
FROM edfi.Descriptor d
WHERE [Namespace] = 'uri://example.org/TransportationMethodDescriptor'
AND [CodeValue] = 'Bus'

SELECT @drivenTransportationDescriptorId = d.DescriptorId
FROM edfi.Descriptor d
WHERE [Namespace] = 'uri://example.org/TransportationMethodDescriptor'
AND [CodeValue] = 'Driven'

SELECT @walkBikeTransportationDescriptorId = d.DescriptorId
FROM edfi.Descriptor d
WHERE [Namespace] = 'uri://example.org/TransportationMethodDescriptor'
AND [CodeValue] = 'Walk/Bike'

INSERT INTO [extension].[StudentSchoolTransportationInformation]
           ([StudentUSI]
           ,[SchoolId]
           ,[ToTransportationMethodDescriptorId]
           ,[ToCodeInfo]
           ,[FromTransportationMethodDescriptorId]
           ,[FromCodeInfo])
     VALUES
           (100039441
           , 867530022
           , @busTransportationDescriptorId
           , '834'
           , @busTransportationDescriptorId
           , '3454')

INSERT INTO [extension].[StudentSchoolTransportationInformation]
           ([StudentUSI]
           ,[SchoolId]
           ,[ToTransportationMethodDescriptorId]
           ,[ToCodeInfo]
           ,[FromTransportationMethodDescriptorId]
           ,[FromCodeInfo])
     VALUES
           (100040483
           , 867530022
           , @busTransportationDescriptorId
           , '23'
           , @busTransportationDescriptorId
           , '4030')

INSERT INTO [extension].[StudentSchoolTransportationInformation]
           ([StudentUSI]
           ,[SchoolId]
           ,[ToTransportationMethodDescriptorId]
           ,[ToCodeInfo]
           ,[FromTransportationMethodDescriptorId]
           ,[FromCodeInfo])
     VALUES
           (100041249
           , 867530022
           , @drivenTransportationDescriptorId
           , NULL
           , @drivenTransportationDescriptorId
           , NULL)

INSERT INTO [extension].[StudentSchoolTransportationInformation]
           ([StudentUSI]
           ,[SchoolId]
           ,[ToTransportationMethodDescriptorId]
           ,[ToCodeInfo]
           ,[FromTransportationMethodDescriptorId]
           ,[FromCodeInfo])
     VALUES
           (100044743
           , 867530022
           , @busTransportationDescriptorId
           , '834'
           , @drivenTransportationDescriptorId
           , NULL)

INSERT INTO [extension].[StudentSchoolTransportationInformation]
           ([StudentUSI]
           ,[SchoolId]
           ,[ToTransportationMethodDescriptorId]
           ,[ToCodeInfo]
           ,[FromTransportationMethodDescriptorId]
           ,[FromCodeInfo])
     VALUES
           (100044859
           , 867530022
           , @drivenTransportationDescriptorId
           , NULL
           , @drivenTransportationDescriptorId
           , NULL)

INSERT INTO [extension].[StudentSchoolTransportationInformation]
           ([StudentUSI]
           ,[SchoolId]
           ,[ToTransportationMethodDescriptorId]
           ,[ToCodeInfo]
           ,[FromTransportationMethodDescriptorId]
           ,[FromCodeInfo])
     VALUES
           (100044880
           , 867530022
           , @walkBikeTransportationDescriptorId
           , NULL
           , @walkBikeTransportationDescriptorId
           , NULL)

INSERT INTO [extension].[StudentSchoolTransportationInformation]
           ([StudentUSI]
           ,[SchoolId]
           ,[ToTransportationMethodDescriptorId]
           ,[ToCodeInfo]
           ,[FromTransportationMethodDescriptorId]
           ,[FromCodeInfo])
     VALUES
           (100033697
           , 867530022
           , @busTransportationDescriptorId
           , '834'
           , @busTransportationDescriptorId
           , '3454')

Open a PowerShell command window and navigate to the \Databases folder. Execute .\build rebuild 3.0. This will call RoundhousE to recreate Dashboard DB, Dashboard DW, Application, ETLLog, and Glendale ODS databases. It will execute the SQL scripts we added above.

Once the PowerShell script completes, the Glendale ODS will have the new extension tables, a new enumeration, new Ed-Fi Descriptors, and test student school transportation information.

Step 3. Extend the ETL Runtime

In the location where the repo was cloned, navigate to Etl\src and open the EdFi Etl.sln solution.

Create the ODS Object Models

First, create the ODS objects that will be streamed on the bus for the School Transportation Information Translators to consume.

Expand the EdFi.ObjectModel project and navigate to the "Ods" folder which contains all the objects that are read from ODS and streamed through the ETL Application runtime.

Add the TransportationMethodDescriptor class

TransportationMethodDescriptor Class

using EdFi.Common.Database;

namespace EdFi.ObjectModel.Ods
{
    using System;

    [QueryFileName(FileName = "ExtensionDescriptor.sql")]
    public partial class TransportationMethodDescriptor : OdsDescriptor
    {
        private static readonly Lazy<TransportationMethodDescriptor> BusLazy = new Lazy<TransportationMethodDescriptor>(() => DescriptorResolver.Get<TransportationMethodDescriptor>("Bus"));
        public static TransportationMethodDescriptor Bus { get { return BusLazy.Value; } }

        private static readonly Lazy<TransportationMethodDescriptor> DrivenLazy = new Lazy<TransportationMethodDescriptor>(() => DescriptorResolver.Get<TransportationMethodDescriptor>("Driven"));
        public static TransportationMethodDescriptor Driven { get { return DrivenLazy.Value; } }

        private static readonly Lazy<TransportationMethodDescriptor> WalkBikeLazy = new Lazy<TransportationMethodDescriptor>(() => DescriptorResolver.Get<TransportationMethodDescriptor>("Walk/Bike"));
        public static TransportationMethodDescriptor WalkBike { get { return WalkBikeLazy.Value; } }
    }
}

Whenever the ETL Runtime, Metric Logic Tests, or Integration tests execute, the runtime will read all descriptor types from the ODS database. The TransportationMethodDescriptor class provides a static property to reference each descriptor value.

The T4 template Descriptors.tt can automatically create the TransportationMethodDescriptor class. However, running the template regenerates all descriptors, including any changes introduced to descriptor values since the last time the template was run. This can cause compilation errors. Additionally, the template doesn't generate an override for QueryFileName, which means the descriptor won't get read in properly.

Add the StudentSchoolTransportationInformation class. This object will be read from the ODS with the hydrated properties. The IChild interface denotes that StudentSchoolTransportationInformation object will be a child object of a Student. The StudentUSI class is a special construct for mapping the student's unique ID from the ODS to the student ID in the Dashboard DB.

StudentSchoolTransportationInformation Class

using EdFi.Common.Eventing;

namespace EdFi.ObjectModel.Ods
{
    public class StudentSchoolTransportationInformation: IChild<Student, StudentUSI>
    {
        public StudentUSI ParentId { get; set; }
        public Student Parent { get; set; }
        public TransportationMethodDescriptor ToTransportationMethod { get; set; }
        public TransportationMethodDescriptor FromTransportationMethod { get; set; }
        public string ToCodeInfo { get; set; }
        public string FromCodeInfo { get; set; }
    }
}

Create the Student School Transportation Information Reader and SQL Queries

To read the Student School Transportation information from the ODS, we will need to create the ETL Reader and associated SQL query. We will also need to create a query for reading in descriptors in the extension schema.

Expand the EdFi.Runtime project.
Under Reading\Queries\3.0, add an ExtensionDescriptor.sql file with the content below.
Set its Copy to Output Directory property to "Copy always".
Note that this file only needs to be created once for this schema, so if you create more descriptors on this schema, you won't need to add this script. Any descriptors you create on this schema will run this script.

SELECT [$TypeId] AS [Descriptor]
FROM [extension].[$Type]
ORDER BY [Descriptor]

In the same directory (Reading\Queries\3.0), also add a StudentSchoolTransportationInformation.sql file with the content below.
Set its Copy to Output Directory property to "Copy always".

StudentSchoolTransportationInformation.sql

SELECT stu.StudentUniqueId AS ParentId
    , ToTransportationMethodDescriptorId AS ToTransportationMethod
    , ToCodeInfo
    , FromTransportationMethodDescriptorId AS FromTransportationMethod
    , FromCodeInfo
FROM extension.StudentSchoolTransportationInformation ssti
	INNER JOIN edfi.School sch ON ssti.SchoolId = sch.SchoolId
	INNER JOIN edfi.Student stu ON ssti.StudentUSI = stu.StudentUSI
WHERE sch.LocalEducationAgencyId = @currentLeaId
ORDER BY sch.LocalEducationAgencyId, sch.SchoolId, stu.StudentUSI

By convention, the SQL file name and the ODS object need to have the same name for the query results to map to the StudentSchoolTransportationInformation ODS object. For ODS v3.2 compatibility, the query will need to return the Student's unique ID as ParentId.

The ParentId provides a hierarchy-mapping convention to map StudentSchoolTransportationInformation objects to Student objects. The query results need to be ordered by LocalEducationAgencyId, SchoolId, and StudentUSI in order to correctly link objects together in the base reader. The results need to be filtered by LocalEducationAgencyId since the ETL Application computes one LEA at a time.

Expand EdFi.Runtime project. Under Reading\CoreReaders, add the class StudentSchoolTransportationInformationReader with the content below.

StudentSchoolTransportationInformationReader Class

using EdFi.Common.Database;
using EdFi.ObjectModel;
using EdFi.ObjectModel.Ods;

namespace EdFi.Runtime.Reading.CoreReaders
{
    public class StudentSchoolTransportationInformationReader : BaseReader<Student, StudentSchoolTransportationInformation, StudentUSI>
    {
        public StudentSchoolTransportationInformationReader(IQuery query) :   
           base(query, false) {}
    }
}

The BaseReader contains the logic to enumerate through the data returned by the SQL query. It is responsible for tying StudentSchoolTransportationInformation objects to Student objects. The second parameter to the base constructor is for caching queries. This is useful for keeping SQL connections open for smaller datasets in the ETL Runtime.

Create the Metric Enumerations Classes

The metric enumeration classes are static classes that hold the metric ID and default goal values. The metric classes also provide methods to determine the metric state (i.e., good, bad) for a metric instance calculation. The new metric IDs (2174, 2175, and 2176) were created by following the steps outlined in the article How To: Add a New Metric.

Expand EdFi.Common project and open the Metric.cs class in the Enumerations folder. Add the following content.

Metric.cs Additions

public static readonly Metric SchoolTransportationInformationContainer = new ContainerMetric(2174, "School Transportation Method Rate");
public static readonly Metric ToSchoolWithBusTransportationInformation = new SchoolTransportationInformation(2175, "To School With Bus Transportation Information", .90m, RateDirection.OneToZero);
public static readonly Metric FromSchoolWithBusTransportationInformation = new SchoolTransportationInformation(2176, "From School With Bus Transportation Information", .90m, RateDirection.OneToZero);

public class SchoolTransportationInformation : Metric
{
    public SchoolTransportationInformation(int value, string displayName, decimal defaultGoal, RateDirection rateDirection)
        : base(value, displayName)
    {
        DefaultGoal = defaultGoal;
        RateDirection = rateDirection;
    }

    sealed public override decimal DefaultGoal { get; set; }
    sealed public override RateDirection RateDirection { get; set; }
}

The RateDirection enum determines if the metric is a positive-trend or negative-trend metric. RateDirection.OneToZero is a positive-trend and RateDirection.ZeroToOne is negative-trend. It is used for calculating the metric state in the metric state helper classes.

For example, if a metric has a positive trend (RateDirection.OneToZero) and its metric goal is 90%, then for any value 90% or higher the metric state is good, otherwise it is bad. If the metric has a negative trend (RateDirection.ZeroToOne) and its metric goal is 10%, then for any value 10% or lower the metric state is good, otherwise it is bad.

The Metric class has helper methods to determine the trend direction. The Metric class compares the current value to the previous value and if the percentage point change is greater than +5%, then the trend direction is 1, if the percentage point change is between 0% and 5% then the trend direction is 0, and if the percentage point change is greater than -5%, then the trend direction is -1. The trend interpretation on the UI side determines if the trend direction from the ETL calculation should show a positive, unchanged, or negative trend in the UI template.

Create the School Transformation Information Translator

Now that the plumbing for streaming StudentSchoolTransportationInformation objects has been created, add the ETL Translators to perform the metric calculations on the streamed data.

Expand Core Metrics solution folder , add a new Class Library project called "SchoolTransportationInformation". Ensure that the new project is created for .NET Framework 4.5.2 and that it is placed in the Etl\src\Core Metrics\ directory.

Add EdFi.Common and EdFi.ObjectModel as project references.

Create the class SchoolTransportationInformationCalculator and add the following content.

SchoolTransportationInformationCalculator Class

using EdFi.Common;
using EdFi.Common.Eventing;
using EdFi.ObjectModel.Application;
using EdFi.ObjectModel.Dashboard;
using EdFi.ObjectModel.Ods;
using Metric = EdFi.Common.Enumerations.Metric;

namespace SchoolTransportationInformation
{
    public abstract class SchoolTransportationInformationCalculator 
        : IStream<StudentSchoolTransportationInformation>
        , IStream<School>
        , IStream<EducationOrganizationGoal>
    {
        private readonly IBus _bus;
        private readonly Metric _granularMetric;
        private int _totalStudentsWithTransportation;
        private int _totalStudentsTakingSpecificTransportationMethod;
        private EducationOrganizationGoal _educationOrganizationGoal;

        protected SchoolTransportationInformationCalculator(IBus bus
            , Metric granularMetric)
        {
            _bus = bus;
            _granularMetric = granularMetric;
        }

        protected abstract bool ShouldCountStudentSchoolTransportation(StudentSchoolTransportationInformation message);

        public void OnStreamBegin(StudentSchoolTransportationInformation message)
        {
            if (ShouldCountStudentSchoolTransportation(message))
            {
                _totalStudentsTakingSpecificTransportationMethod++;
            }

            _totalStudentsWithTransportation++;
        }

        public void OnStreamEnd(StudentSchoolTransportationInformation message) { }

        public void OnStreamBegin(School message)
        {
            _totalStudentsWithTransportation = 0;
            _totalStudentsTakingSpecificTransportationMethod = 0;
            _educationOrganizationGoal = null;
        }

        public void OnStreamEnd(School message)
        {
            var transportationRatio = _totalStudentsTakingSpecificTransportationMethod.DivideBySafeAndRound(_totalStudentsWithTransportation);

            if (transportationRatio == null)
            {
                return;
            }

            var metricState = _granularMetric.GetMetricStateType(transportationRatio.Value, _educationOrganizationGoal);

            _bus.Publish(new MetricInstance
            {
                MetricInstanceSetKey = message.MetricInstanceSetKey,
                MetricId = _granularMetric.Id,
                Goal = _granularMetric.GetGoal(_educationOrganizationGoal),
                MetricStateTypeId = metricState.Value,
                Value = transportationRatio.Display(),
                ValueTypeName = "System.Double"
            }
            , new MetricInstanceExtendedProperty
            {
                MetricId = _granularMetric.Id,
                MetricInstanceSetKey = message.MetricInstanceSetKey,
                Name = Statics.Subpopulation,
                Value = string.Format("{0}", _totalStudentsTakingSpecificTransportationMethod),
                ValueTypeName = "System.Int32"
            }
            , new MetricInstanceExtendedProperty
            {
                MetricId = _granularMetric.Id,
                MetricInstanceSetKey = message.MetricInstanceSetKey,
                Name = Statics.Totalpopulation,
                Value = string.Format("{0}", _totalStudentsWithTransportation),
                ValueTypeName = "System.Int32"
            }
            , new MetricGoal
            {
                MetricId = _granularMetric.Id,
                MetricInstanceSetKey = message.MetricInstanceSetKey,
                Value = _granularMetric.GetGoal(_educationOrganizationGoal)
            });
        }

        public void OnStreamBegin(EducationOrganizationGoal message)
        {
            if (message.MetricId != _granularMetric.Id)
            {
                return;
            }

            _educationOrganizationGoal = message;
        }

        public void OnStreamEnd(EducationOrganizationGoal message)
        {
            
        }
    }
}

The SchoolTransporationInformationCalculator is written as a base class which allows the class to be easily extendible to report different metric calculations. For example, the Translator is currently calculating the percentage of students who take the bus. The Translator can be easily extended to calculate the number of students who are driven to school.

Features of the SchoolTransporationInformationCalculator class:

The IStream<T> interface determines what objects the calculator class listens to on the stream. This class listens to StudentSchoolTransportationInformation, School, and EducationOrganizationGoal objects. The objects are streamed in order based on their hierarchy. The School object will be streamed first and the method OnStreamBegin(School message) will be called with the current school. Then, all the EducationOrganizationGoal objects and StudentSchoolTransportationInformation objects will be streamed for that school calling the OnStreamBegin and OnStreamEnd methods for each object. After all EducationOrganizationGoal objects and StudentSchoolTransportationInformation objects have streamed for the school, then the OnStreamEnd(School message) will be called.
The constructor takes the IBus and granular metric object. The IBus is for publishing new metrics on the bus that will be written to the Dashboard DB. The granular metric will be passed from classes that inherit the SchoolTransporationInformationCalculator.
The EducationOrganizationGoal object holds the configured metric goal (if there was one setup through the Dashboard UI). The calculator needs an if check for the granular metric ID since this method will receive all EducationOrganizationGoals for a school.
The OnStreamBegin(School message) method resets the counters for the metric calculation since this is a per-school calculation.
The OnStreamBegin(StudentSchoolTransporationInformation message) method determines if the StudentSchoolTransporationInformation is included in the metric calculation. It calls the override method ShouldCountStudenSchoolTransportation to determine if it should be counted toward the calculation.
When OnStreamEnd(School message) method is called, all StudentSchoolTransportationInformation objects will have been streamed for the school. The percentage of students who take the bus to school is calculated. The granular metric has the helper class GetMetricStateType to determine the metric state. It will publish the necessary MetricInstance, MetricInstanceExtendedProperty, and MetricGoal objects to the bus.
The MetricInstance object shows the ratio and metric state (i.e., good, bad) on the UI.
The MetricInstanceExtendedProperty objects show the total number of students who attained the goal out of the total numbers of students included in the calculation. The MetricGoal object informs the UI what goal was used in the metric calculation.

Now we'll create a few Translators for our metric.

Create the class ToSchoolWithBusTransporationInformationTranslator with the following content.

using EdFi.Common.Enumerations;
using EdFi.Common.Eventing;
using EdFi.ObjectModel.Ods;

namespace SchoolTransportationInformation
{
    public class ToSchoolWithBusTransportationInformationTranslator
        : SchoolTransportationInformationCalculator
    {
        public ToSchoolWithBusTransportationInformationTranslator(IBus bus) 
            : base(bus, Metric.ToSchoolWithBusTransportationInformation) {}
        protected override bool ShouldCountStudentSchoolTransportation(StudentSchoolTransportationInformation message)
        {
            return message.ToTransportationMethod == TransportationMethodDescriptor.Bus;
        }
    }
}

Create the class FromSchoolWithBusTransportationInformationTranslator with the following content.

using EdFi.Common.Enumerations;
using EdFi.Common.Eventing;
using EdFi.ObjectModel.Ods;

namespace SchoolTransportationInformation
{
    public class FromSchoolWithBusTransportationInformationTranslator : SchoolTransportationInformationCalculator
    {
        public FromSchoolWithBusTransportationInformationTranslator(IBus bus) 
            : base(bus, Metric.FromSchoolWithBusTransportationInformation) {}
        protected override bool ShouldCountStudentSchoolTransportation(StudentSchoolTransportationInformation message)
        {
            return message.FromTransportationMethod == TransportationMethodDescriptor.Bus;
        }
    }
}

Step 4. Test the ETL Runtime Extensions

Create School Transportation Information Metric Logic Tests

To ensure that the SchoolTransportationInformation Translator calculates metrics correctly, add the school transportation information metric logic tests.

Expand EdFi.MetricBusinessLogic.Tests project and add a new folder called "SchoolTransportationInformation".
Create a new SpecFlow feature file called "SchoolTransportationInformation" (Add > New Item...) and add the following scenario tests.

If the option to create a SpecFlow file is missing, install the SpecFlow extension for Visual Studio available at http://www.specflow.org/. SpecFlow uses the Gherkin language for describing scenarios (see http://www.specflow.org/documentation/Using-Gherkin-Language-in-SpecFlow/ for more information).

SpecFlow Test for School Transportation Information

Feature: School Transportation Information
    These tests cover the following metrics
        2175: School - To School Transportation Method
        2174: School - From School Transportation Method

Scenario: Business Rule: To School With Bus Ratio
	Given a High School
	And 20 enrolled students where 12 students take the bus to school
	When executed
	Then the transportation rate of students taking the bus to school should be .600
    And 12 out of 20 students take the bus to school

Scenario: Business Rule: From School With Bus Ratio
	Given a High School
	And 20 enrolled students where 15 students take the bus from school
	When executed
	Then the transportation rate of students taking the bus from school should be .750
    And 15 out of 20 students take the bus from school

Scenario Outline: Business Rule: Metric State - Positive Trend (School)
    Given a High School
    And a campus <Goal> of <GoalValue>
    And <EnrolledStudents> enrolled students where <StudentsTakeTheBus> students take the bus to and from school
    When executed
    * Note: The default goal for school transportation by bus is 90% or higher.
    Then then transportation to and from school by bus metric state should be <MetricState>
    Examples:
        | Goal    | GoalValue | StudentsTakeTheBus | EnrolledStudents | MetricValue | MetricState | Note            |
        | default | null      | 19                 | 20               | .950        | good        |                 |
        | default | null      | 18                 | 20               | .900        | good        | Tests edge case |
        | default | null      | 15                 | 20               | .750        | bad         |                 |
        | goal    | .700      | 15                 | 20               | .750        | good        |                 |
        | goal    | .700      | 14                 | 20               | .700        | good        | Tests edge case |
        | goal    | .700      | 13                 | 20               | .650        | bad         |                 |

Add the class SchoolTransportationMethodTest to the EdFi.MetricBusinessLogic.Tests\SchoolTransportationInformation directory with the content below.

SchoolTransportationMethodTest Class

using System.Linq;
using EdFi.Common.Enumerations;
using EdFi.MetricBusinessLogic.Tests.StudentAttendance;
using EdFi.ObjectModel;
using EdFi.ObjectModel.Ods;
using TechTalk.SpecFlow;

namespace EdFi.MetricBusinessLogic.Tests.SchoolTransportationInformation
{
    [Binding, Scope(Feature = "School Transportation Information")]
    public class SchoolTransportationMethodTest
    {
        private readonly SchoolFeatureSteps _schoolFeatureSteps;
        private readonly StudentFeatureSteps _studentFeatureSteps;

        public SchoolTransportationMethodTest()
        {
            _schoolFeatureSteps = new SchoolFeatureSteps();
            _studentFeatureSteps = new StudentFeatureSteps();
        }

        [Given(@"an? ([A-z]+ School)")]
        public void GivenAHighSchool(SchoolCategoryDescriptor schoolCategoryDescriptor)
        {
            _schoolFeatureSteps.GivenASchoolWithACompletedCalendar(schoolCategoryDescriptor);
        }

        [Given(@"([0-9\-]+) enrolled students where ([0-9\-]+) students take the bus from school")]
        [Given(@"([0-9\-]+) enrolled students where ([0-9\-]+) students take the bus to school")]
        [Given(@"([0-9\-]+) enrolled students where ([0-9\-]+) students take the bus to and from school")]
        public void GivenEnrolledStudentsWhereStudentsTakeTheBusToSchool(int enrolledStudents, int studentsTakeBusToSchool)
        {
            var busDescriptor = DescriptorResolver.Get<TransportationMethodDescriptor>("Bus");
            var drivenDescriptor = DescriptorResolver.Get<TransportationMethodDescriptor>("Driven");

            for (int student = 0; student < enrolledStudents; student++)
            {
                var testStudent = _studentFeatureSteps.AddStudentToSchool();
                
                testStudent.AddStudentSchoolTransportationInformation(new StudentSchoolTransportationInformation
                {
                    FromCodeInfo = "834",
                    FromTransportationMethod = student < studentsTakeBusToSchool ? busDescriptor : drivenDescriptor,
                    ToCodeInfo = "764",
                    ToTransportationMethod = student < studentsTakeBusToSchool ? busDescriptor : drivenDescriptor
                });
            }
        }

        [Given(@"a campus (goal|default) of ([0-9\.]+|null)")]
        public void GivenAGoodMetricStateOf(string goalState, string goalValue)
        {
            var school = ScenarioContext.Current.GetLastSchoolTestData();
            school.AssignCampusGoal(Metric.ToSchoolWithBusTransportationInformation, goalState, goalValue);
            school.AssignCampusGoal(Metric.FromSchoolWithBusTransportationInformation, goalState, goalValue);
        }

        [Then(@"([0-9\-]+) out of ([0-9\-]+) students take the bus to school")]
        public void ThenOutOfStudentsTakeTheBusToSchool(string studentsTakingTheBus, string totalStudents)
        {
            MetricInstanceAssertions.AssertMetricInstanceExtendedPropertyNumerator(Metric.ToSchoolWithBusTransportationInformation.Id, Tier.school, null, studentsTakingTheBus);
            MetricInstanceAssertions.AssertMetricInstanceExtendedPropertyDenominator(Metric.ToSchoolWithBusTransportationInformation.Id, Tier.school, null, totalStudents);
        }

        [Then(@"([0-9\-]+) out of ([0-9\-]+) students take the bus from school")]
        public void ThenOutOfStudentsTakeTheBusFromSchool(string studentsTakingTheBus, string totalStudents)
        {
            MetricInstanceAssertions.AssertMetricInstanceExtendedPropertyNumerator(Metric.FromSchoolWithBusTransportationInformation.Id, Tier.school, null, studentsTakingTheBus);
            MetricInstanceAssertions.AssertMetricInstanceExtendedPropertyDenominator(Metric.FromSchoolWithBusTransportationInformation.Id, Tier.school, null, totalStudents);
        }

        [Then(@"the transportation rate of students taking the bus to school should be ([0-9\.]+|empty)")]
        public void ThenTheTransportationRateToSchoolWithBusShouldBe_(string rate)
        {
            MetricInstanceAssertions.AssertMetricInstance(Metric.ToSchoolWithBusTransportationInformation.Id, Tier.school, rate, null, null, null);
        }

        [Then(@"the transportation rate of students taking the bus from school should be ([0-9\.]+|empty)")]
        public void ThenTheTransportationRateFromSchoolWithBusShouldBe_(string rate)
        {
            MetricInstanceAssertions.AssertMetricInstance(Metric.FromSchoolWithBusTransportationInformation.Id, Tier.school, rate, null, null, null);
        }

        [Then(@"then transportation to and from school by bus metric state should be (good|bad)")]
        public void ThenThenTransporationToAndFromSchoolByBusMetricStateShouldBeGood(MetricStateType metricStateType)
        {
            MetricInstanceAssertions.AssertMetricInstance(Metric.FromSchoolWithBusTransportationInformation.Id, Tier.school, null, null, null, metricStateType);
            MetricInstanceAssertions.AssertMetricInstance(Metric.ToSchoolWithBusTransportationInformation.Id, Tier.school, null, null, null, metricStateType);
        }

        [When(@"executed")]
        public void WhenExecuted()
        {
            FeatureStepRunner.ExecuteScenario(a => a.GetName().Name.Contains("SchoolTransportationInformation"));
        }
    }
}

A few points to note about the code above:

The SchoolTransportationMethodTest class creates the test data for the SchoolTransportationInformation Translator and asserts metric instance data created by the Translator.
[Binding, Scope(Feature = "School Transportation Information")], [Given.., [Then..., and [When... are attributes needed by SpecFlow to bind the feature file the test runner class.
SchoolFeatureSteps and StudentFeatureSteps are helper classes to create test schools and test students for the scenarios.
The MetricInstanceAssertions class provides methods to assert the information on MetricInstance and MetricInstanceExtendedProperty objects.
The FeatureStepRunner.ExecuteScenario takes the name of the Translator project "SchoolTransportationInformation". For increased performance, it ensures that this project is the only metric project loaded when running the scenarios' tests.

Now we'll continue with our code updates.

Update the StudentTestData class in EdFi.MetricBusinessLogic.Tests\StudentTestData.cs with the following content.

private readonly List<StudentSchoolTransportationInformation> _studentSchoolTransportationInformation = new List<StudentSchoolTransportationInformation>(); 

public IEnumerable<StudentSchoolTransportationInformation> StudentSchoolTransportationInformation
{
    get { return _studentSchoolTransportationInformation; }
} 

public void AddStudentSchoolTransportationInformation(StudentSchoolTransportationInformation message)
{
    _studentSchoolTransportationInformation.Add(message);
}

Update the QueryItemsDirect<T> method in the EdFi.MetricBusinessLogic.Tests\FilteredQuery.cs class file with the following content.

else if (typeof(T) == typeof(StudentSchoolTransportationInformation))
{
    foreach (var studentSchoolTransportationInformation in AllStudents(x => x.StudentSchoolTransportationInformation))
    {
        yield return (T)(object)studentSchoolTransportationInformation;
    }
}

The FilteredQuery class returns the sample test data objects created by the SpecFlow scenarios to the ETL Readers. The test will not read StudentSchoolTransportationInformation records from the ODS database. The AllStudents method ensures that the objects are returned in the correct order for the readers to consume the data.

To Execute the SchoolTransportationInformation tests, right-click on the SchoolTransportationInformation folder from the Solution Explorer and select Run Unit Tests. (Additional information is available in the article How To: Execute SpecFlow Tests in the ETL Solution.)

Create School Transportation Information Integration Tests

Integration tests ensure that our SQL StudentSchoolTransportationInformation.sql is pulling the correct data from the ODS.

Expand EdFi.Runtime.Tests project and under the DatabaseIntegrationTests folder create the SchoolTransportationInformationTests folder.
Create a SpecFlow file called "SchoolTransportationInformation" and add the following content.

SpecFlow Integration Test for SchoolTransportationInformation

Feature: School Transportation Information

Scenario: A school where school does not have any student school transportation information
    Given School 867530015
    Then the school transportation information MetricInstances should be empty
    And the school transportation information MetricInstanceExtendedproperties should be empty

Scenario: A school where school has school transportation information
    Given School 867530022
    Then the school transportation information MetricInstances MetricInstanceSetKey,MetricId,MetricStateTypeId,Context,Value,ValueTypeName,Flag,TrendDirection should be
        | MetricInstanceSetKey                 | MetricId | MetricStateTypeId | Context | Value | ValueTypeName | Flag | TrendDirection |
        | 9BA0C2BD-8907-E3FB-4FE4-61BD6C9F4EEF | 2175     | 3                 | NULL    | .500  | System.Double | 0    | NULL           |
        | 9BA0C2BD-8907-E3FB-4FE4-61BD6C9F4EEF | 2176     | 3                 | NULL    | .333  | System.Double | 0    | NULL           |
    And the school transportation information MetricInstanceExtendedproperties MetricInstanceSetKey,MetricId,Name,Value,ValueTypeName should be
        | MetricInstanceSetKey                 | MetricId | Name            | Value | ValueTypeName |
        | 9BA0C2BD-8907-E3FB-4FE4-61BD6C9F4EEF | 2175     | Subpopulation   | 3     | System.Int32  |
        | 9BA0C2BD-8907-E3FB-4FE4-61BD6C9F4EEF | 2175     | TotalPopulation | 6     | System.Int32  |
        | 9BA0C2BD-8907-E3FB-4FE4-61BD6C9F4EEF | 2176     | Subpopulation   | 2     | System.Int32  |
        | 9BA0C2BD-8907-E3FB-4FE4-61BD6C9F4EEF | 2176     | TotalPopulation | 6     | System.Int32  |

The scenarios above are testing the extension.StudentSchoolTransportationInformation data that exists in the Glendale ODS database.

Create a class file called "SchoolTransportationInformationTests.cs" with the following content to wire up the SpecFlow integration tests.

SchoolTransportationInformationTests Class

using System;
using System.Collections.Generic;
using System.Linq;
using EdFi.Common;
using EdFi.ObjectModel.Dashboard;
using EdFi.ObjectModel.Ods;
using Newtonsoft.Json;
using NUnit.Framework;
using Shouldly;
using TechTalk.SpecFlow;
using Metric = EdFi.Common.Enumerations.Metric;

namespace EdFi.Runtime.Tests.DatabaseIntegrationTests.SchoolTransportationInformationTests
{
    [Binding, Scope(Feature = "School Transportation Information")]
    public class SchoolTransportationInformationTests
    {
        private static readonly int[] _metricIds =
        {
            Metric.FromSchoolWithBusTransportationInformation.Id,
            Metric.ToSchoolWithBusTransportationInformation.Id
        };

        [Given(@"School (.*)")]
        public void GivenLocalEducationAgency(int schoolId)
        {
            CurrentSchoolId = schoolId;
        }

        private int CurrentSchoolId { get; set; }

        private Guid? GetSchoolMetricInstanceSetKey()
        {
            var school = Trace.GetMessages<School>()
                .Where(x => x.Id == CurrentSchoolId)
                .SingleOrDefault();

            return (school == null) ? (Guid?)null : school.MetricInstanceSetKey;
        }

        [Then(@"the school transportation information MetricInstances should be empty")]
        public void ThenTheSchoolTransportationInformationMetricInstancesShouldBeEmpty()
        {
            var actual = Trace.GetMessages<MetricInstance>()
                .Where(x => x.MetricInstanceSetKey == GetSchoolMetricInstanceSetKey())
                .Where(x => x.MetricId.IsIn(_metricIds));

            actual.ShouldBeEmpty();
        }

        [Then(@"the school transportation information MetricInstanceExtendedproperties should be empty")]
        public void ThenTheSchoolTransportationInformationMetricInstanceExtendedpropertiesShouldBeEmpty()
        {
            var actual = Trace.GetMessages<MetricInstanceExtendedProperty>()
                .Where(x => x.MetricInstanceSetKey == GetSchoolMetricInstanceSetKey())
                .Where(x => x.MetricId.IsIn(_metricIds));

            actual.ShouldBeEmpty();
        }

        [Then(@"the school transportation information MetricInstances (.*) should be")]
        public void ThenTheSchoolTransportationInformationMetricInstancesShouldBe(string property, Table table)
        {
            var expected = table.CustomCreateInstances<MetricInstance>();
            var actual = Trace.GetMessages<MetricInstance>()
                .Where(x => x.MetricInstanceSetKey == GetSchoolMetricInstanceSetKey())
                .Where(x => x.MetricId.IsIn(_metricIds));
            Func<IEnumerable<MetricInstance>, IEnumerable<MetricInstance>> order = list =>
                list.OrderBy(x => x.MetricId);

            var constraint = ListComparerConstraint.Create(expected, order);
            constraint.DisplayItem = JsonConvert.SerializeObject;
            constraint.EqualityComparerFunc = constraint.CreateEqualityCompareFunc(property);
            Assert.That(actual, constraint);
        }

        [Then(@"the school transportation information MetricInstanceExtendedproperties (.*) should be")]
        public void ThenTheSchoolTransportationInformationMetricInstanceExtendedPropertyShouldBe(string property, Table table)
        {
            var expected = table.CustomCreateInstances<MetricInstanceExtendedProperty>();
            var actual = Trace.GetMessages<MetricInstanceExtendedProperty>()
                .Where(x => x.MetricInstanceSetKey == GetSchoolMetricInstanceSetKey())
                .Where(x => x.MetricId.IsIn(_metricIds));

            Func<IEnumerable<MetricInstanceExtendedProperty>, IEnumerable<MetricInstanceExtendedProperty>> order = list =>
                list.OrderBy(x => x.MetricId)
                .ThenBy(x => x.Name);

            var constraint = ListComparerConstraint.Create(expected, order);
            constraint.DisplayItem = JsonConvert.SerializeObject;
            constraint.EqualityComparerFunc = constraint.CreateEqualityCompareFunc(property);
            Assert.That(actual, constraint);
        }
    }
}

The SchoolTransportationInformationTests class has the following features:

The Trace.GetMessages<T> method has access to all objects that are published on the bus.
The table.CustomCreateInstances<T> is an extension method that maps a SpecFlow table to the object instance.
For comparing objects on the bus with the expected values in the SpecFlow test, the records need to be filtered by the MetricInstanceSetKey and MetricId since there could be other metrics published on the bus.
The ListComparerConstraint class asserts that two object lists are equal. It can take a comma-delimited property list to create the function that compares each object in the list.

Now we'll continue with our code changes.

Navigate to the TestConfiguration.cs file under the EdFi.Runtime.Tests and add the following line to the TestConfigurations constructor.

ExcludeExtensionList = GetExtensionExclusionList("SchoolTransportationInformation");

Integration tests for all metrics can take up to two hours to run. The ExcludeExtensionList can narrow down the number of metrics being calculated to specific projects in the Core Metrics solution folder. The code above specifies that only the SchoolTransportationInformation Translator will calculate metrics when integration tests are run. This greatly reduces the integration test execution time. This code should not be submitted to GitHub since the CI build will run all integration tests.

Execute the SchoolTransportationInformation integration tests, right-click on the SchoolTransportationInformationTests folder and select Run Unit Tests. (Additional information is available in the article How To: Execute SpecFlow Tests in the ETL Solution).

The Glendale ODS sample data has a student who is not currently enrolled in school. As a final level of polish, let's update the Translator to exclude non-enrolled students.

Navigate to the SchoolTransportationInformation project in the Core Metrics solution folder and update the OnStreamBegin(StudentSchoolTransportationInformation message) method in the SchoolTransportationInformationCalculator class to ignore non-enrolled students.

public void OnStreamBegin(StudentSchoolTransportationInformation message)
{
    if (!message.Parent.IsEnrolledToSchool)
    {
        return;
    }

    if (ShouldCountStudentSchoolTransportation(message))
    {
        _totalStudentsTakingSpecificTransportationMethod++;
    }

    _totalStudentsWithTransportation++;
}

Rebuild the solution.
Execute the SchoolTransportationInformation integration tests by right-clicking on the SchoolTransportationInformationTests folder and selecting Run Unit Tests. (Additional information is available in the article How To: Execute SpecFlow Tests in the ETL Solution.)