Analysis is used on across the whole Pricefx application. Their 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, DataSource 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 Prices
Concept
Analytics data tables are stored in PostgreSQL database, which is suitable for large datasets queries. Generally we’re issuing less 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 DataSource 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 PriceAnalyzer, mostly you will query 2 types of tables:
-
DataSource - 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 DataSources
DataSource
-
a simple table with data and indexes
-
each DataSource 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 DataSource tables
-
primary DataSource table - this drives the number of rows, which the Datamart will have.
-
Datamart does not need to have a primary DataSource. In such case you will have to add data into it for example by a logic.
-
-
secondary DataSource 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 DataSources JOINed together.
-
DataSources 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 DataSource and in Datamart, could be different. Bear it mind when using aggregation functions on such fields.
Implementation
Basic PA Query - create, execute, read
The PA Query API is similar to SQL in the capabilities, so let’s show SQL notation and PA 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 PA 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 DataSource, 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 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 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 PA 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 bear in mind
Performance
As you’re querying big datasets, always be reasonable.
Ensure, that:
-
the tables you’re using are well designed. Isn’t the Datamart too large for your purposes? Wouldn’t the 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 PA query is set of functions from Pricefx Groovy API similar to SQL in cappabiliets, enabling us to read/query data from Analytics tables like Datamart, DataSource 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 PA data (PostgreSQL) compared to Master data (MariaDB)?