The Short of It

Using the @XREF function, we can leverage consolidated data from an Aggregate Storage database to drive Block Storage (Essbase/Planning) calculations in a fast, dynamic process.

Example

Your company (Pop Inc.) sells snacks and drinks from kiosks. You calculate driver-based revenue metrics for an array of FP&A exercises based on Year-Over-Year impact. Pop Inc.’s org structure resembles the following:

  1. A sparse Market dimension with 5,000+ legal entities (kiosks)
  2. A sparse Product dimension with 100,000+ base-level SKUs

Your Hyperion system contains:

  1. A BSO calculation cube, Rev, for calculating Revenue metrics
    • No aggregated data or history
  2. An ASO model, POPRPT, for multi-purpose reporting and aggregation of revenue data

Problem
One component of Pop Inc’s revenue calculations is a Year-Over-Year (YOY) comparison of product return volume. This driver is calculated:

Return Ratio = 1 / 2 where:

1 = YTD Return Volume (Returns) for one SKU (base level product/legal entity)
2 = Prior Year YTD Returns across ALL PRODUCTS/LEGAL ENTITIES

The catch is that 2 is only available in the POPRPT ASO cube.

Solution

In this example, let’s say it is currently March. To calculate Return Ratio, we need to bring the March YTD total returns from last year (2):

Calculating a returns ratio using ASO summary data

Into our BSO Return Ratio calculation using an @XREF formula from the ASO cube at the Legal Entity/Product level:

Using an @XREF formula from the ASO cube

This results in a YOY volume rate reflected in the Rev cube for each SKU. This example examines Cola returns at a Buffalo NY Kiosk, and utilizes a member (XREF Return Validation) with a formula to verify the correct total is referenced:

YOY volume rate reflected in the Rev cube for each SKU

 

ASO, BSO, @XREF, and You

This technique has proven useful in our clients’ Hyperion implementations under the following conditions:

  1. Client needs to drive base-level functional or allocation calculations using summary-level information not available in the BSO cube
  2. Creating many member formulas for account driver ‘storage’ is undesirable

This methodology can drastically reduce allocation processing times when compared to aggregating BSO values in a vastly sparse cube.

 

Questions?   Comments?  Feel free to reach us at:

Jay Adler, jadler@ecapitaladvisors.com

Amy Stine, astine@ecapitaladvisors.com

Amy Stine

Over the past 15 years, Amy has focused on designing and implementing planning solutions for Finance, Marketing and Sourcing functions. Technologies include Oracle Planning and Budgeting Cloud Service, Oracle Hyperion Planning, Oracle Essbase, SAP CO-PA, SAP BW-BPS, SAP CRM, and SAP BPC NW.