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.
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:
- A sparse Market dimension with 5,000+ legal entities (kiosks)
- A sparse Product dimension with 100,000+ base-level SKUs
Your Hyperion system contains:
- A BSO calculation cube, Rev, for calculating Revenue metrics
- No aggregated data or history
- An ASO model, POPRPT, for multi-purpose reporting and aggregation of revenue data
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.
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):
Into our BSO Return Ratio calculation using an @XREF formula from the ASO cube at the Legal Entity/Product level:
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:
ASO, BSO, @XREF, and You
This technique has proven useful in our clients’ Hyperion implementations under the following conditions:
- Client needs to drive base-level functional or allocation calculations using summary-level information not available in the BSO cube
- 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.