PsiReduce() eliminates one structural dimension in a multi-dimensional cube by aggregating the values along that dimension. (PsiReduce() does not support parametric dimensions.) If used with a parametric dimension, the error “Invalid use of Dimension/Cube Reduction” will appear on the Solver status bar and Output tab on the Task Pane.
To create a Cube Reduction, click
- Analytic Solver Desktop: Model – Reduction.
- Analytic Solver Cloud: Model – Dimensional Modeling -- Reduction.
(A cube must exist on the worksheet.)
The following dialog appears.
Cube: Select the desired cube from the drop down menu. If multiplying, dividing, subtracting, etc. multiple cubes, simply type the operation, for example: K16*L16, A1 * B1 – 10, etc.
Dimension(s): Click the down arrow to select the desired Dimension. If multiple dimensions are to be included in the Cube, click the “+” button, then select the desired Dimension from the next Dimension(s) field.
Reduce By: Select the how you would like to aggregate the cube. The supported functions are: average, sum, max, min, stdev (or stdev.s), var (or var.s), index, and element.
If “index” is selected, PsiReduce() will reduce the expression in the first argument, which evaluates to a cube, by considering only the element index of the reduction dimension selected in the Dimension drop down menu.
If “element” is selected, PsiReduce() will reduce the expression in the first argument, which evaluates to a cube, by considering only the element specified in the reduction dimension selected in the Dimension drop down menu.
Location: Select a blank cell on the spreadsheet where the dimension will reside.
Function Signatures
One signature is provided for this function.
The function reduces a cube along a given (structural) dimension or along all dimensions by aggregating all relevant elements as requested.
=PsiReduce(cube_expression, aggregation, [dimension])
cube_expression: (required) An existing cube on the spreadsheet. The function reduces the cube to a cube or degenerated cube (single value) by eliminating one or all dimensions through aggregation.
aggregation: (required) A string specifying how to aggregate the cube along a specific dimension or along all dimensions. Supported values are: “average”, “sum”, “max”, “min”, “stdev”, “stdev.s”, “var”, “var.s”. (The second moment aggregations (stdev.s and var.s) are always computed as sample-based. The name “stdev.s” is equivalent to “stdev”; the name “var.s” is equivalent to “var”.)
If the name of a dimension’s element is passed for aggregation, the function will be reduced/sliced at that named element. If an index n is passed for aggregation, then the cube will be reduced/sliced along the nth element of the dimension.
Dimension: (optional) The name of the dimension along which the reduction by aggregation of the cube is requested. If omitted, the cube will be aggregated along all structural dimensions.
Examples
=PsiReduce(A1,”sum”, "state") - Results in the sum of the values in the A1 cube along the state dimension.
=PsiReduce(A2, “average”) – Results in the average of all values in the fact table for the cube located in cell A2.
=PsiReduce(A3, “San Francisco”, “City”) – Reduces the cube in cell A3 by considering only the San Francisco element of the reduction dimension City.
=PsiReduce(A4, 2, “City”) – Reduces the cube in cell A4 by considering only the 2nd element of the reduction dimension City.
The result of PsiReduce() is another cube. If the cell containing =PsiReduce() is selected in the Model tab of the Solver task pane, the name and address of the dimension will appear under Cubes in the Solver Task Pane.
Address: Displays the cell address where the PsiReduce() occurs (read – only).
Formula: Displays the formula in the Address cell (read – only).
Values: Displays the cube being reduced or the first argument of PsiReduce().