Stacked Dimensions

Stacked Dimensions Overview

Until now, we have been working with simple examples that have only one dimension in the rows and one in the columns. Sometimes you’ll have more complex analyses that will require you to stack multiple dimensions in the rows and/or columns. For example:

  • What were my revenue and transfers, comp and non-comp expenses for last year, and what are our plans for this year and next year?
  • What are my non-comp expenses by account, fund, and Chart1 for the DeptIDs in my department for each month of my current year forecast?
  • How does the revenue I planned in my initial forecast compare to the actual revenues received by year and month over the last 3 years? 

Procedure: Creating Stacked Dimensions in the Grid

We’ll use this question as an example to illustrate the procedure: What were my revenue and transfers, comp and non-comp expenses for last year, and what are our plans for this year and next year? 

  1. Identify the dimensions for the rows. Place the first one in column A. Insert columns as needed to place all dimensions to the left of the first column dimension.  [Account]
  2. Identify the dimensions for the columns. Place the first one in row 2. Insert rows as needed to place all dimensions below row 1 and above the first row of data.  [Year, Scenario, Version]
  3. Identify the dimensions that will be constant in all POVs. These are the dimensions that are not in the rows or columns. [Entity, Year, Period, Time_Series, Chart1, Chart2, Program_Code]
  4. All 11 dimensions in the CalRptg cube must be either in the grid or the POV. If any dimension is not in the grid or the POV, be sure to add it
  5. Select Refresh

Example of using stacked dimensions in a report

Guidelines for Creating Stacked Dimensions

To avoid errors, follow these guidelines:

  • Use one row (or column) for each dimension that varies
  • Members of a dimension must all be on the same row or in the same column; you cannot mix and match

Arranging Dimensions in the Grid

There are several ways to arrange dimensions in the grid:

  • Insert rows/columns, cut and paste
  • Drag and drop
  • Pivot

The simplest and least confusing way to arrange dimensions is to do it when you have only one member selected. Once you have positioned dimensions in the rows and columns as desired, then zoom in or open the Member Selection dialog box to add members.

Drag & Drop

Using the right mouse button, you can move a dimension from the POV to the grid, from the grid to the POV, or from one place in the grid to another. Be sure to arrange dimensions before zooming in; when you arrange dimensions, there should be only one member selected.

  • Rows: drag to the left or right and drop
  • Columns: drag above or below and drop

Pivot

The Pivot button allows you to swap dimensions between rows and columns. As there must always be at least one dimension in the rows and one dimension in the columns, this operation is only possible in cases where there are at least two dimensions in the rows or columns. While you can pivot with multiple members in a dimension, it is easier to see the results of your pivot operation with a single member.  

In addition to moving a dimension between rows and columns, the drop-down menu on this button allows you to move the dimension between the grid and POV.

Essbase ribbon with Pivot button highlighted

Summary

Smart View requires at least one dimension in the row display area and at least one dimension in the column display area at all times. There are 4 ways to pivot dimensions:

  1. Cut / Paste dimensions using Excel commands
  2. Select a dimension and then select the Pivot button
  3. Right-click a dimension, drag it to the desired location, and drop it
  4. Drag a dimension from the floating POV to the grid or from the grid to the floating POV 

  Practice - Exercise 5

Complete Exercise 5 to practice before continuing.