7 Replies Latest reply on Apr 30, 2015 4:20 AM by C-vaji

    Repository queries

    New Member

      Hi,

       

      Can someone tell me the table names which stores Source/Target relations or queries to get parent/child relation info.

       

      We are planning to build reporting on these metadata manager repository tables to show source-target or parent/child relationships.

        • 1. Re: Repository queries
          Pritish Umathe Guru

          Hope this will help you :

           

          SELECT

             DISTINCT MAPPING_FLD_MAPPING.TRANS_EXPRESSION, MAPPING_FLD_MAPPING.SOURCE_FIELD_NAME, MAPPING_FLD_MAPPING.SOURCE_NAME, MAPPING_FLD_MAPPING.TARGET_COLUMN_NAME, MAPPING_FLD_MAPPING.TARGET_NAME

          FROM

             REP_FLD_MAPPING MAPPING_FLD_MAPPING

          • 2. Re: Repository queries
            New Member

            Above query would help you achieve what you looking for. You should look into MetaQuery Tool. It will help you do most of the stuff without building queries.

             

            MetaQuery in Support Console

            • 3. Re: Repository queries
              Rahul Shah New Member

              Hello,

               

              I am unable to see table or view named as REP_FLD_MAPPING MAPPING_FLD_MAPPING in our metatdata manager schema.

               

              I see lot of tables named with IDP_, IMC, IME, IMS etc.

               

              Please advise.

               

              Thanks

              • 4. Re: Repository queries
                Guru

                Stop, stop, stop.

                The original question sounds like a PowerCenter related question, but it is related to the lineage in Metadata Manager.

                 

                I don't know much about MM, so I can't tell whether MM offers any such capability. Can please someone knowledgeable in MM respond to this question?

                 

                Thanks,

                Nico

                • 5. Re: Repository queries
                  Nagaraja Ganiga Guru

                  You can use IMA_ELMNT_ASSOC view to get the parent-child relationship information.

                   

                  Sample query for Parent-Child Relationship would look like:

                  SELECT E.REPOSITORY_NAME AS RESOURCE_NAME, E.ELEMENT_NAME AS PARENT_NAME, E.CLASS_LABEL AS PARENT_OBJECT_TYPE,E1.ELEMENT_NAME AS CHILD_NAME, E1.CLASS_LABEL AS CHILD_OBJECT_TYPE

                  FROM IMA_ELMNT_ASSOC A, IMA_ELEMENT E, IMA_ELEMENT E1

                  WHERE A.FROM_ELEMENT_UID=E.ELEMENT_UID

                  AND A.TO_ELEMENT_UID=E1.ELEMENT_UID

                  AND ASSOCIATION_UID=6044

                   

                  Thanks

                  Nagaraja Ganiga

                  • 6. Re: Repository queries
                    New Member

                    Hi Nagaraja,

                     

                    Thanks a lot for looking into this. Your query helps.

                     

                    Is there any specific use of using ASSOCIATION_UID ?

                     

                    I am looking for field/column level parent/child flow from end to end.

                     

                    This data is used to do impact analysis whenever we are touching a field.

                     

                     

                    Regards,

                    Raviteja Ch

                    • 7. Re: Repository queries
                      C-vaji New Member

                      Raviteja

                       

                      it is not that simple to get the end to end report at column level, for which we have lineage diagram.

                      you can export the lineage to excel and check the details

                       

                      Thanks

                      Ramesh