Month: March 2015

Introduction to Essbase Hybrid Aggregation Mode

The Best Thing Since Sliced Data

If you haven’t heard about Essbase Hybrid Aggregation Mode, you’re not alone. Oracle slipped this new calculation engine into the 11.1.2.3.500 patch without much fanfare. Documentation was light, and the list of limitations was long, however Oracle’s release strategy allowed early adopters to try out some fairly amazing new features. As a side benefit, the lack of documentation effectively prevented anyone from trying Hybrid Aggregation Mode in a production environment before the technology had time to “mature”.

When 11.1.2.4 was released, the list of limitations started to shrink. It reminded me of the release of ASO Essbase (when there was no writeback). That limitation didn’t last long, and the product became much better over time. I suspect we can anticipate the same rapid progress with Hybrid Aggregation Mode.

So what is it?

Hybrid Aggregation Mode is an option applied to BSO Essbase cubes that combines the strengths of BSO and ASO into a single cube. In simple terms, the bottom of the cube is BSO, while upper-level sparse member combinations are calculated dynamically . . . ASO-style.

2-23-2015 9-29-17 PMThe Benefits

This combination of features allows something special. It allows the BSO portion of the cube to be calculated using normal BSO calc scripts. But here’s the magic . . . sparse dimensions do not need to be aggregated. Just like an ASO cube, these aggregations are performed when the user retrieves data, in memory. This means developers don’t need to worry about level-0 data not aggregating correctly just because an agg script hasn’t completed. Sparse aggregations are effectively real-time, which is a serious step towards OLAP nirvana.

This design has some really interesting side effects. Because there are so few blocks in the cube, database restructures are much faster. Developers have a certain amount of control here as they can determine how much of the cube is BSO vs. ASO (I’ll show you how in our example). This has a follow-on effect of faster batch times and lower disk space requirements.

The most important benefit is reduced complexity.  Think of all of the applications out there that cobble together BSO and ASO cubes for maximum performance. These solutions require some serious jujitsu to keep hierarchies, data and security in sync. Now imagine these solutions reduced to a single cube . . . sounds pretty good, right?

The Catch

Unfortunately, the news isn’t all good. When a user retrieves data, Essbase will first try to perform the calculation (or aggregation) using Hybrid Aggregation Mode. If this is not possible (and there are a number of reasons why this might be the case), Essbase will try to perform a dynamic sparse calculation using Block Storage Mode. You may recall from Essbase Bootcamp that dynamic sparse calculations are not very efficient. Depending on how many blocks the calculation requires, these calculations can result in very poor retrieval performance. With enough users executing these performance crushing retrieves, the developer is essentially launching a denial of service attack on their own Essbase server. Therefore, it is very important to understand what calculations will fire in Hybrid, and what calculations will not. Let’s explore the details in our example below.

The Steps

The main steps for leveraging Hybrid Aggregation Mode are as follows:

  1. Create a BSO Essbase Cube
  2. Modify the Essbase Config File
  3. Set Upper-Level Sparse Members to Dynamic
  4. Load Data
  5. Calculate “Base” Blocks (if necessary)
  6. Retrieve Data
  7. Review the Log File

Step 1:  Create a BSO Essbase Cube

I’m going to assume most readers can create a BSO Essbase cube. There’s no need to build a fancy cube to play with Hybrid Aggregation Mode. I’m going to use a RedBox demo I built for a presentation to the North Texas Hyperion User Group. It’s simple, but it’s large.  It includes every RedBox location in the country.

3-2-2015 8-02-53 PM

Step 2:  Modify the Essbase Config File

To enable Hybrid Aggregation Mode, a setting is required in the Essbase Config file called “ASODYNAMICAGGINBSO”. This setting can be enabled or disabled for an entire server, application or specific database.

3-2-2015 8-23-59 PM

Developers can select from “Partial”, “Full” or “None” mode.

  • Partial enables Hybrid Aggregation Mode for simple outline aggregations (+, -, ~) only. Everything else (formulas, other unary operators, etc) will fire in Block Storage Mode.
  • Full enables Hybrid Aggregation Mode for simple outline aggregations (+, -, ~) and for certain formulas.  Note that there are quite a few limitations that we will cover later.
  • None disables Hybrid Aggregation Mode. This is the default.

Additional details on this setting for version 11.1.2.4 can be found here in the Essbase technical reference. Developers using version 11.1.2.3.500 will need to review the patch readme files.

Don’t forget to restart Essbase after updating the Config file.

Step 3:  Set Upper-Level Sparse Members to Dynamic

If you’ve worked with Essbase for very long, you probably know that Dynamic calculations work best with Dense dimensions and can be used sparingly with certain Sparse dimensions. But under no circumstances would we ever set all of our upper-level Sparse members to Dynamic . . . until now.

It seems crazy, but if you want to leverage Hybrid Aggregation Mode, you will need to set at least some of your upper-level sparse members to “Dynamic Calc”. In this example, I’m going to set them all to Dynamic. Just remember . . . any sparse members not set to dynamic will need to be aggregated with a calc script (assuming they are aggregated dimensions).

3-2-2015 11-36-54 PM

Step 4:  Load Data

We will need data for testing purposes. Mine is all fake test data . . . about 48,000 records.

Step 5:  Calculate “Base” Blocks (if necessary)

If you’ve ever written a procedural calculation for an ASO cube, you know they’re a bit clunky (even with the enhancements to Calc Manager). Hybrid Aggregation Mode gets us away from this technology by allowing us to use BSO Calc Scripts against the BSO portion of the cube. Remember, the “BSO portion of the cube” is represented by the non-dynamic sparse member combinations. In my example, this is simply level zero sparse members (because I set all aggregated upper-level sparse members to “Dynamic Calc”).

So if you need to run a stored calculation, break out your old BSO calc scripting skills! But make sure you only reference the BSO portion of the cube in your calc script, otherwise, you’ll take a performance hit.

Step 6:  Retrieve Data

This is it . . . La Pièce de Résistance!!!  We can now retrieve data at upper-level sparse member combinations, and we never had to aggregate our cube. In fact, our cube has no upper-level blocks at all.

3-2-2015 11-48-23 PM

Some interesting notes about the query above:

  • Note that Hybrid now supports Dynamic Time Series & Time Balance (not supported in 11.1.2.3.500).
  • In Block Storage Mode, without pre-aggregation, this query would have required over 40,000 blocks.
  • In Hybrid Aggregation Mode, the query only took 0.38 seconds (with no tuning whatsoever).

3-2-2015 11-56-35 PM

Step 7:  Review the Log File

The best way to verify that a query successfully ran in Hybrid Aggregation Mode is to look in the application log.

3-2-2015 11-44-47 PM

The green circle above indicates that a query ran in Hybrid Aggregation Mode.

Here’s an example of a query that did not successfully run in Hybrid Aggregation Mode.

3-3-2015 12-06-37 AM

You’ll want to monitor your logs to ensure the text above (circled in red) isn’t showing up.  The good news is that if Hybrid fails, Essbase will tell you why. The error above tells me that it failed because I have a member called “ThisIsABadAccount”, and it has a cross-dimensional operator in the formula.  We will cover Hybrid Aggregation Mode limitations in more detail below.

3-3-2015 12-10-16 AM

It should be noted that the member formula above is perfectly acceptable as long as you’re retrieving data in the BSO section of the cube. But once you stray into the ASO section (think upper-level sparse member), the query goes south and reverts back into Block Storage Mode (and becomes quite slow).

Certain Limitations Apply

There is a healthy list of limitations regarding formulas that can execute in Hybrid Aggregation Mode. But keep in mind these only apply to calculations you’re running in memory in the ASO portion of the cube, and not to stored calculations that you may execute as part of a calc script on the BSO portion of the cube. None of the limitations below apply to your BSO calc script as long as it is referencing stored sparse member combinations.

Some things you can not do in Hybrid Aggregation Mode:

  • Attribute Calcs
  • Formulas with Cross Dimensional Operators
  • Dynamic Calcs with Formulas that are Target of Transparent Partition
  • Queries with both Two Pass and One Pass Dynamic Calc members from Same Dimension
  • XOLAP
  • Text Measures

Any of the above situations will cause your Essbase query to revert back to Block Storage Mode. This doesn’t mean that the query won’t work. But it probably means it’ll be slow. Potentially very slow.

In addition to the limitations above, there are restrictions on formulas that reference other dimensions. The situations below will work in Hybrid Aggregation Mode.

(Anything else will not.)

  • Sparse-to-Sparse:   Formula on Sparse member . . . only references other Sparse members.
  • Dense-to-Dense:   Formula on Dense member . . . only references other Dense members.
  • Sparse-to-Sparse_AND_Dense:   Formula on Sparse member . . . references both Sparse and Dense members. In this case, dense members must be stored.

Lastly, there are certain formulas that are not supported with Hybrid. For a complete list, click here. This list is different for 11.1.2.3.500, so check the Essbase readme file if you’re using that version.

Remember that by querying members with formulas, Essbase will revert to Block Storage Mode if the “ASODYNAMICAGGINBSO” property is set to “Partial”. Use “Full” if you plan on leveraging member formulas with Hybrid Aggregation Mode.

An Ounce of Prevention is Worth a Pound of Cure

We spend a lot of time testing our cubes. But what if we miss something? Imagine a scenario where your user executes a query, and for whatever reason, the query reverts back to Block Storage Mode. Essbase is now feverishly trying to pull a bazillion blocks into memory. Now imagine that lots of other users are running the same query. The lights in the building start to dim . . . you might want to put a query governor in place.

We can limit either the amount of time a query will run, or the number of blocks a query can reference (or both).

QRYGOVEXECTIME [appname [dbname]] n

– Sets max amount of time a query can execute before being terminated

– Measured in seconds

QRYGOVEXECBLK [appname [dbname]]

– Sets max number of blocks a query can retrieve before being terminated

Conclusions

When Hybrid Aggregation Mode came out with the 11.1.2.3.500 patch, there were two deal-killer limitations. Both Dynamic Time Series and Time Balance account settings would force calculations back to Block Storage Mode. And with the limitations on formulas, it was impossible to create your own custom DTS or Time Balance. Both of these limitations were addressed with 11.1.2.4. Many of the remaining limitations appear to have workarounds (try moving complex calc logic to calc scripts). In my opinion, the limitations are getting much less restrictive.

Is Hybrid ready for production cubes? Maybe. That will depend on risk tolerance within your organization. It’s still very new. But with the enhancements in 11.1.2.4, the documentation, and the support for usage with Hyperion Planning . . . it’s getting tempting.

Pop Quiz!

You’ve read this far . . . surely you want to test your knowledge, right?

I’ll post answers on the blog in a week or so.

  1. Which of the following steps are required to enable Hybrid Aggregation Mode?
    1. Essbase Config File Setting
    2. Create a BSO Essbase Cube
    3. Set Upper-Level Dense Members to “Dynamic Calc”
    4. Pray to the Hyperion Gods
  2. True or False:   Essbase 11.1.2.3.500 supports dynamic time series with Hybrid Aggregation Mode.
  3. True or False:   Sparse dynamic calculations are generally fast in Block Storage Mode.
  4. True or False:   Essbase does not create upper-level blocks associated with dynamic sparse member combinations when Hybrid Aggregation Mode is enabled.
  5. True or False:   A formula that is not technically supported in Hybrid Aggregation Mode may actually run in Hybrid Aggregation Mode if the query avoids upper-level sparse dynamic member combinations.
  6. True or False:   The default “mode” for the “ASODYNAMICAGGINBSO” Essbase config file setting is “None”.
  7. True or False:   Developers should use Essbase query governors if Hybrid Aggregation Mode is enabled.
  8. True or False:   Multiplication and Division unary operators will function in Hybrid Aggregation Mode.
  9. True or False:   A formula on a Dense member that references a Sparse member will execute in Hybrid Aggregation Mode.
  10. True or False:   A query that includes both a Two-Pass and a One-Pass dynamic calc member from the same dimension will execute in Hybrid Aggregation Mode.

Bonus Question!  (This wasn’t covered in the post, so you may have to search . . . )

True or False:   The file system associated with the ASO-style portion of a Hybrid cube disappears when the application is stopped.

I hope you’ll take Hybrid Aggregation Mode for a spin!