PsiOptData() can be used to display all elements in a cube calculating either the objective or constraints in an optimization model.
- In Analytic Solver Desktop, the difference between PsiCubeData() and PsiOptData() is that PsiOptData() will only be populated after an optimization is run, while PsiCubeData can be populated at any time.
- In Analytic Solver Cloud, there is no difference in returned results between PsiCubeData() and PsiOptData().
To insert the PsiOptData() formula, click Formulas – Insert Function on the Excel Ribbon, select Psi Dimension from the Or select a category dropdown menu, select PsiOptData from the list, then click OK.
The following dialog opens.
Function_cell: Enter an existing cube used to calculate either the objective or the constraints in an optimization modelOptimization: This argument is optional. This argument specifies the optimization number to which the function will be applied. If omitted, the optimization selected in the Ribbon will be used.
Struc_format: An optional argument entered as a string. If omitted, all cube values are printed in a single vector.
To use this argument to return the value of a specific element in a 1-dimensional cube containing a structural dimension, use the form: “[StructuralDimisension1].[Element1]”.
To use this argument to return the value of a 2-dimensional cube (containing structural dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element]”.
To use this argument to return the value of an N-dimensional cube (containing structural dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element],…,[StructuralDimensionN].[Element]”.
Param_slice: The param_slice argument is an optional string argument specifying the desired element “slice” for the parametric dimensions. If omitted the element selected for the Dimension’s Current Value in the Solver Task Pane will be used.
To use this argument to return the value of a specific element in a cube with 1-dimensional cube containing a parametrc dimension, use the form: “[ParametricDimisension1].[Element1]”.
To use this argument to return the value of a 2-dimensional cube (containing parametrc dimensions), use the form: “[ParametricDimension1].[Element],[ParametricDimension2].[Element]”.
To use this argument to return the value of an N-dimensional cube (containing parametric dimensions), use the form: “[ParametricDimension1].[Element],[ParametricDimension2].[Element],…,[ParametricDimensionN].[Element]”.
Function Signatures
When a cube evaluation results in a given optimization output_cell, (e.g. a constraint or objective definition cell) is a cube, Excel may display at most one element of that cube. PsiOptData will "spill" all contents or selected elements of the cube result formatted according to the optional struc_format string.
There is one signature provided for this function.
=PsiOptData(output_cell, [optimization], [struc_format], [param_slice])
output_cell: (required) Enter an existing cube used to calculate either the objective or the constraints in an optimization model.
Optimization: (optional) An optional argument specifying the current optimization related to PsiOptParam() functions. If omitted the one selected in the Ribbon will be assumed.
Struc_format: An optional argument entered as a string. If omitted, all cube values are printed in a single vector.
To use this argument to return the value of a specific element in a 1-dimensional cube containing a structural dimension, use the form: “[StructuralDimisension1].[Element1]”.
To use this argument to return the value of a 2-dimensional cube (containing structural dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element]”.
To use this argument to return the value of an N-dimensional cube (containing structural dimensions), use the form: “[StructuralDimension1].[Element],[StructuralDimension2].[Element],…,[StructuralDimensionN].[Element]”.
Param_slice: The param_slice argument is an optional string argument specifying the desired element “slice” for the parametric dimensions. If omitted the element selected for the Dimension’s Current Value in the Solver Task Pane will be used.
To use this argument to return the value of a specific element in a cube with 1-dimensional cube containing a parametrc dimension, use the form: “[ParametricDimisension1].[Element1]”.
To use this argument to return the value of a 2-dimensional cube (containing parametrc dimensions), use the form: “[ParametricDimension1].[Element],[ParametricDimension2].[Element]”.
To use this argument to return the value of an N-dimensional cube (containing parametric dimensions), use the form: “[ParametricDimension1].[Element],[ParametricDimension2].[Element],…,[ParametricDimensionN].[Element]”.
Examples
=PsiOptData(A1, 1, ”dims”) – Returns the dimension name and number of elements will be returned. This formula spills across n coluns where n is the number of dimensions in cell A1.
=PsiOptData(A1, 2, “vals”) – Returns the values of the fact table for the 2nd optimization along with the dimension elements in the form of a relational or pivot table. (Optimizations to run must be set to 2 or larger.)
=PsiOptData(A1, 4, ”[Parts].[Chassis], [Products].[TV]”) - Returns the cube elements as well as the element value for the 4th optimization. (Optimizations to run must be set to 4 or larger.)
Cells containing PsiOptData() will appear under Results in the Model tab of the Solver task pane.
Address: Displays the cell address range where PsiOptData is located (read – only).
Formula: Displays the cell formula located in the Address range (read – only).