MDX in Analysis Services: Calculated Members: Introduction - Page 2

To the set of measures we have retrieved, we will add a simple calculated member to provide our information consumers with an Average Revenue Per Unit figure that can be used (as in the present case) in very rough comparisons between Store States, as a simple illustration of the steps involved.

WITH MEMBER [Measures].[Avg Rev Per Unit] AS 
'[Warehouse Sales]/[Units Shipped]'

As we learned in our previous lesson, as well as indirectly in other lessons, MDX allows us to perform calculations on ranges of cells, leveraging our programming efforts over those of programs that require us to implant them at the cell level. We are able to place calculations in our query axes, making them new members of the dimensions involved. The cells that appear within these members present the query results.

Within the realm of MDX, calculated members can be created a couple of main ways. The first method of creation, using the WITH operator, will be our focus in this and numerous subsequent lessons. The WITH operator in an MDX query creates a dynamic calculated member; that is, the member, and therefore the calculation that the member performs, only exists during the runtime life of the query. The second means of creating a calculated member is through the use of the CREATE MEMBER statement. Calculated members that are born this way are "permanent," and can be made visible to all clients that can access the cube in which the calculated member is housed. Non-query-defined calculated members are beyond the scope of our current lesson, although we will visit them in later tutorials.

The Query pane display, with modifications in place, should be identical with that shown in Illustration 5.

The results dataset that appears is identical to the result set that we obtained before adding the new calculated member. This presents an opportunity to make a further "adjustment" to the .Members function we added above, and to illustrate how our queries can be altered to make calculated members in a cube visible in a results dataset.

The Query pane display, with modifications in place, now appears as depicted in Illustration 6.

The results dataset appears as depicted in Illustration 7:

We now see the existing calculated member - a calculated measure (the most common type of calculated member), and therefore a member of the Measures dimension, as we have stated in earlier lessons. The .AllMembers function allows us to see calculated members as well as the regular members.

Our calculated member is dynamically created in the MDX query, as opposed to having been created in the cube structure; as we noted above, its life extends only for the runtime of the query. Calculated members that we create via the CREATE keyword, or, more visually, through the Analysis Services interface, are permanent and available for retrieval by any process with appropriate access.

To finalize, let's format (rounding to one-tenth of a cent) the lengthy strings of digits that have been spawned by the Sample Application. We add the following instruction to the end of the WITH clause we used to create the calculated member, (added expression is shown in blue):

WITH MEMBER [Measures].[Avg Rev Per Unit] AS 
'[Warehouse Sales]/[Units Shipped]', format = '$ #,###.000'

The final view of the dataset appears as shown in Illustration 8:

The WITH operator can be used in MDX queries, as we have seen above, to define our calculated members, among other uses. WITH precedes the SELECT clause in the statement, stipulating a section where the calculated member (together with named sets) is defined in the query. We need only use the Member clause, preceded by the WITH keyword, for each calculated member we wish to add. (It is also important to remember that, as we have seen in our practice example, to cause calculated members to appear along with the "regular members," we need to insert the .AllMembers function.) We will practice this again, with variations, as we advance through this lesson and in later lessons.

Next in Our Series ...

In this tutorial, our efforts have shown the relative ease with which we can create and display calculated members within our queries to meet various business needs. Our introduction in this lesson to dynamic calculated members has set the stage for the more advanced functionality and processes of calculated members that we will explore in following lessons. We discussed the two main ways of handling the creation of calculated members within MDX, focusing on the use of the WITH operator to create a dynamic calculated member in an MDX query. Then, we practiced the creation of a straightforward calculated member, deriving a useful value from existing measures, and formatting the new calculated measure to meet an illustrated objective.

In our next lesson, Calculated Members: Further Considerations and Perspectives, we will examine multiple additional perspectives of calculated members. In addition, we will explore a means for controlling the order in which calculated members are solved, exposing why precedence of calculation might be critical to generating expected results.

» See All Articles by Columnist William E. Pearson, III