PsiCube() creates a multi-dimensional array which holds the dimensional data in the form of a data range. This data range is structured along the dimension elements of the included set of dimensions. The maximum number of elements in a cube created by PsiCube or by formula evaluation is 1,000,000.
To create a Cube, click Model – Cubes to open the Cube menu.
If using an Excel Pivot Table, select From Pivot Table, otherwise select From Cell Range. (For more information on creating a Cube from within an Excel Pivot Table, please see below.)
If From Cell Range is selected, the following dialog appears.
Range: Enter an Excel range that contains a fact table or data table.
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.
Take care to enter the dimensions in the correct order. In our Structural example above, we have the following table.
When entering the dimensions, the first dimension should be the “most rapidly changing” dimension. Starting from cell B2 (the start of the fact table) and reading from left to right, the 1st element of the cube will be 1 (Chassis & LCD TV), the 2nd element of the cube will be 1 (Chassis & Stereo), the 3rd element of the cube will be 0 (Chassis & Speakers). The 4th element of the cube will be 1 (Screen & LCD TV), the 5th element of the cube will be 0 (Screen & Stereo) and the 6th element of the cube will be 0 (Screen & Speakers). Between Products and Parts, which dimension is changing more frequently as we read from left to right? Correct! Products is changing more frequently which means this dimension should come first in the PsiCube() formula.
Location: Select a blank cell on the spreadsheet where the dimension will reside.
Function Signatures
The signature provided for this function is:
=PsiCube(range_with_values, dim1, [dim2], [dim3], …)
This function creates a cube along the listed dimensions in the data range, range_with_values. Dim1 is the most rapidly changing dimension in the listed dimension set when interpreting the data range. (Dimensions should be entered in order from most rapidly changing to least rapidly changing.)
range_with_values: The data range is simply a range of cells on the Excel worksheet containing data relevant to the problem, i.e. the number of parts needed to manufacture a product.
dim1, [dim2], [dim3],….: At least one dimension is required (dim1) and up to 7 more optional dimensions ([dim2], [dim3], …) are used in cube creation. The product of dimension lengths must be equal to the number of values in range_with_values (the data range). Dimensions can be all structural, all parametric, or mixed.
Examples
=PsiCube($C$14:$E$14, "Products") where cells C14:E14 contain data for the Products dimension.
=PsiCube(C18:E22, "Products", "Parts") where cells C18:E22 contain data for both the Products and Parts dimension.
After a Cube is created, the name and address of the dimension will appear under Dimensions in the Solver Task Pane.
Address: Displays the cell address where the Structural dimension resides (read – only).
Formula: Displays the formula in the Address cell (read – only).
Name: Displays the name of the Structural dimension (read – only).
Dimensions: Displays the Dimensions included in the Cube.