Thursday, January 3, 2013

OBIA ETL Standards


Ø  Check the box “Fail parent if this task fails” of session check box in workflow
Ø  Check the box “Fail parent if this task does not run” of session check box in workflow
Ø  Make all the sessions as reusable
Ø  Remove the defualt value for $$DATASOURCE_NUM_ID & $$ TENANT_ID in the mappings after your unit testing is complete.
Ø  Keep  the session parameter $PMSessionLogFile for Session Log file
Ø  Remove hard coded value in "Target Table Name" from session.
Ø  Remove hard coded value in "Source Table Name" and “Owner Name”  from session.
Ø  Keep  the session parameter $PMSessionLogFile for Session Log file
Ø  Remove hard coded value from Target Table Name in session.
Ø  DAC will create and assign the following parameters and values at the time of execution.
·         PSFT Source Connection parameter is $DBConnection_OLTP
·         BAW  Target connection parameter is $DBConnection_OLAP
  
Assign above parameters for the following connections accordingly after unit testing is done manually.  
o   $Source in Session Properties
o   Source table Connection in Session mapping parameters
o   $Target in Session properties
o   Target table Connection in Session mapping parameters

Ø  Use Bulk mode as Target Load type for all the targets when ever truncating the tables. Usually most of the staging tables will be truncated always as well as all tables will be truncated as part of full load.
Ø  Use Normal mode as Target Load type for all tables when tables are not truncated as part of incremental loads.
Ø  Set “Stop on errors” value to 1 in sessions.
Ø  Rename the Source Object groups to WC_OLAP & WC_OLTP if the source objects are not available in OOTB.  Copy them from OOTB folder if they are already available.
Ø  Few workflows named like “_Full” and few are like “_full”.  One standard “_Full” is recommended.
Ø  We have observed most of the mappings are pretty straight forward. Try to come up with Mapplets when ever the mappings are complex.
Ø  Separate Workflow/session for Full load is required only when there is a difference like Diff SQL Override or Change in the load type (Bulk/Normal).  
   Ex: SDE Audience task is staging mapping (always truncate) and no difference is SQL override aslo. So separate session/workflow is not required for full load.