Deep Dive in @XREF: From Basics to Pro!, What IFs & Can I....

Most of the times we have to refer data from one cube to another. There are many ways in which you can transfer or refer data from a cube, few  of the most popular ways are:
1. Create a partition,
2. Create a XREF,
3. Create an export and import script, etc.

Every way has its own pros and cons. Today our discussion will be focused on XREF. Trust me if you are working with Planning applications you are dealing with XREFs all the time.

If there is a  planning application with multiple plan types, Planning automatically creates the XREFs between cubes for the members. If we have a planning application with two plan types, say REV and INCSTMT and we have a stored member say "OtherRevs". Let's say this member has the source plan type as REV and its enabled for both the PlanTypes. In this case Planning will create this member in both the cubes, however in REV cube it will be a stored member but in INCSTMT it will be a dynamic calc member with an auto generated XREF. This source plan type option is only available in the Account dimension in a planning application (If any of the above is came as a surprise to you, time to go and check in detail),

XREF: As the name suggests, it's eXternal REFerence. If you want to refer data from one cube to the other, we can use Essbase's wonderful XREF function. Just like any data reference we have three main things to consider here:
1. What is the source combination,
2. What is the target combination,
3. What is the point of view.

Say, I want to refer the data of "OtherRevs" for All Entities, For Fy13, Projection, Working to a different cube in member "ExternalRev"

Here my source is OtherRevs, target is ExternalRev and POV is All Entities, For Fy13, Projection, Working.

Syntax: @XREF(LocationAlias, [mbrList])

Location Alias is the first thing we need to set before we even write the XREF function. Now, think logically, if we need two different database to communicate with each other there should be a connectivity, a feasible way on which they will communicate, in case of XREF that is Location Alias. It will contain the information of the cube with which you need your cube to refer values.

Lets take a dive using these two cubes here, they have the same outline, Account and Period are dense and Entity and Year Dims are sparse.



As a first step let me create a location alias, let me call it _SRC_:

When you will create a location alias following things will happen:

[Tue Dec 02 01:58:09 2014]Local/SrcApp/db/admin@Native Directory/6052/Info(1013091)
Received Command [Create location alias] from user [admin@Native Directory]

[Tue Dec 02 01:58:09 2014]Local/SrcApp/db/admin@Native Directory/6052/Info(1019018)
Writing Parameters For Database [db]

[Tue Dec 02 01:58:09 2014]Local/SrcApp/db/admin@Native Directory/6052/Info(1013275)
Location Alias _SRC_ created

Once its created, you are good to start writing your BR or Calc which will refer the data from the other cube.

Lets start with something really simple, in above cubes I need to refer the data of Member A11 for all combinations. The script I need to write for this is really simple.

"A11" = @XREF(_SRC_,"A11");

Once this script is saved and executed, you will see following in the base cube logs:

[Tue Dec 02 05:24:22 2014]Local/SrcApp/db/admin@Native Directory/8316/Info(1013163)

Received Command [Calculate] from user [admin@Native Directory] using [Xref1.csc]

[Tue Dec 02 05:24:22 2014]Local/SrcApp/db/admin@Native Directory/18956/Info(1012675)

Commit Blocks Interval for the calculation is [3000]

[Tue Dec 02 05:24:22 2014]Local/SrcApp/db/admin@Native Directory/18956/Info(1012717)

Remote bitmap cache is [Disabled]

[Tue Dec 02 05:24:22 2014]Local/SrcApp/db/admin@Native Directory/18956/Info(1012669)

Calculating [ Account(A11)]

[Tue Dec 02 05:24:22 2014]Local/SrcApp/db/admin@Native Directory/18956/Info(1012677)

Calculating in serial

[Tue Dec 02 05:24:22 2014]Local/SrcApp/db/admin@Native Directory/18956/Info(1012579)

Total Calc Elapsed Time for [Xref1.csc] : [0.047] seconds

At the same time if you will look at the logs in the referred database:

[Tue Dec 02 05:24:22 2014]Local/TgtApp///6956/Info(1013210)

User [admin@Native Directory] set active on database [db]

[Tue Dec 02 05:24:22 2014]Local/TgtApp///13128/Info(1042059)

Connected from [192.168.100.26]

[Tue Dec 02 05:24:22 2014]Local/TgtApp/db/admin@Native Directory/13128/Info(1013091)

Received Command [XREF/XWRITE Session Clearup] from user [admin@Native Directory]

[Tue Dec 02 05:24:22 2014]Local/TgtApp///9476/Info(1013214)

Clear Active on User [admin@Native Directory] Instance [1]

If you want to write XREF correct at first go, I will recommend to decide on the source and target combination, than, see what all dimensions are common on both cube and are POV, FIX them once done, source will come on the right side of XREF and target on the left, and your done!


I guess basics are done here. Now lets go ahead with What IFs:

1. What IF... the number of dimensions are more in target than in source

Here I have added an additional Value dimension in target, create a location alias _SrcConn_ to connect to the source cube. Below is the data at the source at certain combination:

Now lets run the same script.
"A11" = @XREF(_SrcConn_,"A11");

 As of now don't worry about the block creation, we will talk about it later in this post. Once the script is executed you will get following data at target:

Here is your answer if a dimension is additional in the target Essbase will automatically XREF the data at the top member of the missing dimension.

Again, what if... you want it at different combination, its simple just modify the left hand side of the script. Say you want the data at Base member, New Script will be:

"A11"
(
"Base"->"A11" = @XREF(_SrcConn_,"A11");
)

Member block is needed here as we have a cross dim on the left side of the equation. This calc will result in following:

2. What IF... the number of dimensions are more in source than in target
   In this case, the values will be picked from the top most member of the dimension which is not available. Ex: if we replace the source and target in above example, data will be picked from the value dim member, which will be like:
A11 = Value->A11;


3. What IF... the value at source is #missing, will it overwrite the existing values at target
Yes, will be the answer for that, values will be overwritten by #missing if the source data is missing.

4. What IF... you XREF a label only member combination
 It will follow the normal essbase logic, Label only member always points to the first child, so when you try to retrieve the data for them you will get the data present at the first child. It means when XREF will look for the value of Label only member, it will get some value and the same value will be referred from the source.

To illustrate that, lets change the source, Here we will try to Copy the data from Account (which is label 0) to A11 which is a stored member:

Data at source:

Script for that will be:

"A11" = @XREF(_SrcConn_,"Account");

Once the script is executed, below will be the data at the target:
                                  

5. What IF... you XREF a dynamic calc member combination
I am sure you will be knowing the answer of that by now.

6. What IF... a XREF member is not present in the source
Lets say, in the last script If I change Account to Account1:
"A11" = @XREF(_SrcConn_,"Account1");
This wont though the error when you execute the script in front end, but you wont get the any results, which is obvious. But if you go inside the essbase application logs, you can find the error:


[Tue Dec 02 06:52:04 2014]Local/TgtApp/db/admin@Native Directory/12352/Error(1023040)
msg from remote site [[Tue Dec 02 06:52:04 2014]192.168.100.26/SrcApp/db/admin/Error(1024009) Member Account1 specified for external reference formula does not exist]

[Tue Dec 02 06:52:04 2014]Local/TgtApp/db/admin@Native Directory/12352/Error(1200467)
Error executing formula for [A11]: status code [1024009] in function [@_XREF]

[Tue Dec 02 06:52:04 2014]Local/TgtApp/db/admin@Native Directory/12352/Warning(1080014)
Transaction [ 0x20010( 0x547dd214.0x71480 ) ] aborted due to status [1024009].

In case of viceversa of this What If, you will get the error while validating the script itself.

7. What IF... a value is XREF from source cube and its change later in the source
If you are XREFing the data using a calc script ,  your data will not be updated. However if the XREF is attached to dynamic calc member, data will be referred at run time.

Now Few Can I:

1. Can I... use XREF in ASO
No, you cant write location alias for an ASO cube however you can refer data from ASO.

2. Can I... use Substitution Variables in XREF 
Yes, you can.

3. Can I... Export the data for XREF Members
Yes, if its dynamic, then you need to make you export Dynamic is on.

4. Can I... use a function with the @XREF function, Ex: @RELATIVE("XXX" ,0) 
No, only those functions which returns only one member.

5. Can I... use Attributes and Attribute Operators in XREF
Yes


Lets talk about the block creation:

In order to populate the value at target, you have to create blocks at destination. This is the most common issue, even if you write the calc fine, it  may end up in no results. First thing you should look is the block creation. More about block creation is here


There is a catch and a work around for this: If you create a sparse member block, you don't need to create the block. Lets look into this with an example.

I have cleared data for the target cube. Now if we execute this script it will copy data of E2 (which is a member of sparse dim), even if will not create the block separately.


"E2"
(
"A11" = @XREF(_SrcConn_, Account);
)

Once this is executed, you will see the following at destination:

This happens because when you create the block of a sparse dim member, Essbase calculates it in top down mode and creates the block.


[Tue Dec 02 07:25:19 2014]Local/TgtApp/db/admin@Native Directory/10456/Info(1013163)
Received Command [Calculate] from user [admin@Native Directory] using [Xref.csc]

[Tue Dec 02 07:25:19 2014]Local/TgtApp/db/admin@Native Directory/10456/Info(1200481)
Formula for member [E2] will be executed in [TOPDOWN] mode

[Tue Dec 02 07:25:19 2014]Local/TgtApp/db/admin@Native Directory/14852/Info(1012675)
Commit Blocks Interval for the calculation is [3000]

[Tue Dec 02 07:25:19 2014]Local/TgtApp/db/admin@Native Directory/14852/Info(1012717)
Remote bitmap cache is [Disabled]

[Tue Dec 02 07:25:19 2014]Local/TgtApp/db/admin@Native Directory/14852/Info(1012669)
Calculating [ Entity(E2)]

[Tue Dec 02 07:25:19 2014]Local/TgtApp/db/admin@Native Directory/14852/Info(1012677)
Calculating in serial

[Tue Dec 02 07:25:19 2014]Local/TgtApp/db/admin@Native Directory/14852/Info(1012579)
Total Calc Elapsed Time for [Xref.csc] : [0.187] seconds

If you want to test it with BOTTOMUP, write below script and you will not have the block created and data.

"E2"
(
@CALCMODE(BOTTOMUP);
"A11" = @XREF(_SrcConn_, Account);
)



Cheers..!!
Rahul S.