1 / 73

Informatica Overview

Informatica Overview. Contents . Introduction Clients Server(s) Source, Target, Repository Connectivity. What is Informatica? . Allows you to load data into a centralized location, such as a datamart or data warehouse. ETL Tool E xtract data from multiple sources

halia
Download Presentation

Informatica Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Informatica Overview

  2. Contents • Introduction • Clients • Server(s) • Source, Target, Repository • Connectivity

  3. What is Informatica? • Allows you to load data into a centralized location, such as a datamart or data warehouse. • ETL Tool • Extract data from multiple sources • Transform the data according to business logic and needs • Load the transformed data into file and relational targets

  4. Example EMPLOYEE EMP_DETAILS EMP_ID EMP_NAME EMP_CITY EMP_STATE EMP_COUNTRY EMP_DATE_OF_JOINING EMP_ID EMP_NAME EMP_CITY EMP_STATE EMP_COUNTRY YRS_OF_SRV Transform Date of Joining to Yrs of Service

  5. Data Warehousing Developer End User Metadata Extract Transform Load Operational Sources Data Warehouse

  6. Informatica Architecture • Design Process • Client Tool Review • Repository Manager • Designer • Server Manager

  7. Informatica’s Architecture Data Models Designer Repository Manager Server Manager Targets 1-n Sources 1-n PowerPlugs Repository Server

  8. Informatica Design Process 2. 1. 3. Source Def 5. Mapping Sessions Target Def 4. 1. Create Repository 2. Import Source Definitions 3. Create Target Schema 4. Create Mappings 5. Load Data

  9. Informatica Client • Repository Manager – Can view much of the metadata in the Repository through the Repository Manager. • Designer – Create Source-to-Target mappings that contain transformation instructions for the Informatica Server. • Server Manager – Create, schedule, and monitor sessions. You create a session based on a transformation and schedule it to run on the Informatica Server.

  10. Informatica Client Repository Manager

  11. Metadata Repository • Information about the data mart system • Catalogs the repository • Directs the server • Contains record of user access • Can be shared • Can be searched and reported • Bridged through Metadata Exchange

  12. Navigator Window Analysis Window Dependency Window Output Window Repository Manager

  13. Folder Attributes • FOLDER OWNER - user who serves as focal point for folder permissions • PERMISSIONS - rights to read, write, and/or execute objects in a folder • SHARED - property that allows you to make shortcuts to objects in a folder • SHORTCUT - a dynamic link to an object stored in a shared folder • VERSIONS - folder iterations that indicate development stages

  14. Informatica Client Designer

  15. Designer Workspace OpenFolderList Navigator Workspace Workbook Tabs OutputWindow Status Bar

  16. Designer Options • Tables • columns viewed • column size • object size • object colors • Format • workspace colors • import keys • automatic Source Qualifier creation • General • workspace options • reload objects on open • group source definitions

  17. Informatica Client Server Manager

  18. Navigator Configure Window Monitor Window Output Window Server Manager

  19. Designer • Source Analyzer • Warehouse Designer • Transformation Developer • Mapplet Designer • Mapping Designer

  20. Source Analyzer • Identify the sources used to build the warehouse. • Create repository definitions for these sources

  21. Analyzing Sources • Relational – Oracle, Sybase, Informix, IBM DB2, Microsoft SQL Server, and Teradata • File – Fixed and delimited flat file, COBOL file, and XML • Other – Microsoft Excel, Microsoft Access • Extended – PeopleSoft, SAP R/3, Sieble, and IBM MQSeries (need to purchase additional products for these sources) • Mainframe – Need to purchase additional products.

  22. Warehouse Designer • Create relational tables in Target database • Edit target definitions • Preview relational target data

  23. Targets • Relational – Oracle, Sybase, Sybase IQ, Informix, IBM DB2, Microsoft SQL Server, and Teradata • File – Fixed and delimited flat files and XML • Extended – SAP BW, IBM MQ Series (need to purchase additional products for these targets) • Other - Microsoft Access

  24. Mixing Sources and Targets • You can combine data from different platforms and source types. Oracle Sybase Flat File

  25. Transformation Developer • Generates ,modifies, passes data through ports • 12 objects for transforming data

  26. Transformations Types • Source Qualifier represents all data queried from the source • Normalizer normalizes records from VSAM or relational sources • Expression performs simple calculations • Filter serves as a conditional filter • Aggregator performs aggregate calculations • Rank limits records to top or bottom range

  27. Transformations Types contd… • Update Strategy allows for logic to insert, update, delete, or reject data • Lookup looks up values and passes to other objects • Stored Procedure calls a stored procedure and captures return values • External Procedure calls a procedure defined in a shared library • Sequence Generator generates unique ID values • Joiner allows for heterogeneous joins

  28. SourceQualifier Transformation • Represents records that Informatica server reads when it runs a session • Automatically attached when a Source is added to a mapping

  29. Use a Source Qualifier to: • Filter Records when the Informatica Server reads source data • Specify sorted ports • Order by clause • Select only distinct values from a source • Create a custom query for the Informatica Server to read source data

  30. Expression Transformation • Calculate values in a single row • Adjust employee salaries, concatenate first and last names, convert string to number… • Perform Any Non-Aggregate Calculations • Test conditional statements before you output to target

  31. Example EMPLOYEE EMP_SALARY EMP_ID EMP_NAME ROLE_CODE BASIC_SALARY EMP_ID EMP_NAME ROLE_CODE GROSS_SALARY Gross Salary= Basic Salary * 3.5

  32. Aggregator Transformation • Allows you to perform aggregate calculations, such as averages and sums • While the Expression is on a row-by-row basis, the aggregator can perform calculations on groups

  33. Example REVENUE PU_REVNUE PU_CODE PROJECT_CODE REVENUE PU_CODE MAX_REVENUE MIN_REVENUE AVG_REVENUE Aggregator Transformation Max Revenue = Max (Revenue) Min Revenue = Min(Revenue) Avg Revenue = Avg (Revenue)

  34. Filter Transformation • Provides the means for filtering rows in a mapping • Employees who are currently working in the project “NML” of “WENA” as “SE” • Only rows that meet the condition pass through the mapping.

  35. Filter Transformation • All ports are input/output • Returns TRUE or FALSE for each row passed through the mapping based on the condition • Discarded rows do not appear in the session log or reject files • The input ports must only come from one transformation

  36. Filter vs Source Qualifier (SQ) • SQ provides better performance • SQ only lets you filter rows from relational sources, Filter Transformation filters rows from any source • SQ only uses standard SQL, Filter can use any statement or function that returns True/False

  37. Example EMPLOYEE NM_EMP_DETAILS EMP_ID EMP_NAME PROJECT_CODE PU_CODE ROLE_CODE EMP_ID EMP_NAME PROJECT_CODE PU_CODE ROLE_CODE FilterTransformation Where Project = “NML” and PU = “WENA” and Role = “SE”

  38. Router Transformation • Groups data into many groups • Routes rows of data that do not meet any condition to a default group • Can enter any expression that returns a single value • Condition returns True or False for each row • If the condition = NULL, the row is assumed as FALSE

  39. Router Transformation • One Group can be connected to One transformation or target • One Output Port can be connected to multiple transformations or targets • Multiple Output ports in One Group can be connected to multiple transformations or targets • CANNOT Connect more than One Group to One Transformation or Target

  40. Lookup Transformation • Looks up data in a relational table • Can be the Source, Target, or any database that the Informatica Client and Server can connect to • Lookup table can be a single table or can join multiple tables • Lookups can: • Get a related value (your source include Employee_ID and you want Employee_Name), • Perform a calculation • Update a slowly changing dimension table (check if records exist on a target)

  41. Lookup Transformation • For each input row, the Informatica Server queries the lookup table based on the lookup ports and the condition in the transformation • The Informatica Server can return values from that lookup (static cache) • The Informatica Server inserts a row into the cache to flag rows as new or existing (dynamic cache) OR

  42. Connected and UnconnectedLookup Transformations

  43. Example EMPLOYEE_PROJECT NM_EMP_DETAILS EMP_ID EMP_NAME PROJECT_CODE PROJECT_DESC EMP_ID EMP_NAME PROJECT_CODE LOOK UP Transformation PROJECT Get PROJECT.PROJECT_DESC Where PROJECT.PROJECT_CODE = NM_EMP_DETAILS.PROJECT_CODE PROJECT_CODE PROJECT_DESC

  44. Update Strategy • Two Ways Of doing • Within a Session • Within a Mapping

  45. Update Strategy • Within a Session • Instruct the Informatica Server how to treat the rows when the session is configured • Within a Mapping • Use the update strategy transformation to flag records for insert, delete, update, or reject.

  46. Constraint for each Database Operation

  47. Joiner Transformation • Active Transformation • Join two flat files • Join two tables from different databases • Join a flat file with a relational table

  48. Transformation Overview Three views: • Iconized View -- shows transformation in relation to mapping • Normal View -- shows data flow through transformation • Edit View -- shows transformation properties and allows for editing

  49. Transformation Overview Normal view shows data flow through the transformation Data passes through I/O ports unchanged • DATE_ENTERED passes into transformation through an input port. • It is used in MTH port to extract month. • Month is passed through MTH output port to another transformation.

  50. Define port level handling Define transformation level properties -Switch between transformations -Enter comments -Make reusable Transformation Overview Edit view provides flexibility in defining transformation rules

More Related