Page Comparison

Analysis Data analysis is used on across the whole Pricefx application. Their 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, DataSource 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 Display Sales Data in form of charts or matrices.
Quote - review Review Historical Customer activity , or average Product price.
Pricelist - see See previous year Quantity Sold.
Promotions - review Review last year year’s Prices.

Concept

Analytics data tables are stored in PostgreSQL database, which is suitable for large datasets queries. Generally we’re issuing less fewer queries than to MariaDB but on bigger datasets.

What

is

Is PA Query

PA Query is a set of functions from Pricefx Groovy API, which is used to query tables like DataSource Data Source and Datamart.

Principles

In principle, when you want to use a query, you need to:

build Build the query.
execute Execute the query.
read Read the results.

Similarly as when reading master data, also here you can:

fetch Fetch the resultset into memory (with capped limit of rows read).
fetch 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 PriceAnalyzerAnalytics, mostly you will query 2 two types of tables:

DataSource Data Source - a A simple table with data and indexes.
Datamart - a A table, whose data are (usually) coming from a primary DataSource table, and also JOINs some secondary DataSources

DataSource

a

Data Sources.

Data Source

A simple table with data and indexes.
each DataSource Each Data Source has its own table in PostgreSQL database.
this This table does not have any automatic conversions, you would need to do all on your own.

DataMart

Datamart

usually Usually a composite of several DataSource Data Source tables.
- primary DataSource Primary Data Source table - this This drives the number of rows, which the Datamart will have.
  - Datamart does not need to have a primary DataSourceData Source. In such a case you will have to add data into it for example by a logic.
- secondary DataSource Secondary Data Source tables - additional Additional tables , from which we pull certain columns to the Datamart.
each Each Datamart has its own table in PostgreSQL database. In certain Datamart configuration though, the Datamart can be only a "view" to DataSources Data Sources JOINed together.
DataSources Data Sources used automatically in a Datamart:
- "cal" - to To assign Date field into specific year, quarter, month, and week. This can be any business calendar.
- "ccy" - contains Contains exchange rates for conversion of Money field to different currencies.
- "uom" - to 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 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.

Warning
Because of this behavior, the numbers you’re seeing in the same Money column in an underlying DataSource Data Source and in Datamart, could be different. Bear it mind when using aggregation functions on such fields.

Implementation

Basic

PA

Analytics Query -

create

Create,

execute

Execute,

read

Read

The PA Analytics Query API is similar to SQL in the capabilities, so let’s show SQL notation and PA 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 PA 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 You need to get reference to the DatamartContext.The same context is used for querying not only Datamarts , but also DataSourceData Source, Datafeed, Rollup, Model.
❷ which Which table we want to use.Be cautios: There are different functions to use the other types of tables.
❸ the The query will do a GroupBy (aka aggregarion, aka rollup).
❹ when When doing a rollup, any dimensional field is automatically used for rollup/GroupBy.
❺ execute Execute the query and retrieve the resultset.Caution: all 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 The data are iterable, so you can use Groovy’s "each" method to proceed through all of them.Warning: do 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

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 See toResultMatrix() in Groovy API

Streaming the

results

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 Executes the query.
❷ remember Remember to always close the iterator, otherwise the connection will stay open.

Automatic

currency conversion

Currency Conversion

Datamart has built-in support for automatic currency conversion. This is used by the built-in PA 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 All Money results of the Query will be provided in USD instead of the "default" currency.

Note
this This works only for Datamart, and NOT for a DataSource Data Source tables! If you query a DataSourceData 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.

Considerations

possible Possible important considerations which must be bear borne in mind.

Performance

As you’re querying big datasets, always be reasonable.

Ensure, that:

the The tables you’re using are well designed. Isn’t Can the Datamart be too large for your purposes? Wouldn’t the Could a pre-aggregated dataset with less columns be more suitable?
you’re You’re processing only the necessary dataset - use Filters as much as possible to narrow down the rows you really need.
you’re 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 You measure the performance of your queries on a dataset with similar size, as is expected by customer.

Features

Advanced SQL queries - if If you need to do non-trivial JOINs with DataSource tables, review the Handbook "Advanced SQL Queries".

Summary

The PA Analytics query is a set of functions from Pricefx Groovy API similar . Similar to SQL in cappabilietscapabilities, enabling us to read/query data from Analytics tables like Datamart, DataSource Data Source or RollUp 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 Analytics data (PostgreSQL) compared to Master data (MariaDB)?

...

Versions Compared

Old Version 105

New Version 106

Key

Use Case

Concept

What

Is PA Query

Principles

Data Model

DataSource

Data Source

Datamart

Implementation

Basic

Analytics Query -

Create,

Execute,

Read

Present the

Results as ResultMatrix

Streaming the

Results

Automatic

Currency Conversion

Considerations

Performance

Related

Features

Summary