Skip to Content

7 Comments

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

  1. A. Karam

    Hi Magesh,

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

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

     

    (0) 
  2. A. Karam

    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

     

    (0) 
  3. Magesh Subramanian Post author

    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]

    _______________________________________________________________________________

    Now , YOU OWE ME A COFFEE 🙂

    (0) 
  4. David Steinbruck

    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.

     

    (1) 
    1. Magesh Subramanian Post author

      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.

       

      (0) 

Leave a Reply