Fish Out of Water: New Essbase Functions


February 15th, 2012

I was reading a story about someone trying to clean out his little fish bowl, which held a bunch of fish. He emptied the fish into a bathtub full of water so he can clean up the bowl, and then noticed that the fish remained bunched up together in a circle, not venturing out.And  This story made me think about how we are sometimes reluctant to our comfort zones because we’re used to sticking to the imaginary walls of the fish bowl.

The same sometimes holds true for our approach to business applications: we may upgrade to new versions, but still swim in the boundaries of the old fish tank. A good example is Oracle Essbase. Essbase has always been abundant with functions and functionality that we often overlook. Some of the more impactful new functions are:

  • @EQUAL
  • @LIKE

I want to explore about a significant and pretty nifty function added in version 11.1.2: the last function @XWRITE, which is similar to @XREF, a function used to push data from one cube to another or even loopback and write to existing blocks in the same cube. In using @XREF, data is being pulled from the target, while in @XWRITE is the opposite and data blocks get written dynamically in the same or remote cubes. This becomes incredibly useful when there are multiple cubes in the Planning environment and a single calculation can push data to multiple cubes having different outlines. Additionally, the loopback option in this function, allows writing to blocks in the same cube where the blocks may not have existed before, thus, bypassing the age old ‘Block Creation Issue’ in Essbase. So let’s paddle our fins and swim over to the edges of our tank, perhaps there is more to see out there and begin comparing the @XREF with @XWRITE functions.


Syntax: @XREF(LocationAlias, [,MbrList])

Location Alias defines the server, application and database on source of data, but @XREF function is run from the target database, in other words, data is pulled from source to target. Member list is a comma delimited list of members that define the source. Data source is constructed from a member combination of members in the above member list, current point of view in the target and top member of any dimension not mentioned in the function and executed on the target. For example, in a Capex database Depreciation may be calculated by Asset Class, Line item and Project and later passed on to the GP database to individual Accounts. @XREF member formulas may be dynamically calculated on the target as follows:

“Depreciation_Equip” = @XREF(LocationAlias, “Depreciation”, “AssetClass_Equip”)


“Depreciation_Plants” = @XREF(LocationAlias, “Depreciation”, “AssetClass_Plants”)

As we said earlier, the intersection on the target is defined by the current point of view in the calc script as well as the members on the @XREF function, but one thing that may be troublesome is that Blocks for the target may not exist which means the calculation would not occur. For example, we are trying to write to ‘Forecast’ scenario of Next Fiscal Year a depreciation projection performed on the Capex database. The Entity/Cost Center combination does not have any blocks created in the target, thus, in order to calculate @XREF in the target, we must create the blocks first using the standard block creation methods such as Data Copy or CREATEONMISSINGBLK setting.


Syntax: @XWRITE(expression, LocationAlias [, MbrList])

Expression is a single member or variable corresponding to the value to be stored or the target.

LocationAlias is defined by the Server/App/Database or the target database and must be set on the target database.

MbrList is optional and qualifies the @XWRITE. The members specified on the MbrList and the combination of members in the source point of view defines the source data to be pushed out. @XWRITE will be executed from the Source database.

In the example below, we are trying to dynamically create blocks and write to the account “Promotions” in a future year using current year data, hence the function @LOOPBACK is inserted in the argument. Alternatively, we had to create the blocks that do not yet exist using a DataCopy or other methods that was less efficient.


/*Source and Target should only return one Block within the XWRITE*/


@XWRITE(“Advertising”->”FY12″, @LoopBack, “FY13”);

ENDFIX                                                                                                                                                                   )

If we were to write back to another cube, function @XWRITE behaves similar to @XREF, except that the function is written and executed from the source. So for example, Depreciation Expense is being calculated by Line Item and Asset Class (Right) and pushed to the Expense cube (Left) using a Location Alias that defines the target database.










@XWRITE(“Machinery and Equipment”, LocationAlias, “Depr Exp: Machinary”)

Again, say we are booking Depreciation for Forecast scenario at a Corporate HQ cost center, and the related Blocks do not yet exist, @XWRITE creates them dynamically, preventing us from using costly CREATEONMISSINGBLK function.

In short, @XWRITE is run at the source specifying a target, so the blocks being fixed on are guaranteed to exist, while @XREF is run at the target specifying the source and those set of blocks may or may not exist. Moreover, @XWRITE having a Loopback option, you can write to the same database and getting around almost any kind of block creation issue.

Let’s swim to the edge and see where the flow takes us.



About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

2 comments on “Fish Out of Water: New Essbase Functions”

Leave a Reply

Your email address will not be published. Required fields are marked *