SYMPTOM:
One of the following errors appears when attempting to execute a report containing a metric using a passthrough function that references multiple Facts in different tables:
Error: SQLEngine got an Exception from DFC: Engine Logic: Fact does not exist at a level that can support the requested analysis. Fact: "Units Received, Profit". Level: "Month"
SQLEngine got an Exception from DFC: AppObj:
AppObj Logic:
ERROR: One aggregation cannot have two facts that come from different tables. DFCAggMetricLeaf::DecomposeSplit()
CAUSE:
This issue may occur when attempting to use Facts or Attributes from different tables. The Strategy Intelligence Server must be able to guarantee that the facts used in the passthrough function exist at the same level. Therefore, all the facts must come from the same table.
In some cases, it is possible to generate SQL for a metric expression using facts from different tables. Each fact must be mapped to a table sharing a common key level with the other facts in the formula. For tables to share a common key level, their key attributes must be exactly the same and these key attributes must represent the true unique key in the warehouse (that is, the checkbox "The key specified is the true key for the warehouse table" must be selected in the logical table editor).
For example, an ApplyAgg metric using the Profit and Units Received facts can generate SQL in Strategy Tutorial. Units Received exists only at the level of {Item, Month}. Profit exists in the table ITEM_MNTH_SLS, at the same level, among others. Therefore, the Strategy SQL Generation Engine can find a common key level for both facts. However, if the Profit fact is edited to remove the mapping to ITEM_MNTH_SLS, then there is no common key level and the "Fact does not exist..." error will be returned.
Note also that the "Fact does not exist..." error may occur for multi-fact metrics that do not use Apply functions.
WORKAROUND:
Some possible workarounds to this issue follow:
Workaround 1:
Simplify the function to only use Facts from one table.
Workaround 2:
Create a database view, or Strategy Logical View, for one or more facts to ensure that they exist at the necessary common key level.
For details on Logical Views, consult the Strategy Project Design Guide manual, Appendix B, "Logical Tables."
If a database view is preferred, the view must be imported into the project using the Warehouse Catalog. Following the import, the new table(s) may be used as source table(s) for their facts.
A schema update is required after adding new table mappings to schema objects.
Workaround 3:
Create a database view, or Strategy Logical View, that combines the different fact tables together and reference it as a source table in the Facts.
Depending on the metric formula, this approach may or may not produce correct results. Whenever tables are joined at different levels, it means that some rows from the higher-level table will be repeated across multiple rows of the lower-level table. Performing aggregation on columns from the higher-level table could result in multiple counting and inflated metric results. Effectively, this workaround bypasses the Strategy SQL Generation Engine's safety checks to prevent fact tables at different levels from being joined prior to aggregation. Strategy cannot ensure a correct calculation if this is done carelessly.
