Setting the Dimension Sort Order and Dimension Density

You can modify the dimension performance settings for Profitability and Cost Management and Essbase applications. Performance settings include modifying the dimension sort order and dimension density.

  To set the dimension sort order:

  1. In the Dimension Library, right-click the application name and select Dimension Performance Settings.

  2. Under Dimensions(Type), select a dimension, then use the Move up and Move down buttons to set the dimension sort order described in the following sections.

    A dimension sort order must be set for every Profitability and Cost Management dimension in the model, except Alias and UDA dimensions. The dimension sort order should be sequential, unique, and greater than or equal to 1; otherwise, validation of the model will fail. See Table 16 for the recommended dimension sort order settings.

    Table 16. Recommended Profitability and Cost Management Dimension Sort Order Settings

    Dimension Type Setting
    Measure 1 (Default)
    AllocationType 2
    POV and Business Dimensions 3 or higher
    Attribute Dimensions Sort as the last dimensions.

    For example, if you have four attribute dimensions in a sequence of 12 dimensions, the attribute dimensions must be set as 9, 10, 11, and 12.

    In Essbase, dimension sort order determines the order dimensions appear in the outline and, in turn, impacts the order that dimensions are calculated. Calculation order for ASO and BSO databases should be determined in advance, when designing the database. Note that dimension order can affect performance, especially for BSO databases. Calculation of ASO and BSO databases is described in the Oracle Essbase Database Administrator's Guide. However, keep in mind the following:

    • ASO—The dimension solve order is used by ASO databases and should be taken into account when setting the dimension sort order. If no dimension solve order or sort order is set, aggregations are performed in outline order. Attributes are calculated last.

    • BSO—Dimension sort order is based on the calculation order, which is intrinsic in Essbase. Dense dimensions (for example, the time or measures dimensions) are calculated first, sparse dimensions are calculated next, and attribute dimensions are calculated last.

  3. Optional: For Essbase BSO and Profitability and Cost Management, under Density, select the required density for the dimension.

  4. Click OK.