Essbase @SPLINE

Applies a smoothing spline to a set of data points. A spline is a mathematical
curve that smoothes or interpolates data.

Syntax

@SPLINE (YmbrName [, s [, XmbrName [, XrangeList]]])
Parameter Description
YmbrName A valid single member name that contains the dependent variable values
used (when crossed with rangeList) to construct
the spline.
s Optional. A zero (0) or positive value that determines the smoothness
parameter. The default value is 1.0.
XmbrName Optional. A valid single member name that contains the independent variable
values used (when crossed with rangeList)
to construct the spline. The default independent variable values are 0,1,2,3,
and so on.
XrangeList Optional. A valid member name, a comma-delimited list of member names,
cross dimension members, or a member set function or range function (including
@XRANGE) that returns a list of members from the same dimension. If XrangeList is not specified, Essbase uses
the level 0 members from the dimension tagged as time.

Notes

  • XrangeList must contain
    at least two values.
  • If XrangeList contains
    gaps in the data (for example: Jan, Feb, Mar, Jun, Jul), be sure to specify XmbrName (for example: 0,1,2,5,6) so that correct
    results are returned.
  • The @SPLINE function skips #MISSING values in YmbrName and XmbrName;
    in the result, Essbase replaces the #MISSING values
    of YmbrName with the spline values.
  • The @SPLINE function calculates a smoothing cubic spline for
    (n > 0).
  • Setting the smoothness parameter (s)
    to 0 produces an interpolating spline, that is, a spline that fits the initial
    data exactly. Increasing s results in a
    smoother spline but a less exact approximation of the initial data.
  • The @SPLINE function can be used with the @TREND function
    to forecast future values that are based on the values smoothed with @SPLINE.
  • If you use an Essbase member set function
    to generate a member list for the XrangeList parameter
    (for example, @SIBLINGS), to ensure correct results, consider the order in
    which Essbase sorts the generated member list. For
    more information, see the Oracle Essbase Technical Reference topic for the member set function you
    are using.
  • When you use @SPLINE in a calculation script, use it within
    a FIX statement. Although using FIX is not required, it may improve calculation
    performance.
  • When you use @SPLINE across a large range in a sparse dimension,
    you may need to increase the size of the calculator cache. For more information
    on the calculator cache, see the Oracle Essbase Database Administrator’s Guide.
  • View the Algorithm for
    the smoothing spline.

Algorithm

Example

The following example is based on the Sample Basic database. Assume
that the Measures dimension contains an additional member, Sales Spline. The
formula calculates the spline of Sales values for Jan through Jun, based on
a smoothness parameter of 2.

"Sales Spline" = @SPLINE(Sales,2,,Jan:Jun);

This example produces the following report:

             Colas     Actual    New York
               Sales       Sales Spline
               =====       ============
Jan             645         632.8941564
Feb             675         675.8247101
Mar             712         724.7394598
Apr             756         784.2860765
May             890         852.4398456
Jun             912         919.8157517

See Also

  • @TREND