SYMPTOM:
While using a custom group in a datamart report, Strategy SQL Generation Engine generates SQL as though the datamart resides in a separate instance from the data warehouse even though the datamart resides in the same location as the warehouse in Strategy Intelligence Server 10.x.
CAUSE:
This is functioning as designed. The custom group headers are logical objects which exist only in the metadata and not in the database. In order to move these into the datamart, the results must be assembled in the Analytical Engine in Strategy Intelligence Server 10.x, and therefore never exist as an intermediate table in the warehouse. The only way to write the custom group results into the datamart table is to insert them row by row. Hence, the SQL generated for a datamart report with a custom group is similar to the SQL generated when the datamart resides on a separate database instance.
Refer to the following Strategy Knowledge Base document for more information about how the SQL generated is different if the datamart resided in a different instance from the data warehouse:
KB5395: How to create and use Datamarts in MicroStrategy Intelligence Server.
