How to use XWRITE and XREF in Essbbase
The main intension of writing this Post is to describe how @XREF and @XWRITE can work in similar way.
@XWRITE and @XREF are two calculation commands can be used for such operations.
@XWRITE and @XREF are two calculation commands can be used for such operations.
Using @XREF:
The @XREF function pulls the data to the TARGET cube so your business rule / calc script will be based in the TARGET cube and the FIX statement will be based on the TARGET cube dimensions.
Set your FIX statement to include all of the common dimensions and select the members you want to use for any unique dimensions in the TARGET cube or members which will differ between cubes. In this example the Composite dimension is unique to the TARGET cube and the version dimension is common between the two cubes but we are moving the data from Draft2 in the SORUCE cube to Draft1 in the TARGET cube.
@XREF Syntax:
FIX (Common Dimensions, Unique TARGET Dimensions, Common Dimensions but Different Members selected for TARGET cube)
Open TARGET Account block(
@XREF(“SOURCE Cube Alias”, SOURCE Account member, “Members from unique SOURCE cube Dimensions”);
)
ENDFIX
Using @XWRITE:
The @XWRITE function pushes the data to the TARGET cube so your business rule / calc script will be based in the SOURCE cube and the FIX statement will be based on the SOURCE cube dimensions.
Set your FIX statement to include all of the common dimensions and select the members you want to use for any unique dimensions in the SOURCE cube or members which will differ between cubes. In this example the MarketSector dimension is unique to the SOURCE cube and the version dimension is common between the two cubes but we are moving the data from Draft2 in the SORUCE cube to Draft1 in the TARGET cube.
@XWRITE Syntax
FIX (Common Dimensions, Unique SOURCE Dimensions, Common Dimensions but Different Members selected for SOURCE cube)
Open SOURCE Account block(
@XWRITE(SOURCE Account member, TARGET Cube alias, TARGET Account member, Unique TARGET Dimension members);
)
ENDFIX
- _A1alias_ is location alias name of A1 cube which acts as source for @XREF from where we get the data. B1 is called as target to where we are copying the data.
- @XREF always refer the data cell by taking the combination of Members names given in the Fix statement and the members given in the @XREF. Here Sales and Budget are members from A1 cube where as we still have this calculation under B1 cube .
- Whenever we planned to get the value for some intersection by referring other cube we have to run this calculation. So whenever we run this calculation it will always get into A1 cube and search for the intersection to get data .As we know it will take some time to get the data from another cube .
- If you want to copy more than one member from the same dimension we can write multiple @XREF statements.
- We can alse use this command in member formulas.
- If you want to perform same kind of operation in any of your requirement and I would suggest using @XWRITE over @XREF because @XWRITE will write the data to the target cube whenever we have data in the source cube and it is faster .whereas @XREF always has to fetch data from other cube when we requested ,so it will take time as it depends on source cube availability and we don’t know whether we have data available for the intersection we are seeking. If we don’t have it doesn’t make any sense running the calculation .
- When you have a situation like cube will be accessed a lot and so many calculations might be running frequently, where as you have to populate some cells by referring other cube .If you use XREF to get those values ,it will increase your calculation time .In such situation you can go with XWRITE.
- Using @XWRITE we can also write data to same cube itself by using @LOOPBACK function like followed statement @XWRITE(“Sales”,@LOOPBACK,”Expenses”);
- We can also use @XWRITE command in member formula calculations.
- @XREF can be used for different purpose as specified in the technical reference, it has its own advantages.
