Introduction to Informatica PowerCenter. Data Warehousing . Data warehousing is the entire process of data extraction, transformation, and loading of data to the warehouse and the access of the data by end users and applications . Data Mart.
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
Power Mart and Power Center access the following sources:
Power Mart and Power Center can load data into the following targets:
You can load data into targets using ODBC or native drivers, FTP, or external loaders.
The Informatica repository is a set of tables that stores the metadata you create using the Informatica Client tools. You create a database for the repository, and then use the Repository Manager to create the metadata tables in the database.
You add metadata to the repository tables when you perform tasks in the Informatica Client application such as creating users, analyzing sources, developing mappings or mapplets, or creating sessions. The Informatica Server reads metadata created in the Client application when you run a session. The Informatica Server also creates metadata such as start and finish times of a session or session status.
When you use Power Center, you can develop global and local repository to share metadata:
Global repository. The global repository is the hub of the domain. Use the global repository to store common objects that multiple developers can use through shortcuts. These objects may include operational or application source definitions, reusable transformations, mapplets, and mappings.
Local repositories. A local repository is within a domain that is not the global repository. Use local repositories for development. From a local repository, you can create shortcuts to objects in shared folders in the global repository. These objects typically include source definitions, common dimensions and lookups, and enterprise standard transformations. You can also create copies of objects in non-shared folders.
To create Repository
1. Launch the Repository Manager by choosing Programs-Power Center (or Power Mart) Client-Repository Manager from the Start Menu.
2. In the Repository Manager, choose Repository-Create Repository.
Note: You must be running the Repository Manager in Administrator mode to see the Create Repository option on the menu. Administrator mode is the default when you install the program.
3. In the Create Repository dialog box, specify the name of the new repository, as well as the parameters needed to connect to the repository database through ODBC.
Informatica tools include two basic types of security:
Ex :- Read , Write and Execute.
Folders provide a way to organize and store all metadata in the repository, including mappings, schemas, and sessions. Folders are designed to be flexible, to help you organize your data warehouse logically. Each folder has a set of properties you can configure to define how users access the folder. For example, you can create a folder that allows all repository users to see objects within the folder, but not to edit them. Or you can create a folder that allows users to share objects within the folder.
When you create a folder, you can configure it as a shared folder. Shared folders allow users to create shortcuts to objects in the folder. If you have reusable transformation that you want to use in several mappings or across multiple folders, you can place the object in a shared folder.
For example, you may have a reusable Expression transformation that calculates sales commissions. You can then use the object in other folders by creating a shortcut to the object.
Permissions allow repository users to perform tasks within a folder. With folder permissions, you can control user access to the folder, and the tasks you permit them to perform.
Folder permissions work closely with repository privileges. Privileges grant access to specific tasks while permissions grant access to specific folders with read, write, and execute qualifiers.
However, any user with the Super User privilege can perform all tasks across all folders in the repository. Folders have the following types of permissions:
Use ODBC connection for importing from database
You can create target definitions in the Warehouse Designer for file and relational sources. Create definitions in the following ways:
Import the definition for an existing target.Import the target definition from a relational target.
Create a target definition based on a source definition. Drag one of the following existing source definitions into the Warehouse Designer to make a target definition:
Relational source definition
Flat file source definition
COBOL source definition
Manually create a target definition. Create and design a target definition in the Warehouse Designer.
The source definition appears in the workspace. The Designer automatically connects a Source Qualifier transformation to the source definition. After you add the target definition, you connect the Source Qualifier to the target.
To Connect the Source Qualifier to Target Definition:
Click once in the middle of the <Column Name> in the Source Qualifier. Hold down the mouse button, and drag the cursor to the <Column Name> in the target. Then release the mouse button. An arrow (called a connector) now appears between the row columns
Aggregator performs aggregate calculations
Filter serves as a conditional filter
Router serves as a conditional filter (more than one filters)
Joiner allows for heterogeneous joins
Source qualifier represents all data queried from the source
Expression performs simple calculations
Lookup looks up values and passes to other objects
Sequence generator generates unique ID values
Stored procedure calls a stored procedure and captures return values
Update strategy allows for logic to insert, update, delete, or reject data
You can use the Expression transformations to calculate values in a single row before you write to the target.
For example, you might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers.
You can use the Expression transformation to perform any non-aggregate calculations.
You can also use the Expression transformation to test conditional statements before you output the results to target tables or other transformations.
To use the Expression transformation to calculate values for a single row, you must include the following ports:
Every mapping includes a Source Qualifier transformation, representing all the columns of information read from a source and temporarily stored by the Informatica Server. In addition, you can add transformations such as a calculating sum, looking up a value, or generating a unique ID that modify information before it reaches the target.
When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation.
The Source Qualifier represents the records that the Informatica Server reads when it runs a session. You can use the Source Qualifier to perform the following tasks:
To configure a Source Qualifier:
While a Source Qualifier transformation can join data originating from a common source database, the Joiner transformation joins two related
heterogeneous sources residing in different locations or file systems. The combination of sources can be varied. You can use the following sources:
If two relational sources contain keys, then a Source Qualifier transformation can easily join the sources on those keys. Joiner transformations typically combine information from two different sources that do not have matching keys, such as flat file sources.
The Joiner transformation allows you to join sources that contain binary data.
To create a Joiner Transformation:
Select the Condition tab and set the condition.
Un Connected Lookups
You can configure the Lookup transformation to perform different types of
lookups. You can configure the transformation to be connected or unconnected, cached or uncached:
When you design your data warehouse, you need to decide what type of information to store in targets. As part of your target table design, you need to determine whether to maintain all the historic data or just the most recent changes.
For example, you might have a target table, T_CUSTOMERS, that contains customer data. When a customer address changes, you may want to save the original address in the table, instead of updating that portion of the customer record. In this case, you would create a new record containing the updated address, and preserve the original record with the old customer address. This illustrates how you might store historical information in a target table. However, if you want the T_CUSTOMERS table to be a snapshot of current customer data, you would update the existing customer record and lose the original address.
The model you choose constitutes your update strategy, how to handle changes to existing records. In Power Mart and Power Center, you set your update strategy at two different levels:
During session configuration, you can select a single database operation for all records. For the Treat Rows As setting, you have the following options: