Build Reusable Code in Informatica PowerCenter Using Mapplets

  Level2 Training , Transformations

Reusability is a great feature in Informatica PowerCenter which can be used by developers. Its general purpose is to reduce unnecessary coding which ultimately reduces development time and increases supportability. In this article lets see how we can build mapplet in Informatica PowerCenter to make your code reusable.

What is Mapplet

Mapplet is a reusable object that you create in the Mapplet Designer. It contains a set of transformations and lets you reuse the transformation logic in multiple mappings. When you use a mapplet in a mapping, you use an instance of the mapplet. Any change made to the mapplet is inherited by all instances of the mapplet.

Solution

Lets consider a real time scenario for the demonstration.

The Sales Department is interested in getting both the quarterly and yearly sales. This calculation is required in multiple ETL process. So decided to create a reusable code using Mapplet.

• Build a mapplet that uses multiple sources and aggregate functions.
• Create a variable within the mapplet for use in the aggregate functions.

Mapplet Layout

I. Set the Mapplet Designer Options

1. Manually create a Source Qualifier to pull in data from multiple source definitions. To build a custom Source Qualifier, you must set the Mapplet Designer Options correctly.
2. Select TOOLS | OPTIONS.
3. Click the Format tab.
4. In the Category section, choose Mapplet Designer from the pull-down list.
5. In the Tables section, uncheck the Create Source Qualifiers When Opening Sources box.
6. Click OK.

II. Create a New Mapplet

1. Switch to Mapplet Designer.
1. Select TOOLS | MAPPLET DESIGNER, or
2. Click on the button.
2. Create a Mapplet, by selecting MAPPLETS | CREATE.
3. Name the mapplet MPLT_QtrSales_x.

III. Analyze the source tables.

1. Bring the source definitions ITEMS, ORDER_ITEMS, and ORDERS into the Mapplet Designer Workspace by dragging them from the Navigator Window into the workspace.
2. Create the Source Qualifier either from TRANSFORMATIONS | CREATE, or use the Source Qualifier icon from the transformation toolbar. Name it SQ_SalesByQtr_x.

IV. Create an Aggregator transformation

1. Create an Aggregator transformation and name it Agg_SalesByQtr_x
2. Copy and link ITEM_ID and ITEM_NAME from the Source Qualifier (SQ_SalesByQtr_x) into the aggregator(Agg_SalesByQtr_x).
3. Double-click on Agg_SalesByQtr_x to edit the Aggregator.
4. On the Columns tab, add the following ports:
• YEAR
• MONTH
• Q1SALES
• Q2SALES
• Q3SALES
• Q4SALES
5. Enter the expression for YEAR as TO_CHAR(SQ_SalesByQtr_x.DATE_ENTERED,’YYYY’)
   NOTE: Notice that you now have a new input port DATE_ENTERED in your Aggregator transformation. The local input port is automatically added when the external reference made to the SQ_SalesByQtr_x is validated.
6. Build the expressions for the Variable and Output ports as follows:
• MONTH = GET_DATE_PART(DATE_ENTERED,'MM')
• Q1Sales = SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 1 OR MONTH = 2 OR MONTH = 3)
• Q2Sales = SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 4 OR MONTH = 5 OR MONTH = 6)
• Q3Sales = SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 7 OR MONTH = 8 OR MONTH = 9)
• Q4Sales = SUM(QUANTITY * PRICE – DISCOUNT, MONTH = 10 OR MONTH = 11 OR MONTH = 12)
7. Group records by: ITEM_ID, ITEM_NAME and YEAR.
8. Add the Aggregate functions in the expressions. Your Ports tab will look something like in below image.  
   
9. Select the Properties tab.
10. Check the Sorted Input box.
11. Exit the Edit Transformation dialog box by clicking the OK button.
12. Edit the Source Qualifier.
13. You must now identify to Informatica that the data will be sorted by ITEM_ID, ITEM_NAME, and DATE_ENTERED.
    NOTE: The ports in the Source Qualifier must be in the same order as the ports in the Aggregator, in order to facilitate the correct summarization by the groupings you have specified, above.
14. Double-click the Source Qualifier transformation.
15. Select the Properties tab.
16. Open the SQL query window by clicking on the icon.
17. Click the Generate SQL button.
18. Append the following text to the end of the default SQL statement: ORDER BY, ITEMS.ITEM_ID, ITEMS.ITEM_NAME, ORDERS.DATE_ENTERED
    
19. Enter the ODBC data source, User name, and Password given by your instructor.
20. Click the Validate button.
21. Confirm that there are no errors in the SQL.
22. Click OK to exit the SQL editor.
23. Click OK to exit the Source Qualifier Transformation.

V. Create a Mapplet Output Transformation

1. Select TRANSFORMATION | CREATE or Click on the icon from the Transformations toolbar. Name it Output_SalesByQtr_x
2. Select all of the output ports of the Aggregator and drag them into the mapplet Output transformation.
3. Select MAPPLET | VALIDATE from the menu.
4. Verify the results of the mapplet validation in the Output Window.
5. Save the repository.

We are all done and below is the completed Mapplet layout.

Video Tutorial

Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise.

Share with your friends