Enriching file fields by adding column calculators

This page provides a description of how to add a new column calculator. It features an example in which a new field is populated to a new store. To see how this store was populated, see Adding and Populating a New Store. The techniques employed are generic examples that can be extended, adapted and repeated for any use case that is needed. In all cases, minimal changes are made to the Reference Implementation. The examples outlined in this page will build on the example in Adding and Populating a New Store.

Step 1 - Datastore Modifications

Before this new column can be loaded into the cube, the datastore must have a field that can accept this column. The Adding and Populating a New Store example can be followed to create a new store or modify an existing store.

Step 2 - Define column calculator

This step defines the logic of the column calculator. The example below is a simple column calculator. It returns a value of Negative when the Custom Projected value is less than 0, and Non-Negative otherwise. The custom column calculator is defined inside: /frtb-application/src/main/java/com/activeviam/frtb/application/config/

public class CustomColumnCalculator implements IColumnCalculator<ILineReader> {

	private final String columnName;

	public CustomColumnCalculator(String columnName) {
		this.columnName = columnName;
	}

	@Override
	public String getColumnName() {
		return this.columnName;
	}

	@Override
	public Object compute(IColumnCalculationContext<ILineReader> context) {
		if ((Double) context.getValue("CustomProjected") < 0) {
			return "Negative";
		}
		return "Non-Negative";
	}
}

Column Calculators can be used to add columns from simple operations across any number of columns that are present in the dataset. Below is a common use case; parsing data out of the file name. More metadata is available through the context.

 final String filename=context.getContext().getCurrentFile().getName();

Step 3 - DLC Config

For this step, the Data Load Controller is used. In the example below, the topic description configured here is overridden. The existing topic description is wired in and a column calculator is added into the channel. Since the topic description is given a lower order, it will fully replace any existing topic description with the same unique identifier (name and topic type). Alternatively, a named Custom Field Description can be created and added to the channel via YAML.

@Configuration
public class TopicDescriptionCustomisations {

	public static final String CALCULATED_COLUMN_NAME = "calculatedColumn";

	@Bean
	@Order(1)
	public CsvTopicDescription customTopicDescription(
			@Qualifier("customProjectionTopicDescription") CsvTopicDescription customProjectionTopicDescription,
			NamedEntityResolverService namedEntityResolverService
	) {
		return customProjectionTopicDescription.toBuilder()
				.channel(ChannelDescription.builder(namedEntityResolverService.getTarget("CustomProjectionStore"))
						.customField(AnonymousCustomFieldDescription.of(s -> new CustomColumnCalculator(CALCULATED_COLUMN_NAME)))
						.build()
				)
				.build();
	}
}

Step 4 - Ensure FrtbApplicationConfig picks up the modifications

For the customizations to be picked up, all added Spring @Configuration classes must be included in FrtbApplicationConfig located in /frtb-application/src/main/java/com/activeviam/frtb/application/config/.

When using DirectQuery

When using DirectQuery, column calculators do not apply. This is because the data in DirectQuery is loaded directly and there is no ETL (Extract, Transform, Load) layer. To enrich a table with a new computed column, either pre-compute the values and add them on the remote table, or create a view that adds the extra field. If a view is created, its name must be the same as the database table being enriched and the source table for the view should have a different name. This ensures that when the application starts, Atoti does not read from the incorrect table.

​Step 1 - Datastore Modifications

​Step 2 - Define column calculator

​Step 3 - DLC Config

​Step 4 - Ensure FrtbApplicationConfig picks up the modifications

​When using DirectQuery

​Suggested further reading

Step 1 - Datastore Modifications

Step 2 - Define column calculator

Step 3 - DLC Config

Step 4 - Ensure FrtbApplicationConfig picks up the modifications

When using DirectQuery

Suggested further reading