Date: Fri, 29 Mar 2024 05:00:06 -0500 (CDT) Message-ID: <374711583.30312.1711706406922@PUBEDFIPRDWEB5.public.local> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_30311_328764695.1711706406920" ------=_Part_30311_328764695.1711706406920 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Operational Contexts allow for mapping between sets of Ed-Fi Descriptors= . In practice, the Ed-Fi ETL internally runs calculations based on Ed-Fi-pr= ovided Descriptors. Therefore, if you have custom Descriptors in use in you= r source ODS, you'll need to map these custom Descriptors to the as-shipped= Ed-Fi Descriptors in order for your data to be included in calculations.= p>
The process outlined below shows how to create an operational context th= at will be recognized by the ETL process, and map custom Descriptors throug= h this operational context.
Fully Custom Descriptors
Fully custom descriptors (i.e., wholly new descriptors created by extens= ion to the ODS) are not supported in the ETL. You do not need to map these = Descriptors as they have no equivalents in the as-shipped Ed-Fi technology.=
The high-level steps are:
Details follow.
Note this script must be run as-is. The values provided below are used b= y the ETL code.
INSERT I= NTO interop.OperationalContext (OperationalContextUri, DisplayName) VALUES ('uri://ed-fi.org/Dashboard', 'Ed-Fi Dashboard Operational Context')=
The below query will show you any Descriptors defined in a non-Ed-Fi nam= espace that are not currently mapped in the Dashboard Operational Context. = These are all candidate Descriptors you should consider mapping. Note that = not all Descriptors need to be mapped as not all Descriptors are used by th= e ETL process.
If the result of this query is empty, you have no more custom Descriptor= s that are unmapped in the Dashboard Operational Context.
/* Retrieve ALL of the unmapped custom descriptors=20 */ SET NOCOUNT ON DECLARE @AllUnmappedCustomDescriptors TABLE ( [CodeValue] NVARCHAR(50) NOT NULL, [DescriptorTable] NVARCHAR(255) NOT NULL, UNIQUE ([CodeValue], [DescriptorTable]) ) DECLARE @descriptor_table NVARCHAR(255) DECLARE descriptor_tables CURSOR FOR SELECT t.[name] FROM [sys].[tables] t INNER JOIN [sys].[schemas] s ON t.schema_id =3D s.schema_id WHERE t.[name] LIKE '_%Descriptor' AND s.[name] =3D 'edfi' ORDER BY t.[name] OPEN descriptor_tables FETCH NEXT FROM descriptor_tables INTO @descriptor_table WHILE @@FETCH_STATUS =3D 0 BEGIN =20 INSERT INTO @AllUnmappedCustomDescriptors (DescriptorTable, CodeValue) EXEC('SELECT '''+@descriptor_table+''' AS DescriptorTable, CodeValue FROM edfi.Descriptor WHERE Namespace LIKE ''%'+@descriptor_table+''' AND CodeValue IN ( SELECT CodeValue=20 FROM edfi.'+@descriptor_table+' dt INNER JOIN edfi.Descriptor d ON dt.'+@descriptor_table+'Id =3D d.DescriptorId WHERE Namespace NOT LIKE ''uri://ed-fi.org/%'') GROUP BY CodeValue HAVING COUNT(*) =3D 1') =20 FETCH NEXT FROM descriptor_tables INTO @descriptor_table =20 END =20 CLOSE descriptor_tables; DEALLOCATE descriptor_tables; SELECT d.DescriptorId, d.Namespace, m.DescriptorTable, m.CodeValue FROM @AllUnmappedCustomDescriptors m INNER JOIN edfi.Descriptor d ON d.Namespace LIKE '%/'+DescriptorTable AND d.CodeValue =3D m.CodeValue WHERE NOT EXISTS ( SELECT * FROM interop.OperationalContextSupport s WHERE TargetOperationalContextUri =3D 'uri://ed-fi.org/Dashboard' AND SourceDescriptorUri =3D d.[Namespace] + '#' + d.CodeValue ) ORDER BY DescriptorTable SET NOCOUNT OFF
Modify the @descriptor_table
and @edfi_code=
_value
variables below, and replace the SELECT 'Math'
line with the custom CodeValue you want mapped t=
o @edfi_code_value
. If you have more than one custom Code=
Value to map to the same @edfi_code_value
, uncomment and =
use the UNION ALL
syntax on the subsequent lines.
DECLARE = @DashboardOperationalContextNamespace NVARCHAR(255) SET @DashboardOperationalContextNamespace =3D 'uri://ed-fi.org/Dashboard' DECLARE @descriptor_table NVARCHAR(255) DECLARE @edfi_code_value NVARCHAR(255) IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [name] =3D '##custom_code= _values' AND [type] =3D 'U') DROP TABLE ##custom_code_values CREATE TABLE ##custom_code_values([CodeValue] NVARCHAR(50) NOT NULL) DECLARE @edfi_descriptor_id INT /* Change the values below depending on which descriptors you want to map. */ SET @descriptor_table =3D 'AcademicSubjectDescriptor' -- The Descriptor Typ= e SET @edfi_code_value =3D 'Mathematics' -- The Ed-Fi CodeValu= e that will be mapped TO INSERT INTO ##custom_code_values (CodeValue) SELECT 'Math' -- The Custom Descripto= r CodeValue you want mapped to @edfi_code_value defined above --UNION ALL -- (Optional) Add 'UN= ION ALL' if there are multiple CodeValues to map --SELECT 'Matematicas' -- (Optional) Any Add= itional Custom CodeValues that will map to @edfi_code_value (Add 'UNION ALL= ' if there are more) DECLARE @descriptor_query NVARCHAR(MAX) SET @descriptor_query =3D 'SELECT @edfi_descriptor_id =3D (SELECT TOP 1 Des= criptorId FROM edfi.Descriptor d INNER JOIN edfi.'+ @descriptor_table + ' t ON d.descriptorId =3D t.'+ @descriptor_table +'Id WHERE d.CodeValue =3D'''+@edfi_code_value+''' AND d.Namespace LIKE ''uri://ed-fi.org/%'')' EXEC sp_executesql @descriptor_query, N'@edfi_descriptor_id INT OUT', @edfi= _descriptor_id out IF EXISTS(SELECT * FROM interop.OperationalContextDescriptorUsage WHERE Ope= rationalContextUri =3D @DashboardOperationalContextNamespace AND Descriptor= Id =3D @edfi_descriptor_id) BEGIN DECLARE @error_message NVARCHAR(MAX) SET @error_message =3D 'Mapping already exists for the Ed-Fi Descriptor= ' + @descriptor_table + ' with Code Value ' + @edfi_code_value RAISERROR(@error_message , 20, 1) WITH LOG; END /* Create Descriptor Mapping using the values provided above using either Equi= valence Groups or Generalization */ DECLARE @DescriptorsToMap TABLE ( [DescriptorId] INT NOT NULL ) INSERT INTO @DescriptorsToMap (DescriptorId) EXEC('SELECT DISTINCT DescriptorId FROM edfi.Descriptor d INNER JOIN edfi.'+ @descriptor_table + ' t ON d.DescriptorId =3D t.'+ @descriptor_table +'Id INNER JOIN ##custom_code_values c ON d.CodeValue =3D c.CodeValue') /* Check if mapping already exists for a custom descriptor */ IF EXISTS (SELECT * FROM interop.DescriptorEquivalenceGroupAssignment a INNER JOIN @DescriptorsToMap d ON a.DescriptorId =3D d.DescriptorId) BEGIN RAISERROR('Descriptor mapping already exists for some or all of the cus= tom descriptors provided.', 20, 1) WITH LOG; END BEGIN TRANSACTION; BEGIN TRY INSERT INTO @DescriptorsToMap (DescriptorId) VALUES (@edfi_descriptor_i= d) INSERT INTO interop.OperationalContextDescriptorUsage (OperationalConte= xtUri, DescriptorId) VALUES (@DashboardOperationalContextNamespace, @edfi_descriptor_id) DECLARE @use_generalization_mapping BIT SET @use_generalization_mapping =3D (SELECT CASE WHEN COUNT(*) > 1 T= HEN 1 ELSE 0 END=20 FROM ##custom_code_values) IF(@use_generalization_mapping=3D1) BEGIN DECLARE @EdFiDescriptorGeneralizationGroupMembers TABLE ( [DescriptorId] INT NOT NULL, [GeneralizationDescriptorEquivalenceGroupId] UNIQUEIDENTIFIER N= OT NULL, [DescriptorEquivalenceGroupId] UNIQUEIDENTIFIER NOT NULL ) DECLARE @GeneralizationDescriptorEquivalenceGroupId UNIQUEIDENTIFIE= R SET @GeneralizationDescriptorEquivalenceGroupId =3D NEWID() INSERT INTO @EdFiDescriptorGeneralizationGroupMembers (DescriptorId= , GeneralizationDescriptorEquivalenceGroupId, DescriptorEquivalenceGroupId) SELECT DescriptorId, @GeneralizationDescriptorEquivalenceGroupId, N= EWID()=20 FROM @DescriptorsToMap INSERT INTO [interop].[DescriptorEquivalenceGroup] (DescriptorEquiv= alenceGroupId) VALUES (@GeneralizationDescriptorEquivalenceGroupId) MERGE [interop].[DescriptorEquivalenceGroup] AS [TARGET] USING ( SELECT [DescriptorEquivalenceGroupId] FROM @EdFiDescriptorGeneralizationGroupMembers ) AS [SOURCE] ON ([TARGET].[DescriptorEquivalenceGroupId] =3D [SOURCE].[Descripto= rEquivalenceGroupId]) WHEN NOT MATCHED BY TARGET THEN INSERT ([DescriptorEquivalenceGroupId], [CreateDate], [LastModi= fiedDate], [Id]) VALUES ( [SOURCE].[DescriptorEquivalenceGroupId], GETDATE(), GETDATE= (), NEWID() ); MERGE [interop].[DescriptorEquivalenceGroupGeneralization] AS [TARG= ET] USING ( SELECT [GeneralizationDescriptorEquivalenceGroupId], [DescriptorEquivalenceGroupId] FROM @EdFiDescriptorGeneralizationGroupMembers ) AS [SOURCE] ON ([TARGET].[DescriptorEquivalenceGroupId] =3D [SOURCE].[Descripto= rEquivalenceGroupId]) WHEN NOT MATCHED BY TARGET THEN INSERT ( [GeneralizationDescriptorEquivalenceGroupId], [DescriptorEquivalenceGroupId], [CreateDate], [LastModifiedDate], [Id] ) VALUES ( [SOURCE].[GeneralizationDescriptorEquivalenceGroupId], [SOU= RCE].[DescriptorEquivalenceGroupId], GETDATE(), GETDATE(), NEWID() ); MERGE [interop].[DescriptorEquivalenceGroupAssignment] AS [TARGET] USING ( SELECT [DescriptorId], [DescriptorEquivalenceGroupId] FROM @EdFiDescriptorGeneralizationGroupMembers ) AS [SOURCE] ON ([TARGET].[DescriptorEquivalenceGroupId] =3D [SOURCE].[Descripto= rEquivalenceGroupId] AND [TARGET].[DescriptorId] =3D [SOURCE].[DescriptorId]) WHEN NOT MATCHED BY TARGET THEN INSERT ([DescriptorId], [DescriptorEquivalenceGroupId], [Create= Date], [LastModifiedDate], [Id]) VALUES ( [SOURCE].[DescriptorId], [SOURCE].[DescriptorEquivalenceGro= upId], GETDATE(), GETDATE(), NEWID() ); END ELSE BEGIN DECLARE @EdFiDescriptorEquivalenceGroupMembers TABLE ( [DescriptorId] INT, [DescriptorEquivalenceGroupId] UNIQUEIDENTIFIER NOT NULL ) DECLARE @DescriptorEquivalenceGroupId UNIQUEIDENTIFIER SET @DescriptorEquivalenceGroupId =3D NEWID() INSERT INTO @EdFiDescriptorEquivalenceGroupMembers (DescriptorId, D= escriptorEquivalenceGroupId) SELECT DescriptorId, @DescriptorEquivalenceGroupId=20 FROM @DescriptorsToMap MERGE [interop].[DescriptorEquivalenceGroup] AS [TARGET] USING ( SELECT DISTINCT [DescriptorEquivalenceGroupId] FROM @EdFiDescriptorEquivalenceGroupMembers ) AS [SOURCE] ON ([TARGET].[DescriptorEquivalenceGroupId] =3D [SOURCE].[Descripto= rEquivalenceGroupId]) WHEN NOT MATCHED BY TARGET THEN INSERT ([DescriptorEquivalenceGroupId], [CreateDate], [LastModi= fiedDate], [Id]) VALUES ( [SOURCE].[DescriptorEquivalenceGroupId], GETDATE(), GETDATE= (), NEWID() ); MERGE [interop].[DescriptorEquivalenceGroupAssignment] AS [TARGET] USING ( SELECT [DescriptorId], [DescriptorEquivalenceGroupId] FROM @EdFiDescriptorEquivalenceGroupMembers ) AS [SOURCE] ON ([TARGET].[DescriptorEquivalenceGroupId] =3D [SOURCE].[Descripto= rEquivalenceGroupId] AND [TARGET].[DescriptorId] =3D [SOURCE].[DescriptorId]) WHEN NOT MATCHED BY TARGET THEN INSERT ([DescriptorId], [DescriptorEquivalenceGroupId], [Create= Date], [LastModifiedDate], [Id]) VALUES ( [SOURCE].[DescriptorId], [SOURCE].[DescriptorEquivalenceGro= upId], GETDATE(), GETDATE(), NEWID() ); END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; DROP TABLE ##custom_code_values
Go to Step 2 and repeat for any remaining values you want mapped.