Database Reference
In-Depth Information
7.3 aso Queries: how they work
In order to understand how ASo queries work, we first have to understand how ASo
cubes store data. Essbase is all about hierarchies, whether we are talking ASo or BSo.
Some people have described Essbase as essentially an SQL (Structured Query Language)
database with automatic materialized views at each level of the combined dimensional
hierarchies. (
Note:
“materialized view” is the oracle term, “materialized query tables”
is used in DB2, “indexed views” is used in microsoft®
®
SQL Server.) This is close, but it
is an oversimplification. With BSo cubes, the cube calculation process materializes an
aggregated, dense block for every nondynamic, upper-level sparse member combina-
tion. Then, within each of those aggregated dense blocks, it calculates any nondynamic
upper-level dense members. An SQL materialized view is dynamic and changes when
the underlying data changes. In contrast, in BSo, the values in all of the aggregated, cal-
culated dense blocks are not dynamic and are valid only for the level-0 data at the time
of running the calculation. one must rerun the calculation to update them.
ASo cubes work differently. Instead of materializing the view and adding up all of
the data values for each hierarchy combination (as BSo does), ASo essentially materi-
alizes an index (which I will call a bitmap) and attaches it to the input-level data. This
bitmap index is designed to allow every level of the hierarchy to be rapidly calculated
dynamically at the time of the query.
Then, if that still is not fast enough, ASo allows the creation of materialized views of
this bitmap enhanced input data. These are known as Aggregations or Aggregated views.
7.3.1 What Does a Cube Look Like?
Before I go into the details of how ASo creates this multilevel index and the perfor-
mance insights we can learn from those details, let us first look at a physical analog of
an ASo cube.
7.3.1.1 A Cube You Can Hold in Your Hand
how many readers have actually seen an
ASo cube? really, are you sure? Could you describe what you saw? In reality, I think
you will admit that you have seen how an ASo cube can be
used
, but have not seen an
actual cube (can you look into your server's memory and see how all of those transis-
tors lithographed on the memory chips form a cube-like structure?).
once upon a time, over 40 years ago, people would do data analysis using physical
data cards that may or may not have been generated from a computer database. here is
a physical “cube” that was first produced in 1961 (Figure 7.1) (similar guides were avail-
able for gardeners and bird watchers).
I recently discovered that this mineral sort guide is still manufactured and available
for sale today. Can you imagine someone sitting down and patiently punching out these
cards? I am told that users like the portability and visualization of the query process
they get when using this system. In Figure 7.2, you can see a close-up of what a portion
of an ASosamp data card would look like.
When using these cards:
• Punched holes represent the absence of metadata.
• notches represent metadata.
• holes & notches were used for both:
• Level-0 metadata
• upper-level metadata
• Fact data was printed on the card body.
