Formulate

The Formulate module enables you to build custom KPIs, member lists, formulas, scripts, and more, using the Pyramid Query Language (PQL) or MDX. These can then be used in visualizations and queries to customize your content or inject formulas and values into your data - this includes Discover, Present, Publish, and Illustrate content.

Formulate presents users with standalone entry points into the tools and wizards needed to create calculations and build business logic to drive analytic projects. Usually, it's accessed by Pro users wanting to build more advanced elements, versus using the point-and-click experiences found in Discover.

Formulate vs Discover Calculations

Formulate logic can also be built directly in Discover using tools such as Quick Calcs. These calculations are shortcuts to building the matching logic in Formulate. However, the Formulate tools remain the principal venue to create and edit such content - offering rich graphical and scripting tools

  • Click here to see the Formulate Tutorials Video Library and overviews

Components in Formulate

The formulate module contains the following components:

  • Formula: Create calculated (or 'custom') members and measures; these are user-defined calculations written in either PQL or MDX, and can be used to derive values anywhere in the query.
  • List: A custom list (or set) is a user-defined group of elements, from a single attribute in a data model usually derived using formulas and logic. Lists are combined with a range of functions, such as slicing, filtering, sorting, and conditional logic. They can also be used to drive parameters (see below). Custom lists are written in either PQL or MDX.
  • KPI: Create Key Performance Indicators (KPIs) and utilize them to identify trends and measure your organizational goals. KPIs are written in either PQL or MDX.
  • Custom Columns: Create Custom Columns to dynamically extend the Semantic Model, adding custom-built, shareable attributes and 'base' measures to the model. Custom Columns are built in PQL.
  • Parameters: Parameters are used to inject dynamic values (attribute elements, measures, numerical or text constants) into the query at runtime. They can be nested in other calculation tiers as part of calculated members, measures and lists, which are in-turn used in queries. This functionality enables users to trigger advanced logic and logical flows in the query at runtime through a point-and-click experience.
  • Script: Write R and Python scripts for driving data cleansing logic or machine learning logic.
  • Custom Visual: Create your own custom visualizations and share them with other users.

Common Formula Building Interfaces

The various formulate wizards and components have a common and similar interface allowing users to build advanced logic in a familiar way.

Functions Libraries

The components feature function libraries to help users create their formulations. Formulate logic is written in either PQL or MDX, depending on your datasource: formulations built on MS OLAP, Tabular, and SAP BW data models are written in MDX; formulations built on other data models are written in PQL. See the links below for details on the available functions.

  • PQL: Review the PQL documentation to learn how to write expressions in PQL.
  • MDX: Review a summary of MDX functions available in Pyramid when working with MS OLAP, MS Tabular, and SAP BW data models.

Launching Formulate

You can open Formulate by clicking on the orange Formulate icon in the App Toolbox to the left of the home page.

You can open Formulate from within another module by clicking the arrow on the module tab and selecting New Formulation:

Next, select the Formulate component you want to access:

Opening a Data Model

For most of the Formulate options, you need to open the data model you want to use for your formulation. This is done by selecting the Model in the Content Manager:

  1. Select Explorer (blue arrow above) in the toolbar.

    2. Tip: You could, alternatively, use the Favorites or Recents options if your Model is marked as a favorite or has been used recently.

  2. In the Servers column, find and select the server:
    • Use Search (purple arrow, above) to filter servers by name.
    • Use the Type drop-down (yellow arrow) to filter the server list by type.
    • Use the Sort drop-down (green arrow) to arrange the servers by name, type, or to arrange the list by folder. The folder name is created when the data source is created.

    Note: The filter and sort selections are saved within the session, so any subsequent visual will default to your sort and filter selections.

    Tip: Hover your cursor over the server to see the server name, type, and description as a tooltip.

  3. In the Databases column, select the required database within the given server:
    • Use Search to filter databases by name.
  4. In the Models column, select the data model that you want to query:
    • Click Refresh to refresh the list of data models.
    • Use Search to filter data models by name.
    • Use the buttons to view the data models in a List view, Tile view, or Tree View.
  5. Open the data model.

Adding new Servers and Databases

Depending on your access rights and selected server type, you may be able to use Quick Model to create a new server or database on-the-fly:

  • In the Servers column, click Add New Server (orange arrow above) to create a new server.
  • In the Databases column, click Add New Database (blue arrow) to add a new database to your selected server.
  • Click here for more information about Quick Modeling