How to create dynamic dimensions and measures that can be exchanged

This tip & trick has been provided by Michael Daun who created this solution while working on a project

What to we want to achieve

It is often desirable to exchange dimensions or measures in a visualisation (table, chart) at runtime - i.e. during the analysis and not during creation.

For example, you could run through different measures for a given selection of dimensions ("All product categories in the last 12 months") - sales, costs and profit, for example. Or you may want to change the granularity of the time dimension for the selected period, e.g. switch from a monthly view to a weekly or daily view.

The exchange of dimensions at runtime is possible in Pyramid Analytics as standard - you can call up the "Swap" function via the context menu and, for example, exchange the time dimension "Month" for "Date". However, this procedure is often not intuitive, especially as the user needs knowledge of the data model in order to find the corresponding dimensions (unless you use the new GenBI features, which should also quickly perform the desired swap ). In the case of measures, on the other hand, it is not possible to swap at runtime using on-board tools, as there is no "swap" function or similar.

The solution presented here enables the exchange of dimensions and measures in a discovery via the usual slicer functionalities. We refer to the objects used here as "dynamic" because they are only assigned their final form by the user at runtime via the slicer. All objects required for the solution are created using simple formulas. Specifically, we use a measure created in Formulate for the dynamic measures (see #2), the dynamic dimension is Custom Column (#3). Both objects are each controlled via a slicer, which is provided via a static text parameter (#1). 

#1 Text parameter:

Create a new formulation, select the "Parameters" option and in the first of the three following steps select "Global" because the values of the text parameters do not come from the data model but are statically defined.  Then specify a discrete list of name-value pairs, consisting of the captions the user sees when using the slicer and the corresponding value (a “Text” or a “Number” depending on the List Type you have chosen) which will be used in the definition of the dynamic objects (cf. #2 and #3).

 Text parameter for the dynamic measure - (named “Parameter Measure 1”) 

Text parameter for the dynamic dimension - (named “Parameter Dimension Time”)

In the last step you specify how the text parameter should act as a slicer, i.e. which visualization, and which default value it should have. 

#2 Dynamic Measure:

Create a new Formulation, choose option “Formula”, choose the corresponding data model (here: “Sales”) and define a “case” statement based on the text parameter “Parameter Measure 1” and the appropriate measures.

 “case” statement  for the dynamic measure - (named “Measure 1”)

#3 Dynamic Dimension:

Create a new Formulation, choose option “Column”, choose the corresponding data model (here: “Sales”) and define a “case” statement based on the text parameter “Parameter Dimension Time” and the appropriate time dimension.

 “case” statement  for the dynamic dimension - (named “Dynamic Dimension Time”)

The case statement requires the same data type for the result object of each option. In the underlying model “Sales” the “Quarter” of “Date” is denoted e.g. as “2021-Q2”, i.e. the result object of this option is a string. Therefore, all result objects must be of this type which is the reason why “Year” (which is a number) has to be formatted as a string. 

#4 Create the discovery

In the three preceding chapters we have built two dynamic objects and the corresponding text parameters/slicers for their control. As we like to compare two measures in our showcase we duplicate “Measure 1” and “Parameter Measure 1” so that the resulting list of formulate objects looks like this.

When starting to create the discovery with these objects you could simply drag or double click the objects into the drop zone. As the dynamic dimension is a custom column you will find it in the corresponding “Custom” folder.

The dynamic measures are placed in the Business Logic area of the measures pane. 

A first quick draft of a discovery may look like this:

Please be aware that the slicers are placed automatically in the discovery as the underlying text parameters (#1) are part of the formulations of the dynamic objects! Therefore, the “Filters” drop zone is empty.
 

Now play around a bit with the slicers and experience the dynamic functionalities of the Discovery!

You can find a copy of this example in the How to Section of the Explore Server: Link

The two examples in the “How To” folder are a little bit more sophisticated. They show the comparison of two measures in a combo chart and demonstrate that the dynamic behavior also works for additional calculations which are based on the dynamic objects. So, for example, the difference between the two measures of course can be computed regardless of the dynamic dimensions. 

Big Kudos to Michael Daun for sharing this, if you have questions or comments. Please leave a message below this article