Database Reference
In-Depth Information
query resolves the same results in approximately six seconds. now that is not much
of an argument for one against the other; however, in both cases, we are returning all
level-0 members and, therefore, allowing the calculation to execute. What if we change
[Jan] to the nonlevel-0 member [time]? The second query returns in approximately
four seconds.
It is hard for me to draw any definitive conclusions from such a simple test, particu-
larly on what is a relatively small ASo database. however, my experience with larger
databases leans toward this theory: nested case statements resolve quicker than mul-
tiple AnD logic statements. I have seen considerable improvement in some cases that
has led me to this belief. my speculation is that it takes considerably more resources to
resolve a large array that would be created by multiple AnD statements than it would
to evaluate the case statements line by line. While I will not state this is a hard rule that
will yield better results in all situations, I feel it is worthy of mention and that developers
should explore and test in their own environments to see if they can improve perfor-
mance when working with calculations such as this one.
6.9 time FunCtionality
Without a doubt, the primary use for mDx in Essbase ASo applications is to implement
time calculations. When talking about “time Calculations,” I am primarily referring
to Period-to-Date calculations and, secondly, to time Balancing. other uses for time
calculations might involve trending and rolling calculations.
Let's begin with Period-to-Date functionality. This is one of the areas where BSo
developers get stuck when setting up their first ASo applications. Block Storage has an
incredibly useful feature known as dynamic-time-series (DtS). DtS allows BSo devel-
opers to easily implement Period-to-Date functionality without any customization. It is
an “out of the box” feature that works extremely well. Another advantage in BSo data-
bases is that the time dimension is very often set as a dense dimension. Without getting
into diferences between dense and sparse dimensions (see other chapters in this topic),
time period calculations that are calculated within the same block when it is in memory
are exceptionally efficient.
In the ASo world, where dimensionality is inherently sparse and all non-Stored,
non-Additive calculations are dynamic, time calculations can run into some serious
performance lags. As with most cases, it comes down to the complexity of the applica-
tion, how large the dimensions are, Dynamic versus Stored hierarchies, etc. For smaller
ASo applications (note that a small ASo app can still be pretty large), mDx can be a
quick and easy way to perform time calculations. For larger, more complex applications,
more thought will need to be put into the outline design. most solutions will require
some mDx.
6.9.1 Period-to-Date
Let us discuss some of the various ways you can achieve Period-to-Date (PtD) func-
tionality. keep in mind that this chapter is about mDx, so I am not going to get into too
many details about the non-mDx ways of performing time calculations. however, we
cannot have this conversation without speaking briefly about these other options.
The first method of generating PtD calculations in an ASo database is to utilize
multiple hierarchies in the time dimension. A review of the ASosamp application that
ships with Essbase shows this approach. using this method involves building out the
