A newer version of the Ed-Fi ODS / API is now available. See the Ed-Fi Technology Version Index for a link to the latest version.

How To: Create a Custom Populated Template

The Ed-Fi ODS / API ships with a sample dataset containing approximately 1000 students. This sample is used to automatically populate test and sandbox environments. 

For a variety of reasons, you may want to install a different sample database than the one that ships with ODS / API. You can create your own dataset, for example, to populate sample data with values in extended data fields or load custom data such as education organizations appropriate for your environment, calendars for your schools, and so forth.

This article describes the process to create a custom populated template database, which will enable new sandbox and testing database instances to contain the data you specify.

The different options and steps can be summarized as:

Details of each option and their steps follows.

Option 1. Build from XML Data Using the Create Populated Script

Before you begin:

  • This example uses PowerShell scripts and the EdFi.BulkLoadClient.Console found in the Ed-Fi ODS / API. You should follow the Getting Started - Installation Steps before you get started.
  • This example uses the XML sample data found in the Ed-Fi-Standard repository on GitHub. You will need to clone or download these sample data files in order to follow along.

Step 1:  Import the Create Populated Template Module

From a PowerShell session, import <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Modules\create-populated-template.psm1.

Importing the Populated Template Module
C:\> Import-Module C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Modules\create-populated-template.psm1

Step 2: Use the Initialize-PopulatedTemplate Script

The next command, Initialize-PopulatedTemplate, which will run through a number of predefined tasks that will be described below:

Task NameDescription
Invoke-SampleXmlValidationValidates the xml files against their xsd schema. If there are any xml errors in your sample data they should be surfaced here. This step can be skipped using the -noValidation parameter.
New-TempDirectoryCreates a new temporary directory in order to partition the bootstrap samples, that need to be pre-loaded, from the rest of the sample data
Copy-BootstrapInterchangeFilesCopies files needing to be loaded without authorization using the "Bootstrap Descriptors and EdOrgs" claim set which uses the NoFurtherAuthorization strategy
Copy-SampleInterchangeFilesCopies the remaining sample files. These will be loaded with the "Ed-Fi Sandbox" claim set.
Invoke-SetTestHarnessConfigMerges the separate descriptors files into one and places them into the same directory as the other sample xml.
Add-RandomKeySecretAdds random key/secret. One ApiClient for the bootstrap samples and one for the remaining sample files.
Invoke-RestoreLoadToolsPackagesRestores packages for the Load Tools solution which includes the EdFi.BulkLoadClient.Console 
Invoke-BuildLoadToolsUses MSBuild to build the Load Tools Solution which includes the EdFi.BulkLoadClient.Console 
New-DatabaseTemplateThe new database where the sample data will be loaded into.
Invoke-StartTestHarnessStarts the in-memory ODS/API
Invoke-LoadBootstrapDataLoads any sample data needing to be pre-loaded without authorization
Invoke-LoadSampleDataLoads the remaining sample data through normal authorization channels
Stop-TestHarnessStops the in-memory ODS/API
Backup-DatabaseTemplateCreates a backup of the new populated template at <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.bak
New-DatabaseTemplateNuspecCreates a nuspec template file for the populated template at <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.nuspec


The Initialize-PopulatedTemplate command has a few parameters to be aware of, summarized in the following table:

ParameterDescription

samplePath

An absolute path to the folder to load samples from, for example: C:\MySampleXmlData\.
Also supports specific version folders of the Data Standard repository, for example: C:\Ed-Fi-Standard\v3.2\ or C:\Ed-Fi-Standard\v2.2\. 

noExtensions

Ignores any extension sources when running the sql scripts against the database.

noValidation

Disables xml validation steps. Specifically the "Validate-Descriptors" and "Validate-Core" tasks are skipped.

engine

The database engine provider, either 'SQLServer' or 'PostgreSQL'.


Once you understand the parameters above, you're ready to run the Initialize-PopulatedTemplate command. In this example we are using the Ed-Fi-Standard repository as our sample folder. Since this is a common use case the script is set up to handle its particular folder structure. A simpler folder setup with all XML files in a single folder is also supported by the script.

Executing the Initialize-PopulatedTempate Script
PS C:\> Initialize-PopulatedTemplate -samplePath "C:\Ed-Fi-Standard\v3.2\"

apiClientNameBootstrap         BulkLoadClientBootstrap
apiClientNameSandbox           BulkLoadClientSandbox
apiUrlBase                     http://localhost:8765
apiUrlData                     http://localhost:8765/data/v3
apiUrlMetadata                 http://localhost:8765/metadata
apiUrlOAuth                    http://localhost:8765/oauth
apiYear                        2020
ArtifactSources                {GrandBend, Homograph, Sample}
buildConfiguration             Debug
bulkLoadBootstrapInterchanges  {InterchangeDescriptors, InterchangeStandards, InterchangeEducationOrganization}
...

The script will run through the tasks noted above. The process will take a few minutes to complete.

Initialize-PopulatedTemplate Script Results
...

Duration  Task
--------  ----
00:00.04  New-TempDirectory
00:12.60  Invoke-SampleXmlValidation
00:34.71  New-DatabaseTemplate
00:00.09  Invoke-SetTestHarnessConfig
00:04.43  Invoke-RestoreLoadToolsPackages
00:07.79  Invoke-BuildLoadTools
00:00.58  Copy-BootstrapInterchangeFiles
00:00.66  Copy-SampleInterchangeFiles
00:00.40  Add-RandomKeySecret
00:12.43  Invoke-StartTestHarness
01:33.46  Invoke-LoadBootstrapData
08:48.07  Invoke-LoadSampleData
00:00.04  Stop-TestHarness
00:05.65  Backup-DatabaseTemplate
00:00.11  New-DatabaseTemplateNuspec
-         -
11:41.25  Initialize-PopulatedTemplate


PS C:\>

You should see the above task output when the script has finished successfully.


Option 2. Creating a NuGet Package

Before you begin:

  • This example assumes you have SQL Server Management Studio (SSMS). You can find instructions for installing SSMS in the Step 3. Install and Configure Required Software step of the Getting Started guide for the Ed-Fi ODS / API.
  • This example assumes you have NuGet CLI tool. You can follow the instructions for Installing Nuget Client Tools.
  • This example assumes you have access to a MyGet feed. You can follow MyGet's instructions for Getting Started with NuGet.

Step 1: Create a Database Backup File from SQL Server Management Studio (SSMS)

If you have already created a backup using the Initialize-PopulatedTemplate script, you can skip this step.

Within SSMS, select the database you would like to back up, select Tasks Back Up...

In the Back Up Database dialog, select the default backup destination, click Remove, then click Add to create the backup.

In the Select Backup Destination Dialog, select ...

The database must be placed in <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database and the file name must end in .bak in order for the initialize development and deploy scripts to pick it up.

Step 2: Create a Nuspec File

If you used the Initialize-PopulatedTemplate script, the nuspec file has already been created. You can also create a new one by running the New-PopulatedTemplateNuspec command from the create populated template module or by running nuget spec.

The nuspec file created by the New-PopulatedTemplateNuspec script will be created at <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.nuspec.

Populated.Template.nuspec
<?xml version="1.0"?>
<package>
  <metadata>
    <id>$id$</id>
    <version>$version$</version>
    <title>$title$</title>
    <authors>$authors$</authors>
    <owners>$owners$</owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>$description$</description>
    <releaseNotes>
    </releaseNotes>
    <copyright>$copyright$</copyright>
    <tags>
    </tags>
  </metadata>
  <files>
    <file src="<source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.bak" target="." />
  </files>
</package>


The contents of the Populated.Template.nuspec file needs to be modified with your information. The default information is shown below as an example of the structure:

Populated.Template.nuspec
<?xml version="1.0"?>
<package>
  <metadata>
    <id>Populated.Template</id>
    <version>1.0.0</version>
	<title>Populated.Template</title>
    <authors>Ed-Fi Alliance</authors>
    <owners>Ed-Fi Alliance</owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>EdFi Populated Database Template</description>
    <releaseNotes>
	</releaseNotes>
    <copyright>Copyright ©Ed-Fi Alliance, LLC. 2019</copyright>
  </metadata>
  <files>
    <file src="<source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.bak" target="." />
  </files>
</package>

Once you have your information in your nuspec file, you can navigate to the <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\ folder and execute nuget pack.

NuGet CLI Pack Command
PS C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database> nuget pack
Attempting to build package from 'Populated.Template.nuspec'.
Successfully created package 'C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.1.0.0.nupkg'.

When successful, you should see something similar to above. Additional detail:

Step 3: Upload a Package to MyGet

Now we can nuget push to add the package to the MyGet feed. For this we will need the API Key and the feed URL. You can find these under "Feed Details" when looking at your feed on MyGet.org.

The command should look as follows: nuget push Populated.Template.1.0.0.nupkg <apikey> -source <MyGet Feed URL>

Nuget CLI Push Command
PS C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database> nuget push .\Populated.Template.1.0.0.nupkg <APIKEY> -source https://www.myget.org/F/<FeedName>/api/v2/index.json
Pushing Populated.Template.1.0.0.nupkg to 'https://www.myget.org/F/<FeedName>/api/v2/package'...
  PUT https://www.myget.org/F/<FeedName>/api/v2/package/
  Created https://www.myget.org/F/<FeedName>/api/v2/package/ 19188ms
Your package was pushed.

When successful, you should see something similar to the following:

In your MyGet feed you should see your new package. Additional detail:

Option 3. Creating a Custom Populated Template Source Script

Step 1: Create a Script

In this example we will create a new script called NewPopulatedTemplate.ps1 with the contents:

NewPopulatedTemplate.ps1
$params = @{
    packageName    = "Populated.Template"
    packageVersion = "1.0.0"
    packageSource  = "https://www.myget.org/F/<FeedName>/"
}
& "$PSScriptRoot\..\Modules\get-populated-from-nuget.ps1" @params

This script must be placed in the folder: <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Scripts\.

In our example, we are reusing the <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Modules\get-populated-from-nuget.ps1 script. This script will download the specified NuGet package name and version from the specified source and place it in the Database folder. Alternatively, we could have written a custom script that would get a .bak file from the web or from a shared drive. The source selection script's only requirement is that it places a .bak file in the folder: <source directory>\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\. How it accomplishes this is left up to the developer. You can have multiple scripts saved here but only the ones specified in the config files in the next step will be run.

The Initialize-DevelopmentEnvironment and Sandbox Deploy scripts will use the first .bak file it finds in the Database folder. Having anything else in the Database folder is not recommended.

Step 2: Update the Config File

Add the "sandbox:populatedTemplateScript" key to the appSettings section of the file: <source directory>\Ed-Fi-ODS-Implementation\Application\EdFi.Ods.WebApi\Web.Base.config.

Web.config Example
  ...
  <appSettings>
    <add key="sandbox:populatedTemplateScript" value="NewPopulatedTemplate.ps1" />
  </appSettings>
  ...

This tells Initialize-DevelopmentEnvironment which source selection script to run. If no script is provided or the name specified does not exist the Initialize-DevelopmentEnvironment will fail when trying to reset the populated template.

If you have an environment (usually staging or QA) that also deploys the populated template you will need to add the same key to the file: <source directory>\Ed-Fi-ODS-Implementation\Scripts\NuGet\EdFi.RestApi.Databases\Databases.config.

Step 3: Run Reset-PopulatedTemplate

In a PowerShell session navigate to <source directory>\Ed-Fi-ODS-Implementation\.

Execute .\Initialize-PowershellForDevelopment.ps1.

Import the Initiallize Development Module
PS C:\Ed-Fi-ODS-Implementation\> .\Initialize-PowershellForDevelopment.ps1
Importing Module: InitializeDevelopmentEnvironment.psm1
Using repositories from environment variable: Ed-Fi-Common;Ed-Fi-Ods;Ed-Fi-ODS-Implementation

Execute Reset-PopulatedTemplate.

Execute Reset-PopulatedTemplate
PS C:\Ed-Fi-ODS-Implementation\> Reset-PopulatedTemplate
Invoking Task: Reset-PopulatedTemplate
Found populated template nuget package: Populated.Template v1.0.0
Successfully added Populated.Template.1.0.0.bak to populated template source folder
...
Dropping the EdFi_Ods_Populated_Template Database.
Using backup C:\edfi\Ed-Fi-ODS-Implementation\DatabaseTemplate\Database\Populated.Template.1.0.0.bak
...
Task                    TotalMinutes
----                    ------------
Reset-PopulatedTemplate       3.78

When the process successfully completes, you should see something similar to above.


Downloads

The following GitHub link contains the sample XML files and the as-shipped Ed-Fi Descriptor XML from Ed-Fi-Standard repository.