KB45769: Derived element with calculation type degrades execution performance in a cube report with large data and many derived metrics in MicroStrategy OLAP Services 9.3.0

SYMPTOM:
 
Derived element with calculation type degrades execution performance in a cube report with large data and many derived metrics in Strategy OLAP Services 9.3.0.
 
The following steps demonstrates how to reproduce the issue in Strategy Tutorial project:
 

1. Create an intelligent cube with Category, SubCategory, Item, Warranty, Call Center, Customer, Order, Year, Month, Day in rows and Cost, Revenue and Units Sold in columns as shown below:

 
Publish the cube and it returns 838787 rows.
 
 

• Create derived element with 3 elements with calculation type and 6 elements with list type based on Subcategory as shown below:

 
Units Sold (Level)=Sum() {Subcategory , Customer , , Month }
MaxDay=Max(Day@ID) {~ }
 
MaxDay2=SubStr(, 6.0, 2.0)
 
Rank_Renenue=Rank<ASC=False, BreakBy={, Month, Customer, Subcategory}>(Revenue)
 
Rank_Units Sold (Level)=Rank<ASC=False, BreakBy={, Month, Customer}>()
 
 

• Create cube report R1 based on this intelligent cube and add Subcategory, Day, Item and Call Center to page-by. Add Order and Warranty to rows and metrics to columns. Create 5 derived metrics as shown below:

 
 
 
 
CAUSE:
 
This is a known issue in Strategy OLAP Services 9.3.0.
 
ACTION:
 
Please contact Strategy Technical Support for the latest update on this issue.
 
WORKAROUND:
 
The calculation type shows worse performance than list type. If the calculation element is the sum of some attribute elements, replace it with list element. In the example above, change the 3 calculation elements to list elements will make the execution time of R2 decreased to about 37 seconds as shown below:
 

• Copy R1 to new report and rename it as R2. In R2, link Subcategory to the derived element object.
• Run R1 and R2. The execution time of R1 is about 8 seconds while R2 is about 80 seconds.