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

With a write-back partition, you can update data on-the-fly in the target block storage database while the data in the source aggregate storage database remains unchanged. Creating a write-back partition potentially decreases calculation time and reduces database size.

When creating write-back partitions, follow these guidelines:

Users query and write-back to the block storage database. Queries are processed by the block storage database or transparently by the aggregate storage database.

  To create an aggregate storage and block storage write-back partition, use a tool:

Tool

Topic

Location

Administration Services

Aggregate Storage Partition Wizard

Creating Partitions

Oracle Essbase Administration Services Online Help

MaxL

create database

create partition

Oracle Essbase Technical Reference

You need Database Manager permissions to create a partitioned application.

Figure 158, Transparent Partition Used for Analyzing Variance Between Forecast and Actual Data illustrates using a transparent partition for analyzing the variance between forecast and actual data:

Figure 158. Transparent Partition Used for Analyzing Variance Between Forecast and Actual Data

This image shows a transparent partition that is used to analyze the variance between forecast and actual data.

The following procedure is based on the aggregate storage sample database (ASOsamp.Sample), and uses the Administration Services Aggregate Storage Partition Wizard (see the Oracle Essbase Administration Services Online Help).

  To create a write-back partition:

  1. Select the ASOsamp.Sample database, which contains the actual data for the current year and for previous years.

    See Figure 159, ASOsamp.Sample Aggregate Storage Database Outline.

    Figure 159. ASOsamp.Sample Aggregate Storage Database Outline

    This image shows an outline in which the Years dimension contains current and previous year data.
  2. Create a block storage database containing the following subset of dimensions from ASOsamp.Sample: Measures, Years, Time, Products, Stores, and Geography.

  3. Edit the Years dimension to add the following members to the block storage database outline:

    • A member called Next Year, which will contain the forecast data.

    • A member called Forecast Variance. Add a formula to this member to calculate the variance between actual and forecast data.

      Figure 160. Block Storage Database Outline Showing Years Dimension Members for Calculating Variance Between Actual and Forecast Data

      This image shows a block storage database outline, as described in the text preceding the image.
  4. Delete the following member formulas:

    • Measures dimension member formulas on Avg Units/Transaction and % of Total

    • Years dimension member formulas on Variance and Variance%

    • Time dimension member formulas under QTD and YTD

    These formulas are expressions written in MDX that are copied from the aggregate storage database. MDX formula expressions cannot be interpreted in block storage databases.

  5. Link the databases with a transparent partition on the Years dimension, with the block storage database (forecast data) as the target and the aggregate storage database (actual data) as the source.

    Do not include the write-back members (Forecast and Variance) in the partitioned area.

    Note:

    When using the Administration Services Aggregate Storage Partition wizard, this step is automatic. The databases are automatically partitioned on the Years dimension because you selected the Years dimension in step 3. The write-back members are not included in the partitioned area.

    You input forecast values into the block storage database write-back members. Because the added members are outside the partitioned area, you can write to them and then calculate data and generate reports based on updated data. The transparent partition provides a seamless view of both databases.