If you have a DBLINK, you need to ask them to create a synonym in the FDMEE for the TABLE/VIEW you want to extract data.
I haven't tested with synonyms yet but it should work.
Thanks Francisco. Do you advise the use of synonyms? There are more or less 20 tables that I need to pull from of about 100, 000+ records on each.
Right now, we're using a custom script that queries the source (using dblink) to populate AIF_OPEN_INTERFACE table and we're trying to gauge which configuration is faster.
do the 20 tables have the same structure? If so, why not creating a VIEW on top of them and you just need to configure that View and filter it.
Also, you can tell your DBA you just need a SQL user with read access to the tables. Then you can configure ODI to use that user for UDA.
Which the real reason of using the dblink?
I'd imagine that sourcing from that many tables suing the Open Interface Adpater may be a better approach as it gives you more flexibility over what you can setup in the ODI package to populate the AIF_OPEN INTERFACE table. The UDA is quicker config wise when your source data is in a single table or you can represent it as a single view, and it also offers the benefit that te extra step of loading to the AIF_OPEN_INTERFACE table is removed
We'll ask our DBA if he can give us that. I'll have to check regarding the structures of the table too. Thanks for your guidance!
Thanks for your insight. We're currently using SQL queries in our custom scripts to populate the AIF_OPEN_INTERFACE table. I was told that we can also populate that table via ODI setup but we're having a hard time in configuration.
I'll see if the DBA can give us access to the schema so we can test. Thanks again.
I have created a synonym for one table to test. I have setup UDA for ODI (thanks for your very informative blog!!). I then inputted the synonym name on the Table Name on source adapter for UDA. But upon clicking 'Import Table Definitions' on FDMEE source adapter, I am getting an 'An Error Occured' prompt.
I was able to successfully import the table definitions from a test table. The synonym just doesn't seem to work.