To improve the performance of transparent partitions, consider the following guidelines when creating the partition:
Partitioning along dense dimensions in a transparent partition can greatly slow performance, because dense dimensions are used to determine the structure and contents of data blocks. If a database is partitioned only along a dense dimension at the target, Essbase must compose data blocks by performing network calls for the remote data in the transparent partition and to the disk I/O for the local portion of the block.
To improve performance, consider including one or more sparse dimensions in the area definition so that the number of blocks required is limited to combinations with the sparse members.
Basing transparent partitions on the attribute values of a dimension can increase retrieval time, because attributes are associated with sparse dimensions. In such cases, partitioning at a level higher than the level that is associated with attributes improves retrieval time. For example, in the Product dimension of the Sample.Basic database, if children 100-10, 200-10, and 300-10 (level 0) are associated with attributes, then partition their parents 100, 200, and 300 (level 1) for better retrieval performance.
Loading data into the data source from the data target can greatly slow performance. If possible, load data into the data source locally.
Retrieval time is slower because users access the data over the network.
When a transparent partition is the target, consider using these configuration settings:
For requests sent from a data source to a transparent partition target (whether a block storage or aggregate storage database), you can log transaction response times using the ENABLE_DIAG_TRANSPARENT_PARTITION configuration setting in the essbase.cfg file. Logging these messages is helpful when troubleshooting response times that are too slow.
When the transparent partition target is an aggregate storage database, you can specify the maximum size of the request grid and the response grid, using the MAX_REQUEST_GRID_SIZE and MAX_RESPONSE_GRID_SIZE configuration settings.
Partitioning base dimensions can greatly slow performance.
See Performance Considerations for Transparent Partition Calculations.