Data Developer insights @data@mastodon.lt

Denied logon (ORA-01017) trying to import #oracle table into #powercenter mapping? If #informatica fat client is running on 64-bit OS, make sure to install and use 32-bit Oracle 18 ODBC driver instead of DataDirect Oracle Wire Protocol. Issue is known: https://kb.informatica.com/solution/23/Pages/70/576529.aspx #protip #etl #database

Target Load Type is important: Bulk cannot work if target object has indexes (#Oracle database fails with ORA-26002: Table has index defined upon it error) but is faster due to limited ability to recover because no logging occurs. Normal mode works with indexed target objects. #PowerCenter #ETL #protip #tip

When adding derived ports in Source Qualifier SQL query override, #PowerCenter will fail with "The Source Qualifier contains an unbound field" - SQ ports must always be connected to Source Definition. Workaround: create dummy source table with corresponding data types (important!) and connect them to SQ with ports derived by SQL override. Works smooth 👍 #ETL

Load multiple files (same structure) at once with #PowerCenter by providing file list: 1) develop mapping with one of the files as source definition; 2) create workflow variable $$LoadFileName (do not have it as mapping parameter!); 3) put a list of files to load into indirect.txt; (can automate this with Bash) 4) add $$LoadFileName=indirect.txt to param file; 5) in session level set Source filetype=Indirect, Source filename=$$LoadFileName. All files listed in indirect.txt will be loaded 👍

If you get "ORA-28040: No matching authentication protocol" error trying to import #Oracle v12 table into #PowerCenter metadata and using DataDirect 7.1 Oracle Wire Protocol #ODBC driver, make sure SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11 are set in database config. #protip #ETL https://kb.informatica.com/solution/23/pages/64/522206.aspx

Even though creating and dropping views in #Teradata is a bit expensive, they are mandatory when trying to do UPDATE in #PowerCenter - otherwise following error is displayed: Full pushdown optimization stops at the target because update on Teradata requires a view, and view creation is not enabled. #ETL #tip

When developing with #PowerCenter a mapping that has several targets, it is important to select session setting "Treat source rows as: Data driven" to make sure each target has corresponding operation (DD_INSERT, DD_UPDATE, DD_DELETE) applied as set in Update transformations. When Data driven option is selected, there has to be UPDATE transformations in the mapping for #pushdown optimization to work. #etl

Efficient Slowly Changing Dimension type 1 in #PowerCenter : separate INSERT detection port (record doesn't exist on target) from UPDATE port (check all columns for changes) and evaluate them separately in Filter Transformations. Benefit: generated #sql query for INSERT will not have complex WHERE condition which UPDATE uses. #ETL #Teradata

Sadly: Full #pushdown optimization stops at the target [target] because #update on #Teradata is not supported in the presence of the joiner or lookup. - Informatica #PowerCenter #PDO Solution: replace Joiner Transformations with #sql overrides in Source Qualifiers and it will work. Some say Update Strategy doesn't work with #PDO but actually it does: both DD_INSERT and DD_UPDATE 👍 When #insert is done on target you can use Joiner though. #ETL