KB419891: How to use Nested Aggregation with an Average (AVG) function in MicroStrategy 89.x-10.x

Decimal ValueBinary Bit MaskProcessors used by Intelligence Server10000000103000000110 and 1 (provided the license is for at least 2 CPUs)7000001110, 1 and 2 (provided the license is for at least 3 CPUs)10000010101 and 3 (provided the license is for at least 2 CPUs)15000011110, 1, 2 and 3 (provided the license is for at least 4 CPUs).........

Error in saving the cube, com.microstrategy.webapi.MSTRWebAPIException:()

Decimal ValueBinary Bit MaskProcessors used by Intelligence Server10000000103000000110 and 1 (provided the license is for at least 2 CPUs)7000001110, 1 and 2 (provided the license is for at least 3 CPUs)10000010101 and 3 (provided the license is for at least 2 CPUs)15000011110, 1, 2 and 3 (provided the license is for at least 4 CPUs).........

Error in saving the cube, com.microstrategy.webapi.MSTRWebAPIException:()

select a12.Call_Ctr_Id Call_Ctr_Id,
    max(a13.CENTER_NAME) CENTER_NAME,
    a11.EMP_ID EMP_ID,
    max(a12.EMP_LAST_NAME) EMP_LAST_NAME,
    max(a12.EMP_FIRST_NAME) EMP_FIRST_NAME,
    sum(a11.ORDER_AMT) SUMOFSALES
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)
group by     a12.Call_Ctr_Id,
    a11.EMP_ID

The Engine generates SQL against the ORDER_FACT table and retrieves the SUM(ORDER_AMT). The Average subtotal is calculated by the Analytical Engine and shows the correct results.
DataSQL

The Engine generates SQL against the ORDER_FACT table and retrieves the SUM(ORDER_AMT). The Average subtotal is calculated by the Analytical Engine and shows the correct results.
DataSQL

The Engine generates SQL against the ORDER_FACT table and retrieves the SUM(ORDER_AMT). The Average subtotal is calculated by the Analytical Engine and shows the correct results.
DataSQL

select a12.Call_Ctr_Id Call_Ctr_Id,
    max(a13.CENTER_NAME) CENTER_NAME,
    avg(a11.ORDER_AMT) AVERAGEOFSAL
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)
group by     a12.Call_Ctr_Id

The Engine generates SQL against the ORDER_FACT table and retrieves the AVG(ORDER_AMT). This is correct but the report will be returning a result that may not be the desired one.

This report returns the average 'transaction' sale for each . Under this context, each 'transaction' is identified by the lowest level in the table ():

DataSQL

DataSQL

select a11.EMP_ID EMP_ID,
    sum(a11.ORDER_AMT) WJXBFS1
into #ZZTF400CF1PMD000
from   ORDER_FACT a11
group by     a11.EMP_ID

select a11.Call_Ctr_Id Call_Ctr_Id,
    avg(pa1.WJXBFS1) AVERAGEOFEMP
into #ZZTF400CF1PMD001
from   #ZZTF400CF1PMD000 pa1
    join LU_EMPLOYEE a11
      on (pa1.EMP_ID = a11.EMP_ID)
group by     a11.Call_Ctr_Id

select distinct pa2.Call_Ctr_Id Call_Ctr_Id,
    a11.CENTER_NAME CENTER_NAME,
    pa2.AVERAGEOFEMP AVERAGEOFEMP
from   #ZZTF400CF1PMD001 pa2
    join LU_CALL_CTR a11
      on (pa2.Call_Ctr_Id = a11.Call_Ctr_Id)

The Engine generates multi-pass SQL against the ORDER_FACT table and first retrieves the SUM(ORDER_AMT) at the level.

Then, in the second pass, it generates SQL against the table created in the fist pas and calculates the average of the sum at the level.

This report returns the average sale for each . Notice that the results are exactly the same as the ones shown on the first example that are calculated by the Analytical Engine.
DataSQL

This report returns the average sale for each . Notice that the results are exactly the same as the ones shown on the first example that are calculated by the Analytical Engine.
DataSQL

This report returns the average sale for each . Notice that the results are exactly the same as the ones shown on the first example that are calculated by the Analytical Engine.
DataSQL

select a11.EMP_ID EMP_ID,
    sum(a11.ORDER_AMT) WJXBFS1
into #ZZTF400CGJ0MD000
from   ORDER_FACT a11
group by     a11.EMP_ID

The fist pass aggregates the ORDER_AMT at the level ( inner metric).

select a11.COUNTRY_ID COUNTRY_ID,
    avg(pa1.WJXBFS1) AVERAGEOFEMP
into #ZZTF400CGJ0MD001
from   #ZZTF400CGJ0MD000 pa1
    join LU_EMPLOYEE a11
      on (pa1.EMP_ID = a11.EMP_ID)
group by     a11.COUNTRY_ID

The second pass calculates the average of the inner pass, but now at the country level.

select a13.Region_Id Region_Id,
    sum(a11.ORDER_AMT) WJXBFS1
into #ZZTF400CGJ0MD002
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)
group by     a13.Region_Id

This pass aggregates the ORDER_AMT, now at the level ( inner metric).

select a11.COUNTRY_ID COUNTRY_ID,
    avg(pa3.WJXBFS1) AVERAGEOFREG
into #ZZTF400CGJ0MD003
from   #ZZTF400CGJ0MD002 pa3
    join LU_REGION a11
      on (pa3.Region_Id = a11.Region_Id)
group by     a11.COUNTRY_ID

This pass calculates the average of the inner pass at the country level.

select distinct pa2.COUNTRY_ID COUNTRY_ID,
    a11.COUNTRY_NAME COUNTRY_NAME,
    pa2.AVERAGEOFEMP AVERAGEOFEMP,
    pa4.AVERAGEOFREG AVERAGEOFREG
from   #ZZTF400CGJ0MD001 pa2
    join #ZZTF400CGJ0MD003 pa4
      on (pa2.COUNTRY_ID = pa4.COUNTRY_ID)
    join LU_COUNTRY a11
      on (pa2.COUNTRY_ID = a11.COUNTRY_ID)

The last pass that joins the intermediate results for the final display.

The Engine generates multi-pass SQL against the ORDER_FACT table and first retrieves the SUM(ORDER_AMT) at the level.

This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'.