PsiCubeData() can be used to display all elements in a cube’s data range or display only a portion or “slice” of the data.  (While PsiOptData() should only be used when solving an optimization model, PsiCubeData can be used when creating an “what if” analysis without the existence of an optimization or simulation model.)

Only the first element of a cube will be displayed in an Excel Cell.  Double click the cell to display a pop up window containing the full contents of the cube.  Alternatively, the function PsiCubeData() can be used to display all cube elements or a “slice” or portion of the cube elements in the spreadsheet formatted according to the optional struc_format argument.  PsiCubeData can exist outside of an optimization or simulation model.  When parametric dimensions are present in the model, this function will calculate cubes along structural dimensions only for the current selections of parametric dimension elements as selected in the Model tab of the Solver task pane.  To calculate the function, click Model – Cube Output – Calculate.

To insert the PsiCubeData() formula, click Formulas – Insert Function on the Excel Ribbon, select Psi Dimension from the Or select a category dropdown menu, select PsiCubeData from the list, then click OK. 

The following Function Arguments dialog opens.

Function_cell:  Enter an existing cube here.  (PsiCubeData() can be used with any cube.) 

Struc_format:  An optional argument entered as a string. If omitted, all cube values are printed in a single vector. 

  • If struc_format = “dims”, PsiCubeData() prints all dimensions in the cube with their lengths. 
  • If struc_format = “vals”, to print all cube elements in the form of a relational or pivot table.  In addition, this argument can also be used to selectively print only a portion of a cube’s elements or a “slice” of the data table.  (See below for an example.) 

Function Signatures

The signature provided for this function is: 

=PsiCubeData(output_cell, [struc_format])

output_cell (required):  An existing cube on the spreadsheet. (PsiCubeData() can be used with any cube.) 

struc_format (optional): An optional argument entered as a string. If omitted, all cube values are printed in a single vector. 

  • If struc_format = “dims”, PsiCubeData() prints all dimensions in the cube with their lengths. 
  • If struc_format = “vals”, to print all cube elements in the form of a relational or pivot table.  In addition, this argument can also be used to selectively print only a portion of a cube’s elements or a “slice” of the data table.  (See below for an example.) 

Examples

=PsiCubeData(A1, ”dims”) – Returns the dimension name and number of elements. 

=PsiCubeData(A1, “vals”) – Returns the values of the fact table along with the dimension elements in the form of a relational or pivot table. 

=PsiCubeData(A1, ”[Parts].[Chassis], [Products].[TV]”)  - Returns only the slice of the data range pertaining to the number of chassis used when manufacturing a TV.

Cells containing PsiCubeData() will appear under Results in the Model tab of the Solver task pane.

Model tab on the Solver Task Pane 

Address:  Displays the cell address range where PsiCubeData is located (read – only).

Formula:  Displays the cell formula located in the Address range (read – only).