Diethard Steiner on Business Intelligence

Comparison of resource sharing features in open source reporting

2012-01-21T09:52:00.001-08:00

Comparison of resource sharing features in open source reporting tools

In large scale reporting projects sharing resources is key for efficiency. This article tries to compare the three most popular open source tools to understand to which extend their feature set supports the various resource files that are likely to be shared.

As an example: Maintain style definitions in a centralized global file. Instead of having to change a particular style in all the reports, this can simple be done in one global file. The clear advantages are: (1) It saves a lot of time and (2) consistency: every style is defined the same way for all reports and it is not as error prone as changing the style in every report.

Apart from styles there are several other report properties which can be maintained in global external files to achieve a similar effect.

Book review: Agile Analytics: A Value-Driven Approach to Business Intelligence and Data Warehousing: Delivering the Promise of Business Intelligence

2012-01-08T02:55:00.000-08:00

A high percentage of classic waterfall model business intelligence projects are failing. One of the main reasons for this is that the waterfall model is a sequential model: Simply speaking, you plan first, then develop, then test etc and then the whole project is supposed to be finished. BI project normally span several months. During this time, requirements change, the priority of the requirements might change, the understanding of the client in regards to what BI actually is changes, etc. A classic waterfall model cannot accomodate these changing requirements easily over time as planing was done in the first step only.
With "Agile Analytics: A Value-Driven Approach to Business Intelligence and Data Warehousing: Delivering the Promise of Business Intelligence" Ken Collier introduces agile methodology to BI projects. Readers new to the agile methodology will find a detailed introduction (i.e. What are user stories? How do I conduct an agile project?), learn about the iterative cycles (sprints) which allow feedback driven development, various other approaches like test driven development, continuous integration and much more. Ken Collier also introduces the Message Driven Warehouse which among other benefits allows to easily and quickly implement new requirements.
In a nutshell, it is one of the best books on BI which I read last year, so I can only highly recommend it.

2011-11-27T08:01:00.001-08:00

Jasperwave - the other JasperReports Designer
It's always good when there is new competition on the field: Jasperwave is a recent project on creating a very user-friendly JasperReport Designer. Currently it is at version 0.9.2 and available both as Eclipse plug-in as well as standalone client from here.
I have to say that for such an early version it is already a very impressive offering. All the basic functionality is covered, sub-reports are partially implemented and support for tables and crosstabs is coming with the next release.
Overall, this report designer shows huge potential and I am looking forward to the next release! In the meantime, check out the current version here.

Jaspersoft iReport: How to pass a parameter to a sub-dataset

2011-11-19T11:06:00.001-08:00

Jaspersoft iReport: How to pass a parameter to a sub-dataset

Let’s say our main report is grouped by continent and the details band holds sales figures for each country. We would like to add now a pie chart to the group footer to visualize the share by product by continent.

Imagine our main report query is:

SELECT
product_sales.`continent` AS product_sales_continent,

product_sales.`city` AS product_sales_city,

     product_sales.`product` AS product_sales_product,
     product_sales.`sales` AS product_sales_sales
FROM
    `product_sales` product_sales
ORDER BY
1, 2, 3

To add a sub-dataset right click on the report root element and choose New Dataset:
Follow the wizard instructions and insert a query. In our imaginary case it is something like this:

SELECT
    product_sales.`product` AS product_sales_product,
    SUM(product_sales.`sales`) AS product_sales_sales
FROM
    `product_sales` product_sales
GROUP BY 1
ORDER BY 1

You must have noticed that there is something missing: This is of course not our final query, we want to restrict the results by continent. In order to do that, we have to first pass the parameter to the sub-dataset.

Now let’s have a look at how this is done:

Create the parameter within the sub-dataset: Give it a name in the properties panel and make sure that the parameter class is the same as for the parameter that you want to pass from the main query. Also, untick Use as a prompt:
Now add a chart to the design area. Right click on it and choose Chart Data:
Specify all the standard settings for the chart (Connection etc), then click on Parameters and Add. From the top pull down menu Dataset parameter name choose the sub-dataset parameter we just created before. Click on the button next to Value expression: You can now choose from the parameters, fields and variables of the main dataset. In our case, we just choose the continent field:
Apply, Click Apply, OK, and Close.
Now we can finally change our SQL query in a way that it looks like we originally intended it to be. Therefore, right click on the dataset and choose Edit Query:
Now add a WHERE clause to the query like this one:WHERE
product_sales.`continent`=
From the right hand side, you can drag over the parameter. It should then look like this:OK.

For your reference, please find the whole last SQL query below:
SELECT
    product_sales.`product` AS product_sales_product,
    SUM(product_sales.`sales`) AS product_sales_sales
FROM
    `product_sales` product_sales
WHERE
product_sales.`continent`=$P{CONTINENT_CHART}
GROUP BY 1
ORDER BY 1

Now everything will be working as intended. The parameter will now be passed from the main report to the chart (which is kind of a hidden sub-report). This method works for Lists, Tables and Crosstabs as well.

Star Schema Modeling with Pentaho Data Integration

2011-11-04T15:41:00.001-07:00

Star Schema Modeling with Pentaho Data Integration

Tutorial Details

Software (this tutorial was published on 2011-11-04 and download sources were correct on this date but might change in future):

pdi-ce-4.3.0-M1 (download here)
Star Modeler plug-in (download here)
MySQL or similar database
Star Schema file produced in this tutorial (download here)

Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described)

Introduction

Matt Casters, lead developer of PDI, presented a new feature this week for modeling star schemata within PDI. It’s in the very early stages, but already showing huge potential and this is the reason why I thought I prepare a quick tutorial about it.

Now why is this new feature so noteworthy:

You can create your star schema model within the same interface as you create your ETL process, report models, analyzer models and which allows you to analyze your data as well. So, now it’s really like a start-to-finish environment.
Other features can make use of the metadata you specified within the Star Modeler plug-in! In fact, one button click and PDI automatically generates a job which will run the DDL against the target database. Another click, and a simple ETL transformation gets automatically generated to populate your dimensions. You can use the transformation as a starting point and further improve it if necessary. In future you will be also able to auto-generate the reporting (Pentaho Metadata) model and the Mondrian (Pentaho Analysis) model. By how much will this speed up your development process? I would say a lot!
From my point of view Matt also included some really nifty features, in example, when you specify a table as date dimension, you have the possibility to let PDI auto-generate one for you. The source data to target data mapping is also quite nicely integrated.

The plug-in architecture of PDI allows to add new features quite easily. The Star Modeler can be downloaded from here (status: 2011-11-04). Unzip the file in the PDI plug-ins folder and (re)start Spoon. The Star Modeler will now show up as an additional perspective on the top right hand side.

Note: As said, this feature is still in development, so not everything might work as expected.

In this tutorial we will be working with an extremely simple example. The idea is not to generate a full blown star schema, but to demonstrate the features of the Star Modeler plug-in.

Getting Ready

Let’s create our source tables, so that we have some data to work with. Run the following SQL statements in your favourite SQL client:
CREATE SCHEMA
source_db
;
USE
source_db
;
CREATE TABLE
revenue
(
date DATE,
country_iso_code VARCHAR(3),
revenue DECIMAL
)
;
INSERT INTO
revenue
VALUES
('2011-11-01','GB',22314),
('2011-11-02','GB',23411),
('2011-11-03','GB',22325),
('2011-11-04','GB',22233),
('2011-11-01','US',32423),
('2011-11-02','US',25325),
('2011-11-03','US',43523),
('2011-11-04','US',23453)
;

CREATE TABLE
Countries
(
country_iso_code VARCHAR(3),
country_name VARCHAR(100)
)
;
INSERT INTO
Countries
VALUES
('GB','United Kingdom'),
('US','United States of America')
;

Now let’s create our target database:

CREATE SCHEMA
target_db
;

Our aim is to create this rather simple star schema:

Defining Database Connection Details

Start Spoon. Currently the Star Modeler sources database information from the shared.xml file. So if you haven’t locally share any of your database details yet, create an empty transformation and specify two database connection:

source_db
target_db

Click on the View tab and right click on Database Connections. Click on New then. Fill out all the details for both connections.

Once done, right click on them and choose Share:

Note: There will be a better way to define database connection details to be used with the Star Modeler in future.

Save your transformation.

How to Create a Star Model

Now that we have the connection details defined, let’s click on the Star Models perspective in the right top hand side corner:

You will be greeted by a blank screen. Now click on the New icon and choose Star Model:

Give the star model a name and description. Then choose target_db as our target database.

Click on Create New Model and fill out the form as shown below:

Creating Dimensions

Next click on the Dimensions tab and click on New Dimension:

Let’s create our date dimension: Fill out as shown below. Make sure that you choose DATE as Dimension Type.

Now click on the Attributes definitions tab and then on Add default dimension fields:

You will realize that PDI proposes a quite complete date dimension structure out-of-the-box. What a time saver! We are for now quite happy with this (if you want, you can change this), and click on OK.
So we have now defined our date dimension. Let’s go ahead and work on our country dimension:
Click on New Dimension:

Define the following (Make sure you choose SLOWLY_CHANGING_DIMENSION for Dimension Type):

Next click on the Attributes definitions tab and click on Add default dimension fields:
If you are familiar with Ralph Kimball’s slowly changing dimensions, you will realize that PDI makes really good suggestions. Just change:

the Physical names (replace the ??? with real names)
the natural key to country_iso_code (data type: string) and add country_name as additional attributes.
specify the Source DB, Source table and Source column for country_iso_code and country_name.

It should look then like this:

Click OK.

Creating a Fact Table

In the Star Model window click on the Fact tab and click on Add dimension keys. Now PDI automatically insert the technical keys of the dimensions we defined before.
Add an additional attribute called revenue, provide the details as shown below (make sure you also specify the source):

Our fact table is now properly defined.

Click on the Start model info tab and you will now see a simple graphical representation of our star model:

Click OK.

Automatic Generation Features

Now that our star model is defined, PDI gives us the option to automatically generate the following by just the click on a button:

SQL DDL Job

This job will allow you to automatically create the DDL for the target database. Just click on SQL Job and a second later you will see a job like this:

Update Dimensions (Domain Job)

By clicking on Domain Job PDI will generate a simple transformation to update your dimensions:

Pentaho Metadata Model (Physical Model)

This feature is currently under development.

Pentaho Analyzer Model (Mondrian Schema)

This feature is currently under development.

Documentation

This feature is currently under development.

Conclusion

I hope that this simple tutorial demonstrated the huge potential of this new feature. It will certainly get better and better over time, so watch the space!

Pentaho Data Integration 4 Cookbook - Book Review

2011-07-30T02:37:00.001-07:00

Book Review: Pentaho Data Integration 4 Cookbook

Pentaho Data Integration 4 Cookbook by María Carina Roldan and Adrián Sergio Pulvirenti illustrates with plenty examples how data can be imported from various sources, transformed and exported to various sources with the open source tool Kettle (Pentaho Data Integration). It's all a hands-down approach: Follow the step by step guide of the examples and you will have the right knowledge at the end of the book to create your own data integration solutions. The book is easy to read even for novices and offers a great introduction to the world of Pentaho Kettle.

In times of economic downturn and an extremely competitive market companies are trying to save money where ever possible. In the last few years we have seen a trend towards open source business intelligence solutions. The quality and functionality of open source business intelligence tools has increased over time as well and nowadays they are a very convincing alternative to very costly commercial business intelligence solutions.

For a lot of open source business intelligence tools, documentation has been a weak point in the past. Some products have a really strong community following and maybe a Wiki, which were often you main source of information. In the last 2 years this situation changed: There are now some publications available that cover some of these open source business intelligence packages, one of the very recent one being Pentaho Data Integration 4 Cookbook by María Carina Roldan and Adrián Sergio Pulvirenti.

Data Integration (ETL, short for extract, transform, load data) is one of the most important building blocks of a business intelligence solution. You can have the fanciest dashboards and reports, but if you don't have the correct data for them, they are pretty useless. ETL involves sourcing that data from a database, text file, Excel spreadsheet etc, transforming the data and outputting it to any convenient file format or database. Most ETL tools offer a graphical user interface which allows you to create data flows. The beauty of this is that there is hardly any coding involved, it is easy to understand (as you are looking at a flow diagram) and you are working quicker as well (you are not coding everything). Nowadays, the probably two most popular open source ETL tools are Pentaho Data Integration and Talend.

Pentaho Data Integration 4 Cookbook is an ideal book for somebody who looks for a hands-down tutorial-style book. It is filled with an abundance of examples and you can download the accompanying files from the dedicated website as well. Now what does this book cover:

The first chapter is all about how to get data from databases, parameterizing SQL queries and updating/deleting records from database tables. Especially interesting is the section about using parameters with your SQL query to dynamically source your data. The authors also explain how you can generate primary key values for your dataset in Kettle in case your database table requires them.

The second chapter focuses on getting data from text files. Rich examples are provided to handle structured as well as instructed text files. Especially the section about working with various forms of unstructured text files is really informative. The last section of the chapter is all about retrieving data from Excel spreadsheets. Chapter 3 focuses on working with XML files: Kettle offers features to read, validate and generate XML documents.

Kettle is not only an ETL tool, but also offers workflow management as well: This book gives a good overview on how to create jobs, which allow to run transformations and other tasks (e.g. send email email notifications, create folders, check if a file exists, etc) in sequential order. File management is covered next, which looks at copying, moving and deleting files and folders. Once you imported the data, you also might have to look up some additional data from other sources, so chapter 5 gives you a good overview on this topic. There is really one nifty example: It is about transforming data about museums and looking about the opening hours by using a web service.

Up next: Learn how to split, merge and compare data streams. Chapter 7 shows you how to execute and reuse jobs and transformations. Then it's all about how you can integrate your Kettle ETL processes with the other Pentaho products (Business Intelligence Server, Report Designer, Dashboards etc). The final chapter learns you the tricks on how get the most out of Kettle.

All in all, this book is in excellent read for somebody who wants to have a quick start in open source ETL process design. The book is fully based on examples, which explained in an easy to understand language. There are many illustrations as well and at the end of each example you will find a summary and sometimes hints on where to find more info. This book is ideal for somebody who wants to get up and running with this popular open source ETL tool in a short amount of time. If you interested in this book, you can get it directly from here.

Metadata Driven ETL and Reporting

2011-07-12T06:19:00.001-07:00

Metadata Driven ETL and Reporting with Pentaho Data Integration and Report Designer

Tutorial Details

Software: If PDI Kettle 4.2 GA and PRD 3.8.1 GA or later are available, download them from Sourceforge, otherwise here: PDI/Kettle 4.2 latest stable version and PRD 3.8.1 latest stable version (Note: Do not use these last stable versions in a production environment)
Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described)
OS: Windows, Linux or Mac OS X
Tutorial files can be downloaded here

Acknowledgment
Introduction
Creating a Metadata Driven Report

Defining the query
Defining the report pre-processor
Designing the report

Together we are stronger: Pentaho Data Integration and Pentaho Report Designer for total flexibility

Metadata driven ETL
PDI Data Source in Pentaho Report Designer
Room for improvement

Working with fixed width MDX output in Pentaho Report Designer

Changing the Report Designer settings
Defining a query

Working with dynamic MDX output in Pentaho Report Designer
Conclusion

Acknowledgment

I would like to thank both Matt Casters (Chief Data Integration, Pentaho) and Thomas Morgner (Chief Reporting, Pentaho) for providing me answers on all the questions that I had on this interesting topic.

Introduction

This tutorial will demonstrate how you can create extremely flexible data processing and reporting pipelines by using the metadata approach.

So what does Metadata Driven mean? Imagine you source various data fields from text files or a database, transform the data and then output a final pixel perfect report with summarized data. What happens if a new field shows up in the source data and it has to be added to the data processing and reporting pipeline? In the past this meant that you had to manually add this new field to nearly every step of your pipeline (change the ETL transformation and report template directly, or better said, hard code everything). Metadata driven means that you do not directly change ETL and reporting files, but only supply the name of the fields that have to be processed (and any other metadata).

This article focuses on three very interesting features in the Pentaho BI Suite:

Metadata driven ETL processes with Pentaho Data Integration (Kettle)
Index column reports in Pentaho Report Designer (PDR) which allow extremely flexible report definitions and can utilize the metadata provided by the data query
Pentaho Report Designer can source data from a Kettle transformation … now this is an extremely powerful combination and opens up the door for a lot of new solutions

Now why do I think this is quite important:

Some projects ask for a lot of flexibility in regards to ETL and reporting. New data points should be picked up ideally automatically and pushed to the report output. Now, by using Kettle and PDR together, you can even do this for real-time-reporting.
Another example is template driven ETL. As a developer you can create ETL processes that you can easily reuse for a lot of projects without inventing the wheel over and over again.
Using MDX output in PRD was a bit difficult at times. Imagine a scenario where you had to present the daily performance of the last 10 days. In MDX you can easily use a function to display the last 10 days. The result set might have the dates as columns, which is inconvenient, as they will always change. Hence so far you had to give each date column an alias (i.e.: “date_10_days_ago”) which would not change. Using an index column in PRD is extremely convenient, because you don’t have to worry about this complexity any more
… and many more …

Certainly, this approach has its limitations. Not everything can just happen automatically, hence you have to consider carefully in which scenarios this approach will work well.

Creating a Metadata Driven Report

Defining the query

Open Pentaho Report Designer (PRD) and create a new report. Let’s set up a data source:

Click on the Data tab
Right click on Data sets and add a query (follow all the necessary steps)
Click Ok
Copy your query somewhere in a text editor or so to have it handy, then delete the data source in PRD
So now there shouldn’t be anything under Data Sets. Right click on Data Sets and choose Advanced > JDBC (Custom). In the settings dialog choose your database connetion details and then click OK.
In the data tab, create a new parameter called Query (type: string)
Click on the Structure tab and mark the Master Report.
Click on the Attributes tab and click on the query name value field. Insert your query here.
Click on the query name formula icon (+) and insert following open formula: =[Query]

Do not drag and drop any fields onto the details band. Do not put any fields at all on the details band or the details header or footer! If you want to auto-generate the reports on the fly you have to use a scripted pre-processor to configure the fields based on the structure of the result set returned by the query and then the report design wizard generates the fields and formats for the report.

Thomas Morgner explains:

“A report-pre-processor is a specialized function that is executed after the report processing queried the data from the data-factory, but before the report's functions get initialized and the actual report-processing starts. Each master- and sub-report can have its own set of report-processors. The pre-processor is executed only once for each report-definition. At this point in the processing chain, a pre-processor has full control over the layout and data-computation aspects of the report-definition. The pre-processors are defined on the "advanced::pre-processor" attribute of the "master-report" object.”

More info can be found on the Pentaho Wiki.

Defining the report pre-processor

Click on the Structure tab and mark the Master Report
Click on the Attributes tab and scroll down to the wizard section
Set wizard-enabled to true
Scroll down to the advanced section
Click on the value field of pre-processor
Add a Relational Report Auto-Generator and Wizard Processor

Note: You can have a look at the code of the Relational Report Auto-Generator by clicking twice on the name in the above shown dialog, then on the right hand side a "..." button will show up. Click this one, another dialog will come up, click on the value field of the script row and you will be shown the whole script. If you want, you can edit it here.

PRD includes some examples of report-pre-processors:

Flat output (RelationalAutoGeneratorPreProcessor): Places the fields in the detail band and the labes in the detail header
WizardProcessor: Extended version of the one before, can be used as well to only refresh the metadata of predefined fields
Most features (BSFReportPreProcessor): Use a scripting language to manipulate various settings

Again, these are only examples, you can build your own report-pre-processor.

The code for the report-pre-processor is stored as a expression-property within the report definition. The code uses the wizard-API to define the fields and then the wizard-process generates the fields. With this approach you can do whatever you want with the (GUI)-wizard itself.

In case your are aiming for really fancy reports, you can add another report-pre-precessor after the wizard processor to post-process the fields generated by the wizard.

Designing the report

Expand the Structure tree and mark Details Header
Click on the Attributes tab and set hide-on-canvas to false
Do the same for the Details Footer
Apply any format that you wish to the Details Header, Details and Details Footer (do not place any elements in these areas). I would advice to set at least font-size, text-color, background-color and height.

Note: If you want to use bands, make sure you click on the Attributes tab, scroll down to the wizard section and set generate-content-marker to true. Normally, if you define fields within the band, the wizard wouldn’t be used as the fields are explicitly set. By enabling generate-content-marker this behaviour is overwritten (the wizard removes your fields and brings its own fields).

Sample design:

Now click on the preview button and write any SQL query in the parameter field, then click Update. Your report will be generated on the fly and will look something like this:

Note: This report setup is for illustration purposes only. I strongly advice to be very careful with allowing users to directly enter queries as this is a good target for SQL injections. Somebody could just enter a DELETE statement and execute it. So make sure you have the right security in place in case you really have to use this setup.

Someone unfamiliar with Pentaho Report Designer might wonder what is metadata driven in this example? Well, normally we would define all the fields in the details header, details and details footer bands. Here we didn't define them at all, but relied on Pentaho Report Designer to make use of the query metadata and display the fields accordingly. This could be achieved by using the report pre-processer followed by the wizard functionality.

Together we are stronger: Pentaho Data Integration and Pentaho Report Designer for total flexibility

In this section I will try to highlight future potential of a metadata driven approach. Currently the tools don't have all the necessary features, hence I will point out at the end what new features should be ideally added.

Metadata driven ETL

Now that we know how to set up a metadata driven report, we can have a look at creating a metadata driven ETL process to supply data to our metadata driven report template.

PDI (Kettle) only recently received a new transformation step called ETL Metadata Injection. The idea is to have one transformation that acts as a metadata injector to the main transformation. This main transformation is a skeleton of an ETL process: All the steps are defined, but not all or most of the metadata.

Currently not all transformation steps support metadata injection. Find below a list of steps that currently work with the ETL Metadata Injection step (status: July 2011):

CSV Input
Excel Input
Select Values
Row De-normalizer
Row normalizer

So right now, the possibilites are quite limited, but this list will be growing over time as people start submitting requests on the Pentaho JIRA website. So if you already have an idea as to which transformation step should have metadata injection support, visit the aforementioned website and submit a JIRA feature request (ideally check first if there is not already one set up).

If you use the Metadata Injector step and point it to the main transformation, it will show which steps are supported.

As Matt Casters put it: "The golden rule is: if the step doesn't show up, it's not supporting metadata injection yet".

We will be looking at an extremely simple example (tr_metadata_injection_main.ktr):

Image in we have a dataset which looks like this one:

Our ETL process should pivot this data in this way:

Now if our original dataset contains only a static number of product names (in our case: electronics, consulting), then we can easily set up a predefined ETL process. But what if the number of product names might change in future?

So far we only had the option to amend our ETL process manually, which means we had to edit our Kettle transformation and add the new name(s) to the Row denormalizer step. This was rather inconvenient as ideally this should have been picked up automatically. The great news is, that now this is possible with the ETL Metadata Injection step.

Create following table on your database of choice (amend if necessary):

USE

test

;

CREATE TABLE

product_sales

(

city VARCHAR(20),

continent VARCHAR(20),

product VARCHAR(20),

sales INT(255)

)

engine=innodb

;

INSERT INTO

product_sales

VALUES

("city1","Europe","electronics",1234),

("city2","Africa","consulting",1242),

("city3","Asia","electronics",23412),

("city4","South America","electronics",342),

("city5","North America","consulting",1243),

("city6","Europe","consulting",45432),

("city7","Asia","electronics",4332)

;

Now let's create our transformation based on the screenshot below:

This transformation will query data from the table we just created. The Select values step allows use to keep only the required fields (It's not really useful in this example, but mainly here for demonstration purposes). Next the data goes into the Row denormalizer step, which basically pivots our data.

To test this transformation I suggest adding a Text file output step and filling out all the required settings in all the steps. Once this works, change the settings of the relevant steps and hops so that they look like the ones shown in the screenshot above. As you can see, we leave the Select values step completely unconfigured and for the Row denormalizer step we only define the Key and Group by fields. Everything else we will configure by using the ETL Metadata Injection step.

We create a second transformation (tr_metadata_injection_config.ktr) to supply the metadata to our main transformation:

First off we have to get the unique product names in order to pivot the data correctly. We use a Table input step therefore with following query:

SELECT

DISTINCT(product) AS product

FROM

product_sales

;

For this stream we also add some additional fields to have all the metadata complete for injection to the Row denormalizer step in our original transformation.

We also use a Data Grid step to define the fields that we want to keep for the Select values step in our original transformation.

Now we can map our metadata with the steps of our original transformation:

Double click on the ETL Metadata Injection step and the settings dialog will pop up.
Click the Browse button to choose which transformation you want to inject metadata to.
Click Ok, then double click again on the ETL Metadata Injection step and you will see a tree-like representation of your main transformation. For each step you see all the settings that can be used by the metadata injection. You do not have to provide all of them though, but definitely the essential ones. If you are unsure as to what the essential once are, then just create a separate testing transformation and test which settings are necessary for a certain step to work properly.
For the settings that you want to provide metadata for, click on the Source step field in the respective row and a dialog will guide you through the mapping:
As we want to use the output of our original transformation in this current transformation, we also define the Row denormalizer step as Source step to read from (optional).

The last step Results (Add constant values step) is a hack: Currently there is no way to pass on the dynamic results of the ETL metadata injection to PRD. By using the Add constant values step, we can define the fields and field types that we expect to be return from the metadata injected transformation. In our case it will look like this:

At runtime the ETL metadata injection step will output fields of the same name and pass them to the Add constant values step. As this last step adds field that already exist in the input stream, these field names will be automatically to _1 since they are duplicates.

Note: You execute the original transformation by running the metadata transformation. So in our example, you would run tr_metadata_injection_config.ktr.

So let's test our setup: Mark the Result (Add constant values) step in tr_metadata_injection_config.ktr and click the Preview button. The output will look like this:

PDI Data Source in Pentaho Report Designer

Now that our metadata driven ETL process is running successfully, the next step is to use this data directly for the report output. In Pentaho Report Designer we can define a PDI transformation as a data source:

Open Pentaho Report Designer
Create a new report
Click on the Data tab
Right click on Data Source and choose Pentaho Data Integration
In the dialog click on the (+) button to specify a new query
Click on Browse to find the metadata configuration transformation we just created
Mark the Result step and click Preview
Click Close followed by OK
Define the report-preprocessor and wizard as described in the Defining the report pre-processor
Design the report as described in Designing the report
Preview the report. It should now look something like this:

Room for improvement

As you can see, the only problem currently is that there is no way to pass the dynamic Kettle dataset to PRD - that is why we had to use a work-around. If a new product name shows up, we still have to change the Result (Add constant values) step in our example. Now imagine the possibilites if a future version of PRD would accept dynamic Kettle datasets: In our example we would not have to change anything. The new product name would automatically be passed on to the report output.

Let's add a new product name to our table (use your favourite SQL client):

INSERT INTO

product_sales

VALUES

("city1","Europe","photography",342)

;

In Kettle (Spoon), mark the ETL Metadata Injection step and click on Preview. You will see that our new product name was automatically added to the result set:

The problem now is that we have to manually at this new field name to the Result (Add constant values) step in order for PRD to be able to read it.

The main improvement should happen in PRD to allow dynamic PDI input. PRD already allows dynamic input for most other data source, so adding this feature for PDI data sources should not be a lot of work.

Working with fixed width MDX output in Pentaho Report Designer

We will now have a look at how to easily work with a fixed width MDX result set in Pentaho Report Designer. As mentioned before, sometimes you might have a result set with changing column names. Imagine we have a daily summary for the last 10 days. Each column name has the actual date. Now if I run the same query tomorrow, the column names will have changed. PRD has a feature called index column: So instead of referring to the column names, it allows you to refer to the column position.

For this example here I will reuse an existing PRD example query so that everyone can follow easily:

Changing the Report Designer settings

Before we can get started, we have to enable a special setting. Open up the Pentaho Report Designer and go to Edit > Settings > General and enable Display the index columns in the Report-Designer’s field selectors:

Defining a query

Create a new report
Click on the Data tab
Right click on Data Source and choose OLAP > Pentaho Analysis
Define the OLAP schema file: steelwheels.mondrian.xml. This file should be in the same location as this report.
Mark following database on the left hand side: SampleData. This database comes with PRD, so you don't have to worry about anything here.
In the dialog click on the (+) button to specify a new query (copy the query shown after this numbered list).
Click Preview
Click Close followed by OK
Notice that apart from the standard fields the index fields are shown as well in the Data Sets overview:
Now we can just drag and drop these index fields on the canvas as we would do with standard fields.
Drag and drop the index fields in the details band.
Do exactly the same for the details header. Mark all of the details header fields and choose Format > Morph > label.
Mark all the index fields (in the details band and in the details header) in the Structure tree using CTRL, go to the Attributes tab and enable following attributes for query-metadata:
data-format: true
style-format: true
For each index field in the details header, set the labels-detail-header attribute respectively (i.e.: ::column::0)
If you wish, apply additional formats to the report
Preview the report. It should now look something like this:

MDX to copy (this query actually comes with one of the PRD examples):

with

set [TopSelection] as

'TopCount(FILTER([Customers].[All Customers].Children,[Measures].[Sales]>0), Parameter("TopCount", NUMERIC, 10, "Number of Customers to show"), [Measures].[Sales])'

Member [Customers].[All Customers].[Total] as 'Sum([TopSelection])'

Member [Customers].[All Customers].[Other Customers] as '[Customers].[All Customers] - [Customers].[Total]'

select NON EMPTY {[Measures].[Sales],[Measures].[Quantity] } ON COLUMNS,

{ [TopSelection], [Customers].[All Customers].[Other Customers]} ON ROWS

from [SteelWheelsSales]

where

(

strToMember(Parameter("sLine", STRING, "[Product].[All Products].[Classic Cars]")),

strToMember(Parameter("sMarket", STRING, "[Markets].[All Markets].[Japan]")),

strToMember(Parameter("sYear", STRING, "[Time].[All Years].[2003]"))

)

As we don't know the field values upfront, we also have to make sure that extremely long values can be handled gracefully. This is out of scope of this article.

Working with dynamic MDX output in Pentaho Report Designer

Now we want to actually go a step further and use dynamic MDX results. For this scenario we will not make use of index fields, but follow the same approach as before with the PDI data source. We use exactly the same query again, but theoretically you could now use any MDX query:

Open Pentaho Report Designer
Create a new report
Click on the Data tab
Right click on Data Source and choose OLAP > Pentaho Analysis
Define the OLAP schema file: steelwheels.mondrian.xml. This file should be in the same location as this report.
Mark following database on the left hand side: SampleData. This database comes with PRD, so you don't have to worry about anything here.
In the dialog click on the (+) button to specify a new query (copy the query shown after this numbered list).
Click Preview
Click Close followed by OK
Define the report-preprocessor and wizard as described in the Defining the report pre-processor
Design the report as described in Designing the report
Preview the report. It should now look something like this:

MDX to copy (this query actually comes with one of the PRD examples):

with

set [TopSelection] as

'TopCount(FILTER([Customers].[All Customers].Children,[Measures].[Sales]>0), Parameter("TopCount", NUMERIC, 10, "Number of Customers to show"), [Measures].[Sales])'

Member [Customers].[All Customers].[Total] as 'Sum([TopSelection])'

Member [Customers].[All Customers].[Other Customers] as '[Customers].[All Customers] - [Customers].[Total]'

select NON EMPTY {[Measures].[Sales],[Measures].[Quantity] } ON COLUMNS,

{ [TopSelection], [Customers].[All Customers].[Other Customers]} ON ROWS

from [SteelWheelsSales]

where

(

strToMember(Parameter("sLine", STRING, "[Product].[All Products].[Classic Cars]")),

strToMember(Parameter("sMarket", STRING, "[Markets].[All Markets].[Japan]")),

strToMember(Parameter("sYear", STRING, "[Time].[All Years].[2003]"))

)

The disadvantage of this approach is that the column headers are not particular nice to read ... but there might be a way to control this.

Conclusion

Exploring these advanced features of Pentaho Data Integration and Report Designer has been an extremely interesting journey. There are many more features available which couldn't be covered in this tutorial. I suggest that you take this information here as a starting point and explore all the possibilities if you have time.

Overall, if the next version of PRD brings dynamic PDI data source support, there will a huge potential available. Over the time, PDI will also add additional ETL steps for metadata injection and then our journey will even get more exciting!

Pentaho Data Integration 4 Cookbook

2011-06-28T03:03:00.000-07:00

A new book on Pentaho Data Integration is out: Pentaho Data Integration 4 Cookbook! I just received a review sample and I'll be posting a full review in the next few weeks.
Jump over to PacktPub to see the full details ...

Pentaho Report Designer: Creating a Bar-Line chart with secondary

2011-05-29T12:18:00.001-07:00

Pentaho Report Designer: Creating a Bar-Line Chart with Secondary Axis in Different Format

Tutorial Details

Software: PRD 3.8 (download here)
Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described)
Tutorial files can be downloaded here

Pentaho Report Designer offers a wider variety of chart types. Today we will look at the bar-line chart type. We will create a secondary axis and apply a different format on it. Our final chart will look like this one below (This is a chart created in 2 minutes, if you spend 2 minutes more, it will even look better):

I will not explain everything step by step but only focus on how to set up a chart which is based on data similar to the one shown below:

As you can see, the data set consists of impressions and click thru rate (CTR) broken down by option. In our chart, we want to display the impressions as bars and the CTR as a line. The only complexity that we have here is that CTR should be shown as a percentage whereas the impressions as standard integer values. So how does this work?

The most important point is that although in this case we only have one data set, for the chart we have to define two data sources, as our measures have different formats.

First we will define the settings for the bars (I only mention the very important points here):

In the Primary DataSource settings we only define [impressions] in the value-columns
The format can be defined with the y-tick-fmt-str

Thereafter, we click on Secondary DataSource:

We define [CTR] for the value-columns
Now we would expect something like y2-tick-fmt-str, but this one strangely enough doesn't exist. Instead, the format for the line has to be set in line-tick-fmt:

Provide all the other necessary details in the settings dialog and then the chart should be displayed perfectly.

Kettle: Sourcing data from Hadoop Hive

2011-05-26T13:50:00.001-07:00

Pentaho Data Integration (Kettle): Sourcing data from Hadoop Hive

Tutorial Details

Software: PDI/Kettle 4.1 (download here)
Knowledge: Beginner

Has your company recently started using Hadoop to cope with enormous amounts of data? Have you been using Kettle so far for your ETL? As you are probably aware of, with the Kettle Enterprise Edition you can now create map-reduce jobs for Hadoop. If you want to stay with the open source version, the good news is, that it’s very simple to connect to Hive - a database which can be set up on top of Hadoop.

If you have one of the latest versions of Kettle installed, you will see that it comes already with the required Hive driver. Hence, setting up a connection to Hive is straight forward.

Create a new database connection

Create a new transformation
Click the View tab on the right hand side
Right click on Database connections and choose New

Alternatively, you can also activate the Design tab and drag and drop a Table input step on the canvas, open it and click on New to create a new database connection.

In the database settings window choose Generic database from the available databases.

For the connection URL insert the following:

jdbc:hive://youramazonec2url:10000

For the driver specify:

org.apache.hadoop.hive.jdbc.HiveDriver

Depending on your setup you might have to provide other details as well.

Click Test and all should be working now.

If you want to use any specific settings or user defined functions (UDF), then you can call them as follows:

In the database settings window, click on Advanced in the left hand pane.
Insert these statements in the field entitled Enter the SQL statements (separated by ;) to exectue right after connecting
Click Test again to check if a connection can be created with these specific settings

Everything is set up now for querying Hive from Kettle. Enjoy!

Tutorial Aggregated Tables for Mondrian (Web)

2011-05-23T06:39:00.001-07:00

Tutorial Aggregated Tables for Mondrian
(Republished Tutorial - originally published July 2009)

For better readability please open this article as Google Document here.

Requirements
Know what you are up to

How to enable MDX and SQL logging on BI Server 3
How to configure Mondrian to recognize aggregated tables
Table Names
Column Names

Your first aggregated tables

Aggregated table for login channel
Aggregated table for country

Make use of aggregates in other programs

I've started using Pentaho BI Suite in early 2009. One of my main objectives was to create a multidimensional model for DSS (Decission Support Systems) and Analysis. As the BI Server already comes with Mondrian, it is fairly easy to start off.
I created my first cube definition with the Schema Workbench. The cube definition can be saved as an XML file. This XML file you can then export to your solution folder on the BI Server.
Soon we realized the we will need aggregated tables, as some MDX queries took rather long. I had a look at the Pentaho Aggregation Designer 1.1 and thought that this would be a rather easy task.
One thing the Aggregation Designer does is to check if your Schema (the cube definition xml file) is valid, it also checks if your tables are all properly set up, which is indeed a very good functionalilty, as it will show you an errors and you can correct them then.
I used the Advisor Function within the aggregation designer and created all the recommended tables with the DDL and DML (which the Aggregation Designer creates automatically) and exported the new Cube Schema as well.
But the problem was, that everything didn't work. The problem was that I thought that the Aggregation Designer would do everything, but I was quite wrong about that (at the time of the writing Aggregation Designer 1.1 was available), or maybe, I was just doing something wrong. After some unsuccessful approaches I decided to not use the Aggregation Designer, but to create everything from scratch. I am not recommending here that you shouldn't use the Aggregation Designer, it is definitely worth having a look at it. I am just describing here the approach that worked for me. I am also assuming that you use the standard configuration of Mondrian.

Requirements

This article assumes that you are familiar with

MDX
MySQL (or SQL in general)
Pentaho BI Suite
Pentaho Schema Workbench
Pentaho Design Studio

Know what you are up to

While creating aggregated tables it makes sense to test if they are used for the purpose that you are creating them for. You might have a MDX query that takes quite long to execute and hence you will want to create a dedicated aggregated table therefore. Have a look at the mondrian_sql.log to see which SQL query Mondrian actually creates for your MDX.
Your aggregated table will look quite similar to it.
When you execute the MDX queries, always have a look at these logs in the beginning, you will learn at lot!

How to enable MDX and SQL logging on BI Server 3

Whenever an MDX query is executed, Mondrian transforms it into a SQL query. In some cases you want to see what the resulting SQL query is, for example to check if Mondrian is using aggregated tables.

Go to \tomcat\webapps\pentaho\WEB-INF\classes and change log4j.xml. Just uncomment the last half of the file where it says:


   
   
   

   <appender name="MONDRIAN" class="org.apache.log4j.RollingFileAppender">
     <param name="File" value="mondrian.log"/>
     <param name="Append" value="false"/>
     <param name="MaxFileSize" value="500KB"/>
     <param name="MaxBackupIndex" value="1"/>

     <layout class="org.apache.log4j.PatternLayout">
       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
     </layout>
   </appender>

   <category name="mondrian">
      <priority value="DEBUG"/>
      <appender-ref ref="MONDRIAN"/>
   </category>

   
   
   

   <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
     <param name="File" value="mondrian_mdx.log"/>
     <param name="Append" value="false"/>
     <param name="MaxFileSize" value="500KB"/>
     <param name="MaxBackupIndex" value="1"/>
     <layout class="org.apache.log4j.PatternLayout">
       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
     </layout>
   </appender>

   <category name="mondrian.mdx">
      <priority value="DEBUG"/>
      <appender-ref ref="MDXLOG"/>
   </category>

   
   
   

   <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
     <param name="File" value="mondrian_sql.log"/>
     <param name="Append" value="false"/>
     <param name="MaxFileSize" value="500KB"/>
     <param name="MaxBackupIndex" value="1"/>
     <layout class="org.apache.log4j.PatternLayout">
       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
     </layout>
   </appender>

   <category name="mondrian.sql">
      <priority value="DEBUG"/>
      <appender-ref ref="SQLLOG"/>
   </category>

If you want the SQL to be displayed in a more readable form, add this to the mondrian.properties in pentaho-solutions\system\mondrian (BI Server 3):

mondrian.rolap.generate.formatted.sql=true

Restart the server, then start one xaction that uses Jpivot/MDX. In Tomcat/bin you will find a mondrian.log, mondrian_sql.log and a mondrian_mdx.log now.

How to configure Mondrian to recognize aggregated tables

Well, you have to tell Mondrian as well that aggregated tabels exist. The first step is to add following lines to the Mondrian.properties, which you can find on pentaho-solutions\system\mondrian (BI Server 3):

mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true

Restart the server.
Furthermore, with the standard setup the table names have to follow a convention (I've copied here a bit from Julian Hyde's excellent documentation):

Table Names

As an example of applying the aggregate table name rule, let the fact table be called sales_fact_1997, the Sales cube's fact table from the FoodMart schema. Applying the specific fact table name to the regular expression template creates the following regular expression:

agg_.+_sales_fact_1997

This will match the following table names:

agg_l_05_sales_fact_1997
agg_c_14_sales_fact_1997
agg_lc_100_sales_fact_1997
agg_c_special_sales_fact_1997
AGG_45_SALES_FACT_1997
AGG_drop_time_id_sales_fact_1997

Column Names

There has to be one column called "fact_count", which stores values of a standard count.
If there are any ID columns in the aggregated table, they have to be of the same name as in the fact or dimensional table.
Level columns:

${usage_prefix}${level_column_name}: If you have a level that is used more than once, i.e. Year in a week hierarchy and a month hierarchy within the time dimension, then set a "usagePrefix" in the Schema so that they can be distinguished. I.E. usage prefix "TW_" for year in the week hierarchy (resutls in "TW_Year") and "TM_" for year in the month hierarchy (results in "TM_Year").

${hierarchy_name}_${level_name}
${hierarchy_name}_${level_column_name}
${level_column_name}

If any of these parameters have space characters, ' ', these are mapped to underscore characters, '_', and, similarly, dot characters, '.', are also mapped to underscores. So, if the hierarchy_name is "Time", level_name is "Month" and level_column_name is month_of_year, the possible aggregate table column names are:

time_month
time_month_of_year
month_of_year

For this rule, the "hierarchy_name" and "level_name" are converted to lower case while the "level_column_name" must match exactly.

Make sure the column names are in small letters!

There are three parameters to matching aggregate columns to measures:

1) ${measure_name}: the fact table's cube's measure name, "measure_name",

2) ${measure_column_name}: the fact table's cube's measure column name, "measure_column_name", and

3) ${measure_column_name}_${aggregate_name}: the fact table's cube's measure's aggregator (sum, avg, max, etc.), "aggregate_name".

where the measure name is converted to lower case and both the measure column name and aggregate name are matched as they appear. If the fact table's cube's measure name was, "Avg Unit Sales", the fact table's measure column name is "unit_sales", and, lastly, the fact table's cube's measure's aggregate name is "avg", then possible aggregate table column names that would match are:

avg_unit_sales
unit_sales
unit_sales_avg

In example: A measure called "Distinct Users" has to be referenced as "distinct_users".

Your first aggregated tables

We have following tables:

The fact table:

DROP TABLE IF EXISTS `pentaho`.`sn_login`;
CREATE TABLE `pentaho`.`sn_login` (
  `msisdn` bigint(20) NOT NULL DEFAULT '0',
  `login_date_id` bigint(20) NOT NULL DEFAULT '0',
  `login_channel_id` bigint(20) NOT NULL DEFAULT '6' COMMENT 'Default Value Unknown',
  `sessions` int(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`msisdn`,`login_date_id`,`login_channel_id`),
  KEY `FK_sn_login_date_id` (`login_date_id`),
  KEY `FK_login_channel_id` (`login_channel_id`),
  KEY `login_msisdn` (`msisdn`),
  CONSTRAINT `FK_login_channel_id` FOREIGN KEY (`login_channel_id`) REFERENCES `sn_lt_communication_channels` (`communication_channel_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_login_msisdn` FOREIGN KEY (`msisdn`) REFERENCES `sn_user` (`msisdn`),
  CONSTRAINT `FK_sn_login_date_id` FOREIGN KEY (`login_date_id`) REFERENCES `sn_lt_time_dimension` (`time_dimension_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The time dimension table:

DROP TABLE IF EXISTS `pentaho`.`sn_lt_time_dimension`;
CREATE TABLE `pentaho`.`sn_lt_time_dimension` (
  `the_year` int(11) DEFAULT NULL,
  `the_week_of_the_year` int(11) DEFAULT NULL,
  `the_month` int(11) DEFAULT NULL,
  `the_day_of_the_year` int(11) DEFAULT NULL,
  `the_day_of_the_week` int(11) DEFAULT NULL,
  `the_day_of_the_month` int(11) DEFAULT NULL,
  `the_month_name` varchar(9) DEFAULT NULL,
  `tenure_years` int(11) DEFAULT NULL,
  `tenure_days` int(11) DEFAULT NULL,
  `time_dimension_id` bigint(20) NOT NULL DEFAULT '0',
  `the_date` date DEFAULT NULL,
  `the_date_as_string` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `the_quarter` int(3) DEFAULT NULL,
  PRIMARY KEY (`time_dimension_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The channel dimension table:

DROP TABLE IF EXISTS `pentaho`.`sn_lt_communication_channels`;
CREATE TABLE `pentaho`.`sn_lt_communication_channels` (
  `communication_channel_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `channel_name` varchar(45) NOT NULL,
  `channel_system_name` varchar(45) NOT NULL,
  PRIMARY KEY (`communication_channel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

The user dimension table:

DROP TABLE IF EXISTS `pentaho`.`sn_user`;
CREATE TABLE `pentaho`.`sn_user` (
  `msisdn` bigint(20) NOT NULL DEFAULT '0',
  `sex` char(1) DEFAULT NULL,
  `registration_date_id` bigint(20) NOT NULL DEFAULT '0',
  `country_id` bigint(20) NOT NULL DEFAULT '0',
  `birthday_date_id` bigint(20) NOT NULL,
  `registration_channel_id` bigint(20) NOT NULL,
  PRIMARY KEY (`msisdn`),
  KEY `msisdn` (`msisdn`),
  KEY `FK_birthday_date_id` (`birthday_date_id`),
  KEY `FK_registration_date_id` (`registration_date_id`),
  KEY `FK_country_id_2` (`country_id`),
  KEY `FK_registration_channel_id` (`registration_channel_id`),
  CONSTRAINT `FK_birthday_date_id` FOREIGN KEY (`birthday_date_id`) REFERENCES `sn_lt_time_dimension` (`time_dimension_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_country_id_2` FOREIGN KEY (`country_id`) REFERENCES `sn_lt_countries` (`country_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_registration_channel_id` FOREIGN KEY (`registration_channel_id`) REFERENCES `sn_lt_communication_channels` (`communication_channel_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_registration_date_id` FOREIGN KEY (`registration_date_id`) REFERENCES `sn_lt_time_dimension` (`time_dimension_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The country dimension table:

DROP TABLE IF EXISTS `pentaho`.`sn_lt_countries`;
CREATE TABLE `pentaho`.`sn_lt_countries` (
  `country_id` bigint(20) NOT NULL DEFAULT '0',
  `country_name` tinytext,
  `country_number` double DEFAULT NULL,
  `iso_country_code` tinytext,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We have created an XML Schema (Cube Definition that looks like this):

<Schema name="Test">
<Dimension type="TimeDimension" usagePrefix="TM_" name="Time Monthly">
<Hierarchy name="All Times Monthly" hasAll="true" allMemberName="All Times Monthly" allMemberCaption="All Times Monthly" primaryKey="time_dimension_id">
<Table name="sn_lt_time_dimension">
</Table>
<Level name="Year" column="the_year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
</Level>
<Level name="Quarter" column="the_quarter" type="Integer" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
</Level>
<Level name="Month" column="the_month" nameColumn="the_month_name" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
</Level>
<Level name="Day" column="the_day_of_the_month" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
</Level>
</Hierarchy>
</Dimension>
<Dimension type="StandardDimension" name="Country">
<Hierarchy name="All Countries" hasAll="true" allMemberName="All Countries" allMemberCaption="All Countries" primaryKey="country_id">
<Table name="sn_lt_countries">
</Table>
<Level name="Country" column="country_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
</Level>
</Hierarchy>
</Dimension>
<Dimension type="TimeDimension" usagePrefix="TW_" name="Time Weekly">
<Hierarchy name="All Times Weekly" hasAll="true" allMemberName="All Times Weekly" allMemberCaption="All Times Weekly" primaryKey="time_dimension_id">
<Table name="sn_lt_time_dimension">
</Table>
<Level name="Year" column="the_year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
</Level>
<Level name="Week" column="the_week_of_the_year" type="Integer" uniqueMembers="false" levelType="TimeWeeks" hideMemberIf="Never">
</Level>
<Level name="Day" column="the_day_of_the_week" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
</Level>
</Hierarchy>
</Dimension>
<Cube name="Users" cache="true" enabled="true">
<Table name="sn_user">
</Table>
<Dimension type="StandardDimension" name="Gender">
<Hierarchy name="All Genders" hasAll="true" allMemberName="All Genders" allMemberCaption="All Genders">
<Level name="Gender" column="sex" nameColumn="sex" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
</Level>
</Hierarchy>
</Dimension>
<Dimension type="StandardDimension" foreignKey="registration_channel_id" name="Registration Channel">
<Hierarchy name="All Registration Channels" hasAll="true" allMemberName="All Registration Channels" allMemberCaption="All Registration Channels" primaryKey="communication_channel_id">
<Table name="sn_lt_communication_channels">
</Table>
<Level name="Registration Channel" column="channel_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
</Level>
</Hierarchy>
</Dimension>
<Dimension type="StandardDimension" foreignKey="birthday_date_id" name="Age">
<Hierarchy name="All Ages" hasAll="true" allMemberName="All Ages" allMemberCaption="All Ages" primaryKey="time_dimension_id">
<Table name="sn_lt_time_dimension">
</Table>
<Level name="Age" column="age" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
</Level>
</Hierarchy>
</Dimension>
<DimensionUsage source="Time Monthly" name="Registration Time Monthly" caption="Registration Time Monthly" foreignKey="registration_date_id">
</DimensionUsage>
<DimensionUsage source="Country" name="Country" caption="Country" foreignKey="country_id">
</DimensionUsage>
<DimensionUsage source="Time Weekly" name="Registration Time Weekly" caption="Registration Time Weekly" foreignKey="registration_date_id">
</DimensionUsage>
<Measure name="New Registrations" column="msisdn" datatype="Numeric" aggregator="count" visible="true">
</Measure>
</Cube>

</Schema>

We are going to pay attention to three dimensions:

Country: You can see that this dimension is derived from joining 3 tables: sn_login, sn_user and sn_countries. In fact, our Schema is of the type "snowflake schema".
Login Channel: This is a simple dimension which is derived form just one join: sn_login and sn_lt_communication_channels.
Login Time: This dimension references a global dimension. Within a Cube Definition, a global dimension can be referenced within several cubes in the same Schema. This is very handy, as you don't have to recreate the same dimension over and over again.

Aggregated table for login channel

We basically want to see how many user login by week in each of the channels.

The MDX query therefore looks like:

WITH
MEMBER [Measures].[Prior Period Distinct Logins] AS
(
[Login Time Weekly].CurrentMember.PrevMember,
[Measures].[Distinct Users]
)
MEMBER [Measures].[Change in Distinct Logins] AS
(
[Measures].[Distinct Users] - [Measures].[Prior Period Distinct Logins]
)
MEMBER [Measures].[Percent Change] AS
(
([Measures].[Change in Distinct Logins]) /
([Measures].[Prior Period Distinct Logins])
)

, FORMAT_STRING = Iif(
([Measures].[Percent Change] < 0),
"|#.00%|style='red'|arrow='down'",
"|#.00%|style='green'|arrow='up'"
)
SELECT
NON EMPTY {

LastPeriods(
6,
[Login Time Weekly].[{week1_year}].[{week1_weeknr}]
)

}
ON COLUMNS,
NON EMPTY Union (
Crossjoin (
{[Login Channel].[All Login Channels]},
{[Measures].[Distinct Users],
[Measures].[Percent Change]
}
),
Crossjoin (
{[Login Channel].[All Login Channels].Children},
{[Measures].[Distinct Users]
}
)
)
ON ROWS
from [Logins]

We create an Xaction with a JPivot viewer in the Design Studio, save it into one solution folder and execute it online. Now we open the mondrian_sql.log and we see that Mondrian create a lot of queries. Analyse these queries carefully.

Now, having looked at the queries in the mondrian_sql.log, I decided to create following aggregated tables:

-- channel / week -------------------------------

DROP TABLE IF EXISTS `agg_channel_week_sn_login`;

CREATE TABLE `agg_channel_week_sn_login` (
`login_channel_id` BIGINT(20),
`TW_the_year` INTEGER(11),
`the_week_of_the_year` INTEGER(11),
`distinct_users` INTEGER,
`sessions` DOUBLE,
`fact_count` INTEGER,
KEY `agg_channel_week_login_channel_id` (`login_channel_id`),
KEY `agg_channel_week_TW_the_year` (`TW_the_year`),
KEY `agg_channel_week_the_week_of_the_year` (`the_week_of_the_year`)
);

INSERT INTO `agg_channel_week_sn_login` (
`login_channel_id`,
`TW_the_year`,
`the_week_of_the_year`,
`distinct_users`,
`sessions`,
`fact_count`)
select
`sn_login`.`login_channel_id` as `login_channel_id`,
`sn_lt_time_dimension_3`.`the_year` as `TW_the_year`,
`sn_lt_time_dimension_3`.`the_week_of_the_year` as `the_week_of_the_year`,
count(distinct `sn_login`.`msisdn`) as `distinct_users`,
sum(`sn_login`.`sessions`) as `sessions`,
count(*) as `fact_count`
from
`sn_login` as `sn_login`,
`sn_lt_time_dimension` as `sn_lt_time_dimension_3`
where
`sn_login`.`login_date_id` = `sn_lt_time_dimension_3`.`time_dimension_id`
group by 1,2,3
;

And also one higher level one:

-- week ---------------------------------

DROP TABLE IF EXISTS `agg_week_sn_login`;

CREATE TABLE `agg_week_sn_login` (
`TW_the_year` INTEGER(11),
`the_week_of_the_year` INTEGER(11),
`distinct_users` INTEGER,
`sessions` DOUBLE,
`fact_count` INTEGER,
KEY `agg_week_TW_the_year` (`TW_the_year`),
KEY `agg_week_the_week_of_the_year` (`the_week_of_the_year`)
);

INSERT INTO `agg_week_sn_login` (
`TW_the_year`,
`the_week_of_the_year`,
`distinct_users`,
`sessions`,
`fact_count`)
select
`sn_lt_time_dimension_3`.`the_year` as `TW_the_year`,
`sn_lt_time_dimension_3`.`the_week_of_the_year` as `the_week_of_the_year`,
count(distinct `sn_login`.`msisdn`) as `distinct_users`,
sum(`sn_login`.`sessions`) as `sessions`,
count(*) as `fact_count`
from
`sn_login` as `sn_login`,
`sn_lt_time_dimension` as `sn_lt_time_dimension_3`
where
`sn_login`.`login_date_id` = `sn_lt_time_dimension_3`.`time_dimension_id`
group by 1,2
;

Pay special attention on the naming:

The year column is named TW_the_year. Why the "TW_" prefix you wonder? This is because the year actually shows up twice in our cube schema: in the Time Week and in the Time Month dimension. We defined a usagePrefix in the cube schema in order to distinguish between these two occurrences. Here in the table definition you have to use this prefix, otherwise Mondrian is not able to distinguish between these two occurrences of the year level and will throw an error.
Every table has a column called fact_count, which is a simple count.
Most columns are in lower case, also have you realized how our Measure "Distinct Users" has changed into "distinct_users"?
All columns have the same name as they have in the dimension table (the year has obviously the prefix).
All the table names start with agg_*_yourtablename. This is very important!

Now we have to add following lines to the cube schema (the cube definition) within the table tags in order to tell Mondrian which tables to use etc [highlighted in blue]:

<Table name="sn_login">
<AggName name="agg_channel_week_sn_login">
<AggFactCount column="fact_count">
</AggFactCount>
<AggForeignKey factColumn="login_channel_id" aggColumn="login_channel_id"/>
<AggMeasure column="distinct_users" name="[Measures].[Distinct Users]">
</AggMeasure>
<AggMeasure column="sessions" name="[Measures].[Sessions]">
</AggMeasure>
<AggLevel column="TW_the_year" name="[Login Time Weekly.All Times Weekly].[Year]">
</AggLevel>
<AggLevel column="the_week_of_the_year" name="[Login Time Weekly.All Times Weekly].[Week]">
</AggLevel>
</AggName>
<AggName name="agg_week_sn_login">
<AggFactCount column="fact_count">
</AggFactCount>
<AggMeasure column="distinct_users" name="[Measures].[Distinct Users]">
</AggMeasure>
<AggMeasure column="sessions" name="[Measures].[Sessions]">
</AggMeasure>
<AggLevel column="TW_the_year" name="[Login Time Weekly.All Times Weekly].[Year]">
</AggLevel>
<AggLevel column="the_week_of_the_year" name="[Login Time Weekly.All Times Weekly].[Week]">
</AggLevel>
</AggName>
</Table>

As you can from this schema we are matching the column names of the aggregated table with the measures and dimensions that we defined in the schema itself. This way Mondrian can identify your columns and link it to a dimension/measure.

An important thing to notice here is that we actually don't have a level login_channel_id in the XML Schema (cube definition), but we can just use the AggForeignKey tag to reference it. Within the AggForeignKey you specify the name of the column in the fact table and the name of the same column in the aggregated table.

Now save everything, export everything to your solution folder on the BI Server, go back to the Pentaho website, refresh the repository, clear the Mondrian cash and try to run the Xaction. In case you get an error message, check the Mondrian log files to figure out what went wrong. It is very easy to make mistakes, especially the spelling.
Once you succeed, you will see in the mondrian_sql.log that Mondrian is in fact using your aggregated tables (and certainly you will realize that the execution is way faster).

Aggregated table for country

We have following MDX query with gives us the Top3 countries by week:

SELECT
[Measures].[Distinct Users]
ON COLUMNS,
Generate (

{LastPeriods(
6,
[Login Time Weekly].[{week1_year}].[{week1_weeknr}]
)}
,
{[Login Time Weekly].CurrentMember}*
TopCount(
[Country.All ISO Country Codes].Children,
3,

[Measures].[Distinct Users]

),
ALL
)
ON ROWS
FROM
[Logins]

Again, we create an Xaction, execute it on the website, and check the mondrian_sql.log. Then we decide to create following tables:

-- country / week -------------------------------

DROP TABLE IF EXISTS `agg_country_week_sn_login`;

CREATE TABLE `agg_country_week_sn_login` (
`iso_country_code` VARCHAR(100),
`TW_the_year` INTEGER(11),
`the_week_of_the_year` INTEGER(11),
`distinct_users` INTEGER,
`sessions` DOUBLE,
`fact_count` INTEGER,
KEY `agg_country_week_iso_country_code` (`iso_country_code`),
KEY `agg_country_week_TW_the_year` (`TW_the_year`),
KEY `agg_country_week_the_week_of_the_year` (`the_week_of_the_year`)
);

INSERT INTO `agg_country_week_sn_login` (
`iso_country_code`,
`TW_the_year`,
`the_week_of_the_year`,
`distinct_users`,
`sessions`,
`fact_count`)
select
`sn_lt_countries`.`iso_country_code` as `iso_country_code`,
`sn_lt_time_dimension_3`.`the_year` as `TW_the_year`,
`sn_lt_time_dimension_3`.`the_week_of_the_year` as `the_week_of_the_year`,
count(distinct `sn_login`.`msisdn`) as `distinct_users`,
sum(`sn_login`.`sessions`) as `sessions`,
count(*) as `fact_count`
from
`sn_login` as `sn_login`,
`sn_user` as `sn_user`,
`sn_lt_time_dimension` as `sn_lt_time_dimension_3`,
`sn_lt_countries` as `sn_lt_countries`
where
`sn_login`.`msisdn`=`sn_user`.`msisdn` AND
`sn_login`.`login_date_id` = `sn_lt_time_dimension_3`.`time_dimension_id` AND
`sn_lt_countries`.`country_id` = `sn_user`.`country_id`
group by 1,2,3
;

As you can see this is a bit more complex, as we have to join in fact 4 tables, and the iso_country_code is derived from joining 3 tables.

We add following part to the cube definition (xml schema file) [highlighted in blue]:

<Table name="sn_login">
<AggName name="agg_channel_week_sn_login">
<AggFactCount column="fact_count">
</AggFactCount>
<AggForeignKey factColumn="login_channel_id" aggColumn="login_channel_id"/>
<AggMeasure column="distinct_users" name="[Measures].[Distinct Users]">
</AggMeasure>
<AggMeasure column="sessions" name="[Measures].[Sessions]">
</AggMeasure>
<AggLevel column="TW_the_year" name="[Login Time Weekly.All Times Weekly].[Year]">
</AggLevel>
<AggLevel column="the_week_of_the_year" name="[Login Time Weekly.All Times Weekly].[Week]">
</AggLevel>
</AggName>
<AggName name="agg_week_sn_login">
<AggFactCount column="fact_count">
</AggFactCount>
<AggMeasure column="distinct_users" name="[Measures].[Distinct Users]">
</AggMeasure>
<AggMeasure column="sessions" name="[Measures].[Sessions]">
</AggMeasure>
<AggLevel column="TW_the_year" name="[Login Time Weekly.All Times Weekly].[Year]">
</AggLevel>
<AggLevel column="the_week_of_the_year" name="[Login Time Weekly.All Times Weekly].[Week]">
</AggLevel>
</AggName>

<AggName name="agg_country_week_sn_login">
<AggFactCount column="fact_count">
</AggFactCount>
<AggMeasure column="distinct_users" name="[Measures].[Distinct Users]">
</AggMeasure>
<AggMeasure column="sessions" name="[Measures].[Sessions]">
</AggMeasure>
<AggLevel column="TW_the_year" name="[Login Time Weekly.All Times Weekly].[Year]">
</AggLevel>
<AggLevel column="the_week_of_the_year" name="[Login Time Weekly.All Times Weekly].[Week]">
</AggLevel>
<AggLevel column="iso_country_code" name="[Country.All ISO Country Codes].[ISO Country Code]">
</AggLevel>
</AggName>

</Table>

You see that in this case we don't make use of an ID, as the country ID is not directly stored in our fact table. We have again collapsed the time dimension to the week level.
We save everything, export it to the BI Server and run our Xaction. If we get an error, we check the log, if not, we are pleased that we finally got everything running!

Make use of aggregates in other programs

Other programs or packages usually don't take advantage of Mondrian aggregated tables by default. Usually they provide a mondrian.properties file to configure the exact settings. Just open the properties file and enable following properties:

# Allow the use of aggregates

mondrian.rolap.aggregates.Use=true

mondrian.rolap.aggregates.Read=true

Please find below a reference for some of the programs so that you can easily find the mondrian.properties file:

Pentaho Data Integration (Kettle): data-integration/libext/mondrian/config/mondrian.properties
Pentaho Report Designer: report-designer/resources/mondrian.properties
Schema Workbench: schema-workbench/mondrian.properties

This article is just intended as a brief introduction. In no way I claim that this is the best way to create aggregated tables, this should only give you the idea on how it works and you will have to find the best way for your solution. I have found no tuturial on the web on how to do this and it cost me a lot of time to figure out how to get everything working, so I hope that with the help of this article you won't go through the same troubles. Again, creating everything manually is not easy, always check you spellings etc! Also read the excellent documentation by Julian Hyde which you can find here.

Pentaho Data Integration: Supplying Variables to Shell Scripts

2011-05-07T15:05:00.001-07:00

Pentaho Data Integration (Kettle): Supplying Kettle Variables to Shell Scripts

Tutorial Details

Software: PDI/Kettle 4.1 (download here)
Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described)
OS: Linux or Mac OS X
Tutorial files can be downloaded here

A simple example
Kettle / Infinidb Series: Using JDNI details with colxml

Last time we had a look at Supplying command line arguments to Kettle. This time we take a look at supplying Kettle variables to the command line or shell scripts.

We will start this tutorial with a very general example (so that everybody can set it up) and in the second of half we will focus on a quite specific implementation.

Before we start, we should evaluate the situation. With shell scripts comes huge power in regards to what you can achieve with it. It's easy to fall into temptation and make use of this power, but is it to the benefit of your ETL process?

My advice is to do as much as possible with the dedicated transformation steps and job entries (file and folder management, etc). The reasons are the following (to mention some of the most important ones):

Steps are in most cases OS independent. Shell scripts are OS dependent.
Designing everything within one application (how easy is it to understand what each dedicated step does in contrast to writing custom code)
Easier maintenance

Hence, use the shell job entry only in situation when Kettle doesn't offer the functionality as a step/job entry.

A simple example

The intention of this example is that you can set it up easily and quickly learn how to pass on variables to a shell script. I strongly advice not to use this example in your real world ETL job but to use the dedicated Kettle job entry instead.

Keeping this in mind, we will create a transformation that supplies a folder name to a shell script and the shell script will then create a folder with exactly this name.

Now there are two ways to specify a script for the "Execute a shell script" job entry. One way is to insert the script directly in the job entry properties, but this approach doesn't allow you to specify any variables. To use variables, you have to create a dedicated shell file and reference it in the job entry properties.

Open Spoon (Kettle) and create a new job
Save the job as jb_supply_var_to_shell_script_simple in a convenient folder.

Open the Terminal and navigate to the same folder. Create a file called create_folder.sh using vi:
$ vi create_folder.sh

Note: If you create the shell file on a Windows environment and then copy it over to a Linux environment, make sure that you first get rid of the control characters in the file. One utility which can help you with this is dos2unix. If you don't have it installed, use your favourite package manager (i.e. $ yum install dos2unix) to install it. Once it is installed, run:
dos2unix create_folder.sh

Insert the following (Press i to insert):
mkdir $1
$1 references the first parameter that is supplied via the command line to the shell script.
Make the file executable:
$ chmod a+x create_folder.sh
Go back to Kettle and drag and drop a Start and Execute a shell script job entry onto the canvas
Create a hop between these job entries
We will now set up the folder name variable and specify a default value. Press CTRL+J to call the Job Properties dialog. Click on the Parameters tab and specify VAR_FOLDER_NAME as a parameter as test as default value. Click OK.
Double click on the Execute a shell script job entry
Click on the Script file name input field; then press CTRL+Space and choose the internal job filename directory. Click Browse and choose the shell file we just created. Note: Mac OS X users - the short cut doesn't work, because CTRL+Space is reserved for Spotlight. Either you disable the shortcut or you manually write the variable name.
For the Working directory specify the internal job filename directory variable as well.
In the Fields section supply the ${VAR_FOLDER_NAME} variable.
Save the job and execute it. If you take a look at your folder, you will see that a new subfolder named "test" was created.

Kettle / InfiniDB Series: Using Kettle JDNI details with colxml

Although I make an attempt to provide some basic information below, I recommend reading my tutorial Pentaho Data Integration and Infinidb Series: Bulk Upload to get some more information about Infinidb.This will help understand this section better.

InfiniDB is one of the more popular column oriented database. Currently Kettle doesn't have a dedicated Infinidb bulk upload step. The fastest way to load data into InfiniDB is to use their command line utility called cpimport. Cpimport only accepts job information in the form an xml file, which can be generated with colxml utility . Both cpimport and colxml reside in a sub-directory of the main InfiniDB installation and are usually only executable by the root user. In a nutshell, you tell colxml in which database (optionally table, what kind of delimiter, etc) you want your data file to be loaded and it spits out a nicely formated xml file which cpimport uses to process the data.

Now all this is an ideal candidate for the Execute a shell script job entry, because Kettle doesn't provide this functionality out of the box (yet).

Now that we all know how the process works, let's focus on our ETL process. If somebody wants to follow this section, you can download an open source version of Infinidb here (you can find detailed installation instructions on the same website).

If you are working with a lot of ETL files it is recommended to use JNDI connection details to make the maintenance easier. So instead of specifying all the database connection details for every step that requires them, we can simple store all our database connection details in the simple-jndi/jdbc.properties file, assign a name to each connection and reference this name in the Kettle step. So in the case that the database connection details change, you only have to change them in one place (namely the jdbc.properties file) and not in all Kettle steps. In our example, we will follow this approach.

The only tricky bit here is that colxml requires the database name. Kettle has actually a dedicated Properties Input step (thanks to Slawo for pointing this out) which allows you to read the jdbc.properties file and with a bit of string manipulation we can extract the database name.

For the example below to work you have create a table called fact_impression on a Infinidb database, have a working transformation that outputs a pipe separated file called fact_impression.tbl which we can bulk upload to the fact_impression table. Moreover, you have to define the database connection details in {KETTLE_ROOT}/simple-jndi/jdbc.properties.

Now let's get started. I will only describe the main points. Please find below an overview of the job:

The flow is as follows:

We provide two parameters to this job: ${VAR_DWH} which is the JNDI name we want to use for our ETL job and ${VAR_ETL_FILE} which is unsurprisingly the name of the main ETL transformation that we want to use in this job. Press CTRL+J to see the job properties. Click on the Parameters tab to see these defined parameters. The idea is that these parameters are supplied on the command line when kitchen.sh is called (see Supplying command line arguments to Kettle for more details).
The tr_get_jndi_properties transformation reads the jdbc.properties file and extracts all the database connection details for the JDNI name defined in ${VAR_DWH}. To achieve this we use some regular expressions (this technique is described in my Using Regular Expressions with Pentaho Data Integration tutorial). Note that at the end of this transformation we define the database name as a variable (dwh_database).
All data files for bulk upload have to be stored in /usr/local/Calpont/data/bulk/data/import. In this case we are a bit cautious and delete any old *.tbl file in this directory.
We execute our main ETL transformation (the filename is defined in ${VAR_ETL_FILE}). This transformation has to output a CSV file with the name fact_impression.tbl into /usr/local/Calpont/data/bulk/data/import. Make sure it is pipe delimited.
The next step prepares the Infinidb job file. The infinidb_colxml.sh Shell script job entry calls the external shell file infinidb_colxml.sh. This shell file includes the following:

rm -rf /usr/local/Calpont/data/bulk/job/Job_9991.xml
[ -f /usr/local/Calpont/data/bulk/data/import/fact_impression.tbl ] && /usr/local/Calpont/bin/colxml $1 -j 9991 -t fact_impression

This shell script basically deletes an existing Infinidb job file and if fact_impression.tbl (this is the CSV output file of our main transformation) exists it will create a new Infinidb job file. Please find all the details about this in my tutorial Pentaho Data Integration and Infinidb Series: Bulk Upload. As you can see we define an argument $1 (highlighted in yellow in the above shell script). $1 will basically accept the first parameter supplied by Kettle, which is dwh_database. Hence the order in which you specify the arguments in the Shell script job entry is really important.
Note that we define dwh_database variable as an argument in the Shell script job entry.
The Load Tables Shell script step calls cpimport and initiates a bulk upload.
The last step is not really necessary, but here we delete all data files again in the bulk upload directory.

As you can see, Kettle really offers powerful functionality. The important point is to keep in mind to only use shell scripts in cases when Kettle doesn't provide a dedicated step or job entry.

If you want to learn more about Shell scripting, consider this free book.

Pentaho Data Integration: Excel Writer Step (Plugin)

2011-04-15T12:58:00.001-07:00

Pentaho Data Integration (Kettle): Excel Writer Step

One great thing about Kettle is that everybody with Java coding skills can just write a plugin for it and hence extend its functionality. One plugin that I want to highlight is Slawo's Excel Writer Step which in my opinion is extremely useful. Why?

Quick and easy report creation: Design your report in Excel and tell the Excel Writer step where to place the data. Here's a really cool feature: You can tell the step in which cell to place the data. So you can even use the step several times to write to different areas within the same spreadsheet. Learn how to do this be reading Slawo's excellent tutorial.
Report bursting can be now set up within Kettle: Imagine a scenario where each country manager only receives a report with data related to their country. You can set up a process now in Kettle that takes care of this.
There is always a business user who loves Excel above everything, even if you have the fanciest BI website. You can output reports to Excel using Pentaho Report Designer, but in this case the charts will only be images. If it is required to have native Excel charts, the Kettle Excel plugin in your best friend.

You can download the Excel Writer Plugin from here.

SQLPower Wabit

2011-04-15T12:52:00.001-07:00

SQLPower Wabit: Is it the one-reporting-tool-does-it-all solution?

There are quite different kinds of reports: ranging from dashboard/high level summary reports to very granular data tables spanning 20 or so pages.

As a report designer you usually have to satisfy various clients' needs: The Operations guy wants have a very detailed report, the CEO a high level summary/dashboard style report and so on.

Now Pentaho Report Designer (PRD) is an excellent tool with millions of formatting options etc, but it is more geared towards a very granular/tabular output. If you want to create a dashboard style report, you have to use sub reports. You cannot currently design sub reports within the same window, which is quite inconvenient especially for layouting purposes. Plus every new report that you create has by default the reporting sections required for a table output style (report header, details, report footer) ... so what am I supposed to do if I only want to use one chart? Do I place it in the header, details or footer?

I guess you know where I am getting to: Why not start with a blank canvas and let the user decide what reporting object(s) they want to use?

As much as I like the Pentaho Report Designer for very granular reports, I still find it unexplainable why there is no functionality that allows dashboard style reports (it's more of a user interface problem I guess). I am not talking about interactive dashboards like the ones offered by Pentaho CDF (Community Dashboard Framework), just a combination of static charts, summary tables, crosstabs, etc.

While CDF has really great functionality, it's not possible to email the dashboard report (imagine a scenario when the CEO is on holidays and wants his high level summary to be delivered by email).

I had a quick 5 minutes look at iReport which seems to have the same shortcomings as PRD. So all of them were not really what I was looking for - the one-reporting-tool-does-it-all solution. I kept on search and found SQLPOWER Wabit:

Reading the specs on their website, this just seemed to be the tool I was looking for. I installed it and within 5 minutes I had a dashboard style report created without even reading a manual (which tells a lot about the UI). My report sourced data from Mondrian for a crosstab, and from a MySQL DB for a chart and a summary table. I ddin't have to be bother by using subreports, I could just place the required reporting objects on the canvas and I could use more than one data source as well for the report objects. Nice! Actually, very nice!

Coming back to the initial question: Is it the one-reporting-tool-does-it-all solution? For now SQLPower doesn't seem to offer the millions of formatting options PDR and iReport have, but then it is only in version 1.3.4 and my report was quite nice looking for the 5 minutes I spend on it.

Overall, SQLPOWER Wabit seems to be a very promising product (did I mention that there is an open source version?). It even offers a visual interface to query OLAP cubes. You can download it from here and give it a test ride.

Pentaho Data Integration: Scheduling and command line arguments

2011-03-19T16:01:00.001-07:00

Pentaho Data Integration (Kettle): Command line arguments and scheduling

Tutorial Details

Software: PDI/Kettle 4.1 (download here), MySQL Server (download here)
Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described)
OS: Linux or Mac OS X
Tutorial files can be downloaded here

Approach to provide arguments to one step in a transformation
Approach to provide arguments to more transformations and jobs
Using named Parameters
Scheduling a job on Linux

Once you tested your transformations and jobs there comes the time when you have to schedule them. You want to have a certain amount of flexibility when executing your Pentaho Data Integration/Kettle jobs and transformations. This is where command line arguments come in quite handy.

A quite common example is to provide the start and end date for a SQL query that imports the raw data. Kettle makes it very easy actually to set this up.

Approach to provide arguments to one step in a transformation

If you just need the arguments for one step only, then you can use the Get System Info step and create a hop to your Database Input step.

We will be working with following data set:

Open your favourite SQL Client (and start your MySQL server if it is not running yet) and issue following SQL statements:

USE

test

;

DROP TABLE IF EXISTS

`sales`

;

CREATE TABLE

`sales`

(

`date` DATETIME,

`product_type` VARCHAR(45),

`sales` INT(255)

)

;

INSERT INTO

`sales`

VALUES

('2010-01-20 00:00:00','Shoes',234),

('2010-01-20 00:00:00','Cheese',456),

('2010-01-21 00:00:00','Shoes',256),

('2010-01-21 00:00:00','Cheese',156),

('2010-01-22 00:00:00','Shoes',535),

('2010-01-23 00:00:00','Cheese',433)

;

SELECT

FROM

`sales`

;

CREATE TABLE

`sales_staging`

(

`date` DATETIME,

`product_type` VARCHAR(45),

`sales` INT(255)

)

;

Our goal is to provide the start and end date arguments to our SQL query.

Now let's create our transformation:

Open Kettle and create a new transformation
Drag and drop a Get System Info step on the canvas. You can find it in the Input folder on the left hand side.
Double click on it and populate the names column in the grid with start_date and end_date.
For the type choose command line argument 1 and command line argument 2 respectively

Now add a Table input step and a Table output step (we keep it very simple). Create hops between all these steps in the order that they were mentioned.

Double Click on the Table Input step and populate the SQL field with the query shown below:

SELECT

date

, product_type

, sales

FROM sales

WHERE

date>=? AND

date<?

;

You can feed the start and end date from the Get System Info step to a Table Input step and use the start and end date in the WHERE clause of your SQL query (highlighted in yellow). The question marks will be replaced on execution by the start and end date (but make sure they are defined in this order in the Get System Info step).

Make sure that you enable Replace variables in script? and choose the Get System Info step for Insert data from step.

Define a New ... connection (Connection Name: Localhost, Connection Type: MySQL, Host Name: localhost, Database Name: test, Port Number: 3306, your user name and password).

Click OK. The hop between the Get System Info step and the Table Input step now also displays an info icon.

And this is all that you have to do: Your transformation now accepts command line arguments!

So now let's try to execute the transformation from the command line. Close all the files that we just created, then open your Terminal window.

My transformation is located in:

/Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/tr_get_command_line_arguments.ktr

To execute a transformation from the command line, we have to call pan.sh, which is located in my case in:

/Applications/Dev/PDI\ 4.1\ RC1/pan.sh

Use following approach to execute the transformation (replace the file paths by yours):

Change to the PDI directory:

cd /Applications/Dev/PDI\ 4.1\ RC1/

Use the super user and provide the password:

sudo su

Issue following command (replace yellow highlighted paths with your paths):

./pan.sh -file='/Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/tr_get_command_line_arguments.ktr' '2010-01-20 00:00:00' '2010-01-22 00:00:00' -Level=Basic > /Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/tr_get_command_line_arguments.err.log

Command line parameters (highlighted in red) have to be mentioned after the file argument. Mention them in the order that you expect them to be received in Kettle. The level argument (highlighted in blue) specifies the logging level. Following levels are available (from the most detailed one to the least detailed one): Rowlevel, Detailed, Debug, Basic, Minimal, Error, Nothing.

Pan accepts many more arguments, i.e. to connect to the repository. Please have a look at the Pan User Documentation for all the details.

Once the command is issued and you get no error message returned (check the error file), let's check the data that got exported to our output table:

As you can see from the screenshot above, only the data covering our specified timeframe got processed.

Approach to provide arguments to more transformations and jobs

If you plan to use the command line arguments in more than one step and/or more transformations, the important point is that you will have to do this in a separate transformation which has to be executed before the transformation(s) that require(s) these variables. Let’s call this transformation Set Variables.

The Set Variables transformation has two steps:

Get System Info: It allows you to define variables that are expected to come from the command line
Set Variables: This one will then set the variables for the execution within Kettle, so that you can use them in the next transformation that is specified in your job.

Note: The command line arguments enter Kettle as a String. In some cases the variable is expected to be of a certain data type. Then you will have to use the Get Variable step in a succeeding transformation to define the specific data type for each variable.

There is no additional adjustment needed. Do not fill out the Parameters tab in the Transformation properties or Job properties with these variables!

We can now change our main transformation to make use of these variables.

A typical scenario would be the following: Our ETL process populates a data warehouse (DWH). Before we insert the compiled data into the DWH, we want to make sure that the same data doesn't already exist in it. Hence we decide, that we just want to execute a delete statement that clears the way before we add the newly compiled data.

Our ETL job will do this:

Initialise the variables that are used through the job (done in a dedicated transformation)
Delete any existing DWH entries for the same time period (done in a SQL job entry)
Main ETL transformation

Let's start:

Create a new transformation and call it tr_set_variables.ktr
Drag and drop a Get System Info step on the canvas. You can find it in the Input folder on the left hand side.
Double click on it and populate the names column in the grid with start_date and end_date.
For the type choose command line argument 1 and command line argument 2 respectively
Next drag and drop the Set Variables step from the Job Folder onto the canvas and create a hop from the Get System Info step to this one.
Double click the Set Variables step and click on Get Fields:
Clicking Get Fields will automatically define all input fields as variables. If you don't need all, just delete the relevant rows. In our case we want to keep all of them. Kettle will also automatically capitalize the variable names. As I want to avoid any confusion later on, I explicitly prefix my variables in Kettle with VAR_. You can also define scope type and set a default value.

We have now create a transformation that accepts command line arguments and sets them as variables for the whole job.

Next, let's create the main ETL transformation:

Open tr_get_command_line_arguments (which we created earlier on) and save it as tr_populate_staging_tables.
Delete the Get System Info step. We don't need this step any more as we define the variables already in tr_set_variables.
Double click the Table input step. As our variable can be referenced by names, we have to replace the question marks (?) with our variable names like this:

SELECT
date
, product_type
, sales
FROM sales
WHERE
date>="${VAR_START_DATE}" AND
date<"${VAR_END_DATE}"
;

The variables are now enclosed by quotation marks as we want the date to be treated as string.
Click Ok and save the transformation.

As we have our transformations finished now, we can start creating a job that executes our transformations in a defined order (We will keep this job rather simple. I suggest adding error handling):

Create a new job and name it jb_populate_staging_tables.
Insert following job entries in the order specified and connect them with hops:

Start entry
Tranformation entry: Double click on it and choose tr_set_variables.ktr as the Transformation filename.
From the Script Folder choose the Execute SQL script ... job entry: Define a New ... connection (Connection Name: Localhost, Connection Type: MySQL, Host Name: localhost, Database Name: test, Port Number: 3306, your user name and password). Tick Use variable substitution?. Insert following query:

DELETE FROM
sales_staging
WHERE
date>="${VAR_START_DATE}" AND
date<"${VAR_END_DATE}"
;

Pay attention to the where clause: The variables are now enclosed by quotation marks as we want the date to be treated as string. Also note that the date restriction is exactly the same as the one we use for the raw data import.
Transformation entry: Double click on it and choose tr_populate_staging_tables.ktr as the Transformation filename.

So now let's try to execute the transformation from the command line. Close all the files that we just created, then open your Terminal window.

My job is located in:

/Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/jb_populate_staging_tables.kjb

To execute a job from the command line, we have to call kitchen.sh, which is located in my case in:

/Applications/Dev/PDI\ 4.1\ RC1/pan.sh

Use following approach to execute the transformation (replace the file paths by yours):

Change to the PDI directory:

cd /Applications/Dev/PDI\ 4.1\ RC1/

Use the super user and provide the password:

sudo su

Issue following command (replace yellow highlighted paths with your paths):

./kitchen.sh -file='/Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/jb_populate_staging_tables.kjb' '2010-01-20 00:00:00' '2010-01-22 00:00:00' -Level=Basic > /Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/jb_populate_staging_tables.err.log

Kitchen accepts many more arguments, i.e. to connect to the repository. Please have a look at the Kitchen User Documentation for all the details.

Inspect the error log to see if the job ran successfully. Then have a look at the staging table to see if the data got imported.

Using named Parameters

Named parameters are special in the sense that they are explicitly named command line arguments. If you pass on a lot of arguments to your Kettle job or transformation, it might help to assign those values to an explicitly named parameter.

Named Parameters have following advantages:

On the command line you assign the value directly to a parameter, hence there is zero chance of a mix-up.
A default value can be defined for a named parameter
A description can be provided for a named parameter
No need for an additional transformation that sets the variables for the job

Let's reuse the job that we created in the previous example:

Open jb_populate_staging_tables.kjb and save it as tr_populate_staging_tables_using_named_params.kjb.
Delete the Set Variables job entry and create a hub from the Start to the Execute SQL script entry.
Click CTRL+J to call the Job properties dialog.
Click on the Parameters tab and specify the parameters like this:
In our case we don't define a default value. The reason for this is that we don't want to import any raw data in case there is no start and end date defined.
Click Ok and save the job.

Our job is completely set up. Let's execute it on the command line:

./kitchen.sh -file='/Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/jb_populate_staging_tables_using_named_params.kjb' -param:VAR_START_DATE='2010-01-20 00:00:00' -param:VAR_END_DATE='2010-01-22 00:00:00' -Level=Basic > /Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/jb_populate_staging_tables_using_named_params.err.log

I described the various kitchen arguments in the previous section, so I won't repeat it here. The only difference here are the named parameters (highlighted in yellow).

Inspect the error log to see if the job ran successfully. Then have a look at the staging table to see if the data got imported.

As you can see, named parameters are the crème de la crème!

Scheduling a job on Linux

Now that we have quite intensively explored the possibilites of passing command line arguments to Kettle, it's time to have a look at scheduling:

On Linux crontab is a popular utility that allows to schedule processes. I will not explain crontab here, if you are new to it and want to find out more about it, have a look here.

Our plan is to schedule a job to run every day at 23:00. We pass on two command line arguments to this job: the start and the end datetime. It's required that this job imports each time the raw data of the last two days (23:00 to 23:00). To calculate the start and end date for the raw data processing we will write a shell script. The plan is to schedule this shell script using crontab.

You can edit the crontab by issuing following command:

crontab -e

This will display any scheduled processes. If you are familiar with vi, you can use the same commands here to edit and save. Click i to insert the following:

00 23 * * * /jb_populate_staging_tables_daily.sh

Press ESC followed by :wq to save and exit crontab.

Navigate to the folder where you saved all the jobs and transformations. Create this shell script with vi and name it jb_populate_staging_tables_daily.sh:

cd /Applications/Dev/PDI\ 4.1\ RC1;./kitchen.sh -file='/Users/diethardsteiner/Dropbox/Pentaho/Examples/PDI/command_line_arguments/jb_populate_staging_tables.kjb' "`date --date='2 days ago' '+%Y-%m-%d 23:00:00'`" "`date --date='1 day ago' '+%Y-%m-%d 23:00:00'`" -Level=Basic > populate_staging_tables_daily.err.log

Note: We enclosed our arguments with double quotes. We used enclosing back ticks to indicate that a shell command has to be executed. There is also a blank in our argument, which we enclosed by using single quotes (otherwise Linux is expecting another argument).

Our job is now scheduled. Make sure that you check after the first run for any errors.

In this article you learnt about creating flexible jobs and transformations by using command line arguments. We also had a quick look at scheduling your jobs. I hope this article demonstrated that it is quite easy to set this up.

Pentaho Data Integration: Designing a highly available scalable s

2011-02-23T12:45:00.001-08:00

Pentaho Data Integration: Designing a highly available scalable solution for processing files

Tutorial Details

Software: PDI/Kettle 4.1 (download here) and MySQL server, both installed on your PC.
Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described)
Files: Download from here

Preparation
Set up slave servers

On Windows
On Linux

Define Slave Servers
Monitor Slave Servers
Distribution of the workload
Test the whole process

The raw data generated each year is increasing significantly. Nowadays we are dealing with huge amounts of data that have to be processed by our ETL jobs. Pentaho Data Integration/Kettle offers quite some interesting features that allow clustered processing of data. The goal of this session is to explain how to spread the ETL workload across multiple slave servers. Matt Casters originally provided the ETL files and background knowledge. My way to thank him is to provide this documentation.

Note: This solutions runs ETL jobs on a cluster in parallel independently from each other. It's like saying: Do exactly the same thing on another server without bothering what the other jobs do on the other servers. Kettle allows a different setup as well, where ETL processes running on different servers can send i.e. the result set to a master (so that all data is combined) for sorting and further processing (We will not cover this in this session).

Preparation

Download the accompanying files from here and extract them in a directory of your choice. Keep the folder structure as is, with the root folder named ETL. If you inspect the folders, you will see that there is:

an input directory, which stores all the files that our ETL process has to process
an archive directory, which will store the processed files
a PDI directory, which holds all the Kettle transformations and jobs

Create the ETL_HOME variable in the kettle.properties file (which can be found in C:\Users\dsteiner\.kettle\kettle.properties)

ETL_HOME=C\:\\ETL

Adjust the directory path accordingly.

Navigate to the simple-jndi folder in the data-integration directory. Open jdbc.properties and add following lines at the end of the file (change if necessary):

etl/type=javax.sql.DataSource
etl/driver=com.mysql.jdbc.Driver
etl/url=jdbc:mysql://localhost/etl
etl/user=root
etl/password=root

Next, start your local MySQL server and open your favourite SQL client. Run following statement:

CREATE SCHEMA etl;
USE etl;
CREATE TABLE FILE_QUEUE
(
filename VARCHAR(256)
, assigned_slave VARCHAR(256)
, finished_status VARCHAR(20)
, queued_date DATETIME
, started_date DATETIME
, finished_date DATETIME
)
;

This table will allow us to keep track of the files that have to be processed.

Set up slave servers

For the purpose of this exercise, we keep it simple and have all slaves running on localhost. Setting up a proper cluster is out of this scope of this tutorial.
Kettle comes with a lightweight web server called Carte. All that Carte does is listen to incoming job or transformation calls and process them. For more info about Carte have a look at my Carte tutorial.
So all we have to do now is to start our Carte instances:

On Windows

On the command line, navigate to the data-integration directory within your PDI folder. Run following command:
carte.bat localhost 8081

Open a new command line window and do exactly the same, but now for port 8082.
Do the same for port 8083 and 8084.

On Linux

Open Terminal and navigate to the data-integration directory within your PDI folder. Run following command:

sh carte.sh localhost 8081

Proceed by doing exactly the same, but now for ports 8082, 8083 and 8084.

Now that all our slave servers are running, we can have a look at the ETL process ...

Populate File Queue

Start Kettle (spoon.bat or spoon.sh) and open Populate file queue.ktr (You can find it in the /ETL/PDI folder). If you have Kettle (Spoon) already running, restart it so that the changes can take effect and then open the file.

This transformation will get all filenames from the input directory (of the files that haven't been processed yet) and stores them in the FILE_QUEUE table.

Double click on the Get file names step and click Show filename(s) … . This should show all your file names properly:

Click Close and then OK.
Next, double click on the Lookup entry in FILE_QUEUE step. Click on Edit next to Connection. Then click Test to see if a working connection can be established (the connection details that you defined in the jdbc.properties file will be used):

Click 3 times OK.

Everything should be configured now for this transformation, so hit the Play/Execute Button.

In your favourite SQL Client run following query to see the records that were inserted by the transformation:

SELECT
*
FROM
FILE_QUEUE
;

Once you inspected the results run:

DELETE FROM FILE_QUEUE ;

The idea is that this process is scheduled to run continuously (every 10 seconds or so).

Define Slave Servers

First off, we will create a table that stores all the details about our slave servers. In your favourite SQL Client run following statement:

CREATE TABLE SLAVE_LIST
(
id INT
, name VARCHAR(100)
, hostname VARCHAR(100)
, username VARCHAR(100)
, password VARCHAR(100)
, port VARCHAR(10)
, status_url VARCHAR(255)
, last_check_date DATETIME
, last_active_date DATETIME
, max_load INT
, last_load INT
, active CHAR(1)
, response_time INT
)
;

Open the Initialize SLAVE_LIST transformation in Kettle. The transformation allows to easily configure our slave server definition. We use the Data Grid step to define all the slave details:

id
name
hostname
username
passoword
port
status_url
last_check_date: keep empty, will be populated later on
last_active_date: keep empty, will be populated later on
max_load
last_load: keep empty, will be populated later on
active
response_time: keep empty, will be populated later on

As you can see, we use an Update step: This allows us to basically change the configuration at any time in the Data Grid step and then rerun the transformation.

Execute the transformation.

Run following statement in your favourite SQL client :

SELECT * FROM SLAVE_LIST ;

Monitor Slave Servers

Features:

Checks the status of the active slave servers defined in the SLAVE_LIST
Checks wether they are still active
Calculates the load (number of active jobs) per slave server
Calculates the response time

Open following files in Kettle:

Slave server status check.kjb

Get SLAVE_LIST rows.ktr: Retrieves the list of defined slave servers (including all details) and copies rows the to the result set
Update slave server status.kjb: This job gets executed for each input row (=loop)

Set slave server variables.ktr: receives each time one input row coming from the result set of Get SLAVE_LIST rows.ktr and defines variables for each field, which can be used in the next transformation
Check slave server.ktr:

Checks whether the server is not available or status is 200 and then updates the SLAVE_LIST table with an inactive flag and last_check_date.
If the server is available:

it extracts the amount of running jobs from the xml file returned by the server
keeps the response time
sets an active flag
sets the last_check_date and last_active_date (both derived from the system date)
updates the SLAVE_LIST table with this info

Please find below the combined screenshots of the jobs and transformation:

So let's check if all our slave servers are available: Run Slave server status check.kjb. Run the following statement in your favourite SQL client:

SELECT * FROM SLAVE_LIST;

The result set should now look like on the screenshot below, indicating that all our slave servers are active:

Distribution of the workload

So far we know the names of the files to be processed and the available servers, so the next step is to create a job that processes one file from the queue on each available slave server.

Open Process one queued file.kjb in Kettle.

This job does the following:

It checks if there are any slave servers available that can handle more work. This takes the current and maximum specified load (the number of active jobs) into account.
It loops over the available slave servers and processes one file on each server.
In the case that no slave servers are available, it waits 20 seconds.

Some more details about the job steps:

Any slave servers available?: This fires a query against our SLAVE_LIST table which will return a count of the available slave servers. If the count is bigger than 0, Select slave servers is the next step in the process.
Select slave servers: This transformation retrieves a list of available slave servers from the SLAVE_LIST table and copies it to the result set.
Process a file: The important point here is that this job will be executed for each result set row (double click on the step, click on the Advanced tab and you will see that Execute for each input row is checked).

Open Process a file.kjb.

This job does the following:

For each input row from the previous result set, it sets the slave server variables.
It checks if there are any files to be processed by firing a query against our FILE_QUEUE table.
It retrieves the name of the next file that has to be processed from the FILL_QUEUE table (a SQL query with the limit set to 1) and sets the file name as a variable
It updates the record for this file name in the FILE_QUEUE table with the start date and assigned slave name.
The Load a file job is started on the selected slave server : Replace the dummy Javascript step in this job with your normal ETL process that populates your data warehouse. Some additional hints:

The file name is available in the ${FILENAME} variable. It might be quite useful to store the file name in the target staging table.
In case the ETL process failed once before for this file, you can use this ${FILENAME} variable as well to automatically delete the corresponding record(s) from the FILE_QUEUE table and even the staging table prior to execution.
Note that the slave server is set in the Job Settings > Advanced tab:

If the process is successful, the record in the FILE_QUEUE table gets flagged respectively. The same happens in case the process fails.

Test the whole process

Let's verify that everything is working: Open Main Job.kjb in Kettle and click the Execute icon.

Now let's see if 4 files were moved to the archive folder:

This looks good ... so let's also check if our FILE_QUEUE table has been updated correctly. Run following statement in your favourite SQL client:

Note that the last file (file5.csv) hasn't been processed. This is because we set up 4 slave servers and our job is passing 4 files to each of the slave servers. The last file will be processed with the next execution.

Pentaho Data Integration: Best practice solutions for working wit

2011-02-15T07:39:00.001-08:00

Pentaho Data Integration: Best practice solutions for working with huge data sets

Assign enough memory

Open pan.sh and kitchen.sh (and spoon.sh if you use the GUI) in a text editor and assign as much memory as possible.

Data input

Data from a database table can only be imported as fast as the database allows it. You cannot run multiple copies of a database table input step.
Text files are more convenient, as you can copy them across servers (your cluster) and read them in simultaneously. Another really cool feature that Kettle has is that you can read in text files in parallel (check the "Run in parallel" option in the step configuration and specify the number of copies to start with in the step context menu). How does this work? If your file has a size of 4 GB and you specified 4 copies of the text step, each step will read in chunks of that file at the same time (to be more precise: the first copy will start reading at the beginning of the file, the second copy will start at the first line that is found after 1 GB, the third copy will start at the first line that is found after 2 GB and so on).

Run multiple step copies (Scale up)

Find out first how many cores your server has, as it doesn’t make sense assigning more copies than there are cores available.
Linux: cat /proc/cpuinfo
Have a look how many times processor is mentioned. The first processor has the id 0.

Make sure you test your transformations first! You might get unexpected results (see my other blog post for more details).

Usually you should get better performance by specifying the same amount of copies for steps where possible (herewith you create dedicated data pipelines and Kettle doesn’t have to do the work of sending the rows in a round robin fashion to the other step copies).

Run a cluster (Scale out)

You can run your ETL job on multiple slave servers. Kettle allows you to specify a cluster via the GUI. For this to work, you have to set up Carte first, which comes with PDI (see my other blog post for details).
Of course, you can combine scale out and scale up methods.

Adjust Sort Rows step

Make sure you set a proper limit for Sort size (rows in memory) and/or maybe as well Free memory Threshold (in %).

You can run the Sort Rows step in multiple copies, but make sure you add a Sorted Merge step after it. This one will combine and sort the results in a streaming fashion.

Use UDJC instead of Javascript

Java code will execute faster than javascript, hence use the User Definied Java Class step instead of the Javascript step where possible.

Pentaho Metadata Editor: Joining two fact tables

2011-02-04T14:14:00.003-08:00

Pentaho Metadata Model: Joining two fact tables

This tutorial was possible with the help of Will Gorman, Vice President of Engineering at Pentaho

The data
Metadata Model
My Suggestions

Tutorial Details

Software: PME 3.7 (download here), a database of your liking, in example MySQL
Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described)
Tutorial files can be downloaded here

The Pentaho Metadata layer is amazing: It allows end users to build reports without actually writing a line of SQL. Database and/or table changes can be easily accommodated by only changing the references in the metadata model and hence report, dashboards, etc stay unaffected.

While it is fairly easy to set up a metadata model with one fact table (and some dimensions), using more than one fact table (which also might have different granularity) posses a challenge for many users. Is it really possible to do this in PME (Pentaho Metadata Editor)? The answer is yes, although in the current version I don't consider it to be an ideal solution and I hope that in future it will be possible to visually design this in the relationship diagram.

In this session I'd like to discuss the approach of integrating more than one fact table into your metadata model.

The data

We will base our example on this simple schema:

CREATE SCHEMA

metadatatest

;

CREATE TABLE

metadatatest.fact_sales

(

date_id INT(255),

location VARCHAR(70),

sales INT(255)

)

;

INSERT INTO

metadatatest.fact_sales

VALUES

(1,"London", 34),(1,"Manchester", 44),(2,"London",24),(2,"Manchester",35),(2,"Bristol",34),(3,"London",53)

;

CREATE TABLE

metadatatest.fact_stock

(

date_id INT(255),

location VARCHAR(70),

aisle VARCHAR(70),

stock INT(255)

)

;

INSERT INTO

metadatatest.fact_stock

VALUES

(1,"London","aisle 1", 34),(1,"London","aisle 2", 44),(1,"London","aisle 3", 234),(1,"Manchester","aisle 1", 44),(1,"Manchester","aisle 2", 344),(2,"London","aisle 1",24),(2,"London","aisle 2",25),(2,"Manchester","aisle 1",35),(2,"Bristol","aisle 1",34),(2,"Bristol","aisle 2",324),(3,"London","aisle 1",53)

;

CREATE TABLE

metadatatest.dim_date

(

date_id INT(255),

date DATE

)

;

INSERT INTO

metadatatest.dim_date

VALUES

(1,"2011-01-01"),(2,"2011-01-02"),(3,"2011-01-03")

;

What we want to achieve is basically getting the same result out of PME as with following summary query:

SELECT

FROM

(

SELECT

date,

SUM(sales) AS sales

FROM

metadatatest.fact_sales s

INNER JOIN

metadatatest.dim_date d

s.date_id=d.date_id

GROUP BY 1

) a

INNER JOIN

(

SELECT

date,

SUM(stock) AS stock

FROM

metadatatest.fact_stock s

INNER JOIN

metadatatest.dim_date d

s.date_id=d.date_id

GROUP BY 1

) b

a.date=b.date

;

Open your favourite SQL client and run these queries.

The aggregation query gives back following result:

Metadata Model

So let's start creating our Metadata model: One thing to keep in mind is that our two fact tables have different granularity. In PME you cannot just import them as physical tables, then place them both in a business model and create a relationship between them: Such a model will not return correct aggregations.

The critical element is: You basically have to specify the "physical tables" as join of your star schemas. What I mean by this is that you write a query that joins your fact table with all the dimension tables. This query returns a result set which can act as a "physical table" in PME (although the wording "physical table" doesn't make sense in the context, it is more a derived table or better said a view).

In our case we will have two derived tables. These two derived tables have to have the same granularity:

Sales Table: a join of the fact_sales table with the date dimension
Stock Table: a jon of the fact_stock table with the date dimension, aggregated by date and location

Open PME and create a new domain.
Set up your database connection to the just created schema.
Reference our two fact tables.
Double click on the fact_sales table. The properties window will be shown.
Click on the green plus icon in the Properties section and add the Target Table property. Click OK.
Fill out the Target Table field with this query:

SELECT date date_1, location location_1, SUM(sales) AS sales FROM metadatatest.fact_sales s INNER JOIN metadatatest.dim_date d ON s.date_id=d.date_id GROUP BY 1, 2).

Note we gave the date and location columns intentionally different names so that they are later on easier to distinguish from the other fact table's fields.
Click OK to close the Properties window.
Double click on the fact_stock table. The properties dialog will be shown.
Click on the green plus icon and add the Target Table property. Click OK.
Fill out the Target Table field with this query: Fill out the Target Table field with this query:

SELECT date, location, SUM(stock) AS stock FROM metadatatest.fact_stock s INNER JOIN metadatatest.dim_date d ON s.date_id=d.date_id GROUP BY 1, 2
Click OK to close the Properties window.
Create a business model and drag and drop the physical tables into the Business Tables folder. Your Tables should now look like this (as you can see they have same granularity now):
Next create the relationship: Drag and drop the business tables onto the canvas, hold down the CTRL key, mark the first table and then the second one and right click. Choose Add Relationship ...
Set Relationship to N:N and Join type to Inner.
Tick Complex Join? and copy following lines into the Complex Join Expression:

AND([BT_FACT_SALES_FACT_SALES.BC_FACT_SALES_DATE_1]=[BT_FACT_STOCK_FACT_STOCK.BC_FACT_STOCK_DATE]; [BT_FACT_SALES_FACT_SALES.BC_FACT_SALES_LOCATION_1]=[BT_FACT_STOCK_FACT_STOCK.BC_FACT_STOCK_LOCATION])
Click OK.
Save the model. Now we are ready to test if it is working properly.
Click on the Query Editor icon. Select Date, Sales and Stock as columns, then press the Play button. Examine the result: It returns exactly the same values as the SQL query we ran at the beginning of the tutorial. If you are curious you can as well click on the SQL icon and you can see which SQL query PME created under the hood.

My Suggestions

It is important to provide feedback to the developers of such great products as the PME. I can see in my daily work what a big impact this product makes. I can see end users who can finally create reports to their liking without running to a report designer and waiting for the result to be delivered. When you create a lot of reports you don't have to worry if a database change results in having to change all the references in all your reports. From my point of view a metadata model is a key element in a BI system.

In the beginning of this article I already mentioned that I don't think the current way of setting up this solution is ideal. The reason for this is that it breaks the concept. Normally you would define the relationships between tables in a business model and the SQL would be automatically created. When dealing with more than one fact table (with different granularity), you have to use a completely different approach and write a SQL query yourself by joining your fact table all dimension tables and only then you can create the relationship diagram.

It's not a problem of "Oh I have to write a query", but it is more a problem of consistent approach. I'd really welcome if we could design the complete model in a relationship diagram (consistant approach) instead of hacking the shortcomings by providing SQL queries. I think the user experience would be much better.

I set up a JIRA case on the Pentaho website to, please show your support by voting for it and we might see this feature implemented soon!

At the end of the session I'd like the thank Will for his great support!

Pentaho Data Integration: Remote execution with Carte

2011-01-31T13:31:00.001-08:00

Pentaho Data Integration: Remote execution with Carte

Tutorial Details

Software: PDI/Kettle 4.1 (download here), installed on your PC and on a server
Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described)

Carte is an often overlooked small web server that comes with Pentaho Data Integration/Kettle. It allows remote execution of transformation and jobs. It even allows you to create static and dynamic clusters, so that you can easily run your power hungry transformation or jobs on multiple servers. In this session you will get a brief introduction on how to work with Carte.

Now let's get started: SSH to the server where Kettle is running on (this assumes you have already installed Kettle there).

Encrypt password

Carte requires a user name and password. It's good practise to encrypt this password. Thankfully Kettle already comes with an encryption utility.

In the PDI/data-integration/ directory run:

sh encr.sh -carte yourpassword

OBF:1mpsdfsg323fssmmww3352gsdf7

Open pwd/kettle.pwd and copy the encrypted password after "cluster: ":

vi ./pwd/kettle.pwd

# Please note that the default password (cluster) is obfuscated using the Encr script provided in this release

# Passwords can also be entered in plain text as before

cluster: OBF:1mpsdfsg323fssmmww3352gsdf7

Please note that "cluster" is the default user name.

Start carte.sh

Make sure first that the port you will use is available and open.

In the simplest form you start carte with just one slave that resides on the same instance:

nohup sh carte.sh localhost 8181 > carte.err.log &

After this, press CTRL+C .

To see if it started:

tail -f carte.err.log

Although outside the scope of the session, I will give you a brief idea on how to set up a cluster: If you want to run a cluster, you have to create a configuration XML file. Examples can be found in the pwd directory. Open one of these XMLs and amend it to your needs. Then issue following command:

sh carte.sh ./pwd/carte-config-8181.xml >> ./pwd/err.log

Check if the server is running

Issue following commands:

[root@ip-11-111-11-111 data-integration]# ifconfig

eth0 Link encap:Ethernet HWaddr ...

inet addr:11.111.11.111 Bcast:

[... details omitted ...]

[root@ip-11-111-11-111 data-integration]# wget http://cluster:yourpassword@11.111.11.111:8181

--2011-01-31 13:53:02-- http://cluster:*password*@11.111.11.111:8181/

Connecting to 11.111.11.111:8181... connected.

HTTP request sent, awaiting response... 401 Unauthorized

Reusing existing connection to 11.111.11.111:8181.

HTTP request sent, awaiting response... 200 OK

Length: 158 [text/html]

Saving to: `index.html'

100%[======================================>] 158 --.-K/s in 0s

2011-01-31 13:53:02 (9.57 MB/s) - `index.html' saved [158/158]

If you get a message like the one above, a web server call is possible, hence the web server is running.

With the wget command you have to pass on the

user name (highlighted blue)
password (highlighted violet)
IP address (highlighted yellow)
port number (highlighted red)

Or you can install lynx:

[root@ip-11-111-11-111 data-integration]# yum install lynx

[root@ip-11-111-11-111 data-integration]# lynx http://cluster:yourpassword@11.111.11.111:8181

It will ask you for user name and password and then you should see a simple text representation of the website: Not more than a nearly empty Status page will be shown.

Kettle slave server

Slave server menu

Show status

Commands: Use arrow keys to move, '?' for help, 'q' to quit, '<-' to go back.

Arrow keys: Up and Down to move. Right to follow a link; Left to go back.

H)elp O)ptions P)rint G)o M)ain screen Q)uit /=search [delete]=history list

You can also just type the URL in your local web browser:

http://ec2-11-111-11-111.XXXX.compute.amazonaws.com:8181

You will be asked for user name and password and then you should see an extremely basic page.

Define salve server in Kettle

Open Kettle, open a transformation or job
Click on the View panel
Right click on Slave server and select New.

Specify all the details and click OK. In the tree view, right click on the slave server you just set up and choose Monitor. Kettle will now display the running transformations and jobs in a new tab:

Your transformations can only use the slave server if you specify it in the Execute a transformation dialog.

For jobs you have to specify the remote slave server in each job entry dialog.

If you want to set up a cluster schema, define the slaves first, then right click on Kettle cluster schemas. Define a Schema Name and the other details, then click on Select slave servers. Specify the servers that you want to work with and define one as the master. A full description of this process is outside the scope of this article. For further info, the "Pentaho Kettle Solutions" book will give you a detailed overview.

For me a convenient way to debug a remote execution is to open a terminal window, ssh to the remote server and tail -f carte.err.log. You can follow the error log in Spoon as well, but you'll have to refresh it manually all the time.