KB36904: ApplySimple statement is trying to calculate in the Analytical Engine in MicroStrategy Developer 10.x

SYMPTOM:
 
When placed inside a function that is not supported by the data warehouse, it is possible to have an ApplySimple statement that will try to calculate through the Analytical Engine. This action will result in an error like the message below because the ApplySimple statement must be run against the data warehouse.
 
 
Sample Code/Error
Function not available in DBMS.
 
 
STEPS TO REPRODUCE:
 
The following steps will reproduce the issue in Strategy Tutorial running against a Vertica 4.0 database:
 

1. Create a date value prompt named "End Date"
2. Create a metric "metric1" with the following definition: ApplySimple("Extract(EPOCH FROM DATE #0)", ?)
3. Create a metric "metric2" with the following definition: Min()
4. Create a metric "metric3" with the following definition: Least(metric1,metric2)
5. Place metric3 on the report template and run the report.

The report will generate the following SQL:
 
Sql
select min(a11.ON_ORDER_QTY) AS WJXBFS1
from INVENTORY_CURR a11
 
]
 
WORK AROUND
 
 
Because the Vertica Database does not have a Least function, it must be calculated in the Analytical Engine. To resolve this issue, the ApplySimple statement must be calculated ahead of time in order to prevent an error. To do so, change the definition of the metric made in step 3 to the following:
 
Sql
Sum(ApplySimple("Extract(EPOCH FROM DATE #0)", ?))
 
 
Running this report will produce the following SQL:
 
Sql
select sum(Extract(EPOCH FROM DATE '2011-06-28')) AS WJXBFS1,
min(a11.ON_ORDER_QTY) AS WJXBFS2
from INVENTORY_CURR a11
 
]
 
 
 Note the ApplySimple is now being calculated in the SQL pass and the results are now being placed in the Least function running in the analytical engine.