Granular Library (PQL)

  • Family: PQL

    • Function Categories

    Date-Time

    These functions are used to apply date-time operations on the row or grain of the data

    Identity

    These functions are used to perform derive details of the logged in user's identity and rights

    Logical

    These functions are used to use logical operations on the row or grain of the data

    Math

    These functions are used to apply mathematical operations on the row or grain of the data

    String

    These functions are used to apply text string operations on the row or grain of the data

    Overview

    Granular functions are a set of methods that are designed to operate on the GRAIN of the data feeding a data model. This means that:

    • They are generally materialized as raw SQL functions that are queried directly on the database
    • They can be used to manipulate the raw data structures in the query itself, without ETL operations.
    • They can be used to resolve mathematical calculations that need to be done at the ROW level (or 'grain'), rather than at the aggregate (or 'semantic') level. This is pivotal in solving many analytical problems that MUST be solved at the grain.

    Because granular functions can adjust the 'raw' data flowing into a model, they can also be used to manipulate the aggregations of metrics used in the model itself.

    Syntax

    Click here to see more details on the syntax needed to build PQL statements .

    Adding Granular Calculations

    There are 3 techniques for adding granular calculations in Pyramid:

    1. Via the Data Model app, via 'Virtual Column Calculations'
    2. Via the Formulate Custom Columns app. The calculation can be added using the graphical formula builder, or directly via the script editor tool.
    3. Via the Discover app, custom calculations can be built using the short-cut menus as follows: