How To: Load the TPDM Sample XML Data using Bulk Load Client Utility

This article describes the steps needed to populate an Ed-Fi ODS with sample TPDM XML data, using the Ed-Fi Bulk Load Client utility. It assumes you have deployed TPDM plugin, see How To: Deploy TPDM As Dynamic Plugin for details. This process works very similarly to How To: Load the ODS with Sample XML Data using Bulk Load Client Utility. Walk through those steps first to get familiar with the process.

The steps can be summarized as:

Step 1. Build the Ed-Fi Bulk Load Client

• Ensure that you have an instance of the Ed-Fi ODS / API running locally that has been set up following the Getting Started - Installation Steps.

• Within Visual Studio, Open Ed-Fi-ODS\Utilities\DataLoading\LoadTools.sln.

• Select Build > Build Solution.

• You can verify that the console application has been built by browsing to Ed-Fi-ODS\Utilities\DataLoading\EdFi.BulkLoadClient.Console\bin\Debug\netcoreapp3.1\EdFi.BulkLoadClient.Console.exe

Step 2. Download Scripts and Sample Data

• Download and Extract Ed-Fi-TPDMDataLoad.zip to a local folder. We recommend C:\Ed-Fi-TPDMDataLoad.
  Ed-Fi-TPDMDataLoad.zip contains all the scripts and directory structure used in this how-to article.

• Download the TPDM Sample XML to Ed-Fi-TPDMDataLoad\Sample XML.

• Download the Descriptors to Ed-Fi-TPDMDataLoad\Bootstrap.

• Copy all XSD schema files from Ed-Fi-ODS\Application\EdFi.Ods.Standard\Artifacts\Schemas and the Downloaded TPDM Schemas to Ed-Fi-TPDMDataLoad\Schemas.

• Copy the file Sample XML\EducationOrganization.xml to the Bootstrap\ folder:

Step 3. Create a Populated Sandbox

• Open Ed-Fi-ODS-Implementation\Application\EdFi.Ods.SandboxAdmin\appsettings.development.json.

• Add a populated sandbox by inserting the below configuration in the 'sandboxes' setting.

  "Sandboxes": { ... "Populated TPDM Sandbox": { "Key": "emptyKey", "Type": "Sample", "Secret": "emptysecret", "Refresh": "false" } }

• Add the TPDM namespace prefix by inserting the below line in the 'Namespaceprefixes' setting.

  "NamespacePrefixes": [ ... "uri://tpdm.ed-fi.org" ]

• Restart the Sandbox Administration website.

Step 4. Update Claim Set to Load Descriptors and Education Organizations

• Execute EnableBootstrapClaimset.sql by executing the file against the EdFi_Admin database using any database query tool. Use the version appropriate to your database.

  EnableBootstrapClaimset.sql

  -- Sql Server UPDATE a SET a.ClaimSetName = 'Bootstrap Descriptors and EdOrgs' FROM dbo.Applications a INNER JOIN dbo.ApiClients ac ON a.ApplicationId = ac.Application_ApplicationId WHERE ac.Name = 'Populated TPDM Sandbox' -- Postgres do $$ begin update dbo.Applications set ClaimSetName = 'Bootstrap Descriptors and EdOrgs' from dbo.Applications a inner join dbo.ApiClients ac on a.ApplicationId = ac.Application_ApplicationId where ac.Name = 'Populated TPDM Sandbox'; end $$

Step 5. Run the Bootstrap Script to Load Descriptors and Education Organizations

• Open a PowerShell session.

• Navigate to Ed-Fi-TPDMDataLoad directory (e.g., C:\Ed-Fi-TPDMDataLoad).

• Execute LoadBootstrapData.ps1.

  The script will run the Bulk Load Client loading data from the Bootstrap folder to the TPDM Populated Sandbox Database.

Step 6. Update Claim Set to Load Sample Data

• Execute EnableSandboxClaimsetAndEducationOrganization.sql by executing the file against the EdFi_Admin database using any database query tool. Use the version appropriate to your database.

  EnableSandboxClaimsetAndEducationOrganization.sql

  -- Sql Server DECLARE @EducationOrganizations TABLE ( EducationOrganizationId int NOT NULL ) INSERT @EducationOrganizations (EducationOrganizationId) VALUES (1), (2), (3), (4), (5), (6), (7), (6000203) DECLARE @apiClientId INT DECLARE @applicationId INT SELECT @apiClientId = ApiClientId, @applicationId = Application_ApplicationId FROM dbo.ApiClients WHERE Name = 'Populated TPDM Sandbox' UPDATE dbo.Applications SET ClaimSetName = 'Ed-Fi Sandbox' WHERE ApplicationId = @applicationId INSERT INTO dbo.ApplicationEducationOrganizations (EducationOrganizationId, Application_ApplicationId) SELECT EducationOrganizationId, @ApplicationId FROM @EducationOrganizations INSERT INTO dbo.ApiClientApplicationEducationOrganizations (ApiClient_ApiClientId,ApplicationEducationOrganization_ApplicationEducationOrganizationId) SELECT @apiClientId, aeo.ApplicationEducationOrganizationId FROM dbo.ApplicationEducationOrganizations aeo INNER JOIN @EducationOrganizations eo ON aeo.EducationOrganizationId = eo.EducationOrganizationId WHERE aeo.Application_ApplicationId = @applicationId -- Postgres do $$ declare ApiClientId int; declare AppId int; begin drop table if exists EducationOrganizations; create temp table EducationOrganizations ( EducationOrganizationId int not null ); insert into EducationOrganizations (EducationOrganizationId) values (1), (2), (3), (4), (5), (6), (7), (6000203); select a.ApiClientId, a.Application_ApplicationId into ApiClientId, AppId from dbo.ApiClients a where Name = 'Populated TPDM Sandbox'; update dbo.Applications set ClaimSetName = 'Ed-Fi Sandbox' where ApplicationId = AppId; insert into dbo.ApplicationEducationOrganizations (EducationOrganizationId, Application_ApplicationId) select EducationOrganizationId, AppId from EducationOrganizations; insert into dbo.ApiClientApplicationEducationOrganizations (ApiClient_ApiClientId,applicationedorg_applicationedorgid) select ApiClientId, aeo.ApplicationEducationOrganizationId from dbo.ApplicationEducationOrganizations aeo inner join EducationOrganizations eo on aeo.EducationOrganizationId = eo.EducationOrganizationId where aeo.Application_ApplicationId = AppId; end $$

Step 7. Run the Bootstrap Script to Load Sample Data

• Open a PowerShell session.

• Navigate to Ed-Fi-TPDMDataLoad directory (e.g., C:\Ed-Fi-TPDMDataLoad).

• Execute LoadSampleData.ps1.

  The script will run the Bulk Load Client to load data from the Sample XML folder to the Populated TPDM Sandbox database.