Version 2021
OLAP Function
OLAP functions are a powerful family of functions for business intelligence and data warehousing applications. These functions can provide significant analytical capabilities for many business analysis queries.
MicroStrategy's OLAP functions are calculated via SQL in PostgreSQL rather than in the MicroStrategy analytical engine. Below is a list of MicroStrategy supported OLAP functions that are pushed-down to the database server using the associated SQL patterns. There is no VLDB setting associated with this option.
|
Function |
SQL Pattern |
|---|---|
|
RunningStdevPFunction |
stddev_pop(#0) over(#1) |
|
MovingStdevPFunction |
stddev_pop(#0) over(#1) |
|
RunningSumFunction |
sum(#0) over(#1) |
|
RunningAvgFunction |
avg(#0) over(#1) |
|
MovingAvgFunction |
avg(#0) over(#1) |
|
MovingSumFunction |
sum(#0) over(#1) |
|
MovingMaxFunction |
max(#0) over(#1) |
|
MovingMinFunction |
min(#0) over(#1) |
|
MovingStdevFunction |
stddev_samp(#0) over(#1) |
|
MovingCountFunction |
count(#0) over(#1) |
|
RunningMaxFunction |
max(#0) over(#1) |
|
RunningMinFunction |
min(#0) over(#1) |
|
RunningStdevFunction |
stddev_samp(#0) over(#1) |
|
RunningCountFunction |
count(#0) over(#1) |
|
FirstInRangeFunction |
first_value(#0) over(#1) |
|
LastInRangeFunction |
last_value(#0) over(#1) |
|
OLAPSumFunction |
sum(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|R:y |
|
OLAPAvgFunction |
avg(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|R:y |
|
OLAPCountFunction |
count(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|N:y|R:y |
|
OLAPMaxFunction |
max(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|R:y |
|
OLAPMinFunction |
min(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|R:y |
|
LagFunction |
CASE WHEN count(*) OVER ([#P] [#O] rows between unbounded preceding and current row) <= #1 THEN #2 ELSE max(#0#< #*#>) OVER ([#P] [#O] rows between #1 preceding and #1 preceding) END" OLAPDBPATTERN="P:o|O:r |
|
LeadFunction |
CASE WHEN count(*) OVER ([#P] [#O] rows between current row and unbounded following) <= #1 THEN #2 ELSE max(#0#< #*#>) OVER ([#P] [#O] rows between #1 following and #1 following) END" OLAPDBPATTERN="P:o|O:r |
|
OLAPRankFunction |
rank() over ([#P] [#O])" OLAPDBPATTERN="P:o|O:r |
MicroStrategy uses the analytical engine to implement the OLAP functions shown below.
| Function |
Example of Analytical SQL Calculated by the Analytical Engine |
|---|---|
|
ExpWghMovingAvg |
ExpWghMovingAvg<BreakBy={[O_ORDERSTATUS]}>(WJXBFS1, 4.0, 1.0) |
|
ExpWghRunningAvg |
ExpWghRunningAvg<BreakBy={[O_ORDERSTATUS]}>(WJXBFS1, 1.0) |
|
MovingDifference |
MovingDifference<BreakBy={[O_ORDERSTATUS]}>(WJXBFS1, 2.0) |
|
WeightedCorr |
WeightedCorr(WJXBFS1, WJXBFS2, 1.0) |
|
WeightedCov |
WeightedCov(WJXBFS1, WJXBFS2, 1.0) |
|
WeightedMean |
WeightedMean(WJXBFS1, 1.0) |
|
WeightedStDev |
WeightedStDev(WJXBFS1, 1.0) |
<analytic function> ::=
<function_name> OVER ([<partition clause>] [<order by clause> [<windowing clause>]])
OLAPRank
Select a11.O_ORDERSTATUS O_ORDERSTATUS,
a11.O_ORDERKEY O_ORDERKEY,
rank() over ( partition by a11.O_ORDERSTATUS order by a11.O_TOTALPRICE asc ) WJXBFS1
From ORDERS a11
Where a11.O_ORDERKEY <= 20
MovingAvg
Select a11.O_ORDERSTATUS O_ORDERSTATUS,
a11.O_ORDERKEY O_ORDERKEY,
avg(a11.O_TOTALPRICE) over(partition by a11.O_ORDERSTATUS order by a11.O_TOTALPRICE asc rows 3 preceding) WJXBFS1
From ORDERS a11
Where a11.O_ORDERKEY <= 20