@XWRITE Function
The function that has been commonly referred to as XREF’s big brother, XWRITE is a function in the newer releases of Hyperion that fills almost all the gaps that XREF had. XWRITE is a function that allows a calculation script to push data to another Essbase database or application (as opposed to XREF’s method of “pulling the data”). While being a useful function for any Essbase only application, this new function will likely be the best way to move data between databases within any planning application. This allows for data to be pushed to all of its final locations as part of the process of a user saving within a planning application. If you set up a cube to be a Consolidated cube, the data in it can always be updated on form save with this function.
This new functionality became available in 11.1.2.0, and works on any version after that. The main constraint on this function right now, is that it will only work for a BSO to BSO data movement. BSO to ASO is not supported, and it is unknown if Oracle will pursue that functionality.
XWRITE is designed to push data between databases with very similar dimensionality (such as a planning application) so if you have two databases with significantly different metadata, this might not be the best data option for you.
How Do I Write XWRITE calculation?
So the next question is, how do you write an XWRITE calculation? The syntax, as provided by the Oracle Technical Reference, is as follows:
@XWRITE (expression, location alias [,mbrList])
So let’s break that apart. The first input is the expression. This is the member you plan to move from the source to target. And that is another limitation with XWRITE is that you can only select one member (or a function/expression that returns a single value) in the expression field. Second area is the location alias, with if you have worked with XREF before; you will know this is setup in EAS database properties. In the calculation, you just need to select the relevant connection for the database you pushing data to.
(click to enlarge)
The last item, mbrList, is the cell or intersection of cells that the data is being moved to. If there are dimensions on the target that are not in the source, this is where you would define where the data is going to be moved to.
What are the best ways to write an XWRITE calculation?
With how XWRITE was designed, you will need to be very purposeful in how you design a calculation using this function. The first thing you will need to decide is which dimension you will use as the expression. This is important for both developing the calculation and figuring out how the calculation will function. Since the expression can only be a single member, you will want to choose a dimension that will likely only be moving one member so you do not have to write the calculation line over multiple times. The performance consideration comes in with the fact that XWRITE works faster when the expression member is part of a dense dimension. This way, it should only have to move a single block at a time. However I would suggest that as you are developing, you try multiple methods to see which works best for your situation.
So now that you have selected with dimension to use in the expression, the next step is writing the FIX statement. In writing XWRITE calculations, the FIX is as important as what is included in the XWRITE. The FIX will be where you define the area of data you want to move. The only dimension you will want to leave out of the FIX is the dimension you decide to use in the expression. If there are any dimensions in the source that are not in the target, you will need to FIX on a single member of that dimension.
Once the fix is defined, you will want to work on the body of the calculation. The first thing to note, is that XWRITE needs to function within a calculation block. You can often achieve this using the same member defined as the expression for a particular XWRITE.
(click to enlarge)
The next thing would be to put the XWRITE statement in the calculation block. You should already have selected your expression, and the location alias should already be setup, so the only thing left is to define a mbrList. The mbrList is necessary if you are doing any form of simple mapping, or if the target has a dimension that was not in the source. If either of these are the case, you will want to put a comma in as a cross dimensional operator in to denote the differences. You can use the expression member again here to help in creating the mbrList.
(click to enlarge)
@RETURN
The RETURN function is another new one in 11.1.2 version that can provide a lot of functionality to people, especially when designing planning applications. As planning applications are improving business rules, and making it easier for end users to kick off calculations within the system, @RETURN provides a way for administrators to put boundaries on what can and cannot be run from the Essbase level.
This may seem difficult to understand, so let me come up with an example for you. Let’s say the user needs the ability to input a warehouse allocation that affects a certain subset of locations based on an allocation statistic, square feet for instance. Well what if the end user enters a typo and selects a group of locations that are not warehouse facilities, which do not have any square footage assigned to them? With this function the business rule can assess that the total of all the square footage is zero and the calculation will stop, displaying a custom error message to the user that “The selected locations are not warehouse facilities, and cannot be allocated to”.
How does it work?
Well the setup for this function is relatively easy, as the RETURN function only needs the custom error message, and the priority level of the return (Warning, Error, or Informational). It should be noted that even if it is set to just be informational or a warning, the calculation script will still be stopped.
The real effort in using this function comes in the placement of the function. This function will most often be used as part of an IF statement, where a condition was triggered that requires an error to be returned. Once you have determined where the @RETURN statement should go, the syntax is just @RETURN (“Message”, Priority Level).
(click to enlarge)
(click to enlarge)
You can also make the message dynamic with references to member names, or items in the calculation. To do this, you need to use the CONCATENATE function in the Message field.
(click to enlarge)
(click to enlarge
Free eBook Download
In the eBook “Having a Conversation with Data”, learn what the current BI infrastructure has been and associated challenges with the traditional approach. How important the user experience is in order to best maximize data’s value (think visualizations!!) to your organization and how to gain a competitive advantage with modern analytics platforms.
