Pentaho SQL SERVER 2012 Data Connection Issue

If you face any issue in connecting to SQL SERVER 2012/SQL SERVER by Pentaho Date Connection, then download JTDS driver for sql server (jtds-1.2.5-dist) and load it in the below location:

"~\biserver-ce\tomcat\webapps\pentaho\WEB-INF\jtds-1.2.5-dist"

Now restart the pentaho server, and create new sql server connection.

Pentaho Data Connection with Variables

Q: When you create a normal database connection, you have to edit the transformation or job to connect to a different host or database. How can I make this a variable?
A: Here are the steps to make a connection based on variables and share the connection for easier reuse:
1. In Spoon, open the transformation containing the current hardcoded form of the DB connection.
2. Expand the "Database connections" section of the navigation tree.
3. Double click on the connection you currently have defined to edit it.
4. In the "server host name" textbox, change the currently hardcoded value (e.g. DBserver) to a variable (e.g. ${DB_HOSTNAME})
5. Save and close that dialog (Click OK)
6. Right click the connection you just edited and select the option "Share", to share it.
7. Save the transformation you had open. (Shared connections don't get written out until you save something)
8. Using a file explorer, navigate to the .kettle directory inside your home directory (i.e. "C:\Documents and Settings\<username>\.kettle" for Windows XP/2000 ", "C:\Profiles\<username>\.kettle" for Windows Vista ", /home/<username>/.kettle" for Linux/Unix)
9. The shared connection should now be in .kettle/shared.xml. This file can be copied and pasted to any new Kettle installation.
10. Edit the kettle.properties file using a standard text editor.
11. Create a new line in it below the comments with the name of the variable you defined in step 4.
(The new line would read as follows if you named the variable DB_HOSTNAME: DB_HOSTNAME = localhost)
12. On any new installation, you can edit that kettle.properties file and define a new value for that variable.
13. Once you have completed all of the above, either restart kettle or select the Set environment variables option in the Edit menu.

How to handle Slowly Changing Dimension using Pentaho Data Integration?

The below steps help the user to handle Slowly Changing Dimension using Pentaho Data Integration (PDI) tool.

We can maintain historical attribute of a dimension by using "Combinational Lookup/Update" control in PDI.

Step 1: Design your package as shown below. In my example I had taken "dbo.Branch" table as source with the following columns:

SELECT     [BranchID]
    ,[BranchName]
    ,[Address1]
    ,[Address2]
    ,[City]
    ,[State]
    ,[Country]
    ,[Telephone]
FROM [dbo].[Branch]

Step 2: Add a Combination Look up, and configure the settings as shown below:

Step 3: Select the target table "dbo.DimBranch", map the source columns with the target columns, give th ename of the Surrogate Key in tachnical key field text box "Branch Key", select Use Auto incremental field and enter a datafield column name in Date of last update field.

Step 4: Save and Run the package, the package will execute successfully. Now change the values in any column of the source table and run the package again. A new row will be created with updated row and new surrogate key in target table.

Use of Database lookup in Pentaho Kettle

Database Lookup is used to map the columns of source and destination and get the surrogate key of lookup tables.

For example,

1. My source table (dbo.Employee) contains employee details including DepartmentID
2. My destination table (DimEmployee) including a column DepartmentKey.
3. DepartmentKey is the SurrogateKey in the table dbo.DimDepartment with the following columns: (DepartmentKey, DepartmentID, DepartmentName,...).
4. Now if you want to load the department key into Dimemployee table, we need to map the DepartmentID of the source with DepartmentID of the DimDepartment table (lookup table) to get the DepartmentKey as output.

This can be achieved in Pentaho, create a transformation as shown below:

Incremental Data load in Pentaho using Insert/Update control

Incremental data loading can be achieved in pentaho using Inser/Update controls. The following steps helps one to understand the control:

1. Create a new transformation, containing source and target data connections.
2. Add a "Table Input" configure the connection properties and input columns. In below example two columns EmployeeID and EmployeeName are selected from Employee table.
3. Add a Insert/Update control join by hop.
4. Configure the insert update control as shown below (Steps 1, 2, & 3).
5. My target table is DimEmployee, and look up column in EmployeeID (Steps 4 &5).
6. Click on Get Update fields (Step 5). Map Input and Output columns.
7. Set update Y or N for columns (Y for allow updates, N for fixed attribute).
8. Click OK.