« Guest Blogger: Ray Gans, Community Manager for the BIRT Community | Main | In a ”Stream” of Good News: Sybase acquires Aleri Technology »
February 3, 2010
Deriving Business Intelligence with BIRT
Access to your data and confidence in its integrity is vital. Without the ability to derive intelligence from your data, however, it is just data. Rich visualization tools, such as BIRT, are critical for getting the most from the data you collect and manage daily. And you want those tools to be accessible and easy to use. Recently, I created a common type of BI report, called a Master/Detail report, using BIRT. I was impressed by the capabilities of the tool, and how quickly I was able to build my report.
BIRT is an open source, Eclipse-based reporting system, with features such as report layout, data access and scripting. Sybase continues to stay true to its goal of providing a complete and integrated tooling platform with Sybase WorkSpace. If you have WorkSpace Data Analytics or WorkSpace Data Analytics Enterprise licensing, the BIRT report designer is automatically installed when you install Sybase WorkSpace 2.5. You can also download and install it from the Eclipse Web site.
My goal was to visualize and understand some order processing data stored in my Sybase IQ database. I wanted to create a bar chart of order quantities categorized by country. Then, I wanted to view the details about the orders for a particular country by clicking on a bar in my bar chart, and bringing up a table of order breakdowns by year/quarter and product category:
I started by creating my master report – a bar chart showing order totals by country. To accomplish this, I created a data source (a connection to my Sybase IQ database), a data set (a SQL query), and added a bar chart element from the BIRT palette to my report canvas. Then I associated data elements delivered from my query with the axes of my bar chart. I wanted countries to show up on the X axis, and order totals on my Y axis. A simple drag and drop of data items onto the bar chart skeleton was all I needed to do:
Then I created my detail report. My detail report is like a spreadsheet, with time along the Y axis, and product categories along the X axis. The cells in the chart contain the product totals. To build this report, I used a report element called a dynamic cross tab. This is like a pivot table in Excel. It is not a static chart, but is built upon a data cube. The data cube in turn is based upon a SQL query or stored procedure call. Once you have the data in the cube, you can dynamically move data elements from the cube into various locations in the report. You can easily change what is displayed along the different axes, and what appears in the cells:
Now that I had the master and detail reports, I linked them together by configuring ”interactivity” on the bars of the bar chart in the master report. I specified ”drill down on mouse click”, passing a parameter of country name:
Once I had configured interactivity, I tested the reports within the tool. A click on a bar brought up the correct detail report for the particular country. This was all very easy to accomplish, and in no time, I had a valuable BI report. So, spice up your analytics with Sybase WorkSpace and BIRT, and derive intelligence from your data now.
You can read more about BIRT in this blog from Ray Gans, Community Manager for the BIRT Community:
Courtney Claussen
Posted by Sybase IQ at February 3, 2010 8:25 PM
Twitter 
LinkedIn 
YouTube