Informatica PowerMart / PowerCenter 7 Basics

1. Informatica PowerMart / PowerCenter 7 Basics Education Services PC6B-20030512  Informatica Corporation, 2003. All rights reserved.

2. Course Objectives At the end of this course you will: • Understand how to use all major PowerCenter 7 components • Be able to perform basic Repository administration tasks • Be able to build basic ETL Mappings and Mapplets • Be able to create, run and monitor Workflows • Understand available options for loading target data • Be able to troubleshoot most problems

3. Extract, Transform, and Load RDBMS Operational Systems Other Decision Support Mainframe Aggregate Data Cleanse Data Consolidate Data Apply Business Rules De-normalize Transform DataWarehouse Load Extract • Transaction level data • Aggregated data • Optimized for Transaction Response Time • Historical • Current • Normalized or De- Normalized data ETL

4. PowerCenter Architecture

5. Repository Designer Workflow Workflow Rep Server Manager Manager Monitor Administrative Console PowerCenter 7 Architecture Server native native Targets Sources TCP/IP Repository Server Heterogeneous Targets Heterogeneous Targets TCP/IP Repository Agent Repository native Not Shown: Client ODBC Connections for Source and Target metadata

6. PowerCenter 7 Components • PowerCenter Repository • PowerCenter Repository Server • PowerCenter Client • Designer • Repository Manager • Repository Server Administration Console • Workflow Manager • Workflow Monitor • PowerCenter Server • External Components • Sources • Targets

7. Repository Topics By the end of this section you will be familiar with: • The purpose of the Repository Server and Agent • The Repository Server Administration Console GUI interface • The Repository Manager GUI interface • Repository maintenance operations • Security and privileges • Object sharing, searching and locking • Metadata Extensions

8. Repository Server • Each Repository has an independent architecture for the management of the physical Repository tables • Components: one Repository Server, and a Repository Agent for each Repository Server Repository Server Repository Agent Repository Repository Manager Repository Server Administration Console Client overhead for Repository management is greatly reduced by the Repository Server

9. Repository Server Features • Manages connections to the Repository from client applications • Can manage multiple Repositories on different machines on a network • Uses one Repository Agent process to insert, update and fetch objects from the Repository database tables, for each Repository it manages • Maintains object consistency by controlling object locking The Repository Server runs on the same system running the Repository Agent

10. Repository Server Administration Console Use Repository Administration console to Administer Repository Servers and Repositories through Repository Server. Following tasks can be performed: • Add, Edit and Remove Repository Configurations • Export and Import Repository Configurations • Create a Repository • *Promote a local Repository to a Global Repository • Copy a Repository • Delete a Repository from the Database • Backup and Restore a Repository • Start, Stop, enable and Disable a Repositories • View Repository connections and locks • Close Repository connections. • Upgrade a Repository

11. Repository Server Administration Console Information Nodes HTML View Console Tree Hypertext Links to Repository Maintenance Tasks

12. Repository Management • Perform all Repository maintenance tasks through Repository Server from the Repository Server Administration Console • Create the Repository Configuration • Select Repository Configuration and perform maintenance tasks: • Create • Delete • Backup • Copy from • Disable • Export Connection • Make Global • Notify Users • Propagate • Register • Restore • Un-Register • Upgrade

13. Repository Manager Use Repository manager to navigate through multiple folders and repositories. Perform following tasks: • Manage the Repository • Launch Repository Server Administration Console for this purpose • Implement Repository Security • Managing Users and Users Groups • Perform folder functions • Create, Edit, Copy and Delete folders • View Metadata • Analyze Source, Target, Mappings and Shortcut dependencies.

14. Repository Manager Interface Navigator Window Main Window Dependency Window Output Window

15. Users, Groups and Repository Privileges • Steps: • Create groups • Create users • Assign users to groups • Assign privileges to groups • Assign additional privileges to users (optional)

16. Managing Privileges Check box assignment of privileges

17. Folder Permissions • Assign one user as the folder owner for first tier permissions • Select one of the owner’s groups for second tier permissions • All users and groups in the Repository will be assigned the third tier permissions

18. Object Locking • Object Locks preserve Repository integrity • Use the Edit menu for Viewing Locks and Unlocking Objects

19. Object Searching(Menu- Analyze – Search) • Keyword search • Limited to keywords previously defined in the Repository (via Warehouse Designer) • Search all • Filter and search objects

20. Object Sharing • Reuse existing objects • Enforces consistency • Decreases development time • Share objects by using copies and shortcuts • Required security settings for sharing objects: • Repository Privilege: Use Designer • Originating Folder Permission: Read • Destination Folder Permissions: Read/Write

21. Adding Metadata Extensions • Allows developers and partners to extend the metadata stored in the Repository • Accommodates the following metadata types: • Vendor-defined - Third-party application vendor-created metadata lists • For example, Applications such as Ariba or PowerConnect for Siebel can add information such as contacts, version, etc. • User-defined - PowerCenter/PowerMart users can define and create their own metadata • Must have Administrator Repository or Super User Repository privileges

22. Sample Metadata Extensions Sample User Defined Metadata, e.g. - contact information, business user Reusable Metadata Extensions can also be created in the Repository Manager

23. Design Process Create Source definition(s) Create Target definition(s) Create a Mapping Create a Session Task Create a Workflow from Task components Run the Workflow Monitor the Workflow and verify the results

24. Source Object Definitions By the end of this section you will: • Be familiar with the Designer GUI interface • Be familiar with Source Types • Be able to create Source Definitions • Understand Source Definition properties • Be able to use the Data Preview option

25. Source Analyzer Designer Tools Analyzer Window Navigation Window

26. Repository Source Analyzer Relational XML file Flat file COBOL file Methods of Analyzing Sources • Import from Database • Import from File • Import from Cobol File • Import from XML file • Create manually

27. Source Analyzer ODBC Relational Source Table View Synonym Repository Server DEF TCP/IP Repository Agent native Repository DEF Analyzing Relational Sources

28. Analyzing Relational Sources Editing Source Definition Properties

29. Source Analyzer Flat File Mapped Drive NFS Mount Local Directory DEF Fixed Width or Delimited Repository Server TCP/IP Repository Agent native Repository DEF Analyzing Flat File Sources

30. Flat File Wizard • Three-step wizard • Columns can be renamed within wizard • Text, Numeric and Datetime datatypes are supported • Wizard ‘guesses’ datatype

31. XML Source Analysis Source Analyzer Mapped Drive NFS Mounting Local Directory .DTD File DEF Repository Server DATA TCP/IP Repository Agent In addition to the DTD file, an XML Schema or XML file can be used as a Source Definition native Repository DEF

32. Source Analyzer Mapped Drive NFS Mounting Local Directory .CBL File DEF Repository Server DATA TCP/IP Repository Agent native Repository DEF Analyzing VSAM Sources • Supported Numeric Storage Options: • COMP, COMP-3, COMP-6

33. VSAM Source Properties

34. Target Object Definitions By the end of this section you will: • Be familiar with Target Definition types • Know the supported methods of creating Target Definitions • Understand individual Target Definition properties

35. Creating Target Definitions Methods of creating Target Definitions • Import from Database • Import from an XML file • Manual Creation • Automatic Creation

36. Automatic Target Creation Drag-and-drop a Source Definition into the Warehouse Designer Workspace

37. Warehouse Designer Database ODBC Table View Synonym Repository Server DEF TCP/IP Repository Agent native Repository DEF Import Definition from Database Can “Reverse engineer” existing object definitions from a database system catalog or data dictionary

38. 1. Create empty definition 3. Finished target definition Manual Target Creation 2. Add desired columns ALT-F can also be used to create a new column

39. Target Definition Properties

40. Target Definition Properties

41. Execute SQL via Designer DEF DEF DEF LOGICAL Repository target table definitions PHYSICAL Target database tables Creating Physical Tables

42. Creating Physical Tables Create tables that do not already exist in target database • Connect - connect to the target database • Generate SQL file - create DDL in a script file • Edit SQL file - modify DDL script as needed • Execute SQL file - create physical tables in target database Use Preview Data to verify the results (right mouse click on object)

43. By the end of this section you will be familiar with: Transformation types and views Transformation calculation error treatment Null data treatment Informatica data types Expression transformation Expression Editor Informatica Functions Expression validation Transformation Concepts

44. 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

45. 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

46. 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

47. Define transformation level properties Define port level handling Enter comments Make reusable Edit Mode Allows users with folder “write” permissions to change or create transformation ports and properties Switch between transformations

48. Click here to invoke the Expression Editor Expression Transformation Perform calculations using non-aggregate functions (row level) • Passive Transformation • Connected • Ports • Mixed • Variables allowed • Create expression in an output or variable port • Usage • Perform majority of data manipulation

49. 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

50. Informatica Functions - Samples ASCII CHR CHRCODE CONCAT INITCAP INSTR LENGTH LOWER LPAD LTRIM RPAD RTRIM SUBSTR UPPER REPLACESTR REPLACECHR 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 For backwards compatibility only - use || instead