Math Functions in OBIEE

There are many science, research and development functions which are not much explored or used in business intelligence. Most of such functions belong to Math Functions. I am listing below all Math Functions.

Math Functions

Abs, Acos, Asin, Atan, Atan2, Ceiling, Cos, Cot, Degrees, Exp, Floor, Log, Log10, Mod, Pi, Power, Radians, Rand, RandFromSeed, Round, Sign, Sin, Sqrt, Tan, Truncate

As I am not working in any scientific or research & development project, so I decided to manually create such functions, to describe math functions. I then thought to randomly select some of trigonometric functions & test result through their graph, so I choose sin, cos and tan. But for trigonometric function I had to further make use of pi (you may use degrees as well).

The functions applied in my case for different functions is as below:

Sin - SIN((RCOUNT(1)-1)*PI()/4)

Cos - COS((RCOUNT(1)-1)*PI()/4)

Tan - TAN((RCOUNT(1)-1)*PI()/4)

Angle in Degree - CEILING(DEGREES((RCOUNT(1)-1)*PI()/4))

Where RCOUNT(1)-1 gives numeric value starting from zero & 1 in RCOUNT(1) is numeric symbolizing first column with distinct data & PI()/4 is used to multiply each count to pi/4 ( quarter of semicircle).

Math Function Test
Test Maths
Calculated Attributes
Angle in Degree	Sine Value	Cos Value	Tan Value
0	0	1	0
45	0.707	0.707	1
90	1	0	6.18986E+14
135	0.707	-0.707	-1
180	0	-1	0
225	-0.707	-0.707	1
270	-1	0	2.16235E+14
315	-0.707	0.707	-1
360	0	1	0
405	0.707	0.707	1
450	1	0	1.20485E+14
495	0.707	-0.707	-1
540	0	-1	0
585	-0.707	-0.707	1
630	-1	0	9.01976E+13
675	-0.707	0.707	-1
720	0	1	0
765	0.707	0.707	1
810	1	0	6.77417E+13
855	0.707	-0.707	-1

So as verifying from the graph & tabular value, it is clear to make use of them, leaving tan pi/2 as an exception of giving a finite value. However still aviating, some more scientific calculus functions, which will make obiee full package to be used in scientific research & development projects.

J

To Get Age between two Days in OBIEE Report

For Getting Age between two dates we use SQL Query as :

datediff(d, startdate, enddate) as Age

However default date format in OBIEE is Timestamp. So you need timestamp function (Available under Calendar/Date Function Heading) to get required result.

Syntax:

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

Where:
interval is the specified interval. Valid values are:

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_YEAR

timestamp1 and timestamp2 are any valid timestamps.

Example
For Number of Days between two days:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, “TO_DATE_COLUMN”)

For Number of Days till current date:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, CURRENT_DATE)

*You can also use this function for getting age difference for other time dimension attributes as week, quarter, month or year. All you need to do is change first attribute i.e 'interval' & other attribute's accordingly.

Conditional Expressions ( Function )

Conditional Expressions

Expressions are building blocks for creating conditional expressions that convert a value from one form to another. Expressions include:

·         CASE (Switch)

·         CASE (If)

CASE (Switch)

This form of the CASE statement is also referred to as the CASE(Lookup) form. The value of expr1 is examined, then the WHEN expressions. If expr1 matches any WHEN expression, it assigns the value in the corresponding THEN expression.

If none of the WHEN expressions match, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

If expr1 matches an expression in multiple WHEN clauses, only the expression following the first match is assigned.

Syntax

CASE expr1

     WHEN expr2 THEN expr3

     {WHEN expr... THEN expr...}

     ELSE expr

END 

Where:

CASE starts the CASE statement. Must be followed by an expression and one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.

WHEN specifies the condition to be satisfied.

THEN specifies the value to assign if the corresponding WHEN expression is satisfied.

ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.

END ends the CASE statement.

Example

CASE "TableHeading"."Column Name" 
WHEN 'Col_Val1' THEN 'Val1' 
WHEN 'Col_Val2' THEN 'Val2' 
WHEN 'Col_Val3' THEN 'Val3' 
ELSE "TableHeading"."Column Name" 
END

In this example, the WHEN statements must reflect a strict equality. 

CASE (If)

This form of the CASE statement evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression.

If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

Syntax

CASE 

     WHEN request_condition1 THEN expr1

     {WHEN request_condition2 THEN expr2}

     {WHEN request_condition... THEN expr...}

     ELSE expr

END 

Where:

CASE starts the CASE statement. Must be followed by one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.

WHEN specifies the condition to be satisfied.

THEN specifies the value to assign if the corresponding WHEN expression is satisfied.

ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.

END ends the CASE statement.

Example

CASE 
WHEN ("TableHeading"."ColumnName1"='Val_A' AND "TableHeading"."ColumnName"='Value1') THEN 'Val1' 
WHEN ("TableHeading"."ColumnName2"='Val_B' AND "TableHeading1"."ColumnName2"='Value2') THEN 'Val2' 
WHEN "TableHeading"."Column Name"='Value3' THEN 'Val3' 
ELSE "TableHeading"."Column Name" 
END

Note :

Unlike the case-switch form, the WHEN statements in the case-if form allow comparison operators in a CASE statement, AND has precedence over OR.

Aggregate Functions OBIEE

Aggregate Functions

Aggregate functions perform operations on multiple values to create summary results.

The aggregate functions cannot be used to form nested aggregation in expressions on logical columns that have a default aggregation rule defined in the Aggregation tab of the Logical Column dialog. To specify nested aggregation, you must define a column with a default aggregation rule and then request the aggregation of the column in a SQL statement.

Aggregate functions include:

·         AGGREGATE AT

·         AVG

·         AVGDISTINCT

·         BOTTOMN

·         COUNT

·         COUNTDISTINCT

·         COUNT(*)

·         FIRST

·         GROUPBYCOLUMN

·         GROUPBYLEVEL

·         LAST

·         MAX

·         MEDIAN

·         MIN

·         NTILE

·         PERCENTILE

·         RANK

·         STDDEV

·         STDDEV_POP

·         SUM

·         SUMDISTINCT

·         TOPN

AGGREGATE AT

This function aggregates columns based on the level or levels you specify. Using AGGREGATE AT guarantees that the aggregate for the measure always occurs at the levels specified after the keyword AT, regardless of the WHERE clause.

Syntax

AGGREGATE(expr AT level [, level1, levelN])

Where:

expr is any expression that references at least one measure column

level is the level at which you want to aggregate. You can optionally specify multiple levels.

You cannot specify a level from a dimension that contains levels that are being used as the measure level for the measure you specified in the first argument. For example, you cannot write the function as AGGREGATE(yearly_sales AT month) because "month" is from the same time dimension that is being used as the measure level for "yearly_sales."

Example

The following example shows the AGGREGATE AT function and example results:

SELECT month, year, AGGREGATE(sales AT Year)

FROM timeseriestestingWHERE year = 1994 AND month = 12

Result:

Month    Year    AGGREGATE AT year12       1994    7396Row count: 1

Because the AGGREGATE AT operator is always executed before the predicates, it always returns the correct total for the time level specified after the keyword AT.

AVG

This function calculates the average (mean) value of an expression in a result set. It must take a numeric expression as its argument.

Note that the denominator of AVG is the number of rows aggregated. For this reason, it is usually a mistake to use AVG(x) in a calculation in Oracle Business Intelligence. Instead, write the expression manually so that you can control both the numerator and denominator (x/y).

Syntax

AVG(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

AVGDISTINCT

This function calculates the average (mean) of all distinct values of an expression. It must take a numeric expression as its argument.

Syntax

AVG(DISTINCT numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

BOTTOMN

This function ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numeric value. The BOTTOMN function operates on the values returned in the result set. A request can contain only one BOTTOMN expression.

Syntax

BOTTOMN(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

COUNT

This function calculates the number of rows having a nonnull value for the expression. The expression is typically a column name, in which case the number of rows with nonnull values for that column is returned.

Syntax:

COUNT(expr)

Where:

expr is any expression.

COUNTDISTINCT

This function adds distinct processing to the COUNT function.

Syntax

COUNT(DISTINCT expr)

Where:

expr is any expression.

COUNT(*)

This function counts the number of rows.

Syntax

COUNT(*)

Example

For example, if a table named Facts contained 200,000,000 rows, the sample request would return the results shown:

SELECT COUNT(*) FROM Facts

Result:

200000000

FIRST

This function selects the first returned value of the expression argument. For example, the FIRST function can calculate the value of the first day of the year.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. You cannot use it in SQL statements.

The FIRST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the FIRST function returns the first day in each level.

You should not use the FIRST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance.

When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest PERIODROLLING, FIRST, and LAST functions.

Syntax

FIRST(expr)

Where:

expr is any expression that references at least one measure column.

Example

FIRST(sales)

GROUPBYCOLUMN

For use in setting up aggregate navigation. It specifies the logical columns that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data grouped by store and by month, specify the following syntax in the content filter (General tab of Logical Source dialog):

GROUPBYCOLUMN(STORE, MONTH)

The GROUPBYCOLUMN function is only for use in configuring a repository. You cannot use it to form SQL statements.

GROUPBYLEVEL

For use in setting up aggregate navigation. It specifies the dimension levels that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data at the store and month levels, and if you have defined dimensions (Geography and Customers) containing these levels, specify the following syntax in the content filter (General tab of Logical Source dialog):

GROUPBYLEVEL(GEOGRAPHY.STORE, CUSTOMERS.MONTH)

The GROUPBYLEVEL function is only for use in configuring a repository. You cannot use it to form SQL statements.

LAST

This function selects the last returned value of the expression. For example, the LAST function can calculate the value of the last day of the year.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. You cannot use it in SQL statements.

The LAST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the LAST function returns the last day in each level.

You should not use the LAST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance.

When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest PERIODROLLING, FIRST, and LAST functions.

Syntax

LAST(expr)

Where:

expr is any expression that references at least one measure column.

Example

LAST(sales)

MAX

This function calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.

Syntax

MAX(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MAX function resets its values for each group in the query according to specific rules.

MEDIAN

This function calculates the median (middle) value of the rows satisfying the numeric expression argument. When there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.

Syntax

MEDIAN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MEDIAN function resets its values for each group in the query according to specific rules.

MIN

This function calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.

Syntax

MIN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MIN function resets its values for each group in the query according to specific rules.

NTILE

This function determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. In other words, the resulting sorted data set is broken into several tiles where there are roughly an equal number of values in each tile.

NTile with numTiles = 100 returns what is commonly called the "percentile" (with numbers ranging from 1 to 100, with 100 representing the high end of the sort). This value is different from the results of the Oracle BI PERCENTILE function, which conforms to what is called "percent rank" in SQL 92 and returns values from 0 to 1.

Syntax

NTILE(numExpr, numTiles)

Where:

numExpr is any expression that evaluates to a numeric value.

numTiles is a positive, nonnull integer that represents the number of tiles.

If the numExpr argument is not null, the function returns an integer that represents a rank within the requested range.

PERCENTILE

This function calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.

The percentile is calculated based on the values in the result set.

Syntax

PERCENTILE(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The PERCENTILE function resets its values for each group in the query according to specific rules.

RANK

This function calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

The rank is calculated based on the values in the result set.

Syntax

RANK(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The RANK function resets its values for each group in the query according to specific rules.

STDDEV

This function returns the standard deviation for a set of values. The return type is always a double. STDEV_SAMP is a synonym for STDDEV.

Syntax

STDDEV([ALL | DISTINCT] numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

If ALL is specified, the standard deviation is calculated for all data in the set.

If DISTINCT is specified, all duplicates are ignored in the calculation.

If nothing is specified (the default), all data is considered.

The STDDEV function resets its values for each group in the query according to specific rules.

STDDEV_POP

This function returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.

Syntax

STDDEV_POP([ALL | DISTINCT] numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

If ALL is specified, the standard deviation is calculated for all data in the set.

If DISTINCT is specified, all duplicates are ignored in the calculation.

If nothing is specified (the default), all data is considered.

SUM

This function calculates the sum obtained by adding up all values satisfying the numeric expression argument.

Syntax

SUM(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The SUM function resets its values for each group in the query according to specific rules.

SUMDISTINCT

This function calculates the sum obtained by adding all of the distinct values satisfying the numeric expression argument.

Syntax

SUM(DISTINCT numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

TOPN

This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.

Syntax

TOPN(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

The TOPN function resets its values for each group in the query according to specific rules.

Reference :

otn.oracle.com