Using Single-Value Functions

The key to understanding the computation of a single-value function is to identify the way it is used. The next two subsections provide examples of the two uses of single-value functions and how they are processed.

The first example shows a single-value function applied before a group-value function. This is referred to as transforming a fact. The second example shows a single-value function applied after the group-value function. This is referred to as a compound metric.

Each example contains two reports, the first one showing the SQL syntax when calculations are processed by the database and the second one when calculations are processed by the Intelligence Server.

The use of transform in this context refers to retrieving a fact in a different form. For example, you can obtain the absolute value for a fact or the natural logarithm, but the fact is the same. This differs from a transformation where the data retrieved is different when a transformation is applied, for example, last year's revenue instead of this year's revenue.

Transforming a Fact Into Another Fact

In this case, the fact Revenue is transformed into another fact, namely the natural logarithm values defined as Ln(Revenue) or the truncated revenue values defined as Trunc(Revenue).

The following example describes this type of usage and displays the SQL that is generated.

Consider the two metrics defined as follows:

M3A = Sum(Ln(Revenue)){~+}
M3B = Sum(Trunc(Revenue)){~+}

Put M3A with Region on the template. The database, in this case, SQL Server 2000, supports the function Ln. The following SQL is generated:

Report 3A (SQL Single-Value Before Group-Value Function) - SQL Server 2000

Pass0 - Duration: 0:00:40.45
select a13.REGION_ID  REGION_ID,
	max(a14.REGION_NAME)  REGION_NAME,
	sum(LOG(a11.ORDER_AMT))  WJXBFS1
from ORDER_FACT 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

Based on the SQL, this is what happened:

The Ln function is applied to the fact Revenue, which is defined in the warehouse by the column ORDER_AMT.
The Ln function uses the syntax LOG in SQL Server 2000.
Then, the Sum function is performed on the new fact, namely Ln(Revenue).

Now, put M3B with Region on the template, the following SQL is generated:

Report 3B (MicroStrategy Single-Value Before Group-Value Function)

select a11.[CALL_CTR_ID] AS CALL_CTR_ID,
	a11.[CUST_CITY_ID] AS CUST_CITY_ID,
	a12.[REGION_ID] AS REGION_ID,
	a13.[REGION_NAME] AS REGION_NAME,
	a11.[TOT_DOLLAR_SALES] AS WJXBFS1
from [CITY_CTR_SLS] a11, 
	[LU_CALL_CTR] a12, 
	[LU_REGION] a13
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID] and 
	a12.[REGION_ID] = a13.[REGION_ID]
[An Analytical SQL]

In this case, as noted by the text [An Analytical SQL], the functions Trunc and Sum are computed by the Intelligence Server. Intelligence Server computes the new fact Trunc([Dollar Sales]) first and then uses the Sum function to sum the new fact for each region.

Calculating a Compound Metric

Single-value functions can be used to create compound metrics. Subtraction, addition, division, and multiplication operators (–, +, /, *) are common examples of single-value functions. See the following examples:

(Sum([Dollar Sales]){~}/Sum([Dollar Sales]) {~,Country})
(Sum([Dollar Sales]){~} + Sum([Freight]) {~})

The examples below use a compound metric and a metric that transforms a fact in the same report. The examples illustrate the SQL generated when the function is supported by the database and the Intelligence Server, respectively.

Consider the following metric definitions:

M4A = Ln(Sum(Revenue){~})
M4B = Trunc(Sum(Revenue){~})

Put metric M3A, from the previous example, and metric M4A together with attribute Region on the template. The database, in this case SQL Server 2000, supports the Ln function. The following SQL is generated:

Report 4A (SQL Single-Value Before and After Group-Value Functions) - SQL Server 2000

Pass0 - Duration: 0:00:02.58
select a13.REGION_ID  REGION_ID,
	max(a14.REGION_NAME)  REGION_NAME,
	sum(LOG(a11.ORDER_AMT))  WJXBFS1,
	LOG(sum(a11.ORDER_AMT))  WJXBFS2
from ORDER_FACT 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

To process metric M3A, the single-value function Ln is calculated before the group-value function Sum, and for metric M4A, Ln is calculated after Sum.

The Ln function uses the syntax LOG in SQL Server 2000.

Similarly, if you put metrics M3B and M4B together with attribute Region on the template, and the database does not support the Trunc function, then the following SQL is generated:

Pass0 - Duration: 0:00:12.67
select a11.ORDER_DATE  DAY_DATE,
	a11.EMP_ID  EMP_ID,
	a11.ORDER_ID  ORDER_ID,
	a13.REGION_ID  REGION_ID,
	a14.REGION_NAME  REGION_NAME,
	a11.ORDER_AMT  WJXBFS1
from ORDER_FACT 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)
Pass1 - Duration: 0:00:08.46
[An Analytical SQL]

The fact, Revenue, which isdefined ascolumn ORDER_AMT in the ORDER_FACT table, is retrieved and used to compute both metrics.