SYMPTOM:
The user has a report where the VLDB property of SQL Global Optimization has been set as "Level 4: Level 2 + Merge All Passes with Different WHERE." All the metrics in the report have been specified with outer joins, so theoretically the engine should not generate GODWFLAG in cube SQL and all rows of the intermediate table are preserved. However, the user still sees GODWFLAG in the cube SQL which has infected the performance of engine.
The sample report contains attribute ‘Region’ and metric ‘Filtered Profit’ and ‘Profit’. Both metrics have been set to outer joins.
However, GODWFLAG is still seen in report SQL as highlighted below.
CAUSE:
There is a nested aggregation metric A with a metric condition C, and the component metric B is also on template. When calculating metric A, the condition C will also be applied to the inner component metric B. Thus from Strategy Engine’s perspective, there are two metrics with same formula but different condition: metric A without condition; metric A with condition.
According to the SQL Optimization logic, these SQL passes are combined together – if setting GO to level 0, there are similar SQL passes (half with condition C filtering, half not) . However, in order to correctly calculate the outer join metric A, it needs to use GODWFLAG to distinct the rows. Without GODWFLAG, there is no way to apply the filter while combining the SQL passes.
So the existence of GODWFLAG is expected as metric A and B are not independent from each other though they are all outer join metrics.
To learn more about how the Global Optimization levels 3 and 4 handle inner and outer joins between metrics in Strategy SQL Generation Engine, the below technical note can be used as reference.KB20586: How do Global Optimization levels 3 and 4 handle inner and outer joins between metrics in MicroStrategy SQL Generation Engine?
