Calculations in Discover

The need to add calculations and business logic is highly prevalent in most report building or analytic exercises. Often there is a need to add customized business logic to a report built with just its basic setup (data selection and visualization) or those reports with other query functions (standard or advanced analytical methods).

In Pyramid, business logic tools give the end user the freedom to formulate any type of calculation to drive the outcomes they are after.

Note: There are numerous intricacies in calculation design and operation. The explanations provided below are meant to cover this detail. For most users, this knowledge is not required to use the capabilities as the options exposed in Discover are designed to hide much of this complexity from the average data analyst.

Formulate vs Discover

The calculation capabilities described below can be accessed through two tools in the application. The Formulate tool is the principle venue for designing such elements with powerful formula editors (both graphical and scripted). However, the Formulate capabilities can be overkill for many scenarios and so many of the calculation features are exposed through 'quick' short cuts directly in Discover (albeit with predefined formulas and settings). Almost all quick calc's in Discover need to be edited in Formulate - as such the two sets of tools have a symbiotic relationship and offer users a balance between speed and intuitiveness versus specificity and granular capabilities.

Note: Some calculations are only available from Discover while other are only available when the data models itself it built and designed in Model.

Calculation Types

Business Logic capabilities is exposed through three main calculation capabilities and engines:

  • Semantic or "Model" Functions - these are the most commonly used calculations in analytics. Semantic calculations are usually designed around the details of the data model itself - and are usually designed agnostically of the target report. It is not unusual to design semantic functions in both Discover and Formulate. They come in two types:
    • Custom Members or Elements: This capability allows the user to formulate mathematical operations on data within a query. For example adding France and Germany metrics into a single item called "EU". Or calculating profit by deducting Expenses from Sales.
    • Custom Lists: This capability allows the user to formulate logical ways of selecting elements for analysis in a query. For example listing all US states with sales over $100m. Or finding the top 20% of all customers who bought Bikes in 2019. Or any product starting with the letter "A".
  • Context or "Report" Functions - these functions allow users to build calculations that can only be resolved efficiently in the context of a visualization or report. For example, cumulative sales for the product categories in a chart or Pareto analysis. While its possible to build semantic logic to match most contextual logic - it's generally inefficient. Unlike semantic functions, context functions can only be designed in Discover (because they need the relevant visualization context).
  • Granular or "Line-detail level" Functions - these are functions that allow users to build calculations that must be resolved at the granular or transactional detail level of the underlying data. For example binning each transaction into a range grouping based on its sales value. These are also referred to as 'custom column' calculations - since we are virtually generating a new column in the underlying database and data model.
  • A more thorough overview of the different calculation engines is available here.

Calculation Tools in Discover

Matching the above calculation types, the Discover calculation tools are delivered primarily through Quick Calc's , Context Calc's. and Custom Column Calc's. Beyond these main entry points, there are several other Discover wizards that extend these capabilities further described separately below.

Quick Calc's

Quick Calc's are calculations driven heuristically from the user's selections and the underlying hierarchies selected in an analysis. They are exposed either from visualizations, element trees and in some cases from chips in the drop zones. They include

  • Formulas: these are a mix of mathematical operations as well as standard statistical aggregations using the selected items. The choices presented depend on the type and number of elements chosen contextually: member vs measure, date members, number of items etc.
  • Lists: there are several techniques for building logical element lists using the currently selected items. The choices presented depend on the type and number of elements chosen.
  • Grouping: these are quick macro functions that can group and aggregate the selected items and list the unselected ones (or vice versa). Grouping is a combination of formulas and lists.

Separate from the above three main quick calc 'flavors', there are also options to create quick parameters. 'Parameters' are a special functionality to convert element selections into a parameter listing, that can then be subsequently used to drive other logic and calculations.

Context Calc's

Context Calc's, like quick calc's, are context menu driven calculations and formulations driven from specific metrics (orange value chips) and hierarchies (blue chips). They are also exposed either from visualizations or from the hierarchy trees, measure trees or drop zones. Context Calc's are offered on metric (value) items only. However, their operation is affected by the selections used in the drop zones and the query - hence the 'context'.

Context calculations include:

  • Difference Logic: these are functions that rely on measuring the difference between items on a given axis for the metric. This covers logic like differential movements and percentage change from previous item.
  • Cumulative Logic: these are functions that accumulate the metric across the other 'axes' or selections in the query. This covers logic like Pareto, Percent of Total, as well as rolling averages.
  • Statistical Logic: these are functions that use basic statistical functions a given axis for the metric. This covers logic like average, median and standard deviation.
  • Advanced Analytics Logic: these are functions that drive advanced analytical tools like forecasting and regression.

A special type of context calculation group, known as "categorization", is driven based on the context of the existing query and its selections. However, it generates a custom column in the background. See below for more detail.

Custom Column Calcs

Custom Columns are calculations that are designed to virtually append granular details to an existing data model. They apply to both value metrics (orange) and hierarchies (blue) and are exposed, like the other calculation options in Discover, from the hierarchy trees, element trees, visualizations and drop zones. There are a few custom column calc's that are determined by their contextual usage - however once defined, they do not change based on the report or query structure.

Note: Custom Columns allow the user to add granular logic to an existing model. This functionality is not available on MS OLAP, Tabular and SAP BW cubes and models since this is not possible on a predefined model framework,

Custom Column calculations include:

  • Aggregations: these functions are used to either:
    • Add or change the summation logic used on an existing metric (for example from "sum" "to average") - typically on a numeric data column
    • Add summation logic on an existing attribute (for example "count" or "distinct count") - typically on a non-numeric column
  • Categorization: these functions are used to generate and add new attribute classification columns to the data model using existing data values. This includes classifications like static and dynamic binning as well as outlier flags. Such categorizations usually take the existing query and its selections into account, so they are very much "contextually driven".
  • Date Parts: these functions extract date-time groupings from raw date-time columns in a SQL database. This includes things like years, months, weeks and quarter.

Other Calculation Tools and Wizards

There other various tools and wizards that explicitly build new calculations for analysis or can optionally create calculated artifacts.

  • List Builder - this tool allows users to upload a (large) list of elements to select - instead of manually clicking on each item. Once selected in the elements tree, the user can use those selections to define and build custom elements and lists.
  • Member Search - this tool allows the user to search the data model for members matching search criteria. Once the (composite) list of elements is selected, the user can optionally choose to convert it to a more permanent custom list definition.
  • Variable Lists - this is a wizard to build switchable choices for picking different lists and selections in reports.
  • Time Calculations - this is a wizard to add specialized time based calculations to date-time hierarchies for time-based analysis.