Create a Derived Measure
Objective and Business Scenario
In this article, we
will examine the use of a derived measure, as a substitute for, and in contrast
with, an existing calculated measure that represents a performance bottleneck. For
purposes of our practice procedure, we will assume that we have been contacted
by a client to investigate slowdowns in query response time when accessing an
MSAS cube. We have been informed that reporting response times, in general,
have disappointed information consumers throughout the organization. They
specifically indicate that using some of the calculated members in the current
cube structures lead to report performance issues, although they have no idea
as to the root of the problem.
Discussion of the
issues with various consumers has revealed that the client, as is often the
case these days, failed to become involved in the implementation while it was
underway. As the "go live" commitments of the responsible department
loomed, the attention of its manager was suddenly "redirected" by
upper management. The manager allowed a very brief turnover from the
implementation team to a designated employee, whose original position had been
recently off shored. Having assigned the employee the administration of the
MSAS cubes, the manager dismissed the implementation team without further ado.
While the new support
selectee had some experience in a competitor OLAP application, she had spent only
a little "cram" time with MSAS, barely progressing from a popular "Baby
Steps" book that was once popular on the market. She was confident that
she could "figure it out," however, and expressed optimistic
confidence that managing the MSAS applications would be a "piece of cake."
For that matter, she allowed management to assume that she was an MSAS "guru,"
and promised to take over where the implementation team left off, and meet the
information consumers' evolving needs as they arose.
The "Baby Steps"
book, while an excellent introduction to MSAS functionality, failed to equip
the new MSAS Administrator to deal with the involved troubleshooting, processing
and performance tuning considerations of the real world. Understandably, its
focus was teaching MSAS quickly, and in a way that would appeal to
non-technical people of varied backgrounds. After the manner of most "populist"
technical publications, general concepts, versus optimization and best
practices, was, unsurprisingly, the objective.
Upon initially examining
the cubes in place, we have noticed many things that could be changed,
including the use of lengthy description fields versus (surrogate) id's for
member key columns within the cubes, among a host of other items that could be
adjusted to optimize both processing and querying performance. One specific
item that we have identified, and which is the focus of this article, was the
use of various calculated measures when a derived measure might
have been constructed. While we will take a single example of this for
purposes of our practice exercise, we have actually found several instances
where the same procedures might be applied to build and substitute derived
measures within the MSAS cubes we have examined at the client location.
The calculated measure
upon which we will concentrate in our present article was constructed within
the Sales cube to generate a store cost value, with a fixed
allocation of marketing costs added. The adjusted cost value,
constructed using the calculated measure under consideration, applied an added
fifteen percent to the store cost for products that were subjected to
given marketing promotions. Products that were not marketed under specific
promotions were assigned only the original store cost amounts to their
adjusted cost values. Again, this calculated measure is only one example of
several such constructions that, as we shall see, can be managed in a way that
promotes more efficient query processing.
Considerations and Comments
For purposes of this
exercise, we will create the calculated measure as constructed by the
erstwhile guru, using the Sales sample cube that accompanies the
installation of MSAS. We will then construct a derived measure that
will generate the same values, but with enhanced query processing time.
Unlike calculated
members (including, of course, calculated measures), whose values are created
at runtime, based upon the MDX expression(s) they contain, a derived measure,
just as any other cube measure, is calculated during the cube processing cycle,
and is stored within Analysis Services. In the case of the calculated member,
only its definition is stored, and the values it renders are calculated in
memory when specified in a query. Because only the definition is stored,
cube processing time ("build time") is not materially increased, and,
indeed, depending upon the environment, this might be an appropriate tradeoff.
In our present scenario, however, query processing is the more important
concern, so we lose the primary benefit behind the choice of a calculated
measure to provide the needed values.
Derived measures differ from
"ordinary" measures because they take advantage of the flexibility
that Analysis Services offers us in modifying the source column property
for a given measure. Because they are stored in the cube file, as we have
mentioned, they typically mean more efficient query processing. Derived
measures, by their nature, are calculated prior to the creation of
aggregations. (In contrast, calculated measures are calculated after
aggregations are created.) In general, derived measures make sense if they
will be called upon frequently, as in reporting scenarios such as that of our
hypothetical information consumers. Calculated measures might be a better choice
for values that we expect to be used infrequently, again because the tradeoff
is tipped more in favor of a lower overhead on the cube processing side, where
query response time might not be as high a priority.
We are
limited to creating derived measures from the columns of the fact table, since
MSAS essentially only offers these columns as options in the measure creation
process. However, as many of us have found, a view can be created to contain
columns that lie outside the physical fact table, making this limitation a bit
less restrictive than it might appear at first blush. Derived measures can
extend well beyond simple math, as we shall see in our example, and can apply
conditional logic and other nuances in derivation of the measure. The syntax
obviously has to fit the database - we will note in our practice example that
we use MS Access - friendly syntax, because the FoodMart 2000 data source ships
as an MS Access .mdb.
If the sample FoodMart
MSAS database, or for that matter the FoodMart .mdb that underlies it, was not
installed, or was removed prior to your beginning this article, please see the
MSAS documentation, including the Books Online, for the
straightforward procedure to restore the database from the archive (.cab)
file containing the samples. As of this writing, a copy of the archive can be
obtained from the installation CD or via download from the appropriate
Microsoft site(s).
» 
Comment and Contribute
