6 Replies Latest reply on Nov 8, 2013 10:53 AM by craigwalter

    repository query source to target at field level

    New Member

      We have about 140 mappings of just sources to targets and I am trying to generate list of the target tables and fields with the related source table and fields.  I am able to generate a report of the mapping, source table, and target table.  I can get this to work and can even verify that the tables match as well as to in a separate query  get the fields for source or target tables.   What I can't seem to do is to get something that shows the individual source fields to target fields matchup.

       

      Goal would be something like

       

      mapping      source field     target field

      m_abc          customer          customer_id

      m_abc          name               name

      m_abc          postal               po_box

       

       

      SQL to get table match ups

       

      SELECT distinct

        SUBSTR(REP_ALL_MAPPINGS.MAPPING_NAME,1,40) AS mapping_name,

        PMART_USER_REP_WIDGET_INST_SRC.mapping_ID,

        SUBSTR(REP_ALL_SOURCES.PARENT_SOURCE_NAME,1,40) as parent_source_name,

        SUBSTR(REP_ALL_TARGETS.PARENT_TARGET_NAME,1,40) as parent_target_name

      FROM

        pmartsbx_rep.REP_ALL_SOURCES,

        pmartsbx_rep.REP_ALL_TARGETS,

        pmartsbx_rep.REP_WIDGET_INST  PMART_USER_REP_WIDGET_INST_SRC,

        pmartsbx_rep.REP_WIDGET_INST,

        pmartsbx_rep.REP_ALL_MAPPINGS

      WHERE

        ( PMART_USER_REP_WIDGET_INST_SRC.WIDGET_ID=REP_ALL_SOURCES.SOURCE_ID  )

        AND  ( REP_ALL_MAPPINGS.MAPPING_ID=REP_WIDGET_INST.MAPPING_ID  )

        AND  ( REP_ALL_MAPPINGS.MAPPING_ID=PMART_USER_REP_WIDGET_INST_SRC.MAPPING_ID  )

        AND  ( REP_WIDGET_INST.WIDGET_ID=REP_ALL_TARGETS.TARGET_ID  )

        AND  ( REP_WIDGET_INST.SUBJECT_ID=REP_ALL_TARGETS.SUBJECT_ID  )

        AND  ( PMART_USER_REP_WIDGET_INST_SRC.SUBJECT_ID=REP_ALL_SOURCES.SUBJECT_ID  )

        AND   PMART_USER_REP_WIDGET_INST_SRC.WIDGET_TYPE_NAME = 'Source Definition'  

         AND REP_WIDGET_INST.WIDGET_TYPE_NAME = 'Target Definition' 

      and pmartsbx_rep.REP_ALL_SOURCES.PARENT_SUBJECT_AREA = 'B2CC_ABAP'

      order by SUBSTR(REP_ALL_MAPPINGS.MAPPING_NAME,1,40),

      SUBSTR(REP_ALL_SOURCES.PARENT_SOURCE_NAME,1,40),

      SUBSTR(REP_ALL_TARGETS.PARENT_TARGET_NAME,1,40)

        select * FROM pmartsbx_rep.rep_src_tbl_flds where table_id = 2943

        order by column_number

      SELECT distinct

        SUBSTR(REP_ALL_MAPPINGS.MAPPING_NAME,1,40) AS mapping_name,

        PMART_USER_REP_WIDGET_INST_SRC.mapping_ID,

        SUBSTR(REP_ALL_SOURCES.PARENT_SOURCE_NAME,1,40) as parent_source_name,

        SUBSTR(REP_ALL_TARGETS.PARENT_TARGET_NAME,1,40) as parent_target_name

      FROM

        pmartsbx_rep.REP_ALL_SOURCES,

        pmartsbx_rep.REP_ALL_TARGETS,

        pmartsbx_rep.REP_WIDGET_INST  PMART_USER_REP_WIDGET_INST_SRC,

        pmartsbx_rep.REP_WIDGET_INST,

        pmartsbx_rep.REP_ALL_MAPPINGS

      WHERE

        ( PMART_USER_REP_WIDGET_INST_SRC.WIDGET_ID=REP_ALL_SOURCES.SOURCE_ID  )

        AND  ( REP_ALL_MAPPINGS.MAPPING_ID=REP_WIDGET_INST.MAPPING_ID  )

        AND  ( REP_ALL_MAPPINGS.MAPPING_ID=PMART_USER_REP_WIDGET_INST_SRC.MAPPING_ID  )

        AND  ( REP_WIDGET_INST.WIDGET_ID=REP_ALL_TARGETS.TARGET_ID  )

        AND  ( REP_WIDGET_INST.SUBJECT_ID=REP_ALL_TARGETS.SUBJECT_ID  )

        AND  ( PMART_USER_REP_WIDGET_INST_SRC.SUBJECT_ID=REP_ALL_SOURCES.SUBJECT_ID  )

        AND   PMART_USER_REP_WIDGET_INST_SRC.WIDGET_TYPE_NAME = 'Source Definition'  

         AND REP_WIDGET_INST.WIDGET_TYPE_NAME = 'Target Definition' 

      and pmartsbx_rep.REP_ALL_SOURCES.PARENT_SUBJECT_AREA = 'B2CC_ABAP'

      order by SUBSTR(REP_ALL_MAPPINGS.MAPPING_NAME,1,40),

      SUBSTR(REP_ALL_SOURCES.PARENT_SOURCE_NAME,1,40),

      SUBSTR(REP_ALL_TARGETS.PARENT_TARGET_NAME,1,40)

       

      SQL to get fields for a particular source

       

        select * FROM pmartsbx_rep.rep_src_tbl_flds where table_id = 2943
        order by column_number

       

      [ -- moved over from subforum Troubleshooting because it better fits in here. Regards, Nico ]

        • 1. repository query source to target at field level
          Guru

          Have you taken a look at the Metadata Exchange views (MXviews)? See Repository Guide for more details.

          What you might want to use here is the MXview REP_MAPPING_CONN_PORTS.

           

          Regards,

          Nico

          • 2. repository query source to target at field level
            Guru

            Agreed, REP_MAPPING_CONN_PORTS is very useful. This should help you get started (relies on Oracle 10+ specific syntax).


            -- helper view to shift to-fields to from-fields for target definitions
            create view v_tmp as   (SELECT SUBJECT_ID, SUBJECT_AREA,MAPPING_ID,MAPPING_NAME,
               -- all the FROMs
               FROM_OBJECT_ID,
               FROM_OBJECT_TYPE,   ...
               -- all the TOs
               TO_OBJECT_ID,
               TO_OBJECT_TYPE,...
                  FROM REP_MAPPING_CONN_PORTS
            UNION ALL
                SELECT SUBJECT_ID,SUBJECT_AREA,MAPPING_ID,MAPPING_NAME,
               -- shift the to-fields to from-fields
               TO_OBJECT_ID,
               TO_OBJECT_TYPE,...
              -- to-fields become null 
               NULL,
               NULL,...
                  FROM REP_MAPPING_CONN_PORTS
                 WHERE to_object_type_name = 'Target Definition');
                
                
            -- walk tree from target ports back to sources
            select  SUBJECT_AREA, MAPPING_NAME, FROM_OBJECT_name Src, connect_by_root from_object_name Tgt, ...
               FROM v_helper_tmp x
              where connect_by_isleaf = 1
            CONNECT BY (prior x.from_object_name =x.to_object_name
                           and  prior x.from_OBJECT_INSTANCE_ID = x.to_OBJECT_INSTANCE_ID)
              start with from_object_type_name = 'Target Definition'
            ORDER BY 1, 2, 3, 4

            • 3. repository query source to target at field level
              Guru

              Hi,

               

              Can you reframe and share the proper this query? I guess while reading from the helper view, you are selecting fields which did not exist in the view definition.

               

              I did not do much research on the query but I am looking for something similar. I am looking for a query which can generate source-target matrix.

               

              Lett us say, a port in target has passed through 5 transformations. I want to get the output in below 4 column format. Is it possible?

               

              Tgt field Transformations Transf_Type Src Field

               

              Thanks for all of your help.

               

              Noor.

              • 4. repository query source to target at field level
                Guru

                Now, isn't that "the" classic use case for Metadata Manager (MM)? I mean, building such a query system is one thing, but it takes time and is complicated and complex to maintain. MX views can change from version to version (and in fact did e.g. from 8.1.1 to 8.5 and again from 8.6 to 9.0), so keeping such a system up-to-date is a nightmare in terms of maintenance.

                That's exactly what MM has been designed for: to show you the lineage for every single target field.

                 

                I have to admit, however, that I don't know how to get a list in the desired format out of MM. That's a question for the MM specialists here.

                 

                Regards,

                Nico

                • 5. repository query source to target at field level
                  Guru

                  Nico,

                   

                  Thanks for your inputs.

                   

                  MM is giving me the lineage export in the form of an excel sheet. But the transformations that a field has undergone have been displayed individually.

                   

                  It is just dumping all transformation objects and field level calculations in one tab of excel sheet. I do not want this. This requires lot of time for me to reformat and create a source to target matrix.

                   

                  I would have been happy if it would have generated even this according to the sequence of the transformation objects appearing in the mapping. There was no need to open the mapping and reformat the excel output according to the flow of the mapping.

                   

                  I am looking for output like this:

                   

                  map a source field to a target field with all sorts of transformation logic (include transformation type like lookup, joiner and logic, may be a DECODE) that it undergoes.

                   

                  I understood from one more forum here that it is possible using recursive queries on MM warehouse. There is a case study also presented at Infa world 2010 conference but couldn't understand it well.

                   

                  Request all MM specialists who know the warehouse model well to help out here. This will be of great value for people who want to generate technical documentation on the fly.

                   

                  thanks,

                  Noor.

                  • 6. Re: repository query source to target at field level
                    New Member

                    I am looking for the same thing and the temp view code does not work as posted.  The view name and the field names used to join in the second query don't match.  I have tried to work with this example but I have not been able to determine the correct approach.  It would be very valuable to be able to understand how to do this.  In a sense, I believe we are looking for the ability to duplicate the link (backward or forward) feature in the UI.