Data Sources (Quick Reference)

Analytics data should be stored in a different relational database than all other types of data. Queries to this database are performed with the DatamartContext API.

Despite of its name, DatamartContext can be used to query other types of tables than just Datamarts.

Types of tables that can be queried with the DatamartContext API:

Datamarts
Data Sources
Data Feeds

Collection of Rows

The simplest form of a query is a non-aggregating query. Be careful about the number of rows your query returns – it might be very large.

The returned value from streamQuery() is not Iterable, and therefore you cannot iterate the collection with Groovy collection methods.

You should close the stream as soon as possible.

Transactions.groovy

void forEachTransaction(Closure callback){
    def dataMartAPI = api.getDatamartContext()
    def table = dataMartAPI.getDatamart('Transaction')
    def query = dataMartAPI.newQuery(table, false)
            .selectAll()
    def results = dataMartAPI.streamQuery(query)

    // StreamResults is not iterable. Iterate in this non-standard way.
    while(results.next()){      // ❶
        def row = results.get()
        callback.call(row)      // ❷
    }

    results.close()             // ❸
}

❶ Iterate the table rows. Since the result is neither a collection nor an iterator, use for/while loop.
❷ Do something for each row.
❸ Close the stream after usage.

Where Filters

Limit the rows that are included in your results by applying an SQL WHERE clause. The rows that are included in the query must satisfy the filter that is provided to the where() method.

ExchangeRates.groovy

BigDecimal getExchangeRate(Date validFrom, String currencyFrom, String currencyTo){
    def dataMartAPI = api.getDatamartContext()

    def table = dataMartAPI.getDataSource('ccy')

    def filters = [
            Filter.lessOrEqual('CcyValidFrom', validFrom),
            Filter.greaterThan('CcyValidTo', validFrom),
            Filter.equal('CcyFrom', currencyFrom),
            Filter.equal('CcyTo', currencyTo),
    ]

    def query = dataMartAPI.newQuery(table, true)
            .select('CcyExchangeRate', 'ExchangeRate')
            .where(*filters)    // ❶

    def result = dataMartAPI.executeQuery(query)
    return result.getData().getAt(0)?.ExchangeRate
}

❶ Filter the rows with a WHERE filter.

Aggregation

More often than not, queries to Datamarts perform some sort of aggregation. Turn the query into an aggregation query by setting the second argument to newQuery() to true. Now you can wrap the fields that you want with an SQL aggregate function.

You can declare aliases which are especially useful when you use aggregate functions.

Transactions.groovy

BigDecimal getTotalSales(Date dateFrom, Date dateTo){
    def dataMartAPI = api.getDatamartContext()

    def table = dataMartAPI.getDatamart('Transaction')

    def dateFilters = [
            Filter.greaterOrEqual('InvoiceDate', dateFrom),
            Filter.lessThan('InvoiceDate', dateTo),
    ]

    def query = dataMartAPI.newQuery(table, true)
            .select('SUM(InvoicePrice)', 'TotalSales') // ❶
            .where(*dateFilters)

    def result = dataMartAPI.executeQuery(query)
    return result.getData().getAt(0).TotalSales
}

❶ Aggregate the InvoicePrice field values. The use of an alias means that the result Map will have the key 'TotalSales', instead of InvoicePrice.

Grouping

Rows can can be grouped together according to the value of some field(s). This will automatically happen when the column in the select statement is not wrapped within an aggregate function.

Transactions.groovy

List<Map<String, Object>> getTotalSalesByYear(){
    def dataMartAPI = api.getDatamartContext()

    def table = dataMartAPI.getDatamart("Transaction")

    def query = dataMartAPI.newQuery(table, true)
            .select('InvoiceDateYear')                  // ❶
            .select('SUM(InvoicePrice)', 'TotalSales')  // ❷
            .orderBy('InvoiceDateYear')

    def result = dataMartAPI.executeQuery(query)
    return result.getData().toList()
}

❶ Group by the InvoiceDateYear field values. This field is of the type dimension.
❷ Aggregate the InvoiceDateYear field values. This field is not of the type dimension.

Filtering

After performing an aggregation, you can filter the query results with a SQL HAVING clause. Apply it with the having() method on the query builder.

Transactions.groovy

List<Map<String, Object>> getTotalSalesByYear(BigDecimal totalSalesThreshold){
    def dataMartAPI = api.getDatamartContext()

    def table = dataMartAPI.getDatamart("Transaction")

    def resultFilters = [Filter.greaterOrEqual('TotalSales', totalSalesThreshold)]
    def query = dataMartAPI.newQuery(table, true)
            .select('InvoiceDateYear')
            .select('SUM(InvoicePrice)', 'TotalSales')
            .orderBy('InvoiceDateYear')
            .having(*resultFilters) // ❶

    def result = dataMartAPI.executeQuery(query)
    return result.getData().toList()
}

❶ Apply a filter to the query results.

Distinct Values

To retrieve only unique values from a Data Source, use the selectDistinct() method.

DataMart.groovy

List getUniqueValues(
        String dataMartName,
        String dimensionColumnName
) {
        def dataSourceAPI = api.getDatamartContext()
        def table = dataSourceAPI.getDatamart(dataMartName)
        def query = dataSourceAPI.newQuery(table, true)
                .select(dimensionColumnName)
                .selectDistinct() // ❶

        def results = dataSourceAPI.executeQuery(query)
        return results.getData().toList()[dimensionColumnName]
}

❶ selectDistinct() ensures that all retrieved values are unique.