Transformation Types in Informatica - Quontra Solutions

Information about Transformation Types in Informatica - Quontra Solutions

Published on September 16, 2014

Author: quontra123

Source: authorstream.com

Content

Transformation Types in Informatica: Transformation Types in Informatica About Quontra Solutions: About Quontra Solutions With Quontra support, growth is assured. We are providing the best custom fit training for each and every individual client since identifying the right domain and incubate the knowledge with rigor to deliver the most reliable output . We are among the leading Online and Corporate training Companies for the world! Transformation Types: Transformation Types Informatica PowerCenter 7 provides 23 objects for data transformation Aggregator: performs aggregate calculations Application Source Qualifier: reads Application object sources as ERP Custom: Calls a procedure in shared library or DLL Expression: performs row-level calculations External Procedure (TX): calls compiled code for each row Filter: drops rows conditionally Joiner: joins heterogeneous sources Lookup: looks up values and passes them to other objects Normalizer: reorganizes records from VSAM, Relational and Flat File Rank: limits records to the top or bottom of a range Input: Defines mapplet input rows. Available in Mapplet designer Output: Defines mapplet output rows. Available in Mapplet designer Transformation Types: Transformation Types Router: splits rows conditionally Sequence Generator: generates unique ID values Sorter: sorts data Source Qualifier: reads data from Flat File and Relational Sources Stored Procedure: calls a database stored procedure Transaction Control: Defines Commit and Rollback transactions Union: Merges data from different databases Update Strategy: tags rows for insert, update, delete, reject XML Generator: Reads data from one or more Input ports and outputs XML through single output port XML Parser: Reads XML from one or more Input ports and outputs data through single output port XML Source Qualifier: reads XML data Transformation Views: Transformation Views A transformation has three views: Iconized - shows the transformation in relation to the rest of the mapping Normal - shows the flow of data through the transformation Edit - shows transformation ports and properties; allows editing Edit Mode: Edit Mode Allows users with folder “write” permissions to change or create transformation ports and properties Switch between transformations Define transformation level properties Define port level handling Enter comments Make reusable Expression Transformation: Expression Transformation Passive Transformation Connected Ports Mixed Variables allowed Create expression in an output or variable port Usage Perform majority of data manipulation Perform calculations using non-aggregate functions (row level) Click here to invoke the Expression Editor Expression Editor: Expression Editor An expression formula is a calculation or conditional statement Used in Expression, Aggregator, Rank, Filter, Router, Update Strategy Performs calculation based on ports, functions, operators, variables, literals, constants and return values from other transformations Informatica Functions - Samples: Informatica Functions - Samples Character Functions Used to manipulate character data CHRCODE returns the numeric value (ASCII or Unicode) of the first character of the string passed to this function ASCII CHR CHRCODE CONCAT INITCAP INSTR LENGTH LOWER LPAD LTRIM RPAD RTRIM SUBSTR UPPER REPLACESTR REPLACECHR For backwards compatibility only - use || instead Informatica Functions: Informatica Functions Conversion Functions Used to convert datatypes Date Functions Used to round, truncate, or compare dates; extract one part of a date; or perform arithmetic on a date To pass a string to a date function, first use the TO_DATE function to convert it to an date/time datatype ADD_TO_DATE DATE_COMPARE DATE_DIFF GET_DATE_PART LAST_DAY ROUND (date) SET_DATE_PART TO_CHAR (date) TRUNC (date) TO_CHAR (numeric) TO_DATE TO_DECIMAL TO_FLOAT TO_INTEGER TO_NUMBER Informatica Functions: Informatica Functions Numerical Functions Used to perform mathematical operations on numeric data ABS CEIL CUME EXP FLOOR LN LOG MOD MOVINGAVG MOVINGSUM POWER ROUND SIGN SQRT TRUNC COS COSH SIN SINH TAN TANH Scientific Functions Used to calculate geometric values of numeric data Informatica Functions: Informatica Functions Test Functions Used to test if a lookup result is null Used to validate data ERROR ABORT DECODE IIF IIF(Condition,True,False) ISNULL IS_DATE IS_NUMBER IS_SPACES Special Functions Used to handle specific conditions within a session; search for certain values; test conditional statements Encoding Functions Used to encode string values SOUNDEX METAPHONE Expression Validation: Expression Validation The Validate or ‘OK’ button in the Expression Editor will: Parse the current expression Remote port searching (resolves references to ports in other transformations) Parse transformation attributes e.g. - filter condition, lookup condition, SQL Query Parse default values Check spelling, correct number of arguments in functions, other syntactical errors Variable Ports: Variable Ports Use to simplify complex expressions e.g. - create and store a depreciation formula to be referenced more than once Use in another variable port or an output port expression Local to the transformation (a variable port cannot also be an input or output port) Available in the Expression, Aggregator and Rank transformations Informatica Data Types: Informatica Data Types Transformation datatypes allow mix and match of source and target database types When connecting ports, native and transformation datatypes must be compatible (or must be explicitly converted) NATIVE DATATYPES TRANSFORMATION DATATYPES Specific to the source and target database types PowerMart / PowerCenter internal datatypes based on ANSI SQL-92 Display in source and target tables within Mapping Designer Display in transformations within Mapping Designer Native Native Transformation Datatype Conversions: Datatype Conversions All numeric data can be converted to all other numeric datatypes, e.g. - integer, double, and decimal All numeric data can be converted to string, and vice versa Date can be converted only to date and string, and vice versa Raw (binary) can only be linked to raw Other conversions not listed above are not supported These conversions are implicit; no function is necessary   Integer Decimal Double Char Date Raw Integer X X X X Decimal X X X X Double X X X X Char X X X X X Date X X Raw X Mappings: Mappings By the end of this section you will be familiar with: Mapping components Source Qualifier transformation Mapping validation Data flow rules System Variables Mapping Parameters and Variables Mapping Designer: Mapping Designer Iconized Mapping Mapping List Transformation Toolbar Pre-SQL and Post-SQL Rules : Pre-SQL and Post-SQL Rules Can use any command that is valid for the database type; no nested comments Can use Mapping Parameters and Variables in SQL executed against the source Use a semi-colon (;) to separate multiple statements Informatica Server ignores semi-colons within single quotes, double quotes or within /* ...*/ To use a semi-colon outside of quotes or comments, ‘escape’ it with a back slash (\) Workflow Manager does not validate the SQL Data Flow Rules: Data Flow Rules Each Source Qualifier starts a single data stream (a dataflow) Transformations can send rows to more than one transformation (split one data flow into multiple pipelines) Two or more data flows can meet together -- if (and only if) they originate from a common active transformation Cannot add an active transformation into the mix Example holds true with Normalizer in lieu of Source Qualifier. Exceptions are: Mapplet Input and Joiner transformations DISALLOWED T T Active ALLOWED T Passive T Connection Validation: Connection Validation Examples of invalid connections in a Mapping: Connecting ports with incompatible datatypes Connecting output ports to a Source Connecting a Source to anything but a Source Qualifier or Normalizer transformation Connecting an output port to an output port or an input port to another input port Connecting more than one active transformation to another transformation (invalid dataflow) Mapping Validation: Mapping Validation Mappings must: Be valid for a Session to run Be end-to-end complete and contain valid expressions Pass all data flow rules Mappings are always validated when saved; can be validated without being saved Output Window will always display reason for invalidity Workflows: Workflows By the end of this section, you will be familiar with: The Workflow Manager GUI interface Workflow Schedules Setting up Server Connections Relational, FTP and External Loader Creating and configuring Workflows Workflow properties Workflow components Workflow Tasks Workflow Manager Interface : Workflow Manager Interface Task Tool Bar Output Window Navigator Window Workspace Status Bar Workflow Designer Tools Workflow Manager Tools: Workflow Manager Tools Workflow Designer Maps the execution order and dependencies of Sessions, Tasks and Worklets, for the Informatica Server Task Developer Create Session, Shell Command and Email tasks Tasks created in the Task Developer are reusable Worklet Designer Creates objects that represent a set of tasks Worklet objects are reusable Workflow Structure: Workflow Structure A Workflow is set of instructions for the Informatica Server to perform data transformation and load Combines the logic of Session Tasks, other types of Tasks and Worklets The simplest Workflow is composed of a Start Task, a Link and one other Task Start Task Session Task Link Workflow Scheduler Objects: Workflow Scheduler Objects Setup reusable schedules to associate with multiple Workflows Used in Workflows and Session Tasks Server Connections: Server Connections Configure Server data access connections Used in Session Tasks Configure: Relational MQ Series FTP Custom External Loader Relational Connections (Native ): Relational Connections (Native ) Create a relational (database) connection Instructions to the Server to locate relational tables Used in Session Tasks Relational Connection Properties: Relational Connection Properties Define native relational (database) connection Optional Environment SQL (executed with each use of database connection) User Name/Password Database connectivity information Rollback Segment assignment (optional) FTP Connection: FTP Connection Create an FTP connection Instructions to the Server to ftp flat files Used in Session Tasks External Loader Connection: External Loader Connection Create an External Loader connection Instructions to the Server to invoke database bulk loaders Used in Session Tasks Task Developer: Task Developer Create basic Reusable “building blocks” – to use in any Workflow Reusable Tasks Session Set of instructions to execute Mapping logic Command Specify OS shell / script command(s) to run during the Workflow Email Send email at any point in the Workflow Session Command Email Session Task : Session Task Server instructions to runs the logic of ONE specific Mapping e.g. - source and target data location specifications, memory allocation, optional Mapping overrides, scheduling, processing and load instructions Becomes a component of a Workflow (or Worklet) If configured in the Task Developer, the Session Task is reusable (optional) Command Task: Command Task Specify one (or more) Unix shell or DOS (NT, Win2000) commands to run at a specific point in the Workflow Becomes a component of a Workflow (or Worklet) If configured in the Task Developer, the Command Task is reusable (optional) Commands can also be referenced in a Session through the Session “Components” tab as Pre- or Post-Session commands Command Task : Command Task Additional Workflow Components: Additional Workflow Components Two additional components are Worklets and Links Worklets are objects that contain a series of Tasks Links are required to connect objects in a Workflow Developing Workflows: Developing Workflows Create a new Workflow in the Workflow Designer Customize Workflow name Select a Server Workflow Properties: Workflow Properties Customize Workflow Properties Workflow log displays Select a Workflow Schedule (optional) May be reusable or non-reusable Workflows Properties: Workflows Properties Define Workflow Variables that can be used in later Task objects (example: Decision Task) Create a User-defined Event which can later be used with the Raise Event Task Building Workflow Components: Building Workflow Components Add Sessions and other Tasks to the Workflow Connect all Workflow components with Links Save the Workflow Start the Workflow Save Start Workflow Sessions in a Workflow can be independently executed Workflow Designer - Links: Workflow Designer - Links Required to connect Workflow Tasks Can be used to create branches in a Workflow All links are executed -- unless a link condition is used which makes a link false Link 2 Link 1 Link 3 Session Tasks: Session Tasks After this section, you will be familiar with: How to create and configure Session Tasks Session Task properties Transformation property overrides Reusable vs. non-reusable Sessions Session partitions Session Task: Session Task Created to execute the logic of a mapping (one mapping only ) Session Tasks can be created in the Task Developer (reusable) or Workflow Developer (Workflow-specific) Steps to create a Session Task Select the Session button from the Task Toolbar or Select menu Tasks | Create Session Task Bar Icon Session Task - General: Session Task - General Session Task - Properties: Session Task - Properties Session Task – Config Object: Session Task – Config Object Session Task - Sources: Session Task - Sources Session Task - Targets: Session Task - Targets Session Task - Transformations: Session Task - Transformations Allows overrides of some transformation properties Does not change the properties in the Mapping Session Task - Partitions: Session Task - Partitions Monitor Workflows: Monitor Workflows By the end of this section you will be familiar with: The Workflow Monitor GUI interface Monitoring views Server monitoring modes Filtering displayed items Actions initiated from the Workflow Monitor Truncating Monitor Logs Monitor Workflows: Monitor Workflows The Workflow Monitor is the tool for monitoring Workflows and Tasks Review details about a Workflow or Task in two views Gantt Chart view Task view Gantt Chart view Task view Monitoring Workflows: Monitoring Workflows Perform operations in the Workflow Monitor Restart -- restart a Task, Workflow or Worklet Stop -- stop a Task, Workflow, or Worklet Abort -- abort a Task, Workflow, or Worklet Resume -- resume a suspended Workflow after a failed Task is corrected View Session and Workflow logs Abort has a 60 second timeout If the Server has not completed processing and committing data during the timeout period, the threads and processes associated with the Session are killed Stopping a Session Task means the Server stops reading data Monitoring Workflows: Monitoring Workflows Task View Start Completion Task Workflow Worklet Time Time Status Bar Start, Stop, Abort, Resume Tasks,Workflows and Worklets Monitor Window Filtering: Monitor Window Filtering Task View provides filtering Monitoring filters can be set using drop down menus Minimizes items displayed in Task View Right-click on Session to retrieve the Session Log (from the Server to the local PC Client) Debugger: Debugger By the end of this section you will be familiar with : Creating a Debug Session Debugger windows & indicators Debugger functionality and options Viewing data with the Debugger Setting and using Breakpoints Tips for using the Debugger Debugger Features: Debugger Features Debugger is a Wizard driven tool View source / target data View transformation data Set break points and evaluate expressions Initialize variables Manually change variable values Debugger is Session Driven Data can be loaded or discarded Debug environment can be saved for later use Debugger Interface: Debugger Interface Debugger windows & indicators Session Log tab Target Data window Transformation Instance Data window Flashing yellow SQL indicator Debugger Mode indicator Solid yellow arrow Current Transformation indicator Debugger Log tab PowerPoint Presentation: Active Transformation Connected Ports All input / output Specify a Filter condition Usage Filter rows from flat file sources Single pass source(s) into multiple targets Drops rows conditionally Filter Transformation Aggregator Transformation: Aggregator Transformation Active Transformation Connected Ports Mixed Variables allowed Group By allowed Create expressions in output or variable ports Usage Standard aggregations Performs aggregate calculations Informatica Functions: Informatica Functions Aggregate Functions Return summary values for non-null data in selected ports Use only in Aggregator transformations Use in output ports only Calculate a single value (and row) for all records in a group Only one aggregate function can be nested within an aggregate function Conditional statements can be used with these functions AVG COUNT FIRST LAST MAX MEDIAN MIN PERCENTILE STDDEV SUM VARIANCE Aggregate Expressions: Aggregate Expressions Conditional Aggregate expressions are supported Conditional SUM format: SUM(value, condition) Aggregate functions are supported only in the Aggregator Transformation Aggregator Properties: Aggregator Properties Sorted Input Property Set Aggregator cache sizes (on Informatica Server machine) Instructs the Aggregator to expect the data to be sorted Sorted Data: Sorted Data The Aggregator can handle sorted or unsorted data Sorted data can be aggregated more efficiently, decreasing total processing time The Server will cache data from each group and release the cached data -- upon reaching the first record of the next group Data must be sorted according to the order of the Aggregator “Group By” ports Performance gain will depend upon varying factors Incremental Aggregation: Incremental Aggregation Trigger in Session Properties, Performance Tab Cache is saved into $PMCacheDir: aggregatorname.DAT aggregatorname.IDX Upon next run, files are overwritten with new cache information MTD calculation Best Practice is to copy these files in case a rerun of data is ever required. Reinitialize when no longer needed, e.g. – at the beginning new month processing Example: When triggered, PowerCenter Server will save new MTD totals. Upon next run (new totals), Server will subtract old totals; difference will be passed forward Joiner Transformation: Joiner Transformation By the end of this section you will be familiar with: When to use a Joiner Transformation Homogeneous Joins Heterogeneous Joins Joiner properties Joiner Conditions Nested joins Homogeneous Joins: Homogeneous Joins Joins that can be performed with a SQL SELECT statement: Source Qualifier contains a SQL join Tables on same database server (or are synonyms) Database server does the join “work” Multiple homogenous tables can be joined Heterogeneous Joins: Heterogeneous Joins Joins that cannot be done with a SQL statement: An Oracle table and a Sybase table Two Informix tables on different database servers Two flat files A flat file and a database table Joiner Transformation: Joiner Transformation Active Transformation Connected Ports All input or input / output “M” denotes port comes from master source Specify the Join condition Usage Join two flat files Join two tables from different databases Join a flat file with a relational table Performs heterogeneous joins on records from different databases or flat file sources Joiner Conditions: Joiner Conditions Multiple join conditions are supported Joiner Properties: Joiner Properties Join types: “Normal” (inner) Master outer Detail outer Full outer Joiner can accept sorted data (configure the join condition to use the sort origin ports) Set Joiner Cache Mid-Mapping Join: Mid-Mapping Join The Joiner does not accept input in the following situations: Both input pipelines begin with the same Source Qualifier Both input pipelines begin with the same Normalizer Both input pipelines begin with the same Joiner Either input pipeline contains an Update Strategy Sorter Transformation: Sorter Transformation Can sort data from relational tables or flat files Sort takes place on the Informatica Server machine Multiple sort keys are supported The Sorter transformation is often more efficient than a sort performed on a database with an ORDER BY clause Lookup Transformation: Lookup Transformation By the end of this section you will be familiar with: Lookup principles Lookup properties Lookup conditions Lookup techniques Caching considerations How a Lookup Transformation Works: How a Lookup Transformation Works Lookup value(s) Return value(s) Lookup transformation For each Mapping row, one or more port values are looked up in a database table If a match is found, one or more table values are returned to the Mapping . If no match is found, NULL is returned Lookup Transformation: Lookup Transformation Looks up values in a database table and provides data to other components in a Mapping Passive Transformation Connected / Unconnected Ports Mixed “L” denotes Lookup port “R” denotes port used as a return value (unconnected Lookup only) Specify the Lookup Condition Usage Get related values Verify if records exists or if data has changed Lookup Properties: Lookup Properties Override Lookup SQL option Native Database Connection Object name Toggle caching Additional Lookup Properties: Additional Lookup Properties Set cache directory Set Lookup cache sizes Make cache persistent Lookup Conditions: Lookup Conditions Multiple conditions are supported To Cache or not to Cache?: To Cache or not to Cache? Caching can significantly impact performance Cached Lookup table data is cached locally on the Server Mapping rows are looked up against the cache Only one SQL SELECT is needed Uncached Each Mapping row needs one SQL SELECT Rule Of Thumb : Cache if the number (and size) of records in the Lookup table is small relative to the number of mapping rows requiring lookup Target Options: Target Options By the end of this section you will be familiar with: Row type indicators Row operations at load time Constraint-based loading considerations Rejected row handling options Target Properties: Target Properties Session Task Select target instance Row loading operations Error handling Properties Tab Constraint-based Loading: Constraint-based Loading pk1 fk1, pk2 fk2 pk1 fk1, pk2 fk2 Example 1 With only One Active source, rows for Targets 1-3 will be loaded properly and maintain referential integrity Example 2 With Two Active sources, it is not possible to control whether rows for Target 3 will be loaded before or after those for Target 2 Maintains referential integrity in the Targets The following transformations are ‘Active sources’: Advanced External Procedure, Source Qualifier, Normalizer, Aggregator, Sorter, Joiner, Rank, Mapplet (containing any of the previous transformations) Update Strategy Transformation: Update Strategy Transformation By the end of this section you will be familiar with: Update Strategy functionality Update Strategy expressions Refresh strategies Smart aggregation Update Strategy Transformation: Update Strategy Transformation Used to specify how each individual row will be used to update target tables (insert, update, delete, reject) Active Transformation Connected Ports All input / output Specify the Update Strategy Expression Usage Updating Slowly Changing Dimensions IIF or DECODE logic determines how to handle the record Target Refresh Strategies: Target Refresh Strategies Single snapshot: Target truncated, new records inserted Sequential snapshot: new records inserted Incremental: Only new records are inserted. Records already present in the target are ignored Incremental with Update: Only new records are inserted. Records already present in the target are updated Router Transformation: Router Transformation Rows sent to multiple filter conditions Active Transformation Connected Ports All input/output Specify filter conditions for each Group Usage Link source data in one pass to multiple filter conditions Router Transformation in a Mapping: Router Transformation in a Mapping Parameters and Variables: Parameters and Variables By the end of this section you will understand: System Variables Creating Parameters and Variables Features and advantages Establishing values for Parameters and Variables System Variables: System Variables SESSSTARTTIME $$$SessStartTime Returns the system date value as a string. Uses system clock on machine hosting Informatica Server format of the string is database type dependent Used in SQL override Has a constant value Returns the system date value on the Informatica Server Used with any function that accepts transformation date/time data types Not to be used in a SQL override Has a constant value SYSDATE Provides current datetime on the Informatica Server machine Not a static value Mapping Parameters and Variables: Mapping Parameters and Variables Apply to all transformations within one Mapping Represent declared values Variables can change in value during run-time Parameters remain constant during run-time Provide increased development flexibility Defined in Mapping menu Format is $$ VariableName or $$ ParameterName Mapping Parameters and Variables: Mapping Parameters and Variables Sample declarations Declare Variables and Parameters in the Designer Mappings menu Set the appropriate aggregation type Set optional Initial Value User-defined names Functions to Set Mapping Variables: Functions to Set Mapping Variables SetCountVariable -- Counts the number of evaluated rows and increments or decrements a mapping variable for each row SetMaxVariable -- Evaluates the value of a mapping variable to the higher of two values SetMinVariable -- Evaluates the value of a mapping variable to the lower of two values SetVariable -- Sets the value of a mapping variable to a specified value Unconnected Lookup: Unconnected Lookup Will be physically “unconnected” from other transformations There can be NO data flow arrows leading to or from an unconnected Lookup Function in the Aggregator calls the unconnected Lookup Lookup function can be set within any transformation that supports expressions Lookup data is called from the point in the Mapping that needs it Conditional Lookup Technique: Conditional Lookup Technique Two requirements: Must be Unconnected ( or “function mode”) Lookup Lookup function used within a conditional statement Conditional statement is evaluated for each row Lookup function is called only under the pre-defined condition IIF ( ISNULL(customer_id),:lkp.MYLOOKUP(order_no)) Condition Lookup function Row keys (passed to Lookup) Conditional Lookup Advantage: Conditional Lookup Advantage Data lookup is performed only for those rows which require it. Substantial performance can be gained EXAMPLE: A Mapping will process 500,000 rows. For two percent of those rows (10,000) the item_id value is NULL. Item_ID can be derived from the SKU_NUMB. IIF ( ISNULL(item_id), :lkp.MYLOOKUP (sku_numb)) Condition (true for 2 percent of all rows) Lookup (called only when condition is true) Net savings = 490,000 lookups Connected vs. Unconnected Lookups: Connected vs. Unconnected Lookups Heterogeneous Targets: Heterogeneous Targets By the end of this section you will be familiar with: Heterogeneous target types Heterogeneous target limitations Target conversions Definition: Heterogeneous Targets: Definition: Heterogeneous Targets Supported target definition types: Relational database Flat file XML ERP (SAP BW, PeopleSoft, etc.) A heterogeneous target is where the target types are different or the target database connections are different within a single Session Task Step One: Identify Different Target Types: Step One: Identify Different Target Types Oracle table Flat file Oracle table Tables are EITHER in two different databases, or require different (schema-specific) connect strings One target is a flatfile load Step Two: Different Database Connections: Step Two: Different Database Connections The two database connections WILL differ Flatfile requires separate location information Target Type Override (Conversion): Target Type Override (Conversion) Example: Mapping has SQL Server target definitions. Session Task can be set to load Oracle tables instead, using an Oracle database connection. Only the following overrides are supported: Relational target to flat file target Relational target to any other relational database type SAP BW target to a flat file target CAUTION: If target definition datatypes are not compatible with datatypes in newly selected database type, modify the target definition Mapplet Designer: Mapplet Designer Mapplet Transformation Icons Mapplet Output Transformation Mapplet Designer Tool Mapplet Advantages: Mapplet Advantages Useful for repetitive tasks / logic Represents a set of transformations Mapplets are reusable Use an ‘instance’ of a Mapplet in a Mapping Changes to a Mapplet are inherited by all instances Server expands the Mapplet at runtime Active and Passive Mapplets: Active and Passive Mapplets Passive Mapplets contain only passive transformations Active Mapplets contain one or more active transformations CAUTION: changing a passive Mapplet into an active Mapplet may invalidate Mappings which use that Mapplet Do an impact analysis in Repository Manager first Using Active and Passive Mapplets: Using Active and Passive Mapplets Multiple Passive Mapplets can populate the same target instance Multiple Active Mapplets or Active and Passive Mapplets cannot populate the same target instance Active Passive Reusable Transformations: Reusable Transformations By the end of this section you will be familiar with: Reusable transformation advantages Reusable transformation rules Promoting transformations to reusable Copying reusable transformations Reusable Transformations: Reusable Transformations Define once - reuse many times Reusable Transformations Can be a copy or a shortcut Edit Ports only in Transformation Developer Can edit Properties in the mapping Instances dynamically inherit changes Be careful: It is possible to invalidate mappings by changing reusable transformations Transformations that cannot be made reusable Source Qualifier ERP Source Qualifier Normalizer used to read a Cobol data source Promoting a Transformation to Reusable: Promoting a Transformation to Reusable Place a check in the “Make reusable” box This action is not reversible Sequence Generator Transformation: Sequence Generator Transformation Generates unique keys for any port on a row Passive Transformation Connected Ports Two predefined output ports, NEXTVAL and CURRVAL No input ports allowed Usage Generate sequence numbers Shareable across mappings Sequence Generator Properties: Sequence Generator Properties Number of Cached Values Dynamic Lookup: Dynamic Lookup By the end of this section you will be familiar with: Dynamic lookup theory Dynamic lookup advantages Dynamic lookup rules Additional Lookup Cache Options: Additional Lookup Cache Options Dynamic Lookup Cache Allows a row to know about the handling of a previous row Cache File Name Prefix Reuse cache by name for another similar business purpose Recache from Database Overrides other settings and Lookup data is refreshed Make cache persistent Persistent Caches: Persistent Caches By default, Lookup caches are not persistent When Session completes, cache is erased Cache can be made persistent with the Lookup properties When Session completes, the persistent cache is stored on server hard disk files The next time Session runs, cached data is loaded fully or partially into RAM and reused Can improve performance, but “stale” data may pose a problem Dynamic Lookup Cache Advantages: Dynamic Lookup Cache Advantages When the target table is also the Lookup table, cache is changed dynamically as the target load rows are processed in the mapping New rows to be inserted into the target or for update to the target will affect the dynamic Lookup cache as they are processed Subsequent rows will know the handling of previous rows Dynamic Lookup cache and target load rows remain synchronized throughout the Session run Update Dynamic Lookup Cache : Update Dynamic Lookup Cache NewLookupRow port values 0 – static lookup, cache is not changed 1 – insert row to Lookup cache 2 – update row in Lookup cache Does NOT change row type Use the Update Strategy transformation before or after Lookup, to flag rows for insert or update to the target Ignore NULL Property Per port Ignore NULL values from input row and update the cache using only with non-NULL values from input Example: Dynamic Lookup Configuration: Example: Dynamic Lookup Configuration Router Group Filter Condition should be: NewLookupRow = 1 This allows isolation of insert rows from update rows Concurrent and Sequential Workflows: Concurrent and Sequential Workflows By the end of this section you will be familiar with: Concurrent Workflows Sequential Workflows Scheduling Workflows Stopping, aborting, and suspending Tasks and Workflows Multi-Task Workflows - Sequential: Multi-Task Workflows - Sequential Tasks can be run sequentially: Tasks shows are all Sessions, but they can also be other Tasks, such as Commands, Timer or Email Tasks Multi-Task Workflows - Concurrent: Multi-Task Workflows - Concurrent Tasks can be run concurrently: Tasks shows are all Sessions, but they can also be other Tasks such as Commands, Timer or Email Tasks. Multi-Task Workflows - Combined: Multi-Task Workflows - Combined Tasks can be run in a combination concurrent and sequential pattern within one Workflow: Tasks shows are all Sessions, but they can also be other Tasks such as Commands, Timer or Email Tasks Additional Transformations: Additional Transformations By the end of this section you will be familiar with: The Rank transformation The Normalizer transformation The Stored Procedure transformation The External Procedure transformation The Advanced External Procedure transformation Rank Transformation: Rank Transformation Active Transformation Connected Ports Mixed One pre-defined output port RANKINDEX Variables allowed Group By allowed Usage Select top/bottom Number of records Filters the top or bottom range of records Normalizer Transformation: Normalizer Transformation Normalizes records from relational or VSAM sources Active Transformation Connected Ports Input / output or output Usage Required for VSAM Source definitions Normalize flat file or relational source definitions Generate multiple records from one record Normalizer Transformation: Normalizer Transformation Turn one row YEAR,ACCOUNT,MONTH1,MONTH2,MONTH3, … MONTH12 1997,Salaries,21000,21000,22000,19000,23000,26000,29000,29000,34000,34000,40000,45000 1997,Benefits,4200,4200,4400,3800,4600,5200,5800,5800,6800,6800,8000,9000 1997,Expenses,10500,4000,5000,6500,3000,7000,9000,4500,7500,8000,8500,8250 Into multiple rows Stored Procedure Transformation: Stored Procedure Transformation Calls a database stored procedure Passive Transformation Connected/Unconnected Ports Mixed “R” denotes port will return a value from the stored function to the next transformation Usage Perform transformation logic outside PowerMart / PowerCenter External Procedure Transformation (TX): External Procedure Transformation (TX) Calls a passive procedure defined in a dynamic linked library (DLL) or shared library Passive Transformation Connected/Unconnected Ports Mixed “R” designates return value port of an unconnected transformation Usage Perform transformation logic outside PowerMart / PowerCenter Option to allow partitioning Advanced TX Transformation: Advanced TX Transformation Calls an active procedure defined in a dynamic linked library (DLL) or shared library Active Transformation Connected Mode only Ports Mixed Usage Perform transformation logic outside PowerMart / PowerCenter Sorting, Aggregation Option to allow partitioning Transaction Control Transformation: Transaction Control Transformation Passive Transformation Connected Mode Only Ports Input and Output Properties Continue Commit Before Commit After Rollback Before Rollback After Allows custom commit types (source- or target-based) and user-defined conditional commits Transaction Control Functionality: Transaction Control Functionality Commit Types Target Based Commit - Commit Based on “approximate” number of records written to target Source Based Commit – Ensures that a source record is committed in all targets User Defined Commit – Uses Transaction Control Transform to specify commits and rollbacks in the mapping based on conditions Set the Commit Type (and other specifications) in the Transaction Control Condition Versioning: Versioning View Object Version Properties Track Changes to an Object Check objects “in” and “out” Delete or Purge Object version Apply Labels and Run queries Deployment Groups Informatica Business Analytics Suite: Informatica Business Analytics Suite Custom Built Analytic Solutions Packaged Analytic Solutions Modular Plug-&-Play Approach Informatica Warehouses / Marts: Informatica Warehouses / Marts Informatica Warehouse ™ Supply Chain Human Resources Finance Customer Relationship Sales Marketing Service Web G/L Receivables Payables Profitability Compensation Scorecard Planning Sourcing Inventory Quality Customer Product Supplier Geography Organization Time Employee Common Dimensions Inside the Informatica Warehouse: Inside the Informatica Warehouse Business Adapters ™ (Extract) Data Source Connectivity with Minimal Load Structural/Functional Knowledge of Sources Analytic Bus ™ (Transform) Transaction consolidation and standardization Source independent interface Warehouse Loader (Load) Type I, II slowly changing dimensions History and changed record tracking Analytic Data Model Industry Best Practice Metrics Process-centric model & conformed dimensions Advanced Calculation Engine Pre-aggregations for rapid query response Complex calculation metrics (e.g. statistical) SAP i2 PSFT SEBL ORCL Custom Business Intelligence Informatica Warehouse ™ Analytic Data Model Advanced Calculation Engine Warehouse Loader ™ Analytic Bus ™ Business Adapters ™ Extract Transform Load PowerConnect Products: PowerConnect Products Family of enterprise software products that allow companies to directly source and integrate ERP, CRM, real-time message queue, mainframe, AS/400, remote data and metadata with other enterprise data PowerConnect for MQSeries (real time) PowerConnect for TIBCO (real time) PowerConnect for PeopleSoft PowerConnect for SAP R/3 PowerConnect for SAP BW PowerConnect for Siebel PowerConnect for Mainframe PowerConnect for AS/400 PowerConnect for Remote Data PowerConnect SDK

Related presentations


Other presentations created by quontra123