SYMPTOM:
A report with a consolidation returns no data when it is executed through Strategy. However, the SQL the report generates does return data when executed through the DB Query Tool. Furthermore, if the consolidation is removed from the report and then executed, data is returned correctly.
CAUSE:
One possible cause for this issue is the presence of an inconsistency between the definition of the consolidation in the metadata and the data in the warehouse database. Specifically, if in the warehouse database the data type and precision of the column from where data is queried to build the consolidation does not match the data type and precision defined for the attribute form upon which the consolidation is based, the Analytical Engine will not be able to process the result set, which will cause the report to return no data.
An example of this would be a an attribute form which is created as a 4-byte string and then used to build a consolidation. If later, the column in the table is changed to a 10-byte string, and the data type of the attribute form is updated accordingly, there will be an inconsistency between the definition of the consolidation and the data returned by the attribute form for the consolidation to process. The attribute form will return data with the following format: "1234 ", with 6 spaces at the end. Meanwhile, the Analytical Engine, according to the current definition of the consolidation, will expect data with the following format: "1234".
ACTION:
Recreating the consolidation from scratch should resolve this issue. The MSIFileTable can also be used to determine if there is an inconsistency between the definition of the consolidation and the data returned by the Query Engine.
