Hyperion Planning – Conditional @XREF on Plan Types

So here is a scenario for you… Say you have a plan type that pulls half of its data from another plan type. However, depending on which Scenario you choose, the data is either pulled via @XREF or loaded via a text file. How do you do this when Planning will make the member a dynamic calc member in the target cube?

The key item to use is the UDA HSP_NOLINK. This UDA tells Planning to not assign an @XREF in Essbase when the appliation is deployed.

In Workspace, add the UDAs “HSP_NOLINK” and “HSP_UDF” (tells Planning to not overwrite a member formula in Essbase) to the member(s) you would like the data to be dependent on the Scenario (usually an Account member…where the @XREFs are in the Essbase database). Deploy the application.

In Essbase, you see that the member is now a stored member without an @XREF, versus a Dynamic Calc member. Here, you can create a member formula. If we wanted to use an @XREF for Forecast data to the other plan type and have Actuals data loaded, we can.

Example:

AccountMbr =
IF(@ISMBR(“Scenario”,”Forecast”))
@XREF(“_RevCube_”,”AccountMbr”);
ENDIF

Voila. You can call this member in a calc script to be run and it will pull Forecast data while leaving Actuals data alone.

3 comments

  1. The only downside to this method is you have to run a calc script to update the data. It means data can be out of sync between the cubes. I prefer to have them enter (or load) all of the data for that member into the cube being referenced so I have consistency in where the data exists between scenarios

  2. Sarah, this is a great post! Another consideration when moving data is the size of the data set as it will impact performance. I had a client where running an XRef business rule took 30 minutes. We rewrote the XRef as an XWrite. The benefit here was that we were able to add logic to exclude 0s and #Missing values from being pushed to the target database. By only pushing intersections with data, we were able to process the same data set in 2.5 minutes instead of 30. Of course there are pros and cons to everything so you want to weigh the different options to achieve your desired level of performance.

Leave a Reply