Solve Order in Member Formulas
SolveOrder is a metadata property that can be set either for a dimension or a member that defines the order of member evaluation. The solve order is applied during query execution.
SolveOrder can impact the performance of the query. The value of the SolveOrder property determines the priority with which the member formula is calculated. The formula on the members that have a specified Solve Order are calculated in order from the lowest Solve Order to the highest. When a member formula is dependent on the value of another Dynamic Calc member, the member with the formula must have a higher Solve Order than the Dynamic Calc member on which it depends.
Table 11-15 Default Solve Order Settings
| Dimension Type | Default SolveOrder Value |
|---|---|
| Stored member | 0 |
| Sparse dimension | 10 |
| Dense dimension - Account | 30 |
| Dense dimension - Time | 40 |
| Dense dimension – Two Pass Account | 60 |
| Dense dimension – Two Pass Time | 70 |
| Two Pass | 100 |
| Attribute dimension | 90 |
If a sparse member formula references a dense member with ‘DynamicCalc’ data storage, the reference is ignored because by default, sparse dimensions are calculated first (Sparse default solveOrder – 10, Dense Account default SolveOrder - 30). This behavior can be changed by assigning a custom Solve Order to the sparse dimension that is higher than the dense dimension’s Solve Order.
Example:
Consider the following example with members of different SolveOrders and corresponding valuation.
Dimension1:
-
Accounts
-
A1
-
A2
-
Ratio – Member Formula [A1 / A2]
Dimension2:
-
DataSource
-
DataInput
-
CustomInput
-
Variance – Member Formula [DataInput - CustomInput]
Consider the following dataset for the intersections for the January period:
Calculations with different SolveOrders for Ratio and Variance
Case 1: Ratio with a higher SolveOrder than Variance
In this case, Variance will be calculated first, and then corresponding Ratio.
Ratio of Variance will be calculated as (Variance->A1)/(Variance->A2).
Case 2: Ratio with a lower SolveOrder than Variance
In this case, Ratio will be calculated first, and then corresponding Variance.
Variance of Ratio will be calculated as (Ratio->DataInput) – (Ratio->CustomInput).
