Informatica WIP

Owned by Orcrist
Last updated: Apr 24, 2014
6 min read

The point of this is to document research around refactoring the informatica integration around eliminating redundancy of shared objects, like sources, targets and mappings.  This process is going to involve a LOT of interaction with pmrep, and will take a lifetime if each command is executed on it's own process.  This should be done in conjunction with in process clients, or else write out listings and then create an export script and execute that.

Listing all objects:

 

Listing objects
pmrep>listobjects
The option -o is required.
Usage: listobjects
                   -o <object_type>
                   [-t <object_subtype>]
                   [-f <folder_name>]
                   [-c <column_separator>]
                   [-r <end-of-record_indicator>]
                   [-l <end-of-listing_indicator>]
                   [-b (verbose)]
                    [-s < dbd_seperator>]
Failed to execute listobjects.
pmrep>

 

Some object types I tried:  source, target, mapping, mapplet, session, workflow, transformation, “User Defined Function”, task, Deploymentgroup, label, folder, query, scheduler, sessionconfig, worklet.

 

Running normally just gives a list of objects:

Bare objects
pmrep>listobjects -o source -f __31343730353235323839333237_6_SHARED_EDW
source FlatFile.FF_INT_CONVERSION_TEST
.listobjects completed successfully.
pmrep>

 

From which you can get a list of objects.  If you need to know which are shortcuts, you can add -b:

Verbose listing
pmrep>listobjects -o source -f __31343730353235323839333237_6_SHARED_EDW -b
source FlatFile.FF_INT_CONVERSION_TEST 1
source FlatFile.sc_FF_INT_CONVERSION_TEST 1 shortcut
.listobjects completed successfully.
pmrep>

 

It appears to add two columns:  version number and the word shortcut if it is a shortcut.

 

Exporting:

Exporting objects is done with the exportobject command:

Export object command
pmrep>objectexport
The option -u is required.
Usage: objectexport
                    {{-n <object_name>
                      -o <object_type>
                      [-t <object_subtype>]
                      [-v <version_number>]
                      [-f <folder_name>]} |
                     -i <persistent_input_file>}
                    [-m (export pk-fk dependency)]
                    [-s (export objects referred by shortcut)]
                    [-b (export non-reusable dependents)]
                    [-r (export reusable dependents)]
                    -u <xml_output_file_name>
                    [-l <log_file_name>]
                     [-e < dbd_seperator>]
Failed to execute objectexport.
pmrep>

 

Generally, we should not use any of the -m, -s, -b or -r flags, since we want each object in it's own atomic container - and those flags seem to be off by default.

 

Exporting an object by type
pmrep>objectexport -o source -f __31343730353235323839333237_6_SHARED_EDW -u c:\xml_out.xml -o source -n FlatFile.sc_FF_INT_CONVERSION_TEST
12/18/2013 19:34:21
Analyzing Object Dependencies...
Exporting selected objects in repository REP_SVC_CI ...
Exporting selected objects in folder __31343730353235323839333237_6_SHARED_EDW .
..
        Fetching Source Defination [sc_FF_INT_CONVERSION_TEST] ...
        Exporting Source Defination [sc_FF_INT_CONVERSION_TEST] ...
Export is completed.
Exported 1 object(s) - 0 Error(s), - 0 Warning(s)
objectexport completed successfully.
pmrep>

 

Note that for a source, the source 'dbname' prefix is required (in this case, FlatFile).

 

Listing dependencies for a workflow gets a list of ALL objects therein - including transformations.  The parent dependency direction is invalid, so only children is allowed:

 

Workflow dependencies
pmrep>listobjectdependencies -n wkf_LOAD_ITMMVMT_AUD -o workflow -p both -f EDW_POS_TICKET -b
REP_SVC_DEV EDW_POS_TICKET source FlatFile.sc_d_fct_nt_itmvmt_v20091109 1 shortcut
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_AUD 1 shortcut
REP_SVC_DEV SHARED_POS_ODS target TICKET_DETAIL_AUD 1
REP_SVC_DEV SHARED_POS_ODS target TICKET_SUBHEADER_AUD 1
REP_SVC_DEV EDW_POS_TICKET source_qualifier non-reusable m_LOAD_ITMMVMT_AUD.SQ_Shortcut_to_d_fct_nt_itmvmt_v20091109 1
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_DETAIL_AUD 1 shortcut
REP_SVC_DEV SHARED_POS_ODS target TICKET_AUD 1
REP_SVC_DEV EDW_POS_TICKET sessionconfig default_session_config 1
REP_SVC_DEV EDW_POS_TICKET start non-reusable wkf_LOAD_ITMMVMT_AUD.Start 1
REP_SVC_DEV EDW_POS_TICKET target sc_ITMMVMT_AUD 1 shortcut
REP_SVC_DEV EDW_POS_TICKET router non-reusable m_LOAD_ITMMVMT_AUD.RTRTRANS 1
REP_SVC_DEV EDW_POS_TICKET expression reusable sc_exp_STANDARDIZE_DATE_FORMAT 1 shortcut
REP_SVC_DEV SHARED_POS_ODS sequence reusable seq_AUDIT_SK 1
REP_SVC_DEV SHARED_EDW expression reusable exp_STANDARDIZE_DATE_FORMAT 1
REP_SVC_DEV SHARED_POS_ODS target ITMMVMT_AUD 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_PREPARE_SUBHEADER 1
REP_SVC_DEV EDW_POS_TICKET scheduler non-reusable wkf_LOAD_ITMMVMT_AUD.Scheduler 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_PREPARE_DETAIL 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_TGT_ANCHOR_DETAIL 1
REP_SVC_DEV EDW_POS_TICKET mapping m_LOAD_ITMMVMT_AUD 1
REP_SVC_DEV SHARED_POS_ODS source FlatFile.d_fct_nt_itmvmt_v20091109 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_PREPARE_HEADER 1
REP_SVC_DEV EDW_POS_TICKET workflow wkf_LOAD_ITMMVMT_AUD 1
REP_SVC_DEV EDW_POS_TICKET sequence reusable sc_seq_AUDIT_SK 1 shortcut
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_ADD_PARAMETERS 1
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_SUBHEADER_AUD 1 shortcut
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_TGT_ANCHOR_TICKET_AUD 1
REP_SVC_DEV EDW_POS_TICKET session non-reusable wkf_LOAD_ITMMVMT_AUD.s_m_LOAD_ITMMVMT_AUD 1
.
The total number of records returned: 28
listobjectdependencies completed successfully.

 

Here is a more complex example.  Mapplets look a bit tricky (see the lookup_procedure):

 

Informatica(r) PMREP, version [9.1.0 HotFix4], build [449.0224], Windows 64-bit
Copyright (c) Informatica Corporation 1994 - 2012
All Rights Reserved.
This Software is protected by U.S. Patent Numbers 5,794,246; 6,014,670; 6,016,501; 6,029,178; 6,032,158; 6,035,307; 6,044,374; 6,092,086; 6,208,990; 6,339,775; 6,640,226; 6,789,096; 6,820,077; 6,823,373; 6,850,947; 6,895,471; 7,117,215; 7,162,643; 7,254,590; 7,281,001; 7,421,458; 7,496,588; 7,523,121; 7,584,422; 7,720,842; 7,721,270; and 7,774,791, international Patents and other Patents Pending.
Invoked at Thu Apr 24 15:48:17 2014
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.lkp_SALES_TYPE_ID 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R2_ABS_PLU 1
REP_SVC_DEV EDW_POS_TICKET scheduler non-reusable wkf_LOAD_TICKET_DETAIL_STG.Scheduler 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.lkp_POS_SYSTEM_TYPE 1
REP_SVC_DEV EDW_POS_TICKET start non-reusable wkf_LOAD_TICKET_DETAIL_STG.Start 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_COLLECT_CONDIMENT_SEQ_NUM 1
REP_SVC_DEV EDW_POS_TICKET mapping m_LOAD_TICKET_DETAIL_STG 1
REP_SVC_DEV SHARED_POS_ODS sequence reusable seq_TICKET_DETAIL_ID_SK 1
REP_SVC_DEV EDW_POS_TICKET source_qualifier non-reusable m_LOAD_TICKET_DETAIL_STG.SQ_sc_TICKET_DETAIL_AUD 1
REP_SVC_DEV EDW_POS_TICKET output_transformation non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.OUTPUT 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.lkp_PRODUCT_SIZE 1
REP_SVC_DEV SHARED_POS_ODS target TICKET_STG 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_DEFEAT_PRODUCT_SIZE_LOOKUP_NT 1
REP_SVC_DEV EDW_POS_TICKET sequence reusable sc_seq_TICKET_DETAIL_ID_SK 1 shortcut
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_BR_IND_VALID_PLU 1
REP_SVC_DEV SHARED_EDW expression reusable exp_DATE_CONVERSION 1
REP_SVC_DEV EDW_POS_TICKET update_strategy non-reusable m_LOAD_TICKET_DETAIL_STG.upd_SUBHEADER_INDICATORS 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_BR_IND_TICKET_DETAIL_UNIQUE 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R4b_SIZED_PLU_ALIAS 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R4a_ALIAS 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_TKT_DTL_STG_ANCHOR 1
REP_SVC_DEV EDW_POS_TICKET sessionconfig default_session_config 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_LOOKUP_VALIDATIONS 1
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_SUBHEADER_STG 1 shortcut
REP_SVC_DEV SHARED_POS_ODS target TICKET_SUBHEADER_STG 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.LKP_OMC_PRODUCT_SIZE_TRANSLATION 1
REP_SVC_DEV EDW_POS_TICKET expression reusable EXP_SYNTHETIC_NUM 1
REP_SVC_DEV SHARED_EDW expression reusable exp_INT_CONVERSION 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.lkp_VW_STORE 1
REP_SVC_DEV SHARED_POS_ODS target TICKET_DETAIL_STG 1
REP_SVC_DEV EDW_POS_TICKET custom_transformation non-reusable m_LOAD_TICKET_DETAIL_STG.JTX_CALCULATE_TKT_SBHDR_FLAGS 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R3_SRI_ITM_NBR 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.EXP_SOURCE_ANCHOR 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_COLLECT_SEQ_NUM 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R1_BASE_PLUS_SIZE 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_DC_RULES 1
REP_SVC_DEV EDW_POS_TICKET mapplet reusable mplt_PRODUCT_LOOKUP_OR_ALIAS 1
REP_SVC_DEV SHARED_POS_ODS source EDW_TICKET.TICKET 1
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_STG 1 shortcut
REP_SVC_DEV EDW_POS_TICKET router non-reusable m_LOAD_TICKET_DETAIL_STG.RTRTRANS 1
REP_SVC_DEV EDW_POS_TICKET source EDW_TICKET.sc_TICKET_STG 1 shortcut
REP_SVC_DEV EDW_POS_TICKET filter non-reusable m_LOAD_TICKET_DETAIL_STG.flt_BR_IND_TICKET_DETAIL_UNIQUE 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.EXP_PRODUCT_DECISION 1
REP_SVC_DEV EDW_POS_TICKET input_transformation non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.INPUT 1
REP_SVC_DEV EDW_POS_TICKET expression reusable sc_exp_INT_CONVERSION 1 shortcut
REP_SVC_DEV SHARED_POS_ODS source EDW_TICKET.TICKET_DETAIL_AUD 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_PREP_PORTS_FOR_SEQ_NUM 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_BR_IND_TICKET_DETAIL_UNIQUE_NULL_PRIMARY_KEYS 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_COLLECT_PORTS 1
REP_SVC_DEV EDW_POS_TICKET source EDW_TICKET.sc_TICKET 1 shortcut
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_SOURCE_ANCHOR 1
REP_SVC_DEV EDW_POS_TICKET update_strategy non-reusable m_LOAD_TICKET_DETAIL_STG.upd_UPD_TKT_STG_AGGREGATES 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_SALES_DATE 1
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.LKP_FIND_SUB_TICKET 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_BR_TICKET_DETAIL_VALID 1
REP_SVC_DEV EDW_POS_TICKET source EDW_TICKET.sc_TICKET_DETAIL_AUD 1 shortcut
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_CALCULATED_PORTS 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_MISC_DATA_VALIDATION 1
REP_SVC_DEV EDW_POS_TICKET expression reusable sc_exp_DATE_CONVERSION 1 shortcut
REP_SVC_DEV SHARED_POS_ODS source EDW_TICKET.TICKET_STG 1
REP_SVC_DEV EDW_POS_TICKET session non-reusable wkf_LOAD_TICKET_DETAIL_STG.s_m_LOAD_TICKET_DETAIL_STG 1
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_DETAIL_STG 1 shortcut
REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.lkp_Get_NETSALES_EXCLUSION_FLAG 1
REP_SVC_DEV EDW_POS_TICKET workflow wkf_LOAD_TICKET_DETAIL_STG 1
.
The total number of records returned: 64
listobjectdependencies completed successfully.
Completed at Thu Apr 24 15:48:22 2014

 

This one has a shortcut to a mapplet:

pmrep>listobjectdependencies -n wkf_LOAD_RECIPE_SCD -o workflow -p children -f SCM_ANALYTICS -b
REP_SVC_DEV SCM_ANALYTICS target sc_FF_TGT_MPLT_DIM_SCD_II_DBG 1 shortcut
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_SOFT_DELETE 1
REP_SVC_DEV SHARED_EDW_ODS source EDW_ODS.SCM_RECIPE 1
REP_SVC_DEV SHARED_EDW_ODS output_transformation non-reusable MPLT_DIM_SCD_II.OUTPUT 1
REP_SVC_DEV SHARED_EDW_ODS source EDW_ODS.EDW_RAW_MATERIAL_HIST 1
REP_SVC_DEV SHARED_EDW_ODS target FF_TGT_MPLT_DIM_SCD_II_DBG 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_SRC_ANCHOR_RECIPE_CURRENT_GEN 1
REP_SVC_DEV SHARED_EDW_ODS user_defined_function cleanText 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_FIRST_VERSION 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_SET_DSK_TO_0 1
REP_SVC_DEV SCM_ANALYTICS sequence reusable sc_seq_RECIPE_SK 1 shortcut
REP_SVC_DEV SHARED_EDW_ODS expression non-reusable MPLT_DIM_SCD_II.EXP_INPUT_ANCHOR_CONCAT 1
REP_SVC_DEV SHARED_EDW sequence reusable seq_RECIPE_SK 1
REP_SVC_DEV SCM_ANALYTICS start non-reusable wkf_LOAD_RECIPE_SCD.Start 1
REP_SVC_DEV SCM_ANALYTICS router non-reusable m_LOAD_RECIPE_SCD.RTR_SPLIT_RULES 1
REP_SVC_DEV SCM_ANALYTICS source EDW_ODS.sc_SCM_RECIPE 1 shortcut
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_SRC_ANCHOR_RECIPE 1
REP_SVC_DEV SCM_ANALYTICS scheduler non-reusable wkf_LOAD_RECIPE_SCD.Scheduler 1
REP_SVC_DEV SCM_ANALYTICS workflow wkf_LOAD_RECIPE_SCD 1
REP_SVC_DEV SHARED_EDW_ODS mapplet reusable MPLT_DIM_SCD_II 1
REP_SVC_DEV SCM_ANALYTICS source EDW_ODS.sc_EDW_RAW_MATERIAL_HIST 1 shortcut
REP_SVC_DEV SCM_ANALYTICS sessionconfig default_session_config 1
REP_SVC_DEV SCM_ANALYTICS target sc_RECIPE 1 shortcut
REP_SVC_DEV SCM_ANALYTICS source_qualifier non-reusable m_LOAD_RECIPE_SCD.SQ_sc_SCM_RECIPE 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_END_OF_LIFE 1
REP_SVC_DEV SCM_ANALYTICS joiner non-reusable m_LOAD_RECIPE_SCD.JNR_RECIPE_SCD 1
REP_SVC_DEV SHARED_EDW_ODS expression non-reusable MPLT_DIM_SCD_II.EXP_CATEGORIZE_RECORDS 1
REP_SVC_DEV SCM_ANALYTICS source EDW_ODS.sc_EDW_RECIPE_HIST 1 shortcut
REP_SVC_DEV SCM_ANALYTICS source_qualifier non-reusable m_PRESQL_DELETE_WORKSPACE.SQ_sc_DUMMY_EDW_ODS 1
REP_SVC_DEV SCM_ANALYTICS session non-reusable wkf_LOAD_RECIPE_SCD.s_m_LOAD_RECIPE_SCD 1
REP_SVC_DEV SCM_ANALYTICS mapplet reusable sc_MPLT_DIM_SCD_II 1 shortcut
REP_SVC_DEV SCM_ANALYTICS mapping m_LOAD_RECIPE_SCD 1
REP_SVC_DEV SHARED_EDW_ODS input_transformation non-reusable MPLT_DIM_SCD_II.INPUT 1
REP_SVC_DEV SCM_ANALYTICS source_qualifier non-reusable m_LOAD_RECIPE_SCD.SQ_sc_EDW_RECIPE_HIST 1
REP_SVC_DEV SHARED_EDW_ODS output_transformation non-reusable MPLT_DIM_SCD_II.OUT_DBG 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_PASS_THRU 1
REP_SVC_DEV SHARED_EDW_ODS target EDW_RAW_MATERIAL_HIST 1
REP_SVC_DEV SCM_ANALYTICS mapping m_PRESQL_DELETE_WORKSPACE 1
REP_SVC_DEV SCM_ANALYTICS session reusable s_m_PRESQL_DELETE_WORKSPACE 1
REP_SVC_DEV SHARED_EDW_ODS expression non-reusable MPLT_DIM_SCD_II.EXP_DBG_ANCHOR 1
REP_SVC_DEV SHARED_EDW target RECIPE 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_NEWER_VERSION 1
REP_SVC_DEV SCM_ANALYTICS target sc_EDW_RAW_MATERIAL_HIST 1 shortcut
REP_SVC_DEV SCM_ANALYTICS filter non-reusable m_PRESQL_DELETE_WORKSPACE.FLT_DROP_RECORD 1
REP_SVC_DEV SHARED_EDW_ODS source EDW_ODS.EDW_RECIPE_HIST 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_COMPARE 1
.
The total number of records returned: 46
listobjectdependencies completed successfully.