Essbase Troubleshooting Partitioning

PARTITIONING & MAPPING

Why won’t the cell counts match?

There are several potential causes of this problem:

1) Implied share relationship in the defined area(s).


In this situation, if the areas were defined as Scenario for both the source and target databases, no data would appear in the target Scenario member because of the implied share on the source members . Scenario in the source technically contains no data, only a link to the data contained within its only child member, Actual. To resolve this problem, Actual must be the area defined for the source (keeping Scenario as the target area), then Actual must be mapped to Scenario on the Mappings tab of the Partition Wizard.


In the scenario above, the partition is not created properly. For Qtr1 and @IDESCENDENTS(“East”), there are no equivalent areas defined between the source and target partitions. Depending on what the customer was wanting to do here, if all of the members listed above are represented in both source and target outlines, these area definitions could be combined onto one line of the partitioned area.

Another reason this partition does not make sense is because when you list a member from just one dimension as the partitioned area (for example Qtr1), you are including only that member from that dimension (Time), but ALL members from ALL other dimensions in the outline are encompassed within that partition. In the next line where the @IDESCENDENTS(“East”) is listed, this area, along with all other members of the Market dimension have already been included in the partition by the first line. On top of that oversight, members must be mapped to equivalent areas, not simply to void except in certain instances (which will be discussed shortly).


If you were to create a partitioned area of @IDESC(“Qtr1”) with no additional areas or mappings defined, you automatically include all members of all other dimensions in the outline. In the situation above, this would be incorrect due to the additional Market members in the source outline. The additional member North would add to the total cell count of the partition on the source side, but there is no equivalent area on the target side.

To correct this problem, the partition would need to specify the areas to be included in the partition, excluding areas that don’t match up between the source and target outlines. This can become difficult as there is no “Except” delimiter in Partition Wizard. If you wanted to include all members except those beneath and including North, the area definition would need to be:

@IDESCENDENTS (“Qtr1”) (“Market” @IDESCENDENTS(“East”) @IDESCENDENTS (“West”) @IDESCENDENTS (“South”) @IDESCENDENTS (“Central”))

How do you use mappings in partitions?

Mappings are used to account for some differences and extra dimensionality in either the source or the target outlines. This is done through the Mappings tab of the Partition Wizard.

1) Only one item may be mapped per line. (See below for proper arrangement.)


2) Mapping to Void can be used to map additional dimensionality (i.e.: if a dimension exists on the source but not the target, or vice versa, then a member from the extra dimension can be mapped to void). Mapping cannot be used to map individual member differences in a dimension to void. For example, if you map @IDESC(“East”) as your partition, but there are three additional East members on the source than the target. You cannot simply map those three extra members to void. As seen previously, you must define the partition area to avoid those differences or add the additional members to the outline so that both source and target have the same number of members represented.

3) Mapping can also be used to map differences in member names of equivalent members of a dimension, such as the case between “Eastern Region” and “East” in the example above.

Dealing with additional dimensionality on one side of the partition

There are three common instances in which extra dimensions exist on one side of the partition but not the other.

The following view displays the organization of some of the dimensions in the source and target outlines:

In this arrangement, use Advanced Mapping (accessed from the Mappings tab of Partition wizard) to define the specific mappings.

2) Additional dimensions on the target outline

This is a simplified outline to more clearly illustrate the scenario. For this example, the area definition and mapping needs to resemble the following:

Source Target

Area     @IDESCENDENTS(“Market”)    @IDESCENDENTS(“Market”)”Actual”

Mapping <Void>                “Actual”

The reason that Actual must be included in the area definition is to include an equal number of cells in the target partition area as exist in the source partition area.

Any one member from the Scenario dimension can be used in this example. The member chosen should represent the location where the data should reside. It is also possible to use Budget or even the member Scenario if the data being sent to that cube is considered top-level Scenario data.

Page 15-31 of the 5.x DBA Guide also gives an additional example of how this same partition can be accomplished using Area Specific Mapping.

3) Additional dimensions on the Source


Setting up a partition where there are more dimensions in the source than the target is similar to the previous situation.

Source Target

Area     @IDESCENDENTS(“Market”)”Actual”    @IDESCENDENTS(“Market”)

Mapping
“Actual”                    <Void>

If Actual is not included in the area definition, the partition will still function, but

will not validate due to a cell-count mismatch error.

Aliases and Mappings

Member mappings in a partition definition should always contain member names, never aliases. The following examples illustrate this restriction.

Using aliases of either source or target outline members in a partition’s mapping may cause problems with the partition, normally resulting in data not appearing in the target partition, whether Replicated or Transparent.

Partitioning and Port Usage

There is a simple formula for calculating port usage using Essbase:

Machine + Username = 1 Port

Partitioning also follows these same constraints, and because different partition types use different methods for connecting to the database, port usage may differ.

When using Replicated partitions, the users will normally connect only to the target cube. The process to update the data on the target utilizes one port to connect to the source database and pull data to the target. This is accomplished by the username defined within the partition definition on the Security tab.

Transparent partition port usage differs from Replicated partitions because a connection is established between the source and target partitions each time a user requests data on the target partitioned area. The user defined within the partition makes this connection; any security filters applied to this user take effect and limit what data can be brought over the partition to the end-user.


Each user connecting to the target will take up one port, but the connection between the target and source databases only uses one, regardless of the number of users requesting data from the partitioned area.

Linked partitions operate under more complex port usage situations solely because of the variety of different options that exist in connecting between the source and target databases. If a user connects to the target database and launches the partition to the source, Essbase attempts to use that individual’s username to connect to the source. If that person doesn’t have access to the source database, one of two situations may occur. If the default login is set for the partition (on the Connect page of Partition Wizard), Essbase will use that user ID to login. If none of the above conditions are satisfied, Essbase will bring up a login box in which the user must enter a login name and password to be granted access to the source database.

In the illustration above, User1 had access to both Target and Source, while User2 has access to only to Target. User2’s login request to the source is refused, so he is required to use the default user defined in Partition Wizard.

Partitioning and Calculations/Dynamic Calculations

Calculations

Calculations on the source partition are not of too much concern; because the source will normally not have dependencies on other databases, it is relatively isolated in its functioning. When calculating target partitions, though, there are some issues to be aware of.

The performance of replicated partitions can often be greatly improved through the addition of certain calculations or strategically placed Dynamic Calc or Dynamic Calc and Store members on target outline members. They allow for less data to be sent through the network and, in the case of Dynamic Calc, can save disk space (replicated only) and dynamically consolidate members from different databases. The rules for setting members to Dynamic Calc are generally the same as they would be on an un-partitioned database. Dense members can almost always be tagged as Dynamic Calc without degrading performance. The main exception concerns members that have a formula that has dependencies on other blocks; these calculations can degrade performance and should be tested before placing them in a production model. Sparse members should be tagged dynamic calc only if they have fewer than five children and only with great care if they have a formula. This is due to the fact that every sparse calculation has dependencies on other blocks.

Calculations That Go Forever

A common call from customers concerns running a calculation on the target database that never seems to finish. There can be several causes to this dilemma:

  • Partition across dense dimensions (they require more blocks to be accessed to complete the calculation, thus require additional time)
  • Target members may be tagged as Dynamic Calc with more than 5 children or with a sparse member formula
  • Calculations on target transparent databases that include areas included within the partition.

    When running calculations on the target database of a transparent partition, calculation is performed in a “top-down” manner, rather than the normal “bottom-up,” thus potentially increasing the total calculation time.

Dynamic Calc

Partitioning can be used effectively with Dynamic Calculations in certain situations, but when setting this up, attention must be paid to certain factors that could affect the performance and operation of the database.

  1. Dynamic target partition members

If you create a transparent partition and send data to members of the target outline that are tagged Dynamic Calc, you will be able to view the results of that retrieval (even though the target member is tagged not to store data). But if the same arrangement is made with a replicated partition, the data sent to the target Dynamic Calc members will disappear.

One effective method of using a transparent partitions is to tag parent level members on the target as Dynamic Calc, then define only 0-level members to be sent across the partition. This saves time on the 0-level retrieval because less data is sent over the network, but does take some time to do the local dynamic calculations of the parent members. It can also be a good way to dynamically consolidate data from different databases on the target.

The 5.x DBA Guide, on p. 28-21, lists a number of suggestions for optimizing partitions with Dynamic Calc/Dynamic Calc and Store:

  • Upper level sparse target members in a transparent partition as Dynamic Calc and Store
  • Dynamic Calc in most replicated partition situations
  • Essbase always replicates source Dynamic Calc data.
  1. Source members tagged Dynamic Calc

When setting source members to Dynamic Calc, some caution should be taken. If you are sending over 0-level formula members, this isn’t too much of an issue. Problems are more likely to occur when you are sending parent level Dynamic Calc members to the target – additional blocks need to be accessed in order to calculate the parent members. It is recommended that this operation be performed only if the member is dense or if the member is sparse with fewer than 5 children and does not have a member formula.

In general, the same precautions that you would make in applying Dynamic Calc to a normal database need to also be taken with a partitioned database. Be sure to test after adding each additional dynamic calc member to the outline.

If a source member of a transparent partition is tagged Dynamic Calc but the target member is not, Essbase retrieves the value from the target – NOT the source (see p. 28-21 of the 5.x DBA Guide). This would prevent users on the target from seeing the most current data available.

Fix Statements

FIX statements should be located only on members that are local to the transparent outline (and preferably sparse).

Partitioning and Personal Essbase

The feasibility of partitioning on a Personal Essbase (one port) server has been debated, but comes down to this: it is possible, but only in two restricted ways. This is due solely to way that Personal Essbase functions.

The first way that partitioning can be used is between two databases under the same application on a Personal Essbase server. Personal Essbase allows only one application to start at a time. So, all partitioned databases must reside within the same application if located on the same server.

The other way that a Personal Essbase database can be partitioned is if the Personal Essbase database acts as the target for a transparent partition. There are several rules that must be followed for this to work:

1) The connection from spreadsheet client to the Personal Essbase server must use the same username as was used to define the partition definition.

2) There must be no other connections to the Personal Essbase server that would require the use of an additional port.

3) After the partition definition has been defined, it is not possible to validate the partition.

Partitioning Security

There are a number of different factors to consider when setting up partitioning security. There are two main types of users who will need different levels of access to the databases involved: end-users (those users who access the target database), and the partition user (the user defined within the partition definition).

End users of Transparent and Replicated partitions normally only require access to the target database. Any filters for their access can be applied as on a normal non-partitioned application. If an end user has access to the source database, but connects to it via a Transparent partition, the end user’s security information for the source will not be applied since the connection to that database is being made through a separate connection defined within the partition definition. That user would need to connect directly to the source, bypassing the partition, to utilize that security access.

Security for Transparent partitions is often far more complex than with Replicated partitions due to the fact that users can update data on the source of a Transparent partition via the target database. For minimal access to a Transparent partition, the end-user must have read access to the target, and the partition user must have read access to the source. If an end-user attempts to lock and send data, they must have write access to the target, and the partition user must have write access to the source. Filters can be applied to either the end user or the partition user in this situation.

With Replicated partitions, the partition user must have at least read access to the source and write access to the target since data must be written to the target database. If updates are allowed on the target area by the end-users, those users must have write access to that database and the setting to allow the target partition area to be updated must be enabled within Partition Manager.

Linked partitions do not make use of the partition user security access, but rather it utilizes the end-user’s access. When the end-user launches the linked partition from the source, if no target database access has been defined, the partition logs that user in via the default login setting. If there is no default user specified, then the application will prompt for login information. In this situation, filters function as if with normal non-partitioned databases.

OUTLINE SYNCHRONIZATION

How Outline Synchronization Works

Once you create a partition between two (or more) databases, Outline Synchronization (Outline Sync) begins tracking any changes made to the outline. In partitioning, data can only travel from source to target. In Outline Sync, a setting can be modified to say that outline changes are propagated either from data source to data target OR data target to data source. Here, “source” and “target” will be discussed in terms of which way the outline changes are propagated. Specifically, the source outline is the outline where changes are made and the target outline is the outline where the changes are propagated.

Outline Sync does not blindly track changes made to the outline. Instead, it compares the old and new outlines, then prioritizes the changes that need to be applied to the target outline. If synchronization is unsuccessful, any changes that could not be applied to the target outline from the source will be written to the application log file and the user notified that there were errors.

Here are some examples of the sorts of changes that outline sync will propagate:

  • Members entering or leaving a partitioned area
  • Moves or renames (member names and aliases) within a partitioned area
  • Shared members of members within the partition (even if the shared members aren’t explicitly part of the partition definition area.)

When a member is moved between dimensions, the member is essentially deleted from the first dimension, then added to the second. This is an issue with regards to the data; since data is not retained after the delete, it will no longer be associated with that member once it’s located in the new dimension.

Making the outline changes

The two ESSCMD commands required to synchronize a target database from changes made to the source are: GETPARTITIONOTLCHANGES and APPLYOTLCHANGEFILE. The first command copies the .chg file from the source to the target database directory, while the second actually launches the restructure to make any necessary outline changes.

When using the APPLYOTLCHANGEFILE command, make sure you fully path to the .chg file in the target database directory on the server.

Purging the change file

PURGEOTLCHANGEFILE purges changes from the .chg file, but only in certain situations. Changes that have already been applied to the target will be removed; if the entire file has been applied, then the whole file will be removed from the selected database directory. If any changes were edited out during the synchronization process, those changes will be removed along with any changes that were applied. If this command is accidentally run before changes were applied to the target outline, then no changes will be purged. This intelligence has been built into the command to prevent all changes from being lost in the event that the synchronization could not take place, yet the ESSCMD script continued on.

Resetting the change log file time

RESETOTLCHANGETIME resets the time stamp on the change files (.chg) in a certain database. The source database in this command does not have to be the source of a partition, or have a partition associated with it at all; the source is where the time setting comes from, and the target is where the time stamp is changed. (The target must be part of a partition.) The time stamp is a crucial part of the outline synchronization process. Outline Sync compares the time stamps on the partition source and target databases to determine if the outline source has been updated more recently than the target.

This command is used in instances such as power outages where the time on the target database may have changed. Since Outline Synchronization uses this time stamp to determine whether or not a partition is synchronized, it is crucial that these times be accurate. This command is not meant to be used on a regular basis for partition maintenance.

NEW FOR 6.X

Partitioning

In 6.0, attribute dimensions will be introduced that allow users to assign different specifications to members of a dimension. Partitioning will be enabled to create areas based on these attribute members. The base members associated with the specified partition will be included in the area as defined by the user, but all attribute dimensions will be included within the partition automatically.

Partitioning and Calculations

There have been a number of enhancements in the way that Essbase handles calculations on the target of transparent partitions. In version 5.x it was extremely easy to have a calculation on the target last “forever.” With version 6.x it will be easier to avoid these calculation issues. There will still be some limitations to its functionality, but in general, the enhancements will greatly improve the speed of these calculations.

Partitioning and Attributes

There are a number of new caveats and catches to making use of attributes in partitioning.

  1. If an attribute dimension exists on one side of the partition but not another, that attribute dimension name and all attribute names must be mapped to VOID.


In this example, the attribute dimension Package exists on the source but not the target. Here is the mapping that would need to take place for the partition to function:

  1. Note how the attribute member names are referenced. These names are considered their “full” names by Essbase, as defined in the Outline Editor menu item Settings -> Attribute Member Names. Attribute member names can be set as the attribute name itself without reference to the attribute dimension name, or linked to that dimension name with a character.
  2. When mapping replicated partitions, attribute names cannot be included in the area definition along with its associated parent dimension name. For instance, the Flavor attribute dimension has an attribute called “Apple” which is associated with member “100-20” in the Product dimension. The following area definition could not exist:

Apple, Product, “New York”

The reason is that Essbase considers this grouping of members to be one “block”(or grouping of one member from each dimension) in the database. Since the Product dimension is represented twice (once by the Attribute, once by the dimension name itself), this is not a feasible block.

Outline Synchronization

Outline sync will have the capability to update attribute associations, but comments on members are not updated. Also, change filtering cannot be used on attribute dimensions.