Basic Analytics Query - Create, Execute, Read
The Analytics Query API is similar to SQL in the capabilities, so let’s show SQL notation and Analytics Query API side by side to explain how we build the Queries for Analytics tables.
| What we need to do | SQL (for comparison only) | Query API |
|---|
Select columns to be returned in resultset | | Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| SELECT ProductGroup, SUM(InvoicePrice) AS TotalInvoicePrice |
| | Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| query.select("ProductGroup")
.select("SUM(InvoicePrice)",
"TotalInvoicePrice") |
|
Which table to query | | Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| FROM Transaction |
| | Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| def table = dmCtx.getDatamart("Transaction") |
|
Filter the rows | | Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| WHERE InvoiceDateYear = "2020" |
| | Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| query.where(Filter.equal("InvoiceDateYear", "2020")) |
|
Groupping/Aggregation by certain columns | | Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| GROUP BY dimensionalColumn |
| | Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| def performGroupBy = true
def query = ctx.newQuery(table, performGroupBy)
.select("dimensionalColumn") |
|
Order the results by certain column | | Code Block |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| ORDER BY ProductGroup |
| | Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
| query.orderBy("ProductGroup") |
|
To write the basic Analytics Query in Studio, you can use code template "pfxquery". It will give you a basic code similar to the one below.
| Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| title | Code which builds the query, executes it and processes the data |
|---|
| linenumbers | false |
|---|
|
def ctx = api.getDatamartContext() //❶
def table = ctx.getDatamart("Transaction") //❷
def query = ctx.newQuery(table, true) //❸
.select("ProductGroup") //❹
.select("SUM(InvoicePrice)", "TotalInvoicePrice")
.where(
Filter.equal("InvoiceDateYear", "2020"),
)
.orderBy("ProductGroup")
def result = ctx.executeQuery(query) //❺
result?.getData()?.each {row -> //❻
// process the row of data
} |
❶ You need to get reference to the DatamartContext.The same context is used for querying not only Datamarts but also Data Source, Datafeed, Rollup, Model.
❷ Which table we want to use.Be cautios: There are different functions to use the other types of tables.
❸ The query will do a GroupBy (aka aggregarion, aka rollup).
❹ When doing a rollup, any dimensional field is automatically used for rollup/GroupBy.
❺ Execute the query and retrieve the resultset.Caution: All the rows of resultset are loaded into memory. There’s a cap limit and even though is quite high, you will NOT get all the results back.
❻ The data are iterable, so you can use Groovy’s "each" method to proceed through all of them.Warning: Do not use the indexed approach to the data object, unless you really have to, because it’s much slower than iteration.
Present the Results as ResultMatrix
If you’re doing a Dashboard or Quote Line logic, it might be handy to display the returned dataset via ResultMatrix.
For basic conversion there’s a function directly on the result dataset:
| Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
|
result?.getData()?.toResultMatrix() //❶ |
❶ See toResultMatrix() in Groovy API
Streaming the Results
When you need to process large resultset, which either does not fit all into memory at once, or only need to process the rows one-by-one anyway, you can stream the results, rather than fetch all into memory.
| Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
|
def resultIterator = ctx.streamQuery(query) //❶
resultIterator.each { row ->
// process the row of data
}
resultIterator.close() //❷ |
❶ Executes the query.
❷ Remember to always close the iterator, otherwise the connection will stay open.
Automatic Currency Conversion
Datamart has built-in support for automatic currency conversion. This is used by the built-in Analytics charts and you can also use it when querying Datamarts by Query API.
By default, if you do operations with Money columns, they will be performed in the "default" currency, which is a property of a Datamart. So all rows you work with will be converted to the "default" currency unless you change it in the query.
| Code Block |
|---|
| language | groovy |
|---|
| theme | Midnight |
|---|
| linenumbers | false |
|---|
|
query.setOptions( [ currency: "USD" ] ) //❶ |
❶ All Money results of the Query will be provided in USD instead of the "default" currency.
| Note |
|---|
| This works only for Datamart, and NOT for Data Source tables! If you query a Data Source, where each line has a different currency symbol, NO automatic conversion is used and if you use aggregation function, you will end up with mixed-up wrong results. |