If you need to map multiple members from a source application to an equivalent member in a target application, you must first define the source members.
I. Define Source Members
Option 1 – Shared alternate hierarchy
a. Create new parent member with the same member name as the target application and set property to ignore to prevent duplication of data
b. Add existing members as children of new parent member
- Requires additional maintenance of the dimension structure
- Does not compromise data retrieval
- May increase calculation time
We all want our end-users to be self efficient and I think they may be able to figure out the mapping once they drill-down the hiearchy. I have used alternate hierarchies in large applications and I have yet to compromise calculation time.
Option 2 – User Defined Attributes
a. Assign UDA’s to source members
b. Create a new member and set property to ignore to prevent duplication of data
c. Develop a calc script fixed on the UDA and populate the Sum of the UDAs to “new member”
- Requires additional member property maintenance
- Does not compromise data retrieval
- Does not compromise calculation time
If data is constantly re-stated, re-classed or adjusted you must remember to re-calculate. Throw in a batch script and call it a day – I’m just saying.
Now, the sophisticated/advanced Essbase user will cleverly filter their retrievals on UDA’s and magically list the accounts that make up the mapping.
Option 3 – Attribute Dimension
a. Create attribute dimension property
The attribute dimension gives you the same results as the alternate shared hierarchy, behaves similar to UDA’s. What is the difference? Attributes may significantly compromise report retrievals.
Which one is the best option?
Every environment is different. Performance is significantly dependent on the application configuration and I strongly recommend you test.
II. Data transfer and Data Export Options
Now, we are ready to explore options on how to populate data from the source application to the target application. The options are as follows:
Option 1 – @XREF built in function transfers data from source to target application via calc script
- Excellent option for consistent structured hierarchies
- Leverages built-in calculation functions
- Data flows seamlessly if the structures are one in the same
- Can be easily batched
If you fix at level 0 in a common dimension i.e. @LEVMBR(“ENTITY”,0) and it is not an exact mirror of the source application, duplication of data will result at the level where the dimension hierarchy differ. Therefore, the member names and levels must be identical.
The granular the fix statement, the quicker the performance.
Option 2 – Report Script Exports data to a flat file (see Simplified Essbase Administration Part II)
- Export data from specific Alias tables, UDA, levels
- Specify export column/row format
- Exclude members from a dimension
- Re-name members
- Can be easily batched
Report script performs efficiently when focused on smaller data sets. Larger data sets may take longer to process.
The rename built-in function works brilliantly if you need to map specific members to an additional dimension member in the target application i.e. source member 1234 must map specifically to target member 4567 and COLA {“RENAME” “1234”} “4567 COLA” . This option is best used if the mapping is consistent; however, unique to specific members in a different dimension. This data file can be easily parsed using the Split function within a data load rule.
Option 3 - DataExport – Exports data to a flat file
- Exports data to text file extremely fast
- Leverages built-in calculation functions
- Can be easily batched
DataExport outperforms report scripts. It is engineered to export data in the order that it is organized in the database. Whereas, report script performance is dependent on coding and may retrieve the same blocks multiple times.
DataExport does not support attributes, aliases, member re-name and data format options.
Every environment is different. Performance is significantly dependent on the application configuration and I strongly recommend you test.
This is the beauty of Essbase, the options are available at your disposal.
It is up to the individual to creatively leverage the tool.
