...

Datamart Setup

Let's assume we have a Datamart called TX with the following fields:

...

To access the data, we will query the Datamart context.

Datamart Context

The Datamart Context provides a more flexible solution than querying a Datamart. If you are familiar with the SQL syntax, the Datamart Context API will be easy to understand.

Simple Example

Consider the following code which gets all transactions for a particular product up to the target date from the Datamart.

...

Notice the Boolean parameter passed to dmCtx.newQuery(..., false). This parameter determines whether an aggregation is being performed for numeric fields or not. Since we are not aggregating anything, we need to set it to false.

Processing Result

You can access the result returned from the method executeQuery by calling getData(). Now let's process the result from the previous example with a simple loop.

...

result?.getData()?.collect()

Aggregation and Ordering

The advantage of the Datamart Context is that the select query can contain aggregation methods or even mathematical expressions, just like normal SQL.

...

• The Boolean parameter passed to dmCtx.newQuery(..., true) is set to true this time because we are using an aggregation method in the query. 
• Notice the aggregation method (SUM) in the select query. 
• Notice the orderBy method being called before executing the query. This can contain multiple orderings. Aggregated columns must use aliases.

Performance Improvement

Datamart queries can be quite resource demanding. It can be beneficial to run the first query and save the result into the memory (for example for all products in the Price List). Later on, this result can be further filtered (for example for particular product) and queried without accessing the database.

...