Restore Data Insight rule

During development or maintenance of Data Insight DQ rules, there are times when rules are updated or deleted – sometimes inadvertently.  Also, when deleting objects in Data Insight, the dependent objects like rules can be accidentally deleted.  This led to an investigation in to how to recover a deleted Data Insight rule.

After some testing, it is possible, but you will need access to the IS Repository to run queries.

Note: It helps to use a tool like NOTEPAD++ to see the start and end tags.

This is the process:

1. Run SQL on the repository to find the rule name. For example:
   select * from <IS_REPO>.mmt_rule where test_name =<Rule Name> order by effective_dt desc
2. Find the version you want (latest or any) and copy the SQL from TEST_RULE_DEFINITION.  For example:<SNIP>

   <rule:technical_name>$parameter1</rule:technical_name>

   <rule:description>Parameter Description</rule:description>

   <rule:data_type>varchar</rule:data_type>

   <rule:content_type>NONE</rule:content_type>

   </rule:Parameter>

   <rule:Parameter score=”false”>

   <rule:technical_name>$parametern</rule:technical_name>

   <rule:description>Parameter Description n</r{123456{

3. Now if it’s a really simple rule, it will have all the syntax in this column. If not, it cuts it off and puts a Y in EXTENDED_TEXT column.  It will add a reference (123456 above)
4. Now the number is the link to another table. So run this:
   select * from mmt_extended_text where extended_text_id=123456
5. This may return multiple rows.  Copy in order the data in EXTENDED_TEXT. See now the text above ends “/r” and the first line starts with “ule”.  This is how it captures the rest.ule:description>

   <rule:data_type>varchar</rule:data_type>

   <rule:content_type>NONE</rule:content_type>

   </rule:Parameter>  <SNIP>

6. Copy any/ all rows
7. This can almost work if saved to an XML file. But first, export an existing rule to file (for structure) and paste the first line in the code.<?xml version=”1.0″ encoding=”utf-8″ ?> <RuleExport xmlns=”http://www.sap.com/ICC/Rule” version=”14.2.9.1528″><RuleInfo>
8. Now what the rule syntax does not have are the categories. And the quality dimension is a mandatory field!  So add/ copy from an existing at least this line before the start of the opening tag <rule:Parameters>:<rule:Categories><rule:Category name=”CA.rule dimension”>Integrity</rule:Category></rule:Categories>
9. Save XML in the ZIP folder under rule folder.
10. Test import

It’s a bit of fiddling and could probably be scripted (or joined to the other tables) but due to the infrequent nature of restoring old/ deleted rules, this works.