BIRT 2.6 Data Analysis and Reporting: Create, Design, Format, and Deploy Reports with the world's most popular Eclipse-based Business Intelligence and Reporting Tool

There are two major questions that need to be answered at this point of the book—what is Business Intelligence and why do we need it? Business Intelligence is a lot like many other technology buzzwords that get thrown around. Many people are just aware of this word and say it, and many people will give a complicated definition of it. But the answer is really quite simple.

If I had to give it a formal definition, I would say that Business Intelligence, as it relates to information technology, is any tool or method that allows developers to take data or information, process it, manipulate it, associate it with related information, and present it to decision makers. As for a simplified definition, Business Intelligence involves presenting information to decision makers in a way that helps them make informed decisions.

Consider this scenario. You are the manager for a chain of retail stores. You need to figure out what products you should push to the forefront for the upcoming holiday season. You have a two method approach. First, you get the latest buzz from your marketers, who tell you what the new and upcoming products are. Your second approach is to look at products that have traditionally sold very well. You could also look at web tracking data, usually from large analytics companies such as Omniture or Google Analytics.

In order to project how the current year may go, you need to look at the data you have at your disposal. What product categories have traditionally sold well over a five-year period or even a ten-year period? Has there been any sort of pattern to these sales figures. What individual items have sold well in that period? Are there any trends? When you look at these figures, can you give a projection of how well the products will do? What items should be put on sale in order to push for higher sales? These are the kinds of questions that a decision maker may need answered.

So, the first step in the process is getting to this data, which is usually located someplace such as a data warehouse. If you are looking at a regional level, you may have this information stored separately in a localized data mart, with specific data. If you are looking forward to working on the minutest of data, you may need access to transactional data. Either way, the first hurdle is getting access to the data.

What do you do once you have access to the data? How do you format and present the data in some meaningful fashion that can be used to assist in the BI process? In other words, what is the best way to use this data to tell a good story, one that is easy to understand and also gets you the answers you are looking for? This is where BI tools come into play. Using BI tools, you have the ability to write reports that can present this often time sporadic and confusing data into some sort of format that is useful to decision makers. Once this data is presented, trends can be identified, total figures can be aggregated, and decisions can be made.

The current state of the BI market is similar to the state of any technology field. It can change at the drop of the hat. At the time of this writing, you can divide the major players in two categories—commercial offerings and open source offerings. Each route has its own benefits and drawbacks. Certain familiar names are associated with the commercial offerings, for example Actuate and Business Objects offers various tools aimed at different levels of business. Some of these tools are large enterprise reporting platforms that have the ability to process, analyze, and reformat large quantities of data. With commercial offerings, you get product support and years of experience. With the big guys, you often get professional services too such as consultation in developing your reports for a fee. One of the drawbacks of commercial offerings is the large price associated with them, both in terms of purchasing and running them. Some companies, in addition to the initial cost which, at times, can run up to thousands of dollars, also charge for yearly maintenance, upgrade fees, and have an additional cost of ownership that may be typically overlooked. There is also the cost of licensing. If you are building a large scale custom application, are you allowed to integrate these products into your application? If yes, then are there any restrictions? And finally, the years of engineering behind a product may leverage an obsolete methodology. Perhaps the technology behind these products is no longer viable or powerful enough to handle the demands of a growing enterprise.

Then you have your open source offerings. Currently there are three big names in the open source reporting realm—JasperReport, Pentaho, and BIRT. Two of these projects, JasperReports and BIRT, are run by commercial companies who make money doing professional services for these offerings to small scale, private projects. Again, there are a number of pros and cons associated with open source solutions. With open source, you have full access to the source code of the platform you choose. This allows you to add in functionality, embed it with in your existing applications, and actively participate in a development community that is often times very large and worldwide. There is little initial cost to open source in terms of purchasing because any open source tool is available free of charge. There are a few disadvantages too. There is typically a cost associated with finding individuals knowledgeable in open source. Sometimes open source is not very user friendly. And finally, there is often little to no support for open source products. This is not the case with large open source projects. However, with the large active development communities associated with open source projects such as Linux, Eclipse, and Mozilla/Firefox, answers to your support-related queries are only an internet search away.

Some things are better illustrated with a story. So, let me begin this with what brought me to the world of Open Source Business Intelligence. My story starts on a late Sunday evening in 2001. I was then a student intern for a midsized network security monitoring operation. Here I am, landing a dream job for a college student, a paid internship at a high technology company, and working in one of the most exciting fields in the tech industry—network security. From this single department came some interesting projects and concepts such as Sguil (the Open Source Network Security Analysis front-end for Snort) and SanCP (a network session profiler). Concepts such as Network Security Monitoring (NSM) were being tested and proven over security appliances that promised a silver bullet to all security problems.

In this particular scenario, we are a department dedicated to providing customers with network security monitoring solutions using open source software. This entire NSM philosophy would later be expanded upon and described in The Tao of Network Security (Bejtlich 2004). And yet, here I am, with a broken keyboard, occupying a cubicle in my office where the A/C shuts off on a timer, so it's boiling hot, surrounded by great gadgets and cool blinking lights, flashing screens, and on the cutting edge monitoring of Internet traffic catching bad guys doing bad things. The whole setup is like a scene right out of a spy movie, with a lot of exciting things happening. Such a great opportunity for me, a young college student. Yet, on this particular evening, I was not enjoying my job.

My job here, this evening, was not to monitor the thousands of alerts, nor was it to inspect the exponentially higher amount of network packets in an effort to protect networks from the insidious under doings of the digital underground. Instead, my job this evening was to perform the most dreaded task in the entire operation—the weekly incident report.

Being a company dedicated to open source, we had tons of open source software, proving that the open source paradigm was a workable one. Our workstations were all Red Hat Linux systems configured and customized to help us achieve our mission statement. Our backend servers were all running FreeBSD. Our security console was a custom, in-house developed front-end, built on open source scripting tools (which was the base for what would later become the Sguil project). Our network sensors were all built on Snort and dumped all transactional data into a PostgreSQL data warehouse. Our office productivity suite was an early release of OpenOffice.org. Yet we lacked one important piece of a customer focused service group, a reporting system.

So, what tools did we use to address the area of reporting? We had scripts—lots of tedious, boring, manual scripts that generated lines of ugly text. The scripts took hours to run, mostly due to a lack of proper indexing on the reporting tables as the DBAs refused to listen to us and focused on the transactional databases that housed all our live data. As a result, the scripts were slow and their output was ugly. The scripts may have outputted RTF documents, but no formatting tags were actually used. Part of our job was to go through each of these reports, cut out duplicate lines, change the fonts and font weighting, and manually go through and confirm the accuracy of each of the counts in the summary. This was a time consuming, tedious, and boring task that was highly error prone and, as a result, required a longer validation time. Of course, due to the setup, the scripts were considered part of the Database Administrator and the system developers' tasks, so of course we didn't have access to change anything in the event of an issue. If a query was returning funny results in the reports, all we could see was an output page with no access to the code that generated it.

Now it's the middle of the night, we're tired, the other guy is griping about having to pick up the slack while I am doing the most tedious task of running reports. It is hot, and I'm pouring through hundreds of pages, cutting out duplicates, changing summary numbers to reflect correct counts, and changing the formatting. I am sitting here asking myself the same question I ask myself each week when we run these reports, "Isn't there a better way?".

Fast forward a few years. I have since moved on from BATC into a new role as a senior software developer for Citibank North Americas National Training Department. It's the 2004 Actuate Users Conference in Los Angeles, California. Always on the prowl for innovative reporting technologies since my encounters as BATC, I came to the conference to learn about the different products Actuate had to offer. But the one thing that always stayed at the back of my mind was that nagging desire to find an open source reporting solution. So, as a few days rolled on during the conference, I was getting a little annoyed at one of the product pitches. Therefore, on this particular day's keynote speech, the vice-president of some such department was speaking at a length about all the products that Actuate had to offer. Of course, as I was already starting to zone out, I started doodling in my notebook with little caricatures of him doing sales pitches. But suddenly, as if I had a moment of clarity from a drunken stupor, this VP said the most remarkable thing. He said, "Actuate realizes the importance for an open source reporting product, which is why we have started on an open source initiative with the Eclipse Foundation to make an open source report platform called BIRT".

It was as if he read my mind, and suddenly I couldn't help but listen to my new messiah. Apparently someone out there was listening, and someone had clued in to an untapped market. And thus was my introduction to BIRT.

While my story demonstrates the need for report developers, what about application developers? Well, let me give you a scenario. You work for the MIS/BI group in your company. For years you have leveraged hand coded, database-driven web pages for your reporting. You have come to the conclusion that development takes too long, and decided to leverage your existing J2EE platform because it is time to change your report development habits.

After extensive research, you have come up with a list of requirements. Reports must be accessible online and offer the ability to export to common desktop formats such as Microsoft Office formats and Adobe PDF. You need pagination and navigation as part of your reports. You also would like to take the hand coding of reports out of the loop to make them quicker to develop and deploy. Because you work in a development group, you need the ability to share common reporting elements among your group. You also would like the platform to be flexible and dynamic, and will also need charting capabilities. However, you have no budget at this time for an enterprise reporting platform, so you decide on an open source platform. After some research, you come across BIRT, and decide it is the way to go.

BIRT is part of Eclipse. It has a relaxed license in the form of the Eclipse Public License. As it is part of Eclipse, it has the ability to work with other Eclipse tools inside of a project. BIRT reports are simple, structured, human readable XML documents. When deploying BIRT, there are several options due to BIRT's flexible structure. It can be deployed in a standalone Java application, inside a J2EE application, and inside an OSGi/RCP application framework. It comes with a good set of tools to get you started such as a graphical report editor and an easily deployed report viewer for J2EE applications. BIRT Reports also do not require compiling. They are run and rendered from the XML report design file. The BIRT framework is also extensible. There are built-in extension points that allow application developers to add in new features such as new data sources, new rendered report output types, and new aggregations and report component types. And yes, BIRT is backed by some big players such as IBM and Actuate.

However, the thing that keeps me involved with BIRT is not the technical side. The thing that keeps me involved with BIRT is the community. There has been a very strong community built up around BIRT, centered around the BIRT Exchange and the BIRT news groups. The people who manage and maintain these communities work hard to provide the BIRT users with good examples, answering questions, and promoting the use of BIRT in their products. Users are always helping each other out and I have seen this community grow significantly over the past several years.

If you are new to BIRT, or are unfamiliar with Eclipse, you may be asking "What exactly is BIRT?" The first thing that comes to mind is a fuzzy puppet with a uni-brow. Well, this BIRT isn't related, although some may consider it fuzzy and cute. Others may think of it as mustached rouge on a race to win money, running from the law. Well, this tough guy also isn't what we are talking about.

So, what exactly is BIRT? This is a complex question to answer. Some would tell you that BIRT is a report designer built on top of Eclipse that provides users with a visual WYSIWYG design interface for rapid report development. Others may tell you it is a plugin for Apache Tomcat that allows you to view reports from J2EE web portals. And there are some who may tell you that it is a report engine that can be imported into Java applications. There is also a chart engine that may come up in some peoples answers.

Truth be told, all of these answers are correct. BIRT, which stands for Business Intelligence and Reporting Tools, is actually a development framework. Adding the word "Tools" to the title acronym is appropriate, as BIRT is in fact a collection of development tools and technologies used for report development utilizing the BIRT framework. BIRT isn't necessarily a product, but a series of core technologies that products and solutions are built on top of, similar in fashion to the Eclipse framework.

The focus of this book will be on the most visible and familiar product built with the BIRT framework, which is the BIRT Report Designer. The BIRT Report Designer is an Eclipse perspective built using a set of plugins, utilizing BIRT technologies that allow users to design reports in the BIRT XML-based document format. Inside this application, the Design Engine API, the Report Object Model, the BIRT Report Engine, the BIRT Chart Engine, and a number of other BIRT technologies are utilized to deliver a robust and capable report design environment.

Another product built with BIRT is the AJAX-based Web Viewer that comes with the report engine runtime download. This is a product that is plugged into a J2EE application container such as Apache Tomcat or BEA Weblogic that utilizes the BIRT Report Engine to run reports and output to various formats including HTML and PDF. Several other applications are built with BIRT as well. Actuate BIRT Report Designer and Actuate 9 platforms are centered around BIRT technology.

By looking at the earlier examples, we can see that the core BIRT technologies can be leveraged to build a number of business intelligence products. Hopefully this gives you an idea of what exactly BIRT is. For shorthand, we will refer to the BIRT Report Designer as BIRT throughout the book.

While the story I narrated in an earlier section of this chapter is good for telling my introduction to BIRT and demonstrates the need for open source reporting platforms, it is not the official origin of BIRT. For that, we need to turn to Actuate, and give a story that eventually intersects with my story.

Actuate has been a big player in the reporting arena for many years. Previously they specialized in the realm of enterprise reporting applications. However, seeing a ripe opportunity to address a growing market, the idea of an open source reporting platform was proposed.

Most applications have some sort of reporting requirement, and typically the success or failure of a product is hinged on how well that reporting works. The problem with this is that often times reporting in the real world is a challenging issue. Data is typically spread out and not formatted for reporting purposes. There are several different sources such as data warehouses, transactional systems providing website analysis, points of sale, data marts, and all sorts of different collections of data. To top it off, reporting requirements often change based on user requirements and market influences. Because of all this, creating flexible reports becomes a challenge.

Actuate decided to address these kinds of concerns, leveraging their years of experience to create a more modern framework for report development. Keeping in mind the issues that come across, the Actuate developers focused on addressing these issues in a manner that can assist report developers and reach a level of adoption of reports close to total. The Actuate philosophy has been stated on a number of occasions—creating products that allow its customers to get to "100% adoption" of reports. The success of reports can be measured by how well these reports are consumed, and often, if a report is successful, users will ask for more.

The decision was made to create an open source reporting platform. However, the open source market is a funny industry and so Actuate joined forces with an existing open source development community, the Eclipse Foundation, to leverage their existing framework as a base for its application.

BIRT has grown due to support and feedback from the community. The developers pay close attention to the groups out there and listen for comments about the strengths and shortcomings of BIRT in order to improve it. Unlike a lot of open source projects that have only part time developers, BIRT has a team of full time developers dedicated to improving the BIRT platform as a whole. BIRT even has a few "evangelists" dedicated to promoting the understanding and use of BIRT in the developer community.

What does BIRT stand for? Certainly it's not everyone's favorite fuzzy uni-browed Muppet in the title slide. BIRT actually stands for Business Intelligence and Reporting Tools. Although not as cute, doesn't the definition just fill you with the same warm fuzzy feelings as a Muppet? It is an open source report development environment built on top of the Eclipse framework. I have emphasized the term "environment" as some of us already know that Actuate doesn't just build "products", they build full fledged platforms for report delivery, and BIRT is no exception to this.

BIRT is what the Actuate corporation sees as the future of its product line. While some may consider it in its "infancy", BIRT is actually a fairly mature product built on top of, and in conjunction with, the Eclipse Foundation and the Eclipse Platform. In the years since BIRT's introduction at the 2004 Actuate Users Conference, BIRT has grown and matured to a full fledged reporting platform, which is something that the open source community has been severely lacking. Their announcement in 2004 really peaked my interest as I was looking for a reporting platform to integrate into Sguil. Ever since my early introduction into the world of MIS and BI, I had to struggle with the lack of a decent open source reporting product, and BIRT delivered on that and more.

At a high level, BIRT can be broken up into two main categories. The one most often considered to be"BIRT" is the report development environment that is used to design and develop reports. The graphical designer runs inside Eclipse and leverages a highly customized workspace. It uses a familiar graphical development paradigm similar to what Macromedia's Dreamweaver would use for designing web pages or Visual Basic for designing programs. It contains a number of drag-and-drop visual and data components.

The second component of BIRT is the Java APIs. This is where BIRT gets so much flexibility from. This allows BIRT to be embedded into any number of Java or J2EE applications. For instance, these APIs provide the Java Servlet Report Viewer that allows for implementing a BIRT report engine into Apache Tomcat. I have provided examples of this, and how to use these APIs on my website.

Some of the features that the BIRT Designer provides out of the box as of the 2.5 release are as follows:

BIRT provides connections to existing database platforms via JDBC. So, database back-ends such as Oracle, MySQL, and Postgres are all environments that you can connect to and start reporting. Even some of the lesser known platforms that provide JDBC drivers such as HyperSQL (the engine that Sun's Open Office uses) can be used.

For environments that don't provide JDBC, BIRT can leverage the Eclipse Foundations ODA for building custom data connections. For example, Hibernate objects can be reported on via ODA, which is an example that Jason Weathersby from the BIRT Project Management team has demonstrated. If necessary, Plain Old Java Objects (POJOs), can be used as data sources via ODA and through BIRT's scripted data source.

BIRT also allows for joined datasets. This feature allows a user to join two separate data sources, regardless of physical data location, into one logical dataset. So, say for instance you have an HR database with employee information and a training database with corporate training data, these two can be combined into a single logical dataset for use in BIRT reports.

BIRT reports can be distributed via the Web through Apache Tomcat or any J2EE platform such as IBM WebSphere, using the BIRT Report View applet. While the BIRT Web Viewer is an example application, it provides a full set of report viewing features such as page navigation, export to CSV, and PDF format exporting. Additionally, as it is open source, its features can be extended to include authorization. It offers users a set of URL parameters for customizing report calls, or you can simply include the Report Viewer classes in your existing J2EE applications. This is useful for implementing reporting into your own custom portal using technologies such as Ajax and Reportlets or to extend existing applications.

For the interface designers, things that we have come to expect from large projects such as libraries and templates are also features that BIRT has to offer. And of course, for the fan boys out there, including myself, it's open source. And those are just out of the box examples. BIRT is also extensible, with extension points available not just for ODA, but for output formats (Emitters), graphical components, aggregations, chart types, and scripting functions. For example, let's say you need your report output to go to a specialized XML format to be used in an XSLT transformation. You can create a customized Emitter plugin to output that XML.

If you've never been exposed to Eclipse before, it can seem somewhat overwhelming. In fact, I'm sure some seasoned Eclipse users would still agree. The Eclipse framework is an interesting one to work with. What started as an Integrated Development Environment (IDE) for Java programming has grown into a full fledge framework for application development. With Eclipse, it is possible to develop applications utilizing the already pioneered area of graphical development using Eclipse's SWT and extend Eclipse functionality utilizing Eclipse's Plugin architecture. Applications developed on top of Eclipse can then be deployed as extensions to an already existing Eclipse installation or distributed as Rich Client Platform (RCP) standalone applications.

There is a series of core classes that are included with each Eclipse or RCP application. These classes contain all the necessary classes for building and managing the application and the application interface building. The core application is then extended using plugins. Things such as the Content Versioning System (CVS) browser, Java IDE, C++ IDE, and various tools in the Web Tools Projects are all plugins to Eclipse.

This is how the BIRT Report Designer works. The Designer API extends the core Eclipse functionality by providing the BIRT perspective with the report palette, the report designer, and ways to execute the BIRT Viewer for report previewing. From the BIRT perspective, this provides some really interesting deployment strategies. For shops that are purely focused on report development, a BIRT RCP application can be deployed to the desktops for developers to work with and not be burdened down with any of the other Eclipse features. This is beneficial where simplicity is key and developers might be confused with Eclipse concepts such as having to change perspectives to access the full features of an Eclipse plugin.

For the more robust development house, the plugin approach would be more ideal. This is beneficial for, let's say, building a larger enterprise Web application utilizing the Eclipse Web Tools Project (WTP) for J2EE, and being able to switch over to and utilize BIRT to handle data reporting tasks for this application. This way you can get the best of both worlds, you would have the impressive set of features that WTP offers such as web service creation wizards, server deployments, and the ability to debug applications in Eclipse, and be able to have the WYSIWYG report editor of BIRT for rapid development of data driven user facing interactivity.

This also allows users to develop Java applications that leverage the BIRT Report Engine and quickly switch over to the report designer for you to have your entire development process under one roof. Additionally, you can get the added bonus of utilizing Eclipse's internal tools such as the CVS tool for team project management, or using the free Subclipse third-party plugin for subversion repositories.

Currently, BIRT already leverages ODA for data extensibility, so this is one example of how building on top of the Eclipse framework has benefited the BIRT project. By utilizing the Eclipse Data Tools Project, BIRT can leverage the already existing driver framework and not have to repeat the development cycle.

While previous versions of BIRT included WTP Integration, 2.5 has this as a separate download. This add-on provides several additional project types and deployment wizards that simplify building WTP-based projects that integrate BIRT technologies. There is also a set of JSP tag libraries that make it easy to work with BIRT in JSP pages.

As it stands right now, the BIRT report designer comes in two flavors. The distribution I will be discussing in this book is the Eclipse BIRT designer, available at http://www.eclipse.org/birt. The Eclipse version of BIRT, which is an open source release under the Eclipse public license, is the fly by the seat of your pants version of BIRT, with all the latest bells and whistles, actively supported by the BIRT community—this is where all the action is if you are interested in the development of an application. If you are a computer tweaker or you like the latest and greatest features, a stable distribution is always available. This is the most popular version of BIRT.

The second version of BIRT is the Actuate BIRT designer. Actuate BIRT is a corporate branded version of BIRT, released by Actuate—the parent company behind the BIRT project, Actuate. This version contains all the same features of the Eclipse BIRT, plus some features that are specific to the Actuate platform such as integration into Actuate Iserver. Actuate's next generation of business intelligence solutions, Actuate 9 and 10, is build on top of BIRT technology, with Actuate's BIRT Report Designer, Business Reports Tools, and Iportal products all being heavily coupled with BIRT.

For the shirts out there who won't consider a product unless there is a professional support unit to call in the middle of the night and scream bloody murder when things don't work, this is the option that you are looking for as the Actuate BIRT is fully supported by Actuate. Actuate BIRT is always based on the latest stable version of BIRT, with additional service fixes. It is also a standalone RCP product. While the Eclipse version is integrated into Eclipse and you can actively use other Eclipse products, the Actuate version is BIRT only.

The purpose of this book is to familiarize users with the BIRT Report Designer. In order to do so, I have decided to alternate between two different scenarios in order to demonstrate the capabilities of BIRT to familiarize you, the reader, with BIRT features, and to allow you an opportunity to follow along.

One set of reports will be built using the Classic Cars example database that comes with every BIRT distribution. This gives us an opportunity to allow you to follow along with examples and try them out on your own. The data schema used in Classic Cars is a simple schema, but it will require at least a basic understanding of SQL to follow along. This will allow me to give you some basic reporting examples such as listing reports, drill down reports, some basic charts, and some of the other BIRT features such as parameters, scripting, and the BIRT report emitters.

However, unlike most other books, I am also providing you an opportunity to follow along with a real life reporting scenario as well. With cooperation from the Eclipse BIRT Project Management Committee, I am also including examples of reports for the BIRT Bugzilla database. I feel these are beneficial to readers not only as a source of learning, but as an example of how the BIRT platform can be used in a real world scenario. This lets the user know that a whole lot of theory and sales pitches aren't the only thing BIRT is good for, but also demonstrates some real world usage of BIRT. This also gives us a chance to demonstrate some of the more advanced capabilities of BIRT itself.

In this chapter, we looked at the concepts of Business Intelligence and open source software, along with a brief introduction to some of the other players in the open source business intelligence community such as JasperReports and Pentaho. Finally, we got a little background information on some real world requirements for open source reporting projects.