A Better Average

Past Performance is no guarantee of the Future

How many of you have a metric based on an average over time (i.e. Average Sales for the past 12 months)? Simple mathematical averages are a great tool to quickly compare results to an expected result, based on historical performance. Unfortunately, in their simplicity also lies a key problem: They assume results will be flat, whereas real-world results will often plot onto a curve. Often, changes are not linear either due to seasonality, or an inherent exponential factor underlying a result.


Seasonality

Few, if any, businesses do not have any seasonality. Retail sales are greatest toward the end of the year, tourist destinations have the highest bookings during their respective high season, and real estate transactions occur more frequently in the spring. Activity for these businesses, at least in part, is affected by external factors causing results to fit a curve.


Exponential factors

While not necessarily readily apparent, often activity within an organization will change in an exponential manner.

• A simple example is interest income: Over time interest earned on an investment will exponentially increase due to compounded returns (this assumes no drawdown of the investment, as well as reinvestment of returns). Depending on the size of the investment, over a short period it can be feasible to assume a linear growth, but this will introduce greater and greater variances over time.
• A more complex example can be seen in revenues. Often, a portion of revenues are reinvested into generating greater revenues. Conceptually, this scenario is similar to the compounded interest scenario. This differs in that it is often difficult to determine a rate of growth based on an amount reinvested in a business.

Essbase to the rescue!

Among Essbase's many built-in functions is @TREND. In layman's terms, @TREND calculated a weighted average of a series of values. Options allow for fourdifferent weighting algorithms:

• Linear Regression - Standard linear regression (similar to a typical average), with an option to assign priority to points to adjust the importance of certain events.
• Single Exponential Smoothing - Weighting system giving more importance to earlier values than later values. Allows for an adjustment to how much more weight is applied to earlier values.
• Double Exponential Smoothing - Similar to Single Exponential Smoothing, but includes an additional adjustment to influence the resulting slope, or curve, of the result.
• Triple Exponential Smoothing - Builds on Double Exponential Smoothing with a third influence factor. This algorithm is particularly useful for seasonal values.

Some points to consider:

• #MISSING values: some algorithms remove #MISSINGs from the list of values (i.e. they are not treated as zeros), other algorithms do not allow any values to be #MISSING.
• Usage: The function may only be used in a calculation script; it may not be placed within a member formula. Inside of a calculation script the function must be associated with a member.
• The order of members passed to the function will influence the result, since weights are applied differently depending on where in the list a value appears. Consider carefully the order of members in the outline, whether that order will change, and what impact that will have. It may be useful to utilize the @LIST function to hardcode a specific order.

For more information on @TREND, see the "Trend Calculation Function" in the Essbase Technical Reference (this is available via the Oracle website if it is not installed on your system).

Building Calculations in Oracle BI Pivot Tables

Recently, I ran into a situation with designing a report to appear exactly like an Excel spreadsheet that our client's finance department used for years. Using the pivot table view, I was able to mimic 90% of the Excel spreadsheet. The remaining 10% that remained incomplete was creating group subtotals and grand totals. A colleague of mine, Kevin McGinley, an individual known for his dashboard prowess, suggested creating the pivot table report by building out each row calculation using the option "New Calculated Item," which can be found by clicking on the More Options button in the column.



Following the advice of my colleague, I was able to mimic the Excel spreadsheet 100%.

To build a calculation for an item in an Oracle BI pivot table, you need to do the following:

1. In the Pages, Sections, Rows, or Columns area, click the More Options button for the measure on which you want a calculation performed.

2. Select the option New Calculated Item. The Calculated Item window appears.

3. Assign a name for the calculation in the Name field.

4. To build a formula, choose the Formula function.

• NOTE: A formula creates a dynamic custom grouping within the pivot table. All measures references in a formula must be from the same logical column and must be present in the results. Formulas can be inserted into, or combined with, other calculations.

The mathematical operators become visible. The operators are shown in the following table.

+ Plus sign, for an addition operation in the formula.
- Minus sign, for a multiplication operation in the formula.
/ Divide by sign, for a division operation in the formula.
$ Dollar sign, for acting upon the row position of an item in a formula.
( Open parenthesis, to signify the beginning of a group operation in the formula.
) Close parenthesis, to signify the ending of a group operation in a formula.

• In the Function field, build the formula by typing or clicking measure names, and clicking operators to insert them into the formula.
• Use parentheses where appropriate.

5. When the calculation is complete, click Finished.

Keep in mind that this option isn't perfect. In fact, the main drawback is that each calculated row CANNOT be moved up and down the list of calculated items. If you've created 25 new calculated items and forgot to create an item that should be located at the 10th spot, then you'll have to recreate ALL of the calculated items after #10.

Another draw back that I have encountered is that if your column formulas contain calculations, those calculations may or may not work correctly. This example is more specific to the situation that I encountered and may not apply, but take notice.

The alternative to doing this is building union queries using "Combine with Similiar Request" option in Criteria. In my particular situation, union queries was not an option because there would be too many queries and performance would have suffered significantly.