A Few Words about Freedom, Dimensional Irrelevance, and ASO

… Or, BSO PRACTITIONER: REFORM THYSELF!

What Is Dimensional Irrelevance, Anyway?

Quite simply, it’s dimension members that never happen within your cube.  This can occur for Business reasons:  Snowshoes are not stocked in your Florida stores; Chart of Account issues: Departments were defined as valid only in a single Business Unit; or Cube Structure: Working has no meaning when used with Actual.

Dimensional Irrelevance can be caused by increasing the number of dimensions as well as by increasing dimensional diversity (for example, by changing the typical “Local Currency” member in the “Currency” dimension to have children enumerating each of the different currencies).

In other words, OLAP data tends to be very sparse.

Dimensional Irrelevance and BSO

Okay, we all started with BSO, where we learned that Dimensional Irrelevance and sparse data in general can be your enemy.  Sure, BSO’s use of blocks and your choice of sparse and dense settings could reduce the problem, but in the end you still had to worry about sparsity:

• The more sparse blocks, the more blocks to aggregate
• The more sparse blocks, the larger the index file
• Splitting a sparse dimension into multiple dimensions compounds the issue:
  • It multiplies the number of potential blocks
  • It multiplies the number of upper level blocks
• As a result, both your block count and your calc time increased
• With some designs, you would even hit the BSO design limit of 2**104 potential blocks

In fact, one of the most common ways to improve BSO performance is to combine dimensions.  Think of combining the Business Unit and Department dimensions in situations where departments exist within a single Business Unit only (the reverse of the third bullet above).

Dimensional Irrelevance and ASO

With ASO, we do not have these problems.  There is no calc time to increase because all queries are dynamically calculated.  There are no blocks to be multiplied.

Well, the above sentence is not strictly true… but the explanation of why it is not strictly true contains detail that is not directly relevant to this blog (it had to be included, or there would have been complaints that I had not read my own chapter).  Feel free to skip down to End of Detail come back to this detail later:

• Some would consider the time to Aggregate a cube as a calc time; however, aggregation time is not affected by dimensional irrelevance.  It is a function of only two things:
  • The size of the level 0 dat file, which is proportional to the number of level 0 data cells (see my Rule #3 – All queries against the same aggregation level take the same time†)
  • The number of aggregations performed (see my Rule #4 – Do not depend on aggregation or other “Maintenance” to make up for bad design)
• “Blocks” do existwithinASO (kinda, sorta).  They exist in the form of the compression dimension.  Every group of 16 compression dimension members in the outline becomes a block identified by a sparse key made up from the remaining non-sparse dimensions.  Again, there is no relation between dimensional irrelevance and performance:
  • There is no effect when irrelevance is increased/decreased by splitting/combining non-compression dimensions (the combinations of non-compression dimensions, i.e., the number of “potential blocks,” do not matter – only the number of existing combinations, aka “blocks”)
  • There is an effect when splitting or eliminating the compression dimension, but it’s not related to dimensional irrelevance.  Instead, it’s related to the number of “Blocks” in the cube.Note:  Remember that each “Block” of data has overhead. This overhead is the bitmap or key that identifies the sparse combination to which it belongs.  Therefore, splitting or eliminating the compression dimension changes the amount of overhead per data cell as seen in the ratio of the dat file size level 0 data cells.  Increases in the ratio result in increases in the size of the dat file. Dat file size is directly related to performance.
• There can be performance advantages with no compression dimension, and these can greatly outweigh the cost of a larger dat file
• See my Rule #9 – The use of a Compression dimension is not a given; consider and test alternatives, including not having a Compression dimension

End of Detail:  The summary of all of the above is, “With ASO, we do not have these problems.”

Freedom – Or, Life for the Reformed BSO Practitioner

What does the freedom from dimensional irrelevance allow us to do?  Many things in typical BSO cubes were designed with this issue in mind, three in particular:

1. The [Local Currencies] dimension.  Wouldn’t it be nice to be able to see each currency by itself?
2. Compound Entity dimensions – BusinessUnit/department
3. Allocations – Usually, there are large scripts with many rules and levels or layers.  How about an After-Allocation dimension with each of the layers and rules separately visible, with the original as loaded data at the bottom?

My friend Cameron points out that people think lots of extra dimensions make a cube harder to use.  But with Smartview/SmartSlices and/or transparent partitions, these extra dimensions can be hidden from those users who have no interest in them.

Simply splitting up the [Local Currencies] dimension can make currency conversion much simpler.  The typical BSO design comingles currencies so that they have to be converted at level 0 and then aggregated.  But I learned in 4^th grade math:  A*rate + B*rate = (A + B) * rate.  So, why not aggregate them up and then multiply ((A + B) * rate)?

Why do I suggest that you “aggregate them up and then multiply”?  ASO, as you may know, is great at doing the latter ((A + B) * rate); but ASO requires a calculation script when multiplying then aggregating (A*rate + B*rate).  This means writing a script, and running it in batch mode, and worrying about concurrency, and….  Let me know if you think this topic should be expanded upon in a future blog posting.

There’s an additional practice that you can be freed from when designing an ASO solution.  Dimensional irrelevance and calculation times usually result in people either splitting up cubes or adding partitions and using x-refs.  This is not necessary when using ASO.  Dimensional irrelevance is not a problem and as pointed out above, smart slices can be used to make the larger cube look like the old, small cube to those of your users who cannot cope with the change.

Summary: Relax your requirements and do not let large cubes and many dimensions rule your designs.  ASO can handle it.

Next time on the Essbase Mechanic:  ASO Attributes – What Can We Do If We DECREASE Dimensional Irrelevance, and then Increase It Using Attributes – A New Look at Time Dimensions.

† The 12+1 Rules of ASO Designing for Performance are introduced in my chapter:  How ASO Works and How to Design for Performance in the book Designing Essbase Applications.