How To: Load the TPDM Sample XML Data using Bulk Load Client Utility
- Ian Christopher (Deactivated)
- Vinaya Mayya
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. 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. Install 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 - Source Code Installation.
Ensure .NET 6.0 SDK is installed.
- Add Ed-Fi package source by running the following command in PowerShell: Install the Ed-Fi Bulk Load Client
if (-not [Net.ServicePointManager]::SecurityProtocol.HasFlag([Net.SecurityProtocolType]::Tls12)) { [Net.ServicePointManager]::SecurityProtocol += [Net.SecurityProtocolType]::Tls12 } if(-not (Get-PackageSource -ProviderName NuGet | Where-Object -Property Name -eq "Ed-FiAzureArtifacts")){ Write-Host "Registering Ed-Fi Package Source..." Register-PackageSource -Name Ed-FiAzureArtifacts ` -Location "https://pkgs.dev.azure.com/ed-fi-alliance/Ed-Fi-Alliance-OSS/_packaging/EdFi/nuget/v3/index.json" ` -ProviderName NuGet Write-Host "Ed-Fi package source configured" }
Install the Ed-Fi Bulk Load Client from a PowerShell prompt using the following command:
Install the Ed-Fi Bulk Load Clientc:\>mkdir {YourInstallFolder} c:\> dotnet tool install EdFi.Suite3.BulkLoadClient.Console --version 6.1.24 --tool-path {YourInstallFolder} You can invoke the tool using the following command: EdFi.BulkLoadClient.Console Tool 'edfi.suite3.bulkloadclient.console' (version '6.1.24') was successfully installed.
- You can verify that the EdFi.BulkLoadClient.Console.exe is available by browsing to this location {YourInstallFolder}\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 Sample XML to Ed-Fi-TPDMDataLoad\Sample XML
Download the Descriptors to Ed-Fi-TPDMDataLoad\Bootstrap
Move the following files from Ed-Fi-TPDMDataLoad\Sample XML to Ed-Fi-TPDMDataLoad\Bootstrap
Standards.xml
EducationOrganization.xml
- CreditCategoryDescriptor.xml
- IndicatorDescriptor.xml
- IndicatorGroupDescriptor.xml
- IndicatorLevelDescriptor.xml
- ProgramCharacteristicDescriptor.xml
Download the TPDM Sample XML to Ed-Fi-TPDMDataLoad\Sample XML\TPDM.
Download the TPDM Descriptors to Ed-Fi-TPDMDataLoad\Bootstrap\TPDM.
- Copy the file TPDM Sample XML\EducationOrganization.xml to the Ed-Fi-TPDMDataLoad\Bootstrap\TPDM folder:
Note that the TPDM Sample data depends on the Grand Bend, so we are loading them together in this how-to article.
The Bootstrap folder is used to load the necessary Descriptors and Education Organization used by the ODS / API. Since they require a special claimset (enabled in Step 4, below), they must be loaded separately from the other sample files.
Step 3. Create an Empty Sandbox
- Open a PowerShell session.
- Navigate to \Ed-Fi-ODS-Implementation\ directory
- Execute .\Initialize-PowershellForDevelopment.ps1 and then Reset-EmptySandboxDatabase.
The script will create EdFi_Ods_Sandbox_Empty Database.
Step 4. Update Claim Set to Load Descriptors and Education Organizations
Execute CreateApiClienforEmptySandbox.sql by executing the file against the EdFi_Admin database using any database query tool. Use the version appropriate to your database.
SQL ServerCreateApiClienforEmptySandbox.sqlDECLARE @VendorName nvarchar(150) = 'Empty Test Vendor' DECLARE @NamespacePrefix nvarchar (255) = 'uri://ed-fi.org' DECLARE @NamespacePrefixEdu nvarchar (255) = 'uri://gbisd.edu' DECLARE @NamespacePrefixTPDM nvarchar (255) = 'uri://tpdm.ed-fi.org' DECLARE @UserFullName varchar(150) = 'Empty Test User' DECLARE @UserEmailAddress varchar(150) = 'emptytest@ed-fi.org' DECLARE @ApplicationName nvarchar(255) = 'Default Sandbox Application Empty' DECLARE @ClaimSetName nvarchar(255) = 'Bootstrap Descriptors and EdOrgs' DECLARE @ApiClientName nvarchar(50) = 'Empty Demonstration Sandbox' DECLARE @Key nvarchar(50) = 'empty' DECLARE @Secret nvarchar(100) = 'emptySecret' DECLARE @EducationOrganizations TABLE ( EducationOrganizationId int NOT NULL ) DECLARE @IsPopulatedSandbox bit = 0 -- For Shared/YearSpecific/DistrictSpecific -- For Sandbox DECLARE @UseSandbox bit = 1 DECLARE @VendorId int DECLARE @UserId int DECLARE @ApplicationId int DECLARE @ApplicationEducationOrganizationId int DECLARE @ApiClientId int -- Clear @IsPopulatedSandbox if not using sandbox IF (@UseSandbox = 0) SET @IsPopulatedSandbox = 0 -- Ensure Vendor exists SELECT @VendorId = VendorId FROM [dbo].[Vendors] WHERE VendorName = @VendorName IF(@VendorId IS NULL) BEGIN INSERT INTO [dbo].[Vendors] (VendorName) VALUES (@VendorName) SET @VendorId = SCOPE_IDENTITY() END -- Ensure correct namespace prefixes are set up DELETE FROM [dbo].[VendorNamespacePrefixes] WHERE Vendor_VendorId = @VendorId INSERT INTO [dbo].[VendorNamespacePrefixes] (Vendor_VendorId, NamespacePrefix) VALUES (@VendorId, @NamespacePrefix) INSERT INTO [dbo].[VendorNamespacePrefixes] (Vendor_VendorId, NamespacePrefix) VALUES (@VendorId, @NamespacePrefixEdu) INSERT INTO [dbo].[VendorNamespacePrefixes] (Vendor_VendorId, NamespacePrefix) VALUES (@VendorId, @NamespacePrefixTPDM) -- Ensure User exists for test Vendor SELECT @UserId = UserId FROM [dbo].[Users] WHERE FullName = @UserFullName AND Vendor_VendorId = @VendorId IF(@UserId IS NULL) BEGIN INSERT INTO [dbo].[Users] (Email, FullName, Vendor_VendorId) VALUES (@UserEmailAddress, @UserFullName, @VendorId) SET @UserId = SCOPE_IDENTITY() END ELSE BEGIN UPDATE [dbo].[Users] SET Email = @UserEmailAddress WHERE UserId = @UserId END -- Ensure Application exists SELECT @ApplicationId = ApplicationId FROM [dbo].[Applications] WHERE ApplicationName = @ApplicationName AND Vendor_VendorId = @VendorId IF (@ApplicationId IS NULL) BEGIN INSERT INTO [dbo].[Applications] (ApplicationName, Vendor_VendorId, ClaimSetName) VALUES (@ApplicationName, @VendorId, @ClaimSetName) SET @ApplicationId = SCOPE_IDENTITY() END ELSE BEGIN UPDATE [dbo].[Applications] SET ClaimSetName = @ClaimSetName WHERE ApplicationId = @ApplicationId END -- Ensure ApiClient exists SELECT @ApiClientId = ApiClientId FROM [dbo].[ApiClients] WHERE Application_ApplicationId = @ApplicationId AND [Name] = @ApiClientName IF(@ApiClientId IS NULL) BEGIN INSERT INTO [dbo].[ApiClients] ([Key], [Secret], [Name], IsApproved, UseSandbox, SandboxType, Application_ApplicationId, User_UserId, SecretIsHashed) VALUES (@Key, @Secret, @ApiClientName, 1, @UseSandbox, @IsPopulatedSandbox, @ApplicationId, @UserId, 0) SET @ApiClientId = SCOPE_IDENTITY() END ELSE BEGIN UPDATE [dbo].[ApiClients] SET [Key] = @Key, [Secret] = @Secret, UseSandbox = @UseSandbox, SandboxType = @IsPopulatedSandbox, User_UserId = @UserId, SecretIsHashed = 0 WHERE ApiClientId = @ApiClientId END INSERT @EducationOrganizations (EducationOrganizationId) VALUES (5), (6), (7), (255901) -- Clear all education organization links for the selected application DELETE acaeo FROM dbo.ApiClientApplicationEducationOrganizations acaeo INNER JOIN dbo.ApplicationEducationOrganizations aeo ON acaeo.ApplicationEducationOrganization_ApplicationEducationOrganizationId = aeo.ApplicationEducationOrganizationId WHERE aeo.Application_ApplicationId = @ApplicationId DELETE FROM [dbo].[ApplicationEducationOrganizations] WHERE Application_ApplicationId = @ApplicationId -- Ensure correct education organizations are set up 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
PostgreSQLCreateApiClienforEmptySandbox.sqlDO $$ DECLARE vendor_name varchar(150) := 'Empty Test Vendor'; namespace_prefix varchar(255) = 'uri://ed-fi.org'; namespace_prefix_edu varchar(255) = 'uri://gbisd.edu'; namespace_prefix_tpdm varchar(255) = 'uri://tpdm.ed-fi.org'; user_full_name varchar(150) = 'Local Test User'; user_email_address varchar(150) = 'emptytest@ed-fi.org'; application_name varchar(255) = 'Default Sandbox Application Empty'; claimset_name varchar(255) = 'Bootstrap Descriptors and EdOrgs'; api_client_name varchar(50) = 'Empty Demonstration Sandbox'; client_key varchar(50) = 'Empty'; client_secret varchar(100) = 'emptySecret'; is_populated_sandbox INT = 0; -- For Shared/YearSpecific/DistrictSpecific use_sandbox boolean = False; -- For Sandbox --use_sandbox bit = 1; vendor_id int; user_id int; application_id int; application_education_organization_id int; api_client_id int; BEGIN -- Clear is_populated_sandbox if not using sandbox IF NOT use_sandbox THEN SELECT 0 INTO is_populated_sandbox; END IF; -- Ensure Vendor exists SELECT VendorId INTO vendor_id FROM dbo.Vendors WHERE VendorName = vendor_name; IF(vendor_id IS NULL) THEN INSERT INTO dbo.Vendors (VendorName) VALUES (vendor_name); SELECT LASTVAL() INTO vendor_id; END IF; -- Ensure correct namespace prefixes are set up DELETE FROM dbo.VendorNamespacePrefixes WHERE Vendor_VendorId = vendor_id; INSERT INTO dbo.VendorNamespacePrefixes (Vendor_VendorId, NamespacePrefix) VALUES (vendor_id, namespace_prefix); INSERT INTO dbo.VendorNamespacePrefixes (Vendor_VendorId, NamespacePrefix) VALUES (vendor_id, namespace_prefix_edu); INSERT INTO dbo.VendorNamespacePrefixes (Vendor_VendorId, NamespacePrefix) VALUES (vendor_id, namespace_prefix_tpdm); -- Ensure User exists for test Vendor SELECT UserId INTO user_id FROM dbo.Users WHERE FullName = user_full_name AND Vendor_VendorId = vendor_id; IF(user_id IS NULL) THEN INSERT INTO dbo.Users (Email, FullName, Vendor_VendorId) VALUES (user_email_address, user_full_name, vendor_id); SELECT LASTVAL() INTO user_id; ELSE UPDATE dbo.Users SET Email = user_email_address WHERE UserId = user_id; END IF; -- Ensure Application exists SELECT ApplicationId INTO application_id FROM dbo.Applications WHERE ApplicationName = application_name AND Vendor_VendorId = vendor_id; IF (application_id IS NULL) THEN INSERT INTO dbo.Applications (ApplicationName, Vendor_VendorId, ClaimSetName) VALUES (application_name, vendor_id, claimset_name); SELECT LASTVAL() INTO application_id; ELSE UPDATE dbo.Applications SET ClaimSetName = claimset_name WHERE ApplicationId = application_id; END IF; -- Ensure ApiClient exists SELECT ApiClientId INTO api_client_id FROM dbo.ApiClients WHERE Application_ApplicationId = application_id AND Name = api_client_name; IF(api_client_id IS NULL) THEN INSERT INTO dbo.ApiClients (Key, Secret, Name, IsApproved, UseSandbox, SandboxType, Application_ApplicationId, User_UserId, SecretIsHashed) VALUES (client_key, client_secret, api_client_name, TRUE, use_sandbox, is_populated_sandbox, application_id, user_id, FALSE); SELECT LASTVAL() INTO api_client_id; ELSE UPDATE dbo.ApiClients SET Key = client_key, Secret = client_secret, UseSandbox = use_sandbox, SandboxType = is_populated_sandbox, User_UserId = user_id, SecretIsHashed = FALSE WHERE ApiClientId = api_client_id; END IF; DROP TABLE IF EXISTS EducationOrganizations; CREATE TEMP TABLE EducationOrganizations ( EducationOrganizationId int not null ); INSERT INTO EducationOrganizations (EducationOrganizationId) VALUES (5), (6), (7), (255901); DELETE FROM dbo.ApiClientApplicationEducationOrganizations WHERE ApplicationEdOrg_ApplicationEdOrgId IN ( SELECT ApplicationEducationOrganizationId FROM dbo.ApplicationEducationOrganizations WHERE Application_ApplicationId = application_id); DELETE FROM dbo.ApplicationEducationOrganizations WHERE Application_ApplicationId = application_id; INSERT INTO dbo.ApplicationEducationOrganizations (EducationOrganizationId, Application_ApplicationId) SELECT EducationOrganizationId, application_id FROM EducationOrganizations; INSERT INTO dbo.ApiClientApplicationEducationOrganizations (ApiClient_ApiClientId,applicationedorg_applicationedorgid) SELECT api_client_id, aeo.ApplicationEducationOrganizationId FROM dbo.ApplicationEducationOrganizations aeo INNER JOIN EducationOrganizations eo ON aeo.EducationOrganizationId = eo.EducationOrganizationId WHERE aeo.Application_ApplicationId = application_id; 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).
- Open the LoadBootstrapData.ps1 file in notepad or Windows PowerShell ISE and update the $apiLoaderExe variable Value where EdFi.BulkLoadClient.Console is installed in Step 1.
- Example $apiLoaderExe= "{YourInstallFolderFullPath}\EdFi.BulkLoadClient.Console.exe"
- For Postgres Only, Please update Key & Secret "-k", "Empty", "-s", "emptySecret"
- Save and 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 EnableSandboxClaimset.sql by executing the file against the EdFi_Admin database using any database query tool. Use the version appropriate to your database.
SQL ServerEnableSandboxClaimset.sqlUPDATE [dbo].[Applications] SET [ClaimSetName] = 'Ed-Fi Sandbox' WHERE [ApplicationName] = 'Default Sandbox Application Empty' GO
PostgreSQLEnableSandboxClaimset.sqlDO $$ BEGIN UPDATE dbo.Applications SET ClaimSetName = 'Ed-Fi Sandbox' WHERE ApplicationName = 'Default Sandbox Application Empty'; 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).
- Open the LoadSampleData.ps1 file in notepad or Windows PowerShell ISE and update the $apiLoaderExe variable Value where EdFi.BulkLoadClient.Console is installed in Step 1.
- Example $apiLoaderExe= "{YourInstallFolderFullPath}\EdFi.BulkLoadClient.Console.exe"
- For Postgres Only, Please update Key & Secret "-k", "Empty", "-s", "emptySecret"
- Save and Execute LoadSampleData.ps1.
The script will run the Bulk Load Client to load data from the Sample XML folder to the Empty Sandbox database.
The following link contains the scripts and directory setup used in this how-to article.
The following GitHub links contain the sample XML files and the as-shipped Ed-Fi Descriptor XML.
The following GitHub links contain the sample XML files and the as-shipped TPDM Descriptor XML.