Here’s a sampling of the results we have delivered:
HYPERION FINANCIAL MANAGEMENT RE-DESIGN
Multinational Insurance Provider
- Need to review current HFM application design to point out deficiencies hindering performance
- Need to reduce consolidation times and time spent on application maintenance
- Need to replace manual cash flow with more automated method
- Ensure business users and IT support have a functional understanding of the newly designed HFM application
- Need a trusted partner to guide them through re-design, testing, training, and implementation
CompIntelligence Solution and Methods:
- Provide an experienced Lead HFM developer
- Provide best practice recommendations for utilizing the enhanced functionality of HFM
- Hold design sessions with Finance users to gather all requirements necessary to validate application re-design
- Define testing, training, communication, and knowledge transfer strategies
- Provide proper documentation of technical and functional aspects of the application
- Successful implementation designed to be on-time and on-budget
- Insurance/Banking expertise
- Proven track record at this client
- HFM technical and functional expertise
- Vastly improved application performance
- Reduced time spent on application maintenance
- Application re-designed to be in alignment with client reporting requirements and best practices
- Client finance users now trained on new application
HYPERION PLANNING & FINANCIAL DATA QUALITY MANAGEMENT WITH ERPi
A Leader in Aviation Technology
- Boost efficiency while growing in new industries
- Cut maintenance costs of existing Excel-based planning and budgeting tool
- Eliminate data inconsistencies with actual data entered for forecasting
- Integrate siloed budgeting processes at cost center and product levels
- Shorten lengthy budget cycle
CompIntelligence Solution and Methods:
- Reviewed client issues and capabilities
- Developed detailed plan to update planning and budgeting process to meet needs
- Used ERPI/source adapters to import Oracle 11 GL, w/ flat files, Hyperion Enterprise and Oracle 12
- Drilled back from planning app to Oracle GL system
- Implemented modules built on Oracle Hyperion Planning, FDM and Oracle EPI
- Conducted employee training on use of system
- Seasoned finance professionals with 20 years average experience
- Implementing Hyperion products since 1990’s
- Multiple references from happy clients
- Trusted business and technology partner
- Proven track record
- Harmonized budgeting/forecasting across company
- Centralized/standardized data in single source
- Implemented project-centric budgeting solution
- Increased efficiency (automated consolidation, interfaces and currency translation)
- Eliminated dependency on Hyperion Enterprise
- Enabled Top-side adjustments at multiple levels
- Integrated actuals into planning tool for analysis
- Centralized production system maintained by IT
If you would like to discuss these and other case studies further, please contact a CompIntelligence Practice Leader by filling out the form to the right
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.
Table 33 lists the types of partitions that are supported in Essbase:
Table 33. Partition Types
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
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
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
Reduced network traffic
Reduced disk space
Increased calculation speed
Improved query speed
Invisible to end users
Access to databases with different dimensionality
Easier to recover
Less synchronization required
Ability to query data based on its attributes
Ability to use front-end tools that are not distributed OLAP-aware
Easy to perform frequent updates and calculations
Ability to update data at the data target
View data in a different context
Perform batch updates and simple aggregations
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
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.
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.
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
|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|
Data Sources and Data Targets
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.
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.
Substitution Variables in Partition Definitions
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:
This partition is incorrect:
For more information about these commands, see the Oracle Essbase Technical Reference.
Also see Working with Attributes.
Version: Both ends (the source and target) of the partition must be on the same release level of Essbase Server for these partition types:
Encoding: The application mode—Unicode mode or non-Unicode mode—of both ends of the partition must be the same for these partition types:
Version and Encoding Requirements
Understanding Essbase Partitioning
Use the information in this section to carefully design partitions before implementing them.
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.
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.
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 a Database
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.
Guidelines for Partitioning Data
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.
Create the required end users with the correct filters.
Create accounts for users at the data target.
See Managing Users and Groups in Native Security Mode.
Create read and write filters at the data target to determine what end users can view and update.
See User Management and Security.
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.
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 End-User 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.
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.
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.
Setting up Administrator Security
Security for 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.
Using Backup and Restore and Transaction Logging and Replay with Partitioned Databases
Partition Design Requirements
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
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.
Rules for 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.
Advantages 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.
Disadvantages of 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.
Performance Considerations for Replicated Partitions
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).
Because of the short-term nature of replication, replicated partitions and ports are rarely a problem.
Replicated Partitions and Port Usage
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
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.
Rules for 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.
Advantages 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
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.
Disadvantages of 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.
Performance Considerations for 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.
Calculating Transparent Partitions
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.
Performance Considerations for Transparent Partition Calculations
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 Member Formulas
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.
Transparent Partitions and Port Usage
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.
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.
Advantages 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.
Disadvantages of 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:
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.
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.
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.
Drill Across and Linked Partitions
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).
Linked Partitions and Port Usage
The following sections describe examples of partitioning a 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:
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.
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.
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.
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 1: Partitioning an Existing Database
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
This example is not included with Essbase.
To connect multiple databases:
Determine which data to connect. First, connect the Inventory database.