Versions Compared
Key
- This line was added.
- This line was removed.
- Formatting was changed.
This article describes the steps needed to populate a sandbox Ed-Fi ODS with TPDM descriptors, using the Ed-Fi Bulk Load Client utility for Ed-Fi ODS / API v5.2.
If you need to load TPDM Sample XML data as well as descriptors, How To: Load the TPDM Sample XML Data using Bulk Load Client Utility will walk you through the steps to load descriptors plus XML Sample data.
The steps can be summarized as:
Table of Contents | ||
---|---|---|
|
Step 1. Download Scripts and Descriptor Data
Download and Extract Ed-Fi-TPDMDataLoad.zip to a local folder. We recommend you use C:\Ed-Fi-TPDMDataLoad. The Ed-Fi-TPDMDataLoad.zip contains all the scripts, the Ed-Fi Bulk Load Client, Descriptors and the directory structure needed for this how-to article.
- (Optional but recommended) Update the Descriptors in the C:\Ed-Fi-TPDMDataLoad\Bootstrap folder. The .zip file above contains a snapshot of the TPMD descriptors. The "Descriptors" link on the top right of this article is the live repository that contains the latest and greatest updated descriptors. We recommend you download and update the Descriptors located in the Ed-Fi-TPDMDataLoad\Bootstrap folder with the latest ones from the GitHub repository.
Click the following link to learn more about the Ed-Fi Bulk Load Client .
Step 2. Create an API Client
The following SQL will create the necessary components required to load descriptors without changes, but it is highly recommended that you change the following values to fit the installation
- Copy the SQL code from the code block below
- Update the values for UserFullName, UserEmailAddress, ApplicationName, VendorName and ApiClientName to fit the institution, vendor and user that the client will be used for.
- Update the Key and Secret values
- Update the ClaimSetName if using a claimset other than the Ed-Fi-Sandbox
- Execute the SQL
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
DECLARE @UserFullName varchar(150) = 'Local Test User' DECLARE @UserEmailAddress varchar(150) = 'localtest@ed-fi.org' DECLARE @ApplicationName nvarchar(255) = 'Local Test Application' DECLARE @VendorName nvarchar(150) = 'Local Test Vendor' DECLARE @ApiClientName nvarchar(50) = 'Local Test Api Client' DECLARE @EducationOrganizationId int = 255901 --Must be an ed-org in the ODS DECLARE @Key nvarchar(50) = 'testkey' DECLARE @Secret nvarchar(100) = 'testsecret' DECLARE @ClaimSetName nvarchar(255) = 'Ed-Fi Sandbox' DECLARE @NamespacePrefix nvarchar (255) = 'uri://ed-fi.org' DECLARE @TPDMNamespacePrefix nvarchar (255) = 'uri://tpdm.ed-fi.org' DECLARE @IsPopulatedSandbox bit = 1 -- For Shared/YearSpecific/DistrictSpecific DECLARE @UseSandbox bit = 0 -- 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, @TPDMNamespacePrefix) -- 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 |
NOTE: That every time you update anything related to API Client Claims you should restart your IIS so that the Cached Claims get flushed out.
To do this open a PowerShell window as Administrator and execute the command PS C:\> IISRESET
Step 3. Configure & Run the Bootstrap Script to Load Descriptors
- Use Windows File Explorer and navigate to the C:\Ed-Fi-TPDMDataLoad
- Use your favorite text editor and open the LoadBootstrapData.ps1 file.
- Update the $options = @(... variable to match your configuration parameters.
- You will need the base URL of your API,
and the key and secret that you setup above for the "TPDM-Sandbox" sandbox.
Info |
---|
$options = @( |
- 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 TPDM Descriptor data from the Bootstrap folder to the TPDM-Sandbox Database.
Ensure that the TPDM descriptors made it in to your ODS by running these simple SQL Queries:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT COUNT(*) as AllDescriptorCount from edfi.Descriptor; SELECT COUNT(*) as TPDMDescriptorCount from edfi.Descriptor where Namespace like '%tpdm%'; |
*The descriptor count should be greater than ~2968 and there should be around ~474 TDM Descriptors added.
Congratulations you now have an Ed-Fi ODS with TPDM Descriptors!
Panel | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||
The following link contains the scripts and directory setup used in this how-to article. The following GitHub links contain the latest as-shipped TPDM Descriptor XML. |