This function extracts a dimension from an existing pivot table, making it either structural or parametric. To construct a cube from a pivot table you must explicitly define all table fields as dimensions through PsiPivotDim() first.
To create a Parametric Dimension,
- Analytic Solver Desktop: click Model – Dimension to open the Dimension menu.
- Analytic Solver Cloud: click Model - Dimensional Modeling - Dimension to open the Dimension menu.
Select From Pivot Table. The following dialog appears.
Pivot Tables: Click the down arrow to select the desired Pivot Table from the drop-down menu.
Dimension: Click the down arrow to select the desired Pivot Table field. A dimension will be created from the selected field.
Type: Select Structural to create a Structural Dimension or Parametric to create a Parametric Dimension.
Location: Select a blank cell on the spreadsheet where the dimension will reside.
Function Signatures
There is one signature provided for this function.
=PsiPivotDim(cell_within_pivot, dim_name, [dim_type],…)
cell_within_pivot: (required) A cell reference within the range of a pivot table definition.
dim_name:(required) A string containing the name of the extracted pivot table field as a dimension. This argument is used by Analytic Solver Platform to create a dimension with that name.
dim_type: (optional) Pass False for this argument if creating a Structural Dimension or True if creating a Parametric Dimension. If this argument is omitted, a Structural Dimension will be created.
If a Parametric Dimension is created, two additional arguments can be used, PsiDimLock() and/or PsiDimActive(). See below for explanation of these two functions.
Examples
=PsiPivotDim($A$1, "Product", TRUE) - Results in a Parametric dimension created from the “Products” field from within the Pivot Table contained in cell A1.
=PsiPivotDim($A$7, "Product", FALSE) or =PsiPivotDim($A$7, "Product") – Results in a Structural dimension created from the “Product” field from the Pivot Table located in cell A7.
=PsiPivotDim($A$7, "Product", TRUE, PsiDimLock("dyn tool")) – Results in a Parametric dimension created from the “Product” field from the Pivot Table located in cell A7 locked to the dyn tool dimension element. (For more information on PsiDimLock, see below.)
=PsiPivotDim($A$7, "Product", TRUE, PsiDimLock("dyn tool"), PsiDimActive(FALSE, TRUE, TRUE)) – Results in a Parametric dimension created from the “Product” field from the Pivot Table located in cell A7 locked to the dyn tool dimension element and active in simulation and sensitivity analysis. This dimension will not be active in optimization. (For more information on PsiDimLock() or PsiDimActive(), see below.)
After a Parametric dimension is created, the name and address of the dimension will appear under Dimensions in the Solver Task Pane.
Formula: Displays the formula in the Address cell (read – only).
Name: Displays the name of the parametric dimension (read – only).
Current Value: Controls the values displayed in the cell containing the cube.
Locked: Setting Locked to True, will result in the dimension being “locked” or “frozen” to the Current Value element.
Type: Displays the type of dimension, Structural or Parametric.
Active in Optimization: If False, dimension will be ignored in the next optimization.
Active in Simulation: If False, dimension will be ignored in the next simulation.
Active in Sensitivity Analysis: If False, dimension will be ignored in the next sensitivity analysis.