Teradata functions supported by MicroStrategy

Basic Function

MicroStrategy pushes the calculations for the following list of built-in basic functions down to Teradata using the associated SQL patterns.

MicroStrategy Function Name	SQL Pattern
ABS	ABS(#0)
COS	COS(#0)
EXP	EXP(#0)
LN	LN(#0)
LOG10	LOG(#0)
POWER	POWER(#0, #1)
ROUND	ROUND(#0,0)
ROUND2	ROUND(#0, #1)
SIN	SIN(#0)
SQRT	SQRT(#0)
MOD	((#0) MOD (#1))
STDEV	STDDEV_SAMP(#0#< #*#>)
VAR	VAR_SAMP(#0#< #*#>)
STDEVP	STDDEV_POP(#0#< #*#>)
VARP	VAR_POP(#0#< #*#>)
SKEW	SKEW(#0#< #*#>)
KURTOSIS	KURTOSIS(#0#< #*#>)

Date and Time Function

MicroStrategy supports the following date and time functions.

MicroStrategy Function Name	SQL Pattern
CURRENTDATE	CURRENT_DATE
DAYOFMONTH	EXTRACT(DAY FROM #0)
DAYOFWEEK	TO_NUMBER(TO_CHAR(#0, 'D'))
DAYOFYEAR	TO_NUMBER(TO_CHAR(#0, 'DDD'))
WEEK	TO_NUMBER(TO_CHAR(#0, 'WW'))
MONTH	EXTRACT(MONTH FROM #0)
QUARTER	TO_NUMBER(TO_CHAR(#0, 'Q'))
YEAR	EXTRACT(YEAR FROM #0)
CURRENTDATETIME	LOCALTIMESTAMP
CURRENTTIME	LOCALTIMESTAMP
HOUR	TO_NUMBER(TO_CHAR(#0, 'HH24'))
MINUTE	TO_NUMBER(TO_CHAR(#0, 'MI')
SECOND	TO_NUMBER(TO_CHAR(#0, 'SS'))
DATE	TRUNC(CAST(#0 AS DATE))
DAYSBETWEEN	CAST(#1 AS DATE) - CAST(#0 AS DATE)
MONTHSBETWEEN	FLOOR(MONTHS_BETWEEN(#1, #0))
ADDDAYS	(#0 + #1 * INTERVAL '1' DAY)
ADDMONTHS	ADD_MONTHS(#0, Cast(#1 as Integer))
MONTHSTARTDATE	TRUNC(ADD_MONTHS((LAST_DAY(#0) + 1), -1))
MONTHENDDATE	TRUNC(LAST_DAY(#0))
YEARSTARTDATE	ADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0) + 1, 1 - EXTRACT(MONTH FROM #0))
YEARENDDATE	ADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0), 13 - EXTRACT(MONTH FROM #0))

OLAP Function

MicroStrategy pushes the calculations for the following list of built-in OLAP functions down to Teradata using the associated SQL patterns.

MicroStrategy Function Name		Column Name
RUNNINGAVG		avg(#0) over(#1)
RUNNINGMAX		max(#0) over(#1)
RUNNINGMIN		min(#0) over(#1)
RUNNINGCOUNT		count(#0) over(#1)
RUNNINGSUM		sum(#0) over(#1)
MOVINGAVG		avg(#0) over(#1)
MOVINGMAX		max(#0) over(#1)
MOVINGMIN		min(#0) over(#1)
MOVINGSUM		sum(#0) over(#1)
Rank		#0#,#<partition by#>#<#, ##>#\|rank over(#1#2#<,##> order by #0)
COVARIANCE		COVAR_POP(#0, #1)
CORRELATION		CORR(#0, #1)
SLOPE		REGR_SLOPE(#0, #1)
INTERCEPT		REGR_INTERCEPT(#0, #1)
BANDING		Case when (#0 between #1 and #2) then cast((1.0*(#0- #1)/#3) +1 AS INTEGER) else 0 end
BANDINGC		Case when (#1=#2 and #0=#1) then #3 when (#0=#2) then #3 when (#0 between #1 and #2) then CAST(1.0(#0- #1)/(#2- #1)#3+1 AS INTEGER) else 0 end
IF		(Case when #0 then #1 else #2 end)
RUNNINGSTDEV		stddev_samp(#0) over(#1)
RUNNINGSTDEVP		stddev_pop(#0) over (#1)
MOVINGSTDEV		stddev_samp(#0) over(#1)
MOVINGSTDEVP		stddev_pop(#0) over(#1)
OLAPSUM		sum(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o\|O:o\|W:o\|D:n\|R:n
OLAPAVG		avg(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o\|O:o\|W:o\|D:n\|R:y
OLAPCOUNT		count(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o\|O:o\|W:o\|D:n\|N:y\|R:y
OLAPMAX		max(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o\|O:o\|W:o\|D:n\|R:y
OLAPMIN		min(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o\|O:o\|W:o\|D:n\|R:y
LAG		CASE WHEN count() OVER ([#P] [#O] rows between unbounded preceding and current row) <= #1 THEN #2 ELSE sum(#0#< ##>) OVER ([#P][#O] rows between #1 preceding and #1 preceding) END" OLAPDBPATTERN="P:o\|O:r
LEAD		CASE WHEN count() OVER ([#P] [#O] rows between current row and unbounded following) <= #1 THEN #2 ELSE sum(#0#< ##>) OVER ([#P] [#O] rows between #1 following and #1 following) END" OLAPDBPATTERN="P:o\|O:r
OLAPRANK		rank() over ([#P] [#O])" OLAPDBPATTERN="P:o\|O:r
LAG		lag(#0#<, #*#>) over ([#P][#O])" OLAPDBPATTERN="P:o\|O:r
LEAD		lead(#0#<, #*#>) over ([#P] [#O])" OLAPDBPATTERN="P:o\|O:r
OLAPRANK		rank() over ([#P] [#O])" OLAPDBPATTERN="P:o\|O:r
MEDIAN		MEDIAN(#0)
FIRST IN RANGE		first_value(#0) over(#1)
LAST IN RANGE		last_value(#0) over(#1)

Teradata OLAP Functions

OLAP functions are a powerful family of functions for business intelligence and data warehousing applications. These functions are collectively called “ordered analytical functions” in Teradata, and they provide significant analytical capabilities for many business analysis queries.

MicroStrategy’s OLAP functions are calculated via SQL in Teradata rather than in the MicroStrategy analytical engine. There is no VLDB setting associated with this option.

SQL Syntax for Ordered Analytic Functions:

Moving average example:

select a11.DAY_DATE DAY_DATE,

a12.REGION_ID REGION_ID,

max(a13.REGION_NAME) REGION_NAME,

sum(a11.TOT_DOLLAR_SALES) WJXBFS1,

avg(sum(a11.TOT_DOLLAR_SALES)) over(partition by a12.REGION_ID order by

sum(a11.TOT_DOLLAR_SALES) asc rows 2 preceding) WJXBFS1

from DAY_CTR_SLS a11 j

oin LU_CALL_CTR a12

on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)

join LU_REGION a13 on (a12.REGION_ID = a13.REGION_ID)

group by a11.DAY_DATE, a12.REGION_ID

Ranking: The RANK function is closely related to other ordered analytical functions. Key distinctions of the RANK function are that it requires an order by clause and does not support a window clause. The MicroStrategy Engine is aware that Teradata supports the Rank function, so the ranking will be done by the database rather than the MicroStrategy Analytical Engine.

select a13.REGION_ID REGION_ID, max(a14.REGION_NAME) REGION_NAME,

a11.EMP_ID EMP_ID,

max(a12.EMP_LAST_NAME) EMP_LAST_NAME,

max(a12.EMP_FIRST_NAME) EMP_FIRST_NAME,

sum(a11.TOT_DOLLAR_SALES) WJXBFS1,

rank () over( order by sum(a11.TOT_DOLLAR_SALES) desc nulls last) WJXBFS1

from ITEM_EMP_SLS a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)

join LU_CALL_CTR a13 on (a12.CALL_CTR_ID = a13.CALL_CTR_ID) join LU_REGION a14

on (a13.REGION_ID = a14.REGION_ID)

group by a13.REGION_ID, a11.EMP_ID

Math Function

MicroStrategy pushes the calculations for the following list of built-in Math functions down to Teradata using the associated SQL patterns.

MicroStrategy Function Name	SQL Pattern
ACOS	ACOS(#0)
ASIN	ASIN(#0)
ATAN	ATAN(#0)
ATAN2	ATAN2(#0,#1)
COSH	COSH(#0)
SINH	SINH(#0)
TAN	TAN(#0)
TANH	TANH(#0)
ACOSH	ACOSH(#0)
ASINH	ASINH(#0)
ATANH	ATANH(#0)
GEOMEAN	EXP(AVG(LN(#0)))
PRODUCT	(CASE WHEN SUM(CASE WHEN #0=0 THEN 1 ELSE 0 END) > 0 THEN 0 ELSE (POWER(-1,SUM(CASE WHEN #0<0 THEN 1 END)))*EXP(SUM(LN(ABS(CASE WHEN #0<>0 THEN #0 ELSE NULL END)))) END)
CEILING	CEILING(#0)
DEGRESS	(POWER(ASIN(1),(-1))#090)
FLOOR	FLOOR(#0)
RADIANS	(ASIN(1)*#0/90)
TRUNC	TRUNC(#0)

Financial Function

Currently no predictive functions in Teradata are supported by MicroStrategy.

Statistical Function

MicroStrategy pushes the calculations for the following list of built-in statistical functions down to Teradata using the associated SQL patterns.

MicroStrategy Function Name	SQL Pattern
INT	CASE WHEN #0 > 0 THEN TRUNC(#0, 0) ELSE (TRUNC(#0, 0) - 1) END
INT2	(CASE WHEN (#0-CAST(#0 as integer)) >= 0 THEN CAST(#0 as integer) ELSE (CAST(#0 as integer) - 1) END)
LOG	(LN(#0)/LN(#1))
QUOTIENT	TRUNC((#0)/(CASE WHEN (#1)=0 THEN NULL ELSE(#1) END), 0)
MOVINGCOUNT	SUM(#0- #0+1) OVER(#1)
MOVINGSTDEV	(CASE WHEN SUM(#0 - #0 +1) OVER(#1)=1 THEN 0 ELSE SQRT((SUM(#0#0) OVER(#1)-((SUM(#0) OVER(#1))/(SUM(#0-OVER(#1)SUM(#0) OVER(#1))))/(SUM(#0- #0+1) OVER(#1)-1)) END)
FISHER	(LN((1+#0)/(1- #0))/2)
INVERSEFISHER	((EXP(2#0)-1)/(EXP(2#0)+1))
PEARSON	(((SUM(#0-#0+#1-#1+1)SUM(#0#1))-(SUM(#0)SUM(#1)))/SQRT(((SUM(#0-#1+1)SUM(#0#0))-(SUM(#0)SUM(#0)))((SUM(#0-#1+1)SUM(#1#1))-(SUM(#1)SUM(#1)))))
STANDARDIZE	case when (#2 > 0) then (#0 - #1)/(#2) else NULL end
RSQUARE	REGR_R2(#0,#1)
STEYX	SQRT((1/(SUM(#1- #1+#0- #0+1)(SUM(#1- #1+#0- #0+1)-2)))((SUM(#1- #1+#0- #0+1)SUM(#0#0))- (SUM(#0)SUM(#0))-((((SUM(#1- #0+1)SUM(#1#0))-(SUM(#1)SUM(#0)))((SUM(#1-#0+1)SUM(#1#0))-(SUM(#1)SUM(#0))))/((SUM(#1-#0+1)SUM(#1#1))-(SUM(#1)*SUM(#1))))))
GREATEST	GREATEST(#0#<, #*#>)
LEAST	LEAST(#0#<, #*#>)

Comment

0 comments

Details

Knowledge Article

Published:

January 31, 2024

Last Updated:

January 21, 2026