Hyperion EPMA Data Synchronizations with Substitution Variables

EPMA Data Synchronizations are a fairly simple way to move data between Hyperion applications and to load data into Hyperion from external flat files and interface tables.  The interface is very simple and easy.  But one thing you will not find is the use of substitution variables.  There seems to be a conception that one cannot use subvars in the synchronization filters -- which is quite limiting.

There are a few hints that the design is supposed to support subvars, but there is no mention of it in the documentation and nothing on the boards.  None of this is especially surprising as this is a fairly lightly used capability.  ODI, FDMEE and other technologies have far more features.  Even Essbase partitions are more flexible - only for moving data between Essbase applications.

However, it turns out that subvars are fully supported.  Each dimension in the Synchronization definition can be defined with a filter as one of EQUAL, LIKE, or FUNCTION (from the EPMA Admin Guide):

• EQUAL—any valid member, such as Actual for the Scenario dimension.
• LIKE—use wild cards in the filter:
• * for multiple characters (Only use * wildcard characters at the end of a string)
• ? for single character
• FUNCTION—use any valid member selection function, such as BottomOfHierarchy() retrieves the base level members for the dimension.

Nothing about variables.  Turns out, when putting anything in as EQUAL or FUNCTION, the tool surrounds the values with double quotes, and Essbase does not interpret it as a subvar.  But define the filter as LIKE, it is sent to Essbase without.  And Essbase treats it properly.

So, now you know the trick.  A little used but simple tool just became more useful.

For more information, check out the Oracle® Hyperion EPMA Administrator's Guide for version 11.x.  (Oracle EPM Documentation)