9 Replies Latest reply on Sep 22, 2019 8:59 AM by Rahul Yadav

    Base Object and Supporting Tables in MDM and the SQL Tool

    Michael Galaty Seasoned Veteran

      I am reviewing the presentation on the web by Ravi Kant Singh (link below).  I see that Oracle SQL Developer is being used to see the data values in the Informatica MDM Hub.   I have not to date figured out how I can see the data from MDM Hub Console and now I realize that someone told me that you can't see the data without a SQL Client.

       

      So my questions:

      1.  Is the Oracle SQL Developer the recommend tool?  Or is anyone using something else better?

      2.  Is that the only way to see the data in Informatica MDM?  Or are there other tools besides the SQL Client?

       

      Your courteous replies appreciated!  Thank you,

       

      RE:

      Base Object and Supporting Tables in MDM - YouTube

        • 1. Re: Base Object and Supporting Tables in MDM and the SQL Tool
          Michael Galaty Seasoned Veteran

          I am going to add the the on-boarding documentation does state to use:  Oracle SQL Developer 4.2 or higher  (this is relative to today 9/18/2019).  That's what should be used.  For a cloud installation that can't go over the internet.

          • 2. Re: Base Object and Supporting Tables in MDM and the SQL Tool
            Sawan Singh Seasoned Veteran

            Hello Michael,

             

            You can use any SQL client tool including SQL developer, toad, Squirrel etc.

             

            Informatica does not recommend any SQL client tool and it is up to you to decide on which one you want to use.

             

            For any enterprise application, using SQL client tool is recommended approach to see the data.

             

            You can use SQLPlus in your environment as well but it is not user friendly.

             

            Hope above is helpful for you.

             

            Regards

            Sawan Singh

            1 of 1 people found this helpful
            • 3. Re: Base Object and Supporting Tables in MDM and the SQL Tool
              Sathiesh M Active Member

              MDM data can be consumed in a number of ways

               

                1.  Primarily thru IDD/E360 – A business user tool to explore and maintain Master data

                2.  SIF/BE APIs – GET and SEARCH calls provide a way to consume MDM data in third party applications and other system integrations

                3.  SQL clients – Direct DB access to Data is not recommended generally. But technical users may use this as a means of debugging if any error arises.

               

              Sathiesh M

              • 4. Re: Base Object and Supporting Tables in MDM and the SQL Tool
                kunal pandit Active Member

                Hi Michael,

                 

                It seems your question might be related to viewing the actual data in a form view, if so, then there should be 2 workbenches in the hub console -> Data Manager and Merge Manager. These 2 workbenches were used by Data Steward in the early days of the tool to review the entity data and take decisions on match and merge.

                 

                This was before no web-based UI was present i.e. IDD. You can access IDD for your installation using the /bdd context.

                 

                Please ignore the above responses if your questions was in a different context. If yes, then please elaborate on your question.

                1 of 1 people found this helpful
                • 5. Re: Base Object and Supporting Tables in MDM and the SQL Tool
                  Michael Galaty Seasoned Veteran

                  I am reviewing all the tools on both a Sandbox and Dev system.  We were able to load a model into both of these systems and I would like to be able to be able to both insert data and see data in the model. 

                   

                  When reviewing the presentation by Ravi Kant Singh, I saw in his demo that he switched between the Hub Console and the SQL Developer tools.  I would like to do the same exercises he did in that video.

                   

                  So my questions are relative to the video by Ravi Kant Singh....  does that help?  Click on the video and you will see what I see.

                   

                  Kunal - Thank you!

                  • 6. Re: Base Object and Supporting Tables in MDM and the SQL Tool
                    Ravi Singh New Member

                    Hi Michael,

                     

                    Yes I am same Ravi kant Singh!!

                     

                    When you want to load or update data in MDM, you can not use any SQL clients beyond loading data into landing tables. There are user interfaces to create or update data such as IDD, E360, Hub Console however SQL clients ( Toad, Sqldeveloper etc ) should not be used. To view data, yes you can use Sql clients as mentioned by Sawan above.

                     

                    You can execute all the steps I mentioned in the video if you can access ORS schema. Since you mentioned On-boarding doc, I have to assume that you have your MDM Hosted by Informatica. Requirement to use SqlDeveloper 4.2 and higher is specifically for Hosted MDM where Informatica does not expose direct DB connectivity. In case of Hosted MDM, DB connectivity is via a ssh tunnel which is possible only in Sqldeveloper 4.2 onward.

                     

                    Please let me know your further questions and I will be happy to help.

                     

                     

                    Regards,

                     

                    Ravi

                    1 of 1 people found this helpful
                    • 7. Re: Base Object and Supporting Tables in MDM and the SQL Tool
                      Michael Galaty Seasoned Veteran

                      Hello Ravi Kant Singh,

                       

                      I appreciate your help in my questions around MDM.   My questions were around working with a sample model we've loaded into our sandbox/dev environment.  The sample model was provided by our salesman and unfortunately the demo we received was cut short and we have not been able to experiment with the model yet.  We recorded the demo, but the recording was lost.  So I've been trying to figure out how to use our tools.  I've since gotten a 10 minute recording where our support architect (for on boarding the system) showed me how to setup a package and we can use the Hub Console to work with the data.  That answers my question "are there other tools besides SQL client".

                       

                      Just an fyi...  I've listed to your video a few times

                       

                       

                       

                      (0000) how do we align data in base object and supporting tables.

                      (0020) Why supporting tables are needed

                      contributing record, history, trust&validation rules, data lineage, external match IO, fuzzy match tokens.

                      (0106) high level view of BO and supporting tables.   These tables are created when you create base object(V10)

                      XREF=cross reference to source, dirty=all records needing to be tokenized due to update, VCTL=validation rules setup, HMRG=history of merges, MTCH=match results before merge, CTL=trust settings, HIST=

                      VXR=validation result at XREF level,

                      (0252) Child tables

                       

                       

                      (0323) demo overview

                       

                       

                      (0410) explain he will load data into the landing tables.

                       

                       

                      (0448) showed two inserts into ORS

                       

                       

                      (0520) looking at child and parent records

                       

                       

                      (0650) looking at CTL table explaining trust

                       

                       

                      (0730) showing ctl table

                      (0740) showing vct table

                      (0805) showing vxr table

                      (0820) showing dirty table - table showing all

                      (0840) strp table

                      (0910) have not done merge on parent.

                      (0933) mentioned importance of foriegn keys

                      (0926) about to perform the merge

                      (0953) showing the mtch table

                      showing how #2 will win match

                      (1046) point out the consolidation_ind column

                      (1101) starting the auto merge

                      (1135) both child point to same record, system needs to realign foriegn key

                      (1202) g

                      (1239) going over original rowid object

                      (1313) mentioning unmerge

                      (1350) showing soap call to do an unmerge

                      (1440) hoping the demo will help understand the supporting tables.

                      • 8. Re: Base Object and Supporting Tables in MDM and the SQL Tool
                        Michael Galaty Seasoned Veteran

                        I pasted in the transcript notes I made from your video... I was not able to edit my post to make my point, so I'll comment.   What I got out of your video was a general understanding of how the underlying system of Informatica MDM works with the supporting tables.  I will use those notes to dig into the concepts you introduced in that video.  You introduced a lot of concepts in 15 minutes!  Thank you.

                        • 9. Re: Base Object and Supporting Tables in MDM and the SQL Tool
                          Rahul Yadav Guru

                          Hello Michael,

                           

                          It depends on what use case you are going to achieve.

                           

                          1. If business users would like to view/access the data then IDD or Customer 360 application can be used to access the Informatica MDM hub data

                          2. If business analysts, MDM developer, MDM Tester would like to access the data to investigate issues or data patterns then SQL Developer (For Oracle) or SQL Studio (SQL Server) these tools can be used

                          3. If MDM Administror or data steward with MDM hub access would like to access the data then you can see that data in the MDM hub using Queries, Packages, Data Manager, Merge Manager tools in the MDM hub

                          4. If you have users which do not have access to SQL Developer or MDM hub and they still would like to access to the data which is present in the Informatica MDM then you can export it as spreadsheet and share.