3yK-ybvDRCiivsm7w4QoSQ_92a27be435e341c18998b9f8311f93f1_PentahoDataIntegrationTutorialPostgreSQL

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 3 Figure 2: Spoon Opening Window 1. Managing Database Connections Pentaho Data Integration allows you to define connections to multiple databases provided by multiple database vendors (MySQL, Oracle, PostgreSQL, and many more). Pentaho Data Integration installs with the most suitable JDBC drivers for supported databases and its primary interface to databases is through JDBC. Vendors write a driver that matches the JDBC specification and Pentaho Data Integration uses the driver. Unless you require extensive debugging or have other needs, you will not ever need to write your own database driver. When you define a database connection, the connection information (username, password, port number, and so on) is stored in the Pentaho Enterprise Repository and is available to other users when they connect to the repository. If you are not using the Pentaho Enterprise Repository, the database connection information is stored in the XML file associated with a transformation or job. Connections that are available for use with a transformation or job are listed under the Database Connection step in the explorer View in Spoon. There are several ways to define a new database connection. You will configure the database connection later in this tutorial.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 6 Figure 4: Transformation using Microsoft Access Input To provide guidance about fields in stream records, Pentaho provides menu choices to examine the input and output fields for each step in a transformation. A right click on a step provides a menu with items (Figure 5) for examining the input (Input Fields …) and output (Output fields …) fields on the stream for that step. Selecting Input Fields … for the Sort rows step of the Excel transformation (Figure 3) provides details of the stream input to the step as shown in Figure 6. All fields originate (Step origin) in the SSExcelData step. Figure 5: Menu Choices for Each Step

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 9 Figure 9: List of Input Steps with Microsoft Excel Input step in the Transformation Design Window o Select and drag a Microsoft Excel Input step into the canvas on the right. o Double Click on the Microsoft Excel Input step. The edit properties dialog box (Figure 10) associated with the Microsoft Excel Input step appears. In this dialog box, you specify the properties related to a particular step.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 12 Figure 14: SSExcelData Icon Step 5 – In this part of the tutorial, you will add constraint checking for null values in stream records obtained from the Excel data source. The rows from the Excel data source were added to the stream (data maintained in a Pentaho transformation) in the output of the first step. o Add a Filter Rows step to your transformation. Under the Design table, go to Flow  Filter Rows . o Create a “hop” between the SSExcelSource (Excel file input) step and the Filter Rows step. Hops are used to describe the flow of data in your transformation. To create the hop, click the SSExcel Source (Excel file input) step, then press the < SHIFT > key down and draw a line to the Filter Rows step (Figure 15). Figure 15: Hop connecting an Excel Input Step Connected to a Filter Rows Step o Alternatively, you can draw hops by hovering over a step until the hover menu (Figure 16) appears. Drag the hop painter icon from the source step to your target step. Figure 16: Hover Menu o Double-click the Filter Rows step. The Filter Rows edit properties dialog box appears (Figure 17).

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 15 Figure 19: Comparison Operator List

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 18 o Under the Design tab, expand the contents of the Input step. o Click and drag a Table Input step into your transformation. o Double-click the Table Input step to open its edit properties dialog box (Figure 23). o Rename your Table Input step to SSTimeDim . Figure 23: Property Edit Window of Table Input Step o Click “ New… ” next to the connection field. You must create a connection to the database. The Database connection dialog box appears. o Provide the settings for connecting to the database as shown in Figure 24. You can choose any name for the connection. o IMPORTANT : Before setting the connection information, you should ensure that the database is created in PostgreSQL along with the tables and the records inserted into those tables. Here are the details to connect to the PostgreSQL 12.2. The Database Name and the Username are the one is the one that you create in PostgreSQL. The full value for database connection is given in the remainder of step 1.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 21 o Add another sort rows component Sort rows 2 , and a hop connecting the SSTimeDim step. In the field specification (Figure 27), delete other fields except TIMEDAY, TIMEMOHTH, TIMEYEAR fields. Figure 27: Property Edit Window of Sort Rows 2 Step o Under the Design tab, expand the contents of the Joins step. o Click and drag a Merge Join step into your transformation; create a hop between the Sort rows, Sort rows 2 and Merge Join steps (Figure 28). Figure 28: Two Sort Rows Steps Connected to Merge Join Step o Double-click the Merge Join step to specify its properties (Figure 29). Set First step as Sort rows , Second step as Sort rows 2 , and Join Type as INNER . Click both of the “ Get key fields ” at left and right to get the possible fields to join. In the left table, delete other

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 22 fields except Day, Month and Year fields. In the right table, delete other fields except TIMEDAY , TIMEMONTH , and TIMEYEAR fields. Then click OK. Figure 29: Property Edit Window of Merge Join Step o Now, we have finished inner join between Excel input and SSTimeDim table. Step 2 – Inner join the SSItem , SSCustomer , and SSStore tables. Like getting data from the SSTimeDim table in the previous section, inner joining these tables requires Table Input components. First, you should set the connection and SELECT statement for the SSItem table. Note that these tables should exist in your PostgreSQL schema before these steps. o Drag and drop the Table Input 2 into the design pane. o Double click on the newly created component to open its Basic Settings pane. Specify the connection as shown in previous figure. o Use “SSItem” as the Table Name value and “SELECT * FROM SSItem” as the Query value. o Create two sort rows components: Sort rows 3 and Sort rows 4 , connecting Merge Join and SSItem respectively. See the field to be sorted as: ItemID and ITEMID respectively. o Drag and drop the Merge Join 2 into the design pane. Connect Sort rows 3 and Sort rows 4 to Merge Join 2 . Set the field to be joined as ItemID and ITEMID . o Figure 30 shows all steps and hops to the Merge join 2 step.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 23 Figure 30: Transformation Design Showing Steps and Hops to the Merge Join 2 Step Step 3 – Inner join the tables. o Inner join the tables named SSCustomer and SSStore in your transformation using the same method described previously. o For the SSCustomer step, connect the CustID (from Excel file) and CUSTID (from Database) fields. o For the SSStore step, connect the StoreID (from Excel file) and STOREID (from Database) fields. o Figure 31 shows all steps and hops after the Merge join 4 step.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 24 Figure 31: Transformation Design Showing Steps and Hops after the Merge Join 4 Step Step 4 – Create and connect an Add Sequence step to generate values for the SalesNo column. o Under the Design tab, expand the contents of the Transform step. o Click and drag an Add sequence step into your transformation; create a hop between the Merge Join 4 and Add Sequence steps (Figure 32). To create the hop, click the Merge Join 4 step, then hold the < SHIFT > key down and draw a line to the Add Sequence step. o Double click on the newly created component to open its Basic Settings pane. o Set SalesNo as the name of value. Check the box for use DB to get sequence. Select the connection as PostgreSQL12.2. Set SSSalesNoSeq as sequence name (Figure 33). Figure 32: Transformation Design Showing Steps and Hops after the Add Sequence Step

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 25 Figure 33: Property Edit Window of Add sequence Step 5. Insert data into the SSSales table o Under the Design tab, expand the contents of the Output step. o Click and drag an Insert/Update step into your transformation; create a hop between the Add sequence and Insert/Update steps. Figure 34 shows the Insert/Update step ( SSSales ) connected to Add sequence step.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 26 Figure 34: Connect Insert/Update Step to the Add Sequence Step o Double click the Insert/Update step, to specify its properties (Figure 31). Set the step name as SSSales . Select the connection as PostgreSQL12.2 . Type in the Target table as SSSales or click the Browse button and select the table from the list. Do not click the “ Get fields ” button. Instead, select SalesNo from the two sources and set the comparator to = . The final window should look like Figure 35. o Click the button “ Get Updated fields ” and then click on “ Edit mapping ” button to edit mapping. The mapping edit window is shown in Figure 36. Select the fields named SalesUnits , SalesDollar , SaleCost , CustID, StoreID , ItemID TIMENO and SalesNo into the mappings field. Pentaho will automatically match the corresponding name in the Target field. You may need to choose SalesNo stream field has matched with SALESNO column. Then click OK .

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 27 Figure 35: Property Edit Window of Insert/Update Step

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 28 Figure 36: Mapping Edit Window o Check the button “Don’t perform any updates:”. The final view of the SSSales step will look like Figure 37.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 29 Figure 37: Final view of the SSSales step o Select the SSSales step and run a preview by clicking on . In the transformation debug dialog, click on Quick Launch (Figure 38). o The Examine preview data window is displayed in Figure 39.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 30 Figure 38: Transformation Debug Dialog Figure 39: Examine Preview Data Window

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 31 o To examine the details of each step, you should examine the Execution Results window below the design pane. The Step Metrics tab (Figure 40) shows details about the execution of each step. You should verify that the SSSales step has 8 output rows. Figure 40: Step Metrics in the Execution Result Window o Each step in the transformation should have a check mark indicating execution as shown in Figure 41. o Connect to your PostgreSQL account (on your PC) so you can verify the number of rows in the SSSales table. You should see 200 rows with 8 new rows added to the 192 rows in the PostgreSQL SSSales table. o If you do not see the extra rows, the PostgreSQL output component had a failure. To see the error, check the Logging and Step Metrics tabs of the Execution Results window.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 32 Figure 41: Transformation Design with Check Marks for Each Step 6. Load second data source from Access The next part of the exercise involves creation of a new transformation to process the Access data source. Make sure that you have downloaded the Access database file from the class website and noted its location on your computer. Create a new transformation using File  New  Transformation with name “SSStoreTestAccess”. Use File  Save As … to save the transformation file as “SSStoreTestAccess” to a folder of your choice. Then, you will begin by loading the rows from a table in the Access database. Step 1- Add the Access Input Step o Under the Design tab, expand the Input step. Figure 42 shows the Design table and input step. o Select and drag a Microsoft Access Input step onto the canvas on the right. o Double Click on the Microsoft Access Input . The edit properties dialog box associated with the Microsoft Access Input step appears (Figure 43). In this dialog box, you specify the properties related to this step.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 33 Figure 42: New Microsoft Access Input Step Figure 43: Property Edit Window of Microsoft Access Input Step

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 34 o Set name for the Access Input as Sales and specify the Excel data source path in the Files tab. o In the tab named Content , click the button “ Get tables ” of table section. There will appear a window (Figure 44). Select Sales as the table name, click OK . Figure 44: Table Selection Window o In the tab named Fields , click the button “ Get fields ”. There will appear a list (Figure 45) showing the fields in the table named Sales . Figure 45: Fields Window for Microsoft Access Input Property Editing o Click the button “ Preview rows ” to preview the database (Figure 46). When asked for the number of rows type 12 and click OK.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 35 Figure 46: Examine Preview Data Window o Click OK at the bottom of the window. The input icon will change to the shape shown by Figure 47. Figure 47: Sales Step Icon Step 2 –You will add constraint checking for null values using the Filter Rows step. o Add a Filter Rows step to your transformation. Under the Design table, go to Flow  Filter Rows . o Create a hop between the Sales (Access file input) step and the Filter Rows step. Hops are used to describe the flow of data in your transformation. To create the hop, click the Sales (Access file input) step, then press the < SHIFT > key down and draw a line to the Filter Rows step. The hop should be the main output of the Sales step. Figure 48 shows the transformation window after adding the new step and hop.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 36 Figure 48: Access Input Step and Filter Step in Spoon o Alternatively, you can draw hops by hovering over a step until the hover menu appears. Drag the hop painter icon from the source step to your target step. o Double-click the Filter Rows step. The Filter Rows edit properties dialog box appears. o In the Step Name field type, Filter rows . o The configuration of this step is like the previous Excel transformation. o The final view of filter conditions is shown in Figure 49. Save the transformation before adding new steps.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 37 Figure 49: Filter Conditions Window 7. Separate SalesDay fields into Day, Month, Year fields In this part of the tutorial, you will use the Select Values step to change the format of the myDate field and the Split Fields step to parse the field into date components. o Under the Design tab, expand the contents of the Transform step. o Click and drag a Select values step into your transformation. o Create a “hop” between the Filter rows step and the Select values step (Figure 50). Select Result is TRUE in the filter results selection list.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 38 Figure 50: True Filter Results Connected to Select Values Step o Double-click the Select values step to open its edit properties dialog box. o In the tab named Meta-data, click the button “ Get fields to change ”, to get the fields to change, which is shown by Figure 51. Change the Type of field myDate as String , change its Format to dd-MM-yyyy. Click OK . Figure 51: Meta-data Tab of Select Values Property Edit Window o Under the Design tab, expand the contents of the Transform step. o Click and drag a Split fields step into your transformation (Figure 52). o Create a “hop” between the Select values step and the Split fields step. The hop should be the main output of the previous step. Figure 52: Create Split Fields with Hop between Steps o Double-click the Split fields step to open its edit properties dialog box (Figure 53). o Select myDate in the Field to split , type “ - ” as the Delimiter . Type in Year, Month and Day in the Column named New field , and set their Type as Integer . Click Ok when finished.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 39 Figure 53: Property Edit Window of Field Splitter Step o Select the Split fields step in the canvas and click , to preview this transform (Figure 54). Make sure that Split Fields step is selected from the left side panel of the transformation debug dialog and click on “ Quick Launch ” button. Figure 54: Examine Preview Data Window

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 40 8. Lookup Columns from the PostgreSQL tables This part of the exercise involves looking up the date from the SSTimeDim table to check the validity of dates in the Access data source. In addition, you will lookup primary key columns from other PostgreSQL tables to ensure loaded data does not contain invalid foreign keys. This part of the exercise resembles details in Section 4. Step 1 – Access the SSTimeDim table from PostgreSQL database. o Under the Design tab, expand the contents of the Input step. o Click and drag a Table Input step into your transformation. o Double-click the Table Input step to open its edit properties dialog box. o Rename your Table Input step to SSTimeDim . o For the Connection field, select PostgreSQL12.2 if it is available in the connection list. Otherwise, click “ New ” next to the connection field. Provide the settings for connecting to the database as shown in the Figure 24. o Connection Name: PostgreSQL12.2 (You can use another name if you want) Connection Type: PostgreSQL Host Name: localhost Database Name: StoreSales (unless you used a different name in PostgreSQL) Port Number: 5432 Username: postgres (default administrative user for PostgreSQL) Password: <blank> (or the password used when you installed PostgreSQL) Access: Native (JDBC) o Click “ Test ”, to test the connection. o Type in “ SELECT * FROM SSTimeDim ” in the SQL section. You can click the Preview button to view the database. Click Ok, to exit the Database Connection dialog box. o Under the Design tab, expand the contents of the Transform step. o Click and drag a Sort Rows step into your transformation; create a hop between the Split fields and Sort Rows steps. o Double-click the Sort Rows step to open its edit properties dialog box. Click “ Get fields ” to obtain the fields. Delete other fields except the Day, Month and Year fields. Then click Ok. o Add one more sort rows component Sort rows 2 , and a hop connecting the SSTimeDim step. In the field specification, delete other fields except TIMEDAY , TIMEMOHTH , TIMEYEAR fields. o Under the Design tab, expand the contents of the Join step.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 41 o Click and drag a Merge Join step into your transformation; create a hop between the Sort rows, Sort rows 2 and Merge Join steps. o Double-click the Merge Join step to specify its properties. Set First step as Sort rows , Second step as Sort rows 2 , and Join Type as INNER . Click both of the “ Get key fields ” at left and right to get the possible fields to join. In the left table, delete other fields except Day, Month and Year fields. In the right table, delete other fields except TIMEDAY , TIMEMONTH , and TIMEYEAR fields. Make sure that the steps are in the same order (day, month, year) in each step part. Then click OK. o Now, we have finished inner join between the Access table and SSTimeDim table. o Figure 55 shows the transformation design with all steps and hops to the Merge join step. Figure 55: Transformation Design with Steps and Hops to the Merge Join Step Step 2 – Inner join SSItem , SSCustomer , and SSStore to Access table. o Inner join the tables named SSItem , SSCustomer , and SSStore in your transformation using the same method described before. o For SSItem step, connect ItemID (from Excel file) and ITEMID (from Database) fields. o For SSCustomer step, connect CustID (from Excel file) and CUSTID (from Database) fields. o For SSStore step, connect StoreID (from Excel file) and STOREID (from Database) fields. o Figure 56 shows the transformation design for steps and hops to the Merge join 4 step.

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 42 Figure 56: Transformation Design with Steps and Hops to the Merge Join 2 Step Step 3 – Add SalesNo column. o Under the Design tab, expand the contents of the Transform step. o Click and drag Add sequence step into your transformation; create a hop between the Merge Join 4 and Add Sequence steps (Figure 57). o Double click on the newly created component to open its Basic Settings pane. o Set SalesNo as the name of value. Check the box for use DB to get sequence. Select the connection as PostgreSQL12.2. Set SSSalesNoSeq as sequence name (Figure 58). Figure 57: Transformation Design with Steps and Hops to the Add Sequence Step

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 43 Figure 58: Property Edit Window of Add sequence step 9. Insert rows into the SSSales table o Under the Design tab, expand the contents of the Output step. o Click and drag an Insert/Update step into your transformation; create a hop between the Add sequence and Insert/Update steps. Figure 59 shows the connection in the transformation design pane. o Double click the Insert/Update component, to specify its properties. Set the step name as SSSales . Select the connection as PostgreSQL12.2 . Type in the Target table as SSSales . Do not click the “ Get fields ” button. Instead, select SalesNo from the two sources and set the comparator to = . Figure 56 shows the window with the lookup values in the middle part. o Click the button “ Get Updated fields ” and then click on “ Edit mapping ” button to edit mapping. Select the fields named SalesUnits , SalesDollar , SaleCost , CustID, StoreID , ItemID TIMENO and SalesNo into the mappings field. Pentaho will automatically match

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 44 the corresponding name in the Target field. Only SalesNo column must be manually matched with the SALESNO field. Then click OK . Figure 60 shows the final window. Figure 59: Connect Insert/Update Step to Add Sequence Step

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 45 Figure 60: Insert/Update Step Window o Select the SSSales step and run a preview by clicking on . In the transformation debug dialog click on Quick Launch button. Figure 61 shows the result rows added to the SSSales table after execution of the step. The Step Metrics tab (Figure 62) shows that 8 rows were inserted into the SSSales table in the Output column. o Connect to your PostgreSQL account (on your PC) so you can verify the number of rows in the SSSales table. You should see 208 rows with 8 new rows added to the 200 rows existing after the Excel transformation execution (192 original rows and 8 rows from the Excel transformation).

1 March 2021 Data Integration Exercise with PDI and PostgreSQL P a g e 46 Figure 61: Preview Data for the SSSales Step in the Access Transformation Figure 62: Step Metrics in the Execution Result Window for the Access Transformation