Extracting Financial Management Data to an Essbase Replicated Partition Database Target

In This Section:

About the Essbase Replicated Partition Database Target

Overview of Extracting Data into an Essbase Replicated Partition Database

About Extracting Data into an Essbase Replicated Partition Database

The information in this chapter applies to Essbase replicated partition target databases.

About the Essbase Replicated Partition Database Target

With an Essbase replicated partition database bridge target, Financial Management data is copied from Data Synchronization Server (the source of the replicated partition) to the Essbase database (the target of the replicated partition) through the replicated partition.

When creating a bridge with an Essbase replicated partition database as the bridge target type, note these changes in the bridge window: On the Extract tab, the Table Name field and the Extract Metadata group, which apply only to an RDBMS bridge target, are not displayed.

See Determining the Type of Bridge Target Database.

Overview of Extracting Data into an Essbase Replicated Partition Database

  To extract Analytics Link data to an Essbase replicated partition database:

  1. Open a bridge that was created with Essbase as the bridge target database and Replicated Partition as the connection type.

    See Creating a Bridge and Overview of Creating an Essbase Bridge Target.

  2. On the Extract tab, under Status, verify that Essbase Server, Essbase Application, and Essbase Database are set to Available and Analytics Link Data is set to On.

    See Checking Data Store Status.

  3. Under Extract Data:

    1. Select Default or a user-defined region from the Region list.

      See Adding a User-Defined Financial Management Application Data Region.

    2. For Duplicate, select a consolidation operator that Analytics Link should use when consolidating the value of duplicate data rows.

      Duplicate member consolidation operators:

      • SUM

      • LAST

      • FIRST

      See About Duplicate Data Row Consolidation Operators.

  4. Click Validate and, optionally, select Perform a Full Validation of the Region.

    See Validating Data Regions.

  5. Click Extract Data.

    During the extract process, the name of the region for which data is being extracted, the time the extract process started, and a progress bar appear at the bottom of the Extract tab. When the extract process is finished, the status is set to “Completed.

  6. To extract data from another region, repeat the extract process, starting with step 3.

About Extracting Data into an Essbase Replicated Partition Database

Each Essbase dimension is tagged as sparse or dense. Essbase stores data values in data blocks. Essbase creates a data block for each unique combination of sparse dimension members (providing that at least one data value exists for the combination). Each data block contains all the dense dimension member values for its unique combination of sparse dimension members.

The following example illustrates how Analytics Link extracts data into a replicated partition in an existing Essbase database. Assume the existing Essbase database has the following dimensions, dimensionality, and members:

ESSDim1 (Sparse)
   X
   Y
   Z
ESSDim2 (Dense)
   A
   B
   C
EssDim3 (Dense)
   1
   2
   3

Assume that the following data exists in the Essbase database:

ESSDim1   ESSDim2   ESSDim3   Value
X         A         3         7
X         B         2         7
X         C         1         7
Y         A         1         7
Y         B         2         7
Y         C         3         7
Z         A         1         7

A data value is stored in one cell in the database. To refer to a specific data value in a multidimensional database, you specify its member on each dimension. The data value can be expressed using the cross-dimensional operator (->).

Figure 6, Essbase Blocks represents the three blocks that Essbase creates for each combination of sparse members.

Figure 6. Essbase Blocks

This image represents Essbase blocks, as described in the text following the image.
  • For the block whose header is member X, each of the following intersections has a value:

    • X -> A -> 3 = 7

    • X -> B -> 2 = 7

    • X -> C -> 1 = 7

  • For the block whose header is member Y, each of the following intersections has a value:

    • Y -> A -> 1 = 7

    • Y -> B -> 2 = 7

    • Y -> C -> 3 = 7

  • For the block whose header is member Z, only one intersection has a value: Z -> A -> 1 = 7.

The remaining cells in the blocks are empty.

Now assume that the following data set was extracted from the Data Synchronization Server database, based on the defined region and all member mapping transformations:

ESSDim1   ESSDim2   ESSDim3   Value
X         A         2         9
X         A         1         9
Y         B         2         9

This data set, which is called the Data Synchronization Server extract data set, is ready for replication into the Essbase database.

Analytics Link process for copying data, based on the Data Synchronization Server extract data set, into an Essbase database through a replicated partition:

  1. The sparse and dense dimensionality of the existing Essbase dimensions is taken directly from Essbase.

    • ESSDim1—Sparse

    • ESSDim2—Dense

    • ESSDim3—Dense

    Note:

    If the dimensionality of the Essbase target database changes after the creation of the database from the bridge, you must synchronize the changes with the Data Synchronization Server database by clicking Synchronize Mapping on the Target Design Grid tab before extracting data. See the note at the end of the following topic, About Essbase Sparse and Dense Dimensionality.

  2. From the Data Synchronization Server extract data set, Analytics Link selects the coordinates from all dense dimensions:

    • ESSDim2—Members A and B

    • ESSDim3—Members 1 and 2

    This data set is called the Data Synchronization Server extract data set dense area.

  3. Analytics Link groups the Data Synchronization Server extract data set into blocks with the same logic that Essbase uses, as shown in Figure 7, Data Synchronization Server Extract Data Set. The Data Synchronization Server extract data set dense area is shaded.

    Figure 7. Data Synchronization Server Extract Data Set

    This image represents the Essbase blocks that were described previously; and the blocks that represent the Data Synchronization Server extract data set, as described in the text following the image.
    • For the block whose header is member X, each of the following intersections has a value:

      • X -> A -> 1 = 9

      • X -> A -> 2 = 9

    • For the block whose header is member Y, only one intersection has a value: Y -> B -> 2 = 9

    • There is no corresponding block whose header is member Z.

  4. For each Data Synchronization Server extract data set block that has at least one row of data:

    • In the Essbase block that has the same sparse coordinates as the Data Synchronization Server extract data set block, Analytics Link clears the data that corresponds to the Data Synchronization Server extract data set dense area.

      As shown in Figure 8, Essbase Blocks with Data Cleared by Analytics Link, cleared values are shown in strikethrough text:

      Figure 8. Essbase Blocks with Data Cleared by Analytics Link

      This image represents the Essbase and Data Synchronization Server extract data set blocks that were described previously; and the Essbase blocks in which data has been cleared in the cells corresponding to the Data Synchronization Server extract data set, as described in the text following the image.
      • For the block whose header is member X, the data has been cleared in each of the following intersections:

        • X -> A -> 1

        • X -> A -> 2

        • X -> B -> 1

        • X -> B -> 2

      • For the block whose header is member Y, data has been cleared in each of the following intersections:

        • Y -> A -> 1

        • Y -> A -> 2

        • Y -> B -> 1

        • Y -> B -> 2

      • Because there is no block in the Data Synchronization Server extract data set that corresponds to the Essbase block whose header is member Z, Analytics Link does not touch that Essbase block.

    • Analytics Link then copies the Data Synchronization Server extract data set into Essbase, as shown in Figure 9, Essbase Blocks with Copied Data Synchronization Server Extract Data Set:

      Figure 9. Essbase Blocks with Copied Data Synchronization Server Extract Data Set

      This image represents the existing Essbase blocks, the Data Synchronization Server extract data set blocks, and the data that Analytics Link clears, each of which were previously described; and the Essbase blocks after the Data Synchronization Server extract data set has been copied, as described in the text following the image.
      • For the block whose header is member X, each of the following intersections has a value:

        • X -> A -> 1 = 9 (value extracted from Data Synchronization Server)

        • X -> A -> 2 = 9 (value extracted from Data Synchronization Server)

        • X -> A -> 3 = 7 (existing value in Essbase)

        • X -> C -> 1 = 7 (existing value in Essbase)

      • For the block whose header is member Y, each of the following intersections has a value:

        • Y -> B -> 2 = 9 (extracted from Data Synchronization Server)

        • X -> C -> 3 = 7 (existing value in Essbase)

    • For the block whose header is member Z, only one intersection has a value: Z -> A -> 1 = 7 (existing value in Essbase).

Caution!

Assume that you have extracted a region and the Financial Management application data is replicated into the Essbase database. Now, assume that the Financial Management application data for the region is cleared and the data changes are synchronized with the Data Synchronization Server database. After extracting the region into the Essbase database, the DSS Extract Data Set is empty in this case and no block in the Essbase database is cleared. Therefore, all of the data from the previous extract data process remains in Essbase, which is not what the user expects. The user expects to see in Essbase the same values as in the Financial Management application that are #MISSING. Each time before you extract data for a region, Oracle recommends that you manually clear the Essbase database region that should get the Financial Management application data.

Note:

The same kind of problem explained above occurs if you extract data multiple times and the DSS Extract Dataset Dense Area of the last data extraction is different from the DSS Extract Dataset Dense Area of the previous data extraction on the same region.