Supported Amazon Redshift functions

Associate Scrum Master • Strategy

This article identifies the Amazon Redshift functions or SQL patterns that are supported by MicroStrategy.

Amazon Redshift supports a wide range of built-in functions which allows MicroStrategy to push down almost all the analytical functions that are supported within its platform. The following table lists the number of functions calculated in Amazon Redshift and the total number of functions offered by MicroStrategy, by functional category.

MicroStrategy Function	Calculated in Amazon Redshift	Total
Basic	11	21
Data Mining	0	30
Date and Time	23	31
Financial	0	50
Mathematical	33	34
OLAP	20	39
Other	4	16
Pattern	0	3
Statistical	8	51
String	13	25
Total	112	300

Basic Function

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

Function	SQL Pattern
UnionFunction	#<(#>#1#<#0 union #*#>#<)#>
ExceptFunction	#<(#>#1#<#0 except #*#>#<)#>
StdevPFunction	STDDEV_POP(#0#< #*#>)
LikeOperator	#0 LIKE '#1'
ProductFunction	(CASE WHEN SUM(CASE WHEN #0=0 THEN 1 ELSE 0 END) > 0 THEN 0 ELSE POW(-1,SUM(CASE WHEN #0<0 THEN 1 ELSE 0 END))*EXP((SUM(LN(ABS(CASE WHEN #0<>0 THEN #0::float8 ELSE NULL END))))) END)
StdevFunction	STDDEV_SAMP(#0#< #*#>)
VarFunction	VAR_SAMP(#0#< #*#>)
GeoMeanFunction	EXP(AVG(LN(#0)))
NotLikeOperator	#0 NOT LIKE '#1'
IFOperator	(CASE WHEN #0 THEN #1 ELSE #2 END)
VarPFunction	VAR_POP(#0#< #*#>)

Date and Time Function

MicroStrategy supports the following date and time functions.

Function	SQL Pattern
DaysBetweenFunction	DATEDIFF(DAY, #0, #1)
YearEndDateFunction	ADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0), 13 - EXTRACT(MONTH FROM #0))
CurrentDateFunction	CURRENT_DATE
DayOfMonthFunction	EXTRACT(DAY FROM #0)
DayOfWeekFunction	(DATE_PART('dow',#0)+1)
DayOfYearFunction	DATE_PART('doy',#0)
WeekFunction	DATE_PART('week',#0)
MonthFunction	EXTRACT(MONTH FROM #0)
QuarterFunction	EXTRACT(QUARTER FROM #0)
YearFunction	EXTRACT(YEAR FROM #0)
CurrentDateTimeFunction	SYSDATE
CurrentTimeFunction	SYSDATE
HourFunction	EXTRACT(HOUR FROM #0)
MinuteFunction	EXTRACT(MINUTE FROM #0)
SecondFunction	EXTRACT(SECOND FROM #0)
MilliSecondFunction	EXTRACT(MILLISECOND FROM #0)
MonthStartDateFunction	dateadd(day, -1*(extract(day from #0) -1),#0)
YearStartDateFunction	ADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0) + 1, 1 - EXTRACT(MONTH FROM #0))
AddMonthsFunction	ADD_MONTHS(#0, #1)
MonthsBetweenFunction	FLOOR(MONTHS_BETWEEN(#1,#0))
MonthEndDateFunction	dateadd(day, -1*extract(day from add_months(#0, 1)), add_months(#0, 1))
DateFunction	CAST(#0 AS DATE)

OLAP Function

OLAP functions are a powerful family of functions for business intelligence and data warehousing applications. These functions provide significant analytical capabilities for many business analysis queries. Many of MicroStrategy’s OLAP functions are calculated via SQL in Amazon Redshift rather than in the MicroStrategy analytical engine. There is no VLDB setting associated with this option.

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

Function	SQL Pattern
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)
MovingCountFunction	count(#0) over(#1)
RunningMaxFunction	max(#0) over(#1)
RunningMinFunction	min(#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])
OLAPAvgFunction	avg(#0) over ([#P] [#O] [#W])
OLAPCountFunction	count(#0) over ([#P] [#O] [#W])
OLAPMaxFunction	max(#0) over ([#P] [#O] [#W])
OLAPMinFunction	min(#0) over ([#P] [#O] [#W])
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
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
OLAPRankFunction	rank() over ([#P] [#O])

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
      join 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 Amazon Redshift supports the Rank function, so the ranking will be done by the database rather than the MicroStrategy Analytical Engine.
Rank example:
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 Amazon Redshift using the associated SQL patterns.

Function	SQL Pattern
Round2Function	ROUND(#0, CAST(#1 AS INTEGER))
RankFunction	#0#,#<partition by#>#<#, ##>#\|rank () over(#1#2#<, ##> order by #0)
AbsFunction	ABS(#0)
IntersectFunction	#<(#>#1#<#0 intersect #*#>#<)#>
AcosFunction	ACOS(#0)
AcoshFunction	LN(#0+SQRT(#0-1)*SQRT(#0+1))
AsinFunction	ASIN(#0)
AtanFunction	ATAN(#0)
Atan2Function	ATAN2(#0, #1)
AtanhFunction	((LN(1+#0)-LN(1- #0))/2)
CeilingFunction	CEIL(#0)
CosFunction	COS(#0)
CoshFunction	((EXP(#0::float8)+EXP((#0*(-1))::float8))/2)
DegreesFunction	DEGREES(#0)
ExpFunction	EXP(#0::float8)
FloorFunction	FLOOR(#0)
IntFunction	FLOOR(#0)
LnFunction	LN(#0)
LogFunction	LOG(#0)/LOG(#1)
Log10Function	LOG(#0)
PowerFunction	POW(#0, #1)
QuotientFunction	TRUNC((#0)/NULLIF(#1, 0))
RadiansFunction	RADIANS(#0)
RandbetweenFunction	((#1- #0)*RANDOM()+#0)
RoundFunction	ROUND(#0)
SinFunction	SIN(#0)
SinhFunction	((EXP(#0::float8) - EXP((#0*(-1))::float8))/2)
SqrtFunction	SQRT(#0)
TanFunction	TAN(#0)
TanhFunction	((EXP(#0::float8)-EXP((#0(-1))::float8))/(EXP(#0::float8)+EXP((#0(-1))::float8)))
TruncFunction	TRUNC(#0)
AsinhFunction	LN(#0+SQRT(POWER(#0,2)+1))
ModFunction	(#0-FLOOR((#0)/NULLIF(#1, 0))*(#1))

Statistical Function

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

Function	SQL Pattern
KurtosisFunction	((((SUM(#0- #0+1)(SUM(#0- #0+1)+1))/((SUM(#0- #0+1)-1)(SUM(#0- #0+1)-2)(SUM(#0- #0+1)-3)))((SUM(POW(#0,4)))-(4SUM(POW(#0,3))AVG(#0))+(6SUM(POW(#0,2))POW(AVG(#0),2))-(4SUM(#0)POW(AVG(#0),3))+
FisherFunction	(LN((1+#0)/(1- #0))/2)
InverseFisherFunction	((EXP((2#0)-1)::float8)/(EXP((2#0)::float8)+1))
StandardizeFunction	case when (#2 > 0) then (#0 - #1)/(#2) else NULL end
CovarianceFunction	((1/SUM(#0- #0+#1- #1+1))(SUM(#0#1)-(AVG(#0)SUM(#1))-(SUM(#0)AVG(#1))+(AVG(#0)AVG(#1)SUM(#0- #0+#1- #1+1))))
RSquareFunction	POW((((SUM(#0- #0+#1- #1+1)SUM(#0#1))-(SUM(#0)SUM(#1)))/SQRT(((SUM(#0- #0+#1- #1+1)SUM(#0#0))-(SUM(#0)SUM(#0)))((SUM(#0- #0+#1- #1+1)SUM(#1#1))-(SUM(#1)SUM(#1))))),2)
SlopeFunction	(((SUM(#1- #1+#0- #0+1)SUM(#1#0))-(SUM(#1)SUM(#0)))/((SUM(#1- #1+#0- #0+1)SUM(#1#1))-(SUM(#1)SUM(#1))))
InterceptFunction	(AVG(#0)-((((SUM(#1- #1+#0- #0+1)SUM(#1#0))-(SUM(#1)SUM(#0)))/((SUM(#1- #1+#0- #0+1)SUM(#1#1))-(SUM(#1)SUM(#1))))*AVG(#1)))

String Functions

MicroStrategy pushes the calculations for the following list of built-in string functions down to Amazon Redshift using the associated SQL patterns.

Function	SQL Pattern
ConcatFunction	(#0#< \|\| #*#>)
LengthFunction	LENGTH(#0)
LowerFunction	LOWER(#0)
LTrimFunction	LTRIM(#0)
PositionFunction	POSITION(#0 IN #1)
RTrimFunction	RTRIM(#0)
SubStrFunction	SUBSTRING(#0, #1, #2)
InitCapFunction	INITCAP(#0)
TrimFunction	TRIM(BOTH ' ' FROM #0)
RightStrFunction	SUBSTRING(#0, (LENGTH(#0) - #1) + 1)
LeftStrFunction	SUBSTRING(#0, 1, #1)
UpperFunction	UPPER(#0)
ConcatBlankFunction	(#0#< \|\| ' ' \|\| #*#>)

Comment

0 comments

Details

Knowledge Article

Published:

August 4, 2017

Last Updated:

August 4, 2017