Wednesday, February 26, 2014

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)

3 comments:

  1. Hi,

    We have a requirement where we have to run the epma data sync interfaces for the respective scenarios, years and periods depending on the budgeting and forecasting cycle between two hyperion BSO applications.
    For Example: We want to run the data sync interface for Fcst_H2,FY14 and the periods from Nov to Mar.

    We used only the subvars (CurrYear) for Year FY14 in the year dimension and (CurrFcst) for the scenario Forecast_H2 in the EPMA data sync interface and executed it. But is moving the data for all the scenarios and all the years which is wrong.

    Subvars are not working if we use them in EPMA data sync interfaces.

    ReplyDelete
    Replies
    1. Vishal,

      I have heard that this hack works on some systems and not on others. I think Oracle may have made a change in some patch in 11.1.2.1 or 11.1.2.2 that breaks this. I do not have enough data to know when it changed.

      I recommend that you consider either hard coding and changing it manually - or use partitioning - or FDM.

      You can try an Oracle SR to see if they have any recommendation. However, with the impending retirement of EPMA Data Syncs, I wouldn't hold out much hope.

      Sorry it does not work for you.

      Yours,
      Jonathan

      Delete
  2. Great article. I think you also should read data room review and choose the best variant for data security.

    ReplyDelete