Sample ETL Chainset for Batch Report Generation

[Irfan]

This guide explains how to use an ETL chainset to automate batch report generation, where multiple reports of the same type are created using different data records.

Additional Setup Notes

• To setup the FruitSales test dataset, place the entire batch folder inside /ElixirSamples/batch of the Ambience/Repertoire installation.

• The provided .sql script creates a table called FruitSales with company names (A, B, C) and their yearly fruit sales data (1997–2000). It includes records for various fruits under each company. This table is used in the ETL to generate batch reports grouped by the Company column. Each report will show the fruit sales per company and be saved in a folder named after that company under the current date.

• Please ensure your JDBC drivers are properly set up. You can follow the setup guide here:

  • Creating an Oracle JDBC connection pool - Ambience 2020+ - Elixir Support Forum
  • Creating a Postgresql JDBC connection pool - Ambience 2020+ - Elixir Support Forum
  • Creating a MSSQL JDBC connection pool - Ambience 2020+ - Elixir Support Forum

Steps to configure ETL

1. Download and upload the attached ETL chainset attached sample via the ETL module in the Ambience/Repertoire UI.
   

   

2. Once the chainset has been uploaded, click on it to view the steps. Click the pencil icon to edit the first step labeled “JDBC Definition.”
   Select your JDBC database from the dropdown list. If it does not appear, ensure that your JDBC drivers are properly set up.

3. In the SQL box, enter the query to retrieve your data. In this example, we are using the FruitSales sample data.

4. Edit the third step (JSON Record) to define your report generation parameters:

• Set the path to the RML template you want to use.
• Define the MIME type (e.g., application/pdf, text/csv, etc.).
• Specify the main key placeholder as this will determine how each report is split to their corresponding data.

5. The Date Today step retrieves the current date, and the String Join step combines it with the main key to dynamically create the folder structure. As shown in the example, each report is stored inside a subfolder named after the main key (e.g., A, B, C), which is nested under a date folder (e.g., 2025-05-27). This helps organize the reports by date and category.

6. In the String Add Prefix step, specify the target output folder where reports will be saved. This folder must exist inside the Repertoire installation directory.

NOTE: If you need to output to another location (outside the Repertoire directory), you must configure the path in etc/application.conf.

7. Once all steps are configured, click Run Steps to execute the batch job. The generated reports will appear in the output folder defined in step 6 above.

Batch Report ETL Chainset.zip (4.7 KB)