Choosing a Partitioning Strategy

Partitioning is an effective way to improve run-time access to cubes and OLAP reports that have millions of rows of source data. There is a tradeoff, however. The more partitions there are, the longer it takes to build the cube.

The partitioning process involves multiple passes through the data. Categories are consolidated and sorted, records in the resulting (temporary) work files are counted, and the information is divided into rows that summarize the measures at one or more optimal partition levels.

By default, Cognos® Transformer automatically partitions your cubes. However, if your cubes are very large or unusually structured, or if you have particular reporting needs, you may want to designate one or more dimensions for manual partitioning, provided these dimensions have sufficient depth. Flat hierarchies do not lend themselves to this optimization method, although you can add manual levels to bring them closer to the ideal parent:child ratio (1 parent to 10 children).

Partitioning is warranted, and will most likely be successful, in the following circumstances:

  • Most of your users' queries can be answered from the first or upper partitions, called summary partitions.

    If you partition a dimension for which lowest-level detail reports are needed, access times will be slower, as the data must be returned from several partitions.

  • The information required for most queries can be found within a single partition.
  • Your dimensions have sufficient depth that the category-per-level ratios are 10:1 or less.

    If a dimensional hierarchy is too flat, you may have to add manual levels to bring it closer to the ideal ratio.

Constraints

In developing your partitioning strategy, consider the following constraints:

  • You cannot use auto-partitioning if Consolidate is disabled on the General tab of the PowerCube property sheet, or if Optimization is set to any method other than Auto-Partition or Default, such as Categories or Direct create.
  • Auto-partitioning is disabled if your model has a custom view with a cloaked primary-drill category. You can run Check Model to receive a warning message about this condition, allowing you to uncloak the drill category or otherwise redesign your model to allow auto-partitioning to proceed.
  • Although auto-partitioning may occasionally partition categories from dimensions that have alternate drill-down paths, you can only use manual partitions on categories in the primary drill-down path.
  • You cannot auto-partition cubes that use externally rolled-up or before-rollup calculated measures. However, you can partition cubes that use calculated columns.
  • You should avoid partitioning dimensions that are frequently updated with new categories, or that contain alternate drill-down structures, leaf-level subdimensions, or special categories.
  • You cannot specify partition numbers for leaf categories, drill categories, or the main root category.
  • You can add new categories to an existing partition level. For example, you can add a new region, which will result in a new partition for that region. However, you cannot add new partition levels to a model if any cube in that model uses incremental update. Instead, you must repartition the model and rebuild the cube, incorporating all the data from all increments.

Partitioning Checklist

When partitioning, confirm the following:

  • Ensure that you set up clearly recognized, business-related groupings in your source data, such as commodity types or customer sales channels, and import them into your model as a new data source column.
  • Because you cannot set different partition sizes for different parts of the cube, consider reorganizing your data so that some cubes can be optimized for high-level summary reports and others for low-level detail reports.
  • Add calculated columns, such as product number ranges or alphabetically sorted customer sets, to regroup your data.
  • On the dimension map, manually create alternate drill downs with manual levels and create categories representing subdivisions, such as geographic regions, and use the category viewer to drag lower-level categories to associate them with the appropriate parent categories.

    If you do not need these manually added categories in every cube, create a dimension view and use the Suppress command to remove the unnecessary levels of detail. However, suppressed categories cannot be partitioned and suppressing the manual levels in a custom view will reduce the effectiveness of your overall partitioning strategy.

    For more information about suppressing categories, see Omit Categories Using Suppress.