3 Replies Latest reply on Dec 3, 2016 4:30 PM by Francisco Amores

    Using an FDMEE View for UDA

    3292348
    3292348 Dec 2, 2016 7:24 AM

      Hi,

       

      We're using PeopleSoft EPM tables as our sources but we were only given a dblink to access them. What we did was to create a view on the FDMEE schema for the PS table we're sourcing from and then configured ODI so we can use UDA to load data from that view.

       

      The view only gets from only one table but it has some concatenations.

       

      EXAMPLE: CREATE VIEW source_v AS SELECT A.COLUMN1 ACCOUNT, 'OU'||A.COLUMN2 OPERATING_UNIT, ... FROM PS_TBL@[dblink]

       

      We were able to successfully import and validate data but we're trying to figure out if we might encounter some issues given this setup. Would you know of possible challenges? We're testing performance between this and Open Interface Adapter.

        • 1. Re: Using an FDMEE View for UDA
          SH_INT
          SH_INT Dec 2, 2016 9:52 AM (in response to 3292348)

          For your configuration the UDA approach should give you better performance as it loads directly to the TDATASEG_T staging table and avoids the unecessary extra step of populating the AIF_OPEN_INTERFACE table.

          • 2. Re: Using an FDMEE View for UDA
            3292348
            3292348 Dec 2, 2016 10:20 AM (in response to SH_INT)

            Thanks SH_INT! Is it OK to have the view on the FDMEE schema?

             

            We were not allowed access to the PS schema so that was our workaround for ODI to be configured for UDA. 

            • 3. Re: Using an FDMEE View for UDA
              Francisco Amores
              Francisco Amores Dec 3, 2016 4:30 PM (in response to 3292348)

              Additional comment:

               

              if you crear the view in the FDMEE database, i think the SQL query to extract data from your source will not be executed remotely. Therefore you may have performance issues when importing data.

               

              the good thing of creating the view in the source and using UDA is that the sql query to extract data is executed in the source including the filters.

               

              Why not asking your DBA to create a sql user which read only access to the view? then you can easily configure UDA.