Partition Design Requirements

Benefits of Partitioning

Partitioning can provide the following benefits:

  • For block storage databases, data synchronization across multiple databases

    Essbase tracks changes made to data values in a partition and provides tools for updating the data values in related partitions.

  • Ability for user navigation between databases with differing dimensionality

    When users drill across to the new database, they can drill down to more-detailed data.

Partitioning Strategies

Based on user requirements, select a partitioning strategy:

  • Partition applications from the top down.

    Use top-down partitioning to split a database onto multiple processors, servers, or computers, which can improve the scalability, reliability, and performance of databases. To achieve the best results with top-down partitioning, create a separate application for each partitioned database.

  • Partition applications from the bottom up.

    Use bottom-up partitioning to manage data flow between multiple related databases, which can improve the quality and accessibility of the data in databases.

  • Partition databases according to attribute values associated with base dimensions (a standard dimension associated with one or more attribute dimensions).

    Use this strategy to extract data based on the characteristics of a dimension, such as flavor or size.


    You cannot partition attribute dimensions. See Attributes in Partitions.

Guidelines for Partitioning a Database

Use the following information to help you determine whether to partition a database.

  • Partition a database when:

    • The data should be closer to the people who are using it.

    • A single failure would be catastrophic.

    • It takes too long to perform calculations after new data is loaded, and you want to improve performance by spreading calculations across multiple processors or computers.

    • Users want to see the data in different application contexts, and you want to control how users navigate between databases.

    • You need to synchronize information from different sources.

    • You plan to add new organizational units that would benefit from having their own databases.

    • Users must wait as other users access the database.

    • You want to save disk space by giving users access to data stored in a remote location.

    • You want to reduce network traffic by replicating data in several locations.

    • You need to control database outlines from a central location.

    • You need client write-back functionality on an aggregate storage database.

  • Do not partition a database when:

    • You have disk space, network bandwidth, and administrative resource concerns.

    • You perform complex allocations where unit level values are derived from total values.

    • You are required to keep all databases online at all times.

      Keeping databases online can be a problem if you have databases in several time zones, because peak user load may differ between time zones. Using transparent partitions exacerbates this problem, but using replicated partitions might help.

    • Databases are in different languages or Unicode-related modes.

      Essbase can partition databases only if each database uses the same language, or each database uses the same Unicode or non-Unicode mode.

Guidelines for Partitioning Data

When designing a partitioned database, use the following information to help you determine which data to include in each partition:

  • Which database should be the data source and which the data target? The database that “owns” the data, where the data is updated and where most of the detail data is stored, should be the data source.

  • Are some parts of the database accessed more frequently than others?

  • What data can you share among sites?

  • How granular must the data be at each location?

  • How frequently is the data accessed, updated, or calculated?

  • What are the available resources: disk space, CPUs, and network resources?

  • How much data must be transferred over the network? How long does it take?

  • Is the data stored in one or multiple locations?

  • Is the data accessed in one or multiple locations?

  • Is there information in separate databases that should be accessed from a central location? How closely are groups of data related?

Security for Partitioned Databases

Users accessing partitions may need to view data stored in multiple databases. Set up security so that users do not view or change inappropriate data.

  1. Grant users access to the source and target cubes.

  2. Restrict user access by setting up filters (on the source and target cubes) to the partitioned slices only.

    See Controlling Access to Database Cells Using Security Filters.

  3. If you are creating a replicated partition, determine whether users can make changes to a replicated partition at the data target. The update setting (which allows or disallows updates) overrides user filters that allow users to update data.

    When creating replicated partitions using the create replicated partition MaxL statement, if you do not specify the update allow grammar, replicated partitions cannot be updated by default.