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

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

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

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

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

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

<console>

  <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> 

  <!-- comma separated list of roles (no spaces) --> 

  <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

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

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

-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.