Handy MEDM Query: Get Data Constructor Rules

This Markit EDM query (valid as of v18.2.18) outputs the following columns for every rule on every non-deleted Data Constructor:

  • DC_ID – Data Constructor unique identifier.
  • DC_NAME – Data Constructor name.
  • DC_OUTPUT_SCHEMA – Data Constructor target table schema.
  • DC_OUTPUT_TABLE – Data Constructor target table name.
  • RULE_COLUMN – Rule target column.
  • RULE_PRIORITY – Rule position in the DC rule grid (starts with 1, higher numbers to the right)
  • RULE_TYPE – One of:
    • DefaultValue
    • ReferenceLookup
    • SqlExpression
  • RULE_ENABLED – True if the rule is enabled.
  • RULE_DESCRIPTION – Guess.
  • RULE_DEFINITION – A short XML snippet expressing the rule logic. Pretty human-readable, especially if you click to it from inside SSMS.
SELECT 
  C.IDENTIFIER AS DC_ID,
  C.NAME AS DC_NAME,
  C.[DEFINITION].value('(CadisXml/Content/FinalOutput/Table/Owner)[1]', 'nvarchar(128)') AS DC_OUTPUT_SCHEMA,
  C.[DEFINITION].value('(CadisXml/Content/FinalOutput/Table/Name)[1]', 'nvarchar(128)') AS DC_OUTPUT_TABLE,
  R.TARGETFIELD AS RULE_COLUMN,
  ROW_NUMBER() OVER(PARTITION BY R.CONSTRUCTIONID, R.TARGETFIELD ORDER BY R.[PRIORITY]) AS RULE_PRIORITY,
  RD.RULE_DEFINITION.value('RuleType[1]', 'varchar(36)') AS RULE_TYPE,
  RD.RULE_DEFINITION.value('Enabled[1]', 'bit') AS RULE_ENABLED,
  R.[DESCRIPTION] AS RULE_DESCRIPTION,
  RD.RULE_DEFINITION.query('.') AS RULE_DEFINITION
FROM 
  CADIS_SYS.DC_CONSTRUCTION C
  INNER JOIN CADIS_SYS.DC_RULE R ON R.CONSTRUCTIONID = C.IDENTIFIER
  CROSS APPLY C.[DEFINITION].nodes('//Fields/list/Item/Rules/list/Item') AS RD(RULE_DEFINITION)
WHERE
  R.DELETED IS NULL
  AND R.ORIGINALID = RD.RULE_DEFINITION.value('OriginalId[1]', 'varchar(36)')

Leave a Reply