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
@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
Deepa
@XWRITE and @XREF are two calculation commands can be used for such operations.
- _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.
Deepa
Can i use substitution variable in XREF?
ReplyDeleteYES 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.
DeleteOtherwise 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
Good Article !!
ReplyDeleteI 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
I think Xref or will not work in the way you have written.
Deletefix ("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.
Hi Deepa,
ReplyDeleteAs 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
HI
ReplyDeleteYour 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
Hi ,
ReplyDeletePlease check the syntax :
@XREF (locationAlias [, mbrList])
You should specify the Location alias.Something like the alias name for your Database.Im a newbie too .
Hi ,
ReplyDeleteMy 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.
Hi,
ReplyDeleteCan 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.
Can we have a criteria for write in loopback statement on same data base.
ReplyDeletee.g FIX on entity with UDA1
while XWRITE to entity with UDA2
Can we have a criteria for write in loopback statement on same data base.
ReplyDeletee.g FIX on entity with UDA1
while XWRITE to entity with UDA2
Great piece of informative stuff nice I hope you will continue to share more in the future such as amazing content data copy tool
ReplyDeleteHow 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