Versions Compared
Key
- This line was added.
- This line was removed.
- Formatting was changed.
Info |
---|
This article uses the core sample data released with Ed-Fi Data Standard v3.2.0-c. If you have an extension that extends core entities with additional required fields, the core sample data may fail to fulfill those additional requirements and fail to load. |
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:
Table of Contents | ||
---|---|---|
|
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 - 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\net48netcoreapp3.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
Info |
---|
The Bootstrap folder is used to load the necessary Descriptors and Education Organization used by the ODS / API. These are automatically included in both the Minimal and Populated Sandboxes. Since they require a special claimset (enabled in Step 4, below) they must be loaded separately from the other sample files. |
Step 3.
CreateCreate 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.
Code Block language sql title CreateApiClienforEmptySandbox.sql linenumbers true 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) = 'SIS VendorBootstrap 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
. Update Claim Set to Load Descriptors, Standards and Education OrganizationsExecute EnableBootstrapClaimset.sql by opening the file in SSMS and clicking Execute.
language | sql |
---|---|
title | EnableBootstrapClaimset.sql |
linenumbers | true |
.
[Applications] SET [ClaimSetName] = 'Bootstrap Descriptors and EdOrgs' WHERE [ApplicationName] = 'Default Sandbox Application Empty' GOStep 6.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.
Info 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
76. 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.
Code Block language sql title EnableSandboxClaimsetAndEducationOrganization.sql linenumbers true USE [EdFi_Admin] GO UPDATE [dbo].[Applications] SET [ClaimSetName] = 'Ed-Fi Sandbox' WHERE [ApplicationName] = 'Default Sandbox Application Empty' GO INSERT INTO [dbo].[ApplicationEducationOrganizations] ([EducationOrganizationId], [Application_ApplicationId]) VALUES (255901, ( SELECT [ApplicationId] FROM [dbo].[Applications] WHERE [ApplicationName] = 'Default Sandbox Application Empty')) GO INSERT INTO [dbo].[ApiClientApplicationEducationOrganizations] ([ApiClient_ApiClientId],[ApplicationEducationOrganization_ApplicationEducationOrganizationId]) SELECT [ApiClients].[ApiClientId], [ApplicationEducationOrganizations].[ApplicationEducationOrganizationId] FROM [dbo].[ApiClients] CROSS JOIN [dbo].[Applications] INNER JOIN [dbo].[ApplicationEducationOrganizations] ON [Applications].[ApplicationId] = [ApplicationEducationOrganizations].[Application_ApplicationId] WHERE [ApiClients].[Name] = 'Empty Demonstration Sandbox' AND [Applications].[ApplicationName] = 'Default Sandbox Application Empty'; GO
Info This will change the claim set for the empty sandbox database back to the Ed-Fi Sandbox claimset and add 255901 as an authorized Education Organization. The script assumes that the sandbox name in AdminCredential.config is set to Empty Demonstration Sandbox but can be modified to be appropriate for your environment.
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.
Info 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.
Panel | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||
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. |