Essbase

Excuses . . . Excuses . . .

I do my best to publish as least one meaty, technical EPM blog post per month. But that’s just not going to happen this month. I have three excuses of varying quality:

• Excuse #1:  My current project is in “sprint” mode with some aggressive development timelines.
• Excuse #2:  I’m working on my presentation for Top Gun 2015 US.
• Excuse #3:  I have a new toy.

I wouldn’t normally blog about that last excuse (the toy), but I’m going to try to tie it in with Excuse #2.

On September 17^th, I’ll be speaking at Infratects’ Top Gun US 2015 conference. I’ll be presenting on Essbase Hybrid Aggregation Mode, and I’m very excited about the opportunity.

I have two rules for these types of presentations:

• Rule #1: Show a live demo of something really interesting.
• Rule #2: Have some “giveaways” for people who ask questions.

Because I needed some “giveaways”, and because Essbase databases are typically referred to as “cubes”, I obviously had to go and buy a MakerGear M2 3D Printer kit and make some 3D printed “Gear Cubes”. I had no choice, really . . . so if you attend my presentation and ask an engaging question, you could be the proud new owner of one of these:

Top Gun US 2015 has some amazing speakers and should be a great opportunity to network with Oracle product management and your EPM peers. I hope to see you there!

 

 

There is currently a lot of excitement around the new Essbase Hybrid Aggregation Mode. As this new feature matures, clients are starting to ask about the types of cubes that are best suited for conversion to the new calculation engine. While Hybrid Aggregation Mode is pretty amazing, a side project of mine recently reminded me of a specific class of cubes that are not yet appropriate for conversion.

First, a bit about the side project . . . I’m working on building a set of “reference” cubes that I can easily deploy in various environments to benchmark physical (and virtual) infrastructure. Imagine a set of cubes that run through a standard series of data loads, calculations and retrieves where the performance is recorded for comparison against other environments. The idea is to arrive at a “score”, so I know early in an implementation when I’m working on suboptimal hardware. For this to work, I need some really big cubes (BSO, ASO and Hybrid). And when one builds really big cubes, they are sometimes reminded of Essbase’s size limitations, because when reached . . . dimension builds fail. These limits are very nicely documented here.

The basic Essbase size limitations are pretty straight-forward:

• BSO cubes can have a maximum of roughly 1,000,000 members.
• ASO cubes can have a maximum of roughly 10,000,000 – 20,000,000 members.

Hybrid cube limitations are not specifically called out in the documentation (yet), but we can make some assumptions:

• We know that Hybrid cubes start out life as normal BSO cubes.
• We assume that BSO limits apply to Hybrid cubes. (My limited testing appears to confirm this.)
• We know that BSO cubes have lower size limitations than ASO cubes.
• Therefore, certain large ASO cubes can not be converted to Hybrid.

In addition to the basic size limitations above, there is a slightly different limit that developers are more likely to encounter:

• BSO cubes can have a maximum of 2¹⁰⁴ stored sparse member combinations.

Here’s what makes this limit so interesting . . . the documentation (which is very, very good overall) is incorrect. BSO cubes are not limited to 2¹⁰⁴ stored sparse member combinations. Instead, they are limited to “Two Groups of 2⁵²”stored sparse member combinations. What does that mean? How do we know the documentation is wrong? Let’s dig a little deeper. 

Over 20 Nonillion Stored Sparse Member Combinations!

2¹⁰⁴ is a very large number. It’s a smidge over 20 nonillion. Don’t know what a “nonillion” is? I didn’t either.

2¹⁰⁴ = 20,282,409,603,651,700,000,000,000,000,000

To understand this limit, we must first understand how to calculate the number of potential stored sparse member combinations. (These are “potential” combinations until there is data at a particular intersection of members . . . then they become “actual” combinations.) To arrive at the number of potential stored sparse member combinations, simply multiply the number of stored members from each sparse dimension together.

For example:

• Sparse Dimension #1: 10 Stored Members
• Sparse Dimension #2: 30 Stored Members
• Sparse Dimension #3: 100 Stored Members
• Sparse Dimension #4: 1000 Stored Members

10 * 30 * 100 * 1000 = 30,000,000 Potential Stored Sparse Member Combinations

In other words, there are 30 million unique possible combinations of sparse members if we take one member from each of the above four dimensions.

If we were in fact limited to 2¹⁰⁴ potential stored sparse member combinations in a BSO cube, it is unlikely that anyone would hit this limit. This is because another limit would most likely kick in first. Remember, developers can have a maximum of roughly 1,000,000 members in a BSO cube. Try arriving at 2¹⁰⁴ potential stored sparse member combinations when you only have a million total members to work with . . . it’s possible, however it requires an unusual number of sparse dimensions. 

Will the Real BSO Limit Please Stand Up?

If the 2¹⁰⁴ limit is incorrect, what is the real limit? Luckily, Essbase returns the correct error message during a dimension build . . . it’s only the documentation that is incorrect. Here is what shows up in the Essbase application log after the dimension build fails:

It’s easy to see how the 2¹⁰⁴ limit was incorrectly derived. 2 * 2⁵² = 2¹⁰⁴, right??? Wrong.

2⁵² = 4,503,599,627,370,500

2 * 2⁵² = 9,007,199,254,740,990   (Note that this number is much less than 20+ nonillion or 2¹⁰⁴.)

However, don’t be fooled into thinking that the real limit for potential stored sparse member combinations in a BSO cube is 9,007,199,254,740,990. That’s not correct either. Remember, we get two groups of 2⁵² stored sparse member combinations. How the dimensions fall into these two groups is very important. 

Show Me the Groups!

Here are the basic steps for determining whether or not you will exceed the “Two Groups of 2⁵²” limit:

1. Locate the first sparse dimension (closest to the top of the outline).
2. Multiply the number of stored members in this dimension with the number of stored members in the next dimension.
3. Repeat until the product of stored sparse members exceeds 2⁵² (4,503,599,627,370,500).
4. Back up one dimension. The 1^st sparse dimension down to this dimension makes up the first “group”. The idea is that a group’s stored member product cannot exceed 2⁵².
5. Start multiplying the stored members from each subsequent sparse dimension together. These dimensions represent the 2^nd group. If the product exceeds 2⁵² on the second group, the limit has been reached and the dimension build will fail.

Here is an example of two groups of sparse dimension members:

When building the dimensions in the BSO cube described above, Group 1 ends after the 6^th sparse dimension (“Sparse_06”). This is because including the next dimension (“Sparse_07”) would cause the sparse member combinations in that group to exceed 2⁵². As soon as Group 1 is as full as possible without exceeding this limit, Group 2 begins. Unfortunately, we can see that the dimension build fails at the 10^th sparse dimension (“Sparse_10”), because the second group exceeds 2⁵² and we are only allowed a maximum of two groups of 2⁵².

Hybrid and Dynamic Sparse Members

Readers who are familiar with Essbase Hybrid Aggregation Mode may recall that one of the key design elements in a Hybrid cube involves leveraging dynamically calculated sparse parents (a general no-no in BSO, but required in Hybrid). However the “Two Groups of 2⁵²” limit is all based upon stored sparse member combinations. So could a BSO cube that failed due to this limit potentially work using Hybrid? Maybe. It all depends on how many sparse members are changed from “stored” to “dynamic” during the conversion to Hybrid Aggregation Mode.

Conclusion

If you’re thinking about taking the new Essbase Hybrid Aggregation Mode for a spin (and you should!), remember that size does matter. Some ASO cubes may not be suitable for conversion to Hybrid due to BSO size limitations. But remember that a key feature of Hybrid cubes involves setting upper-level sparse members to “Dynamic”, thus reducing the number “Stored” members in a sparse dimension. This change may create some additional “headroom” before you actually hit the “Two Groups of 2⁵²” limit with a Hybrid cube.

If you would like to hear more about this topic, please plan on attending Infratects’ Top Gun US conference on September 17-18. I’ll be presenting on Hybrid Aggregation Mode and will specifically address questions around conversions. As always, I will have a live demo.

Pop Quiz!

In the example BSO cube above, the dimension build fails at the 10^th dimension. Assume the following:

• If the cube remains a BSO cube . . .
• If no members are deleted . . .
• If all dense/sparse settings remain the same . . .
• If all data storage settings remain the same . . .

What could be done to this cube to make the dimension build successful?

Tweet me the answer at @HyperionNerd.

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.

The 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.

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.

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).

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.

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).

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.

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.

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.

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!