tag:blogger.com,1999:blog-45478364982573520742024-03-13T22:23:42.094+01:00Rama's Free ThoughtsSergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.comBlogger25125tag:blogger.com,1999:blog-4547836498257352074.post-82690089401728115732014-06-07T00:35:00.000+02:002014-06-07T00:35:07.096+02:00Building a Data Mart with Pentaho Data Integration - Video Course Review<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRW_BqCNrCxN6HQMDnhJjPAZfgUJZa6dk1LS2WCg6egiP3VwkK5X5NZT13U-7ddJe_oHltpiYpA9mp9vxjQ_CnCaw4dBsYv-Ygt2rqP-reLRfcUlgD251a-87m1Qb-J-61VtO_xAkSbMY/s1600/building_dm_with_pdi.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRW_BqCNrCxN6HQMDnhJjPAZfgUJZa6dk1LS2WCg6egiP3VwkK5X5NZT13U-7ddJe_oHltpiYpA9mp9vxjQ_CnCaw4dBsYv-Ygt2rqP-reLRfcUlgD251a-87m1Qb-J-61VtO_xAkSbMY/s1600/building_dm_with_pdi.png" height="178" width="320" /></a></div>
Some days ago I had the opportunity to look at this video course, <a href="http://bit.ly/1tFrGX2">Building a Data Mart with Pentaho Data Integration</a> from Packt, same editor I'm working for in other Pentaho books.<br />
<br />
I was really interested in evaluating it, firstly because the author, Diethard Steiner, is a valid community member and author of a great number of useful articles on his own <a href="http://diethardsteiner.blogspot.it/">blog</a>. Secondly because it seemed me a good introduction about how to approach the design and development of a complete ETL process to load a Datamart with Pentaho Data Integration.<br />
<br />
The course is structured through 9 chapters, clearly explained with a good preamble on top of every chapter to give a clear view of the topic the author is covering also to beginners users. The explanation is clearly undertandable because author speaks a good english at right speed (in my opinion) giving also people that are not perfectly familiar with english language the ability to clearly understand the concepts.<br />
<br />
First chapter talks about how to setup PDI (Pentaho Data Integration) and illustrates source and target schema and basics of their design. The thing I liked a lot here was that author used a columnar database (MonetDB) as target platform for its datamart. This way, user can have the opportunity to know about another interesting database technology to use during his/her work. Because the vast majority of the users could not know anything about this database platform, I would have appreciated a few more details about it just to give some necessary background on the database platform and tools used.<br />
<br />
Second chapter shows Table Input and Table Output tasks and their use to massively input and output data from a PDI transformation. Good to see also the illustration of the MonetDB bulk loader step, as a more optimized alternative to Table Output step.<br />
<br />
Third chapter is all about Agile BI made with PDI and Agile BI Plugin. A good introduction to the benefits of early prototyping and how to do this with Pentaho technology.<br />
<br />
Forth chapter is the place where author talks about <a href="http://en.wikipedia.org/wiki/Slowly_changing_dimension">Slowly Changing Dimensions</a> and how to use PDI to read and write data in Slowly Changing Dimensions of Type 1 and 2. Here, I would have appreciated just one more slide to illustrate beginners users a few more theory about Slowly Changing Dimensions to clearly let user understand which are the problems this approach is going to address in the design of a Data Mart.<br />
<br />
Fith chapter talks about time dimension, how to simply structure it and how to effectively generate data to put in it.<br />
<br />
Sixth chapter is a guide through techniques used to populate the Fact Table. This same topic will then continue in eighth chapter where the author talks about Changing Data Capture and how to structure our jobs and transformation to load our fact table. Here, in chapter eight, in my opinion a thing is missing and the CDC topic is partially addressed. I would have appreciated to hear something about using the Merge step to get the table's delta from the previous data load and then synchronizing fact table through the Synchronize After Merge step. In my opinion this is a more generic and interesting way to address this problem.<br />
<br />
Finally, chapter seven is all about how to better organize jobs and transformation efficently and chapter nine adresses the logging and scheduling of transformation.<br />
<br />
Therefore, to give a summary about this video course, it is a good introduction to PDI and how to use it to load a Data Mart. I find it very useful for beginners and nice to see for intermediate users that want to go deeper on some concepts. Samples are well done and really help the user in understanding better the concepts explained. Just a side note: during the videos some of the samples are shown by using an early version of PDI 5 and some other by using the previous version PDI 4.4. Starting from the valid assumption that author was trying to show users the new version of PDI, I found this a bit confusing mostly for beginner users.Anonymoushttp://www.blogger.com/profile/14390316813577140633noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-13717860186679339812013-09-26T11:44:00.001+02:002013-10-22T15:20:47.712+02:00A first look to the new Pentaho BA Server 5.0 CEIt's a lot of time I'm playing with the new Pentaho BA Server 5.0 to see the what's next and now, I think, it is the right time to start talking about it. The first thing that appears is that Pentaho made a big refactoring for this release. A lot of changes from a brand new GUI to a completely new and clean source tree with less and more organized projects than the previous release.<br />
<br />
<b>What's changed from a developer point of view</b><br />
<br />
The first big set of changes, visible only to developers like me interested in looking into the code for getting knowledge or customize something, is summarized below:<br />
<br />
<ol>
<li>Project moved to <a href="http://github.com/" target="_blank">github</a> at <a href="https://github.com/pentaho/pentaho-platform" target="_blank">this url</a>. This is a good choice to improve collaboration between people and collaboration is one of the key points of the OSS philosophy. Almost any project around Pentaho is housed on this platform (Kettle not yet but it seems the only one).</li>
<li>The project organization in the source code radically changed trying to simplify the structure of the codebase (and in my opinion they get it). You can find a description about the the new project structure and role of any of the sub-projects <a href="http://wiki.pentaho.com/display/ServerDoc2x/02.+Exploring+the+Pentaho+Repository+in+5.0" target="_blank">in this article</a> in the Pentaho wiki. </li>
<li>Then, as a Java architect, another interesting change. It seems they moved to <a href="http://www.jetbrains.com/idea/" target="_blank">IntelliJ</a> as development platform of choice because of the presence of the IntelliJ metadata files in the source tree. I'm a user of IntelliJ (after having use Netbeans and Eclipse) so this is a switch that I liked a lot. So <a href="http://wiki.pentaho.com/display/ServerDoc2x/IntelliJ+Checkout+and+Build+Process" target="_blank">here at this page</a> a detailed description about to how pull the platform project directly into IntelliJ to start working on it</li>
</ol>
<div>
<b>Get the platform and start playing with it</b><br />
<br />
What's the best way to have the new release in your hands to start trying it out? Two ways: a) get it from the <a href="http://ci.pentaho.com/job/BISERVER-CE/" target="_blank">pentaho ci build system</a> or b) build it by yourself. As a developer, the second choice is the funniest and the more interesting if you, like me, want to understand how the things changes from time to time and want to be sure that everything goes correctly. Moreover the ability to prepare your own system package gives you a high degree of personalization; to do this you can find useful using <a href="http://www.webdetails.pt/ctools/cbf.html" target="_blank">CBF</a> from <a href="http://www.webdetails.pt/">Webdetails</a>.<br />
<br />
In this case, if you want to build it by yourself firstly you've to pull the project locally on your computer from the pentaho-platform repository on GitHub. Then the quick and dirty way to build Pentaho BA Server is to use the development <a href="http://ant.apache.org/" target="_blank">ant</a> build buildfile (dev-build.xml) given in the source tree and make a new complete development build. If intersted, you can get all of the possible targets for this ant command file by giving the following command<i> ant -f dev_build.xml</i> help<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhM8v60ZIwqxX6EHq-hFXbA1JsJ_OTaxKrbqA7atVq1MnTfQvBTYMvaZ4825K7-7I1-Q8_Ch49oER8_CBJYsOUNhakxOy1IBWX-8vb9KRWyAHYEbLlK6oa0WDGI-yU_KB7RqmRcv7M-prt5/s1600/ba50_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhM8v60ZIwqxX6EHq-hFXbA1JsJ_OTaxKrbqA7atVq1MnTfQvBTYMvaZ4825K7-7I1-Q8_Ch49oER8_CBJYsOUNhakxOy1IBWX-8vb9KRWyAHYEbLlK6oa0WDGI-yU_KB7RqmRcv7M-prt5/s400/ba50_1.png" width="400" /></a></div>
<br />
<br />
but I decided to follow the easy way by using the easiest command available to reach my goal. To do this go to the platform sources' root directory (we indicate that directory as <i><pentaho-platform-root></i>) and give the following command
<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> ant -f dev_build.xml dev-rebuild -Dsolution.dir.server=bin/stage/biserver-ce/pentaho-solutions -Dproject.revision=CE-SNAPSHOT package
</code></pre>
<br />
The system will remain busy building your new release; depending on your hardware it will take 10 to 15 minutes to complete and you will have it cooked rightly. The result is<br />
<br />
<ol>
<li>a packaged version of the Pentaho BA server in the <i><pentaho-platform-root>/assembly/dist</i></li>
<li>an unpackaged version of the same in the <i><pentaho-platform-root>/assembly/bin/stage/biserver-ce</i> directory that you can use immediately for development and testing purposes.</li>
</ol>
<br />
That said, you can use the unpackaged version to easily and quickly look at the new features of the BA server.<br />
<br />
<b>What's changed from a user point of view</b><br />
<div>
<b><br /></b></div>
<div>
As soon as the platform opens in the browser you can see the new look. The new login screen looks really clean and minimal. I really like it! Now, remember that our old and friendly user <i>joe</i> is dead; if you want to login as admin you need to log in as the user <i>Admin</i> with password.... ah yes of course <i>password</i> (too difficult to remember!). In any case you can always get help by clicking with the mouse on the <i>Login as Evaluator</i> link to get the demo users and passwords.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0xZBcnoSshGLP5q2CFpFjTDHGPoAjzpvUptGDPUIN8SuCsGNRU-rmagOrVe-9bE4zcLa1pS2pb8HsMxG6-_oWJ_cvw3G585bYxKJ35ZahefkEZrV2BXXBoUuI2GUXerleFUe_5FYTtUwg/s1600/ba50_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="212" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0xZBcnoSshGLP5q2CFpFjTDHGPoAjzpvUptGDPUIN8SuCsGNRU-rmagOrVe-9bE4zcLa1pS2pb8HsMxG6-_oWJ_cvw3G585bYxKJ35ZahefkEZrV2BXXBoUuI2GUXerleFUe_5FYTtUwg/s400/ba50_2.png" width="400" /></a></div>
<br />
<br />
As soon as you get logged in you discover a totally new user environment that I really like for its. The new environment now is based on a concept called Perspective.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcvlIIK3i6juEqYZDUleJtIMSIrq74HwXDbx_xI_NC-J9nAYlT7KGXJP-NuPn3lPr8HTxs89qptqFDsR9E-7BQr-lmZhyphenhypheneTg2gkrlCnyByFhPBaCCA-eBMlvIMrr0fAOCP7IiTfgWoo59y/s1600/ba50_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcvlIIK3i6juEqYZDUleJtIMSIrq74HwXDbx_xI_NC-J9nAYlT7KGXJP-NuPn3lPr8HTxs89qptqFDsR9E-7BQr-lmZhyphenhypheneTg2gkrlCnyByFhPBaCCA-eBMlvIMrr0fAOCP7IiTfgWoo59y/s400/ba50_3.png" width="400" /></a></div>
<br />
A Pentaho perspective is an area that refers to a specific user context and that collect all the objects and actions referred to that particular context. You can change your perspective by clicking on the cascade menu located on the upper left side of the screen immediately below the main menu<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioycJ3Q6jZFMOqEsEB1WYXE0h_ntWjaB-N3YBY7QeJQ8q68JUQhLYj9gdwHSzZ1SlJls7TWyXWPagH7ojYNbw6Iqx9jH7KqyXSNUE69PKSlHeE3lYkV_R6yhkzcsu1kYv8Z_vz3ic6kVv0/s1600/ba50_2A.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="32" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioycJ3Q6jZFMOqEsEB1WYXE0h_ntWjaB-N3YBY7QeJQ8q68JUQhLYj9gdwHSzZ1SlJls7TWyXWPagH7ojYNbw6Iqx9jH7KqyXSNUE69PKSlHeE3lYkV_R6yhkzcsu1kYv8Z_vz3ic6kVv0/s400/ba50_2A.png" width="400" /></a></div>
<br />
We have 4 perspective in our Pentaho environment:<br />
<br />
<ul>
<li><i>Home</i>: It's the Perspective you get into as soon as you log into Pentaho (see screenshot two pictures above). It contains portlets for <i>Recent files </i>and <i>Favorites files</i> to help you in getting easier to reach the more often used reports or visualizations. Then you have a big blue button called <i>Manage Datasources</i> that is the new way you need to use to define datasources. From this release, this is the only place you can go to deal with datasources: you will not have anymore this function in the administration part of the system as in the previous releases. By clicking on that button you open the<i> Manage Datasources </i>dialog box that let you define new datasources and either edit or delete existing ones.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi07duw72bgh3XteuhyphenhyphenYqc29qub0UaF0KlAVTmQf60sDZOA9sbgxGcDHvd4jHIlOnzdNyIyCs8suvON1o-zIGeccxBHl099jfdPUDwgwpTApJd-yO6lgWJYhdrGqL6kzyjOrrR4Why_S4xH/s1600/ba50_8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi07duw72bgh3XteuhyphenhyphenYqc29qub0UaF0KlAVTmQf60sDZOA9sbgxGcDHvd4jHIlOnzdNyIyCs8suvON1o-zIGeccxBHl099jfdPUDwgwpTApJd-yO6lgWJYhdrGqL6kzyjOrrR4Why_S4xH/s400/ba50_8.png" width="400" /></a></div>
<div>
<br /></div>
<ul>
<li><i>Browse files</i>: it gives you the ability to access the solution through the solution explorer. As you can see here another interesting new feature. Now we've a differentiation between a public and a private part of the solution. As soon as a user gets created a new Home directory is created. That directory and its content is visible only to that particular user. Only a user with the Admin role has access to all of the users' home directories. All of the homes directories are under a parent folder called <i>Home</i>. There's also a public part of the solution identified by a root folder called <i>Public</i>. That part is the part of the solution that is shared, depending on the share level decided by the administrator or by the content owner, by every user in the system. There's no more contextual menu referred to any item of the solution (either folders or files) as in the previous release but you can have the contextual actions using the menu on the right of the <i>File explorer</i>.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjs-x27WN9QVyPftPHrrrHG_xW1V06NCoevLkqFwpBu0VAGEMa_8rQejmyEAd0hiInYLY_0qNmjshrueMjq7r4SkE_Zk-MumkEHJelKvV1MMw2NNe-P6Bcy9NzWkDM-4ODGGB5sTA7TaVsB/s1600/ba50_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="212" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjs-x27WN9QVyPftPHrrrHG_xW1V06NCoevLkqFwpBu0VAGEMa_8rQejmyEAd0hiInYLY_0qNmjshrueMjq7r4SkE_Zk-MumkEHJelKvV1MMw2NNe-P6Bcy9NzWkDM-4ODGGB5sTA7TaVsB/s400/ba50_4.png" width="400" /></a></div>
<div>
<br /></div>
<ul>
<li><i>Open files</i>: As soon as you start an analysis view, a report, a visualization or anything else time the content you get is opened in this perspective. You always have multiple contents opened at the same time through a tabbed interface and you can easily switch back to the <i>Browse files </i>perspective or any other using the drop down menu we illustrated a while ago.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFyjBWaNpm9jkiILwkSJuabKKRxwIIgo7chSzj5FRYdoh8RYxgUDW8xNtJ22zYXDk-gEaImBCU0UwKuiZAuMx86a_EYSQMqxG2FK3gEuo1NVrnFi40c1wiT3aoOxcVDvS7FFi_U50yN8kw/s1600/ba50_5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFyjBWaNpm9jkiILwkSJuabKKRxwIIgo7chSzj5FRYdoh8RYxgUDW8xNtJ22zYXDk-gEaImBCU0UwKuiZAuMx86a_EYSQMqxG2FK3gEuo1NVrnFi40c1wiT3aoOxcVDvS7FFi_U50yN8kw/s400/ba50_5.png" width="400" /></a></div>
<div>
<br /></div>
<ul>
<li><i>Schedule</i>: The old <i>My Workspace</i> view is definitely dead. With this new release we have this perspective where the user can check the status of any the scheduled content and open the content for any terminated execution.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnW0lx5qXyohTWKDKw3LMO_CkX0_bVGwFi_2SbeFXCaphlnKNS8_OyY6TbJX0MeAJpE3SwI7H7Uts_sX8lJwljA_FbEuu1xNpcJfvjbLzbZ-O37gvLhaggvwvl6cBrvIlVdPNV8J6PIcXQ/s1600/ba50_6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="210" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnW0lx5qXyohTWKDKw3LMO_CkX0_bVGwFi_2SbeFXCaphlnKNS8_OyY6TbJX0MeAJpE3SwI7H7Uts_sX8lJwljA_FbEuu1xNpcJfvjbLzbZ-O37gvLhaggvwvl6cBrvIlVdPNV8J6PIcXQ/s400/ba50_6.png" width="400" /></a></div>
<div>
<br /></div>
<ul>
<li><i>Administration</i>: Another important change is about the administration of the Pentaho BA Server. The old administration console is definitely dead (I like this sooooo much!) and we've a new perspective accessible only to the <i>Admin role</i> and called <i>Administration</i> that let you do your administrative tasks. </li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgJ5cdUtYOb5y5ETP3xvHDPEOGevwJo2OCqMmyVEJoDxfV1uYBurS62EojuDdeBp6JoEICJpQkbTtNcrAP1NqeBnjKKWKSuO893YYvT4oYjmHH66dT2WffpeLHIUR5rqx6cyscz9qzBpgr/s1600/ba50_7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgJ5cdUtYOb5y5ETP3xvHDPEOGevwJo2OCqMmyVEJoDxfV1uYBurS62EojuDdeBp6JoEICJpQkbTtNcrAP1NqeBnjKKWKSuO893YYvT4oYjmHH66dT2WffpeLHIUR5rqx6cyscz9qzBpgr/s400/ba50_7.png" width="400" /></a></div>
<br />
<b><br class="Apple-interchange-newline" />Conclusion</b><br />
<div>
<br /></div>
<div>
So what's next? I think they made a big work reorganizing almost anything in a new and clean way. I really like it thanks a lot guys. I really appreciated they choose to implement a responsive design for this new GUI so that it could be easily used without any pain on any device but it isn't. So this is a good point in my opinion where they could invest some time and there's space to make things much better. In any case we must admit a good change versus a more intuitive and clean web interface so thank you Pentaho for this good job.</div>
<div>
<b><br /></b></div>
<div>
<b><br /></b></div>
<div>
<b><br /></b></div>
<br /></div>
Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com11tag:blogger.com,1999:blog-4547836498257352074.post-46360240939021377642013-07-16T21:42:00.000+02:002013-09-26T11:49:21.365+02:00My first book about Pentaho is out!Today is a great day for me and I'm very proud about this. It is the realization of a dream I had from a long time: have the possibility to write a technical book.<br />
<br />
In February 2013, a guy from <a href="http://www.packtpub.com/">Packt Publishing</a> wrote me an email saying that he read at my blog, he liked it and so he decided to ask me about writing a book about Pentaho Data Integration. <a href="http://www.packtpub.com/pentaho-data-integration-kitchen/book">The book</a> is about kitchen and how to use the PDI's command line tools efficiently to help people in their day to day operations with Pentaho Data Integration. It is a practical book and it seemed relatively easy to write. At first I was surprised and excited (a lot I must admit). It seemed me impossible that someone somewhere in the world noticed my blog and decided to trust me. I was really proud and honored about this so I decided to test me on this and I accepted.<br />
<br />
Now, after 4 months of work, <a href="http://www.packtpub.com/pentaho-data-integration-kitchen/book">that book is finally out</a> and you can order it!<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNw7eRaRhWr6nFd7ilcmHIiK_YzeIgx2imfRGnbZsP8zL9nKM7fJ5M97UcmVxI-Oa1mqe7OOLdw_-uZ2w1OA__cbzGSc_E4zesrP0eCSz3BApAi7A5jJ-RR5uUQO8CdQI3r9IJ-SdpOdu2/s1600/51WS11Tif5L._SS500_.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNw7eRaRhWr6nFd7ilcmHIiK_YzeIgx2imfRGnbZsP8zL9nKM7fJ5M97UcmVxI-Oa1mqe7OOLdw_-uZ2w1OA__cbzGSc_E4zesrP0eCSz3BApAi7A5jJ-RR5uUQO8CdQI3r9IJ-SdpOdu2/s320/51WS11Tif5L._SS500_.jpg" width="320" /></a></div>
<br />
<br />
I just wanted to thanks <a href="http://www.packtpub.com/">Packt Publishing</a> for this opportunity and especially all the people from Packt that helped me in this adventure by reviewing my work and giving me useful suggestions about to improve my work of novice writer the best I can. I also want to thanks my technical reviewer <a href="http://pt.linkedin.com/in/latinojoel">Joel Latino</a> for the precious help he gave in reviewing the book and suggesting things to improve it.<br />
<br />Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com13tag:blogger.com,1999:blog-4547836498257352074.post-7929137114633696242013-06-10T10:59:00.002+02:002013-06-10T10:59:25.040+02:00Print Pentaho Reports from PDI efficiently and without pain!These days I'm involved in a project where we need to print reports directly from our PDI processes. So what a good option to use the PRD output step to do that? The idea was to find out a way for not writing the JDBC connection informations in the definition of our Pentaho reports connections. To do this, we decided to go for report connections defined by using JNDI datasources.<br />
<br />
<b>Pentaho Reporting and JNDI database connections</b><br />
<br />
PRD, as any of the tools in the Pentaho family, supports the creation of a database connection using JNDI datasources. This is a cool feature! Creating Pentaho reports database connections using JNDI datasource is a good approach for the following reasons<br />
<br />
<ul>
<li>We are able to not write connection information in the report making the it more easily adaptable to the system environment changes.</li>
<li>We are able to use a server side common datasource, greatly simplify the deployment of our reports to the Pentaho BI server.</li>
<li>Usually a datasource is directly related to the concept of database connection pooling that helps us to efficiently use and streamline server side resources.</li>
</ul>
<br />
Tipically a JNDI datasource is created by an application server or a servlet engine using an appropriate set of connection parameters that differs depending on the particular database we are going to connect to. As soon as the server side middleware creates the datasource, it is flagged with a name decided at design time by the developer and under that name is made available to any application that requires to use it. It is always a good rule of thumb to use a datasource, as soon as we have one, to let our application connect to our database. Pentaho BI server has a section in the Pentaho Administration Console (PAC) to let you create all the datasource connections you need. Pentaho Reporting and any other tool of the Pentaho suite makes extensive use of the JNDI datasources defined under PAC to connect to all the SQL databases. But now, as developers using PRD (Pentaho Report Designer) to develop our reports, the problem is really simple: how can we develop our reports using Pentaho Reporting JDBC Connections based on JNDI JDBC datasources if we don't have any datasource created on our client (remember that the datasource lives on the server).<br />
<br />
<b>First approach about using JNDI datasources in PRD</b><br />
<br />
The first idea, not a good option unfortunately but the first we can find out, could be as follow:<br />
<br />
<ul>
<li>Develop and test the report by defining Pentaho Reporting JDBC Connections based on Native JDBC connections. For this particular Pentaho Reporting JDBC Connection type, the connection information are saved into the Pentaho Report metadata definition making it very difficult to be maintained and eventually changed.</li>
<li>As soon as the report is ready to be deployed to the Pentaho BI Server, edit the Pentaho Reporting JDBC connection definition and change it to use a JNDI JDBC datasource. Basically, this kind of JDBC connection requires that you type the name of a datasource that is made available on the server you are going to deploy. Using JNDI JDBC datasource has the major plus in having a single place were the connections information are kept: in the datasource definition on the application server or servlet engine configuration. So if something changes in our connection information the impact on our big set of reports is really low. That's a good thing.</li>
</ul>
<br />
This approach, at first, seems a good idea but what's wrong with this? For sure it's a bit elaborated but the worst thing is that you can forget to make the change suggested immediately before to deploy to your Pentaho BI Server. I forget for sure.... So what can we do to make the things the easiest the possible?<br />
<br />
<b>Use SimpleJNDI to emulate datasources on the client</b><br />
<br />
Pentaho Reporting, and the same is for any other Pentaho tool, integrates a very cool library called <a href="https://code.google.com/p/osjava/wiki/SimpleJNDI">SimpleJNDI</a>. This library, as stated in the library website <i>"is intended to solve two problems. The first is that of finding a
container independent way of opening a database connection, the second
is to find a good way of specifying application configurations"</i>. It is entirely library based, so no server instances are started, and it
sits upon Java .properties files, XML files or Windows-style .ini
files, so it is easy to use and simple to understand. The files may be
either on the file system or in the classpath.<br />
<br />
In case of PRD we have a file called default.properties typically located in the directory <i><user_home>/.pentaho/simple-jndi</i>. That file contains the datasources definitions. Any datasource is defined by using a set of by 5 entries and any entry is made up by a key-value pair where the key is defined with the following syntax<br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><i><datasource name>/<attribute></i></span><br />
<br />
That said let's have a look at the example below:<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SampleData/type=javax.sql.DataSource</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SampleData/driver=org.hsqldb.jdbcDriver</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SampleData/user=pentaho_user</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SampleData/password=password</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">SampleData/url=jdbc:hsqldb:mem:SampleData</span><br />
<br />
This example represents the definition of a datasource named <i>SampleData</i>; it has 5 attributes as detailed below:<br />
<br />
<ul>
<li><i>type</i>: it represents the type of JNDI resource we're going to define. Basically it's valued to the Java interface that represents a datasource.</li>
<li><i>driver</i>: it is the name of the database JDBC driver and changes depending on the db we're going to connect to</li>
<li><i>user</i>: the username used on the connection</li>
<li><i>password</i>: the user password used for the connection</li>
<li><i>url</i>: the jdbc connection url used to open the connection to the database. Again, this value depends on the database we're going to connect to.</li>
</ul>
<br />
As soon as the Reporting Engine is going to fill the report template with the data and so a database connection is require the library crease the datasource and the connection is shared with Pentaho Report designer that<br />
<div>
<br /></div>
<b>The way to go to develop Pentaho reports using JNDI datasources </b><br />
<br />
That said to properly define a Pentaho JDBC Connection that uses JDBC JNDI datasources, definitely, the way to go is to do it as detailed below<br />
<br />
<ol>
<li>Open the default.properties file located under <i><user_home>/.pentaho/simple-jndi</i>.</li>
<li>Copy and paste the definition of an already defined datasource to simplify the new datasource definition. </li>
<li>Change the name of the datasource in the copied rows to the name of the new datasource we're going ìto define. Remember to call the datasource with the same name of the datasource defined on your Pentaho BI server that the report will use once deployed.</li>
<li>Change the values of the attributes and update them with the connection parameters for the new datasource.</li>
<li>Save and close the file</li>
<li>Open the Pentaho Report Designer and start a new report. </li>
<li>Define a new Pentaho JDBC Datasource connection. Give it a proper name</li>
<li>Add a new JDBC connection in the Pentaho JDBC datasource configuration.</li>
<li>Give the connection a name, select the database connection type and JNDI as access type.</li>
<li>As soon as the JNDI access type is selected set the value of the JNDI name field to the name of the datasource you just configured in the default.properties file (as detailed from point 1 to 5 above).</li>
<li>Press the Test button to verify that the connection through the newly defined datasource works. </li>
<li>Press OK to close the database connection defininition dialog.</li>
</ol>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdA5-PQAZtRtbICnZoFauF5UVF6s2ad7FgpbpQ7Emqnigzb7lytS19ne7wEuaMZr8MTv92r98t4DUTbkXHWjVNrBR5Oe06HHiI45XyGqp31Pf5mO04kG1OwKp66sg8nIgsCad64Q1D_IK2/s1600/db_connection_dialog.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdA5-PQAZtRtbICnZoFauF5UVF6s2ad7FgpbpQ7Emqnigzb7lytS19ne7wEuaMZr8MTv92r98t4DUTbkXHWjVNrBR5Oe06HHiI45XyGqp31Pf5mO04kG1OwKp66sg8nIgsCad64Q1D_IK2/s320/db_connection_dialog.png" width="320" /></a></div>
<br />
At this point your JNDI connection is defined and uses a fake datasource that has the same name as the datasource on the server. So you're not required to remember to make any change before deploying the report because everything is already done. Cool!<br />
<br />
<b>Print Pentaho reports from a PDI transformation</b><br />
<br />
PDI (Pentaho Data Integration) has an output task called Pentaho Reporting Output that you can use to print a Pentaho Report from inside a transformation. Basically it prints your report by configuring the complete template filename, the path to the output directory, the report output format (PDF, Excel, HTML ecc.) and eventually the parameters.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi21rb9PrDXUXp052dOW0qicpas0FsRL9rywuWsibtX_zNxqi5YO1MLnf9pfumb0cSTT4rM_FT5yc-HcMBzF8OlFUX5mY211HseycCA5lunUSQMrk-Xkl-CHiAhWTsnqAiRQ9dXcsMYNQUw/s1600/prd_output_kettle.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi21rb9PrDXUXp052dOW0qicpas0FsRL9rywuWsibtX_zNxqi5YO1MLnf9pfumb0cSTT4rM_FT5yc-HcMBzF8OlFUX5mY211HseycCA5lunUSQMrk-Xkl-CHiAhWTsnqAiRQ9dXcsMYNQUw/s320/prd_output_kettle.png" width="287" /></a></div>
<br />
So that said seems very simple for a PDI developer to print a Pentaho Report and obviously it is. Take data from your sources, manipulate them in your transformation and start printing your report using this cool output step. But there are some things that anyone must know to let the things works properly in any case. Because PDI doesn't inject connection information in Pentaho Reporting, a good way to have Pentaho Reporting JDBC connections details not written into the report metadata is to use JNDI JDBC datasources to have all that stuff externally defined and easily maintainable. Again, the simple-jndi library comes to our attention to help us in dealing with this. <br />
<br />
<b>PDI doesn't inject into our report a database connection</b><br />
<br />
PDI doesn't inject a database connection into the report. So the report uses the connection information defined in Pentaho Reporting datasources connections. At this point, the better option we have to externalize the report database connection information is to use the JNDI capability of our Pentaho Reporting by using a locally defined datasource connection (as detailed above). In this case the <i>default.properties </i>file containing the Pentaho Reporting datasources connection information works as a new external configuration file of our PDI process ad you can distribute it with your process configuration files. As soon as your PDI transformation will start the print output, Pentaho Reporting uses simple-jndi library to create the JNDI JDBC datasource connection and will make that connection available to your report. When we talked about using simple-jndi to support the development process in Pentaho Report Designer of reports using JNDI datasources I said that PRD looks for the file in a specific location of your filesystem. My assumption at this point was that, as usual, even if we use this mechanism to print a report from inside PDI, the reporting subsystem knows where the default.properties file is located. But, unfortunately, I was wrong because Pentaho Reporting is unable to locate the correct location of our default.properties file so the JNDI datasources definitions made in our report are completely. Let analyze the standard use-case: Pentaho Report Designer (the tool) gets the location information for that file through a configuration read by the tool and when we deploy the report to the Pentaho BI server the JNDI name for the datasource is get from the server. But in our new use-case PDI that prints a Pentaho report that uses JNDI definitions, the things are totally different. So how can we deal with this to have our report printed from the PDI step without pain?<br />
<br />
<b>Report with JNDI JDBC datasource </b><b>connections </b><b>printed by PDI: what happens?</b><br />
<br />
As soon as the print is started by PDI the the Pentaho reporting subsystem the path to default.properties is resolved locally to the script that starts the PDI process (either spoon, or kitchen or pan). Let me try to explain. It your ETL process files are located in the /tmp/foo/etlprocess directory and your start your ETL process with kitchen locally to that directory with a relative path to the job file as in this example<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$ cd /tmp/foo/etlprocess</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ kitchen.sh -file:./my_print_job.kjb</span><br />
<br />
PDI looks for the for a simple-jndi directory that is in the directory /tmp/foo/etlprocess so it looks for it in /tmp/foo/etlprocess/simple-jndi. But what happens if you are starting kitchen from inside totally different directory, let me say for example /tmp/foo1 and you're going to start your job my_print_job.kjb that is locate in /tmp/foo/etlprocess<br />
<span style="font-family: Courier New, Courier, monospace;">$ cd /tmp/foo1</span><br />
<span style="font-family: Courier New, Courier, monospace;">$ kitchen.sh -file:/tmp/foo/etlprocess/my_print_job.kjb</span><br />
<br />
In this case, PDI looks for the for a simple-jndi directory that is in the directory /tmp/foo1 so it looks for it in /tmp/foo1/simple-jndi. Because you are unaware about how your final user will start your job this is completely a mess! But don't be afraid there's a solution. The best idea to solve this is:<br />
<br />
<ul>
<li>Have a configuration directory local to your ETL process file, that already contains other configuration items for your process and that you distribute from within your package.</li>
<li>Put your default.properties file inside that directory</li>
<li>Have a way to specify to PDI where that simple-jndi configuration file is located.</li>
</ul>
<br />
This elegantly solves your problem.<br />
<br />
<b>How to specify simple-jndi information to have your report fully working</b><br />
<b><br /></b>
There is a way to specify to the simple-jndi library all the information needed to elegantly solve our issue by specifying a set of environment variables. The source of information for this is in the documentation you find in the binary download that you can get from <a href="https://code.google.com/p/osjava/downloads/detail?name=simple-jndi-0.11.4.1.zip&can=2&q=">here</a>.<br />
<br />
Basically to fix our issue we need redefine the standard value of the PENTAHO_DI_JAVA_OPTIONS to this value:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">PENTAHO_DI_JAVA_OPTIONS="-Xmx512m -XX:MaxPermSize=256m -XX:-UseGCOverheadLimit -Djava.naming.factory.initial=org.osjava.sj.SimpleContextFactory -Dorg.osjava.sj.root=</span><span style="font-family: 'Courier New', Courier, monospace;">/tmp/foo/etlprocess/</span><span style="font-family: Courier New, Courier, monospace;">config -Dorg.osjava.sj.delimiter=/"</span><br />
<br />
As you can see we have the standard memory settings plus three new parameters:<br />
<br />
<ul>
<li><b style="font-family: 'Courier New', Courier, monospace;">-Djava.naming.factory.initial,</b><span style="font-family: inherit;"> this first parameter sets the complete name of the i</span>nitial Context Factory for SimpleContexts. This is an internal simple-jndi object</li>
<li><b style="font-family: 'Courier New', Courier, monospace;">-Dorg.osjava.sj.root,</b><span style="font-family: inherit;"> this parameter sets the complete path to the directory that will contain the default.properties file containing our datasources definitions. Starting from our previous example we're saying to simple.jndi to locate that file in /tmp/foo/etlprocess/config where config would be nic that will be our process configuration directory.</span></li>
<li><b style="font-family: 'Courier New', Courier, monospace;">-D<b>org.osjava.sj.delimiter</b>,</b><span style="font-family: inherit;"> this third parameter sets the </span>the delimiter used to separate
elements in a lookup value. This allows code to get closer to pretending
to be another JNDI implementation, such as DNS or LDAP. In our case we need to use the / character.</li>
</ul>
<br />
<div>
<br /></div>
<div>
Fill free to choose which mechanism to use to set these environment variables; you can either put the variable definition in the user profile file or you can make a script file and call the PDI scripts from there after having set these environment variables.</div>
<div>
<br /></div>
<div>
<b>That's all!</b></div>
<div>
<br /></div>
<div>
So for now that's all! You have all the information required to have your JNDI based reports fully working even when launched through PDI. This is a good way to fully separate connections information from the report and have them saved together to your ETL configuration files. This will give you a clean distribution for your customer and of course, the more easy and clean the things are organized the more happy they will be!</div>
<div>
<br /></div>
<div>
Stay tuned for the next post that will come later on. I've interesting tips to share with you about using a java profiler to support you in investigating memory usage problems in your ETL process so that you get quickly to the solution right solution. Have fun and see you later.</div>
<div>
<br /></div>
<br />Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com3tag:blogger.com,1999:blog-4547836498257352074.post-85660177547835638082013-04-22T07:24:00.002+02:002013-04-22T07:24:43.818+02:00Pretty print XML and JSON files with PDI (2/2)Today I'm back with the second part of my recipe about how two pretty print XML and JSON files with PDI. You can find the <a href="http://ramathoughts.blogspot.it/2013/04/pretty-printing-xml-and-json-files-with.html">first part here</a>.<br />
<br />
To pretty print whatever JSON stream a good starting point is to use the <a href="http://code.google.com/p/google-gson/">GSON library</a>. It is a nice component that lets you<br />
<br />
<ol>
<li>serialize JSON streams starting from a set of Java objects or </li>
<li>convert a JSON stream into an equivalent set of JAVA objects</li>
</ol>
<b>The setup</b><br />
<br />
To prepare PDI to run this example you must:<br />
<br />
<ol>
<li>Download the GSON library from the <a href="http://code.google.com/p/google-gson/downloads/list">following link</a>. In my case I've downloaded the version 2.2.3 but consider the same steps with other versions of the library.</li>
<li>Unzip the file on a temporary directory</li>
<li>Copy the gson-2.2.3.jar file to the <PDI_HOME>/libext directory</li>
<li>Restart PDI </li>
</ol>
<br />
<b>The how-to</b><br />
<br />
First of all I started by making an example to obtain an ugly JSON sample stream to format. To do this I built a new transformation by reusing the input files of the sample multilayer xml file transformation to obtain a simple JSON stream. The interesting part is at the very end of this transformation. Again you have a User Defined Java Class step that contains all the code that does the dirty job for you.<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: import com.google.gson.Gson;
2: import com.google.gson.GsonBuilder;
3: import com.google.gson.JsonParser;
4: import com.google.gson.JsonElement;
5: String jsonOutputField;
6: String jsonPPField;
7: public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException
8: {
9: // First, get a row from the default input hop
10: //
11: Object[] r = getRow();
12: // If the row object is null, we are done processing.
13: //
14: if (r == null) {
15: setOutputDone();
16: return false;
17: }
18: // Let's look up parameters only once for performance reason.
19: //
20: if (first) {
21: jsonOutputField = getParameter("JSONOUTPUT_FIELD");
22: jsonPPField = getParameter("JSONPP_FIELD");
23: first=false;
24: }
25: // It is always safest to call createOutputRow() to ensure that your output row's Object[] is large
26: // enough to handle any new fields you are creating in this step.
27: //
28: Object[] outputRow = createOutputRow(r, data.outputRowMeta.size());
29: logBasic("Input row size: " + r.length);
30: logBasic("Output row size: " + data.outputRowMeta.size());
31: String jsonOutput = get(Fields.In, jsonOutputField).getString(r);
32: Gson gson = new GsonBuilder().setPrettyPrinting().create();
33: JsonParser jp = new JsonParser();
34: JsonElement je = jp.parse(jsonOutput);
35: String jsonpp = gson.toJson(je);
36: // Set the value in the output field
37: //
38: get(Fields.Out, jsonPPField).setValue(outputRow, jsonpp);
39: // putRow will send the row on to the default output hop.
40: //
41: putRow(data.outputRowMeta, outputRow);
42: return true;
43: }
</code></pre>
<br />
This time the interesting code is between lines 32 and 35:<br />
<br />
<ol>
<li>The Gson object is created enabling the pretty printing.</li>
<li>The JSON stream that is coming in is read and parsed appropriately (lines 33-34)</li>
<li>A new JSON stream pretty printed is built and used to fill the jsonpp rowset field (line 35)</li>
</ol>
<br />
Next the output pretty printed JSON stream is saved to a .js file and that's all.<br />
<br />
You can download the sample transformation from <a href="http://download.serasoft.it/public/json_beautify.zip">this link</a>. I hope you enjoyed this two part article and that it can be useful for you.<br />
<br />Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-10180438796109309392013-04-19T22:43:00.000+02:002013-04-22T07:26:59.416+02:00Pretty print XML and JSON files with PDI (1/2)These days I've got into a stupid trouble with PDI. A customer asked me to build a process that produces an XML file as a result; the result I get was horrible from a presentation standpoint: the file wasn't rightly pretty printed and that makes it very difficult to be read. You will encounter this problem anytime you are going to obtain XML files as output, but the same thing happens also anytime you want a JSON output. So this time I decided to take care of this by trying to get into a quick and simple solution to beautify my output; this is what I'm going to talk about in this post.<br />
<br />
<b>Pretty Printing XML files How-To</b><br />
<br />
So what to do in case I need to pretty print XML files? Well that's very simple. PDI ships with <a href="http://dom4j.sourceforge.net/">dom4j</a>, a very well known library that let you easily work with XML, XPATH and XSLT. That library has an object in its API model, <i>OutputFormat</i>, which represents the format configuration used to format the XML output. There you find a method called <i>createPrettyPrint(); </i>this is all the needed to reach our goal very effectively. But now how can we apply all of this to the XML that we're going to produce in our transformation? Very easy to do.<br />
<br />
Let's start considering one of the samples shipped with PDI the multilayer XML files sample. You can find it in the samples/transformations directory in you PDI home directory. Take the original file and make a copy so that we can start working on it.<br />
<br />
Now, in between the Text Output step and the 5th XML Join step (named "<i>XML Join Step 5</i>") I added two new steps a Select Value step and a User Defined Java Step as you can see in the detail shown in the figure below<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH06BAhN0cxxKZWDc2jS1aEalQ78mqNy8UKiIGmvO1v6-mKfW6RM8JcUXgLW93W9jop5eli23qCA5Kntgjef3ehHbD4to-8ns-xrKz9htAVDYPWYIl3vkHRGE3P5sAxt-SAlFhFyxkPo-V/s1600/Screenshot+from+2013-04-19+19:04:36.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="209" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH06BAhN0cxxKZWDc2jS1aEalQ78mqNy8UKiIGmvO1v6-mKfW6RM8JcUXgLW93W9jop5eli23qCA5Kntgjef3ehHbD4to-8ns-xrKz9htAVDYPWYIl3vkHRGE3P5sAxt-SAlFhFyxkPo-V/s320/Screenshot+from+2013-04-19+19:04:36.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
While the Select values steps selects only the xmlOutput field that contains the raw xml that needs to be beautified, the User Defined Java class step is the place where the dirty job is made. To properly configure the User Defined Java Class step, that I renamed "Pretty Print Out XML", we need to<br />
<br />
<br />
<ol>
<li>Under the tab Fields below, define a new field named <i>xpp</i> whose type is String that will contain the produced pretty printed xml stream</li>
<li>Select the tab Parameters </li>
<li>Define a new parameter XMLOUTPUT_FIELD whose value is set to xmlOutput </li>
<li>Define a new parameter XMLPP_FIELD whose value is set to xmlpp</li>
<li>Copy and paste the code below in the related area in the task properties dialog</li>
</ol>
<div>
<br /></div>
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: import java.io.StringWriter;
2: import org.dom4j.io.OutputFormat;
3: import org.dom4j.Document;
4: import org.dom4j.DocumentHelper;
5: import org.dom4j.io.XMLWriter;
6: String xmlOutputField;
7: String xmlPPField;
8: public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException
9: {
10: // First, get a row from the default input hop
11: //
12: Object[] r = getRow();
13: // If the row object is null, we are done processing.
14: //
15: if (r == null) {
16: setOutputDone();
17: return false;
18: }
19: // Let's look up parameters only once for performance reason.
20: //
21: if (first) {
22: xmlOutputField = getParameter("XMLOUTPUT_FIELD");
23: xmlPPField = getParameter("XMLPP_FIELD");
24: first=false;
25: }
26: // It is always safest to call createOutputRow() to ensure that your output row's Object[] is large
27: // enough to handle any new fields you are creating in this step.
28: //
29: Object[] outputRow = createOutputRow(r, data.outputRowMeta.size());
30: logBasic("Input row size: " + r.length);
31: logBasic("Output row size: " + data.outputRowMeta.size());
32: String xmlOutput = get(Fields.In, xmlOutputField).getString(r);
33: StringWriter sw;
34: try {
35: OutputFormat format = OutputFormat.createPrettyPrint();
36: Document document = DocumentHelper.parseText(xmlOutput);
37: sw = new StringWriter();
38: XMLWriter writer = new XMLWriter(sw, format);
39: writer.write(document);
40: }
41: catch (Exception e) {
42: throw new RuntimeException("Error pretty printing xml:\n" + xmlOutputField, e);
43: }
44: String xmlpp = sw.toString();
45: // Set the value in the output field
46: //
47: get(Fields.Out, xmlPPField).setValue(outputRow, xmlpp);
48: // putRow will send the row on to the default output hop.
49: //
50: putRow(data.outputRowMeta, outputRow);
51: return true;
52: }
</code></pre>
<br />
The code we're interested in is between lines 32 and 44.<br />
<br />
<br />
<ol>
<li>The xml that is caming in is read from the xmlOutput row field and is put into the xmlOutput variable</li>
<li>We create an OutputFormat object with the pretty printing enabled by using OutputFormat.createPrettyPrint() fuction (see line 35).</li>
<li>Parse the XML document and then write it to a StringWriter (see lines 36 - 39)</li>
<li>Put the obtained pretty printed XML in the xmlpp variable and the into the new field created in the output row (see lines 44 - 47)</li>
</ol>
<div>
The resulting XML stream is then saved to disk by mean of the Text Output step.</div>
<div>
We made tests by pretty printing quite significant XML files in terms of size with good performances so I strongly suggest you to use this method. We're also planning to build a custom task to do this in a smoother way that will be released to SeraSoft github public repository soon. </div>
<div>
<br /></div>
<div>
You can download the following sample by <a href="http://download.serasoft.it/public/xml_beautify.zip">clicking here</a>. So for now we've done; next time I'll show you how to beautify a JSON file. Stay tuned!</div>
Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-44788121641224569992012-06-23T00:25:00.003+02:002013-06-05T19:23:23.066+02:00Saiku UI Enhancements and new Excel exportHi everybody. As promised we've some chestnuts that are ready to be eaten and here we are! We decided to start working on one of the major pieces of the <a href="http://community.pentaho.com/">Pentaho CE</a> suite: the newest OLAP client <a href="http://analytical-labs.com/">Saiku</a>. We have some enhancement in our pipeline to help the Trout, Paul and the other guys of the crew; this is the first of the list. We decided to start with two enhancements that makes all more beautiful to our customers eyes:<br />
<br />
1) Improved columns header management<br />
2) New and improved Excel export functionality<br />
<br />
<b>Improved columns header management</b><br />
<br />
A thing that I totally don't like in Saiku was the way it managed the columns headers any time we had repeated values on it. You can see a sample of this old behavior in the figure below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPFcLTEX4yz1A9I-AeWFbI7bP_YMQEhMDYF6PcG6Ia8sTia6WV8PKaK26l2HnzU6_JcPNUmrDiqmKu8ydj8izCDnmTvRdKwnDViMty0CuIkgwklvEONVYPrIHQdh-kvKRBTrO96qxPpRAC/s1600/saoku_headers_old.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPFcLTEX4yz1A9I-AeWFbI7bP_YMQEhMDYF6PcG6Ia8sTia6WV8PKaK26l2HnzU6_JcPNUmrDiqmKu8ydj8izCDnmTvRdKwnDViMty0CuIkgwklvEONVYPrIHQdh-kvKRBTrO96qxPpRAC/s400/saoku_headers_old.PNG" width="400" /></a></div>
<br />
<br />
Now the things are totally different. With a little UI improvement column headers with repeated values are grouped together as displayed in the picture below to have better visualization of your data.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3nig5aPdSuPoKeDkEcKsQrWH-zwaBkvPuXvKZ8QQykDgK7UW938JfmeSdwXY8fIMDKgLsjAX1LUWmwL4F0jl1xGqYDYpQathclDttNHFkbTDeuZtxTcTe0yVlgvKDuT3KT-EsmPlkEFqL/s1600/saoku_headers_new.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3nig5aPdSuPoKeDkEcKsQrWH-zwaBkvPuXvKZ8QQykDgK7UW938JfmeSdwXY8fIMDKgLsjAX1LUWmwL4F0jl1xGqYDYpQathclDttNHFkbTDeuZtxTcTe0yVlgvKDuT3KT-EsmPlkEFqL/s400/saoku_headers_new.PNG" width="400" /></a></div>
<br />
<b><br /></b>
<b>New Excel export</b><br />
<br />
Here we have completely rewritten the export module using <a href="http://poi.apache.org/">Apache POI</a> instead of <a href="http://jexcelapi.sourceforge.net/">JExcelAPI</a>. This gives us more power and more feature that could be useful in the future to enhance the functionality. The look and feel now is more elegant and exactly equal to what the user is seeing in the user interface. You can see a sample of the new output below<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZPtgV1zUR9OjKAFRLEVNsNW6VFoDv6F95-NpJ6FMZpK6n4kqYR1Paau9xf4HuKidsX0bHx2BlJnQ3kvgqnNf4ohR_S1tR0N8PHeSyuk_I17d8aeW4domrcLko15_Din40LkeWg2EamYWr/s1600/excel_new_export.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="252" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZPtgV1zUR9OjKAFRLEVNsNW6VFoDv6F95-NpJ6FMZpK6n4kqYR1Paau9xf4HuKidsX0bHx2BlJnQ3kvgqnNf4ohR_S1tR0N8PHeSyuk_I17d8aeW4domrcLko15_Din40LkeWg2EamYWr/s400/excel_new_export.PNG" width="400" /></a></div>
<br />
<br />
<br />
This is a first release beta release of these features so be careful if you decide to use it. Any feedback about how they work and what you would like to have next is fully appreciated.<br />
<br />
<b>Where you can find it</b><br />
<br />
All the code is committed to the <a href="https://github.com/serasoft">GitHub Serasoft</a> repository. You can find it here. We're planning the installation of an Hudson server on our servers in the cloud so that you can pickup anything already compiled so be patient. At the moment you can only grab the sources and compile it. Remember that our code is always aligned with all the new functionalities of the standard Saiku distribution. So what else? Stay tuned for the next things that are going to be released...Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-77831663764800542502012-06-22T11:41:00.001+02:002012-06-22T12:11:29.704+02:00I'm back after a long time missing....Hi everybody and sorry for not writing anything for so much time. Write a post is a nice thing and I love it but it takes time and during this last period I haven't had any time to write anything here or to cooperate tightly with my favorite community: the <a href="http://community.pentaho.com/">Pentaho community</a>. During that time I was taken starting my own little company here in Italy (initially I spent 7 years as a freelance consultant) called <a href="http://www.serasoft.it/">SeraSoft</a>. I decided to do that because I though it was the moment to change something in my professional life. This is a new job for me and it is really hard: manage a new business in a so competitive environment as for the Information Technology is
much harder than developing and designing good software. We have a little office in <a href="http://www.boffaloranet.it/">Boffalora Sopra Ticino</a>, a little town located 20 km far from Milan near to the river <a href="http://it.wikipedia.org/wiki/Ticino_%28fiume%29">Ticino</a>. It is a good place to stay away from the traffic of the city but at the same time we're near and it takes us really a few to be in the city. <br />
<br />
<iframe width="425" height="350" frameborder="0" scrolling="no" marginheight="0" marginwidth="0" src="https://maps.google.it/maps?f=q&source=s_q&hl=it&geocode=&q=SeraSoft+S.r.l.,+Via+Galileo+Galilei,+14,+Boffalora+Sopra+Ticino+Milano&aq=0&oq=Serasoft&sll=41.442726,12.392578&sspn=9.631836,26.784668&ie=UTF8&hq=serasoft+srl&hnear=Via+Galileo+Galilei,+14,+20010+Boffalora+Sopra+Ticino+Milano,+Lombardia&t=m&z=16&iwloc=A&output=embed"></iframe><br /><small><a href="https://maps.google.it/maps?f=q&source=embed&hl=it&geocode=&q=SeraSoft+S.r.l.,+Via+Galileo+Galilei,+14,+Boffalora+Sopra+Ticino+Milano&aq=0&oq=Serasoft&sll=41.442726,12.392578&sspn=9.631836,26.784668&ie=UTF8&hq=serasoft+srl&hnear=Via+Galileo+Galilei,+14,+20010+Boffalora+Sopra+Ticino+Milano,+Lombardia&t=m&z=16&iwloc=A" style="color:#0000FF;text-align:left">Visualizzazione ingrandita della mappa</a></small>
<br />
<br />
During these last months we made a lot of interesting things on real life projects with Pentaho and also other interesting Open Source technologies like <a href="http://www.liferay.com/community/welcome/dashboard">Liferay</a>, <a href="http://www.sugarforge.org/content/open-source/">SugarCRM</a> and <a href="http://www.openbravo.com/">Openbravo</a> working with companies of any size in sectors like Banking, Telecommunication, Services, Industry and Automotive. Lastly I decided that it was the time to came back heavily working in the community and I re-started doing something I hope interesting for anyone giving my two cents to help the Open Source ecosystem to grow. I'm trying to take this idea of cooperating also to my colleagues so we hope to be able to quickly give our contribution as a company.<br />
<br />
That said I thought it's time to came back and start sharing with you all the experiences of myself and my team about Pentaho and the other products we're using and I hope they could be of interest for you all. So stay tuned and for the next news from this channel and also follow us on <a href="https://github.com/serasoft">GitHub</a> to look directly at what we're doing. So see you soon! There are some chestnuts that are cooking on the fire and that are almost ready to be eaten.<br />
<br />
<br />Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com1tag:blogger.com,1999:blog-4547836498257352074.post-20691509774398506712010-11-21T16:43:00.023+01:002010-11-22T08:55:43.469+01:00Adjust detail rows height in WAQR templates dynamicallyToday I'm back writing something about WAQR. Yesterday one of my customers wrote me about a problem they were having while executing WAQR reports that has a long text in their columns. If you had columns with a very long text in it and you're exporting the WAQR report in PDF the columns text gets truncated. Stupid problem but apparently not so trivial to be solved within WAQR templates. <br />
<br />
<b>About WAQR</b><br />
<br />
WAQR (Web AdHoc Query Reporting) is an interesting module in the Pentaho suite. It sits on top of the report engine and the metadata layer and lets the users easily build tabular reports to be used for their daily activities or to just to export some complex data in an easy way from the Pentaho system. The report definition is based on a wizard that takes the users along these easy steps:<br />
<br />
<ol><li>select a template, from the set of available templates, and a business model. </li>
<li>decide were to put which fields in the report layout</li>
<li>adjust some visualization attributes or/and can apply filter conditions and define sort fields and orders</li>
<li>manage some page layout attributes</li>
<li>.... and here we go! We get the report.</li>
</ol><br />
WAQR templates are basically the old JFreereport report designer's template files so they are simple xml files we can view and modify. THEY ARE NOT compatible with the newer report designer template files<span class="Apple-style-span" style="font-family: Times,'Times New Roman',serif;">. </span>The wizard uses text manipulation routines to create a report-definition out of the template. We all know that sooner or later WAQR will be replaced by something more interactive and more attractive that will use the latest report engine's version. But for the moment we have this and with this we have to battle.<br />
<br />
<b>How to expand the height of the detail rows dynamically when export type is PDF</b> <br />
<br />
Try to build a new report with WAQR putting in the report definition a field with a very log text in it. If you try to export the report using the PDF format you will get the text truncated. To fix this it's only a matter of minutes and you need to modify the report template<br />
<br />
<ul><li>adding a new configuration attribute for the report engine and </li>
<li>add a new attribute to the details band to set the row height dynamic. </li>
</ul><br />
Below I'm going to summarize all these steps modifying the Basic template given with the Pentaho demo solution.<br />
<ol><li>Go to <i><biserver_home>/pentaho-solutions/system/waqr/templates/Basic</i> and open <i>jfreereport-template.xml</i></li>
<li>Locate the configuration xml element near the end of the file. Add the following line as a child of the configuration element<br />
<pre style="background: none repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <property name="org.pentaho.reporting.engine.classic.core.modules.output.pageable.pdf.AssumeOverflowY">true</property>
</code></pre></li>
<li>Add the attribute <i>dynamic = "true"</i> to the items element </li>
<li>Save the template and if the BI Server is running refresh the cache using the<i> Tools -> Refresh -> Repository Cache</i> menu entry</li>
</ol>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-37900859507555954572010-08-26T01:05:00.002+02:002010-08-26T01:14:30.532+02:00Processing group of files executing a job once per fileHow many of us ever had the need to process a set of files through Kettle executing jobs or transactions on a per file base. Let me make an example to illustrate a particular use case.<br />
<br />
Suppose we've two groups of files that we call <i>filegroup1 </i>and <i>filegroup2 </i>and suppose we have 2 groups of transformations that we call <i>transf_group1 </i>and <i>transf_group2</i>. The requirement is: we want to execute the transformations in <i>transf_group1 </i>once for each file in <i>filegroup1 </i>and as soon as the processing of this group finishes as a whole we want to start the execution of the transformations in <i>transf_group2 </i>once for each file in <i>filegroup2. </i>Let me analyse how we can do that.<br />
<i><br />
</i><br />
<b>A little about some main Kettle topics</b><br />
<br />
Kettle processes informations that flows through a path made by <i>steps</i>. The user takes the needed <i>steps </i>from a palette, drags them into the client area and builds a workflow. That flow is made up by different types of <i>steps:</i> we've input/output steps, transformation steps, script steps and so on. Any <i>step </i>has an input and an one or more outputs. It gets the information flow in input from the immediate preceding step, processes it and outputs, as a result, a new set of informations that will flow into the immediate next step. The output flow produced by the step can have a layout of fields, in terms of number and data types, that may differ from the flow in input. A set of steps chained together to built a specific task is called a <i>transformation</i>. So <b>transformations = elementary tasks</b>, sort of little reusable components that makes actions. A process is built coordinating a set of orchestrated tasks that can be executed in sequence or in parallel. This role of orchestrator in Kettle is filled by the <i>job</i>. The <i>job</i> orchestrates the execution of a set of <i>transformations </i>to build our complete ETL process. A job is made by a set of steps too but their intended scope is to help in orcherstrating the executions of the tasks (transformations) in our process. As you can see, we have a <i>job steps palette</i> but it contains only steps to check conditions or prepare the execution environment. The real work is made by steps contained in the transformations.<br />
<br />
In an our ETL processes made with kettle we always have a main job, also called root job, that we start to orchestrate the execution of nested jobs or transactions. We can nest as many levels of josb and transformations we want below that main job.<br />
<br />
<b>How Kettle starts nested Jobs or Transactions</b><br />
<br />
The first way of starting jobs or transformations in Kettle is the stupid way. Chain them together using the <i>Start transaction</i> or <i>Start job</i> steps and, when the nested transaction or job steps will be reached in the owner job flow they will be started in sequence or in parallel, it depends on how they are connected. But sometimes we would like to execute a transformation or a jobs once for each line in the input flow. To do that is really simple. Go to the <i>step configuration </i>dialog , select the <i>Advanced</i> tab and check <i>Execute for every single row</i>. We see an example of that below in the <i>Start transaction</i> configuration dialog. You'll find the same setting in the <i>Start job</i> configuration dialog.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhyphenhyphenz6oRJliE-b60v9ZIHoRmk4Ud24x5uRDzhYXzJMBmLpzaEPA1Fg3V9TmX6ezXVdqcGYEIztXjHSeWD5insOAmqGcEbjkFqXau2f5nYuffYGxeJsvDxtU0ldGUr2oy4_7QoZd-PxotCFp/s1600/transf_config1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="184" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhyphenhyphenz6oRJliE-b60v9ZIHoRmk4Ud24x5uRDzhYXzJMBmLpzaEPA1Fg3V9TmX6ezXVdqcGYEIztXjHSeWD5insOAmqGcEbjkFqXau2f5nYuffYGxeJsvDxtU0ldGUr2oy4_7QoZd-PxotCFp/s320/transf_config1.png" width="320" /></a></div><br />
<b>The job step <i>Add filenames to result</i> and why it isn't good for us</b><br />
<br />
So far so good. Well, go back to our requirements now. Because we said that we have 2 groups of transformations, <i>transf_group1 </i>and <i>transf_group2</i>, it is clear that we will have two jobs one that chains all the transformations of <i>transf_group1 </i>and the second all the transformations for <i>transf_group2. </i>We call them respectively <i>jobs1 </i>and <i>jobs2</i>. So we will have:<br />
<br />
a) A root job chains together 2 jobs <i>job1</i> and <i>job2.</i><br />
b) Each job chains all the transformations of the respective group.<br />
c) Because the two job encloses the group of transformations we are sure that the second group of transformations will be executed after the first group, as a whole, will be executed.<br />
<br />
Looking at what explained above regarding the way to start a transformation in a job, to start the two jobs once per file we need step that reads the list of files from a specified directory, fills the result with the set of complete filenames so that it can be used to start our job once for file in the result. Because we talked about two different filegroups we need two of steps like this chained before the respective job. We look into the job steps palette and we found a step that could be fine for us the <i>Add filenames to result</i>. The picture below depict a possible flow for our root job.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyeqTZlRCBN0wxgKMI7uyBF_hkGN5J_WOlyHhUW43UZp1s-Z0Pk137vGw5bd_EcgM2IVkMruRapvcnwn9ifkWfSkZC1_hNO_xkg9DH2Np_Uxu3BDIvErmER6FL88c-1Qh8c1sqkNcAYh-q/s1600/transf2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="85" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyeqTZlRCBN0wxgKMI7uyBF_hkGN5J_WOlyHhUW43UZp1s-Z0Pk137vGw5bd_EcgM2IVkMruRapvcnwn9ifkWfSkZC1_hNO_xkg9DH2Np_Uxu3BDIvErmER6FL88c-1Qh8c1sqkNcAYh-q/s400/transf2.png" width="400" /></a></div>We're starting to smile but unfortunately this solutions is not applicable because it doesn't work. To understand the why we need to understand the difference between <i>row result</i> and <i>file result</i> for Kettle. Typically a file result is a set of filenames that can be used only by steps that are able to manage attachments. The Mail step is the one step that can manage such a result. Row results instead are made by real data typically as output of a transaction. If you look at the Kettle internals you can notice that a job step manages these two datasets as two completely separated collections. The important thing to note here is that whenever you check <i>Execute for every single row </i>in our job/transformation configuration your're saying that you'll start your job/transformation for each row of your row result. So way our solution isn't good for us? Because our <i>Add filenames to result</i> steps fill a file result so our jobs will never starts.<br />
<br />
<b>So what to do??</b><br />
<br />
The solution is make a transformation whose only goal is to get the file list and use that list to populate a result list as shown in the picture below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY18cXv-VgIoHGkzSl0dv2tbZ8YDDuQXoaARxjIrZdlRc87w86RA2k7zyU-jni0IjTvu5nt8DgNPWBX6_yVdV60tHBdIoXkqaNqFGnK9Ac04NubgXLoq7-wLsEPqzSR8Jurieg7btflHWW/s1600/transf3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="90" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY18cXv-VgIoHGkzSl0dv2tbZ8YDDuQXoaARxjIrZdlRc87w86RA2k7zyU-jni0IjTvu5nt8DgNPWBX6_yVdV60tHBdIoXkqaNqFGnK9Ac04NubgXLoq7-wLsEPqzSR8Jurieg7btflHWW/s320/transf3.png" width="320" /></a></div>You need to call that transformation through a Transformation step in our root job chaining it before job1 and job2 to get respectively <i>filegroup1</i> and <i>filegroup2</i>. Here it is the complete layout of our definitive root job<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhegeiNNxY2-xViOFzZq8ImsVld4P-4lcAwRCAy8mwy5MqE5GklnyCmmP4GZtQ7T4KxDgAi_84MgNcOvTpeRBD__zQI2kJQpY0T15l2rDUKnadHc5_m2mmnzeJYm7YqD_ufrdZCUedzpg_I/s1600/transf4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="45" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhegeiNNxY2-xViOFzZq8ImsVld4P-4lcAwRCAy8mwy5MqE5GklnyCmmP4GZtQ7T4KxDgAi_84MgNcOvTpeRBD__zQI2kJQpY0T15l2rDUKnadHc5_m2mmnzeJYm7YqD_ufrdZCUedzpg_I/s400/transf4.png" width="400" /></a></div><br />
You can see the two transformations that gets the file lists before the two jobs. Using this approach the result file list that comes from the transformation fills the <i>row result</i> and the job can be executed once per file that is present in our directory. Remember check the magic flag <i>Execute for every single row </i>in the<i> Start job step </i>configuration as detailed above to correctly activate the jobs once per file as detailed in the paragraph above.<br />
<br />
<b>How to execute the provided sample</b><br />
<br />
To execute my <a href="http://download.serasoft.it/public/filegroup_sample.zip">sample</a> unzip the file in whatever directory. Edit the <i>get file list transformations</i> and change the <i>Get file names</i> step configuration according to a directory and files pattern that exists on your pc. Now, if you start the root job, you can go through the log and clearly see the messages that indicates the job is behaving as expected.<br />
<br />
<a href="http://download.serasoft.it/public/filegroup_sample.zip">Download from here the sample</a>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com2tag:blogger.com,1999:blog-4547836498257352074.post-68626437114043784072010-08-25T17:52:00.002+02:002010-08-25T17:54:46.948+02:00Dealing with Kettle DBCacheI came from 4 weeks of summer holidays... First time in my life but it was beautiful to spend a lot of time with my family. I need to take some time for them more often.<br />
<br />
Anyway, today I was doing my usual work with <a href="http://wiki.pentaho.com/display/EAI/Latest+Pentaho+Data+Integration+(aka+Kettle)+Documentation">Kettle</a> but something strange happened that makes me crazy. I added a new field to my table and then I came to Kettle to update the fields layout in my <a href="http://wiki.pentaho.com/display/EAI/Database+lookup">Database lookup step</a>. When I tried to find out my new field from the fields list I remained surprised... No new field appeared in the fields list for my table. I cried because I thought that it magically disappeared by my table but after a rapid check with my database client I saw my field was there in my table and I was happy. So what happened?<br />
<br />
<b>Cache database metadata with DBCache<br />
</b><br />
<i>DBCache </i>is a cache where recurring query results (about database metadata informations for example) are saved the first time you access them. That informations are persisted in a file called <i>db.cache</i> located in the <i>.kettle</i> directory below your home directory. Informations are saved in row format so you have a look inside the file or edit its content.<br />
<br />
Every time, for example, you go through Kettle database explorer looking for a table layout, the first time you access table metadata that results are cached through <i>DBCache </i>so that second time you you go through the cache saving an access to the db. But if you forget that and you update your table DDL in any way (like me after a veeeery long period of holidays) you could be surprised seeing that your updates seems not to be caught by Kettle.<br />
<br />
How can we clear the cache and have our table metadata updated next time I need to access them? You can go through the <i>Tool </i>menu and choose <i>Database > Clear</i> cache and your fresh set of table metadata will be get from the database.Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-33933641601971549112010-08-14T00:25:00.005+02:002010-08-25T17:53:50.793+02:00Blogging from my summer holidaysToday I was reading through my twitter client the latest news and my attention was catched by all the tweets around the latest <a href="http://forums.pentaho.com/showthread.php?77828-Hey-Community-how-s-it-going">Doug post</a> on the Pentaho forum. So I immediately connected through my phone browser to read that post and think about it.<br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;">Doug's question was asking how the Pentaho community is going. Following what my Pentaho friends said in their answers to this thread I also agree that comparing downloads isn't a good metric to judge how a community is going. The spirit of a community is made by all the people that every day gives a precious help in coding, helps people understanding the product or coming out from problems (that they think they are hard but they aren't) and talks about the product.</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"> </span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;">I personally come from another experience with another important opensource community (not a BI product). From the outside, I remember, everything seemed wonderful. The product stayed for a very long time on top as the best project in one of the biggest open source forges. But from the inside everything was totally different and the approach was really cold.</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"> </span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;">Here may experience is totally different, I'm breathing a completely different air. I found real people that cooperates everyday helping each other to solve their everyday problems with the product or other related technologies. They support people through irc, the forums and the wiki. I'm a software architect so I decided to help mainly with code contributions, but also in the forums and in writing articles about some pentaho topics in my blog .</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;">I studied and I'm continuously studying the code and I'm working with wonderful guys: <a href="http://pedroalves-bi.blogspot.com/">Pedro</a>, <a href="http://pentahomusings.blogspot.com/">Tom</a>, P</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;">aul and many other.</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;">In our Pentaho community, the irc channel is the something I really enjoy but unfortunately I can participate only a few times because very often the various proxies broke my ability to connect. It's a sort of noisy room where anyone expose problems and quickly gets solutions. You always find someone available to support you. But it is also a place to talk with friends about everything. A sort of meeting place. That is wonderful.</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"> </span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;">I said friends not colleagues and this is a very important distinction. These are the things that makes me thinking that IT IS really a community that works and not the number of downloads or any other stupid indicator.</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"> </span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></span><span class="Apple-style-span" style="color: #333333;"><span class="Apple-style-span" style="font-family: inherit;"></span></span><span class="Apple-style-span" style="color: #333333; font-family: Verdana, Tahoma, Arial, Calibri, Geneva, sans-serif; font-size: 13px;"><br />
</span><span class="Apple-style-span" style="color: #333333; font-family: Verdana, Tahoma, Arial, Calibri, Geneva, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-62253896924690164582010-07-21T17:56:00.006+02:002010-07-21T18:01:33.001+02:00Setup Kettle debugging in 2 minutesSometimes it could be very interesting to be able to debug something to understand better how it works from an internal standpoint. My experience says that this is the case with the majority the opensource projects: sooner or later this is will happen.<br />
<br />
Today that happened with Kettle trying to better understand if my assumptions were correct.<br />
<br />
So how can we easily debug Kettle? The answer is very easy is: use remote debugging. I'll explain everything in a minute. The development tool I'm using is <a href="http://www.jetbrains.com/index.html">IntelliJ IDEA</a> but it is fairly simple to set up everything with <a href="http://www.eclipse.org/">Eclipse </a>and <a href="http://netbeans.org/">Netbeans</a>.<br />
<br />
<b>Setup Kettle to enable remote debugging</b><br />
<br />
1) If you're on <i>Windows</i>, open <i>Spoon.bat</i> and add the following line<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> set JAVA_OPTS=-Xdebug -Xrunjdwp:transport=dt_socket,address=8000,server=y,suspend=n
</code></pre><br />
The same on <i>Linux </i>will be to open <i>Spoon.sh</i> and add the following line<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> export JAVA_OPTS="-Xdebug -Xrunjdwp:transport=dt_socket,address=8000,server=y,suspend=n"
</code></pre><br />
I've used the port 8000 as the socket port number at which the debugger attaches but feel free to use the one you prefer.<br />
<br />
2) Go to the last line of <i>Spoon .bat</i> or <i>Spoon.sh</i> and update that last line as follow<br />
<br />
<i>Linux (Spoon.sh)</i>:<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> $JAVA_BIN $JAVA_OPTS $OPT $STARTUP -lib $LIBPATH "${1+$@}"
</code></pre><br />
<i>Windows (Spoon.bat)</i>:<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> start javaw %JAVA_OPTS% %OPT% -jar launcher\launcher.jar -lib %LIBSPATH% %_cmdline%
</code></pre><br />
<b>Configure IntelliJ IDE for remote debugging</b><br />
<br />
1) Configure a project for Kettle source tree<br />
2) Choose <i>Build > Edit</i> configurations from the menu<br />
3) The <i>Run/Debug Configurations </i>dialog opens. Choose <i>Add New Configuration</i> (the + sign in the upper left corner) and select <i>Remote </i>from the <i>Add New Configuration</i> list<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqgy8HIckp2ALIa5_v0qqCz-bDOzriehBBeAPRMJT_8opyTQux_SZDfp2xKvebaDkz6gVHP3Xl29ungFfzgxFi31oAs2A0BZKnWomCdar5Vc57M6JfyLoyTborv90ALxTqjP0OmTNILJyV/s1600/debug_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="406" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqgy8HIckp2ALIa5_v0qqCz-bDOzriehBBeAPRMJT_8opyTQux_SZDfp2xKvebaDkz6gVHP3Xl29ungFfzgxFi31oAs2A0BZKnWomCdar5Vc57M6JfyLoyTborv90ALxTqjP0OmTNILJyV/s640/debug_1.png" width="640" /></a></div><br />
3) The <i>Remote Configuration</i> dialog opens<br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiffVqrg0uoHXICpPh1H43HEHptCtXPxil6NljLcWtwBlEDL33GCL2w3bJO4R8GhOwRpGlSKBxl1rssM5ohBiZh4r9Fb2elUDZy7hYC6z1mmYvEvE_kcnjFjVQqgwhyphenhyphenSqHlmgnT-YoMU_-t/s1600/debug_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="406" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiffVqrg0uoHXICpPh1H43HEHptCtXPxil6NljLcWtwBlEDL33GCL2w3bJO4R8GhOwRpGlSKBxl1rssM5ohBiZh4r9Fb2elUDZy7hYC6z1mmYvEvE_kcnjFjVQqgwhyphenhyphenSqHlmgnT-YoMU_-t/s640/debug_2.png" width="640" /></a></div><br />
Configure <i>Transport</i> and <i>Debugger mode</i> choices as displayed in the image above. The set the <i>Host address</i> (localhost in this case or wherever you need) and the <i>port </i>(8000 in my case) and press Ok<br />
<br />
<b>And now let's dance...</b><br />
<br />
So now start Kettle. As soon as Kettle started load you job or transformation and, before running it, launch the debugger from your IDE. As soon as the debugger started you're ready to launch your job/transformation and begin your investigation. The advantage of this approach is the ability to debug also remotely running kettle instances and that is useful sometimes in helping to investigate problems.<br />
<br />
Have fun playing with your Kettle and see you the next article.Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-78523701614803348692010-07-19T17:16:00.007+02:002010-07-21T17:01:02.277+02:00CDF internationalization early access release<div class="separator" style="clear: both; text-align: center;"></div><br />
Today I finally completed the first implementation of the CDF Internationalization support. It is my first big effort to help in CDF and I'm proud I had the opportunity to work on a so important missing feature.<br />
This is a first release, a sort of early access release, and it is available after building the framework sources from the repository trunk. I hope anyone can share ideas or suggestions on this feature helping making it better.<br />
<br />
<b>How does it works</b><br />
CDF internationalization uses a jQuery i18n plugin to handle internationalized messages. The plugin support has been wrapped in the CDF framework so that the user never had to bother about jQuery specifics but will use the support CDF gives for that. Everything rounds about resource message files as usual in the java platform.<br />
<br />
To create resources files we need to create a file for each locale our dashboard is going to support. These files are named <i><name>.properties</i>, or <i><name>_<language>.properties</i> or<i> <name>_<language>_<country>.properties</i>. For instance a resource bundle for UK will be <i>MessagesBundle_en_GB.properties</i>. In case the message file will not be present the framework support will display the default message string and you'll get a warning in the bi server message console (nothing to be worried about but it would be better to have the required missing resource file).<br />
The <i><language></i> argument is a valid ISO Language Code. These codes are the lower-case, two-letter codes as defined by ISO-639. You can find a full list of these codes at a number of sites, such as: <a href="http://www.loc.gov/standards/iso639-2/englangn.html">http://www.loc.gov/standards/iso639-2/englangn.html</a>.<br />
The <i><country></i> argument is a valid ISO Country Code. These codes are the upper-case, two-letter codes as defined by ISO-3166. You can find a full list of these codes at a number of sites, such as: <a href="http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html">http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html</a>.<br />
<br />
<b>How resource bundles are managed in CDF</b><br />
We have two level of messages files in CDF<br />
<ul><li><b>Global message files</b>: located in <i><biserver_home>/pentaho_solutions/system/pentaho_cdf/resources/languages</i> and useful when used in dashboard global templates.</li>
<li><b>Dashboard specific message files</b>: located in <i><dashboard_home></i> they can be distributed together with single dashboard in a sort of "package". To enable Dashboard specific message files support we've to add a <messages> element to the xcdf file configuration whose value is the base name of the dashboard specific message file.</li>
</ul><br />
<b>The CDF.i18n tag: internationalization support in HTML dashboard templates</b><br />
To use the internationalization support in dashboards HTML templates we implemented a tag to use to get the message string from the resource bundle and use it where required in the HTML template file. This is useful for descriptions, annotations, generic labels and similar.<br />
<br />
We can use the <i>CDF.i18n</i> tag using the following syntax:<br />
<br />
<i>CDF.i18n(<message_key>)</i><br />
<i><br />
</i><br />
where <i><message_key></i> is the message key located in the message resource bundle files.<br />
<br />
I<b>nternationalization support in CDF components</b><br />
Because of the early preview stage of this first release, for the moment, only the jFreechart component supports internationalization. In this case the support applies to the chart's title attribute.<br />
To support an internationalized <i>title </i>in a JFreechart component we added a new <i>titleKey</i> metadata attribute whose value is the message key for the chart title we can found in the resource bundle. A complete internationalization support for all the CDF components (where needed) will come shortly so be patient.<br />
<br />
<b>And here we go...</b><br />
For anyone who want to have a first look on this, in the bi-developer solution you have in the repository trunk has a fully working example you can have a look and experiment with. Below two screenshots of the sample dashboard I made internationalized in the Italian language. I hope you enjoy playing with this first release and stay tuned here for the next updates from the field.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1dqlCxh8XAmJf7yR81hgoNMAd8kV1HFIyp-OraQyG3GJ-QejsPMIez7I7TgUyt9WpCngUCRd9DOHVZrr-b_kOicNlTKMJDQqLohtS6-LJ2UMNAh_Efog1Wh9bpaHFhfc7tR113PY-McWy/s1600/cdf_intern_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1dqlCxh8XAmJf7yR81hgoNMAd8kV1HFIyp-OraQyG3GJ-QejsPMIez7I7TgUyt9WpCngUCRd9DOHVZrr-b_kOicNlTKMJDQqLohtS6-LJ2UMNAh_Efog1Wh9bpaHFhfc7tR113PY-McWy/s640/cdf_intern_1.png" width="640" /></a></div><div><br />
</div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga0iis4NeGWud5TgcdE7pD4SJrY0N6Otcxs_ZW8AfxxY8WpHqakk8XaW4qjCvMc4vQAhqtqkooy3QYQ7AhORjAESHUenqopL3Ss6xm2axNoqcJEDyI7TzTSufkDNepKY1wPmP08VkWOEo0/s1600/cdf_intern_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga0iis4NeGWud5TgcdE7pD4SJrY0N6Otcxs_ZW8AfxxY8WpHqakk8XaW4qjCvMc4vQAhqtqkooy3QYQ7AhORjAESHUenqopL3Ss6xm2axNoqcJEDyI7TzTSufkDNepKY1wPmP08VkWOEo0/s640/cdf_intern_2.png" width="640" /></a></div><div><br />
</div>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com4tag:blogger.com,1999:blog-4547836498257352074.post-15832590815104133502010-07-12T12:26:00.004+02:002010-07-12T12:39:11.578+02:00Joining and appending output datasets with CompoundDataAccessAn interesting topic about CDA is the ability apply join and union constructs to our incoming dataset using a <i>CompoundDataAccess</i> element. This definition is a sort of extension of the <i>DataAccess </i>element and through a <i>type </i>attribute gives us the ability to<i> join</i> or <i>append </i>two given <i>DataAccess </i>datasets outputs forming a new one.<br />
<br />
<b>How does <i>CompoundDataAccess </i>works internally</b><br />
<br />
It is really interesting to have a look at the sources and see how the things really works: <br />
<ul><li>when you want to join the two <i>DataAccess </i>outputs CDA builds a little Kettle transformations and uses the Merge Join step two do the required join operation</li>
<li>if you want to append the two <i>DataAccess </i>outputs CDA very easily appends by itself the two datasets following the order you specified in configuration file.</li>
</ul><br />
Remember that in either cases you can apply this operations considering only two datasets at the time for each <i>CompoundDataAccess </i>element definition.<br />
<br />
<b>CompoundDataAccess basics</b><br />
<br />
To define a new <i>CompoundDataAccess </i>element you have to mandatory specify<br />
<ul><li>an<i> id </i>attribute, to externally identify this <i>DataAccess </i>elemement</li>
<li>a <i>type</i> attribute that accepts two values union or join following the type of composition you're looking for.</li>
</ul>Because <i>CompoundDataAccess </i>is only an extension of the <i>DataAccess </i>element, we can apply the <i>Parameter</i>, <i>Columns</i>, <i>CalculatedColumns </i>and <i>Output </i>elements following the same rules specified for <i>DataAccess </i>in my previous post <a href="http://ramathoughts.blogspot.com/2010/07/cda-configuration-files-basics-part-2.html">here</a><br />
<br />
Moreover, because the <i>CompundDataAccess </i>element works on resulting <i>DataAccess </i>datasets we have to preliminarily define the two needed <i>DataAccess</i>. It is not possible (but would be a nice to have and I can think about implementing it) use <i>DataAccess </i>elements defined in external CDA files.<br />
<br />
<b>Joining datasets output</b><br />
To join two datasets we have to define a <i>CompoundDataAccess </i>element with the type attribute valued to the <i>join </i>value. The join type lets you execute a<b> FULL OUTER JOIN</b> upon the two input datasets. Required elements are:<br />
<br />
<ul><li><i>Left element definition</i>, to specify a dataset for the left part of the join,</li>
<li><i>Right element definition</i>, to specify a dataset for the right part of the join.</li>
</ul>Anyone of the two elements accepts the following attributes:<br />
<br />
<ol><li><i>id</i>, is valued to the id of the <i>DataAccess </i>element definition you are going to consider as the left/right dataset</li>
<li><i>key</i>, is a comma separated list of elements to be considered as the join key. Remember that the columns keys are specified <i><b>giving their position</b> <b>in the DataAccess query and not the name</b></i>.</li>
</ol><br />
Below I give an example to fully understand this concept. Suppose we defined the following two <i>DataAccess</i> definitions in our CDA file<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <DataAccess id="1" connection="1" type="sql" access="private" cache="true" cacheDuration="300">
<Name>Sql Query on SampleData - Jdbc</Name>
<Query>
select o.YEAR_ID, o.STATUS as status, sum(o.TOTALPRICE) as totalprice from orderfact o
group by o.YEAR_ID, o.STATUS
</Query>
.
.
.
.
</DataAccess>
<DataAccess id="2" connection="1" type="sql" access="public" cache="true" cacheDuration="5">
<Name>Sql Query on SampleData</Name>
<Query>
select o.YEAR_ID, o.status, sum(o.TOTALPRICE * 3) as tripleprice from orderfact o
where o.STATUS = ${status} and o.ORDERDATE &gt; ${orderDate}
group by o.YEAR_ID, o.STATUS
order by o.YEAR_ID DESC, o.STATUS
</Query>
.
.
.
</DataAccess>
</code></pre><br />
and suppose in the same cda file we define this CompoundDataAccess element.<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <CompoundDataAccess id="3" type="join">
<Left id="1" keys="0,1"/>
<Right id="2" keys="0,1"/>
<Columns>
<CalculatedColumn>
<Name>PriceDiff</Name>
<Formula>=[TRIPLEPRICE]-[TOTALPRICE]</Formula>
</CalculatedColumn>
</Columns>
<Parameters>
<Parameter name="status" type="String" default="Shipped"/>
<Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2003-03-01"/>
</Parameters>
<Output indexes="0,1,2,5,6"/>
</CompoundDataAccess>
</code></pre><br />
As you can see the <i>CompoundDataAccess </i>element has the two keys, <i>Left </i>and <i>Right</i>, two indicate the two sides of the join. The left one uses the <i>DataAccess </i>id 1 (the first one above) and the second the <i>DataAcccess</i> id 2. Then you see the comma separated list that defines the positions of the keys columns in the related DataAccess query. In this case you can see, looking at the related DataAcces queries, that either the elements uses <i>YEAR_ID</i> and the <i>STATUS </i>columns<br />
<br />
<b>Appending datasets ouputs</b><br />
To append two datasets we have to define a <i>CompoundDataAccess </i>element with the type attribute valued to the <i>union</i> value.The union type lets you execute append two <i>DataAcces </i>output datasets giving their order using two configuration elements:<br />
<ul><li><i>Top</i>, to specify the first dataset,</li>
<li><i>Bottom</i>, to specify the second dataset.</li>
</ul><br />
Anyone of the two elements accepts the following attributes:<br />
<ol><li><i>id</i>, is valued to the id of the <i>DataAccess </i>element definition you are going to consider as the Top/Bottom dataset</li>
</ol>Below a sample of configuration using a compound union. The <i>DataAccess </i>elements considered for this sample are the same defined in the previous sample for the join type.<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <CompoundDataAccess id="3" type="union">
<Top id="2"/>
<Bottom id="1"/>
<Parameters>
<Parameter name="year" type="Numeric" default="2004"/>
</Parameters>
</CompoundDataAccess>
</code></pre>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-90552976946496573412010-07-09T23:29:00.001+02:002010-07-12T12:32:25.246+02:00CDA configuration files basics (part 2)Last time we started talking about <a href="http://ramathoughts.blogspot.com/2010/04/cda-configuration-files-basics.html">the basics of CDA configuration</a>. Today we'll continue our series of articles on CDA better at DataAccess element configuration.<br />
<br />
<b>A little ricap</b><br />
<br />
CDA is a BI Platform plugin that that allows to fetch data in various formats. It works defining a configuration file where we will define connections to our available datasources and a number of data access objects. The data access objects defines the strategy used to access the data using a specified connection. In our previous post we talkes briefly about how to configure Connections a we give a brief overview about the <i>DataAccess</i> element. Today we will complete our introduction to the basics of CDA <i>DataAccess </i>configuration.<br />
<br />
<b>Adding parameters to our DataAccess element</b><br />
<br />
The <i>Parameters </i>element, nested into the <i>DataAccess </i>element, let you define a set of parameters required to get the data from you connection. Every parameter is associated to a <i>Parameter </i>element which accepts at least the following attributes:<br />
<br />
<ul><li><i>name</i>, to specify the Parameter name.</li>
<li><i>type</i>, lets you specify the Parameter type. Allowed types are String, Date, Integer, Numeric.</li>
<li><i>pattern</i>, lets you specify a display pattern required to let the CDA better understand the parameter's value as inputted by the user. Samples of usage for this attribute are dates dates or numeric parameters.</li>
<li><i>default</i>, lets you specify a default value for the parameter so that it will be automatically assigned every time we don't give that value</li>
</ul><br />
Below you've a sample about configuring a <i>Parameters </i>element<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <DataAccess id="1" connection="1" type="sql" access="public" cache="true" cacheDuration="300">
.
.
<Query>
select o.YEAR_ID, o.STATUS, sum(o.TOTALPRICE) as price from orderfact o
where o.STATUS = ${status} and o.ORDERDATE &gt; ${orderDate}
group by o.YEAR_ID, o.STATUS
</Query>
.
.
<Columns>
<Column idx="0">
<Name>Year</Name>
</Column>
<CalculatedColumn>
<Name>PriceInK</Name>
<Formula>=[PRICE]/1000000</Formula>
</CalculatedColumn>
</Columns>
.
.
<!-- Output controls what is outputed and by what order. Defaults to everything -->
<Output indexes="1,0,2,3"/>
.
.
</DataAccess>
</code></pre><br />
For your reference I've also shown you the query definition. Look at the <i>Columns </i>element declaration: you can see that we're going to rename the column with ordinal 0 from <i>"YEAR_ID"</i> to<i> "Year"</i>. To do that we used a Column element where we set the new name.<br />
Then, using the <i>CalculatedColumn </i>element, we added a new calculated column name <i>PriceInK</i>.<br />
Consider that:<br />
<ul><li>New calculated columns are always added at the end of the columns set</li>
<li>CDA uses Pentaho reporting libraries to apply formulas so that means you need to identify streams columns in your formula using square bracket notation and that you can use all the construct found in that libraries.</li>
</ul><ol></ol>Last but not least we changed the positional layout of the output columns through with an Output element. In the indexes attributes we set the columns position using column indexes. You have two consider two things here<br />
<br />
<ul><li>The<b> total columns number</b> is 4 because 3 came from the query and one was the calculated column lastly added to the stream</li>
<li>The index for the first<b> leftmost column is 0 </b>and NOT 1</li>
</ul><br />
<br />
<ol></ol>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-66810823437480760642010-04-28T16:24:00.011+02:002010-09-08T11:58:00.369+02:00Easily build BI Server from behind an NTLM proxyPentaho projects bases on <a href="http://ant.apache.org/ivy/">Apache IVY</a> to manage project's dependencies. This is good because it gives you a consistent and uniform way to manage projects dependencies but gives you some troubles if you need to make a build of one of the many Pentaho projects from behind an NTLM firewall.<br />
<br />
So here it is a brief and concise survival guide about how to do that quickly and efficiently.<br />
<br />
<b>Install a local proxy in you development computer</b><br />
<br />
First of all download <a href="http://cntlm.sourceforge.net/">cntlm</a> a local proxy that let you easily authenticate through your company or customer NTLM authentication proxy. You've versions for Windows and Linux. I learned about it two years ago when I was on Linux and I had to manage some Maven projects. Now I'm on Windows and the good for me is that it has a version also for this platform (I tried using also <a href="http://sourceforge.net/projects/ntlmaps/files/">ntlmaps</a> but I wasn't able to have it working properly with IVY)<br />
<br />
Installation is really simple but, on WIndows, has a tweak we will talk about later. Download the .zip from <a href="http://sourceforge.net/projects/cntlm/files">sourceforge</a> and unzip it wherever is fine for you. Execute the <i>setup.bat</i> and it will rapidly install the local proxy as a Windows Service. <br />
<br />
Go to the installation directory (typically <i>C:\Program Files\cntlm</i>) and edit <i>cntlm.ini</i>. Replace the informations for<i> username, password, domain</i> and your <i>proxies hosts and ports</i> as detailed in the file excerpt given below <br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>#
# Cntlm Authentication Proxy Configuration
#
# NOTE: all values are parsed literally, do NOT escape spaces,
# do not quote. Use 0600 perms if you use plaintext password.
#
Username <your_username>
Domain <your_domain>
Password <your_password> # Use hashes instead (-H)
#Workstation netbios_hostname # Should be auto-guessed
Proxy <1st_proxy_host>:<1st_proxy_port>
Proxy <2nd_proxy_host>:<2nd_proxy_port>
#
# This is the port number where Cntlm will listen
#
Listen 3128
</code></pre><br />
Make the changes and save the file. Now, before starting the local proxy service, here is the tweak. In my case, the <i>setup.bat</i> script forget to set a service startup parameter that sets the cntlm configuration parameter file location. So to workaround that, open your registry and go to the following key <i>HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\cntlm\Parameters</i>. Look for the <i>AppArgs</i> attribute and modify it adding the parameter <i>-i "C:\Program Files\Cntlm\cntlm.ini"</i> as shown below<br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAQ0ULiUAhB_BACx5dd-kJ3WZGmkk_yotnZJM9O4Su-VMwagla6XxkNueD3k6vUipy3iazjy_e6V5MRyqA_o7w6xSNVO0IdvcRcp3qtRNGXJ2Xj3AlYMBTVkMEHTlyHEReVUoCH5pOWqLL/s1600/cntlm_svc.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="48" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAQ0ULiUAhB_BACx5dd-kJ3WZGmkk_yotnZJM9O4Su-VMwagla6XxkNueD3k6vUipy3iazjy_e6V5MRyqA_o7w6xSNVO0IdvcRcp3qtRNGXJ2Xj3AlYMBTVkMEHTlyHEReVUoCH5pOWqLL/s320/cntlm_svc.png" width="320" /></a></div><br />
After that you're ready to start you service successfully otherwise you'll get an error.<br />
<br />
<b>Back to our Pentaho's project build</b><br />
<br />
In my case I was making a build of the newest bi-server 3.6 get from the related branch of Pentaho repository (<i><a href="http://source.pentaho.org/svnroot/bi-platform-v2/branches/3.6">http://source.pentaho.org/svnroot/bi-platform-v2/branches/3.6</a></i>). To enable IVY to use your locally configured authentication proxy server you've to set the following environment variable before starting compiling the project<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ANT_OPTS=-Dhttp.proxyHost=localhost -Dhttp.proxyPort=3128</code></pre><br />
It is up to you to decide where to set that variable either as a Windows environment variable or from within a command line session (if you're starting the build manually).<br />
<br />
<b>And what about for the Linux users....</b><br />
<br />
If you're on Linux you can install cntlm using the package manager of your favorite distro. I used it on Ubuntu and Fedora withou any problem. The configuration file is the same as before but is located in <i>/etc/cntlm.conf</i>. You've to change the same parameters detailed above for <i>cntlm.ini</i> (the file is exactly the same). Start the daemon, set the <i>ANT_OPTS</i> environment variable and have fun with your build.Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-70921364231190396512010-04-21T11:33:00.003+02:002010-07-09T23:35:26.258+02:00CDA configuration files basics (part 1)Last time I give a<a href="http://ramathoughts.blogspot.com/2010/03/first-look-into-pentaho-cda.html"> brief explanation about the basics of CDA</a> compilation and deployment and I showed you how to start executing a simple query on our datamart using CDA getting back a resultset formatted as you expected. I decided to take you to a fully functional sample going through a set of explanatory steps summarized below:<br />
<ul><li>Explain the structure of CDA files and learn how to built a new one</li>
<li>Show the basic verbs you can use to interact with CDA</li>
<li>Put everything together with a working example</li>
</ul>Before start playing with CDA remember to get the <a href="http://code.google.com/p/pentaho-cda/">latest CDA sources</a> compile it and reinstall the plugin as explained in my first post on this blog.<br />
<br />
CDA files has a very basic structure. They are very easy to be learned also by a novice user with a basic knowledge of XML. Basically a CDA file contains definitions for some important elements<br />
<ol><li>datasources</li>
<li>data access</li>
<li>compound data access </li>
</ol><br />
<b>The DataSource element</b><br />
<br />
Datasources in CDA have the usual meaning of a named connection to a database server. The name (in our case the connection id) is commonly used when creating a query to the database.<br />
<br />
Each DataSource is made up by a set of connections defined as follow:<br />
<ul><li>has an id attribute to uniquely identify the connection in this CDA datasource definition. Of course we cannot have two connections with the same id.</li>
<li>has a type attribute whose value depends on the connection type. We've different connection types one for each datasource type: <i>sql, mondrian, Pentaho Metadata (MQL), Olap4J</i></li>
<li>a set of other elements that differs depending on the connection type</li>
</ul>We can have 1 or more connections in the DataSource element according to your needs. <br />
<ul></ul><i>Configuring connections to relational sources </i><br />
<br />
<br />
If we look at the samples that comes together with the latest CDA distribution you have a sample for each of the possible connection types. Open PUC console and go to the folder<i> bi-developer/cda/cdafile</i> and you have the list of cda definitions shown below in the files window. As you can see every CDA file is identified by a specific icon.<br />
<br />
First interesting point: to open a file we've a <i>CDA files editor</i> integrated in Pentaho console. Cool! So to open a CDA file select it, right click on the file name and select Edit<br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjF-CWxi9IJBT_rnNqmOnVdBF-o40NpuwtBueV2eNGezzBjavhVkAI-Fy0LY-s0mAXkcn6M8RzaCvpFAUjz4mwz4bQWHpkoc1DwmJzOIJRy70SkUwlYcr9tre4ecHbCCdQgjE13OjnWOut/s1600/cda1.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjF-CWxi9IJBT_rnNqmOnVdBF-o40NpuwtBueV2eNGezzBjavhVkAI-Fy0LY-s0mAXkcn6M8RzaCvpFAUjz4mwz4bQWHpkoc1DwmJzOIJRy70SkUwlYcr9tre4ecHbCCdQgjE13OjnWOut/s320/cda1.png" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"></div><br />
Let's have a look at how to connect to a relational datasource. We've two samples for that. One to connect through jdbc and another to connect through a JNDI datasource. Open the <i>sql-jdbc.cda</i> for example (the jdbc one). As you can see, in the right area of PUC console the editor opened and shows you the file content. CDA editor is the standard tool to modify CDA files in a quick and easy way. You can modify your file, save it and also test it using the preview functionality (we'll talk about it later). Looking at the file immediately after the <i>CDADescriptor</i> element you'll find the <i>DataSource </i>element and, as its child, the connection definitions.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhc1q0f3bFewRPgECB03_PhzGwoZ8MKUUHvDMroWg7kIFqiVXpFmlbjphrrnGAxfmV_DrBQxVORa5-F8y5GNhacfaF4PRVjB7Guc80XR5_utoL2N3aqT3O3BlewpqwRzJWfcNrhqv3QA8ek/s1600/cda2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="336" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhc1q0f3bFewRPgECB03_PhzGwoZ8MKUUHvDMroWg7kIFqiVXpFmlbjphrrnGAxfmV_DrBQxVORa5-F8y5GNhacfaF4PRVjB7Guc80XR5_utoL2N3aqT3O3BlewpqwRzJWfcNrhqv3QA8ek/s640/cda2.png" width="640" /></a></div><br />
As you can see you can find a connection with <i>id="1</i>" and <i>type="sql.jdbc"</i> and then a set of elements describing properties that are directly related with the selected connection type (in this case the definition of a jdbc connection so we've elements for <i>driver, url, username </i>and <i>password</i>). Take your time to go through all the other samples and see the differences in connection types.<br />
<br />
<b>The DataAccess element</b><br />
<br />
The DataAccess contains the definitions for the query that needs to be executed, the parameters the query accepts and some other side elements that we will explore in the next steps.<br />
<br />
A DataAccess element:<br />
<ul><li> has an<i> id</i>, </li>
<li>has a <i>descriptive name</i> (given through a child element), </li>
<li><b><i>links a specific connection id defined in the previous DataSource section</i></b>, </li>
<li>contains a<i> query element</i>,</li>
<li>contains a set of given <i>parameters </i>(not mandatory),</li>
<li>contains a set of other not mandatory definitions will go thorugh in the next steps. </li>
</ul>We can have one or more DataAccess elements in our CDA file. <br />
So go back to our example (sample-jdbc.cda) and continue our exploration.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlAfe8XAX-33paaXLMAzJkwhZ1DnSyTkEMJIstio23Nu_Zx3qXJg3nICtV2Dk8W0GzZpveB4FVs99VfGwoWjNGZKevmHfw0iInkyTxg3trE5Wq63H8WO3JFpoptg_tklFQQtsaTVU-FFmk/s1600/cda3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="364" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlAfe8XAX-33paaXLMAzJkwhZ1DnSyTkEMJIstio23Nu_Zx3qXJg3nICtV2Dk8W0GzZpveB4FVs99VfGwoWjNGZKevmHfw0iInkyTxg3trE5Wq63H8WO3JFpoptg_tklFQQtsaTVU-FFmk/s640/cda3.png" width="640" /></a></div><br />
As you can see in the picture above we've some things to be noted:<br />
<ul><li>Remember to give an <i>id</i> and to link the connection defined in the <i>DataSources </i>section </li>
<li>Because we're writing text as value of an xml file element, remember the that comparison operators<i> < ></i> has to be written respectively as <i>&lt;</i> and <i>&gt;</i> otherwise you'll get an error.</li>
<li>Parameters has given a name. In the query you can indicate a parameters with the usual syntax <i>${<parameter_name>}</i> where<i> parameter_name</i> is the exact name of the parameter we're going to apply in the where clause.</li>
<li>Every parameter has a type that, together with the name, needs to be mandatory given. Supperted types are: String, Date, Numeric and Integer. Any parameters can have a default value specified.</li>
<li>If the parameter's type is Date <i>you can specify a pattern</i> you followed for your date format.</li>
</ul><b>Test the query</b><br />
<br />
To test our DataAccess definition before moving foward with our implementaion click on the <i>Preview button</i>. When the Preview form appears you can select the DataAccess definition you want to test and see the results below on a nice looking table.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXZQo8YeE5nz88znDxiKfCz2ys8lepHwaPX8JQAh5jf1f0PBan8qC7DiV4svRnG3XmsL3AsI2zX7OGmKqa0bdKEdW33OqA-v4_c9JEKcSApoUkqB4iKaSOCcbXoBt8Pri1TRhAK8QYP8ux/s1600/cda4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="248" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXZQo8YeE5nz88znDxiKfCz2ys8lepHwaPX8JQAh5jf1f0PBan8qC7DiV4svRnG3XmsL3AsI2zX7OGmKqa0bdKEdW33OqA-v4_c9JEKcSApoUkqB4iKaSOCcbXoBt8Pri1TRhAK8QYP8ux/s640/cda4.png" width="640" /></a></div>Some things to note:<br />
<ul><li>the previewer sets the <i>default values</i> for the query (if any) but you can change them accordingly to your needs, </li>
<li>the<i> date format</i> for the orderDate parameter follows the pattern specified in the CDA file definition.</li>
</ul><br />
Now I'll give you the time to play with the samples and experiment a bit. Next time we'll talk about the second part of the DataAccess elements and CompoundDataAccess definitions.Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-19012392813511604632010-03-19T16:02:00.005+01:002010-07-09T23:35:44.218+02:00A first look into Pentaho CDAThese days I decided to have a look into Pentaho CDA the newest idea from <a href="http://pedroalves-bi.blogspot.com/">Pedro Alves</a>. The project is available <a href="http://code.google.com/p/pentaho-cda/">here</a> on Google code. He gave announcement about this two months ago in <a href="http://pedroalves-bi.blogspot.com/2010/01/cda-community-data-access.html">his blog</a> and immediately it appeared interesting.<br />
<br />
<b>What is CDA?</b><br />
<br />
CDA is a BI Platform plugin that that allows to fetch data in various formats. The good things are that it allows you to acces data using all of the possible data sources you can use in BI Server (SQL, MDX, Metadata, Kettle etc.) and can be used by any client (internal or external to BI Server) because you can call it through a simple url and it gives you back a data stream.<br />
<br />
<b>Let's go and compile the code</b><br />
<br />
So after some very busy days of hard work were I only had the opportunity to keep my local copy of the code updated with the repository and see what's going on, yesterday I had some time available and I was able to compile it and give it try. The framework easily compiles with the ant script you find in the source tree. The only thing you've to consider is to create a file named <i>override.properties</i> containing a set of definitions related to your working environment <br />
<br />
<pre style="-moz-background-inline-policy: continuous; background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> plugin.local.install.solutions.dir = <substitute_with_your_biserver_home>/pentaho-solutions
plugin.local.install.user = joe
plugin.local.install.pass = password
</code></pre><br />
After having compiled the code successfully you can install it under your BI Server rel. 3.5.2 with the command<br />
<br />
<pre style="-moz-background-inline-policy: continuous; background: none repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> ant install-plugin
</code></pre><br />
<b>The basics</b><br />
<br />
Immediately after our successfull compilation and installation we can start biserver and login into the system. Under the bi-developer solution you have a folder named CDA where you can find an extended set of examples to start playing with.<br />
<br />
CDA configuration is based on xml files. Every xml file contains a set of data access definitions. Everyone is identified by an <i>id</i> and has a specified data access method. I don't copy and paste a sample here because you can have a look in the samples by yourself. They are very simple and the format is immediately understandable so that you can easily edit them by hand. <br />
<br />
Basically you can have your output data stream in multiple formats. At the time of writing this article you can use <i>JSON</i> (the default), <i>XML</i>, Excel and <i>CSV</i>. This gives you the maximum flexibility on what you can do with that data.<br />
<br />
If you want to try to execute a query using a data access method defined in your <i>.cda</i> file you can use the following this sintax<br />
<br />
<i>http://<biserver_host>:<port>/pentaho/content/cda/doQuery?solution=bi-developers&path=cda/cdafiles&file=sql-jndi.cda&outputType=xml&dataAccessId=1</i><br />
<br />
This calls the method <i>doQuery</i> to execute the query using the <i>dataAccess</i> with <i>id</i> <i>1</i> defined in <i>sql-jndi.cda</i> and located under the solution <i>bi-developers</i> in folder <i>cda/cdafiles</i> giving you the output using <i>XML</i>. Try to execute it changing the output type and see what happens. Remember that if you don't specify an output type it will be JSON by default.<br />
<br />
Suppose that we wnat an output typ like xml, the answer you'll see in your browser window is something like this<br />
<br />
<i>Shipped20044114929.96Shipped20051513074.46</i><br />
<br />
almost insignificant but, if you get the page source you clearly can see the xml stream sent to you by our CDA plugin<br />
<br />
<pre style="-moz-background-inline-policy: continuous; background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <CdaExport>
<MetaData>
<ColumnMetaData index="0" type="String" name="STATUS"/>
<ColumnMetaData index="1" type="Numeric" name="Year"/>
<ColumnMetaData index="2" type="Numeric" name="price"/>
<ColumnMetaData index="3" type="String" name="PriceInK"/>
</MetaData>
<ResultSet>
<Row>
<Col>Shipped</Col>
<Col>2004</Col>
<Col>4114929.96</Col>
<Col isNull="true"/>
</Row>
<Row>
<Col>Shipped</Col>
<Col>2005</Col>
<Col>1513074.46</Col>
<Col isNull="true"/>
</Row>
</ResultSet>
</CdaExport>
</code></pre><br />
Really cool! If you don't want to make your hand dirty in changing jdbc definitions I suggests you to try all the files whose name contains the word <i>jndi</i> that means they use a datasource and not a direct jdbc connection defined in the file (these are the files that contains the word <i>jdbc</i>).<br />
<br />
Read the next part of this serie and learn about <a href="http://ramathoughts.blogspot.com/2010/04/cda-configuration-files-basics.html">CDA configuration files basics</a>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-77492942906309410522010-02-25T17:43:00.003+01:002010-02-25T17:49:56.784+01:00Customize CDF jfreechart componentI was dealing with CDF Dashboards during these days and I had to set <i>line-width</i> and <i>line-style</i> attributes in my <i>jFreechartComponent's LineChart</i> widget.<br />
<br />
Looking at the <i>jFreechartComponent</i> documentation you clearly see that in the standard implementation some attributes, specifics only to some chart types, aren't implemented. This was also for the two attributes <i>line-width</i> and <i>line-style</i> related to the LineChart widget. But this is not a problem because we can add that missing attibutes very easily and I'll show you how. The interesting thing is that I can manage all the attributes that are available for a specific <i>JFreeChart </i>chart type.<br />
<br />
If you need to add support for missed JFreeChart chart's attributes you can follow the steps summarized below. We suppose to add line-width and line-style attributes and to access them from CDF jFreechartComponent as two properties respectively named <i>lineWidth</i> and <i>lineStyle</i>.<br />
<br />
1) Go to <i><biserver-home>/pentaho-solutions/cdf/components</i> and open <i>jfreechart.xaction </i>and open it with your favourite editor.<i><br />
</i><br />
<br />
2) Add two new elements to the <i>inputs</i> section of the .xaction file. Below an example of the file with the two new inputs added. Remember here that the value of the request element (see lines 5 and 11 below) has to be the same in name and in case as the name of the property we want to put in our <i>CDF jFreechartComponent</i> definition. Then be careful to correctly assign a the default value needed in case that the property isn't provided (see lines 7 and 13) by you CDF component call. This is particularly important when our attribute is a number. If you don't assign it properly you'll get a <i>NumberFormatException</i>.<br />
<br />
<pre style="-moz-background-clip: border; -moz-background-inline-policy: continuous; -moz-background-origin: padding; background: rgb(240, 240, 240) url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif) repeat scroll 0% 0%; border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: <inputs>
2: <!-- Omitted for brevity -->
3: <LINEWIDTH type="string">
4: <sources>
5: <request>lineWidth</request>
6: </sources>
7: <default-value>1.5</default-value>
8: </LINEWIDTH>
9: <LINESTYLE type="string">
10: <sources>
11: <request>lineStyle</request>
12: </sources>
13: <default-value/>
14: </LINESTYLE>
15: <!-- Omitted for brevity -->
16: </inputs>
</code></pre><br />
3) Add the new inputs previously defined in the <i>inputs</i> section (see above) to the <i>action-inputs</i> section of our ChartComponent <i>action-definition</i> (as shown below at lines 7 and 8). Then, use these new fields to populate two new property elements added to the <i>chart-attributes</i> section as shown at line 25-26. Be very careful here because the name of the element you give to every new chart attribute has to be equal to the related attribute for the JFreeChart library. For example: if in JFreeChart library the line width attribute is named <i>line-width</i> that is the name to be considered as child element of the <i>chart-attributes</i> element. Then the value to be assigne to that new element is the name of the related action input element (for <i>line-width</i> we will assign as value the <i>LINEWIDTH</i> element. See line 25 below). <br />
<br />
<br />
<pre style="-moz-background-clip: border; -moz-background-inline-policy: continuous; -moz-background-origin: padding; background: rgb(240, 240, 240) url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif) repeat scroll 0% 0%; border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: <action-definition>
2: <component-name>ChartComponent</component-name>
3: <action-type>Chart</action-type>
4: <action-inputs>
5: <chart-data type="result-set" mapping="newResults"/>
6: <!-- Omitted for brevity -->
7: <LINEWIDTH type="string"/>
8: <LINESTYLE type="string"/>
9: <!-- Omitted for brevity -->
10: </action-inputs>
11: <action-resources/>
12: <action-outputs>
13: <chart-filename type="string"/>
14: <base-url type="string"/>
15: <chart-mapping type="string"/>
16: <image-tag type="string"/>
17: <chart-output type="content"/>
18: </action-outputs>
19: <component-definition>
20: <width>{WIDTH}</width>
21: <height>{HEIGHT}</height>
22: <by-row>{BYROW}</by-row>
23: <chart-attributes>
24: <!-- Omitted for brevity -->
25: <line-width>{LINEWIDTH}</line-width>
26: <line-style>{LINESTYLE}</line-style>
27: <!-- Omitted for brevity -->
28: </chart-attributes>
29: </component-definition>
30: </action-definition>
</code></pre>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-46977878522975102442010-02-18T12:29:00.005+01:002010-07-13T11:35:59.518+02:00BI Server & MS Active Directory in 10 minutesRecently I had the need to connect Pentaho to MS Active Directory for user authentication/authorization. Immediately I asked myself how to connect Pentaho BI Server to Microsoft Active directory and I answered "Oh don't worry... it will take no more than 10 minutes!". Then the "look for a how-to document..." discovery process started.<br />
<br />
I found a lot of documentation about this issue (wiki articles, forums thread) but there isn't a well done survival guide on this problem (that's my opinion).<br />
<br />
So I'll try to summarize in few lines the steps followed and problems encountered to try to build a sort of survival guide for anyone with the same issue to solve<br />
<br />
<b>Have a look at spring configuration files</b><br />
<br />
BI Server security architecture is based on <a href="http://static.springsource.org/spring-security/site/index.html">Spring Security</a> so the first guide to be read is the Spring documentation where they talk about <a href="http://static.springsource.org/spring-security/site/docs/2.0.x/reference/ldap.html">LDAP configuration</a>. Better, in case you don't know anything about that, if you came a step backward and have a read at the general architecture of Spring security.<br />
<br />
Spring security beans wires together through spring application context and in Pentaho all the needed spring application context files are located in <i><biserver_home>/pentaho-solutions/system</i>. You'll find a lot of them there but the important things to know are:<br />
<br />
<i>pentaho-spring-beans.xml</i> contains the list of imported spring bean files that will be loaded when BI Server will start.<br />
We've two important file groups there named <i>applicationContext-spring*</i> and <i>applicationContext-pentaho*</i>. In each group, you have one file for every available authentication method defined in Pentaho. Usually the beans located in files belonging to<i> applicationContext-spring</i> group contains definitions for spring related beans needed to configure the specified authentication/authorization method. The beans located in files belonging to <i>applicationContext-pentaho</i> group contains definitions of Pentaho's beans involved in the authorization/authentication methods for the specific authentication method (LDAP, Hibernate, jdbc).<br />
<br />
<b>So how to configure Pentaho to work with MS Active Directory? </b><br />
<br />
The setup to have Pentaho working with MS Active Directory is really simple if you know exactly what to do and to how. Il try to summarize you everything in the following paragraphs. As detailed above all the files we will mention are located in <i><biserver_home>/pentaho-solutions/system</i>.<br />
<br />
<i>1. Setup an MS Active Directory user to let BI Server connect to</i>. You need to define a user in MS Active Directory so that BI Server can connect to and check if the Pentaho's user is going authenticate exists and is valid. The user you're going to define in MS Active Directory doesn't need to have any special right so a normal user is the more appropriate. Remember to check that the <i>"password never expire"</i> flag is not set for this user.<br />
<br />
2. Setup Spring Security files needed to enable LDAP Server authentication/authorization. This is a good point, I think the major one. First of all read the guidelines provided <a href="http://wiki.pentaho.com/display/ServerDoc2x/LDAP+Troubleshooting">here</a> about some rules to be followed when editing Spring configuration files particularly regarding white spaces and special characters. Then follow the points detailed here.<br />
<br />
2.a) Open the <i>applicationContext-security-ldap.properties</i> and change the properties accordingly to your needs. The useful thing about this file is that it contains all the properties needed to configure spring beans so that we doesn't need to look for them in eac xml file. They're all in one single place. Following you'll find an example:<br />
<br />
<pre style="-moz-background-inline-policy: continuous; background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> contextSource.providerUrl=ldap\://ldaphost\:389
contextSource.userDn=cn=ldapuser,OU=my_org_unit,dc=my_dc
contextSource.password=password
userSearch.searchBase=OU=my_org_unit,dc=my_dc
userSearch.searchFilter=(sAMAccountName=\{0\})
populator.convertToUpperCase=false
populator.groupRoleAttribute=cn
populator.groupSearchBase=OU=my_org_unit,dc=my_dc
populator.groupSearchFilter=(member=\{0\})
populator.rolePrefix=
populator.searchSubtree=true
allAuthoritiesSearch.roleAttribute=cn
allAuthoritiesSearch.searchBase=OU=my_org_unit,dc=my_dc
allAuthoritiesSearch.searchFilter=(objectClass=group)
</code></pre><br />
Important things to note here are<br />
<ul><li><i>contextSource.providerUrl </i>- LDAP server url</li>
<li><i>contextSource.userDn </i>- LDAP username. This is the user we've talked about in 1) above</li>
<li><i>contextSource.password</i> - LDAP user password</li>
<li>the <i>populator</i> properties are needed by Spring <i>DefaultLdapAuthoritiesPopulator</i>. That object is needed to load the set of authorities the user was granted.</li>
<li>the <i>userSearch</i> properties configures the attributes needed to fills up the "users" box when assigning permissions to reports etc.</li>
<li>the <i>allAuthorities</i> properties configures the attributes needed to fills up the "roles" portion of the permissions box when setting them for a report etc.</li>
</ul>The excerpt I gave above is fully working so you can copy and paste it in your properties file changing only the definitions specific to your installation. At this point you completed almost the 70% of the required configuration to have everything working on your system. Be careful to declare the full DN (domain name) when you work with MS Active Directory because, if not, it's almost sure you'll have an error like this (The first time I tried I had such an error for this reason)<br />
<br />
<pre style="font-family:arial;font-size:12px;border:1px dashed #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;"> Microsoft Active Directory Error: <br />
javax.naming.AuthenticationException: <br />
[LDAP: error code 49 - 80090308: LdapErr: DSID-0C09030B, comment: <br />
AcceptSecurityContext error, data 525, v893 ] <br />
at com.sun.jndi.ldap.LdapCtx.mapErrorCode(Unknown Source) <br />
at com.sun.jndi.ldap.LdapCtx.processReturnCode(Unknown Source) <br />
at com.sun.jndi.ldap.LdapCtx.processReturnCode(Unknown Source) <br />
at com.sun.jndi.ldap.LdapCtx.connect(Unknown Source) <br />
at com.sun.jndi.ldap.LdapCtx.(Unknown Source) <br />
at com.sun.jndi.ldap.LdapCtxFactory.getUsingURL(Unknown Source) <br />
at com.sun.jndi.ldap.LdapCtxFactory.getUsingURLs(Unknown Source) <br />
at com.sun.jndi.ldap.LdapCtxFactory.getLdapCtxInstance(Unknown Source) <br />
at com.sun.jndi.ldap.LdapCtxFactory.getInitialContext(Unknown Source) <br />
at javax.naming.spi.NamingManager.getInitialContext(Unknown Source) <br />
at javax.naming.InitialContext.getDefaultInitCtx(Unknown Source) <br />
at javax.naming.InitialContext.init(Unknown Source) <br />
at javax.naming.InitialContext.(Unknown Source) <br />
at javax.naming.directory.InitialDirContext.(Unknown Source) <br />
</code></pre><br />
<br />
You can find short but useful informations about this issue <a href="http://www.websina.com/bugzero/faq/ldap-error-code-49.html">here</a><br />
<br />
2.b) Two words about the default role. Anytime a user logs into Pentaho, the system assigns the user a default role of "Authenticated". This is a really important point to have all the things working. If that role isn't assigned you're able to connect but mantle witll never show anything to you. My problem was that in my spring LDAP context file the definitions to have that role assigned by default were missed. So I had to add them manually. So be <b>absolutely</b> sure to check that in <i>applicationContext-spring-security-ldap.xml</i> you have the <i>defaultRole</i> property defined with a value of <i>Authenticated</i> in the <i>popolator</i> bean <b>and if missed add it</b>. Below an excerpt of that bean definition with the <i>defaultRole</i> property added so that you can copy and paste if missed in your file.<br />
<br />
<pre style="-moz-background-inline-policy: continuous; background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <bean id="populator" class="org.springframework.security.ldap.populator.DefaultLdapAuthoritiesPopulator">
<!-- omitted -->
<property name="defaultRole" value="Authenticated" />
<!-- omitted -->
</bean>
</code></pre><br />
3) Rework the imports in <i>pentaho-spring-beans.xml </i>to enable the load of LDAP security beans at startup. Below (at lines 8-9) we disabled the DAO/Hibernate security and (at lines 10-11) we imported the new definitions enabling LDAP security<br />
<br />
<pre style="-moz-background-inline-policy: continuous; background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: <beans>
2: <import resource="pentahoSystemConfig.xml" />
3: <import resource="adminPlugins.xml" />
4: <import resource="systemListeners.xml" />
5: <import resource="sessionStartupActions.xml" />
6: <import resource="applicationContext-spring-security.xml" />
7: <import resource="applicationContext-common-authorization.xml" />
8: <!-- import resource="applicationContext-spring-security-hibernate.xml" />
9: <import resource="applicationContext-pentaho-security-hibernate.xml" / -->
10: <import resource="applicationContext-spring-security-ldap.xml" />
11: <import resource="applicationContext-pentaho-security-ldap.xml" />
12: <import resource="pentahoObjects.spring.xml" />
13: </beans>
</code></pre><br />
4) Now its the time to define a set of groups in your MS Active Directory for your pentaho users (if you don't already have such a groups) depending on your authorization needs. For sure you need to have at least one group to contain pentaho admins. In my system I called that group <i>PentahoAdmin</i>.<br />
<br />
5) Declare the new admin group in Pentaho configuration to assign that group the admin grant. To do that rework the acl-voter element in <i>pentaho.xml</i> as shown below. <br />
<br />
<pre style="-moz-background-inline-policy: continuous; background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <acl-voter>
<!-- What role must someone be in to be an ADMIN of Pentaho -->
<admin-role>PentahoAdmin</admin-role>
</acl-voter>
</code></pre><br />
6) Rework acl-publisher definitions in <i>pentaho.xml</i> for all the Pentaho's groups defined in the LDAP server. In my system I defined two roles <i>PentahoAdmin</i> and <i>PentahoUser</i> so my configuration looks like this<br />
<br />
<pre style="-moz-background-inline-policy: continuous; background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDHAMB4J8OAK-i25iG2QJN8oAwVuuG2J6uQ6uD8s6aPJYRfZD3hCJ2hj3prmjRkMBCwy8c1FE8PDauCtMNxpT6PqF37xdcbG0gp7nho236p7DhYj3hsoadvtFXmQB-_54Wk2ywwZmZnBZb/s320/codebg.gif") repeat scroll 0% 0% rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> <acl-publisher>
<!--
These acls are used when publishing from the file system. Every folder
gets these ACLS. Authenticated is a "default" role that everyone
gets when they're authenticated (be sure to setup your bean xml properly
for this to work).
-->
<default-acls>
<acl-entry role="PentahoAdmin" acl="FULL_CONTROL" /> <!-- Admin users get all authorities -->
<!-- acl-entry role="cto" acl="FULL_CONTROL" / --> <!-- CTO gets everything -->
<acl-entry role="PentahoUser" acl="EXECUTE_SUBSCRIBE" /> <!-- PentahoUser gets execute/subscribe -->
<acl-entry role="Authenticated" acl="EXECUTE" /> <!-- Authenticated users get execute only -->
</default-acls>
<!--
These acls are overrides to specific file/folders. The above default-acls will
be applied and then these overrides. This allows for specific access controls to
be loaded when the repository if first populated. Futher changes to acls can be
made in the platform GUI tool. Uncomment these and change add or delete to your hearts desire -->
<overrides>
<file path="/pentaho-solutions/admin">
<acl-entry role="PentahoAdmin" acl="FULL_CONTROL" />
</file>
</overrides>
</acl-publisher>
</code></pre><br />
7) Stop and restart you Pentaho server and everything is ready for a try.<br />
<br />
I hope I haven't missed anything and that everything is clear enough for everyone who reads these few instructions. Let me know if you have any problems so that I can keep updated this very brief guide.Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com15tag:blogger.com,1999:blog-4547836498257352074.post-14383932054523900552010-02-02T00:14:00.001+01:002010-02-02T00:15:55.854+01:00Pentaho BI Server language files (1/2)It was a long time of hard work the last month and a half but now I'm able to see a little light at the end of the tunnel and I had the time to be back. This time I have an interesting thing for all the italian friends. I completed:<br />
<br />
<ol><li> the italian language files for Mantle GWT Client. The files were already submitted to Pentaho JIRA hoping they will be incorporated in the next release of the product.</li>
<li>the italian language file for <a href="http://jpivot.sourceforge.net/">JPivot</a> toolbar. This was a minor effort because it is a very little file so it takes really a few minutes to be completed. This language file has been already committed by myself on <a href="http://sourceforge.net/projects/jpivot/develop">JPivot's cvs trunk</a>.</li>
</ol><br />
I detailed below the steps to be followed to install the new language on existing BI Server installations. The files are compatible with Pentaho 3.x. A special thanks to my colleague and dear friend Andrea Pasotti who helped (and is helping) me in this work. <br />
<br />
<b>How to activate italian language support for Mantle GWT client</b><br />
<ul><li>Download the files <i>messages_it.properties</i> and <i>MantleLoginMessages_it.properties</i> from the following <a href="http://jira.pentaho.com/browse/BISERVER-4040">link to JIRA</a></li>
<li>Stop BI Server </li>
<li>Copy the file <i>messages_it.properties</i> to <i><biserver_home>/webapps/pentaho/mantle/messages</i></li>
<li>Open the file <i><biserver_home>/webapps/pentaho/mantle/messages/supported_languages.properties</i></li>
<li>Add the following line</li>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 70%;"><code>it=Italiano
</code></pre>
<li>Save and close the file</li>
<li>Copy the file <i>MantleLoginMessages_it.properties</i> to <i><biserver_home>/webapps/pentaho/mantleLogin/messages</i></li>
<li>Open the file <i><biserver_home>/webapps/pentaho/mantleLogin/messages/supported_languages.properties</i></li>
<li>Add the following line</li>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 70%;"><code>it=Italiano
</code></pre>
<li>Save and close the file </li>
<li>Start BI Server </li>
</ul><b>How to activate Italian language support for JPivot toolbar</b><br />
<ul><li>Dowload the <i>resources_it.properties</i> language file from the following <a href="http://download.serasoft.it/public/resources_it.properties">link</a></li>
<li>Go to <i><biserver_home>/webapps/pentaho/WEB-INF/classes </i>and create the following directory path<i> com/tonbeller/jpivot/toolbar</i></li>
<li>Copy<i> resources_it.properties </i>to<i> </i><i><biserver_home>/webapps/pentaho/WEB-INF/classes/</i><i>com/tonbeller/jpivot/toolbar</i></li>
<li>Start BI Server</li>
</ul>I'm going to finalize to complete translation of the GUI so in the next few weeks will follow the italian language files PAC (Pentaho Administration Console) and Ad-Hoc Query Reporting. So what else.... stay tuned!Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-36813631792824285632009-12-10T01:16:00.004+01:002009-12-15T11:08:17.621+01:00Patching JPivot to filter by multiple members belonging to the same dimensionA very annoying problem with the release of JPivot shipped with the actual release of <a href="http://www.pentaho.com/">Pentaho</a> is that you can only select one member from the same cube dimension at a time. But frequently we would like to select more than one member at a time. So what a good opportunity to build a patched release of JPivot and make that happens!<br />
<br />
The first thing I made was try to test that by writing a sample MDX query on the SteelWheels cube that had, as a filter, a tuple made up by a set of members belonging to the same dimension. It worked. Infact, starting from <a href="http://mondrian.pentaho.org/">Mondrian</a> 3.1.2.13008, the OLAP engine supported compound slicers that makes this possible. After this very brief verification I was convinced to continue in my adventure and the next step was to find out where was the latest JPivot sources. As stated by <a href="http://www.willgorman.com/">Will Gorman</a> in the post #7 of <a href="http://forums.pentaho.org/showthread.php?t=65972&highlight=jpivot+sources">this thread</a> the latest and good sources are in the obvious place... the JPivot CVS Repository of the <a href="http://sourceforge.net/projects/jpivot">JPivot project on SourceForge</a>. So I get it and started my work.<br />
<br />
The patch wasn't so difficult to be done and I reached easily my goal. It was a good opportunity to go into the internals of JPivot and learn about it. The new PAT is not round the corner so my opinion is that JPivot will be the production OLAP viewer of choice for Pentaho Community Edition again for a few months. So, I think, this exercise is not a waste of time.<br />
<br />
If you want to try it, you can find the <a href="http://download.serasoft.it/public/jpivot-1.8.0.091203.zip">patched JPivot jar file here</a>. The setup takes just one minute. Move your original JPivot library from <i><biserver_home>/tomcat/webapps/WEB-INF/lib</i> in a different directory (just to be safe). Unzip the archive you just downloaded and copy the extracted jar file to the location specified before. Restart Pentaho BI Server and then... here we go!!Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com10tag:blogger.com,1999:blog-4547836498257352074.post-46076583764338164502009-11-27T14:36:00.000+01:002009-11-27T14:36:51.330+01:00Accessing Mondrian cubes through Pentaho Report DesignerToday I had the opportunity to design some complex reports using the cubes published in my customer's Pentaho BI Server as datasources for my report. Using a cube as a datasource to produce reports is good, in my opinion, because it gives you a perfect way to make easy reporting whenever you've, for example, to produce reports that compares data on different periods.<br />
<br />
A good sample for what we're going to discuss here is the Top N Analysis report you can find in the Pentaho's Steel Wheels samples. For abbreviation I'll refer to this report simply as <i>"the sample"</i>. If you open it and have a look at the defined datasources you can clearly see that it takes the data it needs from <i>steelwheels.mondrian.xml</i> cube schema. So that is good for us.<br />
<br />
<b>Publish the report to Pentaho BI Server </b><br />
<br />
Before thinking about the publishing of your report to your bi-server running instance, you've to think about the way Pentaho will use to access the schema it needs for your report. The strategy used by the reporting plugin goes through two possible path:<br />
<br />
<ul><li>Firstly it tries to access the Analysis Schema file using the path you've specified in the datasource definition of the <a href="http://wiki.pentaho.com/display/Reporting/Pentaho+Reporting+Community+Documentation">Pentaho Report Designer</a>. Whenever you're in the Pentaho BI Server execution environment every path is calculated respect to <i><BISERVER_HOME>/tomcat/bin</i>. That means that if you set a reference to your Analysis Schema cube as a relative path in your report datasource definition (as is for the sample I mentioned in my opening) Pentaho will look for you schema file calculating the absolute file path respect to <i><BISERVER_HOME>/tomcat/bin</i>. So you need to be sure that your file is in the right place before the system will try to access it. I think that this way is not as good because is dependent on your BI Server filesystem layout.</li>
<li>Secondly it tries to access the schema as an XMLA datasource. That is, in my opinion, the more elegant way to make the schema available to the reporting engine.</li>
</ul><br />
<b>How to add a new XMLA datasource to Pentaho BI Server</b><br />
<br />
To define a new XMLA datasource in our Pentaho BI Server environment we've to update the <i>datasources.xm</i>l file in <i><BISERVER_HOME>/pentaho-solution/system/olap</i>.<br />
<br />
This file contains the definitions of all the XMLA datasources available in the system. We can add a new datasource definition using one of these two ways:<br />
<ol><li>Manually add a new <i>Catalog</i> element to configure a new <a href="http://mondrian.pentaho.org/">Mondrian</a> catalog</li>
<li>While publishing the Analysis Schema from the Schema Workbench flag the <i>Enable</i> <i>JNDI datasource</i> and set the <i>JNDI Data Source</i> field appropriately. You can find the procedure to publish the schema clearly explained in <a href="http://wiki.pentaho.com/display/ServerDoc1x/Publishing+an+Analysis+Schema+Using+Schema+Workbench">Pentaho's wiki</a>. <br />
</li>
</ol>Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com0tag:blogger.com,1999:blog-4547836498257352074.post-65946088988057349922009-10-29T12:51:00.000+01:002009-10-29T12:51:53.447+01:00Mondrian cubes debugging: how to display SQL queriesThese days I've got the interesting need to look at the queries that <a href="http://mondrian.pentaho.org/">Mondrian</a> generates while the user is navigating the OLAP cube. This idea came to me when I decided to the check if the indexes applied to my tables gives me the best performances possible. To decide which indexes are eligible to be applied to my tables, my strategy is<br />
<ul><li>collect some queries and then </li>
<li>get the query plan of each query and check if the indexes are properly applied.</li>
</ul><br />
It's really useful to look at Mondrian log files because they gives us a lot of useful informations about how our system is behaving. We can<br />
<ul><li>look at sql statements and MDX queries,</li>
<li>have some profiling informations on queries that are executed,</li>
<li>get other useful debugging informations.</li>
</ul>The following paragraphs illustrates how to enable Mondrian debugging logs, adding some properties to the Mondrian configuration file.<br />
After that, we'll configure two new log4j appenders to have the desired log files properly written on our filesystem.<br />
<br />
<b>Enable Mondrian debug log</b><br />
Mondrian has a big set of configuration settings that can be modified. In our case, to enable Mondrian debug informations follow the steps detailed below:<br />
<br />
Open the <i>mondrian.properties</i> file located in <i><bi-server_home>/pentaho-solution/system/mondrian</i> and add the following line.<br />
<blockquote style="font-family: "Courier New",Courier,monospace;">mondrian.rolap.generate.formatted.sql=true<br />
</blockquote>You can find the complete set of configuration settings <a href="http://mondrian.pentaho.org/documentation/configuration.php">here</a><br />
<br />
<b>Update log4j configuration</b><br />
At this point we're going to modify the log4j configuration file adding the required appenders abd categories to have our logging informations displayed properly<br />
<br />
Open the log4j.xml file located in <i><bi-server_home>/tomcat/webapps/pentaho/WEB-INF/classes</i><br />
<br />
Based on what you want to log, add the one or each of the following lines to the file. They will create two new RollingFileAppenders. You're free to use <br />
the kind of appender you prefer. In case you need further informations about log4j and its configuration parameters you can have a look at <a href="http://logging.apache.org/log4j">here</a><br />
<b>IMPORTANT:</b> The location of the produced files is relative to the <i><bi-server_home>/tomcat/bin directory</i>. You can put the generated log files wherever you <br />
want in the filesystem but always remember this important consideration.<br />
<br />
<blockquote><br />
<span style="font-family: "Courier New",Courier,monospace;"><!-- Add the following appender only if you're interested in logging SQL statements --></span><br />
<div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="File" value="sql.log"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="Append" value="false"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="MaxFileSize" value="500KB"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="MaxBackupIndex" value="1"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <layout class="org.apache.log4j.PatternLayout"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> </layout><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> </appender><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <!-- Add the following appender only if you're interested in logging MDX statements --><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <appender name="MONDRIAN" class="org.apache.log4j.RollingFileAppender"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="File" value="mondrian.log"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="Append" value="false"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="MaxFileSize" value="500KB"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="MaxBackupIndex" value="1"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <layout class="org.apache.log4j.PatternLayout"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> </layout><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> </appender><br />
</div><br />
</blockquote><br />
Add the following new categories to the<i> log4j.xml</i> file according to your logging needs.<br />
<br />
<blockquote><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <!-- and logs only to the SQLLOG --><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <category name="mondrian.sql"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <priority value="DEBUG"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <appender-ref ref="SQLLOG"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> </category><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <!-- and logs only to the MONDRIAN --><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <category name="mondrian"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <priority value="DEBUG"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> <appender-ref ref="MONDRIAN"/><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> </category> <br />
</div></blockquote><br />
<b>Enable the new log settings</b><br />
To have the new log settings enabled restart the Pentaho bi-server instance. Remember, as soon as you satisfied your debugging needs, to disable the tracing logs because they have a severe impact on system performances.Sergio Ramazzinahttp://www.blogger.com/profile/17602567469759786999noreply@blogger.com1