SYMPTOM
A report is executed against a multidimensional, data source such as SAP BW, Microsoft Analysis Services or Hyperion Essbase. The report is observed to return less quickly than similar reports in other clients connecting to the same data source.
In the MDX View of the report in Strategy Developer 9.4.x and 10.x, it is observed that the "Total Duration in Query Engine" is significantly less than the total report execution time. Thus query performance in the data source does not appear to be an issue.
CAUSE
One possible cause in this scenario is schema loading time.
Before Strategy can generate any query, whether for ROLAP (SQL) or OLAP Cube (MDX) reports, it must load schema information from the metadata. Depending on the size of the schema, this can take just a few seconds or several minutes.
In three-tier or four-tier connections, schema loading time for SQL reports is usually not perceptible to the end user because the relational schema is loaded when the Strategy Intelligence Server 8.x starts up. The schema is retained in memory and the memory image is used for SQL generation.
By default, MDX Cube schemas are not loaded into memory during Strategy Intelligence Server startup. Schema loading takes place at the beginning of every report execution cycle, and the schema loading time is added to the overall report response time.
Starting with Strategy 8.0.3, OLAP Cube schemas can be marked for preloading, meaning that they will be loaded along with the ROLAP schema during Strategy Intelligence Server 8.x startup. Reports executing against preloaded cubes will generate the MDX query virtually instantaneously, where MDX generation may take up to several minutes otherwise.
ACTION
Consult the following Strategy Knoweldge Base technical note for details on configuring an OLAP Cube data source to preload its schema.
KB15704: New feature in MicroStrategy SQL Generation Engine 8.0.3: Schema preloading for multidimensional OLAP cube (MDX) data sources
KB18026
