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

This article describes the steps needed to populate an Ed-Fi ODS with sample XML data, using the Ed-Fi Bulk Load Client utility.

The steps can be summarized as:

Detail on each step follows.

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 - Source Code Installation.
• 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-SampleDataLoad.zip to a local folder. We recommend C:\Ed-Fi-SampleDataLoad.
  Ed-Fi-SampleDataLoad.zip contains all the scripts and directory structure used in this how-to article.

• Download the Sample XML to Ed-Fi-SampleDataLoad\Sample XML

• Download the Descriptors to Ed-Fi-SampleDataLoad\Bootstrap

• Move the following files from Ed-Fi-SampleDataLoad\Sample XML to Ed-Fi-SampleDataLoad\Bootstrap

  • Standards.xml

  • EducationOrganization.xml

  • CreditCategoryDescriptor.xml
  • IndicatorDescriptor.xml
  • IndicatorGroupDescriptor.xml
  • IndicatorLevelDescriptor.xml
  • ProgramCharacteristicDescriptor.xml

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. Create API Client for Empty Sandbox

• Execute CreateApiClientforEmptySandbox.sql by opening the file in SSMS and clicking Execute. This will create claims needed to access EdFi_Ods_Sandbox_Empty ODS.

  CreateApiClienforEmptySandbox.sql

  USE [EdFi_Admin]
  GO
  
  DECLARE @VendorName nvarchar(150) = 'Empty Test Vendor'
  DECLARE @NamespacePrefix nvarchar (255) = 'uri://ed-fi.org'
  DECLARE @NamespacePrefixEdu nvarchar (255) = 'uri://gbisd.edu'
  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 @EducationOrganizationId int = 255901 --Must be an ed-org in the ODS
  DECLARE @Key nvarchar(50) = 'empty'
  DECLARE @Secret nvarchar(100) = 'emptySecret'
   
  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) 
  -- 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
   
  IF (@EducationOrganizationId IS NOT NULL)
  BEGIN
      -- 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)
      VALUES (@EducationOrganizationId, @ApplicationId)
      SELECT @ApplicationEducationOrganizationId = SCOPE_IDENTITY()
   
      INSERT INTO [dbo].[ApiClientApplicationEducationOrganizations] (ApplicationEducationOrganization_ApplicationEducationOrganizationId, ApiClient_ApiClientId)
      VALUES (@ApplicationEducationOrganizationId, @ApiClientId)
  END
  
  

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

• Open a PowerShell session.
• Navigate to Ed-Fi-SampleDataLoad directory (e.g., C:\Ed-Fi-SampleDataLoad).
• Execute LoadBootstrapData.ps1.

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

  If your local copy of EdFi.BulkLoadClient.Console.exe is in a different location than the root of the drive, you can modify the LoadBootstrapData.ps1 script to adjust. The script contains a parameter that defines the local path to the Ed-Fi-ODS repository. This is set by default to C:\Ed-Fi-ODS but can be changed to be appropriate for your environment.

Step 6. Update Claim Set to Load Sample Data

• Execute EnableSandboxClaimsetAndEducationOrganization.sql by opening the file in SSMS and clicking Execute. This will change the claim set for the empty sandbox database you created in step 4 to the Ed-Fi Sandbox claimset.

  EnableSandboxClaimsetAndEducationOrganization.sql

  USE [EdFi_Admin]
  GO
  
  UPDATE [dbo].[Applications]
  SET [ClaimSetName] = 'Ed-Fi Sandbox'
  WHERE [ApplicationName] = 'Default Sandbox Application Empty'
  GO

Step 7. Run the Bootstrap Script to Load Sample Data

• Restart the instance of the Ed-Fi ODS / API running locally again to refresh the claims.
• Open a PowerShell session.
• Navigate to Ed-Fi-SampleDataLoad directory (e.g., C:\Ed-Fi-SampleDataLoad).
• Execute LoadSampleData.ps1.

  The script will run the Bulk Load Client to load data from the Sample XML folder to the Empty Sandbox Database.

  If your local copy of EdFi.BulkLoadClient.Console.exe is in a different location than the root of the drive, you can modify the LoadSampleData.ps1 script to adjust. The script contains a parameter that defines the local path to the Ed-Fi-ODS repository. This is set by default to C:/Ed-Fi-ODS but can be changed to be appropriate for your environment.