There are many situations where you need to perform the same calculation on several different columns. It could be something as simple as converting a date to a different format, or adding dashes to an id number so that it displays as you would expect a social security number to display, or something much more complex that requires you to perform the same calculations on different columns of data. Instead of coding the calculation over and over for each column, it would be more efficient to create the calculation once, and then use it whenever you need that calculation. In essence, you create a reusable transformation.

Before you build the transformation, you must first create the function to perform the necessary transformation(s). Functions are created in the DMC as a server object with the type “Define Function”.

This technique is divided into two parts. In the first part, we build the function. In the second, we use the function in transformations within a data flow, then incorporate the data flow object into a process flow to make it available at execution time. In the following example, we build two functions:

• DMPROPER converts a name to “last name, first name” format, where the first letter of each name is in upper case and the rest of the name is in lowercase.
• DMDATFMT converts a date to “2009-JUL-04” format, which includes dashes between the year, month, and day.

These routines are stored in a stored procedure which is identified in the Data Management Console with a distinguished icon and a type of “Userin Functions.” In the following example, DMHR, a human resource table, is the data source, and the extracted records are loaded into a new data target named EMPDATES. Employee name (PROPERNAME), start date (START), and termination date (TERM) are calculated using the define functions.

Refer to the sample data flows in the ibisamp directory, DMREUSE, for the complete example. The User Functions procedure DMFNS contains the sample function DMPROPER and DMDATFMT.

The first function, which we call DMPROPER, takes two parameters, a last name and a first name. It returns a formatted “proper” name.

In the DMC:

1. Right-click an application directory in the navigation pane, select New and then Define Function.

   The Define Function View opens in the workspace in tree view.

2. Click FUN1 and change the name to DMPROPER.
3. Under the Parameters folder, click PARAM1. Its properties panel opens to the right. In the PARAM field box, click the PARAM1 value and type the name of the first parameter LASTNAME. In the USAGE field box, type in the value A17.
4. Right-click the Parameters folder and select Add Parameter. In the PARAM field box in the properties panel, click the PARAM1 value and type the name of the first parameter FIRSTNAME. In the USAGE field box, type in the value A14.
5. Under Defines folder, click DMPROPER. Its properties panel opens to the right. In the EXPRESSION field box, click the ellipsis button.

   The Define Calculator opens.

6. Enter A34Vin the Format field.

   Type

   LCWORD(17, LASTNAME, 'A17') || (', ' | LCWORD(14, FIRSTNAME, 'A14'))

   in the Expression field and click OK to close the calculator.

7. Click theSave As button and save the function as dmfns.

The second function converts a date to a common format.

1. Right-click DMFNS and choose Add Function.

   A new function prototype is added.

2. Click FUN1 and change the name to DMDATFMT.
3. Under the Parameters folder, click PARAM1. Its properties panel opens to the right. In the PARAM field box, click the PARAM1 value and type the name of the first parameter INDATE. In the USAGE field box, type in the value YYMD and in the DEFAULT field box, type in the value &YYMD.
4. Right-click the Defines folder and select Add Define. In the DEFINE field box in the properties pane, click the DEFINE1 value and type the name of the parameter CONVDATE. In the EXPRESSION field box, type in the value

   DATECVT( INDATE, 'YYMD', 'A8YYMD' )

   and in the FORMAT field box, type in the value A8.

5. Under the Defines folder, click FUNC02. Its properties panel opens to the right. In the DEFINE field box, click the FUNC02 value and type the name of the first parameter DMDATFMT. In the EXPRESSION field box, type in the value

   CHGDAT( 'YYMD', 'YYMTD', CONVDATE, 'A11' )

   and in the FORMAT field box, type in the value A11.

6. Test the transform by clicking the Test Function button. A Report window opens showing the default value of today’s date, as shown in the following image.

   

7. Click the Save button.

Note: When creating a define function, the following names must all be the same:

• The name of the function.
• The name of the last define in the function.

In the DMC:

1. Right-click an application directory in the navigation pane and choose New, then Flow. The Data Flow tab opens in the workspace, with the SQL object displayed.
2. Drag the data source object DMHR from the ibisamp application directory in the navigation pane into the workspace, to the left of the SQL object.
3. Right-click the DMHR object and select Source Transformations.

   The Source Transformations window opens.

4. Click the Insert transforms button.

   The Transformations Calculator opens.

5. Enter PROPERNAME in the Column field and A30 in the Format field.
6. Click the Functions tab and expand the User Defined folder, followed by the baseapp directory and the dmfns procedure.

   

7. Double-click DMPROPER.

   The Function Assist window opens. Enter LNAME and FNAME for the two parameters shown.

   

8. Click OK to close the Function Assist window and the Transformation Calculator.
9. Click the Insert transforms button.
10. Enter START in the Column field and A12 in the Format field.

    Click the Functions tab. Expand the User Defined folder, the baseapp directory, and the dmfns procedure.

11. Double-click the DMDATFMT function. The function assistant opens. Enter START_DATE as the parameter for the function.

    Click OK to close the Function Assist and the Transformations Calculator.

12. Enter ENDDATEin the Column field and A12 in the Format field.
13. Click the ellipsis button in the Format field. Select the Show Advanced Properties checkbox and select the Allow Missing Data checkbox.

    Repeat the above procedure to select the DMDTEFMT function, using END_DATE as the parameter.

14. When you have completed the transformations, the Transformations window should appear as shown in following image.

    

15. Click OK to close the Transformations window.
16. Right-click the SQL object and select Column Selection.

    The Column Selection window opens.

17. In the Available Columns list, select PROPERNAME, START, and ENDDATE and click the > arrow to add them to the Selected Columns list.
18. To test the transformations, click the Test SQL Statement button. Note that the transformations are correct, with PROPERNAME correctly defined, and the correct START and ENDDATE dates.
19. Click X to close the Test SQL window, and OK to close the Column Selection window.
20. Right-click the workspace to the right of the SQL object and select Add Target, then New.
21. In the new target, right-click and select Properties.
22. In the erties panel, select the Adapter you used for your sample procedures, the application directory you are using for your samples, and enter EMPDATES as the synonym. Click X to close the panel.
23. Save the flow as DMXREUSE.
24. Click the Run button, and select Submit from the drop-down menu.

To verify that the transformations have worked properly, click the Data Flow tab and right-click the EMPDATES data target and select Toggle. Click the Sample Data tab to review the output.