Use Case: Grouping by Time, A Call Center Story

Recently a company that operates a rather large call center came to Pentaho to help them be able to better analyze their data and gain better insight to the performance and efficiency of their agents.  One of the issues they had was that they wanted to see their call center data grouped by particular time categories so that they can better manage staffing for peak hours of the day as well as view who are the top performing agents in terms of volume.  They had some basic information but the problem was that they wanted to have the grouping within the database so that they can gain some additional performance in pre-aggregating the data, due to the large volume of calls that are occurring.  Here is a sample of what the data looked like (the sample below is only sample data generated by a sample data generator).

The first step in this process is to make the data "BI Ready", in other words take the data and convert to information by enriching it and grouping it by the designated time buckets, for this particular example they want to bucket their time in 15 minute increments starting from 8:00 to 5:00 (the operating hours of the call center).  In this example, I am only using a snapshot of data ranging from 8:00 AM to 9:00 AM.  This will allow them to answer questions like

• During what time span (within 15 minutes) is the call volume the highest?
• What time categories have the highest volume of incoming calls?
• What time categories have the highest volume of outbound calls?

The end transformation looks like this:

Transformation Detailed Description

Read Call Center Data from CSV - This is a csv Input Step that reads in the data from the CSV file.  This does not have to be a csv file but can be almost any flat file, database, web service, etc.

Convert Date-Time to String - This is a Select Values step that allows you to rename or change the type of any of your fields, in this step we are converting the date-time  to a string in order to split it into two different fields, one field that contains only date and the other only time.

Split Date-Time Field - Field Splitter allows you to split any string field based on a common requirement.  In this example because the date-time field called BeginDate in our csv file has a date-time field that is a format where the date and time are separated by a space, we use that as the delimiter to split the field.

Convert Time to Number - The convert time to number is used to convert the Time field that we created in the previous step to a number in order to do our grouping into time ranges.

Create Time Range - This step will create a new column called TimeGroup, read in the rows of data, look at the time field set the appropriate value to the newly created field.  For this example we are creating four groups:  8:00 AM to 8:15 AM, 8:15 AM to 8:30 AM, 8:30 AM to 8:45 AM, and 8:45 AM to 9:00 AM.

Convert Time to Time Format - This step converts the time field back to the time data type for reporting purposes.

Dummy (do nothing) - this step, as it implies does nothing, at this point you can load the data directly into a database, flat file, etc.

The end result of this transformation looks like this:

Download the zip file here for youto run this transformation.  In order for it to work, you will need to import the .ktr file and then make sure to change the path of the csv file in the first step to the location of where you saved the csv file that is included in the .zip file.

Pentaho Data Integration Sample Library

When you download Pentaho and run our Data Integration client tool (also known as Spoon), you also get a huge library of sample transformations that can help you build out your solution.  The great thing about these sample transformation and jobs is that they are all working samples, so you can test and learn by following the entire flow.  There are over 150 working sample transformation and over 40 working jobs.  You can access these transformation and jobs anytime by going to the following directory:

[Install Directory]\pentaho\design-tools\data-integration\samples

To use any of these transformation in Pentaho Data Integration follow these three easy steps:

1. Start Pentaho Data Integration Client Tool (Spoon)
   
2. Go to File > Import from an XML File
   
   
   
3.   Browse to the samples library location stated above and select the sample you wish to view and run.

At Pentaho, the best just keeps getting better!!!

At Pentaho, the best just keeps getting better:

http://www.marketwire.com/press-release/-1568941.htm

Improving Performance with Pentaho Data Integration Table Input and Oracle

When using Pentaho Data Integration Table Input step to connect to Oracle via a JDBC connection there is a setting in your connection information that you can specify that can dramatically improve your performance in retrieving data.  This property is the defaultRowprefetch.  Oracle JDBC drivers allow you to set the number of rows to prefetch from the server while the result set is being populated during a query. Prefetching row data into the client reduces the number of round trips to the server. The default value for this property is 10.

In the table input step, edit your connection, click on the options tab and then enter in your defaultRowprefetch specification:

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:

Download the sample .prpt file by clicking here.

or watch the demonstration:

Pentaho reporting and canned reports for distributed solutions

So recently I came across a use case that I helped someone work through that I thought my audience would find useful. Here it is;

Use Case

A company wants to offer Pentaho as the BI Application with their current solution. As part of this solution they want to create some canned reports that has the logo of the customer that is using their solution. Each customer will have the same report, because it is a report that is common across all their customers, however, they want the logo on the report to be that of the customer. Additionally, the solution is installed on premise at each individual customer site so the location of their solution in relation to the web application will be different for each customer (in other words, the installation and configuration of the Pentaho BI Server is different at each customer). In order to accomplish this in Report Designer, they must do the following steps:

1. Place an image in the report
   
   
2. Put the location of that image as an Open Formula consisting of: =[env::serverBaseURL] & "/pentaho/GetResource?resource=solution/folder_path/image_name.png

Here is a video presentation demonstrating this:

Pentaho 4.0 - Bursting in Pentaho Data Integration

Download instructions on customizing the Transformation

Download the transformation here

For more information on the new Pentaho BI Suite 4.0, please visit Pentaho.com

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

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:

• The First to Bring BI to the iPhone
  
  
• BI on the iPad
  
  
• Agile BI Saves Customers Billions
  
  
• Pentaho Harnesses Apache Hadoop to Deliver Big Data Analytics

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.

Flexibility and Complexity in Business Intelligence

Where to star, where to start? Let's begin by defining what I mean by Flexibility and Complexity:

Flexibility - The ability to do what you need to do. In regards to BI or technology in general, it is the ability to build a custom application to meet your specific, customized needs.

Complexity - The measure of how difficult a particular task, system, technology, what have you, is. This term is also used many times as usability, which is not necessarily true. Something may be very usable to an engineer but not to a typical user.

Many times, too many to count, I have companies requesting a BI tool that "Offers great flexibility and powerful enough for developers to use but easy enough for end users to use as well". This is a very common misplaced expectation that people have. The reason is simple, there is an direct relationship between flexibility and complexity. Take a look at this graph and you will see what I mean.



Basically, the more flexibility that you require a technology, a tool, a software package to be, the more complex it becomes. Think about it this way...let's say you wanted to buy a car. You go to the show room and the only choice you have is one car and two colors, red or blue. That is a very simple decision, basically what color do you prefer? This option offers very little flexibility, however, it offers very little complexity in making a decision. Now let's take this a step further, the dealer offers you the following option, you can choose between a 2.1 ltr engine or a 3.4 ltr engine, you also can choose between a regular gas or hybrid. You also get to choose between 5 different stereo packages, an extended warranty, leather or cloth seats, 5 speaker or 8 speaker system, tow package, child safety features. All of these options increase the flexibility you have in owning a car that meets your specific needs (flexibility), however, now the decision you have is much more complex. You need to research the benefits of hybrid over gas, 5 speaker or 8, extended warranty or not. You see with these added options (flexibility) comes added complexity in you final decision making.

Technology is the same way, especially when it comes to Business Intelligence. When a user comes to me and asks me for a tool that is flexible enough to meet all the needs of a BI developer while making it very easy to use (not complex), it tells me that they do not have very much experience evaluating enterprise level software solutions. Enterprise software companies spend much time and expense in developing tools that meet the needs of specific user communities. So what I have done is taken the chart above and added the different user groups.

Going through my stats on my blog I notice I get a lot of hits from people who are looking at implementing BI and to those people I say, keep this chart in mind. Evaluate each individual tool for the intended audience and do not try to solve your problem with a "fits all sizes" approach because we all know that "one size fits all" does not mean "fits like a glove" but rather "minimum required to accomplish its basic task"

Information Over Load

Information Over Load

I love technology.  Technology has improved my life greatly, especially considering my income is completely related to technology.  There is a downside to all this technology though and I don't think that we may even be able to scratch the surface on how all of this will affect our future.  When a pharmaceutical company comes out with a new drug, it must first go through rigid testing to find out how that drug may adversely affect us.  There may be some benefit to analyze our technology this way as well.

Facebook.com, MySpace.com, Linkedin.com, and Twitter.com are some fairly recent examples of new and innovative Web 2.0 type applications that are having a profound affect on our society.  Take facebook, my daughters are constantly on facebook, they chat with their friends, send them messages, post pictures.  If you ever want to know what is going on in their life, you don't need to sit down at the dinner table, simply log onto facebook and you will find out everything.  My point in all this is that today, there is a ton of information out there for anything and anyone.  We are constantly being bombarded by information, status updates, profile updates, picture updates, tweets, twitters, whatever.  This presents a new problem moving forward.  

In the past, businesses had a difficult time making decisions due to not having enough information.  Business Intelligence is around because companies need access to crucial information that was buried deep down in their computer systems and again, the problem that was typical was that they could not get to the information, therefore, limiting the amount of data you had access to.  I think we can all agree that technology has come to a point that we are really no longer limited by it.  We are now limited by the processing power of the human brain.  I believe it was in the book Good to Great where author Jim Collins wrote in one of the chapters about a brokerage firm and how they were able to maximize the effectiveness of their brokers.  They did a study to find out what the maximum number of contacts could handle without letting any fall through the cracks...these contacts would include both actual clients and prospects.  You see the brokers would spend hours and hours on the phone, making hundreds of dials a day...they are limited to the number of times they could dial the phone and reach the person they were intending to reach.  Factor in time for dial, leaving voice mail, missing a returned call, calling prospects and clients back calculated to 300 contacts in their 'books'.  At the time I read this I was the IT Director at a major investment relations firm.  So what did I do?  Using the data I had been collecting on our call center, I wrote a BI application to analyze the results that Jim Collins found and I was able to verify the information and make the appropriate recommendations to our sales.

Similar to brokers only having enough cycles to handle a book (list of prospects and clients) of about 300, information consumers only have enough bandwidth and brain power to handle only so much information.  For example, think of the CEO in your company.  He has information coming to him constantly.  It may be human resources related, sales related, operations related, production/manufacturing related, marketing related, merger and acquisition related, external economics related, personal/family related.  Our brains can only handle so much at a time, and we cannot upgrade to a brain clustered environment.  All of this information is competing with each other.  So the question is how much information can we handle before we reach information overload and start blocking out things....or information starts falling through the cracks.  I was once consulting with a company on developing their web presence and when we were discussing the home page of their site, I asked, what I thought at the time was a relatively simple question, "What is the most important thing you want to point out about your company?"  The answer they gave me was frustrating.....they said,"everything".  This resulting in a hours long discussion about, if everything stands out then nothing stands out.  It reminds me of a despair.com saying, "You are special, just like everyone else".

What does this long rant have to do with BI?  Well, BI is presenting information that is simply just noise among noise.  It is information that is fed to whichever information consumer we deem to require it and we expect the end user to determine it's importance.  Here is what we need...we need a system that can automatically inform us in a manner that will give it a larger priority over all the other noise and in a way we understand and respond to.  This is where technology innovation has to meet creativity.  Stephen Few has some good examples and blogs about meaningful ways to present data in an efficient and concise manner.

The purpose of this post is not to give you a solution to this problem but rather present a discussion point on how BI can help become a filter for all of this noise.

Do you know your data?

Some things never cease to amaze me. BusinessIntellienge (according to Wikipedia) has been around for over 50 years. Despite this fact, all too often companies are inadequately providing the basic foundation for Business Intelligence. Here is one small example….knowing what data you use to make decisions. Whether it is a government organization, public company, or private company, they typically know what information they need to make their most important decisions, at least from a business perspective, however, from a data perspective, IT typically has very little insight into what data is actually being used.

Let’s check out a typical example. I have seen countless times where companies are implementing new systems that require major database changes. Well, when these changes are made, IT typically has a very difficult time determining exactly what data is being used, by what reports, by which users, not to mention how many times. Now if you think about it, this is very important information. In our society now, it is easy to get lost in data, there are so many sources of information, such as this blog, news sites, facebook, myspace, linkedin, drudgereport. I think we all can agree that the problem is not if the information exist, it is, can we find it and if we can, can we connect to it. So why are the questions about the data so important?

What data is being used? By whom? How often? This is important, especially when implementing solutions such as a data warehouse. Data Warehouse implementation can be very costly and often fail even after spending hundreds of thousands of dollars above what the budget required. Many of these failures can be prevented by the ability of knowing the answer to these questions. I have been working on a project where I an organization has to transfer 200 reports that are on their legacy system and have recreate them using Crystal Reports on their new ERP system. The process is to analyze each report, find out what fields were used to create that particular report and then look to see if the data was converted to the new ERP system. What sense does this make? All of these reports are considered mission critical by this organization, so my question is why is a field in a report that is mission critical no longer available in the new ERP system. A best practice approach could have been taken here before the data conversion process if the organization new which reports were mission critical and were able to generate a report that listed all the fields within those reports.

While I was at Information Builders, Inc. using WebFOCUS, they had a fantastic product called Resource Analyzer that at a click of a button, you would have access to hundred of reports that gives you the ability to analyze what data is being used, by whom, how often. Not only that, but it could also provide you with some what if analysis…for example…what if I removed this field from our database? What reports would that affect? What users would be affected? I don’t know about you but before I make any major changes I definitely want to make sure I know the impact it will have on upper management.