Magesh Subramanian

December 23, 2015 Less than a 1 minute read

List of Information Steward rules from repository

Here’s the query that lists all the rules and the associated projects , tables and dimensions within the Information Steward repository.It also provide the rule created date.

Environment

Information Steward 4.2.5

SQL server 2012

Query


select distinct
t5.technical_name as Project_name
,t1.business_name as Rule_name
,t2.value as Quality_dimension
,t4.technical_name as Table_name 
,t1.effective_dt as Rule_created_Date
from MMT_Rule t1
 join MMT_Custom_Field_Value t2 on t1.rule_id = t2.[object_id] and t2.is_current_Version = 'Y'
 join MMT_Relationship t3 on t1.rule_id = t3.[object_id] and user_flag1 is null  and t3.relationship_subtype_cd in ( 'PRRB' ) and   t3.is_current_Version = 'Y'
 join MMT_Data_Group t5 on t5.data_group_id = t3.container_id and t5.is_current_version = 'Y'  
 join MMT_Data_Group t4 on t4.data_group_id = t3.related_object_id and t4.is_current_version = 'Y'  
where t1.is_current_Version = 'Y'

19 Comments

You must be Logged on to comment or reply to a post.

Former Member

March 27, 2017 at 4:04 pm

Hi Magesh,

This is great. What other tables would I need to add if I also wanted to know:

the name of the Column to which the rule is bound
the name of a Scorecard in which a rule is used

Magesh Subramanian

Blog Post Author

March 27, 2017 at 9:27 pm

Hello Karam , I can't recollect. If you use SQL server as your repo , you may trace it yourself. Read this blog.

https://blogs.sap.com/2016/01/16/tracing-information-steward-metadata-tables-with-sql-server/

I will post the answer when I have time to find it.

Former Member

March 27, 2017 at 9:36 pm

Thanks Magesh,

I did see that earlier today and will give it a shot. Thank you for your posts and quick reply.

Former Member

March 29, 2017 at 9:44 pm

Hi Magesh,

I got the privileges approved and tried it. I'm getting data back, but the Table_Name values are NULL. Any thoughts?

Thanks,

-AK

Magesh Subramanian

Blog Post Author

March 30, 2017 at 9:50 pm

Hello Karam,

You should run the trace sql script in IS repository DB. Did you do that?

To get the columns that are bound to a rule , use the below join and select [MMT_Data_Element].Business_name

MMT_Relationship.related_object_id =[MMT_Data_Element].data_element_id

When you say rule name , do you mean ‘Domains associated with the tables/rules? , if it is then you can use the below join

[MMT_Key_Data_Domain].key_data_domain_id = MMT_Relationship.object_id
and 
[MMT_Key_Data_Domain].configuration_id = MMT_Relationship.onfiguration_id

The domain names are in the column [MMT_Data_Element].[business_name]

Former Member

April 4, 2017 at 5:29 pm

Magesh,

The information your provided was very helpful. However, I was wondering if you have any examples on creating a report of all calculated scores. I have found the table MMT_Key_Data_Domain_Score, which seams to have all of the information we are looking for... However, we want to link with the Project Name, Rule Name, and Quality Dimension, with table names and Rule Created Date. Do you have any examples or advice on how to join the key_data_domain_id to the correct rule_id? Thanks in advance. It is very much appreciated.

Magesh Subramanian

Blog Post Author

April 4, 2017 at 8:37 pm

Hi David , Can you try this query?

select distinct
t5.technical_name as Project_name
,t1.business_name as Rule_name
,t2.value as Quality_dimension
,t4.technical_name as Table_name 
,t1.effective_dt as Rule_created_Date
,t6.business_name as Domain_name
,t7.*
from MMT_Rule t1
 join MMT_Custom_Field_Value t2 on t1.rule_id = t2.[object_id] and t2.is_current_Version = 'Y'
 join MMT_Relationship t3 on t1.rule_id = t3.[object_id] and user_flag1 is null  and t3.relationship_subtype_cd in ( 'PRRB' ) and   t3.is_current_Version = 'Y'
 join MMT_Data_Group t5 on t5.data_group_id = t3.container_id and t5.is_current_version = 'Y'  
 join MMT_Data_Group t4 on t4.data_group_id = t3.related_object_id and t4.is_current_version = 'Y'  
 left join MMT_Key_Data_Domain t6 on t6.key_data_domain_id = t3.[object_id] and t6.configuration_id  = t3.configuration_id 
 left join MMT_Key_Data_Domain_Score t7 on  t6.key_data_domain_id= t7.key_data_domain_id
 where t1.is_current_Version = 'Y'

For some reason , IS is not populating the table MMT_Key_Data_Domain_Score for me. So, I'm not sure if the above query would fetch you the result that you are looking for. Let me know the result , so I can drill further into it.

Monica Enders

January 3, 2019 at 5:52 pm

Hello Magesh, Have you ever figured out why the MMT_Key_Data_Domain_Score was not being populated? I am having the same issue. I have figure out how to get the list of rules, but now I want the scores....

Magesh Subramanian

Blog Post Author

February 27, 2019 at 10:29 pm

Hi Monica, Sorry for the late reply. You may need to run the scorecard from CMC to see the MMT_Key_Data_Domain_Score score. Let me know if that doesn't work. I have another query that I can share.

Monica Enders

April 11, 2019 at 9:42 pm

Hi!

I ran the CMC scorecard, and it did not populate the MMT_Key_Data_Domain result tables. they are coming up all NULLs.

Thanks for your help.

Former Member

April 16, 2021 at 8:27 am

Hi Magesh, thank you for your help so far! Unfortunately im running on NULL values as well. Could you share your other query? It might just be what we need.
Thanks in advance!

Magesh Subramanian

Blog Post Author

April 16, 2021 at 11:30 am

Hi Samuel,

Please check this query. Make sure you pass the IS project name at the second last line of the query. Let me know if this works for you and feel free to contact me if you have any questions.

SELECT 
DISTINCT 
		T7.BUSINESS_NAME AS PROJECT_NAME
		,replace(T1.BUSINESS_NAME,'_',' ' ) AS DOMAIN_NAME
		,T5.BUSINESS_NAME AS RULE_NAME
		,T6.VALUE AS QUALITY_DIMENSION
		,T8.TECHNICAL_NAME AS TABLE_NAME 
		,T10.[TECHNICAL_NAME] AS COLUMN_NAME
		,T2.TOTAL_ROW_COUNT
		,T2.TOTAL_ROW_COUNT - T2.FAILED_ROW_COUNT AS PASSED_ROW_COUNT
		,T2.FAILED_ROW_COUNT 
		,[PASS_PERCENTAGE]=(CASE WHEN TOTAL_ROW_COUNT = 0 THEN 0.00 ELSE  ((T2.TOTAL_ROW_COUNT - T2.FAILED_ROW_COUNT)/T2.TOTAL_ROW_COUNT *100)   END)
FROM  MMT_KEY_DATA_DOMAIN T1
JOIN MMT_KEY_DATA_DOMAIN_SCORE T2 ON T1.KEY_DATA_DOMAIN_ID= T2.KEY_DATA_DOMAIN_ID AND T1.IS_CURRENT_VERSION = 'Y'
JOIN    ( 
		SELECT KEY_DATA_DOMAIN_ID,MAX(SCORE_DT) AS SCORE_DT  FROM  MMT_KEY_DATA_DOMAIN_SCORE
		GROUP BY KEY_DATA_DOMAIN_ID
		) T3 ON T2.KEY_DATA_DOMAIN_ID=T3.KEY_DATA_DOMAIN_ID AND T2.SCORE_DT = T3.SCORE_DT AND  T1.IS_CURRENT_VERSION = 'Y' AND T2.KEY_DATA_DOMAIN_SCORE_TYPE_CD= 'KDDB'
JOIN MMT_RELATIONSHIP T4 ON T2.SCORE_ID = T4.USER_BIGINT
JOIN MMT_RULE T5 ON T4.OBJECT_ID = T5.RULE_ID AND  T5.IS_CURRENT_VERSION = 'Y'
JOIN MMT_CUSTOM_FIELD_VALUE T6 ON T5.RULE_ID =T6.OBJECT_ID
JOIN MMT_DATA_GROUP T7 ON T1.CONFIGURATION_ID = T7.CONFIGURATION_ID
JOIN MMT_DATA_GROUP T8 ON T8.DATA_GROUP_ID = T4.RELATED_OBJECT_ID 
JOIN MMT_RELATIONSHIP T9 ON T2.SCORE_ID = T9.USER_BIGINT AND T9.USER_FLAG1 = 'Y'
JOIN [MMT_DATA_ELEMENT] T10 ON T9.RELATED_OBJECT_ID = T10.DATA_ELEMENT_ID AND T10.IS_CURRENT_VERSION = 'Y'
AND  T7.BUSINESS_NAME = '<IS Project name here>' 
AND T6.VALUE <>T1.BUSINESS_NAME

Mageshwaran SUBRAMANIAN

April 20, 2021 at 7:36 pm

Former Member Does this query work for you? If so, let me know so I can update this blog.

Former Member

April 22, 2021 at 2:07 pm

Hi Magesh, first of all thank you for your work! It helps us a lot in understanding the system further, even though the desired end-result ist not quite there yet: Specifically, this query runs endlessely on my end. Is it expected to take very long? (>1 hour)

Magesh Subramanian

Blog Post Author

April 22, 2021 at 3:11 pm

It should not. I ran this query in my IS repo ( SQL Server) while has a considerable number of projects. It finished running in 20 seconds.

How big is your IS repository, in terms of the number of projects?

Have you run the Scorecard Utility in CMC after executing your rule task(s)?

A S

January 5, 2023 at 3:02 pm

Hey Magesh,

is there a way to also retrieve or get the rule logic that is written in IS from the repository? If so can you include which join to make?

Ie - if i wrote a regex or any expression in the expression builder, I want to be able to get the logic, is there a repo table/field that has that?

Thanks

Ankit Bansal

March 23, 2018 at 12:55 pm

Hi Magesh,

Very useful information, saved my day from struggling around SAP IS repo tables.

Thanks very much!

Ankit

saurabh kumar

August 9, 2022 at 2:39 am

Hi Magesh,

This is immensely helpful. I just get few duplicates every time I execute this query I get an extra row with accuracy dimension for my IS scorecards

Thanks

Saurabh

Martin Böhlen

February 1, 2023 at 9:19 am

Is there also a way to display the number of filtered rows (since the DQ-score is calculated as a percentage of filtered rows not total rows) or does SAP Information Steward only store FAILED_ROW_COUNT and TOTAL_ROW_COUNT?

List of Information Steward rules from repository

Assigned Tags