Custom Columns

Custom Columns allows users to create and share new Hierarchies and Measures against Pyramid Models at run time. This permits the dynamic extension of the Model by users to cater for unforeseen Modeling requirements in terms of model structure, measures and row level calculations.

Custom Columns, as the name implies, create new virtual columns in the underlying Model table definitions which can be used to define new Hierarchies and Measures. The values within the virtual column are calculated at the row level for each row, the calculations defined passed to the underlying database for execution as part of the query by the Pyramid Pyrana Query Engine at run time.

Why Custom Columns are Needed

Pyramid Models define how numerical values are aggregated and viewed by business dimensions. Taking the sales of a commercial company, each transaction will generate a sales value. that sales value will be associated with what product was sold, the date it was sold, to which customer, which salesperson sold it and perhaps many ot business attributes.

Typically when understanding the company performance, the individual sales transactions will be aggregated by these business dimensions. Pyramid Models define how these aggregations are performed to create Measures.

Thus, viewing my Sales by Month and Product, the Pyramid query will aggregate the Sales to show the total Sales for each Product in each Month. In turn, performing calculations on these Measures, the calculations operate on the aggregate values.

For example, to calculate the average sales transaction value I could define the calculation as Sales / Quantity. Viewing this by Month and Country, the calculations will aggregate the Sales and the Quantity for each product within each month, then divide one by the other. The aggregation will take place first, then the division. Because the values inserted into the calculation are defined by the Pyramid Semantic Model in terms of Hierarchies and Measures, this type of operation is known as a Semantic Calculation.

  • Click here to learn more about Semantic Models
  • Click here to learn more about Semantic Calculation formulas.

However, for certain calculations, where the order of calculation and aggregation is significant, this can lead to erroneous results.

Custom Columns vs Virtual Calculated Columns

Custom Columns and Virtual Calculated Columns are almost identical. The difference lies in where they are created and who can create them.

  • Virtual Calculated Columns are built into a semantic model, and are designed by model creators to be accessible to users who can see the model. While access to the virtual columns can be managed via model security (including member level security), the concept is centered around a 'global' calculation that users access in the model itself.
  • Custom Columns are built as custom objects and are additive to an existing semantic model. They are designed by end users (more on a case by case basis) and accessible to those users with access to the calculation logic items (and folders) only. Column access can be managed using the content manager and roles, but there is no member level security option.

There is no difference in performance, analytic or mathematical function between the 2 approaches.