TIBCO EBX®
Add-on for Oracle Hyperion EPM Documentation > User Guide
Navigation modeAdd-on for Oracle Hyperion EPM Documentation > User Guide

Configuring data mapping

Configuration Overview

The data domain called 'Administrative data' is located in dataset 'Oracle Hyperion EPM Mapping'. It encompasses all the information required to define data mapping between the EBX® and Hyperion data structures.

This allows data mapping rules to be readily adapted depending on how EBX® data structures are designed and which XML data formats need to be integrated.

The data mapping is defined in the context of each Hyperion application, such as HFM, or Essbase.

An example of data mapping values is highlighted in appendix.

It is possible to manage different versions of data mapping by using datasets. When the import and export procedures are executed, it is possible to select a dataset where the data mapping is retrieved by the add-on.

EBX® repository data structure

The descriptions below provide an overview of the table structure in the repository.

MDM table

Table

Field

Definition

MDM table

Name

Name of the table in EBX®

For example, "Account", "AccountHierarchy", "AccountDescription"

Path

Absolute path of the table starting with /root in the EBX® repository data structure

For example, /root/Hyperion/Financial/Account

MDM field

Table

Field

Definition

MDM field

MDM table

Name of its containing table in EBX®

For example, "Account"

Field name

Field name in the EBX® table

For example, "Name"

Type

Data type

For example, String, Boolean.

Path

Relative path of the field relative to the EBX® table path

For example, /Name

Is filtering

No: the field is used when exporting data

Yes: the field is ignored when exporting data

Default value

This property is only used when the 'Type' field is set to Boolean and allows you to specify a default value for the field. This value is used during import, update and creation.

If a value is not declared the field is considered undefined and the system automatically gets the default value declared in the data model.

Path

Table

Field

Definition

Path

Path

Path in the EBX® repository data structure. Starts with either '/root' for an absolute path, or '/' for a relative path

Example:

/root/Hyperion/Financial/Account

/CalcAttribute

Mapping definition

All the tables described below are located in the group 'Mapping' in the Administrative Data domain. To complete these tables it is recommended to have mastered XML data structure.

Application

Table

Field

Definition

Application

Name

Name of Hyperion application available when importing and exporting data.

For example HFM, Essbase, Planning, EPMA.

The list of possible applications is retrieved from the 'Application' table declared in the 'TIBCO EBX™ Add-on for Oracle Hyperion EPM' data space, located in EBX® 'Administration' tab.

Has version

Allow for identifying if the application has version for import/export procedure or not. If it has version, the import/export procedure will process the version in table 'Version' as the tag 'Schema_version' on pivot XML file

Mapping table

Table

Field

Definition

Mapping table

Application

Application for which the mapping is defined

For example, HFM

MDM table

Table name in the EBX® repository

XML path

Relative path in the XML file for the table

For example, /MEMBERS/MEMBER

Dimension

Dimension on which the table is mapped. The dimension description gives its XML name (see below Table Dimension)

For example, 'Account'

The list of available dimensions depends on the selected application.

XML path in Planning

Relative path in the PLANNING XML file for the table.

For example, /Member[@name="*"@comment="*"]

Table type

Enumeration value: Member, Node, Description, Reference

Member: dimension. Eg. "Account"

Node: hierarchy table. Eg. "AccountHierarchy"

Description: description table. Eg. "AccountDescription"

Reference: tables in group Financial/Reference. Eg. 'ConsolidationAccountType', 'View'

Is extension table

Configure is the table is an extension or not

Mapping field

Table

Field

Definition

Mapping field

Application

Application for which the mapping is defined

For example, HFM

Mapping table

Mapping table which includes the field

For example, 'HFM-Account' (first the application name, and then the table name)

The tables list depends on the selected application.

MDM field

Field name in the EBX® repository

For example, 'CalcAttribute'

The fields list depends on the selected table.

XML Path

Relative path to the field in XML file including its name

For example, /AT[@Name="CalcAttribute"]

Essbase spread sheet column name

Name of the column corresponding to the field in the spreadsheet used to import and export data for Essbase. This field is not used when not importing from or exporting to Essbase.

XML path in Planning

Relative path in the PLANNING XML file for the field

For example, /Plan5PerfOrder

Planning CSV column name

Name of the corresponding field in CSV file use only to export data for Planning.

For example: "Aggregation (Flow)"

Flat CSV name

Name of corresponding field in the CSV Flat file used to export HFM Flat CSV file. For example: " EnableCustom3Aggr"

Flat ADS name

Name of the corresponding field in the ADS Flat file used to export the EPMA flat ADS file. For example: "SmartList"

Field order

Place of the field in XML file within member. The order value 0 means the first position.

Field type

Enumeration value: Name, DefaultParent, Attribute, Description, Parent, Child, Language, Description - Language, Data Storage, UDA

- Name: Label. 

- DefaultParent: DefaultParent.

- Attribute: AT.

- Description: Description.

- Parent: hierarchy Parent.

- Child: hierarchy Child.

- Language: Language - dimension Language.

- Description - Language: attribute Language in tag

Description.

- Data Storage: Data Storage.

- UDA: UDA.

- AttributeICP: AT - Name = "ICPTopMember".

- Attribute security class: AT - Name = "SecurityClass".

- Weeks distribution member: attribute WeeksDistributionMember.

- Skip value: attribute SkipValue.

- Scale: attribute Scale.

- Symbol: attribute Symbol.

Dimension

Table

Field

Definition

Dimension

Name

Dimension name

Essbase sheet name

Name of the sheet corresponding to the dimension in the spreadsheet used to import and export data for Essbase. This field is not used when not importing from or exporting to Essbase.

XML path in Planning

Relative path in the PLANNING XML file for the dimension

For example:

/DIMENSIONS/Dimension[@csversion="*" @density="*" @dimensionName="*" @name="Account" @dimensionType="*" @origname="*" @DimensionAlias="*" @comment="*"]

XML path

Absolute path for the dimension in the XML file including its name

For example, //DIMENSION[@Name="Account"]

Dimension type

Enumeration value: Business dimension, Currencies, Application setting, Consol method, Language

  • Business dimension: including Account, Custom1...4, Entity, Scenario, Period, Year.

  • Currencies: dimension Currencies.

  • Application setting: dimension MISC - Application setting.

  • Consol method: dimension MISC - Consolidation method.

  • Language: dimension Language.

Is custom dimension

Allow for identifying if the dimension is a Custom one. Some internal treatment can be performed such as creation of a default record with value 'ALL'

Flat ADS name

Name of the corresponding dimension in the ADS Flat file used to export the EPMA flat ADS file. For example: "planning_SampApp_Entity"

Application dimension

Table

Field

Definition

Application Dimension

Application

Application for which the mapping is defined

For example, HFM

Dimension

A dimension held by the application

Dimension order

Place of the dimension in XML file. The order value '0' means the first position

Is default

Allow to identify if the dimension is an automatic import/export dimension. If 'Is default' is set 'True', then the check-box for it selection will be disabled during import and export procedure.

XML path

Table

Field

Definition

XML Path

XML Path

Path in the XML file data structure

For example, /AT[@Name="Custom2TopMember"]

Version

Table

Field

Definition

Version

Version

Version in XML file.

Update time

Time of last update

Application

Application containing this version

Language mapping

Table

Field

Definition

Language mapping

Language

The Language in XML file

EBX® locale

Enumerated value for the locale of EBX®: English, French ...

Validation report on mapping data set

Validation reports run on the 'Oracle Hyperion EPM Mapping' data set are enriched to check the following aspects of configuration integrity:

To run a validation report, navigate to the 'Oracle Hyperion EPM Mapping' data set and from the 'Actions' drop-down menu select 'Validate'.

/29_Validation_Report_On_Mapping_Dataset.png

Default data configuration

The Oracle Hyperion EPM comes with a default, ready-to-use data configuration for each Hyperion application.

When necessary, some reference data values are also provided directly within the 'Financial data' domain such as the table 'Consolidation account type'.

The default data for 'Financial data' domain is provided as an archive named "ebx-addon-hmfh.ebx" (only data for Reference data). The default mapping is provided as an archive named "ebx-addon-hmfh-mapping.ebx".

Please refer to the section Installation of this user guide to get further information.

Additional configurations

The 'TIBCO EBX™ Add-on for Oracle Hyperion EPM' data set of the EBX® Administration area brings a set of additional configurations:

/Additional_Configurations_1.png

Aggregation weight display format

Define the export format for the field 'AggregationWeight' in the dimension Custom. This format is defined follows 'Java - Decimal Format'. Value must conform to the regular expression "[-]?[0#,]+[.]?[0#]*[E]?[0]*".

Here are some examples:

Value

Pattern

Output

Comment

123456.789

###,###.###

123,456.789

The pound sign (#) denotes a digit, the comma is a placeholder for the grouping separator, and the period is a placeholder for the decimal separator.

123456.789

###.##

123456.79

The value has three digits to the right of the decimal point, but the pattern has only two. The format method handles this by rounding up.

123.78

000000.000

000123.780

The pattern specifies leading and trailing zeros, because the 0 character is used instead of the pound sign (#).

123.00

0.#

123

The pound sign (#) at the end of the pattern denotes the digits after the decimal point of the output without trailing zeros.

Default select all on export

This is an option for the default 'select all dimension(s)' when using the export service.

Default select all on Export = 'Yes':

/Default_Select_All_On_Export_1.png

Default select all on Export = 'No':

/Default_Select_All_On_Export_2.png

Default language

Define the default language used when the user create a description of a dimension member.

If the 'Default language' is filled (E.g. English):

Then the add-on uses it as the default language when creating a new member description through the user interface:

/33_Default_Language.png

If the 'Default language' is empty:

Then the system uses the EBX® current language as the default language when creating a new member description though the user interface:

/34_Default_Language_Undefined.png

Export full relationship nodes

Allow to export all relationship nodes declarations when the export procedure is executed with the Excel format.

Export full relationship nodes = 'Yes':

The complete node declaration (including all descendants) is exported in every node.

/10000000000001DD000000EBF6D1EFE1.png

Export full relationship node = 'No':

The complete node declaration is exported only one time. After, only the parent node is exported.

/10000000000001E4000000EE0C9B7395.png

Default export file extension

/Default_Export_File_Extension.png

/Default_Export_File_Extension_2.png

On the service 'Hyperion export', after selecting the application, the option 'Choose dimension(s) for export' requests the user to select the export file type(s). The file types can be XML, Excel 2007-2010 and CSV.

The configuration 'Default export file extension' allows to configure the default selected file types. For example, if the configuration 'Default export file extension' is as follows:

Then the result for the export UI is highlighted below:

/36_Choose_Export_Type.png

Export order for hierarchy

This field provides two options: 'Order using Child order' and 'Same order as Data hierarchy view'. It allows for deciding the order in the hierarchy part of the export file when using the Hyperion export service.

/37_Export_Order_Hierarchy.png

If the option 'Order using Child order' is selected, then the order of the hierarchy part in the export file is ordered by the field 'Child order'. This field is located on the hierarchy tables in the financial data set (Ex: Account hierarchy, Custom1 hierarchy, etc.).

If the option 'Same order as Data hierarchy view' is selected, then the system will arrange the order in the export file in the same order than one displayed in the data hierarchy view.

/38_Export_Order_Hierarchy_1.png

For example, when the export procedure is executed on this hierarchy, the selection of the two options provide the results highlighted in the next pages.

/39_Account_Active_Yes.png

The corresponding Child orders of this hierarchy.

Export order for hierarchy = 'Order using Child order':

/40_Order_Using_Child_Order.png

Export order for hierarchy = 'Same order as Data hierarchy':

/41_Same_Order_Data_Hierarchy.png

Action for import missing value

When importing data to EBX®, it can exist missing values. For example, the missing value for AggrWeight:

/100000000000055900000127CD63267B.png

In order to handle these cases of missing values, the add-on provides an option in TIBCO EBX™ Add-on for Oracle Hyperion EPM to decide the action when importing the missing value field(s) in mode 'Import'.

/42_Import_Missing_Data.png

Example:

For the field 'AggrWeight' in the dimension Custom1:

/43_Aggregation_Weight.png

Action for import missing value = 'Preserve attribute existing values'.

/43_Aggregation_Weight.png

Action for import missing value = 'Set existing attribute values to null':

/44_Aggregation_Weight_Null.png

Flat csv version

Define field 'Version' used by the export procedure with HFM FLAT CSV file.

/100000000000011F0000004FAE29CC9F.png

Export #root

Allow to use or not the '#root' in HFM FLAT CSV file when exporting data.

Export #root = 'Yes':

/10000000000000BF0000004E912E591D.png

Export #root = 'No':

/10000000000000C000000051617C47AE.png

Export empty field for app setting

Decide to export the empty field(s) in table 'Application Setting' or not when using the FLAT CSV file.

Export empty field for AppSetting = 'No:

/10000000000001470000008D4E543713.png

Export empty field for AppSetting = 'Yes':

/100000000000017F0000026D22FE2F2B.png

Other CSV file configuration properties

These additional properties are available to configure the export of flat CSV file:

Property

Description

Header's label of default parent

Allow one to provide a label for the header of the default parent column in the flat CSV file.

This property is not used in case there is no mapping for the field "Default parent" or when the value is empty.

Header's label of description

Allow one to provide a label for the header of the description column in the flat CSV.

This property is not used in case there is no mapping for the field "Description" or when the value is empty.

Is export hierarchy header

Boolean value. Allow one to configure if the hierarchy headers are exported in the flat CSV file or not.

  • 'True': The exported CSV file contains the header of hierarchy.

  • 'False': The exported CSV file does not contain the header of hierarchy.

This property is not used in case there is no mapping for the hierarchy fields.

Is export custom dimension order

Boolean value. Allow one to configure if the item '!CUSTOM_ORDER=Custom1, Custom2...' is exported or not.

  • 'True': the item 'CUSTOM_ORDER' is exported in the CSV file.

  • 'False': the item 'CUSTOM_ORDER' is not exported in the CSV file.

Examples:

/45_Export_Flat_CSV.png

/47_Execute_Export_CSV_0.png

/46_Execute_Export_CSV.png

/Other_CSV_File_Configuration_Properties_4.png

The header's label of default parent will be 'Default parent' and the header's label of description will be 'Description(s)' as configured:

/10000000000003CA00000078DB2F4129.png

The hierarchy header will be removed since the configuration 'Is export hierarchy header' is set as 'No'.

/100000000000014900000136E28F1D79.png

In case the headers contain the delimiter of export files, the add-on will raise an error:

/48_Execute_Export_Error.png

In case there is only value for the property "Header's label of description" as highlighted now:

/Headers_Label_Of_Description.png

Then, the header's label of description will be display as 'Description(s)'. The header's label of default parent column will be missing since the value of "Header's label of default parent" is blank.

/10000000000003450000006494D82160.png

Use TIBCO EBX™ Match and Cleanse Add-on

From the version 1.5.0, it is possible to use the TIBCO EBX® Match and Cleanse Add-on on financial data. When the EBX® Match and Cleanse Add-on is not installed in your environment then the option is no longer available.

When the 'Use TIBCO EBX™ Match and Cleanse Add-on' is set to 'True', the add-on creates a data space for the EBX Match and Cleanse Add-on with the following datasets: 'Oracle Hyperion EPM dimensions' (on which EBX Match and Cleanse Add-on functionality is available), 'Oracle Hyperion Mapping' and 'Oracle Hyperion Report'.

On the 'Oracle Hyperion EPM Dimensions' dataset, the financial tables are extended with the metadata type used by the EBX Match and Cleanse Add-on. To enable data transfer to and from a standard 'Oracle Hyperion EPM Dimensions' dataset to the one using the EBX Match and Cleanse Add-on, use one of the following services: 'Copy to use TIBCO EBX™ Match and Cleanse Add-on' and 'Copy to remove TIBCO EBX™ Match and Cleanse Add-on'. Refer 'Integration with TIBCO EBX™ Match and Cleanse Add-on' in the user guide for more information).

/Use_Match_And_Cleanse_Addon.png

'ebx-addon-hmfh-include-daqa.ebx' archive is provided as the default reference data of "Oracle Hyperion EPM Dimensions" data set in "Oracle Hyperion EPM with Match and Cleanse Add-on metadata" data space.

Application table

As shown in the following image, the list of possible applications is created in the 'Administration' tab → 'TIBCO EBX® Add-on for Oracle Hyperion EPM' → 'Application' table.

/Application_Table.png

As shown below, when you create a data mapping the list of applications is supplied from the 'TIBCO EBX™ Add-on for Oracle Hyperion EPM' data space.

/51_New_Record_Mapping.png

Dynamically handle the hierarchy levels of Essbase rainbow file

Overview

The add-on dynamically handles the number of hierarchy levels exported in an Essbase rainbow file. You only need to set the default number of hierarchy levels, and the number on the exported file dynamically updates based on your input (minimum is your default number).

Handle procedure

/53_Account_Parent_Child.png

/54_ESSBASE_Choose_Dimension.png

/1000000000000281000000CE32806DC1.png

Migration of the 'Mapping data set'

The 'Migrate mapping data set' service is located in the 'Oracle Hyperion EPM' data space and is available to any child data space. This service migrates the existing data mapping configuration into a version that is compatible with the new extension management integrated from the 1.3.0 version of the add-on. Any 'Mapping table' with an undefined 'Is extension table' field is automatically set to 'No'.

/55_Migration_Mapping_Dataset.png

/56_Migration_Mapping_Dataset_1.png

This service migrates the existing data mapping configuration into a version that is compatible with the new validation report integrated from the GA 1.6.0 version of the add-on. The service automatically inserts the following new data mapping declarations for the EPMA application:

Switch the display of boolean fields in exported Excel file between T/F and Y/N

Overview

This feature supports user to switch the display of boolean values between T/F and Y/N in output file of the Export service.

Changing the Boolean display format

/10000000000001160000014ED0149B77.png

/100000000000010E0000014574EDA8C4.png

Align predefined rules data to the current version of TIBCO EBX™ Rules Portfolio Add-on

Overview

TIBCO EBX® Rules Portfolio Add-on 1.5.0 provides a feature to display D.E.C as hierarchy. Therefore, it's needed to update the predefined data for D.E.C on EBX® Add-on for Oracle Hyperion EPM to be aligned to this version of EBX® Rules Portfolio Add-on.

Add default configuration for 'Update dimension and field order' on import service

Overview

Currently, we provide a new option in the Configuration section that allows users to set the default value themselves then they can modify it on Import configuration screen of 'Import' service

/Add_Default_Configuration_For_Update_Dimension_And_Field_Order.png

Regular expression for names

Overview

This feature allows users to add naming validation rule for name fields of financial dimension records. The following sections describe the steps to apply the rule.

Set rule in the configuration

To set a validation rule, go to Administration -> TIBCO EBX® Add-on for Oracle Hyperion EPM -> Basis.

/Regular_Expression_For_Names.png

Validation rule in effect

This rule affects the following tables: Account, Currency, Custom1, Custom2, Custom3, Custom4, Entity, Period, Consolidation account type, Consolidation method, Application setting, ICP, Scenario, Year.

When users create a record to one of above tables, if its name does not match the validation rule, a warning will be displayed, and they won't be able to save until the name conforms to the rule. For example, with this rule set:

/60_Validation_Rule_In_Effect.png

Results in the following screen:

/61_Validation_Rule_In_Effect_22.png