Optimize the order of dimensions in a cube

You can change the order of dimensions in a cube in IBM® Planning Analytics Workspace and view the percent change in memory. Changing the order of dimensions can reduce memory consumption and improve performance.

When you optimize the order of dimensions in a cube, Planning Analytics does not change the actual order of dimensions in the cube structure. Instead, it changes the way dimensions are ordered internally on the database. Because the cube structure is not changed, any rules, functions, or applications that reference the cube remain valid.

As you change the order of dimensions, you can instantly view a report detailing the impact that your changes have on cube memory consumption.

For the following reasons, experiment with the order of dimensions in a cube only in a development environment:

  • Significant memory resources are required for the database to reconfigure the order of dimensions in a cube. During the reordering process, the temporary RAM on the database increases by a factor of two for the cube that you are reordering. For example, a 50 MB cube requires 100 MB of RAM to reconfigure.
  • Reordering puts a read lock on the database, locking all user requests while the reorder is performed.

When you have a satisfactory dimension order in your development environment, modify the order of dimensions in your production environment. Remember that there will be an impact on memory consumption and server availability while the reorder is being executed.

About this task

You can change the order of dimensions for cubes on Planning Analytics databases version 2.0.6 or later. If a cube is on a database before version 2.0.6, the Reorder dimensions option is unavailable when you right-click the cube in the Databases tree.

You must be a Planning Analytics administrator or modeler to optimize the order of dimensions in cubes. When you optimize the order of dimensions in a cube, you cannot move a dimension that includes string members from the last position. If a dimension with string members exists in your cube, it must be the last dimension in the ordering.

You can reorder cube dimension from a modeling workbench.

You cannot reorder dimensions in control cubes.

Procedure

  1. In the Databases tree of a modeling workbench, right-click the cube that you want to optimize.
  2. Click Reorder dimensions.

    The Reorder dimensions page opens. This screen shows the original order of dimensions, the current ordering of dimensions on the database, and a list of cube dimensions that you can manipulate to set a new order.

  3. Select a dimension in the New order list.
  4. Click the up Up arrow button or down Down arrow button arrow to change the order of the dimension in the cube.
  5. Click Apply.

    When you click Apply, the New order is applied to the cube and the Current order list updates. Note that after you click Apply, the Apply and OK options are disabled. This is because clicking Apply results in the Current order being set to the specified New order; there is no difference between current and new so there is no action to apply

    Review the message that indicates the change in memory consumption to determine if the new order is more efficient.

    Dimension reorder screen showing improved memory consumption
  6. Repeat steps 3 - 5 until you achieve the most efficient ordering of dimensions.

Optimize the order of dimensions without reviewing the change in memory consumption

If you already know the order of dimensions that you want to use and don't need to review the impact of different dimension orderings, you can set the order in the New order list and then click OK.

About this task

If you use this option, you don't see a message indicating the change in memory consumption on the Reorder dimensions page. Instead, notifications on the modeling workbench inform you when the reordering is applied and show the change in memory consumption.