Relative functions in Hyperion essbase

Function Name, Syntax & Description	Notes	Example
@PARENTVAL (dimName [, mbrName]) Returns the parent values of the member being calculated in the specified dimension.	"Market Share" = Sales % @PARENTVAL(Market,Sales); Adding the "Market Share" member and formula to the outline would produce the same result as above.
@MDPARENTVAL (numDim, dimName1, . . . dimNameX [,mbrName])	Returns parent-level data from multiple dimensions based on the current member being calculated.
@SPARENTVAL (RootMbr [, mbrName])	Returns parent-level data based on the shared parent value of the current member being calculated.	· You cannot use the @SPARENTVAL function in a FIX statement. · The time required for retrieval and calculation may be significantly longer if this function is in a formula attached to a member tagged as Dynamic Calc or Dynamic Calc and Store.
@ANCESTVAL (dimName, genLevNum [, mbrName])	Returns the ancestor values of a specified member combination.	"SKU Share" = Sales % @ANCESTVAL(Product,2,Sales);
@MDANCESTVAL (dimCount, dimName1, genLevNum1. . . dimNameX, genLevNumX [,mbrName])	Returns ancestor-level data from multiple dimensions based on the current member being calculated.	Marketing = (Sales / @MDANCESTVAL(2, Market, 2, Product, 2, Sales)) * @MDANCESTVAL(2, Market, 2, Product, 2, Marketing);
@SANCESTVAL (rootMbr,genLevNum [, mbrName])	Returns ancestor-level data based on the shared ancestor value of the current member being calculated.	· You cannot use the @SANCESTVAL function in a FIX statement. · The time required for retrieval and calculation may be significantly longer if this function is in a formula attached to a member tagged as Dynamic Calc or Dynamic Calc and Store.
@ATTRIBUTEVAL (attDimName) Returns, for the current member being calculated, the associated attribute value from the specified numeric or date attribute dimension.	"Profit Per Ounce" = Profit/@ATTRIBUTEVAL(@NAME(Ounces)); Note: The @NAME function is required to process the string “Ounces” before passing it to the @ATTRIBUTEVAL function.	· The @ATTRIBUTEVAL function works only with numeric and date attribute dimensions. To return values from text attribute dimensions, use the @ATTRIBUTESVAL function. To return values from Boolean attribute dimensions, use the @ATTRIBUTEBVAL function. For more information on types of attributes, see the Database Administrator's Guide. · Only level 0 members of attribute dimensions can be associated as attributes of members of a base dimension. · If there is no attribute associated with the member being calculated, or if the attribute associated with the member is a text attribute, @ATTRIBUTEVAL returns #MISSING. · When the @ATTRIBUTEVAL function is used with a date attribute dimension, Analytic Services converts the date string to the number of seconds elapsed since midnight, January 1, 1970
@ATTRIBUTEBVAL (attDimName)	Returns, for the current member being calculated, the associated attribute value from the specified Boolean attribute dimension.
@ATTRIBUTESVAL (attDimName)	Returns, for the current member being calculated, the associated attribute value from the specified text attribute dimension.
@GEN (mbrName)	Returns the generation number of the specified member.	@GEN(Year): Returns 1.
@CURGEN (dimName)	Returns the generation number of the current member combination for the specified dimension. This number represents the number of members separating the current member from the top-most member of the dimension.	If the current member of the specified dimension is an implied share member, the member generation returned is the same generation as the stored member. For example, in Sample Basic, Inventory, a member of the Measures dimension, is an implied share member: Inventory Opening Inventory (+) Additions (~) Ending Inventory (~) The generation value of Inventory is the same as the stored member under it, Opening Inventory. For this example, Opening Inventory is at generation 3. When Inventory is the current member @CURGEN(Measures) returns generation 3.
@LEV(mbrName)	Returns the level number of the specified member.
@CURLEV (dimName)	Returns the level number of the current member combination for the specified dimension. This number represents the number of members that separates the current member from its bottom-most descendant.	If the current member of the specified dimension is an implied share member, the member level returned is the same level as the stored member. For example, in Sample Basic, Inventory, a member of the Measures dimension, is an implied share member: Inventory Opening Inventory (+) Additions (~) Ending Inventory (~) The value of Inventory results only from the value of Opening Inventory. When Inventory is the current member @CURLEV (Measures) returns level 0.

@XREF (locationAlias [, mbrList]): Enables a database calculation to incorporate values from a different database.

locationAlias: A location alias for the data source. A location alias is a descriptor that identifies the data source. The location alias specifies a server, application, database, username, and password. Location aliases are set by the database administrator at the database level.

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 members specified in mbrList
The members in the current point of view
The top member in any unspecified dimensions in the data source

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->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:Sales->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 Sales->January from sourceDB.

Description

The @XREF function is a cross-database function that allows a calculation taking place in one Analytic Services database to incorporate values from a different, possibly remote 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 target).
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).

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.

Notes

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. For example, consider the following formula: West->Inventory = @XREF(SourceDb, California, Oregon);

This formula would return two data values, one for Inventory in California and one for Inventory in Oregon. However, since the current point of view calls for only one data cell Inventory->West, an error is returned.

The member list cannot contain functions that return more than one member. For example, the following formula is not valid:

West->Inventory = @XREF(SourceDb, @LEVMBRS(Market,0));

The member list cannot contain ranges. For example, the following formula is not valid: West->Inventory = @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->Sales = @XREF(SourceDb, Red);
mbrList can contain attribute operators. For example, the following formula calculates RedThings as the average sales of all red products:

RedThings->Sales = @XREF(SourceDb, Red, Average);

For more information on attributes, see the 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 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 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 value #M1.

Main Database

Year

  Qtr1

  Qtr2

Measures

  Sales

  Units

Product

    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

Example:

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, Analytic Services calculates the following member combinations:

Units->Qtr1->100-10->East->Budget = Units->Qtr1->100-10->East->Budget *
Inflation->Qtr1->US

Units->Qtr2->100-10->East->Budget = Units->Qtr2->100-10->East->Budget *
Inflation->Qtr2->US
and so on.

Oracle Hyperion

Relative functions in Hyperion essbase

Koti Reddy Battula

0 comments :

Facebook Fan Page

Contact Us

Popular Posts