KB31031: "Generate SQL for fact allocation failed" error message appears when executing a report in MicroStrategy Developer 9.4.x and 10.x while using a fact using the CASE function as part of its definition.

Salesforce Solutions Architect • Strategy

"Generate SQL for fact allocation failed" error message appears when executing a report in MicroStrategy Developer 9.4.x and 10.x while using a fact using the CASE function as part of its definition.

SYMPTOM:
A report fails to generate SQL or execute in Strategy Developer with the following error:


Status: Initialization Failed
Error: SQLEngine got an Exception from DFC: Engine Logic: Generate SQL for fact allocation failed, please check if the allocation expression is valid.

CAUSE:
A CASE function inside the fact definition cannot be translated into appropriate SQL. The definition of the function was validated initially, however if the fact is edited and the expression is re-validated it will be evaluated as an invalid expression.

Initial Fact Creation evaluates the expression correctly:

Re-editing the fact and hitting the validate butting once again will evaluate the expression as invalid now.

ACTION:
Use an ApplySimple expression. The definition of the fact will have to follow the appropriate native CASE statement depending on the database type.

Comment

0 comments

Details

Knowledge Article

Published:

April 18, 2017

Last Updated:

April 18, 2017