MDX in Analysis Services: Calculated Members: Further Considerations and Perspectives - Page 4

Specifying Precedence of Calculation

In the 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.

The 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.

We can 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.

MDX 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.

To 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 actions.

	SOLVE_ORDER = 1

	SOLVE_ORDER = 2

The modified query should appear, in its entirety, in the Query pane as depicted in Illustration 11:

The results dataset appears in Illustration 12:

The results match our expectations, and allow us to substantially meet the requirements of the information consumers.

We 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.

	FORMAT_STRING = '#,##0.00;(#,##0.00)'

	FORMAT_STRING = 'Percent'

The modified query should appear, in its entirety, in the Query pane as depicted in Illustration 13:

The result set appears in Illustration 14:

We 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.

Using 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 practice exercise.

Throughout 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 entire series.

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