Tuesday, July 30, 2013

How XWRITE and XREF can be used …………….

The main intension of writing this Post is to describe how @XREF and @XWRITE can work in similar way.
Sometimes you will be in the need of writing or copying the data between cubes either they can be from within the application or they can be from remote server application.
@XWRITE and @XREF are two calculation commands can be used for such operations.
For example you have two databases(cubes ) called A1 and B1 and they both share different outline structure like below.
Cube :: A1 Outline looks like below.
Account
Sales
Expenses
Period
Q1
Jan
Feb
Mar
Market
East
West
Scenario
Actual
Budget
Year
2011
2012
2013
Cube :: B1 Outline looks like below.
Account
East_Sales
East_Expenses
Period
Q1
Jan
Feb
Mar
Department
Dep_101
Dep_102
Year
2011
2012
2013
 
@XREF: For example East_Sales->Jan->Dept_101->2011intersection of B1 cube has to get data from sales->Jan->East->Budget->2011 intersection in the A1 cube. Nothing but we are copying the data from A1 cube to populate data in B1 cube .
Below calculation script can be referred in such cases,which is written under B1 cube.
FIX(“Jan”,”Dept_101”,”2011”)
“East_Sales”=@XREF(_A1alias_,”Sales”,”Budget”);
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.
 
@XWRITE: Even this works in the same way but source and target are interchanged here. That is we write this calculation script in Source cube that is in A1 cube in our case. So whenever you think data is ready we can run the below calculation to write the data from A1 cube to B1.
FIX(“Jan”,”East”,”Budget”,2011”)
“Sales”
(
@XWRITE(“Sales”,_B1alia_,”East_Sales”,”Dept_101”);
)
ENDFIX

  • 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.

Deepa

13 comments:

  1. Can i use substitution variable in XREF?

    ReplyDelete
    Replies
    1. YES you can use,but make sure that variable should be declared at application level OR that varible should be declared under the DATABASE from where you are running the calculation.
      Otherwise if you use the varible declared at the SOURCE DB level DESTINATION DB can not recognize it and give you an error message.

      Thanks,
      Deepa

      Delete
  2. Good Article !!

    I am wondering if I can adopt it to my situation. In my production system, I have accounts dimension with multiple (multi generation) members BS, PnL etc. In addition, I have Key figures member also as member in Accounts Dimension. The key figure parent member "HR Keyfigures" contains hundreds of child records. I wanted to copy only the child records (level 0, but generation 5 in Accounts Dimension) from one cube to other. I used something like this

    fix ("Key figures Reporting"); /* Label only memeber */

    @children ("HR Keyfigures") = @xref(DBAlias,@children ("HR Keyfigures"));

    endfix

    but I got error 1012016

    Error: 1012016 Cannot calculate dimension member [HR00.001 No. of clerks] with restricted member [Key figures Reporting]

    Any thoughts if it could be solved?

    thanks,
    John

    ReplyDelete
    Replies
    1. I think Xref or will not work in the way you have written.

      fix ("Key figures Reporting"); /* fix the intersection you want to get the values from */

      Memeber/* this should level0 member which is not inluded in above fix*/

      @children ("HR Keyfigures") = @xref(DBAlias,@children ("HR Keyfigures"));/* you can not use @Children on left side*/
      "HR Keyfigures"=@xref(DBAlias,Members(here you have to mention list of non unique member names))
      endfix

      so for your example you can FIX on all the account that want to refer from other DB.
      Also select a member from anyother dimention to write your XREF.
      You have mention only the members that are only available un Source.

      If that doesnt work reply me back with more details.

      Thanks,
      Deepa.

      Delete
  3. Hi Deepa,
    As suggested by you, I tried something like this

    fix (jan,2014,Local GAAP, "local currency", actual,@descendants (BusinessWest,0) );

    "HR Keyfigures" = @xref(DBAlias, @children("HR Keyfigures");

    endfix

    I still got the error

    Error: 1012037 Custom calculation is not allowed for [Label] share member [HR Keyfigures]

    In my scenario I have HR Keyfigures as Label only member and below this are level 0 members.

    When I assign Level 0 member from one cube to other it works, but copying all the level 0 members one by one using xref brings no advantage.

    Any thoughts/workaround on this?


    thanks in advance,
    John

    ReplyDelete
  4. HI

    Your Blog was awesome.
    I started to write a calculation script on XREF


    This is my script


    FIX("DESCENDANTS("Income Account",0),"Jan","FY14","Current","BU Version_1","Entity","Local")
    "Salary"=@XREF(2001,"Product Cost");
    ENDFIX


    where "Salary" is target cell
    2001 is alias name of Source cell
    "Product Cost" is name of Source cell


    I haven't used SOURCE member which are unique..


    I am getting LEXICAL error..

    Can u help me with this.

    Pragadeesh

    ReplyDelete
  5. Hi ,

    Please check the syntax :

    @XREF (locationAlias [, mbrList])

    You should specify the Location alias.Something like the alias name for your Database.Im a newbie too .

    ReplyDelete
  6. Hi ,

    My XREF calculation script is



    fix("Jan","Appload","Current","Adyar","Local","FY14")
    "Donations"=@xref("_Plan2_","Product sales","New York","INR","Actual","Abc");
    endfix


    where _Plan1_ is alias name
    and others are dimension members and i tried to copy fromone application to another.
    My script is verified and deployed successfully. While running the script i am getting error as

    //error detected while attempting to run job.
    Can anyone please give me suggestions.
    Thanks in advance.

    ReplyDelete
  7. Hi,

    Can we use Xwrite and Xref to copy data from one Hyperion planning application to another or only between the plan types of a same Planning application

    Thanks and Regards.

    ReplyDelete
  8. Can we have a criteria for write in loopback statement on same data base.

    e.g FIX on entity with UDA1
    while XWRITE to entity with UDA2

    ReplyDelete
  9. Can we have a criteria for write in loopback statement on same data base.

    e.g FIX on entity with UDA1
    while XWRITE to entity with UDA2

    ReplyDelete
  10. Great piece of informative stuff nice I hope you will continue to share more in the future such as amazing content data copy tool

    ReplyDelete
  11. How do you know that you are only inputting into the east sales data from the east market in cube A1? Aren't you missing parameters in the xref?

    ReplyDelete