7 Replies Latest reply on Sep 9, 2019 6:51 PM by Sathiesh M

    How to capture only changed columns from MQ Data Change?

    Keshav Krishna Seasoned Veteran

      C_REPOS_MQ_DATA_CHANGE provides the ROWID_TABLE and ROWID_OBJECT of an updated record. What if the source systems want only the updated attributes(columns) instead of whole record?

       

      For example, consider a record contains First Name, Middle Name, Last Name, Gender, Date of Birth, Suffix and Prefix.

      Suppose First Name is updated. Then Prefix and Gender are updated.

      We need only First Name, Prefix and Gender along with PKEY_SRC and ROWID_SYSTEM for that record.

        • 1. Re: How to capture only changed columns from MQ Data Change?
          Anuvinda Kulkarni Active Member

          C_Repos_MQ_Data_Change does not have business columns. Maybe you can look at the data_xml column in c_repos_audit to track these changes, if audit is enabled with 'include XML' option'

          • 2. Re: How to capture only changed columns from MQ Data Change?
            Keshav Krishna Seasoned Veteran

            Please let me know how to enable this as in our case c_repos_audit table is empty.

             

            Alternatively can we use Stored Procedure/joins to XREF and/or other tables and get the desired view?

            • 3. Re: How to capture only changed columns from MQ Data Change?
              Shriharsha Manjunath Active Member

              Goto Audit manager in the HUB console and enable the audit and include xml for the SIF calls you want to capture.

               

              If you want to capture the records loading done from the Jobs we would recommend to fire a query on the history table to identify the changes as even in the event xml we do not find the changes.

               

              <siperianEvent xmlns="urn:mdmsample.siperian.mrm.events"><eventMetadata><ns1:eventType xmlns:ns1="urn:siperian.mrm.events">Update XREF</ns1:eventType><ns2:baseObjectUid xmlns:ns2="urn:siperian.mrm.events">BASE_OBJECT.C_PARTY</ns2:baseObjectUid><ns3:packageUid xmlns:ns3="urn:siperian.mrm.events">PACKAGE.PKG_PARTY</ns3:packageUid><ns4:orsId xmlns:ns4="urn:siperian.mrm.events">orcl-MDM_SAMPLE</ns4:orsId><ns5:triggerUid xmlns:ns5="urn:siperian.mrm.events">MESSAGE_QUEUE_RULE.test</ns5:triggerUid><ns6:messageId xmlns:ns6="urn:siperian.mrm.events">2650613</ns6:messageId><ns7:messageDate xmlns:ns7="urn:siperian.mrm.events">2019-08-21T21:56:35.568+05:30</ns7:messageDate></eventMetadata><updateXrefEvent><ns8:sourceSystemName xmlns:ns8="urn:siperian.mrm.events">OrgDataInc</ns8:sourceSystemName><ns9:sourceKey xmlns:ns9="urn:siperian.mrm.events">185712221</ns9:sourceKey><ns10:eventDate xmlns:ns10="urn:siperian.mrm.events">2019-08-21T21:56:35.568+05:30</ns10:eventDate><ns11:rowid xmlns:ns11="urn:siperian.mrm.events">538           </ns11:rowid><ns12:xrefKey xmlns:ns12="urn:siperian.mrm.events"><ns13:systemName xmlns:ns13="urn:siperian.api">OrgDataInc</ns13:systemName><ns14:sourceKey xmlns:ns14="urn:siperian.api">185712221</ns14:sourceKey></ns12:xrefKey><pkgParty><rowidObject>538           </rowidObject><displayName>Time Warner Entertainment</displayName><partyType>Organization</partyType><hubStateInd>1</hubStateInd></pkgParty></updateXrefEvent></siperianEvent>

              <ns3:packageUid xmlns:ns3="urn:siperian.mrm.events">PACKAGE.PKG_PARTY</ns3:packageUid>

              1 of 1 people found this helpful
              • 4. Re: How to capture only changed columns from MQ Data Change?
                Sathiesh M Active Member

                Downstream systems typically doesn't consume all the attributes as-is the MDM system sends. Architecturally its best to identify the changes at the consuming system level, an example of SAP system consuming MDM Party data would expect a complete set of attributes from Party+Address+Identifiers to create an iDOC that can be consumed by SAP BusinessPartner module.

                • 5. Re: How to capture only changed columns from MQ Data Change?
                  Debanjan Chatterjee Active Member

                  C_REPOS_AUDIT for JMS is having issue and may not work,

                   

                  I have came accross the sitation.

                   

                  there is basically no way to capture only the changed columns out of the box,

                   

                  If you are interested to use your own request MDB then you may write the code to see the Changed data in the BO and then publish that part after reading the ROWID_OBJECT from C_REPOS_MQ_DATA_CHANGE table.

                  1 of 1 people found this helpful
                  • 6. Re: How to capture only changed columns from MQ Data Change?
                    Keshav Krishna Seasoned Veteran

                    I am planning to create a stored procedure/Dynamic SQL to compare history table records and create select statements with only updated columns in select list for each record of MQ Data Change. Any suggestions on this?

                    • 7. Re: How to capture only changed columns from MQ Data Change?
                      Sathiesh M Active Member

                      Yes, you can do that but that is a outside of MDM process.

                      Instead of thinking to find a technical solution to get only changed columns, pls think Architecturally of how the changes are consumed by downstream. Because sending different number of everytime, it will be difficult for any downstream to understand the layout.

                      In a general practice it’s the responsibility of consuming system to find the delta they’re interested in.

                       

                      Sathiesh Murugaswami