Basic A Power Center 1

Informatica PowerMart / PowerCenter 7 Basics Education Services PC6B-20030512

 Informatica Corporation, 2003. All rights reserved.

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

2

Extract, Transform, and Load Operational Systems RDBMS Mainframe

Decision Support Data Warehouse

Other

Aggregate Data • Transaction level data Cleanse Data • Optimized for Transaction Consolidate Data Response Time Apply Business Rules De-normalize • Current • Normalized or DeTransform Normalized data

Extract

ETL

• Aggregated data • Historical

Load 3

PowerCenter Architecture

4

PowerCenter 7 Architecture Server

native

native Targets

Sources

TCP/IP

Repository Server

Heterogeneous Targets

TCP/IP

Heterogeneous Targets

Repository Agent

native Repository Designer Workflow Workflow Rep Server Manager Manager Monitor Administrative

Repository

Console

Not Shown: Client ODBC Connections for Source and Target metadata

5

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 6

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 7

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 Server Administration Console

Manager Client overhead for Repository management is greatly reduced by the Repository Server

Repository 8

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

9

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 10

Repository Server Administration Console

Information Nodes

HTML View

Console Tree Hypertext Links to Repository Maintenance Tasks 11

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 12

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.

13

Repository Manager Interface

Navigator Window

Main Window

Dependency Window Output Window

14

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

Managing Privileges Check box assignment of privileges

16

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 17

Object Locking  Object Locks preserve Repository integrity  Use the Edit menu for Viewing Locks and Unlocking Objects

18

Object Searching

(Menu- Analyze – Search)  Keyword search •

Limited to keywords previously defined in the Repository (via Warehouse Designer)

 Search all •

Filter and search objects

19

Object Sharing  Reuse existing objects  Enforces consistency  Decreases development time  Share objects by using copies and shortcuts COPY

SHORTCUT

Copy object to another folder

Link to an object in another folder

Changes to original object not captured Duplicates space

Dynamically reflects changes to original object Preserves space

Copy from shared or unshared folder

Created from a shared folder

Required security settings for sharing objects: • Repository Privilege: • Originating Folder Permission: • Destination Folder Permissions:

Use Designer Read Read/Write

20

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

21

Sample Metadata Extensions

Sample User Defined Metadata, e.g. - contact information, business user

Reusable Metadata Extensions can also be created in the Repository Manager

22

Design Process

1. Create Source definition(s) 2. Create Target definition(s) 3. Create a Mapping 4. Create a Session Task 5. Create a Workflow from Task components 6. Run the Workflow 7. Monitor the Workflow and verify the results 23

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

24

Source Analyzer

Designer Tools

Analyzer Window Navigation Window

25

Methods of Analyzing Sources

    

Repository

Import from Database Import from File Import from Cobol File Import from XML file Create manually

Relational

XML file

Source Analyzer

Flat file

COBOL file 26

Analyzing Relational Sources Source Analyzer

Relational Source ODBC

Table View Synonym DEF

Repository Server TCP/IP

Repository Agent

native

DEF

Repository 27

Analyzing Relational Sources Editing Source Definition Properties

28

Analyzing Flat File Sources Source Analyzer Mapped Drive NFS Mount Local Directory

Flat File DEF

Fixed Width or Delimited Repository Server TCP/IP

Repository Agent

native

DEF

Repository 29

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

XML Source Analysis Source Analyzer

Mapped Drive NFS Mounting Local Directory

.DTD File DEF

DATA

Repository Server TCP/IP

Repository Agent

native

DEF

In addition to the DTD file, an XML Schema or XML file can be used as a Source Definition

Repository 31

Analyzing VSAM Sources Source Analyzer Mapped Drive NFS Mounting Local Directory

Repository Server

.CBL File DEF

DATA

TCP/IP

Repository Agent

native

DEF

Supported Numeric Storage Options: COMP, COMP-3, COMP-6

Repository 32

VSAM Source Properties

33

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

34

Creating Target Definitions Methods of creating Target Definitions  Import from Database  Import from an XML file  Manual Creation  Automatic Creation

35

Automatic Target Creation Drag-anddrop a Source Definition into the Warehouse Designer Workspace

36

Import Definition from Database Can “Reverse engineer” existing object definitions from a database system catalog or data dictionary Warehouse Designer

Database ODBC

Repository Server

DEF TCP/IP

Table View Synonym

Repository Agent

native Repository DEF

37

Manual Target Creation 1. Create empty definition

2. Add desired columns

3. Finished target definition

ALT-F can also be used to create a new column

38

Target Definition Properties

39

Target Definition Properties

40

Creating Physical Tables

DEF DEF

DEF

Execute SQL via Designer

LOGICAL

PHYSICAL

Repository target table definitions

Target database tables 41

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)

42

Transformation Concepts 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 43

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 44

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         

45

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 46

Edit Mode Allows users with folder “write” permissions to change or create transformation ports and properties Define port level handling

Define transformation level properties

Enter comments Make reusable

Switch between transformations

47

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

Click here to invoke the Expression Editor

Usage • Perform majority of data manipulation 48

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

49

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

50