Designing Partitioned Applications

An Hyperion Essbase OLAP Server partitioned application can span multiple servers, processors, or computers. Partitioning your applications can help you:

Improve the scalability, reliability, availability, and performance of your databases
Reduce the size of your databases
Use your resources more efficiently

This chapter contains the following sections:

CAUTION: You must design partitions carefully. We strongly recommend that you read this chapter before creating partitions.

Introducing Hyperion Essbase Partitioning

Hyperion Essbase Partitioning is a collection of features that makes it easy to design and administer databases that span Hyperion Essbase applications or servers. You must license Hyperion Essbase Partitioning separately from Hyperion Essbase OLAP Server. You must license the Hyperion Essbase Partitioning option for every server that contains a database partition.

Partitioning can help you:

Synchronize the data in multiple partitioned databases. Hyperion Essbase tracks changes made to data values in a partition and provides tools for updating the data values in related partitions.
Synchronize the outlines of multiple partitioned databases. Hyperion Essbase tracks changes made to the outlines of partitioned databases and provides tools for updating related outlines.
Allow users to navigate 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, you can decide to:

Partition applications from the top down. Top-down partitioning allows you to split a database onto multiple processors, servers, or computers. Top-down partitioning can improve the scalability, reliability, and performance of your databases. To achieve the best results with top-down partitioning, create a separate application for each partitioned database.
Partition applications from the bottom up. Bottom-up partitioning allows you to manage the flow of data between multiple related databases. Bottom-up partitioning can improve the quality and accessibility of the data in your databases.
Partition database according to attribute values associated with base dimensions (a base dimension is a standard dimension associated with one or more attribute dimensions). Partitioning a base dimension according to its attributes enables the user to extract data based on the characteristics of a dimension such as flavor or size. For more information on attributes, see Working with Attributes

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. A single database can serve as both the data source for one partition and the data target for another. When you define a partition, you map cells in the data source to their counterparts in the data target.

Figure 6-1: Data Source and Data Target

A Hyperion Essbase database can contain many different partitions as well as data that is not shared with any other Hyperion Essbase database. You can define partitions between:

Different databases in different applications, as long as each database uses the same language (for example, German).
Different databases in different applications on different processors or computers, as long as each database uses the same language (for example, German).
Different databases in one application. This is not recommended, because you cannot reap the full benefits of partitioning your databases unless each database is in a separate application.

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

A single 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.

Figure 6-2: Data Shared at Multiple Targets

Overlapping Partitions

An overlapping partition occurs when similar data from two or more databases serve as the data source for a single 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 dimension East, the data for Boston mapped to database 3 from database 1 and database 2, overlap. This data overlap results in an overlapping partition.

Figure 6-3: Overlapping Partitions

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

What Is a Partition?

A partition is the piece of a database that is shared with another database. Partitions contain the following parts:

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 user operations.
Type of partition
A flag indicating whether the partition is replicated, transparent, or linked.
Shared areas
A definition of one or more areas shared between the data source and the data target. An area is a subcube of the database that is shared between databases. To share more than one non-contiguous 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 Hyperion 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. Hyperion 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.

Figure 6-4: Parts of a Partition

Workflow for Partitioning a Database

Here is the suggested workflow for partitioning a database.

Design the partitioned databases. See Determining Which Data to Partition and Deciding Which Type of Partition to Use.
Edit existing applications that use the individual databases.
- Edit the outlines and existing calc scripts.
- If necessary, edit the data sources, rules files, and report scripts.
- Determine whether you need to define additional security filters to control what users can view in a partitioned database. See Setting Up Security for Partitioned Databases.
Create the partitions. See Building and Maintaining Partitions
Load data into the partitions. Load the data into the new databases. See Introducing Data Loading
If necessary, calculate the new databases. See Introduction to Database Calculations
Test and maintain the partitions. Test your partitioned databases, including updates, data loads, calc scripts, report scripts, and database restructures. For more information on maintaining partitioned databases, see Building and Maintaining Partitions

When to Partition a Database

Review the following list of questions. If you find yourself answering yes to many of them, or answering yes to some that are very important to you, partitioning your databases may solve your problems.

Should the data be closer to the people who are using it? Is the network being stressed because users are accessing data that is far away?
Would a single failure be catastrophic? If everyone is using a single database for mission-critical purposes, what happens if the database goes down?
Does it take too long to perform calculations after new data is loaded? Would you like to speed it up by spreading the calculations across multiple processors or computers?
Do users want to see the data in different application contexts? Would you like to control how they navigate between databases?
Do you have separate, disconnected databases storing related information? Does the related information come from different sources? Are you having trouble synchronizing it?
Will you be adding many new organizational units? Would they benefit from having their own databases? Partitioned databases help you grow incrementally.
Are users having to wait as other users access the database?
Do you want to save disk space by giving users access to data stored in a remote location?
Should you reduce network traffic by replicating data in several locations?
Do you need to control database outlines from a central location?

When Not to Partition a Database

Sometimes, it does not make sense to partition a centralized database. Partitioning a database can require additional disk space, network bandwidth, and administrative overhead. Review the following list of questions. If you find yourself answering yes to many of them, or answering yes to some that are very important to you, you may not want to partition your database.

Do you have resource concerns? For example, are you unable to purchase more disk space or allow more network traffic?
Do you perform complex allocations where unit level values are derived from total values?
Are you required to keep all databases online at all times? This could 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 would exacerbate this problem, but using replicated partitions could help.
Are the databases in different languages? Hyperion Essbase can only partition databases if both databases use the same language, such as German.

Determining Which Data to Partition

When designing a partitioned database, you need to determine:

Which database should be the data source and which the data target? The database that "owns" the data should be the data source. Owning the data means that this is the database where the data is updated and where most of the detail data is stored.
Are some parts of the database accessed more frequently than others?
What data can you share among multiple sites?
How granular does the data need to be at each location?
How frequently is the data accessed, updated, or calculated?
What are your resources? How much disk space do you have? CPUs? Network resources?
How much data needs to be transferred over the network? How long does that take?
Where is the data stored? Is it in one location or in more than one location?
Where is the data accessed? Is it in one location or in more than one location?
Is there information in separate databases that should be accessed from a central location? How closely are groups of data related?

The answers to these questions determine which data to include in each partition. For examples, see Scenarios for Designing Partitioned Databases.

Note:

You cannot partition attribute dimensions.

Deciding Which Type of Partition to Use

You can create the following types of partitions:

A replicated partition is a copy of a portion of the data source that is stored in the data target. For more information, see Replicated Partitions.
A transparent partition allow 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, in another Hyperion Essbase database, or on another Hyperion Essbase server. For more information, see Transparent Partitions.
A linked partition sends users from a cell in one database to a cell in another database. This gives users a different perspective on the data. For more information, see Linked Partitions.

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.

In the Samppart and Sampeast applications shipped with Hyperion Essbase, for example, the database administrator 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 don't have to retrieve this data live from the corporate headquarters, their response times are faster and they have more control over the down times and administration of the local data. For a more complete description of the sample partitioned databases provided with Hyperion Essbase, see Scenario 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, Hyperion Essbase overwrites their changes when the database administrator updates the replicated partition.

The database administrator can prevent the data in the replicated portion of the data target from being updated. This setting (choose Settings from the Connect page of the Partition Wizard) takes precedence over access provided by security filters and is also honored by batch operations such as dataload and calc. By default, replicated partitions are not updateable. For information on how to set a partition as updateable or not, see Specifying the Partition Type and Connection Information.

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 even though the shared areas do not have to be identical. This means that you must tell Hyperion 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.
You cannot create a replicated partition on top of a transparent partition. In other words, none of the areas that you use as a replicated partition source can come from a transparent partition target.
Figure 6-5: Invalid Replicated Partition
The cells in the data target of a replicated partition cannot come from two different data sources; the cells in one partition must come from just one database. If you want 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 could replicate the cells in the Sampeast East database into a third database, such as the Sampwest West database.
You cannot use attribute dimension 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. Hence, an attempt to map attributes in replicated partitions results in an error message. However, you can use the WITHATTR command to replicate attribute data in the Areas tab of the Partition Wizard. For example:

For information on using Dynamic Time Series members in replicated partitions, see Using Dynamic Time Series Members in Partitions.

Advantages of Replicated Partitions

Replicated partitions can solve many database problems. Following are the advantages of using a replicated partition:

Replicated partitions can decrease network activity, because the data is now stored closer to the end users, in the data target. Decreased network activity results in improved retrieval times for the 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 a single database fails, only the users connected to that database are unable to access the information. It is still available at and can be retrieved from the other sites.
Local database administrators can control the down time of their local databases. For example, because users in the eastern region are accessing their own replicated data instead of the Company database, the database administrator can bring down the Company database without affecting the 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 just the eastern budget information, instead of accessing a larger company database containing budget information for all regions.

Disadvantages of Replicated Partitions

Replicated partitions are not always the ideal partition type. Following are the disadvantages of using a replicated partition:

You need more disk space, because you are storing the data in two or more locations.
The data must be refreshed regularly by the database administrator, so it is not up-to-the-minute.

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

Performance Considerations for Replicated Partitions

To improve the performance of replicated partitions, consider the following when replicating data.

Not replicating members that are dynamically calculated in the data source can greatly reduce replication time, because Hyperion Essbase must probe the outline to find dynamically calculated members and their children to determine how to perform the calculation. For more information on dynamically calculated members, see Dynamically Calculating Data Values
Not replicating derived data from the data source can greatly reduce replication time. Instead, try to 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 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, you must replicate it from the data source. For example, you cannot calculate derived data at the data source if the data:
- Requires data outside the replicated area to be calculated.
- Requires calc scripts from which you cannot extract just the portion to be calculated at the data target.
- Is being replicated onto a computer with little processing power, such as a laptop.
Partitioning along a dense dimension takes more time than partitioning along a sparse dimension. When Hyperion 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. For example, if the Market dimension were dense and you replicated the data in the East member, Hyperion Essbase would have to access every block in the database and then create each block at the data target during the replication operation.
You cannot replicate data into a member that is dynamically calculated at the data target. Dynamic Calc and Dynamic Calc And Store members do not contain any data until a user requests the data at run time. Hyperion Essbase does not load or replicate into Dynamic Calc and Dynamic Calc And Store members. Hyperion Essbase avoids sending replicated data for both dynamic dense and dynamic sparse members on the replication target, since this data is not stored on the data target. For more information on Dynamic Calc and Dynamic Calc And Store members, see Dynamically Calculating Data Values
Use the Application Manager or ESSCMD to replicate only the data values that have changed instead of the entire partition.

When to Use a Replicated Partition

Use a replicated partition when you want to:

Decrease network activity.
Decrease query response times.
Decrease calculation times.
Make it easier to recover from system failures.

Replicated Partitions and Port Usage

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

In a replicated 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 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 Wizard (partition user) to access the source database. 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.

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.

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

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

Figure 6-6: Transparent Partitions

For example, the database administrator at TBC could use a transparent partition to calculate each member of the Scenario dimension on a separate CPU. This reduces the elapsed time for the calculation, while still providing users with the same view of the data. For a more complete description of the partitioned Sample Basic database, see Scenario 1: Partitioning an Existing Database.

Rules for Transparent Partitions

Transparent partitions must follow these rules:

The shared transparent areas of the data source and data target outlines do not have to be identical, but you must be able to map the dimensions in them. This means that you must tell Hyperion 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. Exception: attribute associations should 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 would be incorrect.
You cannot use attribute dimensions or members to define a transparent partition. For example, associated with the Market dimension, the Market Type attribute dimension has members Urban, Suburban, and Rural. You cannot define a partition on Urban, Suburban, or Rural.
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.
Figure 6-7: Valid 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. This is because each cell in a database must be retrieved from only one location--either the local disk or a remote disk.
Figure 6-8: Invalid Transparent Partition
Carefully consider any formulas you assign to members in the data source and data target. For more information, see Transparent Partitions and Member Formulas.

For more information on using Dynamic Time Series members in transparent partitions, see Using Dynamic Time Series Members in Partitions.

Advantages of Transparent Partitions

Transparent partitions can solve many database problems. Following are the advantages of using a transparent partition:

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, Hyperion 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 the data target.

Disadvantages of Transparent Partitions

Transparent partitions are not always the ideal partition type. Following are the disadvantages of using a transparent partition:

Transparent partitions increase network activity, because Hyperion 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. This means that the network and data source must be available whenever users at the data source or the data target need them.
You can perform some administrative operations only on local data. For example, if you archive the data target, Hyperion Essbase archives just the data target and does not archive the data source. The following administrative operations work only on local data:
- CLEARDATA calculation command
- DATACOPY calculation command
- EXPORT command
- VALIDATE command
- BEGINARCHIVE and ENDARCHIVE commands
- Restructure operations in the Application Manager
When you perform a calculation on a transparent partition, Hyperion Essbase performs the calculation using the current values of the local data and transparent dependents. Hyperion Essbase does not recalculate the values of transparent dependents. To calculate all partitions:
1. Issue a CALC ALL command for each individual partition.
2. Perform a CALC ALL command at the top level using the new values for each partition.
  Hyperion 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 would not be accurate.
  
  For example, suppose that the data target outline contained a Market dimension with East, West, South, and Central members and the data source outline contained 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 and could not reflect any changes made to New York and New Jersey in the data source. To perform an accurate calculation, therefore, you must first calculate East in the data source and then calculate the data target.
  
  For more information on transparent calculations, see Calculating Transparent Partitions.
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. For more information, see Transparent Partitions and Member Formulas.

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

Performance Considerations for Transparent Partitions

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

Partitioning along dense dimensions in a transparent partition can greatly slow performance. This is 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, Hyperion 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 could 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.
Partitioning base dimensions can greatly slow performance.
For calculation-related performance considerations, see Guidelines for Transparent Calculations.

When to Use a Transparent Partition

Use a transparent partition when you want to:

Show users the latest version of the data.
Allow users at the data target to update data.
Decrease disk space.

Calculating Transparent Partitions

When you perform a calculation on a transparent partition, Hyperion Essbase performs the calculation using the current values of the local data and transparent dependents. When calculating local data that depends on remote data, Hyperion Essbase performs a bottom-up calculation. The bottom-up calculation can be done only when the calculator cache on the target database is used properly. For complete information on bottom-up calculations, see Understanding Bottom-Up Versus Top-Down Calculation. For information on the calculator cache, see Using the Calculator Cache.

Increasing the amount of memory assigned to the calculator cache greatly improves calculation performance with transparent partitions. When a calculation is started, a message in the server event log indicates whether or not 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. This, in turn, reduces the amount of network traffic that is generated by transferring blocks across the network. For information on estimating the size of the calculator cache, see Using the Calculator Cache.

Guidelines for Transparent Calculations

Calculating data on the data target can greatly slow performance in some cases. Keep in mind that:
- Hyperion Essbase must perform a top-down calculation on any portion of the data target that contains top-down member formulas. When the data target contains no top-down member formulas, it can perform a bottom-up calculation on the data target, which is much faster. When Hyperion Essbase performs the calculation on the data source, it can always perform a bottom-up calculation. For more information on top-down and bottom-up calculations, see Understanding Bottom-Up Versus Top-Down Calculation.
- Hyperion Essbase must retrieve each dependent data block across the network and then perform the calculation.
  Consider using:
- 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's retrieved. This reduces the batch processing time for batch calc. Hyperion Essbase performs the calculation only when users request it.
- A replicated layer between the low-level transparent data and high-level local data.
Keep the partition fully within the calculator cache area (see Using the Calculator Cache). Keeping a partition fully within the calculator cache 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. For more information, see Using the Calculator Cache.
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 Hyperion Essbase to use the top-down calculation method. For more information, see Understanding Bottom-Up Versus Top-Down Calculation.

Transparent Partitions and Member Formulas

If the data target and data source outlines are identical except for different member formulas, make sure that your partition definition will produce the desired calculation results.

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 contain member formulas, these formulas are calculated, thus affecting the calculated value of Market on the data target. These results may be different from how the Market member would be calculated from the North and South members on the data target, where these formulas may not exist.

Make sure that any formulas you assign to members in the data source and data target will produce the desired results.

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 causes the use of an additional port because different users (user1 and partition user) are connecting to the application.

Linked Partitions

A linked partition connects two different databases with a data cell. When you click the linked cell in the data source, you drill across to a second database, the data target, and view the data there. If you are using Hyperion Essbase 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 very different dimensions than the database from which you connected. To prevent users from seeing privileged data, you must establish security filters on both the data source and the data target. See Setting Up Security for Partitioned Databases.

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

Figure 6-9: Linked Partition

For example, if TBC grew into a large company, they would have several business units. Some data, such as profit and sales, exists in each business unit. TBC could store profit and sales in a centralized database so that the profit and sales for the entire company are available at a glance. The database administrator could link individual business unit databases to the corporate database. For an example of creating a linked partition, see Scenario 3: Linking Two Databases.

A user could:

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 would open a new spreadsheet.
Drill down in the new spreadsheet to more detailed data.

Figure 6-10: Source and Target for Linked Partition

Note:

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 is the opposite of replicated and transparent databases, where users move from the data target to the data source.

Advantages of Linked Partitions

Linked partitions allow users to navigate to databases that contain different dimensions. Following are the advantages of using a linked partition:

You can view data in a different context; that is, you can navigate between databases containing many different dimensions.
You do not have to 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 could link to the Profit cells in the databases of each business unit.
Performance may improve, because you're accessing the database directly and not through a data target.

Disadvantages of Linked Partitions

Linked partitions are not always the ideal partition type. Following are the disadvantages of using a linked partition:

You must create an account for users on each database or default access to the destination database (such as through a guest account). See Setting Up Security for Partitioned Databases.
Users must access the linked database using Hyperion Essbase Release 5-aware tools. If you have custom built your tools, you must extend them using the Hyperion Essbase Release 5 Grid API.

When to Use a Linked Partition

Use a linked partition when you want to connect databases with different dimensionality.

Linked Partitions and Port Usage

When accessing a linked partition, Hyperion 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, Hyperion Essbase looks for the linked partition default user name and password. If these defaults are not specified, user1 is requested to enter login information to access the source database. Port usage varies depending on the number of different user names being used to access the various source and target databases (and whether those databases are contained within the same or different servers).

Questions to Help You Choose a Partition Type

Table 6-1 should help you choose which type of partition to use.

*Table 6-1: Types of Partition*
If the answer to this question is yes...	Use this partition type...
Is decreasing network activity more important than increasing disk space?	Replicated
Is it acceptable for users to access data that's not up to the minute?	Replicated
Are you concerned about a single failure disrupting an entire OLAP application?	Replicated
Do you do batch updates and simple aggregations?	Replicated
Must you access the data using front-end tools that are not Distributed OLAP-aware?	Replicated or Transparent
Do you perform frequent updates and calculations?	Transparent
Does the user have to update the data at the data target?	Transparent
Is decreasing disk space more important than increasing network activity?	Transparent or Linked
Is it important that the user access the absolute latest version of the data?	Transparent or Linked
Does the user want to view data in different application contexts?	Linked
Do you want to map attributes associated with base dimensions?	Transparent or Linked

After you have answered the questions in Table 6-1, you can compare the partition types in the following table.


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

Using Attributes in Partitions

You can use attribute functions for partitioning on attribute values. But you cannot partition an attribute dimension. Use attribute values to partition a database when you want 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, the following partition is incorrect:

Figure 6-11: Incorrect Partitioning

Based on the previous example, the following example is correct:

Figure 6-12: Correct Partitioning

For more information about these commands, refer to the section on Calculation Commands in the Hyperion Essbase Quick Technical Reference.

For more information on attribute dimensions, see Working with Attributes

Setting Up Security for Partitioned Databases

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

Setting Up Security for Replicated Partitions

To set up security for users in a replicated partition, you should:

Create filters at the data target to determine what end users can view and update as local data, just as you would for a standard Hyperion Essbase database. See Managing Security at Global and User Levels
Determine whether the partition is updateable at the data target.
- If the partition is updateable, users can make changes to it, but these changes do not flow back to the data source and Hyperion Essbase overwrites them when the administrator updates the replicated partition.
- If the partition is not updateable, users cannot make changes to it at the data target.
  This setting overrides user filters that allow the user to update data. By default, replicated partitions are not updateable at the data target. See Specifying the Partition Type and Connection Information.

To set up security for the administrative account in a replicated partition, you should:

Create an administrative account at both the data source and the data target. Hyperion Essbase uses this administrative account to log into the data source to retrieve data when you update a replicated partition and to perform outline synchronization operations. See Setting the User Name and Password.
If necessary, set up read filters on the administrative account at the data source to determine which data Hyperion Essbase reads when replicating.
If necessary, set up write filters on the administrative account at the data target to determine which data Hyperion Essbase writes to when replicating.

Setting Up Security for Transparent Partitions

To set up security for users in a transparent partition, you should create filters at the data target to determine what end users can view and update as local data, just as you would for a standard Hyperion Essbase database. Remember that, unlike replicated partitions, end users can update transparent partitions unless you create filters preventing them from doing so. See Managing Security at Global and User Levels

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 could restrict write access on certain cells to avoid relying on administrators in the various regions to set up security correctly for each end user.

To set up security for the administrative account in a transparent partition, you should:

Create an administrative account at both the data source and the data target. Hyperion Essbase uses this administrative account to log into the data source to retrieve data and to perform outline synchronization operations. See Setting the User Name and Password.
Set up read filters on the administrative account at the data source to determine which data Hyperion Essbase retrieves for end users.
Set up write filters on the administrative account at the data source to determine which data Hyperion Essbase updates for end users.

Setting Up Security for Linked Partitions

Users accessing linked databases may need to connect to two or more databases. To facilitate drill across access you can:

Create accounts for each user on each database. For example, if Mary accesses data in a Company and an East database, create an account with the same login and password for Mary on both the Company and East databases. See Creating, Editing, and Copying Users and Groups.
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 privileges. Once you have created the account, use the guest account login and password as the default login when creating the linked partition. For more information on using default accounts in partitions, see Specifying the Partition Type and Connection Information.

For information on creating user accounts and filters, see Managing Security at Global and User Levels

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

Checks to see if the user has an account on the data target with the same name and password. If so, Hyperion Essbase logs the user in using that account.
Checks to see if you have specified a default account on the data target when you created your partition. If you did, Hyperion 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, Hyperion Essbase logs the user in using that account.

Scenarios for Designing Partitioned Databases

The following sections describe some basic ways to partition a database.

From the top down. Top-down partitioning involves splitting a large database into several smaller ones.
From the bottom up. Bottom-up partitioning involves connecting databases that contain related information.
By linking related database. Linking databases allows users to drill across to databases with a very different dimensionality and then drill down to more detailed data.

Scenario 1: Partitioning an Existing Database

Let's 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 was no longer feasible. The networks to the eastern region could not handle the large flow of data. Users were constantly waiting for data that they needed to make decisions. One day, the network went down and users at the eastern region couldn't access the data at all.

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 would stay at company headquarters, but the eastern region budget would move to the eastern region's database.

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

Note:

This scenario is based on the Samppart application, which contains the Company database, and the Sampeast application, which contains the East database. Both are shipped with Hyperion Essbase.

Here are the steps to take:

Determine which data to partition.
TBC's 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.


Members to partition	Data source	Data target	Reasons for choosing
Corp_Budget	Company	East	Because the company owns the corporate budget, it is the source.
Eastern Region, Actual	East	Company	Because the eastern region needs to update its actual and market information, it is the source.

Figure 6-13 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 6-13: Decentralizing a Database

Decide the type of partition to use.


Members	Partition type	Reasons
East	Transparent	The data target (Company) needs up-to-the-minute data
Corp_Budget	Transparent	The data target (East) needs up-to-the minute data
East Actual	Replication	The data target (Company) does not need up-to-the-minute data

Finally, create the partitioned databases by:
- Creating the new Sampeast application.
- Creating the new East database by cutting the Company outline and pasting it into the East outline. Then delete the extra members (that is, South, West, and Central) and promote East.
- If necessary, editing existing data sources, rules files, calc scripts, report scripts, and outlines.
- Creating the partitions.
- Loading data into the new partitions.

Now that the corporate database is partitioned, users and database administrators see the following benefits:

Faster response times, because they are competing with fewer users for the data and they are accessing the data locally.
Database administrators can control the down time of their local databases, making them easier to maintain.
Access to more data--now 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.

Scenario 2: Connecting Existing Related Databases

Let us 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 and you, the database administrator, want to synchronize related data. It is impractical to combine all of the databases into one database, because:

So many users access it that performance is slow.
You could not find a down time to administer the database.
No one has control over their 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's already been completed.
Synchronize the data.

So you decide to connect multiple databases.

Note:

This scenario is not shipped with Hyperion Essbase.

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 they need to.
- Create a partition containing data from the Payroll, Marketing, and Sales databases in the Sample Basic database.
Choose the data source and the 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.
Decide which type of partition to use.
You decide to use a replicated partition for the Opening_Inventory and Ending_Inventory members because the network connection is slow.
Connect the Payroll, Marketing, and Sales databases. Perform the tasks in steps 1-3 for each database.
Finally, create the partitioned databases by:
- Editing existing data sources, rules files, calc scripts, report scripts, and outlines
- Creating the partitions
- If necessary, loading data into the new partitions

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

Database administrators can control the down time of their local databases, making them easier to maintain.
Access to more data--now users can link to new databases.
Higher quality data, because the databases are now synchronized, that is, they use the same data.

Scenario 3: Linking Two Databases

Let us assume that TBC, the fictional soft drink company upon which the Sample Basic database is based, has two main databases the Sample Basic database and TBC Demo. Both databases have similar outlines, but TBC Demo has two additional dimensions: Channel, which describes where a product is sold, and Package, which describes how the product is packaged.

The database administrator for the Sample Basic database notices that more and more users are requesting that she add channel information to the Sample Basic database. But, since she doesn't own the data for channel information, she is reluctant to do so. She decides instead to allow her users to link to the TBC Demo database which already contains this information.

Note:

This scenario is not shipped with Hyperion Essbase.

Here are the steps to take:

Determine which data to link.
The database administrator decides to link the Product dimension of the Sample Basic database to the Product dimension of TBC Demo. Users can then drill across to TBC Demo and view the Channel and Package information.
Choose the data source and the data target. Because users start at the Sample Basic database, it is considered the data target. Likewise, because users move to TBC Demo, it is considered the data source.

Note: This is the opposite of replicated and transparent databases, where users move from the data target to the data source.
Decide on the type of partition to use.

Members Partition type Reasons

Product Linked The database administrator wants to link from the Product dimension.
Finally, create the partition by:
- Establishing a link from the Product member of the Sample Basic database to the Product dimension of the TBC Demo database. Remember to map the extra dimensions from TBC Demo, that is, Channel and Product, to void in the Sample Basic database. For more information, see Mapping Data Source Members to Data Target Members.
- Set up a guest account on TBC Demo that gives the users who connect from the Sample Basic database privileges to access the Channel and Package dimensions. For more information on creating accounts, see Managing Security at the User and Group Layer. For more information on assigning those accounts to linked partitions, see Specifying the Partition Type and Connection Information.

Members	Partition type	Reasons
Product	Linked	The database administrator wants to link from the Product dimension.

Now that the databases are linked, users and database administrators see the following benefits:

Users have access to more data than before.
The database administrator for the Sample Basic database does not need to maintain the TBC Demo database, all she needs to do is check the link periodically to make sure that it still works.