7 Replies Latest reply: Feb 27, 2013 9:16 PM by Dhar RSS

    RPD as a Data Source in Informatica

    799604
      I have a question. Has anyone ever tried importing the BI service as a datasource into Informatica? I'm posting the question to the Informatica community too. (OBIEE 10.1.3.4.2 & Informatica 8.1.1)
      Since it's ODBC, I'm thinking there may be some flexibility. I can see the dimension objects from the RPD show like tables in a schema when importing it. However the objects are empty. No columns.

      The reason why I'm attempting this is because we're trying to get to a database that cannot connect to Informatica due unavailibilty of ODBC drivers for the OS. However the db can connect to the server where the BI server resides.
      Maybe I can retrieve the data from a connection pool that'll be added to the RPD. However dont expose it, but transform it (ETL) then load it to warehouse. Subsequently exposing it after it's been loaded.

      Any one ever attempt something like this? (Not our first or second choice.)

      Thanks,
      Lael
        • 1. Re: RPD as a Data Source in Informatica
          Srini VEERAVALLI
          Just in case you able to import you may not achieve what you are expecting since BI services doesn't have data its own. It would be as good as ODBC client from BI.

          May I know the database name and OS?

          You may go for manual stored procedures or control files to extract data and insert in any major database, from there you might go for ETL for your business logic.

          Edited by: Srini VEERAVALLI on Feb 26, 2013 4:04 PM
          • 2. Re: RPD as a Data Source in Informatica
            799604
            The database is MySQL and the OS is IBM AIX 5.3
            • 3. Re: RPD as a Data Source in Informatica
              Srini VEERAVALLI
              Check these
              http://informaticalabs.blogspot.com/2011/04/setting-up-mysql-database-connections.html
              http://www.tek-tips.com/viewthread.cfm?qid=1620591

              BTW: Once you install the MySQL then go for installation of Informatica this would pick odbc drivers

              Edited by: Srini VEERAVALLI on Feb 26, 2013 4:51 PM
              • 4. Re: RPD as a Data Source in Informatica
                799604
                Thanks, unfortunately those aren't options for us.
                • 5. Re: RPD as a Data Source in Informatica
                  BenS
                  Here is where we are with our current approach:

                  1. Have MySQL tables mapped as source in RPD
                  2. Have MySQL tables exposed in Presentation Layer
                  3. Can run answers query against MySQL data

                  Informatica

                  1. In Source Analyzer select Import Tables
                  2. Select Oracle BI Server as ODBC data source
                  3. Enter Connection Information
                  4. Use Admin Username and PW to connect

                  When I view the list of selected tables have two problems:

                  (1) The table names used are ones used in the Presentation Description (ex: - Account or - Account Address) which when selected does not match the physical name and causes an issue.

                  (2) The selected table list displays the presentation names for all of my Oracle imported tables but I do not see in this list the MySQL tables

                  Any help getting both 1 and 2 resolved would be appreciated.

                  Thanks...
                  • 6. Re: RPD as a Data Source in Informatica
                    BenS
                    Were able to get all of the MySQL tables to appear by selecting the "Show Owners" all button/tab

                    Still have the issue that when Informatica tries to import the data it is using the Presentation table names and not the physical table names which is causing an issue.
                    • 7. Re: RPD as a Data Source in Informatica
                      Dhar
                      Hello Ben,

                      I have not tried this setup as I am currently looking for a generic ODBC driver, but hope this might help you. When BI Server itself is set up as a datasource, you got to have logical foreign key mappings in the BMM layer, instead of logical joins in BMM.

                      Could you create a logical foreign key relationship in your BMM and see if this works?

                      Hope this helps.

                      Thank you,
                      Dhar