Search code examples
essbase

Can someone explain the following Essbase code: FIX, @relative

Can someone please explain the below Essbase code to me please? This is my first time looking at any Essbase code and I'm getting a bit confused as to what it is actually doing.

FIX(&Mth, &Yr, &Version,
        "Sector1","Sector2", @relative("Source Code",0), @relative("Channel", 0) )
    FIX("AccountNo","DepNo")

    DATACOPY "1A11"->"A-500" TO "1BCD"->"C-800"; 

    ENDFIX
ENDFIX

From what I have googled the following is my understanding:

  1. Creates a new command block which restricts database calculations to this subset.

  2. Passes the following members into the command to be used:

    • Mth
    • Yr
    • Version

  3. Returns the following fields:

    • Sector1
    • Sector2
    • returns the 0-level members of the Source Code member - meaning it returns the members of the Total Source Code without children (no other dimensions)
    • returns the 0-level members of the Channel member - meaning it returns the members of the Channel without children (no other dimensions)

  4. Begins a new command block and passes the following members into the command to be used:

    • AccountNo
    • DepNo

  5. Copies the range of cells 1A11, A-500 over to the range 1BCD, C-800

The above is what I understand from the oracle documents on each of the functions, but I can't actually figure out what is happening.

Solution

  • Welcome to the world of Essbase; it can be a little daunting at first especially if you're new to the concept of multidimensionality. You are on the right track regarding analyzing your calc script.

    Try not to think of the FIX statement as a command block, per se. A FIX is used to select a portion of cells in the cube. Every single piece of data in your cube has a particular address that consists of one member from every dimension, plus the actual data value itself. For instance, a cube with the dimensions Time, Year, Scenario, and Location might have a particular piece of data at Jan->2018->Actual->Washington. The number of possible permutations of data in a cube can quickly get very large. For instance, if you're organization has 4 years of data, 12 months in a year, 100 locations, 10000 accounts, 3 versions, and 10 departments, you are talking about 4 * 12 * 100 * 10000 * 3 * 10 = 1.4 billion different potential addresses (cells) of data – and that's actually fairly small for a cube, as they tend to grow much larger.

    That said, FIX statements are used to narrow down the scope of your calculation operation, rather than operating on the ENTIRE cube (all 1.4 billion cells in my hypothetical example), the FIX essentially restricts the calculation to cells that match certain criteria you specify. In this case, the first FIX statement restricts the calculation to a particular month, yr, version, sectors, sources, and channels. Note that the ampersand on Mth, Yr, and Version means that a substitution variable is to be used. This means your server or cube has a substitution variable value set, such as the variable Mth = "Jan" and Yr = "FY2018" and Version might be "Working" or "Final" or something similar. I would guess that Sector1 and Sector2 are possibly two different members from the same dimension. @RELATIVE("Source Code", 0) is a function that finds the level-0 members (leaf/bottom-level members in a dimension, that is, members that do not have children below them) of the specified member.

    In other words, the first FIX statement is narrowing the scope of the calculation to a particular month in a particular year in a particular version (as opposed to all months, all years, all versions), and for that particular month/year/version (for either Sector1 or Sector2) it is fixing on all of the level-0/bottom/leaf members in Source Code and Channel dimensions.

    The next FIX statement just further narrows the current scope of cells to calculate on in addition to the outer FIX. It's not uncommon to see FIX statements nested like this.

    Lastly we get to the part where something actually happens: the DATACOPY. In the given FIX context, this DATACOPY command is saying that for EACH cell in the current FIX, copy values from the source to the destination. DATACOPY is a little more straightforward when it's just DATACOPY "Source" TO "Target" as opposed to using the inter dimensional operator (->)... but this is perhaps more easily understood in terms of the time/year dimensions. For example, imagine the data copy was written like this:

    DATACOPY "FY2018"->"Dec" TO "FY2019"->"Jan";

    In this DATACOPY I'd be telling Essbase that for the given FIX context I would like to copy values from the end of the year (data values where the year is FY2018 AND the month is Dec) to the beginning of the next year (data values where the year is FY2019 AND the month is Jan). Your DATACOPY is working in a similar fashion, but using cost centers or something else. It all just depends on how the cube is setup.