ESSBASE partition

Designing Partitioned Applications

In This Section:

Understanding Essbase Partitioning

Partition Design Requirements

Replicated Partitions

Transparent Partitions

Linked Partitions

Case Studies for Designing Partitioned Databases

Understanding Essbase Partitioning

A partition is the region of a database that is shared with another database. An Essbase partitioned application can span multiple servers, processors, or computers.

Partition Types

Table 33 lists the types of partitions that are supported in Essbase:

Table 33. Partition Types

Partition Type	Description	Applies To
Replicated	A copy of a portion of the data source that is stored in the data target. See Replicated Partitions.	Block storage databases Aggregate storage databases
Transparent	Allows users to access data from the data source as though it were stored in the data target. The data is, however, stored at the data source, which can be in another application or Essbase database, or on another Essbase Server. See Transparent Partitions.	Block storage databases Aggregate storage databases
Linked	Sends users from a cell in one database to a cell in another database. Linked partitions give users a different perspective on the data. See Linked Partitions.	Block storage databases

Use the information in Table 34 to help you choose which type of partition to use:

Table 34. Features Supported by Partition Type

Feature	Replicated	Transparent	Linked
Up-to-the-minute data		x	x
Reduced network traffic	x		x
Reduced disk space		x	x
Increased calculation speed	x
Smaller databases		x	x
Improved query speed	x		x
Invisible to end users	x	x
Access to databases with different dimensionality			x
Easier to recover	x
Less synchronization required			x
Ability to query data based on its attributes		x	x
Ability to use front-end tools that are not distributed OLAP-aware	x	x
Easy to perform frequent updates and calculations		x
Ability to update data at the data target		x	x
View data in a different context			x
Perform batch updates and simple aggregations	x

Parts of a Partition

Partitions contain the following parts, as illustrated in Figure 46, Parts of a Partition and described in Table 35, Parts of a Partition.

Figure 46. Parts of a Partition

Table 35. Parts of a Partition

Part	Description
Type of partition	A flag indicating whether the partition is replicated, transparent, or linked.
Data source information	The server, application, and database name of the data source.
Data target information	The server, application, and database name of the data target.
Login and password	The login and password information for the data source and the data target. This information is used for internal requests between the two databases to execute administrative and end-user operations.
Shared areas	A definition of one or more areas, or regions, shared between the data source and the data target. To share more than one noncontiguous portion of a database, define multiple areas in a single partition. This information determines which parts of the data source and data target are shared so that Essbase can put the proper data into the data target and keep the outlines for the shared areas synchronized.
Member mapping information	A description of how the members in the data source map to members in the data target. Essbase uses this information to determine how to put data into the data target if the data target and the data source use different names for some members and dimensions.
State of the partition	Information about whether the partition is up-to-date and when the partition was last updated.

Data Sources and Data Targets

Partitioned databases contain at least one data source (the primary site of the data) and at least one data target (the secondary site of the data). One database can serve as the data source for one partition and the data target for another partition. When defining a partition, you map cells in the data source to their counterparts in the data target.

Figure 47. Data Source and Data Target

An Essbase database can contain many partitions, as well as data that is not shared with any other Essbase database. You can define partitions between the following databases:

• Different databases in different applications, as long as each database uses the same language and the same Unicode-related mode.

  The applications can be on the same computer or different computers.

• Different databases in one block storage application.

  This practice is not recommended, because the full benefits of partitioning databases are realized when each database is in a separate application.

You can define only one partition of each type between the same two databases. For example, you can create only one replicated partition between the Sampeast.East and Samppart.Company databases. The East or Company databases can, however, contain many replicated partitions that connect to other databases.

One database can serve as the data source or data target for multiple partitions. To share data among many databases, create multiple partitions, each with the same data source and a different data target, as shown in Figure 48, Data Shared at Multiple Targets:

Figure 48. Data Shared at Multiple Targets

Table 36 lists the combinations of block storage and aggregate storage databases as data target and data source that are supported by each partition type:

Table 36. Combinations of Data Targets and Data Sources Supported by Partition Type

Target	Source	Replicated	Transparent	Linked
Block storage	Block storage	Yes	Yes	Yes
Block storage	Aggregate storage	No	Yes	Yes
Aggregate storage	Aggregate storage	No	Yes	Yes
Aggregate storage	Block storage	Yes	Yes	Yes

Overlapping Partitions

An overlapping partition occurs when similar data from multiple databases is the data source for one data target in a partition.

For example, IDESC East, Sales from database 1 and Boston, Sales from database 2 are mapped to IDESC East, Sales and Boston, Sales in database 3. Because Boston is a member of the East dimension, the data for Boston mapped to database 3 from database 1 and database 2 overlap. This data overlap results in an overlapping partition, as shown in Figure 49, Overlapping Partitions:

Figure 49. Overlapping Partitions

An overlapping partition is allowed in linked partitions but is invalid in replicated and transparent partitions and generates an error message during validation.

Substitution Variables in Partition Definitions

Using substitution variables in partition definitions enables you to base the partition definition on different members at different times. Substitution variables act as global placeholders for information that changes regularly; each variable has an assigned value, which the Database Manager can change anytime. For example, you can define a substitution variable named Curmonth and change the substitution variable value to the member name for each month throughout the year to Jan, Feb, Mar, and so on. In this example, using a substitution variable reduces the partition size because you need not include all months in the partition definition area to access data from one month.

To specify a substitution variable in an area definition or in a mapping specification, use the “Use text editor” or “Use inline editing” editing option. Insert “&” at the beginning of the substitution variable name; for example, &Month. Essbase uses substitution values when you verify the partition. When you perform any process that uses partitioned data, Essbase resolves the substitution variable name to its value. The substitution variable name is displayed when you view the partition definition. See Using Substitution Variables.

Attributes in Partitions

For block storage databases, you can use attribute functions for partitioning on attribute values, but you cannot partition an attribute dimension. Use attribute values to partition a database to access members of a dimension according to their characteristics.

For example, in the Sample.Basic database, you cannot partition the Pkg Type attribute dimension, but you can create a partition that contains all the members of the Product dimension that are associated with either or both members (Bottle and Can) of the Pkg Type dimension. If you create a partition that contains members associated with Can, you can access data only on Product members that are packaged in cans; namely, 100-10, 100-20, and 300-30.

You can use the @ATTRIBUTE command and the @WITHATTR command to define partitions.

For example, to extract data on all members of the Product dimension that are associated with the Caffeinated attribute dimension, you can create a partition such as @ATTRIBUTE (Caffeinated). But you cannot partition the Caffeinated attribute dimension.

Based on the previous example, this partition is correct:

Source                   Target
@ATTRIBUTE(Caffeinated)  @ATTRIBUTE(Caffeinated)

This partition is incorrect:

Source                   Target
Caffeinated              Caffeinated

For more information about these commands, see the Oracle Essbase Technical Reference.

Also see Working with Attributes.

Version and Encoding Requirements

Version: Both ends (the source and target) of the partition must be on the same release level of Essbase Server for these partition types:

• Replicated

• Transparent

• Linked

Encoding: The application mode—Unicode mode or non-Unicode mode—of both ends of the partition must be the same for these partition types:

• Replicated

• Transparent

Partition Design Requirements

Use the information in this section to carefully design partitions before implementing them.

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.

• Outline synchronization across multiple databases (except when an aggregate storage database is the target of a transparent partition)

  Essbase tracks changes made to the outlines of partitioned databases and provides tools for updating related outlines.

• 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.

  Note:

  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 have to 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.

    See Using a Transparent Partition to Enable Write-Back for Aggregate Storage Databases.

• 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 linked and transparent partitions exacerbate 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?

See Case Studies for Designing Partitioned Databases.

Security for Partitioned Databases

Users accessing replicated, transparent, or linked partitions may need to view data stored in multiple databases. The following sections describe how to set up security so that users do not view or change inappropriate data.

Setting up End-User Security

Create the required end users with the correct filters.

1. Create accounts for users at the data target.

   See Managing Users and Groups in Native Security Mode.

2. Create read and write filters at the data target to determine what end users can view and update.

   See User Management and Security.

3. If you are creating a replicated partition, determine whether users can make changes to a replicated partition at the data target. This setting overrides user filters that allow users to update data.

   See the Oracle Essbase Administration Services Online Help.

4. If you are creating a linked partition, create accounts for users at the data source. Users accessing linked databases may need to connect to multiple databases.

   See Drill Across and Linked Partitions.

Setting up Administrator Security

The administrative account performs all read and write operations requested by the data target for the data source. For example, when end users request data at the data target, the administrative account retrieves the data. When end users update data at the data target, the administrative account logs into the data source and updates the data there.

You can create filters on the administrative account in addition to filters on the end users. Filters on the administrative account can ensure that no one at the data target can view or update inappropriate data. For example, the administrator at the corporate database can restrict write access on certain cells to avoid relying on administrators in the regions to set up security correctly for each end user.

Create the required administrative users with the correct filters.

1. Create an administrative account at the data source and data target.

   See Setting the User Name and Password.

   Essbase uses this account to log onto the data source to retrieve data and to perform outline synchronization operations.

2. Create read and write filters to determine which data administrators can view and update.

   See User Management and Security.

   • For replicated partitions, set up read filters at the data source to determine which data Essbase reads when replicating, and set up write filters at the data target to determine which data Essbase writes to when replicating.

   • For transparent partitions, set up read filters at the data source to determine which data Essbase retrieves for end users, and set up write filters at the data source to determine which data Essbase updates for end users.

Using Backup and Restore and Transaction Logging and Replay with Partitioned Databases

If you are using Essbase backup and restore and transaction logging and replay features with partitioned databases, there are guidelines that you must follow. See the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.

Replicated Partitions

A replicated partition is a copy of a portion of the data source that is stored in the data target. Some users can then access the data in the data source while others access it in the data target.

For example, in the Samppart and Sampeast sample applications, the DBA at The Beverage Company (TBC) created a replicated partition between the East database and the Company database containing Actual, Budget, Variance, and Variance%. Users in the eastern region now store their budget data locally. Because they do not have to retrieve this data live from corporate headquarters, response times are faster, and they have more control over the downtimes and administration of local data. See Case Study 1: Partitioning an Existing Database.

Changes to the data in a replicated partition flow from the data source to the data target. Changes made to replicated data in the data target do not flow back to the data source. If users change the data at the data target, Essbase overwrites their changes when the DBA updates the replicated partition.

When a replicated partition is defined, the DBA can select a setting to prevent the data in the replicated portion of the data target from being updated. This setting takes precedence over access provided by security filters and is also honored by batch operations, such as data load and calculation. By default, a target of a replicated partition cannot be updated. To set a partition so that it can be updated, see the Oracle Essbase Administration Services Online Help.

Use a replicated partition to achieve any of the following goals:

• Decrease network activity

• Decrease query response times

• Decrease calculation times

• Recover more easily from system failures

Rules for Replicated Partitions

Replicated partitions must follow these rules:

• You must be able to map the shared replicated areas of the data source and data target outlines, although the shared areas need not be identical. 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 non-shared areas do not have to be mappable.

• Because none of the areas that you use as a replicated partition target can come from a transparent partition source, you cannot create a replicated partition on top of a transparent partition, as shown in Figure 50, Invalid Replicated Partition:

  Figure 50. Invalid Replicated Partition

  

• The cells in the data target of a replicated partition cannot come from two data sources; the cells in one partition must come from one database. To replicate cells from more than one database, create a different partition for each data source.

  The cells in a data target can be the data source for a different replicated partition. For example, if the Samppart.Company database contains a replicated partition from the Sampeast.East database, you can replicate the cells in Sampeast.East into a third database, such as Sampwest.West.

• You cannot use attribute members to define a replicated partition. For example, associated with the Market dimension, the Market Type attribute dimension members are Urban, Suburban, and Rural. You cannot define a partition on Urban, Suburban, or Rural, because a replicated partition contains dynamic data, not stored data. Therefore, an attempt to map attributes in replicated partitions results in an error message. However, you can use the WITHATTR command to replicate attribute data.

Advantages of Replicated Partitions

• Because data is stored closer to end users, in the data target, replicated partitions can decrease network activity, resulting in improved retrieval times for users.

• The data is more easily accessible to all users. Some users access the data at the data source, others at the data target.

• Failures are not as catastrophic. Because the data is in more than one place, if one database fails, only the users connected to that database are unable to access the information. Data is still available at and can be retrieved from the other sites.

• Local DBAs can control the downtime of their local databases. For example, because users in the eastern region are accessing their own replicated data instead of the Company database, DBAs can bring down the Company database without affecting users in the eastern region.

• Because only the relevant data is kept at each site, databases can be smaller. For example, users in the eastern region can replicate only the eastern budget information, instead of accessing a larger company database containing budget information for all regions.

Disadvantages of Replicated Partitions

• You need more disk space because data is stored in multiple locations.

• Because the DBA must manually refresh data regularly, users may not see the latest version of the data.

Performance Considerations for Replicated Partitions

To improve the performance of replicated partitions, follow these guidelines:

• Do not replicate members that are dynamically calculated in the data source, because Essbase must probe the outline to find dynamically calculated members and their children to determine how to perform the calculation.

• Do not replicate derived data from the data source. Instead, replicate the lowest practical level of each dimension and perform the calculations on the data target after you complete the replication.

  For example, to replicate the database along the Market dimension:

  • Define the shared area as the lowest-level members of the Market dimension that you care about, for example, East, West, South, and Central and the level 0 members of the other dimensions.

  • After you complete the replication, calculate the values for Market and the upper-level values in the other dimensions at the data target.

    Sometimes you cannot calculate derived data at the data target. In that case, replicate it from the data source. For example, you cannot calculate derived data at the data source if the data meets any of the following criteria:

    • Requires that data outside the replicated area be calculated.

    • Requires calculation scripts from which you cannot extract only the portion to be calculated at the data target.

    • Is being replicated onto a computer with little processing power, such as a laptop.

• To optimize the replication of an aggregate storage database when the aggregate storage database is the target and a block storage database is the source and the two outlines are identical, use one of these methods:

  • The REPLICATIONASSUMEIDENTICALOUTLINE configuration setting in essbase.cfg. The setting can be enabled at the server, application, or database level. The syntax for the setting is as follows:

    REPLICATIONASSUMEIDENTICALOUTLINE [appname [dbname]] TRUE | FALSE

    When updating the essbase.cfg file, you must stop and then restart Essbase Server for the changes to take effect.

  • The alter database MaxL statement with the replication_assume_identical_outline grammar. The statement can be enabled only at the database level. The syntax for the statement is as follows:

    alter database appname.dbname enable | disable replication_assume_identical_outline;

    When using the alter database statement, you do not need to stop and restart the aggregate storage application.

  Both optimization methods affect only the target aggregate storage application; the source block storage application is not affected. The methods do not apply to block storage replication.

• Partitioning along a dense dimension takes longer than partitioning along a sparse dimension. When Essbase replicates data partitioned along a dense dimension, it must access every block in the data source and then create each block in the data target during the replication operation.

• You cannot replicate data into a member that is dynamically calculated at the data target. Essbase does not load or replicate into Dynamic Calc and Dynamic Calc and Store members, because these members do not contain data until a user requests it at runtime. Essbase avoids sending replicated data for both dynamic dense and dynamic sparse members on the replication target, because this data is not stored on the data target.

  To replicate only the data values that have changed instead of the entire partition, see Populating or Updating Replicated Partitions.

Replicated Partitions and Port Usage

With replicated partitions, users connect to the target database only. When data is updated on the target database, the process of replicating data from the source database to the target database utilizes one port and this connection is based on the user name declared in the partition definition (partition user).

Note:

Because of the short-term nature of replication, replicated partitions and ports are rarely a problem.

Transparent Partitions

A transparent partition allows users to manipulate data that is stored remotely as if it were part of the local database. The remote data is retrieved from the data source each time that users at the data target request it. Users do not need to know where the data is stored, because they see it as part of their local database.

Figure 51. Transparent Partitions

Because data is retrieved directly from the data source, users see 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. See Case Study 1: Partitioning an Existing Database.

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

• Decrease disk space

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 Figure 52, Valid Transparent Partition

  Figure 52. Valid Transparent Partition

  

• As illustrated in Figure 53, Invalid Transparent Partition, you cannot create a transparent partition on top of more than one other partition. 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 53. 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.

• You can enable write-back functionality for aggregate storage databases by creating a transparent partition between an aggregate storage database as the source and a block storage database as the target.

  See Using a Transparent Partition to Enable Write-Back for Aggregate Storage Databases.

Disadvantages of Transparent Partitions

If these disadvantages are too serious, consider using replicated or linked partitions instead.

• Transparent partitions increase network activity, because Essbase transfers the data at the data source across the network to the data target. Increased network activity results in slower retrieval times for users.

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

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

• You can perform some administrative operations only on local data. For example, if you archive the data target, Essbase archives only the data target and not the data source. 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

  • Restructure operations in Administration Services

• 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 values of transparent dependents, because the outlines for the data source and the data 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 partition.

  Consider this example:

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

  • The data source outline contains an East dimension with New York and New Jersey members

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

• Formulas assigned to members in the data source may produce calculated results that are inconsistent with formulas or consolidations defined in the data target, 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 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, in addition 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.

• For calculation-related performance considerations, see Performance Considerations for Transparent Partition Calculations.

Calculating Transparent Partitions

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. See Using Bottom-Up 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 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. See Sizing the Calculator Cache.

Performance Considerations for Transparent Partition Calculations

Calculating data on the data target can greatly slow performance when the data target must retrieve each dependent data block across the network, and then perform the calculation.

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 data source, it can always perform a bottom-up calculation. For a comparison of top-down and bottom-up calculations, see Using 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. See the Oracle Essbase Technical Reference.

• Dynamic Calc or Dynamic Calc and Store 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.

• 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 (see Sizing the Calculator Cache), which means that any sparse members in the partition definition must be contained within the calculator cache. For example, in the Sample.Basic database, 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. See Bottom-Up and Top-Down Calculation.

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.

Linked Partitions

A linked partition connects two databases with a data cell. When you click the linked cell in the data target, you drill across to a second database—the data source—and view the data there. If you are using Spreadsheet Add-in, for example, a new sheet opens, displaying the dimensions in the second database. You can then drill down into these dimensions.

Unlike replicated or transparent partitions, linked partitions do not restrict you to viewing data in the same dimensionality as the target database. The database that you link to can contain different dimensions than the database from which you connected. With linked partitions, data is not physically transferred from the source to the target. Instead, a data cell or range of cells on the target provides a link point to a cell or range of cells on the source.

Figure 54. Linked Partition

To prevent users from seeing privileged data, establish security filters on the data source and the data target. See Security for Partitioned Databases.

There are no performance considerations for linked partitions, beyond optimizing the performance of each linked database.

For example, if TBC grew into a large company, it might have several business units. Some data, such as profit and sales, exists in each business unit. TBC can store profit and sales in a centralized database so that the profit and sales for the entire company are available at a glance. The DBA can link business unit databases to the corporate database. See Case Study 3: Linking Two Databases.

A user in such a scenario can perform these tasks:

• View the general profit and sales at the corporate level in a spreadsheet at the data target.

• Drill across to individual business units, such as East (this action opens a new spreadsheet).

• Drill down in the new spreadsheet to more-detailed data.

Figure 55. Source and Target for Linked Partition

For linked partitions, the spreadsheet that the user first views is connected to the data target, and the spreadsheet that opens when the user drills across is connected to the data source. This setup is the opposite of replicated and transparent databases, in which users move from the data target to the data source.

Use a linked partition to connect databases with different dimensionality.

Advantages of Linked Partitions

• You can view data in a different context; that is, you can navigate between databases containing many dimensions.

• You need not keep the data source and data target outlines closely synchronized, because less of the outline is shared.

• A single data cell can allow the user to navigate to more than one database. For example, the Total Profit cell in the Accounting database can link to the Profit cells in the databases of each business unit.

• Performance may improve, because Essbase accesses the database directly, not through a data target.

Disadvantages of Linked Partitions

You must create an account for users on each database or use default access to the destination database (such as through a guest account). See Drill Across and Linked Partitions.

Drill Across and Linked Partitions

When a user clicks on a linked cell in a linked partition, a spreadsheet opens and displays the linked database. This process is called drill across. To facilitate drill-across access, you can use the following strategies:

• Create accounts for each user on each database. For example, if Mary accesses data in a Company database and an East database, create an account with the same login and password for Mary on the Company and East databases.

  See Managing Users and Groups in Native Security Mode.

• Create a default account that users can use when accessing target databases. For example, if users access data through a data source named Company and a data target named East, create a guest account for the East database with the appropriate permissions. Use the guest account login and password as the default login when creating the linked partition.

When a user drills across on data to a data target, Essbase logs the user into the data target using the following steps:

1. Checks whether the user has an account on the data target with the same name and password. If so, Essbase logs in the user using that account.

2. Checks whether you specified a default account on the data target when you created the partition. If you did, Essbase logs the user in using that account.

3. Opens a login window prompting the user to enter a new login and password. Once the user enters a valid login and password, Essbase logs the user in using that account.

Linked Partitions and Port Usage

When accessing a linked partition, Essbase tries to use the end user’s (user1) login information to connect to the source database. If user1 does not have access to the source database, Essbase looks for the linked partition default user name and password. If these defaults are not specified, user1 is asked to enter login information to access the source database. Port usage varies depending on the number of user names being used to access the various source and target databases (and whether those databases are contained within the same or different servers).

Case Studies for Designing Partitioned Databases

The following sections describe examples of partitioning a database:

Case Study 1: Partitioning an Existing Database

Assume that TBC, the fictional soft drink company upon which the Sample.Basic database is based, started out with a centralized database. As the eastern region grew, however, this solution was no longer feasible. The networks to the eastern region could not handle the large data flow. Users were constantly waiting for data that they needed in order to make decisions. One day, the network went down, and users at the eastern region could not access the data.

Everyone agreed that the eastern region needed to access its own data directly, without going through the company database. In addition, TBC decided to change where budgeting information was stored. The corporate budget stays at company headquarters, but the eastern region budget moves to the eastern region’s database.

So, assume that TBC decided to ask you to partition their large centralized database into two smaller databases—Company and East.

This example is based on the Samppart sample application (which contains the Company database) and the Sampeast sample application (which contains the East database).

Figure 56, Data Flow from Data Source to Data Target shows a subset of the partitioned databases. The arrows indicate flow from the data source to the data target. The Company database is the data source for the Corp_Budget member and the data target for the East and the East Actual members. The East database is the data source for its East and Actual members and the data target for the Corp_Budget member.

Figure 56. Data Flow from Data Source to Data Target

  To create a partition based on this example:

1. Determine which data to partition.

   The Sample.Basic database contains five standard dimensions—Year, Measures, Product, Market, and Scenario.

   • Partition the database along the East member of the Market dimension to give the eastern region more control over the contents of its database.

   • Partition the database along the Actual and Corp_Budget members of the Scenario dimension.

2. Choose the data source and the data target.

   • For Corp_Budget, use Company as source and East as Target, because the company owns the corporate budget—it is the source.

   • For Eastern Region and Actual, East is the source and Company is the target, because the eastern region needs to update its market and actual information.

3. Decide which type of partition to use.

   • For East, use transparent because the data target (Company) needs up-to-the-minute data.

   • For Corp_Budget, use transparent because the data target (East) needs up-to-the-minute data.

   • For East Actual, use replication because the data target (Company) does not need up-to-the-minute data.

4. Create the partitioned databases by performing the following tasks.

   • Create the Sampeast application.

   • Create the East database by cutting the Company outline and pasting it into the East outline. Then delete the extra members (South, West, and Central) and promote East.

   • If necessary, edit existing data sources, rules files, calculation scripts, report scripts, and outlines.

   • Create the partitions.

   • Load data into the new partitions.

After the corporate database is partitioned, users and DBAs see the following benefits:

• Faster response times, because they are competing with fewer users for the data and they are accessing the data locally

• Easier maintenance, because DBAs can control the downtime of their local databases

• Access to more data, because users can connect to both the eastern and corporate budgets

• Higher-quality data, because the corporate budget and eastern budget are now synchronized—they use the same data.

Case Study 2: Connecting Existing Related Databases

Assume that TBC has several databases, such as Inventory, Payroll, Marketing, and Sales. Users viewing the Sample.Basic database want to share data with and navigate to those other databases. You, the DBA, want to synchronize related data. It is impractical to combine all of the databases into one database, for the following reasons:

• So many users access it that performance is slow.

• You cannot find downtime to administer the database.

• No one has control over his own data, because it is centrally managed.

• The database is very sparse, because so much of the data is unrelated.

By connecting the databases instead, you can:

• Leverage work that has already been completed

• Synchronize the data

Note:

This example is not included with Essbase.

  To connect multiple databases:

1. Determine which data to connect. First, connect the Inventory database.

   • Replicate the Opening_Inventory and Ending_Inventory members from the Measures dimension of the Inventory database into the Measures dimension of the Sample.Basic database.

   • Do not replicate the Number_On_Hand, Number_Shipped, and Number_Returned members in the Measures dimension of the Inventory database to the Sample.Basic database.

   • Add a link to the Inventory database so that users can view these more-detailed measures, if necessary.

   • Create a partition containing data from the Payroll, Marketing, and Sales databases in the Sample.Basic database.

2. Choose the data source and data target.

   In the case of the Opening_Inventory and Ending_Inventory members, the Inventory database is the data source and the Sample.Basic database is the data target.

3. Decide which type of partition to use.

   Use a replicated partition for the Opening_Inventory and Ending_Inventory members because the network connection is slow.

4. Connect the Payroll, Marketing, and Sales databases. Perform steps step 1 through step 3 for each database.

5. Create the partitioned databases by performing the following tasks:

   • Edit existing data sources, rules files, calculation scripts, report scripts, and outlines

   • Create the partitions

   • If necessary, load data into the new partitions

Now that the Sample.Basic database is partitioned, users and DBAs see the following benefits:

• Easier maintenance, because DBAs can control the downtime of local databases.

• Access to more data; users can link to new databases.

• Higher-quality data, because the databases are now synchronized (they use the same data).

Case Study 3: Linking Two Databases

Assume that TBC has two main databases—Sample.Basic and TBC.Demo. Both databases have similar outlines, but TBC.Demo has two additional dimensions:

• Channel, which describes where a product is sold

• Package, which describes how the product is packaged

The DBA for Sample.Basic notices that more users are requesting that she add channel information to Sample.Basic. But, because she does not own the data for channel information, she is reluctant to do so. Instead, she decides to allow her users to link to the TBC.Demo database, which already contains this information.

Note:

This example is not shipped with Essbase.

  To link two databases:

1. Determine which data to link.

   The DBA decides to link the Product dimension of Sample.Basic to the Product dimension of TBC.Demo.

   Users can then drill across to TBC.Demo and view the Channel and Package information.

2. Choose the data source and the data target.

   Because users start at the Sample.Basic database, it is considered the data target. Because users move to TBC.Demo, it is considered the data source.

   Note:

   This setup is the opposite of replicated and transparent databases, in which users move from the data target to the data source.

3. Decide which type of partition to use.

   Use a linked partition because the databases have different dimensionality.

4. Create the partition:

   • Establish a link from the Product member of Sample.Basic to the Product dimension of TBC.Demo. Remember to map the extra dimensions from TBC.Demo—Channel and Product—to void in Sample.Basic. See Mapping Data Cubes with Extra Dimensions.

   • Set up a guest account on TBC.Demo that gives the users who connect from Sample.Basic permissions to access the Channel and Package dimensions. For a general discussion on creating accounts, see Granting Permissions to Users and Groups in Native Security Mode. To assign accounts to linked partitions, see Choosing a Partition Type and Choosing a Partition Type.

After the databases are linked, users and DBAs see the following benefits:

• Users have access to more data.

• The DBA for Sample.Basic need not maintain TBC.Demo; she needs only to check the link periodically to make sure that it still works.

• 转自 essbase abag

• Oracle Enterprise Performance Management Workspace, Fusion Edition User's Guide