Enables a database calculation to incorporate values from a different database.
@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.|
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 the point of view on the data target or (2) defines a specific point of view on the data source. For example, the following formula modifies the point of view on the data target:
2003(2003->Jan->Inventory = @XREF(sourceDB,Dec);)
If the cube on the data source (sourceDB) contains data only from 2002, this formula sets Inventory for Jan in 2003 to the Inventory value for Dec 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 source (sourceDB) contains the member January. This formula simply maps the member in the data target (Jan) with its corresponding member in the data source (January), and pulls January from sourceDB.
See Notes for more information about the mbrList parameter.
The @XREF function is a cross-database function that allows a calculation taking place in one Essbase database to incorporate values from a different, possibly remote database.
The following terminology is used to describe the @XREF function:
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 Hyperion Essbase - System 9 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.
West = @XREF(SourceDb, @LEVMBRS(Market,0));
West = @XREF(SourceDb, Jan:Mar);
RedThings = @XREF(SourceDb, Sales, Red);
RedThings = @XREF(SourceDb, Sales, Red, Average);
For more information on attributes, see the Hyperion Essbase - System 9 Database Administrator's Guide.
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);
InflatDB is the location alias for the Inflation Rates Database and
Inflation is the member for which a data value is retrieved from
In this example, Essbase calculates the following member combinations:
Units->Qtr1->100-10->East->Budget = Units->Qtr1->100-10->East->Budget *
Units->Qtr2->100-10->East->Budget = Units->Qtr2->100-10->East->Budget *
and so on.
REFERENCECUBESIZELIMIT (configuration setting used with reference cubes)
create passive|active reference_cube (MaxL statement)
drop reference_cube (MaxL statement)
alter database (MaxL statement)
display reference_cube (MaxL statement)
display reference_cube_reg (MaxL statement)