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:


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.

>Radical Innovation

>Still working on my recording, unfortunately Camtasia is not as easy to work with as Pentaho…speaking of Pentaho, I recently read an interesting article in the Harvard Business Review, “Wall Street Is No Friend to Radical Innovation” by Julia Kirby. In it she talkes about a study by Mary Benner of the Wharton School that was publish in Organization Science that examined the way that security analysts research and report on incumbent firms in industries undergoing disruptive change (take for example BI). “She finds that analysts tend to speak glowingly about innovations from major players that extend old technology, while downplaying the initiatives those firms have under way to capitalize on the next wave of technology”

To this I say, hmmm, and look at the latest press releases of Pentaho and say to Wall Street…don’t miss out, here are only two of many examples:

Cheers!

>The Right Tool For The Job – Part 2

>In continuation of my previous post…I already had a tool to perform the duties I needed to changes the brakes but having just the right tool made all the difference in the time it took to successfully complete the task at hand. Took less time…and time is money!

The same holds true in BI. In the July/Aug 2010 issue of Information Management Magazine they had an articaled called Data Definition, How business and IT can speak the same language, Really. Notice how they added the word “Really” on the end of the title. This is because their is a LONG standing gap in the way IT understands data and the way that information consumers (business users) look at data. I don’t know about you but nothing sounds more boring to me than defining data! I remember having vocabulary in most of my schooling where the teacher would assign a long list of words and your homework was to write out the definitions, not what I want to do on a Friday night. Looking back, that task was much easier than the daunting task for companies to define their data. The article referenced to above outlines what makes a clear or unclear data definition and one thing is certain, it takes BOTH IT and BUSINESS USERS to define data and is often a recursive task.

The article recommends the following strategy for defining your data:

  1. Provide examples of unclear versus clear definitions
  2. Understand what the business user wants to do with the data
  3. Bring all stakeholders together to review the definition

While step one is relatively easy to accomplish, Steps 2 and 3 are very difficult. The main reason is the way that IT and Business have to communicate together. This is where the right tool for the job comes in to play. You can either call it Pentaho Agile BI or simply mana from heaven. Either way you slice it, Pentaho Agile BI gives you the ability to complete items 2 and 3 above more quickly then anything else out there. Agile BI is the LINK that empowers IT and Business Users to “Speak the same language…Really!” In the coming week, I will be making a video demonstration on exactly what this looks like…make sure to check back to see!

>The Right Tool For the Job!

>Some people would call me mechanically inclined and while others may call me mechanically challenged…either way…I tend to work on my vehicle probably more than the average person. One common maintenance activity that I do perform myself is changing the brakes on my vehicle. This is a fairly simple job in terms of concept but can be tedious due to the difficulty of getting to and working with the specific areas on the vehicle. For years I worked on these projects and it would typically take me about two hours to complete until one day, I was at an auto parts store and saw a special tool that was made specifically for part of the job of changing the brakes. It was like a gift from heaven, with this new tool, I was able to change the brakes in about 45 minutes with a lot less sweat and head ache.

So what does changing brakes have to do with BI? Nothing at all! However, the connection has to do with how using the right tool can help save you a lot of time and head ache in implementing and maintaining your BI environment. You may be successful right now but as some people say, “you don’t know what you don’t know” and you may have the opportunity to be even more successful. In the next few blog posts, I am going to talk about how Pentaho can help you just like me finding the right tool to help me change my brakes.