Hi Frank,

Using PQL in Formulate, you are creating what we call "Semantic" calculations. By this, we mean that the calculation context is the dimensional semantic model in Pyramid that describes the data tables under concern.

Let's take a simple example. I have a Table called Customers with a Field called Country. In Pyramid's semantic model this translates to a Dimension called Customer with an Attribute / Hierarchy called Country. If I were to put my Country attribute  on  a grid, I will see, in my example model, say 6 countries listed, i.e. all the unique values of Country contained in the Field Country in my Table Customers. If I were to add a measure, like sales, then I would see the total sales for each Country (assuming an aggregate model).

Any PQL calculation I specify involving Sales will operate against the value of sales in the context of my query, in this case the sum of Sales for each Country.

Unless I specify a Discover View where I am looking at the individual record level, say my Transaction ID for all my sales, then the PQL calculation will be taking as input the aggregate value of Sales depending on the context of the query in terms of the attributes used.

If I need calculations to be applied at the record level in the underlying data tables, then there are a two ways this can be done.

1. If the Pyramid model has been created by a Model data flow, then the calculation can be specified in the data flow and materialized as a column in the resultant database for subsequent analysis.
2. If it is a Direct Query model into the underlying tables, then rather than using the default table view, you can create a custom query that defines the calculation required in the underlying database.

What would be great in Pyramid is to be able to specify row level calculations in the underlying tables directly in Discover. We call this "Custom Columns". In fact the first phase of this is already available. If you have a model with a Date type attribute, if you Right click on the Date Attribute, you will see an option in the context menu of "Date Part" to create a new attribute based on Years, Quarters, Months etc. What this is doing is effectively creating a new, calculated column in the underlying table metadata, that is resolved at run time when referenced. You can create "Bins" or Ranges for other types of attributes, like Age or Income for example.

If the underlying model is OLAP, like Analysis Services or SAP BW, then we are using their Semantic model, not ours, so this option is not available

It is Pyramid's intention to broaden this capability to address any type of calculation that needs to be or can be performed at the record level in the underlying table. This is where your Average(col1*col1) type expression would be created. Pyramid would calculate the square of the value for each row, then calculate the average for each Country in the above example.

While this is on our roadmap, I don't yet have dates for its availability.

Hope that helps.

Ian