OK, so there are multiple ways to go about this. Here are some solutions, pls choose one that suits your scenario.
1. SQL - List of all Business Rules
The following query will retrieve the list of all Active Business Rules created in MDS.
SELECT *
FROM [MDM].[mdm].[viw_SYSTEM_SCHEMA_BUSINESSRULES]
WHERE Model_Name = 'YourModelName'
AND BusinessRule_StatusName = 'Active'
You can, of course, further filter by Entity_Name, etc.
The important columns in your case are going to be:
[BusinessRule_Name][BusinessRule_Description][BusinessRule_RuleConditionText][BusinessRule_RuleActionText]
Note: The challenge in your scenario, I think, is going to be that the Subscription View of the entity does not have IDs of the exact Business Rules that failed. So I'm not sure how you'll tie these 2 together (failing Rows -> List of Business Rules). Also remember that each row may have more than 1 business rule that failed.
2. using View viw_SYSTEM_USER_VALIDATION
This is a view that has a historical list of all business rules (+row info) that failed. You may use the view in this way:
SELECT
DISTINCT ValidationIssue_ID, Version_ID, VersionName, Model_ID, ModelName, Entity_ID, EntityName, Hierarchy_ID, HierarchyName, Member_ID, MemberCode, MemberType_ID, MemberType, ConditionText, ActionText, BusinessRuleID, BusinessRuleName, PriorityRank, DateCreated, NotificationStatus_ID, NotificationStatus
FROM [MDM].[mdm].[viw_SYSTEM_USER_VALIDATION]
WHERE --CAST(DateCreated as DATE) = CAST(GETDATE() as DATE) AND
ModelName = 'YourModelName'
--AND EntityName IN ('Entity_1','Entity_2') -- Use this to Filter on specific Entities
Here, use the columns Model_ID, Entity_ID and Member_ID/MemberCode to identify the specific model, entity & row that failed.
Columns BusinessRuleName, ConditionText & ActionText will give your users additional info on the Business Rule that failed.
Note:
- One issue with using this view is that even though, let's say, your failure condition was resolved the next day by the user, the view will still show that on a certain date, a validation had failed. (via column
DateCreated).
- Also note that the same failed data row will appear multiple times here if multiple Business Rules on the same row failed validation (there will be different BusinessRuleID/Name, etc). Just something to take note of.
- Similarly, the same row may appear multiple times if it has failed again & again at different times. To workaround this, and if your final report can do with that, add a WHERE clause on the DateCreated column so that you only get to see any row that Failed today. The commented out line of code
<--CAST(DateCreated as DATE) = CAST(GETDATE() as DATE) AND> does the same. If you can't use that, just make sure the data row are distinct. However, if you do this, remember that if something Failed yesterday (and is still in the Failed status), it may not show up.
My suggestion would be to use a slightly modified version of Solution #2:
Get the list of Failed Rows from your Subscription View (the data row's ValidationStatus is actually still 'Failed'), then JOIN with viw_SYSTEM_USER_VALIDATION making sure that you only select the row with MAX(DateCreated) value (of course for the same data row AND Business Rule).
Best of luck and if you find anything else while solving this issue, do share your learning here with all of us.
Lastly, if you found this useful, pls remember to Mark it as the Answer :)
