more detail without more data and performance losses

In “design guidelines”, “new to Essbase” by Andrea Leandri

Is this for real?? In some cases, design decisions are obvious but in other cases the answers are not as clear cut (that being most of the time) as requirements keep pouring in and seem to force to add and add and add members on your dimension model. Here is the good news: your only weapons are not just dense and sparse dimensions. There are also UDAs, alternate hierarchies and attribute dimensions.

In this blog post I want to review with you the most important design decision points that will guide to choose either of them other than the two basic regular dimensions.

Let’s start with one: Why should I use Attributes?

Attributes are virtual sparse dimensions (eh??): I mean do not require an extra storage and generally don’t impact performance. As such attributes can be used to create cross tab reports, for example, having customer names (as customer dimension) in the rows and customer types (the attribute: small, medium,large) in the columns.

Like a regular dimension, you are not forced to display all choices in the grid but you can use it in the page view to slice the level of detail and let user toggle it. Even further, a regular dimension forces to make a choice, the attribute dimension is by default hidden (therefore set to all) and can be made available on request. So if you don’t need it, it doesn’t add an extra complexity on your reports!

Attributes can be used in calculation logics to also FIX and ENDFIX on and that might very handy when you need to apply different assumptions based on different characteristics, e.g. of products.

And circling back to the start, the advantage of attributes is a practical addition of a “dimension” which doesn’t impact database size.

So why not use Attributes instead of regular dimension?

Ah! Attribute members are labels which can be applied only on spare dimensions . Attribute members are not as smart as regular dimensional members. They don’t offer all the properties choices, they cannot have a formula embedded within.

They are simply virtual labels uncaptured in a virtual, on request, dimension. The ones that played with dynamic calcs in sparse members before will realize soon enough where I’m going to land.

The filtering applied by attributes could be slow: whatever dynamic calc formula member on the account that is engaged against a series of sparse members labeled with a specific attribute choice will have to calculated for you on the moment of request. Essbase will not be able to leverage the pre-calculated stored data but will have to process it on the fly which could turn for large excel analysis into a performance issue. Who wants to wait more than 2 seconds for refreshing data in a report, right?

And what about UDAs and Alternate Hierarchies?

Shared members in alternate hierarchies can have applied security. Attributes and UDAs simply not. UDAs and alternate hierarchies can be created in dense and sparse dimensions. Alternate hierarchies are a great way to reuse the same data without affecting database size while providing different perspective. The legal structure of your company, normally englobed in the entity dimension, might be a lot different than your managerial structure but require also some type of information disclosures.

UDAs are as virtual as attribute members except they are not uncaptured by a dimension and cannot be used to slice a set of data in a report while they can be used in calculation logics.

But the great thing about all three of them, and I’d like to stress this again again, is that they are all wonderful ways to not increase your database size: no data explosions, no performance losses.

about the author

Andrea Leandri


I enjoy solving customer puzzles in Oracle's Planning & Budgeting projects. When everything comes together, it feels like taming a wild beast really. I have learned the do’s and don’ts thanks to a lot of practice and blogs like this one, well not exactly like this one ;-). In the process I cannot deny to have had my share of mistakes. By sharing my knowledge with you my goal is to save your time and resources. Enjoy!

share this post