Users working on the target cube do not need to know where the data is stored, because they access it as if it were part of their local cube.

Figure 9-5 Transparent Partitions

Because data is retrieved directly from the data source, users access the latest version. When they update the data, their updates are written back to the data source. This process means that other users at the data source and the data target have immediate access to those updates.

With a transparent partition, users at the data source and at the data target may notice slower performance as more users access the source data.

For example, the DBA at TBC can use a transparent partition to calculate each member of the Scenario dimension on a separate computer. This process reduces the elapsed time for the calculation while providing users with the same view of the data.

Use a transparent partition to achieve the following goals:

• Show users the latest version of the data

• Allow users at the data target to update data

• Minimize disk space footprint

When you create a transparent partition, the data slice in the target cube is cleared to #MISSING, as the data is expected to be stored on the source cube. It remains cleared even if you delete the partition.

Rules for Transparent Partitions

Transparent partitions must follow these rules:

• The shared transparent areas of the data source and data target outlines need not be identical, but you must be able to map the dimensions in them. You must tell Essbase how each dimension and member in the data source maps to each dimension and member in the data target.

• The data source and data target outlines for the nonshared areas need not be mappable, but attribute associations must be identical. Otherwise, users can get incorrect results for some retrievals. For example, if product 100-10-1010 is associated with the Grape Flavor attribute on the source, but product 100-10-1010 is not associated with Grape on the target, the total of sales for all Grape flavors in New York is incorrect.

• The partition definition must contain only stored members. You cannot use attribute dimensions or members to define a transparent partition. For example, the Market Type attribute dimension, which is associated with the Market dimension, has members Urban, Suburban, and Rural. You cannot define a partition on Urban, Suburban, or Rural.

• If a cell is mapped from the data source to an aggregate storage database as the target, all the cell's dependents must also be mapped to the same partition definition.

• You can create a transparent partition on top of a replicated partition. In other words, you can create a transparent partition target using a replicated partition source, as shown in this illustration:

  Figure 9-6 Valid Transparent Partition

  
  
  

• As illustrated below, you cannot create a transparent partition on top of multiple other partitions. In other words, you cannot create a transparent partition target from multiple sources because each cell in a database must be retrieved from only one location—either the local disk or a remote disk.

  Figure 9-7 Invalid Transparent Partition

  
  
  

• Carefully consider any formulas you assign to members in the data source and data target.

Advantages of Transparent Partitions

Transparent partitions can solve many database problems, but transparent partitions are not always the ideal partition type.

You need less disk space, because you are storing the data in one database.

The data accessed from the data target is always the latest version.

When the user updates the data at the data source, Essbase makes those changes at the data target.

Individual databases are smaller, so they can be calculated more quickly.

The distribution of the data is invisible to the end user and the end user’s tools.

You can load the data from either the data source or data target.

Disadvantages of Transparent Partitions

If the following disadvantages are too serious, consider using replicated or federated partitions instead.

Transparent partitions increase network activity, resulting in slower retrieval times for users.

Because more users are accessing the source data, retrieval time may be slower.

If the source cube fails, users at both the source and target are affected. Therefore, the network and the source cube must be available whenever users connected to the source or target need them.

You can perform some administrative operations only on local data. For example, if you archive the target cube, Essbase archives only the target and not the source cube. The following administrative operations work only on local data in block storage databases:

• CLEARDATA calculation command

• DATACOPY calculation command

• EXPORT command

• VALIDATE command

• BEGINARCHIVE and ENDARCHIVE commands

When you perform a calculation on a transparent partition, Essbase performs the calculation using the current values of the local data and transparent dependents. Essbase does not recalculate the transparent dependents, because the outlines for the source and the target may be so different that such a calculation is inaccurate. To calculate all partitions, issue a CALC ALL command for each individual partition, and then perform a CALC ALL command at the top level using the new values for each.

Consider an example where:

• The target cube's outline contains a Market dimension with East, West, South, and Central members

• The source cube's outline contains an East dimension with New York and New Jersey members

If you tried to calculate the target cube, you would assume that East was a level 0 member. In the source cube, however, East is derived by adding New York and New Jersey. Any calculations at the target, however, would not know this information and could not reflect changes made to New York and New Jersey in the source. To perform an accurate calculation, therefore, calculate East in the source and then calculate the target.

Formulas assigned to members in the source cube may produce calculated results that are inconsistent with formulas or consolidations defined in the target cube, and vice versa.

Performance Considerations for Transparent Partitions

To improve the performance of transparent partitions, consider the following guidelines when creating the partition:

• Partitioning along dense dimensions can greatly slow performance, because dense dimensions are used to determine the structure and contents of data blocks.

  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 source cube from the target cube 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 source cube to a transparent partition target cube, you can log transaction response times using the ENABLE_DIAG_TRANSPARENT_PARTITION configuration setting. Logging these messages is helpful when troubleshooting response times that are too slow.

  • When the transparent partition target is an aggregate storage (ASO) cube, 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.

Calculation of Transparent Partitions

When calculating local data that depends on remote data, Essbase must use bottom-up calculation. Be sure you are using an optimized calculator cache on the target cube. Refer to Size the Calculator Cache.

When you perform a calculation on a transparent partition, Essbase performs the calculation using the current values of the local data and transparent dependents. When calculating local data that depends on remote data, Essbase performs a bottom-up calculation. The bottom-up calculation can be done only if the calculator cache on the target database is used properly. Refer to Bottom-Up and Top-Down Calculation.

Increasing the memory assigned to the calculator cache greatly improves calculation performance with transparent partitions. When a calculation is started, a message in the application log file indicates whether the calculator cache is enabled or disabled on the target database. Using the calculator cache on the target database reduces the number of blocks that are requested from the data source during calculation. Reducing the blocks requested, in turn, reduces the network traffic that is generated by transferring blocks across the network.

Transparent Partition Calculation Performance

Calculating data on the target of a transparent partition can slow performance when Essbase must retrieve dependent blocks across the network from the source before calculating. To optimize transparent calculations, use Dynamic Calc, manage the calculator cache, avoid top down formulas, and avoid complex formulas on area-defining members.

Performance with transparent calculations also may slow if Essbase must perform a top-down calculation on any portion of the data target that contains top-down member formulas. When the data target does not contain top-down member formulas, Essbase can perform a bottom-up calculation on the data target, which is much faster.

When Essbase performs the calculation on the source cube, it can always perform a bottom-up calculation.

Consider using these calculation alternatives:

• If you are absolutely sure that a target partition calculation script does not involve access to remote data, you can use the SET REMOTECALC OFF calculation command in the calculation script to stop retrieval efforts from the source partition.

• Implement Dynamic Calc members as parents of the transparent data so that the data is calculated on the fly when it is retrieved. This process reduces the batch processing time. Essbase performs the calculation only when users request it.

• Implement a replicated layer between the low-level transparent data and high-level local data.

Consider these performance strategies:

• Keep the partition fully within the calculator cache area, which means that any sparse members in the partition definition must be contained within the calculator cache. For example, in the Sample Basic cube, if a partition definition includes @IDESC(East), all descendants of East must be within the calculator cache.

• Enable the calculator cache, and assign a sufficient amount of memory to it.

• Do not use complex formulas on any members that define the partition. For example, in Sample Basic, assigning a complex formula to New York or New Jersey (both children of East) forces Essbase to use the top-down calculation method.

Transparent Partitions and Member Formulas

If the data target and data source outlines are identical except for different member formulas, ensure that the partition definition produces the calculation results you want.

For example, suppose that the data source and data target outlines both contain a Market dimension with North and South members, and children of North and South. On the data target, Market is calculated from the data for the North and South members (and their children) on the data source. If any of these members on the data source contains member formulas, these formulas are calculated, affecting the calculated value of Market on the data target. These results may be different from how the Market member are calculated from the North and South members on the data target, where these formulas may not exist.

Ensure that any formulas you assign to members in the data source and data target produce the results you want.

Transparent Partitions and Port Usage

One port is used for every unique user and machine combination. If a user defines several transparent partitions on one server, using the same user name, then only one port is occupied.

In a transparent partition, when a user (user1) drills into an area in the target that accesses source data, user1 is using the user name declared in the partition definition (partition user) to access the data from the source database. This access causes the use of an additional port because different users (user1 and partition user) are connecting to the application.

If a second user (user2) connects to the target database and drills down to access source data, user2 also uses the user name declared in the partition definition (partition user). Because the partition user is already connected to the source database, an additional port is not needed for the partition user, as long as user2 is accessing the same source database.