Enables a database calculation to incorporate values from another Essbase database.
The following terminology is used to describe the @XREF function:
- Data target: the database on which the current calculation
is running (that is, the database on which the @XREF call originates).
- Data source: the database that is queried by the @XREF function.
This database may be remote (that is, on a different machine than the data
- Point of view: the member combination currently being calculated
on the data target (that is, the member combination that identifies the left
hand side of a calculation).
- Reference cube: a copy in memory of the source cube information,
improving @XREF processing time for certain situations.
The @XREF function retrieves values from a data source to be used in
a calculation on a data target. @XREF does not impose member and dimension
mapping restrictions, which means that the data source and data target outlines
can be different.
As arguments, this function takes a location alias, an implied list
of members that represents the current point of view, and an optional list
of members to qualify the @XREF query on the data source. The second argument
(the members making up the current point of view) is implied; that is, these
members are not specified as an @XREF parameter. An @XREF query that omits
the third argument indicates that a given data point in the data target will
be set to the same data point in the data source.
You can use reference cubes to reduce data-transfer overhead involved
with @XREF executions to source databases. See the Oracle Essbase Database Administrator’s Guide for details
about reference cubes such as why, when, and how to use MaxL statements
to set them up and manage them.
@XREF (locationAlias [, mbrList])
|locationAlias||A location alias for the data source. A location alias is a descriptor
that identifies the data source. The location alias must be set on the database
on which the calculation script will be run. The location alias is set by
the database administrator and specifies a server, application, database,
username, and password for the data source.
|mbrList||Optional. A comma-delimited list of member names that qualify the @XREF
query. The members you specify for mbrList are
sent to the data source in addition to the members in the current point of
view in the data target. The data source then constructs a member combination,
using in order of precedence:
The mbrList parameter (1) modifies
2003(2003->Jan->Inventory = @XREF(sourceDB,Dec);)
If the cube on the data source (sourceDB) contains data only from 2002,
The following formula defines a specific point of view on the data target:
Jan = @XREF(sourceDB,January);
Assume that the data target contains the member Jan, while the data
See Notes for more information about the mbrList parameter.
- An error is returned if the members supplied in mbrList do not exist in the data source.
- The number of data cells queried on the data source must match
the number of data cells expected on the data target.
- The member list cannot contain functions that return more
than one member. For example, the following formula is not valid:
West = @XREF(SourceDb, @LEVMBRS(Market,0));
- The member list cannot contain ranges. For example, the following
formula is not valid:
West = @XREF(SourceDb, Jan:Mar);
- mbrList can contain attribute
members. For example, if the data source classifies products based on a color
attribute, the following formula would calculate the sum of the sales of all
red products and would assign the result to member RedThings:
RedThings = @XREF(SourceDb, Sales, Red);
- mbrList can contain attribute
operators. For example, the following formula calculates RedThings as the
average sales of all red products:
RedThings = @XREF(SourceDb, Sales, Red, Average);
For more information on attributes, see the Oracle Essbase Database Administrator’s Guide.
- @XREF can query all types of members. For example, members
retrieved from a data source can be Dynamic Calc members as well as attribute
members. Keep in mind that all performance considerations that apply to dynamic
and attribute calculations also apply to @XREF queries that depend on dynamic
and attribute members. For more information, see the Oracle Essbase Database Administrator’s Guide.
- Over the course of an @XREF calculation, data in the source
database may change. @XREF does not incorporate changes made after the beginning
of the calculation.
- @XREF is a top-down formula. For more information on top-down
formulas, see the Oracle Essbase Database Administrator’s Guide.
- For a member that does not exist in either the data source
or the data target, @XREF returns the value of the top dimension, not the
- If you are using the @PARENT function within @XREF: the @XREF
function requires the @NAME function to be used around @PARENT. For example:
For this example, consider the following two databases:
Year Qtr1 Qtr2 Measures Sales Units Product 100 100-10 100-20 Market East West Scenario Budget Forecast
Inflation Rates Database
Year Qtr1 Qtr2 Assumptions Inflation Deflation = Inflation * .5 (Dynamic Calc) Country US Canada Europe
The following formula is associated with the Main Database:
Units = Units * @XREF(InflatDB,Inflation,US);
Where InflatDB is the location alias for the Inflation
Rates Database and Inflation is the member for which a data value
is retrieved from InflatDB.
In this example, Essbase calculates the following
Units->Qtr1->100-10->East->Budget = Units->Qtr1->100-10->East->Budget
Units->Qtr2->100-10->East->Budget = Units->Qtr2->100-10->East->Budget
*Inflation->Qtr2->US and so on.
- Create Reference_Cube
- Drop Reference_Cube
- Alter Database
- Display Reference_Cube
- Display Reference_Cube_Reg