Resultados relacionados con el objetivo específico 02

Guidelines Scenario:

There are two Oracle tables that together contain vital information about the products sold by Mersche Motors. You will need to combine the data from both tables into a single staging table that can be used as a source of data for the data warehouse.

Objectives:

 Import relational source definitions.

 View relationships between relational sources.

 Use a Source Qualifier to define a homogeneous join and view the statement.

Duration:

30 minutes

Subject Review

PowerCenter will define a homogeneous join between the two Oracle source tables.

That source database server will perform an inner join on the tables based on a join statement automatically generated by the Source Qualifier. The join set will be loaded into the staging table.

Walkthrough:

Create a Relational Source Definition

1. If you are not already in the PowerCenter Designer Tool, please start it according to the instructions in Lab 2.1 using your Devxx login.

a. Open the Source Analyzer workspace and select Clear All.

i. Choose the menu option Sources  Import from Database.

ii. Connect using the ODBC Data Source SDBU, the username and password are the same.

iii. Import the relational tables PRODUCT and PRODUCT_COST.

iv. Save your work.

b. Your Source Analyzer workspace should appear as displayed in the figure below:

Tip: The arrow connecting the keys PRODUCT_ID AND PRODUCT_CODE denotes a relationship stored in the Informatica repository. By default, referential integrity (primary to foreign key) relationships defined on a database are imported when each of the tables in the relationship are imported. The arrow head is on the Primary Key (Parent) end of the relationship.

Walkthrough:

Create a Shortcut to the Target Definition

1. Open the Target Designer.

a. Right-click in the workspace and select Clear All.

b. Create a shortcut to STG_PRODUCT. Rename it SC_STG_PRODUCT.

2. Save your work.

Walkthrough:

Create a mapping

1. Open the Mapping Designer.

a. If a mapping is visible in the workspace, close it by selected menu option Mappings  Close.

b. Create a new mapping named m_STG_PRODUCT_xx.

c. Add the source definitions PRODUCT and PRODUCT_COST to the mapping.

d. Delete the SQ_PRODUCT and SQ_PRODUCT_COST source qualifier transformations automatically created by PowerCenter.

i. Select the Source Qualifier Transformation from the PowerCenter Transformation toolbar as shown highlighted below:

e. Click on the Mapping Designer workspace.

f. The Select Sources for Source Qualifier Transformation dialog box will appear as shown below:

g. Confirm that both sources are selected and click OK.

2. Double-click the Source Qualifier Transformation to enter edit mode.

a. Rename the object SQ_PRODUCT_PRODUCT_COST.

Add SC_STG_PRODUCT target to the mapping.

b. Link each of the output ports in the Source Qualifier

SQ_PRODUCT_PRODUCT_COST to the target SC_STG_PRODUCT ports with the same name.

c. Right-click anywhere in the workspace and use AutoLink by Name.

d. Link COST port to the PRODUCT_COST port.

3. Save your mapping and confirm that it is valid. Note that the PRODUCT_CODE port in the Source Qualifier is unlinked as intended as it is not needed at the target.

a. Your mapping will appear similar to the figure below:

Walkthrough:

SQL Select Statement

Examine the SQL Select Statement in the Source Qualifier 1. Edit the Source Qualifier

a. Click on the Properties tab.

b. Open the SQL Query Editor by clicking the arrow in the SQL Query property.

c. Click the Generate SQL button.

Note: the join statement can now be previewed, and that it is an inner join.

Also note that the PRODUCT_CODE column is not in the SELECT Statement; this is because the column is not linked in the mapping and is not needed.

d. Your SQL Editor should appear as displayed in the figure below:

e. Click OK twice.

f. Save your work.

Note: It is generally not a good practice to save the generated SQL unless there is a need to override it. If you cancel out of the SQL editor, then at runtime the

session will create what is called the ‘default query’. This is based on the ports and their links in the mapping. If you click OK and leave the SQL in the editor window, you’ve overridden (hardcoded) the default query. Anytime you want to link a new port out of the Source Qualifier (or delete a link), you would have to go in and regenerate the SQL.

Note: The relationship between PRODUCT_ID and PRODUCT_CODE was used to generate the inner join statement. If you desire to join two source tables on two columns that are not keys within the database, you may establish a relationship between them by dragging the foreign key to the primary key column in the Source Analyzer. You may also modify the join statement to make it an outer join.

Walkthrough:

Create and run the workflow

1. Launch the Workflow Manager and sign into your assigned folder.

a. If there is a workflow visible in the workspace, close it by selecting Workflows  Close.

2. Create a new workflow called wf_m_STG_PRODUCT_ xx.

a. Edit the s_m_STG_PRODUCT_xx session.

i. Under the Mapping tab:

1. Set the relational source connection property to SDBU.

2. Set the relational target connection property to STGxx.

3. Click OK.

3. Save your work.

a. Check the output when to ensure the workflow saved to the repository successfully.

4. Start the workflow.

a. Review the session properties.

b. Your information should appear as displayed in the figure below:

c. If your session failed or had errors troubleshoot and correct them by reviewing the session log and make any necessary changes to your mapping or workflow.

Walkthrough:

Review the Data Results

1. Preview the target data from the Designer. Your data should appear as displayed in the figure below: