SYMPTOM:
An Intelligent Cube publication takes significantly long to complete publication (several hours). The cube is based on a MDX datasource, specifically SAP BW.
By profiling the Intelligent Cube publication (as per KB30699 - How to measure the Intelligent Cube performance parameters for time and memory in the MicroStrategy Intelligence Server 9.x. ) and after monitoring external aspects (such as Network) it's concluded the performance deficit isn't related to Query Execution (on the data warehouse) or the Analytical Engine Processing Time but that the majority of the time is spent in the Data Fetching stage. It also doesn't seem the slowness is coming from excessive shared resources utilization such as Memory, Processor, Disk, etc.
Note: as per the above referenced Technical Note the peak memory usage for the Intelligence Server during cube publication is usually higher than the final size of the published cube. For Intelligent Cubes based on MDX datasources this relative difference can be much bigger due to the multi-dimension structure of MDX cubes. From previous experience we have observed that the peak memory usage can be <100x times the size of the published cube (e.g. in the cube monitor in Strategy Developer a cube might be ~100MB large but the maximum memory it requires to be published might be of the order of ~10GB). This memory usage is expected and shouldn't be an issue on its own as long as there is enough available memory in the Intelligence Server machine.
CAUSE:
One reason for the performance deficit might be that the majority of the time spent in the Data Fetching stage is coming from the SAP BAPI (Business Application Programming Interface) functions Strategy needs to use to retrieve data from SAP BW.
ACTION:
To find out how much time of the Data Fetching is spent on SAP BW side it would be useful to enable logging the JCO trace. Strategy Technical Support would be able to advise if this is the next step in the troubleshooting of a cube publication performance related issue and they would also be able to advice users on the steps to perform this. Once the JCO trace log has been collected while reproducing the issue, users would need to send it to Technical Support in order for the log to be analyzed.
If the performance deficit is caused by the intrinsic performance of the SAP BW BAPI functions used then there is the possibility that there might be little in these functions that is configurable from the Strategy side to improve their performance and the solution should be to look for ways to avoid using the problematic functions.
One example would be the case where a particular cube publication requires fetching over 1 million cells from the SAP BW source. Such volume of data requires use of certain BAPI functions that have a slower performance and can result in excessively long publication times. The solution would be to avoid querying large volumes of data by using the Strategy Incremental Refresh functionality to update (add/delete) data in the cube instead of re-publishing it every time. More information can be found in KB45837 - How does the Incremental Refresh option work in MicroStrategy Analytics Enterprise 9.4.x?
The idea is that, if users don't do too many modifications to the "old" data in the data source but generally rather add new records they can use Incremental Refresh to add data to a "base" Intelligent Cube. In this way, only a small query is used each time to refresh the cube. The user would need to find a suitable attribute in their cube by which it makes sense to logically divide the cube in smaller versions of itself. For instance if the cube has a time based attribute like Year and the total cube needs to contain information for the last 6 years then the base cube could contain information for year #1 and then users can use 5 Incremental Refresh reports (one per year) to insert the delta data to the base cube.
In most scenarios Incremental Refresh would work well and even improve the cube publication time. And later, since only new data need to be added to the Intelligent Cube users can reduce the overall time by only insert the most recent data. From in-house testing results the total execution time has sometimes been reduced by as much as 60% but this will vary on each scenario and other factors like the number of dimensions in the SAP BW datasource and the complexity of the Intelligent Cube in question could also have an important effect.
Third Party Software Installation:
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.
The Strategy Internal Reference number for the issue discussed in this technical note is 850928
