MicroStrategy pushes the calculations for the following list of built-in basic functions down to SAP HANA using the associated SQL patterns.
Function | SQL Pattern |
Add | (#0#< + #*#>) |
Average | (#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) |
Avg | AVG(#0#<#*#>) |
Count | COUNT(#0#<#*#>) |
GeoMeanFunction | EXP(AVG(LN(#0))) |
GreatestFunction | GREATEST(#0#<, #*#>) |
LeastFunction | LEAST(#0#<, #*#>) |
Max | MAX(#0#<#*#>) |
Min | MIN(#0#<#*#>) |
Multiply | (#0#< * #*#>) |
StdevFunction | STDDEV(#0#< #*#>) |
Sum | SUM(#0#<#*#>) |
VarFunction | VAR(#0#< #*#>) |
Microstrategy uses the Analytical Engine to implement the following Basic Functions:
MicroStrategy pushes the calculations for the following list of built-in string functions down to SAP HANA using the associated SQL patterns.
Function | SQL Pattern |
|---|---|
ConcatFunction | CONCAT(#0, #1) |
LengthFunction | LENGTH(#0) |
LowerFunction | LOWER(#0) |
LTrimFunction | LTRIM(#0) |
PositionFunction | LOCATE(#1, #0) |
RTrimFunction | RTRIM(#0) |
SubStrFunction | SUBSTRING(#0, #1, #2) |
InitCapFunction | (UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) |
TrimFunction | LTRIM(RTRIM(#0)) |
RightStrFunction | RIGHT(#0, #1) |
LeftStrFunction | LEFT(#0, #1) |
UpperFunction | UPPER(#0) |
ConcatBlankFunction | (#0#< || ' ' || #*#>) |
MicroStrategy uses the Analytical Engine to implement the following string functions:
MicroStrategy pushes the calculations for the following list of built-in date and time functions down to SAP HANA using the associated SQL patterns.
Function | SQL Pattern |
DaysBetweenFunction | DAYS_BETWEEN(#0, #1) |
YearEndDateFunction | ADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0)) |
CurrentDateFunction | NOW() |
DayOfMonthFunction | DAYOFMONTH(#0) |
DayOfWeekFunction | (CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END) |
DayOfYearFunction | DAYOFYEAR(#0) |
WeekFunction | TO_CHAR(#0,'WW') |
MonthFunction | MONTH(#0) |
QuarterFunction | TO_CHAR (#0, 'Q') |
YearFunction | YEAR(#0) |
CurrentDateTimeFunction | NOW() |
CurrentTimeFunction | NOW() |
HourFunction | HOUR(#0) |
MinuteFunction | MINUTE(#0) |
SecondFunction | ROUND(SECOND(#0)) |
MilliSecondFunction | substring(cast(extract(second from #0) as char(10)), 4, 3) |
MonthStartDateFunction | ADD_DAYS(#0,1-DAYOFMONTH(#0)) |
YearStartDateFunction | ADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0))) |
AddMonthsFunction | ADD_MONTHS(#0, #1) |
MonthsBetweenFunction | (12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END)) |
AddDaysFunction | ADD_DAYS(#0, #1) |
MonthEndDateFunction | ADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1)))) |
DateFunction | TO_DATE(#0,'YYYY-MM-DD') |
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 SAP HANA 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 SAP HANA using the associated SQL patterns.
Function | SQL Pattern |
LagFunction | lag(#0#<, #*#>) over ([#P] [#O]) |
LeadFunction | lead(#0#<, #*#>) over ([#P] [#O]) |
OLAPRankFunction | rank() over ([#P] [#O]) |
MicroStrategy pushes the calculations for the following list of built-in math functions down to SAP HANA using the associated SQL patterns.
Function | SQL Pattern |
Round2Function | ROUND(#0, #1) |
RankFunction | #0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0) |
AbsFunction | ABS(#0) |
IntersectFunction | #<(#>#1#<#0 intersect #*#>#<)#> |
Int2Function | FLOOR(#0) |
AcosFunction | ACOS(#0) |
AcoshFunction | LN(#0+SQRT(#0-1)*SQRT(#0+1)) |
AsinFunction | ASIN(#0) |
AtanFunction | ATAN(#0) |
Atan2Function | ATAN2(#1, #0) |
AtanhFunction | ((LN(1+#0)-LN(1- #0))/2) |
CeilingFunction | CEILING(#0) |
CosFunction | COS(#0) |
CoshFunction | COSH(#0) |
ExpFunction | EXP(#0) |
FloorFunction | FLOOR(#0) |
IntFunction | FLOOR(#0) |
LnFunction | LN(#0) |
LogFunction | LOG(#1, #0) |
Log10Function | LOG(10,#0) |
PowerFunction | POWER(#0, #1) |
QuotientFunction | (CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END) |
RoundFunction | ROUND(#0, 0) |
SinFunction | SIN(#0) |
SinhFunction | SINH(#0) |
SqrtFunction | SQRT(#0) |
TanFunction | TAN(#0) |
TanhFunction | TANH(#0) |
TruncFunction | CAST(#0 AS INTEGER) |
AsinhFunction | LN(#0+SQRT(POWER(#0,2)+1)) |
ModFunction | MOD(#0, #1) |
MicroStrategy uses the Analytical Engine to implement the following math functions:
Currently no financial functions in SAP HANA are supported by MicroStrategy.
All 12 statistical functions can be executed by the MicroStrategy Analytics Engine:
Currently no predictive functions in SAP HANA are supported by MicroStrategy.
