WIXLIB

PowerCenter Developer:Tips & Tricks for Mapping DesignerLingaraju Ramasamy (Raju),Technical Architecture ManagerInformatica Professional Services2

Agenda Introduction Architecture Best Practices Mapping Tips & Tricks Transformation Techniques Use of Metadata Repository maintenance Q&A3

Introduction4

Presenter Contact Lingaraju Ramasamy (Raju) lramasamy@informatica.com 408-368-2475 (Mobile) Technical Architecture Manager, InformaticaProfessional Services5

Architecture Best Practices6

Architecture Best Practices Consistency Applying consistent standards reduces long term complications Naming Conventions (Velocity) Descriptions Environments Documentation (Hyperlink to SharePoint) Modularity Develop according to a modular design Common Error Handling Reprocessing Mapping Assistants Reusability Focus on reuse to make quick and universal modifications Mapplets, Worklets, Transformations, reusable functions7

Architecture Best Practices Scalability Keep volumes in mind in order to create efficient mappings Caching Queries Partitioning Initial vs. Incremental Loads Simplicity Multiple simple processes are often better than few complexprocesses Multiple mappings Simple Queries Staging Tables Advantages Easy to develop, debug, maintain and debug8

Sample Complex MappingSC T STR ATTR OUTL ORGWK (Oracle)SQ SC T STRATTR OUTLORG WKSEQ ORGSC T STR ATTR OUTL HOLFN (Oracle)SQ SC T STRATTR OUTLHOL FNSEQ HOLEXP OUTL ORG BOOKENDEXP HOL BOOKENDSEQ OTHERSSEQ OUTLETSC T STR ATTR OUTL FN1(Oracle)SQ SC T STRATTR OUTLFN1EXP OUTL BOOKENDJAVA GENERATE MSGIDJAVA GENERATE SESSIDSEQ PK FK OUTLETEXP CREATEHDR ELEMENTSSEQ HR HDRSC T STR ATTR OUTL HRSWK1 (Oracle)SQ SC T STRATTR OUTLHRS WK1SC T STR ATTR OUTL HRSWK (Oracle)SQ SC T STRATTR OUTLHRS WKEXP HRS HDRMultiple Sources – WebServicesSEQ HRSEXP HRS BOOKENDJNR HRS HDRJNR OUTL HRSJNR OUTL HRHOLSEQ LANGSC T STR ATTR OUTL LANG FN (Oracle)SQ SC T STRATTR OUTLLANG FNEXP LANG BOOKENDJNR OUTL LANG HOL HRSSEQ TEAM MEMBERSC T STR ATTR OUTL TMFN (Oracle)SQ TEAM MEMBEREXP TEAM MEMBERJNR TEAM MEMBERJNR ORGANIZATIONEXP PASS THROUGHSRTEXP CHK NEW RECSTC TRANSACTION RECSRTR HDR DET DATAEXP DETAIL HOURSEXP SEQ SERVICE REC 3EXP SEQ DETAIL HIERARCHY RECSEXP SEQ HEADER HIERARCHY RECS11EXP HEADERHOURSEXP SEQ RECBASICEXP SEQ RECTM ZONEEXP SEQ DETAIL HOLIDAYRECSEXP SEQ RECGEO TYPEEXP SEQ HEADER TEAM MEMBER RECSEXP ACCOUNTING REC 6EXP SEQ DAYOF WEEKEXP SEQ HEADER HOLIDAYRECSEXP SEQ IDENTIFICATIONREC 4EXP SEQ STATUS REC 5EXP SEQ DETAIL TEAM MEMBER RECSUNI OUTLETEXP HDR BOOKENDWSC STR OUTl SV OUTLHOL LEVEL3 (Flat File)9

Simplified Complex MappingSEQ LANGSC T STR ATTR OUTL LANG FN (Oracle)SQ SC T STRATTR OUTLLANG FNSC T STR ATTR OUTL CHGWK1 (Oracle)SQ SC T STRATTR OUTLCHG WKEXP LANG BOOKENDSEQ OUTLETSEQ OUTLETSC T STR ATTR OUTL CHGWK (Oracle)SQ SC T STRATTR OUTLCHG WKEXP SRC BOOKENDSEQ OTHERSSC LKP GETMSGIDEXP OUTL BOOKENDSEQ OTHERSSC EXP CREATE HDR ELEMENTSSC LKP GETMSGIDSC T STR ATTR OUTL HRSWK (Oracle)SC EXP CREATE HDR ELEMENTSJNR OUTL LANG HOL HRSEXP PASS THROUGHSRT INCM RECSEXP CHK NEW RECSRTR HDR DET DATAStaging 1EXP HEADERLANGLANG HEADERSC T STR ATTR OUTL WK (Oracle)EXP DETAIL LANGLANG DETAILSTR ATTR OUTL WK (Oracle)SQ SC T STRATTR OUTLHRS WKSEQ HRSEXP HRS BOOKENDJNR OUTL HRSEXP PASS THROUGHSRT INCM RECSEXP CHK NEW RECSStaging 2RTR HDR DET DATAEXP SEQ DAYOF WEEKDAY OF WEEKSC T STR ATTR OUTL WK2 (Oracle)EXP HEADERHOURSHOURS HDRSC T STR ATTR OUTL WK (Oracle)EXP DETAIL HOURSDETAIL HOURS SC T STRATTR OUTLWK1 (Oracle)SEQ LANGSC T STR ATTR OUTL LANG FN (Oracle)SQ SC T STRATTR OUTLLANG FNEXP LANG BOOKENDSC T STR ATTR SITE FN (Oracle)SEQ OUTLETSC T STR ATTR OUTL CHGWK (Oracle)SQ SC T STRATTR OUTLCHG WKSQ SC T STRATTR SITE FNEXP SRC BOOKENDEXP OUTL BOOKENDSEQ OTHERSJAVA GEN MSG IDJAVA GEN SESSIDSC LKP GETMSGIDSEQ IDSC EXP CREATE HDR ELEMENTSStaging 3JNR OUTL LANG HOL HRSEXP PASS THROUGHSRT INCM RECSEXP CHK NEW RECSRTR HDR DET DATAEXP HEADERLANGLANG HEADERSC T STR ATTR OUTL WK (Oracle)EXP DETAIL LANGLANG DETAILSTR ATTR OUTL WK (Oracle)EXP GET SEQNUMWSC STR ATTR SAVE SITESC T STR ATTR SITE FN1 (Oracle)Staging – WebServices10

Mapping Tips & Tricks11

Mapping Tips Sources and Targets Use shortcuts from shared folders Extract only what is necessary Limit reads on source Distinguish between similar sources and targets Example DIM CUSTOMER1 DIM CUSTOMER insert DIM CUSTOMER2 DIM CUSTOMER update12

Mapping TricksParameters & Variables Reduce overhead of creating multiple mappingsReplace hard coded valuesUse to incrementally extract dataExampleUpdateDateTime TO DATE (‘ PREV RUN TS’): SetVariable (‘ CURR RUN TS, SESSSTARTTIME)13

Mapping TricksParameters & Variables Assign Parameter/Variable values in a Session Pass values from one session to a subsequent session in sameworkflow/worklet On Components Tab in Session Properties Use user-defined workflow/worklet variables Non-reusable Sessions only14

Mapping TricksBuilt-in Mapping Variables Mapping Name Workflow Name Session Name Integration Service NameRepository Service NameRepository User NameFolder NameSession Run ModeSource Table NamesTarget Table Names15

Mapping TricksGroup Expression (Anchor transformation) Add expression transformation after a source qualifier andbefore a target If source or target definition changes, reconnecting ports ismuch easier16

Mass Update pmrep massupdate Session properties Session config attributes Transformation instance attributes Session instance run time options17

Mapping AssistantsPreview Data View Data Accommodate anomalies early Verification of extraction/loading strategies Type of Data Source/Targets Relational, Flat file XML Files For further analysis, use Informatica Analyst Analyze the content, quality and structure of source data Involves separate Profiling warehouse, client and reports18

Mapping AssistantsMapping Wizard Pass-Through Slowly Changing Dimension Type 1 Dimension (No History) Type 2 Dimension (All History) Version Data Flag Current Effective Date Range Type 3 Dimension (Previous Versions)Slowly Changing Dimension Template19

Mapping AssistantsMapping Analyst for Excel (MAE) Standardize specifications Enhance collaboration between analyst and developer Improve documentation & audit ability of business logicData AnalystDefines Business TermsSpecifies Transformation RulesStandardize Excel formatDI DeveloperAugments, TunesGenerated Mappingsfrom SpecificationsGenerate SpecificationGenerate Mapping20

Mapping AssistantsMapping Architect for Visio (MAV) Define consistent methodology & structure for data integration projects Build custom wizard based on pattern without coding Generate multiple mappings at one time Document data flowDI ArchitectCreates & Publishesmapping templateDI DeveloperAugments, TunesGenerated MappingsTemplate FileInformaticaToolbarInformaticaStencilPublish TemplateGenerate MappingsDrawingWindowParameter File21

Mapping AssistantsMapping Architect for Visio (MAV) Case Study#1 7 templates were used across 2 projects to generate 600 mappings 97% of mappings were automatically generated and required no additionalchanges 3% needed to be manually modified or custom developedCase Study #2 1 template was used to create 150 mappings for a data migration projectalong with PowerCenter sessions and workflows Total effort was less than one day Equivalent effort to create 150 mappings manually would have been 2weeks (10x effort)22

Transformation Techniques23

Transformation TipsSource Qualifier Apply Default Query when possible Utilize SQ Attributes(i.e., User Defined Join, Source Filter) Understand advantages and limitations of the SQL overridePROS Utilize database optimizers (i.e., indexes, hints) Can accommodate complex queriesCONS Processing impacts database resources Lose transformation logic in metadata searched Unable to utilize Partitioning or Pushdown Optimization options Minimize complicated queries Add the SQL Override Query to the Description24

Transformation TipsExpressions Understand Port process order INs or IN/OUTs VARIABLEs OUTPUTs Reduce code complexity Use local variables Redundant calculations Check previous record Provide comments (-- or //) in expressions Optimize Expressions Numeric operations are faster than string operations Operators are faster than functions Un-Nest complicated logic (use IIF or DECODE)25

Transformation TipsUser-Defined Functions Build complex expressions and reusethem within repository Two Types: Public: Callable from any transformationexpression Private: Only callable from another userdefined function Include any valid function exceptaggregate functions Can export to XML Files26

Transformation TipsFilters/Routers Consider Source Qualifier witha filter to limit rows withinrelational sources Filter as close to the source aspossible Replace multiple filters with arouter Pertaining to routers, rows willgo to each path where thecriteria is TRUE27

Transformation TipsAggregators Use sorted input to decrease use of aggregate caches Limit connected input/output or output port Filter data before aggregating Use as early as possibleJoiners Perform joins in Source Qualifier when possible Limit use to heterogeneous and flat file sources Perform normal joins when possible Join sorted input when possible Designate the master source as the source with fewer rows28

Transformation TipsLookups Using SQL Override in Lookup Similar to Source Qualifier, avoid when possibleCan apply Parameters and VariablesCan query against multiple tables in same databaseSuppress ORDER BY statement by appending two dashes (--) Add indexes to database columns Replace large lookup tables with joins in the Source Qualifierwhen possible Relational Lookups should only return ports that meet thecondition Remove all ports not used downstream or in the SQL Override29

Transformation TipsLookup Caches Lookup Cache Types Persistent Caches Save lookup cache files for reuse Dynamic Caches Retains the latest changes to data as rows pass throughthe mapping Updating a master table Real-time sessions Slowly changing dimension Cache Sizes Eliminate Paging Stores condition values in index,.idx, files Stores output values in data, .dat, files Apply the Cache Calculator in Session30

Transformation TipsLookups Cache Updates Update the dynamic lookup cache withresults of an expressionUse Case: Update QTY on hand for new timestampAdd WHERE incoming row timestamp cached timestamp SQL Overrides for Uncached Lookups You must choose the Use Any Value on Lookup Policy on MultipleMatch condition Multiple Rows ReturnUse Case: Aggregate customer orders andstore the total value Database Deadlock Resilience NumOfDeadlockRetries DeadlockSleep31

Transformation TricksPipeline Lookup Perform a lookup on an application source that is not arelational table or file Partial pipeline contains Source & Source Qualifier but no target Integration Service reads source data and passes to LookupTransformation to create cache Create partitions to improve performance32

Transformation TipsTransaction Control Transformation Transaction in PowerCenter isa set of rows bound by commitor rollback Control commit and rollbacktransactions based on a row orset of rows that pass throughthe transformationUse Case: Each invoice number iscommitted to the target databaseas a single transaction Change Tracing Level to‘Terse’ At higher tracing levels, everyflush of the write buffers islogged33

Transformation TipsAssociated Source Qualifier Use ASQ when MQ data is flat file or COBOL ASQ is specific to the format of the MQ data34

Transformation TipsSequence Non-Reusable the counter is 0Performance will be affected if cached is lowIncrease of caching will improve the performanceThis doesn’t involve any database operationThe caching allows to reserve number of rows in thememory35

File Source and Target Commands36