Skip to content

Creating drill down on charts within Report Designer

 

Report Designer has the capability of creating charts which you can link the individual values to other reports, creating a drill down chart. This document will help you to create a working example of this using the sample data that comes with Pentaho in addition to a canned sample report that comes with Pentaho (the Order Status report located under the Steel Wheels > Reporting solution folder).

 

  1. Create a new report with Report Designer and add a JDBC connection with the Sample Data and enter the following SQL into the Query Dialog Box.

    SELECT “ORDERFACT”.”STATUS”, sum(“CUSTOMER_W_TER”.”CREDITLIMIT”) AS Sales FROM “ORDERFACT” INNER JOIN “CUSTOMER_W_TER” ON “ORDERFACT”.”CUSTOMERNUMBER” = “CUSTOMER_W_TER”.”CUSTOMERNUMBER” WHERE “ORDERFACT”.”STATUS” NOT LIKE ‘Shipped’ GROUP BY “ORDERFACT”.”STATUS” ORDER BY “ORDERFACT”.”STATUS” ASC

 

 

  1. Add a chart to the Report Header in your new Report and configure it to be a bar chart showing the sales per order status

 

  1. Link to the existing Order Status report located on the BI Server under Steel Wheels\Reporting folder

    Make sure the formula reads as follows:

    =DRILLDOWN(“local-prpt”; NA(); {“oStatus”; [“chart::category-key”] | “showParameters”; [STATUS] | “solution”; “steel-wheels” | “path”; “reports” | “name”; “Order Status.prpt”})

 

Preview the Report in HTML and click on the “On Hold” bar to see the drilldown action:


Advertisements

Improving Transformation Performance by Launching Several Copies of a Step

There are many ways to help improve performance in your transformation, one of those ways are through the utilization of launching several copies of a step. In the case of where you are running Pentaho Data Integration on a machine that has multiple processors/cores, you can leverage those all within one transformation. Let’s take a look at a simple transformation that is made up of three steps:

 

  1. Generate Rows Step: This step generates 10,000,000 rows of data made up of two columns, column A and column B.
  2. Script Values/Mod Step: This step creates a new column C that is generated by adding the values of column A and column B.
  3. Dummy Step: This step receives the input from the previous step, however does nothing, it is simply a placeholder for testing purposes.

 

Transformation Flow

 

As these 10 million rows flow through the transformation it is evident that Step #2 (Modified Java Script Value Step) had to do that calculation on 10 million rows by itself, this produces a bottleneck. The one step leverages one processor and thus again is representative of a bottleneck situation within a data transformation. When we run the transformation above, it completes in 21.5s.

 

 

 

Additionally, we can view using Windows Resource Monitor the CPU utilization caused by the execution of the transformation. You will notice that in the graph below, that the transformation is consuming the cycles of CPU 0, while CPU 1 still has availability to offer additional processing power:

Now, let’s modify our transformation and add a duplicate Step #2 (Modified Java Script Value Step). There are two options when creating a duplicate step and directing the flow of data to that step.

  1. Copy: This option all rows are sent to all destination steps.
  2. Distribute: This option distributes the rows in turn to each step.

 

 

Now that we have created our duplicate step and the data flow is being distributed to both steps, we can now connect that duplicate step to the dummy step which results in our new transformation that has increase our bottleneck throughput as seen below:

When we run this transformation, we will notice three changes:

  1. Data being distributed to newly created duplicate step, each step is processing 5 million rows each instead of one step processing 10 million.
  2. Execution time being reduced from 21.5s to 16.4s resulting in a 24% increase in execution time.
  3. Multiple cores being utilized as show in the graph below.

 

>AgileBI with Pentaho Business Intelligence

>This is about a 9 minute demonstration on AgileBi using Pentaho Data Integration…

>Pentaho LDAP Integration

>In this short video I give a quick demonstration of Pentaho integrated with a 3rd Party LDAP. I will be working on creating a cheat sheet on how to configure what I show in the video, but for now…for your viewing enjoyment…I give you the video:

>Using a Pentaho Data Integration Data Source with the Pentaho Report Designer

>

Pentaho Report Designer can consume an exported Pentaho Data Integration Transformation file (.ktr) as a data source to provide data to a Pentaho Report. The following steps walk you through the best practice when using this type of data source.


1. Export your transform to XML… from Pentaho Data Integration and save your .ktr file to a location on your computer


2. Import your transformation into your report as a Resource. In Report Designer, create a new blank report, select File -> Resources… -> IMPORT -> Select the “..” and browse to the transformation you exported in step 1, input an Entry Name (this name will be referenced later so please remember it), and then select text/xml for the Content-Type, then click OK and Close.



3. Right click on Data Sets and Select “Pentaho Data Integration”, click the “add a new query” button


4. Build your report and publish it to the server as you normally would.

5. Please note that a Transformation with a limited result set (limited number of rows) is recommended at first. The design process will pull data from the transformation in real-time.

6. Publish to and access from Pentaho BI Server as you normally would any other Pentaho Report

7. There is a reference Techcast in greater detal located here:
https://pentaho.webex.com/pentaho/lsr.php?AT=pb&SP=EC&rID=34651817&rKey=d8748782d7bcdaa1

Warning Signs

I recently took a trip to the Great Smokey Mountains with my best friend and as we took off for one of our hikes, this sign was posted at the trail head. As you see below, it read, “DANGER – Fallen deaths HAVE occurred. Closely control children.”

Let me point this out again…”Falling deaths HAVE occurred”. It does not say could occur, is possible…no, not at all, they are telling you they have occurred…people HAVE died. They are speaking from experience not from speculation. When I saw this sign it made me think of Romans 6:23 “For the wages of sin is death” Not the wages of sin may be death, could possibly be death, will most likely result in death. It is plain, simple, straight forward, to the point, no nonsense, no room for speculation, no margin for error, “The Wages of sin is death”…but I love that it does not stop there…”but the Gift of God is eternal life through Jesus Christ our Lord.”
The Question is not what happens to us because we sin…the answer there is abundantly clear, the question is have you accepted God’s gift of eternal life through Jesus Christ?

>Personal Business Intelligence

>One definite benefit to working for a business intelligence company is having access to some of the coolest technology out there. I am one very busy person…sure working for the standard in Commercial Open Source Business Intelligence vendor Pentaho keeps me extremely busy with so many companies making the switch, I am also a single Dad raising 5 kids. As you can imagine, raising 5 kids requires a lot of management, one key area is where it gets rather expensive and I need to keep a good eye on is medical expenses…especially since they are tax deductible.

So what do my kids medical expenses have to do with Pentaho? Simple…with Pentaho, I can easily upload my data and use AgileBI to quickly analyze, gain insight, and take action on the information. Here is a screen shot of my kids medical dashboard:


As you can see here…Emily is my most accident prone kid, she represents the only child that has had the pleasure of visiting the emergency room. Additionally, I may need to make sure she is spending adequate time brushing her teeth as I see she represents a large portion of the dental expense. It also seems that I tend to spend a significant more on medical expenses during the school year…I guess when the kids are not at school, they tend to pick up less germs from the other students.

So in the end, this is not rocket science, but I guess that is my point…Pentaho has made is so easy to connect to data I already keep track of for my family and turn that data into information and help me see trends in my medical spending.