9 Replies Latest reply: Mar 13, 2013 2:29 PM by A. Drieux RSS

    ODI Interfaces

    995309
      I'm actually working on a datawarhouse ( ODI ) that contains several scenario which contains so many interfaces , the problem is
      that some of interfaces are related by some tables while execution, how can I find tables related to each interface in the scenario ?
      in an other way : I have an interface which references so many tables and I need to be able to select the name of these tables and all other related tables in other interfaces?
        • 1. Re: ODI Interfaces
          user964802
          I recommend reviewing the Execution logs in the Operator. Typically, the 'Load Data' steps are pretty handy for what you want to find.

          Cheers
          • 2. Re: ODI Interfaces
            995309
            Ok I will try to see that , but isn't there a way to find all tables correponding to each interface even before execution , because there are several interfaces that still can't work.
            By the way how can u het names of tables throug LOG files ?


            Cheers
            Thank u too much
            • 3. Re: ODI Interfaces
              user964802
              Not sure about log files. Perhaps someone else can assist there.

              You could also review Models in the Designer. If you drill into the objects, there's a 'Used In' node that can be quite useful.

              Cheers
              • 4. Re: ODI Interfaces
                A. Drieux
                Hi

                You can use direct SQL Statement on work repository to get that.

                For example, if you need to display all source tables related to 1 interface, you write this statement :
                (odi_work_dev is the name of a work repository)
                select
                interf.pop_name as Interface ,
                interf.lschema_name as Target_Schema,
                target.res_name as Target_Table,
                tab_source.lschema_name as Source_Schema,
                table_source.res_name as Table_Source

                FROM odi_work_dev.snp_pop interf on interf.i_pop = etape.i_pop
                inner join odi_work_dev.snp_table target on target.i_table = interf.i_table
                inner join odi_work_dev.snp_source_tab tab_source on tab_source.i_pop = interf.i_pop     
                inner join odi_work_dev.snp_table table_source on table_source.i_table = tab_source.i_table

                Where interf.pop_name = ' ' -- enter the name of the interface
                If you need a more complex statement, please explicit exactly what you want to display.


                You can also create a BO universe, or OBIEE repository or something similar to use a user-friendly tool to display what you want.
                • 5. Re: ODI Interfaces
                  995309
                  Hi ,

                  Well I need to retrieve the source tables used in all interfaces mapping and join.
                  I should be able to know all tables related to each table in my datawarhouse .


                  Thank you in advance
                  • 6. Re: ODI Interfaces
                    A. Drieux
                    select DISTINCT
                    interf.lschema_name as Target_Schema,
                    target.res_name as Target_Table,
                    tab_source.lschema_name as Source_Schema,
                    table_source.res_name as Table_Source

                    FROM odi_work_dev.snp_pop interf on interf.i_pop = etape.i_pop
                    inner join odi_work_dev.snp_table target on target.i_table = interf.i_table
                    inner join odi_work_dev.snp_source_tab tab_source on tab_source.i_pop = interf.i_pop     
                    inner join odi_work_dev.snp_table table_source on table_source.i_table = tab_source.i_table
                    Edited by: A. Drieux on 13 mars 2013 04:44
                    • 7. Re: ODI Interfaces
                      A. Drieux
                      ... last post edited

                      you have the list of relation between target tables and source tables, whatever the interfaces
                      • 8. Re: ODI Interfaces
                        995309
                        Thank you very much :D Is there a way to get all tables related to all interfaces please ? I need to display names of tables of every interface in one table ... is that possible ?

                        Edited by: 992306 on 13 mars 2013 12:03
                        • 9. Re: ODI Interfaces
                          A. Drieux
                          if you want all target tables related to all source table (without the name of interfaces), you take the previous SQL statement (12 march)

                          if you want to display the name of all interfaces (with related source and target tables), use the SQL statement in the previous-previous statement (7 march)

                          If it's ok for you, please mark the post as correct/helpful and close the thread.