Friday 31 January 2014

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

Videos for dashboard designers

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.
 






Tuesday 21 January 2014

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