The following sections describe examples of partitioning a database:
• Case Study 1: Partitioning an Existing Database
• Case Study 2: Connecting Existing Related Databases
• Case Study 3: Linking Two Databases Case Study 1: Partitioning an Existing Database
Assume that TBC, the fictional soft drink company upon which the Sample Basic database is based, started out with a centralized database. As the eastern region grew, however, this solution was no longer feasible. The networks to the eastern region could not handle the large 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 could not 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 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 application, which contains the Company database, and the Sampeast application, which contains the East database. Both are shipped with Essbase.
The following illustration 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.
To create a partition based on this example:
1. Determine which data to partition.
The Sample Basic database contains five standard dimensions—Year, Measures, Product, Market, and Scenario.
• Partition the database along the East member of the Market
dimension to give the eastern region more control over the contents of its database.
• Partition the database along the Actual and Corp_Budget members of the Scenario dimension.
2. Choose the data source and the data target.
• For Corp_Budget, use Company as source and East as Target;
because the company owns the corporate budget, it is the source.
• For Eastern Region and Actual, East is the source and Company is the target, because the eastern region needs to update its market and actual information.
3. Decide on the type of partition to use.
• For East, use transparent because the data target (Company) needs up-to-the-minute data.
• For Corp_Budget, use transparent because the data target (East) needs up-to-the minute data.
• For East Actual, use replication because the data target (Company) does not need up-to-the-minute data.
4. Finally, create the partitioned databases by performing the following tasks.
• 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, calculation scripts, report scripts, and outlines.
• Creating the partitions.
• Loading 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.
• DBAs 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.
Case Study 2: Connecting Existing Related Databases
Assume that TBC has several databases, such as Inventory, Payroll, Marketing, and Sales.
Users viewing the Sample Basic database want to share data with and navigate to those other databases and 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 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 reap the following benefits:
• Leverage work that has already been completed.
• Synchronize the data.
So you decide to connect multiple databases.
Note:
This example is not shipped with Essbase.
1. Determine which data to connect. First, connect the Inventory database.
• Replicate the Opening_Inventory and Ending_Inventory members from the Measures dimension of the Inventory database into the Measures dimension of the Sample Basic database.
• Do not replicate the Number_On_Hand, Number_Shipped, and Number_Returned members in the Measures dimension of the Inventory database to the Sample Basic database.
• Add a link to the Inventory database so that users can view these more detailed measures if they need to.
• Create a partition containing data from the Payroll, Marketing, and Sales databases in the Sample Basic database.
2. 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.
3. Decide on the type of partition to use.
Use a replicated partition for the Opening_Inventory and Ending_Inventory members because the network connection is slow.
4. Connect the Payroll, Marketing, and Sales databases. Perform the tasks in 1 through 3 for each database.
5. Finally, create the partitioned databases by performing the following tasks:
• Editing existing data sources, rules files, calculation 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 DBAs see the following benefits:
• DBAs 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.
Case Study 3: Linking Two Databases
Assume that TBC, the fictional soft drink company, 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 DBA 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 does not 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 example is not shipped with Essbase.
Perform the following steps:
1. Determine which data to link.
The DBA 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.
2. 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 setup is the opposite of replicated and transparent databases, where users move from the data target to the data source.
3. Decide on the type of partition to use.
Use a linked partition because the databases have different dimensionality.
4. Finally, create the partition:
• Establish 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, Channel and Product, to void in the Sample Basic database. For more information, see Mapping Data Cubes with Extra Dimensions.
• Set up a guest account on TBC Demo that gives the users who connect from the Sample Basic database permissions to access the Channel and Package dimensions. For a general discussion on creating accounts, see Granting Permissions to Users and Groups in Native Security Mode. For directions on how to assign accounts to linked partitions, see Choosing a Partition Type and Choosing a Partition Type.
After the databases are linked, users and DBAs see the following benefits:
• Users have access to more data than before.
• The DBA 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.
If you see this message, your browser either has disabled or does not support JavaScript.
To use the full features of this help system, such as searching and the table of contents, your browser must have JavaScript support enabled. If your browser supports JavaScript, it provides settings that enable or disable JavaScript. When JavaScript is disabled, you can view only the content of the help topic, which follows this
message.%%noscript_msg%%