MicroStrategy ONE

OLAP Functions

OLAP functions are also known as relative functions. They take multiple elements from a list and return a new list of elements. The following applies to all OLAP functions:

  • SortBy is applied before the engine performs the calculation of an OLAP function.
  • In OLAP functions, the SortBy parameter can be either a metric or an attribute.

Many OLAP functions calculate measures useful for analyzing a set of values such as the sum, average, and standard deviation. These functions fall into three groups depending on how they select the window of values on which they base their calculations. These groups are as follows:

  • Running: Functions with a running window include the current value and all preceding values. For example, given the list (1, 2, 3, 4), RunningSum returns the sums 1, 3, 6, and 10. This example is illustrated in the table below. These functions include the word Running in their name, such as RunningAvg and RunningStDev.
  • Moving: Functions with a moving window include the current value and a fixed number of preceding values. For example, given the list (1,2,3,4) and a window size of 2, MovingSum returns 1, 3, 5, and 7. This example is illustrated in the table below. These functions include the word Moving in their name, such as MovingAvg and MovingStDev.
  • OLAP: Functions with flexible windows allow you to set where windows begin and end in relation to the current value. This feature allows you to include both preceding and succeeding values in your calculations. For example, you can use OLAPSum to include one value above and below the current row. This example is illustrated in the table below. These functions include the word OLAP, such as OLAPSum and OLAPAvg.

The following table lists a comparison of the example scenarios described above.

Values RunningSum MovingSum OLAPSum

1

1 (1)

1 (1)

3 (1+2)

2

3 (1+2)

3 (1+2)

6 (1+2+3)

3

6 (1+2+3)

5 (2+3)

9 (2+3+4)

4

10 (1+2+3+4)

7 (3+4)

7 (3+4)