To access the data from Analytics, you first need a deployed Datamart. You should also be familiar with the Filter API section.

Datamart Setup

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

ID	DateYear	DateMonth	CustomerID	Sku	Quantity	Turnover
ID of the transaction	Year of the transaction	Month of the transaction	ID of the customer	Stock keeping unit	Sold quantity	Total turnover

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.

def dmCtx = api.getDatamartContext()
def salesDM = dmCtx.getDatamart("Sales_DM")
def datamartQuery = dmCtx.newQuery(salesDM, false) // do not do rollup
datamartQuery.select("Sku")
datamartQuery.select("Turnover")
datamartQuery.select("PricingDate") 
datamartQuery.where(
    Filter.equal("Sku", api.getElement("sku")),
    Filter.lessOrEqual("PricingDate", api.targetDate().format("yyyy-MM-dd")),
)
def result = dmCtx.executeQuery(datamartQuery)

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.

def data = result?.getData()
if (data) { 
    for (row = 0; row < data?.getRowCount(); ++row) {
        def sku = data.getValue(row, 0)
        def turnover = data.getValue(row, 1)
        def pricingDate = data.getValue(row, 2)
        // do something with the sku, turnover and pricingDate
    }
}

Another, a more convenient way of processing the Datamart query result is:

result?.getData()?.forEach { row ->
  def sku = row.Sku
  def turnover = row.Turnover
  def pricingDate = row.PricingDate
  // do something with the sku, turnover and pricingDate
}

Or just convert the result to a list of hashmaps:

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.

Let's see the following code where we get the total turnover per product up to the target date ordered from the most sold product to the least sold product.

def dmCtx = api.getDatamartContext()
def salesDM = dmCtx.getDatamart("Sales_DM")
def datamartQuery = dmCtx.newQuery(salesDM, true)
datamartQuery.select("Sku")
datamartQuery.select("SUM(Turnover)", "TT")
datamartQuery.where(
Filter.lessOrEqual("PricingDate", api.targetDate().format("yyyy-MM-dd")),
)
datamartQuery.orderBy("TT DESC", "Sku")
def result = dmCtx.executeQuery(datamartQuery)

Notes:

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.

Here is an example of saving the query result into the memory:

def dmCtx = api.getDatamartContext()
def salesDM = dmCtx.getDatamart("TX_data")
def dmQueryThis = dmCtx.newQuery(salesDM, true)
dmQueryThis.select("sku", "sku")
dmQueryThis.select("sum(Gesamtumsatz)", "Turnover")
dmQueryThis.select("sum(Menge_Umsatz_Gesamt)", "SoldQuantity")
dmQueryThis.select("sum(AE_Menge_Gesamt)", "OrderedQuantity")
dmQueryThis.select("sum(Gross_Margin_Gesamt)", "GrossMargin")
dmQueryThis.where(
	Filter.greaterOrEqual("Datum", date.format("yyyy-MM-dd")),
	Filter.in("CustomerId", assignedCustomersIds)
	)

def result = dmCtx.executeQuery(dmQueryThis)

if (result) {
	def ctx = api.getTableContext()
	ctx.createTable("Sales_This", result)	//save the result into memory
}

Here is an example of working with the saved result:

if (api.isInputGenerationExecution()) {	//Necessary for now, Saving/compilation of the logic will complain about unknown table name "Sales_This" without this. isInputGenerationExecution is supported from version 10.0, in older versions use isSyntaxCheck.
  return
}
 
def sku = api.getElement("MaterialNumber")
def tableCtx = api.getTableContext()
def q = tableCtx.newQuery("Sales_This")

q.select("sku")
q.select("Turnover")
q.select("SoldQuantity")
q.select("OrderedQuantity")
q.select("GrossMargin")
q.where(
	"sku='$sku'"
	)

def data = tableCtx.executeQuery(q)
if (data?.getRowCount() > 0) {
  api.global.salesData = data
}

When querying the "saved/cached query", you are querying a different database (particularly the "H2" engine, which is used to store in-memory data), so you must use different "select projection expressions". For details see http://www.h2database.com/html/grammar.html#select and http://www.h2database.com/html/grammar.html#select_expression.

Specifically, for example, if you need to do COUNT DISTINCT, then you need to write COUNT(DISTINCT column_name), but when querying Datamart, you use Datamart Expressions, and write COUNTDISTINCT(col_name).

The Query API for querying the Table Context is at http://metamodel.apache.org/apidocs/4.5.2/org/apache/metamodel/query/Query.html.

Configuration Engineer Knowledge Base

Query a Datamart