When generating SQL for a report, metric dimensionality must be evaluated and transformed into a state that is valid for use. Metric dimensionality as it is defined in a metric is insufficient to actually generate SQL due to references such as hierarchies, report level and object prompts. These are not objects that can be directly turned into column references for use in a select/group by clause in SQL. Furthermore the engine has to reconcile the differences between the report design and the metric's level. This is to handle situations where level/dimensionality settings would have a metric calculate at a level lower than template level which would cause data integrity issues.
There are two main steps in this process. The first step is the replacement of all non-attributes in the dimensionality with attributes. The second step is applying the level settings per the report design to produce a valid computation unit to turn into SQL. This is all written out in the logic below.
Step 1: Replacement of all non-attributes in level dimensionality with attributes.
A. First all object prompts in the level dimensionality are asked to the end user, these may be answered with attributes or hierarchies.
B. Next all hierarchies are replaced by attributes. A hierarchy can only be replaced by an attribute that is explicitly contained in the hierarchy itself.
1. Hierarchies look first to the full set of template objects (all attributes in report objects window) to choose a replacement.
2. If no attribute is on the template the hierarchy next looks to the attributes used in the filter to find a replacement.
3. If no attribute from the hierarchy is in either place above then the hierarchy is discarded and removed from the level/dimensionality.
4. Side note is that multiple attributes can be used to replace a hierarchy but only from the section being used. If we find an attribute/s from the template the filter will not be examined.
C. Finally report level is replaced by attributes that are in the template or filter that are not covered by the existing attributes in level/dimensionality. Relationships are used here but only to compare against the attributes that are currently in the metric dimensionality. For example if a time hierarchy was replaced by quarter from the steps above then month wouldn't be used to replace report level. Again in this step multiple attributes can be used to replace report level.
At no point from step 1 for handling hierarchies are attribute relationships acknowledged except in one specific situation. If you have multiple attributes from the hierarchy on a report and they have relationships then only the lower level of the two attributes will be used to replace the hierarchy.
Step 2: Application of attribute level/dimensionality to produce a metric calculation pass.
A. Take the lowest level attribute(s) per relationships on the template and compare it to the level dimensionality of the metric to determine grouping
1. if the attribute is present directly apply the grouping settings directly
2. if a child of the attribute is present in level/dimensionality it must be replaced by the lowest level related attribute from the report as a report cannot calculate at a level lower then template level (this is not true for inner aggregations inside of compound metrics). If grouping is set to none for the child attribute the parent will still be included for grouping as attributes apply grouping settings to themselves and children.
3. if the parent of the attribute is present in the level/dimensionality then that parent attribute is used per its grouping settings
4. if no attribute related to the template attribute is present then do not group by the template attribute
B. Apply filtering settings by examining all filters (output level of filter if it is a set qualification)
1. if attribute related to filter attribute is present in level/dimensionality then apply filter settings from dimensionality as expected. Parent and child relationships are identical for behavior here.
2. if no attribute related to filter attribute is present then do not apply the filter
This does not cover non-aggregation grouping settings. Those settings are already covered in KB20363: How do non-aggregatable metrics work in MicroStrategy SQL Generation Engine? . Refer to that tech note for how it handles and chooses attributes for grouping.
