BIRT, Pentaho Conclusion

Most detail and the best comparison matrix

My conclusion (just simple overview)

Trying Pentaho Enterprise

Trying Pentaho Business Analytics
The comparison between Community and Enterprise

1 Installation and configuration

Easy

Getting started

It seems that report designer and other tools are the same as in community edition except server side. Son in this post I concentrate on server side

2 Data Connections

2.1 Supported data sources 

You have four alternatives

2.2 Creating a data connection

You start with JDBC connection. (The most known drivers are already installed). You can directly connect to metadata or Mondrian (Analysis) cube, when you have made them before hand.

You can also use data source Wizard

This way using wizard you can create metadata view in server (the same as with little less functionality than  using metadata editor)

2.3 Connection re-usability

Yes you can use connections, metadata etc

3 Functionality and visualizations

When you start creating new file, you have three choices

3 Functionality and visualizations -ANALYSIS REPORT

You can drop & drag objects

You can create simple charts 

There are also tooltips on charts and keep only, exclude

3.1 Parameters

Yes you can add parameters

3.2 Drilling

Yes works on graphs

3.3 Quick Filters (drop-down, search)

Yes there are quickfilters, you can see them on top of graph

3.4 Geospatial analysis (wms)

Yes you can create graphs, Estonian countys I could not get to work. Uses Google maps

3.5 Complex calculations

There doesn't seem to have any calculated fields opportunity, you can add it in SQL
There are some basic options

3.6 Other

You can disable auto refresh (shows warning)

3 Functionality and visualizations - INTERACTIVE REPORT

For creating reports

4 Functionality and visualizations - DASHBOARDS

You can put together a dashboard. You can use global filters by parameters

When the end user watches interactive or analysis report, then she/he sees the same view as editor. Dashboard is exception

5 Output

5.1 Report output formats

Pdf, Excel, csv (html)

6 Server (Viewer Toolkit)

6.2 Query Logging

Has its own audit repository, you can create reports on it

7 Ease of use

I must say that all looks simple (similar to  Lumira the most). Using is simple and it looks like quite fast. But there are less opportunities than Tableau. Server side looks suitable for smaller companies. Although it looks much more simpler and more attracting, I seems that BIRT has some more functionality

8 Performance

When trying with 20 milj, the query was successful, as it used metadata model and query was made in database (sums)

select "city_isp_daily_speeds"."country_code" as "c0", sum("city_isp_daily_speeds"."n_upload_kbps") as "m0" from "dbo"."city_isp_daily_speeds" as "city_isp_daily_speeds" group by "city_isp_daily_speeds"."country_code"

Trying Pentaho (open source)

PENTAHO REPORT DESIGNER

Trying open source Pentaho Report Designer

1 Installation and configuration

Installation was easy, just need to have Java.

Getting started

The interface looks logical. Create new report and start with adding data source

2 Data Connections

2.1 Supported data sources 

There are many opportunities

And also JDBC connection (you have to add driver for lib/jdbc folder), so that JDBC connection works

2.2 Creating a data connection

Creating data connection is easy, look previous picture

Then you can create data sets - query.

There is also a simple Query Designer graphical interface (join can not be edited). You can rename attributes

2.3 Connection re-usability

It doesn't seem, that I could use data sets defined before

In blog I found " Styles, data sources, queries, parameters and parameter groups can be saved and loaded when designing a report."

http://www.innoventsolutions.com/comparison-matrix.html

2.5 Mergeing/blending

Cannot join multiple data sources.
Only in chart (bars and line) - you can use secondary data source

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

There doesn't seem to be any intelligence background query engine. The query that is made for data set is used. If you want to group data on report, then to get it work, you have to order data the same way

You can add calculations, for example running sum (the summing is made in Pentaho)

But calculations work quite good, you can define also partitioning

3 Functionality

3.1 Parameters

You can add parameters

You have to create your own distinct query to retrieve unic values for creating parameter and then add condition to main query.

3.2 Drilling

There is no simple drilling functionality, you can use HTML or XML to accomplish something like drilling.

"Use anchors to build internal link targets in your detailed report. Use the HTML anchor style to construct a unique, but predictable, identifier for your link target. Use a formula on that style for that, something like

To get a summary of your data, add a subreport to the report-header. Inside the subreport, add the links to your anchors of your main-report."


"Usually for interactive drilling, especially when working against a data-warehouse, we recommend Analyzer (or Saiku, when you are strictly on the community edition). 


These tools dont try to be pixel perfect and optimized for printing, they are optimized for interactivity and quick response times. And they do drill down quickly and efficiently"

One opportunity is to use Mondrian cubes with JPivot (but this is not done in  Designer - it's in Server)...

3.3 Drilling through reports

Yes, exists and you can do it through formula.

=DRILLDOWN("local-prpt"; NA(); {"oStatus"; ["chart::category-key"] | "showParameters"; [STATUS] | "solution"; "steel-wheels" | "path"; "reports" | "name"; "Order Status.prpt"})

3.4 Quick Filters (drop-down, search)

There is no quick filters

3.6 Geospatial analysis (wms)

There seems no easy way to do it in free edition

"We use a series of Pentaho action sequences, which are instructions to Pentaho's solution engine, to retrieve the western region customer and customer address information. We then use Google's GClientGeocoder class to change our address information into latitude and longitude coordinates. These coordinates are passed to the Google Maps API when the browser's onload() event is fired, and the coordinates are plotted as points on the map."

4 Visualizations

4.1 Visualizations

4.2 Complex calculations

You can your own fields (predefined), but there is not very wide selection. You can write java, phyton, SQL etc

5 Output

5.1 Report output formats

There are all export functionalities. You have to put some attention on report pixel design so that in Excel there wouldn't be so many empty rows.

6 Server (BI server)

The web side looks nice. There is folder hierarchy, favourites, recents

6.1 Deploy Report

Quite simple, you choose server and folder. It seems (also info from blogs) that you have to have the same data source defined in server to view your report on server.

6.2 Query Logging

You can configure separate log file for SQL

http://bi-thoughts.com/2011/02/15/pentaho-enabling-sql-logging/

6.3 Scheduling

Scheduling works, but the thing is, that it creates separate file work every schedule

6.4 Personalized scheduling (report Bursting)

It is doable somehow, didn't find any simple tutorial how to do it in current version. It seems that you have to have Design Studio to do this and to create Action Sequences.

"Design Studio is Eclipse based Application and plug-in used to create Action Sequences"

"Activities that the Pentaho BI Platform performs are defined by Action Sequence XML documents.  Action Sequences define activities such as database queries, report generation, email actions and the order in which they occur. Data can be passed between Actions within an Action Sequence or between executions of other Action Sequences.  They define the inputs that must be available prior to execution, the processes that will be performed and the outputs that will be available to other Action Sequences after execution is complete."

"You can use business rules, database queries, web service calls, scripting, and workflows to customize the system as much as you need.

Since the Burst Rule and the Burst Process are separate processes Burst Rule could be implemented in any custom application and messaging, web services, or workflow orchestration used to initiate each Burst Process.

Pentaho supports Report bursting by providing abilities in Data Integration tool and Design studio."'

"Business rules can be used to determine the recipient(s) for any report or parameterized report. Pentaho includes several business rules engines for flexibility"

"Your issue is pretty similar to ours. We had ACCOUNT_NUMBER_1 -> userA, userB. ACCOUNT_NUMBER_2 -> userC, userD ...

You're going to have to get really creative about how you want to go about this without implementing your own JDBC authentication plugin for the BI server. One thing that we did was create a table on the database that mapped ACCOUNT_NUMBER to Pentaho Roles. Based on what role the user was in, dictated which ACCOUNT_NUMBER showed in the drop downs. Additionally, we set folders up using those roles.

However, that leads to a lot of role explosions. In short the only "clean" way of doing what you want is to come up with your own means of authenticating users or come up with some sort of clever work around. Pentaho's security system is a very basic, lowest common denominator one. However, that's pretty much comes with any BI platform as the goal is to hit the lowest hanging fruit first."

6.5 Security

Not very advanced one

6. 6 Embedded reporting

Pentaho Reporting includes report navigation and report viewer components that can be integrated into portals or web pages. We also include JSR-168 compatible portlets and example Java Server Pages (JSPs) that use these components.

6. 6 Other

You can use Community Dashaboard Editor and Framework to create dashbaords in opern source version, but it is just not so simple as in enterprise version. Also you can use plug in's and JPivot for drilling

7 Ease of use

In conclusion, Pentaho seems a little bit simpler than BIRT and free edition looks quite nice, but is seems that it lacks from many aspects (no blending, interactivity, less options in chart making, no simple crosstab functionality built in, for grouping, sorting you have to change query etc)

http://www.innoventsolutions.com/comparison-matrix.html

8 Performance

with 125 000 rows, it was ok, with 14 miljon rows, it took a lot of time

9 Pricing and products

9.1 Business Analytics- BI server

Pentaho's modern, simplified, and interactive approach empowers business users to access, discover and blend any data types regardless of their size. With a full spectrum of increasingly advanced analytics tools, from basic reports to predictive modeling, users can help themselves to analyze and visualize data across multiple measures and dimensions without being dependent on IT.

9.2 Data Integrator

Pentaho’s Data Integration, also known as Kettle, delivers powerful extraction, transformation, and loading (ETL) capabilities. You can use this stand-alone application to visually design transforms and jobs that extract your existing data and make it available for easy reporting and analysis.

9.3 Report Designer

The Report Designer is a graphical tool that generates reports from data streamed through the Data Integration engine without the need for any intermediate staging tables. You can output your reports in several formats, including PDF, Excel, HTML, rich-text-file, XML. and CSV

9.4 Aggregation Designer

The Aggregation Designer provides a simple interface that allows you to create and deploy aggregate tables to improve the performance of your Pentaho Analysis (Mondrian) OLAP cubes.

9.5 Schema Workbench

You can create OLAP cubes

The Schema Workbench is a visual design interface that allows you to create and test Mondrian OLAP cube schemas. You can present your data multi-dimensionally and let users select which dimensions and measures to explore, interactively drilling into cross-tabulating data.

9.6 Metadata Editor

Metadata Editor is a tool that simplifies your experience when creating reports, and allows you to build metadata domains and relational data models.

Tried, looks simple, you can create logical model on physical tables, different business views, add descriptions and you can add custom attributes, default aggregations, different languages

Then you can use view you made in Metadata Editor in Report Designer.
You choose connection Metadata and add query

In here you can also use graphical view, you select your models and columns. In this way the query that is executed is group by, order by in the database already

Trying BIRT Designer Pro

Trying  BIRT BI tool. here I bring out differences from BIRT Designer, what is the same, this I will not comment again

1 Installation and configuration

Installation is easy

Getting started

BIRT Designer Pro looks and functions the same as BIRT Designer. You can use also open BIRT Designer files in Professional. But Vice verse - there were errors, themes missing etc

2 Data Connections

Connection making, data sets etc is the same

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

All the same. In professional you have also Union capability - union different data sets not just blending

It seems that from database it by default queries all data - the data set query. When you filter data in view mode, then you can filter data by all data that is in data set, no query is made to database

See your query - > You can see query execution profile for every element

In Pro there is for creating dashboards  Data Object (consists of data source, data set and data model, shared dimensions) functionality. 

Building a data object entails creating a data object file, then adding data sources, data sets, data models, and cubes to the data object. To add these data items, you can:
*Create new data items within the data object.
*Export data items in reports or libraries to the data object.
Once you create a data object, a data source, and data sets, you can use the data sets to build a data model. Using a data model as a data source is preferable to using a data cube or an information object for the following reasons:
*Data model queries are optimized and therefore offer better performance.
*When creating crosstab reports, a data model offers more flexibility in the choice of dimensions and measures.
Typically, a data model consists of several data sets linked by joins. 

 In Actuate BIRT (commercial) there is way to dynamically change queries based on content of charts and/or tables.

When you create each table as data set and join them in data model, then it queries database only the fields it needs (but no group by ). But join culling does not work. When you use disable bush down, then join is made in BIRT

If you use a JDBC Database Connection for Query Builder data source, in many cases, joins in a data model are pushed down to the database. If either of the following statements is true, disabling join push-down may improve query performance:
*The join columns are not indexed in the underlying database.
*Query performance is unacceptable.

In data model you can create only inner joins.

You define joins between data sets in a data model in the same way that you define joins between tables in a SQL query

When you have different data sets, then it detects loops and you have to remove one join to remove loop. You can create aliases.

3 Functionality

3.2 Drilling

When using data cubes, then you can drill up/down on chart on report

You cannot drill on the table, but you have to open analyze. When in there you drill and end analyzing and save ok - then in report you see this down drill values like HIIU >Mehed/Naised

3.6 Geospatial analysis 

You can create simple map. You can map locations

4 Visualizations

4.1 Visualizations

There are quite many opportunities and although the visual side is quite attractive.
You can also add gadgets

When you view your report, it is quite nice, now you can use enable interactivity. The end user can change chart type (simple), filter, top/bottom N

When you have crosstab in view, then you can use interactive crosstabs to create/modify view

4.2 Dashboards

Dashboard you can make through web browser. You can add filters, that apply to all visuals. It is quite good and looks good. But looks slow, crashed for me.

SQL in the background is not only by data set but is created dynamically. But the problem is that it selects relevant fields but no group by or sum is used. And when you filter then also query is made in database

You can select area and zoom

Drilling on charts works (it seemed, that when i changed drill hierarchy on Designer and then wanted to see change id dashboard - i didn't see this, i had to make new dashboard , but maybe i didn't know how)

6 Server BIRT iHUB 

http://www.actuate.com/download/brochures-datasheets/BIRT-iHub-DataSheet.pdf

Server side is simple but visualizations look good (slow in my computer)

You can schedule and edit etc report

6.1 Deploy Report

Yes you can save report to server. You can create reports/dashboards in server also

6.2 Personalized scheduling (report Bursting)

You can schedule reports
Simple version

Advanced version

Row based security

"1) Page level security - Actuate iServer express V10 offers Page Level Security, a unique and powerful capability that generates a single document, which includes security rules that determine which pages a user can see, print or save. Page Level Security allows a single piece of content to be generated and deployed that addresses the secure information distribution needs of many users. PLS reduces the load on the underlying data source by invoking a single query. PLS reduces the number of report instances to one. And, because a single document is generated, PLS eliminates the management necessity to secure individual access locations.

2) Row level security - BIRT reports deployed via Actuate iServer express V10 can leverage enhanced data access functionality whereby at execution time, the engine will automatically pass user specific credentials in the filter clause of the report query. Thus the data available for the report will be automatically filtered based on the user.

3) Report parameters - Developers can also design reports with specific parameters whose values are then passed along in the WHERE clause of the report query. This ensures that the dataset available for the report is automatically filtered based on who the user is and what he/she is trying to do."

6.3 Metadata repo reading

No metadata layer 

7 Ease of use

The result is more beautiful, but to get things done, is the same as in free version. Yes you have more opportunities like maps and dashboards - dashboard is like Tableau, but with less functionality

8 Performance

Professional was quicker (visualization rendering especially)  then the free version. Using 128 000 rows, it was quite quick, which was surprising

Data containing 14 milj rows - BIRT couldn't read it - just hang

9 BIRT Analytics

Looks like more for analyzing, mining not for so much presenting. Capabilites - trend, forecast, bands etc.