Correct way to get data for BIRT reporting

We have a system which contains 4 Hierarchies. 1. Senior Manager 2. Manager 3. Supervisor 4. Employee

Data related to all these are stored in different tables with respective details. There is a single address table which contains address of all these hierarchies. Sample Data Set

We would be able to do a count on each IDs by City, State and Country. Since OLAP cubes cannot be directly used as data set in BIRT, Shall I combine all this data in one single table and load it and then create a cube with the following Hierarchy?

COUNTRY
 STATE
  CITY
   SENIOR MANAGER
    MANAGER
     SUPERVISOR
      EMPLOYEE

and add the below in summary fields.

SENIOR MANAGER COUNT
MANAGER COUNT
SUPERVISOR COUNT
EMPLOYEE COUNT

The goal is to report count of employee, by Territory or by role(senior manager, manager etc).

Thanks.

Answer

Usually it is best to do aggregation on the DB side, e.g. use SQL JOIN syntax and GROUP BY.

If you need aggregation data and details, you have two choices (depending on your DB, I don’t know if MySQL supports this):

You can either select only the details (all in one query) and use BIRT’s grouping and aggregation features on table or list items to get the aggregations alongside.

Or you can use SQL analytic functions (similar to group functions, but with an OVER(…) clause) to do it all in SQL and let BIRT just emit the results. You still need BIRT’s grouping, though, to output the aggregations in the correct header or footer lines.

As a rule of thumb, it is usually best to let the DB do as much work as possible. This is more reusable (say if you decide to use a different reporting library later) and also much faster, as this is what a RDBMS is designed for.

However, you can also nest table or list items in the BIRT report layout (a feature which is hardly known). This allows you to develop master/detail reports which use individual SQL queries for each level. But if you want aggregations, things will be much more complicated with this approach.