The following api. functions are available:
Once you obtain the DatamartContext, you can call its functions - see DatamartContext.
Groovy SQL Query API
If the standard query API lacks particular functions, expressions, and/or join modes, you can configure, in the Groovy logic, an SQL SELECT statement to be run against Analytics data. This way, the full power of SQL can be leveraged.
def ctx = api.getDatamartContext()
def dm = ctx.getDatamart("TransactionsDM")
def ds = ctx.getDataSource("ForecastDS")
def t1 = ctx.newQuery(dm)
t1.select("ProductID", "product")
t1.select("SUM(InvoicePrice)", "revenue")
t1.select("SUM(Quantity)", "volume")
def t2 = ctx.newQuery(ds, false)
t1.select("ProductID", "product")
t2.select("Revenue, "revenue")
t2.select("Volume", "volume")
def sql = """
SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue
T1.volume AS ActualVolume, T2.volume AS ForecastVolume
FROM T1 LEFT OUTER JOIN T2 USING (product)
"""
return ctx.executeSqlQuery(sql, t1, t2)?.toResultMatrix() // first source gets default alias T1, 2nd gets T2 etc.
For the executeSqlQuery function, the supported SQL syntax depends on the database server of Analytics where typically PostgreSQL 10+ or Greenplum 5.12+ are used.