Trying BIRT Designer

Trying open source BIRT BI tool

1 Installation and configuration

Installation took some time, as it needs Java Development Kit and paths that are defined correctly.
Also had some problems with ODBC connection to my Microsoft SQL server 2014. But changing Windows authentication to SQL Server - then I got it work.

Getting started

You have to create project -> then report -> then you can add data sources

2 Data Connections

2.1 Supported data sources 

You can add many data sources and all different type, if you want to (Excel, databases etc)

Wizards to set up access to the following types of data sources:

• JDBC data sources
• Text files
• XML documents
• Web services

Developers can also use BIRT’s Open Data Access (ODA) framework to write custom data drivers to access data from any source, including data stored in proprietary systems

2.2 Creating a data connection

I have to have driver jar file to point to and you can ask the report user to insert database password and username, if you want to

2.3 Connection re-usability

You can create connection profile and then use profile store as creating data source for your report

2.4 Data Sets

After creating data connection you have to do data set. Reusable vist?

A data set is an object that defines all the data that is available to a report.
You can create as many data sets as are necessary for a report

There is general ODBC connection data set

And ODBC connection with Query Builder, where you can add tables (doesn't seem to recognize key columns)  but there is no nice visual modeling opportunities

You can create loop or whatever you like, the SQL created is used for your report. You cannot use the same table twice (like alias). The SQL window is connected to visual, if you change SQL then visual is updated

You can add calculated fileds, parameters and filters to your data set. If your users are from different countries then you can show different field names

Connecting to Excel (168 000 rows) - got and exception - this means you have to change your ini file

-Xms256m
-Xmx1024m
-XX:MaxPermSize=256m

There is also data cubes - you can create them by scratch or when you create cross-tab, then also cube is created in background. In cube you say which is your measures and dimensions and create hierarchies.

Data Connection -> Data Set -> Data Cube

When creating data cube and there is date field, it lets you choose do you want to year, quarter etc or choose multi (this is no drilling,)

2.5 Mergeing/blending

If you have connected to data source, then you make data set and these can be joint to each other. Data joints can be connected through left, right, outer, full outer join, but only by one column join (solution would be to concatenate columns and then join)

"You can only produce joint datasets from no more than two existing datasets. However, joint datasets can themselves be included as input datasets for other joint datasets - so if you need to join three datasets together, this can be achieved by linking two of them into one joint dataset, then creating a new joint dataset based on the first joint dataset and the third original dataset"

2.6  Complex data model (loops, shortcut joins, multi fact- multipass SQL) and calculations (see your query)

All loops are allowed, the data set query you create- this is all run in database. Yes you can use shortcut joins but all those joins are used and  your data filtered

It seems that from database it always queries all data - the SQL that goes to database is always the same that in data set SQL is (with all joins, even though I don't query data from there

And it seems that even though i don't make changes to report and view report, then it always retrieves data from database

I asked it from forum and the answer is

In Open Source BIRT the query is always what you have in the data set editor. In Actuate BIRT (commercial) there is way to dynamically change queries based on content of charts and/or tables.

I tried to create one chart on data about summing fact values and other chart on count distinct on dimension ID's - and all these from same data set. All was done correctly, the calculation was done inside the tool

See your query - > Data set is the query that is run in database

3 Functionality

3.1 Parameters

When I want user to filter values, then i have to modify SQL and then create a parameter

When you want to use many values, then you have to do like this.

SELECT CustomerName from customers where customers.City IN ( "+ "'" + params["City1"] + "' , '" + params["City2"] +"' , '" + params["City3"] + "' )"

You can also only report parameters

3.2 Drilling

There is no drilling by default. You can "drill" using bookmarks on report, but this is not real drilling

You could write some javascript to create a more like drilling effect

http://developer.actuate.com/community/forum/index.php?/files/file/611-mouse-over-details-for-a-chart/

http://developer.actuate.com/be/documentation/ihub3-dev/ADG/adg72/index.html#page/ADG/JSAPICrosstabs.1.13.html

Birt calls it to expand or collapse a member

You can use something like this (you need to use javascript) or use drilling through reports

3.3 Drilling through reports

Drilling through reports 

The functionality is there and works

3.4 Quick Filters (drop-down, search)

There is different kind of filters, drop-down hierarchical etc. You can group parameters and so on. But they are in default always asked when viewing report and you can change them when refreshing report

3.6 Geospatial analysis (wms)

You can use google maps with some coding and configuration - but seems that in free version there is no easy way

http://developer.actuate.com/community/forum/index.php?/files/file/1071-map-it-google-maps/

The Google Maps rendered are live maps. When combined with Actuate Interactive Viewer, you can also filter and conditionally highlight points on map. Can also be used to display color shaded areas of varying densities, as well as standard pins and circles of dynamic radius on all maps.

There is also product Map Intelligence http://www.integeo.com/mi-for-bi/actuate

4 Visualizations

4.1 Visualizations

There are quite many opportunities and although the visual side is not so attractive, with some coding and samples in the web you can create also more beautiful harts, but it takes some coding and time

4.2 Complex calculations

Runningsum is really easy to implement, all whatever other functions. Using groups you can give the partition functionality

If you want more complex calculations, then you have to write some script, for example year-to year change

5 Output

5.1 Report output formats

You can export the view to any format you wish, it looks ok and you can also export only data

You can save and view a report in the following formats:
*HTML
*Microsoft Excel document (.xls)
*Microsoft PowerPoint document (.ppt)
*Microsoft Word document (.doc)
*PDF
*Postscript

6 Server (Viewer Toolkit)

6.1 Deploy Report

In free version there is no interactive viewing enabled. What you can do/see:

• You can see table of contents and use links
• Export data and content
• Create report link or embedded version
• Go through pages
• Print

6.2 Query Logging

"If you want to log the SQL query text, set logging on the  org.eclipse.birt.report.data.oda.jdbc.Statement , which is responsible for  preparing the SQL queries."

Or you can also save SQL

importPackage(Packages.java.io); 
logFileName = "c:/tmp/birt.log";   
out = new PrintWriter(new FileWriter(logFileName, true));   
out.println("entering DataSet.beforeOpen()");   
out.println("queryText=" + this.queryText); 
out.println("params[param_1]=" + this.getInputParameterValue("param_1")); 
out.close();

6.2 Scheduling

There is no in built scheduling, you can do this by windows scheduler etc.

6.3 Personalized scheduling (report Bursting)

Row based security

"You would have to pass the user into the report either as a parameter or a value in the report context. You would then have to use scripting to affect your query based on the user id passed into the report."

Metadata repo reading

No metadata layer 

6. 4 Embedded reporting

Yes, Linking works and tooltips
Using the JSAPI to embed entire reports or individual bookmarked elements from a report

7 Ease of use

I think there are quite lot of functionality and options and also you can always use javascript. 

The person who uses it must be IT person definitely except when you only want to do report lists and very very simple charts.

You also need to know javascript/java if you want to do some more complex reports or more beautiful or interactive.

8 Performance

Using 128 000 rows, it was quite quick, which was surprising
Data containing 14 milj rows - BIRT couldn't read it - After changing ini file parameters (suggested by others), still I had no results

9 Pricing and products

REPORT CREATION

9.1 BIRT Designer FREE

Eclipse open-source BIRT Designer equips developers with everything they need to access data sources; transform, integrate and apply business logic to data; secure, filter, format and present the resulting reports to users.

9.2 BIRT Designer Pro

The BIRT Designer Pro shares all the features of the Eclipse BIRT designer, and adds hundreds of HTML5-powered charts, gadgets and maps; commercial data drivers; a metadata layer, impact analysis; and cube design and caching features for creating interactive and analytic content.

9.3 Report Studio

Web-based, part of iHUB

Report Studio is a self-service, web-based, ad hoc reporting application that allows business users to create dynamic data visualizations without the need to rely on IT

9.4 BIRT analytics

BIRT Analytics is the first analytic tool to use ALL data, organized in a columnar database and apply prebuilt analytic algorithms with a visual interface
to provide valuable insights for the business analyst to make strategic decisions instantaneously

REPORT VIEWING

9.5 BIRT Runtime Engine FREE

The BIRT Runtime Engine is an API-instructed JavaEE engine that executes and renders report designs built within the BIRT Designer to produce HTML, PDF, or Microsoft Office outputs. Referred to as the BIRT Engine, this open source runtime library simply needs to be hosted within a larger application server such as Tomcat, JBoss or Weblogic in order to provide dev teams a means to generate BIRT Reports repeatably and reliably.

9.6 BIRT Viewer Toolkit FREE

BIRT Viewer Toolkit (BVT) is a free toolkit that was created to enhance the BIRT experience for open-source users. The toolkit provides use of the BIRT Viewer, for a cleaner, more modern look and feel. It also allows open-source users to utilize the JavaScript API (JSAPI) to more easily embed BIRT data visualizations into many different types of applications. 

9.8 BIRT iHub

Build intuitive, secure, and scalable data visualizations that provide improved brand experiences and personalized analytics for your customers, partners, and employees.

BIRT Studio: Ad Hoc Query

Browser user interface (BUI) for creating, modifying, and viewing BIRT content.

BIRT Engine

An open source engine that provides developers the utmost flexibility at no cost.

BIRT Viewer

This freeware allows users to view BIRT content with simple user interface (UI).

BIRT Interactive Viewer

Users can view and modify their content with this presentation UI tool.

BIRT Dashboards

Create dashboards and scorecards using BIRT content or other third-party content: other feeds, supplied gadgets from Actuate, or Google Gadgets™.

Mobile Options

Application options for mobile viewing enable access to BIRT content anywhere and anytime on nearly any mobile device, including: iPhone®, iPad®, and Android™ devices.

BIRT Analytics

Powerful business analytics tool that allows any user to perform real-time visual data mining and predictive analytics easily and effectively.

9.8 BIRT onDemand 

BIRT onDemand is a hosted service of the BIRT iHub and its suite of visualizations including Dashboards, ad-hoc report Studio, and Interactive Viewer BIRT onDemand provides anyone, anywhere with immediate, on-demand content from a fully managed Actuate implementation. With BIRT onDemand, IT can easily and efficiently leverage pre-existing BIRT reports and designs, eliminating the need to recreate company ass