Wednesday, July 21, 2010

Calculate Grand Total in SQL Query

0 comments
To calculate a grand total within an SQL query, the keyword available for this purpose is COMPUTE Keyword which is used with out a BY clause


SELECT itemid, Qty, actualPrice, subtotal
FROM SalesOrderDetail s
WHERE  actualPrice< 1000
COMPUTE SUM(Qty), SUM(subtotal)

Wednesday, June 23, 2010

Date wise SQL Query

0 comments
Column Level SQL Query to extract data with date as condition

Select employee_id, employee_name
(Select count(*) from employee_leave el where e.employee_id=el.employee_id
and el.leave_date between e.hire_Date and '2010-06-20 23:59:59')
from tbl_employee e
where e.hire_date between '2010-06-01 00:00:00' and '2010-06-01 23:59:59'
here in the above query for extracting the total leaves the final date has been set from the employee hire date.

Tuesday, May 25, 2010

Generate Mapping against target Step

0 comments
In cases where you have a fixed target table, you will want to map the fields from the stream to their corresponding fields in the target output table. This is normally  accomplished using a Select Values step in your transformation. The 'Generate mapping against target' option provides you with an easy to use dialog for defining these mappings that will automatically create the resulting Select Values step that can be dropped into your transformation flow prior to the table output step. To access the 'Generate mapping against target' option is accessed by right clicking on the table output step.

After defining your mappings, select OK and the Select Values step containing your mappings will appear on the workspace. Simply, attach the mapping step into your transformation immediately Attach the mapping step into your transformation just before the table output step.

Pentaho Spoon Installation

1 comments
The first step is the installation of Sun Microsystems Java Runtime Environment version 1.4 or higher. You can download a JRE
After this, you can simply unzip the zipfile: Kettle3.0.0. zip in a directory of your choice. In the Kettle directory where you unzipped the file, you will find a number of files. Under Unixlike environments (Solaris, Linux, MacOS, …) you will need to make the shell scripts executable. Execute these commands to make all shell scripts in the Kettle directory executable:

cd Kettle
chmod +x *.sh

Starting Spoon:.

To launch Spoon on the different platforms these are the scripts that are provided:
Spoon.bat: launch Spoon on the Windows platform.
sh spoon.sh: launch Spoon on a Unixlike
platform: Linux, Apple OSX, Solaris, ...
If you want to make a shortcut under the Windows platform an icon is provided: “spoon.ico” to set the correct icon. Simply point the shortcut to the Spoon.bat file.

Supported platforms
  1. The Spoon GUI is supported on the following platforms:
  2.  Microsoft Windows: all platforms since Windows 95, including Vista
  3.  Linux GTK: on i386 and x86_64 processors
  4.  Apple's OSX: works both on PowerPC and Intel machines
  5.  Solaris: using a Motif interface (GTK optional)
  6.  AIX: using a Motif interface
  7.  HPUX:


Friday, May 21, 2010

Group by SQL Query

0 comments
To extract Country wise sales count for the period  MAY-2010 use below code.


Select c.country_name, count(s.id)
from tbl_sales s inner join tbl_country c on s.country_id=c.country_id
where s.saels_date between '2010-05-01 00:00:00' and '2010-05-31 23:59:59'
group by c.coutnry_name 

Sunday, May 16, 2010

How to Insert Extracted Values in Pentaho Spoon

0 comments

To insert the extracted records  from the transactional source database into the destination or data warehouse  following is simple way to do it.

Open the Pentaho Spoon go to file menu and open new Transformation.

in the design mode drill down the Input Folder and drag & drop the Table Input Step, double click the Table Input  and set the source database (How to Set Connection Parameters in Pentaho Spoon)connection parameters this will extract all required fields

image

Again go to the design mode and this time drag & drop the INERT / UPDATE Step lying under the OUT PUT Folder now hold the Shift key and place your mouse icon over the TABLE INPUT and drag upto the INERT / UPDATE Step this will generate a Green color Arrow called a hope with its tail at the TABLE INPUT and head towards INERT / UPDATE Step.

You can enable, disable or even delete the hope by placing your mouse over it and with right click. now double click the  INERT / UPDATE Step image

and set target Database Connection by clicking the New Button for the connection and Edit if you have it already.

For the first grid Click the Get Fields Button this will fill grid with all values you have queried in the previous Table Input Step here in this grid keep only those fields on the basis of which you will insert record.

Loading XML Data Using Pentaho ETL Tool

0 comments
To load the data from a XML file using pentaho kettle do the followings. 
Go to the design mode, drill down the input folder and drag & drop the XML Input Step.image
Double click the XML Input Step as shown in the image below
image
in the first tab of file click the Brows Button and add your desire XML File by clicking Add Button this will add your file(s) inside the selected files Grid under the File /Directory Grid Label.
Below this Grid there is a Show File(s) options that allow you to view your complete files addresses.
Nnow go to the next tab of Fields followed by Content tab.
here in this Fields tab click the Get Fields Button lying below the Grid clicking this will extract all the contents of the XML file along with their data types and positions.
To view all these contents of your XML file you can use the XML OUTPUT Step available under the Out put folder. 


SQL Query For Monthly Calculation

0 comments
To get individual library member's monthly status report use the following column level query that includes how many books have been issued and returned in the month of may 2010 for those members who have their registration date in the same month here is the query

select m.member_id as "Member ID",m.member_name as "Member Name"
         ,(select count(book_id) 
         from tbl_book_issue b
         where m.member_id=b.member_id
        and b.status=1)as "Total Issued Books"  

       , (select count(book_id) 
         from tbl_book_issue b
           where m.member_id=b.member_id
        and b.status=2)as "Total Returned Books"  #status=1 for issued and status=2 for return

from tbl_member m
where m.registration_date between '2010-05-01 00:00:00' and '2010-05-31 23:59:59'


Saturday, May 15, 2010

Concatenation in Pentaho Kettle

0 comments
To get the  concatenated resulted values of the two variables do the followings.
in the design mode drill down the Scripting folder drag and drop the  Modified Java Script Value step
image
Double click the icon  and go the java script value Text area, copy the below code and past it into the Script Value Text Area
var fullName =  FirstName+ LastName;
image
here in this code of java “var” is the reserved word for declaring String variables while “fullName is  the name of variable that will hold the resulted or concatenated value, you can view the “fullName” variable contains the resulted value showing in the grid by clicking get Variable button at bottom of the step.

How to Split a String in pentaho Kettle

0 comments
To get values from a comma separated string we can spilt it in following way.
In the design mode drill down the Scripting folder drag and drop the  Modified Java Script Value step
image
Double click the the icon  and go the java script value Text area, copy the below code and past it into the Script Value Text Area
var employee = EmployeeInfo.getString().split("\\,");
var ID = employee [0];
var  Name= employee [1];
var Address = employee [2];
image
here in this code of java “var” is the reserved word for declaring String variables while “ID”,”name”,”address” are the names of variables that will hold the values after splitting and the EmployeeInfo is the String that contains the ID, Name and Address in the form i.e. 1234,Ali,pakistan

Thursday, May 13, 2010

Date to String Conversion in Pentaho Kettle

0 comments
Here is the simple conversion from Date to String in the spoon
Go the design mode select Modified Java Script Step lying under the Scripting folder drag & drop it.
image
Double click the Java Modified Script  Step and write the following code in the scriptVale
var dateToStringConversion = date2str(datefield_name, "yyyyMMdd");
where dateToStringConversion  is the name of the variable and datefield_name is the field name to whom you want to convert which is coming from the previous step

How to get Distinct Count in Pentaho

0 comments
In order to get Distinct Count in the Kettel spoon
Go to the design mode drill down the Transformation folder and Drag & drop the  Unique Rows Step
image
this step remove the double and repeated Rows and give only unique occurrences but this works only on sorted input so you need to set a Sorted Rows Step available under the Transformation Folder jus drag & drop it  and make the hope between the two steps
image
If the input is not sorted, then only double consecutive rows are handled correctly.

How to Write ETL Script in Pentaho

0 comments
In this posting i m going to exemplify for the beginners that how to write a simple script in the Pentaho Kettle Spoon let say extracting the data from the source or transactional database or from the one data source to destination or target or  another database, here is the example steps.
Extract Customer information from Customer_database from MYSQL
Transform and Load The extracted data in SQL SERVER Customer_database Table
Open the Pentaho Spoon
Go to the file menu and select Transformation or press Ctr+N
Go to the design mode and select Table Input from the Input folder and the Table Output Steps from  the Output folder respectively
Press Shift button and drag from Table input to Table Out Put called a hope having green color that showing all the values shifting to another step.
image
Extracting required fields from the source table representing here in this example as Table Input from MYSQL and loading all the fields in the destination database table representing here as Table output in SQLSERVER.
Double click the each step to set source and destination connections as shown below image name the connection, if working on the local system name local host else IP
select MYSQL from the connection type set ,user id and password followed by the database name and test the connection.
image
after defining the connection write the query in the SQL text area and double click the Table Output set the connection same as defined above here you also have to define the target table that will hold the extracted data as can view in the below image.
image

Wednesday, May 12, 2010

Check Table Column Existence in Pentaho

0 comments
In order to find out the existence of a specific column in a database use the Check if Column Exists Step available under the Lookup Folder in the design mode  
image
  1. Drag & Drop the above icon.
  2. image
  3. Double click the step and name it , defined the connection for MYSQL or SQL SERVER etc.
  4. Table Name: The name of the column to whom the column belongs.
  5. Table name in  Field: Enabled it if you want to read the name of the table from the input field.
  6. Tablename Field:Set fields here that have parameters and its type 
  7. Columnname Field:Set the name of the column field reading from the input field.
  8. Result fieldname:Resulting Boolean flag  that shows weather it exist or not.



Performing Calculations in Pentaho Spoon

0 comments
To perform simple calculations and get new fields on different output fields extracting from the database table on constant values.
  1. Go to Transformation in the design mode
  2. Drill Down the Transformation folder and drag & drop the Calculator
  3.    image
  4. Double Click the Calculator Step a pop window same as below will appear
  5. image
  6. In the New Field  column name your new Field
  7. Select the type of calculation from the list box on the fields you want to perform calculations.
  8. Field A, Field B and Field C combos are available to select your fields that are need to be performed calculations
  9. Select the value type from the combo box and set decimal precision.

Aggregate Functions in Pentaho Spoon

0 comments
To Calculate the Aggregate Functions likewise Sum(), Average(), Count(), MIn(), Max(), First() and Last() usually on the data coming and reading from text files do the followings

  1. Drill Down the Input in the design mode and select Text Input File

  2. image

  3. Double Click the Text File Input in the File tab and brows your file add it  to the selected path

  4. In the Content tab select File Type

  5. Go to Fields tab and click the Get Fields , it will extract all the fields along with data their data types, reading each coma separated field

  6. Go to the design mode again find Deprecated the 3rd last folder drill down it and drag & drop the Aggregate Rows Step as the same icon  mentioned below.

  7. image

  8. Double click the Aggregate Rows a pop window will open click Get Fields it will extract all the fields as it has read from the Text Input File, set the name for each field in the New Name column and finally select the aggregate function from the combo box.
image

What is Checksum in Pentaho Spoon

0 comments
By definition checksum is a way to determine the integrity of data in which both the sender and receiver can test the data integrity weather it is correct or not, the sender calculates the checksum value  by taking the sum of binary data transmitted on the other end the receiver can perform the same calculation on the data and compare it with the checksum value provided by the sender. If the two values match, the receiver has surety that the data was received correctly.
Pentaho Spoon provide the Add a Checksum Step  lying under the  Transformation Tab.


  1. Go to Design
  2. Drill Down Transformation

  3. Drag & Drop Add a Checksum

  4. image

  5. Name the Step

  6. Define the Checksum Algorithm most used Algorithm for  Checksum is MD-5

  7. Click the Get Fields Button, this will extract and show all the fields coming from the previous step at which your going to apply  checksum might be extracting using Input Step from the database table or Stored Procedure
image

Tuesday, May 11, 2010

Hex to integer Conversion in Kettle

0 comments
To convert your Hex value to integer in spoon do the followings
Go to the design mode.
Drill Down the Scripting step.
Drag and drop the Modified Java Script Value.
image
Double Click the Modified Java Script Value and drill down the Transformation Script available at the left hand side of the Script Mod, double click the ScriptValue
in the Script Value paste the following lines
java;
var nummerke = Packages.java.lang.Integer.valueOf(Hex.getString(), 16).intValue();
here Hex is your input value coming from another step, at the bottom of a Script Mod click the Get fields Button now you have get the converted variable with its data type showing in a grid, finally test the script.
image

How to Set Environment Variables in Kettle

0 comments
To set Environment Variables go to Edit menu and click Set Environment Variables or press CTRL+ALT+J, in the name column name your variable and assigned it to any value by default the variable is of String type. You can later in your job or transformation access these variable using GET VARIABLE STEP but it is recommended not to use Environment Variables if your working in multiple or more than two Transformations  or jobs as these variable have global scope and  Changes to the environment variables would conflict in Transformations or jobs for this reason Kettle variables were introduced to provide a way to define variables that are local to the job in which the variable is set. The Set Variable step in a transformation allows you to specify in which job you want to set the variable's scope (i.e. parent job, grandparent job or the root job).
image 
in the same tab of Edit menu you can see your all Environment Variables using Show Environment Variables option, here you can also up and down your Variable on clicking name column header.

Monday, May 10, 2010

What is Get Variable in Pentaho Kettle

0 comments
image
The Get variable step is used to get access your declared variable’s values, whenever you need a variable value you have declared earlier in your script using Set Variable step what you need to do is drag and drop a Get Variable step and do the followings.
image
  1. Go to the Job Folder drag & drop a Get Variable Step
  2. Nam the value of accessing variable that you can use in other steps
  3. in the second column give the same name of the required variable as you have declared it  in Set Variable Step in below format
  4. ${variable} or %%variable%%
Example:
variable value               ${my_variable as declared in Set Varible}