Precedence of Calculation
multiple-step example above, we have created two calculated members, Warehouse
Margin and % Change, to meet the demands of the information consumers
we have referenced throughout our lesson. We placed the % Change member
on the column axis, and we made the Warehouse Margin member a row axis
member, through our query design.
two measures intersect, in our example, at the cells whose coordinates are the %
Change column and the Warehouse Margin row for each of the three
cities, within the result dataset of the MDX query. Our focus at this stage is
the behavior of this resulting "corner" cell: Depending on the order
in which the two calculations are processed, the result in the "corner"
(or intersect) cell is different.
easily assume that what the consumers want here is a result that represents the
change in overall Warehouse Margin between Q2 and Q1. The
number that we see is apparently not correct from that perspective. This
brings us to another implicit requirement for our information product: We need
to be able to dictate, in this and similar situations, which calculation takes
precedence in the design of our query.
provides a solution in the form of the Member Solve Order property. The
solve order is specified when the member is created, and thus becomes a feature
of the WITH clause we have constructed. Member Solve Order is
designated through the assignment of a number, which dictates that the
member(s) with the higher assigned number(s) be processed after the member(s)
with the lower number(s), based upon the results of the preceding
calculation(s). The number is defaulted to zero if no positive integer value
is specified. Member Solve Order allows us to control the precedence of
calculation of the members, as we shall see.
restate our objective within this step, we need to derive and present the
change in Warehouse Margin between Q2 and Q1, not to
present an amount based upon the difference in the Warehouse Sales and
Cost delta values. We thus want the Warehouse Margin member to be
calculated first, or to have precedence, and the % Change
member to be calculated thereafter. We accomplish our objective by taking the
following steps within the query that we have saved as MXAS08-1-5 above, and left open for the next
comment line to read --
MXAS08: Tutorial Query - Step 6.
After the '[Measures].[Warehouse
Sales] - [Measures].[Warehouse Cost]', portion of the first WITH
clause, add a comma, then add the following line below it, just above the MEMBER
[Time].[% Change] AS line:
SOLVE_ORDER = 1
After the '100
* ([Time]..[Q2]-[Time]..[Q1])/[Time]..[Q1]' portion of
the WITH clause, add a comma, then add the following line below it, just
above the SELECT keyword:
SOLVE_ORDER = 2
modified query should appear, in its entirety, in the Query pane as depicted
in Illustration 11:
Illustration 11: Step 6,
with Modifications Noted
results dataset appears in Illustration 12:
Illustration 12: Step 6
results match our expectations, and allow us to substantially meet the
requirements of the information consumers.
Save the query
with changes as MXAS08-1-6, leaving it open for the next
have now completed the derivation of the desired information, and set up the
layout for its presentation, through our query design. Provided no further
calculations or other design criteria are imposed, this might be a good time to
clean up our formats. We will put in place some formatting instructions, which
are typically added in the same area of the WITH clause (where the
calculated members and their attributes are defined), just behind the MEMBER
SOLVE ORDER property values, as we will see in the next steps.
comment line to read --
MXAS08: Tutorial Query - Step 7.
Remove the 100
* that we inserted just ahead of the ([Time]..[Q2]-[Time]..[Q1])/[Time]..[Q1]'
part of the WITH clause (in Step 5 above).
comma, followed by the text below, after the SOLVE ORDER = 1 member
property instruction for the Warehouse Margin calculated member we
created in the WITH clause of the query:
FORMAT_STRING = '#,##0.00;(#,##0.00)'
comma, followed by the text below, after the SOLVE ORDER = 2 member
property instruction for the % Change calculated member we created in
the WITH clause of the query:
FORMAT_STRING = 'Percent'
modified query should appear, in its entirety, in the Query pane as
depicted in Illustration 13:
Illustration 13: Step 7,
with Modifications Noted
result set appears in Illustration 14:
Illustration 14: Step 7
draw nigh unto our objectives in meeting the needs of the intended audience.
The recovering CPA in me tends to be accustomed to the amount at the top of a
column being preceded by the dollar sign; this and other formatting can be
easily accomplished - there are many options available.
Save the query
with changes as MXAS08-1-7.
the WITH clause, we have created the two calculated members that deliver
the desired information specified by the information consumers. Inside the WITH
clause lies the MEMBER clause that actually does the work for us (we can
also create Named Sets, a subject we touched upon in Lesson Six, and in articles in our other DatabaseJournal
series, and which we will revisit in later lessons). The MEMBER clause
allows us to create multiple calculated members, as we have seen in our
our lesson, we have focused on the most common form of calculated member that
we can expect to encounter in the business environment, the calculated
measure (a member of the measures dimension). We will continue our
exploration of calculated members in subsequent lessons, indeed, throughout the
Summary and Conclusion ...
In this lesson, we resumed our exploration of calculated members where we left off in Lesson Seven, where we set the stage for the
more advanced functionality and processes of calculated members that we began
to explore in this lesson, and will continue to examine in subsequent lessons.
We discussed ways of handling the creation of calculated members with MDX, and
practiced adding multiple calculated members in a single MDX query,
discussing and exploring additional
perspectives of these MDX components along the way, including format
considerations. Finally, we explored a means for controlling the order in
which calculated members are solved, discussing why precedence of calculation
might be critical to generating the results we expect, and examining an
instance where the SOLVE_ORDER keyword made it possible to bring about
the desired results in a returned dataset.
See All Articles by Columnist William E. Pearson, III