14.12.2011 von Jens Bleuel.

The ExeBatLauncher is a simple way of calling .bat files as .exe files in creating an .exe command that calls the same .bat command.

For example you want to call the Spoon.bat file by Spoon.exe, simply rename the ExeBatLauncher.exe (that is found in the distrib folder) to Spoon.exe and copy it to the same directory and you are done.

If you want it now, just download it and use it, it’s free

If you want to have a polished Spoon.exe with the right icon to test with, just download it from the PDI-6949 attachment.

The reason why I created this was a Pentaho Data Integration JIRA case: PDI-6949 - “While in PDI-2925 the feature of kettle.exe was removed, you cannot add a .bat file to the quick launch of Windows 7. The workaround is to add a cmd.exe to the quick launch and add /c <path to .bat> to modify it. This is due to microsoft security in Win 7.”

I also thought a lot of launching Kettle just by clicking on a .ktr or .kjb file and accomplished this by a wrapper .bat (calling the target bat with the /file: option). There are also nasty ways of launching .bat files by creating self extracting exe archives (nice trick), but this is just too dirty to be professional. But I tried also this and it worked even with the Spoon icon, but some limitations like having to extract the.bat file from the self extracting archive into the distribution folder are not a good solution. Further investigation into existing solutions of calling Java from .exe brought some nice projects but that is not the solution we want at this time.

I wanted to have a pretty simple .exe that just calls the .bat with the same name and here is the code for this:

As you see it supports:

• calling the .exe/.bat from every folder
• passing all arguments from the caller
• returning the return code to the caller

If you want to compile it, follow the instructions in the _README.txt file in the attachment: ExeBatLauncher.zip

I look forward to your experiences!

Geschrieben in General | Keine Kommentare »

7.12.2011 von Jens Bleuel.

Today was our first German Customer Meeting that Bruno initiated and it took place at one of our customer locations @Wirecard AG (many thanks!).

A nice (and squeezed…) agenda and around 20 attendees discussed the presented customer solutions, their experiences and what Pentaho can do better, but also: what really works well and this is a lot and proven in medium and large deployments. Here are some examples in addition to “normal” BI:

• Processing 500 thousand transactions per day (credit card and alternative payment methods)
• Merchant deposit monitoring including fraud detection of merchants (!)
• Application and data row level role based security in a multi-tenant solution using special role management via a Mondrian plug-in
• Highly performant solutions with load balancing servers and high availability (99.95%), one of them with a failover with two complete separate locations
• Pentaho integrates very well in existing IT infrastructures
• Event triggered ETL by an ESB (Enterprise Service Bus) / SOA architecture: JBoss ESB triggers PDI Jobs for application integration (JMS, File, HTTP, SMTP, FTP etc.). At this time via the Kettle API but it is planned to use Kettle Web-Services
• PDI is used as a data source for reporting, just a nice EAI use case
• Pentaho is integrated into JBoss EAP (the customer version of JBoss AS, everything in this platform is secured by default)
• 3 of the attendees are using Infobright
• Report bursting was accomplished by 3 different methods: 1) using xActions, 2) using ant scripts, 3) using Kettle

Major discussed improvements:

• More integrated and consistent security throughout all products (it’s possible but special requirements are tricky to implement)
• Report bursting with scheduling should be more standardized and user friendly
• Since Pentaho can be extended and embedded very well via extension APIs, the upgrade to next versions should be more seamless by special dedicated documentation for OEM and stable API extension points [this will be addressed in the road map by the SDK]
• Upgrade process: upgrade tools would be a huge benefit for supporting it, also when some releases were not installed (e.g. jump from 3.x to 4.1 instead of 4.0)

As part of the Pentaho Product Management team, I presented the product road map and gave some quick insights in the Kettle Star Modeler.

I look forward to the next event of this type for knowledge sharing between Pentaho customers and users. We found already one volunteer (Guy) to take it from here

BTW: This event was only in German language (at this time!) and thanks again to Wirecard, Rob & Bruno for organizing this and to the attendees for their presentations and active participation!

Here are some impressions….

Geschrieben in General | Keine Kommentare »

22.6.2011 von Jens Bleuel.

New interactive reporting and enhanced visualizations enable fast and affordable user-driven BI

More details can be found over here: http://www.pentaho.com/power-to-the-user

Watch the video !

Geschrieben in General | Keine Kommentare »

20.4.2011 von Jens Bleuel.

Within Kettle you have the Normalizer and Denormalizer steps to help with pivoting (a simplified use case for pivoting is changing the row/column axis, see also transpose). Imagine you would like to have a cross table, you may use a Kettle transformation to accomplish this. (In these days Pentaho Reporting’s cross tab functionality is on the road map but not implemented, yet. One solution would be to use a Kettle Transformation as a data source.).

Another solution for a cross tab is to use the SQL CASE WHEN construct. Here is an example:

The result of the following sample query select status, year_id, sum(totalprice) as totalprice from orderfact group by status, year_id looks like this:

When we want to put the years (2003, 2004, 2005) into separate columns, we can use the SQL CASE WHEN construct:

CASE WHEN Boolean_expression THEN result_expression [ …n ] [ ELSE else_result_expression ]

to accomplish this, e.g.:

select status,
sum(case when year_id=2003 then totalprice else 0 end) as TOTAL_2003,
sum(case when year_id =2004 then totalprice else 0 end) as TOTAL_2004,
sum(case when year_id =2005 then totalprice else 0 end) as TOTAL_2005
from orderfact group by status

Et voilà, you have data that can be used as a cross tab:

Another use case for dealing with planning data

In another use case, we store actual and planning data (e.g. for turnover, quantity, weights etc.). To make it a bit more challenging, planning data is often stored as a budget and multiple forecasts or different plan versions (scenarios).

One solution for this problem is to store these data in separate columns, e.g. have columns for actual and other columns for planning data. Having multiple measures (see above) needs to have multiple columns for each type of the measures, e.g. turnover_actuals, quantity_actuals etc. and have this also for all types of planning scenarios. Thus you would get a big amount of columns and the biggest issue is: What happens when you need a new planning scenario? You would need to add more columns and your data model is not flexible enough and needs to be changed.

Another solution is to store the definition of an actual or planning type as an extra TYPE_ID in our table. This means each row is marked whether it is an actual or any kind of planning type.

But, when you sum up the whole table, the result would be wrong since it contains a mix of actual and planning data.

This can be solved also with the CASE WHEN construct even within a Mondrian schema definition:

    <Measure name=”Sales Actuals” aggregator=”sum” formatString=”#,###.00″>
    <MeasureExpression>    <SQL dialect=”generic”>
             (case when type_id=0 then TOTALPRICE else 0 end)
    </SQL>    </MeasureExpression>    </Measure>

    <Measure name=”Sales Budget” aggregator=”sum” formatString=”#,###.00″>
    <MeasureExpression>    <SQL dialect=”generic”>
          (case when type_id=1 then TOTALPRICE else 0 end)
    </SQL>    </MeasureExpression>    </Measure>

[for more details see the Mondrian documentation about Measures]

With this solution you are flexible with your data model and the result will be correct.

Another more complex use case is to combine this with a reporting solution for project costing or contribution accounting. When you have a large number of different costs and would store these costs in separate columns you will reach a non manageable amount of columns and your data model is not flexible enough. It’s better to store these different types of costs in rows and you are much more flexible.

By all the flexibility with the data model you need to consider performance aspects of your database, e.g. if column based databases behave different with the CASE WHEN (I have not tested this) and different approaches should be tested. Any comments and findings are much appreciated.

Geschrieben in General | Keine Kommentare »

22.1.2011 von Jens Bleuel.

This is the starting point for the blog Fun Stuff about the Open Source ETL Tool Kettle aka Pentaho Data Integration (PDI).

The short history: Five years ago, in early December 2005, Matt Casters released the initial open source version of Kettle. I knew Kettle already before this time, developed a plug-in to get data out of SAP in 2005, did a lot of things around this great tool and joined Pentaho in mid 2007. You can read a bit more about the story in the book Pentaho Kettle Solutions by Matt Casters, Roland Bouman and Jos van Dongen.

More about Pentaho:

Pentaho Links
Pentaho Books

.

More about me:

Homepage
XING Profile
Twitter

Geschrieben in Kettle (PDI), General | Keine Kommentare »