SUMMARY
Global Optimization exists in Strategy so that SQL passes could be combined for efficiency and faster execution performance. If multiple passes use the same base metrics, the calculation passes can be combined for optimal performance. Improvements to Global Optimization have been made in every release of Strategy to refine querying and decrease execution time. This article explains how this logic can be applied to calculations of multiple custom group elements.
DESCRIPTION
Custom group elements work like individual reports with individual report filters. However with SQL global optimization these filters can be treated similar to metric conditions and apply the same logic which permits metrics with different simple filters to be calculated in the same pass of SQL. This optimization is still limited to simple basic filter qualifications. Any custom group element defined with more advanced filter logic will be unable to apply this optimization and will still calculate its elements in separate passes.
The example below illustrates a report where different custom groups can be optimized.
Report template:
SQL View:
If you take a look at the behavior, the SQL is generated optimally for performance by combining both versions of the metric calculation into one pass.
TO LEARN MORE
KB13508 - How does the Global Optimization feature remove unused SQL passes in Strategy SQL Generation Engine?
KB13509 - How does the Global Optimization feature remove duplicate SQL passes in Strategy SQL Generation Engine?
KB13513 - How does the Global Optimization feature merge related SQL passes in Strategy SQL Generation Engine?
KB45719
