Cleaning Out the Closet: Aggregate View Formula
I’m going through a lot of old projects at the moment, deciding what to publish, what to tidy up
and what to quietly let go. One of them is a folder from 2017 called aggregate-view-formula: one
Java file, a pom.xml, and a README whose entire contents were the word “Thanks” and a link.
It’s now on GitHub under the Apache 2.0 license. It doesn’t serve a whole lot of purpose these days, but it’s out of the closet.
What it does
ASO cubes answer queries faster when the right aggregate views exist. Essbase will choose views for
you with execute aggregate selection, but if you know your cube and your users’ queries, you can
choose them yourself: write the view IDs into a view file (a .csc) and run
execute aggregate build ... using view_file.
The catch is the view ID. It’s a single number that stands for one level choice in every stored dimension, and working one out by hand is no fun. Back in 2014, Essbase’ish Stuff published an Aggregate View Builder, a web page that does exactly that, and this was my Java take on it. Hence the “Thanks”.
It was wrong
Going back over it before publishing, it turns out the formula was wrong. It worked perfectly on its own example, three dimensions with one, two and three levels, which happens to be the one case where the wrong formula and the right one agree. On ASOsamp, using five views from that post that Essbase actually built:
| View Essbase built | What the 2017 code said |
|---|---|
| 4415 | 10464 |
| 18999 | 37592 |
| 26888 | 70152 |
| 34717 | 135357 |
| 56447 | 594144 |
Not one right. The good news is that it never got published. Small mercies.
How view IDs actually work
A view ID is a mixed-radix number, a bit like an odometer where every wheel has a different number of positions. Take the stored dimensions in outline order, multiply each one’s level by the number of levels in all the dimensions before it, and add it all up. Here’s view 4415 on ASOsamp:
| Dimension | Levels | Level chosen | × | Adds |
|---|---|---|---|---|
| Measures | 1 | 0 | 1 | 0 |
| Years | 1 | 0 | 1 | 0 |
| Time | 4 | 3 | 1 | 3 |
| Transaction Type | 2 | 1 | 4 | 4 |
| Payment Type | 2 | 1 | 8 | 8 |
| Promotions | 2 | 1 | 16 | 16 |
| Age | 3 | 2 | 32 | 64 |
| Income Level | 2 | 1 | 96 | 96 |
| Products | 6 | 4 | 192 | 768 |
| Stores | 7 | 3 | 1,152 | 3,456 |
| Geography | 7 | 0 | 8,064 | 0 |
| 4,415 |
Level 0 always adds nothing, so the input data is view 0, and a dimension with a single level never changes the ID at all. Tim German described the outline of it back in 2016: level 0 is always 0, the dimensions add up, and earlier dimensions get the smaller numbers. He also pointed out a handy consequence: add a new stored dimension at the end of the outline and your existing view IDs still mean the same thing.
Using it
Build it with mvn package, then give it the outline ID, a file listing each stored dimension and
its level count (from query database APP.DB list aso_level_info), and a file with one view per
line:
java -jar target/aggregate-view-formula.jar 4142187940 examples/asosamp-dimensions.txt examples/asosamp-views.txt > VIEWS.csc
Out comes a view file ready for execute aggregate build. The repository has ASOsamp examples and
tests that check the numbers against the views Essbase built, and the
README covers where to find the
outline ID.
That’s one box out of the closet.