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:

1. Build the query.

2. Execute the query.

3. 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.

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.

...

SELECT ProductGroup, SUM(InvoicePrice) AS TotalInvoicePrice

...

query.select("ProductGroup")
     .select("SUM(InvoicePrice)",
             "TotalInvoicePrice")

...

FROM Transaction

...

def table = dmCtx.getDatamart("Transaction")

...

...

WHERE InvoiceDateYear = "2020"

...

query.where(Filter.equal("InvoiceDateYear", "2020"))

...

...

GROUP BY dimensionalColumn

...

def performGroupBy = true
def query = ctx.newQuery(table, performGroupBy)
   .select("dimensionalColumn")

...

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.

...

...

Code which builds the query, executes it and processes the data

...

...

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()    //❶

❶ 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.

...

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)?

References

Documentation

• Datamarts

• Query Expressions and Aggregation Functions

• How to access PA data

• Filter API

Groovy API

• api.getDatamartContext()

• getDatamart()

• newQuery()

• select()

• where()

• orderBy()

• executeQuery()

• getData()