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.
The descriptions below provide an overview of the table structure in the repository.
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 |
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. |
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 |
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.
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 |
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 |
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. |
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
| |
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" |
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. |
Table | Field | Definition |
---|---|---|
XML Path | XML Path | Path in the XML file data structure For example, /AT[@Name="Custom2TopMember"] |
Table | Field | Definition |
---|---|---|
Version | Version | Version in XML file. |
Update time | Time of last update | |
Application | Application containing this version |
Table | Field | Definition |
---|---|---|
Language mapping | Language | The Language in XML file |
EBX® locale | Enumerated value for the locale of EBX®: English, French ... |
Validation reports run on the 'Oracle Hyperion EPM Mapping' data set are enriched to check the following aspects of configuration integrity:
Duplication Paths (warning)
Duplicated paths of MDM table (warning)
Duplicated MDM tables and Paths of MDM fields (warning)
Duplication of XML paths (warning)
Path defined as a space string (warning)
To run a validation report, navigate to the 'Oracle Hyperion EPM Mapping' data set and from the 'Actions' drop-down menu select 'Validate'.
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.
The 'TIBCO EBX™ Add-on for Oracle Hyperion EPM' data set of the EBX® Administration area brings a set of additional configurations:
'Mail' is used to configure email information required when the execution of the import and export procedures is asynchronous. See the sections related to the import and export procedures for more information.
'Logging' is used to configure the data mapping logging. See Appendix - Data mapping logging for more information.
'Basis' and 'Path' domains are described in this section.
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. |
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 = 'No':
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:
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:
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.
Export full relationship node = 'No':
The complete node declaration is exported only one time. After, only the parent node is exported.
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:
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.
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.
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.
The corresponding Child orders of this hierarchy.
Export order for hierarchy = 'Order using Child order':
Export order for hierarchy = 'Same order as Data hierarchy':
When importing data to EBX®, it can exist missing values. For example, the missing value for AggrWeight:
On Excel file: the empty or null value for AggrWeight or the missing of the column AggrWeight.
On XML file: the empty value for tag <AT Name-"AggrWeight"></AT>
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'.
'Preserve attribute existing values'. The system will keep current value of the missing value fields.
'Set existing attribute values to null'. The system will overwrite current value of the missing value fields as null.
Example:
For the field 'AggrWeight' in the dimension Custom1:
Action for import missing value = 'Preserve attribute existing values'.
When importing in mode 'Import' with the file missing value for AggrWeight, the system will automatically keep the current value for field AggrWeight.
Action for import missing value = 'Set existing attribute values to null':
When importing in mode 'Import' with the file missing value for AggrWeight, the system will automatically overwrite the current value for field AggrWeight as null.
Define field 'Version' used by the export procedure with HFM FLAT CSV file.
Allow to use or not the '#root' in HFM FLAT CSV file when exporting data.
Export #root = 'Yes':
Export #root = 'No':
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:
Export empty field for AppSetting = 'Yes':
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.
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.
|
Examples:
Step 1: Configure export CSV options as follows:
Step 2: Execute the export CSV file operation
The header's label of default parent will be 'Default parent' and the header's label of description will be 'Description(s)' as configured:
The hierarchy header will be removed since the configuration 'Is export hierarchy header' is set as 'No'.
In case the headers contain the delimiter of export files, the add-on will raise an error:
In case there is only value for the property "Header's label of description" as highlighted now:
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.
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).
'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.
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.
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.
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).
On the 'Basis' group under the 'Hyperion Configuration' data set of the 'Administration' data space, set the value of the 'Default minimum number of generated levels' to the desired number. In this example we use five.
On the 'Member' type table under the Oracle Hyperion EPM Dimensions data set, create some records. For example : 3 records (A1, A11, A12) in table 'Account'.
From the 'Actions' menu under the Oracle Hyperion EPM Dimensions data set, run the 'Export' service with input data as below:
Application: Essbase.
Dimension: the dimension of the table that you have just imported data (Account).
Export type: Excel.
Now the value of 'Generate levels dynamically' is displayed as your setting in the Hyperion Configuration data set. You can change this value here if you want.
Click the 'Export' button to export your data. Then download the exported Excel file.
Open the exported file. The number of hierarchy levels is 5.
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'.
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:
MDM table, MDM field: new tables and fields used for storing EPMA data.
Application: new application EPMA.
Dimension: new dimensions for EPMA (Currency, Alias, Version); ADS name for dimensions that can be exported into EPMA ADS file.
Application dimension: define which dimensions can be exported into EPMA ADS file (Account, Custom1 as Generic, Entity, Scenario, Period, Year, Currency, Version, Alias).
Mapping tables: EPMA data mapping for the tables.
Mapping fields: EPMA data mapping for the fields.
This feature supports user to switch the display of boolean values between T/F and Y/N in output file of the Export service.
From the Basis group under the TIBCO EBX™ Add-on for Oracle Hyperion EPM data set of the Administration data space, set the value of the 'Boolean values display format' field to 'Y/N' or 'T/F'.
Create records in the Account table under the Oracle Hyperion EPM Dimensions data set. Then navigate to the Actions menu and run the Export service with the following configuration.
Application: HFM
Dimension: Account
Export type: Select all
In General properties: the default value of 'Boolean values display format' is 'Y/N'.
Download the exported file then check the result.
ADS file
Excel file
XML file
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.
Activating trigger when creating predefined rules. (reuse the Rules trigger to create data.)
Adding D.E.C data for data set into predefined data of D.E.C.
Remove simple expression data in predefined rules to avoid creating unused data since simple expression java classes are not supported.
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
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.
To set a validation rule, go to Administration -> TIBCO EBX® Add-on for Oracle Hyperion EPM -> Basis.
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:
Results in the following screen: