tag:blogger.com,1999:blog-41842296752294106152024-03-08T09:01:53.391-08:00Query InnWays towords providing a framework for Query optimization,Business Intelligence,Easy and fast learning of Database Engineering Tools with best practices.M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.comBlogger73125tag:blogger.com,1999:blog-4184229675229410615.post-73431377280527026282010-07-21T10:09:00.000-07:002010-07-21T10:10:46.288-07:00Calculate Grand Total in SQL Query<iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=0201615762&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>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<br />
<br />
<br />
<pre class="brush:sql">SELECT itemid, Qty, actualPrice, subtotal
FROM SalesOrderDetail s
WHERE actualPrice< 1000
COMPUTE SUM(Qty), SUM(subtotal)</pre>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-69033939334477400732010-06-23T08:54:00.000-07:002010-07-17T09:21:04.613-07:00Date wise SQL Query<div style="text-align: justify;">Column Level SQL Query to extract data with date as condition</div><br />
<pre class="brush:sql">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'</pre>here in the above query for extracting the total leaves the final date has been set from the employee hire date.M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-61194856250464507552010-05-25T11:20:00.000-07:002010-05-25T11:23:28.658-07:00Generate Mapping against target Step<div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;">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.</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;">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. </div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-21536766962150187202010-05-25T10:58:00.000-07:002010-05-25T11:07:09.239-07:00Pentaho Spoon Installation<span style="font-family: "Trebuchet MS",sans-serif;">The first step is the installation of Sun Microsystems Java Runtime Environment version 1.4 or higher. You can download a JRE </span><br />
<div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;">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:</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><b><br />
</b></div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><b>cd Kettle<br />
chmod +x *.sh</b></div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><u><b>Starting Spoon:</b></u>.</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;">To launch Spoon on the different platforms these are the scripts that are provided:<br />
Spoon.bat: launch Spoon on the Windows platform.<br />
sh spoon.sh: launch Spoon on a Unixlike<br />
platform: Linux, Apple OSX, Solaris, ...<br />
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.<br />
<br />
<u><b>Supported platforms</b></u><br />
<ol><li>The Spoon GUI is supported on the following platforms:</li>
<li> Microsoft Windows: all platforms since Windows 95, including Vista</li>
<li> Linux GTK: on i386 and x86_64 processors</li>
<li> Apple's OSX: works both on PowerPC and Intel machines</li>
<li> Solaris: using a Motif interface (GTK optional)</li>
<li> AIX: using a Motif interface</li>
<li> HPUX:</li>
</ol><br />
<br />
</div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com1tag:blogger.com,1999:blog-4184229675229410615.post-42044218784101940552010-05-21T09:36:00.000-07:002010-05-21T09:43:50.254-07:00Group by SQL QueryTo extract Country wise sales count for the period MAY-2010 use below code.<br />
<br />
<br />
<pre class="brush:sql">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
</pre>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-57588668626617998412010-05-16T11:47:00.001-07:002010-05-16T11:47:54.757-07:00How to Insert Extracted Values in Pentaho Spoon<p align="justify"><font face="Trebuchet MS">To insert the extracted records  from the transactional source database into the destination or data warehouse  following is simple way to do it.</font></p> <p align="justify"><font face="Trebuchet MS">Open the Pentaho Spoon go to file menu and open new Transformation.</font></p> <p align="justify"><font face="Trebuchet MS">in the design mode drill down the <strong>Input Folder </strong>and drag & drop the <strong>Table Input Step, </strong>double click the <strong>Table Input  </strong>and set the source database<strong> (How to Set Connection Parameters in Pentaho Spoon)</strong>connection parameters this will extract all required fields </font></p> <p align="justify"><a href="http://lh3.ggpht.com/_zefv-WD8-eU/S_A9zFyWF3I/AAAAAAAAAF8/gta1fULK4b4/s1600-h/image%5B2%5D.png"><font face="Trebuchet MS" color="#333333"><img title="image" style="border-right: 0px; border-top: 0px; display: inline; border-left: 0px; border-bottom: 0px" height="90" alt="image" src="http://lh4.ggpht.com/_zefv-WD8-eU/S_A90MG_QiI/AAAAAAAAAGA/1I4cq3LJ9IM/image_thumb.png?imgmax=800" width="244" border="0" /></font></a><font face="Trebuchet MS"> </font></p> <p align="justify"><font face="Trebuchet MS">Again go to the design mode and this time drag & drop the <strong>INERT / UPDATE Step </strong>lying under the <strong>OUT PUT Folder </strong>now hold the <strong>Shift key </strong>and place your mouse icon over the <strong>TABLE INPUT </strong>and drag upto the <strong>INERT / UPDATE Step </strong>this will generate a Green color Arrow called a <strong>hope</strong> with its tail at the <strong>TABLE INPUT </strong>and head towards <strong>INERT / UPDATE Step. <strong></strong></strong></font></p> <p align="justify"><font face="Trebuchet MS">You can enable, disable or even delete the <strong>hope by placing </strong>your mouse over it and with right click. now double click the  <strong>INERT / UPDATE Step </strong></font><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S_A91ACMkeI/AAAAAAAAAGE/ZXTARJSV8Ws/s1600-h/image%5B6%5D.png"><font face="Trebuchet MS" color="#333333"><img title="image" style="border-right: 0px; border-top: 0px; display: inline; border-left: 0px; border-bottom: 0px" height="191" alt="image" src="http://lh5.ggpht.com/_zefv-WD8-eU/S_A92IpYcAI/AAAAAAAAAGI/06CcVnuK9qc/image_thumb%5B2%5D.png?imgmax=800" width="384" border="0" /></font></a><font face="Trebuchet MS"> </font></p> <p align="justify"><font face="Trebuchet MS">and set target Database Connection by clicking the <strong>New Button </strong>for the <strong>connection</strong> and <strong>Edit </strong>if you have it already.</font></p> <p align="justify"><font face="Trebuchet MS">For the first grid Click the <strong>Get Fields Button </strong>this will fill grid with all values you have queried in the previous <strong>Table Input Step</strong> here in this grid keep only those fields on the basis of which you will insert record.</font><font face="Trebuchet MS"></font></p> M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-34876261632494040962010-05-16T06:47:00.001-07:002010-05-16T07:01:03.737-07:00Loading XML Data Using Pentaho ETL Tool<div align="justify"><span style="font-family: Trebuchet MS;">To load the data from a XML file using pentaho kettle do the followings.</span><span style="font-family: Trebuchet MS;"> </span><br />
<span style="font-family: Trebuchet MS;">Go to the design mode, drill down the input folder and drag & drop the <b>XML Input Step.</b></span><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-_3edsVD5I/AAAAAAAAAFs/y3KZx50NCf8/s1600-h/image%5B2%5D.png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="70" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-_3fadvgsI/AAAAAAAAAFw/JYexYWJsBiY/image_thumb.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="81" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><span style="font-family: Trebuchet MS;">Double click the <b>XML Input Step </b>as shown in the image below </span></div><div align="justify"><a href="http://lh4.ggpht.com/_zefv-WD8-eU/S-_3gA2YqVI/AAAAAAAAAF0/N8dfwbgNENM/s1600-h/image%5B5%5D.png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="202" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-_3hLDY7dI/AAAAAAAAAF4/a5JWnMAW43E/image_thumb%5B1%5D.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="244" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><span style="font-family: Trebuchet MS;">in the first tab of <b>file</b> click the <b>Brows Button </b>and add your desire <b>XML File</b> by clicking <b>Add Button </b>this will add your file(s) inside the <b>selected files Grid</b> under the <b>File /Directory Grid Label. </b></span><br />
<span style="font-family: Trebuchet MS;">Below this Grid there is a <b>Show File(s) options </b>that allow<b> </b>you to view your complete files addresses.</span><br />
<span style="font-family: Trebuchet MS;">Nnow go to the next tab of <b>Fields </b>followed by<b> Content tab.</b></span></div><div align="justify"><span style="font-family: Trebuchet MS;">here in this <b>Fields tab </b>click the <b>Get Fields Button</b> lying below the <b>Grid</b> clicking this will extract all the contents of the <b>XML file</b> along with their <b>data types </b>and<b> positions.</b></span><br />
<span style="font-family: Trebuchet MS;">To view all these contents of your <b>XML file you can use the XML OUTPUT Step </b>available under the Out put folder. <b><br />
</b></span><br />
<span style="font-family: Trebuchet MS;"><b> </b></span></div><div align="justify"><br />
</div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-25746730732011494682010-05-16T06:29:00.000-07:002010-05-16T06:29:50.090-07:00SQL Query For Monthly Calculation<div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;">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</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;">select <span style="color: black;">m.member_id <span style="color: blue;">as </span><span style="color: red;">"Member ID"</span>,m.member_name <span style="color: blue;">as</span><span style="color: red;"><span style="color: blue;"> </span>"Member Name"</span></span></div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"> ,(select count(<span style="color: black;">book_id</span>) </div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"> from<span style="color: black;"> tbl_book_issue b</span></div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"> where <span style="color: black;">m.member_id=b.member_id</span></div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"> and <span style="color: black;">b.status=1</span>)as <span style="color: black;">"<span style="color: red;">Total Issued Books" </span></span></div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"> <span style="color: black;"> ,</span> (select count(<span style="color: black;">book_id</span>) </div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"> from <span style="color: black;">tbl_book_issue b</span><br />
</div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"> where <span style="color: black;">m.member_id=b.member_id</span></div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"> and <span style="color: black;">b.status</span>=2)as <span style="color: black;">"<span style="color: red;">Total Returned Books" </span>#status=1 for issued and status=2 for return</span><br />
</div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"></div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;">from tbl<span style="color: black;">_member m</span></div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;">where <span style="color: black;">m.registration_date </span>between <span style="color: red;">'2010-05-01 00:00:00'</span> and '<span style="color: red;">2010-05-31 23:59:59'</span></div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-31066926905269490732010-05-15T23:19:00.001-07:002010-05-16T05:37:34.028-07:00Concatenation in Pentaho Kettle<div align="justify"><span style="font-family: Trebuchet MS;">To get the concatenated resulted values of the two variables do the followings. </span></div><div align="justify"><span style="font-family: Trebuchet MS;">in the design mode drill down the Scripting folder drag and drop the <b>Modified Java Script Value step</b></span></div><a href="http://lh4.ggpht.com/_zefv-WD8-eU/S--OW3z1EGI/AAAAAAAAAFc/b95RMebSD7g/s1600-h/image%5B2%5D.png"><span style="font-family: Trebuchet MS;"><img alt="image" border="0" height="71" src="http://lh6.ggpht.com/_zefv-WD8-eU/S--OYAhS9rI/AAAAAAAAAFg/mpbdDZzurkw/image_thumb.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="95" /></span></a><span style="font-family: Trebuchet MS;"> </span><br />
<div align="justify"><span style="font-family: Trebuchet MS;">Double click the icon and go the java <b>script value </b>Text area, copy the below code and past it into the <b>Script Value Text Area </b></span></div><div align="justify"><b><span style="color: blue; font-family: Trebuchet MS;">var fullName = FirstName+ LastName;</span></b></div><a href="http://lh4.ggpht.com/_zefv-WD8-eU/S--OZKGMhzI/AAAAAAAAAFk/5RSwb_Fpyfo/s1600-h/image%5B5%5D.png"><span style="font-family: Trebuchet MS;"><img alt="image" border="0" height="157" src="http://lh3.ggpht.com/_zefv-WD8-eU/S--OaFn_uMI/AAAAAAAAAFo/O3DW819Wr7s/image_thumb%5B1%5D.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="244" /></span></a><span style="font-family: Trebuchet MS;"> </span><br />
<div align="justify"><span style="font-family: Trebuchet MS;">here in this code of java “<b>var</b>” is the reserved word for declaring String variables while “<b>fullName </b>is<b> </b> the name of variable that will hold the resulted or concatenated value, you can view the “<b>fullName”</b> variable contains the resulted value showing in the grid by clicking get Variable button at bottom of the step.</span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-63911921140013204602010-05-15T22:54:00.001-07:002010-05-16T05:36:55.504-07:00How to Split a String in pentaho Kettle<div align="justify"><span style="font-family: Trebuchet MS;">To get values from a comma separated string we can spilt it in following way.</span></div><div align="justify"><span style="font-family: Trebuchet MS;">In the design mode drill down the Scripting folder drag and drop the <b>Modified Java Script Value step</b></span></div><div align="justify"><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S--IknjrOjI/AAAAAAAAAFM/NOFgkxqaOUw/s1600-h/image%5B2%5D.png"><span style="font-family: Trebuchet MS;"><img alt="image" border="0" height="76" src="http://lh4.ggpht.com/_zefv-WD8-eU/S--IlUsXDvI/AAAAAAAAAFQ/HTi4KyHXBa8/image_thumb.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="108" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><span style="font-family: Trebuchet MS;">Double click the the icon and go the java <b>script value </b>Text area, copy the below code and past it into the <b>Script Value Text Area </b></span></div><div align="justify"><b><span style="color: blue;"><span style="font-family: Trebuchet MS;">var employee = EmployeeInfo.getString().split("\\,"); <br />
var ID = employee [0]; <br />
var Name= employee [1]; <br />
var Address = employee [2]; <br />
</span><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S--ImYQQ2bI/AAAAAAAAAFU/hMYO9yJ6B6g/s1600-h/image%5B6%5D.png"><span style="font-family: Trebuchet MS;"><img alt="image" border="0" height="165" src="http://lh3.ggpht.com/_zefv-WD8-eU/S--InYCCHHI/AAAAAAAAAFY/30RyCI0zoT0/image_thumb%5B2%5D.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="453" /></span></a><span style="font-family: Trebuchet MS;"> </span></span></b></div><div align="justify"><span style="color: black;"><span style="font-family: Trebuchet MS;">here in this code of java “<b>var</b>” is the reserved word for declaring String variables while <b>“ID”,”name”,”address”</b> are the names of variables that will hold the values after splitting and the <b>EmployeeInfo</b> is the String that contains the ID, Name and Address in the form i.e. <b><i>1234,Ali,pakistan</i></b></span></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-15382434949663208852010-05-13T09:04:00.001-07:002010-05-13T09:05:07.067-07:00Date to String Conversion in Pentaho Kettle<div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;">Here is the simple conversion from Date to String in the spoon</span></div><div align="justify"><span style="font-family: Trebuchet MS;">Go the design mode select <strong>Modified Java Script Step </strong>lying<strong> </strong>under the Scripting folder drag & drop it.</span></div><div align="justify"><a href="http://lh4.ggpht.com/_zefv-WD8-eU/S-wi9GnnAII/AAAAAAAAAFE/QDZ1G0Op_WE/s1600-h/image[5].png"><span style="font-family: Trebuchet MS;"><img alt="image" border="0" height="69" src="http://lh6.ggpht.com/_zefv-WD8-eU/S-wi-Dih5zI/AAAAAAAAAFI/95FfFmAsNJ0/image_thumb%5B1%5D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="111" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><span style="font-family: Trebuchet MS;">Double click the <strong>Java Modified Script Step </strong>and write the following code in the <strong>scriptVale </strong></span></div><div align="justify"><strong><span style="color: blue; font-family: Trebuchet MS;">var dateToStringConversion = date2str(datefield_name, "yyyyMMdd");</span></strong></div><div align="justify"><span style="color: black; font-family: Trebuchet MS;">where <strong>dateToStringConversion </strong>is the name of the variable and <strong>datefield_name </strong>is the field name to whom you want to convert which is coming from the previous step</span></div><div align="justify"><strong><span style="color: blue; font-family: Trebuchet MS;"></span></strong></div><div align="justify"><br />
</div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-42523057292783799282010-05-13T08:35:00.001-07:002010-05-13T08:35:55.298-07:00How to get Distinct Count in Pentaho<div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"><span><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=0321321367&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe></span>In order to get Distinct Count in the Kettel spoon </span></div><div align="justify"><span style="font-family: Trebuchet MS;">Go to the <strong>design mode</strong> drill down the <strong>Transformation folder </strong>and Drag & drop the <strong>Unique Rows Step</strong></span></div><div align="justify"><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-wcHnff_8I/AAAAAAAAAE0/93r3VRZNGbk/s1600-h/image[5].png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="92" src="http://lh6.ggpht.com/_zefv-WD8-eU/S-wcIV_8JpI/AAAAAAAAAE4/INi8R5QOQ7w/image_thumb%5B1%5D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="102" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><span style="font-family: Trebuchet MS;">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</span></div><div align="justify"><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-wcJNINQ3I/AAAAAAAAAE8/iExM_B1BeL4/s1600-h/image[11].png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="81" src="http://lh3.ggpht.com/_zefv-WD8-eU/S-wcKPM2YnI/AAAAAAAAAFA/ImxEwyOoRgQ/image_thumb%5B3%5D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="244" /></span></a></div><div align="justify"><span style="font-family: Trebuchet MS;">If the input is not sorted, then only double consecutive rows are handled correctly. </span></div><div align="justify"><br />
</div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-43806023019487448622010-05-13T08:12:00.001-07:002010-05-13T08:15:31.446-07:00How to Write ETL Script in Pentaho<div align="justify"><span style="font-family: Trebuchet MS;"><span><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=0470084855&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe></span>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. </span></div><div align="justify"><span style="font-family: Trebuchet MS;">Extract Customer information from Customer_database from <strong>MYSQL</strong></span></div><div align="justify"><span style="font-family: Trebuchet MS;">Transform and Load The extracted data in <strong>SQL SERVER</strong> <strong>Customer_database Table</strong></span></div><div align="justify"><span style="font-family: Trebuchet MS;">Open the Pentaho Spoon </span></div><div align="justify"><span style="font-family: Trebuchet MS;">Go to the file menu and select <strong>Transformation</strong> or press Ctr+N</span></div><div align="justify"><span style="font-family: Trebuchet MS;">Go to the design mode and select <strong>Table Input from </strong>the Input folder and the <strong>Table Output Steps</strong> from the Output folder respectively </span></div><div align="justify"><span style="font-family: Trebuchet MS;">Press Shift button and drag from <strong>Table input</strong> to <strong>Table Out Put</strong> called a <strong>hope</strong> having green color that showing all the values shifting to another step. </span></div><div align="justify"><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S-wWpcq_AiI/AAAAAAAAAEU/7mXtdZ-aGF8/s1600-h/image[2].png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="99" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-wWqeHOiLI/AAAAAAAAAEY/vyvgdgJ1aJ8/image_thumb.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="244" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><span style="font-family: Trebuchet MS;">Extracting required fields from the source table representing here in this example as <strong>Table Input from MYSQL</strong> and loading all the fields in the destination database table representing here as <strong>Table output</strong> in <strong>SQLSERVER.</strong></span></div><span style="font-family: Trebuchet MS;">Double click the each step to set source and destination <strong>connections</strong> as shown below </span><a href="http://lh4.ggpht.com/_zefv-WD8-eU/S-wWrbLye8I/AAAAAAAAAEc/Je7_pAEjQVc/s1600-h/image[6].png"><span style="color: #333333; font-family: Trebuchet MS;"><strong><img alt="image" border="0" height="136" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-wWsXlm55I/AAAAAAAAAEg/Q1fyH25X9x0/image_thumb%5B2%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="355" /></strong></span></a><span style="font-family: Trebuchet MS;"><strong> </strong></span><span style="font-family: Trebuchet MS;">name the connection, if working on the local system name <strong>local host else IP</strong></span><br />
<div align="justify"><span style="font-family: Trebuchet MS;">select <strong>MYSQL</strong> from the <strong>connection type set</strong> ,user id and password followed by the database name and test the connection.</span></div><div align="justify"><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-wW6P9xg2I/AAAAAAAAAEk/d8GDdgXckBg/s1600-h/image[10].png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="146" src="http://lh3.ggpht.com/_zefv-WD8-eU/S-wW7bbttHI/AAAAAAAAAEo/ugQaN-g4_p0/image_thumb%5B4%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="390" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><span style="font-family: Trebuchet MS;">after defining the connection write the query in the <strong>SQL text area </strong>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.</span></div><div align="justify"><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-wW8Ha1q6I/AAAAAAAAAEs/EUMY8ERhmM8/s1600-h/image[14].png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="198" src="http://lh3.ggpht.com/_zefv-WD8-eU/S-wW9VQJT6I/AAAAAAAAAEw/P_Jiglw7tmw/image_thumb%5B6%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="452" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><br />
</div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-24211139879437166042010-05-12T08:52:00.001-07:002010-05-12T09:06:36.269-07:00Check Table Column Existence in Pentaho<span style="color: blue; font-family: Trebuchet MS;"><span style="color: black;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=0071546383&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>In order to find out the existence of a specific column in a database use the <b>Check if Column Exists </b>Step available under the <b>Lookup Folder</b> in the design mode</span> </span><br />
<a href="http://lh4.ggpht.com/_zefv-WD8-eU/S-rOsYVEXlI/AAAAAAAAAEE/K_FduTMuqJc/s1600-h/image%5B2%5D.png"><span style="color: blue; font-family: Trebuchet MS;"><img alt="image" border="0" height="71" src="http://lh5.ggpht.com/_zefv-WD8-eU/S-rOtBYSXXI/AAAAAAAAAEI/OJ-MhLQ-jCw/image_thumb.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="180" /></span></a><span style="color: blue; font-family: Trebuchet MS;"> </span><br />
<ol><li><span style="color: blue; font-family: Trebuchet MS;">Drag & Drop the above icon.</span> </li>
<li><a href="http://lh4.ggpht.com/_zefv-WD8-eU/S-rOuenkfCI/AAAAAAAAAEM/IQ4uatsFLqo/s1600-h/image%5B5%5D.png"><img alt="image" border="0" height="189" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-rOvU1MN1I/AAAAAAAAAEQ/ax79n1qBgsQ/image_thumb%5B1%5D.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="244" /></a> </li>
<li><span style="color: blue; font-family: Trebuchet MS;">Double click the step and name it , defined the connection for MYSQL or SQL SERVER etc.</span> </li>
<li><span style="color: blue; font-family: Trebuchet MS;"><b>Table Name</b>: The name of the column to whom the column belongs.</span> </li>
<li><span style="color: blue; font-family: Trebuchet MS;"><b>Table name in Field</b>: Enabled it if you want to read the name of the table from the input field.</span> </li>
<li><span style="color: blue; font-family: Trebuchet MS;"><b>Tablename Field</b>:Set fields here that have parameters and its type </span></li>
<li><span style="color: blue; font-family: Trebuchet MS;"><b>Columnname Field</b>:Set the name of the column field reading from the input field.</span> </li>
<li><span style="color: blue; font-family: Trebuchet MS;"><b>Result fieldname</b>:Resulting Boolean flag that shows weather it exist or not.</span> </li>
</ol><span style="color: blue; font-family: Trebuchet MS;"></span><br />
<span style="color: blue; font-family: Trebuchet MS;"></span><br />
<br />
<span style="color: blue; font-family: Trebuchet MS;"></span>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-16298579823702537542010-05-12T08:21:00.001-07:002010-05-12T09:04:57.271-07:00Performing Calculations in Pentaho Spoon<span style="font-family: Trebuchet MS;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=0619215593&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>To perform simple calculations and get new fields on different output fields extracting from the database table on constant values.</span><br />
<ol><li><span style="color: blue; font-family: Trebuchet MS;">Go to <b>Transformation</b> in the design mode</span></li>
<li><span style="color: blue; font-family: Trebuchet MS;">Drill Down the <b>Transformation folder</b> and drag & drop the Calculator</span></li>
<li><span style="color: blue; font-family: Trebuchet MS;"> </span><a href="http://lh3.ggpht.com/_zefv-WD8-eU/S-rHaHLgDHI/AAAAAAAAAD0/UX0b7lAwVYE/s1600-h/image%5B2%5D.png"><span style="color: blue; font-family: Trebuchet MS;"><img alt="image" border="0" height="74" src="http://lh5.ggpht.com/_zefv-WD8-eU/S-rHbFg62tI/AAAAAAAAAD4/JZp0xEqIRh0/image_thumb.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="72" /></span></a><span style="color: blue; font-family: Trebuchet MS;"> </span></li>
<li><span style="font-family: Trebuchet MS;"><span style="color: blue;">Double Click the <b>Calculator Step a pop window same as below will appear </b></span></span></li>
<li><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S-rHbmUw39I/AAAAAAAAAD8/3zoo8d-SymI/s1600-h/image%5B6%5D.png"><span style="color: blue; font-family: Trebuchet MS;"><img alt="image" border="0" height="101" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-rHchJ_s-I/AAAAAAAAAEA/JuWS3a8N2mU/image_thumb%5B2%5D.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="375" /></span></a><span style="color: blue; font-family: Trebuchet MS;"> </span></li>
<li><span style="color: blue; font-family: Trebuchet MS;">In <b>the New Field column </b>name your new Field</span></li>
<li><span style="color: blue; font-family: Trebuchet MS;">Select the type of calculation from the list box on the fields you want to perform calculations.</span></li>
<li><span style="color: blue; font-family: Trebuchet MS;">Field A, Field B and Field C combos are available to select your fields that are need to be performed calculations</span></li>
<li><span style="color: blue; font-family: Trebuchet MS;">Select the value type from the combo box and set decimal precision. </span></li>
</ol>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-19816178519450468012010-05-12T07:57:00.001-07:002010-05-12T09:04:23.483-07:00Aggregate Functions in Pentaho Spoon<div align="justify"><span style="font-family: Trebuchet MS;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=1423900839&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>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</span></div><ol><li> <br />
<div align="justify"><span style="font-family: Trebuchet MS;"><span style="color: blue;">Drill Down the Input in the design mode and select <b>Text Input File </b></span></span></div></li>
<li> <br />
<div align="justify"><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S-rB2G-u0DI/AAAAAAAAADc/DKEaR0eUfp4/s1600-h/image%5B5%5D.png"><span style="color: blue; font-family: Trebuchet MS;"><img alt="image" border="0" height="92" src="http://lh6.ggpht.com/_zefv-WD8-eU/S-rB3EbHbwI/AAAAAAAAADg/8WVihUxVTVQ/image_thumb%5B1%5D.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="105" /></span></a><span style="color: blue; font-family: Trebuchet MS;"> </span></div></li>
<li> <br />
<div align="justify"><span style="color: blue; font-family: Trebuchet MS;">Double Click the Text File Input in the <b>File tab</b> and brows your file add it to the selected path </span></div></li>
<li> <br />
<div align="justify"><span style="color: blue; font-family: Trebuchet MS;">In the Content tab select File Type </span></div></li>
<li> <br />
<div align="justify"><span style="color: blue; font-family: Trebuchet MS;">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 </span></div></li>
<li> <br />
<div align="justify"><span style="color: blue; font-family: Trebuchet MS;">Go to the design mode again find Deprecated the 3rd last folder drill down it and drag & drop the <b>Aggregate Rows Step</b> as the same icon mentioned below.</span></div></li>
<li> <br />
<div align="justify"><a href="http://lh4.ggpht.com/_zefv-WD8-eU/S-rB3vfX5gI/AAAAAAAAADk/YSKWlJSGbZY/s1600-h/image%5B8%5D.png"><span style="color: blue; font-family: Trebuchet MS;"><img alt="image" border="0" height="56" src="http://lh3.ggpht.com/_zefv-WD8-eU/S-rB4xtl86I/AAAAAAAAADo/wtjTATFzQ7M/image_thumb%5B2%5D.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="68" /></span></a><span style="color: blue; font-family: Trebuchet MS;"> </span></div></li>
<li> <br />
<div align="justify"><span style="color: blue; font-family: Trebuchet MS;">Double click the <b>Aggregate Rows </b>a pop window will open<b> </b>click Get Fields it will extract all the fields as it has read from the <b>Text Input File, </b>set the name for each field in the <b>New Name column</b> and finally select the <b>aggregate function </b>from the combo box.</span></div></li>
</ol><div align="justify"><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-rB5iaVsAI/AAAAAAAAADs/6zg-f8MjSHM/s1600-h/image%5B2%5D.png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="145" src="http://lh3.ggpht.com/_zefv-WD8-eU/S-rB6ityNbI/AAAAAAAAADw/v2VIOgPbpnU/image_thumb.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="244" /></span></a></div><div align="justify"><br />
</div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-83387064172888616942010-05-12T07:23:00.001-07:002010-05-12T09:03:23.035-07:00What is Checksum in Pentaho Spoon<div align="justify"><span style="font-family: Trebuchet MS;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=067233044X&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>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.</span></div><div align="justify"><span style="font-family: Trebuchet MS;">Pentaho Spoon provide the <b>Add a Checksum</b> <b>Step</b> lying under the Transformation Tab.</span></div><div align="justify"><br />
</div><div align="justify"><br />
</div><ol><li><div align="justify"><span style="color: blue; font-family: Trebuchet MS;">Go to Design</span></div></li>
<li><div align="justify"><span style="color: blue; font-family: Trebuchet MS;">Drill Down Transformation</span></div></li>
<li> <br />
<div align="justify"><span style="color: blue; font-family: Trebuchet MS;">Drag & Drop Add a Checksum</span></div></li>
<li> <br />
<div align="justify"><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S-q5uinAKWI/AAAAAAAAADM/-6vUZL-3uY4/s1600-h/image%5B5%5D.png"><img alt="image" border="0" height="72" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-q5vtV_g8I/AAAAAAAAADQ/3PwC77Jgh38/image_thumb%5B1%5D.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="104" /></a> </div></li>
<li> <br />
<div align="justify"><span style="color: blue;">Name the Step</span></div></li>
<li> <br />
<div align="justify"><span style="color: blue;">Define the Checksum Algorithm most used Algorithm for Checksum is <b>MD-5</b></span></div></li>
<li> <br />
<div align="justify"><span style="color: blue;">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</span></div></li>
</ol><div align="justify"><a href="http://lh4.ggpht.com/_zefv-WD8-eU/S-q5wlJXbKI/AAAAAAAAADU/iNGKNvH_iTY/s1600-h/image%5B2%5D.png"><img alt="image" border="0" height="220" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-q5xmwzwPI/AAAAAAAAADY/c01wzYnIwJU/image_thumb.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="244" /></a> </div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><span style="font-family: Trebuchet MS;"></span></div><div align="justify"><br />
</div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-72216138072129567292010-05-11T12:10:00.001-07:002010-05-11T12:14:46.260-07:00Hex to integer Conversion in Kettle<div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=B000J52HKM&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>To convert your Hex value to integer in spoon do the followings </div><span style="font-family: Trebuchet MS;">Go to the design mode.</span><br style="font-family: "Trebuchet MS",sans-serif;" /><span style="font-family: Trebuchet MS;">Drill Down the Scripting step.</span><br style="font-family: "Trebuchet MS",sans-serif;" /><span style="font-family: Trebuchet MS;">Drag and drop the Modified Java Script Value.</span><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-mrgaXbb1I/AAAAAAAAAC8/7espd3JXkqw/s1600-h/image%5B2%5D.png"><img alt="image" border="0" height="71" src="http://lh6.ggpht.com/_zefv-WD8-eU/S-mrhRwnglI/AAAAAAAAADA/5sCzRAWfJdE/image_thumb.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="104" /></a></div><div style="color: black; font-family: "Trebuchet MS",sans-serif; text-align: justify;">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</div><div style="color: black; font-family: "Trebuchet MS",sans-serif; text-align: justify;">in the Script Value paste the following lines</div><div style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"><b>java; <br />
var nummerke = Packages.java.lang.Integer.valueOf(Hex.getString(), 16).intValue();</b></div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;">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. </div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S-mriDPrFHI/AAAAAAAAADE/DSl8SC53wAk/s1600-h/image%5B9%5D.png"><img alt="image" border="0" height="218" src="http://lh6.ggpht.com/_zefv-WD8-eU/S-mrjDPUHWI/AAAAAAAAADI/vWlx13i7hJE/image_thumb%5B5%5D.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="392" /></a> </div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><br />
</div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-27346699695083580942010-05-11T09:05:00.001-07:002010-05-11T09:13:18.934-07:00How to Set Environment Variables in Kettle<div align="justify"><span style="font-family: Trebuchet MS;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=1847193196&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>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 <a href="http://queryinn.blogspot.com/2010/05/what-is-get-variable-in-pentaho-kettle.html">GET VARIABLE</a> 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 <a href="http://www.blogger.com/"><span id="goog_1819605273"></span>Set Variable</a> <span id="goog_1819605274"></span>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).</span></div><div align="justify"><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S-mAYHePbXI/AAAAAAAAAC0/l-r7VdVrdeU/s1600-h/image[3].png"><span style="color: #333333; font-family: Trebuchet MS;"><img alt="image" border="0" height="171" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-mAY8JXxhI/AAAAAAAAAC4/vNycg3b77-4/image_thumb%5B1%5D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="400" /></span></a><span style="font-family: Trebuchet MS;"> </span></div><div align="justify"><span style="font-family: Trebuchet MS;">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. </span></div><span style="font-family: Trebuchet MS;"></span>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-82268022913345851192010-05-10T12:16:00.001-07:002010-05-10T12:22:11.174-07:00What is Get Variable in Pentaho Kettle<div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><a href="http://lh3.ggpht.com/_zefv-WD8-eU/S-hbm8J3yCI/AAAAAAAAACk/ZG7oFkwyCGU/s1600-h/image%5B2%5D.png"><img alt="image" border="0" height="36" src="http://lh6.ggpht.com/_zefv-WD8-eU/S-hbn5WiLuI/AAAAAAAAACo/HPBO1a94h54/image_thumb.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="36" /></a> </div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=0470635177&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>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.</div><div style="font-family: "Trebuchet MS",sans-serif; text-align: justify;"><a href="http://lh5.ggpht.com/_zefv-WD8-eU/S-hbo3lt-KI/AAAAAAAAACs/3v_X0UH06CE/s1600-h/image%5B8%5D.png"><img alt="image" border="0" height="157" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-hbqJTrvtI/AAAAAAAAACw/zdo7VOLltGM/image_thumb%5B2%5D.png?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="244" /></a> </div><ol style="color: blue; font-family: "Trebuchet MS",sans-serif; text-align: justify;"><li>Go to the Job Folder drag & drop a Get Variable Step </li>
<li>Nam the value of accessing variable that you can use in other steps </li>
<li>in the second column give the same name of the required variable as you have declared it in Set Variable Step in below format </li>
<li>${variable} or %%variable%% </li>
</ol><div style="color: #b45f06; font-family: "Trebuchet MS",sans-serif; text-align: justify;"><b>Example:</b></div><div style="color: #b45f06; font-family: "Trebuchet MS",sans-serif; text-align: justify;"><b>variable value ${my_variable as declared in Set Varible} </b></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-59061656469339695762010-05-10T11:56:00.001-07:002010-05-10T12:24:22.667-07:00What is Set Variable in Pentaho Kettle<span style="font-family: "Trebuchet MS",sans-serif;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=1847193196&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe> </span><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-hWzHUVV9I/AAAAAAAAACI/Tu4kT4eVBJM/s1600-h/image%5B8%5D.png"><span style="font-family: "Trebuchet MS",sans-serif;"><img alt="image" border="0" height="36" src="http://lh6.ggpht.com/_zefv-WD8-eU/S-hW0CBVQLI/AAAAAAAAACM/Vm6mz4GYV04/image_thumb%5B2%5D.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="36" /></span></a><span style="font-family: "Trebuchet MS",sans-serif;"> </span><br />
<div align="justify"><span style="font-family: "Trebuchet MS",sans-serif;">The Set Variable is used to set or declare variables to hold values usually database fields likewise you want to set start_date value which you have extracted from your time_dimension table using INPUT Step so that this start_date field will be accessible through out your script.</span></div><a href="http://lh3.ggpht.com/_zefv-WD8-eU/S-hW1FPGvnI/AAAAAAAAACQ/zdl-g9dmy4M/s1600-h/image%5B13%5D.png"><span style="font-family: "Trebuchet MS",sans-serif;"><img alt="image" border="0" height="92" src="http://lh5.ggpht.com/_zefv-WD8-eU/S-hW2OFo0cI/AAAAAAAAACU/01Lqrnj312s/image_thumb%5B5%5D.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="320" /></span></a><span style="font-family: "Trebuchet MS",sans-serif;"> </span><br />
<div align="justify"><span style="font-family: "Trebuchet MS",sans-serif;">in the upper text filed named the step and then click the get fields button to have all desired table fields in the field name column that you have extracted using input step, in the second step name the variable to each field name and in the last column define its scope. In order to avoid null values that might come in your queries using INPUT for setting different variable the last column here to set default value that will be very useful let say if there is null value it will set it to default value.</span></div><a href="http://lh6.ggpht.com/_zefv-WD8-eU/S-hW3fVQDAI/AAAAAAAAACY/xiaKWQNv19o/s1600-h/image%5B23%5D.png"><span style="font-family: "Trebuchet MS",sans-serif;"><img alt="image" border="0" height="87" src="http://lh4.ggpht.com/_zefv-WD8-eU/S-hW4aYaROI/AAAAAAAAACg/g7lEJpEyryU/image_thumb%5B9%5D.png?imgmax=800" style="border: 0px none; display: inline;" title="image" width="244" /></span></a>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-39957139183148509472010-05-09T05:48:00.000-07:002010-05-09T05:48:02.581-07:00Storing Transformations and Jobs in local Files System<span><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=0470635177&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe></span><div style="text-align: justify;"><span style="font-family: "Trebuchet MS", sans-serif;"></span></div><span style="font-family: "Trebuchet MS", sans-serif;"><div style="text-align: justify;"><br />
</div></span><div style="text-align: justify;"><span style="font-family: "Trebuchet MS", sans-serif;">Spoon provides you with the ability to store transformation and job files to the local file system or in the </span><span style="font-family: "Trebuchet MS", sans-serif;">Kettle repository. The Kettle repository can be housed in any common relational database. This means that </span><span style="font-family: "Trebuchet MS", sans-serif;">in order to load a transformation from a database repository, you need to connect to this repository. </span><span style="font-family: "Trebuchet MS", sans-serif;">To do this, you need to define a database connection to this repository. You can do this using the </span><span style="font-family: "Trebuchet MS", sans-serif;">repositories dialog you are presented with when you start up Spoon bat file you will come across a welcome window in order to work for storing your transformations and jobs files in local file system and just click no repository. </span><span style="font-family: Trebuchet MS;"></span></div><div align="center" style="text-align: justify;"><span style="font-family: Trebuchet MS;"> </span></div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-77963639514655337492010-05-09T05:33:00.000-07:002010-05-09T05:36:01.508-07:00What is Pentaho<iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=1847199542&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><br />
<div style="text-align: justify;"><span style="font-family: "Trebuchet MS", sans-serif;">The Pentaho BI Project is open source application software for enterprise reporting, analysis, dashboard, data mining, workflow and ETL capabilities for business intelligence needs.</span></div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><span style="font-family: Trebuchet MS;"><strong><u>What is Spoon</u></strong></span></div><div style="text-align: justify;"><span style="font-family: "Trebuchet MS", sans-serif;">Kettle is an acronym for “Kettle E.T.T.L. Environment”. This means it has been designed to help you with </span><span style="font-family: "Trebuchet MS", sans-serif;">your ETTL needs: the Extraction, Transformation, Transportation and Loading of data. </span><span style="font-family: "Trebuchet MS", sans-serif;">Spoon is a graphical user interface that allows you to design transformations and jobs that can be run with </span><span style="font-family: "Trebuchet MS", sans-serif;">the Kettle tools Pan and Kitchen. Pan is a data transformation engine that is capable of performing a </span><span style="font-family: "Trebuchet MS", sans-serif;">multitude of functions such as reading, manipulating and writing data to and from various data</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">sources.Kitchen is a program that can execute jobs designed by Spoon in XML or in a database repository. Usually</span><br />
<br />
<br />
Transformations and Jobs can describe themselves using an XML file or can be put in a Kettle database <br />
<br />
repository. This information can then be read by Pan or Kitchen to execute the described steps in the<br />
transformation or run the job.</div>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0tag:blogger.com,1999:blog-4184229675229410615.post-78159300989244387012010-05-09T02:55:00.000-07:002010-07-20T12:24:28.258-07:00ETL Scripting with Open Source ETL Tools<iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=0470484322&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><br />
<div style="text-align: justify;"></div><div style="text-align: justify;"><span style="font-family: "Trebuchet MS",sans-serif;"><b><u>Extract Transform Load (ETL)</u></b></span></div><div style="text-align: justify;"><span style="font-family: "Trebuchet MS",sans-serif;">Extracting, transforming, and loading (ETL) is a process used in database especially in data warehousing and have followin steps</span></div><div style="text-align: justify;"><span style="color: blue; font-family: "Trebuchet MS",sans-serif;">Extracting data from sources (The transactinal Database)</span></div><div style="text-align: justify;"><span style="color: blue; font-family: "Trebuchet MS",sans-serif;">Transforming it to fit operational needs (which can include quality levels)</span></div><div style="text-align: justify;"><span style="color: blue; font-family: "Trebuchet MS",sans-serif;">Loading in destination (database or data warehouse or staging where you practice about data)</span></div><br />
<div style="text-align: justify;"><span style="font-family: "Trebuchet MS",sans-serif;"><b><u>Extraction:</u></b></span></div><div style="text-align: justify;">The first part of an ETL process involves extracting the data from the source systems usually called a transactional database where actual transactions are perforemd. Most data warehousing projects consolidate data from different source systems like Relational Databases.</div><div style="text-align: justify;">Extraction converts the data into a format for transformation processing. An intrinsic part of the extraction involves the parsing of extracted data, resulting in a check if the data meets an expected pattern or structure. If not, the data may be rejected entirely or in part. This step invloves extracting all the data as mentioned in the written SQL queries and procedure from the source system.</div><div style="text-align: justify;"><br />
<div></div></div><div style="text-align: justify;"><b><u>Transformation:</u></b></div><div style="text-align: justify;">The transform stage applies a series of rules or functions to the extracted data from the source to derive the data for loading into the end target. Some data sources will require very little or even no manipulation of data </div><div style="text-align: justify;"><br />
<div></div></div><div style="text-align: justify;"><b><u><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=3642104231&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>Loading</u></b></div><div style="text-align: justify;">This step involves process lodaing data which has extracted from one source database say MYSQL to load in SQLSERVER The load phase loads the data into the end target, usually the data warehouse (DW). Depending on the requirements of the organization, this process varies widely. Some data warehouses may overwrite existing information with cumulative, frequently updating extract data is done on daily, weekly or monthly. while other DW (or even other parts of the same DW) may add new data in a historicized form, for example, hourly </div><div> </div><div style="text-align: justify;"><span style="font-family: Trebuchet MS;"><b><u>Example:</u></b></span></div><div style="text-align: justify;"><span style="font-family: Trebuchet MS;">It might be your requirement that you need to accumulate data that is residing in different database like wise Customer Sales information on MYUSQL and its support and maintenance information on SQL SERVER so here you need to gather the information in order to present it the ETL script is the best solution for you.</span></div><div style="text-align: justify;"><br />
<div></div></div><div align="justify"></div><div align="justify"><b><u>BEST Practices:</u></b></div><div align="justify"><b>Four-layered approach for ETL architecture design</b> </div><div align="justify"><ul><li><span style="color: blue;">Functional layer: Core functional ETL processing (extract, transform, and load). </span></li>
<li><span style="color: blue;">Operational management layer: Job-stream definition and management, parameters, scheduling, monitoring, communication and alerting. </span></li>
<li><span style="color: blue;">Audit, balance and control (ABC) layer: Job-execution statistics, balancing and controls, rejects- and error-handling, codes management. </span></li>
<li><span style="color: blue;">Utility layer: Common components supporting all other layers. </span></li>
</ul></div><u><b>Open Source ETL Tools:</b></u><br />
<ul><li><span style="color: blue;">Apatar </span></li>
<li><span style="color: blue;">CloverETL </span></li>
<li><span style="color: blue;">Flat File Checker </span></li>
<li><span style="color: blue;">Jitterbit 2.0 </span></li>
<li><span style="color: blue;">Pentaho Data Integration (now[update] included in OpenOffice Base) </span></li>
<li><span style="color: blue;">RapidMiner </span></li>
<li><span style="color: blue;">Scriptella </span></li>
<li><span style="color: blue;">Talend Open Studio </span></li>
</ul>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com2tag:blogger.com,1999:blog-4184229675229410615.post-71845335219181823572010-05-08T22:58:00.000-07:002010-05-08T22:58:09.750-07:00SELECT AS CLAUSE<span><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=quer-20&o=1&p=8&l=bpl&asins=007222729X&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe></span><br />
In SQL Query you can define column heading jus write heading near to field name with out any space but if you want to make space in column heading then use AS clause like wise<br />
Column Heading with out AS Clause<br />
<span style="color: blue;">SELECT LASTNAME, SALARY <strong>TotalSalary</strong></span><br />
<span style="color: blue;">FROM EMP</span><br />
<span style="color: blue;">WHERE WORKDEPT = 'C01'</span><br />
<span style="color: blue;">ORDER BY 2 DESC</span><br />
Column Heading with space<br />
<span style="color: blue;">SELECT LASTNAME, SALARY + BONUS + COMM <strong>AS TOTAL EANINGS</strong></span><br />
<span style="color: blue;">FROM EMP</span><br />
<span style="color: blue;">WHERE WORKDEPT = 'C01'</span><br />
<span style="color: blue;">ORDER BY TOTAL_EANINGS DESC</span>M.Junaid Khanhttp://www.blogger.com/profile/07381906207288148117noreply@blogger.com0