Wednesday, January 25, 2012

Utilize existing SQL Code for your Report Generation with Pentaho

I often get asked if developers can utilize SQL that they already use in their legacy reports within Pentaho and the answer is always a resounding YES!  There are hardly if any companies out there that have not leveraged some type of Business Intelligence, even if it is generating csv files from a SQL query within a database.  So there is a common concern when making any switch in technology, especially Business Intelligence/Analytics, and that is leveraging the work you have already done.  here is a short video demonstration that show how easy it is to leverage Pentaho Report Designer to simply cut and paste your existing SQL code and how to extend that by parameterizing the report.  Enjoy the video!

Friday, January 20, 2012

Increasing Pentaho's Data Sampling Set to Sample your entire CSV File

Pentaho offers a very valuable capability in that you can quickly and easily prototype your data using a CSV file as your source data for the prototype.  Pentaho will bring in the CSV file, automatically profile the data and set the data types and lengths.  One important thing to note here, however, is the default sampling list is set to 200 rows.  I would recommend that you increase this limit to the size of your CSV file.  For example, if you have a CSV file that contains 10,000 rows, I would increase the limit to 10,000.  The next obvious question is how do I do this?  Well, it really is easy, just follow these simple steps:


  1. Open the following file in a text editor:   C:\Program Files\pentaho\server\biserver-ee\pentaho-solutions\system\data-access\settings.xml
  2. Change the default value that is located in the following tag to the desired sampling amount:      data-access-csv-sample-rows>10000/data-access-csv-sample-rows> (in this tag I have increased it to 10000)
  3. Restart your BI Server

This will help ensure that you have a success production of your prototype so that you can show your end users how easy Pentaho is to use to slice and dice their data, build reports and dashboards.

Tuesday, January 03, 2012

Exception Reporting Can Dramatically Save Time and Money

Business Intelligence has been around for a very long time and all companies have at least some form of it, whether it is a written ledger or whether they are using the most cutting edge, advanced and innovative BI solution such as Pentaho ;).  That being said, Exception Reporting is still not widely used in today's business.  What exactly is exception reporting?  I am glad you asked.


Exception reporting is a method of reporting that, well, reports on the exceptions, which is A person or thing that is excluded from a general statement or does not follow a rule.  One of the key words is "rule", so to really be able to effectively do exception reporting, a business must have some key business rules in place in order to know what exception they are looking for, here are a couple of industry specific examples:


Customer Service Example


Have you ever eaten fast food?  I am sure you have and you probably have noticed when going through a drive through that many of them have a timer clock by the window showing the current wait time.  Why is this?  This is because the company has deemed it important to monitor what the average wait time is for their customers.  The smaller the average the higher the customer satisfaction...hence the name...fast food.  The timer is there for the benefit of the line workers.  Shift managers are well aware of what the acceptable average wait times are and are often compensated for staying within range or even beating expectations.  What exception reporting does is collects all the information and presents it from a summary down to a granular basis.  Follow me here...Somewhere, back at corporate there is a person who is responsible for managing what the average wait time is for their customers among other things.  Now this person cannot sit there just staring at a dashboard that says what the average wait time is, so what they have done is setup some exception reporting.  To do this, they have defined the acceptable limits in which the company allows.  For example, an acceptable wait time is anywhere between 30 seconds and 3 minutes.  Any time the average falls above that range, the responsible party is notified and is offered the ability to click on the number and receive a list of only those stores that are above the limits along with what their average wait time currently is.


Financial Example


Financial institutions commonly have very strict regulations that they must abide by and these regulations change often.  Due to this, they often have implement a vast array of different information systems needed to meet these regulations.  The problem is that now they have the burden of making sure that these systems are in balance.  Any time you increase the complexity and diversity of all these information systems, you typically run into a data integration nightmare.  You have data spread out among many systems, some of them duplicated and some not.  In regards to the data that must be duplicated, it is important that those systems be able to reconcile with each other.  Recently, I helped a company be able to do this.  Their current process was to receive a report from one of their financial systems via a PDF, due to the system being a SaaS application with certain limitations and reconcile that with a legacy system they have.  It was a few people job to look at this report that was over 2,000 pages and compare account balances across both systems.  When the balances did not match, they had to look at the detail information and make any appropriate journal entries in order to correct it.  Since neither of these systems "talked" to each other, this was a very hands on, manual process.  Well, a little bit of know how and of course Pentaho, they now are able to connect to both systems and compare balances automatically and create a report that only contains accounts that don't reconcile with drill down to detail capability...saving days of several FTEs (full time employees).


Pentaho is rather unique in that the way our Data Integration and Business Analytics are architected, performing exception reporting is well exceptional.  My apologies for such a corny ending...but...I am a father of 5...one has to have a sense of humor at this point ;)


BTW - Happy New Years to all my blog followers, I appreciate all your views!