Using Group-Value Functions

The computation of group-value functions is done by either the Intelligence Server or the database depending upon the function used and support available. The following examples discuss how MicroStrategyperforms the group-value computations by providing the SQL syntax for specific situations.

Group-Value Functions in Simple Metrics

This subsection contains two examples that illustrate the processing of group-value functions. Each example contains two reports, the first one showing the SQL for a function supported by the database and the second one by the Analytical Engine.

Example 1: Sum(Revenue) vs. AvgDev(Revenue)

Consider a simple report, Report 1A, where the attribute Region is placed on the row axis and a simple metric defined as M1A=Sum(Revenue){~} is on the column axis. This report shows the sales for each region. The database, in this case Microsoft Access, supports the function Sum. The following SQL is generated:

• Report 1A (SQL Group-value function) - Microsoft Access

  select a12.[REGION_ID] AS REGION_ID,
  	max(a13.[REGION_NAME]) AS REGION_NAME,
  	sum(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]
  group by a12.[REGION_ID]

Now, consider Report 1B, again with the same attribute, Region, on the row axis, but with a different simple metric defined as M1B=AvgDev(Revenue){~} on the column axis. This report shows how revenue data varies from its mean for each region. The database does not support the function Average Deviation; therefore, the computation is performed by the Analytical Engine. The following SQL is generated:

• Report 1B (MicroStrategy 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 Report 1B, Intelligence Server performs the following steps:

• It retrieves all fact data from the warehouse. [TOTAL_ DOLLAR_SALES] is the column alias used for the fact (Revenue) in the temporary table during SQL generation.
• It uses the result set (that is held in memory) to compute the metric, namely AvgDev([TOTAL_ DOLLAR_SALES]){~} for each region.
• The notation [An analytical SQL] indicates that the computation is taking place in the Analytical Engine.
• It displays the final result.

In the previous examples, the dimensionality of both metrics is defined as {~}, which means that they both are calculated at the report level of Region, since the attribute Region is on the reports.

The next example explains how Intelligence Server processes dimensionality. When metric dimensionality is defined, the Analytical Engine can insert records back into the temporary database structures after the function calculation is performed so that dimensionality can be applied.

Example 2: Sum(Revenue) {~, Country} vs. AvgDev(Revenue) {~, Country}

Now use the same report template as in Example 1, but add a dimensionality to each metric for comparison. Use Sum(Revenue){~,Country} for Report 2A and AvgDev(Revenue){~,Country} for Report 2B. Notice that attribute Country is a parent of Region, and the relationship is one to many. In Report 2A, the Sum function is supported by the database; in Report 2B, the AvgDev function is supported by Intelligence Server. The SQL generated for both reports is as follows:

Report 2A (SQL Group-Value Function) - Microsoft Access

create table ZZT1Y03009ZMD000 (
 COUNTRY_ID BYTE, 
 WJXBFS1 DOUBLE)
insert into ZZT1Y03009ZMD000 
select a12.[COUNTRY_ID] AS COUNTRY_ID,
	sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from [CITY_CTR_SLS] a11, 
	[LU_CALL_CTR] a12
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
group by a12.[COUNTRY_ID]
select a11.[REGION_ID] AS REGION_ID,
	a11.[REGION_NAME] AS REGION_NAME,
	pa1.[WJXBFS1] AS WJXBFS1
from [ZZT1Y03009ZMD000] pa1, 
	[LU_REGION] a11
where pa1.[COUNTRY_ID] = a11.[COUNTRY_ID]
drop table ZZT1Y03009ZMD000

The first pass of SQL creates a temporary table to hold the data. The second pass computes the metric at the Country level, while the third pass joins with attribute Region since the result of the aggregation has to be displayed for each region. The final pass drops the temporary table.

Report 2B (MicroStrategy Group-Value Function)

select a11.[CUST_CITY_ID] AS CUST_CITY_ID,
	a12.[COUNTRY_ID] AS COUNTRY_ID,
	a11.[CALL_CTR_ID] AS CALL_CTR_ID,
	a11.[TOT_DOLLAR_SALES] AS WJXBFS1
from [CITY_CTR_SLS] a11, 
	[LU_CALL_CTR] a12
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
create table ZZMD00 (
 COUNTRY_ID BYTE, 
 WJXBFS1 FLOAT)
[An analytical SQL]
insert into ZZMD00 values ([DummyInsertValue])
select a11.[REGION_ID] AS REGION_ID,
	a11.[REGION_NAME] AS REGION_NAME,
	pa1.[WJXBFS1] AS WJXBFS1
from [ZZMD00] pa1, 
	[LU_REGION] a11
where pa1.[COUNTRY_ID] = a11.[COUNTRY_ID]
drop table ZZMD00

In Report 2B, an Analytical SQL pass is necessary to compute AvgDev since it is not a database supported group-value function. In the next pass, the results of the calculation are inserted back into the temporary database structures. The last SQL pass is the same as Report 2A, since it is used to display the result for all regions.