Pentaho: An Open Source Business Intelligence

Pentaho Data-Integration (aka. Kettle): SCD Type3

2013-03-27T01:58:00.000-07:00

Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.

The Type 3 method tracks changes using separate columns. Whereas Type 2 had unlimited history preservation, Type 3 has limited history preservation, as its limited to the number of columns we designate for storing historical data.

Following are the steps:

1. Create following tables in database:

CREATE TABLE SCOTT.EMP

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10 BYTE),

JOB VARCHAR2(9 BYTE),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

CREATE TABLE SCOTT.EMP_TYPE3

(

EMP_KEY NUMBER(4),

EMPNO NUMBER(4),

ENAME VARCHAR2(10 BYTE),

SAL NUMBER(7,2),

ENAME_OLD VARCHAR2(10 BYTE)

)

2. Open SPOON à Fileà New à Transformation

3. Insert Table Input Step

4. Right clickàEdit Step. Set the following properties:

5.Insert DataBase Lookup step

6. Set the following properties:

7. Create a Hop between Table Input step and Database Lookup Step

8. Add a Sequence Step. Set the following properties.

Note: A sequence must be created in database.

9. Add Filter Rows. Set the following properties:

10. Insert Select Value Step and set the following properties:

11.Add Filter Row 2 Step. Set the following properties:

12. Add a Dummy(do nothing) Step.

13. Add Execute SQL Script Step to write a Query.

Set the following properties:

14. Insert a Update Step. Set the following properties:

15. Create appropriate Hop between steps. The overall structure will look like following:

16. Save and execute.

Pentaho Data-Integration (aka. Kettle): SCD Type2

2013-02-10T01:25:00.000-08:00

The Type 2 method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. With Type 2, we have unlimited history preservation as a new record is inserted each time a change is made.

Following are the steps:

1. Create required tables in database.

2. Insert following Steps with following properties

2.1 Table Input

2.2 Database Lookup

2.3 Filter Rows

2.4 Get System Info

2.5 Add Sequence 2

2.6 Select Value

2.7 Table Output

2.8 Filter row 2

2.9 Dummy(do nothing)

2.10 Execute SQL Script

2.11 Add System Info 2

2.12 Add Sequence

2.13 Select Value 2

2.14 Table Output 2

3. The overall steps arrangement will be as follows:

4. Save and execute.

Pentaho Data-Integration (aka. Kettle): SCD Type1

2013-01-05T00:44:00.000-08:00

Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.

The Type 1 methodology overwrites old data with new data, and therefore does not track historical data at all.

Following are the steps:

1. Create required tables in database.

2. Insert following Steps with following properties

2.1 Table Input

2.2 Database Lookup

2.3 Filter Rows

2.4 Select Value

2.5 Table Output

2.6 Update

3. The overall arrangement will look as follows:

4. Save and execute.

Pentaho: Configuring Enterprise Console

2012-11-18T05:07:00.000-08:00

Console.xml (/home/sumeet/server/administration-console/resource/config)

<?xml version="1.0" encoding="UTF-8"?>

<solution-path>/home/sumeet/server/biserver-ce/pentaho-solutions</solution-path>

<war-path>/home/sumeet/server/biserver-ce/tomcat/webapps/pentaho</war-path>

<platform-username>joe</platform-username>

<biserver-status-check-period-millis>30000</biserver-status-check-period-millis>

<homepage-url>http://www.pentaho.com/console_home</homepage-url>

<homepage-timeout-millis>15000</homepage-timeout-millis>

<default-roles>Authenticated</default-roles>

<default-server-dir>biserver-ce</default-server-dir>

</console>

Web.xml(/home/sumeet/server/biserver-ce/tomcat/pentaho/webapps/web-inf)

<context-param>

<param-name>fully-qualified-server-url</param-name>

<param-value>http://ctg-BI.srv.citagus.com:8080/pentaho/</param-value>

</context-param>

<init-param>

<param-name>TrustedIpAddrs</param-name>

<param-value>127.0.0.1,192.168.0.240</param-value>

<description>Comma separated list of IP addresses of a trusted hosts.</description>

</init-param>

Hosts(/etc ) or (D:\WINDOWS\system32\drivers\etc\)

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

192.168.0.240 ctg-BI.srv.citagus.com

::1 localhost6.localdomain6 localhost6

PENTAHO: CREATING HYPERLINK

2012-10-04T07:08:00.000-07:00

STEP 1: Open report designer. Create a new report.

STEP 2: Drag fields to Item band.

STEP 3: Select the field for which you want to create hyperlink. Right Click on it and select CREATE HYPERLINK option.

STEP 4: Give the URL.

Note: Once you have created hyperlink you can see the automatically generated code in STYLE EXPRESSIONS :

report: SUBSTITUTE("http://images.google.com/images?q={0}"; "{0}"; URLENCODE([EMPNO]))

STEP 5: Save and Run the report.

STEP 6: End.

PENTAHO: CREATING SUB REPORT

2012-09-15T05:23:00.000-07:00

A Sub-report is a report within another report. Following are the steps to create a sub-report in report designer:

STEP 1: Open pentaho report designer. Add a data source.

STEP 2: ADD another data source in the same report. Give QueryName as SubReport.

Add caption

STEP 3: Drag the sub Report icon to page.

STEP 4: Set the sub report properties as follows:

FILE PATH: This is the path of the sub report.

QUERY: Select the query name for the subreport. E.g. subreport.

STEP 5: Now double click on the sub report field and save it with name subreport2. Another window will be open.

STEP 6: Drag the fields to page area.

STEP 7: Run the main project.

PENTAHO: DRILL DOWN CHART

2012-06-20T04:54:00.000-07:00

-Create one parameterized report in report designer and publish it as xaction.

-Open design studio, create one new java project. Then create one new action sequence and

Input with parameter name.

-In process Action, select get data from in that select relational. Give the required data base

connection details, and query same as given in report designer.

-In process Action, click on add, in that report and select pentaho report.

In report specification, browse and select the xml of the report, in report data select query result.

Give the output report name.

-In the process output select the name of output report name and select response. Save the

xaction and run it.

- now we need to create the main xaction with chart. Create one new xaction, Process input add

Input with the same parameter name, select request and give the default value. In the process

action select get data from, in that relation. Give the required database connection details, and

write down the query.

-in process action, click on add, select chart in that select chart of your choice. Give chart data as

Query_result. Click on toggle tab, in that under configuration add the below tag, i.e. details of link xaction path

<urltemplate><![CDATA[/ViewAction?solution=GIVE SOLUTION NAME&path=&action=XACTION

NAME&PARAMETER NAME={FIELD NAME}]]></url-template>

<paramName>GIVE THE NAME(I.E. NAME OF THE PARAMETER FIELD)</paramName>

<series-name>GIVE THE SERIES NAME</series-name>

- Save and run the xaction.

Pentaho: Parameterized Report

2012-05-26T05:15:00.000-07:00

Step 1: Add a new Data Source.

Step 2: Add two queries

Query 1:

SELECT

"EMPLOYEES"."EMPLOYEENUMBER",

"EMPLOYEES"."FIRSTNAME",

"EMPLOYEES"."JOBTITLE"

FROM

"EMPLOYEES"

Where "EMPLOYEES"."EMPLOYEENUMBER"=${ENO}

Query 2:

SELECT

"EMPLOYEES"."EMPLOYEENUMBER"

FROM

"EMPLOYEES"

Step 3: Add a parameter. Set the properties.

Step 4: Run the report.

Pentaho: Passing Parameter from JSP to Pentaho Report

2012-02-18T00:32:00.000-08:00

Pentaho provides different API’s, which can be used in JSP pages. We can easily pass parameter from a JSP page to Pentaho reports using these APIs. Below are the steps

1. Create a .xaction file in design studio.

2. Add a Process Input as NAME.

3. The query will be as follows:

SELECT "EMP"."EMPNO", "EMP"."ENAME", "EMP"."SAL", "EMP"."DEPTNO"

FROM "EMP" where "EMP"."ENAME"={NAME}

4. Create a HTML file with following code:

<HTML>

<BODY BGCOLOR="pink">

<br><br><br><br><br><br><br><br>

<Center>

<FORM ACTION="http://localhost:8080/pentaho/sumeet_session" METHOD=post>

ENTER YOUR NAME : <INPUT TYPE="TEXT" NAME="NAME" SIZE=20>

<P><INPUT TYPE="SUBMIT" value="Submit">

</FORM>

</center>

</BODY>

</HTML>

5. Create a JSP (sumeet_session.jsp) page with following code:

<%@ page import=

"java.util.Hashtable,

java.util.Calendar

,java.util.Date

,java.util.Vector

,java.sql.*

,java.text.SimpleDateFormat

,javax.naming.Context

,javax.naming.InitialContext

,javax.sql.DataSource

,java.util.*" pageEncoding="UTF-8"%>

<%@ page language="java"

import="java.util.ArrayList,

java.util.Date,

java.io.ByteArrayOutputStream,

org.pentaho.platform.util.web.SimpleUrlFactory,

org.pentaho.platform.web.jsp.messages.Messages,

org.pentaho.platform.engine.core.system.PentahoSystem,

org.pentaho.platform.uifoundation.chart.DashboardWidgetComponent,

org.pentaho.platform.web.http.request.HttpRequestParameterProvider,

org.pentaho.platform.web.http.session.HttpSessionParameterProvider,

org.pentaho.platform.api.engine.IPentahoSession,

org.pentaho.platform.web.http.WebTemplateHelper,

org.pentaho.platform.util.VersionHelper,

org.pentaho.platform.util.messages.LocaleHelper,

org.pentaho.platform.engine.core.solution.SimpleParameterProvider,

org.pentaho.platform.engine.services.solution.SolutionHelper,

org.pentaho.platform.uifoundation.chart.ChartHelper,

org.pentaho.platform.web.http.PentahoHttpSessionHelper"

<HTML>

<BODY>

String name = request.getParameter( "NAME" );

session.setAttribute( "NAME", name );

response.setCharacterEncoding(LocaleHelper.getSystemEncoding());

String baseUrl = PentahoSystem.getApplicationContext().getBaseUrl();

IPentahoSession userSession = PentahoHttpSessionHelper.getPentahoSession( request );

HttpRequestParameterProvider requestParameters = new HttpRequestParameterProvider( request );

HttpSessionParameterProvider sessionParameters = new HttpSessionParameterProvider( userSession );

SimpleParameterProvider parameters = new SimpleParameterProvider();

// pass the NAME to the report

parameters.setParameter( "outputType", "html" );

parameters.setParameter( "NAME",name);

// create an output stream for the report content

ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

ArrayList messages = new ArrayList();

// run the action sequence 'samples/dashboard/jsp/report.xaction'

SolutionHelper.doAction("SessionPara","","session_para.xaction","sumeet_session1.jsp",parameters,outputStream,userSession,messages, null ) // write the report content into this page

out.write(outputStream.toString());

</BODY>

</HTML>

6. Save the jsp file at following location:

biserver-ce\tomcat\webapps\pentaho\jsp

7. Make the required changes into web.xml file.

8. Run the html file.

9. Enter the name in text box and click submit button.

Hope this article will make your task easier.

Installing JAVA in Linux(SentOS)

2012-02-01T04:55:00.000-08:00

1. Download jdk.bin and extract it to any location.

2. Create the following file /etc/profile.d/java.sh with following content

Export JRE_HOME=/home/Sumeet/java/jdk_1.6/jre

Export PATH=$PATH : $JRE_HOME/bin

Export JAVA_HOME=/home/Sumeet/java/jdk_1.6

Export JAVA_PATH=$JAVA_HOME

Export PATH=$PATH : $JAVA_HOME/bin

3. Give the read, write and execute permission on all files under tomcat/bin folder using CHMOD command:

CHMOD +rwx *.*

Or,

CHMOD a+rwx *.*

4. Run ../tomcat/bin/startup.sh file

Sh startup.sh

5. Start the server

Pentaho: Embedding a chart into a Report

2012-01-07T05:25:00.000-08:00

1: Drag the chart element into report header area.

2: Click on Edit Chart button. Select the chart type.

3: Select the desired attributes.

4: Give category column as DEPTNO, value columns as [DEPTNO,TOTSAL], series names as [DEPTNO,TOTSAL] .

Note: summary only attribute must be checked off.

5: Run the report.

PENTAHO DESIGN STUDIO: CREATING SIMPLE xaction

2011-12-04T05:07:00.000-08:00

STEP 1: Open Design Studio

STEP 2: Select NEW->FILE->PROJECT

STEP 3: Select java project-> Next->Give the project name->select create project prom existing source

->select the directory->Next->Click Finish.

STEP 4: Select BI Platform->New Action Sequence->select the container-> give the file name-> Finish

Step 5: Select define process. Select get data from->Relational under Process Output

STEP 6: Give the following details like name, query, output etc.

STEP 7: under process output select the result set name.

STEP 8: select TEST->give the user console URL-> click generate URL-> Run

STEP 9: END

Pentaho: Passing parameter to chart from jsp

2011-08-31T05:01:00.000-07:00

1. Create a simple xaction with parameter DN.

2. Query:

select deptno,sum(sal) totsal

from emp where deptno={DN}

group by deptno;

Output for xaction

3. Now create a widgetsample.widget.xml file with the following code:

<chart>

<!-- Created August 6, 2008

@author Yoganand-->

<chart-type>BarChart</chart-type>

<title>EmployeeID v/s Salary Chart</title>

<title-position>top</title-position>

<title-font>

<font-family>Verdana</font-family>

<is-bold>false</is-bold>

<is-italic>false</is-italic>

</title-font>

<chart-background type="color">#FFFFFF</chart-background>

<plot-background type="color">#FFFFFF</plot-background>

<color-palette>

<color>#CCCCCC</color>

</color-palette>

<is-3D>true</is-3D>

<is-stacked>true</is-stacked>

<start-angle>150</start-angle>

<border-visible>true</border-visible>

<max-bar-width>0.08</max-bar-width>

<min-bar-width>0.06</min-bar-width>

<include-legend>true</include-legend>

<legend-font>

<font-family>Verdana</font-family>

<is-bold>false</is-bold>

<is-italic>false</is-italic>

</legend-font>

<label-font>

<font-family>Verdana</font-family>

<is-bold>false</is-bold>

<is-italic>false</is-italic>

</label-font>

<label-background-paint>#FFFFFF</label-background-paint>

<label-gap>0.01</label-gap>

<data>

<data-solution>sample</data-solution>

<data-path>samplesofwidget</data-path>

<data-action>widgetsample.xaction</data-action>

<data-output>query_result</data-output>

<data-orientation>columns</data-orientation>

</data>

</chart>

4. Save xaction and widgetsample.widget.xml file at following location

C:\Program Files\pentaho\server\biserver-ee\pentaho-solutions\sample\samplesofwidget

5. Create a JSP file with following code:

<%@ page language="java"

import="java.util.ArrayList,

java.util.Date,

java.io.ByteArrayOutputStream,

org.pentaho.platform.util.web.SimpleUrlFactory,

org.pentaho.platform.web.jsp.messages.Messages,

org.pentaho.platform.engine.core.system.PentahoSystem,

org.pentaho.platform.uifoundation.chart.DashboardWidgetComponent,

org.pentaho.platform.web.http.request.HttpRequestParameterProvider,

org.pentaho.platform.web.http.session.HttpSessionParameterProvider,

org.pentaho.platform.api.engine.IPentahoSession,

org.pentaho.platform.web.http.WebTemplateHelper,

org.pentaho.platform.util.VersionHelper,

org.pentaho.platform.util.messages.LocaleHelper,

org.pentaho.platform.engine.core.solution.SimpleParameterProvider,

org.pentaho.platform.uifoundation.chart.ChartHelper,

org.pentaho.platform.web.http.PentahoHttpSessionHelper"%>

// set the character encoding e.g. UFT-8

response.setCharacterEncoding(LocaleHelper.getSystemEncoding());

// get the current Pentaho session or create a new one if needed

IPentahoSession userSession = PentahoHttpSessionHelper.getPentahoSession( request );

<html>

<head>

<title>Chart By sumeet</title>

</head>

<body>

<tr>

SimpleParameterProvider parameters = new SimpleParameterProvider();

parameters.setParameter( "image-width", "400"); //$NON-NLS-1$ //$NON-NLS-2$

parameters.setParameter( "image-height", "400"); //$NON-NLS-1$ //$NON-NLS-2$

parameters.setParameter("DN","20");

StringBuffer content = new StringBuffer();

ArrayList messages = new ArrayList();

ChartHelper.doChart( "sample", "samplesofwidget", "widgetsample.widget.xml", parameters, content, userSession, messages, null ); //$NON-NLS-1$ //$NON-NLS-2$

<br/>

<%= content.toString() %>

</td>

</tr>

</table>

</div>

</body>

</html>

6. Run the jsp page

7. END

PENTAHO: CREATING HYPERLINK

2011-08-25T05:04:00.000-07:00

1: Open report designer. Create a new report.

2: Drag fields to Item band.

3: Select the field for which you want to create hyperlink. Right Click on it and select CREATE HYPERLINK option.

4: Give the URL.

Note: Once you have created hyperlink you can see the automatically generated code in STYLE EXPRESSIONS :

report: SUBSTITUTE("http://images.google.com/images?q={0}"; "{0}"; URLENCODE([EMPNO]))

5: Save and Run the report.

6: End.

Pentaho: Passing parameters from .xaction to .prpt

2011-07-26T00:39:00.000-07:00

Pentaho allow us to pass parameters from .xaction file to .prpt file. This can be done by following steps:

1. Create a .prpt file in Report Designer.

2. Add a parameter DN.

3. Query:

SELECT "EMP"."EMPNO", "EMP"."ENAME", "EMP"."SAL", "EMP"."DEPTNO"

FROM "EMP" WHERE "EMP"."DEPTNO"=${DN}

4. Add Row Banding Feature.

5. Preview will look like this:

6. Create a xaction in Design Studio.

7. Add an input parameter DN

8. Add pentaho Report in Process Action. Under that select a .prpt file and add a Report Parameter DN.

9. Select Process Output.

10. Test the xaction.

DELETE Vs TRUNCATE Vs DROP

2011-07-25T06:22:00.000-07:00

DELETE

· It’s a DML command

· It can be used to delete all or specifed number of records

· We can use WHERE clause with DELETE

· We can do ROLLBACK

· Oracle server do not free the allocated memory after DELETE operation

TRUNCATE

· It’s a DDL command

· It can be used to delete all records

· We can not use WHERE clause with TRUNCATE

· We can not do ROLLBACK, since it is a DDL command

· Oracle server free the allocated memory after TRUNCATE operation

DROP

· It’s a DDL command

· It can be used to delete all records along with table structure

· We can not use WHERE clause with DROP

· We can not do ROLLBACK, since it is a DDL command

· Oracle server free the allocated memory after DROP operation

ANSI Join Vs Oracle Traditional Join

2011-07-25T06:06:00.001-07:00

Sometimes you need to use data from more than one table. A join is used to view information from multiple tables. Hence, you can join tables together to view information from more than one table.

Prior to the Oracle9i release, the join syntax was different from the ANSI standards. ANSI join syntax does not offer any performance benefits over the Oracle-traditional join syntax that existed in prior releases.

The main strengths of ANSI join syntax, which I found while working, are

Clarity and Easiness: The use of the JOIN clause separates the relationship logic from the filter logic (the WHERE) and is thus cleaner and easier to understand.

For example, instead of using

SELECT d.dname, e.empno
FROM dept d, emp e
WHERE d.deptno = e.deptno;

You can use this

SELECT d.dname, e.empno
FROM dept d JOIN emp e
USING (deptno);

Or this

SELECT d.dname, e.empno
FROM dept d JOIN emp e
ON (d.deptno = e.deptno);

And instead of using traditional left outer join

SELECT e.ename, d.deptno, d.dname

FROM emp e , dept d

WHERE e.deptno=d.deptno(+);

You can use this

SELECT e.ename, d.deptno, d.dname

FROM emp e FULL OUTER JOIN dept d

ON (e.deptno = d.deptno) ;

Support for Full Outer Join: Oracle traditional join does not support full outer join. But you can do full outer join using ANSI ‘FULL OUTER JOIN’ clause.

SELECT e.last_name, d.department_id, d.department_name

FROM employees e FULL OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

Best Practice: It's good practice as most developers and dba's will use ANSI-join nowadays and you should follow the standard. Certainly all modern query tools will generate ANSI-join.

ANSI join has the following weaknesses compared to Oracle traditional join:

· People are far more familiar and comfortable with the traditional Oracle approach.

· ANIS join is not supported in Oracle 8i or prior versions.