Data analysis is used across the whole Pricefx application. Its results are used either using charts, or also inside of logics, when calculating prices.
This article discusses the way to read/query data from Analytics tables like Datamart, Data Source or RollUp and also from Optimizer’s Model table.
Use Case
Let’s mention some particular usage of those data coming from Price Setting:
-
Dashboard - Display Sales Data in form of charts or matrices.
-
Quote - Review Historical Customer activity or average Product price.
-
Pricelist - See previous year Quantity Sold.
-
Promotions - Review last year’s Prices.
Concept
Analytics data tables are stored in PostgreSQL database, which is suitable for large datasets queries. Generally we’re issuing fewer queries than to MariaDB but on bigger datasets.
What Is PA Query
PA Query is a set of functions from Pricefx Groovy API, which is used to query tables like Data Source and Datamart.
Principles
In principle, when you want to use a query, you need to:
-
Build the query.
-
Execute the query.
-
Read the results.
Similarly as when reading master data, also here you can:
-
Fetch the resultset into memory (with capped limit of rows read).
-
Fetch the resultset row by row, using a streamed query.
Data Model
Before we start with queries, it’s good to remind some facts about the tables you will query.
In Analytics, mostly you will query two types of tables:
-
Data Source - A simple table with data and indexes.
-
Datamart - A table, whose data are (usually) coming from a primary DataSource table, and also JOINs some secondary Data Sources.
Data Source
-
A simple table with data and indexes.
-
Each Data Source has its own table in PostgreSQL database.
-
This table does not have any automatic conversions, you would need to do all on your own.
Datamart
-
Usually a composite of several Data Source tables.
-
Primary Data Source table - This drives the number of rows, which the Datamart will have.
-
Datamart does not need to have a primary Data Source. In such a case you will have to add data into it for example by a logic.
-
-
Secondary Data Source tables - Additional tables from which we pull certain columns to the Datamart.
-
-
Each Datamart has its own table in PostgreSQL database. In certain Datamart configuration though, the Datamart can be only a "view" to Data Sources JOINed together.
-
Data Sources used automatically in a Datamart:
-
"cal" - To assign Date field into specific year, quarter, month, and week. This can be any business calendar.
-
"ccy" - Contains exchange rates for conversion of Money field to different currencies.
-
"uom" - To provide conversion between standard Units Of Measures (e.g., between kilograms and pounds, kilometers and miles, etc). This is not for product-specific UoM conversions.
-
-
When you create the query, the usage of JOINed fields is transparent for you - you do not have to specify those columns in any special way.
-
Money fields are recalculated to Total values.
Because of this behavior, the numbers you’re seeing in the same Money column in an underlying Data Source and in Datamart, could be different. Bear it mind when using aggregation functions on such fields.
Implementation
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 |
SELECT ProductGroup, SUM(InvoicePrice) AS TotalInvoicePrice |
query.select("ProductGroup")
.select("SUM(InvoicePrice)",
"TotalInvoicePrice")
|
Which table to query |
FROM Transaction |
def table = dmCtx.getDatamart("Transaction")
|
Filter the rows |
WHERE InvoiceDateYear = "2020" |
query.where(Filter.equal("InvoiceDateYear", "2020"))
|
Groupping/Aggregation by certain columns |
GROUP BY dimensionalColumn |
def performGroupBy = true
def query = ctx.newQuery(table, performGroupBy)
.select("dimensionalColumn")
|
Order the results by certain column |
ORDER BY ProductGroup |
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.
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:
result?.getData()?.toResultMatrix() //❶
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.
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.
query.setOptions( [ currency: "USD" ] ) //❶
❶ All Money results of the Query will be provided in USD instead of the "default" currency.
Considerations
Possible important considerations which must be borne in mind.
Performance
As you’re querying big datasets, always be reasonable.
Ensure, that:
-
The tables you’re using are well designed. Can the Datamart be too large for your purposes? Could a pre-aggregated dataset with less columns be more suitable?
-
You’re processing only the necessary dataset - use Filters as much as possible to narrow down the rows you really need.
-
You’re using only the columns you really need - do not return more columns than you need for processing on Groovy code side. Can some sum or avg be calculated by the database?
-
You measure the performance of your queries on a dataset with similar size, as is expected by customer.
Related Features
-
Advanced SQL queries - If you need to do non-trivial JOINs with DataSource tables, review the Handbook "Advanced SQL Queries".
Summary
The Analytics query is a set of functions from Pricefx Groovy API. Similar to SQL in capabilities, enabling us to read/query data from Analytics tables like Datamart, Data Source or Rollup and also from Optimizer’s Model table.
Analytics data tables are stored in PostgreSQL database, which is suitable for large datasets queries.
Question to think about: What are the main differences when working with Analytics data (PostgreSQL) compared to Master data (MariaDB)?