Extracting Financial Management Data and Metadata to an RDBMS Bridge Target

In This Section:

About the RDBMS Bridge Target

Extracting Financial Management Data to an RDBMS Bridge Target

About Duplicate Data Row Consolidation Operators

About the Use Default Entity Parent Extract Data Option

Extracting Financial Management Metadata to an RDBMS Bridge Target

Synchronizing Mapping Data Changes for RDBMS Bridge Targets

The information in this chapter applies to RDBMS target databases.

About the RDBMS Bridge Target

Analytics Link enables you to quickly extract Financial Management data and metadata directly from the Data Synchronization Server on demand for use in a relational database.

The relational database, which is defined as the Data Store, must already exist; Analytics Link does not create one. Extracted data and metadata are stored in the Data Store. Region definitions, which apply to extracting data, are stored in the Analytics Link repository. See Data Store.

Note:

You should create a dedicated database for the Data Store, if you plan to extract Analytics Link data and metadata.

When creating a bridge with RDBMS as the bridge target type, note these changes in the bridge window:

  • On the Summary tab, the Create Bridge Application button and Essbase Settings group are not displayed.

  • The Essbase tab is not displayed.

See Determining the Type of Bridge Target Database.

Extracting Financial Management Data to an RDBMS Bridge Target

Regions are used to extract data to an RDBMS database. Creating user-defined regions for data extraction is useful for extracting only the data that is required by the user. See Determining the Data Region of the Financial Management Application.

Only one region can be extracted at a time. Extracting data is an asynchronous process; the next extract can be preformed even if the previous extract has not finished. The extracted data is stored in the Data Store, in a table with a name that you specify.

The data extraction table columns:

  • A column for each value in the Target Dimensions column in the Target Design Grid tab of the bridge.

  • A measures dimension column, named MEASURES, which Analytics Link automatically creates.

  • EXTRACTDATE column, which stores the date on which the extraction was performed.

The rules that regulate usage of the data movement:

  • The structure of the source data is transformed by the defined mappings.

    Note:

    The Matrix Mapping transformation type is supported only for Essbase replicated partition or RDBMS database bridge targets.

  • The Entity dimension is represented by two columns: one for Entity member and one for the Entity Parent. The name of the Entity Parent column is EntityTargetDimName_PARENTID. If the Entity dimension uses the Matrix Mapping. transformation type, the Entity Parent column is not automatically created in the target database table.

  • When the Matrix Mapping transformation type is used with the Financial Management Entity dimension, the value of Add Parent to Entity Member database property is ignored (as if it is set to Off). The parent name is not be added to the Entity member name in the extracted table column. Instead, the mapping table template for the Matrix Mapping transformation type includes a separate column named SRC_PARENT for the Entity parent, so that the Entity parent can be extracted to its own column in the extracted table by the specific definition of matrix mapping. To get the value of the Entity parent, use the =parentId expression in the destination column of the matrix mapping table.

  • If the data extract table does not exist, Analytics Link creates the table.

  • If the data extract table already exists, the extracted data is appended to existing table.

  • If the user wants to define the extracted data table name or column names with special characters or save the case of the name:

    • On Oracle RDBMS, enclose the string in inverted commas (“string”)

    • On Microsoft SQL Server, you do not have to do anything

  To extract Analytics Link data to an RDBMS database:

  1. Open a bridge that was created with RDBMS as the bridge target database.

    See Creating a Bridge.

  2. On the Target Design Grid tab, if you make changes to the mapping grid or mapping tables, you must click Save Grid and then click Synchronize Mappings before proceeding with the extraction process.

    See Synchronizing Mapping Data Changes for RDBMS Bridge Targets.

  3. On the Extract tab, under Status, verify that Data Store is set to Available and Analytics Link Data is set to On.

    See Checking Data Store Status.

  4. 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. Enter a Table Name.

      Analytics Link creates the table in the Data Store if the table does not exist. If the table exists, data is appended to the table.

    3. 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. Define the Use Entity Default Parent option.

      See About the Use Default Entity Parent Extract Data Option.

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

    See Validating Data Regions.

  6. 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.

  7. To extract data from another region, repeat the extract process, starting with step 4.

Figure 10. Extract tab: Extracting Data to an RDBMS Database

This image shows the Extract tab, as described in the procedure proceeding the image.

About Duplicate Data Row Consolidation Operators

You can use the following consolidation operators to determine the value of duplicate data rows in the extracted data set:

  • SUM—The values of all duplicate data rows in the extracted data set are summed.

  • LAST—The value of the last of the duplicate data rows in the extracted data set is used.

  • FIRST—The value of the first of the duplicate data rows in the extracted data set is used.

About the Use Default Entity Parent Extract Data Option

Use Default Entity Parent is an option on the Extract tab.

If the Use Default Entity Parent option is selected and data for Entity Parent-independent Value dimension members exist in the region that is used for data extraction, Analytics Link extracts this data for only the entity Default Parent.

If Use Default Entity Parent is not selected and data for Entity Parent-independent Value dimension members exist in the region that is used for data extraction, Analytics Link extracts this data for all entity parents that are defined in the region.

Extracting Financial Management Metadata to an RDBMS Bridge Target

You can extract Analytics Link metadata only after the data is synchronized. Tables in the Data Store are dropped and recreated each time you extract metadata.

  To extract Analytics Link metadata to an RDBMS database:

  1. On the Extract tab, under Status, verify that Data Store is set to Available and Analytics Link Data is set to On.

    See Checking Data Store Status.

  2. Under Extract Metadata, select the hierarchy option that you want Analytics Link to use when creating the metadata tables:

  3. Click Validate.

    See Validating Data Regions.

  4. Click Extract Metadata.

Parent-Child Columns Metadata Extraction Type

When extracting metadata using the Parent-Child Columns option, Analytics Link creates a table for each dimension named bridgeName_dimensionName_PCD. For example, the parent-child table for the Custom 1 dimension in a bridge named RDBMS is RDBMS_Custom1_PCD.

Parent-child table columns:

Column NameColumn TypeDescription
IDNumericFinancial Management member internal ID
LABELTextFinancial Management member name
DESCRIPTIONTextFinancial Management member description[1]
PARENT_IDNumericFinancial Management parent member internal ID[2]
PARENT_LABELTextFinancial Management parent member name[2]
HIERARCHYTextHierarchy name

1 If a member does not have a description, the value of DESCRIPTION is Null.

2 If a member does not have a parent, the value of Parent_ID and Parent_LABEL is Null.

Fixed Levels Columns Metadata Extraction Type

When extracting metadata using the Fixed Levels Columns option, Analytics Link creates tables named bridgeName_dimensionName_LD. For example, the fixed levels table for the Custom 1 dimension in a bridge named RDBMS is RDBMS_Custom1_LD.

Fixed Levels table columns for each hierarchy level:

Column NameColumn TypeDescription
dimensionName_KEYTextContains the lowest level for the row
Ln_IDNumericFinancial Management member internal ID
Ln_LABELTextFinancial Management member name
Ln_DESCRIPTIONTextFinancial Management member description[1]

1 If a member does not have a description, the value of DESCRIPTION is Null.

  • Levels are numbered top-down, with the top level designated as L0. All members that have no parent are considered L0. The number of levels is the length of the longest branch.

  • Bottom-level columns of short branches are filled with Nulls.

  • If a member has multiple parents, a row is included for each parent.

  • If a member belongs to different levels in different hierarchies, the maximal level is considered a member level.

OBI Parent-Child Relationship Metadata Extraction Type

By extracting metadata using the OBI parent-child relationship extraction type, you can create hierarchies based on relational tables in Oracle Business Intelligence Enterprise Edition.

In an OBI parent-child relationship, the distance of one member from another member is the number of parent-child hierarchical levels between the member and an ancestor or descendant. For example, the number of parent-child hierarchical levels between a member and its parent is 1.

When extracting metadata using the OBI Parent-Child Relationship option, Analytics Link creates tables named bridgeName_dimensionName_PCDR. For example, the OBI parent-child relationship table for the Custom 1 dimension in a bridge named RDBMS is named RDBMS_Custom1_PCDR.

Table 50 describes the columns in an OBI parent-child relationship table:

Table 50. OBI Parent-Child Relationship Table

Column NameColumn TypeDescription
IDNumericFinancial Management member internal ID
MEMBERTextFinancial Management member name
ANCESTOR_IDText

Financial Management member ancestor internal ID

The ancestor may be the parent of the member or a higher-level ancestor.

ANCESTORTextFinancial Management member ancestor name
DISTANCENumericNumber of parent-child hierarchy levels from the member to the ancestor
ISLEAFNumeric

Indicates whether the Financial Management member is a leaf, or base, member

1=TRUE

0=FALSE

For example, consider the following hierarchy, in which Andrew is the topmost member; Barbara and Carlos are children of Andrew; Dawn and Emre are children of Barbara; and Carlos does not have children.

Andrew
   Carlos
   Barbara
      Dawn
      Emre

In the OBI parent-child relationship table shown in Table 51, Example: OBI Parent-Child Relationship Table, the distance between Dawn and Andrew, and Emre and Andrew, is 2, because Dawn and Emre are children of Barbara, who is the child of Andrew. All other relationships are 1, because they are between a parent and child.

Table 51. Example: OBI Parent-Child Relationship Table

MEMBERANCESTORDISTANCEISLEAF
Andrewnull10
BarbaraAndrew10
CarlosAndrew11
DawnBarbara11
DawnAndrew21
EmreBarbara11
EmreAndrew21

Synchronizing Mapping Data Changes for RDBMS Bridge Targets

On the Target Design Grid tab, the Synchronize Mappings button is mostly used when the bridge target is an RDBMS database.

In extracting data into the target RDBMS database, the mapping grid definition on the Target Design Grid tab and mapping table data, if specified, are not saved in the Analytics Link repository and are not updated in the Data Synchronization Server database. Therefore, if you make changes to the mapping grid definition or mapping table data, you must explicitly save the mapping grid definition and synchronize the mapping table data changes in the Analytics Link repository and Data Synchronization Server database before extracting data into the target RDBMS database. Click Save Grid and then click Synchronize Mapping.