How to apply SCD Type2 using pentaho Kettle

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

Create a job like below:-



Create a  Transformation like below:- 


  you can Download job and tranformation from below link

https://drive.google.com/file/d/0B_j1hJxesvxdV3hMMWJmZndyQUk/edit?usp=sharing

Detail of Each Step is given Below:-
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 BTable -- A new horizon of OLAP data representation

BTable is a component for the Pentaho Community Dashboard Designer (CDE) that extends the standard Table Component with OLAP functionalities and provides a new drill experience.
Dashboard users can easily interact with the table through a context menu that allows to:

• add, change or remove dimensions
• add, change or remove measures
• create crosstables
• display grand totals and subtotals
• sort by dimensions or measures
• add additional filters to the table
• unlink the table from the dashboard context to stop listening to external parameters
• drill a cell or drill a row
• swap axis
• export data and also formatting to Excel

Dashboard designers can build flexible and rich tables with little effort, indicating a Mondrian schema and a
JNDI connection and writing the initial query in a more friendly manner.
Resouces:-
Videos for dashboard users

• Play with Dimensions
• Play with Measures
• Play with Pivot
• Drill Anywhere
• Play with Filters and Sorts
• Table Settings
• Other Functions

Videos for dashboard designers

• Add a BTable to a dashboard

BTable is available on Pentaho Marketplace.

Release files available on SourceForge.
Source code available on GitHub.
For questions, suggestions and bugs reporting use Pentaho Forums.

 

Dealing with MySQL connection idle connection timeout

The solution turns out to be fairly easy.  What is required is a piece of software to manage the database connection pool and keep the connections alive.  The recommended method is to use c3p0.
More information about c3p0 can be found on the project's site:

• http://sourceforge.net/projects/c3p0
• http://www.mchange.com/projects/c3p0/index.html

To use c3p0 to solve this problem, first download the c3p0-0.9.1.2.bin.zip, stop your Java application server, take the jar files c3p0-0.9.2.jar and mchange-commons-java-0.2.3.3.jar inside the downloaded archive and copy them to your WEB-INF/lib directory on your java application server. I use Tomcat 6 on Ubuntu 8.10, so my installation path happens to be:

 /var/lib/tomcat6/webapps/pentaho/WEB-INF/lib

Next, we'll need to modify the hibernate settings for MySQL.  The file we will modify is called mysql5.hibernate.cfg.xml and is located in the pentaho-solutions/system/hibernate folder by default.

You will insert the following text just after the <session-factory> tag and just before the <!-- MySQL Configuration --> comment.

         <!--
            hibernate c3p0 settings
        -->

        <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.acquire_increment">3</property>
        <property name="hibernate.c3p0.idle_test_period">10</property>
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">75</property>
        <property name="hibernate.c3p0.max_statements">0</property>
        <property name="hibernate.c3p0.timeout">25200</property>
        <property name="hibernate.c3p0.preferredTestQuery">select 1</property>
        <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>


        <!--
            hibernate cache settings
        -->
 <!-- End of patch added Friday April 3, 2009 to address issues of the database connection going dead
 -->

        <property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
        <property name="hibernate.generate_statistics">true</property>
        <property name="hibernate.cache.use_query_cache">true</property>

How to Create pentaho Analyser Report

Pentaho Analyzer Report

Note:- This Is only for pentaho Enterprise Edition  BA server.
In this article we'll look at Pentaho Analyzer Report tool. This is modern OLAP tool and it's available only in commercial editions of Pentaho BI.

For evaluation I'll use demo data warehouse supplied with Pentaho BI Server called SteelWheels.

Result table setup

As usually let's start from initial screen.

Big white area in the middle is a place for building a table. The list from the right is columns from data warehouse: Dimensions and Measures. To create a table you may simply drag fields from the right pane to the middle area. Analyzer Report also allows to use layout pane for building a table. You may open layout pane by clicking it's icon, which marked on the image above.

I drag Product Line to Rows, Markets Territory to Columns and Quantity to Numbers. The result you may see on the picture below.

Note that this table don't have total values. And I like it because total values only add more mess to a table. And here you may concentrate on comparing values of similar levels. But if you need total values - you may add them via Report Options. Let me show you Report Options window, to open it click on "More" in the top right corner and then select "Set Report Options".

One useful type of analysis is to compare how Measure changes over same months in different years. But if you try to put Months to rows and Years to Columns Pentaho Analyzer Report will tell you that "Months has to be on the same axis as Years." Same issue present in Jasper Ad Hoc and JPivot. This issue extends to all Dimensions where you need to put higher level on rows and lower level on columns. Currently this is a restriction of mondrian tool on which all these tools are based. JPivot  allows to achieve similar to desired result by using MDX queries, but it's not very convenient. And Pentaho Analyzer Report have a resolution for Time dimension which I'll show later, but I don't sure if it's possible to use this solution with other types of dimensions.

Data filtering

One of the most frequent request in data analysis is to look at recent data. That may be expenses by months for recent year, or sales by days in recent month. Navigating trough Time dimension every time you need a recent period is tedious. And I'm happy to demonstrate one feature which Pentaho suggest for that purpose. To demonstrate it I'm using a data warehouse with recent data of time which I spend for self education.

Say I want to see how many time I spent in each month of recent 1.5 year for self-education. To achieve that, first thing I do is adding Month to Filters. You may either open Filter pane and drag Month there or right-click on Month level, as shows on the picture below, and choose Filter option.

Filter Dialog will be shown. Check "previous" and enter 18 in corresponding Month field. And press OK. Now all data will be filtered by this criteria.

After that I've added Years and Months to Rows, selected Measure to use, and get following result:

This feature is actually extremely useful by the following fact. All these relative terms from Date Filter Dialog(current, previous, next) are really relative. In other words if you create a report with "current month" filter, save it, and then open it next month - you will see data for new month.

Changing detail level

Seems like Pentaho Analyzer Report don't support Drill Member and Drill Position types of navigation, instead it suggest clear and simple Drill Replace and Drill Through.

For Drill Replace just right-click on a Dimension's member, say "Classic Cars", and select "Keep Only Classic Cars and Show Vendor" like on the picture below.

As a result you'll see details for "Classic Cars".

What I like about Drill Through in Pentaho Analyzer Report is that it shows all dimensions in the result table. Drill Through links can be turned on in Report Option dialog. I won't spend time on that, let's go further.

Calculations

Pentaho Analyzer Report provides several types of calculations. First is a set of available functions: percent, rank, running sum and percent of running sum. Second type is an ability to enter MDX calculated member formula. Third is Trends. And forth is an ability to change subtotal aggregation.

They are available from menu when right-clicking on Measure header in a table. The menu is shown on the picture below.

So on one hand Pentaho provides many calculations out of the box, and makes simple MDX formulas easy to write, but on the other hand, if you need something more complex - fill free to use MDX expressions :-).  I'm going to analyze deeper available facilities, but now we'll dive deeper into one really useful type of calculations called Trends.

Do you remember the type of analysis when you need to put months by rows and years by columns described and the start of this article. It's possible to achieve this result with Trends set of functions. They are available from menu shown above: User Defined Number > Trend Number.

To prepare for using this function I put Year = 2005 to filters. Put Months on Rows and select Quantity as a Measure. As a result we see quantities for each month of 2005. Then right-click on Quantity header and select menu option User Defined Number > Trend Number. Dialog as below will be shown.

After doing all this magic and pressing ok, new column is added to the table which contain values for a year 2004. You may check the result on the picture below.

Visualization

For demonstrating visualization features lets use following table. Years are on rows, Product Lines on columns and Quantity is a Measure. Now click on chart icon for switching to chart mode.

We switched to chart mode. Column chart show comparative quantities of different product lines in different years. For time is better to use line chart, so let's change Chart type to Line.

On screenshot below you see the results of changing chart type to Line. And now, another great feature is an ability to drill down on a graphs and charts. Let's click on Vintage Cars of 2004

Results of drilldown are shown below. You see a line chart for quantities of Vintage Cars over quarters of 2004.

Conclusions
I have to admit that Pentaho Analyzer Report made a good impression on me. Analyzer Report looks like it was carefully designed with a great attention to user requirements. Many features which I planned to put in this review as desirable I found in Pentaho Analyzer Report in the process of preparing the review and demonstrated here.

How to create JNDI Connection for pentaho reports

How To create ‘JNDI’  Connection for Reports

Most important benefit of JNDI over JDBC is that it provides centralized access/management of database connection used in different files (prpt/xaction, xml, jsp etc.)So if you are using JNDI for your PUC reports or Analysis reports and you want to update any database credentials you can update in one place and it is affected in your all reports and analysis.

JNDI	JDBC
Need to define credentials at one place	Need to define credentials(jdbc connection)  in every file
Update at one place and changes will be updated in every file which is using that JNDI connection	Need to update each place in the file where JDBC connection is defined
This is faster as only you need to create connection one time.	This is slower because every time it is creating new connection.

 Creating a JNDI connection:-

1)      Login to Pentaho enterprise console :-

http://localhost:8088

                        *replace localhost with your server IP address.

2)      Now click on Administration tab of Penataho Enterprise Console to create/view/edit database connection

                                 

Here you can view all of connections created on the Pentaho Enterprise Console and new connection to the database can be created.

Now let’s create a new connection (for SQL Server) by clicking on [+] explore button:-

1)      Put name of the connection, this name will be used as name of JNDI connection.

i.e.  pentahoLocal

2)      Select database driver as Net.sourceforge.jtds.jdbc.Driver from drop down.

3)      Enter appropriate user name and password.

4)      Put connection URL for your database connection.

i.e.          jdbc:jtds:sqlserver://SVAISH-PC:1433/pentaho;instance=SQL2008R2

For more details please refer below screenshot:

Now test your connection by clicking “Test” button at the bottom of the page.

 

If you get success message then click on “Update” button so that this connection can be available on PUC (Pentaho user console).later this (connection) can be referred on Reports and Analysis as a JNDI connection name.

Now select Services tab on Enterprise Cconsole to get update/refresh screen.

Now Refresh Pentaho BI Server (PUC) to get server updated for these new changes.

Now login to Pentaho user console (PUC)

http://localhost:8080/pentaho/Home

Now refresh PUC by clicking refresh button.

Creating a JNDI connection on Pentaho Report Designer

1)      open Pentaho Report Designer  and create a new report

2)      now right click on dataset to create a JNDI/JDBC connection

 

3)      now click on JDBC to create a new JNDI/jdbc connection

 

Now click on expand [+] to create/edit a new connection 

 

Now create/edit connection for JNDI which will be used inside PUC Report.

Now publish your report on PUC.

This report will run on JNDI connection.

Benefits of ‘JNDI’ over ‘JDBC’ connection approach

Most important benefit of JNDI over JDBC is that it provides centralized access/management of database connection used in different files (prpt/xaction, xml, jsp etc.)So if you are using JNDI for your PUC reports or Analysis reports and you want to update any database credentials you can update in one place and it is affected in your all reports and analysis.

JNDI	JDBC
Need to define credentials at one place	Need to define credentials(jdbc connection)  in every file
Update at one place and changes will be updated in every file which is using that JNDI connection	Need to update each place in the file where JDBC connection is defined
This is faster as only you need to create connection one time.	This is slower because every time it is creating new connection.

         

Creating a JNDI connection:-

1)      Login to Pentaho enterprise console :-

http://localhost:8088

                        *replace localhost with your server IP address.

2)      Now click on Administration tab of Penataho Enterprise Console to create/view/edit database connection

Here you can view all of connections created on the Pentaho Enterprise Console and new connection to the database can be created.

Now let’s create a new connection (for SQL Server) by clicking on [+] explore button:-

1)      Put name of the connection, this name will be used as name of JNDI connection.

i.e.  pentahoLocal

2)      Select database driver as Net.sourceforge.jtds.jdbc.Driver from drop down.

3)      Enter appropriate user name and password.

4)      Put connection URL for your database connection.

i.e.          jdbc:jtds:sqlserver://SVAISH-PC:1433/pentaho;instance=SQL2008R2

For more details please refer below screenshot:

Now test your connection by clicking “Test” button at the bottom of the page.

If you get success message then click on “Update” button so that this connection can be available on PUC (Pentaho user console).later this (connection) can be referred on Reports and Analysis as a JNDI connection name.

Now select Services tab on Enterprise Cconsole to get update/refresh screen.

Now Refresh Pentaho BI Server (PUC) to get server updated for these new changes.

Now login to Pentaho user console (PUC)

http://localhost:8080/pentaho/Home

Now refresh PUC by clicking refresh button.

Creating a JNDI connection on Pentaho Report Designer

1)      open Pentaho Report Designer  and create a new report

2)      now right click on dataset to create a JNDI/JDBC connection

3)      now click on JDBC to create a new JNDI/jdbc connection

Now click on expand [+] to create/edit a new connection 

Now create/edit connection for JNDI which will be used inside PUC Report.

Now publish your report on PUC.

This report will run on JNDI connection.