Business Intelligence for Programmers

Introduction

Business Intelligence Reporting is an important feature of any information system. In order to fulfill the reporting requirements of end-users, programmers spend good amount of time writing complex queries. Traditional Object-Relational Mapping tools provide little help to accomplish this task. In this post, we show how programmers can retrieve and handle  reporting data using Hibernate (Most popular ORM tool for Java). Then we show how Data Access Agility can help programmers retrieve and handle reporting data more efficiently.

The Example

The model that we will use for our example is composed of two entities: Customer and Order. The following entity model shows the attributes that belong to each entity. As you might expect, a customer can make multiple orders and each order is associated with only one customer.

Our example is a report that shall include the following fields:

  • Customer Id, Name, and Country
  • Total purchase amount per customer
  • Average purchase amount per customer
  • Number of orders made by each customer

The report shall satisfy the following requirements:

  • Customers who have a total purchase amount less than $100 shall be excluded
  • Customers who have higher total purchase amount must be listed first
  • The report will be generated for a specific country or for all countries

Hibernate

Hibernate provides several options to retrieve and handle data. The following code uses one of those options to return the data we need for our report

Model Class


public class CustomerStats {
   private final int customerId;
   private final String customerName;
   private final String customerCountry;
   private final double totalPurchaseAmount;
   private final double averagePurchaseAmount;
   private final int ordersCount;

   public CallStatistics(int customerId, String customerName, String customerCountry, double totalPurchaseAmount, double averagePurchaseAmount, int ordersCount) {
      this.customerId= customerId;
      this.customerName= customerName;
      this.customerCountry= customerCountry;
      this.totalPurchaseAmount= totalPurchaseAmount;
      this.averagePurchaseAmount= averagePurchaseAmount;
      this.ordersCount= ordersCount;
   }
}

Query Code

List<CustomerStats> customerStats = entityManager.createQuery(
   "SELECT new CustomerStats(" +
       "c.id, " +
       "c.name, " +
       "c.country, " +
       "SUM(o.totalAmount), " +
       "AVG(o.totalAmount), " +
       "COUNT(o.number)" +
   ") " +
   "FROM Customer c INNER JOIN Order o ON o.customerId = c.id " +
   "WHERE c.country = :country OR :country IS NULL " +
   "GROUP BY c.id, c.name, c.country " +
   "HAVING SUM(o.totalAmount) >= 100 " +
   "ORDER BY SUM(o.totalAmount) DESC", CustomerStats.class)
   .setParameter("country", "USA");
   .getResultList();

There are several alternatives to this approach. For example, programmer can use native SQL queries instead of HQL or PQL queries. In this case, programmers shall use annotations (E.g. SqlResultsetMapping) to map the output to Java model classes. Using criteria queries is also a viable option in our case.

Despite the variety of available options, all of them require:

  1. Writing query; either a native SQL query, an HQL query, or a Criteria query
  2. Writing Java code to execute the query
  3. Creating Java model class that holds the output data: Sometimes, this step could be optional if the programmer feels comfortable dealing with variables of type Object[] or List<Object[]>. However, for maintainability purposes, this step becomes necessary.

As you might note, this approach has several disadvantages. The foremost are:

  1. Writing queries and Java code is time-consuming
  2. Writing bugs-free queries and code is challenging
  3. As the number of output and filter expressions increases, code will become much more complicated

In the next section, we show how to use Data Access Agility to generate data access code for complex queries.

Data Access Agility

Data Access Agility is a cloud tool that allows you to generate the code of a highly customized data access layer. With Data Access Agility, you do not need to write any query or code for most of your data access needs. In addition to generating highly customized CRUD operations, Data Access Agility has a powerful business intelligence panel that allows programmers to generate code for complex queries. So instead of writing the above Java code, programmers can retrieve and handle the reporting data by making simple method calls. For example, the following code shows how to retrieve and handle the data for the proposed report:


List<GetCustomersStatsDTO> stats = SalesReportsData.GetCustomersStats("USA");

As you can see, no code should be written by programmers except the code that calls the auto generated methods. On the other hand, Data Access Agility generates the following code for you:

  • Native SQL queries
  • Data access objects that wrap the auto generated SQL queries (E.g. SalesReportsData)
  • Model classes that will hold the data (E.g. GetCustomersStatsDTO)

Using Data Access Agility has the following advantages:

  • Reducing software development time
  • Eliminating bugs found in data access code
  • Improving maintainability; where data access code is fully encapsulated
  • Standardizing the way we access data

In the next section, we will explain how to generate a data access code for the proposed report.

Getting Started with Business Intelligence Panel

For information on how to get started with Data Access Agility, watch the tutorial video How to Use Data Access Agility. This short video  (~4 Minutes) will show you how to setup your first project in Data Access Agility.

Once the database schema of your project is ready, go to Business Intelligence panel and follow the steps below.

Step 1: Create a New Business Intelligence Object

Business intelligence objects encapsulate several queries. Make sure to checkout the project before you can add a new BI object, then click on the [+] button.

Step 2: Create a New Query

To add new query, click on the Add Query button. Each query represents one report.

Step 3: Enter a Name and Select a Return Type

Step 4: Select the Entities

Each entity represents a table. You can select the same entity multiple times. However, you have to assign unique aliases to them.

Step 5: Specify the Parameters

Step 6: Specify the Output Expressions

The BI expression editor allows you to specify complex output expressions

Step 7: Specify the Filter Expressions

Step 8: Specify the Sorting Expressions

Step 9: Save the Query and Check In the Project

You will need to check in the project before you can generate code for your query. To do that, click on the Check In button and confirm your decision.

Step 10: Generate Code

You can generate code through the Release Management panel. For information on how to generate code, watch the tutorial video How to Use Data Access Agility.

Conclusion

Using Data Access Agility to manipulate databases is easy and convenient. It has several advantages over traditional Object-Relational Mapping tools. The foremost is the less amount of code that should be written. Create your first project for free by visiting the tool website http://www.dataaccessagility.com.