Case Study 1: Partitioning an Existing Database

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

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

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

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

Figure 58, 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 58. Data Flow from Data Source to Data Target

This image illustrates the flow of data from the data source to the data target, as described in the text preceding the image.

  To create a partition based on this example:

  1. Determine which data to partition.

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

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

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

  2. Choose the data source and the data target.

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

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

  3. Decide which type of partition to use.

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

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

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

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

    • Create the Sampeast application.

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

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

    • Create the partitions.

    • Load data into the new partitions.

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